Cálculo de la cantidad total de equipos para un rango de fechas

Proyecto: Estoy trabajando en un proyecto que trata sobre algunas habitaciones y equipos que se usan en las habitaciones. El software se trata de progtwigr los equipos en las habitaciones. En otras palabras, es un software de reserva que reserva los equipos seleccionados en salas separadas para las fechas y los intervalos de tiempo necesarios. Tengo muchas tablas en la base de datos MYsSQL trabajando con Php, pero mencionaré las tablas de las que se trata mi pregunta. Las tablas que relacionaré con mis preguntas son la tabla de equipos (Tabla A), la tabla de horarios (Tabla B) y los equipos que se usan en el progtwig relacionado (Tabla C).

Tabla A: tabla de lista de equipos

eqid | eqName | available| 1 | book | 90 | 2 | pen | 82 | 3 | computer | 25 | 

En la tabla A; eqid representa la identificación única de un equipo, eqName representa el nombre de un equipo, disponible representa el total de equipos disponibles existentes.

Tabla B: tabla de progtwigción

 scheduleid | startDate | endDate | startTime | endTime | office | 1 | 2012-08-27 | 2012-08-27 | 08:30:00 | 10:00:00 | room1 | 2 | 2012-08-27 | 2012-08-27 | 09:30:00 | 11:00:00 | room3 | 3 | 2012-08-28 | 2012-08-30 | 08:30:00 | 12:00:00 | room2 | 4 | 2012-08-29 | 2012-08-31 | 11:30:00 | 14:00:00 | room1 | 5 | 2012-08-28 | 2012-08-28 | 10:30:00 | 14:00:00 | room3 | 6 | 2012-08-27 | 2012-08-30 | 08:30:00 | 10:00:00 | room4 | 7 | 2012-08-27 | 2012-08-27 | 10:30:00 | 12:00:00 | room4 | 8 | 2012-08-27 | 2012-08-30 | 08:30:00 | 11:00:00 | room6 | 9 | 2012-08-27 | 2012-08-27 | 10:30:00 | 12:00:00 | room5 | 

En la tabla B; Scheduid representa ID único para un cronogtwig, startDate y endDate son rangos de fechas para un cronogtwig, horario de inicio y horario de tiempo de finalización para un cronogtwig, la oficina significa que el cronogtwig tendrá lugar. Déjame dar un ejemplo aquí. Scheduleid 1 significa que hay una reserva el 27 de agosto de 2012, el lunes y es de 08.30 a 10:00. Como comienza y termina el mismo día, es solo un día de reserva en room1. Sin embargo, Scheduleid 3 significa que hay una reserva que comienza el 28 de agosto de 2012, martes y continúa hasta el 30 de agosto de 2012, jueves a las 08: 00-12: 00 … en otras palabras, dura 3 días y todos los días desde 08:30 a 12:00 … Así que hay una reserva de martes a jueves de 08:30 a 12:00 en la habitación2 … Espero que esto esté claro.

Tabla C: equipos que utilizan en el calendario relacionado

 Autoid | scheduleid | eqid | amountInSch| 1 | 1 | 1 | 2 | 2 | 1 | 2 | 3 | 3 | 1 | 3 | 1 | 4 | 2 | 1 | 1 | 5 | 2 | 2 | 1 | 6 | 2 | 3 | 2 | 7 | 3 | 2 | 1 | 8 | 3 | 3 | 3 | 9 | 4 | 2 | 1 | 10 | 4 | 3 | 1 | 11 | 5 | 1 | 1 | 12 | 6 | 1 | 1 | 13 | 6 | 3 | 2 | 14 | 6 | 2 | 4 | 15 | 7 | 1 | 5 | 16 | 7 | 2 | 6 | 17 | 8 | 2 | 1 | 18 | 9 | 1 | 8 | 19 | 9 | 2 | 5 | 20 | 9 | 3 | 6 | 

En la tabla C: Autoid representa la identificación automática única generada por autoincremento, el planid proviene de la Tabla B, eqid proviene de la Tabla A, amountInSch representa cuántos equipos (cantidad) usará en la progtwigción relacionada. Quiero dar un ejemplo aquí. Scheduleid 1 en la Tabla C, hay 3 filas. Esto significa que el planid 1 relacionado en TAble B usará 2 libros (eqid 1), 3 plumas (eqid 2) y 1 computadora (eqid 3) en la habitación 1 de las fechas y horas especificadas en la tabla B. Otro ejemplo es que el planid 3 en la Tabla C está relacionado con 2 filas. Significa que 1 bolígrafo (eqId 2) y 3 computadoras (eqId 3) se usarán en room2 del 27 al 30 de agosto de 2012 todos los días de 08:30 a 12:00.

Lo anterior es la explicación y brinda información sobre el proyecto. Las filas de la tabla no son permanentes. Cuando haga una reserva, habrá una nueva fila en la Tabla B y si se selecciona un equipo, habrá nuevas filas en la tabla C …

La pregunta:

Quiero calcular la cantidad restante de un equipo específico cuando proporciono eqId, startDate, endDate, startTime y endTime …

Un ejemplo:

eqId: 1 (libro)

Fecha de comienzo: 2012-08-27

endDate: 2012-08-27

Hora de inicio: 08:30:00

hora de finalización: 12:00:00

El resultado debería ser: 14 libros utilizados en el cronogtwig y 76 libros disponibles disponibles

Porque: si miras los calendarios y las ecuaciones relacionadas, solo verás 1, 2, 6, 7, 9 calendarios relacionados con mi consulta (fechas y eqId). Si sum la cantidad total relacionada en la Tabla C, obtendrá el resultado incorrecto. En otras palabras, las cantidades relacionadas para eqId (1-libro) y para 1, 2, 6, 7, 9 scheduleIds son 2, 1, 1, 5, 8 respectivamente. Entonces, si los sum, obtendrá 17, lo cual es incorrecto. Porque el horario 1 y 9 no se cruzan entre sí en términos de tiempos de inicio y final, y 6 y 7 tampoco se cruzan entre sí. como resultado de ellos, 2 se quedan solos y se pueden contar por separado. Debemos considerar 1 y 9 como sumdos 8 porque 8 es mayor que 2. es igual para 6 y 7, se considera 5 porque 5 es mayor que 1 …

Entonces gente! No estoy seguro de cómo puedo sumr / esto en el algoritmo de progtwigción. ¿Hay alguna manera de hacerlo en SQL o tengo que usar PHP y Mysql juntos? ¿y cómo?

¡Aclamaciones!

Registros SQLFiddle

Empecé con el siguiente SQL para reunir todos los rangos de fechas que se cruzan con el rango dado:

 SELECT MAX(available) - IFNULL(SUM(amountInSch), 0) FROM Table1 LEFT JOIN Table3 USING (eqid) LEFT JOIN Table2 USING (scheduleid) WHERE DATE(startDate) <= '2012-08-27' AND DATE(endDate) >= '2012-08-27' AND endTime > '08:30' AND startTime < '12:00' AND eqid = 1 

Violín

Esta es solo la primera parte. A continuación, debe resolver las posibles superposiciones; esto no sería práctico con SQL, por lo que sugeriría hacer esto en PHP.

El algoritmo genérico que elegiría desafortunadamente es O (n ** 2), dice así:

  • crear una línea de tiempo (demarcada por cada día) con el tiempo como el eje horizontal
  • itere sobre cada rango de fecha / hora y marque el tiempo de su borde izquierdo y derecho para crear segmentos de tiempo de cada permutación posible.
  • al usar los segmentos, se sumn verticalmente para superposiciones y se toma el máximo diario.

Espero que ayude.