I am trying to implement an Oracle pl/sql script to batch insert appointments into a database.
The application is for managing appointments for operators/offices as follow:
There are X offices, each having Y number of operators. (Ex: Office #1 has 4 Operators, #2 has 6, etc)
Each office has difference opening hours, defined in a table (Office #1 from 8:30-13:00, 15:30-18:00).
Currently there is a simple management interface to assign single appointments manually, (ex: Appointment in office 1, operator 2, from 10:00-11:00), there are checks assure there are no intersections.
What i need to do now is a batch Pl/Sql (or even java) that can do a batch assignment of appointments imported from a table with [appointment, office, duration] that will automatically fill the schedule of each operator, taking in consideration all the inputs in a way that:
appointments are within the opening hours, for ex an appointment has a duration of 2 hours cannot be started at 17:00 if the office closes at 18:00, but will automatically goes to the next day.
appointments are distributed for all the operators in that office
Solution: What i need is some kind of a function that takes in input the [office ID] and [a duration], and gives in output an [operator] and [the first empty slot] large enough for the duration. And this is my problem, I can't come with such a function.
PS. I don't need the optimal solution, just a working one that can be implemented very quickly.
Update:
Tables are as follows:
Office Hours [Week Day is an integer const where 0 is monday, etc]:
OFFICE_ID, WEEK_DAY, OPEN_TIME, CLOSE_TIME
1 , 0 , 08:30 , 13:00
1 , 0 , 14:00 , 18:00Appointments:
ID, OFFICE, OPERATOR, START_DATE, END_DATE, [OTHER__FIELDS as client id, title, message]Appointment Import table:
CLIENT_ID, OFFICE, DURATION
I was thinking of taking a copy of the office working hours table and adding to it full dates, then subtracting all current appointments by modifying the lines:
OfficeID, Operator Id, START DATE, END DATE
1, 10, 19/10/2010 08:30, 19/10/2010 13:00
1, 10, 19/10/2010 14:00, 19/10/2010 18:00
and loop on the actual appointments table, then subtract, for example of there is an appointment for office 1, Operator 10, from 10:30-11:00, the table will become:
OfficeID, Operator Id, START DATE, END DATE
1, 10, 19/10/2010 08:30, 19/10/2010 10:30
1, 10, 19/10/2010 11:00, 19/10/2010 13:00
1, 10, 19/10/2010 14:00, 19/10/2010 18:00
But this need to do a cartisian multiplication of all offices and operators, which might cost extra complexity.