37 lines
1.1 KiB
MySQL
37 lines
1.1 KiB
MySQL
|
DELIMITER $$
|
||
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`workerTimeControl_weekCheckBreak`(vStarted DATE, vEnded DATE)
|
||
|
|
BEGIN
|
||
|
|
/**
|
||
|
|
* Retorna los trabajadores que no han respetado el descanso semanal de 36/72 horas
|
||
|
|
* El sistema verificará el descanso corto en la siguiente semana
|
||
|
|
* o el largo en las 2 siguientes semanas a partir de las fechas dadas
|
||
|
|
* @param vStarted día inicio para verificar
|
||
|
|
* @param vEnded día final para verificar
|
||
|
|
* @return tmp.workerWithoutWeekBreak (workerFk)
|
||
|
|
*/
|
||
|
|
DECLARE vDone INT DEFAULT FALSE;
|
||
|
|
|
||
|
|
IF(vStarted > vEnded) then
|
||
|
|
CALL util.throw ('Date end can not be greater then start');
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
DROP TEMPORARY TABLE IF EXISTS tmp.workerWithoutWeekBreakInWeek;
|
||
|
|
CREATE TEMPORARY TABLE tmp.workerWithoutWeekBreakInWeek
|
||
|
|
(workerFk INT,
|
||
|
|
PRIMARY KEY(workerFk))
|
||
|
|
ENGINE = MEMORY;
|
||
|
|
|
||
|
|
|
||
|
|
WHILE vStarted <= vEnded DO
|
||
|
|
|
||
|
|
CALL workerTimeControl_checkBreak(vStarted);
|
||
|
|
INSERT IGNORE INTO tmp.workerWithoutWeekBreakInWeek
|
||
|
|
SELECT workerFk
|
||
|
|
FROM tmp.workerWithoutWeekBreak;
|
||
|
|
DROP TEMPORARY TABLE IF EXISTS tmp.workerWithoutWeekBreak;
|
||
|
|
SET vStarted = DATE_ADD(vStarted, INTERVAL 1 DAY);
|
||
|
|
|
||
|
|
END WHILE;
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|