﻿#20-03-2011 Nabil update two triggers  PurchaseDT_AU and PurchaseDT_AI create last cost in itemtrans


Grant All on *.* to root@"%";

######################################################################################################
#Purchases Tables

Drop Trigger if exists Purchase_BD;
Drop Trigger if exists Purchase_BD;
Delimiter $$
CREATE  Trigger Purchase_BD Before Delete on Purchases
For Each Row Begin
    Delete From PurchasesDt WHERE PurchasesDt.Purchase_id = old.Purchase_id;
END $$
Delimiter;

######################################################################################################
# PurchaseDT Table

Drop Trigger if exists PurchaseDT_AI;
Delimiter $$
CREATE  Trigger PurchaseDT_AI After Insert on PurchasesDT
For Each Row Begin
	UPDATE Purchases SET Purchase_TotalNoOfItem = Purchase_TotalNoOfItem + 1 where Purchases.Purchase_ID = new.Purchase_ID;

	if new.Journal_Type = 'PI' then
    Set @Sign = 1;
	  UPDATE Purchases SET Purchase_TotalQuantity = Purchase_TotalQuantity + new.PurchaseDt_Quantity
	  where Purchases.Purchase_ID = new.Purchase_ID;
  else
    if new.Journal_Type = 'PR' then
      Set @Sign = -1;
    end if;
  end if;

  Set @ItemUnit = 0;
  Select Item_Code, Item_Unit,Item_LastDatePurchase
  into @ItemCode, @ItemUnit ,@LastPurchaseDate
  From Items WHERE Item_Code = new.Item_Code;
  IF @ItemUnit=0 OR @ItemUnit IS NULL  then
	set @ItemUnit=1;
  end if;

  #Ayman 03.12.2009
  Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1;
  if @ModuleDoubleUnit then
  	Set @Price = new.PurchaseDt_Price + @Sign*new.PurchaseDT_Charges/new.PurchaseDt_Quantity
      - new.PurchaseDt_Price*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountAmount/new.PurchaseDt_Quantity;
  	Set @PriceBase1 = new.PurchaseDt_PriceBase1 + @Sign*new.PurchaseDT_ChargesBase1/new.PurchaseDt_Quantity
      - new.PurchaseDt_PriceBase1*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase1/new.PurchaseDt_Quantity;
	  Set @PriceBase2 = new.PurchaseDt_PriceBase2 + @Sign*new.PurchaseDT_ChargesBase2/new.PurchaseDt_Quantity
      - new.PurchaseDt_PriceBase2*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase2/new.PurchaseDt_Quantity;

    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo)
    Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,@Sign*new.PurchaseDt_Quantity,@Price,ifnull(@PriceBase1,0),ifnull(@PriceBase2,0),new.Synchronized,ifnull(@PriceBase1,0),ifnull(@PriceBase2,0),new.Item_UnitCoef,new.Item_Unit2Qty, new.PurchaseDt_ExpireDate, new.PurchaseDt_BatchNo );
  else
  	Set @Price=((new.PurchaseDt_Price/@ItemUnit)+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_Charges/(@ItemUnit*new.PurchaseDt_Quantity))-
		((((new.PurchaseDt_Price/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountAmount/(@ItemUnit*new.PurchaseDt_Quantity)));

  	Set @PriceBase1=((new.PurchaseDt_PriceBase1/@ItemUnit)+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase1/(@ItemUnit*new.PurchaseDt_Quantity))-
		((((new.PurchaseDt_PriceBase1/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase1/(@ItemUnit*new.PurchaseDt_Quantity)));

	 Set @PriceBase2=((new.PurchaseDt_PriceBase2/@ItemUnit)+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase2/(@ItemUnit*new.PurchaseDt_Quantity))-
		((((new.PurchaseDt_PriceBase2/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase2/(@ItemUnit*new.PurchaseDt_Quantity)));

  	if @Price is null then set @Price = 0;end if;
	if @PriceBase1 is null then set @PriceBase1 = 0;end if;
  	if @PriceBase2 is null then set @PriceBase2 = 0;end if;

    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2, Item_ExpireDate, Item_BatchNo)
    #Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.PurchaseDt_Quantity*@ItemUnit),@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2);
    Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.PurchaseDt_Quantity*(Select Item_Unit From Items WHERE Item_Code = new.Item_Code))
    ,@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2, new.PurchaseDt_ExpireDate, new.PurchaseDt_BatchNo);
  end if;

  if new.RelatedDt_Id <> 0 and new.journal_type = 'PI' then
	  Update `StkTransaction` Set QuantityPordered = QuantityPordered - new.PurchaseDt_Quantity
    where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'PO';
  end if;

#  select Purchase_invoiceno into @PInvoice from Purchases where Purchase_Id = new.Purchase_id;
#	select module_freezone into @module from module limit 1;
#	if @module then
#	  update itemCustom set ItemCust_QtyIn = ItemCust_QtyIn + @sign * new.PurchaseDt_Quantity
#  	where Purchase_invoiceNo = @PInvoice and Item_code = new.Item_Code and Warehouse_code = new.Warehouse_Code;
#	  if row_count() = 0 then
#		  insert into itemCustom
#		  set Purchase_invoiceNo = @PInvoice, Item_code = new.Item_Code, Warehouse_code = new.Warehouse_Code,
#	    ItemCust_QtyIn = @sign * new.PurchaseDt_Quantity;
#	  end if;
#  end if;
#  if ((@LastPurchaseDate <=new.Purchase_Date) or (@LastPurchaseDate is null)) AND (new.journal_type = 'PI') AND (@price>0) then
#	  update items set Item_LastDatePurchase= cast(new.Purchase_Date as date),
#		   item_lastcostprice=if(currency_code=new.currency_code,@price,
#		   convertcurFunc(new.currency_code,currency_code,new.Purchase_Date,0,0,'','',@price)),
#		   item_lastcostpriceBase1=@PriceBase1,item_lastcostpriceBase2=@PriceBase2
#  			where item_code=new.item_code;
#  end if;
    set @VarDate:=null;
    select ItemTrans_LastDatePurchase into @VarDate from ItemsTrans where item_code=new.Item_Code;
    if @VarDate is null then
          insert into ItemsTrans (Item_code,ItemTrans_LastDatePurchase,
             itemTrans_lastcostprice,itemTrans_lastcostpriceBase1,itemTrans_lastcostpricebase2,ItemTrans_LastPricePurchase) values
             (new.Item_Code,new.Purchase_date,@Price,@PriceBase1,@PriceBase2,new.PurchaseDt_Price);
    else
        if new.Purchase_date>=@VarDate  then
           update ItemsTrans
            set ItemTrans_LastDatePurchase=new.Purchase_date,
             itemTrans_lastcostprice=@Price,
             itemTrans_lastcostpriceBase1=@PriceBase1,
             itemTrans_lastcostpriceBase2=@PriceBase2,
             ItemTrans_LastPricePurchase=new.PurchaseDt_Price
           where item_code=new.Item_Code;
        end if;
    end if;

			
END $$
Delimiter;

Drop Trigger if exists PurchaseDT_AU;
Delimiter $$
CREATE  Trigger PurchaseDT_AU After Update on PurchasesDT
For Each Row Begin

  if new.Journal_Type = 'PI' then
    Set @Sign = 1;
	  UPDATE Purchases SET Purchase_TotalQuantity = Purchase_TotalQuantity - old.PurchaseDt_Quantity
  	where Purchases.Purchase_ID = old.Purchase_ID;

    UPDATE Purchases SET Purchase_TotalQuantity = Purchase_TotalQuantity + new.PurchaseDt_Quantity
	  where Purchases.Purchase_ID = new.Purchase_ID;
	else
    if new.Journal_Type = 'PR' then
      Set @Sign = -1;
    end if;
  end if;

  Set @ItemUnit = 0;
  Select Item_Code, Item_Unit,item_LastDatePurchase
  into @ItemCode, @ItemUnit ,@LastPurchaseDate
  From Items WHERE Item_Code = new.Item_Code;
  IF @ItemUnit=0 OR @ItemUnit IS NULL  then
	set @ItemUnit=1;
  end if;  
  #toni habshi 3.09.2008


  #delete from StkTransaction where Transaction_id = old.Purchasedt_id and Transaction_Type = old.Journal_Type;
  #Ayman 03.12.2009
  Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1;
  if @ModuleDoubleUnit then
    Set @Price = new.PurchaseDt_Price + @Sign*new.PurchaseDT_Charges/new.PurchaseDt_Quantity
      - new.PurchaseDt_Price*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountAmount/new.PurchaseDt_Quantity;
  	Set @PriceBase1 = new.PurchaseDt_PriceBase1 + @Sign*new.PurchaseDT_ChargesBase1/new.PurchaseDt_Quantity
      - new.PurchaseDt_PriceBase1*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase1/new.PurchaseDt_Quantity;
	  Set @PriceBase2 = new.PurchaseDt_PriceBase2 + @Sign*new.PurchaseDT_ChargesBase2/new.PurchaseDt_Quantity
      - new.PurchaseDt_PriceBase2*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase2/new.PurchaseDt_Quantity;

    #Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty)
    #Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,@Sign*new.PurchaseDt_Quantity,@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty);
    update `StkTransaction` set
    Transaction_Date=new.Purchase_Date
    ,Item_Code=new.Item_Code
    , Warehouse_Code=new.Warehouse_Code
    , Project_Code=new.Project_Code
    ,Costcent_Code=new.Costcent_Code
    ,Currency_Code=new.Currency_Code
    ,QuantityEntered=@Sign*new.PurchaseDt_Quantity
    ,Price=@Price
    ,PriceBase1=@PriceBase1
    ,PriceBase2=@PriceBase2
    ,Synchronized=new.Synchronized
    ,CostBase1=@PriceBase1
    ,CostBase2=@PriceBase2
    ,UnitCoef=new.Item_UnitCoef
    ,Unit2Qty=new.Item_Unit2Qty
    ,Item_ExpireDate = new.PurchaseDt_ExpireDate
    ,Item_BatchNo = new.PurchaseDt_BatchNo
     where Transaction_ID=new.Purchasedt_id and Transaction_Type=new.Journal_Type;


  else
    Set @Price = ((new.PurchaseDt_Price/@ItemUnit)	+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_Charges/(@ItemUnit*new.PurchaseDt_Quantity))-
			((((new.PurchaseDt_Price/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountAmount/(@ItemUnit*new.PurchaseDt_Quantity)));
    Set @PriceBase1 = ((new.PurchaseDt_PriceBase1/@ItemUnit) + (if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase1/(@ItemUnit*new.PurchaseDt_Quantity))-
			((((new.PurchaseDt_PriceBase1/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase1/(@ItemUnit*new.PurchaseDt_Quantity)));
    Set @PriceBase2 = ((new.PurchaseDt_PriceBase2/@ItemUnit)	+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase2/(@ItemUnit*new.PurchaseDt_Quantity))-
			((((new.PurchaseDt_PriceBase2/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase2/(@ItemUnit*new.PurchaseDt_Quantity)));
	  if @Price is null then set @Price = 0;end if;
	  if @PriceBase1 is null then set @PriceBase1 = 0;end if;
	  if @PriceBase2 is null then set @PriceBase2 = 0;end if;

	  #Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2)
    #Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.PurchaseDt_Quantity*(Select Item_Unit From Items WHERE Item_Code = new.Item_Code))
    #,@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2);

    update `StkTransaction` set
     Transaction_Date=new.Purchase_Date
     ,Item_Code=new.Item_Code
     , Warehouse_Code=new.Warehouse_Code
     , Project_Code=new.Project_Code
     ,Costcent_Code=new.Costcent_Code
     ,Currency_Code=new.Currency_Code
     ,QuantityEntered=(@Sign*new.PurchaseDt_Quantity*(Select Item_Unit From Items WHERE Item_Code = new.Item_Code))
     ,Price=@Price
     ,PriceBase1=@PriceBase1
     ,PriceBase2=@PriceBase2
     ,Synchronized=new.Synchronized
     ,CostBase1=@PriceBase1
     ,CostBase2=@PriceBase2
     ,Item_ExpireDate = new.PurchaseDt_ExpireDate
     ,Item_BatchNo = new.PurchaseDt_BatchNo
      where Transaction_ID=new.Purchasedt_id and Transaction_Type=new.Journal_Type;

  end if;

  if new.RelatedDt_Id <> 0 and new.journal_type = 'PI' then
  	Update `StkTransaction` Set QuantityPordered = QuantityPordered + old.PurchaseDt_Quantity
    where Transaction_ID = old.RelatedDt_ID and Transaction_Type = 'PO';

    Update `StkTransaction` Set QuantityPordered = QuantityPordered - new.PurchaseDt_Quantity
    where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'PO';
  end if;

#  select Purchase_invoiceno into @PInvoice from Purchases where Purchase_Id = old.Purchase_id;
#	select module_freezone into @module from module limit 1;
# if @module then
#	  update itemCustom set ItemCust_QtyIn = ItemCust_QtyIn - @sign * old.PurchaseDt_Quantity
#	  where Purchase_invoiceNo = @PInvoice and Item_code = old.Item_Code and Warehouse_code = old.Warehouse_Code;
#    if row_count() = 0 then
#	  	Insert into itemCustom set Purchase_invoiceNo = @PInvoice, Item_code = old.Item_Code,
#      Warehouse_code = old.Warehouse_Code, ItemCust_QtyIn = (-1)*@sign * old.PurchaseDt_Quantity;
#	  end if;
#	end if;

#  select Purchase_invoiceno into @PInvoice from Purchases where Purchase_Id = new.Purchase_id;
#	select module_freezone into @module from module limit 1;
#  if @module then
#	  update itemCustom set ItemCust_QtyIn = ItemCust_QtyIn + @sign * new.PurchaseDt_Quantity
#	  where Purchase_invoiceNo = @PInvoice and Item_code = new.Item_Code and Warehouse_code = new.Warehouse_Code;
# 	if row_count() = 0 then
#	  	insert into itemCustom set Purchase_invoiceNo = @PInvoice, Item_code = new.Item_Code,
#		  Warehouse_code = new.Warehouse_Code, ItemCust_QtyIn = @sign * new.PurchaseDt_Quantity;
#	  end if;
#	end if;
# if ((@LastPurchaseDate <=new.Purchase_Date) or (@LastPurchaseDate is null)) AND (new.journal_type = 'PI') AND (@price>0) then
#	  update items set Item_LastDatePurchase= cast(new.Purchase_Date as date),
#		   item_lastcostprice=if(currency_code=new.currency_code,@price,
#		   convertcurFunc(new.currency_code,currency_code,new.Purchase_Date,0,0,'','',@price)),
#		   item_lastcostpriceBase1=@PriceBase1,item_lastcostpriceBase2=@PriceBase2
#  			where item_code=new.item_code;
#  end if;
set @VarDate:=null;
    delete from itemstrans where item_code=old.Item_Code and ItemTrans_LastDatePurchase=old.Purchase_date;
    select ItemTrans_LastDatePurchase into @VarDate from ItemsTrans where item_code=new.Item_Code;
    if @VarDate is null then
          insert into ItemsTrans (Item_code,ItemTrans_LastDatePurchase,
             itemTrans_lastcostprice,itemTrans_lastcostpriceBase1,itemTrans_lastcostpriceBase2,ItemTrans_LastPricePurchase) values
             (new.Item_Code,new.Purchase_date,@Price,@PriceBase1,@PriceBase2,new.PurchaseDt_Price);
    else
        if new.Purchase_date>=@VarDate  then
           update ItemsTrans
            set ItemTrans_LastDatePurchase=new.Purchase_date,
             itemTrans_lastcostprice=@Price,
             itemTrans_lastcostpriceBase1=@PriceBase1,
             itemTrans_lastcostpriceBase2=@PriceBase2,
             ItemTrans_LastPricePurchase=new.PurchaseDt_Price
           where item_code=new.Item_Code;
        end if;
    end if;

END $$
Delimiter;

Drop Trigger if exists PurchaseDt_BD;
Delimiter $$
CREATE  Trigger PurchaseDt_BD Before Delete on Purchasesdt
For Each Row Begin
  Declare exit handler for 1442 begin end;
	if old.Journal_Type = 'PI' then
    Set @Sign = 1;
  else
    if old.Journal_Type = 'PR' then
      Set @Sign = -1;
    end if;
  end if;

  Set @ItemUnit = 0;
  Select Item_Code, Item_Unit into @ItemCode, @ItemUnit
	From Items WHERE Item_Code = old.Item_Code;
  IF @ItemUnit=0 OR @ItemUnit IS NULL  then
	set @ItemUnit=1;
  end if;
  Delete from StkTransaction where Transaction_id = old.Purchasedt_id and Transaction_Type = old.Journal_Type;
  if old.RelatedDt_Id <> 0 and old.journal_type = 'PI' then
    Update `StkTransaction` Set QuantityPordered = QuantityPordered + old.PurchaseDt_Quantity
    where Transaction_ID = old.RelatedDt_ID and Transaction_Type = 'PO';
  end if;

#  select Purchase_invoiceno into @PInvoice from Purchases where Purchase_Id = old.Purchase_id;
#	select module_freezone into @module from module limit 1;
#  if @module then
#	  update itemCustom set ItemCust_QtyIn = ItemCust_QtyIn - @sign * old.PurchaseDt_Quantity
#	  where Purchase_invoiceNo = @PInvoice and Item_code = old.Item_Code and Warehouse_code = old.Warehouse_Code;
#  	if row_count() = 0 then
#	  	insert into itemCustom set Purchase_invoiceNo = @PInvoice, Item_code = old.Item_Code,
#		  Warehouse_code = old.Warehouse_Code, ItemCust_QtyIn = (-1)*@sign * old.PurchaseDt_Quantity;
#	  end if;
#	end if;

  if old.Journal_Type = 'PI' then
	  UPDATE Purchases SET Purchase_TotalQuantity = Purchase_TotalQuantity - old.PurchaseDt_Quantity
	  where Purchases.Purchase_ID = old.Purchase_ID;
  end if;
	UPDATE Purchases SET Purchase_TotalNoOfItem = Purchase_TotalNoOfItem - 1 where Purchases.Purchase_ID = old.Purchase_ID;
  delete from itemstrans where item_code=old.Item_Code and ItemTrans_LastDatePurchase=old.Purchase_date;
END $$
Delimiter;

########################################################################################################################
# PorderDT Table

Drop Trigger if exists PorderDt_AI;
Delimiter $$
CREATE  Trigger PorderDt_AI After Insert on PorderDt
For Each Row Begin
    Update Porder set Porder_TotalQuantity = Porder_TotalQuantity + new.PorderDt_Quantity
    where Porder.Porder_ID = new.Porder_ID;

    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code,Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityPOrdered,Price,PriceBase1,PriceBase2,Synchronized,UnitCoef,Unit2Qty)
    Values (new.POrderdt_id,new.POrder_Date,'PO',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.POrderDt_Quantity,new.Porderdt_Price,new.Porderdt_PriceBase1,new.ProderDt_PriceBase2,new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty);
END $$
Delimiter;

Drop Trigger if exists PorderDt_AU;
Delimiter $$
CREATE  Trigger PorderDt_AU After Update on PorderDt
For Each Row Begin
    Update Porder set Porder_TotalQuantity = Porder_TotalQuantity - old.PorderDt_Quantity
    where Porder.Porder_ID = old.Porder_ID;

    Update Porder set Porder_TotalQuantity = Porder_TotalQuantity + new.PorderDt_Quantity
    where Porder.Porder_ID = new.Porder_ID;

  	Delete from stkTransaction where Transaction_ID = old.Porderdt_Id and Transaction_Type = 'PO';
    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code,Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityPOrdered,Price,PriceBase1,PriceBase2,Synchronized,UnitCoef,Unit2Qty)
	  Values (new.POrderdt_id,new.POrder_Date,'PO',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.POrderDt_Quantity,new.Porderdt_Price,new.Porderdt_PriceBase1,new.ProderDt_PriceBase2,new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty);
END $$
Delimiter;

Drop Trigger if exists PorderDt_BD;
Delimiter $$
CREATE  Trigger PorderDt_BD Before Delete on PorderDt
For Each Row Begin
	Declare exit handler for 1442 begin end;

	Delete from stkTransaction where Transaction_ID = old.Porderdt_Id and Transaction_Type = 'PO';

	Update Porder set Porder_TotalQuantity = Porder_TotalQuantity - old.PorderDt_Quantity
  where Porder.Porder_ID = old.Porder_ID;
END $$
Delimiter;

########################################################################################################################
# Porder Table

Drop Trigger if exists Porder_BD;
Delimiter $$
CREATE  Trigger Porder_BD Before Delete on Porder
For Each Row Begin
    Delete From PorderDt WHERE PorderDt.Porder_ID = old.Porder_ID;
END $$
Delimiter;

######################################################################################################
# AdjustmentDT Table

Drop Trigger if exists AdjustmentDT_BI;
Delimiter $$
CREATE Trigger AdjustmentDT_BI Before Insert on AdjustmentDt
	For Each Row Begin
	if new.AdjustmentDt_ID is not null then
		set new.Currency_Code = (Select Currency_Code from Adjustment where Adjustment_ID = new.Adjustment_ID);
  end if;
END $$
Delimiter;

Drop Trigger if exists AdjustmentDT_AI;
Delimiter $$
CREATE  Trigger AdjustmentDT_AI After Insert on adjustmentdt
For Each Row Begin
	Select Item_LastDatePurchase
  	into @LastPurchaseDate  From Items WHERE Item_Code = new.Item_Code;

  if new.AdjustmentDt_Quantity > 0 then
	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityEntered,Currency_Code,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo)
	  Values (new.Adjustmentdt_id,new.Adjustment_Date,'AI',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.AdjustmentDt_Quantity,new.Currency_Code,new.AdjustmentDt_Price,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.AdjustmentDt_QtyUnit,ifnull(new.AdjustmentDt_PricePerUnit,0),new.Synchronized,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.AdjustmentDt_ExpireDate, new.AdjustmentDt_BatchNo);

	 /* Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
	  Values (new.AdjustmentdT_ID,new.AdjustmentdT_ID,'AI',new.Size_Measure,new.Color_Code,new.AdjustmentDt_Quantity);
	  */
	else
	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityDelivered,Currency_Code,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo)
	  Values (new.Adjustmentdt_id,new.Adjustment_Date,'AR',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,abs(new.AdjustmentDt_Quantity),new.Currency_Code,new.AdjustmentDt_Price,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.AdjustmentDt_QtyUnit,ifnull(new.AdjustmentDt_PricePerUnit,0),new.Synchronized,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.AdjustmentDt_ExpireDate, new.AdjustmentDt_BatchNo);

	  /*Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
	  Values (new.AdjustmentdT_ID,new.AdjustmentdT_ID,'AR',new.Size_Measure,new.Color_Code,new.AdjustmentDt_Quantity);
	  */
   end if;

     #if ((@LastPurchaseDate <=new.Adjustment_Date) or (@LastPurchaseDate is null) ) AND (new.AdjustmentDt_Price>0) then
     #	  update items set Item_LastDatePurchase= cast(new.Adjustment_Date as date),
	#	   item_lastcostprice=if(currency_code=new.currency_code,new.AdjustmentDt_Price,
	#	   convertcurFunc(new.currency_code,currency_code,new.Adjustment_Date,0,0,'','',new.AdjustmentDt_Price)),
	#	   item_lastcostpriceBase1=new.AdjustmentDt_PriceBase1,item_lastcostpriceBase2=new.AdjustmentDt_PriceBase2
  	#		where item_code=new.item_code;
     #end if;

END $$
Delimiter;

Drop Trigger if exists AdjustmentDT_BU;
Delimiter $$
CREATE Trigger AdjustmentDT_BU Before Update on AdjustmentDt
For Each Row Begin
	if new.AdjustmentDt_ID is not null then
	  set new.Currency_Code = (Select Currency_Code from Adjustment where Adjustment_ID = new.Adjustment_ID);
	end if;
end $$
Delimiter;

Drop Trigger if exists AdjustmentDT_AU;
Delimiter $$
CREATE  Trigger AdjustmentDT_AU After Update on adjustmentdt
For Each Row Begin
	Delete from stkTransaction where Transaction_ID = old.Adjustmentdt_Id and (Transaction_Type = 'AI' or Transaction_Type = 'AR');
	#Delete from StkTransactionSizes where Transactiondt_id = old.AdjustmentDt_ID and (Transaction_Type = 'AI' or Transaction_Type = 'AR');
	if new.AdjustmentDT_Quantity > 0 then
	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityEntered,Currency_Code,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo)
	  Values (new.Adjustmentdt_id,new.Adjustment_Date,'AI',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.AdjustmentDt_Quantity,new.Currency_Code,new.AdjustmentDt_Price,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.AdjustmentDt_QtyUnit,ifnull(new.AdjustmentDt_PricePerUnit,0),new.Synchronized,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.AdjustmentDt_ExpireDate, new.AdjustmentDt_BatchNo);

	  #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
	  #Values (new.AdjustmentdT_ID,new.AdjustmentdT_ID,'AI',new.Size_Measure,new.Color_Code,new.AdjustmentDt_Quantity);
  else
	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityDelivered,Currency_Code,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo)
	  Values (new.Adjustmentdt_id,new.Adjustment_Date,'AR',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,Abs(new.AdjustmentDt_Quantity),new.Currency_Code,new.AdjustmentDt_Price,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.AdjustmentDt_QtyUnit,ifnull(new.AdjustmentDt_PricePerUnit,0),new.Synchronized,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.AdjustmentDt_ExpireDate, new.AdjustmentDt_BatchNo);

	  #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
	  #Values (new.AdjustmentdT_ID,new.AdjustmentdT_ID,'AR',new.Size_Measure,new.Color_Code,new.AdjustmentDt_Quantity);
  end if;
    # if ((@LastPurchaseDate <=new.Adjustment_Date) or (@LastPurchaseDate is null) ) AND (new.AdjustmentDt_Price>0) then
	#  update items set Item_LastDatePurchase= cast(new.Adjustment_Date as date),
	#	   item_lastcostprice=if(currency_code=new.currency_code,new.AdjustmentDt_Price,
	#	   convertcurFunc(new.currency_code,currency_code,new.Adjustment_Date,0,0,'','',new.AdjustmentDt_Price)),
	#	   item_lastcostpriceBase1=new.AdjustmentDt_PriceBase1,item_lastcostpriceBase2=new.AdjustmentDt_PriceBase2
  	#		where item_code=new.item_code;
   #end if;
END $$
Delimiter;

Drop Trigger if exists AdjustmentDT_BD;
Delimiter $$
CREATE  Trigger AdjustmentDT_BD Before Delete on adjustmentdt
For Each Row Begin
  Delete from stkTransaction where Transaction_ID = old.Adjustmentdt_Id and (Transaction_Type = 'AI' or Transaction_Type = 'AR');
	#Delete from StkTransactionSizes where Transactiondt_id = old.AdjustmentDt_ID and (Transaction_Type = 'AI' or Transaction_Type = 'AR');
END $$
Delimiter;

########################################################################################################################
# Adjustment Table

Drop Trigger if exists Adjustment_BD;
Delimiter $$
CREATE  Trigger Adjustment_BD Before Delete on Adjustment
For Each Row Begin
    Delete From AdjustmentDt WHERE AdjustmentDt.Adjustment_ID = old.Adjustment_ID;
END $$
Delimiter;

########################################################################################################################
# DeliveryDT Table

Drop Trigger if Exists DeliveryDt_AI;
DELIMITER $$
CREATE TRIGGER DeliveryDt_AI AFTER INSERT ON deliverydt
FOR EACH ROW BEGIN
	Declare vCheck TINYINT(1);
	select ifnull(Module_ConfirmDR,0) into @module_confirmDR from module limit 1;
	set vCheck = 0;
	if @module_confirmDR = 1 and new.Journal_Type = 'DR' and new.confirmDR = 1 then
		set vCheck = 1;
	end if;
	
	if @module_confirmDR = 0 and new.Journal_Type = 'DR' then
		set vCheck = 1;
	end if;
	
  if new.Journal_Type = 'DI' then
	  set @Sign = 1;
	  set vCheck = 1;
	  UPDATE Deliveries SET Delivery_TotalQuantity = Delivery_TotalQuantity + new.DeliveryDt_Quantity
  	where Deliveries.Delivery_ID = new.Delivery_ID;
	else
	  if new.Journal_Type = 'DR' then
	    set @Sign = -1;
	  end if;
  end if;

  if new.Journal_Type = 'DI' then
    if new.RelatedDt_Id <> 0 then
		  Update `StkTransaction` Set QuantitySordered = QuantitySordered - (new.DeliveryDt_Quantity)
		  where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'SO';
	  end if;
  end if;
	
	if vCheck = 1 then
	Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityDelivered,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_CodePacked,PriceTTC,PriceTTCBase1,PriceTTCBase2, Item_ExpireDate, Item_BatchNo )
        Values (new.Deliverydt_id,new.Delivery_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.DeliveryDt_Quantity),new.DeliveryDt_Price,new.DeliveryDt_PriceBase1,new.DeliveryDt_PriceBase2,new.DeliveryDt_QtyUnit,ifnull(new.DeliveryDt_PricePerUnit,0),new.Synchronized,new.DeliveryDt_CostBase1,new.DeliveryDt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked,new.deliverydt_priceTTC,new.deliverydt_priceTTCBase1,new.deliverydt_priceTTCBase2, new.DeliveryDt_ExpireDate, new.DeliveryDt_BatchNo);

	Select Module_Delivery into @Module From Module limit 1;
	if not @Module then
	  Update `StkTransaction` set QuantitySold = @Sign*new.DeliveryDt_Quantity,Synchronized = new.Synchronized
	  where `StkTransaction`.Transaction_ID = new.DeliveryDt_ID and Transaction_Type = new.Journal_Type;
	  if row_Count() = 0  then
		  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantitySold,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_CodePacked,PriceTTC,PriceTTCBase1,PriceTTCBase2, Item_ExpireDate, Item_BatchNo)
		  Values (new.Deliverydt_id,new.Delivery_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.DeliveryDt_Quantity),new.DeliveryDt_Price,new.DeliveryDt_PriceBase1,new.DeliveryDt_PriceBase2,new.DeliveryDt_QtyUnit,ifnull(new.DeliveryDt_PricePerUnit,0),new.Synchronized,new.DeliveryDt_CostBase1,new.DeliveryDt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked,new.deliverydt_priceTTC,new.deliverydt_priceTTCBase1,new.deliverydt_priceTTCBase2, new.DeliveryDt_ExpireDate, new.DeliveryDt_BatchNo);
	  end if;
	end if;

	select module_freezone into @module from module limit 1;
	if @module = 1 then
	  update itemCustom set ItemCust_QtyOut = ItemCust_QtyOut + (@sign * new.Deliverydt_Quantity)
	  where Purchase_invoiceNo = new.Purchase_invoiceNo and Item_code = new.Item_Code and Warehouse_code = new.Warehouse_Code;
  	if row_count() = 0 then
	  	insert into itemCustom set Purchase_invoiceNo = new.Purchase_invoiceNo, Item_code = new.Item_Code,
      Warehouse_code = new.Warehouse_Code, ItemCust_QtyOut = @sign * new.Deliverydt_Quantity;
	  end if;
	end if;
	
	end if;
