DROP	FUNCTION IF EXISTS	fn_calculate_rule;

CREATE FUNCTION	fn_calculate_rule(a_service_flag VARCHAR(10),
								a_service_rate	decimal(15, 2),
									a_ruleID 	int,
											a_map_lvl	varchar(20),
											a_map_id	int,
											adt_fromdt 	datetime, 
											adt_todt		datetime)
RETURNS DECIMAL(15, 2)
DETERMINISTIC
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE v_used_time INT;
	DECLARE v_loop_counter INT;
	DECLARE v_iteration	INT;
	DECLARE v_result_time	INT;
	DECLARE	v_time INT;
	DECLARE v_mod_time	INT;
	DECLARE	v_slb_intrvl	INT;
	DECLARE v_slb_time_celi_flr VARCHAR(2);
	DECLARE v_inc_dec VARCHAR(2);
	DECLARE v_dsc_typ VARCHAR(2);
	DECLARE	v_actual_rate DECIMAL(12, 2);
	DECLARE	v_value DECIMAL(12, 2);
	DECLARE	v_charges DECIMAL(12, 2) DEFAULT 0.00;


	DECLARE cur_rule CURSOR FOR 
			SELECT	IFNULL(slb_max_time/slb_intrvl,0) as iteration, 
							case 
								when 	slb_time_unit = 'h' then slb_intrvl*60 
								when 	slb_time_unit = 'd' then slb_intrvl*60*24 
								when 	slb_time_unit = 'm' then slb_intrvl
								else	0 
							end as slb_intrvl, 
							slb_time_celi_flr, inc_dec, dsc_typ, value, actual_rate
			FROM		rule_master rm, rule_logics rl
			WHERE		rm.id = rl.rule_id 
			AND			rm.id = a_ruleID
			AND			map_lvl = a_map_lvl
			AND			map_id = a_map_id
			Order By slb_time_seq;

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

	SET	v_used_time	= TIMESTAMPDIFF(MINUTE, adt_fromdt, adt_todt);

	read_loop: LOOP
		
			
		FETCH cur_rule INTO v_iteration, v_slb_intrvl, v_slb_time_celi_flr, v_inc_dec, v_dsc_typ,  v_value, v_actual_rate;
		IF done THEN
			LEAVE read_loop;
		END IF;

		CASE v_inc_dec 
			/*Calculate slab-wise rule*/
			WHEN 'NA' THEN

				/*When iteration = 0 and service used time > 0*/
				IF v_iteration = 0 AND v_used_time > 0 THEN
						SET	v_result_time =	FLOOR(v_used_time / v_slb_intrvl);
						SET	v_mod_time	=	MOD(v_used_time, v_slb_intrvl);

						If v_mod_time > 0 THEN
							SET	v_result_time	=	v_result_time + 1;
						END If;

						SET	v_used_time = 0;
						SET	v_charges = v_charges + (v_actual_rate * v_result_time);

				/*When iteration > 0 and service used time > (each slab interval * No. of iteration ) or total max time for a slab*/
				ELSEIF v_used_time > 0	AND	v_iteration > 0 AND (v_slb_intrvl * v_iteration) < v_used_time	THEN
						SET	v_charges = v_charges + (v_actual_rate * v_iteration);
						SET	v_used_time = v_used_time - (v_slb_intrvl * v_iteration);
				
				/*When iteration > 0 and service used time > each slab interval */
				ELSEIF v_used_time > 0	AND	v_iteration > 0 AND v_slb_intrvl < v_used_time	THEN
						SET	v_time	=	floor(v_used_time/v_slb_intrvl);
						SET	v_mod_time	=	MOD(v_used_time, v_slb_intrvl);

						If v_mod_time > 0 THEN
							SET	v_time	=	v_time + 1;
						END If;
						
						SET	v_charges = v_charges + (v_actual_rate * v_time);
						SET	v_used_time = v_used_time - (v_slb_intrvl * v_time);
				
				/*When iteration > 0 and used time > 0 and  each slab interval > used time*/
				ELSEIF v_used_time > 0	AND	v_iteration > 0 AND v_slb_intrvl > v_used_time	THEN
						SET	v_charges = v_charges + v_actual_rate;
						SET	v_used_time = 0;
				END IF;
			WHEN	'D'	THEN
				IF v_dsc_typ = 'P' THEN
					SET	v_charges = ROUND(a_service_rate * (100 - v_value) * 0.01, 2);
				ELSE
					SET	v_charges = a_service_rate - v_value;
				END IF;
			WHEN	'I' THEN
				IF v_dsc_typ = 'P' THEN
					SET	v_charges = ROUND(a_service_rate * (100 + v_value) * 0.01, 2);
				ELSE
					SET	v_charges = a_service_rate + v_value;
				END IF;
			END CASE;
		/*SELECT	v_charges, v_used_time;*/

	END LOOP read_loop;
	CLOSE cur_rule; 

	RETURN	(v_charges);
END;