--If (Length(quUpdKlaimMasterNo_Proposal.Value)<>0)
-- AND (Length(quUpdKlaimMasterNo_Santunan.Value)<>0)
-- AND (Length(quUpdKlaimMasterId_Produk.Value)<>0)
-- AND (Length(quUpdKlaimMasterNo_Klaim.Value)<>0) Then
-- Begin
--      If quUpdKlaimDetail.Active Then
--      Begin
--           quUpdKlaimDetail.DisableControls;
--           quItemSantunanPolis.Close;
--           quItemSantunanPolis.Open;
--           quItemSantunanPolis.First;
--           While Not quItemSantunanPolis.Eof Do
--           Begin
--                If Not quUpdKlaimDetail.Locate('Kode_Santunan',quItemSantunanPolisKode_Santunan.Value,[]) Then
  --                Begin
  --                     quUpdKlaimDetail.Append;
  --                     quUpdKlaimDetailNo_Klaim.Value:=quUpdKlaimMasterNo_Klaim.Value;
  --                     quUpdKlaimDetailKode_Santunan.Value:=quItemSantunanPolisKode_Santunan.Value;
  --                     quUpdKlaimDetailJml_Diajukan.Value:=0;
  --                End
--                Else 
--                  quUpdKlaimDetail.Edit;
--                If quItemSantunanPolisBts_Maks.Value Then
  --                Begin 
--                        quUpdKlaimDetailJml_Benefit.Value:= quItemSantunanPolisCurr_Santunan.Value;
  --                End
--                Else
--                  Begin

