USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_post_claim_from_admedika]    Script Date: 01/11/2017 18:24:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

--ALTER Procedure [dbo].[proc_post_claim_from_admedika]
DECLARE
@pi_id_user varchar(4)='SYS'
--AS
  SET NOCOUNT ON
  DECLARE @AdMedikaYa bit
  ,@Claim_Ref	varchar	(11)
  ,@No_Klaim_Curr varchar	(11)
  ,@No_Klaim	varchar	(11)
  ,@No_Proposal	varchar	(09)
  ,@No_Polis	varchar	(119)
  ,@No_Peserta	varchar	(12)
  ,@No_Santunan	varchar	(12)
  ,@Id_Produk	varchar	(10)
  ,@Sub_Kelompok	varchar	(06)
  ,@Kode_Diagnosa	varchar	(06)
  ,@Kode_AdProvider varchar	(06)
  ,@Kode_Provider	varchar(06)
  ,@Tgl_Klaim	datetime
  ,@Tgl_Aju	datetime
  ,@No_Bukti	varchar	(18)
  ,@Notes	varchar	(150)
  ,@Jenis_Pembayaran varchar (01)
  ,@Claim_Type 	varchar (01)
  ,@Jml_Diajukan	float	
  ,@Jml_Deductible	float	
  ,@Jml_Dibayar	float	
  ,@Jml_Ditolak	float	
  ,@Jml_Dispensasi	float	
  ,@Uang_Muka	float	
  ,@Diskon_Rs	float	
  ,@Status	varchar	(02)
  ,@Flag		smallint	
  ,@Jml_Dibayar_Old float	
  ,@Jenis	smallint	
  ,@Jumlah	float	
  ,@Tolakan	varchar	(200)
  ,@No_Pasien	varchar	(10)
  ,@Create_Date	datetime
  ,@Kode_Equity	varchar	(06)
  ,@Kode_ICD	varchar	(06)
  ,@Mode_Update int
  ,@Keluarga bit
  ,@CLAIM_ID varchar(8)
  ,@last_status varchar(1)
  ,@id_claim_from_admedika_header bigint
  ,@claim_proses_status varchar(1)
  ,@tgl_proses datetime
	
  SET @Create_Date= dbo.func_FixedDate(GetDate())
  SET @last_status='G'
  
  DECLARE claim_from_admedika_header_cursor CURSOR FOR
  --SELECT id_claim_from_admedika_header, CLAIM_ID, coalesce(claim_proses_status,'0') 
  --from claim_from_admedika_header 
  --WHERE 
  --dbo.func_fixed_date(create_date)>=dbo.func_fixed_date(@Create_Date)
  SELECT 
    id_claim_from_admedika_header, CLAIM_ID, coalesce(claim_proses_status,'0') 
  FROM 
    claim_from_admedika_header 
  WHERE 
  --  create_date >= '2017-01-01' 
  --  AND COALESCE(CLAIM_PROSES_STATUS,0)=0 
  --  AND POLICY_NUMBER IN (SELECT REPLACE(No_Polis,'.','') FROM dbo.Polis_Asuransi WHERE Open_Polis=1 AND No_Polis IS NOT NULL)
  --  AND CLAIM_ID NOT IN (SELECT Claim_Ref FROM dbo.Klaim_Peserta WHERE COALESCE(Claim_Ref,'')<>'') 
    CLAIM_ID = '57484420'
    AND id_claim_from_admedika_header = 3992474

  OPEN claim_from_admedika_header_cursor
  FETCH NEXT FROM claim_from_admedika_header_cursor INTO @id_claim_from_admedika_header, @CLAIM_ID, @claim_proses_status
  WHILE @@FETCH_STATUS = 0
  BEGIN
	    --PRINT @CLAIM_ID
  		SET @no_klaim=null;
		IF EXISTS(SELECT 1 from Klaim_Peserta where Claim_Ref=@CLAIM_ID and (dbo.func_fixed_date(Create_Date)=dbo.func_fixed_date(change_date) OR change_date is null))
		BEGIN
			SELECT @last_status=status, @No_Klaim=no_klaim from Klaim_Peserta where Claim_Ref=@CLAIM_ID
			IF @last_status='G'
			BEGIN
				DELETE from Klaim_detail WHERE No_Klaim in 
				(SELECT no_klaim FROM Klaim_Peserta WHERE Claim_Ref=@CLAIM_ID)
				
				DELETE from Klaim_Peserta WHERE Claim_Ref=@CLAIM_ID
			END
		END
		
		IF NOT EXISTS(select 1 from Klaim_Peserta where Claim_Ref=@CLAIM_ID)
		BEGIN
			IF @No_Klaim is null
			EXEC gproc_GetNo 1, @Create_Date, 'no_klaim', @No_Klaim OUTPUT
		
			IF not exists(select 1 from Klaim_Peserta where No_Klaim=@No_Klaim)
			BEGIN
			
			  PRINT 'insert klaim: '+@No_Klaim
			  
				INSERT INTO Klaim_Peserta (
    			No_Klaim, No_Proposal, No_Peserta, No_Santunan, Id_Produk, Sub_Kelompok, Kode_Diagnosa, Kode_Provider,
                Tgl_Klaim, Jenis_Pembayaran, Jml_Diajukan, Jml_Dibayar, Jml_Ditolak, 
                Create_By, Create_Date, Change_By, Change_Date, Status, Claim_Ref, Tgl_Kwitansi, Jenis, AdMedika_Ya)
                  
			SELECT   TOP 1  @No_Klaim, Polis_Asuransi.No_Proposal, 
			member_id	= CASE WHEN RIGHT(polis_asuransi.no_polis,1)=0 AND produk_polis.sub_kelompok='0304' THEN
      LEFT(
      CASE	WHEN CHARINDEX('-',cfah.MEMBER_ID)=0 
								THEN SUBSTRING(cfah.MEMBER_ID, 1, 10)+ '-' + RIGHT(cfah.MEMBER_ID, 1)
								ELSE cfah.MEMBER_ID
								END,11)+'0'
      ELSE
      CASE	WHEN CHARINDEX('-',cfah.MEMBER_ID)=0 
								THEN SUBSTRING(cfah.MEMBER_ID, 1, 10)+ '-' + RIGHT(cfah.MEMBER_ID, 1)
								ELSE cfah.MEMBER_ID
								END