END $$
DELIMITER;

Drop Trigger if Exists DeliveryDt_AU;
DELIMITER $$
CREATE TRIGGER DeliveryDt_AU AFTER UPDATE ON deliverydt
FOR EACH ROW BEGIN
Declare vCheck TINYINT(1);
	select ifnull(Module_ConfirmDR,0) into @module_confirmDR from module limit 1;
	set vCheck = 0;
	if @module_confirmDR = 1 and new.Journal_Type = 'DR' and new.confirmDR = 1 then
		set vCheck = 1;
	end if;
	
	if @module_confirmDR = 0 and new.Journal_Type = 'DR' then
		set vCheck = 1;
	end if;
	
  if new.Journal_Type = 'DI' then
	  set @Sign = 1;
	  set vCheck = 1;
	  
	  UPDATE Deliveries SET Delivery_TotalQuantity = Delivery_TotalQuantity - old.DeliveryDt_Quantity
	  where Deliveries.Delivery_ID = old.Delivery_ID;

    UPDATE Deliveries SET Delivery_TotalQuantity = Delivery_TotalQuantity + new.DeliveryDt_Quantity
	  where Deliveries.Delivery_ID = new.Delivery_ID;
  else
	  if new.Journal_Type = 'DR' then
		  set @Sign = -1;
    end if;
  end if;

  if old.Journal_Type = 'DI' then
	  if old.RelatedDt_Id <> 0 then
      Update `StkTransaction` Set QuantitySordered = QuantitySordered + (old.DeliveryDt_Quantity)
		  where Transaction_ID = old.RelatedDt_ID and Transaction_Type = 'SO';
		  Update `StkTransaction` Set QuantitySordered = QuantitySordered - (new.DeliveryDt_Quantity)
		  where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'SO';
	  end if;
	end if;
	
	if  vCheck = 1 then
	Delete from StkTransaction where Transaction_ID = old.DeliveryDt_ID and Transaction_Type = old.Journal_Type;
	Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityDelivered,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_CodePacked,PriceTTC,PriceTTCBase1,PriceTTCBase2, Item_ExpireDate, Item_BatchNo)
	Values (new.Deliverydt_id,new.Delivery_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.DeliveryDt_Quantity),new.DeliveryDt_Price,new.DeliveryDt_PriceBase1,new.DeliveryDt_PriceBase2,new.DeliveryDt_QtyUnit,ifnull(new.DeliveryDt_PricePerUnit,0),new.Synchronized,new.DeliveryDt_CostBase1,new.DeliveryDt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked,new.deliverydt_priceTTC,new.deliverydt_priceTTCBase1,new.deliverydt_priceTTCBase2, new.DeliveryDt_ExpireDate, new.DeliveryDt_BatchNo);

	Select Module_Delivery into @Module From Module limit 1;
  if not @Module then
    Update `StkTransaction` set QuantitySold = @Sign*new.DeliveryDt_Quantity,Synchronized = new.Synchronized
	  where `StkTransaction`.Transaction_ID = new.DeliveryDt_ID and Transaction_Type = new.Journal_Type;
	  if row_Count() = 0  then
		  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantitySold,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_CodePacked,PriceTTC,PriceTTCBase1,PriceTTCBase2, Item_ExpireDate, Item_BatchNo)
		  Values (new.Deliverydt_id,new.Delivery_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.DeliveryDt_Quantity),new.DeliveryDt_Price,new.DeliveryDt_PriceBase1,new.DeliveryDt_PriceBase2,new.DeliveryDt_QtyUnit,ifnull(new.DeliveryDt_PricePerUnit,0),new.Synchronized,new.DeliveryDt_CostBase1,new.DeliveryDt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked,new.deliverydt_priceTTC,new.deliverydt_priceTTCBase1,new.deliverydt_priceTTCBase2, new.DeliveryDt_ExpireDate, new.DeliveryDt_BatchNo);
	  end if;
  end if;

	select module_freezone into @module from module limit 1;
	if @module then
	  update itemCustom set ItemCust_QtyOut = ItemCust_QtyOut + @sign * old.Deliverydt_Quantity
  	where Purchase_invoiceNo = old.Purchase_invoiceNo and Item_code = old.Item_Code and Warehouse_code = old.Warehouse_Code;
	  if row_count() = 0 then
		  insert into itemCustom set Purchase_invoiceNo = old.Purchase_invoiceNo, Item_code = old.Item_Code,
		  Warehouse_code = old.Warehouse_Code, ItemCust_QtyOut = (-1)*@sign * old.Deliverydt_Quantity;
	  end if;
	end if;
	end if;
