USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_Rep_Portofolio_OpenPolis]    Script Date: 07/23/2013 13:54:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- by Pribadi Pamungkas, 2013-07-23
ALTER Procedure [dbo].[proc_Rep_Portofolio_OpenPolis] 
@TglMulai Datetime, 
@TglPesertaAwal Datetime, 
@TglPesertaAkhir Datetime, 
@TglMulaYa bit,
@TglPesertaAwalYa bit, 
@TglPesertaAkhirYa bit 
AS
BEGIN

DECLARE @tabel_id varchar(50)
DECLARE @nama_tabel_1 varchar(100)

SELECT @tabel_id=REPLACE(newid(),'-', '')   -- generate unique ID

SELECT @nama_tabel_1='TmpInsurance.dbo.Rep_Portofolio_OpenPolis_'+@tabel_id   -- nama tabel pertama
--SELECT @tabel_id

DECLARE   @ExecSQL NVARCHAR(400)
SET @ExecSQL = 'CREATE TABLE '+@nama_tabel_1+'([Ket] [varchar] (8) NULL ,	[No_Proposal] [varchar] (10) NULL ,	[Jml_Peserta] [int] NULL ,	[Jml_UP] [int] NULL )'
EXEC (@ExecSQL)   -- create tabel pertama


-- saldo awal
INSERT INTO TmpInsurance.dbo.Rep_Portofolio_OpenPolis (Ket, No_Proposal)
SELECT 'awal', No_Proposal 
FROM Polis_Asuransi 
WHERE 
  (Tgl_Mulai <= @TglMulai) AND 
  (Tgl_Selesai IS NULL) AND
  (Status <> 'C') AND 
  (Status <> 'G')


/*** Langkah ke-2: DETAIL PESERTA ***/
EXEC TmpInsurance..gproc_DelTable 'Rep_Portofolio_OpenPolis_Detail'
CREATE TABLE [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis_Detail](
	[No_Proposal] [varchar] (10) NULL ,
	[No_Peserta] [varchar] (12) NULL ,
	[Id_Produk] [varchar] (10) NULL ,
	[Sub_Kelompok] [varchar] (6) NULL ,
	[Tgl_Mulai] [datetime] NULL ,
	[Tgl_Selesai] [datetime] NULL ,
	[Kelas] [varchar] (10) NULL ,
	[Curr_Premi] [float] NULL ,
	[Curr_Santunan] [float] NULL ,
	[New_UP] [float] NULL ,
)

INSERT INTO TmpInsurance.dbo.Rep_Portofolio_OpenPolis_Detail (
  No_Proposal, No_Peserta, Id_Produk, Sub_Kelompok, Tgl_Mulai, Tgl_Selesai, Kelas, Curr_Premi, Curr_Santunan, New_UP
)
SELECT
    pst.No_Proposal,
    pst.No_Peserta,
    pp.Id_Produk,
    pp.Sub_Kelompok,
    pp.Tgl_Mulai,
    pp.Tgl_Selesai,
    pp.Kelas,
    pp.Curr_Premi,
    pp.Curr_Santunan,
    CASE
      WHEN (pp.Curr_Santunan >= 1000000000) AND (LEFT(Sub_Kelompok, 2) = '03') THEN dbo.func_GetUnlimited(pp.Id_Produk, pp.Kelas)
      ELSE pp.Curr_Santunan 
    END
FROM
    (SELECT * FROM Peserta
       WHERE No_Proposal IN (
         SELECT No_Proposal FROM [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis]) 
         AND (Tgl_Mulai>=@TglPesertaAwal AND Tgl_Mulai<=@TglPesertaAkhir)
    ) pst
    INNER JOIN
    Peserta_Produk pp 
    ON (pst.No_Proposal = pp.No_Proposal) AND
       (pst.No_Peserta = pp.No_Peserta)
        

/*** Langkah ke-3: PESERTA ***/

SELECT
  h.Ket,
  h.No_Proposal,
  pst.No_Peserta,
  pst.Kelompok
