viewandproc.sql

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

	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   


/*------------------------------------------------------------------------------------*/
/****** Object:  procedure dbo.up_modify_price    Script Date: 00-4-3 14:47:31 ******/
if (select count(*) from sysobjects where type='p' and name='up_modify_price')>0
	drop proc up_modify_price
go

CREATE PROC up_modify_price
	@today char(8),
	@goodsno Tgoods,
	@deptno Tdept,
	@oretailprice Tprice,
	@nretailprice Tprice,
	@omemberprice Tprice,
	@nmemberprice Tprice,
	@alldept char(1),
	@startdate char(8),
	@enddate char(8),
	@ospecialflag char(1),
	@nspecialflag char(1)
AS
Declare @LocalFlag  char(1)
Declare @EveryPoints  numeric(10,3)

Select @LocalFlag=LocalFlag from Dept where DeptNo=@deptNo
Select @EveryPoints=Cast(value as numeric(10,3)) from sysparams where name='everypoints'
if @EveryPoints=0  Select @EveryPoints=10

if @enddate is null
    Select @EndDate=convert(char(8),dateadd(dd,1,getdate()),112)
if @enddate<@today  Return

if @startdate=@today and (@alldept='1' or @LocalFlag='1')
   begin
     update goods set 
	retailprice=@nretailprice,memberprice=@nmemberprice,spriceflag=@nspecialflag,
        Points=Round(@NRetailPrice/@EveryPoints,3)
	where goodsno=@goodsno
     update deptPs set
         retailprice=@nretailprice,memberprice=@nmemberprice,spriceflag=@nspecialflag 
	where goodsno=@goodsno
   end

if @enddate=@today  and (@alldept='1' or @LocalFlag='1')
    begin
     update goods set 
	retailprice=@oretailprice,memberprice=@omemberprice,spriceflag=@ospecialflag, 
        Points=Round(@ORetailPrice/@EveryPoints,3)
	where goodsno=@goodsno
     /* added by zhiping bao */
     update deptPs set
         retailprice=@nretailprice,memberprice=@nmemberprice,spriceflag=@nspecialflag,
         ModifyDate=Getdate()	where goodsno=@goodsno
     end

go

print 'up_modify_price is created already.'
go

/*------------------------------------------------------------------------------------*/
/****** Object:  procedure dbo.up_adjust_account    Script Date: 00-4-3 14:47:31 ******/
if (select count(*) from sysobjects where type='p' and name='up_adjust_account')>0
	drop proc up_adjust_account
go

CREATE PROC up_adjust_account
AS
declare @today char(8)
declare @goodsno Tgoods
declare @deptno Tdept
declare @oretailprice Tprice
declare @nretailprice Tprice
declare @omemberprice Tprice
declare @nmemberprice Tprice
declare @alldept char(1)
declare @startdate char(8)
declare @enddate char(8)
declare @ospecialflag char(1)
declare @nspecialflag char(1)
declare @auditflag char(1)

if datepart(hh,getdate())<12
	select @today=convert(char(8),getdate(),112)
else
	select @Today=Convert(char(8),dateadd(dd,1,getdate()),112)

declare adjust_cursor Cursor for 
select  goodsno,deptno,convert(char(8),startdate,112),convert(char(8),enddate,112),
	oretailprice,nretailprice,omemberprice,nmemberprice,ospecialflag,nspecialflag,
	alldepart
from adjustdetail,adjust 
where (adjustdetail.adjustno=adjust.adjustno) and (auditflag='1')   
and (convert(char(8),startdate,112)=@today or convert(char(8),enddate,112)=@today) 

open adjust_cursor
fetch next from adjust_cursor 
into  	@goodsno,@deptno,@startdate,@enddate,@oretailprice,@nretailprice,
	@omemberprice,@nmemberprice,@ospecialflag,@nspecialflag,@alldept

