USE [HRIS]
GO

/****** Object:  StoredProcedure [dbo].[sp_generate_pegawai_eligible]    Script Date: 11/17/2020 2:08:16 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_generate_pegawai_mst] 
	@kd_gol varchar(2),
	@cutoff_date date,
     --@kd_pend_umum varchar(2),
     @masa_dinas tinyint,
     @sisa_masa_dinas tinyint,
     --@md_tinggi int,
     --@md_reguler int,
	@md_leveljabatan tinyint,
	@usia tinyint,
	@nk_2018 float,	-- sementara nama parameter hardcode tahun dulu
	@nk_2019 float,	-- sementara nama parameter hardcode tahun dulu
	@na tinyint,


     @nk_tinggi float,
     @nk_reguler float,
     @sts_hukuman varchar(5),
     @periode_pemenuhan varchar(10),
     @query_date date = '1900-01-01'
AS
BEGIN
    DECLARE @level_jabatan_pemenuhan varchar(25)

    SELECT @level_jabatan_pemenuhan = NM_GOLONGAN FROM SPG_GOLONGAN WHERE KD_GOL = @kd_gol

    DELETE
    FROM MST_DAFTAR_PEGAWAI_AKTIF
    WHERE PERIODE_PEMENUHAN = @periode_pemenuhan
      AND KETERANGAN = 'GENERATE BY SYSTEM'
      AND LEVEL_JABATAN_PEMENUHAN = @level_jabatan_pemenuhan

    INSERT INTO MST_DAFTAR_PEGAWAI_AKTIF (NIP, NM_PEG, USIA_SAAT_INI, EMAIL, STS_PENERIMAAN_PEG, ANGKATAN, TAHUN_PEREKRUTAN,
                                      STS_PENSIUN, NM_STATUS_KEPEG, NM_KANTOR, LEVEL_JABATAN, NM_JABATAN, FORMASI,
                                      KATEGORI_FUNGSI, BIDANG, DEPUTI_KOMISIONER, DEPARTEMEN, DIREKTUR, DEPUTI_DIREKTUR,
                                      KEPALA_BAGIAN, KEPALA_SUBBAGIAN, SATUAN_KERJA, KET_LEMBAGA_ASAL, TMT_LEMBAGA_ASAL,
                                      TMT_OJK, TMT_LEVEL_JABATAN, TMT_JABATAN, TMT_SATKER, TMT_PENSIUN, TGL_MASUK,
                                      PENDIDIKAN, NILAI_KINERJA_1, LEVEL_KINERJA_1, NILAI_KINERJA_2, LEVEL_KINERJA_2,
                                      NILAI_KINERJA_3, LEVEL_KINERJA_3, HUKUMAN, MASA_DINAS_OJK, SISA_MASA_DINAS_OJK,
                                      MASA_DINAS_LEVEL_JABATAN, MD_LEVEL_JABATAN_POTENSI_TINGGI,
                                      MD_LEVEL_JABATAN_REGULER, NK_POTENSI_TINGGI, NK_REGULER, ELIGIBLE_POTENSI_TINGGI,
                                      ELIGIBLE_REGULER, ELIGIBLE_COMBINE, KATEGORI_SISA_MD_OJK, PERIODE_PEMENUHAN,
                                      KETERANGAN, LEVEL_JABATAN_PEMENUHAN, KD_GOL, KD_BIDANG, KD_SATKER, KD_JABATAN_STR)
    SELECT NIP,
           NM_PEG,
           USIA_SAAT_INI,
           EMAIL,
           STS_PENERIMAAN_PEG,
           ANGKATAN,
           TAHUN_PEREKRUTAN,
           STS_PENSIUN,
           NM_STATUS_KEPEG,
           NM_KANTOR,
           LEVEL_JABATAN,
           NM_JABATAN,
           FORMASI,
           KATEGORI_FUNGSI,
           BIDANG,
           DEPUTI_KOMISIONER,
           DEPARTEMEN,
           DIREKTUR,
           DEPUTI_DIREKTUR,
           KEPALA_BAGIAN,
           KEPALA_SUBBAGIAN,
           SATUAN_KERJA,
           KET_LEMBAGA_ASAL,
           TMT_LEMBAGA_ASAL,
           TMT_OJK,
           TMT_LEVEL_JABATAN,
           TMT_JABATAN,
           TMT_SATKER,
           TMT_PENSIUN,
           TGL_MASUK,
           PENDIDIKAN,
           NILAI_KINERJA_1,
           LEVEL_KINERJA_1,
           NILAI_KINERJA_2,
           LEVEL_KINERJA_2,
           NILAI_KINERJA_3,
           LEVEL_KINERJA_3,
           HUKUMAN,
           MASA_DINAS_OJK,
           SISA_MASA_DINAS_OJK,
           MASA_DINAS_LEVEL_JABATAN,
           IIF(CAST(LEFT(MASA_DINAS_LEVEL_JABATAN, 2) as int) >= @md_tinggi, 'IYA',
               'TIDAK')                                                                       AS MD_LEVEL_JABATAN_POTENSI_TINGGI,
           IIF(CAST(LEFT(MASA_DINAS_LEVEL_JABATAN, 2) as int) >= @md_reguler, 'IYA',
               'TIDAK')                                                                       AS MD_LEVEL_JABATAN_REGULER,
           NK_POTENSI_TINGGI,
           NK_REGULER,
           IIF((IIF(CAST(LEFT(MASA_DINAS_LEVEL_JABATAN, 2) as int) >= @md_tinggi, 1, 0)) = 1 AND
               NK_POTENSI_TINGGI = 'IYA', 'IYA',
               'TIDAK')                                                                       AS ELIGIBLE_POTENSI_TINGGI,
           IIF((IIF(CAST(LEFT(MASA_DINAS_LEVEL_JABATAN, 2) as int) >= @md_reguler, 1, 0)) = 1 AND NK_REGULER = 'IYA',
               'IYA', 'TIDAK')                                                                AS ELIGIBLE_REGULER,
           IIF((
                       ((IIF(CAST(LEFT(MASA_DINAS_LEVEL_JABATAN, 2) as int) >= @md_tinggi, 1, 0)) = 1 AND
                        NK_POTENSI_TINGGI = 'IYA')
                       OR
                       ((IIF(CAST(LEFT(MASA_DINAS_LEVEL_JABATAN, 2) as int) >= @md_reguler, 1, 0)) = 1 AND
                        NK_REGULER = 'IYA')
                   ), 'ELIGIBLE', 'BELUM')                                                    AS ELIGIBLE_COMBINE,
           KATEGORI_SISA_MD_OJK,
           PERIODE_PEMENUHAN,
           KETERANGAN,
           LEVEL_JABATAN_PEMENUHAN,
           KD_GOL,
           KD_BIDANG,
           KODE_SATKER_ORGANISASI,
           KD_JABATAN_STR
    FROM (
             SELECT a.NIP,
                    NM_PEG,
                    USIA_SAAT_INI,
                    EMAIL,
                    STS_PENERIMAAN_PEG,
                    ANGKATAN,
                    TAHUN_PEREKRUTAN,
                    STS_PENSIUN,
                    NM_STATUS_KEPEG,
                    NM_KANTOR,
                    KD_GOL,
                    NM_GOL                                                                                         AS LEVEL_JABATAN,
                    NM_JABATAN_STR                                                                                 AS NM_JABATAN,
                    POSISI                                                                                         AS FORMASI,
                    NAMA_FUNGSI                                                                                    AS KATEGORI_FUNGSI,
                    NM_UNIT_ES1                                                                                    AS BIDANG,
                    NM_UNIT_ES2                                                                                    AS DEPUTI_KOMISIONER,
                    NM_UNIT_ES3                                                                                    AS DEPARTEMEN,
                    NM_UNIT_ES4                                                                                    AS DIREKTUR,
                    NM_UNIT_ES5                                                                                    AS DEPUTI_DIREKTUR,
                    NM_UNIT_ES6                                                                                    AS KEPALA_BAGIAN,
                    NM_UNIT_ES7                                                                                    AS KEPALA_SUBBAGIAN,
                    SATUAN_KERJA,
                    KET_LEMBAGA_ASAL,
                    TMT_LEMBAGA_ASAL,
                    TMT_OJK,
                    TMT_GOL                                                                                        AS TMT_LEVEL_JABATAN,
                    TMT_JABATAN,
                    TMT_SATKER,
                    TMT_PENSIUN,
                    TGL_MASUK,
                    KD_PEND_UMUM,
                    NM_PEND_UMUM                                                                                   AS PENDIDIKAN,
                    PFM1.NILAI                                                                                     AS NILAI_KINERJA_1,
                    PFM1.LEVEL_KINERJA                                                                             AS LEVEL_KINERJA_1,
                    PFM2.NILAI                                                                                     AS NILAI_KINERJA_2,
                    PFM2.LEVEL_KINERJA                                                                             AS LEVEL_KINERJA_2,
                    PFM3.NILAI                                                                                     AS NILAI_KINERJA_3,
                    PFM3.LEVEL_KINERJA                                                                             AS LEVEL_KINERJA_3,
                    HUKUMAN,
                    MASA_TAHUN_OJK,
                    CASE WHEN b.lama_clto > 0
                            THEN dbo.MasaDinasCLTO(CAST(MASA_TAHUN_OJK as VARCHAR(2)) + ' Tahun ' + CAST(MASA_BULAN_OJK as VARCHAR(2)) + ' Bulan', b.lama_clto, '-')
                        ELSE CAST(MASA_TAHUN_OJK as VARCHAR(2)) + ' Tahun ' + CAST(MASA_BULAN_OJK as VARCHAR(2)) + ' Bulan'
                    END AS MASA_DINAS_OJK,
                    CASE WHEN b.lama_clto > 0
                        THEN dbo.MasaDinasCLTO(SISA_MASA_DINAS_OJK, b.lama_clto, '+')
                        ELSE SISA_MASA_DINAS_OJK
                    END AS SISA_MASA_DINAS_OJK,
                    CASE WHEN b.lama_clto > 0
                        THEN dbo.MasaDinasCLTO( CASE
                                    WHEN STS_PENSIUN = '0' THEN dbo.MasaDinasTahunBulanDataMart(TMT_GOL, @query_date)
                                    WHEN STS_PENSIUN = '1' THEN dbo.MasaDinasTahunBulanDataMart(TMT_GOL, TGL_BERHENTI)
                                END, b.lama_clto, '-')
                        ELSE CASE
                                WHEN STS_PENSIUN = '0' THEN dbo.MasaDinasTahunBulanDataMart(TMT_GOL, @query_date)
                                WHEN STS_PENSIUN = '1' THEN dbo.MasaDinasTahunBulanDataMart(TMT_GOL, TGL_BERHENTI)
                            END
                    END AS MASA_DINAS_LEVEL_JABATAN,
                    IIF(PFM2.NILAI >= @nk_tinggi AND PFM3.NILAI >= @nk_tinggi, 'IYA',
                        'TIDAK')                                                                                   AS NK_POTENSI_TINGGI,
                    IIF(PFM2.NILAI >= @nk_reguler AND PFM3.NILAI >= @nk_reguler, 'IYA',
                        'TIDAK')                                                                                   AS NK_REGULER,
                    CASE
                        WHEN DATEDIFF(mm, @query_date, TMT_PENSIUN) <= 12 THEN '0_1'
                        WHEN DATEDIFF(mm, @query_date, TMT_PENSIUN) <= 36 THEN '1_3'
                        WHEN DATEDIFF(mm, @query_date, TMT_PENSIUN) > 36 THEN '_3_'
                    END                                                                                            AS KATEGORI_SISA_MD_OJK,
                    @periode_pemenuhan                                                                             AS PERIODE_PEMENUHAN,
                    'GENERATE BY SYSTEM'                                                                           AS KETERANGAN,
                    @level_jabatan_pemenuhan                                                                       AS LEVEL_JABATAN_PEMENUHAN,
                    CREATE_DATE,
                    KD_UNIT_ES1                                                                                    AS KD_BIDANG,
                    KODE_SATKER_ORGANISASI,
                    KD_JABATAN_STR
             FROM db_mart.mart_profile_pegawai a
            LEFT JOIN (
                select nip, ISNULL(SUM((DATEDIFF(DAY, TGL_AWAL_CUTI_PEG, TGL_AKHIR_CUTI_PEG)/30)), 0) as lama_clto from SPG_CUTI_PEGAWAI where JNS_CUTI='16'
                group by nip
            ) b on a.nip=b.nip
                      OUTER APPLY(
                 SELECT PFMRK.PFM_ID,
                        PFMRK.NIP,
                        PFMRK.NILAI,
                        PFMRK.RANGKING,
                        PFMRK.LEVEL_KINERJA,
                        PFMPR.TANGGAL_AWAL,
                        PFMPR.TANGGAL_AKHIR
                 FROM PFM_RIWAYAT_KINERJA (NOLOCK) AS PFMRK
                          INNER JOIN PFM_PERIODE (NOLOCK) PFMPR ON PFMRK.PFM_ID = PFMPR.PFM_ID
                 WHERE PFMRK.NIP = a.NIP
                   AND PFMRK.PFM_ID = (
                     SELECT TOP 1 lag(PFM_ID, 2, 0) OVER (ORDER BY PFM_ID)
                     FROM PFM_RIWAYAT_KINERJA (NOLOCK)
                     GROUP BY PFM_ID
                     ORDER BY PFM_ID DESC
                 )
             ) AS PFM1
                      OUTER APPLY(
                 SELECT PFMRK.PFM_ID,
                        PFMRK.NIP,
                        PFMRK.NILAI,
                        PFMRK.RANGKING,
                        PFMRK.LEVEL_KINERJA,
                        PFMPR.TANGGAL_AWAL,
                        PFMPR.TANGGAL_AKHIR
                 FROM PFM_RIWAYAT_KINERJA (NOLOCK) AS PFMRK
                          INNER JOIN PFM_PERIODE (NOLOCK) PFMPR ON PFMRK.PFM_ID = PFMPR.PFM_ID
                 WHERE PFMRK.NIP = a.NIP
                   AND PFMRK.PFM_ID = (
                     SELECT TOP 1 lag(PFM_ID, 1, 0) OVER (ORDER BY PFM_ID)
                     FROM PFM_RIWAYAT_KINERJA (NOLOCK)
                     GROUP BY PFM_ID
                     ORDER BY PFM_ID DESC
                 )
             ) AS PFM2
                      OUTER APPLY
                  (
                      SELECT PFMRK.PFM_ID,
                             PFMRK.NIP,
                             PFMRK.NILAI,
                             PFMRK.RANGKING,
                             PFMRK.LEVEL_KINERJA,
                             PFMPR.TANGGAL_AWAL,
                             PFMPR.TANGGAL_AKHIR
                      FROM PFM_RIWAYAT_KINERJA (NOLOCK) AS PFMRK
                               INNER JOIN PFM_PERIODE PFMPR (NOLOCK) ON PFMRK.PFM_ID = PFMPR.PFM_ID
                      WHERE PFMRK.NIP = a.NIP
                        AND PFMRK.PFM_ID = (
                          select max(pfm_id)
                          from PFM_RIWAYAT_KINERJA (NOLOCK)
                      )
                  ) AS PFM3
            WHERE PEGAWAI_TUGAS_BELAJAR <> 'Ya' AND CUTI_CLTO <> 'Ya'
         ) AS PEG_ELIGIBLE
    WHERE CREATE_DATE = '2020-10-13'
      AND nip not like 'H%'
      AND nip not like 'P%'
      AND sts_pensiun = '0'
      AND kd_gol = (cast(@kd_gol as int) - 10)
      AND KD_PEND_UMUM >= @kd_pend_umum
      AND HUKUMAN = @sts_hukuman
      AND MASA_TAHUN_OJK >= @masa_dinas
      AND LEFT(SISA_MASA_DINAS_OJK, 1) >= @sisa_masa_dinas


    DELETE
    FROM MST_DAFTAR_PEGAWAI_AKTIF
    WHERE PERIODE_PEMENUHAN = @periode_pemenuhan
      AND KETERANGAN = 'GENERATE BY SYSTEM'
      AND LEVEL_JABATAN_PEMENUHAN = @level_jabatan_pemenuhan
      AND ELIGIBLE_COMBINE = 'BELUM'
END
GO