INTO [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis_Peserta]
FROM
  [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis] h
  INNER JOIN
  (
    -- Ambil Peserta
    SELECT
      DISTINCT
      No_Proposal,
      No_Peserta,
      LEFT(Sub_Kelompok, 2) AS Kelompok
    FROM [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis_Detail]
    WHERE
      (Sub_Kelompok <> '0310') AND -- DBD tidak masuk
      (
        (LEFT(Sub_Kelompok, 2) <> '03') OR -- Selain askes
        (Sub_Kelompok = '0302')            -- Askes tetapi hanya RI
      )
  ) pst
  ON h.No_Proposal = pst.No_Proposal


/*** Langkah ke-4: REKAP ***/

SELECT
  x.No_Proposal,
  y.Nama_Perusahaan, 
  y.No_Polis, 
  y.Tgl_Mulai, 
  y.Tgl_Selesai,
  (
    CASE
      WHEN x.Kelompok = '01' THEN 'AJB'
      WHEN x.Kelompok = '02' THEN 'ACC'
      WHEN x.Kelompok = '03' THEN 'Askes'
      ELSE 'Lain2'
    END
  ) AS Produk,
  (
    CASE 
      WHEN y.Mata_Uang = '01' THEN 'IDR'
      WHEN y.Mata_Uang = '02' THEN 'USD'
      ELSE 'Lain2'
    END
  ) AS Mata_Uang,

  SUM(x.Polis_Awal) AS Polis_Awal,
  SUM(x.Polis_Baru) AS Polis_Baru,
  SUM(x.Polis_Lapse) AS Polis_Lapse,
  (SUM(x.Polis_Awal) + SUM(x.Polis_Baru) - SUM(x.Polis_Lapse)) AS Polis_Akhir,

  SUM(x.Peserta_Awal) AS Peserta_Awal,
  SUM(x.Peserta_Baru) AS Peserta_Baru,
  SUM(x.Peserta_Lapse) AS Peserta_Lapse,
  (SUM(x.Peserta_Awal) + SUM(x.Peserta_Baru) - SUM(x.Peserta_Lapse)) AS Peserta_Akhir,
  
  SUM(x.Premi_Awal) AS Premi_Awal,
  SUM(x.Premi_Baru) AS Premi_Baru,
  SUM(x.Premi_Lapse) AS Premi_Lapse,
  (SUM(x.Premi_Awal) + SUM(x.Premi_Baru) - SUM(x.Premi_Lapse)) AS Premi_Akhir,

  SUM(x.UP_Awal) AS UP_Awal,
  SUM(x.UP_Baru) AS UP_Baru,
  SUM(x.UP_Lapse) AS UP_Lapse,
  (SUM(x.UP_Awal) + SUM(x.UP_Baru) - SUM(x.UP_Lapse)) AS UP_Akhir
