SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
ALTER Procedure [dbo].[proc_SetNoteLunas] @NoRef varchar(11)
AS
  DECLARE @TglBayar dateTime
  DECLARE @No_Bukti varchar(20) 
  DECLARE @Produk varchar(7) 
  DECLARE @Jumlah float
  DECLARE @Total float
  DECLARE @Pembagi float
 
  IF EXISTS(SELECT * FROM Note_Lunas_Detail WHERE No_Ref=@NoRef)
    DELETE FROM Note_Lunas_Detail WHERE No_Ref=@NoRef 
  DECLARE Temp_Cursor CURSOR FOR
  SELECT     dbo.Note_Lunas_Master.Tgl_Bayar, dbo.Note_Lunas_Master.No_Bukti, dbo.Note_Terbit_Detail.Sub_Kelompok, 
                      dbo.Note_Terbit_Detail.Total * dbo.Note_Lunas_Master.Jumlah  , isnull(dbo.Note_Terbit_Master.Total,0)
  FROM         dbo.Note_Lunas_Master INNER JOIN
                      dbo.Note_Terbit_Detail ON dbo.Note_Lunas_Master.No_Ref = dbo.Note_Terbit_Detail.No_Ref INNER JOIN
                      dbo.Note_Terbit_Master ON dbo.Note_Lunas_Master.No_Ref = dbo.Note_Terbit_Master.No_Ref
  WHERE dbo.Note_Terbit_Master.No_Ref=@NoRef
  OPEN Temp_Cursor
  FETCH NEXT FROM Temp_Cursor
  INTO @TglBayar, @No_Bukti, @Produk, @Jumlah, @Pembagi
  WHILE @@FETCH_STATUS = 0
  BEGIN
     IF @Pembagi<>0 SET @Total = ROUND(@Jumlah/@Pembagi,2)
     ELSE SET @Total = 0
     INSERT  Note_Lunas_Detail (No_Ref, Tgl_Bayar, No_Bukti, Sub_Kelompok, Jumlah)
      VALUES (@NoRef, @TglBayar, @No_Bukti, @Produk, @Total)
     FETCH NEXT FROM Temp_Cursor
     INTO @TglBayar, @No_Bukti, @Produk, @Jumlah, @Pembagi
  END
  CLOSE Temp_Cursor
  DEALLOCATE Temp_Cursor
  SELECT @Jumlah = Sum(isnull(Jumlah,0)), @TglBayar=Max(Tgl_Bayar) FROM Note_Lunas_Master WHERE No_Ref=@NoRef
  UPDATE Note_Terbit_Master SET Jml_Lunas=isnull(@Jumlah,0), Tgl_Lunas=@TglBayar WHERE No_Ref=@NoRef
  
  
  -- start: ini untuk custport, by Pribadi Pamungkas. 2016-08-15
  DECLARE @No_Klaim VARCHAR(11)

  DECLARE list_cursor CURSOR FAST_FORWARD FOR
  SELECT DISTINCT
    (No_Klaim)
  FROM
    Klaim_Ref_Detail
  WHERE
    No_Ref = @NoRef 

  OPEN list_cursor
  FETCH NEXT FROM list_cursor INTO @No_Klaim

  WHILE @@FETCH_STATUS = 0 
  BEGIN

    IF EXISTS(SELECT * FROM custport_klaim WHERE core_no_klaim = @No_Klaim)
      BEGIN
          DECLARE
            @custport_create_by INT = NULL,
            @custport_no_peserta VARCHAR(12),
            @custport_no_polis VARCHAR(12),
            @custport_email VARCHAR(100),
            @custport_id_klaim INT
            
          SELECT
            @custport_create_by = create_by,
            @custport_no_peserta = no_peserta,
            @custport_no_polis = no_polis,
            @custport_id_klaim = id_klaim
          FROM custport_klaim 
          WHERE core_no_klaim = @No_Klaim
          
          IF @custport_create_by IS NULL
            BEGIN
              SELECT TOP 1
          	    @custport_email = username
              FROM
          	    dbo.custport_user_login_client
              WHERE
                no_peserta = @custport_no_peserta
                AND no_polis = @custport_no_polis
            END
          ELSE
            BEGIN
              SELECT TOP 1
          	    @custport_email = username
              FROM
          	    dbo.custport_user_login_client
              WHERE
                id_user = @custport_create_by
            END
            
          UPDATE
            custport_klaim
          SET
            status = 'Paid'
          WHERE
            core_no_klaim = @No_Klaim
          
          INSERT INTO [GrpInsurance].[dbo].[custport_payment_email]
                 ([id_klaim]
                 ,payment_date
                 ,[email_to]
                 ,[email_link]
                 ,[queue]
                 ,[onprocess]
                 ,[sent])
           VALUES
                 (@custport_id_klaim
                 ,@TglBayar
                 ,@custport_email
                 ,NULL
                 ,1
                 ,0
                 ,0)
      END

    FETCH NEXT FROM list_cursor INTO @No_Klaim
  END
  CLOSE list_cursor
  DEALLOCATE list_cursor
  -- end: ini untuk custport, by Pribadi Pamungkas. 2016-08-15
  
GO

