-- kesepakatan dgn bu rosma, bahwa yg dimaksud new bisnins adalah jenis note 1 saja
-- jadi tidak melihat apakah itu polis baru (newbisnis) atau bukan, yg penting jenis notenya adalah 1

--SELECT
--	*
--FROM
--	dbo.Note_Terbit_Master AS ntm
--WHERE
--  status NOT IN ('C', 'G')
--  AND dbo.func_fixed_date(ntm.tgl_buat) >= '2015-01-01'
--  AND dbo.func_fixed_date(ntm.tgl_buat) <= '2015-12-31'
--  AND ntm.Jenis = 1 -- Tagihan Premi New Bisnis
--  AND ntm.Jml_Terbit<>ntm.Total
  
  
--SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))


--DECLARE @dtDate DATETIME
--SET @dtDate = '2016-02-09'
--SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
---------------------------------------------------------

DECLARE 
  @pi_tahun SMALLINT,
  @pi_mata_uang VARCHAR(3)

SET @pi_tahun = 2015
--SET @pi_mata_uang = UPPER('USD')

DECLARE @hasil TABLE ( 
                  bulan TINYINT,
                  valuta VARCHAR(3),
                  jan_premi NUMERIC(18,2),
                  jan_terbayar NUMERIC(18,2),
                  feb_premi NUMERIC(18,2),
                  feb_terbayar NUMERIC(18,2),
                  mar_premi NUMERIC(18,2),
                  mar_terbayar NUMERIC(18,2),
                  apr_premi NUMERIC(18,2),
                  apr_terbayar NUMERIC(18,2),
                  mei_premi NUMERIC(18,2),
                  mei_terbayar NUMERIC(18,2),
                  jun_premi NUMERIC(18,2),
                  jun_terbayar NUMERIC(18,2),
                  jul_premi NUMERIC(18,2),
                  jul_terbayar NUMERIC(18,2),
                  ags_premi NUMERIC(18,2),
                  ags_terbayar NUMERIC(18,2),
                  sep_premi NUMERIC(18,2),
                  sep_terbayar NUMERIC(18,2),
                  okt_premi NUMERIC(18,2),
                  okt_terbayar NUMERIC(18,2),
                  nov_premi NUMERIC(18,2),
                  nov_terbayar NUMERIC(18,2),
                  des_premi NUMERIC(18,2),
                  des_terbayar NUMERIC(18,2),
                  no_ref VARCHAR(12))

DECLARE @p_bulan TINYINT = 1
--DECLARE @kode_mata_uang VARCHAR(2)
--SELECT @kode_mata_uang=Kode FROM dbo.Mata_Uang WHERE Deskripsi = @pi_mata_uang

