
SET @from_date = '2022-01-01';

SET @to_date = '2022-02-24';

SET @ot_status = 'C';

SET @ot_room_id = 15;

SET @ot_package_id = 7;

SET @ot_doctor_id = 2;

SET @ot_date_type = 'A';
SELECT
	vw_pharmacy_patient.patient_unique_id AS patient_unique_id,
	vw_pharmacy_patient.patient_name AS patient_name,
	vw_pharmacy_patient.contact_details AS patient_contact,
	next_ofkin.next_ofkin_name AS relatives_name,
	g_cons.contact_details AS relatives_contact,
	ipd_admission.admission_no AS admission_no,
	ipd_admission.admission_date AS admission_date,
	CONCAT( vw_pharmacy_patient.locality, ",", vw_pharmacy_patient.street, ",", vw_pharmacy_patient.pin ) AS address,
	doctor1.doctor_name AS ipd_consultant,
	doctor2.doctor_name AS ot_consultant,
	floorwise_room.room_name AS room_name,
	ot_name.ot_name AS ot_package,
	ot_bookings.planed_start_dt AS planed_start_dt,
	ot_bookings.planed_end_dt AS planed_end_dt,
	ot_bookings.actual_start_dt AS actual_start_dt,
	ot_bookings.actual_end_dt AS actual_end_dt,
	ot_bookings.STATUS AS ot_status,
	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 doctor2 ON doctor2.id = ipd_admission.doctor_id
	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 next_ofkin ON next_ofkin.id =(
	SELECT
		id 
	FROM
		next_ofkin 
	WHERE
		patient_id = vw_pharmacy_patient.id 
		LIMIT 1 
	)
	LEFT JOIN contacts AS g_cons ON g_cons.id = (
	SELECT
		g_cons1.id 
	FROM
		contacts AS g_cons1 
	WHERE
		g_cons1.data_source_id = next_ofkin.id 
		AND g_cons1.data_source = 'kin' 
		AND g_cons1.contact_type = 'M' 
		AND g_cons1.is_active = 'Y' 
	ORDER BY
		g_cons1.id DESC 
		LIMIT 1 
	)
	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( @ot_date_type = 'A', ot_bookings.actual_start_dt, ot_bookings.planed_start_dt ))>= @from_date 
	AND DATE(IF( @ot_date_type = 'A', ot_bookings.actual_start_dt, ot_bookings.planed_start_dt ))<= @to_date 
	AND ot_bookings.STATUS = @ot_status OR @ot_status is NULL
	AND ot_bookings.room_id = @ot_room_id OR @ot_room_id is NULL
	AND ot_bookings.ot_name_id = @ot_package_id OR @ot_package_id is NULL
	AND ot_bookings.ot_main_consultant = @ot_doctor_id OR @ot_doctor_id is NULL
GROUP BY
	vw_pharmacy_patient.patient_unique_id,
	vw_pharmacy_patient.patient_name,
	vw_pharmacy_patient.contact_details,
	next_ofkin.next_ofkin_name,
	ipd_admission.admission_no,
	ipd_admission.admission_date,
	vw_pharmacy_patient.locality,
	vw_pharmacy_patient.street,
	vw_pharmacy_patient.pin,
	doctor1.doctor_name,
	doctor2.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,
	g_cons.contact_details