r_report_sales_amount

CREATE OR REPLACE FUNCTION r_report_sales_amount(bigint, bigint, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    vRefHeader          REFCURSOR := 'refHeader';
    vRefDetail          REFCURSOR := 'refDetail';

    pTenantId           ALIAS FOR $1;
    pOuId               ALIAS FOR $2;
    pPeriodFrom         ALIAS FOR $3;
    pPeriodTo           ALIAS FOR $4;

    vReportTitle                        character varying(50) := 'REPORT SALES AMOUNT';
    vReturnSoMlmDocTypeId               bigint := 562;
    vBuybackDocTypeId                   bigint := 563;

BEGIN

OPEN vRefHeader FOR
    SELECT vReportTitle AS report_title, pPeriodFrom AS period_from, pPeriodTo AS period_to;
RETURN NEXT vRefHeader;


OPEN vRefDetail FOR

    WITH summary_rekap AS (
    SELECT A.ref_doc_date,
        SUM(
            CASE WHEN A.ref_doc_type_id = 502 THEN
    --      -1*(A.item_amount)
            -1*(A.item_amount+COALESCE(B.tax_amount,0))
            ELSE
            (A.item_amount+COALESCE(B.tax_amount,0))
    --      (A.item_amount)
            END
        ) AS total_amount
    FROM sl_so_balance_invoice A
    LEFT JOIN   sl_so_balance_invoice_tax B ON A.tenant_id = B.tenant_id
        AND A.ou_id=B.ou_id
        AND A.partner_id =B.partner_id
        AND A.ref_doc_type_id=B.ref_doc_type_id
        AND A.ref_id = B.ref_id
        AND A.ref_item_id=B.ref_item_id
        AND A.do_receipt_item_id=B.do_receipt_item_id
    WHERE A.tenant_id = pTenantId
        AND A.ou_id = pOuId
        AND A.ref_doc_date BETWEEN pPeriodFrom AND pPeriodTo
    GROUP BY A.ref_doc_date
    UNION ALL
    SELECT A.ref_doc_date,
        SUM(
            /*
            CASE WHEN A.ref_doc_type_id IN(562,563) THEN
            (-1*(A.item_amount - (A.item_amount * 0.01 * A.discount_member_percentage) - (A.item_amount * 0.01 * A.discount_member_percentage * 0.01 * A.discount_promo_percentage)))
            /((100+tax_percentage)/100)
            ELSE
            (A.item_amount - (A.item_amount * 0.01 * A.discount_member_percentage) - (A.item_amount * 0.01 * A.discount_member_percentage * 0.01 * A.discount_promo_percentage))
            /((100+tax_percentage)/100)
            END
            */
        CASE WHEN A.ref_doc_type_id IN(562,563) THEN
--          -1*((A.item_amount_member_price)-trunc(A.item_amount_member_price*A.commission_percentage/100))
            -1*((A.item_amount_member_price) - A.commission_amount)
        ELSE
--          ((A.item_amount_member_price)-trunc(A.item_amount_member_price*A.commission_percentage/100))
            ((A.item_amount_member_price) - A.commission_amount)
        END
        ) AS total_amount
    FROM sl_so_mlm_balance_invoice A
    WHERE A.tenant_id = pTenantId
        AND A.ou_id = pOuId
        AND A.ref_doc_date BETWEEN pPeriodFrom AND pPeriodTo
    GROUP BY A.ref_doc_date
    )
    SELECT A.ref_doc_date AS tanggal, SUM(A.total_amount) AS jumlah_sales
    FROM summary_rekap A
    WHERE A.ref_doc_date BETWEEN pPeriodFrom AND pPeriodTo
    GROUP BY A.ref_doc_date
    ORDER BY A.ref_doc_date;

RETURN NEXT vRefDetail;

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