---IPD Admission Report || Discharge Report ||  Deceased Report || ALL  ---

SET @type = 'AL';  /*  DI => Discharge, DE = Deceased, AC = Active, AL = All */

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(CONCAT( gurds.next_ofkin_name, ' (', IFNULL( g_cons.contact_details, '--' ), ')' ),'--') as primary_gurd,
	IFNULL(CONCAT( p_adrs.city, ',', p_adrs.street, ' PIN: ', p_adrs.pin ),'--') as p_address,
	doc.doctor_name as doc_nm,
	bd.bed_name as bd_nm,
	IFNULL(CONCAT(tpa_org.organisation_name,' (',tpa_pol.policy_name,')'),'--') as tpa_dtls,
	IFNULL(tpa_stat.status,'N/A') as tpa_stat,
	(CASE 
		WHEN admt.is_deceased = 'Y' THEN 'EXPIRED'
		WHEN admt.is_deceased = 'N' AND admt.is_discharged = 'Y' THEN 'DISCHARGED'
		ELSE 'ACTIVE'
	END) as pat_stat,
	IFNULL(admt.discharged_date,'--') as discharged_date

FROM
	ipd_admission 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 next_ofkin gurds ON gurds.id = ( SELECT gurds1.id FROM next_ofkin gurds1 WHERE p.id = gurds1.patient_id ORDER BY gurds1.id DESC LIMIT 1 )
	LEFT JOIN contacts g_cons ON g_cons.id = (
	SELECT
		g_cons1.id 
	FROM
		contacts g_cons1 
	WHERE
		g_cons1.data_source_id = gurds.id 
		AND g_cons1.data_source = 'kin' 
		AND g_cons1.contact_type = 'M' 
		AND g_cons1.is_active = 'Y' 
	ORDER BY
		g_cons1.id DESC 
		LIMIT 1 
	)
	LEFT JOIN addresses p_adrs ON p_adrs.data_source_id = p.id 
	AND p_adrs.data_source = 'Patient' 
	AND p_adrs.address_type = 'P' 
	AND p_adrs.is_active = 'Y'
	INNER JOIN doctors doc ON admt.doctor_id = doc.id
	
	INNER JOIN ipd_bed_details ip_bd_dtls ON ip_bd_dtls.id = (SELECT ip_bd_dtls1.id FROM ipd_bed_details ip_bd_dtls1 WHERE ip_bd_dtls1.ipd_admission_id = admt.id ORDER BY ip_bd_dtls1.id DESC LIMIT 1)
	
	LEFT JOIN bed bd ON ip_bd_dtls.bed_id = bd.id
	LEFT JOIN tpa_policies tpa_pol ON admt.policy_id = tpa_pol.id
	LEFT JOIN tpa_organisations tpa_org ON tpa_pol.tpa_id = tpa_org.id
	LEFT JOIN tpa_patient_claim_workflow tpa_stat ON tpa_stat.ipd_admission_id = admt.id
	WHERE (CASE 
		WHEN @type = 'DI' THEN (admt.is_discharged = 'Y' AND admt.is_deceased = 'N')
		WHEN @type = 'DE' THEN (admt.is_deceased = 'Y')
		WHEN @type = 'AC' THEN (admt.is_discharged = 'N' AND admt.is_deceased = 'N')
		WHEN @type = 'AL' THEN 1 = 1
	END)