select @ReqAuditQty=auditqty,@Reachqty =qty from req 
	  where receiptno=@reqno and GoodsNo=@GoodsNo
	if @ReqAuditQty > @ReachQty 
		update req
			set qty=qty + @Qty
		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 @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),
	CurCostPrice = Case When Isnull(CurQty,0) + @AuditQty > 0  then (Isnull(CurCostAmt,0) + round(@AuditQty*@CostPrice,2))/nullif((isnull(CurQty,0) + @AuditQty),0)
	End,
	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),
	CurCostPrice = Case When Isnull(CurQty,0) - @AuditQty > 0  then (Isnull(CurCostAmt,0) - round(@AuditQty*@CostPrice,2))/nullif((isnull(CurQty,0) - @AuditQty),0)
	End,
	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_importadjust_insert')
	drop trigger tr_importadjust_insert
go

CREATE TRIGGER TR_Importadjust_Insert ON dbo.ImportAdjust
FOR INSERT,Update
AS

declare @deptno char(4)
declare @AdjustNo char(10)
declare @AuditFlag char(1)
Declare @Deleted int

select @AdjustNo=AdjustNO,@AuditFlag=AuditFlag from inserted
if @@RowCount <> 1 Return

Select @Deleted=count(*) from deleted
Select @DeptNo=left(@AdjustNo,len(@AdjustNo) - 6)

if @Deleted=0 
	Execute up_UpdateReceiptNo 'importadjust',@DeptNo,@AdjustNo
else
    if @AuditFlag='1' and update(auditflag)
       Update ImportAdjust Set AuditDate=GetDate() where AdjustNo=@AdjustNO  /* modifyied 2000-08-04 */

go
print 'tr_importadjust is created'
go



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

CREATE TRIGGER TR_ImportadjustDetail_Insert ON ImportadjustDetail
FOR INSERT,update
AS
Declare @No 		TReceipt
Declare @VendorNO 	TVendor
Declare @TaxRatio 	numeric(8,3)
Declare @Price    	TPrice
Declare @Goodsno	TGoods
Declare @AuditFlag	char(1)
Declare @StartDate	tDate

Select @No=AdjustNO,@GoodsNO=GoodsNO,@Price=NPrice,@StartDate=StartDate from Inserted
Select @AuditFlag=AuditFlag,@VendorNo=VendorNo from ImportAdjust Where AdjustNO=@No

if @AuditFlag='0' Return
if Convert(char(8),@StartDate,112)<>Convert(char(8),GetDate(),112) Return

Select @taxRatio=taxRatio from Goods where goodsno=@GoodsNo

Update Goods Set EvaluatePrice=@Price,NEvaluatePrice=@Price/(1+TaxRatio) Where Goodsno=@GoodsNo

if exists  ( select 1 from vendorprice where goodsno=@goodsno and vendorno=@vendorno)
	Update Vendorprice set TaxPrice=@Price,NTaxPrice=@Price/(1+@TaxRatio) Where Goodsno=@GoodsNo and VendorNo=@VendorNO
else
    Insert VendorPrice (vendorno,goodsno,taxprice,ntaxprice) values (@vendorNo,@goodsno,@price,@price/(1 + @TaxRatio))

go

print 'tr_ImportAdjustDetail_Insert is Created ok!'
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'
         begin
	   Update Goods set 
		RetailPrice=@NRetailPrice,MemberPrice=@NMemberPrice,SpriceFlag=@NSPriceFlag,
	        Points=Round(@NRetailPrice/@EveryPoints,3)
		Where GoodsNo=@GoodsNo
	   Update stock set retailprice=@NRetailPrice where goodsno=@goodsno
	end
  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
declare @count 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)
Select @Year=Year,@Month=Month from months where convert(char(8),EndDate,112)=@SBusinessDate
Select @Count=@@RowCount

if @Count>0 
     Execute up_ComputeCostPrice

Execute CreateDayReport @DeptNo,@SBusinessDate