mysql — PHP Назначение Бронирование Доступные временные интервалы

Я разрабатываю систему онлайн-бронирования для клиента, где мне нужно найти доступное время встречи, которое их клиенты могут выбрать, чтобы забронировать следующую встречу.

Я попытаюсь объяснить текущую настройку базы данных:

Название таблицы: назначения — это встречи, которые в настоящее время забронированы клиентами.

+----+---------+------------+-----------+----------+--------------+------+-----------+
| id | Staffid | дата | время начала | конечное время | имя клиента | комната | лечение |
+ ---- + --------- + ------------ + ----------- + --------- - + -------------- + ------ + ----------- +
| 1 | 2 | 2015-08-24 | 09:00:00 | 10:00:00 | Мистер смит | 1 | 1 |
| 2 | 2 | 2015-08-24 | 11:00:00 | 12:00:00 | Мистер джонс | 2 | 1 |
+ ---- + --------- + ------------ + ----------- + --------- - + -------------- + ------ + ----------- +

---

Название таблицы: штат сотрудников — список сотрудников, день недели, когда они работают, а также время начала и окончания их смен в соответствующий день.

+----+------+-----------+-----------+----------+
| id | имя | день недели | время начала | конечное время |
+ ---- + ------ + ----------- + ----------- + ---------- +
| 1 | Фил | 1 | 09:00:00 | 17:00:00 |
| 2 | Лиза | 5 | 09:00:00 | 18:00:00 |
| 3 | Лиза | 3 | 09:00:00 | 17:00:00 |
| 4 | Боб | 5 | 15:00:00 | 17:00:00 |
+ ---- + ------ + ----------- + ----------- + ---------- +

---

Название таблицы: лечение — список доступных процедур

+----+-------------+----------+
| id | Имя | продолжительность |
+ ---- + ------------- + ---------- +
| 1 | Лечение 1 | 01:30:00 |
| 2 | Лечение 2 | 01:00:00 |
+ ---- + ------------- + ---------- +

---

Название таблицы: stafftreatments — справочная таблица, чтобы определить, какие процедуры проводятся каким персоналом.

+----+-------+-----------+
| id | персонал | лечение |
+ ---- + ------- + ----------- +
| 1 | 1 | 1 |
| 2 | 2 | 1 |
+ ---- + ------- + ----------- +

Таким образом, мы можем предположить, что лечение 1 проводится сотрудником Лизой, но Лиза работает только в среду с 9 до 17 часов и в пятницу с 9 до 18 часов. Фил также может выполнять процедуру 1, но работает только в понедельник с 9 до 17 часов.

В моем приложении я спрашиваю пользователя, какое обращение они хотят заказать. Например, лечение 1, которое длится 90 минут. Затем клиент выберет дату, на которую он хочет записаться. Таким образом, на этом этапе мы знаем продолжительность требуемой встречи и дату, а затем день недели.

Я собираюсь взять выбранную дату и перебрать каждый день, скажем, на следующие 5 дней

Я застрял, пытаясь разработать запрос, который позволит мне показать все доступные временные интервалы, которые клиент может выбрать, основываясь на таблице встреч, которая содержит предварительно забронированные встречи.

Предположим, клиент хочет заказать лечение 1 24 августа. Мы знаем, что день недели пятница, поэтому будет 5, мы знаем, что Лиза работает в пятницу с 9:00 до 18:00, но у нее уже назначена встреча с 9:00 до 12:00.

Поскольку лечение длится 90 минут, мне нужно получить такие данные, как:

+----------+-------+----------------+--------------+
| дата | Персонал | Доступен старт | Доступен конец |
+ ---------- + ------- + ---------------- + ------------- - +
| 24.08.15 | Лиза | 12:00 | 13:30 |
| 24.08.15 | Лиза | 13:30 | 15:00 |
| 24.08.15 | Лиза | 15:00 | 16:30 |
| 24.08.15 | Лиза | 16:30 | 18:00 |
| 24.08.15 | Боб | 15:00 | 16:30 |
+ ---------- + ------- + ---------------- + ------------- - +

