create procedure goods_putout as Begin Tran --填充产品库存表(产品入库表视图中只包括没有计算过的,及状态为0的入库记录,其基表为入库产品表) insert into 产品库存表 (入库时间,数量,单价,商品ID,入库表编号) (select 入库时间,数量,单价,商品ID,入库表编号 from 产品入库表视图 union all select 入库时间,数量,单价,商品ID,退货表编号 from 退货产品表视图 ) order by 商品ID,入库时间if @@Error<>0 Begin Rollback Tran Return 1 end --计算过的作标记(入库产品表) update 入库产品表 set 状态='1' where ID in(select ID from 产品入库表视图)--创建更新表 declare @up table(inid bigint,outid bigint,入库时间 datetime,入库数量 real, 出库数量 real,单价 real,商品ID varchar(100), 发货时间 datetime, 出库编号 varchar(50), 使用者 varchar(50))--创建结果表 declare @result table(id bigint identity(1,1), 发货时间 datetime,数量 real,单价 real,商品ID varchar(100),领料申请表编号 varchar(50),使用者 varchar(50) )--创建入库临时表 declare @in table(id bigint identity(1,1),入库时间 datetime,数量 real,单价 real,商品ID varchar(100),入库表编号 varchar(50))insert into @in(入库时间,数量,单价,商品ID,入库表编号) select 入库时间,数量,单价,商品ID,入库表编号 from 产品库存表 order by 商品ID,入库时间--创建出库临时表(产品出库表视图只包括没有计算过的,及状态为0的出库记录,其基表为领料申请产品表) declare @out table(id bigint identity(1,1),发货时间 datetime,数量 real,商品ID varchar(100),数量1 real,出库编号 varchar(50),使用者 varchar(50))insert into @out(发货时间,数量,商品ID,数量1,出库编号,使用者) select 发货时间,数量,商品ID,数量,领料申请表编号,使用者 from 产品出库表视图 order by 商品ID,发货时间 if @@Error<>0 Begin Rollback Tran Return 2 end --计算过的置位(领料申请产品表) update 领料申请产品表 set 状态='1' where ID in(select ID from 产品出库表视图)while exists (select 1 from @out) --出库临时表存在数据就执行 begin if exists(select 1 from @in) --入库临时表存在数据就执 begin delete from @up --清空更新表 insert into @up (inid,outid,入库时间,入库数量,单价,商品ID,出库数量,发货时间,出库编号,使用者) select i.id,o.id,i.入库时间 ,case when i.数量<o.数量 then i.数量 else o.数量 end ,i.单价,i.商品ID,o.数量1,o.发货时间,o.出库编号,o.使用者 from (select * from @in a where id=(select min(id) from @in where 商品ID=a.商品ID)) i ,(select * from @out a where id=(select min(id) from @out where 商品ID=a.商品ID)) o where i.商品ID=o.商品ID insert into @result select 发货时间,入库数量,单价,商品ID,出库编号,使用者 from @up update @in set 数量=数量-b.入库数量 from @in a,@up b where a.id=b.inid delete from @in where 数量=0
update @out set 数量=数量-b.入库数量 from @out a,@up b where a.id=b.outid delete from @out where 数量=0 end end insert into 产品出库表 (领料申请表编号,发货时间,使用者,商品ID,数量,单价) select a.领料申请表编号,a.发货时间,a.使用者,a.商品ID,a.数量,a.单价 from @result a order by 发货时间--更新产品出库表(实实在在的出库记录) update 产品出库表 set 数量=a.数量 - {fn IFNULL(b.数量, 0)} from 产品出库表 a ,退货产品表视图 b where a.单价=b.单价 and a.商品ID=b.商品ID and a.领料申请表编号=b.领料申请表编号--计算过的标记(退货产品表) update 退货产品表 set 状态='1' where ID in(select ID from 退货产品表视图)--更新产品库存表(实实在在的库存记录) delete 产品库存表insert into 产品库存表 (入库时间,商品ID,数量,单价,入库表编号) select a.入库时间,a.商品ID,a.数量,a.单价,a.入库表编号 from @in a order by 入库时间Commit Tran--为了提高效率,我将计算过的状态置为1,下次就不算了
as
Begin Tran
--填充产品库存表(产品入库表视图中只包括没有计算过的,及状态为0的入库记录,其基表为入库产品表)
insert into 产品库存表 (入库时间,数量,单价,商品ID,入库表编号)
(select 入库时间,数量,单价,商品ID,入库表编号
from 产品入库表视图
union all
select 入库时间,数量,单价,商品ID,退货表编号
from 退货产品表视图 )
order by 商品ID,入库时间if @@Error<>0
Begin
Rollback Tran
Return 1
end
--计算过的作标记(入库产品表)
update 入库产品表 set 状态='1' where ID in(select ID from 产品入库表视图)--创建更新表
declare @up table(inid bigint,outid bigint,入库时间 datetime,入库数量 real, 出库数量 real,单价 real,商品ID varchar(100), 发货时间 datetime, 出库编号 varchar(50), 使用者 varchar(50))--创建结果表
declare @result table(id bigint identity(1,1), 发货时间 datetime,数量 real,单价 real,商品ID varchar(100),领料申请表编号 varchar(50),使用者 varchar(50) )--创建入库临时表
declare @in table(id bigint identity(1,1),入库时间 datetime,数量 real,单价 real,商品ID varchar(100),入库表编号 varchar(50))insert into @in(入库时间,数量,单价,商品ID,入库表编号)
select 入库时间,数量,单价,商品ID,入库表编号
from 产品库存表
order by 商品ID,入库时间--创建出库临时表(产品出库表视图只包括没有计算过的,及状态为0的出库记录,其基表为领料申请产品表)
declare @out table(id bigint identity(1,1),发货时间 datetime,数量 real,商品ID varchar(100),数量1 real,出库编号 varchar(50),使用者 varchar(50))insert into @out(发货时间,数量,商品ID,数量1,出库编号,使用者)
select 发货时间,数量,商品ID,数量,领料申请表编号,使用者
from 产品出库表视图
order by 商品ID,发货时间
if @@Error<>0
Begin
Rollback Tran
Return 2
end
--计算过的置位(领料申请产品表)
update 领料申请产品表 set 状态='1' where ID in(select ID from 产品出库表视图)while exists (select 1 from @out) --出库临时表存在数据就执行
begin
if exists(select 1 from @in) --入库临时表存在数据就执
begin
delete from @up --清空更新表 insert into @up (inid,outid,入库时间,入库数量,单价,商品ID,出库数量,发货时间,出库编号,使用者)
select i.id,o.id,i.入库时间
,case when i.数量<o.数量 then i.数量 else o.数量 end
,i.单价,i.商品ID,o.数量1,o.发货时间,o.出库编号,o.使用者
from
(select * from @in a where id=(select min(id) from @in where 商品ID=a.商品ID)) i
,(select * from @out a where id=(select min(id) from @out where 商品ID=a.商品ID)) o
where i.商品ID=o.商品ID insert into @result
select 发货时间,入库数量,单价,商品ID,出库编号,使用者 from @up update @in set 数量=数量-b.入库数量
from @in a,@up b where a.id=b.inid
delete from @in where 数量=0
update @out set 数量=数量-b.入库数量
from @out a,@up b where a.id=b.outid
delete from @out where 数量=0
end
end
insert into 产品出库表 (领料申请表编号,发货时间,使用者,商品ID,数量,单价)
select a.领料申请表编号,a.发货时间,a.使用者,a.商品ID,a.数量,a.单价
from @result a
order by 发货时间--更新产品出库表(实实在在的出库记录)
update 产品出库表 set 数量=a.数量 - {fn IFNULL(b.数量, 0)}
from 产品出库表 a ,退货产品表视图 b
where a.单价=b.单价 and a.商品ID=b.商品ID and a.领料申请表编号=b.领料申请表编号--计算过的标记(退货产品表)
update 退货产品表 set 状态='1' where ID in(select ID from 退货产品表视图)--更新产品库存表(实实在在的库存记录)
delete 产品库存表insert into 产品库存表 (入库时间,商品ID,数量,单价,入库表编号)
select a.入库时间,a.商品ID,a.数量,a.单价,a.入库表编号
from @in a
order by 入库时间Commit Tran--为了提高效率,我将计算过的状态置为1,下次就不算了