USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[Proc_Rep_Klaim_SMI_Summary]    Script Date: 31/05/2018 14:10:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- last modified by Pribadi Pamungkas. 2018-07-23 
-- (original query by Zainul Fahmi N.)
ALTER PROCEDURE [dbo].[Proc_Rep_Klaim_SMI_Summary]
	@pi_no_proposal VARCHAR(20)
AS
BEGIN

	SET NOCOUNT ON;	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

	DECLARE @temp_table_klaim_peserta TABLE (
		No_Prs VARCHAR(10) ,
		Nama_Perusahaan VARCHAR(100) ,
		No_Proposal VARCHAR(9) ,
		no_peserta_keluarga VARCHAR(12) ,
		Sub_Kelompok VARCHAR(10) ,
		Nama_Paket VARCHAR(50) ,
		Nama_Pendek VARCHAR(50) ,
		penggunaan_klaim FLOAT ,
		prod_cat VARCHAR(300)
	)

	INSERT INTO @temp_table_klaim_peserta
					( No_Prs ,
						Nama_Perusahaan ,
						No_Proposal ,
						no_peserta_keluarga ,
						Sub_Kelompok ,
						Nama_Paket ,
						Nama_Pendek ,
						penggunaan_klaim ,
						prod_cat
					)
	SELECT 
		prs.No_Prs,
		prs.Nama_Perusahaan ,
		PA.No_Proposal ,
		LEFT(p.No_Peserta, 10) AS no_peserta_keluarga ,
		kp.Sub_Kelompok ,
		ppol.Nama_Paket ,
		ppol.Nama_Pendek ,
		SUM(kd.Jml_Dibayar) AS penggunaan_klaim ,
		CASE
			WHEN kd.Kode_Santunan = 'RJK03' THEN 'Kacamata per tahun'
			WHEN kd.Kode_Santunan IN ('RJ0217','RJ0218') THEN 'Pembelian tanpa resep per tahun'
			ELSE 'Rawat Jalan'
		END AS prod_cat 	
	FROM 
		dbo.Polis_Asuransi AS pa
		INNER JOIN
		dbo.Perusahaan AS prs
		ON pa.No_Prs = prs.No_Prs
		INNER JOIN
		dbo.Peserta AS p
		ON pa.No_Proposal = p.No_Proposal
		INNER JOIN	
		dbo.Klaim_Peserta AS kp
		ON p.No_Proposal = kp.No_Proposal
			AND p.No_Peserta = kp.No_Peserta 
		INNER JOIN
		dbo.Klaim_Detail AS kd
		ON kp.No_Klaim = kd.No_Klaim	
		LEFT JOIN
		dbo.Item_Santunan AS ist
		ON kd.Kode_Santunan = ist.Kode_Item
		LEFT JOIN
		dbo.Peserta_Produk AS ppr
		ON pa.No_Proposal = ppr.No_Proposal
			AND p.No_Peserta = ppr.No_Peserta
			AND kp.Id_Produk = ppr.Id_Produk
		LEFT JOIN
		dbo.Produk_Polis AS ppol
		ON kp.Id_Produk = ppol.Id_Produk		
		LEFT JOIN
		dbo.Benefit_Polis AS bp
		ON kp.Id_Produk = bp.Id_Produk
			AND kd.Kode_Santunan = bp.Kode_Santunan
			AND ppr.Kelas = bp.Kelas			
	WHERE
		pa.No_Proposal = @pi_no_proposal	
		AND ppol.Nama_Pendek = 'R.Jalan'	
	GROUP BY 
		prs.No_Prs,
		prs.Nama_Perusahaan ,
		PA.No_Proposal ,
		LEFT(p.No_Peserta, 10) ,
		kp.Sub_Kelompok ,
		ppol.Nama_Paket ,
		ppol.Nama_Pendek ,
		CASE
			WHEN kd.Kode_Santunan = 'RJK03' THEN 'Kacamata per tahun'
			WHEN kd.Kode_Santunan IN ('RJ0217','RJ0218') THEN 'Pembelian tanpa resep per tahun'
			ELSE 'Rawat Jalan'
		END	
		
	--SELECT * FROM @temp_table_klaim_peserta


	-- untuk mendapatkan santunan yang 1M
	-- yang 1M di exclude...Confirmed by pak rudi...
	DECLARE @temp_table_peserta_santunan_1m TABLE (
		No_Proposal VARCHAR(20),
		Nama_Perusahaan VARCHAR(200),
		no_peserta_keluarga VARCHAR(20),	
		santunan VARCHAR(200),
		santunan_maksimum_rj FLOAT		
	)

	INSERT INTO @temp_table_peserta_santunan_1m
					( No_Proposal ,
						Nama_Perusahaan ,
						no_peserta_keluarga ,					
						santunan ,
						santunan_maksimum_rj
					)
			SELECT
				prd_pst_grp.No_Proposal ,
				prd_pst_grp.Nama_Perusahaan ,
				prd_pst_grp.no_peserta_keluarga ,			
				prd_pst_grp.santunan ,
				prd_pst_grp.santunan_maksimum_rj	
			FROM
				(	
					SELECT
						prd_pst.*
					FROM 
						(
							SELECT
								pa.No_Proposal ,
								prs.Nama_Perusahaan ,
								LEFT(p.No_Peserta, 10) AS no_peserta_keluarga,							
								ppr.Nama_Paket ,
								ppr.Nama_Pendek ,
								ppr.Sub_Kelompok ,
								'Rawat Jalan' AS santunan ,
								ppr.kelas ,
								ppr.Status ,							
								ppr.santunan_maksimum - bp.santunan_km_n_obat_tanpa_resep AS santunan_maksimum_rj	
							FROM 
								dbo.Polis_Asuransi AS pa
								INNER JOIN
								dbo.Perusahaan AS prs
								ON pa.No_Prs = prs.No_Prs
								INNER JOIN
								dbo.Peserta AS p
								ON pa.No_Proposal = p.No_Proposal	
								INNER JOIN
								(
									SELECT
										ppr.* ,
										ppol.Nama_Paket ,
										ppol.Nama_Pendek ,
										prempol.Benefit AS santunan_maksimum
									FROM
										dbo.Peserta_Produk AS ppr
										INNER JOIN
										dbo.Produk_Polis AS ppol
										ON ppr.Id_Produk = ppol.Id_Produk
										LEFT JOIN
										dbo.Premi_Polis AS prempol
										ON ppol.Id_Produk = prempol.Id_Produk		
											AND ppr.kelas = prempol.Kelas
								) AS ppr
								ON p.No_Proposal = ppr.No_Proposal
									AND p.No_Peserta = ppr.No_Peserta
								LEFT JOIN
								(SELECT Id_Produk, kelas, SUM(Benefit) AS santunan_km_n_obat_tanpa_resep FROM dbo.Benefit_Polis WHERE Kode_Santunan IN ('RJK03','RJ0217','RJ0218') GROUP BY Id_Produk, kelas) AS bp
								ON ppr.id_produk = bp.Id_Produk	
									 AND ppr.kelas = bp.kelas
							WHERE
								pa.No_Proposal = @pi_no_proposal
								AND p.Status <> 'C'
								AND ppr.Status <> 'C'
								AND ppr.Nama_Pendek = 'R.Jalan'										

							UNION ALL

							SELECT
								pa.No_Proposal ,
								prs.Nama_Perusahaan ,
								LEFT(p.No_Peserta, 10) AS no_peserta_keluarga,							
								ppr.Nama_Paket ,
								ppr.Nama_Pendek ,
								ppr.Sub_Kelompok ,
								ist.Nama_Santunan AS santunan ,
								ppr.kelas ,
								ppr.Status ,
								bp.Benefit
							FROM 
								dbo.Polis_Asuransi AS pa
								INNER JOIN
								dbo.Perusahaan AS prs
								ON pa.No_Prs = prs.No_Prs
								INNER JOIN
								dbo.Peserta AS p
								ON pa.No_Proposal = p.No_Proposal	
								INNER JOIN
								(
									SELECT
										ppr.* ,
										ppol.Nama_Paket ,
										ppol.Nama_Pendek ,
										prempol.Benefit AS santunan_maksimum
									FROM
										dbo.Peserta_Produk AS ppr
										INNER JOIN
										dbo.Produk_Polis AS ppol
										ON ppr.Id_Produk = ppol.Id_Produk
										LEFT JOIN
										dbo.Premi_Polis AS prempol
										ON ppol.Id_Produk = prempol.Id_Produk		
											AND ppr.kelas = prempol.Kelas
								) AS ppr
								ON p.No_Proposal = ppr.No_Proposal
									AND p.No_Peserta = ppr.No_Peserta
								LEFT JOIN
								(SELECT * FROM dbo.Benefit_Polis WHERE Kode_Santunan IN ('RJK03','RJ0217','RJ0218')) AS bp
								ON ppr.id_produk = bp.Id_Produk	
									 AND ppr.kelas = bp.kelas
								LEFT JOIN
								dbo.Item_Santunan AS ist
								ON bp.Kode_Santunan = ist.Kode_Item	 
							WHERE
								pa.No_Proposal = @pi_no_proposal
								AND p.Status <> 'C'
								AND ppr.Status <> 'C'
								AND ppr.Nama_Pendek = 'R.Jalan'										
						) AS prd_pst
				)	AS prd_pst_grp
			WHERE
				prd_pst_grp.santunan_maksimum_rj >= 1000000000
			GROUP BY 
				prd_pst_grp.No_Proposal ,
				prd_pst_grp.Nama_Perusahaan ,
				prd_pst_grp.no_peserta_keluarga ,			
				prd_pst_grp.santunan ,
				prd_pst_grp.santunan_maksimum_rj	



	DECLARE @temp_summary_klaim TABLE (
		No_Proposal VARCHAR(20),
		Nama_Perusahaan VARCHAR(200),
		no_peserta_keluarga VARCHAR(20),
		peserta_sebagai_karyawan VARCHAR(200),
		santunan_maksimum_rj FLOAT,
		penggunaan_klaim 	FLOAT
	)

	INSERT INTO @temp_summary_klaim
					( No_Proposal ,
						Nama_Perusahaan ,
						no_peserta_keluarga ,
						peserta_sebagai_karyawan ,
						santunan_maksimum_rj ,
						penggunaan_klaim 
					)
	SELECT
		prd_pst_final.No_Proposal ,
		prd_pst_final.Nama_Perusahaan ,
		prd_pst_final.no_peserta_keluarga ,
		p.Nama AS peserta_sebagai_karyawan ,
		SUM(prd_pst_final.santunan_maksimum_rj) AS santunan_maksimum_rj ,
		SUM(COALESCE(temp_klaim.penggunaan_klaim, 0)) AS penggunaan_klaim 	
	FROM 
		(
			SELECT
				prd_pst_grp.No_Proposal ,
				prd_pst_grp.Nama_Perusahaan ,
				prd_pst_grp.no_peserta_keluarga ,			
				prd_pst_grp.santunan ,
				prd_pst_grp.santunan_maksimum_rj	
			FROM
				(	
					SELECT
						prd_pst.*
					FROM 
						(
							SELECT
								pa.No_Proposal ,
								prs.Nama_Perusahaan ,
								LEFT(p.No_Peserta, 10) AS no_peserta_keluarga,							
								ppr.Nama_Paket ,
								ppr.Nama_Pendek ,
								ppr.Sub_Kelompok ,
								'Rawat Jalan' AS santunan ,
								ppr.kelas ,
								ppr.Status ,							
								ppr.santunan_maksimum - bp.santunan_km_n_obat_tanpa_resep AS santunan_maksimum_rj	
							FROM 
								dbo.Polis_Asuransi AS pa
								INNER JOIN
								dbo.Perusahaan AS prs
								ON pa.No_Prs = prs.No_Prs
								INNER JOIN
								dbo.Peserta AS p
								ON pa.No_Proposal = p.No_Proposal	
								INNER JOIN
								(
									SELECT
										ppr.* ,
										ppol.Nama_Paket ,
										ppol.Nama_Pendek ,
										--prempol.Benefit AS santunan_maksimum
										ppr.Curr_Santunan AS santunan_maksimum
									FROM
										dbo.Peserta_Produk AS ppr
										INNER JOIN
										dbo.Produk_Polis AS ppol
										ON ppr.Id_Produk = ppol.Id_Produk
										LEFT JOIN
										dbo.Premi_Polis AS prempol
										ON ppol.Id_Produk = prempol.Id_Produk		
											AND ppr.kelas = prempol.Kelas
								) AS ppr
								ON p.No_Proposal = ppr.No_Proposal
									AND p.No_Peserta = ppr.No_Peserta
								LEFT JOIN
								(SELECT Id_Produk, kelas, SUM(Benefit) AS santunan_km_n_obat_tanpa_resep FROM dbo.Benefit_Polis WHERE Kode_Santunan IN ('RJK03','RJ0217','RJ0218') GROUP BY Id_Produk, kelas) AS bp
								ON ppr.id_produk = bp.Id_Produk	
									 AND ppr.kelas = bp.kelas
							WHERE
								pa.No_Proposal = @pi_no_proposal
								AND p.Status <> 'C'
								AND ppr.Status <> 'C'
								AND ppr.Nama_Pendek = 'R.Jalan'										

							UNION ALL

							SELECT
								pa.No_Proposal ,
								prs.Nama_Perusahaan ,
								LEFT(p.No_Peserta, 10) AS no_peserta_keluarga,							
								ppr.Nama_Paket ,
								ppr.Nama_Pendek ,
								ppr.Sub_Kelompok ,
								ist.Nama_Santunan AS santunan ,
								ppr.kelas ,
								ppr.Status ,
								bp.Benefit
							FROM 
								dbo.Polis_Asuransi AS pa
								INNER JOIN
								dbo.Perusahaan AS prs
								ON pa.No_Prs = prs.No_Prs
								INNER JOIN
								dbo.Peserta AS p
								ON pa.No_Proposal = p.No_Proposal	
								INNER JOIN
								(
									SELECT
										ppr.* ,
										ppol.Nama_Paket ,
										ppol.Nama_Pendek ,
										--prempol.Benefit AS santunan_maksimum
										ppr.Curr_Santunan AS santunan_maksimum
									FROM
										dbo.Peserta_Produk AS ppr
										INNER JOIN
										dbo.Produk_Polis AS ppol
										ON ppr.Id_Produk = ppol.Id_Produk
										LEFT JOIN
										dbo.Premi_Polis AS prempol
										ON ppol.Id_Produk = prempol.Id_Produk		
											AND ppr.kelas = prempol.Kelas
								) AS ppr
								ON p.No_Proposal = ppr.No_Proposal
									AND p.No_Peserta = ppr.No_Peserta
								LEFT JOIN
								(SELECT * FROM dbo.Benefit_Polis WHERE Kode_Santunan IN ('RJK03','RJ0217','RJ0218')) AS bp
								ON ppr.id_produk = bp.Id_Produk	
									 AND ppr.kelas = bp.kelas
								LEFT JOIN
								dbo.Item_Santunan AS ist
								ON bp.Kode_Santunan = ist.Kode_Item	 
							WHERE
								pa.No_Proposal = @pi_no_proposal
								AND p.Status <> 'C'
								AND ppr.Status <> 'C'
								AND ppr.Nama_Pendek = 'R.Jalan'										
						) AS prd_pst
				)	AS prd_pst_grp
			GROUP BY 
				prd_pst_grp.No_Proposal ,
				prd_pst_grp.Nama_Perusahaan ,
				prd_pst_grp.no_peserta_keluarga ,			
				prd_pst_grp.santunan ,
				prd_pst_grp.santunan_maksimum_rj	
				
		) AS prd_pst_final		
		LEFT JOIN
		@temp_table_klaim_peserta AS temp_klaim
		ON prd_pst_final.No_Proposal = temp_klaim.no_proposal
			AND prd_pst_final.santunan = temp_klaim.prod_cat
			AND prd_pst_final.no_peserta_keluarga = temp_klaim.no_peserta_keluarga		
		LEFT JOIN
		(SELECT * FROM dbo.Peserta WHERE RIGHT(No_Peserta, 1) = '0') AS p
		ON prd_pst_final.no_proposal = p.no_proposal 
			AND prd_pst_final.no_peserta_keluarga = LEFT(p.No_Peserta, 10)	
	WHERE
		prd_pst_final.no_peserta_keluarga NOT IN (SELECT no_peserta_keluarga FROM @temp_table_peserta_santunan_1m)
	GROUP BY 	
		prd_pst_final.No_Proposal ,
		prd_pst_final.Nama_Perusahaan ,
		prd_pst_final.no_peserta_keluarga ,
		p.Nama 

			
		
	SELECT
		No_Proposal ,
		Nama_Perusahaan ,
		no_peserta_keluarga ,
		peserta_sebagai_karyawan ,
		santunan_maksimum_rj ,
		penggunaan_klaim ,
		santunan_maksimum_rj - penggunaan_klaim AS sisa_santunan 
	FROM 
		@temp_summary_klaim 
	ORDER BY  
		--(santunan_maksimum_rj - penggunaan_klaim) ASC
		no_peserta_keluarga
	
END
