valid - Conflictos de horario MySQL

set timezone mysql argentina

Question

Oye, me encontré con este sitio buscando soluciones para superposiciones de eventos en tablas mySQL. Estaba tan impresionado con la solución (que ya está ayudando) Pensé que vería si podía obtener más ayuda ...

De acuerdo, entonces Joe quiere intercambiar turnos con alguien en el trabajo. Él tiene una cita en la corte. Va a la forma de cambio de turno y detiene el cronograma de esta semana (o lo que queda de él). Esto se hace con una consulta DB. Sin sudar. Él elige un turno. A partir de este punto, se pone espinoso.

Entonces, primero, el formulario pasa el inicio del turno y el final del turno al guión. Ejecuta una consulta para cualquier persona que tenga un turno que se superponga a este turno. No pueden trabajar dos turnos a la vez, por lo que todos los ID de usuario de esta consulta se ponen en una lista negra. Esta consulta se ve así:

SELECT DISTINCT user_id FROM shifts
WHERE
FROM_UNIXTIME(''$swap_shift_start'') < shiftend
AND FROM_UNIXTIME(''$swap_shift_end'') > shiftstart

A continuación, ejecutamos una consulta para todos los turnos que son a) de la misma duración (política de la compañía), yb) no se superponen con ningún otro turno en el que esté trabajando Joe.

Lo que tengo actualmente es algo como esto:

SELECT *
FROM shifts
AND shiftstart BETWEEN FROM_UNIXTIME(''$startday'') AND FROM_UNIXTIME(''$endday'')
AND user_id NOT IN ($busy_users)
AND (TIME_TO_SEC(TIMEDIFF(shiftend,shiftstart)) = ''$swap_shift_length'')
$conflict_dates
ORDER BY shiftstart, lastname

Ahora, probablemente se esté preguntando "¿qué es $ conflict_dates ???"

Bueno, cuando Joe envía el turno de intercambio, vuelve a cargar sus turnos para la semana en caso de que decida ver el potencial de otro turno. Entonces, cuando hace la primera consulta, mientras la secuencia de comandos realiza un bucle y genera sus elecciones, también está creando una cadena que se parece a algo así:

AND NOT(
''joe_shift1_start'' < shiftend
AND ''joe_shift1_end'' > shiftstart)
AND NOT(
''joe_shift2_start'' < shiftend
AND ''joe_shift2_end'' > shiftstart)
...etc

De modo que la base de datos obtiene una consulta bastante larga en la línea de:

SELECT *
FROM shifts
AND shiftstart BETWEEN FROM_UNIXTIME(''$startday'') AND FROM_UNIXTIME(''$endday'')
AND user_id NOT IN (''blacklisteduser1'', ''blacklisteduser2'',...etc)
AND (TIME_TO_SEC(TIMEDIFF(shiftend,shiftstart)) = ''$swap_shift_length'')
AND NOT(
''joe_shift1_start'' < shiftend
AND ''joe_shift1_end'' > shiftstart)
AND NOT(
''joe_shift2_start'' < shiftend
AND ''joe_shift2_end'' > shiftstart)
AND NOT(
''joe_shift3_start'' < shiftend
AND ''joe_shift3_end'' > shiftstart)
AND NOT(
''joe_shift4_start'' < shiftend
AND ''joe_shift4_end'' > shiftstart)
...etc
ORDER BY shiftstart, lastname

Por lo tanto, mi esperanza es que SQL tenga una manera genial de lidiar con esto de una manera más simple, o que alguien pueda señalar un principio lógico fantástico que explique los conflictos potenciales de una manera mucho más inteligente. (Observe el uso del ''inicio> fin, fin <inicio'', antes de encontrar que estaba usando intermedios y tuve que restar un minuto de ambos extremos.)

¡Gracias!

UN

Answer 1

Creo que debería poder excluir los otros cambios de Joe usando una selección interna en lugar de la cadena generada, algo así como:

SELECT *
FROM shifts s1
AND shiftstart BETWEEN FROM_UNIXTIME(''$startday'') AND FROM_UNIXTIME(''$endday'')
AND user_id NOT IN ($busy_users)
AND (TIME_TO_SEC(TIMEDIFF(shiftend,shiftstart)) = ''$swap_shift_length'')
AND (SELECT COUNT(1) FROM shifts s2
WHERE s2.user_id = $joes_user_id
AND s1.shiftstart < s2.shiftend
AND s2.shiftstart < s1.shiftend) = 0
ORDER BY shiftstart, lastname

Básicamente, cada fila tiene una consulta interna para el recuento de los cambios de Joe que se superponen, y se asegura de que sea cero. Por lo tanto, solo se devolverán las filas que no se solapen con ninguno de los cambios existentes de Joe.

Answer 2

Puede cargar los joe_shift{1,2,3} en una tabla TEMPORAL y luego hacer una consulta para unirse contra ella, utilizando una combinación externa para buscar solo turnos que no coincidan con ninguno:

CREATE TEMPORARY TABLE joes_shifts (
shiftstart DATETIME
shiftend DATETIME
);
INSERT INTO joes_shifts (shiftstart, shiftend) VALUES
(''$joe_shift1_start'', ''$joe_shift1_end''),
(''$joe_shift2_start'', ''$joe_shift2_end''),
(''$joe_shift3_start'', ''$joe_shift3_end''),
(''$joe_shift4_start'', ''$joe_shift4_end'');
-- make sure you have validated these variables to prevent SQL injection
SELECT s.*
FROM shifts s
LEFT OUTER JOIN joes_shifts j
ON (j.shiftstart < s.shiftend OR j.shiftend > s.shiftstart)
WHERE j.shiftstart IS NULL
AND s.shiftstart BETWEEN FROM_UNIXTIME(''$startday'') AND FROM_UNIXTIME(''$endday'')
AND s.user_id NOT IN (''blacklisteduser1'', ''blacklisteduser2'',...etc)
AND (TIME_TO_SEC(TIMEDIFF(s.shiftend,s.shiftstart)) = ''$swap_shift_length'');

Debido a LEFT OUTER JOIN, cuando no hay una fila coincidente en joes_shifts , las columnas son NULL.