USE [GrpInsuranceUAT]
GO
/****** Object:  StoredProcedure [dbo].[proc_custport_klaim_from_aai]    Script Date: 02/13/2017 11:07:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Stored procedure untuk memasukan data klaim dari AAI ke aplikasi GroupInsurance (core)
-- by Pribadi Pamungkas. 2016-08-11
-- last modified by Pribadi Pamungkas. 2017-02-11

ALTER PROCEDURE [dbo].[proc_custport_klaim_from_aai]
	--@pi_no_klaim_custport VARCHAR(11), 
	@pi_claim_id VARCHAR(50),
	@po_status TINYINT OUTPUT,
	@po_message VARCHAR(200) OUTPUT
AS
BEGIN
	SET NOCOUNT ON; -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	
	DECLARE @sekarang DATETIME = dbo.func_FixedDate(GETDATE())

  -- 1. cek apakah datanya sudah lengkap (ada header dan detail)
  --IF EXISTS(SELECT * FROM custport_from_aai_header AS head INNER JOIN custport_from_aai_detail AS det ON head.claim_id = det.claim_id WHERE head.claim_register = @pi_no_klaim_custport)
  IF EXISTS(SELECT * FROM custport_from_aai_header AS head INNER JOIN custport_from_aai_detail AS det ON head.claim_id = det.claim_id WHERE head.claims_process_status IN ('13','14') AND head.claim_id = @pi_claim_id)
    BEGIN
      -- cek apakah sudah ada klaim tsb di core
      -- jika sudah ada dan statusnya masih merah, maka update, jika statusnya sudah ijo maka create baru
      DECLARE
        --@core_no_klaim VARCHAR(11),
        @aai_id_from_aai_header INT,
        @aai_no_peserta VARCHAR(12),
        @aai_claim_id VARCHAR(15),
        @aai_policy_number VARCHAR(11),
        @aai_provider_code VARCHAR(6),
        @aai_admission_date DATETIME,
        @aai_claims_process_status VARCHAR(2),
        @aai_diagnosis_code VARCHAR(6),
        @aai_plain_id VARCHAR(9),
        @aai_amt_incured FLOAT,
        @aai_amt_approved FLOAT,
        @aai_amt_not_approved FLOAT,
        @aai_claim_register VARCHAR(11),
        @aai_remarks VARCHAR(400),

        @id_produk VARCHAR(10),
        @keluarga BIT,
        @sub_kelompok VARCHAR(6),
        @no_klaim VARCHAR(11),
        @no_peserta VARCHAR(12),
        @no_santunan VARCHAR(12),
        @no_proposal VARCHAR(9),
        @jenis_pembayaran VARCHAR(1)
        
      SELECT TOP 1 
        --@core_no_klaim = core_no_klaim,
        @aai_id_from_aai_header = id_from_aai_header,
          --id_from_aai,
          --received_date,
        @aai_no_peserta = member_id,
        @jenis_pembayaran = CASE claim_type
                              WHEN 'E' THEN 'P'
                              WHEN 'M' THEN 'R'
                              WHEN 'R' THEN 'R'
                            END,
        @aai_claim_id = claim_id,
        @aai_policy_number = policy_number,
          --member_name,
          --coverage_type,
        --@aai_provider_code = provider_code,
        @aai_provider_code = CASE claim_type
                              WHEN 'E' THEN provider_code
                              WHEN 'M' THEN 'NPK'
                              WHEN 'R' THEN 'NPK'
                            END,
          --other_provider,
        @aai_admission_date = CONVERT(DATETIME, admission_date, 120), -- 105: utk format 14-06-2015, 120: utk format 2016-03-31
          --discharge_date,
          --duration_days,
        @aai_claims_process_status = claims_process_status,
          --app_date,
          --app_by,
        @aai_diagnosis_code = diagnosis_code,
          --diagnosis_desc,
        @aai_plain_id = plan_id,
        @aai_amt_incured = CAST(amt_incured AS FLOAT),
        @aai_amt_approved = CAST(amt_approved AS FLOAT),
        @aai_amt_not_approved = CAST(amt_not_approved AS FLOAT),
        @aai_claim_register = dbo.TRIM(claim_register),
          --currency_origin,
          --amt_of_currency_origin,
          --exchange_rate_currency_origin_to_idr,
          --exchange_rate_idr_to_usd,
          --claim_register_reference,
        @aai_remarks = remarks
      FROM 
        custport_from_aai_header 
      WHERE 
        --claim_register = @pi_no_klaim_custport 
        claim_id = @pi_claim_id
        AND claims_process_status IN ('13','14')
      ORDER BY 
        received_date DESC

------------
DECLARE
  @core_no_klaim VARCHAR(11) = NULL,
  @core_create_date DATETIME,
  @core_change_date DATETIME,
  @core_create_user VARCHAR(4),
  @core_change_user VARCHAR(4)
SELECT
	@core_no_klaim = ckl.no_klaim,
	@core_create_date = kp.Create_Date,
	@core_change_date = COALESCE(kp.Change_Date,kp.Create_Date),
	@core_create_user = kp.Create_By,
	@core_change_user = COALESCE(kp.Change_By,kp.Create_By)
FROM
	dbo.custport_klaim AS ck INNER JOIN
	dbo.custport_klaim_link AS ckl ON ck.id_klaim = ckl.id_klaim INNER JOIN
	dbo.Klaim_Peserta AS kp ON ckl.no_klaim = kp.No_Klaim
WHERE
  ck.no_klaim = @aai_claim_register
  AND ckl.claim_id = @aai_claim_id
------------
      
      -- get no proposal
      SELECT
      	@no_proposal = No_Proposal
      FROM
      	dbo.Polis_Asuransi
      WHERE
        No_Polis = @aai_policy_number
        AND Tgl_Mulai <= @aai_admission_date
        AND COALESCE(Tgl_Selesai,GETDATE()) >= @aai_admission_date
        --AND Status = 'I'  -- ?
        AND Status NOT IN ('G', 'C')  -- ?
      
      -- get produk
      SELECT
        @id_produk = Id_Produk,
        @keluarga = Keluarga,
        @sub_kelompok = Sub_Kelompok
      FROM
      	dbo.Produk_Polis
      WHERE
        Id_Produk = LEFT(@aai_plain_id, 4) + '.' + SUBSTRING(@aai_plain_id, 5, 5)

      SET @no_peserta = @aai_no_peserta
      
      IF COALESCE(@keluarga, 0) = 1 
         SET @no_santunan = LEFT(@aai_no_peserta, 11) + '0'
      ELSE 
         SET @no_santunan = @aai_no_peserta
      
      --IF ((COALESCE(@core_no_klaim,'')='') -- jika di custport_from_aai_header belum ada no_klaim core/group insurance, maka create
      --    AND @aai_claims_process_status IN ('13','14'))  -- [nebeng IF disini] dan jika status dari AAI 13 (approve) atau 14 (reject)
      IF (@aai_claims_process_status IN ('13','14'))
        BEGIN
          
          IF (COALESCE(@core_no_klaim,'')='') -- jika belum ada no klaim
            BEGIN
              -- generate no klaim
              -- insert
            END
          ELSE
            BEGIN
            	IF ((@core_create_date=@core_change_date) AND (@core_create_user=@core_change_user))
            	  BEGIN
            	  -- update menggunakan key no_klaim @core_no_klaim, caranya bukan update tapi:
            	  -- DELETE FROM dbo.Klaim_Peserta WHERE No_Klaim = @core_no_klaim
            	  -- DELETE FROM dbo.Klaim_Detail WHERE No_Klaim = @core_no_klaim
            	  -- insert
            	  END
            END
            
          
          EXEC gproc_GetNo 1, @sekarang, 'No_Klaim', @no_klaim OUTPUT -- generate no klaim (group insurance)
          IF COALESCE(@no_klaim,'')<>'' -- jika proses generate no klaim (group insurance) berhasil
            BEGIN
               -- get tgl kuitansi
               DECLARE @custport_tgl_kuitansi DATETIME = NULL
               SELECT @custport_tgl_kuitansi = CAST(MIN(ckd.tgl_kuitansi) AS DATETIME) FROM dbo.custport_klaim AS ck INNER JOIN dbo.custport_klaim_detail AS ckd ON ck.id_klaim = ckd.id_klaim WHERE ck.no_klaim = @aai_claim_register
               
               DECLARE @jenis_klaim SMALLINT
               IF @jenis_pembayaran='R' -- Jika Reimburst
                  SET @jenis_klaim = 21
               ELSE IF @jenis_pembayaran='P'  -- Jika Provider
                  SET @jenis_klaim = 20
                  
               INSERT INTO Klaim_Peserta
                      (No_Klaim,
                       No_Proposal,
                       No_Peserta,
                       No_Santunan,
                       Id_Produk,
                       Sub_Kelompok,
                       Kode_Diagnosa,
                       Kode_Provider,
                       Tgl_Klaim,
                       Remarks,
                       Jenis_Pembayaran,
                       Jml_Diajukan,
                       Jml_Dibayar,
                       Jml_Ditolak,
                       Create_By,
                       Create_Date,
                       Change_By,
                       Change_Date,
                       Status,
                       Claim_Ref,
                       Tgl_Kwitansi,
                       Jenis,
                       AdMedika_Ya)
               VALUES
                      (@no_klaim,
                       @no_proposal,
                       @no_peserta,
                       @no_santunan,
                       @id_produk,
                       @sub_kelompok,
                       @aai_diagnosis_code,
                       @aai_provider_code,  --'NPK'
                       @aai_admission_date,
                       @aai_remarks,
                       @jenis_pembayaran,
                       @aai_amt_incured,
                       @aai_amt_approved,
                       @aai_amt_not_approved,
                       'WEB',
                       @sekarang,
                       'WEB',
                       @sekarang,
                       'G',
                       @aai_claim_id,  -- @Claim_Ref
                       @custport_tgl_kuitansi,  -- Tgl_Kwitansi ??
                       @jenis_klaim,  --21/20
                       0)
                
                UPDATE
                  custport_from_aai_header 
                SET
                  core_no_klaim = @no_klaim
                WHERE 
                  --id_from_aai_header = @aai_claim_id
                  id_from_aai_header = @aai_id_from_aai_header
                
                UPDATE
                  custport_klaim 
                SET
                  core_no_klaim = @no_klaim,
                  remarks = @aai_remarks,
                  id_from_aai_header = @aai_id_from_aai_header
                WHERE 
                  no_klaim = @aai_claim_register
                       
                DECLARE @id_from_aai_detail INT

                DECLARE list_cursor CURSOR FOR  
                SELECT id_from_aai_detail FROM custport_from_aai_detail WHERE id_from_aai = (SELECT MAX(id_from_aai) FROM custport_from_aai_detail WHERE claim_id = @aai_claim_id)

                OPEN list_cursor  
                FETCH NEXT FROM list_cursor INTO @id_from_aai_detail  

                WHILE @@FETCH_STATUS = 0  
                BEGIN 
                
                  INSERT INTO dbo.Klaim_Detail
                          (No_Klaim,
                           Kode_Santunan,
                           Tabel_Prosen,
                           Amount,
                           Tgl_Kwitansi,
                           Jml_Diajukan,
                           Jml_Klaim,
                           Jml_Deductible,
                           Jml_Benefit,
                           Jml_Dibayar,
                           Jml_Ditolak,
                           Kode_Tolakan,
                           Jml_Adm,
                           Keterangan,
                           Jml_Dispensasi,
                           Jml_Dibayar_Old,
                           Status,
                           Change_By,
                           Change_Date,
                           Create_By,
                           Create_Date)
                  SELECT
                  	  @no_klaim, -- No_Klaim - varchar(11)
                  	  benefit_code, -- Kode_Santunan - varchar(10)
                      0, -- Tabel_Prosen - float
                      0, -- Amount - float
                      @aai_admission_date, -- Tgl_Kwitansi - datetime ???
                      CAST(amt_incurred AS FLOAT), -- Jml_Diajukan - float
                      CAST(amt_incurred AS FLOAT), -- Jml_Diajukan - float
                      0, -- Jml_Deductible - float
                      CAST(benefit_limit AS FLOAT), -- Jml_Benefit - float ?????
                      CAST(amt_approved AS FLOAT), -- Jml_Dibayar - float
                      CAST(amt_not_approved AS FLOAT), -- Jml_Ditolak - float
                      NULL, -- Kode_Tolakan - varchar(5)
                      0, -- Jml_Adm - float
                      remarks, -- Keterangan - varchar(150)   -- perlu diclean, kalo ada kutip
                      0,  -- Jml_Dispensasi - float
                      0,  -- Jml_Dibayar_Old - float
                      NULL, -- Status - varchar(1)
                      'WEB', -- Change_By - varchar(4)
                      GETDATE(), -- Change_Date - datetime
                      'WEB', -- Create_By - varchar(4)
                      GETDATE()  -- Create_Date - datetime
                           
                  	  --received_date,
                  	  --claim_id,
                  	  --member_id,
                  	  --benefit_description,
                  	  --benefit_limit,
                  	  --amt_incurred,
                  	  --amt_approved,
                  	  --amt_not_approved,
                  	  --coverage_type,
                  	  --plan_id,
                  	  --remarks
                  FROM
                  	custport_from_aai_detail
                  WHERE
                    id_from_aai_detail = @id_from_aai_detail
                
                  FETCH NEXT FROM list_cursor INTO @id_from_aai_detail  
                END  

                CLOSE list_cursor  
                DEALLOCATE list_cursor 
                
            END
        END

      SET @po_status = 0
      SET @po_message = 'Success'
      
    END
  ELSE
    BEGIN
      SET @po_status = 1
      SET @po_message = 'Data header dan detail belum lengkap. Claim ID: '+@pi_claim_id
    END

END
