costflag.sql

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

if exists (select * from sysobjects where id = object_id('up_ProcessDayRetail') and sysstat & 0xf = 4)
	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 b.CostFlag='1' 

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



/*月初始化*/
if exists (select * from sysobjects where id = object_id('dbo.MonthInit') and sysstat & 0xf = 4)
	drop procedure dbo.MonthInit
go


CREATE PROCEDURE MonthInit(@DeptNO varchar(4),@Year Integer,@Month Integer)	
 AS 
 begin 
    declare @CostMethod char(1)

    select @CostMethod=value from sysparams where name='cost'
    if @CostMethod='1'
        begin
	/*  compute month cost price */
       Update stock set curCostPrice = (isnull(initcostamt,0) + isnull(ImportAmt,0) + isnull(AllocInAmt,0))/
	  Nullif(isnull(InitQty,0) + isnull(ImportQty,0) + isnull(AllocInQty,0),0)

        /* modify abnormal cost price */
       Update stock set curCostPrice = LastCostPrice where curCostPrice is null or curCostPrice < 0
        /* compute out cost amt */
       Update a set 
	    a.LossAmt = a.LossQty * a.curCostPrice,		a.RetailCost=a.RetailQty * a.curCostPrice,
	    a.AllocoutCost = a.AllocOutQty * a.curCostPrice,	a.SaleCost=a.SaleQty * a.CurcostPrice,
	    a.Checkamt=a.CheckQty * a.curCostPrice,		a.CurCostAmt=a.CurQty * a.CurCostPrice,
	    a.AdjustCost= a.CurCostamt - a.CurQty * a.CurCostPrice
        from stock a,goods b
        Where a.goodsno=b.GoodsNo and b.CostFlag='0'
	end

	Update stock Set 
	     AdjustAmt = CurQty * RetailPrice - ( InitQty * InitRetailPrice + ImportRetailAmt + AllocInRetailAmt
			+ LossRetailAmt - RetailAmt - RetailDisAmt - AllocOutRetailAmt + CheckRetailAmt - SaleAmt
		        - SaledisAmt )
	  /* backup to history stock table --- deptstock ---- */
	Insert DeptStock  Select @year,@Month,* from Stock
          /* Initialize current Stock */
	Update Stock set 
		InitQty = CurQty,	InitCostPrice=CurCostPrice,
		InitCostAmt=CurCostAmt,	InitRetailPrice =  RetailPrice,
		ImportQty=0,	ImportAmt=0,	ImportRetailAmt=0,
		AllocInQty=0,	AllocInAmt=0,	AllocInRetailAmt=0,
		AllocOutQty=0,	AllocOutAmt=0,	AllocOutRetailAmt=0,	AllocOutCost=0,
		RetailQty=0,	RetailAmt=0,	RetailCost=0,		RetailDisAmt=0,
		SaleQty=0,	SaleAmt=0,	SaleCost=0,		SaleDisAmt=0,
		LossQty=0,	LossAmt=0,	LossRetailAmt=0,
		CheckQty=0,	CheckAmt=0,	CheckRetailAmt=0,
		AdjustAmt=0,	AdjustCost=0
	Where DeptNo=@DeptNo
 end  

go