salix/db/routines/vn/functions/client_getFromPhone.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`client_getFromPhone`(vPhone VARCHAR(255))
	RETURNS int(11)
	DETERMINISTIC
BEGIN
/**
 * Searchs a customer associated to the phone number.
 *
 * @param vPhone The phone number
 * @return The client id or %NULL if not found
 */
	DECLARE vClient INT DEFAULT NULL;

	CREATE OR REPLACE TEMPORARY TABLE tClient
		ENGINE = MEMORY
		SELECT id clientFk
			FROM `client`
			WHERE phone = vPhone
				OR mobile = vPhone
		UNION
		SELECT clientFk
			FROM address
			WHERE phone = vPhone
				OR mobile = vPhone
		UNION
		SELECT clientFk
			FROM clientContact
			WHERE phone = vPhone;

	SELECT t.clientFk INTO vClient
		FROM tClient t
			JOIN `client` c ON c.id = t.clientFk
		WHERE c.isActive
			AND c.salesPersonFk
		LIMIT 1;

	DROP TEMPORARY TABLE tClient;

	RETURN vClient;
END$$
DELIMITER ;