WHILE @p_bulan <= 12
BEGIN
  --DECLARE 
  --  @no_ref VARCHAR(11),
  --  @tgl_buat DATETIME,
  --  @jml_terbit NUMERIC(18,2),
  --  @total_terbit NUMERIC(18,2),
  --  @jml_lunas_terbit NUMERIC(18,2),
  --  @jml_lunas NUMERIC(18,2),
  --  @saldo NUMERIC(18,2),
  --  @tgl_lunas DATETIME,
  --  @saldo_terbit NUMERIC(18,2)
    
  DECLARE 
    @id_note_terbit VARCHAR(12),
    @tgl_terbit DATETIME,
    @jml_terbit NUMERIC(18,2),
    @tgl_lunas DATETIME,
    @total_lunas NUMERIC(18,2)
  
  DECLARE list_cursor CURSOR FAST_FORWARD FOR  
  --DECLARE @pi_tahun SMALLINT = 2015, @p_bulan TINYINT = 1
  SELECT
	  nt.id_note_terbit,
	  nt.tgl_terbit,
	  CAST(COALESCE(nt.jml_terbit,0) AS NUMERIC(18,2)) AS jml_terbit,
	  lunas.tgl_lunas,
	  COALESCE(lunas.total_lunas,0)
  FROM
	  dbo.note_terbit AS nt LEFT JOIN
	  (
	  SELECT
		  id_note_terbit,
		  MAX(tgl_lunas) AS tgl_lunas,
		  SUM(CAST(COALESCE(total_lunas,0) AS NUMERIC(18,2))) AS total_lunas
	  FROM
		  dbo.note_lunas
	  WHERE
	    status NOT IN ('C', 'G')
    GROUP BY
      id_note_terbit
	  ) AS lunas ON nt.id_note_terbit = lunas.id_note_terbit
	                --AND YEAR(lunas.tgl_lunas) = @pi_tahun
	                --AND MONTH(lunas.tgl_lunas) = @p_bulan
	                AND lunas.tgl_lunas <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,CONVERT(DATETIME,(CAST(@pi_tahun AS VARCHAR(4))+'-'+CAST(@p_bulan AS VARCHAR(2))+'-'+CAST('01' AS VARCHAR(2))),20))+1,0))
  WHERE
    YEAR(nt.tgl_terbit) = @pi_tahun
    AND MONTH(nt.tgl_terbit) = @p_bulan
    AND nt.status NOT IN ('C', 'G')
  

  OPEN list_cursor  
  FETCH NEXT FROM list_cursor INTO @id_note_terbit, @tgl_terbit, @jml_terbit, @tgl_lunas, @total_lunas

  WHILE @@FETCH_STATUS = 0  
  BEGIN
    --IF @p_bulan = 1 
    --SELECT @jml_terbit, @total_lunas, @jml_terbit + @total_lunas, @jml_terbit - @total_lunas AS 'test'  
  
    --SELECT @id_note_terbit, @tgl_terbit, @jml_terbit, @jml_terbit, @tgl_lunas, @total_lunas
    
    -- penerimaan current month
    INSERT INTO @hasil
            (bulan,
             valuta,
             jan_premi,
             jan_terbayar,
             feb_premi,
             feb_terbayar,
             mar_premi,
             mar_terbayar,
             apr_premi,
             apr_terbayar,
             mei_premi,
             mei_terbayar,
             jun_premi,
             jun_terbayar,
             jul_premi,
             jul_terbayar,
             ags_premi,
             ags_terbayar,
             sep_premi,
             sep_terbayar,
             okt_premi,
             okt_terbayar,
             nov_premi,
             nov_terbayar,
             des_premi,
             des_terbayar,
             no_ref)
    SELECT
             @p_bulan, -- bulan - tinyint
             NULL, -- valuta - varchar(3)
             CASE WHEN @p_bulan = 1 THEN @jml_terbit ELSE NULL END, -- jan_premi - numeric
             CASE WHEN @p_bulan = 1 THEN @total_lunas ELSE NULL END, -- jan_terbayar - numeric
             CASE WHEN @p_bulan = 2 THEN @jml_terbit ELSE NULL END, -- feb_premi - numeric
             CASE WHEN @p_bulan = 2 THEN @total_lunas ELSE NULL END, -- feb_terbayar - numeric
             CASE WHEN @p_bulan = 3 THEN @jml_terbit ELSE NULL END, -- mar_premi - numeric
             CASE WHEN @p_bulan = 3 THEN @total_lunas ELSE NULL END, -- mar_terbayar - numeric
             CASE WHEN @p_bulan = 4 THEN @jml_terbit ELSE NULL END, -- apr_premi - numeric
             CASE WHEN @p_bulan = 4 THEN @total_lunas ELSE NULL END, -- apr_terbayar - numeric
             CASE WHEN @p_bulan = 5 THEN @jml_terbit ELSE NULL END, -- mei_premi - numeric
             CASE WHEN @p_bulan = 5 THEN @total_lunas ELSE NULL END, -- mei_terbayar - numeric
             CASE WHEN @p_bulan = 6 THEN @jml_terbit ELSE NULL END, -- jun_premi - numeric
             CASE WHEN @p_bulan = 6 THEN @total_lunas ELSE NULL END, -- jun_terbayar - numeric
             CASE WHEN @p_bulan = 7 THEN @jml_terbit ELSE NULL END, -- jul_premi - numeric
             CASE WHEN @p_bulan = 7 THEN @total_lunas ELSE NULL END, -- jul_terbayar - numeric
             CASE WHEN @p_bulan = 8 THEN @jml_terbit ELSE NULL END, -- ags_premi - numeric
             CASE WHEN @p_bulan = 8 THEN @total_lunas ELSE NULL END, -- ags_terbayar - numeric
             CASE WHEN @p_bulan = 9 THEN @jml_terbit ELSE NULL END, -- sep_premi - numeric
             CASE WHEN @p_bulan = 9 THEN @total_lunas ELSE NULL END, -- sep_terbayar - numeric
             CASE WHEN @p_bulan = 10 THEN @jml_terbit ELSE NULL END, -- okt_premi - numeric
             CASE WHEN @p_bulan = 10 THEN @total_lunas ELSE NULL END, -- okt_terbayar - numeric
             CASE WHEN @p_bulan = 11 THEN @jml_terbit ELSE NULL END, -- nov_premi - numeric
             CASE WHEN @p_bulan = 11 THEN @total_lunas ELSE NULL END, -- nov_terbayar - numeric
             CASE WHEN @p_bulan = 12 THEN @jml_terbit ELSE NULL END, -- des_premi - numeric
             CASE WHEN @p_bulan = 12 THEN @total_lunas ELSE NULL END, -- des_terbayar - numeric
             @id_note_terbit
    
    -------------------------------------------------------------------------
    --DECLARE @p_bulan_kolom TINYINT = 0;
    --WHILE @p_bulan_kolom <= 10
    --  BEGIN
      
    --    DECLARE @total_penerimaan_bulan_sebelumnya NUMERIC(18,2) = @jml_terbit
      
    --    -- penerimaan bulan berikutnya
    --    DECLARE
    --      @tgl_lunas_sebelum DATETIME,
    --      @total_lunas_sebelum NUMERIC(18,2)
    --    SELECT
    --      --No_Ref,
    --      @tgl_lunas_sebelum = MAX(Tgl_Bayar),
    --      @total_lunas_sebelum = CAST(SUM(Jumlah) AS NUMERIC(18,2))
    --    FROM
    --      dbo.Note_Lunas_Master
    --    WHERE
    --      No_Ref = @no_ref
    --      AND YEAR(Tgl_Bayar) = @pi_tahun
    --      AND MONTH(Tgl_Bayar) = @p_bulan + @p_bulan_kolom
    --    GROUP BY
    --      no_ref
          
    --    -- total penerimaan bulan sebelumnya
    --    INSERT INTO @hasil
    --            (bulan,
    --             valuta,
    --             jan_premi,
    --             jan_terbayar,
    --             feb_premi,
    --             feb_terbayar,
    --             mar_premi,
    --             mar_terbayar,
    --             apr_premi,
    --             apr_terbayar,
    --             mei_premi,
    --             mei_terbayar,
    --             jun_premi,
    --             jun_terbayar,
    --             jul_premi,
    --             jul_terbayar,
    --             ags_premi,
    --             ags_terbayar,
    --             sep_premi,
    --             sep_terbayar,
    --             okt_premi,
    --             okt_terbayar,
    --             nov_premi,
    --             nov_terbayar,
    --             des_premi,
    --             des_terbayar,
    --             no_ref
    --             )
    --    SELECT
    --             @p_bulan, -- bulan - tinyint
    --             @pi_mata_uang, -- valuta - varchar(3),
    --             NULL,
    --             NULL,
    --             CASE WHEN @p_bulan = 1 THEN @jml_terbit-@total_lunas ELSE NULL END, -- jan_premi - numeric
    --             CASE WHEN @p_bulan = 1 THEN @jml_lunas_sebelum ELSE NULL END, -- jan_terbayar - numeric
    --             CASE WHEN @p_bulan = 2 THEN @jml_terbit-@total_lunas ELSE NULL END, -- feb_premi - numeric
    --             CASE WHEN @p_bulan = 2 THEN @jml_lunas_sebelum ELSE NULL END, -- feb_terbayar - numeric
    --             CASE WHEN @p_bulan = 3 THEN @jml_terbit-@total_lunas ELSE NULL END, -- mar_premi - numeric
    --             CASE WHEN @p_bulan = 3 THEN @jml_lunas_sebelum ELSE NULL END, -- mar_terbayar - numeric
    --             CASE WHEN @p_bulan = 4 THEN @jml_terbit-@total_lunas ELSE NULL END, -- apr_premi - numeric
    --             CASE WHEN @p_bulan = 4 THEN @jml_lunas_sebelum ELSE NULL END, -- apr_terbayar - numeric
    --             CASE WHEN @p_bulan = 5 THEN @jml_terbit-@total_lunas ELSE NULL END, -- mei_premi - numeric
    --             CASE WHEN @p_bulan = 5 THEN @jml_lunas_sebelum ELSE NULL END, -- mei_terbayar - numeric
    --             CASE WHEN @p_bulan = 6 THEN @jml_terbit-@total_lunas ELSE NULL END, -- jun_premi - numeric
    --             CASE WHEN @p_bulan = 6 THEN @jml_lunas_sebelum ELSE NULL END, -- jun_terbayar - numeric
    --             CASE WHEN @p_bulan = 7 THEN @jml_terbit-@total_lunas ELSE NULL END, -- jul_premi - numeric
    --             CASE WHEN @p_bulan = 7 THEN @jml_lunas_sebelum ELSE NULL END, -- jul_terbayar - numeric
    --             CASE WHEN @p_bulan = 8 THEN @jml_terbit-@total_lunas ELSE NULL END, -- ags_premi - numeric
    --             CASE WHEN @p_bulan = 8 THEN @jml_lunas_sebelum ELSE NULL END, -- ags_terbayar - numeric
    --             CASE WHEN @p_bulan = 9 THEN @jml_terbit-@total_lunas ELSE NULL END, -- sep_premi - numeric
    --             CASE WHEN @p_bulan = 9 THEN @jml_lunas_sebelum ELSE NULL END, -- sep_terbayar - numeric
    --             CASE WHEN @p_bulan = 10 THEN @jml_terbit-@total_lunas ELSE NULL END, -- okt_premi - numeric
    --             CASE WHEN @p_bulan = 10 THEN @jml_lunas_sebelum ELSE NULL END, -- okt_terbayar - numeric
    --             CASE WHEN @p_bulan = 11 THEN @jml_terbit-@total_lunas ELSE NULL END, -- nov_premi - numeric
    --             CASE WHEN @p_bulan = 11 THEN @jml_lunas_sebelum ELSE NULL END, -- nov_terbayar - numeric
    --             --CASE WHEN @p_bulan = 13 THEN @jml_terbit-@total_lunas ELSE NULL END, -- des_premi - numeric
    --             --CASE WHEN @p_bulan = 13 THEN @jml_lunas_sebelum ELSE NULL END, -- des_terbayar - numeric
    --             @no_ref      
                 
    --    SET @total_penerimaan_bulan_sebelumnya = @jml_terbit-@total_lunas
        
    --    SET @p_bulan_kolom = @p_bulan_kolom + 1;
    --  END
    
    ------------------------------------------------------
    
    -- penerimaan bulan berikutnya  [1]
    DECLARE
      @tgl_lunas_sebelum DATETIME,
      @jml_lunas_sebelum NUMERIC(18,2),
      @total_penerimaan NUMERIC(18,2)
    SELECT
    	@tgl_lunas_sebelum = MAX(tgl_lunas),
    	@jml_lunas_sebelum = CAST(SUM(COALESCE(total_lunas,0)) AS NUMERIC(18,2))
    FROM
    	dbo.note_lunas
    WHERE
	    status NOT IN ('C', 'G')
	    AND id_note_terbit = @id_note_terbit
	    AND YEAR(tgl_lunas) = @pi_tahun
	    AND MONTH(tgl_lunas) = @p_bulan
	  GROUP BY
      id_note_lunas
    
    PRINT @jml_terbit
    PRINT @total_lunas
    SET @total_penerimaan = @jml_terbit - @total_lunas
    
    --IF @p_bulan = 1 
    --SELECT @jml_terbit, @total_lunas, @jml_terbit + @total_lunas, @jml_terbit - @total_lunas AS 'test'

    -- total penerimaan bulan sebelumnya
    INSERT INTO @hasil
            (bulan,
             valuta,
             jan_premi,
             jan_terbayar,
             feb_premi,
             feb_terbayar,
             mar_premi,
             mar_terbayar,
             apr_premi,
             apr_terbayar,
             mei_premi,
             mei_terbayar,
             jun_premi,
             jun_terbayar,
             jul_premi,
             jul_terbayar,
             ags_premi,
             ags_terbayar,
             sep_premi,
             sep_terbayar,
             okt_premi,
             okt_terbayar,
             nov_premi,
             nov_terbayar,
             des_premi,
             des_terbayar,
             no_ref
             )
    SELECT
             @p_bulan, -- bulan - tinyint
             NULL, -- valuta - varchar(3),
             NULL,
             NULL,
             CASE WHEN @p_bulan = 1 THEN @total_penerimaan ELSE NULL END, -- jan_premi - numeric
             CASE WHEN @p_bulan = 1 THEN @jml_lunas_sebelum ELSE NULL END, -- jan_terbayar - numeric
             CASE WHEN @p_bulan = 2 THEN @total_penerimaan ELSE NULL END, -- feb_premi - numeric
             CASE WHEN @p_bulan = 2 THEN @jml_lunas_sebelum ELSE NULL END, -- feb_terbayar - numeric
             CASE WHEN @p_bulan = 3 THEN @total_penerimaan ELSE NULL END, -- mar_premi - numeric
             CASE WHEN @p_bulan = 3 THEN @jml_lunas_sebelum ELSE NULL END, -- mar_terbayar - numeric
             CASE WHEN @p_bulan = 4 THEN @total_penerimaan ELSE NULL END, -- apr_premi - numeric
             CASE WHEN @p_bulan = 4 THEN @jml_lunas_sebelum ELSE NULL END, -- apr_terbayar - numeric
             CASE WHEN @p_bulan = 5 THEN @total_penerimaan ELSE NULL END, -- mei_premi - numeric
             CASE WHEN @p_bulan = 5 THEN @jml_lunas_sebelum ELSE NULL END, -- mei_terbayar - numeric
             CASE WHEN @p_bulan = 6 THEN @total_penerimaan ELSE NULL END, -- jun_premi - numeric
             CASE WHEN @p_bulan = 6 THEN @jml_lunas_sebelum ELSE NULL END, -- jun_terbayar - numeric
             CASE WHEN @p_bulan = 7 THEN @total_penerimaan ELSE NULL END, -- jul_premi - numeric
             CASE WHEN @p_bulan = 7 THEN @jml_lunas_sebelum ELSE NULL END, -- jul_terbayar - numeric
             CASE WHEN @p_bulan = 8 THEN @total_penerimaan ELSE NULL END, -- ags_premi - numeric
             CASE WHEN @p_bulan = 8 THEN @jml_lunas_sebelum ELSE NULL END, -- ags_terbayar - numeric
             CASE WHEN @p_bulan = 9 THEN @total_penerimaan ELSE NULL END, -- sep_premi - numeric
             CASE WHEN @p_bulan = 9 THEN @jml_lunas_sebelum ELSE NULL END, -- sep_terbayar - numeric
             CASE WHEN @p_bulan = 10 THEN @total_penerimaan ELSE NULL END, -- okt_premi - numeric
             CASE WHEN @p_bulan = 10 THEN @jml_lunas_sebelum ELSE NULL END, -- okt_terbayar - numeric
             CASE WHEN @p_bulan = 11 THEN @total_penerimaan ELSE NULL END, -- nov_premi - numeric
             CASE WHEN @p_bulan = 11 THEN @jml_lunas_sebelum ELSE NULL END, -- nov_terbayar - numeric
             --CASE WHEN @p_bulan = 13 THEN @jml_terbit-@total_lunas ELSE NULL END, -- des_premi - numeric
             --CASE WHEN @p_bulan = 13 THEN @jml_lunas_sebelum ELSE NULL END, -- des_terbayar - numeric
             @id_note_terbit
    
    FETCH NEXT FROM list_cursor INTO @id_note_terbit, @tgl_terbit, @jml_terbit, @tgl_lunas, @total_lunas
  END  

  CLOSE list_cursor  
  DEALLOCATE list_cursor 

   
  SET @p_bulan = @p_bulan + 1;

