USE [OJKRecruitmentDB]
GO
/****** Object:  Trigger [dbo].[trig_insert_kandidat]    Script Date: 28/12/2020 13:46:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




-- Trigger tabel temporary penampungan dari file excel berisi list kandidat
-- by Pribadi Pamungas 2020-11-11

ALTER TRIGGER [dbo].[trig_insert_kandidat]
   ON  [dbo].[Temp_kandidat]
   AFTER INSERT
AS 
BEGIN
	
	SET NOCOUNT ON;-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

	DECLARE
		@PRF_ID varchar(50),
		@Nama varchar(200), 
		@Email varchar(200), 
		@Universitas varchar(400), 
		@timestamp datetime,
		@created_by varchar(50),
		@created_date datetime,
		@po_message varchar(400)

	SET @created_by = 'System'
	SET @created_date  = SYSDATETIME()

	SELECT 
		@PRF_ID = LTRIM(RTRIM(COALESCE(PRF_ID, ''))), 
		@Nama = LTRIM(RTRIM(COALESCE(Nama, ''))), 
		@Email = LOWER(LTRIM(RTRIM(COALESCE(Email, '')))), 
		@Universitas = LTRIM(RTRIM(COALESCE(Universitas, ''))), 
		@timestamp = [timestamp]
	FROM 
		inserted;

	IF @Nama = '' GOTO CLEAN_OUT
	IF @Email = '' GOTO CLEAN_OUT

	--IF NOT EXISTS(SELECT 1 FROM dbo.Login WHERE LOWER(EMAIL) = @Email)
		BEGIN

			BEGIN TRY

				-- begin: generate password acak --
				DECLARE	-- konfigurasi untuk string acak
					@min_length tinyint = 6,		-- panjang karakter minimal
					@max_length tinyint = 6,		-- panjang karakter maksimal
					@char_pool varchar(100) = 'abcdefghijkmnopqrstuvwxyz1234567890'	-- karakter yang dihasilkan

				DECLARE
					@random_string varchar(100),
					@pool_length smallint,
					@loop_count smallint,
					@string_length smallint

				SET @string_length = RAND() * (@max_length - @min_length + 1) + @min_length
				SET @pool_length = LEN(@char_pool)

				SET @loop_count = 0
				SET @random_string = ''

				WHILE (@loop_count < @string_length) BEGIN
				    SELECT @random_string = @random_string + 
					   SUBSTRING(@char_pool, CONVERT(int, RAND() * @pool_length) + 1, 1)
				    SELECT @loop_count = @loop_count + 1
				END
				-- end: generate password acak --

				-- generate GUID
				DECLARE @log_id uniqueidentifier = NEWID()

				BEGIN TRANSACTION proses_import

					-- insert ke tabel Login
					INSERT INTO dbo.Login
					(
						LOG_ID, 
						NAMA, 
						EMAIL, 
						PASSWORD, 
						ROLE, 
						AKSES, 
						STS_AKTIF, 
						AKTIF_DATE, 
						CRE_BY, 
						CRE_TM
					)
					VALUES
					(
						@log_id, 
						@Nama, 
						@Email, 
						SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', @random_string)), 3, 32),
						'USER', 
						'PELAMAR', 
						0, 
						NULL, 
						@created_by, 
						@created_date
					)
					IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
						BEGIN
							SET @po_message = 'Failed insert Login'
							GOTO ERROR_OUT
						END

					-- insert ke tabel Profile_Pelamar
					INSERT INTO dbo.Profile_Pelamar
					(
						PRF_ID, 
						LOG_ID, 
						NM_PEG, 
						EMAIL, 
						NM_LEMB_PEND_FORM, 
						CRE_BY, 
						CRE_TM
					)
					VALUES
					(
						@PRF_ID, 
						@log_id, 
						@Nama, 
						@Email, 
						@Universitas, 
						@created_by, 
						@created_date
					)
					IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
						BEGIN
							SET @po_message = 'Failed insert Profile_Pelamar'
							GOTO ERROR_OUT
						END

					-- insert ke tabel Profile_Pendidikan
					INSERT INTO dbo.Profile_Pendidikan
					(
						PRF_ID, 
						NM_LEMB_PEND_FORM, 
						CRE_BY, 
						CRE_TM
					)
					VALUES
					(
						@PRF_ID, 
						@Universitas, 
						@created_by, 
						@created_date
					)
					IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
						BEGIN
							SET @po_message = 'Failed insert Profile_Pendidikan'
							GOTO ERROR_OUT
						END

					-- insert ke tabel Profile_Login
					INSERT INTO dbo.Profile_Login
					(
						LOG_ID,
						PRF_ID,
						NM_PEG,
						EMAIL,
						PASSWORD_MD5,
						PASSWORD_GEN,
						NM_LEMB_PEND_FORM,
						CRE_BY, 
						CRE_TM
					)
					VALUES
					(
						@log_id,
						@PRF_ID,
						@Nama,
						@Email,
						SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', @random_string)), 3, 32),
						@random_string,
						@Universitas, 
						@created_by, 
						@created_date
					)
					IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
						BEGIN
							SET @po_message = 'Failed insert Profile_Login'
							GOTO ERROR_OUT
						END

					-- Email notifikasi
					DECLARE
						@email_subject varchar(200) = 'Informasi Akun Rekrutmen PCS OJK',
						@email_body nvarchar(max)
					SET @email_body ='<p>Kepada Sdr. '+@Nama+',<br>
						'+@Universitas+'.</p>
						<p>Sehubungan dengan Rekrutmen <i>Talent Scouting</i> Calon Staf Otoritas Jasa Keuangan (PCS 5), 
						<br>dengan ini diberitahukan bahwa Saudara telah diajukan oleh '+@Universitas+' sebagai Peserta untuk mengikuti seleksi rekrutmen tersebut.
						<br>Selanjutnya Saudara diminta untuk segera menyampaikan data diri dan dokumen pendukung yang <b>wajib</b> diupload berupa:
						<br>
						<br>1.	<i>Scan</i> Surat Lamaran;
						<br>2.	<i>Scan</i> Curriculum Vitae/Riwayat Hidup;
						<br>3.	<i>Scan</i> pas foto berwarna dengan latar belakang merah;
						<br>4.	<i>Scan</i> Kartu Tanda Penduduk (KTP);
						<br>5.	<i>Scan</i> Ijazah S1 dan/atau S2;
						<br>6.	<i>Scan</i> Transkrip Nilai S1 dan/atau S2;
						<br>7.	<i>Scan</i> surat keputusan/dokumen keanggotaan organisasi di Perguruan Tinggi dan/atau sosial kemasyarakatan;
						<br>8.	<i>Scan</i> sertifikat Bahasa Inggris berskala internasional yang masih berlaku, yaitu IELTS (min. 6) /TOEFL iBT (min. 61)/TOEFL ITP (min. 500);
						<br>9.	<i>Scan</i> Surat Keterangan Catatan Kepolisian (SKCK) yang masih berlaku;
						<br>10.	<i>Scan</i> sertifikat atau piagam penghargaan tingkat regional/nasional/internasional (optional);
						<br>11.	<i>Scan</i> dokumen lain yang mendukung (optional),
						<br>
						<br>melalui website sebagai berikut:
						</p>
						<table>
							<tr>
								<td>Website</td>
								<td>:</td>
								<td>https://recruitment.ojk.go.id/</td>
							</tr>
							<tr>
								<td>Username</td>
								<td>:</td>
								<td>'+@Email+'</td>
							</tr>
							<tr>
								<td>Password</td>
								<td>:</td>
								<td>'+@random_string+'</td>
							</tr>
						</table>
						<p>Batas waktu penyampaian data dimaksud paling lambat tanggal <b>29 Desember 2020 pukul 15.00 WIB</b>.<br>
						<br>Jika sampai batas waktu tersebut data tidak kami terima sesuai dengan persyaratan tersebut di atas, maka kandidat akan dianggap gugur dalam tahapan seleksi administrasi.<br>
						<br>Demikian informasi ini disampaikan, terima kasih atas perhatian dan kerjasamanya.</p>
						<br>
						<p>Salam,</p>
						<p>Panitia Rekrutmen OJK</p>'
					INSERT INTO dbo.Notifikasi_Email
					(
						type,
						PRF_ID,
						email_to, 
						email_subject, 
						email_body, 
						queue, 
						onprocess, 
						sent, 
						sent_date
					)
					VALUES
					(
						'login',
						@PRF_ID,
						@Email, 
						@email_subject, 
						@email_body, 
						1, 
						0, 
						0, 
						NULL
					);
					IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
						BEGIN
							SET @po_message = 'Failed insert Notifikasi_Email'
							GOTO ERROR_OUT
						END


					-- update status ke diri sendiri
					UPDATE 
						t
					SET 
						inserted_to_login = 1
					FROM 
						Temp_kandidat AS t
						INNER JOIN 
						inserted i ON t.PRF_ID = i.PRF_ID
								    AND t.Nama = i.Nama
								    AND t.Email = i.Email
								    AND t.Universitas = i.Universitas
								    AND t.timestamp = i.timestamp
					WHERE
						i.timestamp = @timestamp


				COMMIT TRANSACTION proses_import
				SET @po_message = 'Sukses'
				GOTO CLEAN_OUT

			END TRY
			BEGIN CATCH	-- tangkap error message selain yg sudah dideskripsikan
				SET @po_message = LEFT(ERROR_MESSAGE(),400)	-- berhubung variable diatas diset 400, maka potong error message
				GOTO ERROR_OUT
			END CATCH


			ERROR_OUT: 
				ROLLBACK TRANSACTION proses_import
				--RETURN 1

			CLEAN_OUT:
				--RETURN 0

		END

END
