f_insert_leader_summary_for_report_achievment_vs_target_2018

--Adrian, Mar 12, 2018
--Untuk mendapatkan data parent salesman dalam Mapping Salesman Hierarchy

CREATE OR REPLACE FUNCTION f_insert_leader_summary_for_report_achievment_vs_target(character varying, bigint, bigint, character varying, bigint)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pSalesmanId         ALIAS FOR $3;
    pGroupBrand         ALIAS FOR $4;
    pMaxSalesmanLevel   ALIAS FOR $5;

    vSalesmanLevel      bigint;
    vFilterSalesman     character varying;
    vZero               bigint;
    vEmptyId            bigint;

BEGIN

    vSalesmanLevel := -99;
    vFilterSalesman := '';
    vZero := 0;
    vEmptyId := -99;

    -- Ambil level salesman yang akan dicari atasannya
    SELECT f_get_salesman_level(pSalesmanId) INTO vSalesmanLevel;

    -- Tentukan filter berdasarkan salesman yang akan dicari atasannya
    IF(vSalesmanLevel = 1) THEN
        vFilterSalesman := ' AND A.bod_id = '||pSalesmanId||' ';
    ELSIF (vSalesmanLevel = 2) THEN
        vFilterSalesman := ' AND A.saso_id = '||pSalesmanId||' ';
    ELSIF (vSalesmanLevel = 3) THEN
        vFilterSalesman := ' AND A.sales_manager_id = '||pSalesmanId||' ';
    ELSIF (vSalesmanLevel = 4) THEN
        vFilterSalesman := ' AND A.sales_supervisor_id = '||pSalesmanId||' ';
    ELSIF (vSalesmanLevel = 5) THEN
        vFilterSalesman := ' AND A.salesman_id = '||pSalesmanId||' ';
    ELSIF (vSalesmanLevel = 6) THEN
        vFilterSalesman := ' AND A.salesman_junior_id = '||pSalesmanId||' ';
    END IF;

    -- Ambil data atasan, set amount dan target menjadi 0
    -- Ambil hanya sebelumnya belum ada pada table temporary untuk summary
    -- Ambil hanya yang memiliki level >= level salesman dalam filter report
    EXECUTE '
        WITH data_detail AS (
            SELECT A.salesman_junior_id AS id, A.tenant_id, A.group_brand,
                CASE WHEN f_get_salesman_level(A.salesman_junior_id) <> $6 THEN f_get_salesman_level(A.salesman_junior_id) ELSE 6 END AS level
            FROM tt_salesman_customer_info_for_report_achievement_and_target A
            WHERE A.tenant_id = $1
                AND A.session_id = $2
                AND A.group_brand = $4 '
            || vFilterSalesman || '

            UNION ALL

            SELECT A.salesman_id AS id, A.tenant_id, A.group_brand,
                CASE WHEN f_get_salesman_level(A.salesman_id) <> $6 THEN f_get_salesman_level(A.salesman_id) ELSE 5 END AS level
            FROM tt_salesman_customer_info_for_report_achievement_and_target A
            WHERE A.tenant_id = $1
                AND A.session_id = $2
                AND A.group_brand = $4 '
            || vFilterSalesman || '

            UNION ALL

            SELECT A.sales_supervisor_id AS id, A.tenant_id, A.group_brand,
                CASE WHEN f_get_salesman_level(A.sales_supervisor_id) <> $6 THEN f_get_salesman_level(A.sales_supervisor_id) ELSE 4 END AS level
            FROM tt_salesman_customer_info_for_report_achievement_and_target A
            WHERE A.tenant_id = $1
                AND A.session_id = $2
                AND A.group_brand = $4 '
            || vFilterSalesman || '

            UNION ALL

            SELECT A.sales_manager_id AS id, A.tenant_id, A.group_brand,
                CASE WHEN f_get_salesman_level(A.sales_manager_id) <> $6 THEN f_get_salesman_level(A.sales_manager_id) ELSE 3 END AS level
            FROM tt_salesman_customer_info_for_report_achievement_and_target A
            WHERE A.tenant_id = $1
                AND A.session_id = $2
                AND A.group_brand = $4 '
            || vFilterSalesman || '
        )
        INSERT INTO tt_summary_for_report_achievement_and_target (
            session_id, tenant_id, salesman_id,
            original_target_amount, target_amount, original_year_to_date_accumulative_amount, year_to_date_accumulative_amount,
            salesman_level, group_brand_parent_code,
            salesman_handle_id, salesman_handle_level
        )
        SELECT $2, A.tenant_id, A.id,
            $5, $5, $5, $5,
            A.level, A.group_brand,
            $7, $3
        FROM data_detail A
        WHERE A.level < $3
            AND A.level >= $8
            AND NOT EXISTS(
                SELECT 1
                FROM tt_summary_for_report_achievement_and_target Z
                WHERE Z.session_id = $2
                    AND Z.tenant_id = A.tenant_id
                    AND Z.salesman_id = A.id
                    AND Z.salesman_level = A.level
                    AND Z.group_brand_parent_code = A.group_brand
                    AND Z.salesman_handle_id = $7
                    AND Z.salesman_handle_level = $3
            )
        GROUP BY A.id, A.tenant_id, A.level, A.group_brand'
    USING pTenantId, pSessionId, vSalesmanLevel, pGroupBrand, vZero, vEmptyId, pSalesmanId, pMaxSalesmanLevel;

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