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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`timeWorkerControl_getDirection`(vUserFk INT, vTimed DATETIME)
	RETURNS varchar(6) CHARSET utf8mb3 COLLATE utf8mb3_unicode_ci
	NOT DETERMINISTIC
	READS SQL DATA
BEGIN
/**
 * Verifica la dirección de la fichada 
 * @param vUserFk Identificador del trabajador
 * @param vTimed Hora de la fichada
 * @return Retorna sentido de la fichada 'in, out, middle'
 */

	DECLARE vPrevious DATETIME ;
    DECLARE vNext DATETIME ;
    DECLARE vPreviousDirection VARCHAR(3) ;
    DECLARE vNextDirection VARCHAR(3) ;
    DECLARE vDayStayMax INT;
    DECLARE vTimedSeconds INT;
    DECLARE vLastTimeIn INT;
	
    SELECT UNIX_TIMESTAMP(vTimed) INTO vTimedSeconds;
    
    SELECT dayStayMax INTO vDayStayMax
		FROM vn.workerTimeControlParams;

    SELECT timed, direction INTO vNext,vNextDirection
		FROM vn.workerTimeControl 
        WHERE userFk = vUserFk 
			AND direction IN ('in','out')
            AND timed > vTimed
		ORDER BY timed ASC
        LIMIT 1;
        
    SELECT timed, direction INTO vPrevious, vPreviousDirection
		FROM vn.workerTimeControl 
        WHERE userFk = vUserFk 
			AND direction IN ('in','out')
            AND timed < vTimed
		ORDER BY timed DESC
        LIMIT 1;   
	
    IF (vTimedSeconds - UNIX_TIMESTAMP(vPrevious) + UNIX_TIMESTAMP(vNext) - vTimedSeconds)<= vDayStayMax AND vPreviousDirection = 'in'  AND vNextDirection = 'out' THEN 
		RETURN 'middle';
	END IF;
    
    
    IF (vTimedSeconds> UNIX_TIMESTAMP(vPrevious)) THEN
		IF vPreviousDirection = 'in' THEN 
			RETURN 'out';
		ELSE
			SELECT UNIX_TIMESTAMP(MAX(timed)) INTO vLastTimeIn
				FROM vn.workerTimeControl 
				WHERE userFk = vUserFk 
					AND direction ='in'
					AND timed < vPrevious;
			IF vTimedSeconds - vLastTimeIn <= vDayStayMax THEN
				RETURN 'out';
            END IF;
		END IF;
	END IF;

	RETURN 'in';
END$$
DELIMITER ;