drop procedure if EXISTS sp_ot_details;
Create procedure sp_ot_details(	a_flag VARCHAR(1), 
								a_from_date date, 
								a_to_date date)
BEGIN
	SELECT	pt.id as patient_id, 
			pt.patient_type as patient_type,
			pt.patient_unique_id as patient_unique_id,
			pt.admission_date as admission_date,
			pt.patient_name as patient_name,
			pt.dob as dob,
			pt.gender as gender,
			pt.marital_status as marital_status,
			pt.blood_group as blood_group,
			fn_patient_dtls('PADDR', pt.id) as present_addr,
			fn_patient_dtls('MOB', pt.id) as present_mobile,
			fn_patient_dtls('EMAIL', pt.id) as present_email,
			fn_patient_dtls('KIN', pt.id) as present_kin,
			ia.admission_date as admission_date,
			ia.admission_no as admission_no,
			doc.doctor_name as ipd_doctor,
			CASE 
				WHEN is_deceased='Y' THEN 'Deceased'
				WHEN is_discharged='Y' THEN 'Discharge'
				ELSE 'Not Discharge'
			END as present_status,
			ia.discharged_date as discharged_deceased_date,
			ia.policy_id as policy_id,
			ia.beneficiary_id as beneficiary_id,
			ia.card_no as policy_card_no,
			ob.ot_booking_date as ot_booking_date,
			ob.ot_date as ot_date,
			otn.ot_name as ot_name,
			ob.planed_start_dt as planed_start_dt,
			ob.planed_end_dt as planed_end_dt,
			ob.planed_duration as planed_duration,
			ob.actual_start_dt as actual_start_dt,
			ob.actual_end_dt as actual_end_dt,
			ob.actual_duration as actual_duration,
			ob.status as ot_booking_status,
			doc.doctor_name as main_ot_consultant,
			fr.room_name as room_name,
			b.bed_name as bed_name , 
			fn_ot_details('OTDOC', ob.id) as oths_ot_consultant_doctor,

			cd.case_no as case_no,
			cd.case_register_date as case_register_date,
			cd.case_status as case_status,
			cd.is_settled as is_case_settled,
			vd.visit_no as visit_no,
			vd.visit_open_dt as visit_open_dt,
			vd.visit_close_dt as visit_close_dt,
			vd.visit_status as visit_status,

			fn_symptom_vital_hist('S', 'T', ob.id) as symptom_dtls,
			fn_symptom_vital_hist('V', 'T', ob.id) as vital_dtls,

			vd.visit_no as visit_no,
			vd.visit_open_dt as visit_open_dt,
			vd.visit_close_dt as visit_close_dt,
			fn_service_charge_dtls(psc.id) as service_charges,
			ocd.case_no as ot_case_no,
			otv.visit_no as ot_visit_no,
			fn_prescription_dtls(otv.id) as medicine_dtls

	FROM 	patients pt
			INNER JOIN	ipd_admission ia ON pt.id = ia.patient_id
			INNER JOIN	ot_bookings ob ON ob.ref_opd_ipd_id = ia.ID
			INNER JOIN	ot_name otn ON otn.id = ob.ot_name_id
			INNER JOIN 	doctors doc ON doc.id = ob.ot_main_consultant 
			INNER JOIN 	case_details cd ON cd.id = ob.ipd_case_id
			INNER JOIN	visit_details vd ON vd.id = ob.ipd_visit_id
			INNER JOIN	floorwise_room fr ON fr.id = ob.room_id
			INNER JOIN	patient_service_charges psc ON psc.service_source_id = ob.id AND psc.billing_module_id = 12
			INNER JOIN case_details ocd On ocd.opd_ipd_ot_id = ob.id	AND	ocd.opd_ipd_ot_flag = 'T'
			INNER JOIN visit_details otv On otv.case_id = ocd.id
			LEFT JOIN	bed b ON b.room_id = fr.id
	WHERE	(a_from_date <= (CASE WHEN a_flag = 'B' THEN ot_booking_date WHEN a_flag = 'A' THEN ot_date END) OR a_from_date is null)

	And		(a_to_date >= (CASE WHEN a_flag = 'B' THEN ot_booking_date WHEN a_flag = 'A' THEN ot_date END) OR a_to_date is null)

	ORDER BY (CASE WHEN a_flag = 'B' THEN ot_booking_date WHEN a_flag = 'A' THEN ot_date END), patient_unique_id ;
END;
