CREATE TABLE #hasil  ( 
	PRF_ID VARCHAR(50),
	NM_PEG VARCHAR(60),
	NM_KELAMIN_PEG VARCHAR(50),
	NM_AGAMA VARCHAR(20),

	NM_PEND_UMUM_1 VARCHAR(50),
	NM_LEMB_PEND_FORM_1 VARCHAR(250),
	FAKULTAS_1 VARCHAR(200),
	JURUSAN_1 VARCHAR(200),
	NILAI_1 VARCHAR(50),
	STATUS_PT_1 VARCHAR(50),

	NM_PEND_UMUM_2 VARCHAR(50),
	NM_LEMB_PEND_FORM_2 VARCHAR(250),
	FAKULTAS_2 VARCHAR(200),
	JURUSAN_2 VARCHAR(200),
	NILAI_2 VARCHAR(50),
	STATUS_PT_2 VARCHAR(50),

	NM_PEND_UMUM_3 VARCHAR(50),
	NM_LEMB_PEND_FORM_3 VARCHAR(250),
	FAKULTAS_3 VARCHAR(200),
	JURUSAN_3 VARCHAR(200),
	NILAI_3 VARCHAR(50),
	STATUS_PT_3 VARCHAR(50),

	NM_PEND_UMUM_4 VARCHAR(50),
	NM_LEMB_PEND_FORM_4 VARCHAR(250),
	FAKULTAS_4 VARCHAR(200),
	JURUSAN_4 VARCHAR(200),
	NILAI_4 VARCHAR(50),
	STATUS_PT_4 VARCHAR(50),

	NM_PEND_UMUM_5 VARCHAR(50),
	NM_LEMB_PEND_FORM_5 VARCHAR(250),
	FAKULTAS_5 VARCHAR(200),
	JURUSAN_5 VARCHAR(200),
	NILAI_5 VARCHAR(50),
	STATUS_PT_5 VARCHAR(50)
)

DECLARE 
	@PRF_ID VARCHAR(50),
	@NM_PEG VARCHAR(60),
	@NM_KELAMIN_PEG VARCHAR(50),
	@NM_AGAMA VARCHAR(20),
	@NM_PEND_UMUM VARCHAR(50),
	@NM_LEMB_PEND_FORM VARCHAR(250),
	@FAKULTAS VARCHAR(200),
	@JURUSAN VARCHAR(200),
	@NILAI VARCHAR(50),
	@STATUS_PT VARCHAR(50),
	@urut TINYINT

DECLARE list_cursor CURSOR FOR 
	SELECT
		pp.PRF_ID,
		pp.NM_PEG,
		mk.NM_KELAMIN_PEG,
		ma.NM_AGAMA,
		--pend.KD_PEND_UMUM,
		pend.NM_PEND_UMUM,
		pend.NM_LEMB_PEND_FORM,
		pend.FAKULTAS,
		pend.JURUSAN,
		pend.NILAI,
		pend.STATUS_PT,
		ROW_NUMBER() OVER(PARTITION BY pp.PRF_ID ORDER BY pp.PRF_ID, pend.KD_PEND_UMUM, pend.TAHUN_LULUS) AS urut
	FROM
		Profile_Pelamar AS pp LEFT JOIN
		Master_Kelamin AS mk ON pp.JNS_KELAMIN_PEG = mk.JNS_KELAMIN_PEG LEFT JOIN
		Master_Agama AS ma ON pp.KD_AGAMA = ma.KD_AGAMA LEFT JOIN
		(
			SELECT
				pnd.PRF_ID,
				pnd.KD_PEND_UMUM,
				mp.NM_PEND_UMUM,
				pnd.NM_LEMB_PEND_FORM,
				pnd.FAKULTAS,
				pnd.JURUSAN,
				pnd.NILAI,
				mu.STATUS,
				pnd.NEGARA,
				CASE
					WHEN pnd.NEGARA = 'INDONESIA' THEN mu.STATUS
					ELSE CASE 
							WHEN COALESCE(mu.STATUS, '') <> '' THEN mu.STATUS
							ELSE 'Luar Negeri'
						END
				END AS STATUS_PT,
				pnd.TAHUN_LULUS
			FROM
				(
					SELECT *
					FROM Profile_Pendidikan
					UNION ALL
					SELECT *
					FROM Profile_Pendidikan_Riwayat
				) AS pnd LEFT JOIN
				Master_Pendidikan AS mp ON pnd.KD_PEND_UMUM = mp.KD_PEND_UMUM LEFT JOIN
				Master_Universitas AS mu ON pnd.NM_LEMB_PEND_FORM = mu.UNIVERSITAS
			--ORDER BY
			--	pnd.PRF_ID,
			--	pnd.KD_PEND_UMUM,
			--	pnd.TAHUN_LULUS
		) AS pend ON pp.PRF_ID = pend.PRF_ID
	WHERE 
		pp.STS_SUBMIT = 1
		AND pp.PRF_ID <> '000'
		AND pp.CRE_TM >= '2020-12-28'
	ORDER BY
		pp.PRF_ID,
		pend.KD_PEND_UMUM,
		pend.TAHUN_LULUS

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @PRF_ID, @NM_PEG, @NM_KELAMIN_PEG, @NM_AGAMA, @NM_PEND_UMUM, @NM_LEMB_PEND_FORM, @FAKULTAS, @JURUSAN, @NILAI, @STATUS_PT, @urut

