USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[Proc_Rep_Klaim_SMI_Detail]    Script Date: 24/05/2018 10:11:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- by Pribadi Pamungkas. 2018-02-15 (original query by Zainul Fahmi N.)
--ALTER PROCEDURE [dbo].[Proc_Rep_Klaim_SMI_Detail]
DECLARE
	@pi_no_proposal VARCHAR(20) = '2017.0599'
--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

	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		
	)

	-- untuk mendapatkan santunan yang 1M
	-- yang 1M di exclude...Confirmed by pak rudi...
	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.Id_Produk ,
								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.Id_Produk ,
								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_final_results TABLE (
		No_Proposal VARCHAR(20) ,
		Nama_Perusahaan VARCHAR(255) ,
		urutan INT ,
		no_peserta_keluarga VARCHAR(20) ,
		peserta_sebagai_karyawan VARCHAR(255) ,
		santunan VARCHAR(255) ,
		santunan_maksimum_rj float ,
		penggunaan_klaim FLOAT ,
		sisa_santunan_rj FLOAT,
		curr_santunan_rj FLOAT
	)

	INSERT INTO @temp_final_results
					( No_Proposal ,
					   Nama_Perusahaan ,
					   urutan ,
					   no_peserta_keluarga ,
					   peserta_sebagai_karyawan ,
					   santunan ,
					   santunan_maksimum_rj ,
					   penggunaan_klaim ,
					   sisa_santunan_rj,
					   curr_santunan_rj
					)
	SELECT
		prd_pst_final.No_Proposal ,
		prd_pst_final.Nama_Perusahaan ,
		ROW_NUMBER() OVER (PARTITION BY prd_pst_final.no_peserta_keluarga ORDER BY prd_pst_final.no_peserta_keluarga ASC , prd_pst_final.santunan DESC) AS urutan ,
		prd_pst_final.no_peserta_keluarga ,	
		'',--p.Nama AS peserta_sebagai_karyawan ,	
		prd_pst_final.santunan ,
		prd_pst_final.santunan_maksimum_rj ,
		0,--COALESCE(temp_klaim.penggunaan_klaim, 0) AS penggunaan_klaim,
		0,--prd_pst_final.santunan_maksimum_rj - COALESCE(temp_klaim.penggunaan_klaim, 0) AS sisa_santunan_rj,
		prd_pst_final.curr_santunan_rj
	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,
				0 AS curr_santunan_rj--prd_pst_grp.curr_santunan_rj
			FROM
				(	
					SELECT
						prd_pst.*
					FROM 
						(
							SELECT
								'' AS No_Proposal ,
								'' AS Nama_Perusahaan ,
								'' AS no_peserta_keluarga,							
								'' AS Nama_Paket ,
								'' AS Nama_Pendek ,
								'' AS Sub_Kelompok ,
								'Rawat Jalan' AS santunan ,
								'' AS kelas ,
								'C' AS Status ,							
								0 AS santunan_maksimum_rj,
								0 AS curr_santunan_rj
							--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,
							--	ppr.santunan_maksimum - ROUND(bp.santunan_km_n_obat_tanpa_resep * ppr.Prop_Santunan,0) AS santunan_maksimum_rj,
							--	--ppr.santunan_maksimum AS santunan_maksimum_rj,
							--	ppr.Curr_Santunan AS curr_santunan_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
							--			--ROUND(prempol.Benefit * ppr.Prop_Santunan,0) 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
							--	--(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
							--	--(SELECT Id_Produk, kelas, SUM(ROUND(Benefit * ppr.Prop_Santunan,0)) 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
							--	(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,
								ppr.Prop_Santunan,
								--ROUND(bp.Benefit * ppr.Prop_Santunan,0) AS Benefit,
								ppr.Curr_Santunan AS curr_santunan_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
										pp.* ,
										ppol.Nama_Paket ,
										ppol.Nama_Pendek ,
										prempol.Benefit AS santunan_maksimum
										--ppr.Curr_Santunan AS santunan_maksimum
									FROM
										dbo.Peserta_Produk AS pp
										INNER JOIN
										dbo.Produk_Polis AS ppol
										ON pp.Id_Produk = ppol.Id_Produk
										LEFT JOIN
										dbo.Premi_Polis AS prempol
										ON ppol.Id_Produk = prempol.Id_Produk		
											AND pp.kelas = prempol.Kelas
								) AS ppr
								ON p.No_Proposal = ppr.No_Proposal
									--AND p.No_Peserta = ppr.No_Peserta
									AND LEFT(p.No_Peserta,11)+'0' = 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--,
				--prd_pst_grp.curr_santunan_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)	
	ORDER BY 
		prd_pst_final.no_peserta_keluarga ASC ,
		prd_pst_final.santunan DESC 

SELECT * FROM @temp_final_results where no_peserta_keluarga = '2017000635'
--SELECT 
--    no_peserta_keluarga,
--    SUM(sisa_santunan_rj) 
--FROM 
--    @temp_final_results 
--WHERE 
--    santunan<>'Rawat Jalan'
--GROUP BY
--    no_peserta_keluarga

SELECT
    No_Proposal ,
    Nama_Perusahaan ,
    CASE 
	   WHEN urutan = 1 THEN rj1.no_peserta_keluarga
	   ELSE ''
    END AS no_peserta_keluarga ,		
    CASE 
	   WHEN urutan = 1 THEN peserta_sebagai_karyawan
	   ELSE ''
    END AS peserta_sebagai_karyawan ,
    santunan ,
    --santunan_maksimum_rj,
    --prop_santunan,
    CASE urutan
	   --WHEN 1 THEN ROUND((santunan_maksimum_rj + penambah) * prop_santunan,0)
	   WHEN 1 THEN curr_santunan_rj
	   ELSE santunan_maksimum_rj
    END AS santunan_maksimum_rj,
    penggunaan_klaim ,
    CASE urutan
	   --WHEN 1 THEN (santunan_maksimum_rj + penambah) - penggunaan_klaim
	   WHEN 1 THEN curr_santunan_rj - penggunaan_klaim
	   ELSE sisa_santunan_rj
    END AS sisa_santunan_rj_1
FROM
    @temp_final_results AS rj1 INNER JOIN
    (
	   SELECT 
		  no_peserta_keluarga,
		  SUM(sisa_santunan_rj) AS penambah
	   FROM 
		  @temp_final_results 
	   WHERE 
		  santunan<>'Rawat Jalan'
	   GROUP BY
		  no_peserta_keluarga
) AS rj2 ON rj1.no_peserta_keluarga = rj2.no_peserta_keluarga
WHERE
    rj1.no_peserta_keluarga = '2017000635'

SELECT 
    No_Proposal ,
    Nama_Perusahaan ,
    CASE 
	   WHEN urutan = 1 THEN no_peserta_keluarga
	   ELSE ''
    END AS no_peserta_keluarga ,		
    CASE 
	   WHEN urutan = 1 THEN peserta_sebagai_karyawan
	   ELSE ''
    END AS peserta_sebagai_karyawan ,
    santunan ,
    santunan_maksimum_rj ,
    penggunaan_klaim ,
    sisa_santunan_rj
FROM 
    @temp_final_results
WHERE
    no_peserta_keluarga = '2017000635'
--END
