USE [ccrsdb]
GO
/****** Object:  StoredProcedure [dbo].[PMPM_VALIDATE_1]    Script Date: 10/11/2018 9:47:57 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER  PROCEDURE [dbo].[PMPM_VALIDATE_1]  
AS
BEGIN
	-- Stored Proc to validate PMPM invoice lines against CCRS claim data. First step.
	SET NOCOUNT ON;

	print 'starting PMPM_VALIDATE_1 note TEMPTEST lines forcing test configuration';

	declare @b_key bigint ; 
	declare @b_url varchar(250);

	declare @i_pmpm_invoice_key bigint, @i_referral_number varchar(100), @i_prior_authorization_number varchar(100), @i_icn varchar(100);
	declare @i_date_of_service date, @i_edipi varchar(100), @i_date_paid date ,@i_amount_paid decimal(18,2);
	declare @i_billed_amount decimal(18,2), @i_ccn_provider_name varchar(500), @i_ccn_provider_npi varchar(100), @i_clin_code varchar(100);
	declare @i_raw_clin_code varchar(100);

	declare @d_key bigint;
	-- declare @totRecs integer, @totValid integer, @totInvalid integer, @totUnique integer, @totRepeat integer;
	-- declare @header_text varchar(200), @footer_text varchar(200);
	declare @clinCode varchar(30);
	
	declare @runDate varchar(12) = format(CURRENT_TIMESTAMP, 'MM/dd/yyyy');

	declare @pmpm_year_month int;
	declare @ccn_num int;

	declare @int1 int;
	declare @int2 int;
	declare @big1 bigint;
	declare @str1 varchar(250);

	declare c01 cursor for 
	select pmpm_batch_log_key, url -- , status as transmission_status, header_history_key, footer_history_key
	from das.PMPM_BATCH_LOG
	where report_timestamp is null;

	-- update das.PMPM_BATCH_LOG set report_timestamp = '2000-01-01 00:00:00' where header_history_key is null;

	open c01;
	
	fetch next from c01 into @b_key, @b_url;
	while @@FETCH_STATUS = 0
	begin -- loop on c01

	-- SEPTEMBER 2018: It seems file name is not always following ICD. Assume 1 character Region code
		-- we get CCN number in URL of transmission file (also in a few other places)
			-- select charindex('-', upper('/ccrs_support/Binary/CCN-Region3-20180104-20180228.txt'), 26);
			-- select substring('/ccrs_support/Binary/CCN-Region3-20180104-20180228.txt', 26 + 6,  - 26 - 6);
		set @int1 = charindex('REGION', upper(@b_url));
		set @int2 = @int1 + 7; -- charindex('-', @b_url, @int1);
		set @ccn_num = cast(substring(@b_url, @int1 + 6, @int2 - @int1 - 6) as int);
	    print 'c01 loop on ' + ltrim(str(@b_key)) + ' CCN ' + str(@ccn_num);
	   
		-- NO LONGER UPDATING BATCH_REPORT TABLE. Report Builder can create intermediate info.

		-- during development, we'll NULL out invoice reporting details to make re-runs easier
		update das.PMPM_INVOICE set report_timestamp = NULL, report_status = NULL, report_duplicate_key = NULL
		  where batch_log_key = @b_key;
		
		-- print 'updating invoice lines for batch ' + str(@b_key);

		declare c02 cursor forward_only for 
	      -- select pmpm_invoice_key, icn, edipi, clin_code, status
		  select pmpm_invoice_key, referral_number, prior_authorization_number, icn ,date_of_service ,edipi,
			date_paid ,amount_paid ,billed_amount ,ccn_provider_name ,ccn_provider_npi, clin_code
	       from das.PMPM_INVOICE
	       where batch_log_key = @b_key -- and report_status is null TEMPTEST
		   for update of status, report_status, report_duplicate_key, clin_code;

		open c02;

        fetch next from c02 into @i_pmpm_invoice_key, @i_referral_number, @i_prior_authorization_number, @i_icn, @i_date_of_service, 
		  @i_edipi, @i_date_paid, @i_amount_paid, @i_billed_amount, @i_ccn_provider_name, @i_ccn_provider_npi, @i_clin_code;
	    while @@FETCH_STATUS = 0
	    begin -- loop on c02
			print '--TEMPTEST co2 loop start ' + str(@i_pmpm_invoice_key) + @i_referral_number
		    -- see if this is valid, then a duplicate

			-- NOTE I moved individual record validation checks into a cursor. Since we have to consider each record 
			-- individually when looking for duplicates, and most records should pass the simple validation checks and
			-- lookups, running everything through a cursor avoids creating multiple dynasets if we did checks via sets.

			-- Defect 750185: check format of CLIN. Correct minor CLIN format errors (should not happen in practice but we see in manual tests)
			-- remove blanks from CLIN (Defect 787457)
			set @i_raw_clin_code = @i_clin_code;
			set @i_clin_code = replace(upper(replace(@i_clin_code, ' ', '')), 'CLIN', '') -- some test data has CLIN as start of data

			-- we get CCN number in file name of transmission file (also in a few other places)

			-- Referral Number existence check 1. Note that we do some simple format checks earlier. Defect 750231
			set @int1 = NULL;
			select @int1 = count(1) from dim_va_Claim where Referral_Number = @i_referral_number;
			-- select top(1) claim_key from dim_va_Claim where Referral_Number = 'VA0000000077'
			-- WATCH OUT: I accidentally added some CRs to data and took 2 days to find them! print 'int 1 is ' + isnull(str(@int1), 'NULLLLLL');
			if (@int1 = 0) 
			begin
				update das.PMPM_INVOICE
				   set report_status = 'RptInvalid', status = 'CLMUF001', clin_code = @i_clin_code 
				   where current of c02;
				print '--TEMPTEST CLMUF001 num1 @i_referral_number ' + @i_referral_number; -- PMPM_VALIDATE_1
			end
			else begin

			-- ICN validation
			-- NOTE ICN/PCN processing is changing. Validation rules will have to follow. 

			set @int1 = NULL;
			select @int1 = patient_key from dbo.DIM_PATIENT where icn = @i_icn;
			if (@int1 is null)
			begin
				-- print '   CLMUF03I on ' + ltrim(str(@i_pmpm_invoice_key));
				update das.PMPM_INVOICE
				   set report_status = 'RptInvalid', status = 'CLMUF03I', clin_code = @i_clin_code
				   where current of c02;
				 print '--TEMPTEST CLMUF03I @i_icn ' + str(@i_pmpm_invoice_key) + ' ICN ' + @i_icn; -- PMPM_VALIDATE_1
			end
			
			else begin -- NO INDENT -- if ICN found
			-- Referral Number validation 2
			set @int1 = NULL;
			select top(1) @int1 = claim_key from dbo.DIM_VA_CLAIM where referral_number = @i_referral_number;
			if (@int1 is null)
			begin
				-- print '   CLMUF001 on ' + ltrim(str(@i_pmpm_invoice_key));
				update das.PMPM_INVOICE
				   set report_status = 'RptInvalid', status = 'CLMUF001', clin_code = @i_clin_code
				   where current of c02;
				print '--TEMPTEST CLMUF001 num2 @i_referral_number ' + str(@i_pmpm_invoice_key) + @i_referral_number; -- PMPM_VALIDATE_1
			end
			else begin -- NO INDENT -- if Referral Number found

	-- TODO: Figure out how PCNs work.  CLMUF03P . Note Build 3 Sprint 1 will change database structure. Work out commented validations in 
	-- new structure. USE NEW DATA ARRAY VIEW AS BASIS OF DATA VALIDATION

	-- NOTE: Sprint 2 story 748730 will impact DUPLICATE checking with new table which spans months.

	-- Defect 750219: Date sanity checking: do not allow date-of-service lookup more than 20 years back, or date of service into the future.
	-- NOTE CHANGE THIS TO 270 DAYS (plus fudge factor) at some point
	set @int1 = abs(datediff(year, current_timestamp,  @i_date_of_service ));
	if ((@int1 > 20) OR (@i_date_of_service > current_timestamp))
	    begin
			update das.PMPM_INVOICE
			   set report_status = 'RptInvalid', status = 'CLMUS04D', clin_code = @i_clin_code
			   where current of c02;
			print '--TEMPTEST CLMUS04D @i_date_of_service ' + str(@i_pmpm_invoice_key) + format(@i_date_of_service, 'YYYY-MO-DD'); -- PMPM_VALIDATE_1
		end
	  else begin -- NO INDENT -- if Date check OK
	    


--	Select set of DIM_VA_CLAIM rows which match referral_number and (ICN or PCN).
--	Check if Date Paid can be found in column date_paid in ONE of the tables
--  F_DENTAL_CLAIM_DETAILS or F_INSTITUTIONAL_MEDICAL_CLAIM_DETAILS or F_PHARMACY_CLAIM_DETAILS or F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS, 
-- joined on column claim_key. There should be matches in at most one table, though multiple rows in that table could be a match.
-- If no date match on a valid line, set status
-- 	Check for Provider in DIM_PROVIDER
--   	If not found, set status 
--	Select set of DIM_VA_CLAIM rows which match referral_number and (ICN or PCN) and Provider. If no approved claim is found, set status 
--	If all above checks are passed, set status to Verf1OK and see if unique

			-- Check CLIN code format. Reject if not a known format.
			
			if NOT((len(@i_clin_code) = 6) AND ((right(@i_clin_code, 5) = '011AA') OR (right(@i_clin_code, 5) = '011AB') OR 
				(right(@i_clin_code, 5) = '011AC')))
			BEGIN
				update das.PMPM_INVOICE
				   set report_status = 'RptInvalid', status = 'BadF011', clin_code = @i_clin_code
				   where current of c02;
				print '--TEMPTEST BadF011 2 @i_clin_code ' + str(@i_pmpm_invoice_key) + @i_clin_code; -- PMPM_VALIDATE_1
			END

			else begin


			-- FUTURE: CLIN_CODE will come in referral, and check CLIN against that. Not present in test data yet, but adding manually
			-- (skip for now until we know it's in the test)

			
--      IF a Valid Record Lines CLIN on the PMPM_INVOICE does not match another Valid Record Lines CLIN on PMPM_INVOICE 
--          THEN note the lines Valid Status as Y.
--  IF a Valid Record Lines ICN (or PCN) on the PMPM_INVOICE does not match another Valid Record Lines ICN (or PCN) on the PMPM_INVOICE 
--          THEN note the lines Valid Status as Y. 
--  IF a Valid Record Lines Date Paid value on the PMPM_INVOICE is not the same month as another Valid Record Lines Date Paid on the PMPM_INVOICE 
--          THEN note the lines Valid Status as Y. 
--  IF a Valid Record Lines CLIN on the PMPM_INVOICE matches another Valid Record Lines CLIN AND its ICN (or PCN) match 
--    AND their Date Paid value is the same month on the PMPM_INVOICE  
--          THEN note the lines Valid Status as Y-D AND include in the Reject Reason field: Duplicate of Valid Record (Invoice Key: XXX).

	
			-- see if this ICN or PCN already seen TODO: PCN
			set @d_key = NULL;
			-- print 'icn ' + @i_icn + ' batch_log_key ' + str(@b_key) + ' clin_code ' + @i_clin_code;
			-- select @d_key = pmpm_invoice_key from das.PMPM_INVOICE
			--     where batch_log_key = @b_key and icn = @i_icn and clin_code = @i_clin_code and report_status = 'RptUnique';

			-- Calculate pmpm_year_month -- this is based on PAYMENT date. This definition may change if PMPM input changes.
			set @pmpm_year_month = (datepart(YYYY, @i_date_paid) * 100) + datepart(mm, @i_date_paid);

			-- check for unique record.

			select @d_key = original_pmpm_invoice_key from das.PMPM_MONTHLY_STATUS
			     where icn = @i_icn and clin = @i_clin_code and ccn_paid = @ccn_num
				 and pmpm_year_month = @pmpm_year_month;

			if (@d_key is null)
			begin
				-- print '   RptUnique on ' + ltrim(str(@i_pmpm_invoice_key));
				update das.PMPM_INVOICE
					set report_status = 'RptUnique', status = 'Verf1OK', clin_code = @i_clin_code
					where current of c02;

				INSERT INTO [das].[PMPM_MONTHLY_STATUS] ([icn], [clin], [pmpm_year_month], [ccn_paid],
					[original_pmpm_invoice_key], [create_timestamp], [create_user], [update_timestamp], [update_user])
				VALUES
					( @i_icn, @i_clin_code, @pmpm_year_month, @ccn_num, 
					  @i_pmpm_invoice_key, CURRENT_TIMESTAMP, CURRENT_USER, CURRENT_TIMESTAMP, CURRENT_USER)

					print '--TEMPTEST Unique @i_clin_code ' + str(@i_pmpm_invoice_key) + @i_clin_code; -- PMPM_VALIDATE_1
			end
			else begin
				-- print '   RptDupICN on ' + ltrim(str(@i_pmpm_invoice_key));
				update das.PMPM_INVOICE
					set report_status = 'RptDupICN', report_duplicate_key = @d_key, status = 'Verf1OK', clin_code = @i_clin_code
					where current of c02;
				print '--TEMPTEST RptDupICN @i_clin_code ' + str(@i_pmpm_invoice_key) + @i_clin_code; -- PMPM_VALIDATE_1
			end

			end; -- if Date Check OK
			end; -- if Referral Number found
			end; -- if ICN found
			end; -- if CLIN format OK
			end; -- if Referral Number 1 passed

			print '--TEMPTEST co2 loop end ' + str(@i_pmpm_invoice_key)

			fetch next from c02 into @i_pmpm_invoice_key, @i_referral_number, @i_prior_authorization_number, @i_icn, @i_date_of_service, 
		  @i_edipi, @i_date_paid, @i_amount_paid, @i_billed_amount, @i_ccn_provider_name, @i_ccn_provider_npi, @i_clin_code;
		end; -- loop on c02

		close c02;
	    deallocate c02;

		fetch next from c01 into @b_key, @b_url;
	end -- loop on c01

	close c01;
	deallocate c01;

END;

