F AGGREGATE PERCENTAGE

CREATE OR REPLACE FUNCTION f_agregate_percentage(character varying, character varying, character varying, integer, integer, integer)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId      ALIAS FOR $1;
    pIdData         ALIAS FOR $2;
    pDelimeter      ALIAS FOR $3;
    pChild          ALIAS FOR $4;
    pParent         ALIAS FOR $5;
    pDecimalPoint       ALIAS FOR $6;

BEGIN
    DELETE FROM tt_dashboard_data WHERE session_id = pSessionId AND id_data = pIdData;
    DELETE FROM tt_dashboard_data_precision WHERE session_id = pSessionId AND id_data = pIdData;
    DELETE FROM tt_dashboard_measure_precision WHERE session_id = pSessionId AND id_data = pIdData;

    IF pChild = 1 THEN
      UPDATE tt_input_data SET child_group = data_group_1 WHERE session_id = pSessionId AND id_data = pIdData;
    ELSE
        IF pChild = 2 THEN
          UPDATE tt_input_data SET child_group = data_group_1||pDelimeter||data_group_2 WHERE session_id = pSessionId AND id_data = pIdData;
        ELSE
            IF pChild = 3 THEN
              UPDATE tt_input_data SET child_group = data_group_1||pDelimeter||data_group_2||pDelimeter||data_group_3 WHERE session_id = pSessionId AND id_data = pIdData;
            ELSE
                IF pChild = 4 THEN
                  UPDATE tt_input_data SET child_group = data_group_1||pDelimeter||data_group_2||pDelimeter||data_group_3||pDelimeter||data_group_4 WHERE session_id = pSessionId AND id_data = pIdData;
                ELSE
                  UPDATE tt_input_data SET child_group = data_group_1||pDelimeter||data_group_2||pDelimeter||data_group_3||pDelimeter||data_group_4||pDelimeter||data_group_5 WHERE session_id = pSessionId AND id_data = pIdData;
                END IF;
            END IF;
        END IF;
    END IF;

    IF pParent = 1 THEN
      UPDATE tt_input_data SET parent_group = data_group_1 WHERE session_id = pSessionId AND id_data = pIdData;
    ELSE
        IF pParent = 2 THEN
          UPDATE tt_input_data SET parent_group = data_group_1||pDelimeter||data_group_2 WHERE session_id = pSessionId AND id_data = pIdData;
        ELSE
            IF pParent = 3 THEN
              UPDATE tt_input_data SET parent_group = data_group_1||pDelimeter||data_group_2||pDelimeter||data_group_3 WHERE session_id = pSessionId AND id_data = pIdData;
            ELSE
                IF pParent = 4 THEN
                  UPDATE tt_input_data SET parent_group = data_group_1||pDelimeter||data_group_2||pDelimeter||data_group_3||pDelimeter||data_group_4 WHERE session_id = pSessionId AND id_data = pIdData;
                ELSE
                  UPDATE tt_input_data SET parent_group = data_group_1||pDelimeter||data_group_2||pDelimeter||data_group_3||pDelimeter||data_group_4||pDelimeter||data_group_5 WHERE session_id = pSessionId AND id_data = pIdData;
                END IF;
            END IF;
        END IF;
    END IF;

    INSERT INTO tt_group_data
    (session_id,id_data,data_group_1,data_group_2,data_group_3,data_group_4,data_group_5,
    child_group,parent_group)
    SELECT session_id, id_data, data_group_1, data_group_2,
        data_group_3, data_group_4, data_group_5,
        child_group, parent_group
    FROM tt_input_data
    WHERE session_id = pSessionId AND
        id_data = pIdData
    GROUP BY session_id, id_data, data_group_1, data_group_2, data_group_3, data_group_4, data_group_5,
        child_group,parent_group;

        /**
         * Adrian, Mar 27, 2017
         * Add tax_amount, flg_tax_amount, tax_id
         */
        INSERT INTO tt_dashboard_data
        (session_id,id_data,child_group,parent_group,
         sum_measure, min_measure,max_measure,avg_measure,
         sub_total, total, percentage,
         total_measure, percentage_measure,
         tax_amount, flg_tax_amount, tax_id)
        SELECT DISTINCT session_id, id_data, child_group, parent_group,
               SUM(data_measure) OVER (PARTITION BY child_group),
               MIN(data_measure) OVER (PARTITION BY child_group),
               MAX(data_measure) OVER (PARTITION BY child_group),
               AVG(data_measure) OVER (PARTITION BY child_group),
               SUM(data_count) OVER (PARTITION BY child_group),
               SUM(data_count) OVER (PARTITION BY parent_group),
               100,
               SUM(data_measure) OVER (PARTITION BY parent_group),
               100,
               SUM(tax_amount) OVER (PARTITION BY child_group),
               flg_tax_amount, tax_id
        FROM tt_input_data
        WHERE session_id = pSessionId AND id_data = pIdData
        ORDER BY child_group;

    UPDATE tt_dashboard_data SET percentage = ROUND(100*sub_total/total,pDecimalPoint)::numeric WHERE session_id = pSessionId AND id_data = pIdData;

    /**
     * Adrian, Mar 27, 2017
     * Handle division by zero
     */
    UPDATE tt_dashboard_data SET percentage_measure =
        CASE WHEN total_measure > 0 THEN ROUND(100*sum_measure/total_measure,pDecimalPoint)::numeric
        ELSE 0 END
    WHERE session_id = pSessionId AND id_data = pIdData;

    INSERT INTO tt_dashboard_data_precision
    (session_id, id_data, rank_id,
    child_group, percentage,
    total_percentage, sum_percentage)
    SELECT session_id, id_data, RANK() OVER (PARTITION BY parent_group ORDER BY parent_group, sub_total DESC, id DESC),
        child_group, percentage,
        100, SUM(percentage) OVER (PARTITION BY parent_group)
    FROM tt_dashboard_data
    WHERE session_id = pSessionId AND id_data = pIdData;

    UPDATE tt_dashboard_data_precision
    SET percentage = percentage - (sum_percentage - total_percentage)
    WHERE session_id = pSessionId AND
        rank_id = 1 AND
        id_data = pIdData;

    INSERT INTO tt_dashboard_measure_precision
    (session_id, id_data, rank_id,
    child_group, percentage,
    total_percentage, sum_percentage)
    SELECT session_id, id_data, RANK() OVER (PARTITION BY parent_group ORDER BY parent_group, sum_measure DESC, id DESC),
        child_group, percentage_measure,
        100, SUM(percentage_measure) OVER (PARTITION BY parent_group)
    FROM tt_dashboard_data
    WHERE session_id = pSessionId AND id_data = pIdData;

    UPDATE tt_dashboard_measure_precision
    SET percentage = percentage - (sum_percentage - total_percentage)
    WHERE session_id = pSessionId AND
        rank_id = 1 AND
        id_data = pIdData;

    /**
     * Adrian, Mar 27, 2017
     * Add tax_amount, flg_tax_amount, tax_id
     */
    INSERT INTO tt_output_data
    (session_id, id_data,data_group_1,data_group_2,
    data_group_3,data_group_4,data_group_5,
    child_group,parent_group,
    sum_measure,min_measure,max_measure,avg_measure,
    sub_total,total,percentage,
    total_measure, percentage_measure,
    tax_amount, flg_tax_amount, tax_id)
    SELECT DISTINCT a.session_id, a.id_data, c.data_group_1, c.data_group_2,
        c.data_group_3, c.data_group_4, c.data_group_5,
        a.child_group, a.parent_group,
        ROUND(a.sum_measure,pDecimalPoint) AS sum_measure,
        ROUND(a.min_measure,pDecimalPoint) AS min_measure,
        ROUND(a.max_measure,pDecimalPoint) AS max_measure,
        ROUND(a.avg_measure,pDecimalPoint) AS avg_measure,
        a.sub_total, a.total,
        b.percentage,
        a.total_measure, d.percentage,
        ROUND(a.tax_amount,pDecimalPoint) AS tax_amount,
        a.flg_tax_amount, a.tax_id
    FROM tt_dashboard_data a,tt_dashboard_data_precision b,tt_group_data c, tt_dashboard_measure_precision d
    WHERE a.session_id = b.session_id AND
        a.child_group = b.child_group AND
        a.id_data = b.id_data AND
        a.session_id = pSessionId AND
        a.id_data = pIdData AND
        a.session_id = c.session_id AND
        a.id_data = c.id_data AND
        a.child_group = c.child_group AND
        a.session_id = d.session_id AND
        a.child_group = d.child_group AND
        a.id_data = d.id_data

    ORDER BY a.child_group;

    DELETE FROM tt_dashboard_data WHERE session_id = pSessionId AND id_data = pIdData;
    DELETE FROM tt_dashboard_data_precision WHERE session_id = pSessionId AND id_data = pIdData;
    DELETE FROM tt_dashboard_measure_precision WHERE session_id = pSessionId AND id_data = pIdData;
    DELETE FROM tt_input_data WHERE session_id = pSessionId AND id_data=pIdData;
    DELETE FROM tt_group_data WHERE session_id = pSessionId AND id_data=pIdData;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /