-- Function: public.f_update_mstr_min_sell_price_from_hpp_avg(character varying, bigint, bigint, bigint, character varying)

-- DROP FUNCTION public.f_update_mstr_min_sell_price_from_hpp_avg(character varying, bigint, bigint, bigint, character varying);

CREATE OR REPLACE FUNCTION f_update_mstr_min_sell_price_from_hpp_avg(
    character varying,
    bigint,
    bigint,
    bigint,
    character varying)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId                      alias for $1;
    pTenantId                       alias for $2;
    pOuId                           alias for $3;
    pUserId                         alias for $4;
    pDatetime                       alias for $5;

    vLedgerCodeCostFg               character varying(7) := 'COST.FG';
    vLastProcessedYearMonth         character varying(6);
    vNextYearMonthFromLastProcessed character varying(6);
    vCurrentDatetime                character varying(14);
    vCurrentYearMonth               character varying(6);
    vDocTypeIdRG                    bigint := 111;
    vDocTypeIdAdjStockAmount        bigint := 522;
    vDocTypeIdSaldoAwal             bigint := -99;
    vOuBuId                         bigint ;
    vDataTypeBeg                    character varying(8) := 'BEG';
    vDataTypeTrx                    character varying(8) := 'TRX';
    vDataTypeHppAvg                 character varying(12) := 'HPP-AVG';
    vEmptyAmount                    numeric := 0;

    vOuExceptionSellPrice           character varying := '';
    vLevelPriceSpecial              character varying := '';
    vTimeMillisecond                character varying := '';
    vDigitRounding                  integer := 0;

