SELECT * FROM custport_klaim WHERE no_klaim = 'C1710000008'
SELECT * FROM custport_from_aai WHERE received_rawdata LIKE '%C1710000015%'
SELECT * FROM custport_from_aai WHERE received_rawdata LIKE '%151708%'
SELECT * FROM custport_from_aai 
SELECT * FROM Peserta WHERE Nama LIKE '%Vincent Francois Fernandez%'
SELECT * FROM Peserta_handphone WHERE No_Peserta = '2017007377-0'
SELECT * FROM custport_user_login_client WHERE no_polis = '2017.0031.0' username = 'vincent.fernandez@gt-tires.com'
SELECT * FROM Polis_Asuransi WHERE No_Proposal = '2017.0070'
--------------------------
SELECT * FROM Peserta WHERE No_Peserta IN ( '2016221212-0', '2016221228-0' )
SELECT * FROM custport_user_login_client WHERE no_peserta IN ( '2016221212-0', '2016221228-0' )
SELECT * FROM custport_user_login_client_polis WHERE id_user IN (99,100)
--------------------------

---- get polis yg saat ini ada di portal, kecuali polis equity (polis equity hanya digunakan untuk testing saja) ---- 
DECLARE @list_polis TABLE ( no_polis VARCHAR(12))
INSERT INTO @list_polis ( no_polis )
SELECT DISTINCT
    c.no_polis--,
    --prs.Nama_Perusahaan
FROM 
    custport_user_login_client AS c INNER JOIN 
    Polis_Asuransi AS pa ON c.no_polis = pa.No_Polis INNER JOIN 
    Perusahaan prs ON pa.No_Prs = prs.No_Prs
WHERE
    c.no_polis <> '1992.0011.0' -- EQUITY LIFE INDONESIA, PT


---- proses utama ---- 
DECLARE 
    @no_polis VARCHAR(11),
    @email VARCHAR(80),
    @no_peserta VARCHAR(12),
    @status VARCHAR(1),
    @tgl_lapse DATETIME
DECLARE list_cursor CURSOR FAST_FORWARD FOR  
SELECT
    list.no_polis,
    LOWER(hp.email),
    pst.No_Peserta,
    pst.Status,
    pst.Tgl_Lapse
FROM 
    @list_polis AS list INNER JOIN
    Peserta_handphone AS hp ON list.no_polis = hp.No_Polis INNER JOIN
    Polis_Asuransi AS pa ON hp.No_Polis = pa.No_Polis INNER JOIN
    Peserta AS pst ON pa.No_Proposal = pst.No_Proposal
				  AND hp.No_Peserta = pst.No_Peserta
WHERE
    pst.Sebagai = 0	-- hanya karyawan saja
    AND pst.Status <> 'G'
    AND pa.Status = 'I' -- polis aktif

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

WHILE @@FETCH_STATUS = 0  
BEGIN  
    IF EXISTS(SELECT
			 *
		    FROM
			 custport_user_login_client
		    WHERE
			 no_polis = @no_polis
			 AND no_peserta = @no_peserta)
	   BEGIN	 -- jika sudah ada di portal, maka
		  -- cek status peserta
		  IF (@status = 'O')  -- jika lapse
			 BEGIN
				PRINT 'No Peserta: '+@no_peserta+' No Polis: '+@no_polis+' ['+@email+'] lapse tanggal '+CAST(CAST(@tgl_lapse AS DATE) AS VARCHAR(30))
				---- Jika peserta sudah lapse 2 bulan keatas, maka non-aktifkan di portal
				---- Tapi ini perlu dikonfirmasikan terlebih dahulu
				--IF (DATEDIFF(month,@tgl_lapse,GETDATE()) >= 2)
				--    UPDATE
				--	   custport_user_login_client
				--    SET
				--	   status = 'C'
				--    WHERE
				--	   no_polis = @no_polis
				--	   AND no_peserta = @no_peserta
			 END
		  ELSE IF (@status = 'C')
			 BEGIN
				PRINT 'No Peserta '+@no_peserta+' No Polis: '+@no_polis+' ['+@email+'] cancel'
				---- Jika peserta cancel, maka non-aktifkan di portal
				---- Tapi ini perlu dikonfirmasikan terlebih dahulu
				--IF (DATEDIFF(month,@tgl_lapse,GETDATE()) >= 2)
				--    UPDATE
				--	   custport_user_login_client
				--    SET
				--	   status = 'C'
				--    WHERE
				--	   no_polis = @no_polis
				--	   AND no_peserta = @no_peserta
			 END

		  -- cek alamat email berubah gak
		  IF NOT EXISTS(SELECT
					   *
					 FROM
					   custport_user_login_client
					 WHERE
					   no_polis = @no_polis
					   AND no_peserta = @no_peserta
					   AND username = @email)
			 BEGIN
				PRINT 'No Peserta '+@no_peserta+' No Polis: '+@no_polis+' ['+@email+'] alamat emailnya beda'
				---- Jika alamat email berbeda, apakah alamat email di portal mengikuti alamat email di data peserta group insurance?
				---- Perlu didiskusikan lebih lanjut
			 END
	   END
    ELSE
	   BEGIN	 -- jika belum ada di portal, maka insert
		  PRINT @email
		  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'	  -- status belum aktivasi
				--    ,'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, @email, @no_peserta, @status, @tgl_lapse  
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 

