USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_Rep10361]    Script Date: 03/27/2018 09:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


-- changelog:
-- - Zainul Fahmi Nasution 2014-09-09. Sebelumnya pada form report nya ada filter [SumberBisnis] tapi di script ini belum ditambahkan filternya.
-- - Pribadi Pamungkas. 2018-03-27. Ticket RQ2018030817 => Permintaannya yaitu DN yg ditarik tidak hanya status I saja, namun semua selain Cancel. Dicek script ternyata sudah sesuai. Sehingga hanya diubah filter untuk AJB, sebelumnya '0100', diganti menjadi LEFT(Sub_Kelompok,3) = '010' agar AJB (Natural Death) ikut dihitung

ALTER Procedure [dbo].[proc_Rep10361]
  @TglAwal datetime,
  @TglAkhir datetime,
  @TglTengah datetime,
  @IdValas varchar(4),
  @SubInit int,
  @GTOG int,
  @IdMarketing Varchar(5),
  @NoProposal varchar(9),
  @SumberBisnis varchar(100),
  @TglAwalYa bit,
  @TglAkhirYa bit,
  @TglTengahYa BIT
  
AS

  DECLARE @No_Proposal varchar(9)
  DECLARE @nama_sumber_bisnis varchar(100)
  DECLARE @Premi_Brutto float
  DECLARE @Premi_Diskon float
  DECLARE @Premi_Netto  float
  DECLARE @Sub_Kelompok varchar(10)
  DECLARE @Sub_Init varchar (1)
  DECLARE @GT_OG varchar (2)
  DECLARE @Id_Valas varchar (4)

  IF isnull(@SubInit,0)=0 SET @Sub_Init=''
  ELSE IF isnull(@SubInit,0)=1 SET @Sub_Init='I'
  ELSE IF isnull(@SubInit,0)=2 SET @Sub_Init='S'

  IF isnull(@GTOG,0)=0 SET @GT_OG=''
  ELSE IF isnull(@GTOG,0)=1 SET @GT_OG='GT'
  ELSE IF isnull(@GTOG,0)=2 SET @GT_OG='OG'

  IF isnull(@IdValas,0)=0 SET @Id_Valas='01'
  ELSE IF isnull(@IdValas,0)=1 SET @Id_Valas='02'
  ELSE IF isnull(@IdValas,0)=2 SET @Id_Valas='03'
  
  EXEC TmpInsurance.dbo.gproc_DelTable 'Rep10361'
  CREATE TABLE [TmpInsurance].[dbo].[Rep10361] (
	   [No_Proposal] [varchar] (9) NOT NULL ,
	   [Sub_Kelompok] [varchar] (10) NOT NULL ,
	   [nama_sumber_bisnis] [varchar] (100) NULL,
	   [Premi_Brutto1] [float] NULL ,
	   [Premi_Diskon1] [float] NULL ,
	   [Premi_Netto1] [float] NULL ,
     [Premi_Brutto2] [float] NULL ,
	   [Premi_Diskon2] [float] NULL ,
	   [Premi_Netto2] [float] NULL ,
	   CONSTRAINT [PK_Rep10361] PRIMARY KEY  CLUSTERED 
	  ([No_Proposal], [Sub_Kelompok])  ON [PRIMARY] 
  ) ON [PRIMARY]
  
-- sp_Columns 'note_terbit_detail'

