USE [GrpInsurance]
GO
/****** Object:  UserDefinedFunction [dbo].[func_custport_ind_info_klaim]    Script Date: 14/01/2019 10.53.53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- last modified by Pribadi Pamungkas. 2018-01-14. Perubahan: kolom tgl_Lunas diubah tipe datanya dari DATETIME menjadi VARCHAR, dan ditambahkan logic
-- history changelog:
-- - Pribadi Pamungkas. 2018-12-06. Perubahan: tambah kolom: nama_Sub_Id, nama_Sub_En, nama_Sub_Pendek_Id, nama_Sub_Pendek_En
-- - Pribadi Pamungkas. 2018-02-28. Perubahan: jika no peserta spesifik, tampilkan spesifik. jika no peserta general, tampilkan sekeluarga
-- - Pribadi Pamungkas. 2018-02-01. Perubahan: kolom nama_Peserta pada @hasil_data diubah dari VARCHAR(30) menjadi VARCHAR(50)
-- - Pribadi Pamungkas. 2017-12-04. Perubahan: klaim jenis provider (yg bukan berasal dari Portal) ditampilkan juga
ALTER FUNCTION [dbo].[func_custport_ind_info_klaim]
(
	@pi_no_Polis VARCHAR(11),
	@pi_no_Peserta VARCHAR(12),
	@pi_no_Proposal VARCHAR(9)	-- saat ini tidak digunakan, namun tetep dicantumkan agar compatible dengan aplikasi saat ini
)
RETURNS 
@hasil_data TABLE 
(
	no_Proposal VARCHAR(9),
	no_Polis VARCHAR(11),
	claim_Register VARCHAR(11),
	no_Klaim VARCHAR(11),
	no_Peserta VARCHAR(12),
	nama_Peserta VARCHAR(50),
	nama_Sub_Id VARCHAR(50),
	nama_Sub_En VARCHAR(50),
	nama_Sub_Pendek_Id VARCHAR(50),
	nama_Sub_Pendek_En VARCHAR(50),
	nama_Produk VARCHAR(50),
	tgl_Pengajuan DATETIME,
	tgl_kuitansi DATETIME,
	mata_Uang VARCHAR(4),
	nilai_Klaim FLOAT,
	deductible FLOAT,
	klaim_Tolak FLOAT,
	klaim_Bayar FLOAT,
	--tgl_Lunas DATETIME,
	tgl_Lunas VARCHAR(50),
	no_Nota VARCHAR(25),
	jenis_Bayar VARCHAR(9),
	rumah_Sakit VARCHAR(60)
)
AS
BEGIN

	DECLARE
		@dari_tanggal DATETIME,
		@tgl_mulai_polis DATETIME

	SELECT
		@dari_tanggal = CASE
							WHEN COALESCE(cp.view_klaim_backdate_bulan,0) = 0 THEN CAST(GETDATE() AS DATE)
							ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, (DATEADD(MONTH,(cp.view_klaim_backdate_bulan * -1),pa.Tgl_Mulai))), 0)
						END,
		@tgl_mulai_polis = CASE
								WHEN COALESCE(cp.view_klaim_backdate_bulan,0) = 0 THEN pa.Tgl_Mulai
								ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, (DATEADD(MONTH,(cp.view_klaim_backdate_bulan * -1),pa.Tgl_Mulai))), 0)
							END
	FROM
		dbo.Polis_Asuransi AS pa INNER JOIN
		dbo.custport_polis_member AS cp ON pa.No_Polis = cp.no_polis
	WHERE
		cp.status = 'I'
		AND pa.no_polis = @pi_no_Polis
		AND pa.Tgl_Mulai <= CAST(GETDATE() AS DATE)
		AND pa.Tgl_Selesai >= CAST(GETDATE() AS DATE)

	INSERT INTO @hasil_data (no_Proposal,
							no_Polis,
							claim_Register,
							no_Klaim,
							no_Peserta,
							nama_Peserta,
							nama_Sub_Id,
							nama_Sub_En,
							nama_Sub_Pendek_Id,
							nama_Sub_Pendek_En,
							nama_Produk,
							tgl_Pengajuan,
							tgl_kuitansi,
							mata_Uang,
							nilai_Klaim,
							deductible,
							klaim_Tolak,
							klaim_Bayar,
							tgl_Lunas,
							no_Nota,
							jenis_Bayar,
							rumah_Sakit) 
	SELECT
		klaim.No_Proposal AS no_Proposal,
		klaim.No_Polis AS no_Polis,
		ck.no_klaim AS claim_Register,
		klaim.No_Klaim AS no_Klaim,
		klaim.No_Peserta AS no_Peserta,
		klaim.Nama AS nama_Peserta,
		klaim.Nama_Sub,
		klaim.Nama_Sub_Inggris,
		klaim.Nama_Pendek,
		klaim.Nama_Pendek_Inggris,
		klaim.Nama_Paket AS nama_Produk,
		klaim.Tgl_Klaim AS tgl_Pengajuan,
		klaim.Tgl_Kwitansi AS tgl_kuitansi,
		klaim.Mata_Uang AS mata_Uang,
		klaim.Jml_Diajukan AS nilai_Klaim,
		deductible = CASE WHEN ISNULL(detail.No_Klaim, '') <> '' THEN detail.Jml_Deductible
													ELSE klaim.Jml_Deductible
										 END,
		klaim_Tolak = CASE WHEN ISNULL(detail.No_Klaim, '') <> '' THEN detail.Jml_Ditolak
											 ELSE klaim.Jml_Ditolak
									END,
		klaim_Bayar = CASE WHEN ISNULL(detail.No_Klaim, '') <> '' THEN detail.Jml_Dibayar
											 ELSE klaim.Jml_Dibayar
									END,
		--detail.Tgl_Lunas AS tgl_Lunas,
		CASE klaim.Kode_Jenis_Pembayaran
			WHEN 'P' THEN 
						CASE klaim.Kode_Mata_Uang
							WHEN '01' THEN 'Dibayarkan ke Provider'	-- IDR
							WHEN '02' THEN 'Paid to Provider'	-- USD
						END
			WHEN 'R' THEN
						CASE
							WHEN detail.Tgl_Lunas IS NULL THEN
															CASE klaim.Kode_Mata_Uang
																WHEN '01' THEN 'Belum Dibayar'	-- IDR
																WHEN '02' THEN 'Waiting Claim Process'	-- USD
															END
							ELSE
								CASE klaim.Kode_Mata_Uang
									WHEN '01' THEN CAST(DATEPART(day, detail.Tgl_Lunas) AS VARCHAR(4)) + ' ' + dbo.func_get_nama_bulan(DATEPART(month, detail.Tgl_Lunas)) + ' ' + CAST(DATEPART(year, detail.Tgl_Lunas) AS VARCHAR(4))	-- IDR
									WHEN '02' THEN CAST(DATEPART(day, detail.Tgl_Lunas) AS VARCHAR(4)) + ' ' + FORMAT(detail.Tgl_Lunas, 'MMMM', 'en-US') + ' ' + CAST(DATEPART(year, detail.Tgl_Lunas) AS VARCHAR(4))	-- USD
									-- FORMAT(detail.Tgl_Lunas, 'MMMM', 'en-US') bisa diganti dengan SELECT DATENAME(month, detail.Tgl_Lunas)
								END
						END
		END AS tgl_Lunas,
		detail.No_Note AS no_Nota,
		klaim.Jenis_Pembayaran AS jenis_Bayar,
		klaim.rumah_Sakit
	FROM
		(
			SELECT
				pa.No_Proposal,
				pa.No_Polis,
				kp.No_Klaim,
				kp.No_Peserta,
				p.Nama,
				sk.Nama_Sub,
				sk.Nama_Sub_Inggris,
				sk.Nama_Pendek,
				sk.Nama_Pendek_Inggris,
				pp.Nama_Paket,
				kp.Tgl_Klaim,
				kp.Tgl_Kwitansi,
				mu.Deskripsi AS Mata_Uang,
				kp.Jml_Diajukan,
				kp.Jml_Deductible,
				kp.Jml_Ditolak,
				kp.Jml_Dibayar,
				kp.Id_Produk,
				CASE kp.Jenis_Pembayaran
					WHEN 'R' THEN 'Reimburse'
					WHEN 'P' THEN 'Provider'
					ELSE ''
				END AS Jenis_Pembayaran,
				rs.Nama AS rumah_Sakit,
				pa.Mata_Uang AS Kode_Mata_Uang,
				kp.Jenis_Pembayaran AS Kode_Jenis_Pembayaran
			FROM
				dbo.Klaim_Peserta AS kp INNER JOIN
				dbo.Polis_Asuransi AS pa ON kp.No_Proposal = pa.No_Proposal INNER JOIN
				dbo.Peserta AS p ON pa.No_Proposal = p.No_Proposal
														AND kp.No_Peserta = p.No_Peserta INNER JOIN
				dbo.Produk_Polis AS pp ON kp.Id_Produk = pp.Id_Produk INNER JOIN
				dbo.Mata_Uang AS mu ON pa.Mata_Uang = mu.Kode LEFT JOIN
				dbo.Provider AS rs ON kp.Kode_Provider = rs.IDI INNER JOIN
				dbo.Sub_Kelompok AS sk ON kp.Sub_Kelompok = sk.Sub_Kelompok
			WHERE
				kp.Status = 'I'
				AND pa.No_Polis = @pi_no_Polis
				AND pa.Tgl_Selesai >= @dari_tanggal
				AND pa.Tgl_Mulai <= CAST(GETDATE() AS DATE)
				--AND LEFT(kp.No_Peserta,10) = LEFT(@pi_no_Peserta,10)
				AND kp.No_Peserta LIKE @pi_no_Peserta+'%'	-- by Pribadi Pamungkas. 2018-02-28
				AND kp.Tgl_Kwitansi >= @tgl_mulai_polis
		) AS klaim LEFT OUTER JOIN 
		(
			SELECT
				krd.No_Peserta,
				krd.No_Klaim,
				krd.Tgl_Klaim,
				sk.Nama_Sub,
				krd.No_Ref,
				dbo.func_GetTglLunasKlaimRef(krd.Tgl_Klaim, krd.No_Ref) AS Tgl_Lunas,
				SUM(COALESCE(krd.Jml_Diajukan, 0)) AS Jml_Diajukan,
				SUM(COALESCE(krd.Jml_Deductible, 0)) AS Jml_Deductible,
				SUM(COALESCE(krd.Jml_Dibayar - krd.Jml_Dibayar_Old, 0)) AS Jml_Dibayar,
				SUM(COALESCE(krd.Jml_Ditolak, 0)) AS Jml_Ditolak,
				krd.Id_Produk,
				ntm.No_Note
			FROM
				dbo.Klaim_Ref_Detail AS krd INNER JOIN 
				dbo.Sub_Kelompok AS sk ON krd.Sub_Kelompok = sk.Sub_Kelompok INNER JOIN
				dbo.Note_Terbit_Master AS ntm ON krd.No_Ref = ntm.No_Ref INNER JOIN
				(
					SELECT
						krd.No_Peserta,
						krd.No_Klaim,
						MAX(ntm.Tgl_Buat) AS Tgl_Buat
					FROM
						dbo.Klaim_Ref_Detail AS krd INNER JOIN 
						dbo.Sub_Kelompok AS sk ON krd.Sub_Kelompok = sk.Sub_Kelompok INNER JOIN
						dbo.Note_Terbit_Master AS ntm ON krd.No_Ref = ntm.No_Ref
					GROUP BY
						krd.No_Peserta,
						krd.No_Klaim
				) AS note_maks ON krd.No_Klaim = note_maks.No_Klaim
													AND ntm.Tgl_Buat = note_maks.Tgl_Buat
			GROUP BY
				krd.No_Peserta,
				krd.No_Klaim,
				krd.Tgl_Klaim,
				sk.Nama_Sub,
				krd.No_Ref,
				dbo.func_GetTglLunasKlaimRef(krd.Tgl_Klaim, krd.No_Ref),
				krd.Id_Produk,
				ntm.No_Note
		) AS detail ON detail.No_Peserta = klaim.No_Peserta
									 AND detail.No_Klaim = klaim.No_Klaim
									 AND detail.Tgl_Klaim = klaim.Tgl_Klaim
									 AND detail.Id_Produk = klaim.Id_Produk LEFT JOIN
		dbo.custport_klaim_link AS ckl ON klaim.No_Klaim = ckl.no_klaim LEFT JOIN
		dbo.custport_klaim AS ck ON ckl.id_klaim = ck.id_klaim
	ORDER BY
		klaim.Tgl_Klaim
	
	RETURN 
END
