Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 462
  • Last Modified:

PHP/MySQL Time slot availability check for staff id and all staff

Hello all,

I've been working on a personal project (including learning php/jquery), which includes writing my own appointment booking system, which due to the nature of the business, needs to be based on a pre-allocated time-slots table.

I've got working 80%, but the remaining 20% is eluding me - hence my trawling of EE and reading a number of suggestions and solutions, but none seem to cover exactly what I need - so any help is greatly appreciated.

247timeslots table - contains only one field;

bookingstart                        timestamp      

247bookings table

id                                    int(11)                  PRI            auto_increment
bookingref                        varchar(12)      
statuscode                        varchar(12)
customerid                        int(11)            
staffid                              int(11)            
serviceid                              varchar(10)      
appointmentstart                  datetime
appointmentend                  datetime
appointmentcreated            timestamp      


247staffschedules table ~ I've not included this in my scripts as yet.

id                                    int(11)                  PRI            auto_increment
dayofweek                        tinyint(4)      
starttime                        time
breakstarttime                  time
breakendtime                  time
endtime                              time

What I'm trying to write is a mysql script, that given;

DateStart      DateEnd      AppointmentDuration - e.g, multiple of minimum appoint time of 20 minutes, so 20, 40, 60, 80, 100, 120 mins etc

Produces a list of all dates, with available (A) and already booked (B) appointments - for individual staffid or all available staff.

Currently I've got 2 scripts, but I'm sure this could be achieved by one script, as mine has been created and grown thought trial and error.

Script 1 - Select available slots for given date range

SELECT date(bookingstart) AS 'dateslot', count(bookingstart) AS 'cnt'
FROM 247timeslots
WHERE date(bookingstart)
    BETWEEN '" . date('Y-m-d', $date_from) . "'
        AND '" . date('Y-m-d', $date_to) . "'
        AND NOT EXISTS 
    (SELECT *
    FROM 247bookings
    WHERE date(247bookings.appointmentstart) = date(bookingstart)
            AND ( time(247timeslots.bookingstart)
        BETWEEN time(247bookings.appointmentstart)
            AND time(247bookings.appointmentend) - interval 1 minute
            OR time(247bookings.appointmentstart)
        BETWEEN time(247timeslots.bookingstart)
            AND time(247timeslots.bookingstart) + interval " . $convmins . " minute)
            AND 247bookings.statuscode NOT LIKE '%CANCELLED%' )
GROUP BY  date(bookingstart)
ORDER BY  1

Open in new window


Script 2 - Select available slots for single date

SELECT 'A' AS 'AB', bookingstart
FROM 247timeslots
WHERE date(bookingstart) = '" . $scandate2 . "'
        AND NOT EXISTS 
    (SELECT *
    FROM 247bookings
    WHERE date(247bookings.appointmentstart) = '" . $scandate2 . "'
            AND ( time(247timeslots.bookingstart)
        BETWEEN time(247bookings.appointmentstart)
            AND time(247bookings.appointmentend) - interval 1 minute
            OR time(247bookings.appointmentstart)
        BETWEEN time(247timeslots.bookingstart)
            AND time(247timeslots.bookingstart) + interval " . $convmins . " minute)
            AND 247bookings.statuscode NOT LIKE '%CANCELLED%' )
UNION
SELECT 'B' AS 'AB', bookingstart
FROM 247timeslots
WHERE date(bookingstart) = '" . $scandate2 . "'
        AND EXISTS 
    (SELECT *
    FROM 247bookings
    WHERE date(247bookings.appointmentstart) = '" . $scandate2 . "'
            AND ( time(247timeslots.bookingstart)
        BETWEEN time(247bookings.appointmentstart)
            AND time(247bookings.appointmentend) - interval 1 minute
            OR time(247bookings.appointmentstart)
        BETWEEN time(247timeslots.bookingstart)
            AND time(247timeslots.bookingstart) + interval " . $convmins . " minute)
            AND 247bookings.statuscode NOT LIKE '%CANCELLED%')
ORDER BY  2

Open in new window



Many thanks

Regards

Jamie
0
Jamie
Asked:
Jamie
  • 3
  • 2
1 Solution
 
Ray PaseurCommented:
I got a neglected question alert on this one.  It appears that you're looking at a design pattern similar to a resource scheduling calendar.  These are described in the EE articles on date/time processing.

https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html

https://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL-Procedural-Version.html
1
 
JamieAuthor Commented:
Hi Ray,

Many thanks for your reply and pointing me to your articles on resource scheduling calendar, very useful and interesting, especially as it offers the greatest flexibility as it is not restricted to pre allocated time-slots, I can see the advantages to this.

If I did want to work with pre-allocated time-slots, how would this work - should I have a single resource calendar for all resources - or a separate calendar for each resource?
 
Many thanks

Regards

Jamie
0
 
Ray PaseurCommented:
You could have a single calendar.  Just identify the resources differently.  If you were scheduling rooms in a hotel, each room would be a different resource, but they can all be part of the same calendar table.

As far as timeslots go, the minimum timeslot (practically speaking) is one second.  If you want greater elapsed time in each timeslot, you can extend the minimum amount of time, or you can designate the timeslots start and end times, if they are not contiguous and of the same duration.
0
 
JamieAuthor Commented:
Hi Ray,

I was hoping a single calendar was ok to use as this is my preferred option as is scheduled time-slots.

Given this, I'm still struggling with my original question, especially in that I've not been able to include 247staffschedules into my scripts.

Regards

Jamie
0
 
JamieAuthor Commented:
Very useful, easy to understand and much appreciated articles on resource scheduling calendar. The excellent worked through examples are based on no pre allocated time-slots, which offers the greatest flexibility. Unfortunately, the articles does not cover my requirement of pre-allocated time-slots and time availability of individual resources - but I have learned a great deal and has given me ideas for the future.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now