yeartr.sql

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

if @Count > 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
Declare @DetailAmt	TAmt
Declare @Dis		TDiscount
Declare @UnitQty  	TQty

Declare  @Goodsno	Tgoods
Declare  @DeptNo	Tdept
Declare  @SaleMan	TStaff
Declare  @Casher	TStaff
Declare  @PosNo		Varchar(4)
Declare  @InvoiceNo	Varchar(12)
Declare  @ReceiptNo    Varchar(12)
Declare  @BusinessFlag Char(1)
Declare  @PayMode	Tinyint
Declare  @CustNo	TVendor
Declare  @Qty		TQty
Declare  @Amt		TAmt
Declare  @DisCount	TDisCount
Declare  @RetailPrice	TPrice
Declare  @Disamt	TAmt
Declare  @BatchNo	TBatch
Declare  @Ud1		Varchar(10)
Declare  @Ud2	        Varchar(10)
Declare  @ud3		Varchar(10)
Declare  @Ud4		Varchar(10)


Select @GoodsNo=GoodsNo,@DeptNo=DeptNo,@SaleMan=SaleMan,@Casher=Casher,@PosNo=PosNo,
	@InvoiceNo=InvoiceNo,@ReceiptNo=ReceiptNo,@BusinessFlag=BusinessFlag,@PayMode=Paymode,
	@CustNo=CustNo,@qty=qty,@Amt=Amt,@Discount=Discount,@RetailPrice=RetailPrice,
	@DisAmt=DisAmt,@BatchNo=BatchNo,@Ud1=ud1,@Ud2=ud2,@Ud3=ud3,@Ud4=ud4
	From Inserted

if @@RowCount <> 1 
      begin
	Rollback
	Return
      end

if left(@GoodsNo,2)='29'
   begin
        Select @PackGoodsNo=@GoodsNo
	Select @PRetailPrice=sum(a.qty * b.RetailPrice) from packgoodsDetail a,goods b
	where a.GoodsNo=b.GoodsNO and a.PackGoodsNo=@PackGoodsNO

	Select @Dis=@Amt/(@Qty * @PRetailPrice)

	Declare mycur Cursor for 
	   Select a.GoodsNo,a.Qty,b.RetailPrice From PackGoodsDetail a,Goods b 
		where a.GoodsNo=b.GoodsNo and a.PackGoodsNo=@PackGoodsNo
	Open mycur
	Fetch mycur into @GoodsNo,@UnitQty,@RetailPrice
	While @@Fetch_Status=0
	  begin
	    Select @DetailQty = @UnitQty * @Qty     
	    Select @DetailAmt = Round(@Dis * @RetailPrice * @DetailQty,2)
	    Select @disAmt = Round(@RetailPrice * @DetailQty,2) - @DetailAmt
            Execute up_InsertRetail   @Goodsno,
		  @DeptNo, @SaleMan, @Casher, @PosNo,  @InvoiceNo,  @ReceiptNo,
		  @BusinessFlag,  @PayMode,  @CustNo,  @DetailQty,  @DetailAmt,  @Dis,
		  @RetailPrice,  @Disamt,  @BatchNo,  @Ud1,@Ud2,@ud3,@Ud4
  	    Fetch mycur into @GoodsNo,@UnitQty,@RetailPrice
          end
	Close mycur
	DealLocate mycur
     Return
  end

     Execute up_InsertRetail   @Goodsno,
	  @DeptNo, @SaleMan, @Casher, @PosNo,  @InvoiceNo,  @ReceiptNo,
	  @BusinessFlag,  @PayMode,  @CustNo,  @Qty,  @Amt,  @DisCount,
	  @RetailPrice,  @Disamt,  @BatchNo,  @Ud1,@Ud2,@ud3,@Ud4
    Commit Tran
go

Print " trigger for posretail is created!"
go

/***************************************************************/
/*		结算用			       */
/**************************************************************/

