CREATE OR REPLACE FUNCTION f_count_daily_nbp_nbg(bigint, bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId               alias for $1;
    pOuId                   alias for $2;
    pYearMonth              alias for $3;
    pSessionId              alias for $4;
    vCurrentDateTime        character varying;

    vDbName                 text;
    vDbUser                 text;
    vDbPassword             text;
    vDbPort                 text;
    vDbHostAddr             text;

BEGIN

    RAISE NOTICE 'Start counting... %', clock_timestamp();

SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWEB.DB.NAME')::text INTO vDbName;
SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWEB.DB.USER')::text INTO vDbUser;
SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWEB.DB.PASSWORD')::text INTO vDbPassword;
SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWEB.DB.PORT')::text INTO vDbPort;
SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWEB.DB.HOSTADDR')::text INTO vDbHostAddr;

vCurrentDateTime := to_char(current_timestamp, 'YYYYMMDDHH24MISS');

-- Function ini akan menghitung jumlah nbp dan nbg setiap dari bulan terakhir process hingga hari ini.
/*
vCurrentPeriod := to_char(current_timestamp, 'YYYYMM');

-- mendapatkan last processed period
SELECT date_year_month INTO vLastProcessedPeriod
FROM m_admin_process_ledger
WHERE tenant_id = pTenantId
AND ou_id = pOuId
AND ledger_code='MLM'
AND status_ledger = '1'
ORDER BY date_year_month DESC
LIMIT 1;
*/

/*
 * Modified By Tommy Ky 9 Maret 2017
 *  - dblink tidak langsung menghitung nilai nbp, tetapi ditampung dulu di table temporer tt_mlm_member_sales_detail
 *  - menambahkan perhitungan untuk bnp (nbp * product_value per item) dan bng (total bnp)
 */

/*
 *  - dblink tidak langsung menghitung nilai nbp, tetapi ditampung dulu di table temporer tt_mlm_member_sales_detail
-- lakukan perhitungan member yang sudah belanja dan masukkan kedalam tabel temporary tt_current_nbp;
-- 'dbname=palomaweb port=5432 hostaddr=192.168.0.225 user=sts password=Awesome123!'::text,
    INSERT INTO tt_current_nbp(member_id, nbp, session_id)
    SELECT member_id,nbp_member,session_id
    FROM dblink(
        'dbname='||vDbName||' port='||vDbPort||' hostaddr='||vDbHostAddr||' user='||vDbUser||' password='||vDbPassword||''::text,
        'SELECT member_id, SUM(nett_amount) AS nbp_member, '''||pSessionId||''' AS session_id
        FROM mlm_member_sales A
        INNER JOIN mlm_member_sales_detail B ON A.id = B.doc_id
        WHERE SUBSTRING(doc_date,0,7) = '''||pYearMonth||'''
        AND flag_cancel = ''N''
        GROUP BY member_id
        ORDER BY member_id ASC'
        ) AS A
        (
            member_id   bigint,
            nbp_member  numeric,
            session_id  character varying
        );
*/
RAISE NOTICE 'Retrieve data from Web DS... %', clock_timestamp();

    INSERT INTO tt_mlm_member_sales_detail
        (session_id, member_sales_detail_id, member_sales_id, product_catalog_id, qty, catalog_price, percent_discount_member, percent_discount_promo,
        amount_discount_member, amount_discount_promo, nett_price, nett_amount, flg_cancel, create_user_id, create_datetime, update_user_id,
        update_datetime, version, product_value, member_id)
    SELECT A.session_id, A.id, A.doc_id, A.product_catalog_id, A.qty, A.catalog_price, A.percent_discount_member, A.percent_discount_promo,
        A.amount_discount_member, A.amount_discount_promo, A.nett_price, A.nett_amount, A.flag_cancel, A.create_user_id, A.create_datetime, A.update_user_id,
        A.update_datetime, A.version, D.product_value, A.member_id
    FROM dblink(
        'dbname='||vDbName||' port='||vDbPort||' hostaddr='||vDbHostAddr||' user='||vDbUser||' password='||vDbPassword||''::text,
        'SELECT '''||pSessionId||''' AS session_id, B.id, doc_id, product_catalog_id, qty, catalog_price, percent_discount_member, percent_discount_promo,
        amount_discount_member, amount_discount_promo, nett_price, nett_amount, flag_cancel, B.create_user_id, B.create_datetime, B.update_user_id,
        B.update_datetime, B.version, A.member_id
        FROM mlm_member_sales A
        INNER JOIN mlm_member_sales_detail B ON A.id = B.doc_id
        WHERE SUBSTRING(doc_date,0,7) = '''||pYearMonth||'''
        AND flag_cancel = ''N'''
        ) AS A
        (
            session_id              character varying(100),
            id                      bigint,
            doc_id                  bigint,
            product_catalog_id      bigint,
            qty                     bigint,
            catalog_price           numeric(15,2),
            percent_discount_member numeric(15,2),
            percent_discount_promo  numeric(15,2),
            amount_discount_member  numeric(15,2),
            amount_discount_promo   numeric(15,2),
            nett_price              numeric(15,2),
            nett_amount             numeric(15,2),
            flag_cancel             character varying(1),
            create_user_id          bigint,
            create_datetime         character varying(14),
            update_user_id          bigint,
            update_datetime         character varying(14),
            version                 bigint,
            member_id               bigint
        )
    INNER JOIN m_product_catalog B ON A.product_catalog_id = B.product_catalog_id
    INNER JOIN m_product_custom C ON B.product_id = C.product_id
    INNER JOIN m_style_product D ON C.style_product = D.style_product AND B.catalog_id = D.catalog_id
    GROUP BY A.session_id, A.id, A.doc_id, A.product_catalog_id, A.qty, A.catalog_price, A.percent_discount_member, A.percent_discount_promo,
    A.amount_discount_member, A.amount_discount_promo, A.nett_price, A.nett_amount, A.flag_cancel, A.create_user_id, A.create_datetime, A.update_user_id,
    A.update_datetime, A.version, D.product_value, A.member_id
    ORDER BY A.doc_id, A.id;

    -- lakukan perhitungan member yang sudah belanja dan masukkan kedalam tabel temporary tt_current_nbp;
    RAISE NOTICE 'Insert into Current NBP... %', clock_timestamp();

    INSERT INTO tt_current_nbp(member_id, nbp, session_id, bnp)
    SELECT member_id, SUM(nett_amount) AS nbp_member, session_id, SUM(nett_amount * product_value / 100) AS bnp
    FROM tt_mlm_member_sales_detail
    GROUP BY member_id, session_id;

    -- Ambil data Penjualan Pulsa
    INSERT INTO tt_current_nbp(member_id, nbp, session_id, bnp)
    SELECT B.member_id, SUM(A.item_amount), pSessionId, SUM(A.item_amount * A.pv_percent / 100) AS bnp
    FROM mlm_trx_penjualan_pulsa A
    INNER JOIN mlm_member B ON A.member_code = B.member_code
    WHERE SUBSTRING(A.create_datetime,1,6) = pYearMonth
    GROUP BY B.member_id;

    ANALYZE tt_current_nbp;

-- lakukan perhitungan nbg semua member dan masukkan kedalam tabel temporary tt_current_nbg;

    INSERT INTO tt_current_nbg(member_id,nbg,session_id, bng)
    SELECT B.upline_id as member_id, COALESCE(SUM(nbp),0) AS nbg_member, pSessionId AS session_id, COALESCE(SUM(bnp),0)
    FROM tt_current_nbp A
    RIGHT JOIN mlm_member_tree B ON A.member_id = B.member_id
      WHERE A.session_id = pSessionId
    GROUP BY B.upline_id;

    ANALYZE tt_current_nbg;

-- insert ke tabel mlm_member_daily_nbp_nbg
    RAISE NOTICE 'Re-Insert into mlm_member_daily_nbp_nbg... %', clock_timestamp();

    DELETE FROM mlm_member_daily_nbp_nbg WHERE period = pYearMonth;

    INSERT INTO mlm_member_daily_nbp_nbg (tenant_id, ou_id,member_id,period,nbp,nbg,last_process_date, bnp, bng)
    SELECT pTenantId AS tenant_id, pOuId AS ou_id, A.member_id,
        pYearMonth as period, COALESCE(nbp,0) AS nbp,
        nbg, vCurrentDateTime AS last_process_date, COALESCE(bnp,0) AS bnp, bng
    FROM tt_current_nbg A
    LEFT JOIN tt_current_nbp B ON A.member_id = B.member_id AND B.session_id = pSessionId
    WHERE
       A.session_id = pSessionId;

    DELETE FROM tt_mlm_member_sales_detail WHERE session_id = pSessionId;
    DELETE FROM tt_current_nbp where session_id = pSessionId;
    DELETE FROM tt_current_nbg where session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;