--Fitra 25 Agustus 2017
--2017-12-15 fitra, menambahkan kodisi tidak sedang / sudah finalisasi diquery mendapatkan data RGTO
CREATE OR REPLACE FUNCTION r_report_rekap_saldo_stock_nasional(BIGINT, BIGINT, CHARACTER VARYING, CHARACTER VARYING)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              REFCURSOR := 'refHeader';
    pRefDetail              REFCURSOR := 'refDetail';
    pTenantId           ALIAS FOR $1;
    pReportMessageId    ALIAS FOR $2;
    pSessionId          ALIAS FOR $3;
    pProcessNo          ALIAS FOR $4;

    vOuId                   bigint;
    vCtgrProductId          bigint;
    vSubCtgrProductId       bigint;

    vGolonganProduct        character varying;
    vProductCodeName        character varying;
    vFlgActive              character varying;

    vOuBuInfoReport         OU_INFO_REPORT%ROWTYPE;
    result                  record;
    vFilterCtgrProduct      character varying= '';
    vFilterSubCtgrProduct   character varying= '';
    vFilterGolonganProduct  character varying= '';
    vFilterProductCodeName  character varying= '';
    vFilterActive           character varying= '';
    vEmptyId                bigint := -99;
    vEmptyString            character varying := '';
    vFlgStock               character varying := 'STOCK';
    vFlgReserved                character varying := 'RSVD';
    vFlgRgto                character varying := 'RGTO';
    vFlgBuffer              character varying := 'BUFFER';
    vFlgRekap               character varying := 'REKAP';
    vAll                    character varying := 'ALL';
    vNo                     character varying := 'N';
    vYes                    character varying := 'Y';
    vInProgress             character varying := 'I';
    vflgFinalRgto               character varying := 'V';
    vRgtoDocId                  bigint  := 538;
    vGtoDocId                   bigint  := 533;
    vFinalizationRgtoDocId      bigint  := 589;
    vWarehouse              RECORD;
    vValueColoumNameStock               text[];
    vValueColoumNameRsvd                text[];
    vValueColoumNameRgto                text[];
    vValueColoumNameBuffer              text[];
    vValueColoumNameAll                 text[];

    vColoumNameStock                text := '';
    vColoumNameRsvd                 text := '';
    vColoumNameRgto                 text := '';
    vColoumNameBuffer               text := '';
    vColoumNameForHeader            text := '';


    vColoumNameTotalStock   text := 'TOTAL NASIONAL';
    vColoumNameTotalBuffer  text := 'BUFFER NASIONAL';
    vJumlahKolom            bigint := 0;
    vCounter                bigint := 0;

