49 lines
1.2 KiB
SQL
49 lines
1.2 KiB
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 ;
|