/*
 *	Insert login untuk Portal Member PT EDELMAN INDONESIA
 *	Request ticket RQ2018040384
 *	Script by Pribadi Pamungkas. 2018-04-20
*/

USE [GrpInsurance]

DECLARE @list_email TABLE ( 
						  no_peserta VARCHAR(12),
						  email VARCHAR(60)
					   )

INSERT INTO @list_email (no_peserta,email) VALUES ('2018003039-0','ketut.arthawan@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003002-0','ezta.wahyudi@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003000-0','aldila.mandalilka@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003049-0','pretty.wahyudi@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003053-0','yulia.irmarini@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003051-0','bernadette.duisters@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003060-0','azura.saphira@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003068-0','dion.kusmanto@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003010-0','satriyo.pamungkas@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003022-0','stephanie.kalandra@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003024-0','monica.triasoka@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003032-0','putri.adelia@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003030-0','rizka.lenggogini@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003055-0','nabila.khairunisa@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003056-0','safira.maulidina@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002980-0','rudijanto@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002989-0','ferra.handayani@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003005-0','annika.scott@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003025-0','waraney.rawung@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002994-0','abdullah.matzen@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003021-0','annisa.heningtyas@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003038-0','sabrina@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003050-0','evanggala.prasetya@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003035-0','dwi.probowati@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003069-0','dwi.lukita@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002988-0','toby.budiartono@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002973-0','masiatun.abdulhadi@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003006-0','muhammad.david@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003015-0','calvin.sidjaya@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003067-0','juliana.pratiwi@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003018-0','afrizal@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003004-0','fitriasari@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003003-0','rido.da''i@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003011-0','citra.festina@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003007-0','citra.sutomo@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003016-0','savitri@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003028-0','gatra.raditya@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003033-0','debby.cynthia@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003031-0','muhammad.hafidz@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003041-0','fadhel.pratama@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003045-0','alfia.nadhira@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003058-0','apriditia.ananda@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002982-0','rangga.nugraha@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003014-0','daniel.radjalado@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003048-0','tantri.beekelaar@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002993-0','bruce.poan@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003042-0','uria.simanjuntak@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003065-0','maisha.ardani@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003023-0','nabila.fachir@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003036-0','dwitya.basitha@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003012-0','ayesha.zulazmi@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003017-0','afra.irhami@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003019-0','andre.pianaung@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003029-0','risa.andiani@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003034-0','tiara.maharanie@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003040-0','dwinda.adrianto@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003044-0','theo.sibarani@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002992-0','intan.wibisono@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002995-0','misharati.israkhmellia@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003054-0','edwin.lengkei@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003059-0','marsha.putri@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003020-0','muhammad.gustiasa@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003061-0','laksmita.larasati@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002999-0','stelita.marsha@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002978-0','sefanya.maitimoe@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003027-0','simon.sibarani@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003046-0','riezki.setyana@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003071-0','shabilla.ghizani@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003070-0','jessica.nugroho@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002984-0','hartiasri.ariviani@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002979-0','gito.handoyo@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002981-0','rian.astuti@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002983-0','iqbal.saputra@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002987-0','agam.kurniawan@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002990-0','agnes.diah@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002986-0','prima.harrison@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002997-0','sari.selmelia@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018002998-0','yaniar.siagian@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003001-0','supriyanto.s@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003008-0','ratna.sari@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003009-0','dyah.ayu@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003013-0','jeni.bastiaan@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003026-0','eva.yasin@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003037-0','teddy.pratomo@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003043-0','jealy.vallerincia@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003047-0','intan.camellia@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003052-0','fiyarni.pamuntjak@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003057-0','jeneffer.joy@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003066-0','lutfi.yusniar@edelman.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003094-0','aditya.arnoldi@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003093-0','jamal.ahmad@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003085-0','akbar.yandi@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003091-0','christian.sidharta@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003104-0','christie.tobing@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003101-0','dandy.widiarko@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003106-0','david.liasdanu@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003076-0','desita.ulfa@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003097-0','eko.wahyu@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003083-0','felix.yogama@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003096-0','fransisca.natasya@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003099-0','hafizh.huda@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003089-0','elias.marendra@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003107-0','mariel.grazella@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003090-0','meryl.astari@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003081-0','michael.teja@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003084-0','nadia.zainuddin@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003080-0','nedy.prasetiyo@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003105-0','nobel.afiansyah@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003102-0','pratiwi.kinanti@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003087-0','prima.mustika@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003095-0','putri.sazanka@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003078-0','radityo.prabowo@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003082-0','rani.wisnuwardani@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003103-0','raras.cynanthia@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003092-0','reviana.veronica@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003086-0','rini.kusumawardhani@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003098-0','rizky.fuadhi@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003079-0','satrya.pinandita@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003077-0','sovia.siburian@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003088-0','tugana.pasaribu@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003108-0','vanessa.reinneke@zenogroup.com')
INSERT INTO @list_email (no_peserta,email) VALUES ('2018003100-0','veronika.sihombing@zenogroup.com')


DECLARE 
    @no_polis VARCHAR(11),
    @email VARCHAR(80),
    @no_peserta VARCHAR(12)

DECLARE list_cursor CURSOR FAST_FORWARD FOR  
SELECT
	'2018.0036.1',	-- No_Polis PT EDELMAN INDONESIA
	no_peserta,
	email
FROM
	@list_email

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_polis, @no_peserta, @email

WHILE @@FETCH_STATUS = 0  
BEGIN  
		IF NOT EXISTS(SELECT
					   *
				    FROM
					   dbo.custport_user_login_client
				    WHERE
					   username = @email)
			BEGIN
				DECLARE @id_user INT
				INSERT INTO [dbo].[custport_user_login_client]
					    ([username]
					    ,[password]
					    ,[no_polis]
					    ,[no_peserta]
					    ,[status]
					    ,[create_by]
					    ,[create_date]
					    ,[change_by]
					    ,[change_date])
					VALUES
					    (@email
					    ,NULL
					    ,@no_polis
					    ,@no_peserta
					    ,'G'
					    ,'SYS'
					    ,GETDATE()
					    ,'SYS'
					    ,GETDATE())
				SELECT @id_user = SCOPE_IDENTITY()
				INSERT INTO [dbo].[custport_user_login_client_polis]
					   ([create_by]
					   ,[create_date]
					   ,[change_by]
					   ,[change_date]
					   ,[is_deleted]
					   ,[id_user]
					   ,[no_polis]
					   ,[no_peserta]
					   ,[status])
				  VALUES
					   ('SYS'
					   ,GETDATE()
					   ,'SYS'
					   ,GETDATE()
					   ,0
					   ,@id_user
					   ,@no_polis
					   ,@no_peserta
					   ,'I')
			END

    FETCH NEXT FROM list_cursor INTO @no_polis, @no_peserta, @email
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 