20010108.sql

这是用PB6开发的一个POS管理系统

if exists ( Select * from sysobjects where name='up_Processdayretail' and type='p')
	drop procedure up_Processdayretail
go

Create Proc up_Processdayretail (@InputDate varchar(8))
as
Declare @GoodsNo 	TGoods
Declare @DeptNo 	Tdept
Declare @Amt		TAmt
Declare @DisAmt	TAmt
Declare @Qty		TQty
Declare @CostAmt	TAmt
Declare @CostFlag	char(1)
Declare @CostRatio	numeric(10,3)

Update a set a.CostPrice=(a.amt*b.CostRatio)/a.Qty 
from Retail a,Goods b
Where a.goodsno=b.goodsno and b.CostFlag='1' and a.Qty<>0 and Convert(char(8),a.InputDate,112)=@Inputdate

Declare c1 Cursor for 
   select deptno,goodsno,sum(amt),sum(round(costprice*qty,2)),sum(qty),sum(DisAmt)
   from retail
   Where Convert(char(8),inputdate,112)=@InputDate
   group by deptno,goodsno

Open c1
Fetch c1 into @deptNo,@GoodsNo,@Amt,@CostAmt,@Qty,@DisAmt
while @@Fetch_Status=0
  begin
   Select @CostFlag=isnull(CostFlag,'0'),@CostRatio=isnull(CostRatio,0) from goods Where goodsno=@GoodsNo
   update stock
	set retailqty = isnull(retailqty,0) + @qty,
	    retailamt = isnull(retailamt,0) + @amt,
	    retaildisamt = isnull(retaildisamt,0) + @disamt,
	    RetailCost = Isnull(RetailCost,0) + @CostAmt,
	    curqty = Case @CostFlag When '0' then isnull(curqty,0) - @qty
		     end,
	    curcostamt = Case @CostFlag When '0' then isnull(curcostamt,0) - @CostAmt
			 end,
	    ModifyDate=GetDate()
   where goodsno = @goodsno and deptno=@DeptNO
Fetch c1 into @deptNo,@GoodsNo,@Amt,@CostAmt,@Qty,@DisAmt
end
close c1
dealLocate c1

go


print 'proc up_processdayretail is created!'

go