USE [GrpInsurance]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[Proc_Rep_Peserta_OJK]
      @pi_no_proposal VARCHAR(9),
      @pi_tgl_mulai DATETIME,
      @pi_tgl_selesai DATETIME,
      @pi_jenis INTEGER -- 0: baru, 1: perubahan, 2: keluar (dari tagl mulai)
AS 
BEGIN
  SET NOCOUNT ON;
  DECLARE @Sebagai VARCHAR(30)
  DECLARE @Umur INT
  DECLARE @Jenis INT
  DECLARE @No INT
  DECLARE @Produk VARCHAR(20)
  DECLARE @SQLText NVARCHAR(1000)
  DECLARE @SQLTotal NVARCHAR(1000)
  DECLARE @ColField VARCHAR(50)
  DECLARE @No_Peserta VARCHAR(12) 
  DECLARE @No_Proposal VARCHAR(12) 
  DECLARE @Nama VARCHAR(30) 
  DECLARE @Nik VARCHAR(40) 
  DECLARE @Tgl_Lahir DATETIME 
  DECLARE @Tgl_Mulai DATETIME 
  DECLARE @Kelamin VARCHAR(1) 
  DECLARE @Status_Kawin VARCHAR(2) 
  DECLARE @Kelas VARCHAR(10) 
  DECLARE @Premi FLOAT
  DECLARE @Urut INT
  DECLARE @Grd INT
  DECLARE @Pln INT

  DECLARE @No_Polis VARCHAR(100)
  DECLARE @GT_OG VARCHAR(2)
  DECLARE @Masa_ASuransi VARCHAR(50)
  DECLARE @Periode_Bayar VARCHAR(30)
  DECLARE @sTglTerbit VARCHAR(100)
  DECLARE @Periode VARCHAR(100)
  DECLARE @Up FLOAT
  DECLARE @sUP VARCHAR(20)
    
  SELECT
    @No_Polis = dbo.Polis_Asuransi.No_Polis + '/' + dbo.Perusahaan.GT_OG,
    @Masa_Asuransi = dbo.func_Datetostr(dbo.Polis_Asuransi.Tgl_Mulai) + ' - ' + dbo.func_Datetostr(dbo.Polis_Asuransi.Tgl_Selesai),
    @Periode_Bayar = dbo.Periode_Bayar.Deskripsi,
    @GT_OG = dbo.Perusahaan.GT_OG
  FROM
    dbo.Polis_Asuransi
    INNER JOIN dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs
    INNER JOIN dbo.Periode_Bayar ON dbo.Polis_Asuransi.Periode_Bayar = dbo.Periode_Bayar.Kode
  WHERE
    dbo.Polis_Asuransi.No_Proposal = @pi_no_proposal
    
  EXEC gproc_CreateRepHeader 'Peserta_OJK'
  EXEC gproc_AddRepHeader 'Peserta_OJK', 'No. Polis', @No_Polis
  EXEC gproc_AddRepHeader 'Peserta_OJK', 'Masa Asuransi', @Masa_Asuransi
  EXEC gproc_AddRepHeader 'Peserta_OJK', 'Periode Bayar', @Periode_Bayar

    
  EXEC TmpInsurance.dbo.gproc_DelTable 'RepPeserta_OJK'
  SET @SQLTotal = N''
  SET @SQLText = 'CREATE TABLE [TmpInsurance].[dbo].[RepPeserta_OJK] (' + '[No_Proposal] varchar(9) NOT NULL, ' + '[No_Peserta] varchar(12) NOT NULL, ' + '[Nama] varchar(30) NULL, ' + '[Nik] varchar(30) NULL, ' + '[Tgl_Lahir] Datetime NULL, ' + '[Sex] varchar(1) NULL, ' + '[Umur] int NULL, ' + '[Status] varchar(2) NULL, ' + '[Sebagai] varchar(30) NULL, ' + '[Grd] int NULL, ' + '[Pln] int NULL, ' + '[Tgl_Mulai] Datetime NULL, '

  DECLARE TmpCursor CURSOR
  FOR
  SELECT
    dbo.Sub_Kelompok.Field_Name,
    dbo.Sub_Kelompok.Nama_Pendek
  FROM
    dbo.Produk_Polis
    INNER JOIN dbo.Sub_Kelompok ON dbo.Produk_Polis.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok
  WHERE
    (dbo.Produk_Polis.No_Proposal = @pi_no_proposal)
    AND ISNULL(dbo.Sub_Kelompok.Field_Name, '') <> ''
  GROUP BY
    dbo.Sub_Kelompok.Urutan,
    dbo.Sub_Kelompok.Field_Name,
    dbo.Sub_Kelompok.Nama_Pendek
  ORDER BY
    dbo.Sub_Kelompok.Urutan
  OPEN TmpCursor
  FETCH NEXT FROM TmpCursor INTO @ColField, @Produk
  SET @No = 10
  WHILE @@FETCH_STATUS = 0 
        BEGIN
              SET @SQLText = @SQLText + N'[' + @ColField + N'_Kelas] [varchar] (10), ' 
              SET @SQLText = @SQLText + N'[' + @ColField + N'_Premi] float Default (0), '
              IF @ColField = 'ACC'
                 OR @ColField = 'AJB' 
                 SET @SQLText = @SQLText + N'[' + @ColField + N'_Up] float Default (0), '
              SET @SQLTotal = @SQLTotal + N'[' + @ColField + N'_Premi]  +'
              SET @No = @No + 2
              FETCH NEXT FROM TmpCursor INTO @ColField, @Produk
        END
  CLOSE TmpCursor
  DEALLOCATE TmpCursor

  SET @SQLText = @SQLText + '[Total] float default(0), '
  SET @SQLTotal = @SQLTotal + N'0'
  SET @SQLText = @SQLText + N' CONSTRAINT pk_RepPeserta_OJK PRIMARY KEY ([No_Proposal], [No_Peserta])) ON [PRIMARY]'
     
     
  EXEC(@SQLText)

           DECLARE TmpCursor CURSOR
           FOR
           SELECT
            pst.No_Proposal,
            pst.No_Peserta,
            pst.Nama,
            pst.Nik,
            pst.Tgl_Lahir,
            pst.Kelamin,
            pst.Status_Kawin,
            pst.Grade,
            pst.Pln,
            pst.Tgl_Mulai,
            pp.Kelas,
            sk.Field_Name,
            SUM(pp.Curr_Premi),
            sk.Urutan,
            sbg.Status_Peserta,
            dbo.func_GetUmur(pst.Tgl_Lahir, pst.Tgl_Mulai),
            SUM(pp.Benefit)
           FROM
            dbo.Peserta AS pst INNER JOIN 
            dbo.Peserta_Produk AS pp ON pst.No_Proposal = pp.No_Proposal
                                        AND pst.No_Peserta = pp.No_Peserta INNER JOIN 
            dbo.Sub_Kelompok AS sk ON pp.Sub_Kelompok = sk.Sub_Kelompok INNER JOIN 
            dbo.Sebagai AS sbg ON pst.Sebagai = sbg.IDI INNER JOIN 
            dbo.Produk_Polis AS pp2 ON pp.No_Proposal = pp2.No_Proposal
                                AND pp.Id_Produk = pp2.Id_Produk
           WHERE
            pp.Status NOT IN ('G','C')
            AND ( (pst.Status = 'I') AND (@pi_jenis IN (0,1)) )
            AND ( (pst.Status = 'O') AND (@pi_jenis = 2) )
            AND (pp.No_Proposal = @pi_no_proposal)
            AND (pst.No_Proposal = pp.No_Proposal)
            
            AND ( (pst.Tgl_Mulai >= @pi_tgl_mulai AND pst.Tgl_Mulai <= @pi_tgl_selesai) AND (@pi_jenis = 0) )
            
            --? AND (pp.Curr_Premi <> 0)
           GROUP BY
            pst.No_Proposal,
            pst.No_Peserta,
            pst.Nama,
            pst.Nik,
            pst.Tgl_Lahir,
            pst.Kelamin,
            pst.Tgl_Mulai,
            pst.Status_Kawin,
            pp.Kelas,
            sk.Field_Name,
            sk.Urutan,
            pst.Grade,
            pst.Pln,
            sbg.Status_Peserta
           ORDER BY
            pst.No_Proposal,
            pst.No_Peserta,
            sk.Urutan 

           OPEN TmpCursor
           FETCH NEXT FROM TmpCursor INTO @No_Proposal, @No_Peserta, @Nama, @Nik, @Tgl_Lahir, @Kelamin, @Status_Kawin, @Grd, @Pln, @Tgl_Mulai, @Kelas, @ColField, @Premi, @Urut, @Sebagai, @Umur, @Up
           WHILE @@FETCH_STATUS = 0 
                 BEGIN
                       BEGIN TRAN
                       IF NOT EXISTS ( SELECT
                                        *
                                       FROM
                                        TmpInsurance.dbo.RepPeserta_OJK
                                       WHERE
                                        [No_Proposal] = @No_Proposal
                                        AND [No_Peserta] = @No_Peserta ) 
                          INSERT  INTO TmpInsurance.dbo.RepPeserta_OJK
                                  ([No_Proposal],
                                   [No_Peserta],
                                   [Nama],
                                   [Nik],
                                   [Tgl_Lahir],
                                   [Sex],
                                   [Status],
                                   Grd,
                                   Pln,
                                   [Tgl_Mulai],
                                   Sebagai,
                                   Umur)
                          VALUES
                                  (@No_Proposal,
                                   @No_Peserta,
                                   @Nama,
                                   @Nik,
                                   @Tgl_Lahir,
                                   @Kelamin,
                                   @Status_Kawin,
                                   @Grd,
                                   @Pln,
                                   @Tgl_Mulai,
                                   @Sebagai,
                                   @Umur)
      
                       EXEC ('UPDATE TmpInsurance.dbo.RepPeserta_OJK SET ['+@ColField+'_Premi]='+@Premi+', ['+@ColField+'_Kelas]='''+@Kelas+''' WHERE [No_Proposal]= '''+@No_Proposal+''' AND [No_Peserta]= '''+@No_Peserta+'''')
                       IF @ColField = 'ACC'
                          OR @ColField = 'AJB' 
                          BEGIN
                                SET @sUP = STR(ISNULL(@up, 0), 15)
                                EXEC ('UPDATE TmpInsurance.dbo.RepPeserta_OJK SET ['+@ColField+'_UP]='+@sUp+' WHERE [No_Proposal]= '''+@No_Proposal+''' AND [No_Peserta]= '''+@No_Peserta+'''')
                          END
                       IF @@Error > 0 
                          ROLLBACK TRAN
                       COMMIT TRAN

                       FETCH NEXT FROM TmpCursor INTO @No_Proposal, @No_Peserta, @Nama, @Nik, @Tgl_Lahir, @Kelamin, @Status_Kawin, @Grd, @Pln, @Tgl_Mulai, @Kelas, @ColField, @Premi, @Urut, @Sebagai, @Umur, @Up
                 END
           CLOSE TmpCursor
           DEALLOCATE TmpCursor
           EXEC ('UPDATE TmpInsurance.dbo.RepPeserta_OJK SET [Total]='+@SQLTotal)

   
  SELECT TOP 100 PERCENT
    TmpInsurance.dbo.RepPeserta_OJK.*,
    dbo.Cabang.Nama AS Cabang,
    dbo.Kartu_Peserta.Card_No
  FROM
    TmpInsurance.dbo.RepPeserta_OJK
    INNER JOIN dbo.Peserta ON TmpInsurance.dbo.RepPeserta_OJK.No_Peserta = dbo.Peserta.No_Peserta
                              AND TmpInsurance.dbo.RepPeserta_OJK.No_Proposal = dbo.Peserta.No_Proposal
    INNER JOIN dbo.Sebagai ON dbo.Peserta.Sebagai = dbo.Sebagai.IDI
    LEFT OUTER JOIN dbo.Kartu_Peserta ON TmpInsurance.dbo.RepPeserta_OJK.No_Proposal = dbo.Kartu_Peserta.No_Proposal
                                         AND dbo.Peserta.No_Peserta = dbo.Kartu_Peserta.No_Peserta
    LEFT OUTER JOIN dbo.Cabang ON dbo.Peserta.No_Prs = dbo.Cabang.No_Prs
                                  AND dbo.Peserta.Cabang = dbo.Cabang.Kode
  ORDER BY
    TmpInsurance.dbo.RepPeserta_OJK.No_Peserta

END