USE [GrpInsurance]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


  DECLARE @TglJTempo1 DATETIME
  DECLARE @TglJTempo2 DATETIME

  SET @TglJTempo1  = '2014-01-01'
  SET @TglJTempo2 = '2014-08-07'



  --Laporan Jatuh Tempo Premi
  SET NOCOUNT ON
  EXEC TmpInsurance.dbo.gproc_DelTable 'Rep_Prib_02'
  CREATE TABLE TmpInsurance.dbo.[Rep_Prib_02] (
	[No_Proposal] [varchar] (10) NOT NULL ,
--	[Nama_Perusahaan] [varchar] (100) NULL ,
--	[No_Polis] [varchar] (12) NULL ,
--	[Periode_Bayar] [varchar] (20) NULL ,
--	[Tgl_Mulai] [datetime] NULL ,
--	[Tgl_Selesai] [datetime] NULL ,
	[Tgl_JTempo] [datetime] NULL ,
	[JT_Ke] [int] NULL ,
	[JT_Tanggal] [datetime] NULL ,
        [Jml_Premi] [Float] NULL ,
	CONSTRAINT [PK_Rep_Prib_02] PRIMARY KEY  CLUSTERED 
	([No_Proposal])  ON [PRIMARY] ) ON [PRIMARY]

  DECLARE @No_Proposal varchar(9)
  --DECLARE @Nama_Perusahaan varchar(50)
  --DECLARE @No_Polis varchar(11) 
  --DECLARE @Periode_Bayar varchar(20) 
  DECLARE @Ketemu bit
  DECLARE @Tgl_Mulai Datetime
  DECLARE @Tgl_Selesai Datetime
  DECLARE @Tgl_JT Datetime
  DECLARE @JT_Count Int
  DECLARE @Jml_JT Int
  DECLARE @JT_Ke Int
  DECLARE @Fak Int
  DECLARE @Jml_Premi Float


  DECLARE TempCursor CURSOR FOR
  SELECT dbo.Polis_Asuransi.No_Proposal, dbo.Polis_Asuransi.Tgl_Mulai, dbo.Polis_Asuransi.Tgl_Selesai, ROUND(dbo.Periode_Bayar.Pengali, 0) 
  FROM   dbo.Polis_Asuransi INNER JOIN
         dbo.Periode_Bayar ON dbo.Polis_Asuransi.Periode_Bayar = dbo.Periode_Bayar.Kode INNER JOIN
         dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs
  WHERE  (dbo.Polis_Asuransi.Status <>'C') AND  (dbo.Polis_Asuransi.Status <>'G') AND
         (dbo.Polis_Asuransi.Tgl_Selesai>=@TglJTempo2)
         --AND dbo.Polis_Asuransi.No_Proposal='2013.0786' -- ini utk testing
         --AND dbo.Polis_Asuransi.No_Proposal='2014.0033'
   OPEN TempCursor

   FETCH NEXT FROM TempCursor INTO @No_Proposal, @Tgl_Mulai, @Tgl_Selesai, @Jml_JT
   WHILE @@FETCH_STATUS = 0
   BEGIN
        SET @Jml_Premi=0
        --IF EXISTS(SELECT No_Ref FROM Note_Terbit_Master WHERE Jenis<=19 AND Status<>'C' AND No_Proposal=@No_Proposal)
        --SELECT @Jml_Premi=Sum(Total) FROM Note_Terbit_Master WHERE Jenis<=19 AND Status<>'C' AND No_Proposal=@No_Proposal

        IF EXISTS(SELECT Peserta_Produk.No_Peserta  FROM Peserta_Produk INNER JOIN Peserta ON Peserta_Produk.No_Proposal=Peserta.No_Proposal AND Peserta_Produk.No_Peserta=Peserta.No_Peserta WHERE Peserta_Produk.Status<>'C' AND Peserta.Status='I' AND Peserta_Produk.No_Proposal=@No_Proposal)
        SELECT @Jml_Premi=Sum(Curr_Premi) FROM Peserta_Produk INNER JOIN Peserta ON Peserta_Produk.No_Proposal =Peserta.No_Proposal AND Peserta_Produk.No_Peserta =Peserta.No_Peserta WHERE Peserta_Produk.Status<>'C' AND Peserta.Status='I' AND Peserta_Produk.No_Proposal=@No_Proposal

        SET @Ketemu=0
        SET @Tgl_JT=@Tgl_Mulai 
        SET @JT_Count=0
        SET @JT_Ke = 0
        IF isnull(@Jml_JT,0)=0 
          SET @Fak = 1
        ELSE 
          SET @Fak = Round(12/@Jml_JT,0)
        WHILE (@JT_Count<=12) AND (@Ketemu=0)
        BEGIN
             SET @Tgl_JT = DATEADD(Month , @JT_Count, @Tgl_Mulai) 
             IF (@Tgl_JT >= @TglJTempo1) AND (@Tgl_JT <= @TglJTempo2) 
              BEGIN
                SET @Ketemu = 1
              END
             ELSE 
              BEGIN 
                SET @Ketemu = 0 
                SET @JT_Count = @JT_Count + @Fak 
                SET @JT_Ke = @JT_Ke +1
              END
        END