BEGIN
    --ambil parameter
    vOuId   := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'ouId') AS bigint);
    vCtgrProductId  := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'ctgrProductId') AS bigint);
    vSubCtgrProductId := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'subCtgrProductId') AS bigint);
    vGolonganProduct := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'golonganProduct') AS character varying);
    vProductCodeName := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'productCodeName') AS character varying);
    vFlgActive := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'flgActive') AS character varying);


    raise notice 'vOuId: % vCtgrProductId: % vSubCtgrProductId: % vGolonganProduct: % vProductCodeName: % vFlgActive: %',
    vOuId,vCtgrProductId,vSubCtgrProductId,vGolonganProduct,vProductCodeName,vFlgActive;

    --fillter terhadap ctgr product
    IF vCtgrProductId != vEmptyId THEN
        vFilterCtgrProduct = ' A.ctgr_product_id = '||vCtgrProductId ||' AND ';
    END IF;

    --fillter terhadap sub ctgr product
    IF vSubCtgrProductId != vEmptyId THEN
        vFilterSubCtgrProduct = ' A.sub_ctgr_product_id = '||vSubCtgrProductId ||' AND ';
    END IF;

    --fillter terhadap golongan
    IF vGolonganProduct != vEmptyString THEN
        vFilterGolonganProduct = ' C.style_product = '''||vGolonganProduct||''' AND ';
    END IF;

    --fillter terhadap product code name
    IF vProductCodeName != vEmptyString THEN
        vFilterProductCodeName = ' (UPPER(A.product_code) LIKE UPPER('''||'%'||vProductCodeName||'%'||''') OR UPPER(A.product_name) LIKE UPPER('''||'%'||vProductCodeName||'%'||''')) AND ';
    END IF;
    IF vFlgActive != vAll THEN
        vFilterActive = ' A.active = '''||vFlgActive||''' AND ';
    END IF;

    DELETE FROM tt_product_saldo_stock_nasional WHERE session_id = pSessionId;
    DELETE FROM tt_detail_saldo_stock_nasional WHERE session_id = pSessionId;

    --1. Ambil data product yg memenuhi filter yg diinput user.
    EXECUTE '
    INSERT INTO tt_product_saldo_stock_nasional(
            session_id, product_id, product_code, product_name,
            ctgr_product_id, sub_ctgr_product_id, style_product, product_active)
    SELECT $1, A.product_id, A.product_code,A.product_name,
           A.ctgr_product_id, A.sub_ctgr_product_id, C.style_product, A.active
    FROM m_product A
    INNER JOIN m_product_custom C ON A.product_id = C.product_id
    WHERE '||
          vFilterCtgrProduct ||
          vFilterSubCtgrProduct ||
          vFilterGolonganProduct ||
          vFilterProductCodeName||
          vFilterActive ||
          ' A.tenant_id = $2 '
    USING pSessionId, pTenantId;

    --2. Ambil dari data saldo stock (in_product_balance_stock)
    --   yang terdaftar di tt_product_saldo_stock_nasional
    --   dan ada stok nya (qty > 0)
    INSERT INTO tt_detail_saldo_stock_nasional(
            session_id, flg_data, product_id, warehouse_id,
            qty_stock, qty_reserved, qty_rgto, qty_buffer)
    SELECT pSessionId, vFlgStock, A.product_id, A.warehouse_id,
           SUM(A.qty), 0, 0, 0
    FROM in_product_balance_stock A
    INNER JOIN tt_product_saldo_stock_nasional B ON A.product_id = B.product_id
    INNER JOIN m_warehouse_ou C ON A.warehouse_id = C.warehouse_id
    WHERE A.tenant_id = pTenantId AND
          (f_get_ou_bu_structure(C.ou_id)).ou_bu_id = vOuId AND
          A.qty > 0 AND
          B.session_id = pSessionId
    GROUP BY A.product_id, A.warehouse_id;

    --3. Ambil data saldo reserved stok (in_product_balance_stock_reserved)
    --   yang terdaftar di tt_product_saldo_stock_nasional
    --   dan ada stok nya (qty > 0)
    INSERT INTO tt_detail_saldo_stock_nasional(
            session_id, flg_data, product_id, warehouse_id,
            qty_stock, qty_reserved, qty_rgto, qty_buffer)
    SELECT pSessionId, vFlgReserved, A.product_id, A.warehouse_id,
           0, A.qty, 0, 0
    FROM in_product_balance_stock_reserved A
    INNER JOIN tt_product_saldo_stock_nasional B ON A.product_id = B.product_id
    INNER JOIN m_warehouse_ou C ON A.warehouse_id = C.warehouse_id
    WHERE A.tenant_id = pTenantId AND
          (f_get_ou_bu_structure(C.ou_id)).ou_bu_id = vOuId AND
          A.qty > 0 AND
          B.session_id = pSessionId;


    --4. Ambil data RGTO yang masih outstanding
    -- 2017-12-15 fitra, menambahkan kondisi tidak sedang / sudah finalisasi
    INSERT INTO tt_detail_saldo_stock_nasional(
            session_id, flg_data, product_id, warehouse_id,
            qty_stock, qty_reserved, qty_rgto, qty_buffer)
    SELECT pSessionId, vFlgRgto, C.product_id, A.warehouse_id,
           0, 0, COALESCE(F.qty_req_int- F.qty_rcv_int,C.qty_request), 0
    FROM in_balance_req_transfer_out A
    INNER JOIN in_inventory_item C ON A.inventory_id = C.inventory_id
    INNER JOIN tt_product_saldo_stock_nasional B ON C.product_id = B.product_id
    LEFT JOIN in_req_trf_out_po_balance_item F ON F.inventory_item_id = C.inventory_item_id
    WHERE A.tenant_id = pTenantId AND
          A.flg_real NOT IN (vYes,vflgFinalRgto) AND
          (f_get_ou_bu_structure(A.ou_id)).ou_bu_id = vOuId AND
          B.session_id = pSessionId AND
          NOT EXISTS ( SELECT 1
                       FROM in_inventory D
                       INNER JOIN in_inventory_item E ON E.inventory_id = D.inventory_id AND E.ref_id = C.inventory_item_id AND E.ref_doc_type_id = vRgtoDocId
                       WHERE D.ref_id = A.inventory_id AND
                             D.ref_doc_type_id = vRgtoDocId AND
                             D.doc_type_id = vGtoDocId) AND
          NOT EXISTS( SELECT 1
                      FROM in_inventory E
                      WHERE E.ref_id = A.inventory_id AND
                            E.ref_doc_type_id = vRgtoDocId AND
                            E.doc_type_id = vFinalizationRgtoDocId );

    --5. Ambil data qty buffer stock (m_warehouse_buffer_stock) yg ada qty buffer
    --   (qty_buffer > 0)
    INSERT INTO tt_detail_saldo_stock_nasional(
            session_id, flg_data, product_id, warehouse_id,
            qty_stock, qty_reserved, qty_rgto, qty_buffer)
    SELECT pSessionId, vFlgBuffer, A.product_id, A.warehouse_id,
           0, 0, 0, A.qty_buffer
    FROM m_warehouse_buffer_stock A
    INNER JOIN tt_product_saldo_stock_nasional B ON A.product_id = B.product_id
    WHERE A.tenant_id = pTenantId AND
          (f_get_ou_bu_structure(A.ou_id)).ou_bu_id = vOuId AND
          A.active = vYes AND
          A.qty_buffer > 0 AND
          B.session_id = pSessionId;

    --6. Ambil data detail saldo stok nasional, rekap berdasarkan session, product,
    --   dan warehouse. Tulis hasil rekap ke table temp dan product tt_product_saldo_stock_nasional yang tidak ada di tt_detail_saldo_stock_nasional
    INSERT INTO tt_detail_saldo_stock_nasional(
            session_id, flg_data, product_id, warehouse_id,
            qty_stock, qty_reserved, qty_rgto, qty_buffer)
     SELECT pSessionId, vFlgRekap, A.product_id, A.warehouse_id,
           SUM(qty_stock), SUM(qty_reserved), SUM(qty_rgto), SUM(qty_buffer)
    FROM tt_detail_saldo_stock_nasional A
    WHERE A.session_id = pSessionId
    GROUP BY A.session_id, A.product_id, A.warehouse_id;

    --untuk product yang tidak di warehouse maka diseting 0 untuk qty nya
    WITH warehouse AS (select warehouse_id
                       from tt_detail_saldo_stock_nasional
                       WHERE session_id = pSessionId AND flg_data = vFlgRekap
                       group by warehouse_id )
    INSERT INTO tt_detail_saldo_stock_nasional(
            session_id, flg_data, product_id, warehouse_id,
            qty_stock, qty_reserved, qty_rgto, qty_buffer)
     SELECT pSessionId, vFlgRekap, A.product_id, B.warehouse_id,
           0, 0, 0, 0
    FROM tt_product_saldo_stock_nasional A, warehouse B
    WHERE A.session_id = pSessionId AND
          NOT EXISTS (
            SELECT 1 from tt_detail_saldo_stock_nasional C
            WHERE A.session_id = C.session_id AND
                  C.flg_data = vFlgRekap AND
                  A.product_id = C.product_id AND
                  B.warehouse_id = C.warehouse_id
          );
    -- looping untuk menentukan nama kolom yang dinamis berdasarkan Qty Stock, Qty Reserved,
    -- Qty RGTO, Qty Buffer
    FOR vWarehouse IN SELECT f_get_warehouse_name(warehouse_id) as warehouse_name
                      FROM tt_detail_saldo_stock_nasional
                      WHERE session_id = pSessionId
                      GROUP BY warehouse_id
                      ORDER BY warehouse_id LOOP
        vValueColoumNameStock := array_append(vValueColoumNameStock,CONCAT('Qty Stock ',vWarehouse.warehouse_name));
        vValueColoumNameRsvd := array_append(vValueColoumNameRsvd,CONCAT('Qty Reserved ',vWarehouse.warehouse_name));
        vValueColoumNameRgto := array_append(vValueColoumNameRgto,CONCAT('Qty RGTO ',vWarehouse.warehouse_name));
        vValueColoumNameBuffer := array_append(vValueColoumNameBuffer,CONCAT('Qty Buffer ',vWarehouse.warehouse_name));

        vColoumNameStock := CONCAT(vColoumNameStock,',','"Qty_Stock_',vWarehouse.warehouse_name,'" numeric');
        vColoumNameRsvd := CONCAT(vColoumNameRsvd,',','"Qty_Reserved_',vWarehouse.warehouse_name,'" numeric');
        vColoumNameRgto := CONCAT(vColoumNameRgto,',','"Qty_RGTO_',vWarehouse.warehouse_name,'" numeric');
        vColoumNameBuffer := CONCAT(vColoumNameBuffer,',','"Qty_Buffer_',vWarehouse.warehouse_name,'" numeric');
    END LOOP;
    --vColoumNameStock := CONCAT(vColoumNameStock,',"',vColoumNameTotalStock,'" numeric');
    --vColoumNameBuffer := CONCAT(vColoumNameBuffer,',"',vColoumNameTotalBuffer,'" numeric');

    --penjagaan jika tidak ada item yang ditemukan makan hanya menampilkan header product code dan product name
    IF NOT EXISTS (SELECT 1 FROM tt_detail_saldo_stock_nasional) THEN
        vValueColoumNameAll = ARRAY[''];
        Open pRefHeader FOR
        EXECUTE '
            SELECT 3 AS _COUNT, ''NO'', ''Product Code'', ''Product Name'', 1 AS ordinal
            order by ordinal';
        RETURN NEXT pRefHeader;
    ELSE
        -- menggabungkan nama kolom menjadi satu
        vValueColoumNameStock := array_append(vValueColoumNameStock,vColoumNameTotalStock);
        vValueColoumNameBuffer := array_append(vValueColoumNameBuffer,vColoumNameTotalBuffer);
        vValueColoumNameAll = array_cat(vValueColoumNameStock,vValueColoumNameRsvd);
        vValueColoumNameAll = array_cat(vValueColoumNameAll,vValueColoumNameRgto);
        vValueColoumNameAll = array_cat(vValueColoumNameAll,vValueColoumNameBuffer);
        vJumlahKolom := array_length(vValueColoumNameAll,1);

        --membuat nama reaf header yang dinamis
        SELECT ARRAY_TO_STRING(ARRAY( SELECT unnest(vValueColoumNameAll::text::text[])),''',''') INTO vColoumNameForHeader;
        vColoumNameForHeader := CONCAT('''',vColoumNameForHeader,'''');

        Open pRefHeader FOR
        EXECUTE '
            SELECT $1 + 3 AS _COUNT, ''NO'', ''Product Code'', ''Product Name'', '||vColoumNameForHeader||', 1 AS ordinal
            order by ordinal'
        USING vJumlahKolom;
        RETURN NEXT pRefHeader;
    END IF;

    --item dibuat menggunakan crosstab sesuai kolom dinamis di atas
    Open pRefDetail FOR
    EXECUTE
        ' SELECT row_number() OVER(),* FROM crosstab(
           ''SELECT B.product_code, B.product_name, concat(''''Qty Stock '''',f_get_warehouse_name(warehouse_id)) as warehouse, qty_stock
            FROM   tt_detail_saldo_stock_nasional A
            INNER JOIN tt_product_saldo_stock_nasional B ON B.session_id = A.session_id AND B.product_id = A.product_id
            WHERE A.session_id = '''''||pSessionId||''''' AND A.flg_data = '''''||vFlgRekap||'''''
            GROUP  BY A.session_id, A.product_id, B.product_code, B.product_name, A.warehouse_id, A.qty_stock

            UNION
            SELECT B.product_code, B.product_name, ''''TOTAL NASIONAL'''' as warehouse, SUM(qty_stock)
            FROM   tt_detail_saldo_stock_nasional A
            INNER JOIN tt_product_saldo_stock_nasional B ON B.session_id = A.session_id AND B.product_id = A.product_id
            WHERE A.session_id = '''''||pSessionId||''''' AND A.flg_data = '''''||vFlgRekap||'''''
            GROUP  BY A.session_id, A.product_id, B.product_code, B.product_name

            UNION
            SELECT B.product_code, B.product_name, concat(''''Qty Reserved '''',f_get_warehouse_name(warehouse_id)) as warehouse, qty_reserved
            FROM   tt_detail_saldo_stock_nasional A
            INNER JOIN tt_product_saldo_stock_nasional B ON B.session_id = A.session_id AND B.product_id = A.product_id
            WHERE A.session_id = '''''||pSessionId||''''' AND A.flg_data = '''''||vFlgRekap||'''''
            GROUP  BY A.session_id, A.product_id, B.product_code, B.product_name, A.warehouse_id, A.qty_reserved

            UNION
            SELECT B.product_code, B.product_name, concat(''''Qty RGTO '''',f_get_warehouse_name(warehouse_id)) as warehouse, qty_rgto
            FROM   tt_detail_saldo_stock_nasional A
            INNER JOIN tt_product_saldo_stock_nasional B ON B.session_id = A.session_id AND B.product_id = A.product_id
            WHERE A.session_id = '''''||pSessionId||''''' AND A.flg_data = '''''||vFlgRekap||'''''
            GROUP  BY A.session_id, A.product_id, B.product_code, B.product_name, A.warehouse_id, A.qty_rgto

            UNION
            SELECT B.product_code, B.product_name, concat(''''Qty Buffer '''',f_get_warehouse_name(warehouse_id)) as warehouse, qty_buffer
            FROM   tt_detail_saldo_stock_nasional A
            INNER JOIN tt_product_saldo_stock_nasional B ON B.session_id = A.session_id AND B.product_id = A.product_id
            WHERE A.session_id = '''''||pSessionId||''''' AND A.flg_data = '''''||vFlgRekap||'''''
            GROUP  BY A.session_id, A.product_id, B.product_code, B.product_name, A.warehouse_id, A.qty_buffer

            UNION
            SELECT B.product_code, B.product_name, ''''BUFFER NASIONAL'''' as warehouse, SUM(qty_buffer)
            FROM   tt_detail_saldo_stock_nasional A
            INNER JOIN tt_product_saldo_stock_nasional B ON B.session_id = A.session_id AND B.product_id = A.product_id
            WHERE A.session_id = '''''||pSessionId||''''' AND A.flg_data = '''''||vFlgRekap||'''''
            GROUP  BY A.session_id, A.product_id, B.product_code, B.product_name
            ORDER  BY product_code, product_name'',
            $$SELECT unnest('''||vValueColoumNameAll::text||'''::text[])$$
           )
        AS B ("product_code" character varying, "product_name" character varying
                '||vColoumNameStock||', "'||vColoumNameTotalStock||'" numeric '||vColoumNameRsvd||' '||vColoumNameRgto||' '||vColoumNameBuffer||', "'||vColoumNameTotalBuffer||'" numeric)';
    RETURN NEXT pRefDetail;

    DELETE FROM tt_product_saldo_stock_nasional WHERE session_id = pSessionId;
    DELETE FROM tt_detail_saldo_stock_nasional WHERE session_id = pSessionId;



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