WHILE @@FETCH_STATUS = 0  
BEGIN  

	DECLARE @urut_str VARCHAR(2)
	SET @urut_str = CAST(@urut AS VARCHAR(2))
	DECLARE @sql nvarchar(1000)
	IF NOT EXISTS(SELECT 1 FROM #hasil WHERE PRF_ID = @PRF_ID)
		BEGIN
			SET @sql = 'INSERT INTO #hasil (PRF_ID, NM_PEG, NM_KELAMIN_PEG,NM_AGAMA, NM_PEND_UMUM_' + @urut_str + ', NM_LEMB_PEND_FORM_' + @urut_str + ', FAKULTAS_' + @urut_str + ', JURUSAN_' + @urut_str + ', NILAI_' + @urut_str + ', STATUS_PT_' + @urut_str + ') VALUES (@PRF_ID, @NM_PEG, @NM_KELAMIN_PEG, @NM_AGAMA, @NM_PEND_UMUM, @NM_LEMB_PEND_FORM, @FAKULTAS, @JURUSAN, @NILAI, @STATUS_PT)'
			EXEC sp_executesql @sql, N'@NM_PEG VARCHAR(60), @NM_KELAMIN_PEG VARCHAR(50), @NM_AGAMA VARCHAR(20), @NM_PEND_UMUM VARCHAR(50), @NM_LEMB_PEND_FORM VARCHAR(250), @FAKULTAS VARCHAR(200), @JURUSAN VARCHAR(200),	@NILAI VARCHAR(50), @STATUS_PT VARCHAR(50), @PRF_ID VARCHAR(50)', @NM_PEG=@NM_PEG, @NM_KELAMIN_PEG=@NM_KELAMIN_PEG, @NM_AGAMA=@NM_AGAMA, @NM_PEND_UMUM=@NM_PEND_UMUM, @NM_LEMB_PEND_FORM=@NM_LEMB_PEND_FORM, @FAKULTAS=@FAKULTAS, @JURUSAN=@JURUSAN, @NILAI=@NILAI, @STATUS_PT=@STATUS_PT, @PRF_ID=@PRF_ID
		END
	ELSE
		BEGIN
			SET @sql = 'UPDATE #hasil SET NM_PEND_UMUM_' + @urut_str + ' = @NM_PEND_UMUM, NM_LEMB_PEND_FORM_' + @urut_str + ' = @NM_LEMB_PEND_FORM, FAKULTAS_' + @urut_str + ' = @FAKULTAS, JURUSAN_' + @urut_str + ' = @JURUSAN, NILAI_' + @urut_str + ' = @NILAI, STATUS_PT_' + @urut_str + ' = @STATUS_PT WHERE PRF_ID = @PRF_ID'
			EXEC sp_executesql @sql, N'@NM_PEND_UMUM VARCHAR(50), @NM_LEMB_PEND_FORM VARCHAR(250), @FAKULTAS VARCHAR(200), @JURUSAN VARCHAR(200),	@NILAI VARCHAR(50), @STATUS_PT VARCHAR(50), @PRF_ID VARCHAR(50)', @NM_PEND_UMUM=@NM_PEND_UMUM, @NM_LEMB_PEND_FORM=@NM_LEMB_PEND_FORM, @FAKULTAS=@FAKULTAS, @JURUSAN=@JURUSAN, @NILAI=@NILAI, @STATUS_PT=@STATUS_PT, @PRF_ID=@PRF_ID
		END


	FETCH NEXT FROM list_cursor INTO @PRF_ID, @NM_PEG, @NM_KELAMIN_PEG, @NM_AGAMA, @NM_PEND_UMUM, @NM_LEMB_PEND_FORM, @FAKULTAS, @JURUSAN, @NILAI, @STATUS_PT, @urut
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 

SELECT
	h.*,
	pp.[TEMPAT_LAHIR_PEG],
pp.[TGL_LAHIR_PEG],
mk.[NM_KABUPATEN] + ' ' + mp.[NM_PROPINSI],
--org.jml_org,
CASE WHEN COALESCE(org.jml_org,0)>0 THEN 'Ada' ELSE 'Tidak Ada' END AS ORGANISASI,
bhs.SERTIFIKAT,
sbhs.TGL_BAHASA,
sbhs.LEMB_PENYELENGGARA,
sbhs.SCORE,
CASE WHEN COALESCE(pres.jml_pres,0)>0 THEN 'Ada' ELSE 'Tidak Ada' END AS PRESTASI,
wis.STS_WISUDA,
wis.JADWAL_WISUDA,
pp.[HANDPHONE_1],
pp.[HANDPHONE_2],
pp.[EMAIL]
FROM  
	#hasil AS h 
	INNER JOIN 
	Profile_Pelamar AS pp ON h.PRF_ID = pp.PRF_ID
	LEFT JOIN 
	Master_Kabupaten AS mk ON pp.KD_KABUPATEN_DOMISILI = mk.KD_KABUPATEN AND pp.KD_PROPINSI_DOMISILI = mk.KD_PROPINSI
	LEFT JOIN 
	Master_Propinsi AS mp ON pp.KD_PROPINSI_DOMISILI = mp.KD_PROPINSI
	LEFT JOIN
	(
		SELECT
			PRF_ID,
			COUNT(*) AS jml_org
		FROM
			Profile_Organisasi
		GROUP BY
			PRF_ID
	) AS org ON pp.PRF_ID = org.PRF_ID
	LEFT JOIN
	(
		SELECT [PRF_ID],
			  LEFT(Main.Students,Len(Main.Students)-1) AS SERTIFIKAT
		FROM
		    (
			   SELECT DISTINCT ST2.[PRF_ID], 
				  (
					 SELECT [SERTIFIKASI] + ', ' AS [text()]
					 --FROM [OJKRecruitmentDB].[dbo].[Profile_Penguasaan_Bahasa] ST1
					 FROM (SELECT DISTINCT PRF_ID, SERTIFIKASI FROM Profile_Penguasaan_Bahasa)  ST1
					 WHERE ST1.[PRF_ID] = ST2.[PRF_ID]
					 ORDER BY ST1.[PRF_ID]
					 FOR XML PATH ('')
				  ) [Students]
			   FROM [OJKRecruitmentDB].[dbo].[Profile_Penguasaan_Bahasa]  ST2
		    ) [Main]
	) AS bhs ON pp.PRF_ID = bhs.PRF_ID
	OUTER APPLY
	(
		SELECT TOP 1
			TGL_BAHASA,
			LEMB_PENYELENGGARA,
			SCORE
		FROM
			Profile_Penguasaan_Bahasa AS ppb
		WHERE
			pp.PRF_ID = ppb.PRF_ID
		ORDER BY
			TGL_BAHASA DESC
	) AS sbhs
	LEFT JOIN
	(
		SELECT
			PRF_ID,
			COUNT(*) AS jml_pres
		FROM
			Profile_Penghargaan_Prestasi
		GROUP BY
			PRF_ID
	) AS pres ON pp.PRF_ID = pres.PRF_ID
	OUTER APPLY
	(
		SELECT TOP 1
			CASE WHEN pnd.STS_WISUDA = 1 THEN 'Sudah' WHEN pnd.STS_WISUDA = 2 THEN 'Belum' END AS STS_WISUDA,
			CASE WHEN pnd.STS_WISUDA = 2 THEN convert(varchar, pnd.TGL_WISUDA, 23) ELSE '-' END AS JADWAL_WISUDA
		FROM
			(
				SELECT *
				FROM Profile_Pendidikan AS pp1
				WHERE pp.PRF_ID = pp1.PRF_ID
				UNION ALL
				SELECT *
				FROM Profile_Pendidikan_Riwayat AS pp2
				WHERE pp.PRF_ID = pp2.PRF_ID
			) AS pnd
		ORDER BY
			pnd.TAHUN_LULUS DESC
	) AS wis

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

