PHP/MySQL Appointment Calendar Question

[talknerdy2mee]

If this is not in the right place, feel free to move it, but since I'm not asking for help with actual code, this seemed like the best place.

 

I have a problem and I'm not sure how to approach it.  Basically, I am designing a web application suite for a charity that does a lot of Social Services work.  I want to include an appointment calendar in it, but I need to roll my own, because I haven't found anything out there that really fits my needs.

 

The way I have it set up is that I have 3 tables - apptTemplates, apptSetup, and apptDetails.  apptTemplates has the following fields:

startTime (the earliest time an appt can be set)

endTime (the latest appt time plus the appt interval)

apptInterval (the amount of time between appointments in minutes)

maxAppts (the maximum number of appointments at any given time)

 

apptSetup has the following fields:

date

templateID1 (the unique identifier of the template from apptTemplates)

templateID2

templateID3

 

There are multiple templates per date because the schedule can vary throughout the day (two caseworkers from 8 to 10, one caseworker from 10 to 2, two caseworkers from 2 to 5, etc.).

 

apptDetails has the following fields:

apptID

date

time

clientID

dateTimeBooked

 

What I want to do is display a list of the available times based on the templates, and list appointments or available time slots next to them.  For example, a basic template has startTime 0800 end time 1700, apptInterval 45, maxAppts 2.  We have one appointment booked for 8 am and 2 appointments booked for 9:30 am. I want to list:

 

8:00    Client A (Booked 2nd)

          Book This Appointment

 

8:45    Client B (Booked 1st)

          Client C (Booked 3rd

 

9:30    Book This Appointment

          Book This Appointment

 

etc.

 

My basic blueprint for this is:

-Select row for the appropriate date from apptSetup

-Select row for templateID1 from apptTemplates

-Select row(s) from apptDetails where date == date and time == start time ordered by dateTimeBooked

-if rowcount <= maxAppts, i=maxAppts-rowcount, list client(s), while i > 0 list Empty Appointment, i--

-else if rowcount > maxAppts, i=0, while i < maxAppts list clients i++

-select row(s) from apptDetails where date==date and time ==start time + apptInterval

etc.....

 

I'm running into a problem, though, because the template can be changed if something comes up - a caseworker is unable to come in, etc.

 

I need to be able to pull any rows for the correct date that WERE NOT previously listed and list them in a section labeled "Conflicts" so that someone can deal with them and get them rescheduled, but I'm stumped as to how to go about this.  Does anyone have any ideas on techniques I could use to do this?