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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`client_getSalesPerson`(vClientFk INT, vDated DATE)
	RETURNS int(11)
	DETERMINISTIC
BEGIN
/**
 * Dado un id cliente y una fecha, devuelve su comercial para ese dia, teniendo 
 * en cuenta la jerarquía de las tablas: 1º la de sharingclient, 2º la de 
 * sharingcart y tercero la de clientes.
 *
 * @param vClientFk El id del cliente
 * @param vDated Fecha a comprobar
 * @return El id del comercial para la fecha dada
 **/
	DECLARE vSalesPersonFk INT DEFAULT NULL;
	DECLARE vWorkerSubstituteFk INT DEFAULT NULL;
	DECLARE vLoop BOOLEAN;

	-- Obtiene el comercial original y el de sharingclient

	SELECT c.salesPersonFk, s.workerFk
		INTO vSalesPersonFk, vWorkerSubstituteFk
		FROM client c
			LEFT JOIN sharingClient s
				ON c.id = s.clientFk
					AND vDated BETWEEN s.started AND s.ended
		WHERE c.id = vClientFk
		ORDER BY s.id
        LIMIT 1;

	-- Si no hay ninguno en sharingclient busca en sharingcart

	IF vWorkerSubstituteFk IS NOT NULL
	THEN
		SET vSalesPersonFk = vWorkerSubstituteFk;
	ELSEIF vSalesPersonFk IS NOT NULL
	THEN
		DROP TEMPORARY TABLE IF EXISTS tmp.stack;
		CREATE TEMPORARY TABLE tmp.stack
			(INDEX (substitute))
			ENGINE = MEMORY
			SELECT vSalesPersonFk substitute;

		l: LOOP
			SELECT workerSubstitute INTO vWorkerSubstituteFk
				FROM sharingCart 
				WHERE util.VN_CURDATE() BETWEEN started AND ended
					AND workerFk = vSalesPersonFk
				ORDER BY id
				LIMIT 1;
				
			IF vWorkerSubstituteFk IS NULL THEN
				LEAVE l;
			END IF;
				
			SELECT COUNT(*) > 0 INTO vLoop
				FROM tmp.stack WHERE substitute = vWorkerSubstituteFk;

			IF vLoop THEN
				LEAVE l;
			END IF;
			
			INSERT INTO tmp.stack SET
				substitute = vWorkerSubstituteFk;
			
			SET vSalesPersonFk = vWorkerSubstituteFk;
		END LOOP;
		
		DROP TEMPORARY TABLE tmp.stack;
	END IF;

	RETURN vSalesPersonFk;
END$$
DELIMITER ;