------ Bed Status ------

SET @typ = 'A';  /* O = Occupied beds, U = Unoccupied beds, A = ALL */


SELECT
	bed.bed_name as bed_name,
	floorwise_room.room_name as room_name,
	blockwise_floor.floor_name as floor_name,
	bulding_block.block_name as block_name,
	admt.admission_no as admit_no,
	admt.admission_date as admit_dt,
	p.patient_unique_id as P_id,
	CONCAT( p.patient_name, ' (', IFNULL( p_cons.contact_details, '--' ), ')' ) as P_name 
FROM
	bed
	JOIN floorwise_room ON bed.room_id = floorwise_room.id
	JOIN blockwise_floor ON floorwise_room.floor_id = blockwise_floor.id
	JOIN service_rates ON service_rates.id = bed.service_rate_id 
	AND service_rates.service_item_id = bed.service_item_id
	JOIN bulding_block ON blockwise_floor.block_id = bulding_block.id
	LEFT JOIN visit_details ON bed.id = visit_details.bed_id 
	AND visit_details.visit_status = 'O'
	LEFT JOIN case_details ON case_details.id = visit_details.case_id 
	AND case_details.case_status = 'O'
	LEFT JOIN ipd_admission admt ON case_details.opd_ipd_ot_id = admt.id 
	AND case_details.opd_ipd_ot_flag = 'I'
	LEFT JOIN patients p ON admt.patient_id = p.id
	LEFT JOIN contacts p_cons ON p_cons.id = (
	SELECT
		p_cons1.id 
	FROM
		contacts p_cons1 
	WHERE
		p_cons1.data_source_id = p.id 
		AND p_cons1.data_source = 'Patient' 
		AND p_cons1.contact_type = 'M' 
		AND p_cons1.is_active = 'Y' 
	ORDER BY
		p_cons1.id DESC 
		LIMIT 1 
	) 
WHERE
	floorwise_room.room_type IN ( 'IPDR', 'ICU' ) 
	AND bed.is_active = 'Y' 
	AND (
	CASE
			
			WHEN @typ = 'O' THEN
			( bed.is_occupied = 'Y' ) 
			WHEN @typ = 'U' THEN
			( bed.is_occupied = 'N' ) 
			WHEN @typ = 'A' THEN
			( 1 = 1 ) 
	END	) 
	ORDER BY
		bulding_block.id ASC,
		blockwise_floor.id ASC,
		floorwise_room.id ASC,
		bed.id ASC