/* tr_payable_initvalue_insert 供货商应付帐款初始值录入触发器 */
if (select count(*) from sysobjects where name='tr_payable_initvalue_insert') > 0
	drop trigger tr_payable_initvalue_insert
go

create trigger tr_payable_initvalue_insert
on Payable
for insert
as
declare @vendorno TVendor
declare @balanceamt TAmt
declare @businessdate datetime
declare @businesstype char(12)
select @vendorno = VendorNo,@businesstype = BusinessType,@businessdate=BusinessDate,@BalanceAmt = BalanceAmt from inserted
if @businesstype = '期初数'
   exec up_payable_init @vendorno,@businessdate,@balanceamt
go
print 'tr_payable_initvalue_insert is created!'
go


/* tr_importcheck_insert  进货结算表头插入的触发器 */
/* 当结算录入时，该供应商的应付金额减少            */
if (select count(*) from sysobjects where name='tr_importcheck_insert') > 0
	drop trigger tr_importcheck_insert
go

CREATE TRIGGER tr_importcheck_insert 
ON ImportCheck
FOR INSERT 
AS 
declare @checkno TReceipt
declare @VendorNo TVendor
declare @CheckAmt TAmt
declare @BalanceAmt TAmt
declare @Inputdate datetime 
Declare @DeptNo tDept

select @vendorno = VendorNo,@Inputdate=Inputdate,@CheckNo = CheckNo,@CheckAmt = CheckAmt from inserted 
Select @Deptno=left(@CheckNo,len(@CheckNo) - 6)
Execute up_UpdateReceiptNo 'importcheck',@DeptNo,@CheckNo

select @BalanceAmt = isnull(BalanceAmt,0) from Payable 
	where businessdate=(select isnull(max(businessdate),0) from Payable where VendorNo = @VendorNo )		
insert into Payable(VendorNo,BusinessDate,BusinessType,InvoiceNo,SubAmt,BalanceAmt) 
	 values(@VendorNo,@Inputdate,'付款',@CheckNo,@CheckAmt,@BalanceAmt - @CheckAmt)  
go
print 'tr_importcheck_insert is Created!'
go


/* tr_importcheck_delete 当主表删除时，把从表相应记录删除,并修改应付帐的应付金额字段*/
if (select count(*) from sysobjects where name='Tr_ImportCheck_Delete') > 0  
   drop trigger Tr_ImportCheck_Delete

go

CREATE TRIGGER Tr_ImportCheck_Delete
ON IMPORTCHECK
FOR DELETE  
AS  
declare @checkno TReceipt
declare @checktype char(1)  
select @checkno = CheckNo,@CheckType = CheckType from deleted  
If @checktype = '1'  /*单据结算*/
	delete from ImportCheckByReceipt where checkno = @checkno
else if @checkType = '2'  /*单品结算*/
  delete from ImportCheckByGoods where checkno = @checkno
else  /*金额结算*/
	delete from ImportCheckByAmt where CheckNO = @CheckNo
delete from Payable where InVoiceNo= @checkno and BusinessType='付款'

go

print 'tr_ImportCheck_Delete is Created!'
go


/* tr_ImportCheckViaReceipt_insert 当进货结算单据明细插入时修改供货商进货_结算_销售关联表的结算数量、结算金额、未结算数量等字段 */

if (select count(*) from sysobjects where name='Tr_ImportCheckViaReceipt_Insert') > 0  
   drop trigger Tr_ImportCheckViaReceipt_Insert
go

CREATE TRIGGER Tr_ImportCheckViaReceipt_Insert
ON ImportCheckByReceipt 
FOR INSERT  
AS  
DECLARE @ImportNo TReceipt
DECLARE @TaxAmt TAmt  		
SELECT @ImportNo = ImportNo,@TaxAmt = TaxAmt FROM INSERTED 
UPDATE Import_To_Check 
	set CheckQty = isnull(ImportQty,0) + isnull(ExportQty,0), 
		 CheckAmt = round((isnull(ImportQty,0) + isnull(ExportQty,0))*TaxPrice,2), 
		 NoCheckQty = 0, 
		 NoCheckAmt = 0 
