DROP PROCEDURE `sp_patrientSearch`;
CREATE PROCEDURE `sp_patrientSearch`(IN `srchKy` VARCHAR(255), IN `a_search_modules` VARCHAR(50))
BEGIN 
SET SESSION sql_mode='';

SELECT  LOCATE("X", a_search_modules) 
INTO	@tv_all;

Select id, patient_name, patient_unique_id, opd_ipd_ot_flag, contact_details, admission_no,is_dead
FROM
(
	SELECT
		patients.id,
		patients.patient_name,
		patients.patient_unique_id,
		IFNULL(contacts.contact_details, '') as contact_details,
		IFNULL(opd_ipd_ot_flag,'X') as opd_ipd_ot_flag,	
		IFNULL(ipd_admission.admission_no, '') as admission_no, 
		IFNULL(ipd_admission.is_deceased, 'N') as is_dead, 
		MATCH ( contacts.contact_details ) AGAINST ( srchKy ) AS ft_contact_details,
		MATCH ( patients.patient_unique_id ) AGAINST ( srchKy ) AS ft_patient_unique_id,
		MATCH ( patients.patient_name ) AGAINST ( srchKy ) AS ft_patient_name 
		
	FROM
		patients
		LEFT JOIN case_details ON  case_details.patient_id = patients.id And case_status = 'O' AND	IFNULL(opd_ipd_ot_flag,'X') <>  (CASE WHEN @tv_all = 0 THEN 'X' ELSE 'Y' END)
		LEFT JOIN contacts ON patients.id = contacts.data_source_id AND contacts.contact_type = 'M' AND contacts.data_source = 'Patient' 
		LEFT JOIN  ipd_admission ON  ipd_admission.patient_id = patients.id 
	WHERE
		 (MATCH ( contacts.contact_details ) AGAINST ( srchKy ) 
		OR MATCH( patients.patient_unique_id ) AGAINST ( srchKy ) 
		OR MATCH( patients.patient_name ) AGAINST ( srchKy ) 
		OR MATCH( ipd_admission.admission_no ) AGAINST ( srchKy ) 
		)
	GROUP BY patients.patient_unique_id, opd_ipd_ot_flag
	ORDER BY
		( ft_contact_details + ft_patient_unique_id + ft_patient_name + admission_no ) DESC
) pt_search
	WHERE opd_ipd_ot_flag IN (SELECT *
		 FROM
		   JSON_TABLE(
				 a_search_modules,
			 "$[*]"
			 COLUMNS(case_flag VARCHAR(10) PATH "$")
		   ) data);

END