I'm developing an online booking system for a client where I need to find available appointment times that their customers can choose from to book their next appointment.
I'll try to explain the current database setup:
Table name: appointments - these are appointments that are currently booked by customers.
+----+---------+------------+-----------+----------+--------------+------+-----------+ | 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 | +----+---------+------------+-----------+----------+--------------+------+-----------+
Table name: staff - a list of staff, the day of the week they work and the start and end times of their shifts on the relevant day.
+----+------+-----------+-----------+----------+ | 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 | +----+------+-----------+-----------+----------+
Table name: treatments - a list of the treatments available
+----+-------------+----------+ | id | Name | duration | +----+-------------+----------+ | 1 | Treatment 1 | 01:30:00 | | 2 | Treatment 2 | 01:00:00 | +----+-------------+----------+
Table name: stafftreatments - a lookup table to identify what treatments are carried out by what staff.
+----+-------+-----------+ | id | staff | treatment | +----+-------+-----------+ | 1 | 1 | 1 | | 2 | 2 | 1 | +----+-------+-----------+
So, we can assume that Treatment 1 is carried out by staff member Lisa but Lisa only works on a Wednesday between 9am and 5pm and on a Friday between 9am and 6pm. Phil can also perform Treatment 1 but only works on a Monday between 9am and 5pm.
In my application I am asking the user what treatment they are looking to book. For example, Treatment 1 which last for 90 mins. The customer will then select a date that they are looking to book their appointment on. So at this stage we know the length of time of the required appointment and the date, subsequently the day of week.
I am going to take the selected date and loop through each day for say the next 5 days
I am stuck trying to develop a query that will allow me to show all available time slots that a customer can choose from based around the appointments table that contains pre booked appointments.
Assume the customer wants to book treatment 1 on the 24th August. We know the day of the week is a Friday so would be 5, we know Lisa works on a Friday between 9am and 6pm but she already has appointment between 9am and 12pm.
Because the treatment is 90 mins I need to end up with data such as:
+----------+-------+----------------+--------------+ | 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 | +----------+-------+----------------+--------------+
However, the next problem is that there are only 4 rooms available so no more than 4 treatments can be booked at the same time.
I saw this post - Finding free blocks of time in mysql and php?
but my retirement is slightly more complicated and I'm not sure how to construct the query. Any help would be very much appreciated.
Have made a few changes to the structure and my query is as follows:
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;
with the following data:
+----+-------------+---------+------------+----------+-------------+------+-----------+ | 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 |
and
+-----------------+---------+-----+-----------+------------+-------------+ | 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 | +-----------------+---------+-----+-----------+------------+-------------+
The data being returned is:
+---------+-----+-------------+-----------+----------+----------+ | 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 | +---------+-----+-------------+-----------+----------+----------+
but is not showing the bookingdate, just Null values.
Any ideas ?