--                     If (quItemSantunanPolisSantunan_Prop.Value) AND (quItemSantunanPolisItem_Prop.Value) Then
--                     quUpdKlaimDetailJml_Benefit.Value:=Round(quUpdKlaimMastercaPropSantunan.Value * quItemSantunanPolisBenefit.Value)
--                     Else quUpdKlaimDetailJml_Benefit.Value:=Round(quItemSantunanPolisBenefit.Value);
--                  End;
--                quItemSantunanPolis.Next;
--           End;
--           quUpdKlaimDetail.First;
--           While Not quUpdKlaimDetail.Eof Do
--           Begin
--                If Length(quUpdKlaimDetailluItem.Value)=0 Then quUpdKlaimDetail.Delete
--                Else quUpdKlaimDetail.Next;
--           End;
--           quUpdKlaimDetail.EnableControls;
--           quUpdKlaimDetail.First;
--      End;
-- End;
--mohon dibuatkan menu utk refresh data claim secara sekaligus berdasarkan :
--1. Tanggal klaim (range)
--2. Jenis Tagihan (provider/reimbursement)
--3. Rumah Sakit
--4. By leader
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- by Pribadi Pamungkas. 2017-05-02
-- Description:	<Description,,>
CREATE PROCEDURE proc_batch_refresh_klaim 
  @pi_tgl_klaim1 DATETIME,  -- tgl pengajuan klaim atau tgl create?
  @pi_tgl_klaim2 DATETIME,  -- tgl pengajuan klaim atau tgl create?
  @pi_jenis_tagihan TINYINT,  -- 0: all, 1: provider, 2: reimburse
  @pi_kode_provider VARCHAR(10),
  --@pi_leader VARCHAR(4),  -- leader tidak ada, karena belum di create ref
  @pi_tgl_klaim1Ya BIT,
  @pi_tgl_klaim2Ya BIT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE 
	  @v_no_klaim VARCHAR(11),
	  @v_id_produk VARCHAR(10)
	
	DECLARE list_cursor CURSOR FAST_FORWARD FOR  
	  SELECT
		  No_Klaim,
		  Id_Produk
	  FROM
		  dbo.Klaim_Peserta
	  WHERE
	    Status = 'G'
	    AND (dbo.func_fixed_date(Create_Date) >= dbo.func_fixed_date(@pi_tgl_klaim1) OR @pi_tgl_klaim1Ya = 0)
	    AND (dbo.func_fixed_date(Create_Date) <= dbo.func_fixed_date(@pi_tgl_klaim2) OR @pi_tgl_klaim2Ya = 0)
      AND (isnull(Kode_Provider,'')=isnull(@pi_kode_provider,'') OR isnull(@pi_kode_provider,'')='' OR isnull(@pi_kode_provider,'')='0')
      AND (
            (@pi_jenis_tagihan=1 AND Jenis_Pembayaran='P')
            OR (@pi_jenis_tagihan=2 AND Jenis_Pembayaran='R')
            OR (@pi_jenis_tagihan=0 AND Jenis_Pembayaran IN ('P','R'))
          )
      AND COALESCE(No_Santunan,'') <> ''
      AND COALESCE(Id_Produk,'') <> ''
      AND COALESCE(No_Klaim,'') <> ''
 
  OPEN list_cursor  
  FETCH NEXT FROM list_cursor INTO @v_no_klaim, @v_id_produk

  WHILE @@FETCH_STATUS = 0  
  BEGIN 

	  --1 tambahkan yg gak ada
	  INSERT INTO dbo.Klaim_Detail
	          (No_Klaim,
	           Kode_Santunan,
	           Tabel_Prosen,
	           Amount,
	           Tgl_Kwitansi,
	           Jml_Diajukan,
	           Jml_Klaim,
	           Jml_Deductible,
	           Jml_Benefit,
	           Jml_Dibayar,
	           Jml_Ditolak,
	           Kode_Tolakan,
	           Jml_Adm,
	           Keterangan,
	           Jml_Dispensasi,
	           Jml_Dibayar_Old,
	           Status,
	           Change_By,
	           Change_Date,
	           Create_By,
	           Create_Date)
    SELECT
      @v_no_klaim, -- No_Klaim - varchar(11)
	    Kode_Santunan, -- Kode_Santunan - varchar(10)
	    0, -- Tabel_Prosen - float
	    0, -- Amount - float
	    NULL, -- Tgl_Kwitansi - datetime
	    0, -- Jml_Diajukan - float
      0, -- Jml_Klaim - float
      0, -- Jml_Deductible - float
      0, -- Jml_Benefit - float
      0, -- Jml_Dibayar - float
      0, -- Jml_Ditolak - float
      '', -- Kode_Tolakan - varchar(5)
      0, -- Jml_Adm - float
      NULL, -- Keterangan - varchar(254)
      0, -- Jml_Dispensasi - float
      0, -- Jml_Dibayar_Old - float
      NULL, -- Status - varchar(1)
      NULL, -- Change_By - varchar(4)
      NULL, -- Change_Date - datetime
      NULL, -- Create_By - varchar(4)
      NULL  -- Create_Date - datetime
    FROM
	    dbo.Item_Santunan_Polis
    WHERE
      Id_Produk = @v_id_produk
      AND Kode_Santunan NOT IN (SELECT Kode_Santunan FROM dbo.Klaim_Detail WHERE No_Klaim = @v_no_klaim)

    --2 edit yg ada
    --SELECT
    --  CASE
    --    WHEN isp.Bts_Maks=1 THEN pp.Curr_Santunan
    --    ELSE
    --      CASE
    --        WHEN pp2.Santunan_Prop=1 AND mis.Santunan_Prop=1 THEN ROUND(pp.Prop_Santunan + bp.Benefit,0)  -- harusnya pake round dgn metode bankers rules
    --        ELSE ROUND(bp.Benefit,0)  -- harusnya pake round dgn metode bankers rules
    --      END
    --  END AS jml_benefit
    --FROM
	   -- dbo.Klaim_Peserta AS kp INNER JOIN
	   -- dbo.Klaim_Detail AS kd ON kp.No_Klaim = kd.No_Klaim INNER JOIN
	   -- dbo.Item_Santunan_Polis AS isp ON kp.Id_Produk = isp.Id_Produk 
	   --                                   AND kd.Kode_Santunan = isp.Kode_Santunan INNER JOIN
	   -- dbo.Peserta_Produk AS pp ON kp.No_Proposal = pp.No_Proposal 
	   --                             AND kp.No_Peserta = pp.No_Peserta
	   --                             AND kp.Id_Produk = pp.Id_Produk INNER JOIN
	   -- dbo.Produk_Polis AS pp2 ON kp.Id_Produk = pp2.Id_Produk INNER JOIN
	   -- dbo.Item_Santunan AS mis ON kd.Kode_Santunan = mis.Kode_Item INNER JOIN
	   -- dbo.Benefit_Polis AS bp ON kp.Id_Produk = bp.Id_Produk
	   --                            AND kd.Kode_Santunan = bp.Kode_Santunan
	   --                            AND ( CASE WHEN pp2.Sub_Kelompok='0100' THEN '*' ELSE pp.Kelas END = bp.Kelas )
    --WHERE
    --  kp.No_Klaim = @v_no_klaim
    
    UPDATE
      kd
    SET
      kd.Jml_Benefit = CASE
                        WHEN isp.Bts_Maks=1 THEN pp.Curr_Santunan
                        ELSE
                          CASE
                            WHEN pp2.Santunan_Prop=1 AND mis.Santunan_Prop=1 THEN ROUND(pp.Prop_Santunan + bp.Benefit,0)  -- harusnya pake round dgn metode bankers rules
                            ELSE ROUND(bp.Benefit,0)  -- harusnya pake round dgn metode bankers rules
                          END
                      END
    FROM
	    dbo.Klaim_Peserta AS kp INNER JOIN
	    dbo.Klaim_Detail AS kd ON kp.No_Klaim = kd.No_Klaim INNER JOIN
	    dbo.Item_Santunan_Polis AS isp ON kp.Id_Produk = isp.Id_Produk 
	                                      AND kd.Kode_Santunan = isp.Kode_Santunan INNER JOIN
	    dbo.Peserta_Produk AS pp ON kp.No_Proposal = pp.No_Proposal 
	                                AND kp.No_Peserta = pp.No_Peserta
	                                AND kp.Id_Produk = pp.Id_Produk INNER JOIN
	    dbo.Produk_Polis AS pp2 ON kp.Id_Produk = pp2.Id_Produk INNER JOIN
	    dbo.Item_Santunan AS mis ON kd.Kode_Santunan = mis.Kode_Item INNER JOIN
	    dbo.Benefit_Polis AS bp ON kp.Id_Produk = bp.Id_Produk
	                               AND kd.Kode_Santunan = bp.Kode_Santunan
	                               AND ( CASE WHEN pp2.Sub_Kelompok='0100' THEN '*' ELSE pp.Kelas END = bp.Kelas )
    WHERE
      kp.No_Klaim = @v_no_klaim

    FETCH NEXT FROM list_cursor INTO @v_no_klaim, @v_id_produk  
  END  

  CLOSE list_cursor  
  DEALLOCATE list_cursor 

END
GO
