salix/db/routines/vn/procedures/workerJourney_replace.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`workerJourney_replace`(
	vDatedFrom DATE, 
	vDatedTo DATE, 
	vWorkerFk INT)
BEGIN
/**
 * Actualiza la tabla workerJourney para que actue como caché permanente revisable 
 * de las jornadas laborales.
 * @param vDatedFrom workerTimeControl
 * @param vDatedTo workerTimeControl
 * @param vWorkerFk, en caso de pasar 0 ó NULL se aplica a todos
 */	
	DECLARE vDatedTimeTo DATETIME;
	DECLARE vMaxTimePerDay INT; 
	DECLARE vMinHoursToBreak DECIMAL(10,2);
	DECLARE vBreakHours DECIMAL(10,2);
	DECLARE vMonthByYear INT;
	DECLARE vWeeksByYear INT DEFAULT 52;
	DECLARE vMonthsByYear INT DEFAULT 12;
	DECLARE vDaysByWeek INT DEFAULT 5;
	DECLARE vDatedFromYesterday DATETIME;
	DECLARE vDatedToTomorrow DATETIME; 
	
	SELECT DATE_SUB(vDatedFrom, INTERVAL 1 DAY), DATE_ADD(vDatedTo, INTERVAL 1 DAY) 
		INTO vDatedFromYesterday, vDatedToTomorrow;

	SET vDatedTimeTo = util.dayEnd(vDatedTo);
	
	SELECT minHoursToBreak, breakHours, maxTimePerDay 
			INTO vMinHoursToBreak, vBreakHours, vMaxTimePerDay
		FROM workerTimeControlConfig;

	DELETE FROM workerJourney
		WHERE dated BETWEEN vDatedFrom AND vDatedTo 
			AND NOT isPaid
			AND NOT isUpdated
			AND (vWorkerFk IS NULL OR userFK = vWorkerFk); 

	-- Se inserta todos los días del periodo por worker y business en el rango de fechas. 
	INSERT IGNORE INTO workerJourney(userFk, dated, businessFk)
		 SELECT b.workerFk, t.dated, b.id
				FROM business b 
					JOIN time t ON t.dated BETWEEN b.started AND IFNULL(b.ended,util.VN_CURDATE())
				WHERE t.dated BETWEEN vDatedFrom AND vDatedTo
					AND (vWorkerFk IS NULL OR b.workerFk = vWorkerFk)
				ORDER BY b.workerFk, t.dated, b.id;
	
	DROP TEMPORARY TABLE IF EXISTS tmp.`user`;

	IF vWorkerFk THEN
		CALL timeControl_calculateByUser(vWorkerFk, vDatedFrom , vDatedTimeTo);
		CREATE TEMPORARY TABLE IF NOT EXISTS tmp.`user` 
			SELECT vWorkerFk userFk;	
	ELSE
		CALL timeControl_calculateAll(vDatedFrom, vDatedTimeTo);
		CREATE TEMPORARY TABLE IF NOT EXISTS tmp.`user` 
			SELECT id userFk
				FROM worker w;
	END IF; 

	UPDATE workerJourney wj
		JOIN tmp.timeControlCalculate t ON wj.dated = t.dated AND wj.userFk = t.userFk
			SET wj.total = CAST(IF(t.timeWorkDecimal >= vMinHoursToBreak, 
					t.timeWorkDecimal - vBreakHours, 
					t.timeWorkDecimal) AS DECIMAL (10,2)),
				wj.lunch = IF(t.timeWorkDecimal >= vMinHoursToBreak , vBreakHours , 0)
		WHERE wj.dated BETWEEN vDatedFrom AND vDatedTo
			AND NOT wj.isPaid
			AND NOT wj.isUpdated
			AND (vWorkerFk IS NULL OR wj.userFk = vWorkerFk);
	
	-- NOCTURNIDAD 
	CALL timeControl_getError(vDatedFrom, vDatedTimeTo);

	SET @vIsOdd := TRUE;
	SET @vDated := NULL;

	DROP TEMPORARY TABLE IF EXISTS tmp.workerTimeControl;
	CREATE TEMPORARY TABLE tmp.workerTimeControl
			(INDEX (userFk), INDEX (timed))
			ENGINE = MEMORY
		SELECT DISTINCT(wtc.id), 
				wtc.userFk, 
				wtc.timed, 
				wtc.direction
			FROM workerTimeControl wtc
				JOIN tmp.`user` w ON w.userFk = wtc.userFk
				LEFT JOIN tmp.timeControlError tce ON tce.id = wtc.id
			WHERE wtc.timed BETWEEN vDatedFrom AND vDatedToTomorrow
				AND tce.id IS NULL
				AND (vWorkerFk IS NULL OR wtc.userFk = vWorkerFk)
			ORDER BY wtc.userFk, wtc.timed ASC;

	SELECT MAX(id) INTO @vCont 
		FROM tmp.workerTimeControl;

	DROP TEMPORARY TABLE IF EXISTS tmp.workerTimeControlAux; 
	CREATE TEMPORARY TABLE tmp.workerTimeControlAux (
		`id` int(11) ,
		`userFk` int(10) unsigned ,
		`timed` datetime,
		`direction` enum('in', 'out','middle')
		) ENGINE=MEMORY;

	SET @vIsOdd := TRUE;
	SET @lastUserFk := NULL;
	SET @lastDirection := NULL;

	-- Cambio de dia en medio de un descanso
	INSERT INTO tmp.workerTimeControlAux (id, userFk, timed, direction)
		SELECT @vCont:= @vCont + 1,
				lastUserFk,
				util.dayEnd(dated),
				'middle'
			FROM (SELECT IF(@lastUserFk <> userFk, @vIsOdd := TRUE, NULL),
						DATE(@lastTimed) dated,
						@lastUserFk lastUserFk,
						IF(direction ='middle', @vIsOdd := NOT @vIsOdd, FALSE) isOdd,
						IF(@vIsOdd 
							AND @lastDirection = 'middle' 
							AND (DATE(@lastTimed) <> date(timed) 
								OR @lastUserFk <> userFk), TRUE, FALSE) hasBreak,
						@lastTimed := wtc.timed,
						@lastUserFk := userFk,
						@lastDirection := direction
					FROM tmp.workerTimeControl wtc
					ORDER BY wtc.userFk, wtc.timed)sub
			WHERE hasBreak;

	SET @vIsOdd := TRUE;
	SET @lastUserFk := NULL;
	SET @lastDirection := NULL;

	INSERT INTO tmp.workerTimeControlAux (id, userFk, timed, direction)
		SELECT @vCont:= @vCont + 1, 
				lastUserFk, 
				DATE_ADD(sub.dated, INTERVAL 1 DAY), 
				'middle'
			FROM (SELECT IF(@lastUserFk <> userFk, @vIsOdd := TRUE, NULL),
						DATE(@lastTimed) dated,
						@lastUserFk lastUserFk,
						IF(direction ='middle', @vIsOdd := NOT @vIsOdd, FALSE) isOdd,
						IF(@vIsOdd 
							AND @lastDirection = 'middle' 
							AND (DATE(@lastTimed)<>date(timed)
								OR @lastUserFk<>userFk), TRUE, FALSE) hasBreak,
						@lastTimed := wtc.timed,
						@lastUserFk := userFk,
						@lastDirection := direction
					FROM tmp.workerTimeControl wtc
					ORDER BY wtc.userFk, wtc.timed)sub
			WHERE hasBreak;

	-- Cambio de dia
	INSERT INTO tmp.workerTimeControlAux (id, userFk, timed, direction)
		SELECT @vCont:= @vCont + 1,
				wtc.userFk, 
				util.dayEnd(date(wtc.timed)),
				'out'
			FROM workerTimeControl wtc
				JOIN tmp.`user` w ON w.userFk = wtc.userFk
				LEFT JOIN tmp.timeControlError tce ON tce.id = wtc.id
			WHERE wtc.timed BETWEEN vDatedFromYesterday AND vDatedToTomorrow
				AND wtc.direction <> 'out'
			GROUP BY wtc.userFk, DATE(wtc.timed);

	INSERT INTO tmp.workerTimeControlAux (id, userFk, timed, direction)
		SELECT @vCont:= @vCont + 1, 
				wtc.userFk, 
				DATE_ADD(date(wtc.timed), INTERVAL 1 DAY),
				'in'
			FROM workerTimeControl wtc
				JOIN tmp.`user` w ON w.userFk = wtc.userFk
				LEFT JOIN tmp.timeControlError tce ON tce.id = wtc.id
			WHERE wtc.timed BETWEEN vDatedFromYesterday AND vDatedToTomorrow
				AND wtc.direction <> 'out'
			GROUP BY wtc.userFk, DATE(wtc.timed);

	INSERT INTO tmp.workerTimeControl (id, userFk, timed, direction)
		SELECT id, userFk, timed, direction
			FROM tmp.workerTimeControlAux;

	SET @lastUserFk := NULL;
	SET @vIsOdd := TRUE;
	SET @vDated := NULL;
	SET @vLastTimed := NULL;

	UPDATE workerJourney wj
			JOIN (SELECT sub.dated, userFk, SUM(NigthlyHours) NigthlyHours
					FROM(SELECT (@vIsOdd := NOT @vIsOdd) isOdd,
								IF(direction='in', @vIsOdd := TRUE, @vIsOdd := @vIsOdd),
								IF(@vIsOdd, @vLastTimed:=UNIX_TIMESTAMP(timed),@vLastTimed),
								IF(direction='in', @vDated := DATE(wtc.timed), @vDated ) dated,
								wtc.userFk,
								IF(UNIX_TIMESTAMP(timed)- @vLastTimed < vMaxTimePerDay, 
									workerNigthlyHours_calculate(FROM_UNIXTIME(@vLastTimed), wtc.timed), 
									0) NigthlyHours,
								@lastUserFk := userFk
							FROM (SELECT DISTINCT(wtc.id), wtc.userFk, wtc.timed, wtc.direction
									FROM tmp.workerTimeControl wtc
										LEFT JOIN tmp.timeControlError tce ON tce.id = wtc.id
									WHERE wtc.timed BETWEEN vDatedFrom AND util.dayEnd(vDatedToTomorrow)
										AND tce.id IS NULL
									ORDER BY userFk, timed ASC
									LIMIT 10000000000000000000
								) wtc
							ORDER BY wtc.userFk, wtc.timed
						)sub
					WHERE sub.dated BETWEEN vDatedFrom AND vDatedTo	 
					GROUP BY userFk, sub.dated
				)night ON night.userFk = wj.userFk AND night.dated = wj.dated
		SET wj.nocturn = night.NigthlyHours
		WHERE NOT wj.isPaid
			AND NOT isUpdated
			AND night.NigthlyHours <> 0;

	-- Horas téoricas y precio de la hora ordinaria
	UPDATE workerJourney wj
			JOIN business b ON b.id = wj.businessFk
			JOIN calendarType ct ON ct.id = b.calendarTypeFk
		SET wj.priceOrdinaryHour = 
				(vMonthsByYear * b.amount) / 
				(vWeeksByYear * ct.hoursWeek),
			wj.contractJourney = ct.hoursWeek / vDaysByWeek
		WHERE wj.dated BETWEEN vDatedFrom AND vDatedTo
			AND (vWorkerFk IS NULL OR wj.userFk = vWorkerFk)
			AND NOT wj.isPaid
			AND NOT isUpdated;

	-- Precio Extras, Vacaciones y Nocturnas 
	UPDATE workerJourney wj
			JOIN(SELECT MAX(w.dated), t.dated, w.holidayInc, w.nightInc, w.extraInc
					FROM time t
						JOIN workerHourPrice w ON w.dated <= t.dated
					WHERE t.dated BETWEEN vDatedFrom AND vDatedTo
					GROUP BY t.dated
				) sub ON sub.dated = wj.dated
		SET wj.priceHolidayHour = wj.priceOrdinaryHour * sub.holidayInc,
			wj.priceNocturnHour = wj.priceOrdinaryHour * sub.nightInc,
			wj.priceExtraHour = wj.priceOrdinaryHour * sub.extraInc
		WHERE wj.dated BETWEEN vDatedFrom AND vDatedTo
			AND (vWorkerFk IS NULL OR wj.userFk = vWorkerFk)
			AND NOT wj.isPaid
			AND NOT wj.isUpdated;

	-- Elimina el precio de las horas extras para los contratos parciales.
	UPDATE workerJourney wj
			JOIN business b ON b.id = wj.businessFk
			JOIN calendarType ct ON ct.id = b.calendarTypeFk
		SET wj.priceExtraHour = 0
		WHERE ct.isPartial
			AND wj.dated BETWEEN vDatedFrom AND vDatedTo
			AND (vWorkerFk IS NULL OR wj.userFk = vWorkerFk)
			AND NOT wj.isPaid 
			AND NOT wj.isUpdated;

	-- Ausencias
	UPDATE workerJourney wj
			JOIN businessCalendar bc ON bc.businessFk = wj.businessFk 
				AND bc.dated = wj.dated
			JOIN absenceType ab ON ab.id = bc.absenceTypeFk
		SET wj.permission = ab.permissionRate * wj.contractJourney,
			wj.contractJourney = IF(ab.permissionRate = 1, 0, ab.permissionRate) * wj.contractJourney
		WHERE ab.permissionRate > 0
			AND wj.dated BETWEEN vDatedFrom AND vDatedTo
			AND (vWorkerFk IS NULL OR wj.userFk = vWorkerFk)
			AND NOT wj.isPaid
			AND NOT wj.isUpdated;

	-- Sábados / Domingos
	 UPDATE workerJourney
		SET holiday = IF(DAYNAME(dated)='sábado', 0, total),
			contractJourney = 0
		WHERE DAYNAME(dated) IN ('sábado', 'domingo') 
			AND dated BETWEEN vDatedFrom AND vDatedTo
			AND (vWorkerFk IS NULL OR userFk = vWorkerFk)
			AND NOT isPaid
			AND NOT isUpdated;

	-- Festivos por centro de trabajo
	UPDATE workerJourney wj
			JOIN calendarHolidays ch ON ch.dated = wj.dated
			JOIN business b ON b.id = wj.businessFk 
				AND b.workcenterFk = ch.workcenterFk 
		SET wj.holiday = wj.total,
			wj.permission = wj.contractJourney,
			wj.contractJourney = 0
		WHERE wj.dated BETWEEN vDatedFrom AND vDatedTo
			AND (vWorkerFk IS NULL OR wj.userFk = vWorkerFk)
			AND NOT wj.isPaid
			AND NOT wj.isUpdated;

	-- Horas extras
	UPDATE workerJourney 
		SET extra = lunch + total - contractJourney
		WHERE dated BETWEEN vDatedFrom AND vDatedTo
			AND (vWorkerFk IS NULL OR userFk = vWorkerFk)
			AND NOT isPaid
			AND NOT isUpdated;

	DROP TEMPORARY TABLE tmp.timeControlCalculate;
	DROP TEMPORARY TABLE tmp.`user`;
	DROP TEMPORARY TABLE tmp.timeControlError;
	DROP TEMPORARY TABLE tmp.workerTimeControlAux;
	DROP TEMPORARY TABLE tmp.workerTimeControl;
END$$
DELIMITER ;