USE [HRIS]
GO
/****** Object:  StoredProcedure [dbo].[rep_lowongan_jabatan_dan_pegawai_eligible_per_bidang]    Script Date: 27/11/2020 20:19:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Rekapitulasi Lowongan Jabatan dan Pegawai Eligible Per Bidang
-- by Pribadi Pamungkas 2020-10-18
-- last modified by Pribadi Pamungkas 2020-11-27 => tambah filter AND LEFT(d.KD_BIDANG,3) = LEFT(suo.KD_UNIT_ORG,3)

ALTER PROCEDURE [dbo].[rep_lowongan_jabatan_dan_pegawai_eligible_per_bidang]
	@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,
						lowjab_90 SMALLINT NULL,
						eligible_90 SMALLINT NULL,
						selisih_90 SMALLINT NULL,
						lowjab_80 SMALLINT NULL,
						eligible_80 SMALLINT NULL,
						selisih_80 SMALLINT NULL,
						lowjab_70 SMALLINT NULL,
						eligible_70 SMALLINT NULL,
						selisih_70 SMALLINT NULL,
						lowjab_60 SMALLINT NULL,
						eligible_60 SMALLINT NULL,
						selisih_60 SMALLINT NULL,
						lowjab_50 SMALLINT NULL,
						eligible_50 SMALLINT NULL,
						selisih_50 SMALLINT NULL,
						lowjab_40 SMALLINT NULL,
						eligible_40 SMALLINT NULL,
						selisih_40 SMALLINT NULL,
						lowjab_30 SMALLINT NULL,
						eligible_30 SMALLINT NULL,
						selisih_30 SMALLINT NULL
					)

	INSERT INTO #hasil
		(
		    bidang,
		    lowjab_90,
		    eligible_90,
		    selisih_90,
		    lowjab_80,
		    eligible_80,
		    selisih_80,
		    lowjab_70,
		    eligible_70,
		    selisih_70,
		    lowjab_60,
		    eligible_60,
		    selisih_60,
		    lowjab_50,
		    eligible_50,
		    selisih_50,
		    lowjab_40,
		    eligible_40,
		    selisih_40,
		    lowjab_30,
		    eligible_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,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),
		@jml SMALLINT,
		@eligible SMALLINT,
		@selisih SMALLINT

	DECLARE list_cursor CURSOR FAST_FORWARD FOR  
	SELECT
		suo.NM_UNIT_ORG,
		lowjab.KD_GOL,
		lowjab.KD_JABATAN,
		lowjab.jml,
		peg.eligible,
		peg.eligible - lowjab.jml
	FROM 
		dbo.SPG_08_UNIT_ORGANISASI suo 
		CROSS APPLY
		(
			SELECT 
				a.MPP_ID,
				LEFT(a.KD_UNIT_ORG, 3) AS bid,
				a.KD_GOL,
				a.KD_JABATAN,
				SUM(b.JUMLAH) AS jml
			FROM 
				dbo.REC_RENCANA_PEMENUHAN_PEG AS a
				INNER JOIN
				dbo.REC_PTK AS b ON a.ID_FORMASI = b.ID_FORMASI
				LEFT JOIN
				dbo.REC_PERMINTAAN_ADVERTISING AS c ON b.PTK_ID = c.REQ_ID
			WHERE
			    a.STATUS_KIRIM = 1
			    AND a.STATUS_AKTIF = 1
			    AND LEFT(a.KD_UNIT_ORG, 3) = LEFT(suo.KD_UNIT_ORG, 3)
			    --AND kd_kantor = '100000000000000'	--?
			    AND c.JAM_SELESAI IS NOT NULL	-- sudah posting
			GROUP BY
				a.MPP_ID,
				LEFT(a.KD_UNIT_ORG, 3),
				a.KD_GOL,
				a.KD_JABATAN
		) AS lowjab
		OUTER APPLY
		(
			SELECT
				COUNT(*) AS eligible
			FROM
				dbo.SPG_PEGAWAI_ELIGIBLE AS d
			WHERE
				d.PERIODE_PEMENUHAN = lowjab.MPP_ID
				AND d.STS_PENSIUN = 0
				AND d.KD_GOL = lowjab.KD_GOL
				AND d.KD_JABATAN_STR = lowjab.KD_JABATAN	-- ?
				AND LEFT(d.KD_BIDANG,3) = LEFT(suo.KD_UNIT_ORG,3)
		) AS peg
	WHERE 
		suo.KD_ESELON = '00' 
		AND LEFT(suo.KD_UNIT_ORG,3) >= '101' 
		AND LEFT(suo.KD_UNIT_ORG,3) <= '107' 
		AND lowjab.MPP_ID = @mpp_id
	ORDER BY
		LEFT(suo.KD_UNIT_ORG,3),
		lowjab.KD_GOL DESC;

	OPEN list_cursor  
	FETCH NEXT FROM list_cursor INTO @bidang, @kd_gol, @kd_jabatan, @jml, @eligible, @selisih  

	WHILE @@FETCH_STATUS = 0  
	BEGIN  

		IF EXISTS(SELECT 1 FROM #hasil WHERE bidang = @bidang)
			BEGIN
				IF EXISTS (SELECT * FROM TempDB.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'lowjab_' + @kd_gol AND TABLE_NAME LIKE '#hasil%')
					BEGIN
						DECLARE @sql nvarchar(1000)
						SET @sql = 'UPDATE #hasil SET lowjab_' + @kd_gol + ' = @jml, eligible_' + @kd_gol + ' = @eligible, selisih_' + @kd_gol + ' = @selisih WHERE bidang = @bidang'
						EXEC sp_executesql @sql, N'@jml SMALLINT, @eligible SMALLINT, @selisih SMALLINT, @bidang VARCHAR(100)', @jml = @jml, @eligible = @eligible, @selisih = @selisih, @bidang = @bidang
					END
			END
	 

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

	CLOSE list_cursor  
	DEALLOCATE list_cursor 

	SELECT * FROM #hasil 

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