-- 2014.0835 & 2014.0771

  
-- For Debug --
--------------------------------------------
DECLARE
  @IdBroker varchar(5) = '',
  @GroupPrs varchar(5) = '',
  @GTOG varchar(2) = '',
  @NoProposal varchar(9) = '2014.0771',
  @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 = 0,
  @TglMulai2Ya BIT = 0,
  @TglSelesai1Ya BIT = 0,
  @TglSelesai2Ya BIT = 0,
  @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


  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
    T1.Deskripsi AS Cara_Bayar,    --add zai 20140619   
    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.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          
      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
    ) 
    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.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


