-- Case Transfer Report || Visit Transfer Report --

SET @type = 'V'; /* V = Visit(ALL), C = CASE only */


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,
	IFNULL(c_frm.case_no,'--') as case_from,
	IFNULL(c_to.case_no,'--') as case_to,
	IFNULL(v_frm.visit_no,'--') as visit_from,
	IFNULL(v_to.visit_no,'--') as visit_to,
	trnfr_data.transfer_date
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 
	)
LEFT JOIN ipd_case_visit_transfer trnfr_data ON trnfr_data.ipd_admission_id = admt.id
LEFT JOIN case_details c_frm ON c_frm.id = trnfr_data.from_case_id
LEFT JOIN visit_details v_frm ON v_frm.id = trnfr_data.from_visit_id
LEFT JOIN case_details c_to ON c_to.id = trnfr_data.to_case_id
LEFT JOIN visit_details v_to ON v_to.id = trnfr_data.to_visit_id
WHERE (
	CASE
		WHEN @type = 'C' THEN (trnfr_data.is_trf_new_case = 'Y')
		WHEN @type = 'V' THEN  (1 = 1)
	END)
ORDER BY admt.id DESC