Однако следующая проблема заключается в том, что доступно только 4 комнаты, поэтому можно забронировать не более 4 процедур одновременно.

Я видел этот пост — Найти свободные блоки времени в MySQL и PHP?

но моя отставка немного сложнее, и я не уверен, как построить запрос. Любая помощь будет очень высоко ценится.

Сделали несколько изменений в структуре, и мой запрос выглядит следующим образом:

ВЫБЕРИТЕ тренера, день, дату бронирования, время от времени, время до времени
ОТ
(
ВЫБЕРИТЕ
, день
, Дата бронирования
, TIMEDIFF (start_time, IF (bookingdate = @ prevdate, @ prevend, open_time)) как временной интервал
, IF (bookingdate = @ prevdate, @ prevend, open_time) с момента времени
, start_time как to_time
@prevend: = end_time как prevend
, @prevdate: = bookingdate как предыдущий
ОТ резервирования
ПРИСОЕДИНЯЙТЕСЬ (ВЫБЕРИТЕ @prevend: = null, @ prevdate: = null) как init
ВНУТРЕННИЕ РЕГИСТРАЦИИ
ON a.trainer = c.trainer
И НЕДЕЛЯ (c.bookingdate) = a.day

UNION

ВЫБЕРИТЕ
день
, Дата бронирования
, TIMEDIFF (close_time, IFNULL (MAX (end_time), open_time)) в качестве временного интервала
, IFNULL (MAX (end_time), open_time) как from_time
, close_time как to_time
, null как prevend
, ноль, как преобладают
ОТ резервирования
СЛЕДУЕТ ПРИСОЕДИНЯТЬСЯ
ON a.trainer = c.trainer
И НЕДЕЛЯ (c.bookingdate) = a.day
GROUP BY a.trainer, день, дата бронирования
) как пробелы
ГДЕ таймслот> '00: 00: 00'
ЗАКАЗАТЬ тренером, день, дату бронирования, с_ времени;

со следующими данными:

+----+-------------+---------+------------+----------+-------------+------+-----------+
| id | дата бронирования | тренер | время начала | end_time | customer_id | комната | лечение |
+ ---- + ------------- + --------- + ------------ + ------- --- + ------------- + ------ + ----------- +
| 1 | 2015-08-24 | 2 | 15:00:00 | 16:00:00 | Мистер смит | 1 | 1 |
| 2 | 2015-08-31 | 2 | 16:00:00 | 17:00:00 | Мистер джонс | 2 | 1 |

а также

+-----------------+---------+-----+-----------+------------+-------------+
| наличие_идентификатора | тренер | день | open_time | close_time | имя тренера |
+ ----------------- + --------- + ----- + ----------- + --- --------- + ------------- +
| 4 | 1 | 2 | 09:00:00 | 17:00:00 | Лиза |
| 6 | 1 | 4 | 09:00:00 | 17:00:00 | Лиза |
| 7 | 1 | 5 | 09:00:00 | 17:00:00 | Лиза |
+ ----------------- + --------- + ----- + ----------- + --- --------- + ------------- +

Возвращаемые данные:

+---------+-----+-------------+-----------+----------+----------+
| тренер | день | дата бронирования | from_time | to_time | временной интервал |
+ --------- + ----- + ------------- + ----------- + ------- --- + ---------- +
| 1 | 2 | NULL | 09:00:00 | 17:00:00 | 08:00:00 |
| 1 | 4 | NULL | 09:00:00 | 17:00:00 | 08:00:00 |
| 1 | 5 | NULL | 09:00:00 | 17:00:00 | 08:00:00 |
+ --------- + ----- + ------------- + ----------- + ------- --- + ---------- +

но не показывает дату бронирования, только нулевые значения.

Есть идеи ?