yeartr.sql

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

		 curcostamt = Isnull(CurCostAmt,0) + @Amt,
		 CurCostPrice = Case When Isnull(CurQty,0) + @Qty = 0  or (Isnull(CurCostAmt,0) + @Amt)/nullif((isnull(CurQty,0) + @AuditQty),0)<=0 then @Price
		   Else (Isnull(CurCostAmt,0) + @Amt)/nullif((isnull(CurQty,0) + @AuditQty),0)
		    End,
		 ModifyDate=GetDate()
	where goodsno = @goodsno and deptno=@destdept

  /* Process Valid Date */
  /* Process Batch */

select @localflag=localflag from dept where deptno=@SrcDept
if @LocalFlag='1' 
update stock
	set allocoutqty = isnull(allocoutqty,0) + @Auditqty,
		 allocoutamt = isnull(allocoutamt,0) + @amt,
		 allocoutcost = isnull(allocoutcost,0) + round(@Auditqty*@costprice,2),
		 allocoutretailamt = isnull(allocoutretailamt,0) + round(@AuditQty*@retailprice,2),
		 curqty = isnull(curqty,0) - @AuditQty,
		 curcostamt = isnull(curcostamt,0) -  round(@AuditQty*@costprice,2),
		 ModifyDate=GetDate()
where goodsno = @goodsno and deptno=@srcdept
  /* Process Valid Date */
  /* Process Batch */

 /* Data from Req,Process Req Table */
   if @Reqno is null Return

	select @ReqAuditQty=auditqty,@Reachqty =qty from req 
	  where receiptno=@reqno and GoodsNo=@GoodsNo
	if @ReqAuditQty > @ReachQty 
		update req
			set qty=qty + @Auditqty
		where goodsno=@goodsno and receiptno=@reqno


GO

print 'tr_allocdetail is created'
go

/****** Object:  Trigger dbo.tr_Lossdetail_insert    Script Date: 00-4-3 14:47:31 ******/
if exists ( select * from sysobjects where name = 'tr_lossdetail_insert')
	drop trigger tr_lossdetail_insert 
go

CREATE TRIGGER tr_Lossdetail_Insert ON LossDETAIL 
FOR INSERT,Update
AS

Declare @GoodsNo TGoods
declare @LossNo TReceipt
declare @amt    TAmt
declare @qty    TQty
declare @RetailPrice TPrice
declare @CostPrice TPrice
declare @DeptNo    TDept
declare @Auditflag char(1)
declare @AuditQty  TQty
declare @LocalFlag char(1)

if @@rowcount > 1 
   return

select @GoodsNo=Goodsno,@LossNo=Lossno,@qty=qty,@AuditQty=AuditQty,
	@RetailPrice=RetailPrice,@CostPrice=Costprice  from inserted
select @Deptno=DeptNo,@Auditflag=AuditFlag from Loss where Lossno=@LossNo
if @AuditFlag='0'
	Return
Select @LocalFlag=LocalFlag From Dept Where DeptNO=@DeptNo

if @LocalFlag <> '1'
	Return

/* update stock */

update stock set  LossAmt=IsNull(LossAmt,0) + round(@Qty*@CostPrice,2),
	LossQty=Isnull(LossQty,0) + @AuditQty,
	LossRetailAmt=Isnull(LossRetailAmt,0) + @Auditqty * @RetailPrice,
	CurQty=Isnull(CurQty,0) + @AuditQty,
	CurCostAmt=isnull(CurCostAmt,0) + round(@AuditQty*@CostPrice,2),
	ModifyDate=GetDate()
   Where GoodsNo=@GoodsNo and Deptno=@DeptNo
  /* Process Valid Date */
  /* Process Batch */

GO

print 'tr_lossdetail is created'
go

/****** Object:  Trigger dbo.saledetail_insert    Script Date: 00-3-31 16:12:04 ******/
if exists ( select * from sysobjects where name = 'tr_saleDetail_insert')
	drop Trigger Tr_SaleDetail_Insert
go

