CREATE OR REPLACE FUNCTION r_analysis_sales_gross_profit_by_invoice_with_monthly_avg_cogs(character varying, bigint, character varying, bigint, character varying, character varying, character varying, bigint, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              REFCURSOR := 'refHeader';
    pRefDetail              REFCURSOR := 'refDetail';
    pSessionId              ALIAS FOR $1;
    pTenantId               ALIAS FOR $2;
    pYearMonthDate          ALIAS FOR $3;
    pUserId                 ALIAS FOR $4;
    pPartnerCodeName        ALIAS FOR $5;
    pTglAwal                ALIAS FOR $6;
    pTglAkhir               ALIAS FOR $7;
    pOuId                   ALIAS FOR $8;
    pDatetime               ALIAS FOR $9;

    vAwalTahun              character varying(6) := '';
    vValutaBuku             character varying(5) := '';
    vEmptyValue             character varying(1) := '';
    vFlagYes                character varying(1) := 'Y';
    vRoundingModeUp         character varying(2) := 'RU';

    vDocTypeIdSI                bigint := 321;
    vDocTypeIdSIShopInShop      bigint := 341;
    vDocTypeIdRSI               bigint := 331;
    vDocTypeIdCNAR              bigint := 251;
    vDocTypeIdDNAR              bigint := 241;
    vPartnerIdMagento           bigint := -99;
    vZero                       integer := 0;
    vIsFilterTglTrue            integer := 1;

BEGIN

    vAwalTahun := SUBSTRING(pYearMonthDate, 1, 4) || '01';

    SELECT f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku') INTO vValutaBuku;
    SELECT partner_id INTO vPartnerIdMagento FROM m_partner WHERE partner_code = 'MAGENTO';

    RAISE NOTICE 'vPartnerIdMagento : %',vPartnerIdMagento;

    -- Check filter tanggal
    IF(pTglAwal=vEmptyValue OR pTglAkhir=vEmptyValue) THEN
        vIsFilterTglTrue := 0;
    END IF;

    -- RESET Temp folder
    DELETE FROM tt_sales_gross_profit_by_invoice WHERE session_id = pSessionId;

    Open pRefHeader FOR
    SELECT A.fullname AS username, B.ou_name AS ou_name, pYearMonthDate AS current_month_year, vAwalTahun AS start_month_year, pYearMonthDate AS end_month_year,
    vValutaBuku AS valuta_buku, pDatetime AS datetime
    FROM t_user A
    INNER JOIN t_ou B ON A.tenant_id = B.tenant_id
    WHERE user_id = pUserId
    AND ou_id = pOuId;

    RETURN NEXT pRefHeader;

    /*
     * Mengupulkan semua product dengan harga belinya, dari beberapa doc sbb :
     * - SI
     * - SI Shop In Shop
     * - RSI
     */

    -- #1. Ambil data sales invoice item untuk period awal tahun sampai period yang terpilih
    INSERT INTO tt_sales_gross_profit_by_invoice(
        session_id, tenant_id, ou_bu_id, partner_id,
        partner_code, partner_name,
        doc_type_id, doc_no, doc_date, year_month_date, product_id, curr_code,
        purch_price)
    SELECT pSessionId, A.tenant_id, E.ou_bu_id, A.partner_id,
            f_get_partner_code(A.partner_id) AS partner_code, f_get_partner_name(A.partner_id) AS partner_name,
            A.doc_type_id, A.doc_no, A.doc_date, F.year_month_date, D.product_id, A.curr_code,
            (CASE WHEN COALESCE(G.qty_total, vZero) = vZero OR COALESCE(G.amount_total, vZero) = vZero
                THEN vZero
                ELSE COALESCE(G.amount_total/G.qty_total, vZero)
            END)
    FROM fi_invoice_ar_balance A
    INNER JOIN sl_invoice B ON A.tenant_id = B.tenant_id AND A.invoice_ar_id = B.invoice_id AND A.doc_type_id = B.doc_type_id
    INNER JOIN sl_invoice_item C ON B.invoice_id = C.invoice_id
    INNER JOIN sl_do_item D ON C.ref_item_id = D.do_item_id AND C.ref_id = D.do_id
    INNER JOIN m_ou_structure E ON A.ou_id = E.ou_id
    INNER JOIN dt_date F ON A.doc_date = F.string_date
    LEFT JOIN in_summary_monthly_cogs G ON A.tenant_id = G.tenant_id AND D.product_id = G.product_id AND F.year_month_date = G.date_year_month AND E.ou_bu_id = G.ou_id
    WHERE A.doc_type_id = vDocTypeIdSI
    AND F.year_month_date BETWEEN vAwalTahun AND pYearMonthDate
    AND ((A.doc_date BETWEEN pTglAwal AND pTglAkhir) OR vIsFilterTglTrue = vZero)
    AND (UPPER(f_get_partner_code(A.partner_id)) LIKE UPPER('%'||pPartnerCodeName||'%')
        OR UPPER(f_get_partner_name(A.partner_id)) LIKE UPPER('%'||pPartnerCodeName||'%')
        OR pPartnerCodeName = vEmptyValue);

    -- #2. Ambil data sales invoice shop in shop item untuk period awal tahun sampai period yang terpilih
    INSERT INTO tt_sales_gross_profit_by_invoice(
        session_id, tenant_id, ou_bu_id, partner_id,
        partner_code, partner_name,
        doc_type_id, doc_no, doc_date, year_month_date, product_id, curr_code,
        purch_price)
    SELECT pSessionId, A.tenant_id, F.ou_bu_id, A.partner_id,
            f_get_partner_code(A.partner_id) AS partner_code, f_get_partner_name(A.partner_id) AS partner_name,
            A.doc_type_id, A.doc_no, A.doc_date, G.year_month_date, E.product_id, A.curr_code,
            (CASE WHEN COALESCE(H.qty_total, vZero) = vZero OR COALESCE(H.amount_total, vZero) = vZero
                THEN vZero
                ELSE COALESCE(H.amount_total/H.qty_total, vZero)
            END)
    FROM fi_invoice_ar_balance A
    INNER JOIN sl_invoice_shop_in_shop B ON A.tenant_id = B.tenant_id AND A.invoice_ar_id = B.invoice_shop_in_shop_id AND A.doc_type_id = B.doc_type_id
    INNER JOIN sl_invoice_shop_in_shop_item C ON B.invoice_shop_in_shop_id = C.invoice_shop_in_shop_id
    INNER JOIN sl_pos_balance_invoice D ON C.ref_id = D.pos_balance_invoice_id
    INNER JOIN sl_pos_balance_invoice_item E ON D.pos_balance_invoice_id = E.pos_balance_invoice_id
    INNER JOIN m_ou_structure F ON A.ou_id = F.ou_id
    INNER JOIN dt_date G ON A.doc_date = G.string_date
    LEFT JOIN in_summary_monthly_cogs H ON A.tenant_id = H.tenant_id AND E.product_id = H.product_id AND G.year_month_date = H.date_year_month AND F.ou_bu_id = H.ou_id
    WHERE A.doc_type_id = vDocTypeIdSIShopInShop
    AND G.year_month_date BETWEEN vAwalTahun AND pYearMonthDate
    AND ((A.doc_date BETWEEN pTglAwal AND pTglAkhir) OR vIsFilterTglTrue = vZero)
    AND (UPPER(f_get_partner_code(A.partner_id)) LIKE UPPER('%'||pPartnerCodeName||'%')
        OR UPPER(f_get_partner_name(A.partner_id)) LIKE UPPER('%'||pPartnerCodeName||'%')
        OR pPartnerCodeName = vEmptyValue);

    -- #3. Ambil data retur sales invoice item untuk period awal tahun sampai period yang terpilih
    INSERT INTO tt_sales_gross_profit_by_invoice(
        session_id, tenant_id, ou_bu_id, partner_id,
        partner_code, partner_name,
        doc_type_id, doc_no, doc_date, year_month_date, product_id, curr_code,
        purch_price)
    SELECT pSessionId, A.tenant_id, E.ou_bu_id, A.partner_id,
            f_get_partner_code(A.partner_id) AS partner_code, f_get_partner_name(A.partner_id) AS partner_name,
            A.doc_type_id, A.doc_no, A.doc_date, F.year_month_date, D.product_id, A.curr_code,
            (CASE WHEN COALESCE(G.qty_total, vZero) = vZero OR COALESCE(G.amount_total, vZero) = vZero
                THEN vZero
                ELSE COALESCE(G.amount_total/G.qty_total, vZero)
            END)
    FROM fi_invoice_ar_balance A
    INNER JOIN sl_invoice B ON A.tenant_id = B.tenant_id AND A.invoice_ar_id = B.invoice_id AND A.doc_type_id = B.doc_type_id
    INNER JOIN sl_invoice_item C ON B.invoice_id = C.invoice_id
    INNER JOIN in_inventory_item D ON C.ref_item_id = D.ref_item_id AND C.ref_id = D.inventory_id
    INNER JOIN m_ou_structure E ON A.ou_id = E.ou_id
    INNER JOIN dt_date F ON A.doc_date = F.string_date
    LEFT JOIN in_summary_monthly_cogs G ON A.tenant_id = G.tenant_id AND D.product_id = G.product_id AND F.year_month_date = G.date_year_month AND E.ou_bu_id = G.ou_id
    WHERE A.doc_type_id = vDocTypeIdRSI
    AND ((A.doc_date BETWEEN pTglAwal AND pTglAkhir) OR vIsFilterTglTrue = vZero)
    AND F.year_month_date BETWEEN vAwalTahun AND pYearMonthDate
    AND (UPPER(f_get_partner_code(A.partner_id)) LIKE UPPER('%'||pPartnerCodeName||'%')
        OR UPPER(f_get_partner_name(A.partner_id)) LIKE UPPER('%'||pPartnerCodeName||'%')
        OR pPartnerCodeName = vEmptyValue);

    Open pRefDetail FOR

        -- Data purch price per partner untuk periode terpilih
        WITH summary_purch_price_per_partner_selected_period AS (

            SELECT A.partner_id, A.partner_code, A.partner_name, SUM(A.purch_price) AS purch_price
            FROM tt_sales_gross_profit_by_invoice A
            WHERE A.session_id = pSessionId
            AND A.year_month_date = pYearMonthDate
            GROUP BY A.partner_id, A.partner_code, A.partner_name

        ),
        -- Data purch price per partner dari awal tahun (januari) sampai periode terpilih
        summary_purch_price_per_partner_all_period AS (

            SELECT A.partner_id, A.partner_code, A.partner_name, SUM(A.purch_price) AS purch_price
            FROM tt_sales_gross_profit_by_invoice A
            WHERE A.session_id = pSessionId
            GROUP BY A.partner_id, A.partner_code, A.partner_name

        ),
        -- Data CN DN AR per partner untuk periode terpilih
        data_cn_dn_ar_partner_selected_period AS (
            SELECT A.partner_id,
                    CASE WHEN A.doc_type_id = vDocTypeIdCNAR
                        THEN SUM(D.add_amount*f_commercial_rate(C.tenant_id,C.doc_date,D.curr_code, vValutaBuku)) * -1
                        ELSE SUM(D.add_amount*f_commercial_rate(C.tenant_id,C.doc_date,D.curr_code, vValutaBuku))
                    END AS current_sales
            FROM fi_invoice_ar_balance A
            INNER JOIN dt_date B ON A.doc_date = B.string_date
            INNER JOIN fi_invoice_ar C ON A.invoice_ar_id = C.invoice_ar_id AND A.doc_type_id = C.doc_type_id
            INNER JOIN fi_invoice_ar_cost D ON C.invoice_ar_id = D.invoice_ar_id
            INNER JOIN m_activity_gl E ON D.activity_gl_id = E.activity_gl_id
            INNER JOIN m_coa F ON E.coa_id = F.coa_id
            INNER JOIN m_group_coa G ON F.group_coa_id = G.group_coa_id
            WHERE A.tenant_id = pTenantId
            AND A.doc_type_id IN (vDocTypeIdCNAR, vDocTypeIdDNAR)
            AND G.group_coa_code IN ('PendapatanPenjualan', 'ReturPenjualan')
            AND B.year_month_date = pYearMonthDate
            AND ((A.doc_date BETWEEN pTglAwal AND pTglAkhir) OR vIsFilterTglTrue = vZero)
            AND (UPPER(f_get_partner_code(A.partner_id)) LIKE UPPER('%'||pPartnerCodeName||'%')
                OR UPPER(f_get_partner_name(A.partner_id)) LIKE UPPER('%'||pPartnerCodeName||'%')
                OR pPartnerCodeName = vEmptyValue)
            GROUP BY A.partner_id, A.doc_type_id
        ),
        -- Data CN DN AR per partner dari awal tahun (januari) sampai periode terpilih
        data_cn_dn_ar_partner_all_period AS (
            SELECT A.partner_id,
                    CASE WHEN A.doc_type_id = vDocTypeIdCNAR
                        THEN SUM(D.add_amount*f_commercial_rate(C.tenant_id,C.doc_date,D.curr_code, vValutaBuku)) * -1
                        ELSE SUM(D.add_amount*f_commercial_rate(C.tenant_id,C.doc_date,D.curr_code, vValutaBuku))
                    END AS before_sales
            FROM fi_invoice_ar_balance A
            INNER JOIN dt_date B ON A.doc_date = B.string_date
            INNER JOIN fi_invoice_ar C ON A.invoice_ar_id = C.invoice_ar_id AND A.doc_type_id = C.doc_type_id
            INNER JOIN fi_invoice_ar_cost D ON C.invoice_ar_id = D.invoice_ar_id
            INNER JOIN m_activity_gl E ON D.activity_gl_id = E.activity_gl_id
            INNER JOIN m_coa F ON E.coa_id = F.coa_id
            INNER JOIN m_group_coa G ON F.group_coa_id = G.group_coa_id
            WHERE A.tenant_id = pTenantId
            AND A.doc_type_id IN (vDocTypeIdCNAR, vDocTypeIdDNAR)
            AND G.group_coa_code IN ('PendapatanPenjualan', 'ReturPenjualan')
            AND B.year_month_date BETWEEN vAwalTahun AND pYearMonthDate
            AND ((A.doc_date BETWEEN pTglAwal AND pTglAkhir) OR vIsFilterTglTrue = vZero)
            AND (UPPER(f_get_partner_code(A.partner_id)) LIKE UPPER('%'||pPartnerCodeName||'%')
                OR UPPER(f_get_partner_name(A.partner_id)) LIKE UPPER('%'||pPartnerCodeName||'%')
                OR pPartnerCodeName = vEmptyValue)
            GROUP BY A.partner_id, A.doc_type_id
        ),
        -- Data sales per partner untuk periode terpilih
        data_sales_per_partner_selected_period AS (
            SELECT A.partner_id,
                SUM(CASE WHEN A.doc_type_id = vDocTypeIdSI AND A.partner_id = vPartnerIdMagento
                    THEN
                        f_get_amount_before_tax(A.amount, vFlagYes, D.tax_percentage, vZero, vRoundingModeUp)*f_commercial_rate(A.tenant_id,A.doc_date,A.curr_code, vValutaBuku)
                    ELSE
                        A.amount*f_commercial_rate(A.tenant_id,A.doc_date,A.curr_code, vValutaBuku)
                END)+COALESCE(C.current_sales,0) AS current_sales
            FROM fi_invoice_ar_balance A
            INNER JOIN dt_date B ON A.doc_date = B.string_date
            LEFT JOIN data_cn_dn_ar_partner_selected_period C ON A.partner_id = C.partner_id
            LEFT JOIN sl_invoice_tax D ON A.invoice_ar_id = D.invoice_id AND A.doc_type_id = vDocTypeIdSI AND A.partner_id = vPartnerIdMagento
            WHERE A.tenant_id = pTenantId
            AND A.doc_type_id IN (vDocTypeIdSI, vDocTypeIdSIShopInShop, vDocTypeIdRSI)
            AND B.year_month_date = pYearMonthDate
            AND ((A.doc_date BETWEEN pTglAwal AND pTglAkhir) OR vIsFilterTglTrue = vZero)
            AND (UPPER(f_get_partner_code(A.partner_id)) LIKE UPPER('%'||pPartnerCodeName||'%')
                OR UPPER(f_get_partner_name(A.partner_id)) LIKE UPPER('%'||pPartnerCodeName||'%')
                OR pPartnerCodeName = vEmptyValue)
            GROUP BY A.partner_id, C.current_sales
        ),
        -- Data sales per partner dari awal tahun (januari) sampai periode terpilih
        data_sales_per_partner_all_period AS (
            SELECT A.partner_id,
                   SUM(CASE WHEN A.doc_type_id = vDocTypeIdSI AND A.partner_id = vPartnerIdMagento
                        THEN
                            f_get_amount_before_tax(A.amount, vFlagYes, D.tax_percentage, vZero, vRoundingModeUp)*f_commercial_rate(A.tenant_id,A.doc_date,A.curr_code, vValutaBuku)
                        ELSE
                            A.amount*f_commercial_rate(A.tenant_id,A.doc_date,A.curr_code, vValutaBuku)
                        END)+COALESCE(C.before_sales,0) AS before_sales
            FROM fi_invoice_ar_balance A
            INNER JOIN dt_date B ON A.doc_date = B.string_date
            LEFT JOIN data_cn_dn_ar_partner_all_period C ON A.partner_id = C.partner_id
            LEFT JOIN sl_invoice_tax D ON A.invoice_ar_id = D.invoice_id AND A.doc_type_id = vDocTypeIdSI AND A.partner_id = vPartnerIdMagento
            WHERE A.tenant_id = pTenantId
            AND A.doc_type_id IN (vDocTypeIdSI, vDocTypeIdSIShopInShop, vDocTypeIdRSI)
            AND B.year_month_date BETWEEN vAwalTahun AND pYearMonthDate
            AND ((A.doc_date BETWEEN pTglAwal AND pTglAkhir) OR vIsFilterTglTrue = vZero)
            AND (UPPER(f_get_partner_code(A.partner_id)) LIKE UPPER('%'||pPartnerCodeName||'%')
                OR UPPER(f_get_partner_name(A.partner_id)) LIKE UPPER('%'||pPartnerCodeName||'%')
                OR pPartnerCodeName = vEmptyValue)
            GROUP BY A.partner_id, C.before_sales

        ),
        -- Data profit untuk periode terpilih
        data_profit_selected_period AS(
            SELECT A.partner_id, A.partner_code, A.partner_name, vValutaBuku AS curr_code,
                B.current_sales, B.current_sales-A.purch_price AS current_gross_profit,
                CASE WHEN B.current_sales = vZero THEN vZero
                    ELSE COALESCE ((B.current_sales-A.purch_price)/B.current_sales, vZero)
                END AS current_profit_percentage
            FROM summary_purch_price_per_partner_selected_period A
            INNER JOIN data_sales_per_partner_selected_period B ON A.partner_id = B.partner_id
        ),
        -- Data profit untuk periode terpilih dari awal tahun (januari) sampai periode terpilih
        data_profit_all_period AS(
            SELECT A.partner_id, A.partner_code, A.partner_name, vValutaBuku AS curr_code,
                B.before_sales, B.before_sales-A.purch_price AS before_gross_profit,
                CASE WHEN B.before_sales = vZero THEN vZero
                    ELSE COALESCE ((B.before_sales-A.purch_price)/B.before_sales, vZero)
                END AS before_profit_percentage
            FROM summary_purch_price_per_partner_all_period A
            INNER JOIN data_sales_per_partner_all_period B ON A.partner_id = B.partner_id
        )

        SELECT A.partner_id, A.partner_code, A.partner_name, A.curr_code,
                A.current_sales, A.current_gross_profit, A.current_profit_percentage,
                B.before_sales, B.before_gross_profit, B.before_profit_percentage
        FROM data_profit_selected_period A
        INNER JOIN data_profit_all_period B ON A.partner_id = B.partner_id
        ORDER BY A.partner_name;

    RETURN NEXT pRefDetail;

    DELETE FROM tt_sales_gross_profit_by_invoice WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;