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

SELECT 
vw_pharmacy_patient.patient_unique_id,
vw_pharmacy_patient.patient_name,
vw_pharmacy_patient.contact_details as patient_contact,
ipd_admission.admission_no,
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,
ot_bookings.planed_start_dt,
ot_bookings.planed_end_dt,
ot_bookings.status,
ot_bookings.actual_start_dt,
ot_bookings.actual_end_dt,
SUM(patient_service_charges.net_amount) as ot_charge_for_service,
SUM(pharmacy_billing_header.total_net_amount) as ot_charge_for_pharmacy,
SUM(pharmacy_return_header.total_gross_amt) as ot_charge_for_pharmacy_return

FROM `ot_bookings`

INNER JOIN ipd_admission
ON ipd_admission.id=ot_bookings.ref_opd_ipd_id 

INNER JOIN case_details
ON case_details.opd_ipd_ot_id=ot_bookings.id 
AND case_details.opd_ipd_ot_flag='T'

INNER JOIN vw_pharmacy_patient
ON vw_pharmacy_patient.id=case_details.patient_id 
AND vw_pharmacy_patient.patient_type='E'

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

INNER JOIN patient_service_charges
ON patient_service_charges.service_source_id=ot_bookings.id AND patient_service_charges.billing_module_id=12

LEFT JOIN pharmacy_billing_header
ON pharmacy_billing_header.opd_ipd_no=ot_bookings.id AND pharmacy_billing_header.opd_ipd_ot_flag='T'

LEFT JOIN pharmacy_return_header
ON pharmacy_return_header.pharmacy_billing_header_id=pharmacy_billing_header.id AND pharmacy_return_header.status='A'

WHERE 
ot_bookings.status!='N'
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
vw_pharmacy_patient.patient_unique_id,
vw_pharmacy_patient.patient_name,
vw_pharmacy_patient.contact_details,
ipd_admission.admission_no,
doctor1.doctor_name,
floorwise_room.room_name,
ot_name.ot_name,
ot_bookings.planed_start_dt,
ot_bookings.planed_end_dt,
ot_bookings.status,
ot_bookings.actual_start_dt,
ot_bookings.actual_end_dt,
patient_service_charges.net_amount,
pharmacy_billing_header.total_net_amount,
pharmacy_return_header.total_gross_amt
