/* Written by Racktim Guin */

DROP PROCEDURE IF EXISTS sp_clientSearch;
CREATE PROCEDURE `sp_clientSearch`(IN `srchKy` VARCHAR(255))
BEGIN 
SET SESSION sql_mode='';


Select id, client_name, contact_person, client_org, description, contact_details, ft_contact_details, ft_client_name, ft_contact_person
FROM
(
	SELECT
		clients.id,
		clients.client_name,
		clients.contact_person,
		clients.client_org,
		clients.description,
		IFNULL(contacts.contact_details, '') as contact_details,
		MATCH ( contacts.contact_details ) AGAINST ( srchKy ) AS ft_contact_details,
		MATCH ( clients.client_name) AGAINST ( srchKy ) AS ft_client_name,
		MATCH ( clients.contact_person) AGAINST ( srchKy ) AS ft_contact_person
		
	FROM
		clients
		LEFT JOIN contacts ON clients.id = contacts.data_source_id AND contacts.contact_type = 'M' AND contacts.data_source = 'Org' 
	WHERE
		clients.is_active = 'Y' AND
		(MATCH ( contacts.contact_details ) AGAINST ( srchKy ) 
		OR MATCH( clients.client_name ) AGAINST ( srchKy ) 
		OR MATCH( clients.client_org ) AGAINST ( srchKy ) 
		OR MATCH( clients.contact_person ) AGAINST ( srchKy ) 
		)
	ORDER BY
		( ft_contact_details + ft_client_name ) DESC
) pt_search;

END