/* Warning: To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

/*

	Enhancement Portal Member: History Klaim bisa backdate, tergantung parameter
	by Pribadi Pamungkas. 2017-12-05
	
	Dijalankan di server: eli-db10\grpinsurance
	Database: GrpInsurance
	
	Step 1. Create tabel [dbo].[custport_polis_member]
	Tabel ini akan menampung no polis yang menggunakan portal-member, beserta parameter history klaim backdate yang diperbolehkan (dalam satuan bulan)
	
*/

USE [GrpInsurance]

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.custport_polis_member
	(
	no_polis varchar(11) NOT NULL,
	view_klaim_backdate_bulan tinyint NOT NULL,
	status varchar(1) NOT NULL,
	create_date datetime NOT NULL,
	create_by varchar(4) NULL,
	change_date datetime NULL,
	change_by varchar(4) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.custport_polis_member ADD CONSTRAINT
	DF_custport_polis_member_view_klaim_backdate_bulan DEFAULT 0 FOR view_klaim_backdate_bulan
GO
ALTER TABLE dbo.custport_polis_member ADD CONSTRAINT
	DF_custport_polis_member_status DEFAULT 'G' FOR status
GO
ALTER TABLE dbo.custport_polis_member ADD CONSTRAINT
	DF_custport_polis_member_create_date DEFAULT GETDATE() FOR create_date
GO
ALTER TABLE dbo.custport_polis_member ADD CONSTRAINT
	PK_custport_polis_member PRIMARY KEY CLUSTERED 
	(
	no_polis
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.custport_polis_member SET (LOCK_ESCALATION = TABLE)
GO
COMMIT



/*
	----------------------------------------------------------------------------------------------------------------------------------------------------------------------
	
	Step 2. Insert no polis yang saat ini menggunakan portal-member ke tabel yang dibuat pada step 1
	
*/

USE [GrpInsurance]

INSERT INTO [dbo].[custport_polis_member]
           ([no_polis]
           ,[view_klaim_backdate_bulan]
           ,[status]
           ,[create_date]
           ,[create_by]
           ,[change_date]
           ,[change_by])
SELECT DISTINCT
	pa.No_Polis,
	0,	-- nilai default (0 berarti tidak bisa melihat periode asuransi sebelumnya)
	'I',
	GETDATE(),
	'SYS',
	GETDATE(),
	'SYS'
FROM
	dbo.Polis_Asuransi AS pa INNER JOIN
	dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
	dbo.Group_Perusahaan AS gp ON prs.Group_Perusahaan = gp.IDI
WHERE
	prs.Group_Perusahaan IN (
                            23017, -- MAP
                            23186, -- PLI
                            4      -- GT   
                           )
  AND pa.Mata_Uang = '02'
ORDER BY
	pa.No_Polis



/*
	----------------------------------------------------------------------------------------------------------------------------------------------------------------------
	
	Step 3. Create function dbo.func_custport_ind_info_klaim
	Function ini untuk menampilkan History Klaim yang bisa backdate, tergantung seting
	
*/

USE [GrpInsurance]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- by Pribadi Pamungkas. 2017-12-04
CREATE 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(30),
	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,
	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_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_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,
		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,
				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
			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
			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.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 INNER JOIN
		dbo.custport_klaim AS ck ON ckl.id_klaim = ck.id_klaim
	ORDER BY
		klaim.Tgl_Klaim
	
	RETURN 
END
GO



/*
	----------------------------------------------------------------------------------------------------------------------------------------------------------------------
	
	Step 4. Ubah service untuk History Klaim
	Sebelumnya, service untuk History Klaim menggunakan view custPort_ind_View_Info_Klaim, kemudian diubah menggunakan function (step 3 diatas)
	
*/

USE [GrpInsurance]

UPDATE
	dbo.custport_svc_action_list
SET
	action_type = 7,
	condition_field = 'no_Polis,no_Peserta,no_Proposal',
	result_object = 'func_custport_ind_info_klaim',
	change_date = GETDATE(),
	change_by = 'SUP'
WHERE 
	action_name='Info_Klaim' 
	AND grp_ind = 'ind'


/*
	
	done.
	Thanks
	
*/