
DROP procedure IF EXISTS `count_avr_cost`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `count_avr_cost`(IN `gdID` INT, IN `gdQuantity` DOUBLE, IN `gdCost` DOUBLE, IN `performed` INT, IN `gdObj` INT, IN `gdCost_cur` DOUBLE, IN `oprPos` INT)
    NO SQL
Begin


DECLARE Q1 DOUBLE;
DECLARE C1 DOUBLE;
DECLARE C1_CUR DOUBLE;
DECLARE qROWS int;
DECLARE avgcID int;
DECLARE AVG_COST DOUBLE;
DECLARE AVG_COST_CUR DOUBLE;

SELECT 
(IFNULL(SUM(CASE WHEN (opr_positive=1 AND opr_performed = 1) THEN opr_quantity ELSE 0 END),0)  
- IFNULL(SUM(CASE WHEN (opr_positive=0 AND opr_performed = 1 ) THEN opr_quantity ELSE 0 END),0)) gd_quantity
INTO Q1 FROM  operations 
WHERE (opr_good = gdID) AND ( opr_object = gdObj ) AND (opr_performed=1);

SELECT IFNULL(avgc_value,0), IFNULL(avgc_value_cur,0), IFNULL(avgc_id,0) INTO C1,  C1_CUR, avgcID FROM `dir_avg_cost` WHERE (`avgc_good`=gdID) AND  (`avgc_object` = gdObj) LIMIT 0,1;

SELECT COUNT(*) INTO qROWS FROM `dir_avg_cost` WHERE (`avgc_good`=gdID) AND (`avgc_object` = gdObj);

SET qROWS = IFNULL(qROWS,0);
SET C1 = IFNULL(C1,0);
SET C1_CUR = IFNULL(C1_CUR,0);
SET Q1 = IFNULL(Q1,0);

SET Q1 = IF(Q1<0,0,Q1);

If (oprPos = 1) AND (performed = 0)  then
SET AVG_COST = IFNULL (( ( (Q1 * C1) + (gdQuantity * gdCost)) / ( Q1 + gdQuantity ) ) ,0);
SET AVG_COST_CUR = IFNULL (( ( (Q1 * C1_CUR) + (gdQuantity * gdCost_cur)) / ( Q1 + gdQuantity ) ) ,0);
end if;

If (oprPos = 1) AND (performed = 1)  then 
SET AVG_COST = IFNULL (( ( (Q1 * C1) - (gdQuantity * gdCost)) / ( Q1 - gdQuantity ) ),0);
SET AVG_COST_CUR = IFNULL (( ( (Q1 * C1_CUR) - (gdQuantity * gdCost_cur)) / ( Q1 - gdQuantity ) ),0);
end if;


If (oprPos = 0) AND (performed = 0)  then
SET AVG_COST = IFNULL (( ( (Q1 * C1) - (gdQuantity * gdCost)) / ( Q1 - gdQuantity ) ) ,0);
SET AVG_COST_CUR = IFNULL (( ( (Q1 * C1_CUR) - (gdQuantity * gdCost_cur)) / ( Q1 - gdQuantity ) ) ,0);
end if;

If (oprPos = 0) AND (performed = 1)  then
SET AVG_COST = IFNULL (( ( (Q1 * C1) + (gdQuantity * gdCost)) / ( Q1 + gdQuantity ) ),0);
SET AVG_COST_CUR = IFNULL (( ( (Q1 * C1_CUR) + (gdQuantity * gdCost_cur)) / ( Q1 + gdQuantity ) ),0);
end if;

SET AVG_COST=IFNULL(AVG_COST,0);
SET AVG_COST_CUR=IFNULL(AVG_COST_CUR,0);

If (qROWS = 0) THEN INSERT INTO `dir_avg_cost` (avgc_id,avgc_good,avgc_object,avgc_value,avgc_value_cur) VALUES (NULL, gdID, gdObj, AVG_COST,AVG_COST_CUR);
end if;
If (qROWS = 1) THEN UPDATE `dir_avg_cost` SET `avgc_value` = AVG_COST, `avgc_value_cur` = AVG_COST_CUR WHERE (`avgc_id` = avgcID);
end if;



end$$

DELIMITER ;

