<?php
return [
	"DROP PROCEDURE if exists `omfs_calc_report`;",
	"CREATE  PROCEDURE `omfs_calc_report`(IN p_email VARCHAR(255), IN p_service_name VARCHAR(255), IN p_date_of_service DATE, IN p_place_of_service_type VARCHAR(255), IN p_provider_type VARCHAR(255), IN p_conversion_factor_name VARCHAR(255), IN p_locality_name VARCHAR(255),IN p_locality_state VARCHAR(255), IN p_hcpcs VARCHAR(255), IN p_modifier VARCHAR(255), IN p_units INT, IN p_payment_total DECIMAL(10,2))
BEGIN
	DECLARE v_check INT DEFAULT 0;
	DECLARE v_anes_cpt VARCHAR(10) DEFAULT NULL;
	DECLARE v_conv_factor DECIMAL(10,4) DEFAULT 1;
	DECLARE v_work_rvu DECIMAL(10,4) DEFAULT 1;
	DECLARE v_mp_rvu DECIMAL(10,4) DEFAULT 1;
	DECLARE v_facility_pe_rvu DECIMAL(10,4) DEFAULT 1;
	DECLARE v_non_fac_pe_rvu DECIMAL(10,4) DEFAULT 1;
	DECLARE v_statewide_work_gaf DECIMAL(10,4) DEFAULT 0;
	DECLARE v_statewide_pe_gaf DECIMAL(10,4) DEFAULT 0;
	DECLARE v_statewide_mp_gaf DECIMAL(10,4) DEFAULT 0;
	DECLARE v_wgpci DECIMAL(10,4) DEFAULT 1;
	DECLARE v_pegpci DECIMAL(10,4) DEFAULT 1;
	DECLARE v_mpgpci DECIMAL(10,4) DEFAULT 1;
	DECLARE v_localitynumber TEXT DEFAULT NULL;
	DECLARE v_omfs DECIMAL(10,2) DEFAULT 0;
	DECLARE v_percentexpected DECIMAL(10,0) DEFAULT 0;
	DECLARE v_balancedue DECIMAL(10,4) DEFAULT 0;
	DECLARE v_total_work DECIMAL(10,4) DEFAULT 0;
	DECLARE v_total_pe DECIMAL(10,4) DEFAULT 0;
	DECLARE v_total_mp DECIMAL(10,4) DEFAULT 0;
	DECLARE v_sum DECIMAL(10,4) DEFAULT 0;
	DECLARE where_In_Code VARCHAR(100) DEFAULT '';
	DECLARE v_cbe BOOLEAN DEFAULT FALSE;
	DECLARE v_cfv BOOLEAN DEFAULT FALSE;
	DECLARE v_serial INT DEFAULT NULL;
	DECLARE v_dos DATE;
	DECLARE v_doe DATE;
	DECLARE v_description TEXT DEFAULT NULL;
	DECLARE v_status_code VARCHAR(100) DEFAULT NULL;
	DECLARE v_pctc_ind VARCHAR(100) DEFAULT NULL;
	DECLARE v_glob_days VARCHAR(100) DEFAULT NULL;
	DECLARE v_intra_op VARCHAR(100) DEFAULT NULL;
	DECLARE v_post_op VARCHAR(100) DEFAULT NULL;
	DECLARE v_mult_proc INT DEFAULT NULL;
	DECLARE v_bilat_surg INT DEFAULT NULL;
	DECLARE v_asst_surg INT DEFAULT NULL;
	DECLARE v_co_surg INT DEFAULT NULL;
	DECLARE v_team_surg INT DEFAULT NULL;
	DECLARE v_endo_base VARCHAR(100) DEFAULT NULL;
	DECLARE v_physician_supervision_of_diagnostic_procedures VARCHAR(100) DEFAULT NULL;
	DECLARE v_diagnostic_imaging_family_indicator INT DEFAULT NULL;
	DECLARE v_pre_op VARCHAR(100) DEFAULT NULL;

-- 	RESULT DECLARE VARIBALES
	DECLARE r_email VARCHAR(255);
	DECLARE r_service_name VARCHAR(255);
	DECLARE r_date_of_service DATE;
	DECLARE r_place_of_service_type VARCHAR(255);
	DECLARE r_provider_type VARCHAR(255);
	DECLARE r_conversion_factor_name VARCHAR(255);
	DECLARE r_conv_factor DECIMAL(10,4);
	DECLARE r_localityname VARCHAR(255);
	DECLARE r_localitystate VARCHAR(255);
	DECLARE r_hcpcs VARCHAR(255);
	DECLARE r_modifier VARCHAR(255);
	DECLARE r_units INT;
	DECLARE r_payment_total DECIMAL(10,2);
	DECLARE r_omfs DECIMAL(10,2);
	DECLARE r_serial INT;
	DECLARE r_localitynumber TEXT;
	DECLARE r_dos DATE;
	DECLARE r_doe DATE;
	DECLARE r_balancedue DECIMAL(10,2);
	DECLARE r_percentexpected VARCHAR(255);
	DECLARE r_description TEXT;
	DECLARE r_status_code VARCHAR(255);
	DECLARE r_work_rvu DECIMAL(10,4);
	DECLARE r_non_fac_pe_rvu DECIMAL(10,4);
	DECLARE r_facility_pe_rvu DECIMAL(10,4);
	DECLARE r_mp_rvu DECIMAL(10,4);
	DECLARE r_pctc_ind VARCHAR(255);
	DECLARE r_glob_days VARCHAR(255);
	DECLARE r_intra_op VARCHAR(255);
	DECLARE r_post_op VARCHAR(255);
	DECLARE r_mult_proc INT;
	DECLARE r_bilat_surg INT;
	DECLARE r_asst_surg INT;
	DECLARE r_co_surg INT;
	DECLARE r_team_surg INT;
	DECLARE r_endo_base VARCHAR(255);
	DECLARE r_physician_supervision_of_diagnostic_procedures VARCHAR(255);
	DECLARE r_diagnostic_imaging_family_indicator INT;
	DECLARE r_pre_op VARCHAR(255);
	DECLARE r_anes_cpt VARCHAR(255);
	DECLARE r_statewide_work_gaf DECIMAL(10,4);
	DECLARE r_statewide_pe_gaf DECIMAL(10,4);
	DECLARE r_statewide_mp_gaf DECIMAL(10,4);
	DECLARE r_wgpci DECIMAL(10,4);
	DECLARE r_pegpci DECIMAL(10,4);
	DECLARE r_mpgpci DECIMAL(10,4);
	DECLARE r_total_work DECIMAL(10,4);
	DECLARE r_total_pe DECIMAL(10,4);
	DECLARE r_total_mp DECIMAL(10,4);
	DECLARE r_sum DECIMAL(10,2);
	DECLARE i INT DEFAULT 1;
  	DECLARE end_value INT DEFAULT 1;

	SET p_email = IFNULL(p_email, NULL);
	SET p_service_name = IFNULL(p_service_name, NULL);
	SET p_place_of_service_type = IFNULL(p_place_of_service_type, NULL);
	SET p_provider_type = IFNULL(p_provider_type, 'Physician');
	SET p_conversion_factor_name = IFNULL(p_conversion_factor_name, NULL);
	SET p_locality_name = IFNULL(p_locality_name, NULL);
	SET p_locality_state = IFNULL(p_locality_state, NULL);
	SET p_hcpcs = IFNULL(p_hcpcs, NULL);
	SET p_modifier = IFNULL(p_modifier, NULL);
	SET p_units = IFNULL(p_units, 1);
	SET p_payment_total = IFNULL(p_payment_total, 1);
 -- SET v_check = 0;
	SELECT COUNT(*) INTO v_check FROM users ou WHERE TRIM(ou.email) = TRIM(p_email) LIMIT 1;
	SET v_cbe = FALSE;
	SET v_cfv = FALSE;
-- CASE FOR EXPECTED OMFS BASED CPT CODES --
IF (EXISTS (SELECT 1 FROM california_based_entries cbe WHERE DATE(p_date_of_service) BETWEEN cbe.dos AND cbe.doe AND cbe.cpt_code = p_hcpcs LIMIT 1)) THEN
    SET v_cbe = true;
    SELECT expected_value INTO v_omfs FROM california_based_entries cbe WHERE DATE(p_date_of_service) BETWEEN cbe.dos AND cbe.doe AND cbe.cpt_code = p_hcpcs LIMIT 1;
ELSE
    SET v_cbe = false;
END IF;
-- END CBE CASE--

-- CASE FOR ANESTHESIA BASED CPT CODES --
	SELECT work_rvu, mp_rvu, facility_pe_rvu, non_fac_pe_rvu, anes_cpt INTO v_work_rvu, v_mp_rvu, v_facility_pe_rvu, v_non_fac_pe_rvu, v_anes_cpt FROM omfs_all oa
	WHERE TRIM(oa.hcpcs) = TRIM(p_hcpcs) AND CASE WHEN TRIM(oa.mod) = 0 then oa.mod = '' ELSE  oa.mod END AND DATE(p_date_of_service) BETWEEN oa.dos AND oa.doe  LIMIT 1;

	IF v_omfs IS NULL THEN
    IF v_cbe = FALSE THEN
        SELECT expected_value INTO v_omfs FROM california_based_entries cbe WHERE DATE(p_date_of_service) BETWEEN cbe.dos AND cbe.doe AND TRIM(cbe.cpt_code) = TRIM(p_hcpcs) LIMIT 1;
    END IF;
	END IF;

	IF TRIM(v_anes_cpt) = 'Anesthesia' THEN
    SELECT conv_factor INTO v_conv_factor FROM omfs_all oa WHERE TRIM(oa.hcpcs) = TRIM(p_hcpcs) AND CASE WHEN TRIM(oa.mod) = 0 then oa.mod = '' ELSE  oa.mod END AND DATE(p_date_of_service) BETWEEN oa.dos AND oa.doe LIMIT 1;

		ELSE IF p_conversion_factor_name IS NOT NULL THEN
			SELECT cf.conversion_factor_value INTO v_conv_factor FROM conversion_factors_2014_2022 cf WHERE TRIM(cf.conversion_factor_name) = TRIM(p_conversion_factor_name) AND
			DATE(p_date_of_service) BETWEEN cf.dos AND cf.doe LIMIT 1;

    SET v_cfv = TRUE;
		END IF;
	END IF;
-- END ANESTHESIA CASE--

	IF p_locality_name IS NOT NULL THEN
    SELECT wgpci, pegpci, mpgpci, localitynumber INTO v_wgpci, v_pegpci, v_mpgpci, v_localitynumber FROM localitylist_2019_2022 ll WHERE ll.localitynumber = TRIM(p_locality_name) AND TRIM(ll.state) = TRIM(p_locality_state) AND  p_date_of_service BETWEEN DATE(ll.dos) AND DATE(ll.doe)
    LIMIT 1;
	ELSE
    SELECT wgpci, pegpci, mpgpci, localitynumber INTO v_wgpci, v_pegpci, v_mpgpci, v_localitynumber FROM localitylist_2019_2022 ll WHERE DATE(p_date_of_service) BETWEEN ll.dos AND ll.doe
    LIMIT 1;
	END IF;

	SELECT work_rvu_gaf, pe_rvu_gaf, mp_rvu_gaf into v_statewide_work_gaf, v_statewide_pe_gaf, v_statewide_mp_gaf FROM gaf_data gd WHERE DATE(p_date_of_service) BETWEEN gd.dos AND gd.doe LIMIT 1;


	IF v_statewide_work_gaf IS NULL THEN
		SET v_statewide_work_gaf = 1;
	END IF;

	IF v_statewide_pe_gaf IS NULL THEN
		SET v_statewide_pe_gaf = 1;
	END IF;

	IF v_statewide_mp_gaf IS NULL THEN
		SET v_statewide_mp_gaf = 1;
	END IF;

	IF v_cfv IS FALSE THEN
-- if v_conv_factor is null then
		SELECT expected_value  INTO v_conv_factor FROM california_based_entries cbe WHERE DATE(p_date_of_service) BETWEEN cbe.dos AND cbe.doe AND cbe.cpt_code = p_hcpcs LIMIT 1;
	END IF;


	IF v_conv_factor IS NULL THEN
		SET v_conv_factor = 1;
	END IF;

	IF v_anes_cpt IS NULL THEN
		SET v_anes_cpt = '';
	END IF;


	-- --------------------------------------------------------------------------------
	-- ------- CHECK & APPLY CONDITIONS AS PER THE PROVIDED PARAMETERS VALUES ---------
	-- ----------------------- CALCULATE omfs AS PER THE FORMULA ----------------------
	-- --------------------------------------------------------------------------------

	IF v_check >= 1 THEN

		IF p_service_name = 'Physician Services' THEN

-- -- For Facility --

			IF p_place_of_service_type = 'Facility' THEN

				IF DATE(p_date_of_service) < '2019-01-01' THEN
					SET where_In_Code = CONCAT(' ', 'Formula No 1');

					IF v_cbe IS FALSE AND v_anes_cpt != 'Anesthesia' THEN
					SET v_omfs = ((v_work_rvu * v_statewide_work_gaf) + (v_facility_pe_rvu * v_statewide_pe_gaf) + (v_mp_rvu * v_statewide_mp_gaf)) * v_conv_factor;

					END IF;


					IF p_place_of_service_type = 'Facility' THEN
						SET v_total_pe = v_facility_pe_rvu * v_statewide_pe_gaf;
					END IF;

					IF p_place_of_service_type = 'Non-Facility' THEN
						SET v_total_pe = v_non_fac_pe_rvu * v_statewide_pe_gaf;
					END IF;
--
					SET v_total_mp = v_mp_rvu * v_statewide_mp_gaf;
					SET v_total_work = v_work_rvu * v_statewide_work_gaf;
					SET v_sum = v_total_work + v_total_pe + v_total_mp;

IF v_omfs > 0 THEN
SET v_balancedue = v_omfs - p_payment_total;
END IF;

					IF v_omfs > 0 THEN

	SET v_percentexpected = round((100-((v_omfs - p_payment_total) / v_omfs) * 100));
	-- SET v_percentexpected = CONCAT(ROUND(((v_omfs - p_payment_total) / v_omfs) * 100),'%');

					END IF;
				ELSE IF DATE(p_date_of_service) > '2018-12-31' THEN

-- Formula No 2

						SET where_In_Code = CONCAT(' ', 'Formula No 2');

						IF v_cbe IS FALSE AND v_anes_cpt != 'Anesthesia' THEN
							SET v_omfs = ((v_work_rvu * v_wgpci) + (v_facility_pe_rvu * v_pegpci) + (v_mp_rvu * v_mpgpci)) * v_conv_factor;
						END IF;

						IF p_place_of_service_type = 'Facility' THEN
							SET v_total_pe = v_facility_pe_rvu * v_pegpci;
						END IF;
						IF p_place_of_service_type = 'Non-Facility' THEN
							SET v_total_pe = v_non_fac_pe_rvu * v_pegpci;
						END IF;

						SET v_total_work = v_work_rvu * v_wgpci;
						SET v_total_mp = v_mp_rvu * v_mpgpci;
						SET v_sum = v_total_work + v_total_pe + v_total_mp;

IF v_omfs > 0 THEN
SET v_balancedue = v_omfs - p_payment_total;
END IF;

						IF v_omfs > 0 THEN
	SET v_percentexpected = round((100-((v_omfs - p_payment_total) / v_omfs) * 100));
	-- SET v_percentexpected = CONCAT(ROUND(((v_omfs - p_payment_total) / v_omfs) * 100),'%');
						END IF;

				END if;


					END if;
				END if;

-- For NON Facility --
				IF p_place_of_service_type = 'Non-Facility' THEN
					IF DATE(p_date_of_service) < '2019-01-01' THEN

-- Formula No 3

						SET where_In_Code = CONCAT(' ', 'Formula No 3');
						IF v_cbe IS FALSE AND v_anes_cpt != 'Anesthesia' THEN
							SET v_omfs =  ((v_work_rvu * v_statewide_work_gaf) + (v_non_fac_pe_rvu * v_statewide_pe_gaf) + (v_mp_rvu * v_statewide_mp_gaf)) * v_conv_factor;
						END IF;

						IF p_place_of_service_type = 'Facility' THEN
							SET v_total_pe = v_facility_pe_rvu * v_statewide_pe_gaf;
						END IF;
						IF p_place_of_service_type = 'Non-Facility' THEN
							SET v_total_pe = v_non_fac_pe_rvu * v_statewide_pe_gaf;
						END IF;

						SET v_total_mp = v_mp_rvu * v_statewide_mp_gaf;
						SET v_total_work = v_work_rvu * v_statewide_work_gaf;
						SET v_sum = v_total_work + v_total_pe + v_total_mp;

IF v_omfs > 0 THEN
SET v_balancedue = v_omfs - p_payment_total;
END IF;

						IF v_omfs > 0 THEN
	SET v_percentexpected = round((100-((v_omfs - p_payment_total) / v_omfs) * 100));
	-- SET v_percentexpected = CONCAT(ROUND(((v_omfs - p_payment_total) / v_omfs) * 100),'%');
						END IF;
--
--
					ELSE IF DATE(p_date_of_service) > '2018-12-31' THEN
-- Formula No 4
						SET where_In_Code = CONCAT(' ', 'Formula No 4');
--
						IF v_cbe IS FALSE AND v_anes_cpt != 'Anesthesia' then
							SET v_omfs = ((v_work_rvu * v_wgpci) + (v_non_fac_pe_rvu * v_pegpci) + (v_mp_rvu * v_mpgpci)) * v_conv_factor;
						END IF;

						IF p_place_of_service_type = 'Facility' THEN
							SET v_total_pe = v_facility_pe_rvu * v_pegpci;
						END IF;

							IF p_place_of_service_type = 'Non-Facility' THEN
								SET v_total_pe = v_non_fac_pe_rvu * v_pegpci;
							END IF;

							SET v_total_work = v_work_rvu * v_wgpci;
							SET v_total_mp = v_mp_rvu * v_mpgpci;
							SET v_sum = v_total_work + v_total_pe + v_total_mp;

IF v_omfs > 0 THEN
SET v_balancedue = v_omfs - p_payment_total;
END IF;

							IF v_omfs > 0 THEN
	SET v_percentexpected = round((100-((v_omfs - p_payment_total) / v_omfs) * 100));
	-- SET v_percentexpected = CONCAT(ROUND(((v_omfs - p_payment_total) / v_omfs) * 100),'%');
							END IF;

					END IF;

				END if; -- p_date_of_service::DATE < '2019-01-01'
			END if; -- p_place_of_service_type = 'Facility'
		END if; -- p_service_name = 'Physician Services'
	END if; -- v_check >= 1

	IF v_statewide_work_gaf = 1 THEN
		SET v_statewide_work_gaf = NULL;
	END IF;
	IF v_statewide_pe_gaf = 1 THEN
		SET v_statewide_pe_gaf = NULL;
	END IF;
	IF v_statewide_mp_gaf = 1 THEN
		SET v_statewide_mp_gaf = NULL;
	END IF;



WHILE i <= end_value DO


SELECT serial, dos, doe, description, status_code, work_rvu,
non_fac_pe_rvu,facility_pe_rvu,mp_rvu,pctc_ind,glob_days, intra_op, post_op, mult_proc, bilat_surg, asst_surg, co_surg, team_surg, endo_base, physician_supervision_of_diagnostic_procedures, diagnostic_imaging_family_indicator, pre_op, anes_cpt into v_serial, v_dos, v_doe, v_description, v_status_code, v_work_rvu, v_non_fac_pe_rvu, v_facility_pe_rvu, v_mp_rvu, v_pctc_ind, v_glob_days, v_intra_op, v_post_op, v_mult_proc, v_bilat_surg, v_asst_surg, v_co_surg, v_team_surg, v_endo_base, v_physician_supervision_of_diagnostic_procedures, v_diagnostic_imaging_family_indicator, v_pre_op, v_anes_cpt
FROM omfs_all oa
WHERE TRIM(oa.hcpcs) = TRIM(p_hcpcs) AND TRIM(oa.`mod`) = TRIM(p_modifier) AND p_date_of_service BETWEEN oa.dos AND oa.doe
LIMIT 1;


SET r_email 														= p_email;
SET r_service_name 											= p_service_name;
SET r_date_of_service 									= p_date_of_service;
SET r_place_of_service_type 						= p_place_of_service_type;
SET r_provider_type 										= p_provider_type;
SET r_conversion_factor_name 						= p_conversion_factor_name;
SET r_conv_factor 											= v_conv_factor;
SET r_localityname 											= p_locality_name;
SET r_localitystate 										= p_locality_state;
SET r_hcpcs															= p_hcpcs;
SET r_modifier													= p_modifier;
SET r_units 														= p_units;
SET r_payment_total 										= p_payment_total;
IF v_omfs IS NULL THEN
    SET v_omfs = 0;
END IF;
SET r_omfs 												= v_omfs;
SET r_serial 											= v_serial;
SET r_localitynumber 									= v_localitynumber;
SET r_dos 												= v_dos;
SET r_doe 												= v_doe;
IF v_balancedue < 0 THEN
SET v_balancedue = 0;
END IF;
SET r_balancedue 		= v_balancedue * r_units;
SET r_percentexpected = v_percentexpected;
-- if v_percentexpected > 0 then
-- 	SET r_percentexpected = v_percentexpected || ' %';
-- end if;
SET r_description 										= v_description;
SET r_status_code 										= v_status_code;
SET r_work_rvu 											= v_work_rvu;
SET r_non_fac_pe_rvu 									= v_non_fac_pe_rvu;
SET r_facility_pe_rvu 									= v_facility_pe_rvu;
SET r_mp_rvu 											= v_mp_rvu;
SET r_pctc_ind 											= v_pctc_ind;
SET r_glob_days 										= v_glob_days;
SET r_intra_op 											= v_intra_op;
SET r_post_op 											= v_post_op;
SET r_mult_proc 										= v_mult_proc;
SET r_bilat_surg 										= v_bilat_surg;
SET r_asst_surg 										= v_asst_surg;
SET r_co_surg 											= v_co_surg;
SET r_team_surg 										= v_team_surg;
SET r_endo_base 										= v_endo_base;
SET r_physician_supervision_of_diagnostic_procedures 	= v_physician_supervision_of_diagnostic_procedures;
SET r_diagnostic_imaging_family_indicator 				= v_diagnostic_imaging_family_indicator;
SET r_pre_op 											= v_pre_op;
SET r_anes_cpt 											= v_anes_cpt;
SET r_statewide_work_gaf								= v_statewide_work_gaf;
SET r_statewide_pe_gaf									= v_statewide_pe_gaf;
SET r_statewide_mp_gaf									= v_statewide_mp_gaf;
SET r_wgpci												= v_wgpci;
SET r_pegpci											= v_pegpci;
SET r_mpgpci											= v_mpgpci;
SET r_total_work										= v_total_work;
SET r_total_pe											= v_total_pe;
SET r_total_mp											= v_total_mp;
SET r_sum												= v_sum;

SELECT r_email, r_service_name, r_date_of_service, r_place_of_service_type, r_provider_type, r_conversion_factor_name, r_conv_factor, r_localityname,r_localitystate, r_hcpcs, r_modifier, r_units, r_payment_total, r_omfs, r_serial, r_localitynumber, r_dos, r_doe, r_balancedue, r_percentexpected, r_description, r_status_code, r_work_rvu, r_non_fac_pe_rvu, r_facility_pe_rvu, r_mp_rvu, r_pctc_ind, r_glob_days, r_intra_op, r_post_op, r_mult_proc, r_bilat_surg, r_asst_surg, r_co_surg, r_team_surg, r_endo_base, r_physician_supervision_of_diagnostic_procedures, r_diagnostic_imaging_family_indicator, r_pre_op, r_anes_cpt, r_statewide_work_gaf, r_statewide_pe_gaf, r_statewide_mp_gaf, r_wgpci, r_pegpci, r_mpgpci, r_total_work, r_total_pe, r_total_mp, r_sum;

SET i = i + 1;
END WHILE;


END"

];
