Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2016-09-25
5
144 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 109

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 109

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mysql database, schema and table creation 13 57
SubQuery link 4 35
How is this connection happening? 3 20
How to check if a session is disconnected in php 9 31
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

791 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