END $$
DELIMITER;

Drop Trigger if Exists DeliveryDt_BD;
DELIMITER $$
CREATE TRIGGER DeliveryDt_BD BEFORE DELETE ON deliverydt
FOR EACH ROW BEGIN
  Declare exit handler for 1442 begin end;
  if old.Journal_Type = 'DI' then
    set @sign = 1;
  else
    if old.Journal_Type = 'DR' then
		  set @sign = -1;
    end if;
  end if;

	if old.Journal_Type = 'DI' then
	  if old.RelatedDt_Id <> 0 then
		  Update `StkTransaction` Set QuantitySordered = QuantitySordered + (old.DeliveryDt_Quantity)
  		where Transaction_ID = old.RelatedDt_ID and Transaction_Type = 'SO';
	  end if;
	end if;

  Delete from StkTransaction where Transaction_ID = old.DeliveryDt_ID and Transaction_Type = old.Journal_Type;
  select module_freezone into @module from module limit 1;
	if @module then
	  update itemCustom set ItemCust_QtyOut = ItemCust_QtyOut - @sign * old.Deliverydt_Quantity
    where Purchase_invoiceNo = old.Purchase_invoiceNo and Item_code = old.Item_Code and Warehouse_code = old.Warehouse_Code;
	  if row_count() = 0 then
		  insert into itemCustom set Purchase_invoiceNo = old.Purchase_invoiceNo, Item_code = old.Item_Code,
      Warehouse_code = old.Warehouse_Code, ItemCust_QtyOut = (-1)*@sign * old.Deliverydt_Quantity;
	  end if;
  end if;

	if old.Journal_Type = 'DI' then
    UPDATE Deliveries SET Delivery_TotalQuantity = Delivery_TotalQuantity - old.DeliveryDt_Quantity
	  where Deliveries.Delivery_ID = old.Delivery_ID;
  end if;
END $$
Delimiter;

########################################################################################################################
# Deliveries Table

Drop Trigger if exists Deliveries_BD;
Delimiter $$
CREATE  Trigger Deliveries_BD Before Delete on Deliveries
For Each Row Begin
	Delete From DeliveryDt WHERE DeliveryDt.Delivery_ID = old.Delivery_ID;
	update purchases set purchase_delivered = false where purchase_id = old.purchase_id;
END $$
Delimiter;

######################################################################################################
# SalesDt Table

Drop Trigger if Exists SalesDt_AI;
DELIMITER $$
CREATE TRIGGER SalesDt_AI AFTER INSERT ON SalesDt
FOR EACH ROW BEGIN
  	UPDATE Sales SET Sale_TotalNoOfItem = Sale_TotalNoOfItem + 1 where Sales.Sale_id = new.Sale_id;
	if new.Journal_Type = 'SI' then
	  set @Sign = 1;
  	  UPDATE Sales SET Sale_TotalQuantity = Sale_TotalQuantity + new.SaleDt_Quantity
	  where Sales.Sale_ID = new.Sale_ID;
  else
    if new.Journal_Type = 'SR' then
		  set @Sign = -1;
    end if;
  end if;

	Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code, QuantitySold,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_CodePacked,PriceTTC,PriceTTCBase1,PriceTTCBase2, Item_ExpireDate, Item_BatchNo)
        Values (new.Saledt_id,new.Sale_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(@Sign*new.SaleDt_Quantity),new.SaleDt_Price,new.SaleDt_PriceBase1,new.SaleDt_PriceBase2,new.SaleDt_QtyUnit,ifnull(new.SaleDt_PricePerUnit,0),new.Synchronized,new.SaleDt_PriceBase1,new.SaleDt_PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked,new.saledt_priceTTC,new.saledt_priceTTCBase1,new.saledt_priceTTCBase2, new.SaleDt_ExpireDate, new.SaleDt_BatchNo);
END $$
DELIMITER;

Drop Trigger if Exists SalesDt_AU;
DELIMITER $$
CREATE TRIGGER SalesDt_AU AFTER UPDATE ON SalesDt
FOR EACH ROW BEGIN
	if new.Journal_Type = 'SI' then
    set @Sign = 1;
  	UPDATE Sales SET Sale_TotalQuantity = Sale_TotalQuantity - old.SaleDt_Quantity
	  where Sales.Sale_ID = old.Sale_ID;

	  UPDATE Sales SET Sale_TotalQuantity = Sale_TotalQuantity + new.SaleDt_Quantity
	  where Sales.Sale_ID = old.Sale_ID;
  else
    if new.Journal_Type = 'SR' then
		  set @Sign = -1;
    end if;
  end if;

	Delete from stkTransaction where Transaction_ID = old.Saledt_ID and Transaction_Type = old.Journal_Type;
	Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code, QuantitySold,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_CodePacked,PriceTTC,PriceTTCBase1,PriceTTCBase2, Item_ExpireDate, Item_BatchNo)
	Values (new.Saledt_id,new.Sale_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(@Sign*new.SaleDt_Quantity),new.SaleDt_Price,new.SaleDt_PriceBase1,new.SaleDt_PriceBase2,new.SaleDt_QtyUnit,ifnull(new.SaleDt_PricePerUnit,0),new.Synchronized,new.SaleDt_PriceBase1,new.SaleDt_PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked,new.saledt_priceTTC,new.saledt_priceTTCBase1,new.saledt_priceTTCBase2, new.SaleDt_ExpireDate, new.SaleDt_BatchNo);
END $$
DELIMITER;

Drop Trigger if Exists SalesDt_BD;
DELIMITER $$
CREATE TRIGGER SalesDt_BD BEFORE DELETE ON SalesDt
FOR EACH ROW BEGIN
	Declare exit handler for 1442 begin end;

	if old.Journal_Type = 'SI' then
    set @Sign = 1;
  else
    if old.Journal_Type = 'SR' then
      set @Sign = -1;
    end if;
  end if;
	Delete from stkTransaction where Transaction_ID = old.Saledt_ID and Transaction_Type = old.Journal_Type;

	if old.Journal_Type = 'SI' then
	  UPDATE Sales SET Sale_TotalQuantity = Sale_TotalQuantity - old.SaleDt_Quantity
	  where Sales.Sale_ID = old.Sale_ID;
	end if;
	UPDATE Sales SET Sale_TotalNoOfItem = Sale_TotalNoOfItem - 1 where Sales.Sale_id = old.Sale_id;
END $$
Delimiter;

########################################################################################################################
# Sales Table

Drop Trigger if exists Sales_BD;
Delimiter $$
CREATE  Trigger Sales_BD Before Delete on Sales
For Each Row Begin
    Delete From SalesDt WHERE SalesDt.Sale_ID = old.Sale_ID;
END $$
Delimiter;

########################################################################################################################
# ProductionDt Table MODIFIED BY FADY SEE BELOW

#Drop Trigger if Exists ProductionDt_AI;
#DELIMITER $$
#CREATE TRIGGER ProductionDt_AI AFTER INSERT ON ProductionDt
#FOR EACH ROW BEGIN
#  Select Production_Reversed into @Reversed From Production where Production_ID = new.Production_ID;
#  if @Reversed = 1 then
#	  set @Sign = -1;
#	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty)
#	  Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.ProductionDt_Quantity),new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty);
#	else
#	  set @Sign = -1;
#	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty)
#	  Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.ProductionDt_Quantity),new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty);
#  end if;
#
#	Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
#  Values (new.ProductiondT_ID,new.ProductiondT_ID,'PDt',new.Size_Measure,new.Color_Code,@Sign * new.ProductionDt_Quantity);
#END $$
#DELIMITER;#

#Drop Trigger if Exists ProductionDt_BU;

#Drop Trigger if Exists ProductionDt_AU;
#DELIMITER $$
#CREATE TRIGGER ProductionDt_AU AFTER UPDATE ON ProductionDt
#FOR EACH ROW BEGIN
#	Delete from StkTransaction where Transaction_ID = old.Productiondt_id and Transaction_Type = 'PDt';
#	Select Production_Reversed into @Reversed From Production where Production_ID = new.Production_ID;
#  if @Reversed = 1 then
#	  set @Sign = 1;
#	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty)
#	  Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.ProductionDt_Quantity,new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty);
#	else
#	  set @Sign = -1;
#	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty)
#	  Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.ProductionDt_Quantity,new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty);
#	end if;
#	Delete from StkTransactionSizes where Transactiondt_id = old.ProductionDt_ID and Transaction_Type = 'PDt';
#	Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
# Values (new.ProductiondT_ID,new.ProductiondT_ID,'PDt',new.Size_Measure,new.Color_Code,@Sign * new.ProductionDt_Quantity);
#END $$
#DELIMITER;

Drop Trigger if Exists ProductionDt_AI;
DELIMITER $$
CREATE TRIGGER ProductionDt_AI AFTER INSERT ON ProductionDt
FOR EACH ROW BEGIN
  Select Production_Reversed into @Reversed From Production where Production_ID = new.Production_ID;
# if new.item_special=0 then
 if (ifnull(new.item_special,0)=0) then
  if @Reversed = 1 then
	  set @Sign = -1;
	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo)
	  Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.ProductionDt_Quantity),new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.ProductionDt_ExpireDate, new.ProductionDt_BatchNo);
	else
	  set @Sign = -1;
	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo)
	  Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.ProductionDt_Quantity),new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.ProductionDt_ExpireDate, new.ProductionDt_BatchNo);
  end if;
  end if;

	#Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.ProductiondT_ID,new.ProductiondT_ID,'PDt',new.Size_Measure,new.Color_Code,@Sign * new.ProductionDt_Quantity);
END $$
DELIMITER;

Drop Trigger if Exists ProductionDt_BU;

Drop Trigger if Exists ProductionDt_AU;
DELIMITER $$
CREATE TRIGGER ProductionDt_AU AFTER UPDATE ON ProductionDt
FOR EACH ROW BEGIN
	Delete from StkTransaction where Transaction_ID = old.Productiondt_id and Transaction_Type = 'PDt';
	Select Production_Reversed into @Reversed From Production where Production_ID = new.Production_ID;

  #if new.item_special=0 then
 if (ifnull(new.item_special,0)=0) then
  if @Reversed = 1 then
	  set @Sign = 1;
	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo)
	  Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.ProductionDt_Quantity,new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.ProductionDt_ExpireDate, new.ProductionDt_BatchNo);
	else
	  set @Sign = -1;
	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo)
	  Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.ProductionDt_Quantity,new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.ProductionDt_ExpireDate, new.ProductionDt_BatchNo);
	end if;
   end if;

	#Delete from StkTransactionSizes where Transactiondt_id = old.ProductionDt_ID and Transaction_Type = 'PDt';
	#Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.ProductiondT_ID,new.ProductiondT_ID,'PDt',new.Size_Measure,new.Color_Code,@Sign * new.ProductionDt_Quantity);
END $$
DELIMITER;
Drop Trigger if Exists ProductionDt_BD;
DELIMITER $$
CREATE TRIGGER ProductionDt_BD BEFORE DELETE ON ProductionDt
FOR EACH ROW BEGIN
	Delete from StkTransaction where Transaction_ID = old.Productiondt_id and Transaction_Type = 'PDt';
	#Delete from StkTransactionSizes where Transactiondt_id = old.ProductionDt_ID and Transaction_Type = 'PDt';
END $$
Delimiter;

######################################################################################################
# Production Table

Drop Trigger if Exists Production_AI;
DELIMITER $$
CREATE TRIGGER Production_AI AFTER INSERT ON Production
FOR EACH ROW BEGIN
  Select Item_Unit Into @ItemUnit From Items Where Item_Code = new.Item_Code;
  IF @ItemUnit=0 OR @ItemUnit IS NULL  then
	set @ItemUnit=1;
  end if;
  #Ayman 05.12.2009
  Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1;
  if @ModuleDoubleUnit then
  	if new.Production_Reversed = 1 then
	    set @Sign =-1;
	    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo)
  	  Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Item_UnitCoef,new.Item_Unit2Qty, new.Production_ExpireDate, new.Production_BatchNo);
	  else
	    set @Sign = 1;
  	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo)
	    Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Item_UnitCoef,new.Item_Unit2Qty, new.Production_ExpireDate, new.Production_BatchNo);
  	end if;
  else
	  if new.Production_Reversed = 1 then
	    set @Sign =-1;
  	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2, Item_ExpireDate, Item_BatchNo)
	    Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity), new.Production_ExpireDate, new.Production_BatchNo);
  	else
	    set @Sign = 1;
  	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2, Item_ExpireDate, Item_BatchNo)
	    Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity), new.Production_ExpireDate, new.Production_BatchNo);
  	end if;
  end if;
	#Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.Production_ID,new.Production_ID,'PD',new.Size_Measure,new.Color_Code,@Sign * new.Production_Quantity);
END $$
DELIMITER;

Drop Trigger if Exists Production_BU;

Drop Trigger if Exists Production_AU;
DELIMITER $$
CREATE TRIGGER Production_AU AFTER UPDATE ON Production
FOR EACH ROW BEGIN
  Delete from StkTransaction where Transaction_ID = old.Production_id and Transaction_Type = 'PD';
  Select Item_Unit Into @ItemUnit From Items Where Item_Code = new.Item_Code;
  IF @ItemUnit=0 OR @ItemUnit IS NULL  then
	set @ItemUnit=1;
  end if;
  #Ayman 05.12.2009
  Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1;
  if @ModuleDoubleUnit then
  	if new.Production_Reversed = 1 then
	    set @Sign =-1;
	    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo)
  	  Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Item_UnitCoef,new.Item_Unit2Qty, new.Production_ExpireDate, new.Production_BatchNo);
	  else
	    set @Sign = 1;
  	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo)
	    Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Item_UnitCoef,new.Item_Unit2Qty, new.Production_ExpireDate, new.Production_BatchNo);
  	end if;
  else
	  if new.Production_Reversed = 1 then
	    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2, Item_ExpireDate, Item_BatchNo)
	    Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity), new.Production_ExpireDate, new.Production_BatchNo);
	  else
	    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2, Item_ExpireDate, Item_BatchNo)
	    Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity), new.Production_ExpireDate, new.Production_BatchNo);
	  end if;
  end if;
	#Delete from StkTransactionSizes where Transactiondt_id = old.Production_ID and Transaction_Type = 'PD';
	#Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.Production_ID,new.Production_ID,'PD',new.Size_Measure,new.Color_Code,@Sign * new.Production_Quantity);
END $$
DELIMITER;

Drop Trigger if Exists Production_BD;
DELIMITER $$
CREATE TRIGGER Production_BD BEFORE DELETE ON Production
FOR EACH ROW BEGIN
	Delete from StkTransaction where Transaction_ID = old.Production_id and Transaction_Type = 'PD';
	#Delete from StkTransactionSizes where Transactiondt_id = old.Production_ID and Transaction_Type = 'PD';
	Delete From ProductionDt where ProductionDt.Production_ID = old.Production_ID;
END $$
Delimiter;

##########################################################################################################
Drop Trigger if exists TransferDT_AI;
Delimiter $$
CREATE  Trigger TransferDT_AI After Insert on transferdt
For Each Row Begin

	Select Module_TransferConfirmation into @Module_TransferConfirmation From Module Limit 1;
	Select IfNull(Transfer_Confirmed,0) into @Transfer_Confirmed From Transfer Where Transfer_Id = new.Transfer_id Limit 1;

if(@Transfer_Confirmed =1 and @Module_TransferConfirmation =1) then
  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,QuantityDelivered,
  QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo)
  Values (new.Transferdt_id,new.Transfer_Date,'TR',new.Item_Code,new.TransferDt_warehouseFrom,new.Project_Code1,new.Costcent_Code,new.TransferDt_Quantity,
  new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo);


	Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,QuantityEntered,
  QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo)
  Values (new.Transferdt_id,new.Transfer_Date,'TRT',new.Item_Code,new.TransferDt_warehouseTo,new.Project_Code2,new.Costcent_Code,new.TransferDt_Quantity,
  new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo);


	#Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.TransferDt_ID,new.TransferDt_ID,'TR',new.Size_Measure,new.Color_Code,(-1) * new.TransferDt_Quantity),
  	#	   (new.TransferDt_ID,new.TransferDt_ID,'TRT',new.Size_Measure,new.Color_Code,new.TransferDt_Quantity);
#nadine #08-11-2016
ELSEIF (@Module_TransferConfirmation = 0) then
  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,QuantityDelivered,
  QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo)
  Values (new.Transferdt_id,new.Transfer_Date,'TR',new.Item_Code,new.TransferDt_warehouseFrom,new.Project_Code1,new.Costcent_Code,new.TransferDt_Quantity,
  new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo);


	Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,QuantityEntered,
  QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo)
  Values (new.Transferdt_id,new.Transfer_Date,'TRT',new.Item_Code,new.TransferDt_warehouseTo,new.Project_Code2,new.Costcent_Code,new.TransferDt_Quantity,
  new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo);


	#Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.TransferDt_ID,new.TransferDt_ID,'TR',new.Size_Measure,new.Color_Code,(-1) * new.TransferDt_Quantity),
  	#	   (new.TransferDt_ID,new.TransferDt_ID,'TRT',new.Size_Measure,new.Color_Code,new.TransferDt_Quantity);
End If;


END $$
Delimiter;
Drop Trigger if exists TransferDT_BU;

Drop Trigger if exists TransferDT_AU;
Delimiter $$
CREATE  Trigger TransferDT_AU After Update on transferdt
For Each Row Begin

	Select Module_TransferConfirmation into @Module_TransferConfirmation From Module Limit 1;
	Select IfNull(Transfer_Confirmed,0) into @Transfer_Confirmed From Transfer Where Transfer_Id = new.Transfer_id Limit 1;

