---------------   Consultant Report  -------------------

SET SESSION sql_mode = '';


SELECT
	p.patient_unique_id as P_id,
	CONCAT( p.patient_name, ' (', IFNULL( p_cons.contact_details, '--' ), ')' ) as P_name,
	admt.admission_no as admit_no,
	admt.admission_date as admit_dt,
	m_doc.doctor_name as main_doc,
	o_doc.docNms as other_docs,
	doc_visits.visited_date as v_time,
	v_doc.doctor_name as v_by,
	doc_visits.standard_rate as v_rate
FROM
	ipd_admission as admt
	INNER JOIN patients p ON admt.patient_id = p.id
	LEFT JOIN contacts p_cons ON p_cons.id = (
	SELECT
		p_cons1.id 
	FROM
		contacts p_cons1 
	WHERE
		p_cons1.data_source_id = p.id 
		AND p_cons1.data_source = 'Patient' 
		AND p_cons1.contact_type = 'M' 
		AND p_cons1.is_active = 'Y' 
	ORDER BY
		p_cons1.id DESC 
		LIMIT 1 
	)
	INNER JOIN doctors m_doc ON admt.doctor_id = m_doc.id
	LEFT JOIN (
	SELECT
		GROUP_CONCAT( doctors.doctor_name ) docNms,
		ipd_patient_consultant.ipd_admission_id ipId 
	FROM
		ipd_patient_consultant
		INNER JOIN doctors ON doctors.id = ipd_patient_consultant.doctor_id 
	GROUP BY
	ipd_patient_consultant.ipd_admission_id 
	) o_doc ON o_doc.ipId = admt.id
	LEFT JOIN ipd_patient_consultant_visit doc_visits ON doc_visits.ipd_admission_id = admt.id AND doc_visits.is_active = 'Y'
	LEFT JOIN doctors v_doc ON v_doc.id = doc_visits.doctor_id