WHERE ImportNo = @ImportNo
go

print 'Tr_ImportCheckViaReceipt_Insert is Created!'
go

/* tr_ImportCheckViaReceipt_delete 当进货结算单据明细删除时修改供货商进货_结算_销售关联表的结算数量、结算金额、未结算数量等字段 */
if (select count(*) from sysobjects where name='Tr_ImportCheckviaReceipt_Delete') > 0  
   drop trigger Tr_ImportCheckviaReceipt_Delete
go

CREATE TRIGGER Tr_ImportCheckviaReceipt_Delete
ON ImportCheckByReceipt 
FOR DELETE  
AS  
UPDATE Import_To_Check 
	set CheckQty = 0,
		 CheckAmt = 0, 
		 NoCheckQty = ISNULL(a.ImportQty,0) + Isnull(a.ExportQty,0), 
		 NoCheckAmt = round((ISNULL(a.ImportQty,0) + Isnull(a.ExportQty,0))*a.TaxPrice,2) 
FROM Import_To_Check a,deleted b
WHERE a.ImportNo = b.ImportNo
go

print 'Tr_ImportCheckviaReceipt_Delete is Created!'
go


/* tr_ImportCheckViaGoods_Insert 当结算单品明细插入时修改进货_销售_结算关联表 */
if (select count(*) from sysobjects where name='TR_ImportCheckViaGoods_INSERT') > 0  
   drop trigger Tr_ImportCheckViaGoods_INSERT
go

CREATE TRIGGER Tr_ImportCheckViaGoods_Insert  
ON ImportCheckByGoods 
FOR INSERT  
AS  
declare @ImportNo TReceipt
declare @GoodsNo TGoods
declare @CheckQty TQty
declare @CheckAmt TAmt
select @GoodsNo = Goodsno, @CheckQty = CheckQty, @CheckAmt=CheckAmt, @ImportNo = ImportNo from Inserted
UPDATE Import_To_Check
	SET CheckQty = Isnull(CheckQty,0) + @CheckQty,
		 CheckAmt = Isnull(CheckAmt,0) + @CheckAmt,
		 NoCheckQty = isnull(NocheckQty,0) - @CheckQty,
		 NoCheckAmt = Isnull(NoCheckAmt,0) - @CheckAmt
WHERE GoodsNo = @GoodsNo And ImportNo = @ImportNo
go

print 'Tr_ImportCheckViaGoods_Insert  is Created'
go


/* tr_ImportCheckViaGoods_Delete 当结算单品明细删除时修改进货_销售_结算关联表 */
if (select count(*) from sysobjects where name='TR_ImportCheckViaGoods_Delete') > 0  
   drop trigger Tr_ImportCheckViaGoods_Delete
go

CREATE TRIGGER Tr_ImportCheckViaGoods_Delete  
ON ImportCheckByGoods 
FOR DELETE  
AS
UPDATE Import_To_Check
	SET CheckQty = Isnull(a.CheckQty,0) - b.CheckQty,
		 CheckAmt = Isnull(a.CheckAmt,0) - b.CheckAmt,
		 NoCheckQty = isnull(a.NocheckQty,0) + b.CheckQty,
		 NoCheckAmt = Isnull(a.NoCheckAmt,0) + b.CheckAmt
FROM Import_To_Check a,deleted b
WHERE a.GoodsNo = b.GoodsNo And a.ImportNo = b.ImportNo
go

print 'tr_ImportCheckViaGoods_Delete is Created'
go

/* tr_ImportCheckViaGoods_Update 当结算单品明细修改时修改进货_销售_结算关联表 */
if (select count(*) from sysobjects where name='TR_ImportCheckViaGoods_Update') > 0  
   drop trigger Tr_ImportCheckViaGoods_Update