BEGIN TRAN
  INSERT INTO [TmpInsurance].[dbo].Rep10361 (No_Proposal, Sub_Kelompok, nama_sumber_bisnis, Premi_Brutto1, Premi_Diskon1, Premi_Netto1)
  SELECT A.No_Proposal, F.Sub_Kelompok,G.nama_sumber_bisnis,
         isnull(E.Pengali * Sum(isnull(F.Jumlah,0)), 0) AS Premi_Brutto,
         isnull(E.Pengali * Sum(isnull(F.Diskon,0)), 0) AS Diskon_Thn,
         isnull(E.Pengali * Sum(isnull(F.Total,0)), 0) AS Premi_Netto
  FROM 
      polis_asuransi A  
      LEFT OUTER JOIN
      sumber_bisnis G
      ON G.kode_bisnis=A.kode_bisnis
      INNER JOIN --LEFT JOIN 
      Note_Terbit_Master B
      ON (B.Status <> 'C') AND 
      (B.Jenis IN (1,2)) AND 
      --(B.Tgl_Awal = A.Tgl_Mulai) AND 
      (B.No_Proposal = A.No_Proposal)  INNER JOIN Note_Terbit_Detail F ON B.No_Ref =F.No_Ref
      INNER JOIN
      Perusahaan C 
      ON A.No_Prs = C.No_Prs INNER JOIN --LEFT OUTER JOIN 
      .[gUser_Name] D
      ON A.Marketing = D.Id_User
      INNER JOIN 
      Periode_Bayar E 
      ON A.Periode_Bayar = E.Kode
  WHERE (A.Status<>'C')
      AND (A.No_Proposal=@NoProposal OR isnull(@NoProposal,'')='')
      AND (A.Marketing=@IdMarketing OR isnull(@IdMarketing,'')='')
      AND (A.Mata_Uang=@Id_Valas OR isnull(@Id_Valas,'')='')
      AND (A.Sub_Init=@Sub_Init OR isnull(@Sub_Init,'')='')
--      AND (A.kode_bisnis=@nama_sumber_bisnis OR isnull(@nama_sumber_bisnis,'')='')
      AND (A.kode_bisnis=@SumberBisnis OR isnull(@SumberBisnis,'')='')     -- add zai 20140909
      AND (C.GT_OG=@GT_OG OR isnull(@GT_OG,'')='')
      AND (B.Tgl_Buat>=@TglAwal OR isnull(@TglAkhirYa,0)=0)
      AND (B.Tgl_Buat<=@TglAkhir  OR isnull(@TglAkhirYa,0)=0)
  GROUP BY
      A.No_Proposal, F.Sub_Kelompok,G.nama_sumber_bisnis,
      E.Pengali
      
IF @@Error>0 ROLLBACK TRAN
ELSE COMMIT TRAN

---------------------------------

DECLARE Employee_Cursor CURSOR FOR
  SELECT No_Proposal, Sub_kelompok FROM [TmpInsurance].[dbo].[Rep10361] 
  
OPEN Employee_Cursor

FETCH NEXT FROM Employee_Cursor INTO @No_Proposal, @Sub_Kelompok

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT 
      @Premi_Brutto=isnull(E.Pengali * Sum(isnull(F.Jumlah,0)), 0),-- AS Premi_Brutto,
      @Premi_Diskon=isnull(E.Pengali * Sum(isnull(F.Diskon,0)), 0),-- AS Diskon_Thn,
      @Premi_Netto =isnull(E.Pengali * Sum(isnull(F.Total,0)), 0)-- AS Premi_Netto
    FROM 
      polis_asuransi A 
      INNER JOIN  
      Note_Terbit_Master B
      ON (B.Status <> 'C') AND 
         (B.Jenis IN (1,2)) 
         AND  (B.No_Proposal = A.No_Proposal)INNER JOIN Note_Terbit_Detail F ON B.No_Ref = F.No_Ref
      INNER JOIN
      Perusahaan C 
      ON A.No_Prs = C.No_Prs 
      INNER JOIN --LEFT OUTER JOIN 
      .[gUser_Name] D
      ON A.Marketing = D.Id_User
      INNER JOIN 
      Periode_Bayar E 
      ON A.Periode_Bayar = E.Kode
    WHERE A.Status<>'C' AND A.No_Proposal=@No_Proposal 
      AND (Sub_Kelompok=@Sub_Kelompok)
      AND (A.Mata_Uang=@Id_Valas OR isnull(@Id_Valas,'')='')
      AND (A.Sub_Init=@Sub_Init OR isnull(@Sub_Init,'')='')
