create procedure 盘点
as
begin tran
declare @id int,@temp int
declare csr1 cursor
for
select 商品ID from 库存表
open csr1
FETCH NEXT FROM csr1 INTO @id
while (@@FETCH_STATUS=0)
BEGIN
select @temp=isnull(sum(出货数量),0) from 出货单 where 商品ID =@id
update 库存表 set 库存量=库存量-@temp where 商品ID=@id
update 出货单 set 是否盘点=1
select @temp=isnull(sum(入货数量),0) from 入货单 where 商品ID =@id
update 库存表 set 库存量=库存量+@temp where 商品ID=@id
update 入货单 set 是否盘点=1
fetch next from csr1 into @id
END
CLOSE csr1
DEALLOCATE csr1
commit
库存表:Z
商品ID,库存量,商品单价,库存金额
ID CO PRICE AMOUNT
出货单:A
单号,商品ID,出货数量,出货金额,是否盘点
NO ID CO PRICE IS
入货单:B
单号,商品ID,入货数量,入货金额,是否盘点
NO ID CO PRICE IS
UPDATE Z SET Z.CO = Z.CO - AA.CO
, Z.AMOUNT = Z.AMOUNT - Z.PRICE * AA.CO
FROM (SELECT SUM(CO) AS CO FROM A GROUP BY ID) AA
WHERE Z.ID = AA.ID
UPDATE A SET A.IS = '1' WHERE A.IS = '0'
UPDATE Z SET Z.CO = Z.CO + BB.CO
, Z.PRICE = ( Z.AMOUNT + BB.PRICE * BB.CO ) / ( Z.CO + BB.CO)
, Z.AMOUNT = Z.AMOUNT + BB.PRICE * BB.CO
FROM (SELECT SUM(CO) AS CO FROM B GROUP BY ID) BB
WHERE Z.ID = BB.ID