OK so I may not explain this well, apologies in advance.
The query however is not a coding one, Im happy to sort that out but more a pick of your brains on how you would go about doing this logically.
Im working on a booking system for a group of photographers, each photographer works varying shifts (each day can be different) so I have a table of "availability" which contains the following:
Photographer Id
Date
Start Time
End Time
straight forward, so Mary may have the following entry
56, 2015-10-28, 09:00, 16:00
showing shes working from 9 til 4 on the 28th
Im outputting these on a bar style page for each day with the 24 hours at the top and I create a div from midnight until the start time (each hour is 45pixels wide + 1 pixel border) so it calculates the width as 9 x 46 and creates a simple grey div to denote time before work.
Then theres a 2nd table of bookings which shows the jobs the clients are booked on, this contains
Photographer_id
Date
Start Time
Duration
So after creating the 1st div, I pull all the bookings for that client in the day and create divs to show they're busy. Same logic, calculate the width of the div based on the length of the job (1 hour = 45pixels), calculate the time between the last job (or them starting work) and set the left margin to that amount.
eg Mary has a 1 hour job at 10am would create a div of 46 px wide and set its left margin to 46px (10am - 9am = 1hour).
I do this for all the jobs and then finish off with another grey div to show theyre not working using the same logic (24:00 - 16:00 = 8hours so set the width to be 8x46).
So my basic logic is:
For each record in availability table for requested date
Create Pre start div
Cycle through any bookings creating booking divs
Create Post end div
Loop
That all works great however......................
they have a new contract that means they need to provide cover 24 hours a day so now a person could be working say 8pm until 4am and their availability table would show:
56, 2015-10-28, 20:00, 04:00
which wouldnt make sense so instead Im creating two records.
56, 2015-10-28, 20:00, 23:59
56, 2015-10-29, 00:00. 04:00
this however means that Mary can now have two records for the 29th, the one above and then the next shift:
56, 2015-10-29, 20:00, 23:59
Now no matter how many times Ive ran this through in my head I cant come up with how best to change the display page to incorporate this logic.
The only thing I can see to do would be to remove the availability table all together and create records showing times they arent available ie for Marys 9 to 4 shift, I would add two records to the bookings table (with a new indicator to denote OFF) of
56, 2015-10-28, 00:00, 08:59
56, 2015-10-28, 16:00, 23:59
Is this the best way forward or is there some easier way you can see to incorporate multiple shifts in a day?
Any help would be appreciated.