BEGIN

    -- hapus data pada table temporary
    DELETE FROM tt_calculate_hpp_avg WHERE session_id = pSessionId;

    -- prosesnya akan dijalankan jika di table tt_item_for_calc_hpp_average ada datanya (list product yg akan diproses)
    IF EXISTS (SELECT 1 FROM tt_item_for_calc_hpp_average WHERE session_id = pSessionId) THEN

        RAISE NOTICE '-- proses hitung harga pokok penjualan (HPP) -- ';

        -- get digit rounding untuk perhitungan harga rata-rata
        SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, 'rounding.hpp.average') AS integer) INTO vDigitRounding;

        -- get daftar ou exception sell price & level price special
        SELECT f_get_value_system_config_by_param_code(pTenantId, 'SELL.PRICE.SO.OU.EXCEPTION') INTO vOuExceptionSellPrice;
        SELECT f_get_value_system_config_by_param_code(pTenantId, 'SELL.PRICE.SO.LEVELPRICE.SPECIAL') INTO vLevelPriceSpecial;

        -- get nilai current datetime, current year month, & time millisecond
        SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS') INTO vCurrentDatetime;
        SELECT LEFT(vCurrentDatetime,6) INTO vCurrentYearMonth;
        SELECT CAST(extract(epoch from now()) AS character varying) INTO vTimeMillisecond;

        -- get nilai ou bu id
        SELECT ou_bu_id INTO vOuBuId
        FROM m_ou_structure B
        WHERE ou_id = pOuId;

        -- ambil bulan terakhir closing ledger COST.FG
        SELECT COALESCE(MAX(A.date_year_month),' ') INTO vLastProcessedYearMonth
        FROM m_admin_process_ledger A
        WHERE A.tenant_id = pTenantId AND
            A.ou_id = vOuBuId AND
            A.status_ledger = '1' AND
            A.ledger_code = vLedgerCodeCostFg AND
            A.date_year_month < vCurrentYearMonth;

        SELECT TO_CHAR(TO_DATE(vLastProcessedYearMonth,'YYYYMM') + interval '1 Month','YYYYMM') INTO vNextYearMonthFromLastProcessed;

        /* ambil data saldo awal hasil proses costing terakhir
        (in_summary_monthly_amount yg date_year_month = thn bln terakhir closing + 1, doc type id = -99,
        produk sesuai yg dimasukan ke table tt_item_for_calc_hpp_average >> yg diinsert di func submit RG & Adj Stock Amount) */
        INSERT INTO tt_calculate_hpp_avg(
                session_id, data_type, product_id, qty, amount, hpp_avg )
        SELECT  pSessionId, vDataTypeBeg, A.product_id, A.qty, A.gl_amount, vEmptyAmount
        FROM in_summary_monthly_amount A
        INNER JOIN tt_item_for_calc_hpp_average B ON B.session_id = pSessionId AND A.product_id = B.product_id
        WHERE A.tenant_id = pTenantId
            AND A.ou_bu_id = vOuBuId
            AND A.date_year_month = vNextYearMonthFromLastProcessed
            AND A.doc_type_id = vDocTypeIdSaldoAwal;

        /* ambil semua nilai pembelian (doc type id RG = 111) dan adj stock amount(522)
        dari table in_product_price_balance, yg product_buy_date nya >= tgl 1 dari (thn bln closing+1),
        produk sesuai yg dimasukan ke table tt_item_for_calc_hpp_average >> yg diinsert di func submit RG & Adj Stock Amount)  */
        INSERT INTO tt_calculate_hpp_avg(
                session_id, data_type, product_id, qty, amount, hpp_avg )
        SELECT  pSessionId, vDataTypeTrx, A.product_id, A.qty, A.amount, vEmptyAmount
        FROM in_product_price_balance A
        INNER JOIN tt_item_for_calc_hpp_average B ON B.session_id = pSessionId AND A.product_id = B.product_id
        INNER JOIN m_ou_structure C ON A.ou_id = C.ou_id
        WHERE A.tenant_id = pTenantId
            AND C.ou_bu_id = vOuBuId
            AND A.doc_type_id IN (vDocTypeIdRG, vDocTypeIdAdjStockAmount)
            AND LEFT(A.product_buy_date,6) >= vNextYearMonthFromLastProcessed
            AND A.qty > 0;

        /* Tulis log data perhitungan harga jual sementara:
            a. Saldo awal dari hasil proses costing (date year month = last closing, data type = ‘BEG’)
            b. Summary trx dari product price balance (date year month = current year month, data type = ‘TRX’) */
        INSERT INTO in_log_calculate_hpp_avg(
                tenant_id, product_id, qty, amount,
                date_year_month, data_type, time_millisecond, create_datetime,
                create_user_id, update_datetime, update_user_id, version)
        SELECT  pTenantId, product_id, SUM(qty) AS qty, SUM(amount) AS amount,
                CASE WHEN data_type = vDataTypeBeg THEN vLastProcessedYearMonth
                     ELSE vCurrentYearMonth END AS date_year_month,
                data_type, vTimeMillisecond, vCurrentDatetime,
                pUserId, vCurrentDatetime, pUserId, 0
        FROM tt_calculate_hpp_avg
        WHERE session_id = pSessionId
        GROUP BY product_id, data_type
        ORDER BY data_type;

        -- hitung harga pokok penjualan (hpp) rata-rata berdasarkan data saldo awal & nilai pembelian trx
        INSERT INTO tt_calculate_hpp_avg(
                session_id, data_type, product_id,
                qty, amount, hpp_avg )
        SELECT  pSessionId, vDataTypeHppAvg, product_id,
                SUM(qty) AS qty, SUM(amount) AS amount, (SUM(amount)/SUM(qty)) AS hpp_avg
        FROM tt_calculate_hpp_avg
        WHERE session_id = pSessionId
        GROUP BY product_id;


        RAISE NOTICE '-- proses insert log & update master harga jual -- ';

        /* Tulis log master harga jual sblm diupdate, khusus untuk harga jual yg akan diupdate saja
            Data master harga jual yg diupdate:
            - sesuai dengan product RG/Adjustment stock yg dikirim
            - yg berlaku saat ini
            - untuk ou & level price tertentu
                -> berdasarkan value dari sysconfig `SELL.PRICE.SO.OU.EXCEPTION` & `SELL.PRICE.SO.LEVELPRICE.SPECIAL` */
        INSERT INTO m_log_sell_price_product_for_so(
                sell_price_product_for_so_id, tenant_id, ou_id, product_id, date_from, date_to,
                curr_code, gross_sell_price, flg_tax_amount, tax_id, tax_amount, sell_price,
                min_sell_price, price_level, create_datetime, create_user_id,
                update_datetime, update_user_id, version, time_millisecond)
        SELECT  B.sell_price_product_for_so_id, B.tenant_id, B.ou_id, B.product_id, B.date_from, B.date_to,
                B.curr_code, B.gross_sell_price, B.flg_tax_amount, B.tax_id, B.tax_amount, B.sell_price,
                B.min_sell_price, B.price_level, vCurrentDatetime, pUserId,
                vCurrentDatetime, pUserId, 0, vTimeMillisecond
        FROM tt_calculate_hpp_avg A
        INNER JOIN m_sell_price_product_for_so B ON A.product_id = B.product_id
        INNER JOIN t_ou C ON B.ou_id = C.ou_id
        WHERE A.session_id = pSessionId
            AND A.data_type = vDataTypeHppAvg
            AND B.tenant_id = pTenantId
            AND C.ou_code = ANY(string_to_array(vOuExceptionSellPrice, ','))
            AND LEFT(vCurrentDatetime, 8) BETWEEN B.date_from AND B.date_to
            AND B.price_level = vLevelPriceSpecial;


        /* Update master min sell price so = hasil hitung harga pokok penjualan (hpp) rata-rata
            Data master harga jual yg diupdate:
            - sesuai dengan product RG/Adjustment stock yg dikirim
            - yg berlaku saat ini
            - untuk ou & level price tertentu
                -> berdasarkan value dari sysconfig `SELL.PRICE.SO.OU.EXCEPTION` & `SELL.PRICE.SO.LEVELPRICE.SPECIAL` */
        UPDATE m_sell_price_product_for_so A
        SET min_sell_price = ROUND(B.hpp_avg, vDigitRounding),
            version = A.version + 1,
            update_user_id = pUserId,
            update_datetime = vCurrentDatetime
        FROM tt_calculate_hpp_avg B, t_ou C
        WHERE B.session_id = pSessionId
            AND B.data_type = vDataTypeHppAvg
            AND B.product_id = A.product_id
            AND A.tenant_id = pTenantId
            AND A.ou_id = C.ou_id
            AND C.ou_code = ANY(string_to_array(vOuExceptionSellPrice, ','))
            AND LEFT(vCurrentDatetime, 8) BETWEEN A.date_from AND A.date_to
            AND A.price_level = vLevelPriceSpecial;

    END IF;

    -- hapus data pada table temporary
    DELETE FROM tt_calculate_hpp_avg WHERE session_id = pSessionId;
    DELETE FROM tt_item_for_calc_hpp_average WHERE session_id = pSessionId;


END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
/