DROP	PROCEDURE IF EXISTS	sp_select_rule;

CREATE PROCEDURE	sp_select_rule(	a_service_flag	varchar(10),
									a_trgt_mods	VARCHAR(2),
									a_service_id 	int,
									a_service_rate decimal(15, 2),
									a_pateint_id int,
									adt_fromdt 	datetime, 
									adt_todt		datetime)

BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE v_ruleID INT;
	DECLARE v_CategoryID INT;
	DECLARE v_SubCategoryID INT;
	DECLARE v_count INT;
	DECLARE v_MapLvl VARCHAR(15);
	DECLARE v_MapID INT;
	DECLARE cur_rule CURSOR FOR 
		SELECT	rule_id
		FROM		patient_rule_map prm, rule_master rm
		WHERE		patient_id = a_pateint_id
		AND			prm.rule_id = rm.id
		AND			is_active = 'Y'
		AND			trgt_mods = a_trgt_mods
		AND			rule_type  = a_service_flag
		Order by 	priority_num;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
	OPEN cur_rule;  

	CASE a_service_flag
		WHEN 'serv' THEN
			SELECT		ssc.category_id, ssc.id
			INTO		v_CategoryID, v_SubCategoryID
			FROM		service_sub_categories ssc, service_category sc, service_items si
			WHERE		ssc.category_id = sc.id
			AND			ssc.id = si.service_sub_category
			AND			si.id = a_service_id;
		WHEN	'doc'	THEN
			SELECT	specialty_id
			INTO	v_CategoryID
			FROM	doctors
			WHERE	id = a_service_id;
		WHEN 'rm_n_bd' THEN
			SELECT	room_id
			INTO	v_CategoryID
			FROM	bed
			WHERE	id = a_service_id;
			
	END CASE;

	read_loop: LOOP
		FETCH cur_rule INTO v_ruleID;
		IF done THEN
			LEAVE read_loop;
		END IF;

		/*select v_CategoryID, v_ruleID, a_service_flag;*/
		CASE a_service_flag
			WHEN 'serv' THEN
				SET v_MapLvl = 'serv';
				SET	v_MapID	=	a_service_id;
				SET v_count = fn_is_rule_exists(v_ruleID, v_MapLvl, v_MapID);
				
				IF	v_count = 0 THEN
					SET v_MapLvl 	= 'cat';
					SET	v_MapID	=	v_CategoryID;
					SET v_count = fn_is_rule_exists(v_ruleID, v_MapLvl, v_MapID);
				END IF;

				IF	v_count = 0 THEN
					SET v_MapLvl = 'subCat';
					SET	v_MapID	=	v_SubCategoryID;
					SET v_count = fn_is_rule_exists(v_ruleID, v_MapLvl, v_MapID);
				END IF;

			WHEN	'doc'	THEN
				SET v_MapLvl = 'doct';
				SET	v_MapID	=	a_service_id;
				SET v_count = fn_is_rule_exists(v_ruleID, v_MapLvl, v_MapID);

				IF	v_count = 0 THEN
					SET v_MapLvl = 'doct-spclty';
					SET	v_MapID	=	v_CategoryID;
					SET v_count = fn_is_rule_exists(v_ruleID, v_MapLvl, v_MapID);
				END IF;

			WHEN	'rm_n_bd'	THEN
				SET v_MapLvl = 'bed';
				SET	v_MapID	=	a_service_id;
				SET v_count = fn_is_rule_exists(v_ruleID, v_MapLvl, v_MapID);
				/*
				Select v_ruleID, v_MapLvl, v_MapID, v_count;*/

				IF	v_count = 0 THEN
					SET v_MapLvl = 'rooms';
					SET	v_MapID	=	v_CategoryID;
					SET v_count = fn_is_rule_exists(v_ruleID, v_MapLvl, v_MapID);
					/*Select v_ruleID, v_MapLvl, v_MapID, v_count;*/
				END IF;
				/*Select v_ruleID, v_MapLvl, v_MapID, v_count, v_CategoryID;*/
			END CASE;

			IF v_count > 0 THEN
				LEAVE read_loop;
			END IF;
	END LOOP read_loop;

	CLOSE cur_rule; 

	If v_count > 0 THEN
		/*
		Select a_service_flag, a_service_rate, v_ruleID, v_MapLvl, v_MapID;*/
		SELECT	fn_calculate_rule(a_service_flag, a_service_rate, v_ruleID, v_MapLvl, v_MapID, adt_fromdt, adt_todt) as charge;
	ELSE
		SELECT -1 as charge;
	END IF;
END;

