USE [HRIS]
GO
/****** Object:  StoredProcedure [dbo].[rep_pelamar_sebelum_rekomendasi_lowjab]    Script Date: 18/04/2022 09:48:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- by Pribadi Pamungkas 2021-06-17
--ALTER PROCEDURE [dbo].[rep_pelamar_sebelum_rekomendasi_lowjab]
DECLARE
	@mpp_id     VARCHAR(10) = 'MPP-000002', -- contoh 'MPP-000001'
	@tgl_posisi DATE = CAST(GETDATE() AS DATE),       	-- contoh '2021-06-17'
	@nama_bidang VARCHAR(100),
	@kd_gol VARCHAR(2),
	@filter TINYINT		-- 0: all, 1: belum ada pelamar, 2: ada pelamar
--AS
--BEGIN
	SET NOCOUNT ON;	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

	--DECLARE @kd_bidang VARCHAR(3)
	--SELECT
	--	@kd_bidang = LEFT(KD_UNIT_ORG,3)
	--FROM
	--	dbo.SPG_08_UNIT_ORGANISASI
	--WHERE
	--	KD_ESELON = '00'
	--	--AND NM_UNIT_ORG = @nama_bidang
	--	AND (NM_UNIT_ORG = @nama_bidang OR ISNULL(@nama_bidang, '') = '')

	SELECT
		lowjab.bid,
		lowjab.satuan_kerja,
		lowjab.kantor,
		lowjab.NM_GOLONGAN,
		lowjab.posisi_jabatan,
		--SUM(COALESCE(terisi.id_formasi_terisi,0)) AS pelamar,
		lowjab.ID_FORMASI,
		lowjab.NM_ROOM,
		lowjab.DAMPAK,
		--SUM(lowjab.jumlah) AS jumlah
		--COUNT(lowjab.jumlah) AS jumlah
		jml_pelamar.*,
		COALESCE(jml_pelamar_rekomendasi.jml_pelamar_rekomendasi, 0) AS jml_pelamar_rekomendasi,
		COALESCE(jml_pelamar_terkirim.jml_pelamar_terkirim, 0) AS jml_pelamar_terkirim
	FROM
		(
			select
				r.ID_FORMASI,
				LEFT(r.KD_UNIT_ORG, 3) AS bid,
				CASE
					WHEN COALESCE(d.nama_satker_organisasi, '') = '' THEN CASE
																	WHEN COALESCE(f.nm_unit_org, '') = '' THEN g.nm_unit_org
																	ELSE f.nm_unit_org
																END
					ELSE d.nama_satker_organisasi
				END AS satuan_kerja,
				c.nm_kantor as kantor,
				j.posisi as posisi_jabatan,
				a.JUMLAH AS jumlah,
				h.NM_GOLONGAN,
				h.KD_GOL,
				--COALESCE(CAST(room.ID AS VARCHAR(10)), '') AS ID_ROOM
				room.ID AS ID_ROOM,
				room.NM_ROOM,
				COALESCE(room.DAMPAK,r.DAMPAK,'') AS DAMPAK
				--b.req_id,a.ptk_id,d.kode_satker_organisasi as kd_satker,d.nama_satker_organisasi as satker,f.nm_unit_org as depkom,m.masa_dinas_minimal,m.sisa_dinas,m.tanggal_dinas, g.nm_unit_org as bidang,n.NM_PEND_UMUM,
				--isnull(n.KD_PEND_UMUM,0) as kd_pend_umum, a.kd_jabatan,b.tanggal_publish,b.tanggal_selesai,b.jam_selesai,m.kd_gol,a.mpp_id,m.tanggal_buat,h.NM_GOLONGAN,
				--m.nilai_kinerja as nilai1, m.nilai_kinerja_1 as nilai2, m.nilai_kinerja_2 as nilai3,
				--DATEADD(day, 0, DATEDIFF(day, 0, b.tanggal_selesai)) + DATEADD(day, 0 - DATEDIFF(day, 0, b.jam_selesai), b.jam_selesai) as closing_date, d.KD_UNIT_ES1, a.id_formasi
			from 
				dbo.REC_RENCANA_PEMENUHAN_PEG AS r INNER JOIN
				dbo.REC_PTK AS a ON r.ID_FORMASI = a.ID_FORMASI
				left join rec_permintaan_advertising b ON a.ptk_id=b.req_id
				left join SPG_08_KANTOR c on a.kd_kantor=c.kd_kantor
				left join spg_08_unit_organisasi d on a.kd_unit_org=d.kd_unit_org
				left JOIN SPG_08_UNIT_ORGANISASI e on d.KD_UNIT_ES3=e.KD_UNIT_ORG
				left JOIN SPG_08_UNIT_ORGANISASI f on d.KD_UNIT_ES2=f.KD_UNIT_ORG
				left JOIN SPG_08_UNIT_ORGANISASI g on d.KD_UNIT_ES1=g.KD_UNIT_ORG
				left join SPG_08_JABATAN_UNIT j on (a.KD_JABATAN=j.KD_JABATAN and a.KD_UNIT_ORG=j.KD_UNIT_ORG)
				left join SPG_08_JABATAN_STR k on a.kd_jabatan=k.KD_JABATAN_STR
				left join SPG_JABATAN_PANGKAT l on a.kd_jabatan=l.KD_JABATAN_STR
				left join REC_PERSYARATAN m on (m.KD_GOL=l.KD_GOL and m.periode=a.mpp_id)
				left join spg_golongan h on m.kd_gol=h.kd_gol
				left join spg_eselon i on h.KD_ESELON=i.KD_ESELON
				--left join SPG_JNS_PEND_UMUM n on m.PENDIDIKAN = n.KD_PEND_UMUM
				INNER JOIN
				dbo.SPG_08_UNIT_ORGANISASI AS suo ON LEFT(r.KD_UNIT_ORG, 3) = LEFT(suo.KD_UNIT_ORG, 3)
				LEFT JOIN
				dbo.REC_ROOM_FORMASI_FUNCTIONAL AS room ON a.ID_FORMASI = room.ID_FORMASI
			where 
				b.ram_id='RAM-000020' and b.rac_id='RAC-000001'
				AND r.MPP_ID = @mpp_id
				--AND LEFT(r.KD_UNIT_ORG, 3) = @kd_bidang
				AND suo.KD_ESELON = '00'
				AND (suo.NM_UNIT_ORG = @nama_bidang OR ISNULL(@nama_bidang, '') = '')

				AND r.STATUS_KIRIM = 1
				AND r.STATUS_AKTIF = 1
				AND b.JAM_SELESAI IS NOT NULL	-- sudah posting
				AND (r.KD_GOL = @kd_gol OR ISNULL(@kd_gol, '') = '')
		) AS lowjab
		--OUTER APPLY
		--(
		--	SELECT 
		--		--COUNT(d.id_formasi) AS id_formasi_terisi
		--		d.NIP,
		--		d.NM_PEG,
		--		d.PRIORITAS--,
		--		--d.[NILAI_KINERJA_1],
		--		--d.[NILAI_KINERJA_2],
		--		--d.[NILAI_KINERJA_3],
		--		--d.[NILAI_KINERJA_TOTAL]
		--	FROM
		--		dbo.REC_PELAMAR_INTERNAL AS d
		--		CROSS APPLY
		--		(
		--			SELECT 
		--				e.ID_PELAMAR 
		--			FROM 
		--				dbo.REC_PELAMAR_AKTIFITAS AS e
		--			WHERE
		--				d.ID = e.ID_PELAMAR
		--				AND e.STS_PELAMAR = 0
		--				AND CAST(e.TGL_BUAT_AKTIFITAS AS DATE) <= @tgl_posisi
		--				--AND CAST(e.TGL_BUAT_AKTIFITAS AS DATE) >= @tgl_posisi
		--			GROUP BY 
		--				e.ID_PELAMAR
		--		) AS c
		--	WHERE
		--		COALESCE(d.TGL_REKOMENDASI_PIMPINAN, GETDATE()) >= @tgl_posisi
		--		AND d.id_formasi = lowjab.id_formasi
		--		AND (d.ID_ROOM = lowjab.ID_ROOM OR lowjab.ID_ROOM IS NULL) 
		--	--GROUP BY
		--	--	d.id_formasi
		--) AS terisi
		OUTER APPLY
		(
			SELECT 
				COUNT(f.id_formasi) AS jml_pelamar
			FROM
				dbo.REC_PELAMAR_INTERNAL AS f
			WHERE
				--COALESCE(f.TGL_REKOMENDASI_PIMPINAN, GETDATE()) >= @tgl_posisi
				f.id_formasi = lowjab.id_formasi
				--AND COALESCE(CAST(f.ID_ROOM AS VARCHAR(10)), '-') = lowjab.ID_ROOM
				AND (f.ID_ROOM = lowjab.ID_ROOM OR lowjab.ID_ROOM IS NULL) 
			GROUP BY
				f.id_formasi,
				f.ID_ROOM
		) AS jml_pelamar
		OUTER APPLY
		(
			SELECT 
				COUNT(g.id_formasi) AS jml_pelamar_rekomendasi
			FROM
				dbo.REC_PELAMAR_INTERNAL AS g
			WHERE
				--COALESCE(g.TGL_REKOMENDASI_PIMPINAN, GETDATE()) >= @tgl_posisi
				g.TGL_REKOMENDASI_PIMPINAN IS NOT NULL
				AND g.id_formasi = lowjab.id_formasi
				--AND COALESCE(CAST(f.ID_ROOM AS VARCHAR(10)), '-') = lowjab.ID_ROOM
				AND (g.ID_ROOM = lowjab.ID_ROOM OR lowjab.ID_ROOM IS NULL) 
			GROUP BY
				g.id_formasi,
				g.ID_ROOM
		) AS jml_pelamar_rekomendasi
		OUTER APPLY
		(
			SELECT 
				COUNT(h.id_formasi) AS jml_pelamar_terkirim
			FROM
				dbo.REC_PELAMAR_INTERNAL AS h
			WHERE
				--COALESCE(g.TGL_REKOMENDASI_PIMPINAN, GETDATE()) >= @tgl_posisi
				h.TGL_REKOMENDASI_PIMPINAN IS NOT NULL
				AND h.id_formasi = lowjab.id_formasi
				--AND COALESCE(CAST(f.ID_ROOM AS VARCHAR(10)), '-') = lowjab.ID_ROOM
				AND (h.ID_ROOM = lowjab.ID_ROOM OR lowjab.ID_ROOM IS NULL) 
			GROUP BY
				h.id_formasi,
				h.ID_ROOM
		) AS jml_pelamar_terkirim
	--WHERE
		--lowjab.satuan_kerja IN ('Departemen Keuangan', 'Departemen Pengelolaan Sistem Informasi', 'Grup Pengembangan Aplikasi Sistem Informasi')
		--terisi.PRIORITAS = 2
		--AND jml_pelamar.jml_pelamar = jml_prioritas_kedua.jml_prioritas_kedua
		--COALESCE(jml_pelamar.jml_pelamar,0) = 0
		--jml_prioritas_kedua.jml_prioritas_kedua
	--GROUP BY
	--	lowjab.bid,
	--	lowjab.satuan_kerja,
	--	lowjab.kantor,
	--	lowjab.NM_GOLONGAN,
	--	lowjab.KD_GOL,
	--	lowjab.posisi_jabatan,
	--	lowjab.ID_FORMASI,
	--	lowjab.NM_ROOM,
	--	lowjab.DAMPAK
	--HAVING
	--	(SUM(COALESCE(terisi.id_formasi_terisi,0)) = 0 OR ISNULL(@filter, 0) <> 1)
	--	AND (SUM(COALESCE(terisi.id_formasi_terisi,0)) > 0 OR ISNULL(@filter, 0) <> 2)
	ORDER BY
	--	lowjab.KD_GOL DESC,
		lowjab.bid,
		lowjab.satuan_kerja,
		lowjab.posisi_jabatan,
		lowjab.NM_ROOM
--END

--select * from rec_pelamar_internal where tanggal_apply >= '2022-04-14'