--      AND (A.kode_bisnis=@nama_sumber_bisnis OR isnull(@nama_sumber_bisnis,'')='')
      AND (A.kode_bisnis=@SumberBisnis OR isnull(@SumberBisnis,'')='')     -- add zai 20140909
      AND (C.GT_OG=@GT_OG OR isnull(@GT_OG,'')='')
      AND (B.Tgl_Buat>=@TglAwal OR isnull(@TglAwalYa,0)=0)
      AND (B.Tgl_Buat<=@TglAkhir OR isnull(@TglAkhirYa,0)=0)
    GROUP BY
      E.Pengali
      
    BEGIN TRAN
      IF EXISTS(SELECT * FROM [TmpInsurance].[dbo].[Rep10361] WHERE No_Proposal=@No_Proposal AND Sub_Kelompok=@Sub_Kelompok)
        UPDATE [TmpInsurance].[dbo].Rep10361 SET Premi_Brutto2=@Premi_Brutto, Premi_Diskon2=@Premi_Diskon, Premi_Netto2=@Premi_Netto
        WHERE No_Proposal=@No_Proposal AND Sub_Kelompok=@Sub_Kelompok
      ELSE
        INSERT INTO [TmpInsurance].[dbo].Rep10361 (No_Proposal, Sub_Kelompok, Premi_Brutto2, Premi_Diskon2, Premi_Netto2)
        VALUES(@No_Proposal, @Sub_Kelompok, @Premi_Brutto, @Premi_Diskon, @Premi_Netto)

    IF @@Error>0 ROLLBACK TRAN
    ELSE COMMIT TRAN
    
    FETCH NEXT FROM Employee_Cursor INTO @No_Proposal, @Sub_Kelompok
  
END

CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor


---------------VIEW--------------------
SELECT dbo.Polis_Asuransi.Tgl_Mulai, 
       dbo.Polis_Asuransi.Tgl_Selesai, 
   --    dbo.Polis_Asuransi.Marketing AS Kode_Mkt, 
       dbo.gUser_Name.Nama AS Marketing_NB,
       REN.nama AS marketing_REN, 
       dbo.Perusahaan.Nama_Perusahaan, 
       dbo.Polis_Asuransi.No_Polis, 
       dbo.Perusahaan.GT_OG, 
       --dbo.Polis_Asuransi.Jml_Peserta, 
       count_peserta.jml_peserta ,  -- add zai
       dbo.Broker.Broker_Nama, 
       dbo.sumber_bisnis.nama_sumber_bisnis AS Nama_Sumber_Bisnis, 
       dbo.Sub_Kelompok.Nama_Sub AS Produk,
       CASE
         --WHEN dbo.Sub_Kelompok.Sub_Kelompok = '0100' THEN ttl_up_ajb.total_up_ajb
         WHEN LEFT(dbo.Sub_Kelompok.Sub_Kelompok,3) = '010' THEN ttl_up_ajb.total_up_ajb	-- semua jenis AJB. modified by Pribadi Pamungkas. 2018-03-27
         WHEN dbo.Sub_Kelompok.Sub_Kelompok = '0200' THEN ttl_up_acc.total_up_acc
         WHEN dbo.Sub_Kelompok.Sub_Kelompok = '0500' THEN ttl_up_tpd.total_up_tpd
         WHEN dbo.Sub_Kelompok.Sub_Kelompok = '0111' THEN ttl_up_ci.total_up_ci
         ELSE 0
       END AS total_up , -- add zai    
       Periode_Bayar.Deskripsi as Periode_Bayar, 
       TmpInsurance.dbo.Rep10361.Premi_Brutto1 AS MTD_Premi_Brutto, 
       TmpInsurance.dbo.Rep10361.Premi_Diskon1 AS MTD_Premi_Diskon, 
       TmpInsurance.dbo.Rep10361.Premi_Netto1 AS MTD_Premi_Netto, 
       TmpInsurance.dbo.Rep10361.Premi_Brutto2 AS MTY_Premi_Brutto, 
       TmpInsurance.dbo.Rep10361.Premi_Diskon2 AS MTY_Premi_Diskon, 
       TmpInsurance.dbo.Rep10361.Premi_Netto2 AS MTY_Premi_Netto
