194 lines
6.6 KiB
SQL
194 lines
6.6 KiB
SQL
DELIMITER $$
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`timeControl_calculate`(
|
|
vDatedFrom DATETIME,
|
|
vDatedTo DATETIME)
|
|
BEGIN
|
|
/*
|
|
* Agrupa por trabajador y día, el tiempo de trabajo y descanso retribuido(si tiene).
|
|
* Los registros horarios incorrectos (tmp.timeControlError) no se considerarán.
|
|
* Si un trabajador ha trabajado más de un cierto umbral de tiempo (vTimeToBreakTime)
|
|
* y no ha tenido descansos que superen un parámetro determinado(vMaxTimeToBreak),
|
|
* se le añadirá un tiempo de descanso (vBreakTime) a sus horas trabajadas.
|
|
* El tiempo de descanso solo se añade si el trabajador realmente disfrutó del descanso.
|
|
* Si disfrutó de menos tiempo de descanso, solo se añade el tiempo que disfrutó.
|
|
*
|
|
* @param vDatedFrom
|
|
* @param vDatedTo
|
|
*
|
|
* @return tmp.timeControlCalculate
|
|
* (workerFk, dated, timeWorkSeconds, timeWorkSexagesimal, timeWorkDecimal, timed)
|
|
*/
|
|
DECLARE vHourSeconds INTEGER;
|
|
DECLARE vDatedFromYesterday DATETIME;
|
|
DECLARE vDatedToTomorrow DATETIME;
|
|
DECLARE vTimeToBreakTime INT;
|
|
DECLARE vBreakTime INT;
|
|
DECLARE vMaxTimeToBreak INT;
|
|
|
|
SELECT DATE_SUB(vDatedFrom, INTERVAL 1 DAY), DATE_ADD(vDatedTo, INTERVAL 1 DAY)
|
|
INTO vDatedFromYesterday, vDatedToTomorrow;
|
|
|
|
SELECT timeToBreakTime, breakTime, maxTimeToBreak, TIME_TO_SEC('01:00:00')
|
|
INTO vTimeToBreakTime, vBreakTime, vMaxTimeToBreak, vHourSeconds
|
|
FROM workerTimeControlConfig
|
|
LIMIT 1;
|
|
|
|
CALL timeControl_getError(vDatedFromYesterday, vDatedToTomorrow);
|
|
|
|
CREATE OR REPLACE TEMPORARY TABLE tmp.workerTimeControl
|
|
(INDEX(userFk, timed), INDEX(timed), INDEX(direction))
|
|
ENGINE = MEMORY
|
|
SELECT wtc.userFk,
|
|
wtc.timed,
|
|
DATE(wtc.timed) dated,
|
|
wtc.direction,
|
|
TRUE isReal
|
|
FROM workerTimeControl wtc
|
|
JOIN tmp.`user` u ON u.userFk = wtc.userFk
|
|
LEFT JOIN (
|
|
SELECT wtc.userFk, MIN(wtc.timed) firstIn
|
|
FROM workerTimeControl wtc
|
|
JOIN tmp.`user` u ON u.userFk = wtc.userFk
|
|
LEFT JOIN tmp.timeControlError tce ON tce.id = wtc.id
|
|
WHERE wtc.timed BETWEEN vDatedFromYesterday AND vDatedToTomorrow
|
|
AND wtc.direction = 'in'
|
|
AND tce.id IS NULL
|
|
GROUP BY userFk
|
|
) fi ON wtc.userFk = fi.userFk
|
|
LEFT JOIN (
|
|
SELECT wtc.userFk, MAX(wtc.timed) lastOut
|
|
FROM workerTimeControl wtc
|
|
JOIN tmp.`user` u ON u.userFk = wtc.userFk
|
|
LEFT JOIN tmp.timeControlError tce ON tce.id = wtc.id
|
|
WHERE wtc.timed BETWEEN vDatedFromYesterday AND vDatedToTomorrow
|
|
AND wtc.direction = 'out'
|
|
AND tce.id IS NULL
|
|
GROUP BY userFk
|
|
) lo ON wtc.userFk = lo.userFk
|
|
LEFT JOIN tmp.timeControlError tce ON tce.id = wtc.id
|
|
WHERE wtc.timed BETWEEN fi.firstIn AND lo.lastOut
|
|
AND tce.id IS NULL
|
|
ORDER BY wtc.userFk, wtc.timed;
|
|
|
|
CREATE OR REPLACE TEMPORARY TABLE tmp.wtcToinsert
|
|
(INDEX(timed))
|
|
ENGINE = MEMORY
|
|
WITH wtc AS(
|
|
SELECT timed,
|
|
userFk,
|
|
dated,
|
|
direction,
|
|
LEAD(dated) OVER
|
|
(PARTITION BY userFk, dated ORDER BY timed) nextDay,
|
|
LEAD(userFk) OVER
|
|
(PARTITION BY userFk ORDER BY timed) nextUserFk,
|
|
ROW_NUMBER() OVER (ORDER BY userFk, timed) MOD 2 isOdd
|
|
FROM tmp.workerTimeControl
|
|
WHERE timed BETWEEN vDatedFromYesterday AND vDatedToTomorrow
|
|
ORDER BY userFk, timed
|
|
), wtcToinsert AS(
|
|
SELECT userFk,
|
|
dated,
|
|
IF(userFk = nextUserFk
|
|
AND nextDay IS NULL
|
|
AND isOdd
|
|
AND direction <> 'out', TRUE, FALSE) outNextDay,
|
|
IF(userFk = nextUserFk
|
|
AND nextDay IS NULL
|
|
AND NOT isOdd
|
|
AND direction <> 'out', TRUE, FALSE) outNextDayWhitBreak
|
|
FROM wtc
|
|
HAVING outNextDay OR outNextDayWhitBreak
|
|
)SELECT userFk, util.dayEnd(dated) timed, 'out' direction
|
|
FROM wtcToinsert
|
|
WHERE outNextDay
|
|
UNION ALL
|
|
SELECT userFk, dated + INTERVAL 1 DAY, 'in'
|
|
FROM wtcToinsert
|
|
WHERE outNextDay
|
|
UNION ALL
|
|
SELECT userFk, util.dayEnd(dated) - INTERVAL 1 SECOND, 'middle'
|
|
FROM wtcToinsert
|
|
WHERE outNextDayWhitBreak
|
|
UNION ALL
|
|
SELECT userFk, util.dayEnd(dated), 'out'
|
|
FROM wtcToinsert
|
|
WHERE outNextDayWhitBreak
|
|
UNION ALL
|
|
SELECT userFk, dated + INTERVAL 1 DAY, 'in'
|
|
FROM wtcToinsert
|
|
WHERE outNextDayWhitBreak
|
|
UNION ALL
|
|
SELECT userFk, dated + INTERVAL 1 DAY + INTERVAL 1 SECOND, 'middle'
|
|
FROM wtcToinsert
|
|
WHERE outNextDayWhitBreak;
|
|
|
|
INSERT INTO tmp.workerTimeControl (userFk, timed, dated, direction, isReal)
|
|
SELECT userFk, timed, DATE(timed), direction, FALSE
|
|
FROM tmp.wtcToinsert;
|
|
|
|
SET @accumulatedForBreakTime = 0;
|
|
SET @oldrealDay = NULL;
|
|
CREATE OR REPLACE TEMPORARY TABLE tmp.timeControlCalculate
|
|
WITH workerTimed AS (
|
|
SELECT
|
|
userFk,
|
|
dated,
|
|
timed,
|
|
(direction ='in' AND isReal) breakPoint,
|
|
SUM(CASE WHEN (direction ='in' AND isReal) THEN TRUE ELSE FALSE END)
|
|
OVER (ORDER BY userFk, timed) AS realDay,
|
|
TIMESTAMPDIFF(SECOND, LAG(timed)
|
|
OVER (PARTITION BY userFk, dated ORDER BY timed), timed) gapTime,
|
|
ROW_NUMBER()
|
|
OVER (PARTITION BY userFk, dated ORDER BY timed) MOD 2 isOdd
|
|
FROM tmp.workerTimeControl
|
|
WHERE timed BETWEEN vDatedFromYesterday AND vDatedToTomorrow
|
|
), accumulated AS (
|
|
SELECT SUM(IF(isOdd, 0, gapTime))
|
|
OVER (PARTITION BY userFk,dated ORDER BY userFk,timed) accumulatedWorkTime,
|
|
SUM(IF(NOT isOdd OR breakPoint, 0, IFNULL(gapTime, 0)))
|
|
OVER (PARTITION BY realDay ORDER BY realDay,timed) accumulatedBreakTime,
|
|
IF(realDay <> @oldrealDay OR (isOdd AND gapTime >= vMaxTimeToBreak),
|
|
@accumulatedForBreakTime := 0,
|
|
@accumulatedForBreakTime := @accumulatedForBreakTime +
|
|
IF(isOdd, 0, gapTime )) accumulatedForBreakTime,
|
|
@oldrealDay := realDay,
|
|
userFk,
|
|
dated,
|
|
realDay
|
|
FROM workerTimed
|
|
), totalWorked AS (
|
|
SELECT userFk,
|
|
dated,
|
|
MAX(accumulatedWorkTime) +
|
|
IF(MAX(accumulatedForBreakTime) >= vTimeToBreakTime,
|
|
LEAST(vBreakTime, MAX(accumulatedBreakTime)),
|
|
0) timeWorkSeconds
|
|
FROM accumulated
|
|
GROUP BY userFk, dated
|
|
)SELECT tw.userFk,
|
|
tw.dated,
|
|
timeWorkSeconds,
|
|
SEC_TO_TIME(timeWorkSeconds) timeWorkSexagesimal,
|
|
timeWorkSeconds / vHourSeconds timeWorkDecimal,
|
|
sub.tableTimed
|
|
FROM totalWorked tw
|
|
JOIN (
|
|
SELECT userFk,
|
|
dated,
|
|
GROUP_CONCAT(DATE_FORMAT(timed, "%H:%i") ORDER BY timed ASC
|
|
SEPARATOR ' - ')tableTimed
|
|
FROM tmp.workerTimeControl
|
|
WHERE timed BETWEEN vDatedFromYesterday AND vDatedToTomorrow
|
|
AND isReal
|
|
GROUP BY userFk, dated
|
|
)sub ON sub.dated = tw.dated
|
|
AND sub.userFk = tw.userFk
|
|
WHERE tw.dated BETWEEN vDatedFrom AND vDatedTo;
|
|
|
|
DROP TEMPORARY TABLE tmp.timeControlError;
|
|
DROP TEMPORARY TABLE tmp.wtcToinsert;
|
|
DROP TEMPORARY TABLE tmp.workerTimeControl;
|
|
END$$
|
|
DELIMITER ; |