INTO TmpInsurance.dbo.Rep_Portofolio_OpenPolis_Rekap
FROM
  (
    SELECT * 
    FROM
      (
        -- Peserta Awal
        SELECT
          No_Proposal,
          Kelompok,
          1 AS Polis_Awal,
          COUNT(No_Peserta) AS Peserta_Awal,
          0 AS Polis_Baru,
          0 AS Peserta_Baru,
          0 AS Polis_Lapse,
          0 AS Peserta_Lapse,
          0 AS Premi_Awal,
          0 AS Premi_Baru,
          0 AS Premi_Lapse,
          0 AS UP_Awal,
          0 AS UP_Baru,
          0 AS UP_Lapse
        FROM [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis_Peserta]
        WHERE (Ket = 'awal')
        GROUP BY
          No_Proposal,
          Kelompok
      ) awal

      UNION ALL

      
      (
        -- Peserta Baru
        SELECT
          No_Proposal,
          Kelompok,
          0 AS Polis_Awal,
          0 AS Peserta_Awal,
          1 AS Polis_Baru,
          COUNT(No_Peserta) AS Peserta_Baru,
          0 AS Polis_Lapse,
          0 AS Peserta_Lapse,
          0 AS Premi_Awal,
          0 AS Premi_Baru,
          0 AS Premi_Lapse,
          0 AS UP_Awal,
          0 AS UP_Baru,
          0 AS UP_Lapse
        FROM [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis_Peserta]
        WHERE (Ket = 'new')
        GROUP BY
          No_Proposal,
          Kelompok      
      ) 
      
      UNION ALL

      (
        -- Peserta  Lapse
        SELECT
          No_Proposal,
          Kelompok,
          0 AS Polis_Awal,
          0 AS Peserta_Awal,
          0 AS Polis_Baru,
          0 AS Peserta_Baru,
          1 AS Polis_Lapse,
          COUNT(No_Peserta) AS Peserta_Lapse,
          0 AS Premi_Awal,
          0 AS Premi_Baru,
          0 AS Premi_Lapse,
          0 AS UP_Awal,
          0 AS UP_Baru,
          0 AS UP_Lapse
        FROM [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis_Peserta]
        WHERE (Ket = 'lapse')
        GROUP BY
          No_Proposal,
          Kelompok      
      ) 

      UNION ALL

      ( 
        -- Premi Awal
        SELECT
          a.No_Proposal,
          LEFT(b.Sub_Kelompok, 2) AS Kelompok,
          0 AS Polis_Awal,
          0 AS Peserta_Awal,
          0 AS Polis_Baru,
          0 AS Peserta_Baru,
          0 AS Polis_Lapse,
          0 AS Peserta_Lapse,
          SUM(b.Curr_Premi) AS Premi_Awal,
          0 AS Premi_Baru,
          0 AS Premi_Lapse,
          0 AS UP_Awal,
          0 AS UP_Baru,
          0 AS UP_Lapse
        FROM 
          [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis] a
          INNER JOIN
          [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis_Detail] b
          ON a.No_Proposal = b.No_Proposal
        WHERE 
          (a.Ket = 'awal') AND
          (b.Sub_Kelompok <> '0310') -- DBD tidak masuk
        GROUP BY
          a.No_Proposal,
          LEFT(b.Sub_Kelompok, 2)
      )

      UNION ALL

      ( 
        -- Premi Baru
        SELECT
          a.No_Proposal,
          LEFT(b.Sub_Kelompok, 2) AS Kelompok,
          0 AS Polis_Awal,
          0 AS Peserta_Awal,
          0 AS Polis_Baru,
          0 AS Peserta_Baru,
          0 AS Polis_Lapse,
          0 AS Peserta_Lapse,
          0 AS Premi_Awal,
          SUM(b.Curr_Premi) AS Premi_Baru,
          0 AS Premi_Lapse,
          0 AS UP_Awal,
          0 AS UP_Baru,
          0 AS UP_Lapse
        FROM 
          [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis] a
          INNER JOIN
          [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis_Detail] b
          ON a.No_Proposal = b.No_Proposal
        WHERE 
          (a.Ket = 'new') AND
          (b.Sub_Kelompok <> '0310') -- DBD tidak masuk
        GROUP BY
          a.No_Proposal,
          LEFT(b.Sub_Kelompok, 2)
      )
      

      UNION ALL

      ( 
        -- Premi Lapse
        SELECT
          a.No_Proposal,
          LEFT(b.Sub_Kelompok, 2) AS Kelompok,
          0 AS Polis_Awal,
          0 AS Peserta_Awal,
          0 AS Polis_Baru,
          0 AS Peserta_Baru,
          0 AS Polis_Lapse,
          0 AS Peserta_Lapse,
          0 AS Premi_Awal,
          0 AS Premi_Baru,
          SUM(b.Curr_Premi) AS Premi_Lapse,
          0 AS UP_Awal,
          0 AS UP_Baru,
          0 AS UP_Lapse
        FROM 
          [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis] a
          INNER JOIN
          [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis_Detail] b
          ON a.No_Proposal = b.No_Proposal
        WHERE 
          (a.Ket = 'lapse') AND
          (b.Sub_Kelompok <> '0310') -- DBD tidak masuk
        GROUP BY
          a.No_Proposal,
          LEFT(b.Sub_Kelompok, 2)
      )

      UNION ALL

      ( 
        -- UP Awal
        SELECT
          a.No_Proposal,
          LEFT(b.Sub_Kelompok, 2) AS Kelompok,
          0 AS Polis_Awal,
          0 AS Peserta_Awal,
          0 AS Polis_Baru,
          0 AS Peserta_Baru,
          0 AS Polis_Lapse,
          0 AS Peserta_Lapse,
          0 AS Premi_Awal,
          0 AS Premi_Baru,
          0 AS Premi_Lapse,
          SUM(b.New_UP) AS UP_Awal,
          0 AS UP_Baru,
          0 AS UP_Lapse
        FROM 
          [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis] a
          INNER JOIN
          [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis_Detail] b
          ON a.No_Proposal = b.No_Proposal
        WHERE 
          (a.Ket = 'awal') AND
          (b.Sub_Kelompok <> '0310') -- DBD tidak masuk
        GROUP BY
          a.No_Proposal,
          LEFT(b.Sub_Kelompok, 2)
      )
      
      UNION ALL

      ( 
        -- UP Baru
        SELECT
          a.No_Proposal,
          LEFT(b.Sub_Kelompok, 2) AS Kelompok,
          0 AS Polis_Awal,
          0 AS Peserta_Awal,
          0 AS Polis_Baru,
          0 AS Peserta_Baru,
          0 AS Polis_Lapse,
          0 AS Peserta_Lapse,
          0 AS Premi_Awal,
          0 AS Premi_Baru,
          0 AS Premi_Lapse,
          0 AS UP_Awal,
          SUM(b.New_UP) AS UP_Baru,
          0 AS UP_Lapse
        FROM 
          [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis] a
          INNER JOIN
          [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis_Detail] b
          ON a.No_Proposal = b.No_Proposal
        WHERE 
          (a.Ket = 'new') AND
          (b.Sub_Kelompok <> '0310') -- DBD tidak masuk
        GROUP BY
          a.No_Proposal,
          LEFT(b.Sub_Kelompok, 2)
      )

      UNION ALL

      ( 
        -- UP Lapse
        SELECT
          a.No_Proposal,
          LEFT(b.Sub_Kelompok, 2) AS Kelompok,
          0 AS Polis_Awal,
          0 AS Peserta_Awal,
          0 AS Polis_Baru,
          0 AS Peserta_Baru,
          0 AS Polis_Lapse,
          0 AS Peserta_Lapse,
          0 AS Premi_Awal,
          0 AS Premi_Baru,
          0 AS Premi_Lapse,
          0 AS UP_Awal,
          0 AS UP_New,
          SUM(b.New_UP) AS UP_Lapse
        FROM 
          [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis] a
          INNER JOIN
          [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis_Detail] b
          ON a.No_Proposal = b.No_Proposal
        WHERE 
          (a.Ket = 'lapse') AND
          (b.Sub_Kelompok <> '0310') -- DBD tidak masuk
        GROUP BY
          a.No_Proposal,
          LEFT(b.Sub_Kelompok, 2)
      )
      
  ) x

  LEFT JOIN

  (
    SELECT 
      pa.Mata_Uang, 
      pa.No_Proposal, 
      prs.Nama_Perusahaan, 
      pa.No_Polis, 
      pa.Tgl_Mulai, 
      pa.Tgl_Selesai 
    FROM 
      Polis_Asuransi pa
      INNER JOIN 
      Perusahaan prs
      ON pa.No_Prs = prs.No_Prs
  ) y 
  ON x.No_Proposal = y.No_Proposal
  
--WHERE
--  (No_Proposal = '2010.0741')
GROUP BY
  x.No_Proposal,
  y.Nama_Perusahaan, 
  y.No_Polis, 
  y.Tgl_Mulai, 
  y.Tgl_Selesai,
  (
    CASE
      WHEN x.Kelompok = '01' THEN 'AJB'
      WHEN x.Kelompok = '02' THEN 'ACC'
      WHEN x.Kelompok = '03' THEN 'Askes'
      ELSE 'Lain2'
    END
  ),
  (
    CASE 
      WHEN y.Mata_Uang = '01' THEN 'IDR'
      WHEN y.Mata_Uang = '02' THEN 'USD'
      ELSE 'Lain2'
    END
  )
END

SELECT * FROM [TmpInsurance].[dbo].[Rep_Portofolio_OpenPolis_Rekap]