ul_upload_ou

CREATE OR REPLACE FUNCTION ul_upload_ou(bigint)
  RETURNS bigint AS
$BODY$
DECLARE
    pUploadHeaderId         ALIAS FOR $1;

    vFail                   character varying(4) := 'FAIL';
    vOk                     character varying(2) := 'OK';
    vYes                    character varying(1) := 'Y';
    vNo                     character varying(1) := 'N';
    vActionInsert           character varying(1) := 'I';
    vActionUpdate           character varying(1) := 'U';
    vActionError            character varying(1) := 'E';
    vEmpty                  character varying(1) := '';
    vSpaceValue             character varying(1) := ' ';

    vTenantId               bigint;
    vUserId                 bigint;
    vDatetime               character varying(14);

    vCountFail              bigint:=0;

    vEmptyId                bigint := -99;
    vTempId                 bigint := -999;

BEGIN
    -- siapkan parameter
    vTenantId = CAST( f_get_upload_parameter(pUploadHeaderId, 'tenantId') AS bigint );
    vUserId = CAST( f_get_upload_parameter(pUploadHeaderId, 'userId') AS bigint );
    vDatetime = CAST( f_get_upload_parameter(pUploadHeaderId, 'datetime') AS character varying );

    -- Validasi
    -- # Kode OU harus diisi
    -- # Max character dari Kode OU adalah 50
    -- # Kode OU harus belum ada dalam sistem
    -- # Kode Tipe OU harus diisi
    -- # Kode Tipe OU harus ada dalam sistem
    -- # Jika Kode OU Parent diisi, Kode OU Parent tersebut harus memiliki tenant yang sama
    -- # Nama OU harus diisi
    -- # Max character dari Nama OU adalah 255
    -- # Jika Kode OU Parent diisi, Kode OU Parent tersebut harus ada dalam sistem
    -- # List pada CSV yang diupload, tidak boleh duplikat berdasarkan Kode OU

    -- # Kode OU harus diisi
    UPDATE ul_import_data_ou A
    SET status = vFail,
        message = message || 'Kode OU harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_ou) = vEmpty;

    -- # Max character dari Kode OU adalah 50
    UPDATE ul_import_data_ou A
    SET status = vFail,
        message = message || 'Panjang Kode OU maksimal 50 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.kode_ou) > 50;

    -- # Kode OU harus belum ada dalam sistem
    UPDATE ul_import_data_ou A
    SET status = vFail,
        message = message || 'Kode OU sudah ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND EXISTS (
            SELECT 1
            FROM t_ou B
            WHERE B.ou_code = A.kode_ou
        );

    -- # Kode Tipe OU harus diisi
    UPDATE ul_import_data_ou A
    SET status = vFail,
        message = message || 'Kode Tipe OU harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_tipe_ou) = vEmpty;

    -- # Kode Tipe OU harus ada dalam sistem
    UPDATE ul_import_data_ou A
    SET status = vFail,
        message = message || 'Kode Tipe OU tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_tipe_ou) <> vEmpty
        AND NOT EXISTS (
            SELECT 1
            FROM t_ou_type B
            WHERE B.ou_type_code = A.kode_tipe_ou
            AND B.tenant_id = vTenantId
        );

    -- # Nama OU harus diisi
    UPDATE ul_import_data_ou A
    SET status = vFail,
        message = message || 'Nama OU harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.nama_ou) = vEmpty;

    -- # Max character dari Nama OU adalah 255
    UPDATE ul_import_data_ou A
    SET status = vFail,
        message = message || 'Panjang Nama OU maksimal 255 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.nama_ou) > 255;

    -- # Jika Kode OU Parent diisi, Kode OU Parent tersebut harus ada dalam sistem
    UPDATE ul_import_data_ou A
    SET status = vFail,
        message = message || 'Kode OU Parent tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_ou_parent) <> vEmpty
        AND NOT EXISTS (
            SELECT 1
            FROM t_ou B
            WHERE B.ou_code = A.kode_ou_parent
            AND B.tenant_id = vTenantId
        );

    -- # List pada CSV yang diupload, tidak boleh duplikat berdasarkan Kode OU
    WITH duplicate_data_ou AS (
        SELECT A.kode_ou
        FROM ul_import_data_ou A
        WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_ou) <> vEmpty
        GROUP BY A.kode_ou
        HAVING COUNT(1) > 1
    )
    UPDATE ul_import_data_ou A
    SET status = vFail,
        message = message || 'Kode OU duplikat, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND EXISTS (
            SELECT 1 FROM duplicate_data_ou B WHERE A.kode_ou = B.kode_ou
        );

    -- # Cari data Tipe OU
    UPDATE ul_import_data_ou A
    SET flg_bu = B.flg_bu,
        flg_accounting = B.flg_accounting,
        flg_sub_bu = B.flg_sub_bu,
        flg_branch = B.flg_branch
    FROM t_ou_type B
    WHERE A.upload_header_id = pUploadHeaderId
        AND A.status <> vFail
        AND B.ou_type_code = A.kode_tipe_ou;

    -- # Cari data OU Parent
    UPDATE ul_import_data_ou A
    SET ou_parent_id = B.ou_id,
        parent_flg_bu = C.flg_bu,
        parent_flg_accounting = C.flg_accounting,
        parent_flg_sub_bu = C.flg_sub_bu,
        parent_flg_branch = C.flg_branch
    FROM t_ou B
    INNER JOIN t_ou_type C ON B.ou_type_id = C.ou_type_id
    WHERE A.upload_header_id = pUploadHeaderId
        AND A.status <> vFail
        AND B.ou_code = A.kode_ou_parent;

    -- # Untuk OU yang merupakan Business Unit, parent OU harus -99
    UPDATE ul_import_data_ou A
    SET status = vFail,
        message = message || 'OU business unit tidak boleh memiliki parent, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND A.status <> vFail
        AND A.flg_bu = vYes
        AND A.flg_accounting = vNo
        AND A.flg_sub_bu = vNo
        AND A.flg_branch = vNo
        AND A.ou_parent_id <> vEmptyId;

    -- # Selain Business Unit, harus memiliki parent Id
    UPDATE ul_import_data_ou A
    SET status = vFail,
        message = message || 'OU selain business unit harus memiliki parent, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND A.status <> vFail
        AND NOT (A.flg_bu = vYes
            AND A.flg_accounting = vNo
            AND A.flg_sub_bu = vNo
            AND A.flg_branch = vNo)
        AND A.ou_parent_id = vEmptyId;

    -- # Untuk OU yang merupakan Sub Business Unit, parent OU nya harus merupakan Business Unit atau Branch
    UPDATE ul_import_data_ou A
    SET status = vFail,
        message = message || 'OU sub business unit harus memiliki parent business unit atau branch, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND A.status <> vFail
        AND A.flg_bu = vYes
        AND A.flg_accounting = vNo
        AND A.flg_sub_bu = vYes
        AND A.flg_branch = vNo
        AND NOT (A.parent_flg_bu = vYes
            AND A.parent_flg_accounting = vNo
            AND A.parent_flg_sub_bu = vNo);

    -- # Untuk OU yang merupakan Branch, parent OU nya harus merupakan Business Unit
    UPDATE ul_import_data_ou A
    SET status = vFail,
        message = message || 'OU branch harus memiliki parent business unit, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND A.status <> vFail
        AND A.flg_bu = vYes
        AND A.flg_accounting = vNo
        AND A.flg_sub_bu = vNo
        AND A.flg_branch = vYes
        AND NOT (A.parent_flg_bu = vYes
            AND A.parent_flg_accounting = vNo
            AND A.parent_flg_sub_bu = vNo
            AND A.parent_flg_branch = vNo);

    -- Cek apakah ada item CSV yang statusnya fail
    IF EXISTS ( SELECT 1 FROM ul_import_data_ou B
                WHERE B.upload_header_id = pUploadHeaderId
                AND B.status = vFail ) THEN

                -- Sesuaikan message, agar message paling belakang tidak ada koma
                UPDATE ul_import_data_ou A
                SET message = substr(A.message, 1, length(A.message)-2)
                WHERE A.upload_header_id = pUploadHeaderId
                    AND A.status = vFail
                    AND TRIM(A.message) != vEmpty;

                -- Update semua status menjadi FAIL jika ada salah satu item yang terkena validasi
                -- Karena jika ada salah 1 item yang fail, maka 1 csv dianggap tidak valid
                -- ubah update_status menjadi E untuk menandakan bahwa data error / tidak digunakan untuk membuat data
                UPDATE ul_import_data_ou A
                SET status = vFail,
                    update_status = vActionError
                WHERE upload_header_id = pUploadHeaderId;

                -- Hitung jumlah data yang FAIL
                SELECT COUNT(1) INTO vCountFail
                FROM ul_import_data_ou
                WHERE upload_header_id = pUploadHeaderId
                    AND status = vFail;
    ELSE
                -- Update status non FAIL to OK
                UPDATE ul_import_data_ou
                SET status = vOk
                WHERE upload_header_id = pUploadHeaderId;

                -- UPDATE OK data untuk ou code terdaftar
                /*WITH update_data_ou AS (
                    UPDATE t_ou A SET
                        ou_name=B.nama_ou,
                        --ou_parent_id=B.ou_parent_id,
                        ou_type_id=C.ou_type_id,
                        update_datetime=vDatetime,
                        update_user_id=vUserId,
                        version=A.version+1
                    FROM ul_import_data_ou B
                    INNER JOIN t_ou_type C ON B.kode_tipe_ou = C.ou_type_code
                    WHERE B.upload_header_id = pUploadHeaderId
                    AND B.status = vOk
                    AND A.ou_code = B.kode_ou
                    AND A.tenant_id = vTenantId
                    AND C.tenant_id = vTenantId
                    RETURNING A.*
                )
                -- Ubah update_status menjadi U jika data digunakan untuk Update
                UPDATE ul_import_data_ou A SET
                    update_status = vActionUpdate
                WHERE A.upload_header_id = pUploadHeaderId
                    AND A.status = vOk
                    AND EXISTS (
                        SELECT 1 FROM update_data_ou B WHERE A.kode_ou = B.ou_code AND B.tenant_id = vTenantId
                    );*/

                -- INSERT OK data untuk ou code baru
                WITH insert_data_ou AS (
                    INSERT INTO t_ou(
                        tenant_id, ou_code, ou_name, ou_parent_id, ou_type_id,
                        create_datetime, create_user_id, update_datetime, update_user_id,
                        version, active, active_datetime, non_active_datetime)
                    SELECT vTenantId, kode_ou, nama_ou, ou_parent_id, B.ou_type_id, vDatetime,
                            vUserId, vDatetime, vUserId, 0, vYes,
                            vDatetime, vSpaceValue
                    FROM ul_import_data_ou A
                    INNER JOIN t_ou_type B ON A.kode_tipe_ou = B.ou_type_code
                    WHERE A.upload_header_id = pUploadHeaderId
                    AND A.status = vOk
                    AND B.tenant_id = vTenantId
                    AND NOT EXISTS (
                        SELECT 1 FROM t_ou Z
                        WHERE A.kode_ou = Z.ou_code
                        AND B.tenant_id = vTenantId
                    )
                    RETURNING *
                )
                -- Ubah update_status menjadi I jika data digunakan untuk Insert
                UPDATE ul_import_data_ou A SET
                    update_status = vActionInsert
                WHERE A.upload_header_id = pUploadHeaderId
                    AND A.status = vOk
                    AND EXISTS (
                        SELECT 1 FROM insert_data_ou B WHERE A.kode_ou = B.ou_code AND B.tenant_id = vTenantId
                    );
    END IF;

    RETURN vCountFail;

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