vn-asterisk/sql/client-from-phone.sql

/**
 * Obtains a customer id from a phone number.
 *
 * @param v_phone The caller phone number
 * @return The customer id or %NULL if customer not exists or is inactive
 **/
DROP FUNCTION IF EXISTS pbx.clientFromPhone;
DELIMITER $$
CREATE FUNCTION pbx.clientFromPhone (vPhone VARCHAR(255))
	RETURNS INT
	DETERMINISTIC
BEGIN
    DECLARE vClient INT DEFAULT NULL;
    
    -- SET vPhone = vPhone COLLATE 'utf8_unicode_ci';
    
    -- Searchs a customer associated to the phone number

	DROP TEMPORARY TABLE IF EXISTS tmp.customer;
	CREATE TEMPORARY TABLE tmp.customer
		ENGINE = MEMORY
		SELECT id_cliente customer
			FROM vn2008.Clientes c
			WHERE telefono = vPhone
				OR movil = vPhone
		UNION
		SELECT id_cliente
			FROM vn2008.Consignatarios
			WHERE telefono = vPhone
				OR movil = vPhone
		UNION
		SELECT r.id_cliente
			FROM vn2008.Relaciones r
				JOIN vn2008.Contactos c ON r.Id_Contacto = c.Id_Contacto
			WHERE c.telefono = vPhone
				OR c.movil = vPhone;

	SELECT t.customer INTO vClient
		FROM tmp.customer t
			JOIN vn2008.Clientes c ON c.id_cliente = t.customer
		WHERE c.activo
        LIMIT 1;

	DROP TEMPORARY TABLE tmp.customer;

	RETURN vClient;
END$$
DELIMITER ;