Stored Procedures
1. Drop all triggers
delimiter $$
drop trigger if exists ´before_insert_damagedetail´ $$
drop trigger if exists ´before_update_damagedetail´ $$
drop trigger if exists ´before_delete_damagedetail´ $$
drop trigger if exists ´before_insert_damagehead´ $$
drop trigger if exists ´before_update_damagehead´ $$
drop trigger if exists ´before_delete_damagehead´ $$
drop trigger if exists ´before_insert_productinventoryadjust´ $$
drop trigger if exists ´before_update_productinventoryadjust´ $$
drop trigger if exists ´before_delete_productinventoryadjust´ $$
drop trigger if exists ´before_insert_purchasereceivedetail´ $$
drop trigger if exists ´before_update_purchasereceivedetail´ $$
drop trigger if exists ´before_delete_purchasereceivedetail´ $$
drop trigger if exists ´before_insert_purchasereceivehead´ $$
drop trigger if exists ´before_update_purchasereceivehead´ $$
drop trigger if exists ´before_delete_purchasereceivehead´ $$
drop trigger if exists ´before_insert_purchasereturndetail´ $$
drop trigger if exists ´before_update_purchasereturndetail´ $$
drop trigger if exists ´before_delete_purchasereturndetail´ $$
drop trigger if exists ´before_insert_purchasereturnhead´ $$
drop trigger if exists ´before_update_purchasereturnhead´ $$
drop trigger if exists ´before_delete_purchasereturnhead´ $$
drop trigger if exists ´before_insert_salesdetail´ $$
drop trigger if exists ´before_update_salesdetail´ $$
drop trigger if exists ´before_delete_salesdetail´ $$
drop trigger if exists ´before_insert_saleshead´ $$
drop trigger if exists ´before_update_saleshead´ $$
drop trigger if exists ´before_delete_saleshead´ $$
drop trigger if exists ´before_insert_stockdeliverydetail´ $$
drop trigger if exists ´before_update_stockdeliverydetail´ $$
drop trigger if exists ´before_delete_stockdeliverydetail´ $$
drop trigger if exists ´before_insert_stockdeliveryhead´ $$
drop trigger if exists ´before_update_stockdeliveryhead´ $$
drop trigger if exists ´before_delete_stockdeliveryhead´ $$
delimiter ;2. Give permission to the user to access/edit/add/remove triggers
UPDATE mysql.user SET Password=PASSWORD('121586') WHERE User='root';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON nelsoft.* TO root @'%' IDENTIFIED BY '121586';
FLUSH PRIVILEGES;
UPDATE mysql.user SET SELECT_PRIV='Y' WHERE User='root';
FLUSH PRIVILEGES;3. Run the following queries to re-initialize the procedures
#Run the drop all triggers query found above.
CREATE TRIGGER ´before_insert_damagedetail´ BEFORE INSERT ON ´damagedetail´ FOR EACH ROW
proc_:BEGIN
call process_detail ((new.quantity*new.bigquantity),new.´productid´,
new.headid, -1,11,new.wid);
END $$
CREATE TRIGGER ´before_update_damagedetail´ BEFORE UPDATE ON ´damagedetail´ FOR EACH ROW
proc_:BEGIN
call process_detail ((old.quantity*old.bigquantity),old.´productid´,
old.headid, 1,11,old.wid);
call process_detail ((new.quantity*new.bigquantity),new.´productid´,
new.headid, -1,11,new.wid);
END $$
CREATE TRIGGER ´before_delete_damagedetail´ BEFORE DELETE ON ´damagedetail´ FOR EACH ROW
proc_:BEGIN
call process_detail ((old.quantity*old.bigquantity),old.´productid´,
old.headid, 1,11,old.wid);
END $$
CREATE TRIGGER ´before_insert_damagehead´ BEFORE INSERT ON ´damagehead´ FOR EACH ROW
proc_:BEGIN
CALL ´process_damagehead´(new.wid, new.´show´, new.´status´,
new.branchid, -1, new.´date´);
END $$
CREATE TRIGGER ´before_update_damagehead´ BEFORE UPDATE ON ´damagehead´ FOR EACH ROW
proc_:BEGIN
CALL ´process_damagehead´(old.wid, old.´show´, old.´status´,
old.branchid, 1,old.´date´);
CALL ´process_damagehead´(new.wid, new.´show´, new.´status´,
new.branchid, -1,new.´date´);
END $$
CREATE TRIGGER ´before_delete_damagehead´ BEFORE DELETE ON ´damagehead´ FOR EACH ROW
proc_: BEGIN
CALL ´process_damagehead´(old.wid, old.´show´, old.´status´, old.branchid, 1,old.´date´);
END $$
CREATE TRIGGER ´before_insert_productinventoryadjust´ BEFORE INSERT ON ´productinventoryadjust´ FOR EACH ROW
proc_:BEGIN
IF( new.wid > 0 AND new.´status´ = 1 AND new.branchid > 0) THEN
CALL ´process_productbranchinventory´(
new.productid, new.branchid, new.changedquantity , new.´datecreated´,9,new.wid);
end if;
END $$
CREATE TRIGGER ´before_update_productinventoryadjust´ BEFORE UPDATE ON ´productinventoryadjust´ FOR EACH ROW
proc_:BEGIN
IF( new.wid > 0 AND old.´status´=1 AND new.branchid > 0) THEN
CALL ´process_productbranchinventory´(
old.productid, old.branchid, old.changedquantity*(-1), old.´datecreated´,9,old.wid);
end if;
IF( new.wid > 0 AND new.´status´ = 1 AND new.branchid > 0) THEN
CALL ´process_productbranchinventory´(
new.productid, new.branchid, new.changedquantity , new.´datecreated´,9,new.wid);
end if;
END $$
CREATE TRIGGER ´before_delete_productinventoryadjust´ BEFORE DELETE ON ´productinventoryadjust´ FOR EACH ROW
proc_:BEGIN
IF( old.wid > 0 AND old.´status´ = 1 AND old.branchid > 0) THEN
CALL ´process_productbranchinventory´(
old.productid, old.branchid, old.changedquantity*(-1), old.´datecreated´,9,old.wid);
end if;
END $$
CREATE TRIGGER ´before_insert_purchasereceivedetail´ BEFORE INSERT ON ´purchasereceivedetail´ FOR EACH ROW
proc_:BEGIN
call process_detail ((new.quantity*new.bigquantity),new.´productid´,
new.headid, 1,1,new.wid);
#CALL ´set_suppliertransactionsummary´(new.headid,2,-1);
END $$
CREATE TRIGGER ´before_update_purchasereceivedetail´ BEFORE UPDATE ON ´purchasereceivedetail´ FOR EACH ROW
proc_:BEGIN
call process_detail ((old.quantity*old.bigquantity),old.´productid´,
old.headid, -1,1,old.wid);
call process_detail ((new.quantity*new.bigquantity),new.´productid´,
new.headid, 1,1,new.wid);
#if(old.headid > 0) then
# CALL ´set_suppliertransactionsummary´(old.headid,2,-1);
#elseif (old.headid = 0) then
# CALL ´set_suppliertransactionsummary´(new.headid,2,-1);
#end if;
END $$
CREATE TRIGGER ´before_delete_purchasereceivedetail´ BEFORE DELETE ON ´purchasereceivedetail´ FOR EACH ROW
proc_:BEGIN
call process_detail ((old.quantity*old.bigquantity),old.´productid´,
old.headid, -1,1,old.wid);
#CALL ´set_suppliertransactionsummary´(old.headid,2,-1);
END $$
CREATE TRIGGER ´before_insert_purchasereceivehead´ BEFORE INSERT ON ´purchasereceivehead´ FOR EACH ROW
proc_:BEGIN
CALL ´process_purchasereceivehead´(new.wid, new.´show´, new.´status´,
new.branchid, 1,new.´receivedate´);
END $$
CREATE TRIGGER ´before_update_purchasereceivehead´ BEFORE UPDATE ON ´purchasereceivehead´ FOR EACH ROW
proc_:BEGIN
CALL ´process_purchasereceivehead´(old.wid, old.´show´, old.´status´,
old.branchid, -1,old.´receivedate´);
CALL ´process_purchasereceivehead´(new.wid, new.´show´, new.´status´,
new.branchid, 1,new.´receivedate´);
#CALL ´set_suppliertransactionsummary´(old.wid,2,-1);
END $$
CREATE TRIGGER ´before_delete_purchasereceivehead´ BEFORE DELETE ON ´purchasereceivehead´ FOR EACH ROW
proc_: BEGIN
CALL ´process_purchasereceivehead´(old.wid, old.´show´, old.´status´, old.branchid, -1,old.´receivedate´);
#CALL ´set_suppliertransactionsummary´(old.wid,2,-1);
END $$
CREATE TRIGGER ´before_insert_purchasereturndetail´ BEFORE INSERT ON ´purchasereturndetail´ FOR EACH ROW
proc_:BEGIN
call process_detail ((new.quantity*new.bigquantity),new.´productid´,
new.headid, -1,2,new.wid);
#CALL ´set_suppliertransactionsummary´(new.headid,4,-1);
END $$
CREATE TRIGGER ´before_update_purchasereturndetail´ BEFORE UPDATE ON ´purchasereturndetail´ FOR EACH ROW
proc_:BEGIN
call process_detail ((old.quantity*old.bigquantity),old.´productid´,
old.headid, 1,2,old.wid);
call process_detail ((new.quantity*new.bigquantity),new.´productid´,
new.headid, -1,2,new.wid);
#if(old.headid > 0) then
# CALL ´set_suppliertransactionsummary´(old.headid,4,-1);
#elseif (old.headid = 0) then
# CALL ´set_suppliertransactionsummary´(new.headid,4,-1);
#end if;
END $$
CREATE TRIGGER ´before_delete_purchasereturndetail´ BEFORE DELETE ON ´purchasereturndetail´ FOR EACH ROW
proc_:BEGIN
call process_detail ((old.quantity*old.bigquantity),old.´productid´,
old.headid, 1,2,old.wid);
#CALL ´set_suppliertransactionsummary´(old.headid,4,-1);
END $$
CREATE TRIGGER ´before_insert_purchasereturnhead´ BEFORE INSERT ON ´purchasereturnhead´ FOR EACH ROW
proc_:BEGIN
CALL ´process_purchasereturnhead´(new.wid, new.´show´, new.´status´,
new.branchid, -1,new.´orderdate´);
END $$
CREATE TRIGGER ´before_update_purchasereturnhead´ BEFORE UPDATE ON ´purchasereturnhead´ FOR EACH ROW
proc_:BEGIN
CALL ´process_purchasereturnhead´(old.wid, old.´show´, old.´status´,
old.branchid, 1,old.´orderdate´);
CALL ´process_purchasereturnhead´(new.wid, new.´show´, new.´status´,
new.branchid, -1,new.´orderdate´);
#CALL ´set_suppliertransactionsummary´(old.wid,4,-1);
END $$
CREATE TRIGGER ´before_delete_purchasereturnhead´ BEFORE DELETE ON ´purchasereturnhead´ FOR EACH ROW
proc_: BEGIN
CALL ´process_purchasereturnhead´(old.wid, old.´show´, old.´status´, old.branchid, 1,old.´orderdate´);
#CALL ´set_suppliertransactionsummary´(old.wid,4,-1);
END $$
CREATE TRIGGER ´before_insert_salesdetail´ BEFORE INSERT ON ´salesdetail´ FOR EACH ROW
proc_:BEGIN
SET @qty = new.quantity * new.bigquantity;
CALL process_detail(@qty, new.productid, new.headid, -1,3,new.wid);
SET @addback = new.addbackqty * new.addbackbigqty;
CALL process_detail(@addback, new.productid, new.headid, 1,3,new.wid);
call ´process_sales_fncs´(new.productid,@qty,@addback,-1,new.headid);
#CALL ´set_customertransactionsummary´(new.headid,2,-1);
END $$
CREATE TRIGGER ´before_update_salesdetail´ BEFORE UPDATE ON ´salesdetail´ FOR EACH ROW
proc_:BEGIN
SET @qty = old.quantity * old.bigquantity;
CALL process_detail(@qty, old.productid, old.headid, 1,3,old.wid);
SET @addback = old.addbackqty * old.addbackbigqty;
CALL process_detail(@addback, old.productid, old.headid, -1,3,old.wid);
call ´process_sales_fncs´(old.productid,@qty,@addback,1,old.headid);
SET @qty = new.quantity * new.bigquantity;
CALL process_detail(@qty, new.productid, new.headid, -1,3,new.wid);
SET @addback = new.addbackqty * new.addbackbigqty;
CALL process_detail(@addback, new.productid, new.headid, 1,3,new.wid);
call ´process_sales_fncs´(new.productid,@qty,@addback,-1,new.headid);
#if(old.headid > 0) then
# CALL ´set_customertransactionsummary´(old.headid,2,-1);
#elseif (old.headid = 0) then
# CALL ´set_customertransactionsummary´(new.headid,2,-1);
#end if;
END $$
CREATE TRIGGER ´before_delete_salesdetail´ BEFORE DELETE ON ´salesdetail´ FOR EACH ROW
proc_:BEGIN
SET @qty = old.quantity * old.bigquantity;
CALL process_detail(@qty, old.productid, old.headid, 1,3,old.wid);
SET @addback = old.addbackqty * old.addbackbigqty;
CALL process_detail(@addback, old.productid, old.headid, -1,3,old.wid);
call ´process_sales_fncs´(old.productid,@qty,@addback,1,old.headid);
#CALL ´set_customertransactionsummary´(old.headid,2,-1);
END $$
CREATE TRIGGER ´before_insert_saleshead´ BEFORE INSERT ON ´saleshead´ FOR EACH ROW
proc_: BEGIN
CALL ´process_saleshead´(new.wid, new.´show´, new.´status´, new.branchid, -1, new.´date´);
call ´process_sales_fncs_head´(new.branchid,new.´date´,new.wid,new.´status´,new.´show´,-1,3);
END $$
CREATE TRIGGER ´before_update_saleshead´ BEFORE UPDATE ON ´saleshead´ FOR EACH ROW
proc_: BEGIN
CALL ´process_saleshead´(old.wid, old.´show´, old.´status´, old.branchid, 1, old.´date´);
call ´process_sales_fncs_head´(old.branchid,old.´date´,old.wid,old.´status´,old.´show´,1,3);
CALL ´process_saleshead´(new.wid, new.´show´, new.´status´, new.branchid, -1, new.´date´);
call ´process_sales_fncs_head´(new.branchid,new.´date´,new.wid,new.´status´,new.´show´,-1,3);
#CALL ´set_customertransactionsummary´(old.wid,2,-1);
END $$
CREATE TRIGGER ´before_delete_saleshead´ BEFORE DELETE ON ´saleshead´ FOR EACH ROW
proc_: BEGIN
CALL ´process_saleshead´(old.wid, old.´show´, old.´status´, old.branchid, 1, old.´date´);
call ´process_sales_fncs_head´(old.branchid,old.´date´,old.wid,old.´status´,old.´show´,1,3);
#CALL ´set_customertransactionsummary´(old.wid,2,-1);
END $$
CREATE TRIGGER ´before_insert_stockdeliverydetail´ BEFORE INSERT ON ´stockdeliverydetail´ FOR EACH ROW
proc_:BEGIN
call process_detail ((new.quantity*new.bigquantity),new.´productid´,
new.headid, -1,5,new.wid);
call process_detail (new.recvqty,new.´productid´,
new.headid, 1,6,new.wid);
END $$
CREATE TRIGGER ´before_update_stockdeliverydetail´ BEFORE UPDATE ON ´stockdeliverydetail´ FOR EACH ROW
proc_:BEGIN
call process_detail ((old.quantity*old.bigquantity),old.´productid´,
old.headid, 1,5,old.wid);
call process_detail ((new.quantity*new.bigquantity),new.´productid´,
new.headid, -1,5,new.wid);
call process_detail (old.recvqty,old.´productid´,
old.headid, -1,6,old.wid);
call process_detail (new.recvqty,new.´productid´,
new.headid, 1,6,new.wid);
END $$
CREATE TRIGGER ´before_delete_stockdeliverydetail´ BEFORE DELETE ON ´stockdeliverydetail´ FOR EACH ROW
proc_:BEGIN
call process_detail ((old.quantity*old.bigquantity),old.´productid´,
old.headid, 1,5,old.wid);
call process_detail (old.recvqty,old.´productid´,
old.headid, -1,6,old.wid);
END $$
CREATE TRIGGER ´before_insert_stockdeliveryhead´ BEFORE INSERT ON ´stockdeliveryhead´ FOR EACH ROW
proc_:BEGIN
CALL ´process_stockdeliveryhead´(new.wid, new.´show´, new.´status´,
new.frombranchid, -1,1,new.´deliverydate´);
CALL ´process_stockdeliveryhead´(new.wid, new.´show´, new.´status´,
new.tobranchid, 1,2,new.´datereceived´);
END $$
CREATE TRIGGER ´before_update_stockdeliveryhead´ BEFORE UPDATE ON ´stockdeliveryhead´ FOR EACH ROW
proc_:BEGIN
CALL ´process_stockdeliveryhead´(old.wid, old.´show´, old.´status´,
old.frombranchid, 1,1,old.´deliverydate´);
CALL ´process_stockdeliveryhead´(new.wid, new.´show´, new.´status´,
new.frombranchid, -1,1,new.´deliverydate´);
CALL ´process_stockdeliveryhead´(old.wid, old.´show´, old.´status´,
old.tobranchid, -1,2,old.´datereceived´);
CALL ´process_stockdeliveryhead´(new.wid, new.´show´, new.´status´,
new.tobranchid, 1,2,new.´datereceived´);
END $$
CREATE TRIGGER ´before_delete_stockdeliveryhead´ BEFORE DELETE ON ´stockdeliveryhead´ FOR EACH ROW
proc_: BEGIN
CALL ´process_stockdeliveryhead´(old.wid, old.´show´, old.´status´, old.frombranchid, 1,1,old.´deliverydate´);
CALL ´process_stockdeliveryhead´(old.wid, old.´show´, old.´status´, old.tobranchid, -1,2,old.´datereceived´);
END $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´check_product´(
in wid_d BIGINT(20),
in stockno_d varchar(50),
in product_d varchar(300),
in barcode_d varchar(50),
in clientbarcode_d varchar(50),
in clientbarcode2_d varchar(50),
in packbarcode_d varchar(50),
in packbarcode2_d varchar(50)
)
proc_checkprod:BEGIN
declare nelsoft_stockno_cntr double unsigned default 0;
declare nelsoft_product_cntr double unsigned default 0;
declare nelsoft_barcode_cntr double unsigned default 0;
declare nelsoft_clientbarcode_cntr double unsigned default 0;
declare nelsoft_clientbarcode2_cntr double unsigned default 0;
declare nelsoft_packbarcode_cntr double unsigned default 0;
declare nelsoft_packbarcode2_cntr double unsigned default 0;
start transaction;
SET AUTOCOMMIT=0;
select wid from product where ´wid´ > 10 and ´show´ = 1 for update;
SELECT count(*) into nelsoft_stockno_cntr
FROM product
WHERE stockno = stockno_d AND wid != wid_d AND ´show´ = 1
AND length(stockno_d) > 0 for update;
if(nelsoft_stockno_cntr > 0) then
rollback;
select 1;
LEAVE proc_checkprod;
end if;
SELECT count(*) into nelsoft_product_cntr
FROM product
WHERE ´product´ = product_d
AND wid != wid_d
AND ´show´ = 1
and length(product_d) > 0 for update;
if(nelsoft_product_cntr > 0) then
rollback;
select 2;
LEAVE proc_checkprod;
end if;
#barcode
SELECT count(*) into nelsoft_barcode_cntr
FROM product
WHERE ((´barcode´ = barcode_d OR ´clientbarcode´ = barcode_d OR ´packbarcode´ = barcode_d
OR ´clientbarcode2´ = barcode_d OR ´packbarcode2´ = barcode_d)
and length(barcode_d) > 0)
AND wid != wid_d AND ´show´ = 1 for update;
if(nelsoft_barcode_cntr > 0) then
rollback;
select 3;
LEAVE proc_checkprod;
end if;
#clientbarcode
SELECT count(*) into nelsoft_clientbarcode_cntr
FROM product
WHERE ((´barcode´ = clientbarcode_d OR ´clientbarcode´ = clientbarcode_d OR ´packbarcode´ = clientbarcode_d
OR ´clientbarcode2´ = clientbarcode_d OR ´packbarcode2´ = clientbarcode_d)
and length(clientbarcode_d) > 0)
AND wid != wid_d AND ´show´ = 1 for update;
if(nelsoft_clientbarcode_cntr > 0) then
rollback;
select 4;
LEAVE proc_checkprod;
end if;
#clientbarcode2
SELECT count(*) into nelsoft_clientbarcode2_cntr
FROM product
WHERE ((´barcode´ = clientbarcode2_d OR ´clientbarcode´ = clientbarcode2_d OR ´packbarcode´ = clientbarcode2_d
OR ´clientbarcode2´ = clientbarcode2_d OR ´packbarcode2´ = clientbarcode2_d)
and length(clientbarcode2_d) > 0)
AND wid != wid_d AND ´show´ = 1 for update;
if(nelsoft_clientbarcode2_cntr > 0) then
rollback;
select 5;
LEAVE proc_checkprod;
end if;
#packbarcode
SELECT count(*) into nelsoft_packbarcode_cntr
FROM product
WHERE ((´barcode´ = packbarcode_d OR ´clientbarcode´ = packbarcode_d OR ´packbarcode´ = packbarcode_d
OR ´clientbarcode2´ = packbarcode_d OR ´packbarcode2´ = packbarcode_d)
and length(packbarcode_d) > 0)
AND wid != wid_d AND ´show´ = 1 for update;
if(nelsoft_packbarcode_cntr > 0) then
rollback;
select 6;
LEAVE proc_checkprod;
end if;
#packbarcode2
SELECT count(*) into nelsoft_packbarcode2_cntr
FROM product
WHERE ((´barcode´ = packbarcode2_d OR ´clientbarcode´ = packbarcode2_d OR ´packbarcode´ = packbarcode2_d
OR ´clientbarcode2´ = packbarcode2_d OR ´packbarcode2´ = packbarcode2_d)
and length(packbarcode2_d) > 0)
AND wid != wid_d AND ´show´ = 1 for update;
if(nelsoft_packbarcode2_cntr > 0) then
rollback;
select 7;
LEAVE proc_checkprod;
end if;
update ´product´ set
´stockno´ = stockno_d,
´product´ = product_d,
´barcode´ = barcode_d,
´clientbarcode´ = clientbarcode_d,
´packbarcode´ = packbarcode_d,
´clientbarcode2´ = clientbarcode2_d,
´packbarcode2´ = packbarcode2_d,
´show´ = 1
where ´wid´ = wid_d;
select 0;
commit;
END $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´procedure_set_begininginv´(in frombranch integer, in uptobranch integer)
BEGIN
DECLARE sitecode INT DEFAULT frombranch;
DECLARE curbranch INT DEFAULT 10;
DECLARE isremote_val INT DEFAULT 0;
DECLARE branchno int default 0;
select ´value´ into curbranch from config where particular = 'branchid';
SELECT COUNT(*) INTO branchno FROM ´branch´;
IF( branchno > 0 ) THEN
TRUNCATE TABLE productinventorylog;
ALTER TABLE ´productinventorylog´ AUTO_INCREMENT = 1 ;
test_loop : LOOP
IF (sitecode > uptobranch) THEN
LEAVE test_loop;
END IF;
select ´isremote´ into isremote_val from branch where wid = sitecode;
if(sitecode = curbranch or (isremote_val = 1 and curbranch = 10 )) then
DELETE FROM ´productbranchinventory´ WHERE ´branchid´ = sitecode;
REPLACE INTO productbranchinventory (´branchid´, ´productid´, ´inv´, ´date´)
select * from (
SELECT sitecode, P.´wid´ AS 'pwid',
COALESCE(PR.´qty´,0)
- COALESCE(PRtn.´qty´,0)
- COALESCE(SRtn.´qty´,0)
+ COALESCE(SRtn.´rqty´,0)
- COALESCE(SD.´qty´,0)
+ COALESCE(SR.´qty´,0)
+ COALESCE(ADJ.´qty´,0)
- COALESCE(DMG.´qty´,0) AS 'inv', NOW()
FROM ´product´ AS P
LEFT JOIN (
SELECT D.´productid´, SUM(D.´quantity´ * D.´bigquantity´) AS 'qty'
FROM ´purchasereceivehead´ AS H, ´purchasereceivedetail´ AS D
WHERE D.´headid´ = H.´wid´ AND H.´show´ = 1
AND H.´status´ = 1 AND H.´branchid´ = sitecode
AND D.´productid´ > 0
GROUP BY D.´productid´
) AS PR ON P.´wid´ = PR.´productid´
LEFT JOIN (
SELECT D.´productid´, SUM(D.´quantity´ * D.´bigquantity´) AS 'qty'
FROM ´purchasereturnhead´ AS H, ´purchasereturndetail´ AS D
WHERE D.´headid´ = H.´wid´ AND H.´show´ = 1
AND H.´status´ = 1 AND H.´branchid´ = sitecode
AND D.´productid´ > 0
GROUP BY D.´productid´
) AS PRtn ON P.´wid´ = PRtn.´productid´
LEFT JOIN (
SELECT D.´productid´,
SUM(IF(D.´quantity´ < 0,0,D.´quantity´) * D.´bigquantity´) AS 'qty',
SUM(D.´addbackqty´ * D.´addbackbigqty´) AS 'rqty'
FROM ´saleshead´ AS H, ´salesdetail´ AS D
WHERE D.´headid´ = H.´wid´ AND H.´show´ = 1
AND H.´status´ = 1 AND H.´branchid´ = sitecode
AND D.´productid´ > 0
GROUP BY D.´productid´
) AS SRtn ON P.´wid´ = SRtn.´productid´
LEFT JOIN (
SELECT D.´productid´, SUM(D.´quantity´ * D.´bigquantity´) AS 'qty'
FROM ´stockdeliveryhead´ AS H, ´stockdeliverydetail´ AS D
WHERE D.´headid´ = H.´wid´ AND H.´show´ = 1
AND H.´status´ = 1 AND H.´frombranchid´ = sitecode
AND D.´productid´ > 0
GROUP BY D.´productid´
) AS SD ON P.´wid´ = SD.´productid´
LEFT JOIN (
SELECT D.´productid´, SUM(´recvqty´) AS 'qty'
FROM ´stockdeliveryhead´ AS H, ´stockdeliverydetail´ AS D
WHERE D.´headid´ = H.´wid´ AND H.´show´ = 1
AND H.´status´ = 1 AND H.´tobranchid´ = sitecode
AND D.´productid´ > 0
GROUP BY D.´productid´
) AS SR ON P.´wid´ = SR.´productid´
LEFT JOIN (
SELECT ´productid´, SUM(´changedquantity´) AS 'qty'
FROM ´productinventoryadjust´
WHERE ´status´ = 1 AND ´branchid´ = sitecode
AND ´productid´ > 0
GROUP BY ´productid´
) AS ADJ ON P.´wid´ = ADJ.´productid´
LEFT JOIN (
SELECT D.´productid´, SUM(D.´quantity´ * D.´bigquantity´) AS 'qty'
FROM ´damagehead´ AS H, ´damagedetail´ AS D
WHERE D.´headid´ = H.´wid´ AND H.´show´ = 1
AND H.´status´ = 1 AND H.´branchid´ = sitecode
AND D.´productid´ > 0
GROUP BY D.´productid´
) AS DMG ON P.´wid´ = DMG.´productid´
WHERE P.´show´ = 1 AND P.´wid´ > 1
) A
where inv <> 0;
end if;
SET sitecode = sitecode +1;
END LOOP;
END IF;
END $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´procedure_set_customer_transaction_summary´()
BEGIN
DECLARE curbranch INT DEFAULT 10;
select ´value´ into curbranch from config where particular = 'branchid';
if(curbranch = 10) then
delete from ´customertransactionsummary´ where branchid = 10;
elseif(curbranch > 10) then
TRUNCATE table ´customertransactionsummary´;
end if;
insert into customertransactionsummary (customerid, branchid, ´date´, orderamount, tradeamount, returnamount, paidamount)
select A.customerid, curbranch, A.´date´,sum(if(A.´type´ = 1,A.´amt´,0)) as 'orderamount',
sum(if(A.´type´ = 2,A.´amt´,0)) as 'tradeamount',
sum(if(A.´type´ = 2,A.´retamt´,0)) as 'returnamount',
sum(if(A.´type´ = 3,A.´amt´,0)) as 'paidamount'
from (
select 1 as 'type', H.´customerid´, date(´date´) as 'date', sum(D.´quantity´*D.´bigquantity´*D.´price´) as 'amt', 0 as 'retamt'
from ´customer´ as C, ´salesorderhead´ as H, ´salesorderdetail´ as D
where H.´wid´ = D.´headid´ and H.´customerid´ = C.´wid´
and H.´show´ = 1
and H.´status´ = 1
and C.´show´ = 1
and H.´branchid´ = curbranch
and H.´customerid´ > 0
group by date(´date´), H.´customerid´
union all
select 2 as 'type', A.customerid, A.´date´, sum(A.posamt) as 'amt' , sum(A.negamt) as 'retamt'
from (
select H.wid, date(H.´date´) as 'date', H.customerid, case
when H.´type´ = 3 then
sum(if(D.quantity>0,D.quantity*D.price,0))
when H.´type´ <> 3 then
sum(if(D.quantity>0,D.quantity*D.price*D.bigquantity,0))
*((100 - H.´discount1´)/100)
*((100 - H.´discount2´)/100)
*((100 - H.´discount3´)/100)
*((100 - H.´discount4´)/100)
+(´adjust´*abs(sum(if(D.quantity>0,D.quantity*D.price*D.bigquantity,0))/sum(abs(D.quantity)*D.price*D.bigquantity)))
end as 'posamt',
abs(case
when H.´type´ = 3 then
sum(if(D.quantity<0,D.quantity*D.price,0))
when H.´type´ <> 3 then
(sum(if(D.quantity<0,D.quantity*D.price*D.bigquantity,0))
*((100 - H.´discount1´)/100)
*((100 - H.´discount2´)/100)
*((100 - H.´discount3´)/100)
*((100 - H.´discount4´)/100))
+(´adjust´*abs(sum(if(D.quantity<0,D.quantity*D.price*D.bigquantity,0))/sum(abs(D.quantity)*D.price*D.bigquantity)))
end) as 'negamt'
from ´customer´ as C,saleshead as H, salesdetail as D
where H.´wid´ = D.´headid´ and H.´customerid´ = C.´wid´
and H.´show´ = 1
and H.´status´ = 1
and C.´show´ = 1
and ´branchid´ = curbranch
group by H.wid
) A
group by date(´date´), ´customerid´
union all
select 3 as 'type', H.´customerid´, date(´collectiondate´) as 'date', sum(D.amount) as 'amt', 0 as 'retamt'
from ´customer´ as C,´collectionhead´ as H, ´collectiondetail´ as D
where H.´wid´ = D.´headid´ and H.´customerid´ = C.´wid´
and H.´show´ = 1
and H.´status´ = 1
and C.´show´ = 1
and H.´branchid´ = curbranch
and H.´customerid´ > 0
group by date(´collectiondate´), H.´customerid´
) A
group by date(´date´), ´customerid´;
END $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´process_damagehead´(
in ´wid_d´ BIGINT(20),
in ´show_d´ integer,
in ´status_d´ integer,
in ´branchid_d´ integer,
in ´add_minus´ integer,
in ´date_d´ datetime
)
proc_: begin
DECLARE bDone BOOL DEFAULT FALSE;
DECLARE productid_d VARCHAR(200) DEFAULT '';
DECLARE qty_d DOUBLE DEFAULT 0;
DECLARE dwid_d DOUBLE DEFAULT 0;
DECLARE curs CURSOR FOR
SELECT ´productid´, ´wid´, ´quantity´*´bigquantity´ AS 'qty'
FROM ´damagedetail´ WHERE ´headid´ = wid_d;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = TRUE;
IF( wid_d > 0 and show_d = 1 AND status_d = 1 AND branchid_d > 0) THEN
OPEN curs;
read_loop: LOOP
FETCH curs INTO productid_d, dwid_d, qty_d;
IF bDone THEN
LEAVE read_loop;
END IF;
CALL ´process_productbranchinventory´(
productid_d, branchid_d, add_minus*qty_d, date_d, 11, dwid_d);
END LOOP;
CLOSE curs;
END IF;
end $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´process_detail´(
in ´qty_d´ double,
in ´productid_d´ BIGINT(20),
in ´headid_d´ BIGINT(20),
in ´add_minus´ integer,
in ´table_d´ integer,
in ´dwid_d´ BIGINT(20)
)
proc_: begin
IF( productid_d > 0 AND headid_d > 0 AND qty_d > 0 ) THEN
CASE
WHEN table_d = 1 THEN
SELECT COUNT(*), ´show´, ´status´, ´branchid´,´receivedate´
INTO @cnt, @show_d, @status_d, @branchid_d, @date_d
FROM ´purchasereceivehead´ WHERE ´wid´ = headid_d;
WHEN table_d = 2 THEN
SELECT COUNT(*), ´show´, ´status´, ´branchid´, ´orderdate´
INTO @cnt, @show_d, @status_d, @branchid_d, @date_d
FROM ´purchasereturnhead´ WHERE ´wid´ = headid_d;
WHEN table_d = 3 THEN
SELECT COUNT(*), ´show´, ´status´, ´branchid´, ´date´
INTO @cnt, @show_d, @status_d, @branchid_d, @date_d
FROM ´saleshead´ WHERE ´wid´ = headid_d;
WHEN table_d = 5 THEN
SELECT COUNT(*), ´show´, ´status´, ´frombranchid´, ´deliverydate´
INTO @cnt, @show_d, @status_d, @branchid_d, @date_d
FROM ´stockdeliveryhead´ WHERE ´wid´ = headid_d;
WHEN table_d = 6 THEN
SELECT COUNT(*), ´show´, ´status´, ´tobranchid´, ´datereceived´
INTO @cnt, @show_d, @status_d, @branchid_d, @date_d
FROM ´stockdeliveryhead´ WHERE ´wid´ = headid_d;
WHEN table_d = 11 THEN
SELECT COUNT(*), ´show´, ´status´, ´branchid´, ´date´
INTO @cnt, @show_d, @status_d, @branchid_d, @date_d
FROM ´damagehead´ WHERE ´wid´ = headid_d;
END CASE;
IF( qty_d > 0 AND @cnt > 0 AND @show_d = 1 AND @status_d = 1 AND @branchid_d > 0) THEN
CALL ´process_productbranchinventory´(
productid_d, @branchid_d, add_minus*qty_d, @date_d, table_d, dwid_d);
END IF;
END IF;
end $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´process_productbranchinventory´(
in ´productid_d´ BIGINT(20),
in ´branchid_d´ integer,
in ´qty_d´ double,
in ´date_d´ datetime,
in ´type_d´ integer,
in ´referencewid_d´ BIGINT(20)
)
begin
SELECT ´value´, COUNT(*) INTO @cur_branchid, @branchid_cnt
FROM ´config´ WHERE ´particular´ = 'branchid';
IF(qty_d <> 0 AND productid_d > 1 AND branchid_d > 0) THEN
IF(type_d <> 3 and type_d <> 4) then
call process_producttransactionsummary(date_d,branchid_d,type_d,productid_d,qty_d);
end if;
END IF;
select ´isremote´ into @isRemote from ´branch´ where wid = branchid_d;
IF( qty_d <> 0 AND productid_d > 1 AND branchid_d > 0 AND (@cur_branchid = branchid_d or (@isRemote = 1 and @cur_branchid = 10))) THEN
INSERT INTO ´productbranchinventory´
(´productid´, ´branchid´, ´inv´, ´date´)
VALUES (productid_d, branchid_d, qty_d, NOW())
ON DUPLICATE KEY UPDATE ´inv´ = ´inv´ + qty_d, ´date´ = now();#date_d -> now()
SET @newinv = (SELECT ´inv´ FROM ´productbranchinventory´
WHERE ´productid´ = productid_d AND ´branchid´ = branchid_d);
INSERT INTO ´productinventorylog´
(´productid´,´branchid´,´date´,´qty´,´newinv´,´type´,´referencewid´) VALUES
( productid_d, branchid_d, now(), qty_d, @newinv, type_d, referencewid_d ); #date_d -> now()
delete from ´sync´
where ´tablename´ = 'productbranchinventory'
and ´wid´= productid_d
and ´branchid´<> @cur_branchid;
if(@isRemote = 0) then
if(@cur_branchid = 10) then
INSERT INTO sync (´tablename´, ´wid´, ´branchid´)
SELECT 'productbranchinventory', productid_d, ´wid´
FROM ´branch´ WHERE ´wid´ <> @cur_branchid and ´isremote´ = 0;
ELSEIF ( @cur_branchid <> 10 ) THEN
INSERT INTO sync (´tablename´, ´wid´, ´branchid´)
SELECT 'productbranchinventory', productid_d, 10;
end if;
end if;
END IF;
end $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´process_producttransactionsummary´(
in ´date_d´ datetime,
in ´branchid_d´ integer,
in ´table_d´ integer,
in ´productid_d´ BIGINT(20),
in ´qty_d´ double
)
proc_: begin
SELECT ´value´, COUNT(*) INTO @cur_branchid, @branchid_cnt
FROM ´config´ WHERE ´particular´ = 'branchid';
set @isRemote = 0;
select ´isremote´ into @isRemote from ´branch´ where wid = branchid_d;
IF( qty_d <> 0 AND productid_d > 1 AND branchid_d > 0 AND (@cur_branchid = branchid_d or (@isRemote = 1 and @cur_branchid = 10))) THEN
set @purchasereceive = 0;
set @purchasereturn = 0;
set @sales = 0;
set @salesreturn = 0;
set @stockdelivery = 0;
set @stockreceive = 0;
set @damage = 0;
set @adjust_inc = 0;
set @adjust_dec = 0;
set @temp = 0;
set @formatted_date = date(concat(date(date_d),' 00:00:00'));
if(table_d = 1)THEN
set @purchasereceive = qty_d;
elseif(table_d = 2)THEN
set @purchasereturn = qty_d * -1;
elseif(table_d = 3 )THEN
set @sales = qty_d * -1;
elseif(table_d = 4)THEN
set @salesreturn = qty_d;
elseif(table_d = 5)THEN
set @stockdelivery = qty_d * -1;
elseif(table_d = 6)THEN
set @stockreceive = qty_d;
elseif(table_d = 9 and qty_d > 0)THEN
set @adjust_inc = qty_d;
elseif(table_d = 9 and qty_d < 0)THEN
set @adjust_dec = qty_d * -1;
elseif(table_d = 11)THEN
set @damage = qty_d * -1;
end if;
update ´producttransactionsummary´
set
´purchasereceive´ = ´purchasereceive´ + @purchasereceive,
´purchasereturn´ = ´purchasereturn´ + @purchasereturn,
´sales´ = ´sales´ + @sales,
´salesreturn´ = ´salesreturn´ + @salesreturn,
´damage´ = ´damage´ + @damage,
´adjust_inc´ = ´adjust_inc´ + @adjust_inc,
´adjust_dec´ = ´adjust_dec´ + @adjust_dec,
´stockdelivery´ = ´stockdelivery´ + @stockdelivery,
´stockreceive´ = ´stockreceive´ + @stockreceive,
´productid´ = (@temp := productid_d),
´id´=LAST_INSERT_ID(´id´)
where ´branchid´ = branchid_d and ´date´ = @formatted_date and ´productid´ = productid_d;
if(@temp = 0) then
INSERT INTO ´producttransactionsummary´
(´branchid´,´date´,´productid´,´purchasereceive´,´purchasereturn´,´sales´,´salesreturn´,
´damage´,´adjust_inc´,´adjust_dec´,´stockdelivery´,´stockreceive´)
values
(branchid_d,date(date_d),productid_d,@purchasereceive,@purchasereturn,@sales,@salesreturn,
@damage,@adjust_inc,@adjust_dec,@stockdelivery,@stockreceive);
end if;
set @lastid = LAST_INSERT_ID();
if(´branchid_d´ <> 10 && @isRemote = 0) then
delete from sync where tablename = 'producttransactionsummary' and wid = @lastid;
insert into ´sync´(´tablename´,´wid´,´branchid´) values ('producttransactionsummary',@lastid,10);
end if;
END IF;
end $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´process_producttransactionsummary_head´(
in ´branchid_d´ integer,
in ´date_d´ datetime,
in ´wid_d´ BIGINT(20),
in ´status_d´ integer,
in ´show_d´ integer,
in ´addminus´ integer,
in ´table_d´ integer
)
proc_: begin
DECLARE bDone BOOL DEFAULT FALSE;
DECLARE productid_d VARCHAR(200) DEFAULT '';
DECLARE qty_d DOUBLE DEFAULT 0;
DECLARE addback_d DOUBLE DEFAULT 0;
DECLARE curs CURSOR FOR
SELECT ´productid´, ´quantity´*´bigquantity´ AS 'qty',
´addbackqty´ * ´addbackbigqty´ AS 'addback'
FROM ´salesdetail´ WHERE ´headid´ = wid_d;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = TRUE;
IF( wid_d > 0) THEN
IF( show_d = 1 AND status_d = 1 AND branchid_d > 0) THEN
OPEN curs;
read_loop: LOOP
FETCH curs INTO productid_d, qty_d, addback_d;
IF bDone THEN
LEAVE read_loop;
END IF;
IF(qty_d > 0) THEN
call ´process_producttransactionsummary´(date_d,branchid_d,table_d,productid_d,addminus*qty_d);
END IF;
END LOOP;
CLOSE curs;
END IF;
END IF;
end $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´process_purchasereceivehead´(
in ´wid_d´ BIGINT(20),
in ´show_d´ integer,
in ´status_d´ integer,
in ´branchid_d´ integer,
in ´add_minus´ integer,
in ´date_d´ datetime
)
proc_: begin
DECLARE bDone BOOL DEFAULT FALSE;
DECLARE productid_d VARCHAR(200) DEFAULT '';
DECLARE qty_d DOUBLE DEFAULT 0;
DECLARE dwid_d DOUBLE DEFAULT 0;
DECLARE curs CURSOR FOR
SELECT ´productid´, ´wid´, ´quantity´*´bigquantity´ AS 'qty'
FROM ´purchasereceivedetail´ WHERE ´headid´ = wid_d;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = TRUE;
IF( wid_d > 0 and show_d = 1 AND status_d = 1 AND branchid_d > 0) THEN
OPEN curs;
read_loop: LOOP
FETCH curs INTO productid_d, ´dwid_d´, qty_d;
IF bDone THEN
LEAVE read_loop;
END IF;
CALL ´process_productbranchinventory´(
productid_d, branchid_d, add_minus*qty_d, date_d, 1, dwid_d);
END LOOP;
CLOSE curs;
END IF;
end $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´process_purchasereturnhead´(
in ´wid_d´ BIGINT(20),
in ´show_d´ integer,
in ´status_d´ integer,
in ´branchid_d´ integer,
in ´add_minus´ integer,
in ´date_d´ datetime
)
proc_: begin
DECLARE bDone BOOL DEFAULT FALSE;
DECLARE productid_d VARCHAR(200) DEFAULT '';
DECLARE qty_d DOUBLE DEFAULT 0;
DECLARE dwid_d DOUBLE DEFAULT 0;
DECLARE curs CURSOR FOR
SELECT ´productid´, ´wid´, ´quantity´*´bigquantity´ AS 'qty'
FROM ´purchasereturndetail´ WHERE ´headid´ = wid_d;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = TRUE;
IF( wid_d > 0 and show_d = 1 AND status_d = 1 AND branchid_d > 0) THEN
OPEN curs;
read_loop: LOOP
FETCH curs INTO productid_d, dwid_d, qty_d;
IF bDone THEN
LEAVE read_loop;
END IF;
CALL ´process_productbranchinventory´(
productid_d, branchid_d, add_minus*qty_d, date_d, 2, dwid_d);
END LOOP;
CLOSE curs;
END IF;
end $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´process_sales_fncs´(
in ´productid_d´ BIGINT(20),
in ´qty_d´ integer,
in ´addbackqty_d´ integer,
in ´add_minus´ integer,
in ´headid_d´ BIGINT(20)
)
proc_: begin
SELECT COUNT(*), ´show´, ´status´, ´branchid´,´date´
INTO @cnt, @show_d, @status_d, @branchid_d, @date_d
FROM ´saleshead´ WHERE ´wid´ = headid_d;
IF( @show_d = 1 AND @status_d = 1 AND @branchid_d > 0) THEN
IF(qty_d > 0) THEN
call ´process_producttransactionsummary´(@date_d,@branchid_d,3,productid_d,qty_d*add_minus);
END IF;
call ´process_salesreturn´(@date_d,qty_d,addbackqty_d,productid_d,headid_d,add_minus);
END IF;
end $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´process_sales_fncs_head´(
in ´branchid_d´ integer,
in ´date_d´ datetime,
in ´wid_d´ BIGINT(20),
in ´status_d´ integer,
in ´show_d´ integer,
in ´add_minus´ integer,
in ´table_d´ integer
)
proc_: begin
IF( show_d = 1 AND status_d = 1 AND branchid_d > 0) THEN
call ´process_producttransactionsummary_head´(branchid_d,date_d,wid_d,status_d,show_d,add_minus,3);
call ´process_salesreturn_head´(wid_d,date_d,branchid_d,status_d,show_d,add_minus);
END IF;
end $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´process_saleshead´(
in ´wid_d´ BIGINT(20),
in ´show_d´ integer,
in ´status_d´ integer,
in ´branchid_d´ integer,
in ´add_minus´ integer,
in ´date_d´ datetime
)
proc_: begin
DECLARE bDone BOOL DEFAULT FALSE;
DECLARE productid_d VARCHAR(200) DEFAULT '';
DECLARE qty_d DOUBLE DEFAULT 0;
DECLARE dwid_d DOUBLE DEFAULT 0;
DECLARE addback_d DOUBLE DEFAULT 0;
DECLARE curs CURSOR FOR
SELECT ´productid´, ´wid´, ´quantity´*´bigquantity´ AS 'qty',
´addbackqty´ * ´addbackbigqty´ AS 'addback'
FROM ´salesdetail´ WHERE ´headid´ = wid_d;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = TRUE;
IF( wid_d > 0) THEN
IF( show_d = 1 AND status_d = 1 AND branchid_d > 0) THEN
OPEN curs;
read_loop: LOOP
FETCH curs INTO productid_d, dwid_d, qty_d, addback_d;
IF bDone THEN
LEAVE read_loop;
END IF;
IF qty_d > 0 THEN
CALL ´process_productbranchinventory´(
productid_d, branchid_d, add_minus*qty_d, date_d, 3, dwid_d);
END IF;
IF addback_d > 0 THEN
CALL ´process_productbranchinventory´(
productid_d, branchid_d, -1*add_minus*addback_d, date_d, 3, dwid_d);#from 4 to 3
END IF;
END LOOP;
CLOSE curs;
END IF;
END IF;
end $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´process_salesreturn´(
in ´date_d´ datetime,
in ´salesqty_d´ double,
in ´addbackqty_d´ double,
in ´productid_d´ BIGINT(20),
in ´headid_d´ BIGINT(20),
in ´add_minus´ integer
)
proc_: begin
set @cnt = 0;
set @show_d = 0;
set @status_d = 0;
set @branchid_d = 0;
set @damagedqty_d = abs(salesqty_d)-abs(addbackqty_d);
SELECT COUNT(*), ´show´, ´status´, ´branchid´
INTO @cnt, @show_d, @status_d, @branchid_d
FROM ´saleshead´ WHERE ´wid´ = headid_d;
IF( @damagedqty_d > 0 AND salesqty_d < 0 AND @cnt > 0 AND @show_d = 1 AND @status_d = 1 AND @branchid_d > 0) THEN
call process_producttransactionsummary(date_d,@branchid_d,11,productid_d,add_minus*@damagedqty_d);
#call process_producttransactionsummary(date_d,@branchid_d,3,productid_d,-1*add_minus*@damagedqty_d);
END IF;
IF( salesqty_d < 0 AND @cnt > 0 AND @show_d = 1 AND @status_d = 1 AND @branchid_d > 0) THEN
call process_producttransactionsummary(date_d,@branchid_d,4,productid_d,add_minus*salesqty_d);
END IF;
end $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´process_salesreturn_head´(
in ´wid_d´ BIGINT(20),
in ´date_d´ datetime,
in ´branchid_d´ integer,
in ´status_d´ integer,
in ´show_d´ integer,
in ´add_minus´ integer
)
proc_: begin
DECLARE bDone BOOL DEFAULT FALSE;
DECLARE productid_d VARCHAR(200) DEFAULT '';
DECLARE qty_d DOUBLE DEFAULT 0;
DECLARE addback_d DOUBLE DEFAULT 0;
DECLARE curs CURSOR FOR
SELECT ´productid´, ´quantity´*´bigquantity´ AS 'qty',
´addbackqty´ * ´addbackbigqty´ AS 'addback'
FROM ´salesdetail´ WHERE ´headid´ = wid_d;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = TRUE;
IF( wid_d > 0) THEN
IF( show_d = 1 AND status_d = 1 AND branchid_d > 0) THEN
OPEN curs;
read_loop: LOOP
FETCH curs INTO productid_d, qty_d, addback_d;
IF bDone THEN
LEAVE read_loop;
END IF;
IF(qty_d < 0) THEN
set @damagedqty_d = abs(qty_d)-abs(addback_d);
IF( @damagedqty_d > 0 AND qty_d < 0 AND show_d = 1 AND status_d = 1 AND branchid_d > 0) THEN
call process_producttransactionsummary(date_d,branchid_d,11,productid_d,add_minus*@damagedqty_d);
#call process_producttransactionsummary(date_d,branchid_d,3,productid_d,-1*add_minus*@damagedqty_d);
END IF;
IF( qty_d < 0 AND show_d = 1 AND status_d = 1 AND branchid_d > 0) THEN
call process_producttransactionsummary(date_d,branchid_d,4,productid_d,add_minus*qty_d);
END IF;
END IF;
END LOOP;
CLOSE curs;
END IF;
END IF;
end $$
CREATE DEFINER=´root´@´localhost´ PROCEDURE ´process_stockdeliveryhead´(
in ´wid_d´ BIGINT(20),
in ´show_d´ integer,
in ´status_d´ integer,
in ´branchid_d´ integer,
in ´add_minus´ integer,
in ´whichQTY´ integer,
in ´date_d´ datetime
)
proc_: begin
DECLARE bDone BOOL DEFAULT FALSE;
DECLARE productid_d VARCHAR(200) DEFAULT '';
DECLARE qty_d DOUBLE DEFAULT 0;
DECLARE dwid_d DOUBLE DEFAULT 0;
DECLARE recvqty_d DOUBLE DEFAULT 0;
DECLARE curs CURSOR FOR
SELECT ´productid´, ´wid´, ´quantity´*´bigquantity´ AS 'qty', ´recvqty´
FROM ´stockdeliverydetail´ WHERE ´headid´ = wid_d;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = TRUE;
IF( wid_d > 0 and show_d = 1 AND status_d = 1 AND branchid_d > 0) THEN
OPEN curs;
read_loop: LOOP
FETCH curs INTO productid_d, dwid_d, qty_d, recvqty_d;
IF bDone THEN
LEAVE read_loop;
END IF;
if(whichQTY = 1)then
CALL ´process_productbranchinventory´(
productid_d, branchid_d, add_minus*qty_d, date_d, 5, dwid_d);
end if;
if(whichQTY = 2)then
CALL ´process_productbranchinventory´(
productid_d, branchid_d, add_minus*recvqty_d, date_d, 6, dwid_d);
end if;
END LOOP;
CLOSE curs;
END IF;
end $$
delimiter $$
DROP TRIGGER IF EXISTS before_insert_productinventoryadjust$$
CREATE TRIGGER ´before_insert_productinventoryadjust´ BEFORE INSERT ON ´productinventoryadjust´ FOR EACH ROW
proc_:BEGIN
IF( new.wid > 0 AND new.´status´ = 1 AND new.branchid > 0 AND new.istransfer = 0) THEN
CALL ´process_productbranchinventory´(
new.productid, new.branchid, new.changedquantity , new.´datecreated´,9,new.wid);
end if;
END $$
DROP TRIGGER IF EXISTS before_update_productinventoryadjust$$
CREATE TRIGGER ´before_update_productinventoryadjust´ BEFORE UPDATE ON ´productinventoryadjust´ FOR EACH ROW
proc_:BEGIN
IF( new.wid > 0 AND old.´status´=1 AND new.branchid > 0 AND new.istransfer = 0) THEN
CALL ´process_productbranchinventory´(
old.productid, old.branchid, old.changedquantity*(-1), old.´datecreated´,9,old.wid);
end if;
IF( new.wid > 0 AND new.´status´ = 1 AND new.branchid > 0 AND new.istransfer = 0) THEN
CALL ´process_productbranchinventory´(
new.productid, new.branchid, new.changedquantity , new.´datecreated´,9,new.wid);
end if;
END $$
DROP TRIGGER IF EXISTS before_delete_productinventoryadjust$$
CREATE TRIGGER ´before_delete_productinventoryadjust´ BEFORE DELETE ON ´productinventoryadjust´ FOR EACH ROW
proc_:BEGIN
IF( old.wid > 0 AND old.´status´ = 1 AND old.branchid > 0 AND old.istransfer = 0) THEN
CALL ´process_productbranchinventory´(
old.productid, old.branchid, old.changedquantity*(-1), old.´datecreated´,9,old.wid);
end if;
END $$delimiter $$
drop procedure ´process_sales_fncs´ $$
CREATE PROCEDURE ´process_sales_fncs´(
in ´productid_d´ BIGINT(20),
in ´qty_d´ double,
in ´addbackqty_d´ double,
in ´add_minus´ integer,
in ´headid_d´ BIGINT(20)
)
proc_: begin
SELECT COUNT(*), ´show´, ´status´, ´branchid´,´date´
INTO @cnt, @show_d, @status_d, @branchid_d, @date_d
FROM ´saleshead´ WHERE ´wid´ = headid_d;
IF( @show_d = 1 AND @status_d = 1 AND @branchid_d > 0) THEN
IF(qty_d > 0) THEN
call ´process_producttransactionsummary´(@date_d,@branchid_d,3,productid_d,qty_d*add_minus);
END IF;
call ´process_salesreturn´(@date_d,qty_d,addbackqty_d,productid_d,headid_d,add_minus);
END IF;
end $$