Laporan Penjualan

CREATE OR REPLACE FUNCTION rpt.r_report_sales(character varying, bigint, bigint, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              REFCURSOR := 'refHeader';
    pRefDetail              REFCURSOR := 'refDetail';
    pSessionId              ALIAS FOR $1;
    pRecordOwnerId          ALIAS FOR $2;
    pSalesmanId             ALIAS FOR $3;
    pStartDate              ALIAS FOR $4;
    pEndDate                ALIAS FOR $5;

    vEmpty                  CHARACTER VARYING := '';
    vNo                     CHARACTER VARYING := 'N';
    vYes                    CHARACTER VARYING := 'Y';
    vFilterRecordOwner      character varying := '';
    vFilterSalesman         character varying := '';

    vPosDocTypeId           BIGINT := 390;
    vEmptyId                BIGINT := -99;

BEGIN

    OPEN pRefHeader FOR
        SELECT pStartDate AS date_from, pEndDate AS date_to, f_get_record_owner_name(pRecordOwnerId) AS record_owner_name,
            f_get_salesman_name(pSalesmanId) AS salesman_name;
    RETURN NEXT pRefHeader;

    IF pRecordOwnerId <> vEmptyId THEN
        vFilterRecordOwner := ' AND A.record_owner_id = '''|| pRecordOwnerId ||'''';
    END IF;

    IF pSalesmanId <> vEmptyId THEN
        vFilterSalesman := ' AND A.salesman_id = '''|| pSalesmanId ||'''';
    END IF;

    OPEN pRefDetail FOR
        EXECUTE '
            SELECT A.doc_date, f_get_record_owner_name(A.record_owner_id) AS store_name, A.doc_date, f_get_salesman_name(A.salesman_id) AS salesman_name,
                D.top_days, COALESCE(SUM(B.item_amount - B.item_discount_amount), 0) AS total_amount_sales,
                COALESCE(SUM(C.amount_total), 0) AS hpp_amount,
                COALESCE(SUM(B.item_amount - B.item_discount_amount), 0) - COALESCE(SUM(C.amount_total), 0) AS profit_amount
            FROM trx_pos A
            INNER JOIN trx_pos_item B ON A.pos_id = B.pos_id
            INNER JOIN m_customer D ON A.customer_id = D.customer_id
            LEFT JOIN in_summary_cogs C ON A.record_owner_id = C.record_owner_id
                AND substring(A.doc_date, 1, 6) = C.year_month
                AND C.product_id = B.product_id
            WHERE A.doc_type_id = $1
                AND A.doc_date BETWEEN $2 AND $3
                ' || vFilterRecordOwner || vFilterSalesman || '
            GROUP BY A.doc_date, A.record_owner_id, A.doc_date, D.top_days, A.salesman_id'
        USING vPosDocTypeId, pStartDate, pEndDate;
    RETURN NEXT pRefDetail;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  SET search_path = public, mstr, ptg, htg, pb, pj, batch
  COST 100;
  /