-- Rasio Klaim
-- diambil dari proc_Rep10381_2

---- tabel untuk hasil:
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
--SET ANSI_PADDING ON
--GO
--CREATE TABLE [dbo].[temp_prib_rasio_klaim_20151012](
--	[Nama_Perusahaan] [varchar](50) NULL,
--	[No_Polis] [varchar](11) NULL,
--	[GT_OG] [varchar](2) NULL,
--	[Sub_Init] [varchar](1) NULL,
--	[Tgl_Mulai] [datetime] NULL,
--	[Tgl_Selesai] [datetime] NULL,
--	[jml_karyawan] [int] NULL,
--	[jml_peserta] [int] NULL,
--	[jml_peserta_new] [int] NULL,
--	[Cara_Bayar] [varchar](10) NULL,
--	[Status_Peserta] [varchar](10) NULL,
--	[Status_Kawin] [varchar](2) NULL,
--	[Nama_Sub] [varchar](50) NULL,
--	[Jml_Premi] [float] NULL,
--	[Jml_Diajukan] [float] NULL,
--	[Jml_Dibayar] [float] NULL,
--	[Diskon_Pr] [float] NULL,
--	[Diskon_Cp] [float] NULL,
--	[Rasio1] [float] NULL,
--	[Rasio2] [float] NULL,
--	[Rasio3] [float] NULL
--) ON [PRIMARY]
--GO
--SET ANSI_PADDING OFF
--GO
  

DECLARE @no_proposal VARCHAR(9)

DECLARE list_cursor CURSOR FOR  
SELECT
	pa.No_Proposal
FROM
	dbo.Polis_Asuransi AS pa INNER JOIN
	dbo.Perusahaan AS p ON pa.No_Prs = p.No_Prs
WHERE
  p.GT_OG = 'GT'
  AND pa.Status = 'I'
ORDER BY
  p.Nama_Perusahaan

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_proposal  