if(@Transfer_Confirmed =1 and @Module_TransferConfirmation =1) then

  Delete From StkTransaction where Transaction_id = old.TransferDt_id and (Transaction_Type = 'TR' or Transaction_Type = 'TRT');
  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityDelivered,
  QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo)
	values(new.Transferdt_id,new.Transfer_Date,'TR',new.Item_Code,new.TransferDt_warehouseFrom,new.Project_Code1,new.Costcent_Code,new.TransferDt_Quantity,
  new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo);

	Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityEntered,
  QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo)
	Values (new.Transferdt_id,new.Transfer_Date,'TRT',new.Item_Code,new.TransferDt_warehouseTo,new.Project_Code2,new.Costcent_Code,new.TransferDt_Quantity,
  new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo);

	#Delete from StkTransactionSizes where Transactiondt_id = old.TransferDt_ID and Transaction_Type in('TR','TRT');
	#Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.TransferDt_ID,new.TransferDt_ID,'TR',new.Size_Measure,new.Color_Code,(-1) * new.TransferDt_Quantity),
	#  	   (new.TransferDt_ID,new.TransferDt_ID,'TRT',new.Size_Measure,new.Color_Code,new.TransferDt_Quantity);
#nadine #08-11-2016
ELSEIF (@Module_TransferConfirmation = 0) then
  Delete From StkTransaction where Transaction_id = old.TransferDt_id and (Transaction_Type = 'TR' or Transaction_Type = 'TRT');
  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityDelivered,
  QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo)
	values(new.Transferdt_id,new.Transfer_Date,'TR',new.Item_Code,new.TransferDt_warehouseFrom,new.Project_Code1,new.Costcent_Code,new.TransferDt_Quantity,
  new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo);

	Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityEntered,
  QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo)
	Values (new.Transferdt_id,new.Transfer_Date,'TRT',new.Item_Code,new.TransferDt_warehouseTo,new.Project_Code2,new.Costcent_Code,new.TransferDt_Quantity,
  new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo);

	#Delete from StkTransactionSizes where Transactiondt_id = old.TransferDt_ID and Transaction_Type in('TR','TRT');
	#Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.TransferDt_ID,new.TransferDt_ID,'TR',new.Size_Measure,new.Color_Code,(-1) * new.TransferDt_Quantity),
	#  	   (new.TransferDt_ID,new.TransferDt_ID,'TRT',new.Size_Measure,new.Color_Code,new.TransferDt_Quantity);
End If;
		   
END $$
Delimiter;

Drop Trigger if exists TransferDT_BD;
Delimiter $$
CREATE  Trigger TransferDT_BD Before Delete on transferdt
For Each Row Begin
  Delete from StkTransaction where Transaction_id = old.TransferDt_id and (Transaction_Type = 'TR' or Transaction_Type = 'TRT');
	#Delete from StkTransactionSizes where Transactiondt_id = old.TransferDt_ID and Transaction_Type in('TR','TRT');
END $$
Delimiter;

########################################################################################################################
# Transfer Table

Drop Trigger if exists Transfer_BD;
Delimiter $$
CREATE  Trigger Transfer_BD Before Delete on Transfer
For Each Row Begin
    Delete From TransferDt WHERE TransferDt.Transfer_ID = old.Transfer_ID;
END $$
Delimiter ;

########################################################################################################################
# SorderDT Table

Drop Trigger if exists SorderDt_AI;
Delimiter $$
CREATE  Trigger SorderDt_AI After Insert on SorderDt
For Each Row Begin
    Declare vCompany VARCHAR(64);
    Declare vInvoiceModule,vInvoiceOnSorder, vSalesTTC TINYINT(1);
    Declare vForm_id VARCHAR(50);
	  Declare vYear Integer;
    Declare vBase1, vBase2, vVatPercentage varchar(6);
    Declare vJournal_Type VARCHAR(2);
    Declare vSalesman_Code VARCHAR(10);
    Declare vSorder_reference, vTotal, vTotalB1, vTotalB2, vDecimalBase double;


	Update Sorder set Sorder_TotalQuantity = Sorder_TotalQuantity + new.SorderDt_Quantity
  where Sorder.Sorder_ID = new.Sorder_ID;

  Select Sorder_Type into @SorderType from Sorder Where Sorder.Sorder_id = new.Sorder_id;
  if @SorderType = 'SO' then
	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantitySOrdered,Price,PriceBase1,PriceBase2,Synchronized,UnitCoef,Unit2Qty,Item_CodePacked)
	  Values (new.Sorderdt_id,new.Sorder_Date,@SorderType,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.SorderDt_Quantity,new.SorderDt_Price,new.SorderDt_PriceBase1,new.SroderDt_PriceBase2,new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked);
  end if;

  #headerlink detaillink
    select Module_InvoiceGrid into vInvoiceModule from module;
    select InvoiceOnSorder into vInvoiceOnSorder from transsetup limit 1;
    select base1, base2, ifnull(Setup_VatPercentage,0), decimalbase into vBase1, vBase2, vVatPercentage, vDecimalBase  from setup;
    select SalesTTC into vSalesTTC From SalesSetup;
    select journal_type, Salesman_Code, Sorder_reference into vJournal_Type, vSalesman_Code, vSorder_reference from sorder where sorder_id = NEW.sorder_id;

if vInvoiceModule = 2 and vInvoiceOnSorder = 1 then
    select SUBSTRING_INDEX(DATABASE(), '_', 1) into vCompany from setup;
  	select uExtractNumberFromString(DATABASE()) into vYear;
    set vForm_id = concat(vJournal_Type,NEW.Sorderdt_id,vYear);



if(vJournal_Type = 'SO') then

    #get Amounts
    select if(vSalesTTC = 1,
    Round(((SorderDt_Quantity*SorderDt_PriceTTC)-((SorderDt_Quantity*SorderDt_PriceTTC*ifNull(SorderDt_Discount,0))/100)), vDecimalBase),
    Round(((SorderDt_Quantity*SorderDt_Price)-((SorderDt_Quantity*SorderDt_Price*ifNull(SorderDt_Discount,0))/100)), vDecimalBase)) As Total into vTotal
    from sorderdt where sorderdt_id = NEW.sorderdt_id;

    if vTotal = '' or vTotal is null then
      set vTotal = 0;
    end if;

    set vTotalB1 = convertcurFunc(NEW.Currency_Code,vBase1, NEW.Sorder_Date,0, 0, '','', vTotal);
    set vTotalB2 = convertcurFunc(NEW.Currency_Code,vBase2, NEW.Sorder_Date,0, 0, '','', vTotal);

    insert into accesslabels.headerlink (headerlink_date,year,company,journal_type,form_id,ledger_number,project_code, costcent_code,
    saleman_number, description,value_date, reference,currency_code, manual_no, amount, amountbase1, amountbase2 )
    values
   (NEW.Sorder_Date,vYear,vCompany,vJournal_Type,vForm_id,NEW.Client_Code,NEW.Project_code,NEW.Costcent_code,
    vSalesman_Code, NEW.item_code, NEW.Sorder_date, vSorder_reference, NEW.Currency_Code, NULL, vTotal, vTotalB1, vTotalB2);

    end if; #end if journal type

  END IF; #end first if
END $$
Delimiter;

Drop Trigger if exists SorderDt_AU;
Delimiter $$
CREATE  Trigger SorderDt_AU After Update on SorderDt
For Each Row Begin
  Declare vCompany VARCHAR(64);
  Declare vHeaderlink_id, vdetaillink_id INTEGER;
  Declare vInvoiceModule,vInvoiceOnSorder, vSalesTTC TINYINT(1);
  Declare vCheckid TINYINT(1);
  Declare vForm_id VARCHAR(50);
  Declare detaillinkid double;
  Declare vYear Integer;
  Declare vBase1, vBase2, vVatPercentage varchar(6);
  Declare vJournal_Type VARCHAR(2);
  Declare vSalesman_Code VARCHAR(10);
  Declare vSorder_reference, vTotal, vTotalB1, vTotalB2, vDecimalBase double;

  Update Sorder set Sorder_TotalQuantity = Sorder_TotalQuantity - old.SorderDt_Quantity
  where Sorder.Sorder_ID = old.Sorder_ID;

  Update Sorder set Sorder_TotalQuantity = Sorder_TotalQuantity + new.SorderDt_Quantity
  where Sorder.Sorder_ID = new.Sorder_ID;

  Select Sorder_Type into @SorderType from Sorder Where Sorder.Sorder_id = old.Sorder_id;
  if @SorderType = 'SO' then
	  Delete from StkTransaction where Transaction_Type = @SorderType and Transaction_ID = old.SorderDt_ID;
	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantitySOrdered,Price,PriceBase1,PriceBase2,Synchronized,UnitCoef,Unit2Qty,Item_CodePacked)
	  Values (new.Sorderdt_id,new.Sorder_Date,@SorderType,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.SorderDt_Quantity,new.SorderDt_Price,new.SorderDt_PriceBase1,new.SroderDt_PriceBase2,new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked);
	end if;



  #headerlink detaillink

 select Module_InvoiceGrid into vInvoiceModule from module;
 select uExtractNumberFromString(DATABASE()) into vYear;
 select InvoiceOnSorder into vInvoiceOnSorder from transsetup limit 1;
 select base1, base2, ifnull(Setup_VatPercentage,0), decimalbase into vBase1, vBase2, vVatPercentage, vDecimalBase  from setup;
 select SalesTTC into vSalesTTC From SalesSetup;

  if vInvoiceModule = 2 and vInvoiceOnSorder = 1 then
    select SUBSTRING_INDEX(DATABASE(), '_', 1) into vCompany from setup;
    select journal_type, Salesman_Code, Sorder_reference into vJournal_Type, vSalesman_Code, vSorder_reference from sorder where sorder_id = NEW.sorder_id;

    set vForm_id = concat(vJournal_Type,NEW.Sorderdt_id,vYear);

    select headerlink_id into vHeaderlink_id from accesslabels.headerlink
    where form_id=vForm_id
    and journal_type=vJournal_Type and year=vYear and company=vCompany;

    select  1 into vCheckid from accesslabels.detaillink
    where form_id=vForm_id
    and related_journalType=vJournal_Type and year=vYear
    and company=vCompany;

    if(vCheckid='' or vCheckid is null) then set vCheckid:=0; end if;

  IF (vHeaderlink_id != '' or vHeaderlink_id is not null) and (vCheckid=0) then

    if(vJournal_Type = 'SO') then

    #get Amounts
    select if(vSalesTTC = 1,
    Round(((SorderDt_Quantity*SorderDt_PriceTTC)-((SorderDt_Quantity*SorderDt_PriceTTC*ifNull(SorderDt_Discount,0))/100)), vDecimalBase),
    Round(((SorderDt_Quantity*SorderDt_Price)-((SorderDt_Quantity*SorderDt_Price*ifNull(SorderDt_Discount,0))/100)), vDecimalBase)) As Total into vTotal
    from sorderdt where sorderdt_id = NEW.sorderdt_id;

    if vTotal = '' or vTotal is null then
      set vTotal = 0;
    end if;

    set vTotalB1 = convertcurFunc(NEW.Currency_Code,vBase1, NEW.Sorder_Date,0, 0, '','', vTotal);
    set vTotalB2 = convertcurFunc(NEW.Currency_Code,vBase2, NEW.Sorder_Date,0, 0, '','', vTotal);


    update accesslabels.headerlink set
    headerlink_date = NEW.Sorder_date,
    ledger_number = NEW.Client_code,
    project_code = NEW.Project_code,
    costcent_code = NEW.costcent_code,
    saleman_number = vSalesman_Code,
    amount =  vTotal,

    amountbase1 = vTotalB1,

    amountbase2 = vTotalB2,

    currency_code = NEW.Currency_Code,
    reference = vSorder_reference,
	  description = NEW.item_code,
	  value_date = NEW.Sorder_date
    where headerlink_id=vHeaderlink_id;

    end if; #end if journal type
  end if; #end if headerlink
 END IF; #end first if
END $$
Delimiter;


Drop Trigger if exists SorderDt_BD;
Delimiter $$
CREATE  Trigger SorderDt_BD Before Delete on SorderDt
For Each Row Begin
  Declare vCompany VARCHAR(64);
  Declare vHeaderlink_id INTEGER;
  Declare vInvoiceModule,vInvoiceOnSorder TINYINT(1);
  Declare vCheckid TINYINT(1);
  Declare vForm_id VARCHAR(50);
  Declare vYear Integer;
  Declare detaillinkid DOUBLE;
  Declare vJournal_Type VARCHAR(2);

	DECLARE EXIT HANDLER FOR 1442 BEGIN END;
	Select Sorder_Type into @SorderType from Sorder Where Sorder.Sorder_id = old.Sorder_id;
  if @SorderType = 'SO' then
	  Delete from StkTransaction where Transaction_Type = @SorderType and Transaction_ID = old.SorderDt_ID;
  end if;

	Update Sorder set Sorder_TotalQuantity = Sorder_TotalQuantity - old.SorderDt_Quantity
  where Sorder.Sorder_ID = old.Sorder_ID;

  #headerlink detaillink

    select Module_InvoiceGrid into vInvoiceModule from module;
	select uExtractNumberFromString(DATABASE()) into vYear;
    select InvoiceOnSorder into vInvoiceOnSorder from transsetup limit 1;

  if vInvoiceModule = 2 and vInvoiceOnSorder = 1 then
    select SUBSTRING_INDEX(DATABASE(), '_', 1) into vCompany from setup;

    select journal_type into vJournal_Type from sorder where sorder_id = OLD.sorder_id;
    set vForm_id = concat(vJournal_Type,OLD.Sorderdt_id,vYear);

    select headerlink_id into vHeaderlink_id from accesslabels.headerlink
    where form_id=vForm_id and journal_type=vJournal_Type and year=vYear
    and company=vCompany;

    select  1 into vCheckid from accesslabels.detaillink
    where form_id=vForm_id and related_journalType=vJournal_Type and year=vYear
    and company=vCompany;

    if(vCheckid='' or vCheckid is null) then set vCheckid:=0; end if;

  IF (vHeaderlink_id != '' or vHeaderlink_id is not null) and (vCheckid=0) then

    DELETE FROM accesslabels.headerlink where headerlink_id=vHeaderlink_id;

  END IF; #end if headerlink


 END IF; #end first if

END $$
Delimiter;

########################################################################################################################
# Sorder Table

Drop Trigger if exists Sorder_BD;
Delimiter $$
CREATE  Trigger Sorder_BD Before Delete on Sorder
For Each Row Begin
    Delete From SorderDt WHERE SorderDt.Sorder_ID = old.Sorder_ID;
END $$
Delimiter;

####################################################################################################################
#RMAdt Table

DROP TRIGGER IF EXISTS RMADt_AI;
Delimiter $$
CREATE  TRIGGER RMADt_AI AFTER INSERT ON RMADt
FOR EACH ROW BEGIN
	IF NEW.RMA_Type='MI' then
	  UPDATE RMA SET RMA_TotalQuantity = RMA_TotalQuantity + NEW.RMADt_Quantity WHERE RMA.RMA_ID = NEW.RMA_ID;
	ELSE
      IF NEW.RMA_Type='MR' then
		SELECT RMA_ID INTO @RID FROM RMADt WHERE RMADt_ID = NEW.MI_ID;
		UPDATE RMA SET RMA_TotalQuantityUsed = RMA_TotalQuantityUsed + NEW.RMADt_Quantity WHERE RMA.RMA_ID = @RID;
      END IF;
    END IF;
END $$
Delimiter;

DROP TRIGGER IF EXISTS RMADt_AU;
Delimiter $$
CREATE  TRIGGER RMADt_AU AFTER UPDATE ON RMADt
FOR EACH ROW BEGIN
	IF OLD.RMA_Type='MI' then
	  UPDATE RMA SET RMA_TotalQuantity = RMA_TotalQuantity - OLD.RMADt_Quantity WHERE RMA.RMA_ID = OLD.RMA_ID;
	ELSE
      IF OLD.RMA_Type='MR' then
		Select RMA_id into @RID from RMADt where RMADt_ID = old.MI_ID;
		UPDATE RMA SET RMA_TotalQuantityUsed = RMA_TotalQuantityUsed - OLD.RMADt_Quantity WHERE RMA.RMA_ID = @RID;
	  END IF;
	END IF;

	IF NEW.RMA_Type='MI' then
	  UPDATE RMA SET RMA_TotalQuantity = RMA_TotalQuantity + NEW.RMADt_Quantity WHERE RMA.RMA_ID = NEW.RMA_ID;
	ELSE
      IF NEW.RMA_Type='MR' then
		Select RMA_ID into @RID from RMADt where RMADt_ID = new.MI_ID;
		UPDATE RMA SET RMA_TotalQuantityUsed = RMA_TotalQuantityUsed + NEW.RMADt_Quantity WHERE RMA.RMA_ID = @RID;
	  END IF;
	END IF;
END $$
Delimiter;

DROP TRIGGER IF EXISTS RMADt_BD;
Delimiter $$
CREATE  TRIGGER RMADt_BD BEFORE DELETE ON RMADt
FOR EACH ROW BEGIN
	DECLARE EXIT HANDLER FOR 1442 BEGIN END;
	IF OLD.RMA_Type='MI' then
	  UPDATE RMA SET RMA_TotalQuantity = RMA_TotalQuantity - OLD.RMADt_Quantity WHERE RMA.RMA_ID = OLD.RMA_ID;
	ELSE
      IF OLD.RMA_Type='MR' then
		Select RMA_id into @RID from RMADt where RMADt_ID = old.MI_ID;
		UPDATE RMA SET RMA_TotalQuantityUsed = RMA_TotalQuantityUsed - OLD.RMADt_Quantity WHERE RMA.RMA_ID = @RID;
	  END IF;
	END IF;