while @@fetch_status=0
begin
	exec up_modify_price
	@today,@goodsno,@deptno,@oretailprice,@nretailprice,@omemberprice,
	@nmemberprice,@alldept,@startdate,@enddate,@ospecialflag,@nspecialflag 

	fetch next from adjust_cursor 
	into  	@goodsno,@deptno,@startdate,@enddate,@oretailprice,@nretailprice,
		@omemberprice,@nmemberprice,@ospecialflag,@nspecialflag,@alldept
end 
close adjust_cursor
deallocate adjust_cursor
go

print 'up_adjust_account is created already.'
go



/*产生日报表 */

if exists (select * from sysobjects where id = object_id('dbo.CreateDayReport') and sysstat & 0xf = 4)
	drop procedure dbo.CreateDayReport
go

CREATE PROCEDURE CreateDayReport (@DeptNO varchar(4),@InputDate char(8)) AS 
  
  DECLARE @CheckFlag char(1) 
  declare @day datetime 
  declare @priorday char(8)
  select @day=convert(datetime,@InputDate,112)   
  select @priorday=convert(char(8),dateadd(dd,-1,@day),112)

     /* Process Retail first */
   Execute up_ProcessDayRetail @Inputdate

       	/* Backup Current  stock */
    Insert DayStock (Date,DeptNO,GoodsNo,CurQty,CurAmt,RetailPrice)
     Select @InputDate,@deptno,GoodsNO,CurQty,CurCostAmt,RetailPrice 
	From Stock Where DeptNo=@DeptNo and (CurQty <> 0 or CurCostAmt <> 0)

	/* Insert Prior Day Amt */
  Insert into Dayreport (BusinessDate,DeptNO,GroupNo,InitRetailAmt,InitAmt,nInitAmt)
  Select @InputDate,@DeptNo,GroupNo,CurRetailAmt,CurAmt,NCurAmt
  From DayReport 
  Where BusinessDate=@PriorDay and DeptNo=@DeptNo

	/* Insert New GroupNo */
  Insert into DayReport (BusinessDate,DeptNO,GroupNo)
  Select @InputDate,@DeptNo,GroupNo from Team
  Where GroupNo not in ( select Groupno from Dayreport 
  where deptno=@deptno and businessdate=@Priorday)

	/* Update Retail Amt */
  Update DayReport set 
	DayReport.RetailAmt=VGroupRetail.amt,DayReport.Retailcost=VGroupRetail.CostAmt,
	DayReport.NRetailCost=VGroupRetail.NCostAmt,DayReport.RetailDisAmt=VGroupRetail.DisAmt
  From DayReport,VGroupRetail
  Where DayReport.GroupNO=VGroupRetail.GroupNO and VGroupRetail.Inputdate=@InputDate
        And DayReport.DeptNo=VGroupRetail.DeptNo and VGroupRetail.DeptNo=@Deptno  
	and DayReport.BusinessDate = @InputDate

	/* update Sale Amt */
  Update DayReport set
         DayReport.SaleAmt=VGroupSale.Amt,DayReport.SaleCost=VGroupSale.CostAmt,
	 DayReport.NSaleCost=VGroupSale.NCostAmt,
	 DayReport.SaleDisAmt=VGroupSale.SaleDisAmt,
	 DayReport.SaleRetailAmt=VGroupSale.RetailAmt
  From DayReport,VGroupSale
  Where DayReport.GroupNO=VGroupSale.GroupNO and DayReport.DeptNo=VGroupSale.DeptNo
	And VGroupSale.DeptNo=@DeptNo and VGroupSale.AuditDate=@InputDate
	and DayReport.BusinessDate = @InputDate

	/* Update Loss Amt */
  Update DayReport set
	DayReport.LossAmt=VGroupLoss.Amt,DayReport.NLossAmt=VGroupLoss.NTaxAmt,
	DayReport.LossRetailAmt=VGroupLoss.RetailAmt
  From DayReport,VGroupLoss
  Where DayReport.GroupNO=VGroupLoss.GroupNo and DayReport.DeptNo=VGroupLoss.DeptNo
 	And VGroupLoss.DeptNo=@DeptNo and VGroupLoss.AuditDate=@InputDate
	and DayReport.BusinessDate = @InputDate

	/* Update Import Amt */
  Update DayReport Set
	DayReport.ImportAmt=VGroupImport.Amt,DayReport.NImportAmt=VGroupImport.NTaxAmt,
	DayReport.ImportRetailAmt=VGroupImport.RetailAmt
  From DayReport,VGroupImport
  Where DayReport.GroupNo=VGroupImport.GroupNO and DayReport.DeptNO=VGroupImport.InputDept
  	And VGroupImport.InputDept=@DeptNO and VGroupImport.AuditDate=@InputDate
	and DayReport.BusinessDate = @InputDate

	/* Update AllocIn Amt */
 Update DayReport Set
	DayReport.AllocInAmt=VDestGroupAlloc.Amt,DayReport.NAllocInAmt=VDestGroupAlloc.NTaxAmt,
	DayReport.AllocInRetailAmt=VDestGroupAlloc.RetailAmt
 From DayReport,VDestGroupAlloc
 Where DayReport.GroupNO=VDestGroupAlloc.GroupNO and DayReport.DeptNo=VDestGroupAlloc.DestDept
	And VDestGroupAlloc.DestDept=@DeptNo and VDestGroupAlloc.AuditDate=@InputDate
	and DayReport.BusinessDate = @InputDate

	/* update Allocout Amt */
 Update DayReport Set
	DayReport.AllocOutAmt=VSrcGroupAlloc.Amt,
	DayReport.AllocOutRetailAmt=VSrcGroupAlloc.RetailAmt,
	DayReport.AllocOutCost=VSrcGroupAlloc.CostAmt,
	DayReport.NAllocOutcost=VSrcGroupAlloc.NCostAmt
 From DayReport,VSrcGroupAlloc
 Where DayReport.GroupNO=VSrcGroupAlloc.GroupNO and DayReport.DeptNo=VSrcGroupAlloc.srcDept
	And VSrcGroupAlloc.SrcDept=@DeptNo and VSrcGroupAlloc.AuditDate=@InputDate
	and DayReport.BusinessDate = @InputDate

  	/* Update Cur Amt */
 Update DayReport Set
	DayReport.CurRetailAmt=VGroupStock.CurRetailAmt,
	DayReport.CurAmt=VGroupStock.CurCostAmt,
	DayReport.NCurAmt=VGroupStock.CurNCostAmt
 From DayReport,VGroupStock
 Where DayReport.GroupNO=VGroupStock.GroupNO and DayReport.DeptNo=VGroupStock.DeptNo
	And VGroupStock.DeptNo=@DeptNo
	and DayReport.BusinessDate = @InputDate

    /* Update Check Amt */