CREATE TRIGGER tr_saledetail_Insert ON SALEDETAIL 
FOR INSERT,Update
AS
Declare @GoodsNo TGoods
declare @SaleNo TReceipt
declare @amt    TAmt
declare @qty    TQty
Declare @AuditQty TQty
declare @RetailPrice TPrice
declare @CostPrice TPrice
declare @CustNo    TVendor
declare @DeptNo    TDept
declare @Price     TPrice
declare @AuditFlag char(1)
declare @Inputdate TDate
declare   @LocalFlag   char(1)

if @@rowcount > 1 
   return

select @GoodsNo=Goodsno,@SaleNo=Saleno,@amt=Amt,@qty=qty,@Price=Price,
	@RetailPrice=RetailPrice,@CostPrice=Costprice,@AuditQty=AuditQty  from inserted
select @AuditFlag=AuditFlag,@custno=custno,@deptno=DeptNo,@InputDate=InputDate
from sale where saleno=@SaleNo

if @AuditFlag='0' 
	Return
Select @LocalFlag=LocalFlag From Dept Where DeptNO=@DeptNo

if @LocalFlag <> '1'
	Return

/* update stock */
update stock set  SaleAmt=IsNull(SaleAmt,0) + @Amt,
	SaleQty=Isnull(SaleQty,0) + @Auditqty,
	SaleCost=IsNull(SaleCost,0) + @AuditQty*@CostPrice,
	SaleDisAmt=Isnull(SaleDisAmt,0) + @RetailPrice * @AuditQty - @Amt,
	CurQty=Isnull(CurQty,0) - @AuditQty,
	CurCostAmt=isnull(CurCostAmt,0) - round(@AuditQty*@CostPrice,2),
	Modifydate=getDate()
   Where GoodsNo=@GoodsNo and Deptno=@DeptNo

/* update cust price */

Execute up_UpdateCustPrice @CustNo,@goodsNo,@Price,@InputDate 

  /* Process Valid Date */
  /* Process Batch */

GO
print 'tr_saledetail is created'
go

if exists ( Select * from sysobjects where name='tr_adjustdetail_Insert' and type='tr')
	Drop Trigger tr_adjustDetail_Insert
go


CREATE TRIGGER TR_adjustDetail_Insert ON dbo.adjustDetail
FOR INSERT,update
AS

declare @no char(10)
declare @localflag char(1)
Declare @AuditFlag char(1)
Declare @AllDepart char(1)
Declare @DeptNo	   TDept
Declare @StartDate TDate
Declare @GoodsNo TGoods
Declare @NRetailPrice TPrice
Declare @NMemberPrice TPrice
declare @NSpriceFlag char(1)
Declare @AdjustNo TReceipt
Declare @EveryPoints numeric(10,3)

select  @AdjustNo=AdjustNo,@GoodsNo=GoodsNo,@NRetailPrice=nRetailPrice,
	@NMemberPrice=NMemberPrice,@AllDepart=AllDepart,@DeptNo=DeptNo,
	@StartDate=StartDate,@NSpriceFlag=NSPecialFlag from Inserted

select @AuditFlag=AuditFlag from Adjust Where AdjustNo=@AdjustNo
if @AuditFlag='0' 	Return

Select @EveryPoints=cast(value as numeric(10,3)) from sysparams where name='everypoints'
if @EveryPoints=0 Select @EveryPoints=10

select @localflag=localflag from dept where deptno=@deptno
if Convert(char(8),@StartDate,112)=Convert(char(8),GetDate(),112)
  begin
      if @AllDepart='1' or @Localflag='1'
	   Update Goods set 
		RetailPrice=@NRetailPrice,MemberPrice=@NMemberPrice,SpriceFlag=@NSPriceFlag,
	        Points=Round(@NRetailPrice/@EveryPoints,3)
		Where GoodsNo=@GoodsNo
  end 

GO
print 'tr_adjustdetail is created'
go

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

CREATE TRIGGER TR_importdetail_Insert ON dbo.importdetail 
FOR INSERT,Update
AS

declare @goodsno Tgoods
declare @deptno Tdept
declare @importno Treceipt
declare @orderno Treceipt
declare @batchno Tbatch
declare @vendorno Tvendor
declare @operator Tstaff
declare @employee Tstaff
declare @paymode Tpaymode
declare @qty Tqty
declare @inputdate Tdate
declare @ntaxprice Tprice
declare @taxprice Tprice
declare @amt Tamt
declare @ntaxamt Tamt
declare @validdate Tdate
declare @destdept Tdept
declare @localflag char(1)
declare @allocno Treceipt
declare @costprice Tprice
declare @auditflag char(1)
declare @businessflag char(1)
declare @RetailPrice TPrice