END $$
Delimiter;

####################################################################################################################
#RMA Table

DROP TRIGGER IF EXISTS RMA_BU;

DROP TRIGGER IF EXISTS RMA_BD;
Delimiter $$
CREATE  TRIGGER RMA_BD  BEFORE DELETE ON RMA
FOR EACH ROW BEGIN
	DELETE FROM RMADt where RMA_ID = old.RMA_ID;
END $$
Delimiter;

##########################################################################################################################
#DeliveriedQty Delivery

DROP TRIGGER IF EXISTS DeliveriedQty_AI;
Delimiter $$
CREATE  TRIGGER DeliveriedQty_AI After Insert on DeliveriedQty
For EACH Row Begin
	Update Deliverydt set DeliveryDt_DeliveriedQty = DeliveryDt_DeliveriedQty + new.DeliveriedQty_Qty where DeliveryDt_id = new.DeliveryDt_id;
END $$
Delimiter;

DROP TRIGGER IF EXISTS DeliveriedQty_BU;

DROP TRIGGER IF EXISTS DeliveriedQty_AU;
Delimiter $$
CREATE  TRIGGER DeliveriedQty_AU After UPDATE on DeliveriedQty
For EACH Row Begin
	Update Deliverydt set DeliveryDt_DeliveriedQty = DeliveryDt_DeliveriedQty - old.DeliveriedQty_Qty where DeliveryDt_id = old.DeliveryDt_id;
	Update Deliverydt set DeliveryDt_DeliveriedQty = DeliveryDt_DeliveriedQty + new.DeliveriedQty_Qty where DeliveryDt_id = new.DeliveryDt_id;
END $$
Delimiter;

DROP TRIGGER IF EXISTS DeliveriedQty_BD;
Delimiter $$
CREATE  TRIGGER DeliveriedQty_BD Before Delete on DeliveriedQty
For EACH Row Begin
	Update Deliverydt set DeliveryDt_DeliveriedQty = DeliveryDt_DeliveriedQty - old.DeliveriedQty_Qty where DeliveryDt_id = old.DeliveryDt_id;
END $$
Delimiter;

####################################################################################################################
#DeliveriedQtyH Table
DROP TRIGGER IF EXISTS DeliveriedQtyH_BD;
Delimiter $$
CREATE TRIGGER DeliveriedQtyH_BD Before Delete on DeliveriedQtyH
For EACH Row Begin
	Delete from DeliveriedQty where DeliveriedQtyH_id = old.DeliveriedQtyH_id;
END $$
Delimiter;

#############################################
#Items Table
Drop trigger if exists Items_BI;
Delimiter $$
create trigger Items_BI before Insert on Items
For each row begin
	#if new.Item_Unit < 1 then
  if new.Item_Unit < 0 then
    set new.Item_Unit = 1;
	end if;
END $$
Delimiter;

Drop trigger if exists Items_BU;
DELIMITER $$
create trigger Items_BU before update on Items
For each row begin
	#if new.Item_Unit < 1 then
  if new.Item_Unit < 0 then
    set new.Item_Unit = 1;
	end if;
END $$
Delimiter;

###################################################
# PrequestDT Table

Drop Trigger if exists PrequestDt_AI;
Delimiter $$
CREATE  Trigger PrequestDt_AI After Insert on PrequestDt
For Each Row Begin
  Update Prequest set Prqst_TotalQuantity = Prqst_TotalQuantity + new.PrqstDt_Quantity
  where Prequest.Prqst_ID = new.Prqst_ID;
/*
	Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code,Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,Quantity_PQR,Synchronized,UnitCoef,Unit2Qty)
  Values (new.Prqstdt_id,new.Prqst_Date,'PQ',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.PrqstDt_Quantity,new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty);
*/  
END $$
Delimiter;

Drop Trigger if exists PrequestDt_AU;
Delimiter $$
CREATE  Trigger PrequestDt_AU After Update on PrequestDt
For Each Row Begin
	Update Prequest set Prqst_TotalQuantity = Prqst_TotalQuantity - old.PrqstDt_Quantity
  where Prequest.Prqst_ID = old.Prqst_ID;

	Update Prequest set Prqst_TotalQuantity = Prqst_TotalQuantity + new.PrqstDt_Quantity
  where Prequest.Prqst_ID = new.Prqst_ID;

/*	Delete from stkTransaction where Transaction_ID = old.Prqstdt_Id and Transaction_Type = 'PQ';
  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code,Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,Quantity_PQR,Synchronized,UnitCoef,Unit2Qty)
	Values (new.Prqstdt_id,new.Prqst_Date,'PQ',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.PrqstDt_Quantity,new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty);
*/	
END $$
Delimiter;

Drop Trigger if exists PrequestDt_BD;
Delimiter $$
CREATE  Trigger PrequestDt_BD Before Delete on PrequestDt
For Each Row Begin
	Declare exit handler for 1442 begin end;
	Delete from stkTransaction where Transaction_ID = old.Prqstdt_Id and Transaction_Type = 'PQ';

	Update Prequest set Prqst_TotalQuantity = Prqst_TotalQuantity - old.PrqstDt_Quantity
  where Prequest.Prqst_ID = old.Prqst_ID;
END $$
Delimiter;

########################################################################################################################
# Prequest Table

Drop Trigger if exists Prequest_BD;
Delimiter $$
CREATE  Trigger Prequest_BD Before Delete on Prequest
For Each Row Begin
  Delete From PrequestDt WHERE PrequestDt.Prqst_ID = old.Prqst_ID;
END $$
Delimiter;

####################################################################################################################
#STKEVAL trigger

Drop Trigger if exists StkEvalDt_AI;
Delimiter $$
CREATE  Trigger StkEvalDt_AI After Insert on StkEvalDt
For Each Row Begin
  select base1, base2 into @base1,@base2 from setup;
  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code,Project_Code,Costcent_Code,Currency_Code,Price,PriceBase1,PriceBase2)
  Values (new.StkEvaldt_id,new.StkEval_Date,'SE',new.Item_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.StkEvaldt_Value,
  ConvertCurFunc(new.currency_code,@base1, new.STkEval_date, 0, 0, '', '', new.STkEvaldt_value),
  ConvertCurFunc(new.currency_code,@base2, new.STkEval_date, 0, 0, '', '', new.STkEvaldt_value));
END $$
Delimiter;

Drop Trigger if exists StkEvalDt_AU;
Delimiter $$
CREATE  Trigger StkEvalDt_AU After Update on StkEvalDt
For Each Row Begin
  select base1, base2 into @base1,@base2 from setup;
	Delete from stkTransaction where Transaction_ID = old.StkEvaldt_Id and Transaction_Type = 'SE';
  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code,Project_Code,Costcent_Code,Currency_Code,Price,PriceBase1,PriceBase2)
	Values (new.StkEvaldt_id,new.StkEval_Date,'SE',new.Item_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.StkEvaldt_Value,
  ConvertCurFunc(new.currency_code,@base1, new.STkEval_date, 0, 0, '', '', new.STkEvaldt_value),
  ConvertCurFunc(new.currency_code,@base2, new.STkEval_date, 0, 0, '', '', new.STkEvaldt_value));
END $$
Delimiter;

Drop Trigger if exists StkEvalDt_BD;
Delimiter $$
CREATE  Trigger StkEvalDt_BD Before Delete on StkEvalDt
For Each Row Begin
	Delete from stkTransaction where Transaction_ID = old.StkEvaldt_Id and Transaction_Type = 'SE';
END $$
Delimiter;

####################################################################################################################
# STkEval table

Drop Trigger if exists StkEval_BD;
Delimiter $$
CREATE  Trigger StkEval_BD Before Delete on StkEval
For Each Row Begin
  Delete From StkEvalDt WHERE StkEvalDt.StkEval_ID = old.StkEval_ID;
END $$
Delimiter;

####################################################################################################################
#PurchaseDTSizes Table

Drop Trigger if exists PurchasesDTSizes_AI;
Delimiter $$
CREATE  Trigger PurchasesDTSizes_AI After Insert on PurchasesDTSizes
For Each Row Begin
	if new.Journal_Type = 'PI' then
    Set @Sign = 1;
	else
    if new.Journal_Type = 'PR' then
      Set @Sign = -1;
    end if;
  end if;

 # Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
 # Values (new.PurchasesDtSizes_Incremen,new.Purchasedt_id,new.Journal_Type,new.Size_Measure,new.Color_Code,@Sign * new.PurchasesDtSizes_Quantity);
END $$
Delimiter;

Drop Trigger if exists PurchasesDTSizes_AU;
Delimiter $$
CREATE  Trigger PurchasesDTSizes_AU After Update on PurchasesDTSizes
For Each Row Begin
  if new.Journal_Type = 'PI' then
    Set @Sign = 1;
	else
    if new.Journal_Type = 'PR' then
      Set @Sign = -1;
    end if;
  end if;

  #Delete from StkTransactionSizes where Transactiondt_id = old.PurchasesDtSizes_Incremen and Transaction_Type = old.Journal_Type;
#	Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
 # Values (new.PurchasesDtSizes_Incremen,new.Purchasedt_id,new.Journal_Type,new.Size_Measure,new.Color_Code,@Sign * new.PurchasesDtSizes_Quantity);
END $$
Delimiter;

Drop Trigger if exists PurchasesDTSizes_BD;
Delimiter $$
CREATE  Trigger PurchasesDTSizes_BD Before Delete on PurchasesdtSizes
For Each Row Begin
  Delete from StkTransactionSizes where Transactiondt_id = old.PurchasesDtSizes_Incremen and Transaction_Type = old.Journal_Type;
END $$
Delimiter;

########################################################################################################################
#DeliveryDTSizes Table

Drop Trigger if Exists DeliveryDtSizes_AI;
DELIMITER $$
CREATE TRIGGER DeliveryDtSizes_AI AFTER INSERT ON deliverydtSizes
FOR EACH ROW BEGIN
  if new.Journal_Type = 'DI' then
	  set @Sign = -1;
	else
	  if new.Journal_Type = 'DR' then
		  set @Sign = 1;
    end if;
  end if;

	#Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.DeliveryDtSizes_Increment,new.DeliveryDt_ID,new.Journal_Type,new.Size_Measure,new.Color_Code,@Sign * new.DeliveryDtSizes_Quantity);
END $$
DELIMITER;

Drop Trigger if Exists DeliveryDtSizes_AU;
DELIMITER $$
CREATE TRIGGER DeliveryDtSizes_AU AFTER UPDATE ON deliverydtSizes
FOR EACH ROW BEGIN
  if new.Journal_Type = 'DI' then
	  set @Sign = -1;
  else
	  if new.Journal_Type = 'DR' then
	  	set @Sign = 1;
    end if;
  end if;

	#Delete from StkTransactionSizes where Transactiondt_id = old.DeliveryDtSizes_Increment and Transaction_Type = old.Journal_Type;
	#Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.deliveryDtSizes_Increment,new.Deliverydt_id,new.Journal_Type,new.Size_Measure,new.Color_Code,@Sign * new.DeliveryDtSizes_Quantity);
END $$
DELIMITER;

Drop Trigger if Exists DeliveryDtSizes_BD;
DELIMITER $$
CREATE TRIGGER DeliveryDtSizes_BD BEFORE DELETE ON deliverydtSizes
FOR EACH ROW BEGIN
	Delete from StkTransactionSizes where Transactiondt_id = old.DeliveryDtSizes_Increment and Transaction_Type = old.Journal_Type;
END $$
Delimiter;

########################################################################################################################
# Journal Table
/*Drop Trigger if Exists Journal_BI;
DELIMITER $$
CREATE TRIGGER Journal_BI Before INSERT ON Journal
FOR EACH ROW BEGIN

  insert into temptablefolio (journaldate,folio,event,ttime)
  values (new.journal_id,new.Journal_Folio,'BI',now());
  Set @Value =(new.Journal_Remark<>'');
  Set New.Journal_Folio = Concat(LPad(Month(new.Journal_Date),2,'0'),LPad(NextFolio(Month(new.Journal_Date),new.Journal_ID),4,'0'));

  insert into temptablefolio (journaldate,folio,event,ttime)
  values (new.Journal_id,new.Journal_Folio,'ABI',now());
END $$
DELIMITER ;

Drop Trigger if Exists Journal_BU;
DELIMITER $$
CREATE TRIGGER Journal_BU BEFORE UPDATE ON Journal
FOR EACH ROW BEGIN
  insert into temptablefolio (journaldate,folio,event,ttime)
  values (new.Journal_id,new.Journal_Folio,'BU',now());

  if (Month(new.Journal_Date) <> Month(old.Journal_Date)) then
    Set New.Journal_Folio = Concat(LPad(Month(new.Journal_Date),2,'0'),LPad(NextFolio(Month(new.Journal_Date),new.Journal_ID),4,'0'));
  end if;

  insert into temptablefolio (journaldate,folio,event,ttime)
  values (new.Journal_id,new.Journal_Folio,'ABU',now());

END $$*/
########################################################################################################################


#maroun 2010-07-12   ----------------------------------------------start--------------------------------------------------------------
######################################################################################################
# Production Master Table

Drop Trigger if Exists ProductionMaster_AI;
DELIMITER $$
CREATE TRIGGER ProductionMaster_AI AFTER INSERT ON ProductionMaster
FOR EACH ROW BEGIN
  Select Item_Unit Into @ItemUnit From Items Where Item_Code = new.Item_Code;
  IF @ItemUnit=0 OR @ItemUnit IS NULL  then
	set @ItemUnit=1;
  end if;
  Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1;
  if @ModuleDoubleUnit then
      set @Sign = 1;
  	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty)
	  Values (new.ProductionMaster_id,new.ProductionMaster_date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.ProductionMaster_Quantity,(new.ProductionMaster_Amount),(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2),new.Synchronized,(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2),new.Item_UnitCoef,new.Item_Unit2Qty);
  else
	  set @Sign = 1;
  	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2)
	    Values (new.ProductionMaster_id,new.ProductionMaster_date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(new.ProductionMaster_Quantity*@ItemUnit),(new.ProductionMaster_Amount),(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2),new.Synchronized,(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2));

  end if;
	
END $$
DELIMITER;

Drop Trigger if Exists ProductionMaster_BU;

Drop Trigger if Exists ProductionMaster_AU;
DELIMITER $$
CREATE TRIGGER ProductionMaster_AU AFTER UPDATE ON ProductionMaster
FOR EACH ROW BEGIN
  Delete from StkTransaction where Transaction_ID = old.ProductionMaster_id and Transaction_Type = 'PD';
  Select Item_Unit Into @ItemUnit From Items Where Item_Code = new.Item_Code;
  IF @ItemUnit=0 OR @ItemUnit IS NULL  then
	set @ItemUnit=1;
  end if;

  Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1;
  if @ModuleDoubleUnit then
  	    set @Sign = 1;
  	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty)
	    Values (new.ProductionMaster_id,new.ProductionMaster_date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.ProductionMaster_Quantity,(new.ProductionMaster_Amount),(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2),new.Synchronized,(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2),new.Item_UnitCoef,new.Item_Unit2Qty);
    else
	    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2)
	    Values (new.ProductionMaster_id,new.ProductionMaster_date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(new.ProductionMaster_Quantity*@ItemUnit),(new.ProductionMaster_Amount),(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2),new.Synchronized,(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2));
  end if;
END $$
DELIMITER;

Drop Trigger if Exists ProductionMaster_BD;
DELIMITER $$
CREATE TRIGGER ProductionMaster_BD BEFORE DELETE ON ProductionMaster
FOR EACH ROW BEGIN
	Delete from StkTransaction where Transaction_ID = old.ProductionMaster_id and Transaction_Type = 'PD';
	
	
END $$
Delimiter;

##########################################################################################################


######################################################################################################
# Production Table

Drop Trigger if Exists Production_AI;
DELIMITER $$
CREATE TRIGGER Production_AI AFTER INSERT ON Production
FOR EACH ROW BEGIN
  Select Item_Unit Into @ItemUnit From Items Where Item_Code = new.Item_Code;
  IF @ItemUnit=0 OR @ItemUnit IS NULL  then
	set @ItemUnit=1;
  end if;
  #maroun 08.07.2010
  Select module_productionmm into @moduleproductionmm From Module Limit 1;
 if not @moduleproductionmm then
  #Ayman 05.12.2009
  Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1;

  if @ModuleDoubleUnit then
  	if new.Production_Reversed = 1 then
	    set @Sign =-1;
	    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty)
  	  Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Item_UnitCoef,new.Item_Unit2Qty);
	  else
	    set @Sign = 1;
		if not @moduleproductionmm then
		  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty)
	    Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Item_UnitCoef,new.Item_Unit2Qty);
  	    end if;
	end if;
  else
	  if new.Production_Reversed = 1 then
	    set @Sign =-1;
  	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2)
	    Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)));
  	else
	    set @Sign = 1;

  	  Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2)
	    Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)));

	end if;
  end if;
	#Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.Production_ID,new.Production_ID,'PD',new.Size_Measure,new.Color_Code,@Sign * new.Production_Quantity);
end if;
END $$
DELIMITER;

Drop Trigger if Exists Production_BU;

Drop Trigger if Exists Production_AU;
DELIMITER $$
CREATE TRIGGER Production_AU AFTER UPDATE ON Production
FOR EACH ROW BEGIN
  Delete from StkTransaction where Transaction_ID = old.Production_id and Transaction_Type = 'PD';
  Select Item_Unit Into @ItemUnit From Items Where Item_Code = new.Item_Code;
  IF @ItemUnit=0 OR @ItemUnit IS NULL  then
	set @ItemUnit=1;
  end if;
#maroun 08.07.2010
  Select module_productionmm into @moduleproductionmm From Module Limit 1;
