
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Rekapitulasi Pegawai Eligible Yang Melamar Lowongan Jabatan
-- last modified by Pribadi Pamungkas 2020-10-18

CREATE PROCEDURE rep_pegawai_eligible_yang_melamar_lowongan_jabatan
	@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,
						melamar_80 SMALLINT NULL,
						selisih_80 SMALLINT NULL,
						eligible_70 SMALLINT NULL,
						melamar_70 SMALLINT NULL,
						selisih_70 SMALLINT NULL,
						eligible_60 SMALLINT NULL,
						melamar_60 SMALLINT NULL,
						selisih_60 SMALLINT NULL,
						eligible_50 SMALLINT NULL,
						melamar_50 SMALLINT NULL,
						selisih_50 SMALLINT NULL,
						eligible_40 SMALLINT NULL,
						melamar_40 SMALLINT NULL,
						selisih_40 SMALLINT NULL,
						eligible_30 SMALLINT NULL,
						melamar_30 SMALLINT NULL,
						selisih_30 SMALLINT NULL
					)

	INSERT INTO #hasil
		(
		    bidang,
		    eligible_80,
		    melamar_80,
		    selisih_80,
		    eligible_70,
		    melamar_70,
		    selisih_70,
		    eligible_60,
		    melamar_60,
		    selisih_60,
		    eligible_50,
		    melamar_50,
		    selisih_50,
		    eligible_40,
		    melamar_40,
		    selisih_40,
		    eligible_30,
		    melamar_30,
		    selisih_30
		)
	SELECT
		NM_UNIT_ORG,
		0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
	FROM 
		dbo.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,
		@jml_pelamar SMALLINT,
		@selisih SMALLINT

	DECLARE list_cursor CURSOR FOR  
	SELECT
		a.BIDANG,
		a.KD_GOL,
		--a.NM_GOL,
		a.KD_JABATAN,
		SUM(peg.eligible) AS eligible,
		SUM(g.jml_pelamar) AS jml_pelamar,
		SUM(peg.eligible) - SUM(g.jml_pelamar) AS selisih
	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
				dbo.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.REKOMENDASI_PIMPINAN = '1'	-- 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, @jml_pelamar, @selisih  

	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, melamar_' + @kd_gol + ' = @jml_pelamar, selisih_' + @kd_gol + ' = @selisih WHERE bidang = @bidang'
				EXEC sp_executesql @sql, N'@eligible SMALLINT, @jml_pelamar SMALLINT, @selisih SMALLINT, @bidang VARCHAR(100)', @eligible = @eligible, @jml_pelamar = @jml_pelamar, @selisih = @selisih, @bidang = @bidang
			END
	 

	  FETCH NEXT FROM list_cursor INTO @bidang, @kd_gol, @kd_jabatan, @eligible, @jml_pelamar, @selisih  
	END  

	CLOSE list_cursor  
	DEALLOCATE list_cursor 

	SELECT * FROM #hasil 

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

END
GO
