SET @from_date= '2022-02-22';
SET @to_date= '2022-02-22';
SET @ot_status='P';
SET @group_by= 'room';
SET @date_type='A';

SELECT 
CASE 
WHEN @group_by= 'doctor' THEN doctor1.doctor_name 
WHEN @group_by= 'room' THEN floorwise_room.room_name 
WHEN @group_by= 'package' THEN ot_name.ot_name 
ELSE
	CONCAT(ot_name.ot_name,'|', floorwise_room.room_name, '|', ot_name.ot_name)
END as group_info,
DATE(ot_bookings.planed_start_dt) as date,
'SERVICE' as type,
service_items.service_item_name,
SUM(patient_service_charge_details.net_amount) as amount

FROM patient_service_charge_details

INNER JOIN patient_service_charges
ON patient_service_charge_details.patient_service_charge_id=patient_service_charges.id 
AND patient_service_charges.billing_module_id=12

INNER JOIN service_items
ON service_items.id=patient_service_charge_details.service_id 

INNER JOIN ot_bookings
ON ot_bookings.id=patient_service_charges.service_source_id 
AND ot_bookings.`status`!='N'

INNER JOIN doctors as doctor1
ON doctor1.id=ot_bookings.ot_main_consultant 

INNER JOIN floorwise_room
ON floorwise_room.id=ot_bookings.room_id

INNER JOIN ot_name
ON ot_name.id=ot_bookings.ot_name_id

WHERE 
patient_service_charge_details.`status`!='D'
AND DATE(IF(@date_type='A', ot_bookings.actual_start_dt, ot_bookings.planed_start_dt))>=@from_date
AND DATE(IF(@date_type='A', ot_bookings.actual_start_dt, ot_bookings.planed_start_dt))<=@to_date
AND ot_bookings.`status`=@ot_status

GROUP BY 
ot_name.ot_name,
doctor1.doctor_name,
floorwise_room.room_name,
ot_bookings.planed_start_dt,
service_items.id

UNION

SELECT
CASE 
WHEN @group_by= 'doctor' THEN doctor1.doctor_name 
WHEN @group_by= 'room' THEN floorwise_room.room_name 
WHEN @group_by= 'package' THEN ot_name.ot_name 
ELSE
	CONCAT(ot_name.ot_name,'|', floorwise_room.room_name, '|', ot_name.ot_name)
END as group_info,
DATE(ot_bookings.planed_start_dt) as date,
'MEDICINE' as type,
items.item_name,
SUM(pharmacy_billing_header.total_net_amount)as amount

FROM pharmacy_billing_item

INNER JOIN pharmacy_billing_header
ON pharmacy_billing_header.id= pharmacy_billing_item.pharmacy_billing_header_id
AND pharmacy_billing_header.opd_ipd_ot_flag='T'

INNER JOIN items
ON items.id=pharmacy_billing_item.item_id

INNER JOIN ot_bookings
ON ot_bookings.id=pharmacy_billing_header.opd_ipd_no 
AND ot_bookings.`status`!='N'

INNER JOIN doctors as doctor1
ON doctor1.id=ot_bookings.ot_main_consultant 

INNER JOIN floorwise_room
ON floorwise_room.id=ot_bookings.room_id

INNER JOIN ot_name
ON ot_name.id=ot_bookings.ot_name_id

WHERE 
pharmacy_billing_item.`status`='A'
AND DATE(IF(@date_type='A', ot_bookings.actual_start_dt, ot_bookings.planed_start_dt))>=@from_date
AND DATE(IF(@date_type='A', ot_bookings.actual_start_dt, ot_bookings.planed_start_dt))<=@to_date
AND ot_bookings.`status`=@ot_status

GROUP BY
ot_name.ot_name,
doctor1.doctor_name,
floorwise_room.room_name,
ot_bookings.planed_start_dt,
items.item_name