if not @moduleproductionmm then
  #Ayman 05.12.2009
  Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1;
  if @ModuleDoubleUnit then
  	if new.Production_Reversed = 1 then
	    set @Sign =-1;
	    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty)
  	  Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Item_UnitCoef,new.Item_Unit2Qty);
	  else
	    set @Sign = 1;
	 if not @moduleproductionmm then
		Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty)
	    Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Item_UnitCoef,new.Item_Unit2Qty);
	 end if;
	end if;
  else
	  if new.Production_Reversed = 1 then
	    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2)
	    Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)));
	  else

		Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2)
	    Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)));

	  end if;
  end if;
	#Delete from StkTransactionSizes where Transactiondt_id = old.Production_ID and Transaction_Type = 'PD';
	#Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.Production_ID,new.Production_ID,'PD',new.Size_Measure,new.Color_Code,@Sign * new.Production_Quantity);
end if;
END $$
DELIMITER;

Drop Trigger if Exists Production_BD;
DELIMITER $$
CREATE TRIGGER Production_BD BEFORE DELETE ON Production
FOR EACH ROW BEGIN
  Select module_productionmm into @moduleproductionmm From Module Limit 1;
  if not @moduleproductionmm then	
	Delete from StkTransaction where Transaction_ID = old.Production_id and Transaction_Type = 'PD';
	#Delete from StkTransactionSizes where Transactiondt_id = old.Production_ID and Transaction_Type = 'PD';
  end if;
  Delete From ProductionDt where ProductionDt.Production_ID = old.Production_ID;
  Delete From ProductionMaster where ProductionMaster.Production_ID = old.Production_ID;
END $$
Delimiter;

##########################################################################################################
#maroun 2010-07-12   ----------------------------------------------End--------------------------------------------------------------


#nabil 2010-08-23
Drop Trigger if Exists BudgetInvAmendment_AI;
DELIMITER $$
CREATE TRIGGER BudgetInvAmendment_AI After Insert on BudgetInvAmendment
For Each Row Begin
    update `budgetinvdt` set BUDGETINVDT_DEACTIVATE=1,
			BUDGETINVDT_DEACTIVATEDATE=new.BudgetInvAmendment_activatedate,
			BUDGETINVAMENDMENT_ID=0
	where ITEM_CODE=new.item_code and BUDGETINVDT_DEACTIVATE=0;
    Insert into `budgetinvdt` (BUDGETINV_ID,ITEM_CODE,BUDGETINVDT_QUANTITY,BUDGETINVDT_AMOUNT,BUDGETINVDT_AMOUNTBASE1,
			BUDGETINVDT_AMOUNTBASE2,BUDGETINVDT_REMARK,JOURNAL_TYPE,BUDGETINVDT_DEACTIVATE,
			BUDGETINVDT_DEACTIVATEDATE,BUDGETINVDT_SYSDATE,BUDGETINVDT_ACTIVATEDATE,BUDGETINVSTATUS_ID,
			BUDGETINVAMENDMENT_ID)
    Values
      (new.BUDGETINV_ID,new.item_code,new.BudgetInvAmendment_quantity,new.BudgetInvAmendment_amount,
      new.BudgetInvAmendment_amountBase1,new.BudgetInvAmendment_amountBase2,
      new.BudgetInvAmendment_remark,new.JOURNAL_TYPE,(SELECT BUDGETINVSTATUS_DEACTIVATE FROM BUDGETINVSTATUS WHERE BUDGETINVSTATUS_ID=new.BudgetInvStatus_id),
      new.budgetinvamendment_deactivatedate,NOW(),new.BudgetInvAmendment_activatedate,new.BudgetInvStatus_id,
      new.BUDGETINVAMENDMENT_ID);
END $$
Delimiter;

Drop Trigger if Exists BudgetInvAmendment_AU;
DELIMITER $$
CREATE TRIGGER BudgetInvAmendment_AU After UPDATE on BudgetInvAmendment
For Each Row Begin
    UPDATE `budgetinvdt` SET ITEM_CODE=new.item_code,
			BUDGETINVDT_QUANTITY=new.BudgetInvAmendment_quantity,
			BUDGETINVDT_AMOUNT=new.BudgetInvAmendment_amount,
			BUDGETINVDT_AMOUNTBASE1=new.BudgetInvAmendment_amountBase1,
			BUDGETINVDT_AMOUNTBASE2=new.BudgetInvAmendment_amountBase2,
			BUDGETINVDT_REMARK=new.BudgetInvAmendment_remark,
			JOURNAL_TYPE=new.JOURNAL_TYPE,
			BUDGETINVDT_DEACTIVATE=(SELECT BUDGETINVSTATUS_DEACTIVATE FROM BUDGETINVSTATUS WHERE BUDGETINVSTATUS_ID=new.BudgetInvStatus_id),
			BUDGETINVDT_DEACTIVATEDATE=new.budgetinvamendment_deactivatedate,
			BUDGETINVDT_SYSDATE=NOW(),
			BUDGETINVDT_ACTIVATEDATE=new.BudgetInvAmendment_activatedate,
			BUDGETINVSTATUS_ID=new.BudgetInvStatus_id
			WHERE BUDGETINVAMENDMENT_ID=new.BUDGETINVAMENDMENT_ID;
END $$
Delimiter;


Drop Trigger if Exists BudgetInvAmendment_BD;
DELIMITER $$
CREATE TRIGGER BudgetInvAmendment_BD Before DELETE on BudgetInvAmendment
For Each Row Begin
    delete from `budgetinvdt` WHERE BUDGETINVAMENDMENT_ID=OLD.BUDGETINVAMENDMENT_ID;
END $$
Delimiter;


##nabil 2011-02-18

#Drop Trigger if Exists Chkjour_AI;
#DELIMITER $$
#CREATE TRIGGER Chkjour_AI After Insert on Chkjour
#For Each Row Begin
#    if new.chk_type='C' then
#	      update checks set journaldt_id2=new.journaldt_id
#        where ledger_number=new.ledger_number
#          and chk_number=new.chk_number;
#    else
#	    insert into checks (chk_number,ledger_number,chk_bank,ledger_activity,
#                          currency_code,Chk_ValueDate,
#                          Chk_Amount,Chk_Amount1,Chk_Amount2,journaldt_id1)
#	    values
#			                  (new.chk_number,new.ledger_number,new.chk_bank,
#                          (select ledger_activity from ledger where ledger_number=new.ledger_number),
#                        new.currency_code,new.chk_valuedate,
#			                  new.Chk_Amount,new.Chk_Amount1,new.Chk_Amount2,new.journaldt_id);
#    end if;
#END $$
#Delimiter;


#Drop Trigger if Exists Chkjour_BD;
#DELIMITER $$
#CREATE TRIGGER Chkjour_BD Before DELETE on Chkjour
#For Each Row Begin
#    if old.chk_type='C' then
#        update checks set journaldt_id2=0
#		where ledger_number=old.ledger_number
#			and chk_number=old.chk_number;
#   else
#      	delete from checks
#		WHERE chk_number=old.chk_number
#			and ledger_number=old.ledger_number
#			and journaldt_id1=old.journaldt_id
#			and (journaldt_id2=0 or  journaldt_id2 is null);
#    end if;
#END $$
#Delimiter;



#Drop Trigger if Exists billjour_AI;
#DELIMITER $$
#CREATE TRIGGER billjour_AI After Insert on billjour
#For Each Row Begin
#    if new.bill_type='C' then
#	      update bills set journaldt_id2=new.journaldt_id
#        where ledger_number=new.ledger_number
#          and bill_number=new.bill_number;
#    else
#	    insert into bills (bill_number,ledger_number,bill_bank,ledger_activity,
#                          currency_code,bill_ValueDate,
#                          bill_Amount,bill_Amount1,bill_Amount2,journaldt_id1)
#	    values
#			                  (new.bill_number,new.ledger_number,new.bill_bank,
#                          (select ledger_activity from ledger where ledger_number=new.ledger_number),
#                        new.currency_code,new.bill_valuedate,
#			                  new.bill_Amount,new.bill_Amount1,new.bill_Amount2,new.journaldt_id);
#    end if;
#END $$
#Delimiter;


#Drop Trigger if Exists billjour_BD;
#DELIMITER $$
#CREATE TRIGGER billjour_BD Before DELETE on billjour
#For Each Row Begin
#    if old.bill_type='C' then
#        update bills set journaldt_id2=0
#		where ledger_number=old.ledger_number
#			and bill_number=old.bill_number;
#    else
#      	delete from bills
#		WHERE bill_number=old.bill_number
#			and ledger_number=old.ledger_number
#			and journaldt_id1=old.journaldt_id
#			and (journaldt_id2=0 or  journaldt_id2 is null);
#    end if;
#END $$
#Delimiter;




##TOOK FROM TAREK 11/11/2011
Drop Trigger if exists GRNPurchaseDt_BD;
Delimiter $$
CREATE  Trigger GRNPurchaseDt_BD Before Delete on GRNPurchasesdt
For Each Row Begin
  Declare exit handler for 1442 begin end;

  Delete from StkTransaction where Transaction_id = old.Purchasedt_id and Transaction_Type ='GR';
  if old.RelatedDt_Id <> 0  then
    Update `StkTransaction` Set QuantityPordered = QuantityPordered + old.PurchaseDt_Quantity
    where Transaction_ID = old.RelatedDt_ID and Transaction_Type = 'PO';
  end if;

	  UPDATE GRNPurchases SET Purchase_TotalQuantity = Purchase_TotalQuantity - old.PurchaseDt_Quantity
	  where GRNPurchases.Purchase_ID = old.Purchase_ID;

	UPDATE GRNPurchases SET Purchase_TotalNoOfItem = Purchase_TotalNoOfItem - 1 where GRNPurchases.Purchase_ID = old.Purchase_ID;
END $$
Delimiter;

Drop Trigger if exists GRNPurchaseDT_AI;
Delimiter $$
CREATE  Trigger GRNPurchaseDT_AI After Insert on GRNPurchasesDT
For Each Row Begin
	UPDATE GRNPurchases SET Purchase_TotalNoOfItem = Purchase_TotalNoOfItem + 1 where GRNPurchases.Purchase_ID = new.Purchase_ID;

    if new.Journal_Type = 'PI' then
    Set @Sign = 1;
    else
    if new.Journal_Type = 'PR' then
      Set @Sign = -1;
    end if;
    end if;


	  UPDATE GRNPurchases SET Purchase_TotalQuantity = Purchase_TotalQuantity + new.PurchaseDt_Quantity;
    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,
    Costcent_Code,QuantityEntered,Price,PriceBase1,PriceBase2,CostBase1,CostBase2, UnitCoef, Unit2Qty, Item_ExpireDate, Item_BatchNo)
    Values (new.Purchasedt_id,new.Purchase_Date,'GR',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,
    @Sign*new.PurchaseDt_Quantity,0,0,0,0,0, new.Item_UnitCoef, @Sign*new.Item_Unit2Qty, new.PurchaseDt_ExpireDate, new.PurchaseDt_BatchNo);

  if new.RelatedDt_Id <> 0  then
	  Update `StkTransaction` Set QuantityPordered = QuantityPordered - new.PurchaseDt_Quantity
    where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'PO';
  end if;


END $$
Delimiter;

Drop Trigger if exists GRNPurchaseDT_AU;
Delimiter $$
CREATE  Trigger GRNPurchaseDT_AU After Update on GRNPurchasesDT
For Each Row Begin


	  UPDATE GRNPurchases SET Purchase_TotalQuantity = Purchase_TotalQuantity - old.PurchaseDt_Quantity
  	where GRNPurchases.Purchase_ID = old.Purchase_ID;

    UPDATE GRNPurchases SET Purchase_TotalQuantity = Purchase_TotalQuantity + new.PurchaseDt_Quantity
	  where GRNPurchases.Purchase_ID = new.Purchase_ID;




if new.Journal_Type = 'PI' then
    Set @Sign = 1;
    else
    if new.Journal_Type = 'PR' then
      Set @Sign = -1;
    end if;
    end if;

  delete from StkTransaction where Transaction_id = old.Purchasedt_id and Transaction_Type = 'GR';



	  UPDATE GRNPurchases SET Purchase_TotalQuantity = Purchase_TotalQuantity + new.PurchaseDt_Quantity;
    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,
    Costcent_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2, UnitCoef, Unit2Qty, Item_ExpireDate, Item_BatchNo)
    Values (new.Purchasedt_id,new.Purchase_Date,'GR',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,
    @Sign*new.PurchaseDt_Quantity,0,0,0,0,0,0, new.Item_UnitCoef, new.Item_Unit2Qty, new.PurchaseDt_ExpireDate, new.PurchaseDt_BatchNo);

  if new.RelatedDt_Id <> 0  then
  	Update `StkTransaction` Set QuantityPordered = QuantityPordered + old.PurchaseDt_Quantity
    where Transaction_ID = old.RelatedDt_ID and Transaction_Type = 'PO';

    Update `StkTransaction` Set QuantityPordered = QuantityPordered - new.PurchaseDt_Quantity
    where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'PO';
  end if;

END $$
Delimiter;



Drop Trigger if exists ProdDtItem_BI;
Delimiter $$
CREATE  Trigger ProdDtItem_BI Before Insert on ProdDtItem
for each row begin
	set new.serialno_code=concat(
 ((
   (dayofyear(new.proddtitem_date)-1) +
   (dayofweek(concat(year(new.proddtitem_date),'-01-01')))
   -1
 ) div 7) +1,
  substr(year(new.proddtitem_date),3),
  substr('0000',1,4-length(new.ProdDtItem_Reference1)),
     new.ProdDtItem_Reference1,
     substr('0000',1,4-length(new.ProdDtItem_Reference2)) ,
     new.ProdDtItem_Reference2);

END $$
Delimiter;


Drop Trigger if exists ProdDtItem_BU;
Delimiter $$
CREATE  Trigger ProdDtItem_BU Before Update on ProdDtItem
for each row begin
	set new.serialno_code=concat(
 ((
   (dayofyear(new.proddtitem_date)-1) +
   (dayofweek(concat(year(new.proddtitem_date),'-01-01')))
   -1
 ) div 7) +1,
  substr(year(new.proddtitem_date),3),
  substr('0000',1,4-length(new.ProdDtItem_Reference1)),
     new.ProdDtItem_Reference1,
     substr('0000',1,4-length(new.ProdDtItem_Reference2)) ,
     new.ProdDtItem_Reference2);

END $$
Delimiter;




######################################################################################################
# ProductionRM Table

Drop Trigger if Exists ProductionRM_AI;
DELIMITER $$
CREATE TRIGGER ProductionRM_AI AFTER INSERT ON ProductionRM
FOR EACH ROW BEGIN
  /*Select Divisions_Code Into @ProjectCode
  From ProductionHDDivisions
  Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id
  Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id
  Where ProductionHD_ID = new.ProductionHD_ID
  And Upper(DivisionTypes_Name) = 'PROJECT';

  Select Divisions_Code Into @CostcenterCode
  From ProductionHDDivisions
  Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id
  Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id
  Where ProductionHD_ID = new.ProductionHD_ID
  And Upper(DivisionTypes_Name) = 'COSTCENTER';

  Select Divisions_Code Into @MachineCode
  From ProductionHDDivisions
  Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id
  Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id
  Where ProductionHD_ID = new.ProductionHD_ID
  And Upper(DivisionTypes_Name) = 'MACHINE';*/

  Set @JourType := 'PDT';
  if (new.RelatedDt_Id Is Not Null and new.RelatedDt_Id != "") Then Set @JourType := 'PDO'; End If;

  Select ProductionHD_Reversed into @Reversed From ProductionHD where ProductionHD_ID = new.ProductionHD_ID;
  if @Reversed = 1 then
     set @Sign = 1;
     Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo)
     Values (new.ProductionRM_Id,new.ProductionHD_Date,@JourType,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,(new.ProductionRM_Quantity),new.ProductionRM_Cost,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,0,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionRM_ExpireDate,new.ProductionRM_BatchNo);
  else
     set @Sign = -1;
     Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo)
     Values (new.ProductionRM_Id,new.ProductionHD_Date,@JourType,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,(new.ProductionRM_Quantity),new.ProductionRM_Cost,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,0,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionRM_ExpireDate,new.ProductionRM_BatchNo);
  end if;

  #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.ProductionRM_ID,new.ProductionRM_ID,@JourType,new.Size_Measure,new.Color_Code,@Sign * new.ProductionRM_Quantity);
END $$
DELIMITER;


Drop Trigger if Exists ProductionRM_AU;
DELIMITER $$
CREATE TRIGGER ProductionRM_AU AFTER UPDATE ON ProductionRM
FOR EACH ROW BEGIN
  Set @JourType := 'PDT';
  if (new.RelatedDt_Id Is Not Null and new.RelatedDt_Id != "") Then Set @JourType := 'PDO'; End If;

  Delete from StkTransaction where Transaction_ID = old.ProductionRM_Id and Transaction_Type = @JourType;
  Select ProductionHD_Reversed into @Reversed From ProductionHD where ProductionHD_ID = new.ProductionHD_ID;

  /*Select Divisions_Code Into @ProjectCode
  From ProductionHDDivisions
  Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id
  Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id
  Where ProductionHD_ID = new.ProductionHD_ID
  And Upper(DivisionTypes_Name) = 'PROJECT';

  Select Divisions_Code Into @CostcenterCode
  From ProductionHDDivisions
  Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id
  Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id
  Where ProductionHD_ID = new.ProductionHD_ID
  And Upper(DivisionTypes_Name) = 'COSTCENTER';

  Select Divisions_Code Into @MachineCode
  From ProductionHDDivisions
  Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id
  Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id
  Where ProductionHD_ID = new.ProductionHD_ID
  And Upper(DivisionTypes_Name) = 'MACHINE';*/

  if @Reversed = 1 then
     set @Sign = 1;
     Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo)
     Values (new.ProductionRM_id,new.ProductionHD_Date,@JourType,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,new.ProductionRM_Quantity,new.ProductionRM_Cost,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,0,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionRM_ExpireDate,new.ProductionRM_BatchNo);
  else
     set @Sign = -1;
     Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo)
     Values (new.ProductionRM_id,new.ProductionHD_Date,@JourType,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,new.ProductionRM_Quantity,new.ProductionRM_Cost,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,0,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionRM_ExpireDate,new.ProductionRM_BatchNo);
  end if;

  #Delete from StkTransactionSizes where Transactiondt_id = old.ProductionRM_ID and Transaction_Type = @JourType;
  #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.ProductionRM_ID,new.ProductionRM_ID,@JourType,new.Size_Measure,new.Color_Code,@Sign * new.ProductionRM_Quantity);