WHILE @@FETCH_STATUS = 0  
BEGIN 

  DECLARE
    @IdBroker varchar(5) = '',
    @GroupPrs varchar(5) = '',
    @GTOG varchar(2) = '',
    @NoProposal varchar(9) = @no_proposal,
    @TglMulai1 DATETIME = NULL,
    @TglMulai2 DATETIME = NULL,
    @TglSelesai1 DATETIME = NULL,
    @TglSelesai2 DATETIME = NULL,
    @TglPremi1 DATETIME = NULL,
    @TglPremi2 DATETIME = NULL,
    @TglKlaim1 DATETIME = NULL,
    @TglKlaim2 DATETIME = NULL,
    @Pilihan INT = 2,
    @PilihBroker SMALLINT = 0,
    @BidangUsaha varchar(10)= '',
    @TglMulai1Ya BIT = NULL,
    @TglMulai2Ya BIT = NULL,
    @TglSelesai1Ya BIT = NULL,
    @TglSelesai2Ya BIT = NULL,
    @TglPremi1Ya BIT = NULL,
    @TglPremi2Ya BIT = NULL,
    @TglKlaim1Ya BIT = NULL,
    @TglKlaim2Ya BIT = NULL,
    @Marketing VARCHAR(4) = '',
    @StatusNasabah  INT = 0


    DECLARE 
      @StatusYa varchar(1)  --add zai 20140526
      
	  IF @StatusNasabah = 0 
	    SET @StatusYa = ''
	  ELSE IF @StatusNasabah = 1 
	    SET @StatusYa = 'I'  --add zai 20140526

    --EXEC gproc_RepColumnLoad '10381', 2

    INSERT INTO temp_prib_rasio_klaim_20151012
            (Nama_Perusahaan,   -- Nama_Perusahaan - varchar(50)
             No_Polis,          -- No_Polis - varchar(11)
             GT_OG,             -- GT_OG - varchar(2)
             Sub_Init,          -- Sub_Init - varchar(1)
             Tgl_Mulai,         -- Tgl_Mulai - datetime
             Tgl_Selesai,       -- Tgl_Selesai - datetime
             jml_karyawan,      -- jml_karyawan - int
             jml_peserta,       -- jml_peserta - int
             jml_peserta_new,   -- jml_peserta_new - int
             Cara_Bayar,        -- Cara_Bayar - varchar(10)
             Status_Peserta,    -- Status_Peserta - varchar(10)
             Status_Kawin,      -- Status_Kawin - varchar(2)
             Nama_Sub,          -- Nama_Sub - varchar(50)
             Jml_Premi,         -- Jml_Premi - float
             Jml_Diajukan,      -- Jml_Diajukan - float
             Jml_Dibayar,       -- Jml_Dibayar - float
             Diskon_Pr,         -- Diskon_Pr - float
             Diskon_Cp,         -- Diskon_Cp - float
             Rasio1,            -- Rasio1 - float
             Rasio2,            -- Rasio2 - float
             Rasio3)            -- Rasio3 - float
    SELECT  
      T1.Nama_Perusahaan, 
      T1.No_Polis, 
      T1.GT_OG, 
      T1.Sub_Init, 
      T1.Tgl_Mulai, 
      T1.Tgl_Selesai,                
      T1.jml_karyawan,               --add zai 20140619
      T1.jml_peserta,                --add zai 20140619
      
      COUNT(T1.No_Peserta) AS jml_peserta_new,
      
      T1.Deskripsi AS Cara_Bayar,    --add zai 20140619 
      
      T1.Status_Peserta,
      T1.Status_Kawin,
      --T1.Grade,
        
      T1.Nama_Sub, 
      Sum(T2.Jml_Premi) as Jml_Premi, 
      Sum(isnull(T3.Jml_Diajukan,0)) as Jml_Diajukan, 
      Sum(isnull(T3.Jml_Dibayar,0)) as Jml_Dibayar, 
      Sum(0.01*isnull(T1.Diskon_Pr,0)*isnull(T2.Jml_Premi,0)+isnull(T1.Diskon_PrRp,0)) as Diskon_Pr,  
      Sum(0.01*isnull(T1.Diskon_Cp,0)*isnull(T2.Jml_Premi,0)+isnull(T1.Diskon_CpRp,0)) as Diskon_Cp,
      Rasio1 = Round(100.0*dbo.func_Bagi(Sum(isnull(T3.Jml_Dibayar,0)), Sum(isnull(T2.Jml_Premi,0))),1),
      Rasio2 = Round(100.0*dbo.func_Bagi(Sum(isnull(T3.Jml_Dibayar,0)), Sum(isnull(T2.Jml_Premi,0) - 0.01*isnull(T1.Diskon_Pr,0)*isnull(T2.Jml_Premi,0)+isnull(T1.Diskon_PrRp,0))),2),                                  
      Rasio3 = Round(100.0*dbo.func_Bagi(Sum(isnull(T3.Jml_Dibayar,0)), Sum(isnull(T2.Jml_Premi,0) - 0.01*isnull(T1.Diskon_Pr,0)*isnull(T2.Jml_Premi,0)+isnull(T1.Diskon_PrRp,0) - 0.01*isnull(T1.Diskon_Cp,0)*isnull(T2.Jml_Premi,0)+isnull(T1.Diskon_CpRp,0))),2)
      --Rasio3 = Round(100.0*dbo.func_Bagi(Sum(T3.Jml_Dibayar),Sum(T2.Jml_Premi-T1.Diskon_Pr*T2.Jml_Premi+T1.Diskon_PrRp-T1.Diskon_Cp*T2.Jml_Premi+T1.Diskon_CpRp)),2)
    FROM
      (
        SELECT  
          dbo.Polis_Asuransi.No_Proposal, 
          dbo.Polis_Asuransi.No_Polis, 
          dbo.Perusahaan.GT_OG, 
          dbo.Polis_Asuransi.Sub_Init, 
          dbo.Polis_Asuransi.Tgl_Mulai, 
          dbo.Polis_Asuransi.Tgl_Selesai,
          karyawan.jml_karyawan,                    --add zai 20140619      
          dbo.polis_asuransi.Jml_Peserta,           --add zai 20140619 
          pb.Deskripsi,                             --add zai 20140619
          dbo.Produk_Polis.Id_Produk, 
          dbo.Perusahaan.Nama_Perusahaan,  
          dbo.Peserta.No_Peserta, 
          dbo.Peserta.Nama, 
          
          dbo.Peserta.Sebagai,
          dbo.Sebagai.Status_Peserta,
          dbo.Peserta.Status_Kawin,
          dbo.Peserta.Grade,
          
          '[' + dbo.Peserta.Cabang + ']' + 
          dbo.Cabang.Nama AS Nama_Unit, 
          dbo.Produk_Polis.Nama_Paket as Nama_Sub, 
          isnull(dbo.Produk_Polis.Diskon_Pr,0) as Diskon_Pr,
          isnull(dbo.Produk_Polis.Diskon_PrRp,0) as Diskon_PrRp, 
          isnull(dbo.Produk_Polis.Diskon_Cp,0) as Diskon_Cp, 
          isnull(dbo.Produk_Polis.Diskon_CpRp,0) as Diskon_CpRp
        FROM    
          dbo.Peserta 
          INNER JOIN
          dbo.Polis_Asuransi 
          ON dbo.Peserta.No_Proposal = dbo.Polis_Asuransi.No_Proposal 
          INNER JOIN
          dbo.Produk_Polis 
          ON dbo.Polis_Asuransi.No_Proposal = dbo.Produk_Polis.No_Proposal 
          INNER JOIN
          dbo.Perusahaan 
          ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs 
          LEFT OUTER JOIN
          dbo.Cabang 
          ON dbo.Polis_Asuransi.No_Prs = dbo.Cabang.No_Prs 
             AND dbo.Peserta.Cabang = dbo.Cabang.Kode
          LEFT OUTER JOIN                                            --add zai 20140619
          (
            SELECT 
              No_Proposal ,
              COUNT(*) AS jml_karyawan
            FROM dbo.Peserta
              WHERE Sebagai = 00 AND Status = 'I'    
            GROUP BY No_Proposal          
          ) AS karyawan
          ON karyawan.No_Proposal = polis_asuransi.No_Proposal      
          LEFT OUTER JOIN                                            --add zai 20140619
          dbo.Periode_Bayar AS pb
          ON dbo.Polis_Asuransi.Periode_Bayar = pb.Kode  INNER JOIN
          dbo.Sebagai ON dbo.Peserta.Sebagai = dbo.Sebagai.IDI
        WHERE 
          (dbo.Peserta.Status<>'C') 
          AND (dbo.Perusahaan.Group_Perusahaan=@GroupPrs OR isnull(@GroupPrs,'')='')
          AND (dbo.Perusahaan.GT_OG=@GTOG OR isnull(@GTOG,'')='')
          AND (dbo.Perusahaan.Bidang_Usaha=@BidangUsaha OR isnull(@BidangUsaha,'')='')
          AND (dbo.Polis_Asuransi.Broker_Id=@IdBroker OR isnull(@IdBroker,'')='')
          AND ((isnull(dbo.Polis_Asuransi.Broker_Id,'')<>'' AND @PilihBroker=1) OR (isnull(dbo.Polis_Asuransi.Broker_Id,'')='' AND @PilihBroker=2) OR isnull(@PilihBroker,0)=0)
          AND (dbo.Polis_Asuransi.No_Proposal=@NoProposal OR isnull(@NoProposal,'')='') 
          AND (dbo.Polis_Asuransi.Tgl_Mulai>=@TglMulai1 OR isnull(@TglMulai1Ya,0)=0)
          AND (dbo.Polis_Asuransi.Tgl_Mulai<=@TglMulai2 OR isnull(@TglMulai2Ya,0)=0)
          AND (dbo.Polis_Asuransi.Tgl_Selesai>=@TglSelesai1 OR isnull(@TglSelesai1Ya,0)=0)
          AND (dbo.Polis_Asuransi.Tgl_Selesai<=@TglSelesai2 OR isnull(@TglSelesai2Ya,0)=0) 
          AND (dbo.Peserta.No_Proposal = @NoProposal OR isnull(@NoProposal,'')='') 
          AND (dbo.Polis_Asuransi.Marketing = @Marketing OR ISNULL(@Marketing,'')='')    --add zai 20140528
          AND (dbo.Polis_Asuransi.Status = @StatusYa OR ISNULL(@StatusYa,'')='')    --add zai 20140526
          --AND dbo.Peserta.Grade >= 1
          --AND dbo.Peserta.Grade <= 9
      ) 
      as T1 
      INNER JOIN
      ( 
        SELECT 
          dbo.Note_Terbit_Master.No_Proposal, 
          dbo.Peserta_Produk_Detail.No_Peserta, 
          dbo.Peserta_Produk_Detail.Id_Produk, 
          --SUM(dbo.Note_Terbit_Master.Tipe * isnull(dbo.Peserta_Produk_Detail.Jumlah,0)) AS Jml_Premi
          SUM(dbo.Note_Terbit_Master.Tipe* ABS(isnull(dbo.Peserta_Produk_Detail.Jumlah,0))) AS Jml_Premi

        FROM 
          dbo.Note_Terbit_Master 
          INNER JOIN
          dbo.Peserta_Produk_Detail 
          ON dbo.Note_Terbit_Master.No_Ref = dbo.Peserta_Produk_Detail.No_Ref 
          INNER JOIN
          dbo.Peserta 
          ON dbo.Note_Terbit_Master.No_Proposal = dbo.Peserta.No_Proposal AND 
             dbo.Peserta_Produk_Detail.No_Peserta = dbo.Peserta.No_Peserta 
        WHERE   
          (dbo.Note_Terbit_Master.Jenis <= 19) 
          AND (dbo.Note_Terbit_Master.Status<>'C') 
          AND (dbo.Note_Terbit_Master.No_Proposal = @NoProposal OR isnull(@NoProposal,'')='')
          AND (dbo.Note_Terbit_Master.Tgl_Buat>=@TglPremi1 OR isnull(@TglPremi1Ya,0)=0)
          AND (dbo.Note_Terbit_Master.Tgl_Buat<=@TglPremi2 OR isnull(@TglPremi2Ya,0)=0)
        GROUP BY 
          dbo.Note_Terbit_Master.No_Proposal, dbo.Peserta_Produk_Detail.No_Peserta, dbo.Peserta_Produk_Detail.Id_Produk
       ) 
       as T2 
       ON T1.No_Proposal=T2.No_Proposal AND 
          T1.No_Peserta=T2.No_Peserta AND 
          T1.Id_Produk=T2.Id_Produk
       LEFT OUTER JOIN
       (
         SELECT  
           dbo.Klaim_Ref_Detail.No_Proposal, 
           dbo.Klaim_Ref_Detail.No_Santunan as No_Peserta, 
           dbo.Klaim_Ref_Detail.Id_Produk, 
           SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Diajukan,0))) as Jml_Diajukan,
           SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar,0)) - Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar_Old,0))) as Jml_Dibayar
         FROM 
           dbo.Note_Terbit_Master 
           INNER JOIN 
           dbo.Klaim_Ref_Detail 
           ON dbo.Note_Terbit_Master.No_Ref = dbo.Klaim_Ref_Detail.No_Ref
           INNER JOIN  -- add zai 20140524
           (
             SELECT * FROM dbo.Klaim_Peserta
             WHERE Status = 'I'               
           ) AS kp
           ON dbo.Klaim_Ref_Detail.No_Proposal = kp.No_Proposal AND 
              dbo.Klaim_Ref_Detail.No_Peserta = kp.No_Peserta AND 
              dbo.Klaim_Ref_Detail.No_Klaim = kp.No_Klaim       
         WHERE 
          (dbo.Note_Terbit_Master.Jenis IN (20, 21, 22, 23, 24, 25, 29))  -- 29 jenis note baru, by Pribadi Pamungkas 2015-04-07
          AND (dbo.Note_Terbit_Master.Status<>'C') 
          AND (dbo.Note_Terbit_Master.No_Proposal = @NoProposal OR isnull(@NoProposal,'')='')
          AND (dbo.Note_Terbit_Master.Tgl_Buat>=@TglKlaim1 OR isnull(@TglKlaim1Ya,0)=0)
          AND (dbo.Note_Terbit_Master.Tgl_Buat<=@TglKlaim2 OR isnull(@TglKLaim2Ya,0)=0)
        GROUP BY 
          dbo.Klaim_Ref_Detail.No_Proposal, dbo.Klaim_Ref_Detail.No_Santunan, dbo.Klaim_Ref_Detail.Id_Produk
       ) 
       as T3 
       ON T1.No_Proposal=T3.No_Proposal AND 
          T1.No_Peserta=T3.No_Peserta AND 
          T1.Id_Produk=T3.Id_Produk

    GROUP BY 
      T1.Nama_Perusahaan, 
      T1.No_Polis, 
      T1.GT_OG, 
      T1.Sub_Init, 
      T1.Tgl_Mulai, 
      T1.Tgl_Selesai,     
      T1.jml_Karyawan,          --add zai 20140619
      T1.jml_peserta,           --add zai 20140619 
      T1.Deskripsi,             --add zai 20140619
      
      T1.Status_Peserta,
      T1.Status_Kawin,
      --T1.Grade,
      
      T1.Nama_Sub
    ORDER BY 
      T1.Nama_Perusahaan, 
      T1.No_Polis, 
      T1.GT_OG, 
      T1.Sub_Init, 
      T1.Tgl_Mulai, 
      T1.Tgl_Selesai, 
      T1.jml_Karyawan,         --add zai 20140619
      T1.jml_peserta,          --add zai 20140619
      T1.Deskripsi,            --add zai 20140619  
      T1.Nama_Sub


  FETCH NEXT FROM list_cursor INTO @no_proposal  
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 


