SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Orderinterface]
as
create table #a --创建临时表#a储存查询的结果集
(
ID int IDENTITY (0,1),--自增列从0开始每次增加1
o_id int,--医药网订单ID
o_buyer_internal_code varchar(256),--采购客户ERP内码
o_add_time datetime, --订单创建时间
o_note varchar(512),--订单备注
);
--将查询结果集插入到临时表中
INSERT INTO #a select o_id,o_buyer_internal_code,o_add_time ,o_note from mph_order
where is_fetch = '否'
--创建临时变量
declare
@ii int,--循环更新变量
@i int,
@jj int,--循环更新变量
@j int,
@Ordermaxid int,--千方OrderIndex表最大id
@oo_id int--医药网订单id
set @ii=0 --从0开始
set @jj=0 --从0开始
--查询临时表a的ID总数赋值给i控制循环次数
select @i = count(ID) from #a;
--创建临时变量
declare
@BRec int,--采购客户ERP内码
@ERec int,
@CheckEID int,
@BillEID int,
@KRec int,
@BillDate smalldatetime,--订单创建时间
@ReachDate smalldatetime,
@Billcode varchar(50),
@billtype smallint,
@totalmoney money,
@totalqty numeric(18, 6),
@period tinyint,
@checked tinyint,
@BillOver tinyint,
@Comment varchar(256), --订单备注
@explain char(256),
@plan_id int,
@gspid int,
@Tax smallmoney,
@ChangeID int,
@YS_Weway varchar(200),
@YS_ADDR varchar(200),
@YS_DW varchar(200)
set @ERec=120
set @CheckEID=107
set @BillEID=107
set @KRec=5
set @ReachDate=GETDATE()
set @billtype=300
set @totalmoney=10
set @totalqty=10
set @period=1
set @checked=1
set @BillOver=0
set @explain=''
set @plan_id=0
set @gspid=0
set @Tax=-10
set @ChangeID=1983
set @YS_Weway=''
set @YS_ADDR=''
set @YS_DW=''
create table #c --创建临时表#c储存千方订单表最大id
(
ID int IDENTITY (0,1),--自增列从0开始每次增加1
maxid int,
);
create table #d --创建临时表#c储存千方订单表最大id
(
ID int IDENTITY (0,1),--自增列从0开始每次增加1
o_id int,--医药网订单ID
);
--循环判断
WHILE @ii<@i
BEGIN
select @Billcode= o_id from #a where ID=@ii
create table #b --创建临时表#b储存查询的结果集
(
ID int IDENTITY (0,1),--自增列从0开始每次增加1
od_id int,
od_g_in_sn varchar(128),
maxid int,
od_buy_number int,
od_price decimal(14, 6),
);
select @BRec = o_buyer_internal_code from #a where ID=@ii
select @BillDate = o_add_time from #a where ID=@ii
select @Comment = o_note from #a where ID=@ii
--千方订单插入数据
insert into [OrderIndex](BRec,ERec,CheckEID,BillEID,KRec,BillDate,ReachDate,
Billcode,billtype, totalmoney, totalqty, period, checked, BillOver,
Comment, explain, plan_id, gspid, Tax,ChangeID, YS_Weway, YS_ADDR, YS_DW )
values(@BRec,@ERec,@CheckEID,@BillEID,@KRec,@BillDate,@ReachDate,
@Billcode,@billtype, @totalmoney,@totalqty,@period,@checked,@BillOver,
@Comment,@explain,@plan_id,@gspid,@Tax,@ChangeID,@YS_Weway,@YS_ADDR,@YS_DW)
--千方订单表最大id
select @Ordermaxid = max(BillID) from OrderIndex
--医药网订单详情对应的订单id
select @oo_id = o_id from #a where ID=@ii
--修改中间表中ERP提取状态
UPDATE mph_order SET is_fetch = '是' WHERE o_id = @oo_id
--医药网订单明细对应订单id插入到临时表#b中
INSERT INTO #b (od_id,od_g_in_sn,maxid,od_buy_number,od_price)
select od_id,od_g_in_sn,0,od_buy_number,od_price from mph_order_detail where o_id=@oo_id
select * from #b
select @j = count(ID) from #b;
select count(ID) from #b;
--
if @j = 1
begin
INSERT INTO #c select max(BillID) from OrderIndex
end
UPDATE #b SET maxid = @Ordermaxid where maxid=0
select * from #b
--
INSERT INTO OrderBill(BillID,PRec,KRec,qty,AssQty,price,AssPrice
,total,Tax,TaxPrice,TaxTotal,comment,mxord)
Select maxid,od_g_in_sn,1,od_buy_number,od_buy_number,
od_price,od_price,(od_buy_number*od_price),17,
((od_price*0.17)+od_price),(((od_price*0.17)+od_price)*od_buy_number),
od_id,1
from #b
drop table #b
SET @ii=@ii+1 --更新循环变量
END
