USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_rep_smi_rj]    Script Date: 08/10/2017 11:42:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- by Pribadi Pamungkas. 2017-05-22
--ALTER PROCEDURE [dbo].[proc_rep_smi_rj]
DECLARE
	@pi_no_proposal VARCHAR(9) = '2016.0587',
	@pi_tgl_bayar1 DATETIME,
	@pi_tgl_bayar2 DATETIME,
	@pi_tgl_bayar1Ya BIT = 0,
	@pi_tgl_bayar2Ya BIT = 0
--AS
--BEGIN
	SET NOCOUNT ON;

  DECLARE
    @pi_type TINYINT = 0
  --RI (Rawat Inap) ==> jumlahnya apa adanya (semua item santunan)
  --RJ (Rawat Jalan) ==> exclude item santunan melahirkan, kacamata, obat
  --LH (Melahirkan)
  --Obat (Obat obatan)
  --KC (Kacamata)

  -- item di RJ:
  --RJ01	Konsultasi (per kunjungan, per hari )
  --RJ0101	Pemeriksaan Dokter umum
  --RJ0103	Dokter + Obat
  --RJ0105	Dokter Spesialis tanpa surat pengantar dari dokter umum sebelumnya, per kunjungan
  --RJ0215	Pembelian obat-obatan tanpa resep per tahun
  --RJ03	Pemeriksaan penunjang diagnosa (laboratorium), per tahun
  --RJ0508	Keluarga Berencana per tahun
  --RJ0624	Imunisasi dan vaksinasi per tahun
  --RJ1101	Fisioterapi per tahun
  --RJG14	Perawatan gigi per tahun
  --RJK02	Kacamata per tahun
  --RJT1503	Medical check up per tahun

  SELECT
    x.[NIK],
    x.[NAMA KARYAWAN],
    x.[NAMA PESERTA] AS [NAMA CLAIMANT],
    x.[MEMBER NO] AS [NO PESERTA],
    x.[PRODUK PLAN],
    x.[CLAIM NO] AS [NO KLAIM],
    x.[NO REFERENSI],
    x.[PROVIDER],
    x.[DIAGNOSA],
    x.[TGL MASUK],
    x.[TGL KELUAR],
    x.[TERIMA] AS [TGL TERIMA],
    x.[TGL KUITANSI],
    x.[TGL BAYAR],
    
    x.Jml_Benefit,
    --x.[EMP ID],
    --x.[JAMINAN],
    --x.[TGL AKSEP],
    --x.[TGL ADMIN],
    --x.[EXCESS],
    --x.[DIVISION],
    --x.[KET],
    --SUM(x.[RJ01_Diajukan]) AS    RJ01,
    SUM(x.[RJ0101_Diajukan]) AS  RJ0101,
    SUM(x.[RJ0105_Diajukan]) AS  RJ0105,
    SUM(x.[RJ0103_Diajukan]) AS  RJ0103,
    SUM(x.[RJ0215_Diajukan]) AS RJ0215,
    SUM(x.[RJ03_Diajukan]) AS RJ03,
    SUM(x.[RJ0508_Diajukan]) AS RJ0508,
    SUM(x.[RJ0624_Diajukan]) AS RJ0624,
    SUM(x.[RJ1101_Diajukan]) AS RJ1101,
    SUM(x.[RJG14_Diajukan]) AS RJG14,
    SUM(x.[RJK02_Diajukan]) AS RJK02,
    SUM(x.[RJT1503_Diajukan]) AS RJT1503,
    x.[JML PENGAJUAN] AS [JML DIAJUKAN],
    x.[DEPOSIT],
    --x.[Jml_Diajukan],
    --SUM(x.[RJ01_Dibayar]) AS RJ01,
    SUM(x.[RJ0101_Dibayar]) AS RJ0101,
    SUM(x.[RJ0105_Dibayar]) AS RJ0105,
    SUM(x.[RJ0103_Dibayar]) AS RJ0103,
    SUM(x.[RJ0215_Dibayar]) AS RJ0215,
    SUM(x.[RJ03_Dibayar]) AS RJ03,
    SUM(x.[RJ0508_Dibayar]) AS RJ0508,
    SUM(x.[RJ0624_Dibayar]) AS RJ0624,
    SUM(x.[RJ1101_Dibayar]) AS RJ1101,
    SUM(x.[RJG14_Dibayar]) AS RJG14,
    SUM(x.[RJK02_Dibayar]) AS RJK02,
    SUM(x.[RJT1503_Dibayar]) AS RJT1503,
    x.[JML_BAYAR] AS [JUMLAH DIBAYAR],
    x.Jml_Ditolak AS [JUMLAH DITOLAK],
    x.[TOLAKAN] AS KETERANGAN,
    x.[KET],
    --x.[Jml_Dibayar]
    x.Status
  FROM
	  (
      SELECT
	      kp.No_Peserta AS [MEMBER NO],
	      pst.Nama AS [NAMA PESERTA],
	      karyawan.Nama AS [NAMA KARYAWAN],
	      NULL AS [EMP ID],
	      karyawan.Nik AS NIK,
	      note.No_Ref AS [NO REFERENSI],
	      rs.Nama AS PROVIDER,
	      kp.No_Klaim AS [CLAIM NO],
	      diag.Diagnosis1 AS DIAGNOSA,
	      NULL AS JAMINAN,
	      pp.Nama_Paket AS [PRODUK PLAN],
	      --kp.Tgl_Masuk AS [TGL MASUK],
	      kp.Tgl_Kwitansi AS [TGL MASUK],
	      COALESCE(kp.Tgl_Keluar,kp.Tgl_Kwitansi) AS [TGL KELUAR],
	      kp.Tgl_Kwitansi AS [TGL KUITANSI],
	      kr.Tgl_Terima AS TERIMA,
	      NULL AS [TGL AKSEP],
	      NULL AS [TGL ADMIN],
	      --nlm.Tgl_Bayar AS [TGL BAYAR],
	      --dbo.func_GetTglLunasKlaimRef(kp.No_Klaim, note.No_Ref) AS Tgl_Lunas,
	      ----COALESCE(detail.Tgl_Lunas,ref_provider.Tgl_Bayar) AS Tgl_Lunas,
       -- ref_provider.Tgl_Bayar,
        CASE
          WHEN kp.Jenis_Pembayaran='P' THEN note.Tgl_Bayar--ref_provider.Tgl_Bayar
          ELSE dbo.func_GetTglLunasKlaimRef(kp.No_Klaim, note.No_Ref)
        END AS [TGL BAYAR],
      	
	      note.Jml_Diajukan AS [JML PENGAJUAN],
	      NULL AS EXCESS,
	      kp.Uang_Muka AS DEPOSIT,
	      kp.Tolakan AS TOLAKAN,
	      note.Jml_Dibayar AS JML_BAYAR,
	      note.Jml_Ditolak,
	      NULL AS DIVISION,
	      kp.Notes AS KET,
	      
	      kd.Jml_Benefit,
	      --nlm.No_Ref,
	      --nlm.Jumlah
	      --kd.Kode_Santunan,
	      --CASE
	      --  WHEN kd.Kode_Santunan='RJ01' THEN kd.Jml_Diajukan
	      --  ELSE 0
       -- END AS RJ01_Diajukan,
        CASE
	        WHEN kd.Kode_Santunan='RJ0101' THEN kd.Jml_Diajukan
	        ELSE 0
        END AS RJ0101_Diajukan,
        CASE
	        WHEN kd.Kode_Santunan='RJ0103' THEN kd.Jml_Diajukan
	        ELSE 0
        END AS RJ0103_Diajukan,
        CASE
	        WHEN kd.Kode_Santunan='RJ0105' THEN kd.Jml_Diajukan
	        ELSE 0
        END AS RJ0105_Diajukan,
        CASE
	        WHEN kd.Kode_Santunan='RJ0215' THEN kd.Jml_Diajukan
	        ELSE 0
        END AS RJ0215_Diajukan,
        CASE
	        WHEN kd.Kode_Santunan='RJ03' THEN kd.Jml_Diajukan
	        ELSE 0
        END AS RJ03_Diajukan,
        CASE
	        WHEN kd.Kode_Santunan='RJ0508' THEN kd.Jml_Diajukan
	        ELSE 0
        END AS RJ0508_Diajukan,
        CASE
	        WHEN kd.Kode_Santunan='RJ0624' THEN kd.Jml_Diajukan
	        ELSE 0
        END AS RJ0624_Diajukan,
        CASE
	        WHEN kd.Kode_Santunan='RJ1101' THEN kd.Jml_Diajukan
	        ELSE 0
        END AS RJ1101_Diajukan,
        CASE
	        WHEN kd.Kode_Santunan='RJG14' THEN kd.Jml_Diajukan
	        ELSE 0
        END AS RJG14_Diajukan,
        CASE
	        WHEN kd.Kode_Santunan='RJK02' THEN kd.Jml_Diajukan
	        ELSE 0
        END AS RJK02_Diajukan,
        CASE
	        WHEN kd.Kode_Santunan='RJT1503' THEN kd.Jml_Diajukan
	        ELSE 0
        END AS RJT1503_Diajukan,
        --kd.Jml_Diajukan,
  	    
	      --kd.Jml_Ditolak,
	      --CASE
	      --  WHEN kd.Kode_Santunan='RJ01' THEN kd.Jml_Dibayar
	      --  ELSE 0
       -- END AS RJ01_Dibayar,
        CASE
	        WHEN kd.Kode_Santunan='RJ0101' THEN kd.Jml_Dibayar
	        ELSE 0
        END AS RJ0101_Dibayar,
        CASE
	        WHEN kd.Kode_Santunan='RJ0103' THEN kd.Jml_Dibayar
	        ELSE 0
        END AS RJ0103_Dibayar,
        CASE
	        WHEN kd.Kode_Santunan='RJ0105' THEN kd.Jml_Dibayar
	        ELSE 0
        END AS RJ0105_Dibayar,
        CASE
	        WHEN kd.Kode_Santunan='RJ0215' THEN kd.Jml_Dibayar
	        ELSE 0
        END AS RJ0215_Dibayar,
        CASE
	        WHEN kd.Kode_Santunan='RJ03' THEN kd.Jml_Dibayar
	        ELSE 0
        END AS RJ03_Dibayar,
        CASE
	        WHEN kd.Kode_Santunan='RJ0508' THEN kd.Jml_Dibayar
	        ELSE 0
        END AS RJ0508_Dibayar,
        CASE
	        WHEN kd.Kode_Santunan='RJ0624' THEN kd.Jml_Dibayar
	        ELSE 0
        END AS RJ0624_Dibayar,
        CASE
	        WHEN kd.Kode_Santunan='RJ1101' THEN kd.Jml_Dibayar
	        ELSE 0
        END AS RJ1101_Dibayar,
        CASE
	        WHEN kd.Kode_Santunan='RJG14' THEN kd.Jml_Dibayar
	        ELSE 0
        END AS RJG14_Dibayar,
        CASE
	        WHEN kd.Kode_Santunan='RJK02' THEN kd.Jml_Dibayar
	        ELSE 0
        END AS RJK02_Dibayar,
        CASE
	        WHEN kd.Kode_Santunan='RJT1503' THEN kd.Jml_Dibayar
	        ELSE 0
        END AS RJT1503_Dibayar--,
	      --kd.Jml_Dibayar
	      ,note.Status
      FROM
	      dbo.Klaim_Peserta AS kp INNER JOIN
	      dbo.Klaim_Detail AS kd ON kp.No_Klaim = kd.No_Klaim INNER JOIN
	      dbo.Polis_Asuransi AS pa ON kp.No_Proposal = pa.No_Proposal INNER JOIN
	      dbo.Peserta AS pst ON kp.No_Proposal = pst.No_Proposal
	                            AND kp.No_Peserta = pst.No_Peserta INNER JOIN
        dbo.Peserta AS karyawan ON kp.No_Proposal = karyawan.No_Proposal 
                                   AND LEFT(kp.No_Peserta,10)+'-0' = karyawan.No_Peserta LEFT JOIN
        ( SELECT
    	      ntm.No_Ref,
    	      ntm.No_Note,
    	      krm.No_Klaim,
    	      ntm.Tgl_Bayar,
    	      krm.Jml_Diajukan,
    	      krm.Jml_Ditolak,
    	      krm.Jml_Dibayar,
    	      ntm.Status
          FROM
    	      dbo.Klaim_Ref_Master AS krm INNER JOIN
            dbo.Note_Terbit_Master AS ntm ON krm.No_Ref = ntm.No_Ref
        ) AS note ON kp.No_Klaim = note.No_Klaim LEFT JOIN
        dbo.Note_Lunas_Master AS nlm ON note.No_Ref = nlm.No_Ref LEFT JOIN
        dbo.Provider AS rs ON kp.Kode_Provider = rs.IDI LEFT JOIN
        dbo.Diagnosa AS diag ON kp.Kode_Diagnosa = diag.Kode LEFT JOIN
        dbo.Klaim_Register AS kr ON kp.Id_RegKlaim = kr.Id_RegKlaim LEFT JOIN
        dbo.Produk_Polis AS pp ON kp.Id_Produk = pp.Id_Produk LEFT JOIN
        (
          SELECT
            ntpd.No_Ref,
            MAX(ntph.Tgl_Bayar) AS Tgl_Bayar
          FROM
            dbo.Note_Terbit_Provider_Header AS ntph INNER JOIN
            dbo.Note_Terbit_Provider_Detail AS ntpd ON ntph.No_Ref_Provider = ntpd.No_Ref_Provider
          GROUP BY
            ntpd.No_Ref
        ) ref_provider ON note.No_Ref = ref_provider.No_Ref
      WHERE
        --pa.No_Polis = '2016.0140.1'
        pa.No_Proposal = @pi_no_proposal
        AND kp.Status = 'I'
        --AND note.Jml_Dibayar > 0
        AND pp.Sub_Kelompok = '0301'  -- RJ
        --AND note.Tgl_Bayar >= '2017-03-24'
        --AND note.Tgl_Bayar <= '2017-04-30'
        AND (dbo.func_fixed_date(note.Tgl_Bayar) >= @pi_tgl_bayar1 OR @pi_tgl_bayar1Ya = 0)
        AND (dbo.func_fixed_date(note.Tgl_Bayar) <= @pi_tgl_bayar2 OR @pi_tgl_bayar2Ya = 0)
        
        --AND kd.Kode_Santunan NOT IN ('RJ0215','RJK02')  -- tipe RJ
        --AND kd.Kode_Santunan = 'RJ0215'  -- tipe Obat-obatan
        AND kd.Kode_Santunan = 'RJK02'  -- tipe Kacamata
        AND CAST(kd.Jml_Diajukan AS NUMERIC(14,2)) > 0
      --ORDER BY
      --  kp.No_Klaim
    ) AS x
  --WHERE
  --  x.[TGL BAYAR] IS NOT NULL
  GROUP BY
    x.[MEMBER NO],
    x.[NAMA PESERTA],
    x.[NAMA KARYAWAN],
    --x.[EMP ID],
    x.[NIK],
    x.[NO REFERENSI],
    x.[PROVIDER],
    x.[CLAIM NO],
    x.[DIAGNOSA],
    --x.[JAMINAN],
    x.[PRODUK PLAN],
    x.[TGL MASUK],
    x.[TGL KELUAR],
    x.[TERIMA],
    --x.[TGL AKSEP],
    --x.[TGL ADMIN],
    x.[TGL KUITANSI],
    x.[TGL BAYAR],
    
    x.Jml_Benefit,
    
    x.[JML PENGAJUAN],
    --x.[EXCESS],
    x.[DEPOSIT],
    x.[TOLAKAN],
    x.[JML_BAYAR],
    x.Jml_Ditolak,
    --x.[DIVISION],
    x.[KET]--,
    --x.[Jml_Diajukan],
    --x.[Jml_Dibayar]
    ,x.Status
  ORDER BY
    x.[CLAIM NO]

--END

SELECT
	*
FROM
	dbo.Klaim_Detail 
WHERE
  No_Klaim = '2017.028886'