Untitled

CREATE OR REPLACE FUNCTION fzidw_summary_daily_hondok_per_dokter(
    pSessionId    varchar,
    pTenantId     bigint,
    pUserId       bigint,
    pDatetime     varchar,
    pDocDateFrom  varchar,
    pDocDateTo    varchar
)
RETURNS void AS
$BODY$
DECLARE
    vPembayaranTunai varchar := 'tunai';
    vPembayaranAsuransi varchar := 'asuransi';
    vPembayaranBPJS varchar := 'bpjs';
    vPembayaranPerusahaan varchar := 'perusahaan';
    vInsertedCount   int;
    vUpdatedCount    int;
    vUpdatedEstimateCount int;

BEGIN
    WITH raw_data AS (
        SELECT
            A.poli_id,
            A.dokter_id AS partner_code,
            C.partner_id AS partner_id_dokter,
            C.partner_name AS dokter_name,
            A.petugas_id,
            B.doc_date,
            A.process_no,
            A.tenant_id,
            A.trx_pos_id,
            B.ou_id,
            (f_get_ou_bu_structure(B.ou_id)).ou_bu_id AS ou_bu_id,
            D.jns_pembayaran_id
        FROM
            i_trx_pos_procedure_taken A
        JOIN
            i_trx_pos B ON A.process_no = B.process_no
                        AND A.trx_pos_id = B.trx_pos_id
                        AND A.tenant_id = B.tenant_id
        JOIN
            m_partner C ON A.dokter_id = C.partner_code
                        AND A.tenant_id = C.tenant_id
        JOIN
            i_trx_pos_admission D ON A.process_no = D.process_no
                                AND A.tenant_id = D.tenant_id
                                AND A.trx_pos_id = D.trx_pos_id
                                AND A.admission_id = D.admission_id
        WHERE B.doc_date BETWEEN pDocDateFrom AND pDocDateTo
        AND A.jns_pelayanan_id IN (
            2,3,4,5,6,7,8,9,11,13,14,15,
            10,16,18,19,20,21,24,27,28
        )
        AND EXISTS (
            SELECT 1
            FROM zi_hondok_config_poli E
            WHERE E.poli_code = A.poli_id
            AND E.active = 'Y'
        )
    ),
    inserted AS (
        INSERT INTO zidw_summary_daily_hondok_per_dokter (
            doc_date, ou_id, tenant_id, poli_code, dokter_id, flg_process,
            count_cash_admission, count_bpjs_admission, count_asuransi_admission, count_perusahaan_admission,
            uang_duduk, active, active_datetime, non_active_datetime, create_user_id, create_datetime, update_user_id, update_datetime,
            remark, version
        )
        SELECT
            doc_date,
            ou_bu_id,
            pTenantId,
            poli_id,
            partner_id_dokter,
            'Y',
            SUM(CASE WHEN jns_pembayaran_id = vPembayaranTunai THEN 1 ELSE 0 END),
            SUM(CASE WHEN jns_pembayaran_id = vPembayaranBPJS THEN 1 ELSE 0 END),
            SUM(CASE WHEN jns_pembayaran_id = vPembayaranAsuransi THEN 1 ELSE 0 END),
            SUM(CASE WHEN jns_pembayaran_id = vPembayaranPerusahaan THEN 1 ELSE 0 END),
            fzi_get_tarif_uang_duduk(A.poli_id, SUM(CASE WHEN jns_pembayaran_id IN (vPembayaranTunai, vPembayaranBPJS, vPembayaranAsuransi, vPembayaranPerusahaan) THEN 1 ELSE 0 END)),
            'Y',
            pDatetime,
            '',
            pUserId,
            pDatetime,
            pUserId,
            pDatetime,
            '',
            0
        FROM
            raw_data A
        WHERE
            NOT EXISTS (
                SELECT 1
                FROM zidw_summary_daily_hondok_per_dokter B
                WHERE B.dokter_id = A.partner_id_dokter
                AND B.doc_date BETWEEN pDocDateFrom AND pDocDateTo
                AND A.ou_bu_id = B.ou_id
            )
        GROUP BY
            A.doc_date, A.ou_bu_id, A.partner_id_dokter, A.poli_id
        RETURNING *
    ),
    updated_count AS (
        -- First UPDATE block
        UPDATE zidw_summary_daily_hondok_per_dokter A
        SET
            count_cash_admission = B.count_cash_admission,
            count_bpjs_admission = B.count_bpjs_admission,
            count_asuransi_admission = B.count_asuransi_admission,
            count_perusahaan_admission = B.count_perusahaan_admission,
            uang_duduk = B.uang_duduk,
            update_datetime = pDatetime,
            update_user_id = pUserId,
            version = A.version + 1
        FROM (
            SELECT
                doc_date,
                ou_bu_id,
                poli_id,
                partner_id_dokter,
                SUM(CASE WHEN jns_pembayaran_id = vPembayaranTunai THEN 1 ELSE 0 END) AS count_cash_admission,
                SUM(CASE WHEN jns_pembayaran_id = vPembayaranBPJS THEN 1 ELSE 0 END) AS count_bpjs_admission,
                SUM(CASE WHEN jns_pembayaran_id = vPembayaranAsuransi THEN 1 ELSE 0 END) AS count_asuransi_admission,
                SUM(CASE WHEN jns_pembayaran_id = vPembayaranPerusahaan THEN 1 ELSE 0 END) AS count_perusahaan_admission,
                fzi_get_tarif_uang_duduk(A.poli_id, SUM(CASE WHEN jns_pembayaran_id IN (vPembayaranTunai, vPembayaranBPJS, vPembayaranAsuransi, vPembayaranPerusahaan) THEN 1 ELSE 0 END)) AS uang_duduk
            FROM raw_data A
            GROUP BY doc_date, ou_bu_id, poli_id, partner_id_dokter
        ) B
        WHERE A.dokter_id = B.partner_id_dokter
        AND A.doc_date BETWEEN pDocDateFrom AND pDocDateTo
        AND A.ou_id = B.ou_bu_id
        RETURNING *
    ),
    updated_estimated_amount AS (
        -- Second UPDATE block
        UPDATE zidw_summary_daily_hondok_per_dokter A
        SET
            estimate_amount_cash = fzi_get_estimate_amount_cash(pTenantId, B.partner_id_dokter, pDocDateFrom, pDocDateTo, B.poli_id, A.uang_duduk),
            estimate_amount_bpjs = fzi_get_estimate_amount_bpjs(pTenantId, B.partner_id_dokter, pDocDateFrom, pDocDateTo, B.poli_id, A.uang_duduk),
            estimate_amount_asuransi = fzi_get_estimate_amount_asuransi(pTenantId, B.partner_id_dokter, pDocDateFrom, pDocDateTo, B.poli_id, A.uang_duduk),
            estimate_amount_perusahaan = fzi_get_estimate_amount_perusahaan(pTenantId, B.partner_id_dokter, pDocDateFrom, pDocDateTo, B.poli_id, A.uang_duduk),
            update_datetime = pDatetime,
            update_user_id = pUserId,
            version = A.version + 1
        FROM raw_data B
        WHERE A.dokter_id = B.partner_id_dokter
        AND A.doc_date BETWEEN pDocDateFrom AND pDocDateTo
        AND A.ou_id = B.ou_bu_id
        RETURNING *
    )
    -- Menghitung jumlah baris yang berhasil di-insert dan di-update
    SELECT
        (SELECT COUNT(1) FROM inserted),
        (SELECT COUNT(1) FROM updated_count),
        (SELECT COUNT(1) FROM updated_estimated_amount)
    INTO vInsertedCount, vUpdatedCount, vUpdatedEstimateCount;

    -- Output RAISE NOTICE
    RAISE NOTICE 'Rows inserted: %', vInsertedCount;
    RAISE NOTICE 'Rows updated (count): %', vUpdatedCount;
    RAISE NOTICE 'Rows updated (estimated amount): %', vUpdatedEstimateCount;
END;
$BODY$
LANGUAGE plpgsql;
/