END -- end WHILE @p_bulan <= 12

SELECT * FROM @hasil
SELECT
  bulan,
  valuta,
  SUM(jan_premi) AS jan_premi,
  SUM(jan_terbayar) AS jan_terbayar ,
  SUM(feb_premi) AS feb_premi,
  SUM(feb_terbayar) AS feb_terbayar,
  SUM(mar_premi) AS mar_premi,
  SUM(mar_terbayar) AS mar_terbayar,
  SUM(apr_premi) AS apr_premi,
  SUM(apr_terbayar) AS apr_terbayar ,
  SUM(mei_premi) AS mei_premi,
  SUM(mei_terbayar) AS mei_terbayar,
  SUM(jun_premi) AS jun_premi,
  SUM(jun_terbayar) AS jun_terbayar,
  SUM(jul_premi) AS jul_premi,
  SUM(jul_terbayar) AS jul_terbayar,
  SUM(ags_premi) AS ags_premi,
  SUM(ags_terbayar) AS ags_terbayar,
  SUM(sep_premi) AS sep_premi,
  SUM(sep_terbayar) AS sep_terbayar,
  SUM(okt_premi) AS okt_premi,
  SUM(okt_terbayar) AS okt_terbayar,
  SUM(nov_premi) AS nov_premi,
  SUM(nov_terbayar) AS nov_terbayar,
  SUM(des_premi) AS des_premi,
  SUM(des_terbayar) AS des_terbayar
FROM
  @hasil
GROUP BY
  bulan,
  valuta