CREATE PROCEDURE `sp_bed_calc` ( `ad_ipd_admission_id` INT ) BEGIN
	DECLARE
		v_service_flag VARCHAR ( 15 ) DEFAULT 'rm_n_bd';
	DECLARE
		v_trgt_mods VARCHAR ( 1 ) DEFAULT 'I';
	DECLARE
		v_pateint_id INT;
	SELECT
		patient_id INTO v_pateint_id 
	FROM
		ipd_admission 
	WHERE
		id = ad_ipd_admission_id;
	SELECT
		ipd_admission_id,
		bed_id,
		bed_name,
		occupied_from,
		occupied_to,
		service_item_id,
		service_rate_id,
		standard_charge_per_day,
	CASE
			
			WHEN charge_day = '100' THEN
			standard_charge_per_day ELSE fn_calculate_rule (
				v_service_flag,
				standard_charge_per_day,
				SUBSTRING_INDEX( charge_day, ';', 1 ),
			SUBSTRING_INDEX( SUBSTRING_INDEX( charge_day, ';', - 2 ), ';', 1 ),
			SUBSTRING_INDEX( charge_day, ';', - 1 ),
			occupied_from,
			occupied_to 
		) 
	END AS charge_per_day,
	hours,
	days,
	before_checkin,
	after_checkout 
	FROM
		(
		SELECT
			ipd_admission_id,
			bed_id,
			bed_name,
			occupied_from,
			occupied_to,
			service_item_id,
			service_rate_id,
			rate standard_charge_per_day,
			fn_select_rule ( v_service_flag, v_trgt_mods, bed_id, rate, v_pateint_id, occupied_from, occupied_to ) AS charge_day,
			TIMESTAMPDIFF( DAY, occupied_from, occupied_to ) AS days,
			TIMESTAMPDIFF( HOUR, occupied_from, occupied_to ) AS hours,
		CASE
				
				WHEN (
					HOUR (
					CAST( fn_get_lookup_value ( 'APP_PARAM', 'IPD_CHECKIN' ) AS time )) - HOUR ( occupied_from )) > 0 THEN
					1 ELSE 0 
				END AS before_checkin,
			CASE
					
					WHEN (
						HOUR ( occupied_to ) - HOUR (
						CAST( fn_get_lookup_value ( 'APP_PARAM', 'IPD_CHECKOUT' ) AS time ))) > 0 THEN
						1 ELSE 0 
					END AS after_checkout 
				FROM
					(
					SELECT
						ipd_admission_id,
						bed_id,
						b.bed_name,
						occupied_from,
						IFNULL( occupied_to, CURRENT_TIMESTAMP ) AS occupied_to,
						sr.rate AS rate,
						sr.id AS service_rate_id,
						sr.service_item_id 
					FROM
						ipd_bed_details bd,
						bed b,
						service_rates sr 
					WHERE
						bd.bed_id = b.id 
						AND sr.id = b.service_rate_id 
						AND ipd_admission_id = ad_ipd_admission_id 
					) bed_dtl 
				) charge_dtl 
			ORDER BY
				occupied_from;
		
END
