USE [HRIS]
GO
/****** Object:  StoredProcedure [dbo].[rep_pegawai_eligible_yang_melamar]    Script Date: 11/4/2020 1:08:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[rep_pegawai_eligible_yang_melamar] @mpp_id VARCHAR(10) -- contoh 'MPP-000003'
AS
BEGIN

    SET NOCOUNT ON;-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.


    CREATE TABLE #hasil
    (
        bidang       VARCHAR(100) NULL,
        eligible_80  SMALLINT     NULL,
        internal_80  SMALLINT     NULL,
        eksternal_80 SMALLINT     NULL,
        eligible_70  SMALLINT     NULL,
        internal_70  SMALLINT     NULL,
        eksternal_70 SMALLINT     NULL,
        eligible_60  SMALLINT     NULL,
        internal_60  SMALLINT     NULL,
        eksternal_60 SMALLINT     NULL,
        eligible_50  SMALLINT     NULL,
        internal_50  SMALLINT     NULL,
        eksternal_50 SMALLINT     NULL,
        eligible_40  SMALLINT     NULL,
        internal_40  SMALLINT     NULL,
        eksternal_40 SMALLINT     NULL,
        eligible_30  SMALLINT     NULL,
        internal_30  SMALLINT     NULL,
        eksternal_30 SMALLINT     NULL
    )

    INSERT INTO #hasil
    (bidang,
     eligible_80,
     internal_80,
     eksternal_80,
     eligible_70,
     internal_70,
     eksternal_70,
     eligible_60,
     internal_60,
     eksternal_60,
     eligible_50,
     internal_50,
     eksternal_50,
     eligible_40,
     internal_40,
     eksternal_40,
     eligible_30,
     internal_30,
     eksternal_30)
    SELECT NM_UNIT_ORG,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0
    FROM SPG_08_UNIT_ORGANISASI
    WHERE KD_ESELON = '00'
      AND LEFT(KD_UNIT_ORG, 3) >= '101'
      AND LEFT(KD_UNIT_ORG, 3) <= '107'
    ORDER BY NO_URUT

    DECLARE
        @bidang VARCHAR(100),
        @kd_gol VARCHAR(2),
        @kd_jabatan VARCHAR(3),
        @eligible SMALLINT,
        @internal SMALLINT,
        @eksternal SMALLINT

    DECLARE list_cursor CURSOR FAST_FORWARD FOR
        SELECT a.BIDANG,
               a.KD_GOL,
               --a.NM_GOL,
               a.KD_JABATAN,
               SUM(peg.eligible)  AS eligible,
               SUM(g.jml_pelamar) AS internal,
               0                  AS eksternal
        FROM REC_RENCANA_PEMENUHAN_PEG a
                 INNER JOIN
             REC_PTK b ON
                 a.ID_FORMASI = b.ID_FORMASI
                 INNER JOIN
             REC_PERMINTAAN_ADVERTISING c ON
                 b.PTK_ID = c.REQ_ID

                 OUTER APPLY
             (
                 SELECT COUNT(*) AS eligible
                 FROM SPG_PEGAWAI_ELIGIBLE AS d
                 WHERE d.PERIODE_PEMENUHAN = a.MPP_ID
                   AND d.STS_PENSIUN = 0
                   AND d.KD_GOL = a.KD_GOL
                   AND d.KD_JABATAN_STR = a.KD_JABATAN -- ?
             ) AS peg
                 OUTER APPLY
             (
                 SELECT COUNT(*) AS jml_pelamar
                 FROM REC_PELAMAR_INTERNAL f
                 WHERE b.PTK_ID = f.REQ_ID
                   AND c.REQ_ID = f.REQ_ID
                   AND f.NIP_REKOMENDASI <> '' -- jika sudah melalui rekomendasi pimpinan
             ) g
        WHERE a.STATUS_KIRIM = 1
          AND a.STATUS_AKTIF = 1
          AND a.MPP_ID = @mpp_id
        GROUP BY LEFT(a.KD_UNIT_ORG, 3),
                 a.BIDANG,
                 a.KD_GOL,
                 --a.NM_GOL
                 a.KD_JABATAN
        ORDER BY LEFT(a.KD_UNIT_ORG, 3),
                 a.KD_GOL DESC;

    OPEN list_cursor
    FETCH NEXT FROM list_cursor INTO @bidang, @kd_gol, @kd_jabatan, @eligible, @internal, @eksternal

    WHILE @@FETCH_STATUS = 0
        BEGIN

            IF EXISTS(SELECT 1 FROM #hasil WHERE bidang = @bidang)
                BEGIN
                    DECLARE @sql nvarchar(1000)
                    SET @sql = 'UPDATE #hasil SET eligible_' + @kd_gol + ' = @eligible, internal_' + @kd_gol +
                               ' = @internal, eksternal_' + @kd_gol + ' = @eksternal WHERE bidang = @bidang'
                    EXEC sp_executesql @sql,
                         N'@eligible SMALLINT, @internal SMALLINT, @eksternal SMALLINT, @bidang VARCHAR(100)',
                         @eligible = @eligible, @internal = @internal, @eksternal = @eksternal, @bidang = @bidang
                END


            FETCH NEXT FROM list_cursor INTO @bidang, @kd_gol, @kd_jabatan, @eligible, @internal, @eksternal
        END

    CLOSE list_cursor
    DEALLOCATE list_cursor

    SELECT * FROM #hasil

    IF OBJECT_ID('tempdb..#hasil', 'U') IS NOT NULL
        DROP TABLE #hasil;

END