Dear Oracle users and experts,
I'm helping out on creating an "employee planning tool" in APEX, but I can't seem to get a decent algorithm to make it happen. I was hoping one of you might be able to help me out here, since I'm only a "newbie" in Apex.
These are the specific requirements:
- For every day in a month, we need to have 2 employees (out of a pool of multiple people)
- Some people need to work around 7 days in a month, some around 12, etc...
- People can't work more than 2 or 4 days in a row
- Sometimes 2 days in a row are required (example: X-mas and the day before). The same 2 people need to work on both days.
- Absences (holiday, illness) need to be registered in a table and taken into account
- Preferences should also be taken into account, if possible ("I don't want to work on the 22nd because I have a family dinner", stuff like that). If possible, it should be taken into account, but we can't allow this to corrupt the planning.
- There are 2 categories of people. Categorie X should always work with someone from category Y, but category Y can also work with another category Y person. (category Y has more responsibilities, so at least one of those people should be present)
I will create tables for the people (and the number of days they need to work), the days, the absences, the preferences, etc..
However, I'm not sure how to make this work. I was thinking of "looping through" the days in a month, and then looking for random people in the persons table, and checking how many days are left in the month for them. But the numbers will not always match... Some people might need to work overtime (extra days) or sometimes less days. It can't always be the same person, so I was thinking of taking the people with the least overtime this year.
However, if they ever decide to "re-generate" the planning with the same parameters, the result should be the same, it can't be fully random. If something changes (holiday for a person, illness, ...), the planning can shift offcourse.
So, does anyone have an idea on what the best approach might be here? Can you help me out with creatign an algorithm for this? I'm going to use APEX, thus it can be in PL/SQL.
Thanks in advance.
Kind regards