END,      

            NO_SANTUNAN = 
            CASE WHEN isnull(Produk_Polis.keluarga,0)=1 THEN
LEFT(
            CASE WHEN CHARINDEX('-',cfah.MEMBER_ID)=0 
						       THEN SUBSTRING(cfah.MEMBER_ID, 1, 10)+ '-' + RIGHT(cfah.MEMBER_ID, 1)
						       ELSE cfah.MEMBER_ID
								END,11)+'0'
ELSE
            CASE WHEN CHARINDEX('-',cfah.MEMBER_ID)=0 
						       THEN SUBSTRING(cfah.MEMBER_ID, 1, 10)+ '-' + RIGHT(cfah.MEMBER_ID, 1)
						       ELSE cfah.MEMBER_ID
								END
END,
                
                
    --            IF (RIGHT(@No_Polis,1)='0') AND @Sub_Kelompok='0304' SET @No_Peserta=LEFT(@No_Peserta,11)+'0'
    --SET @No_Santunan =LEFT(@No_Peserta,11)+'0'
    --ELSE SET @No_Santunan = @No_Peserta


            id_plan		= SUBSTRING(cfah.PLAN_ID, 1, 4) + '.' + SUBSTRING(cfah.PLAN_ID, 5, 5), 
						produk_polis.Sub_Kelompok,
						cfah.DIAGNOSIS_CODE, 
						--CASE cfah.CLAIM_TYPE WHEN 'R' THEN '9999' ELSE provider.IDI END, 
						provider.IDI,
						--CASE cfah.CLAIM_TYPE WHEN 'R' THEN '9999' ELSE provider.IDI END, 
            ADMISSION_DATE = CASE WHEN CHARINDEX('-',cfah.ADMISSION_DATE)<>0 THEN
            SUBSTRING(cfah.ADMISSION_DATE, 7, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 4, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
            ELSE
            SUBSTRING(cfah.ADMISSION_DATE, 5, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 3, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
            END,
            
            CLAIM_TYPE =CASE cfah.CLAIM_TYPE WHEN 'R' THEN 'R' ELSE 'P' END, 
            cfah.AMT_INCURRED, cfah.AMT_APPROVED,
			cfah.AMT_NOT_APPROVED,
						
			@pi_id_user, GETDATE(), @pi_id_user, GETDATE(), 'G', 
			cfah.CLAIM_ID,
			CASE WHEN CHARINDEX('-',cfah.ADMISSION_DATE)<>0 THEN
            SUBSTRING(cfah.ADMISSION_DATE, 7, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 4, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
            ELSE
            SUBSTRING(cfah.ADMISSION_DATE, 5, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 3, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
            END,                       
                      
            CASE cfah.CLAIM_TYPE WHEN 'R' THEN 21 ELSE 20 END, 1
                       
			FROM   claim_from_admedika_header cfah INNER JOIN
                      Polis_Asuransi ON cfah.POLICY_NUMBER = REPLACE(Polis_Asuransi.No_Polis, '.', '') INNER JOIN
                      Produk_Polis ON SUBSTRING(cfah.PLAN_ID, 1, 4) 
                      + '.' + SUBSTRING(cfah.PLAN_ID, 5, 5) = Produk_Polis.Id_Produk left join 
                      (select Max(IDI) as IDI , Kode_AdProvider from Provider group by Kode_AdProvider) as 
                      Provider on Provider.Kode_AdProvider=cfah.PROVIDER_CODE

			WHERE cfah.CLAIM_ID=@CLAIM_ID 
			
			and id_claim_from_admedika_header= @id_claim_from_admedika_header
			and coalesce(claim_proses_status,'0')= @CLAIM_PROSES_STATUS
			AND
			CASE 
            WHEN CHARINDEX('-',cfah.ADMISSION_DATE)<>0 THEN SUBSTRING(cfah.ADMISSION_DATE, 7, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 4, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
            ELSE SUBSTRING(cfah.ADMISSION_DATE, 5, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 3, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
      END 
      >=
      CASE 
            --WHEN Polis_Asuransi.Open_polis=1 THEN CAST(DATEPART(year,getdate()) as varchar(4))+'-01-01' 
            WHEN Polis_Asuransi.Open_polis=1 THEN CAST(2016 as varchar(4))+'-01-01' -- by Prib
            ELSE Polis_Asuransi.tgl_mulai 
      END
            
		and
		CASE 
            WHEN CHARINDEX('-',cfah.ADMISSION_DATE)<>0 THEN
            SUBSTRING(cfah.ADMISSION_DATE, 7, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 4, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2) 
            ELSE
            SUBSTRING(cfah.ADMISSION_DATE, 5, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 3, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
            END<=
            CASE WHEN Polis_Asuransi.Open_polis=1 THEN CAST(DATEPART(year,getdate()) as varchar(4))+'-12-31' ELSE Polis_Asuransi.Tgl_Selesai END
    
		AND LEFT(member_id,1) in ('1','2','3','4','5','6','7','8','9','0')
		AND LEFT(cfah.DIAGNOSIS_CODE,6)<>'PRORAT'
		AND
		
		(LEFT(COVERAGE_ID,1) NOT in ('1','2','3','4','5','6','7','8','9','0') OR PROVIDER_CODE<>'NPK') 
		and (
    Polis_Asuransi.status ='I' OR polis_asuransi.open_polis=0)

		------------------------------------------------
		
		DECLARE detail_cursor CURSOR FOR
		select 
		cfad.CLAIM_ID, 
		SUBSTRING(cfah.PLAN_ID, 1, 4) + '.' + SUBSTRING(cfah.PLAN_ID, 5, 5) ,
		max(dbo.func_fixed_date(cfad.create_date)) as create_date 
		from claim_from_admedika_detail cfad INNER JOIN claim_from_admedika_header cfah
		ON
		cfad.CLAIM_ID=cfah.CLAIM_ID
		
		where 
		id_claim_from_admedika_header= @id_claim_from_admedika_header
		and cfad.CLAIM_ID=@CLAIM_ID
		and coalesce(cfah.claim_proses_status,'0')=@claim_proses_status
		and (
			coalesce(cfah.claim_proses_status,'0')='1' AND LEFT(BENEFIT_CODE,1) in ('1','2','3','4','5','6','7','8','9','0')
			OR
            coalesce(cfah.claim_proses_status,'0')='0' AND LEFT(BENEFIT_CODE,1) not in ('1','2','3','4','5','6','7','8','9','0')
			)
		group by coalesce(cfah.claim_proses_status,'0'), cfad.CLAIM_ID, 
		SUBSTRING(cfah.PLAN_ID, 1, 4) + '.' + SUBSTRING(cfah.PLAN_ID, 5, 5) 
	
		OPEN detail_cursor;
		FETCH NEXT FROM detail_cursor INTO @CLAIM_ID, @id_produk, @tgl_proses
		WHILE @@FETCH_STATUS = 0
		BEGIN
				INSERT INTO Klaim_Detail (No_Klaim, Kode_Santunan, Tgl_Kwitansi, Jml_Benefit, Jml_Diajukan, jml_adm, Jml_Dibayar, Jml_Ditolak, Kode_Tolakan)

				SELECT  @no_klaim, 
				
				CASE WHEN coalesce(isp.benefit_code,'')<>'' THEN  isp.benefit_code
				ELSE
				CASE WHEN coalesce(cfah.claim_proses_status,'0')='1' 
				THEN cfad.KODE_SANTUNAN
				ELSE cfad.BENEFIT_CODE 
				END
				END, 
            
				Max(
				CASE 
				WHEN coalesce(cfah.claim_proses_status,'0')='1' 
				THEN SUBSTRING(cfah.ADMISSION_DATE, 7, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 4, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
				ELSE SUBSTRING(cfah.ADMISSION_DATE, 5, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 3, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
				END), 
            
				Max(
				CASE 
				WHEN coalesce(cfad.benefit_limit,0)=0 OR coalesce(cfad.benefit_limit,0)=999999999 
				THEN dbo.func_get_benefit_limit(
				CASE	WHEN CHARINDEX('-',cfah.MEMBER_ID)=0  THEN SUBSTRING(cfah.MEMBER_ID, 1, 10)+ '-' + RIGHT(cfah.MEMBER_ID, 1)
						ELSE cfah.MEMBER_ID
						END, 
						SUBSTRING(cfah.PLAN_ID, 1, 4) + '.' + SUBSTRING(cfah.PLAN_ID, 5, 5), 
						CASE 
						WHEN coalesce(cfah.claim_proses_status,'0')='1' 
						THEN cfad.KODE_SANTUNAN
						ELSE cfad.BENEFIT_CODE 
						END)
				ELSE coalesce(cfad.benefit_limit,0)
				END)

				,SUM(CAST(cfad.AMT_INCURRED as float)), 
            
				sum(
				CASE cfah.CLAIM_TYPE WHEN 'R' THEN cfad.AMT_NOT_APPROVED ELSE 0 END), 
            
				Sum(CAST(cfad.AMT_APPROVED as float)), 
				sum(CAST(cfad.AMT_NOT_APPROVED as float)), 
				max(mapping_tolakan_admedika.[kode Cit])
            
				FROM claim_from_admedika_detail cfad INNER JOIN
				claim_from_admedika_header cfah ON cfad.CLAIM_ID = cfah.CLAIM_ID 
				LEFT JOIN mapping_tolakan_admedika ON mapping_tolakan_admedika.[REASON CODE]=cfad.[REASON_CODE_OR_DESCRIPTION]
				left join item_santunan_polis isp on isp.id_produk=@id_produk and isp.kode_santunan=
				CASE WHEN coalesce(cfah.claim_proses_status,'0')='1' 
				THEN cfad.KODE_SANTUNAN
				ELSE cfad.BENEFIT_CODE 
				END 
            

				WHERE 
				(
					coalesce(cfah.claim_proses_status,'0')='1' AND LEFT(cfad.BENEFIT_CODE,1) in ('1','2','3','4','5','6','7','8','9','0')
					OR
					coalesce(cfah.claim_proses_status,'0')='0' AND LEFT(cfad.BENEFIT_CODE,1) not in ('1','2','3','4','5','6','7','8','9','0')
				)
				and id_claim_from_admedika_header=@id_claim_from_admedika_header
				and cfah.CLAIM_ID=@CLAIM_ID 
				and dbo.func_FixedDate(cfad.create_date) =dbo.func_FixedDate(@tgl_proses)
				and coalesce(cfah.claim_proses_status,'0')=@claim_proses_status
				group by 
				CASE WHEN coalesce(isp.benefit_code,'')<>'' THEN  isp.benefit_code
				ELSE
				CASE WHEN coalesce(cfah.claim_proses_status,'0')='1' 
				THEN cfad.KODE_SANTUNAN
				ELSE cfad.BENEFIT_CODE 
				END
				END

				FETCH NEXT FROM detail_cursor INTO @CLAIM_ID, @id_produk, @tgl_proses
			END;
			CLOSE detail_cursor;
			DEALLOCATE detail_cursor;
   		
			EXEC proc_PostToKlaim @No_Klaim 
    
			UPDATE claim_from_admedika_header SET No_Klaim=@No_Klaim, Status='I' WHERE CLAIM_ID=@CLAIM_ID
		END
	END
    FETCH NEXT FROM claim_from_admedika_header_cursor INTO @id_claim_from_admedika_header, @CLAIM_ID, @claim_proses_status

  END
  CLOSE claim_from_admedika_header_cursor
  DEALLOCATE claim_from_admedika_header_cursor
