viewandproc.sql

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

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

declare adjust_cursor Cursor for 
select  goodsno,vendorno,convert(char(8),startdate,112),convert(char(8),enddate,112),
	oprice,nprice,stockflag
from importadjustdetail,importadjust 
where (importadjustdetail.adjustno=importadjust.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,@vendorno,@startdate,@enddate,@oprice,@nprice,@stockflag

while @@fetch_status=0
begin
	exec up_modify_import_price
	     @today,@goodsno,@vendorno,@oprice,@nprice,@startdate,@enddate,@stockflag
	fetch next from adjust_cursor 
	into  	@goodsno,@vendorno,@startdate,@enddate,@oprice,@nprice,@stockflag
end 
close adjust_cursor
deallocate adjust_cursor

go

print 'up_adjust_import_account is Created ok!'
go


if exists ( select * from sysobjects where name='up_InserthRetailFromRetail' and type='p')
  drop Proc up_InserthRetailFromRetail
go

CREATE PROCEDURE up_InserthRetailFromRetail 
AS
 Declare @day Int
 if datepart(hh,getdate())<19 
    Select @day=-1
 else
    Select @day=0

Insert hRetail
	 ( GoodsNo,DeptNo,SaleMan,Casher,PosNo,InvoiceNo,ReceiptNo,BusinessFlag,PayMode,
	  CustNo,Qty,Amt,DisCount,RetailPrice,CostPrice,DisAmt,BatchNo,ud1,ud2,ud3,ud4,inputdate) 
Select GoodsNo,DeptNo,SaleMan,Casher,PosNo,InvoiceNo,ReceiptNo,BusinessFlag,PayMode,
	  CustNo,Qty,Amt,DisCount,RetailPrice,CostPrice,DisAmt,BatchNo,ud1,ud2,ud3,ud4,inputdate
From Retail
Where Convert(char(8),inputdate,112)=Convert(char(8),dateadd(dd,@day,getdate()),112)

go

print 'proc up_InserthRetailFromRetail is Created!'
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 importprice adjust  2001-07-31*/
       Execute up_adjust_import_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
      
      Execute up_InserthRetailFromRetail

       /* added by zhiping bao 2000-12-21 */
       Update a set a.CurQty=b.CurQty,a.retailprice=b.retailprice,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)

       /* added by zhiping bao 2001-06-20 (modify goods evaluateprice with lastcostprice)*/
       Update a set a.EvaluatePrice=b.LastCostPrice,a.NEvaluatePrice=b.lastCostPrice/(1+a.TaxRatio)
       from Goods a,Stock b
       Where a.GoodsNo=b.GoodsNo and Convert(char(8),b.Modifydate,112)=@InputDate and b.DeptNo=@DeptNo
go

print 'proc createdayreport is created'
go

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

Create Proc up_ComputeCostPrice
as
 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 or isnull(InitQty,0) + isnull(ImportQty,0) + isnull(AllocInQty,0)<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
        from stock a,goods b
        Where a.goodsno=b.GoodsNo and isnull(b.CostFlag,'0')='0'
      
        update a set a.CurCostamt=isnull(a.InitCostamt,0) + isnull(a.Importamt,0) + isnull(a.AllocInAmt,0) -
             isnull(a.allocoutcost,0) - isnull(a.SaleCost,0) - isnull(a.RetailCost,0) + isnull(a.Lossamt,0) +
             isnull(a.CheckAmt,0)
        from stock a,goods b
        where a.goodsno=b.goodsno and isnull(b.CostFlag,'0')='0'

	end

go

print 'proc up_computeCostPrice is created!'
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 

	Update stock Set  AdjustAmt = isnull(CurQty * RetailPrice,0) - ( isnull(InitQty * InitRetailPrice,0) + isnull(ImportRetailAmt,0)
			 + isnull(AllocInRetailAmt,0) + isnull(LossRetailAmt,0) - isnull(RetailAmt,0) - isnull(RetailDisAmt,0)
			 - isnull(AllocOutRetailAmt,0) + isnull(CheckRetailAmt,0) - isnull(SaleAmt,0) - isnull(SaledisAmt,0) )

	  /* 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

CREATE PROCEDURE up_UpdateReceiptNO
@TableName Varchar(30),
@DeptNo    Tdept,
@ReceiptNo TReceipt
AS

Declare @No TReceipt
Declare @len int
Declare @Count int

select @no=no from receiptno where name=@TableName and deptno=@deptno
Select @Count=@@RowCount
Select @len=len(@deptNo)
if @DeptNo <> left(@ReceiptNo,@len) Return

     if @count<1 
     begin
         select @no=ltrim(rtrim(@deptno)) + '000001'
         Insert Into receiptno (name,deptno,no) values (@TableName,@deptno,@no)
     end
     else
        if Right(@No,6)='999998'
           Delete from Receiptno where name=@TableName
        else
           update receiptno set no=@ReceiptNO where name=@TableName and deptno=@deptno
go

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

CREATE PROC up_getreceiptno
	@tablename varchar(30),
	@deptno Tdept,
	@receiptno Treceipt output
AS
declare @no integer
declare @temp Treceipt

if (select count(*) from receiptno where deptno=@deptno and name=@tablename)>0   
begin
   select @temp=no from receiptno where deptno=@deptno and name=@tablename
   select @receiptno=left(@temp,4)
   select @no=convert(int,right(@temp,6))+1