Solved

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

Posted on 2016-09-25
5
77 Views
Last Modified: 2016-10-05
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
Comment
Question by:Jamie
  • 3
  • 2
5 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 41825742
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
 

Author Comment

by:Jamie
ID: 41826720
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41826892
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
 

Author Comment

by:Jamie
ID: 41827466
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
 

Author Closing Comment

by:Jamie
ID: 41831233
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now