if exists ( select * from VGroupChecks where EndDate = @InputDate )
	Update DayReport Set
		DayReport.CheckRetailAmt=VGroupChecks.CheckRetailAmt,
		DayReport.CheckAmt=VGroupChecks.CheckAmt,
		DayReport.NCheckAmt=VGroupChecks.NCheckAmt
	From DayReport,VGroupChecks
	Where DayReport.GroupNo=VGroupChecks.GroupNo and  DayReport.DeptNo=VGroupChecks.DeptNo
		And VGroupChecks.DeptNo=@DeptNo and VGroupChecks.EndDate=@Inputdate
		and DayReport.BusinessDate = @InputDate

   /* Update Adjust Amt */
 Update DayReport  Set
	DayReport.AdjustAmt=
	isnull(DayReport.CurRetailAmt,0) - Isnull(DayReport.InitRetailAmt,0) 
	- isnull(DayReport.ImportRetailAmt,0) -	Isnull(DayReport.AllocInRetailAmt,0) 
        + isnull(DayReport.AllocOutRetailAmt,0) + Isnull(DayReport.SaleRetailAmt,0) 
	+ Isnull(DayReport.RetailAmt,0) + Isnull(DayReport.RetailDisAmt,0)
	- Isnull(Dayreport.LossRetailAmt,0) - Isnull(DayReport.CheckRetailAmt,0),
	DayReport.AdjustCost =
	isnull(DayReport.CurAmt,0) - Isnull(DayReport.InitAmt,0) 
	- isnull(DayReport.ImportAmt,0) - Isnull(DayReport.AllocInAmt,0) 
	+ isnull(DayReport.AllocOutCost,0) + Isnull(DayReport.SaleCost,0) 
	+ Isnull(DayReport.RetailCost,0) - Isnull(Dayreport.LossAmt,0) - Isnull(DayReport.CheckAmt,0),
	DayReport.NAdjustCost =
	isnull(DayReport.NCurAmt,0) - Isnull(DayReport.NInitAmt,0) 
	- isnull(DayReport.NImportAmt,0) - Isnull(DayReport.NAllocInAmt,0) 
	+ isnull(DayReport.NAllocOutCost,0) + Isnull(DayReport.NSaleCost,0) 
	+ Isnull(DayReport.NRetailCost,0) - Isnull(Dayreport.NLossAmt,0) 
	- Isnull(DayReport.NCheckAmt,0) 
 Where DayReport.DeptNO=@deptno and BusinessDate=@InputDate
 
    	/* process adjust */
       Execute up_Adjust_Account

         /* Process day Retail */
       Insert DeptRetail (deptno,RetailDate,GoodsNo,Qty,Amt,DisAmt,CostAmt)
       Select DeptNo,max(Inputdate),GoodsNo,Sum(Qty),Sum(Amt),Sum(DisAmt),sum(round(CostPrice*Qty,2))
       From Retail
       Where Convert(char(8),InputDate,112)=@InputDate and DeptNo=@DeptNO
	Group by DeptNo,GoodsNo

       /* added by zhiping bao 2000-12-21 */
       Update a set a.CurQty=b.CurQty,a.ModifyDate=b.ModifyDate from Deptps a,Stock b
        where a.goodsno=b.goodsno and a.deptno=b.deptno and 
        convert(char(8),b.ModifyDate,112)=Convert(char(8),GetDate(),112)
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