PRINT @Ketemu
PRINT @Tgl_JT
PRINT @Tgl_Mulai
PRINT @Tgl_Selesai
        IF @Ketemu=1 AND @Tgl_JT>=@Tgl_Mulai AND @Tgl_JT<=@Tgl_Selesai
          INSERT INTO TmpInsurance.dbo.Rep_Prib_02 (No_Proposal, Tgl_JTempo, JT_Ke, JT_Tanggal, Jml_Premi) 
          VALUES (@No_Proposal, @Tgl_JT, @JT_Ke+1, @Tgl_JT, @Jml_Premi)
        ELSE
          INSERT INTO TmpInsurance.dbo.Rep_Prib_02 (No_Proposal, Tgl_JTempo, JT_Ke, JT_Tanggal, Jml_Premi) 
          VALUES (@No_Proposal, NULL, NULL, NULL, @Jml_Premi)
          
        FETCH NEXT FROM TempCursor INTO @No_Proposal, @Tgl_Mulai, @Tgl_Selesai, @Jml_JT
END
CLOSE TempCursor
DEALLOCATE TempCursor


--SELECT No_Proposal, Nama_Perusahaan, No_Polis, Periode_Bayar, Tgl_Mulai, Tgl_Selesai, Tgl_JTempo, JT_Ke, Jml_Premi FROM TmpInsurance.dbo.Rep_Prib_02 
--ORDER BY Tgl_JTempo, Nama_Perusahaan
SELECT     TOP 100 PERCENT dbo.Perusahaan.GT_OG, TmpInsurance.dbo.Rep_Prib_02.No_Proposal, dbo.Perusahaan.Nama_Perusahaan, 
                      dbo.Polis_Asuransi.No_Polis, dbo.Polis_Asuransi.Tgl_Mulai, dbo.Polis_Asuransi.Tgl_Selesai, dbo.Periode_Bayar.Deskripsi AS Periode_Bayar, 
                      TmpInsurance.dbo.Rep_Prib_02.Tgl_JTempo, TmpInsurance.dbo.Rep_Prib_02.JT_Ke, dbo.Mata_Uang.Deskripsi AS Mata_Uang, 
                      TmpInsurance.dbo.Rep_Prib_02.Jml_Premi
FROM         TmpInsurance.dbo.Rep_Prib_02 INNER JOIN
                      dbo.Polis_Asuransi ON TmpInsurance.dbo.Rep_Prib_02.No_Proposal = dbo.Polis_Asuransi.No_Proposal 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 INNER JOIN
                      dbo.Mata_Uang ON dbo.Polis_Asuransi.Mata_Uang = dbo.Mata_Uang.Kode
ORDER BY dbo.Perusahaan.GT_OG, dbo.Perusahaan.Nama_Perusahaan, TmpInsurance.dbo.Rep_Prib_02.Tgl_JTempo
