-- OPD Registration Charge --
SET SESSION sql_mode = "";
SET @app_status = 'A';

SELECT
	patients.patient_unique_id,
	patients.patient_name,
	oda.appointment_no,
	oda.booking_date,
	oda.doctor_appointment_date,
	doc_nd_sp.*,
	SUM(oda.doctor_fees) as doctor_fees,
	SUM(oda.registration_fees) as registration_fees
FROM
	opd_doctor_appointments AS oda
	INNER JOIN patients ON patients.id = oda.patient_id
	INNER JOIN (
	SELECT
		doc.id AS doc_id,
		doc.doctor_name,
		doc_sp.speciality_name 
	FROM
		doctors AS doc
		INNER JOIN doctor_speciality AS doc_sp ON doc_sp.id = doc.specialty_id 
	) AS doc_nd_sp ON doc_nd_sp.doc_id = oda.doctor_id 
WHERE
	DATE( oda.doctor_appointment_date ) >= '2022-02-14' 
	AND DATE( oda.doctor_appointment_date ) <= '2022-02-28'
	AND 
	CASE
			WHEN @app_status = 'A' THEN
			oda.appointment_status IN ( 'B', 'C', 'M', 'R' )
			WHEN @app_status = 'B' THEN
			oda.appointment_status IN ( 'B' )
			WHEN @app_status = 'C' THEN
			oda.appointment_status IN ( 'C' ) 
			WHEN @app_status = 'R' THEN
			oda.appointment_status IN ( 'R' ) 
	END
GROUP BY patients.id
	