TYPE=TRIGGERS
triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tbl_invoice_det_before_ins_tr BEFORE INSERT ON tbl_invoice_det FOR EACH ROW
\nBEGIN
\nIF (@DISABLE_TRIGGERS IS NULL) then
\nIF ISNULL(NEW.in_det_id) OR (NEW.in_det_id=0) THEN
\nSET NEW.in_det_id=`func_get_uuid`();
\nEND IF;
\nEND IF;
\nEND' 'CREATE DEFINER=`root`@`localhost` TRIGGER tbl_invoice_det_after_ins_tr AFTER INSERT ON tbl_invoice_det FOR EACH ROW
\nBEGIN
\nIF (@DISABLE_TRIGGERS IS NULL) then
\n
\nINSERT IGNORE INTO `tbl_item_balance` SET
\n`it_balance_item_id`=NEW.in_det_item_id
\n,`it_balance_store_id`=NEW.in_det_store_id
\n,`it_balance_qty`=0
\n,`it_balance_qty_cost`=0
\n,`it_balance_qty_out`=0
\n,`it_balance_avg_cost`=0;
\n
\n
\nIF NEW.in_det_effect_amount=1 THEN
\nUPDATE `tbl_item_balance` SET
\n`it_balance_qty`=IF(NEW.in_det_const1=2,`it_balance_qty`-NEW.in_det_qty,IF(NEW.in_det_const1=1,`it_balance_qty`+NEW.in_det_qty,0))
\nWHERE
\n`it_balance_item_id`=NEW.in_det_item_id AND
\n`it_balance_store_id`=NEW.in_det_store_id;
\nEND IF;
\n
\n
\nIF
\n(NEW.in_det_qty_rec>0) AND
\n(NEW.in_det_effect_amount=1) AND
\n(NEW.in_det_effect_cost=1)
\nTHEN
\nINSERT INTO tbl_invoice_det_buy(
\n`in_det_id`,
\n`in_det_list_id`,
\n`in_det_store_id`,
\n`in_det_item_id`,
\n`in_det_unit_id`,
\n`in_det_qty`,
\n`in_det_qty_equal`,
\n`in_det_price`,
\n`in_det_cost`,
\n`in_det_in_out_type`,
\n`in_det_const1`,
\n`in_det_effect_price`,
\n`in_det_effect_cost`,
\n`in_det_currency_id`,
\n`in_det_currency_val`,
\n`in_det_date_temp`,
\n`in_det_cust_id_temp`)
\nSELECT
\nNEW.`in_det_id`,
\nNEW.`in_det_list_id`,
\nNEW.`in_det_store_id`,
\nNEW.`in_det_item_id`,
\nNEW.`in_det_unit_id`,
\nNEW.`in_det_qty`,
\nNEW.`in_det_qty_equal`,
\nNEW.`in_det_price`,
\nNEW.`in_det_cost`,
\nNEW.`in_det_in_out_type`,
\nNEW.`in_det_const1`,
\nNEW.`in_det_effect_price`,
\nNEW.`in_det_effect_cost`,
\nNEW.`in_det_currency_id`,
\nNEW.`in_det_currency_val`,
\nNEW.`in_det_date_temp`,
\nNEW.`in_det_cust_id_temp`;
\n
\nUPDATE `tbl_item` SET
\n`it_un_price_cost_avg`=
\nIF(NEW.in_det_const1=2,
\nIFNULL(((`it_un_price_cost_avg`*`it_un_qty_cost_avg`)-(NEW.`in_det_qty`*(NEW.`in_det_cost`/NEW.`in_det_qty_equal`)*NEW.`in_det_currency_val`))/ (`it_un_qty_cost_avg`-NEW.`in_det_qty`),0),
\nIFNULL(((`it_un_price_cost_avg`*`it_un_qty_cost_avg`)+(NEW.`in_det_qty`*(NEW.`in_det_cost`/NEW.`in_det_qty_equal`)*NEW.`in_det_currency_val`))/ (NEW.`in_det_qty`+`it_un_qty_cost_avg`),0)),
\n`it_un_qty_cost_avg`=`it_un_qty_cost_avg`+IF(NEW.in_det_const1=2,NEW.in_det_qty*-1,NEW.in_det_qty)
\nWHERE
\n`it_id`=NEW.in_det_item_id;
\nEND IF;
\n
\n
\nIF
\n(NEW.in_det_qty_rec>0) AND
\n(NEW.in_det_effect_amount=1) AND
\n(NEW.in_det_effect_cost=0)
\nTHEN
\nUPDATE `tbl_item` SET
\n`it_un_qty_cost_out`=`it_un_qty_cost_out`+IF(NEW.in_det_const1=1,NEW.in_det_qty*-1,NEW.in_det_qty)
\nWHERE
\n`it_id`=NEW.in_det_item_id;
\n
\nEND IF;
\n
\nEND IF;
\nEND' 'CREATE DEFINER=`root`@`localhost` TRIGGER tbl_invoice_det_after_del_tr AFTER DELETE ON tbl_invoice_det FOR EACH ROW
\nBEGIN
\nIF (@DISABLE_TRIGGERS IS NULL) then
\nIF
\n(OLD.in_det_qty_rec>0) AND
\n(OLD.in_det_effect_amount=1) AND
\n(OLD.in_det_effect_cost=1)
\nTHEN
\nDELETE FROM tbl_invoice_det_buy WHERE tbl_invoice_det_buy.`in_det_id`=OLD.`in_det_id`;
\nUPDATE `tbl_item` SET
\n`it_un_price_cost_avg`=
\nIF(OLD.in_det_const1=2,
\nIFNULL(((`it_un_price_cost_avg`*`it_un_qty_cost_avg`)+(OLD.`in_det_qty`*(OLD.`in_det_cost`/OLD.`in_det_qty_equal`)*OLD.`in_det_currency_val`))/ (OLD.`in_det_qty`+`it_un_qty_cost_avg`),0),
\nIFNULL(((`it_un_price_cost_avg`*`it_un_qty_cost_avg`)-(OLD.`in_det_qty`*(OLD.`in_det_cost`/OLD.`in_det_qty_equal`)*OLD.`in_det_currency_val`))/ (`it_un_qty_cost_avg`-OLD.`in_det_qty`),0)),
\n`it_un_qty_cost_avg`=`it_un_qty_cost_avg`-IF(OLD.in_det_const1=2,OLD.in_det_qty*-1,OLD.in_det_qty)
\nWHERE
\n`it_id`=OLD.in_det_item_id;
\n
\nEND IF;
\n
\nIF OLD.in_det_effect_amount=1 THEN
\nUPDATE `tbl_item_balance` SET
\n`it_balance_qty`=IF(OLD.in_det_const1=2,`it_balance_qty`+OLD.in_det_qty,IF(OLD.in_det_const1=1,`it_balance_qty`-OLD.in_det_qty,0))
\nWHERE
\n`it_balance_item_id`=OLD.in_det_item_id AND
\n`it_balance_store_id`=OLD.in_det_store_id;
\nEND IF;
\n
\nIF (OLD.in_det_qty_rec>0) AND
\n(OLD.in_det_effect_amount=1) AND
\n(OLD.in_det_effect_cost=0)
\nTHEN
\nUPDATE `tbl_item` SET
\n`it_un_qty_cost_out`=`it_un_qty_cost_out`-IF(OLD.in_det_const1=1,OLD.in_det_qty*-1,OLD.in_det_qty)
\nWHERE
\n`it_id`=OLD.in_det_item_id;
\nEND IF;
\n
\nEND IF;
\nEND'
sql_modes=1344274434 1344274434 1344274434
definers='root@localhost' 'root@localhost' 'root@localhost'
client_cs_names='cp1256' 'cp1256' 'cp1256'
connection_cl_names='cp1256_general_ci' 'cp1256_general_ci' 'cp1256_general_ci'
db_cl_names='cp1256_general_ci' 'cp1256_general_ci' 'cp1256_general_ci'