END $$
DELIMITER;

Drop Trigger if Exists ProductionRM_BD;
DELIMITER $$
CREATE TRIGGER ProductionRM_BD BEFORE DELETE ON ProductionRM
FOR EACH ROW BEGIN
    Set @JourType := 'PDT';
    if (old.RelatedDt_Id Is Not Null and old.RelatedDt_Id != "") Then Set @JourType := 'PDO'; End If;

    Delete from StkTransaction where Transaction_ID = old.ProductionRM_Id and Transaction_Type = @JourType;
   # Delete from StkTransactionSizes where Transactiondt_id = old.ProductionRM_ID and Transaction_Type = @JourType;
END $$
Delimiter;

######################################################################################################
# ProductionFG Table

Drop Trigger if Exists ProductionFG_AI;
DELIMITER $$
CREATE TRIGGER ProductionFG_AI AFTER INSERT ON ProductionFG
FOR EACH ROW BEGIN
  /*Select Divisions_Code Into @ProjectCode
  From ProductionHDDivisions
  Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id
  Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id
  Where ProductionHD_ID = new.ProductionHD_ID
  And Upper(DivisionTypes_Name) = 'PROJECT';

  Select Divisions_Code Into @CostcenterCode
  From ProductionHDDivisions
  Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id
  Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id
  Where ProductionHD_ID = new.ProductionHD_ID
  And Upper(DivisionTypes_Name) = 'COSTCENTER';

  Select Divisions_Code Into @MachineCode
  From ProductionHDDivisions
  Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id
  Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id
  Where ProductionHD_ID = new.ProductionHD_ID
  And Upper(DivisionTypes_Name) = 'MACHINE';*/

  Select ProductionHD_Reversed into @Reversed From ProductionHD where ProductionHD_ID = new.ProductionHD_ID;
  if @Reversed = 1 then
     set @Sign = 1;
     Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo)
     Values (new.ProductionFG_Id,new.ProductionHD_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,(new.ProductionFG_Quantity),new.ProductionFG_Cost,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,0,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionFG_ExpireDate,new.ProductionFG_BatchNo);
  else
     set @Sign = -1;
     Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo)
     Values (new.ProductionFG_Id,new.ProductionHD_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,(new.ProductionFG_Quantity),new.ProductionFG_Cost,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,0,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionFG_ExpireDate,new.ProductionFG_BatchNo);
  end if;

  #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.ProductionFG_ID,new.ProductionFG_ID,'PD',new.Size_Measure,new.Color_Code,@Sign * new.ProductionFG_Quantity);
END $$
DELIMITER;


Drop Trigger if Exists ProductionFG_AU;
DELIMITER $$
CREATE TRIGGER ProductionFG_AU AFTER UPDATE ON ProductionFG
FOR EACH ROW BEGIN
  Delete from StkTransaction where Transaction_ID = old.ProductionFG_Id and Transaction_Type = 'PD';
  Select ProductionHD_Reversed into @Reversed From ProductionHD where ProductionHD_ID = new.ProductionHD_ID;

  /*Select Divisions_Code Into @ProjectCode
  From ProductionHDDivisions
  Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id
  Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id
  Where ProductionHD_ID = new.ProductionHD_ID
  And Upper(DivisionTypes_Name) = 'PROJECT';

  Select Divisions_Code Into @CostcenterCode
  From ProductionHDDivisions
  Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id
  Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id
  Where ProductionHD_ID = new.ProductionHD_ID
  And Upper(DivisionTypes_Name) = 'COSTCENTER';

  Select Divisions_Code Into @MachineCode
  From ProductionHDDivisions
  Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id
  Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id
  Where ProductionHD_ID = new.ProductionHD_ID
  And Upper(DivisionTypes_Name) = 'MACHINE';*/

  if @Reversed = 1 then
     set @Sign = 1;
     Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo)
     Values (new.ProductionFG_id,new.ProductionHD_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,new.ProductionFG_Quantity,new.ProductionFG_Cost,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,0,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionFG_ExpireDate,new.ProductionFG_BatchNo);
  else
     set @Sign = -1;
     Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo)
     Values (new.ProductionFG_id,new.ProductionHD_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,new.ProductionFG_Quantity,new.ProductionFG_Cost,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,0,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionFG_ExpireDate,new.ProductionFG_BatchNo);
  end if;

  #Delete from StkTransactionSizes where Transactiondt_id = old.ProductionFG_ID and Transaction_Type = 'PD';
  #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity)
  #Values (new.ProductionFG_ID,new.ProductionFG_ID,'PD',new.Size_Measure,new.Color_Code,@Sign * new.ProductionFG_Quantity);
END $$
DELIMITER;

Drop Trigger if Exists ProductionFG_BD;
DELIMITER $$
CREATE TRIGGER ProductionFG_BD BEFORE DELETE ON ProductionFG
FOR EACH ROW BEGIN
	Delete from StkTransaction where Transaction_ID = old.ProductionFG_Id and Transaction_Type = 'PD';
	#Delete from StkTransactionSizes where Transactiondt_id = old.ProductionFG_ID and Transaction_Type = 'PD';
END $$
Delimiter;

##########################################################################################################



Drop Trigger if Exists Ledger_BI;
DELIMITER $$
CREATE TRIGGER Ledger_BI before insert on ledger
	For Each Row Begin
	#if new.ledger_ID is null then
		set new.ledger_id = (Select ifnull(max(ledger_id),1)+1 AS MAXID from ledger);
  #end if;
END $$
Delimiter;


#****************************************************************************************************************

/*Drop Trigger if Exists Currency_BI;
DELIMITER $$
CREATE TRIGGER Currency_BI before insert on Currency
	For Each Row Begin
	#if new.Currency_ID is null then
		set new.Currency_id = (Select max(Currency_id)+1 AS MAXID from Currency);
    #end if;
END $$
Delimiter;
*/

Drop Trigger if Exists clients_BI;
DELIMITER $$
CREATE TRIGGER clients_BI before insert on clients
	For Each Row Begin
	#if new.client_ID is  null then
		set new.client_id = (Select ifnull(max(client_id),0)+1 AS MAXID from clients);
   #end if;
END $$
Delimiter;
/*
Drop Trigger if Exists costcent_BI;
DELIMITER $$
CREATE TRIGGER costcent_BI before insert on costcent
	For Each Row Begin
	#if new.costcent_ID is null then
		set new.costcent_id = (Select max(ifnull(costcent_id,0))+1 AS MAXID from costcent);
    #end if;
END $$
Delimiter;
*/
#****************************************************************************************************************
#Gemayel trigger
#Drop Trigger if Exists bankledger_AI;
#Delimiter $$
#create trigger bankledger_AI after insert on ledger
#for each row begin
#	if substr(new.ledger_number,1,3) = '511' then
#  insert into bankledger( Bank_ID, Ledger_Number,BankType_id, BankLedgerType_id, bankledger_outstandingcheck)
#                        values(7,new.ledger_number,6,2,0);
#  end if;
#END $$
delimiter;
#****************************************************************************************************************

#Gilbert 25-11-2013
/*Drop Trigger if Exists project_BI;
DELIMITER $$
CREATE TRIGGER project_BI before insert on project
	For Each Row Begin
	#if new.project_ID is null then
		set new.project_id = (Select max(ifnull(project_id,0))+1 AS MAXID from project);
    #end if;
END $$
Delimiter;*/
#****************************************************************************************************************
#Nabil 27-01-2014
Drop Trigger if Exists Items_BI;
/*DELIMITER $$
CREATE TRIGGER Items_BI before insert on items
	For Each Row Begin
    if new.Item_Unit < 0 then
      set new.Item_Unit = 1;
    end if;
	set new.item_id = (Select max(ifnull(item_id,0))+1 AS MAXID from items);

END $$
Delimiter;
*/
#######################################################################################################################
#nadine #04-10-2016

Drop Trigger if exists PurchaseDT_AI;
Delimiter $$
CREATE  Trigger PurchaseDT_AI After Insert on PurchasesDT
For Each Row Begin
	UPDATE Purchases SET Purchase_TotalNoOfItem = Purchase_TotalNoOfItem + 1 where Purchases.Purchase_ID = new.Purchase_ID;

	if new.Journal_Type = 'PI' then
    Set @Sign = 1;
	  UPDATE Purchases SET Purchase_TotalQuantity = Purchase_TotalQuantity + new.PurchaseDt_Quantity
	  where Purchases.Purchase_ID = new.Purchase_ID;
  else
    if new.Journal_Type = 'PR' then
      Set @Sign = -1;
    end if;
  end if;

  Set @ItemUnit = 0;
  Select Item_Code, Item_Unit,Item_LastDatePurchase
  into @ItemCode, @ItemUnit ,@LastPurchaseDate
  From Items WHERE Item_Code = new.Item_Code;
  IF @ItemUnit=0 OR @ItemUnit IS NULL  then
	set @ItemUnit=1;
  end if;

  #Ayman 03.12.2009
  Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1;
  if @ModuleDoubleUnit then
  	Set @Price = new.PurchaseDt_Price + @Sign*new.PurchaseDT_Charges/new.PurchaseDt_Quantity
      - new.PurchaseDt_Price*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountAmount/new.PurchaseDt_Quantity;
  	Set @PriceBase1 = new.PurchaseDt_PriceBase1 + @Sign*new.PurchaseDT_ChargesBase1/new.PurchaseDt_Quantity
      - new.PurchaseDt_PriceBase1*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase1/new.PurchaseDt_Quantity;
	  Set @PriceBase2 = new.PurchaseDt_PriceBase2 + @Sign*new.PurchaseDT_ChargesBase2/new.PurchaseDt_Quantity
      - new.PurchaseDt_PriceBase2*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase2/new.PurchaseDt_Quantity;

    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo)
    Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,@Sign*new.PurchaseDt_Quantity,@Price,ifnull(@PriceBase1,0),ifnull(@PriceBase2,0),new.Synchronized,ifnull(@PriceBase1,0),ifnull(@PriceBase2,0),new.Item_UnitCoef,new.Item_Unit2Qty, new.PurchaseDt_ExpireDate, new.PurchaseDt_BatchNo );
  else
  	Set @Price=((new.PurchaseDt_Price/@ItemUnit)+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_Charges/(@ItemUnit*new.PurchaseDt_Quantity))-
		((((new.PurchaseDt_Price/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountAmount/(@ItemUnit*new.PurchaseDt_Quantity)));

  	Set @PriceBase1=((new.PurchaseDt_PriceBase1/@ItemUnit)+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase1/(@ItemUnit*new.PurchaseDt_Quantity))-
		((((new.PurchaseDt_PriceBase1/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase1/(@ItemUnit*new.PurchaseDt_Quantity)));

	 Set @PriceBase2=((new.PurchaseDt_PriceBase2/@ItemUnit)+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase2/(@ItemUnit*new.PurchaseDt_Quantity))-
		((((new.PurchaseDt_PriceBase2/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase2/(@ItemUnit*new.PurchaseDt_Quantity)));

  	if @Price is null then set @Price = 0;end if;
	if @PriceBase1 is null then set @PriceBase1 = 0;end if;
  	if @PriceBase2 is null then set @PriceBase2 = 0;end if;

    Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2, Item_ExpireDate, Item_BatchNo)
    #Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.PurchaseDt_Quantity*@ItemUnit),@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2);
    Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.PurchaseDt_Quantity*(Select Item_Unit From Items WHERE Item_Code = new.Item_Code))
    ,@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2, new.PurchaseDt_ExpireDate, new.PurchaseDt_BatchNo);
  end if;

  if new.RelatedDt_Id <> 0 and new.journal_type = 'PI' then
	  Update `StkTransaction` Set QuantityPordered = QuantityPordered - new.PurchaseDt_Quantity
    where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'PO';
  end if;

#  select Purchase_invoiceno into @PInvoice from Purchases where Purchase_Id = new.Purchase_id;
#	select module_freezone into @module from module limit 1;
#	if @module then
#	  update itemCustom set ItemCust_QtyIn = ItemCust_QtyIn + @sign * new.PurchaseDt_Quantity
#  	where Purchase_invoiceNo = @PInvoice and Item_code = new.Item_Code and Warehouse_code = new.Warehouse_Code;
#	  if row_count() = 0 then
#		  insert into itemCustom
#		  set Purchase_invoiceNo = @PInvoice, Item_code = new.Item_Code, Warehouse_code = new.Warehouse_Code,
#	    ItemCust_QtyIn = @sign * new.PurchaseDt_Quantity;
#	  end if;
#  end if;
#  if ((@LastPurchaseDate <=new.Purchase_Date) or (@LastPurchaseDate is null)) AND (new.journal_type = 'PI') AND (@price>0) then
#	  update items set Item_LastDatePurchase= cast(new.Purchase_Date as date),
#		   item_lastcostprice=if(currency_code=new.currency_code,@price,
#		   convertcurFunc(new.currency_code,currency_code,new.Purchase_Date,0,0,'','',@price)),
#		   item_lastcostpriceBase1=@PriceBase1,item_lastcostpriceBase2=@PriceBase2
#  			where item_code=new.item_code;
#  end if;
    set @VarDate:=null;
    select ItemTrans_LastDatePurchase into @VarDate from ItemsTrans where item_code=new.Item_Code;
    if @VarDate is null then
          insert into ItemsTrans (Item_code,ItemTrans_LastDatePurchase,
             itemTrans_lastcostprice,itemTrans_lastcostpriceBase1,itemTrans_lastcostpricebase2,ItemTrans_LastPricePurchase) values
             (new.Item_Code,new.Purchase_date,@Price,@PriceBase1,@PriceBase2,new.PurchaseDt_Price);
    else
        if new.Purchase_date>=@VarDate  then
           update ItemsTrans
            set ItemTrans_LastDatePurchase=new.Purchase_date,
             itemTrans_lastcostprice=@Price,
             itemTrans_lastcostpriceBase1=@PriceBase1,
             itemTrans_lastcostpriceBase2=@PriceBase2,
             ItemTrans_LastPricePurchase=new.PurchaseDt_Price
           where item_code=new.Item_Code;
        end if;
    end if;

#nadine #04-10-2016
select items.Category_code,Currency_Code into @item_category, @item_cur
from items
left join category on category.Category_code = items.Category_code
where item_code = new.Item_Code;

select ifnull(module_sellingbyclient,0),ifnull(sellingprice_basedonCost,0)  into @module_sellingbyclient, @sellingprice_basedonCost from module;

if @sellingprice_basedonCost =1 and  @item_category is not null and @item_category != '' then
  set @vtotprice      := (((ifnull(NEW.PurchaseDt_Price,0)*NEW.PurchaseDt_Quantity)*(1-(NEW.PurchaseDt_DiscountPer/100)) - ifnull(NEW.PurchaseDt_DiscountAmount,0) ) + ifnull(NEW.PurchaseDt_Charges,0))/NEW.PurchaseDt_Quantity;

  set @vtotpriceb1    := (((ifnull(NEW.PurchaseDt_PriceBase1,0)*NEW.PurchaseDt_Quantity)*(1-(NEW.PurchaseDt_DiscountPer/100)) - ifnull(NEW.PurchaseDt_DiscountBase1,0) ) + ifnull(NEW.PurchaseDt_ChargesBase1,0))/NEW.PurchaseDt_Quantity;
  set @vtotpriceb2    := (((ifnull(NEW.PurchaseDt_PriceBase2,0)*NEW.PurchaseDt_Quantity)*(1-(NEW.PurchaseDt_DiscountPer/100)) - ifnull(NEW.PurchaseDt_DiscountBase2,0) ) + ifnull(NEW.PurchaseDt_ChargesBase2,0))/NEW.PurchaseDt_Quantity;
  set @vtotpricecur   := convertcurFunc(@item_cur, NEW.Currency_Code, NEW.Purchase_Date, 0, 0, '', '', @vtotprice);


update items,category set
      Item_SellingPrice1 = if(ifnull(Category_Price1,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price1,0)/100)),
      Item_SellingPrice1Base1 = if(ifnull(Category_Price1,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price1,0)/100)),
      Item_SellingPrice1Base2 = if(ifnull(Category_Price1,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price1,0)/100)),

      Item_SellingPrice2 = if(ifnull(Category_Price2,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price2,0)/100)),
      Item_SellingPrice2Base1 = if(ifnull(Category_Price2,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price2,0)/100)),
      Item_SellingPrice2Base2 = if(ifnull(Category_Price2,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price2,0)/100)),

      Item_SellingPrice3 = if(ifnull(Category_Price3,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price3,0)/100)),
      Item_SellingPrice3Base1 = if(ifnull(Category_Price3,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price3,0)/100)),
      Item_SellingPrice3Base2 = if(ifnull(Category_Price3,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price3,0)/100)),

      Item_SellingPrice4 = if(ifnull(Category_Price4,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price4,0)/100)),
      Item_SellingPrice4Base1 = if(ifnull(Category_Price4,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price4,0)/100)),
      Item_SellingPrice4Base2 = if(ifnull(Category_Price4,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price4,0)/100)),

      Item_SellingPrice5 = if(ifnull(Category_Price5,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price5,0)/100)),
      Item_SellingPrice5Base1 = if(ifnull(Category_Price5,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price5,0)/100)),
      Item_SellingPrice5Base2 = if(ifnull(Category_Price5,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price5,0)/100)),

      Item_SellingPrice6 = if(ifnull(Category_Price6,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price6,0)/100)),
      Item_SellingPrice6Base1 = if(ifnull(Category_Price6,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price6,0)/100)),
      Item_SellingPrice6Base2 = if(ifnull(Category_Price6,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price6,0)/100))

