Allocating to alternate entires

Calahan

I am totally at a loss as to how I'm going to do this. Guess that's why I put it off until the end....
Here's a basic outline of my problem. I won't include all of the code as it's far too long.
I have 5 tables:
employees
eng_job_cat
engineers
job_cat
job_details

Each engineer can be assigned to any number of job categories. This assignment goes in the eng_job_cat table. Each Job Category has its own ID number as does each engineer. The eng_job_cat table is updated to reflect the assigned Job Categories. For example. Engineer Joe Bloggs (ID 1) is assigned to Printers (ID 1) and Networks (ID 2) and Engineer John Doe is assigned to just Networking (ID 2). In this example, it would look like this:
engineer-----job_cat
----1-----------1
----1-----------2
----2-----------2
A user logs a job. It gets entered into the job_details table but before it does it needs to be assigned to an engineer. It works fine if there is only 1 engineer assigned to each job category but when there is more than one in the case of the Neworks category above, things get tricky. I've approached it from the angle of:
Find the number of engineers assigned to the selected job category and select that number of entries from the job details table in the selected Job Category and in decending order. Then try to assign that result to an array and extract the last name in the array. Now assign that job to that engineer. This was an attempt to get a sort of round robin thing going on so that the jobs were shared out equally. This was fraught with problems. Even if I had gotten it to work (which I didn't), the whole thing would have failed had a new engineer been added.
So my question here is this: Is there a simple way to get some sort of auto allocation thing going here so that if a job gets logged in the Networks category, it gets assigned to the first engineer that is responsible for that particular job category and if another job is logged in the same category, it checks and realises that it should assign it to the next engineer?

nemonoman

You're looking, it seems from your desciption for a decision making tree, right?

What factor(s) determine(s) who gets a job, right?

Sounds like:

Determine the job type,

Find all guys who can do that job type.

Find the guy with the fewest number of active jobs.

If all have an equal number of jobs, find the guy whose previous assignment is older than everybody else's previous assignment.

Something like that??

Calahan

Close.

Determine the job type,

Find all guys who can do that job type.

Check to see who was the last person to do a job assigned to the selected category and then assign it to the next engineer available in the engineers table. Obviously it needs to go deeper than that as the more engineers there are assigned to one job category, the furthur back it has to go to keep it balanced. For example, if you had 6 engineers in the engineers table that could do the job that was being logged, you would check the job_details table to see who were the last 6 engineers assigned a job in the Networking category and take the top one and assign the job to him as thats the earliest engineer that was assigned a job in this category. That way it all roates to balance out the jobs. As more engineers are added and possibly assigned to the same job category, it needs to be able to take tha into consideration.

Hope I'm making myself clear, if not, let me know and I'll try and make it clearer.
🙂

nemonoman

If you make it any clearer, you can start to code it...🙂