drop procedure if EXISTS sp_opd_appoinment_details;
Create procedure sp_opd_appoinment_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,
			oda.booking_date as booking_date,
			oda.doctor_appointment_date as doctor_appointment_date,
			oda.appointment_no as appointment_no,
			oda.appointment_srl as appointment_srl,
			oda.appointment_queue as appointment_queue,
			oda.doctor_clinic_schedule_id as doctor_clinic_schedule_id,
			oda.room_id as room_id,
			oda.is_register as is_register_at_opd,
			oda.registration_fees as registration_fees,
			doc.doctor_name as opd_doctor,
			oda.doctor_fees as doctor_fees,
			oda.appointment_status as appointment_status,
			oda.rescheduled_type as rescheduled_type,
			oda.prev_appointment_id as prev_appointment_id,
			cv.case_no as case_no,
			cv.case_register_date as case_register_date,
			cv.clearence_status as clearence_status,
			cv.case_status as case_status,
			cv.is_case_settled as is_case_settled,
			cv.visit_no as visit_no,
			cv.visit_open_dt as visit_open_dt,
			cv.visit_close_dt as visit_close_dt,
			cv.visit_status as visit_status,
			cv.is_prescription_uploaded as is_prescription_uploaded,
			cv.is_digital_prescription_filled as is_digital_prescription_filled,
			cv.is_visit_cancelled as is_visit_cancelled,
			fn_symptom_vital_hist('S', 'O', oda.id) as symptom_dtls,
			fn_symptom_vital_hist('V', 'O', oda.id) as vital_dtls,
			psc.bill_no as  bill_no,
			psc.bill_date as bill_date,
			psc.gross_amount as gross_amount,
			psc.discount_amount as discount_amount,
			psc.total_gst_amount as total_gst_amount,
			psc.net_amount as net_amount,
			fn_service_charge_dtls(psc.id) as service_charges,
			fn_prescription_dtls(cv.visit_id) as prescription_dtls
	FROM 	patients pt
			INNER JOIN	opd_doctor_appointments oda ON pt.id = oda.patient_id
			INNER JOIN doctors doc ON doc.id = oda.doctor_id 
			LEFT JOIN (SELECT 
					vd.id as visit_id,
					vd.opd_id as opd_id,
					cd.case_no as case_no,
					cd.case_register_date as case_register_date,
					cd.clearence_status as clearence_status,
					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.room_id as room_id,
					vd.doctor_id as doctor_id,
					vd.visit_status as visit_status,
					vd.is_prescription_uploaded as is_prescription_uploaded,
					vd.is_digital_prescription_filled as is_digital_prescription_filled,
					vd.is_cancelled as is_visit_cancelled 
			FROM	case_details cd
			INNER JOIN	visit_details vd ON cd.id = vd.case_id AND	cd.opd_ipd_ot_flag = 'O' )  cv ON oda.id = cv.opd_id
			LEFT JOIN	patient_service_charges psc ON psc.service_source_id = oda.id AND psc.billing_module_id = 3
	WHERE	(a_from_date <= (CASE WHEN a_flag = 'B' THEN booking_date WHEN a_flag = 'A' THEN cast(doctor_appointment_date as date) END) OR a_from_date is null)
	And		(a_to_date >= (CASE WHEN a_flag = 'B' THEN booking_date WHEN a_flag = 'A' THEN cast(doctor_appointment_date as date) END) OR a_to_date is null)

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