FROM   TmpInsurance.dbo.Rep10361 
       INNER JOIN
       dbo.Polis_Asuransi 
       ON TmpInsurance.dbo.Rep10361.No_Proposal = dbo.Polis_Asuransi.No_Proposal 
       LEFT OUTER JOIN
       dbo.sumber_bisnis 
       ON dbo.sumber_bisnis.kode_bisnis = dbo.Polis_Asuransi.kode_bisnis 
       INNER JOIN
       dbo.Perusahaan 
       ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs 
       INNER JOIN
       dbo.gUser_Name 
       ON dbo.Polis_Asuransi.Marketing = dbo.gUser_Name.Id_User 
       LEFT OUTER JOIN
       dbo.gUser_Name AS REN 
       ON dbo.Polis_Asuransi.Marketing_REN = REN.Id_User 
       INNER JOIN
       dbo.Sub_Kelompok 
       ON TmpInsurance.dbo.Rep10361.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok 
       LEFT OUTER JOIN
       dbo.Broker 
       ON dbo.Polis_Asuransi.Broker_Id = dbo.Broker.Broker_Id 
       INNER JOIN 
       Periode_Bayar ON dbo.Polis_Asuransi.Periode_Bayar=Periode_Bayar.Kode 
       LEFT JOIN
       (
         SELECT 
           No_Proposal,
           SUM(Benefit) AS total_up_ajb
         FROM 
           dbo.Peserta_Produk
         WHERE 
           --Sub_Kelompok = '0100'    --AJB
           LEFT(Sub_Kelompok,3) = '010'		-- semua jenis AJB. modified by Pribadi Pamungkas. 2018-03-27
           AND Status = 'I'
         GROUP BY No_Proposal
       ) AS ttl_up_ajb  -- add zai  
       ON TmpInsurance.dbo.Rep10361.No_Proposal = ttl_up_ajb.No_Proposal
       LEFT JOIN
       (
         SELECT 
           No_Proposal,
           SUM(Benefit) AS total_up_acc
         FROM 
           dbo.Peserta_Produk
         WHERE Sub_Kelompok = '0200' AND Status = 'I'    --ACC
         GROUP BY No_Proposal
       ) AS ttl_up_acc  -- add zai  
       ON TmpInsurance.dbo.Rep10361.No_Proposal = ttl_up_acc.No_Proposal       
       LEFT JOIN
       (
         SELECT 
           No_Proposal,
           SUM(Benefit) AS total_up_tpd
         FROM 
           dbo.Peserta_Produk
         WHERE Sub_Kelompok = '0500' AND Status = 'I'    --TPD 
         GROUP BY No_Proposal
       ) AS ttl_up_tpd  -- add zai  
       ON TmpInsurance.dbo.Rep10361.No_Proposal = ttl_up_tpd.No_Proposal        
       LEFT JOIN
       (
         SELECT 
           No_Proposal,
           SUM(Benefit) AS total_up_ci
         FROM 
           dbo.Peserta_Produk
         WHERE Sub_Kelompok = '0111' AND Status = 'I'    --CIA
         GROUP BY No_Proposal
       ) AS ttl_up_ci  -- add zai  
       ON TmpInsurance.dbo.Rep10361.No_Proposal = ttl_up_ci.No_Proposal
       LEFT JOIN
       (
       SELECT COUNT(*) AS jml_peserta, No_Proposal FROM dbo.Peserta  
       WHERE Status = 'I'
       GROUP BY No_Proposal     
       ) AS count_peserta   -- add zai
       ON count_peserta.no_proposal =  TmpInsurance.dbo.Rep10361.No_Proposal                  
--GROUP BY dbo.Polis_Asuransi.Tgl_Mulai
ORDER BY dbo.Polis_Asuransi.Marketing, dbo.Sub_Kelompok.Nama_Sub


