r_daily_sales Spoor Setiawan

-- Function: r_daily_sales(character varying, bigint, bigint, character varying, character varying, character varying, character varying, character varying)

-- DROP FUNCTION r_daily_sales(character varying, bigint, bigint, character varying, character varying, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION r_daily_sales(character varying, bigint, bigint, character varying, character varying, character varying, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              REFCURSOR := 'refHeader';
    pRefDetail              REFCURSOR := 'refDetail';
    pSessionId              ALIAS FOR $1;
    pTenantId               ALIAS FOR $2;
    pOuId                   ALIAS FOR $3;
    pVehicleBrand           ALIAS FOR $4;
    pVehicleType            ALIAS FOR $5;
    pYearMade               ALIAS FOR $6;
    pDateFrom               ALIAS FOR $7;
    pDateTo                 ALIAS FOR $8;

    vEmptyValue             character varying(1);
    vEmptyId                bigint;
    vAllId                  bigint;

    vFilterOuId             text := '';
    vFilterVehicleBrand     text := '';
    vFilterVehicleType      text := '';
    vFilterYearMade         text := '';

    vParentOuId             bigint;
    vYearMonth              character varying(6);
    vDatetime               character varying(14);
    vMaxYearMonth           character varying(6);

BEGIN

    vEmptyValue := '';
    vEmptyId := -99;
    vAllId := -99;

    DELETE FROM tr_daily_sales_report WHERE session_id = pSessionId;
    DELETE FROM tt_in_latest_purchasing_price_by_date WHERE session_id = pSessionId;
    DELETE FROM tt_out_latest_purchasing_price_by_date WHERE session_id = pSessionId;

    vParentOuId := f_get_parent_ou_bu(pTenantId, pOuId);
    SELECT TO_CHAR(now(), 'YYYYMM') INTO vYearMonth;
    SELECT TO_CHAR(now(), 'YYYYMMDDHH24MISS') INTO vDatetime;
    SELECT MAX(year_month_date) INTO vMaxYearMonth FROM in_product_standard_cogs;

    IF pOuId <> vAllId THEN
        vFilterOuId := ' AND A.ou_id = ' || pOuId;
    END IF;

    IF pVehicleBrand <> vEmptyValue THEN
        vFilterVehicleBrand := ' AND D.vehicle_brand = ''' || pVehicleBrand || '''';
    END IF;

    IF pVehicleType <> vEmptyValue THEN
        vFilterVehicleType := ' AND D.vehicle_type = ''' || pVehicleType || '''';
    END IF;

    IF pYearMade <> vEmptyValue THEN
        vFilterYearMade := ' AND D.year_made = ''' || pYearMade '''';
    END IF;

    -- produk non nempil
    EXECUTE '
    WITH summed_i_trx_pos_non_cash_payment AS (
        SELECT A.process_no, A.tenant_id, A.trx_pos_id, SUM(COALESCE(A.payment_amount, 0)) AS payment_amount, COALESCE(A.curr_payment_code, $2) AS curr_payment_code
        FROM i_trx_pos_non_cash_payment A
        WHERE NOT EXISTS (
            SELECT (1)
            FROM m_device_merchant B
            INNER JOIN t_ou C ON B.device_merchant_code = C.ou_code
            WHERE B.device_merchant_id = A.device_merchant_id
        )
        GROUP BY A.process_no, A.tenant_id, A.trx_pos_id, A.curr_payment_code
    ), summed_i_trx_pos_transfer_payment AS (
        SELECT A.process_no, A.tenant_id, A.trx_pos_id, SUM(COALESCE(A.payment_amount, 0)) AS payment_amount, COALESCE(A.curr_payment_code, $2) AS curr_payment_code
        FROM i_trx_pos_non_cash_payment A
        WHERE EXISTS (
            SELECT (1)
            FROM m_device_merchant B
            INNER JOIN t_ou C ON B.device_merchant_code = C.ou_code
            WHERE B.device_merchant_id = A.device_merchant_id
        )
        GROUP BY A.process_no, A.tenant_id, A.trx_pos_id, A.curr_payment_code
    )
    INSERT INTO tr_daily_sales_report (
        session_id, ou_name, year,
        month, date, doc_no, vehicle_brand,
        vehicle_type, year_made, customer_name, product_name,
        ctgr_product_name, qty, curr_code,
        modal_price, gross_sell_price, discount_amount,
        nett_sell_price, curr_payment_code_cash, payment_amount_cash,
        curr_payment_code_non_cash, payment_amount_non_cash,
        curr_payment_code_transfer, payment_amount_transfer,
        curr_under_payment, under_payment_amount,
        tenant_id, ou_id, doc_date, product_id, line_no)
    SELECT  $1, B.ou_name, SUBSTRING(A.doc_date, 1, 4) AS year,
            SUBSTRING(A.doc_date, 5, 2) AS month, SUBSTRING(A.doc_date, 7, 2) AS date, A.doc_no, D.vehicle_brand,
            D.vehicle_type, D.year_made, COALESCE(E.partner_name, ''UMUM'') AS customer_name, f_get_product_name(G.product_id) AS product_name,
            f_get_ctgr_product_name(G.ctgr_product_id) AS ctgr_product_name, F.qty, F.curr_code,
            0, F.gross_sell_price, COALESCE(F.discount_amount, 0) * -1 AS discount_amount,
            F.nett_sell_price, COALESCE(H.curr_payment_code, $2) AS curr_payment_code_cash, CASE WHEN F.line_no = 1 THEN COALESCE(H.payment_amount, 0) ELSE 0 END AS payment_amount_cash,
            I.curr_payment_code AS curr_payment_code_non_cash, CASE WHEN F.line_no = 1 THEN I.payment_amount ELSE 0 END AS payment_amount_non_cash,
            M.curr_payment_code AS curr_payment_code_transfer, CASE WHEN F.line_no = 1 THEN M.payment_amount ELSE 0 END AS payment_amount_transfer,
            COALESCE(J.curr_payment_code, $2) AS curr_under_payment, CASE WHEN F.line_no = 1 THEN COALESCE(j.under_payment_amount, 0) ELSE 0 END AS under_payment_amount,
            A.tenant_id, B.ou_id, A.doc_date, G.product_id, F.line_no
    FROM i_trx_pos A
    INNER JOIN t_ou B on A.ou_id = B.ou_id
    INNER JOIN i_trx_pos_ext C on C.process_no  = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
    INNER JOIN m_vehicle_customer_own D on C.vehicle_code = D.vehicle_code
    LEFT OUTER JOIN m_partner E on C.partner_id = E.partner_id
    INNER JOIN i_trx_pos_item F on A.process_no = F.process_no and A.tenant_id = F.tenant_id and A.trx_pos_id = F.trx_pos_id
    INNER JOIN m_product G on F.product_id = G.product_id
    LEFT OUTER JOIN i_trx_pos_cash_payment H on A.process_no = H.process_no and B.tenant_id = H.tenant_id and A.trx_pos_id = H.trx_pos_id
    LEFT OUTER JOIN summed_i_trx_pos_non_cash_payment I on A.process_no = I.process_no and A.tenant_id = I.tenant_id and A.trx_pos_id = I.trx_pos_id
    LEFT OUTER JOIN summed_i_trx_pos_transfer_payment M on A.process_no = M.process_no and A.tenant_id = M.tenant_id and A.trx_pos_id = M.trx_pos_id
    LEFT OUTER JOIN i_trx_pos_termin_payment J on A.process_no = J.process_no and A.tenant_id = J.tenant_id and A.trx_pos_id = J.trx_pos_id
    LEFT JOIN t_combo_value K ON D.vehicle_brand = K.code AND K.combo_id = ''VEHICLEBRAND''
    LEFT JOIN t_combo_value L ON D.vehicle_type = L.code AND L.combo_id = ''VEHICLETYPE''
    WHERE A.status <> ''V''
        AND A.tenant_id = $3
        AND A.doc_date BETWEEN $4 AND $5 ' ||
        vFilterOuId ||
        vFilterVehicleBrand ||
        vFilterVehicleType ||
        vFilterYearMade
    USING pSessionId, vEmptyValue, pTenantId, pDateFrom, pDateTo;

    ANALYZE tr_daily_sales_report;

    INSERT INTO tt_in_latest_purchasing_price_by_date(session_id, tenant_id, ou_bu_id, doc_date, product_id)
        SELECT pSessionId, pTenantId, B.ou_bu_id, A.doc_date, A.product_id
    FROM tr_daily_sales_report A
        INNER JOIN m_ou_structure B ON B.ou_id = A.ou_id
    WHERE A.tenant_id = pTenantId
        AND A.session_id = pSessionId
    GROUP BY B.ou_bu_id, A.doc_date, A.product_id;

    PERFORM pu_get_latest_purchasing_price_by_date(pSessionId, pTenantId, vYearMonth, B.ou_bu_id, vDatetime, -1)
    FROM tr_daily_sales_report A
    INNER JOIN m_ou_structure B ON B.ou_id = A.ou_id
    GROUP BY B.ou_bu_id;

    ANALYZE tt_out_latest_purchasing_price_by_date;

    UPDATE tr_daily_sales_report B
    SET modal_price = COALESCE(A.gl_purch_gross_price, 0)
    FROM tt_out_latest_purchasing_price_by_date A
    INNER JOIN m_ou_structure C ON C.ou_bu_id = A.ou_bu_id
    WHERE A.session_id = B.session_id
        AND B.product_id = A.product_id
        AND B.tenant_id = A.tenant_id
        AND B.doc_date = A.doc_date
        AND C.ou_id = B.ou_id;

    WITH in_product_standard_cogs_temp AS (
        SELECT D.tenant_id, D.ou_id, MAX(D.year_month_date) AS year_month_date, D.product_id, D.amount
        FROM in_product_standard_cogs D
        INNER JOIN m_ou_structure E ON E.ou_bu_id = D.ou_id
        GROUP BY D.tenant_id, D.ou_id, D.product_id, D.amount
    )
    UPDATE tr_daily_sales_report B
    SET modal_price = COALESCE(A.amount, 0)
    FROM in_product_standard_cogs_temp A
    INNER JOIN m_ou_structure C ON C.ou_bu_id = A.ou_id
    WHERE B.session_id = pSessionId
        AND B.product_id = A.product_id
        AND B.tenant_id = A.tenant_id
        AND A.year_month_date <= SUBSTRING(B.doc_date, 1, 6)
        AND C.ou_id = B.ou_id
        AND B.modal_price = 0;

    -- produk nempil
    /**
     * mod by Didit, 2 des 2016
     * gunakan distinct on, karena ada produk nempil yg kode produk nya sama tp beda di trx_nempil_barang_item_id sehingga
     * produk nempil akan muncul lebih dari satu kali dan menyebabkan perhitungan nett amount akan salah
     */
    EXECUTE '
    WITH summed_i_trx_pos_non_cash_payment AS (
        SELECT A.process_no, A.tenant_id, A.trx_pos_id, SUM(COALESCE(A.payment_amount, 0)) AS payment_amount, COALESCE(A.curr_payment_code, $2) AS curr_payment_code
        FROM i_trx_pos_non_cash_payment A
        WHERE NOT EXISTS (
            SELECT (1)
            FROM m_device_merchant B
            INNER JOIN t_ou C ON B.device_merchant_code = C.ou_code
            WHERE B.device_merchant_id = A.device_merchant_id
        )
        GROUP BY A.process_no, A.tenant_id, A.trx_pos_id, A.curr_payment_code
    ), summed_i_trx_pos_transfer_payment AS (
        SELECT A.process_no, A.tenant_id, A.trx_pos_id, SUM(COALESCE(A.payment_amount, 0)) AS payment_amount, COALESCE(A.curr_payment_code, $2) AS curr_payment_code
        FROM i_trx_pos_non_cash_payment A
        WHERE EXISTS (
            SELECT (1)
            FROM m_device_merchant B
            INNER JOIN t_ou C ON B.device_merchant_code = C.ou_code
            WHERE B.device_merchant_id = A.device_merchant_id
        )
        GROUP BY A.process_no, A.tenant_id, A.trx_pos_id, A.curr_payment_code
    )
    INSERT INTO tr_daily_sales_report (
        session_id, ou_name, year,
        month, date, doc_no, vehicle_brand,
        vehicle_type, year_made, customer_name, product_name,
        ctgr_product_name, qty, curr_code,
        modal_price, gross_sell_price, discount_amount,
        nett_sell_price, curr_payment_code_cash, payment_amount_cash,
        curr_payment_code_non_cash, payment_amount_non_cash,
        curr_payment_code_transfer, payment_amount_transfer,
        curr_under_payment, under_payment_amount,
        tenant_id, ou_id, doc_date, product_id, line_no)
    SELECT $1, B.ou_name, SUBSTRING(A.doc_date, 1, 4) AS year,
            SUBSTRING(A.doc_date, 5, 2) AS month, SUBSTRING(A.doc_date, 7, 2) AS date, A.doc_no, D.vehicle_brand,
            D.vehicle_type, D.year_made, COALESCE(E.partner_name, ''UMUM'') AS customer_name, G.product_name,
            $2 AS ctgr_product_name, F.qty, F.curr_code,
            0 AS modal_price, F.gross_sell_price, 0 AS discount_amount,
            F.gross_sell_price, COALESCE(H.curr_payment_code, $2) AS curr_payment_code_cash, CASE WHEN F.line_no = 1 THEN COALESCE(H.payment_amount, 0) ELSE 0 END AS payment_amount_cash,
            I.curr_payment_code AS curr_payment_code_non_cash, CASE WHEN F.line_no = 1 THEN I.payment_amount ELSE 0 END AS payment_amount_non_cash,
            M.curr_payment_code AS curr_payment_code_transfer, CASE WHEN F.line_no = 1 THEN M.payment_amount ELSE 0 END AS payment_amount_transfer,
            COALESCE(J.curr_payment_code, $2) AS curr_under_payment, CASE WHEN F.line_no = 1 THEN COALESCE(J.under_payment_amount, 0) ELSE 0 END AS under_payment_amount,
            A.tenant_id, B.ou_id, A.doc_date, -99, F.line_no
    FROM i_trx_pos A
    INNER JOIN t_ou B on A.ou_id = B.ou_id
    INNER JOIN i_trx_pos_ext C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
    INNER JOIN m_vehicle_customer_own D on C.vehicle_code = D.vehicle_code
    LEFT OUTER JOIN m_partner E on C.partner_id = E.partner_id
    INNER JOIN i_trx_pos_item_nempil_barang F on A.process_no = F.process_no and A.tenant_id = F.tenant_id and A.trx_pos_id = F.trx_pos_id
    INNER JOIN in_nempil_barang_balance_stock G on F.product_code = G.product_code AND A.ou_id = G.ou_id
    LEFT OUTER JOIN i_trx_pos_cash_payment H on A.process_no = H.process_no and A.tenant_id = H.tenant_id and A.trx_pos_id = H.trx_pos_id
    LEFT OUTER JOIN summed_i_trx_pos_non_cash_payment I on A.process_no = I.process_no and A.tenant_id = I.tenant_id and A.trx_pos_id = I.trx_pos_id
    LEFT OUTER JOIN summed_i_trx_pos_transfer_payment M on A.process_no = M.process_no and A.tenant_id = M.tenant_id and A.trx_pos_id = M.trx_pos_id
    LEFT OUTER JOIN i_trx_pos_termin_payment J on A.process_no = J.process_no and A.tenant_id = J.tenant_id and A.trx_pos_id = J.trx_pos_id
    LEFT JOIN t_combo_value K ON D.vehicle_brand = K.code AND K.combo_id = ''VEHICLEBRAND''
    LEFT JOIN t_combo_value L ON D.vehicle_type = L.code AND L.combo_id = ''VEHICLETYPE''
    WHERE A.status <> ''V''
        AND A.tenant_id = $3
        AND A.doc_date BETWEEN $4 AND $5 ' ||
        vFilterOuId ||
        vFilterVehicleBrand ||
        vFilterVehicleType ||
        vFilterYearMade
    USING pSessionId, vEmptyValue, pTenantId, pDateFrom, pDateTo;

    /**
     * mod by Didit, 2 Des 2016
     * cash, non cash, dan piutang hanya terisi untuk baris yg pertama
     * selain baris pertama di isi 0
     */
    UPDATE tr_daily_sales_report A
    SET payment_amount_cash = 0,
        payment_amount_non_cash = 0,
        under_payment_amount = 0
    WHERE A.product_id = -99
        AND A.line_no = 1
        AND EXISTS(
            SELECT 1 FROM tr_daily_sales_report B WHERE A.doc_no = B.doc_no AND B.product_id <> -99 AND B.line_no = 1
        );

    -- nilai rounding
    EXECUTE 'INSERT INTO tr_daily_sales_report (
        session_id, ou_name, year,
        month, date, doc_no, vehicle_brand,
        vehicle_type, year_made, customer_name, product_name,
        ctgr_product_name, qty, curr_code,
        modal_price, gross_sell_price, discount_amount,
        nett_sell_price, curr_payment_code_cash, payment_amount_cash,
        curr_payment_code_non_cash, payment_amount_non_cash,
        curr_payment_code_transfer, payment_amount_transfer,
        curr_under_payment, under_payment_amount,
        tenant_id, ou_id, doc_date, product_id, line_no)
    SELECT  $1, B.ou_name, SUBSTRING(A.doc_date, 1, 4) AS year,
            SUBSTRING(A.doc_date, 5, 2) AS month, SUBSTRING(A.doc_date, 7, 2) AS date, A.doc_no, D.vehicle_brand,
            D.vehicle_type, D.year_made, COALESCE(E.partner_name, ''UMUM'') AS customer_name, ''ROUNDING'' AS product_name,
            ''ROUNDING'' AS ctgr_product_name, 1, 0,
            0, 0, 0,
            A.rounding_amount, COALESCE(A.curr_code, $2) AS curr_payment_code_cash, CASE WHEN A.rounding_amount < 0 THEN A.rounding_amount ELSE 0 END AS payment_amount_cash,
            COALESCE(A.curr_code, $2) AS curr_payment_code_non_cash, 0 AS payment_amount_non_cash,
            COALESCE(A.curr_code, $2) AS curr_payment_code_transfer, 0 AS payment_amount_transfer,
            COALESCE(A.curr_code, $2) AS curr_under_payment, 0 AS under_payment_amount,
            A.tenant_id, B.ou_id, A.doc_date, -99, -1
    FROM i_trx_pos A
    INNER JOIN t_ou B on A.ou_id = B.ou_id
    INNER JOIN i_trx_pos_ext C on C.process_no  = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
    INNER JOIN m_vehicle_customer_own D on C.vehicle_code = D.vehicle_code
    LEFT OUTER JOIN m_partner E on C.partner_id = E.partner_id
    WHERE A.status <> ''V''
        AND A.tenant_id = $3
        AND A.doc_date BETWEEN $4 AND $5
        AND A.rounding_amount <> 0 ' ||
        vFilterOuId ||
        vFilterVehicleBrand ||
        vFilterVehicleType ||
        vFilterYearMade
    USING pSessionId, vEmptyValue, pTenantId, pDateFrom, pDateTo;

    Open pRefHeader FOR
    SELECT pTenantId;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
    SELECT ou_name, year, month, date, doc_no, vehicle_brand, vehicle_type, year_made, customer_name, product_name, ctgr_product_name, qty,
            curr_code, modal_price, gross_sell_price, discount_amount, nett_sell_price, curr_payment_code_cash, payment_amount_cash,
            curr_payment_code_non_cash, payment_amount_non_cash, curr_payment_code_transfer, payment_amount_transfer, curr_under_payment, under_payment_amount
    FROM tr_daily_sales_report
    WHERE session_id = pSessionId
    ORDER BY ou_name, year, month, date, doc_no, line_no;
    RETURN NEXT pRefDetail;

    DELETE FROM tr_daily_sales_report WHERE session_id = pSessionId;
    DELETE FROM tt_in_latest_purchasing_price_by_date WHERE session_id = pSessionId;
    DELETE FROM tt_out_latest_purchasing_price_by_date WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION r_daily_sales(character varying, bigint, bigint, character varying, character varying, character varying, character varying, character varying)
  OWNER TO sts;