views:

34

answers:

0

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:00

Appointments:
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.