select  @importno=importno,
	@goodsno=goodsno,
	@qty=auditqty,
	@taxprice=taxprice,
	@ntaxprice=ntaxprice,
	@RetailPrice=RetailPrice,
	@amt=amt,
	@ntaxamt=ntaxamt,
	@batchno=batchno,
	@validdate=validdate
from inserted

select @deptno=inputdept,@vendorno=vendorno,@inputdate=inputdate,@businessflag=businessflag,
@operator=operator,@paymode=paymode,@orderno=orderno,@destdept=deptno,@auditflag=auditflag
from import where importno=@importno

if @auditflag<>'1'  return

if (select count(*) from dept where deptno=@deptno and localflag='1')<1 Return
   
update stock
set curqty=isnull(curqty,0)+@qty,
    lastcostprice=case when @taxprice>0 then @taxprice
                  end,
    importqty=isnull(importqty,0)+@qty,
    importamt=isnull(importamt,0)+@amt,
    ImportRetailAmt=Isnull(ImportRetailAmt,0) + @Qty * @RetailPrice,
    CurCostAmt=  Isnull(CurCostAmt,0) + @Amt,
    ModifyDate=GetDate(),
    CurCostPrice = Case  When isnull(CurQty,0) + @Qty<=0 or (Isnull(CurCostAmt,0) + @Amt)/nullif((Isnull(CurQty,0) + @Qty),0)<=0 then @TaxPrice
			 Else (Isnull(CurCostAmt,0) + @Amt)/nullif((Isnull(CurQty,0) + @Qty),0)
			 end
where goodsno=@goodsno and deptno=@deptno  

if @TaxPrice>0 Execute Up_UpdateVendorPrice @VendorNo,@GoodsNo,@TaxPrice

if @OrderNo is not null Execute Up_UpdateOrders @OrderNo,@GoodsNo,@Qty

/* Process Batch */

/* Process Alloc */
select @localflag=localflag from dept where deptno=@destdept

if @destdept<>@deptno and @localflag<>'1'  
   Execute Up_InsertAlloc @DeptNo,@destDept,@ImportNo,@GoodsNo,@Qty,@taxPrice,@NTaxPrice,
	   @Amt,@NTaxAmt,@ValidDate,@VendorNo,@BatchNo,@RetailPrice,@Operator,@Employee,@BusinessFlag

go

print 'TR_importdetail_Insert is created already.'	
go                                      


/*-----------------------------------------------------------------------------------------*/
/*              Trigger for Tally at the end of day 				*/
/*-----------------------------------------------------------------------------------------*/


if exists ( select * from sysobjects where name = 'tr_Tally_insert')
	Drop Trigger Tr_Tally_Insert
go

CREATE TRIGGER  Tr_tally_Insert ON TALLY 
FOR INSERT
AS

Declare @BusinessDate TDATE
Declare @TallyDate TDATE
Declare @SBusinessDate char(8)
Declare @DeptNo TDept
Declare @Flag char(1)
Declare @Year Integer
Declare @Month Integer

if @@RowCount <> 1 Return
Select @Businessdate=BusinessDate,@Flag=Flag,@DeptNo=DeptNo From Inserted
if @Flag<> '0' Return

Select @SBusinessDate = Convert(char(8),@BusinessDate,112)

Execute CreateDayReport @DeptNo,@SBusinessDate

Select @Year=Year,@Month=Month from months where convert(char(8),EndDate,112)=@SBusinessDate
if @@RowCount > 0
    Execute MonthInit @DeptNo,@year,@Month

Update Tally Set Flag='1' Where BusinessDate=@BusinessDate and DeptNo=@DeptNo

go

print 'tr_tally is created'
go

/* Trigger for PosRetail */
if exists ( select * from sysobjects where name='tr_posretail_insert' and type='tr')
  drop trigger tr_posretail_insert
go

CREATE trigger tr_PosRetail_Insert on posretail
for Insert
as

Declare @PackGoodsNo	TGoods
Declare @PRetailPrice 	TPrice
declare @DetailQty 	TQty