USE [GrpInsurance]
GO

DECLARE @hasil TABLE ( 
						no_polis VARCHAR(11),
						nama_perusahaan VARCHAR(100),
						email VARCHAR(100)
					)

DECLARE 
	@no_polis VARCHAR(11),
	@nama_perusahaan VARCHAR(100),
	@email VARCHAR(200)

DECLARE list_polis CURSOR FAST_FORWARD FOR  
SELECT DISTINCT
  pa.No_Polis,
  prs.Nama_Perusahaan,
  prs.Email3
FROM
  dbo.Polis_Asuransi AS pa
  INNER JOIN 
  dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
  (
	SELECT DISTINCT
		pa1.No_Proposal
	FROM
		Polis_Asuransi AS pa1 INNER JOIN
		Produk_Polis AS pp ON pa1.No_Proposal = pp.No_Proposal
	WHERE
		LEFT(pp.Sub_Kelompok,2) = '03'
  ) AS askes ON pa.No_Proposal = askes.No_Proposal
WHERE
  pa.Status = 'I'
  AND pa.Active_Ya = 1
  AND COALESCE(prs.Email3,'') <> ''
  AND pa.Mata_Uang = '01'
  --AND prs.GT_OG = 'GT'
  AND prs.GT_OG = 'OG'
  --AND pa.AdMedika_Ya = 1
  AND pa.show_card_ya = 1
ORDER BY
  prs.Nama_Perusahaan

OPEN list_polis  
FETCH NEXT FROM list_polis INTO @no_polis, @nama_perusahaan, @email

WHILE @@FETCH_STATUS = 0  
BEGIN  

	DECLARE @delimiter_fix VARCHAR(100)
	SET @delimiter_fix = REPLACE(REPLACE(REPLACE(@email,'/',';'),'\',';'),',',';');

	INSERT INTO @hasil ( no_polis, nama_perusahaan, email )
	SELECT @no_polis, @nama_perusahaan, LOWER(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(item,CHAR(9),' '),CHAR(10),' '),CHAR(13),' '),CHAR(32),' ')))) FROM dbo.func_explode_index (@delimiter_fix, ';') WHERE COALESCE(item,'') <> ''
	
	FETCH NEXT FROM list_polis INTO @no_polis, @nama_perusahaan, @email
END  

CLOSE list_polis  
DEALLOCATE list_polis 


SELECT 
	no_polis,
	nama_perusahaan,
	email,
	CASE 
		WHEN (email LIKE '%_@__%.__%' AND PATINDEX('%[^a-z,0-9,@,.,_,\-]%', email) = 0) THEN 'Valid'
		ELSE 'Not valid'
	END AS validasi
FROM 
	@hasil
ORDER BY
	nama_perusahaan,
	no_polis,
	email

