Доступные временные интервалы для бронирования встреч в PHP

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

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

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

+----+---------+------------+-----------+----------+--------------+------+-----------+
| id | staffid |    date    | starttime | endtime  | customername | room | treatment |
+----+---------+------------+-----------+----------+--------------+------+-----------+
|  1 |       2 | 2015-08-24 | 09:00:00  | 10:00:00 | Mr Smith     |    1 |         1 |
|  2 |       2 | 2015-08-24 | 11:00:00  | 12:00:00 | Mr Jones     |    2 |         1 |
+----+---------+------------+-----------+----------+--------------+------+-----------+

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

+----+------+-----------+-----------+----------+
| id | name | dayofweek | starttime | endtime  |
+----+------+-----------+-----------+----------+
|  1 | Phil |         1 | 09:00:00  | 17:00:00 |
|  2 | Lisa |         5 | 09:00:00  | 18:00:00 |
|  3 | Lisa |         3 | 09:00:00  | 17:00:00 |
|  4 | Bob  |         5 | 15:00:00  | 17:00:00 |
+----+------+-----------+-----------+----------+

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

+----+-------------+----------+
| id |    Name     | duration |
+----+-------------+----------+
|  1 | Treatment 1 | 01:30:00 |
|  2 | Treatment 2 | 01:00:00 |
+----+-------------+----------+

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

+----+-------+-----------+
| id | staff | treatment |
+----+-------+-----------+
|  1 |     1 |         1 |
|  2 |     2 |         1 |
+----+-------+-----------+

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

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

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

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

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

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

+----------+-------+----------------+--------------+
|   date   | Staff | Availablestart | Availableend |
+----------+-------+----------------+--------------+
| 24/08/15 | Lisa  | 12:00          | 13:30        |
| 24/08/15 | Lisa  | 13:30          | 15:00        |
| 24/08/15 | Lisa  | 15:00          | 16:30        |
| 24/08/15 | Lisa  | 16:30          | 18:00        |
| 24/08/15 | Bob   | 15:00          | 16:30        |
+----------+-------+----------------+--------------+

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

Я видел этот пост - Поиск свободных блоков времени в mysql и php ?

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

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

SELECT trainer, day, bookingdate, from_time, to_time, timeslot 
FROM
    (
    SELECT a.trainer
    , a.day
    , bookingdate
    , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,open_time )) as timeslot
    , IF(bookingdate=@prevdate,@prevend,open_time ) as from_time
    , start_time as to_time
    , @prevend := end_time as prevend
    , @prevdate := bookingdate as prevdate
    FROM bookingavailability a
        JOIN (SELECT @prevend:=null,@prevdate:=null) as init
        INNER JOIN bookingscalendar c 
            ON a.trainer = c.trainer
            AND WEEKDAY(c.bookingdate) = a.day

    UNION

      SELECT a.trainer
    , day
    , bookingdate
    , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) as timeslot
    , IFNULL(MAX(end_time),open_time) as from_time
    , close_time as to_time
    , null as prevend
    , null as prevdate
    FROM bookingavailability a
    LEFT JOIN bookingscalendar c 
        ON a.trainer = c.trainer
        AND WEEKDAY(c.bookingdate) = a.day
    GROUP BY a.trainer,day,bookingdate
    ) as gaps
WHERE timeslot > '00:00:00'
ORDER BY trainer, day, bookingdate, from_time;

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

+----+-------------+---------+------------+----------+-------------+------+-----------+
| id | bookingdate | trainer | start_time | end_time | customer_id | room | treatment |
+----+-------------+---------+------------+----------+-------------+------+-----------+
|  1 | 2015-08-24  |       2 | 15:00:00   | 16:00:00 | Mr Smith    |    1 |         1 |
|  2 | 2015-08-31  |       2 | 16:00:00   | 17:00:00 | Mr Jones    |    2 |         1 |

и

+-----------------+---------+-----+-----------+------------+-------------+
| availability_id | trainer | day | open_time | close_time | trainername |
+-----------------+---------+-----+-----------+------------+-------------+
|               4 |       1 |   2 | 09:00:00  | 17:00:00   | Lisa        |
|               6 |       1 |   4 | 09:00:00  | 17:00:00   | Lisa        |
|               7 |       1 |   5 | 09:00:00  | 17:00:00   | Lisa        |
+-----------------+---------+-----+-----------+------------+-------------+

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

+---------+-----+-------------+-----------+----------+----------+
| trainer | day | bookingdate | from_time | to_time  | timeslot |
+---------+-----+-------------+-----------+----------+----------+
|       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 |
+---------+-----+-------------+-----------+----------+----------+

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

Любые идеи ?