where category.Category_code = items.Category_code and item_code=NEW.item_code;

  if @module_sellingbyclient = 0 then
  select clientssellingprice_amount=Item_SellingPrice1, clientssellingprice_amount2=Item_SellingPrice2, clientssellingprice_amount3=Item_SellingPrice3,
   clientssellingprice_amount4 = Item_SellingPrice4, clientssellingprice_amount5 = Item_SellingPrice5, clientssellingprice_amount6 = Item_SellingPrice6
  into @checkprice1,@checkprice2,@checkprice3,@checkprice4,@checkprice5,@checkprice6
  from clientssellingprice
  left join items on item_code = clientssellingprice_code
  where clientssellingprice_code = NEW.item_code and clientssellingprice_default = 1;

    if @checkprice1=0 or @checkprice2=0 or @checkprice3=0 or @checkprice4=0 or @checkprice5=0 or @checkprice6=0 then
      update clientssellingprice set clientssellingprice_default = 0 where clientssellingprice_code = NEW.item_code;

      insert into clientssellingprice (clientssellingprice_code, clientssellingprice_default,clientssellingprice_currency,
      clientssellingprice_amount,clientssellingprice_amountb1, clientssellingprice_amountb2,
       clientssellingprice_amount2,clientssellingprice_amount2b1, clientssellingprice_amount2b2,
       clientssellingprice_amount3, clientssellingprice_amount3b1, clientssellingprice_amount3b2,
      clientssellingprice_amount4, clientssellingprice_amount4b1, clientssellingprice_amount4b2,
      clientssellingprice_amount5, clientssellingprice_amount5b1,clientssellingprice_amount5b2,
      clientssellingprice_amount6, clientssellingprice_amount6b1, clientssellingprice_amount6b2)
      select NEW.item_code, 1,@item_cur,
      Item_SellingPrice1,Item_SellingPrice1Base1,Item_SellingPrice1Base2,
      Item_SellingPrice2,Item_SellingPrice2Base1,Item_SellingPrice2Base2,
      Item_SellingPrice3,Item_SellingPrice3Base1,Item_SellingPrice3Base2,
      Item_SellingPrice4,Item_SellingPrice4Base1,Item_SellingPrice4Base2,
      Item_SellingPrice5,Item_SellingPrice5Base1,Item_SellingPrice5Base2,
      Item_SellingPrice6,Item_SellingPrice6Base1,Item_SellingPrice6Base2
      from items
      where item_code = NEW.item_code;
    end if;

  end if;

end if;
			
END $$
Delimiter;
####################################################################################################################
#nadine #04-10-2016
Drop Trigger if exists PurchaseDT_AU;
Delimiter $$
CREATE  Trigger PurchaseDT_AU After Update on PurchasesDT
For Each Row Begin

  if new.Journal_Type = 'PI' then
    Set @Sign = 1;
	  UPDATE Purchases SET Purchase_TotalQuantity = Purchase_TotalQuantity - old.PurchaseDt_Quantity
  	where Purchases.Purchase_ID = old.Purchase_ID;

    UPDATE Purchases SET Purchase_TotalQuantity = Purchase_TotalQuantity + new.PurchaseDt_Quantity
	  where Purchases.Purchase_ID = new.Purchase_ID;
	else
    if new.Journal_Type = 'PR' then
      Set @Sign = -1;
    end if;
  end if;

  Set @ItemUnit = 0;
  Select Item_Code, Item_Unit,item_LastDatePurchase
  into @ItemCode, @ItemUnit ,@LastPurchaseDate
  From Items WHERE Item_Code = new.Item_Code;
  IF @ItemUnit=0 OR @ItemUnit IS NULL  then
	set @ItemUnit=1;
  end if;  
  #toni habshi 3.09.2008


  #delete from StkTransaction where Transaction_id = old.Purchasedt_id and Transaction_Type = old.Journal_Type;
  #Ayman 03.12.2009
  Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1;
  if @ModuleDoubleUnit then
    Set @Price = new.PurchaseDt_Price + @Sign*new.PurchaseDT_Charges/new.PurchaseDt_Quantity
      - new.PurchaseDt_Price*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountAmount/new.PurchaseDt_Quantity;
  	Set @PriceBase1 = new.PurchaseDt_PriceBase1 + @Sign*new.PurchaseDT_ChargesBase1/new.PurchaseDt_Quantity
      - new.PurchaseDt_PriceBase1*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase1/new.PurchaseDt_Quantity;
	  Set @PriceBase2 = new.PurchaseDt_PriceBase2 + @Sign*new.PurchaseDT_ChargesBase2/new.PurchaseDt_Quantity
      - new.PurchaseDt_PriceBase2*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase2/new.PurchaseDt_Quantity;

    #Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty)
    #Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,@Sign*new.PurchaseDt_Quantity,@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty);
    update `StkTransaction` set
    Transaction_Date=new.Purchase_Date
    ,Item_Code=new.Item_Code
    , Warehouse_Code=new.Warehouse_Code
    , Project_Code=new.Project_Code
    ,Costcent_Code=new.Costcent_Code
    ,Currency_Code=new.Currency_Code
    ,QuantityEntered=@Sign*new.PurchaseDt_Quantity
    ,Price=@Price
    ,PriceBase1=@PriceBase1
    ,PriceBase2=@PriceBase2
    ,Synchronized=new.Synchronized
    ,CostBase1=@PriceBase1
    ,CostBase2=@PriceBase2
    ,UnitCoef=new.Item_UnitCoef
    ,Unit2Qty=new.Item_Unit2Qty
    ,Item_ExpireDate = new.PurchaseDt_ExpireDate
    ,Item_BatchNo = new.PurchaseDt_BatchNo
     where Transaction_ID=new.Purchasedt_id and Transaction_Type=new.Journal_Type;


  else
    Set @Price = ((new.PurchaseDt_Price/@ItemUnit)	+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_Charges/(@ItemUnit*new.PurchaseDt_Quantity))-
			((((new.PurchaseDt_Price/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountAmount/(@ItemUnit*new.PurchaseDt_Quantity)));
    Set @PriceBase1 = ((new.PurchaseDt_PriceBase1/@ItemUnit) + (if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase1/(@ItemUnit*new.PurchaseDt_Quantity))-
			((((new.PurchaseDt_PriceBase1/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase1/(@ItemUnit*new.PurchaseDt_Quantity)));
    Set @PriceBase2 = ((new.PurchaseDt_PriceBase2/@ItemUnit)	+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase2/(@ItemUnit*new.PurchaseDt_Quantity))-
			((((new.PurchaseDt_PriceBase2/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase2/(@ItemUnit*new.PurchaseDt_Quantity)));
	  if @Price is null then set @Price = 0;end if;
	  if @PriceBase1 is null then set @PriceBase1 = 0;end if;
	  if @PriceBase2 is null then set @PriceBase2 = 0;end if;

	  #Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2)
    #Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.PurchaseDt_Quantity*(Select Item_Unit From Items WHERE Item_Code = new.Item_Code))
    #,@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2);

    update `StkTransaction` set
     Transaction_Date=new.Purchase_Date
     ,Item_Code=new.Item_Code
     , Warehouse_Code=new.Warehouse_Code
     , Project_Code=new.Project_Code
     ,Costcent_Code=new.Costcent_Code
     ,Currency_Code=new.Currency_Code
     ,QuantityEntered=(@Sign*new.PurchaseDt_Quantity*(Select Item_Unit From Items WHERE Item_Code = new.Item_Code))
     ,Price=@Price
     ,PriceBase1=@PriceBase1
     ,PriceBase2=@PriceBase2
     ,Synchronized=new.Synchronized
     ,CostBase1=@PriceBase1
     ,CostBase2=@PriceBase2
     ,Item_ExpireDate = new.PurchaseDt_ExpireDate
     ,Item_BatchNo = new.PurchaseDt_BatchNo
      where Transaction_ID=new.Purchasedt_id and Transaction_Type=new.Journal_Type;

  end if;

  if new.RelatedDt_Id <> 0 and new.journal_type = 'PI' then
  	Update `StkTransaction` Set QuantityPordered = QuantityPordered + old.PurchaseDt_Quantity
    where Transaction_ID = old.RelatedDt_ID and Transaction_Type = 'PO';

    Update `StkTransaction` Set QuantityPordered = QuantityPordered - new.PurchaseDt_Quantity
    where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'PO';
  end if;

#  select Purchase_invoiceno into @PInvoice from Purchases where Purchase_Id = old.Purchase_id;
#	select module_freezone into @module from module limit 1;
# if @module then
#	  update itemCustom set ItemCust_QtyIn = ItemCust_QtyIn - @sign * old.PurchaseDt_Quantity
#	  where Purchase_invoiceNo = @PInvoice and Item_code = old.Item_Code and Warehouse_code = old.Warehouse_Code;
#    if row_count() = 0 then
#	  	Insert into itemCustom set Purchase_invoiceNo = @PInvoice, Item_code = old.Item_Code,
#      Warehouse_code = old.Warehouse_Code, ItemCust_QtyIn = (-1)*@sign * old.PurchaseDt_Quantity;
#	  end if;
#	end if;

#  select Purchase_invoiceno into @PInvoice from Purchases where Purchase_Id = new.Purchase_id;
#	select module_freezone into @module from module limit 1;
#  if @module then
#	  update itemCustom set ItemCust_QtyIn = ItemCust_QtyIn + @sign * new.PurchaseDt_Quantity
#	  where Purchase_invoiceNo = @PInvoice and Item_code = new.Item_Code and Warehouse_code = new.Warehouse_Code;
# 	if row_count() = 0 then
#	  	insert into itemCustom set Purchase_invoiceNo = @PInvoice, Item_code = new.Item_Code,
#		  Warehouse_code = new.Warehouse_Code, ItemCust_QtyIn = @sign * new.PurchaseDt_Quantity;
#	  end if;
#	end if;
# if ((@LastPurchaseDate <=new.Purchase_Date) or (@LastPurchaseDate is null)) AND (new.journal_type = 'PI') AND (@price>0) then
#	  update items set Item_LastDatePurchase= cast(new.Purchase_Date as date),
#		   item_lastcostprice=if(currency_code=new.currency_code,@price,
#		   convertcurFunc(new.currency_code,currency_code,new.Purchase_Date,0,0,'','',@price)),
#		   item_lastcostpriceBase1=@PriceBase1,item_lastcostpriceBase2=@PriceBase2
#  			where item_code=new.item_code;
#  end if;
set @VarDate:=null;
    delete from itemstrans where item_code=old.Item_Code and ItemTrans_LastDatePurchase=old.Purchase_date;
    select ItemTrans_LastDatePurchase into @VarDate from ItemsTrans where item_code=new.Item_Code;
    if @VarDate is null then
          insert into ItemsTrans (Item_code,ItemTrans_LastDatePurchase,
             itemTrans_lastcostprice,itemTrans_lastcostpriceBase1,itemTrans_lastcostpriceBase2,ItemTrans_LastPricePurchase) values
             (new.Item_Code,new.Purchase_date,@Price,@PriceBase1,@PriceBase2,new.PurchaseDt_Price);
    else
        if new.Purchase_date>=@VarDate  then
           update ItemsTrans
            set ItemTrans_LastDatePurchase=new.Purchase_date,
             itemTrans_lastcostprice=@Price,
             itemTrans_lastcostpriceBase1=@PriceBase1,
             itemTrans_lastcostpriceBase2=@PriceBase2,
             ItemTrans_LastPricePurchase=new.PurchaseDt_Price
           where item_code=new.Item_Code;
        end if;
    end if;

#nadine #04-10-2016
select items.Category_code,Currency_Code into @item_category, @item_cur
from items
left join category on category.Category_code = items.Category_code
where item_code = new.Item_Code;

select ifnull(module_sellingbyclient,0),ifnull(sellingprice_basedonCost,0)  into @module_sellingbyclient, @sellingprice_basedonCost from module;

if @sellingprice_basedonCost =1 and @item_category is not null and @item_category != '' then
  set @vtotprice      := (((ifnull(NEW.PurchaseDt_Price,0)*NEW.PurchaseDt_Quantity)*(1-(NEW.PurchaseDt_DiscountPer/100)) - ifnull(NEW.PurchaseDt_DiscountAmount,0) ) + ifnull(NEW.PurchaseDt_Charges,0))/NEW.PurchaseDt_Quantity;

  set @vtotpriceb1    := (((ifnull(NEW.PurchaseDt_PriceBase1,0)*NEW.PurchaseDt_Quantity)*(1-(NEW.PurchaseDt_DiscountPer/100)) - ifnull(NEW.PurchaseDt_DiscountBase1,0) ) + ifnull(NEW.PurchaseDt_ChargesBase1,0))/NEW.PurchaseDt_Quantity;
  set @vtotpriceb2    := (((ifnull(NEW.PurchaseDt_PriceBase2,0)*NEW.PurchaseDt_Quantity)*(1-(NEW.PurchaseDt_DiscountPer/100)) - ifnull(NEW.PurchaseDt_DiscountBase2,0) ) + ifnull(NEW.PurchaseDt_ChargesBase2,0))/NEW.PurchaseDt_Quantity;
  set @vtotpricecur   := convertcurFunc(@item_cur, NEW.Currency_Code, NEW.Purchase_Date, 0, 0, '', '', @vtotprice);


update items,category set
      Item_SellingPrice1 = if(ifnull(Category_Price1,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price1,0)/100)),
      Item_SellingPrice1Base1 = if(ifnull(Category_Price1,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price1,0)/100)),
      Item_SellingPrice1Base2 = if(ifnull(Category_Price1,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price1,0)/100)),

      Item_SellingPrice2 = if(ifnull(Category_Price2,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price2,0)/100)),
      Item_SellingPrice2Base1 = if(ifnull(Category_Price2,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price2,0)/100)),
      Item_SellingPrice2Base2 = if(ifnull(Category_Price2,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price2,0)/100)),

      Item_SellingPrice3 = if(ifnull(Category_Price3,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price3,0)/100)),
      Item_SellingPrice3Base1 = if(ifnull(Category_Price3,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price3,0)/100)),
      Item_SellingPrice3Base2 = if(ifnull(Category_Price3,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price3,0)/100)),

      Item_SellingPrice4 = if(ifnull(Category_Price4,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price4,0)/100)),
      Item_SellingPrice4Base1 = if(ifnull(Category_Price4,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price4,0)/100)),
      Item_SellingPrice4Base2 = if(ifnull(Category_Price4,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price4,0)/100)),

      Item_SellingPrice5 = if(ifnull(Category_Price5,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price5,0)/100)),
      Item_SellingPrice5Base1 = if(ifnull(Category_Price5,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price5,0)/100)),
      Item_SellingPrice5Base2 = if(ifnull(Category_Price5,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price5,0)/100)),

      Item_SellingPrice6 = if(ifnull(Category_Price6,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price6,0)/100)),
      Item_SellingPrice6Base1 = if(ifnull(Category_Price6,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price6,0)/100)),
      Item_SellingPrice6Base2 = if(ifnull(Category_Price6,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price6,0)/100))

where category.Category_code = items.Category_code and item_code=NEW.item_code;




  if @module_sellingbyclient = 0 then
  select clientssellingprice_amount=Item_SellingPrice1, clientssellingprice_amount2=Item_SellingPrice2, clientssellingprice_amount3=Item_SellingPrice3,
   clientssellingprice_amount4 = Item_SellingPrice4, clientssellingprice_amount5 = Item_SellingPrice5, clientssellingprice_amount6 = Item_SellingPrice6
  into @checkprice1,@checkprice2,@checkprice3,@checkprice4,@checkprice5,@checkprice6
  from clientssellingprice
  left join items on item_code = clientssellingprice_code
  where clientssellingprice_code = NEW.item_code and clientssellingprice_default = 1;

    if @checkprice1=0 or @checkprice2=0 or @checkprice3=0 or @checkprice4=0 or @checkprice5=0 or @checkprice6=0 then
      update clientssellingprice set clientssellingprice_default = 0 where clientssellingprice_code = NEW.item_code;

      insert into clientssellingprice (clientssellingprice_code, clientssellingprice_default,clientssellingprice_currency,
      clientssellingprice_amount,clientssellingprice_amountb1, clientssellingprice_amountb2,
       clientssellingprice_amount2,clientssellingprice_amount2b1, clientssellingprice_amount2b2,
       clientssellingprice_amount3, clientssellingprice_amount3b1, clientssellingprice_amount3b2,
      clientssellingprice_amount4, clientssellingprice_amount4b1, clientssellingprice_amount4b2,
      clientssellingprice_amount5, clientssellingprice_amount5b1,clientssellingprice_amount5b2,
      clientssellingprice_amount6, clientssellingprice_amount6b1, clientssellingprice_amount6b2)
      select NEW.item_code, 1,@item_cur,
      Item_SellingPrice1,Item_SellingPrice1Base1,Item_SellingPrice1Base2,
      Item_SellingPrice2,Item_SellingPrice2Base1,Item_SellingPrice2Base2,
      Item_SellingPrice3,Item_SellingPrice3Base1,Item_SellingPrice3Base2,
      Item_SellingPrice4,Item_SellingPrice4Base1,Item_SellingPrice4Base2,
      Item_SellingPrice5,Item_SellingPrice5Base1,Item_SellingPrice5Base2,
      Item_SellingPrice6,Item_SellingPrice6Base1,Item_SellingPrice6Base2
      from items
      where item_code = NEW.item_code;
    end if;

  end if;


end if;

END $$
Delimiter;
#####################################################################################################################