go

CREATE TRIGGER Tr_ImportCheckViaGoods_Update  
ON ImportCheckByGoods 
FOR UPDATE  
AS
UPDATE Import_To_Check
	SET CheckQty = Isnull(a.CheckQty,0) - b.CheckQty + c.CheckQty,
		 CheckAmt = Isnull(a.CheckAmt,0) - b.CheckAmt + c.CheckAmt,
		 NoCheckQty = isnull(a.NocheckQty,0) + b.CheckQty + c.CheckQty,
		 NoCheckAmt = Isnull(a.NoCheckAmt,0) + b.CheckAmt + c.CheckAmt
FROM Import_To_Check a,deleted b,Inserted c
WHERE a.GoodsNo = b.GoodsNo And a.GoodsNo = c.GoodsNo 
	and a.ImportNo = b.ImportNo And a.ImportNo = c.ImportNo
go
print 'tr_ImportCheckViaGoods_Update is Created!'
go

/* tr_ImportCheckViaAmt_Insert 当结算金额明细插入时修改进货_销售_结算关联表 */
if (select count(*) from sysobjects where name='TR_ImportCheckViaAmt_Insert') > 0  
   drop trigger Tr_ImportCheckViaAmt_Insert
go

CREATE TRIGGER Tr_ImportCheckViaAmt_Insert
ON ImportCheckbyAmt
FOR INSERT
AS
DECLARE @CheckNo  TReceipt
declare @VendorNo TVendor
DECLARE @PayingAmt TAmt
select @CheckNo = CheckNo, @PayingAmt = PayingAmt from Inserted
select @VendorNo = ( select VendorNo from ImportCheck where CheckNo = @CheckNo)
exec up_importcheck_update_by_checkviaamt @VendorNo,@PayingAmt
go

print 'tr_ImportCheckViaAmt_Insert is Created'
go

/* tr_ImportCheckViaAmt_Delete 当结算金额明细插入时修改进货_销售_结算关联表 */
if (select count(*) from sysobjects where name='TR_ImportCheckViaAmt_Delete') > 0  
   drop trigger Tr_ImportCheckViaAmt_Delete
go

CREATE TRIGGER Tr_ImportCheckViaAmt_Delete
ON ImportCheckbyAmt
FOR Delete
AS
DECLARE @VendorNo TVendor
DECLARE @CheckNo TRECEIPT
DECLARE @PayingAmt TAmt
select @CheckNo = CheckNo, @PayingAmt = 0 - PayingAmt from Inserted
select @VendorNo = ( select VendorNo from ImportCheck where CheckNo = @CheckNo)
exec up_importcheck_update_by_checkviaamt @VendorNo,@PayingAmt
go
print 'tr_ImportCheckViaAmt_Delete is Created!'
go

/* tr_ImportCheckViaAmt_Update 当结算金额明细插入时修改进货_销售_结算关联表 */
if (select count(*) from sysobjects where name='TR_ImportCheckViaAmt_Update') > 0  
   drop trigger Tr_ImportCheckViaAmt_Update
go

CREATE TRIGGER Tr_ImportCheckViaAmt_Update
ON ImportCheckbyAmt
FOR UPDATE
AS
DECLARE @VendorNo TVendor
DECLARE @CheckNO TRECEIPT
DECLARE @PayingAmt1 TAmt
DECLARE @PayingAmt2 TAmt
select @CheckNo = CheckNo, @PayingAmt1 = 0 - PayingAmt from Deleted
select @PayingAmt2 = PayingAmt from Inserted
select @PayingAmt1 = @PayingAmt1 + @PayingAmt2
select @VendorNo = ( select VendorNo from ImportCheck where CheckNo = @CheckNo)
exec up_importcheck_update_by_checkviaamt @VendorNo,@PayingAmt1
go

print 'tr_ImportCheckViaAmt_Update is Created'
go


/*  End Create Trigger */