Appointment booking script for calendar

[guido78]

Thank you so much Barand.  Can the original query be modified to show timeslots even if no bookings have been made for a day ?  I've restricted the query to only show available time slots for a given date, as:

SELECT trainer, trainername, day, bookingdate, from_time, to_time, timeslot 
FROM
    (
    SELECT a.trainer, trainername
    , 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, trainername
    , 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' and bookingdate = '2015-06-03'
ORDER BY trainer, day, bookingdate, from_time;

So, if a customer is wishing to book an appointment for today, at the moment it will only produce a result if there is a valid entry in bookings availability table.  However if no bookings have been made for today then really there should be a time slot from the start_time to end_time of the bookings calendar entry.  Is this possible ?

[Barand]

The first half of the UNION finds (for each trainer)

• free time before first booking
• free time between bookings
• all trainers time if no bookings

The second part finds

• trainers' free time after last booking of the day.

SELECT 
    trainer
  , from_time
  , to_time
  , timeslot
FROM 
    (
    SELECT
      a.day
    , TIMEDIFF(IFNULL(start_time,close_time), IF(a.trainer=@prevt,@prevend,open_time )) as timeslot
    , CAST(IF(a.trainer=@prevt,@prevend,open_time ) as TIME) as from_time
    , IFNULL(start_time,close_time) as to_time
    , @prevend := end_time as prevend
    , @prevt := a.trainer as trainer
    FROM bookingavailability a
        JOIN (SELECT @prevend:=null,@prevt:=null) as init
        LEFT JOIN bookingscalendar c 
            ON a.trainer = c.trainer
            AND WEEKDAY(c.bookingdate) = a.day
            AND c.bookingdate = '2014-08-18'
    WHERE a.day = WEEKDAY('2014-08-18')
    ORDER BY a.trainer, c.start_time
    ) gaps
UNION
SELECT
    a.trainer
  , MAX(end_time) as from_time
  , a.close_time as to_time
  , TIMEDIFF(MAX(end_time),close_time) as timeslot  
FROM bookingavailability a
INNER JOIN (
    SELECT trainer
        , MAX(end_time) as end_time
    FROM bookingscalendar
    WHERE bookingdate = '2014-08-18'
    GROUP BY trainer
    ) eod
WHERE a.day = WEEKDAY('2014-08-18')
ORDER BY trainer,from_time;

[amee]

Hi.. can you please help me .. i am not able to do this

[Barand]

1 )  Not able to do what?

 

2 )  When you have a few more details to add, start you own topic and do not hijack someone else's.

[amee]

1 )  Not able to do what?

 

2 )  When you have a few more details to add, start you own topic and do not hijack someone else's.

 Hi,,,i am not able to do booking system .. can you please help me

[Barand]

... start you own topic and do not hijack someone else's.