if exists (select * from sysobjects where id = object_id('dbo.up_UpdateCustPrice') and sysstat & 0xf = 4)
	drop procedure dbo.up_UpdateCustPrice
go

Create Procedure up_UpdateCustPrice (@CustNo TVendor,@GoodsNO TGoods,@Price TPrice,@InputDate Tdate)
as
declare @hPrice TPrice
declare @lPrice TPrice

if not exists ( select * from CustPrice where CustNo=@CustNo and GoodsNo=@GoodsNo)
  Insert CustPrice (CustNo,GoodsNo,HighPrice,LowPrice,highDate,LowDate)
  values (@CustNo,@GoodsNo,@Price,@price,@InputDate,@InputDate)
else
   Begin
	Select @hPrice=HighPrice,@LPrice=LowPrice From CustPrice where CustNo=@custNo and Goodsno=@GoodsNo
        if @Price > @hPrice 
             Update CustPrice Set HighPrice=@Price,SubPrice=@hPrice Where CustNo=@CustNo and GoodsNo=@GoodsNo
        if @Price < @lPrice
	     Update CustPrice Set LowPrice=@Price Where CustNo=@CustNo and GoodsNo=@GoodsNo
  end

go


/*------------------------------------------------------------------------------------*/
/****** Object:  procedure dbo.up_getcostprice    Script Date: 00-4-3 14:47:31 ******/
/*取成本价*/
if (select count(*) from sysobjects where type='p' and name='up_getcostprice')>0
	drop proc up_getcostprice
go

CREATE PROC up_getcostprice
	@goodsno Tgoods,
	@deptno Tdept,
	@costprice Tprice output
AS
	select @costprice=curcostprice from stock where goodsno=@goodsno and deptno=@deptno

go
print 'up_getcostprice is created already.'
go

/*单据号*/
if (select count(*) from sysobjects where type='p' and name='up_UpdateReceiptNo')>0
	drop proc up_UpdateReceiptno
go