USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_SetNoteLunas]    Script Date: 08/25/2016 09:22:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER 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. last modified 2016-08-16
  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) -- jika dari web portal
      BEGIN
        IF EXISTS(SELECT Jml_Dibayar FROM dbo.Klaim_Peserta WHERE No_Klaim = @No_Klaim AND CAST(Jml_Dibayar AS NUMERIC(20,2))<>0) -- jika ada nilai jumlah dibayar
          BEGIN
              DECLARE
                @custport_create_by INT = NULL,
                @custport_no_peserta VARCHAR(12),
                @custport_no_polis VARCHAR(12),
                @custport_email VARCHAR(100) = NULL,
                @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 EXISTS(SELECT * FROM dbo.custport_user_login_client WHERE no_polis=@custport_no_polis AND no_peserta=LEFT(@custport_no_peserta,11)+'0')
                BEGIN
                  SELECT TOP 1 @custport_email = username FROM dbo.custport_user_login_client WHERE no_polis=@custport_no_polis AND no_peserta=LEFT(@custport_no_peserta,11)+'0'
                END

              --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 history klaim custport
              INSERT INTO custport_klaim_history
                     ([id_klaim]
                     ,[action]
                     ,[tanggal])
               VALUES
                     (@custport_id_klaim
                     ,'Paid'
                     ,GETDATE())
              
              IF COALESCE(@custport_email,'')<>'' -- jika ada alamat email
                BEGIN
                  -- tambahkan pengecekan, jika sudah ada email, jangan kirim email lagi
                  IF NOT EXISTS(SELECT * FROM dbo.custport_payment_email WHERE id_klaim=@custport_id_klaim) -- jika tidak ada perintah kirim email
                    BEGIN
                      INSERT INTO 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 -- END: jika tidak ada perintah kirim email
                END -- END: jika ada alamat email

              INSERT INTO custport_close_claim_aai
                         ([id_klaim]
                         ,[payment_date]
                         ,[xml_content]
                         ,[queue]
                         ,[onprocess]
                         ,[sent]
                         ,[sent_date]
                         ,[response]
                         ,[response_date])
                   VALUES
                         (<id_klaim, int,>
                         ,<payment_date, datetime,>
                         ,<xml_content, varchar,>
                         ,<queue, bit,>
                         ,<onprocess, bit,>
                         ,<sent, bit,>
                         ,<sent_date, datetime,>
                         ,<response, varchar,>
                         ,<response_date, datetime,>)
                
          END -- END: jika ada nilai jumlah dibayar
      END -- END: jika dari web portal

    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
  