Set order for Staff Picking Vacation

[mstoehr]

I have 3 staff members that need to pick vacation in a certain order.
Each have X weeks vacation and can pick off a calendar their choice 1-3 weeks  per round.
I'm trying to loop through the DB to find who is next to pick with weeks left

~~~~~~First round of picking~~~~~~
STAFF 1 - PICK ORDER 1 -  3 weeks available
STAFF 2 - PICK ORDER 2 -  5 weeks available
STAFF 3 - PICK ORDER 3 -  3 weeks available

Staff 1 takes 2 Weeks
Staff 2 takes 1 Weeks
Staff 3 takes 3 Weeks

~~~~~~Second round of picking~~~~~~
STAFF 1 - PICK ORDER 1 -  1 weeks available
STAFF 2 - PICK ORDER 2 -  4 weeks available
STAFF 3 - PICK ORDER 3 -  No weeks left

Staff 1 takes 1 Weeks
Staff 2 takes 3 Weeks
Staff 3 Skipped

~~~~~~Third round of picking~~~~~~
STAFF 1 - PICK ORDER 1 -  No weeks left
STAFF 2 - PICK ORDER 2 -  1 weeks available
STAFF 3 - PICK ORDER 3 -  No weeks left

Staff 1 Skipped
Staff 2 takes 1 Weeks
Staff 3 Skipped

~~~~~~~~~~~~
All staff 0 weeks left end

--calendar.php--
$year=2020;
$sql = "SELECT * FROM vac_admin WHERE pick_year='$year';
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
$row_admin = mysqli_fetch_assoc($result);
}
$current_pick_staff = $row_admin['current_pick_staff'];

$sql = "SELECT *  FROM vac_pick_order WHERE  pick_year='$year'  &&  pick_order = '$current_pick_staff'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
if($row['vac_c_counter'] < 0){
$emp_num = $row['emp_num'];
}ELSE{
??????????????????
goto next staff with weeks > 0
?????Somthing like
if ($current_pick_staff == 3){
	$current_pick_staff = 1;
}ELSE{
	$current_pick_staff++;
}
??????????????????  
}

~<FORM>~~~~~~~~~~~~~~~~~~~~~
Staff with $emp_num can now pick ~~~~~~ $_POST -> $date = XXXX-XX-XX; $num_weeks = X; $emp_num;
~</FORM>~~~~~~~~~~~~~~~~~~~~~

--process.php--
$year = 2020;
$date = $_POST['date'];
$num_weeks = $_POST['num_weeks'];
$emp_num = $_POST['emp_num'];

$sql = "INSERT INTO vac_picks (pick_year,emp_num,date) VALUES ($year,$emp_num,$date)";
$sql = "UPDATE vac_pick_order SET vac_c_counter=vac_c_counter - $num_weeks WHERE emp_num='$emp_num';
$sql = "UPDATE vac_admin SET pick_order=pick_order +1 WHERE pick_year='$year' ;

Then back to calendar.php until all weeks gone.

 

 

 

 

[requinix]

Does the order of staff change? If so then generate the list and store it somewhere for each period.

Store with the period the staff member who gets to make the next choice. It starts with the first person. When they make their choice, switch to the next person who has weeks left. Repeat until you get to the last person.

[mstoehr]

SORRY I made a BIG ERROR explaining what i need . In every round the order changes by 1 every time 

~~~~~~First round of picking~~~~~~
STAFF 1 - PICK ORDER 1 -  3 weeks available
STAFF 2 - PICK ORDER 2 -  5 weeks available
STAFF 3 - PICK ORDER 3 -  3 weeks available

Staff 1 takes 2 Weeks
Staff 2 takes 1 Weeks
Staff 3 takes 3 Weeks

~~~~~~Second round of picking~~~~~~
STAFF 2 - PICK ORDER 1 -  4 weeks available
STAFF 3 - PICK ORDER 2 -  No weeks left
STAFF 1 - PICK ORDER 3 -  1 weeks available

Staff 2 takes 3 Weeks
Staff 3 Skipped
Staff 1 takes 1 Weeks

~~~~~~Third round of picking~~~~~~

STAFF 3 - PICK ORDER 1 -  No weeks left
STAFF 1 - PICK ORDER 2 -  No weeks left
STAFF 2 - PICK ORDER 3 -  1 weeks available

Staff 3 Skipped
Staff 1 Skipped
Staff 1 takes 1 Weeks

~~~~~~~~~~~~
All staff 0 weeks left end

 

thanks in advance for any help

[requinix]

I kinda suspected it should have been along those lines...

If the order is simple like that - fixed sequence but shifts by one each round - then what I said before except you also store the round number. Given the round number and current staff member you can determine who the next is, and whether to advance to a new round.

[mstoehr]

Man i just can't wrap my head around this bit of code 😫😫 I have been trying all day still not the result i need.

[mstoehr]

This is my DB structure

~~~~~Table ~~~~vac_admin

Id    |   pick_year   |   current_pick_staff
-------------------------------------------------------------
1     |      2020        |               1

~~~~~Table ~~~~vac_pick_order

Id    |   pick_year   |   pick_order   |   vac_c_counter    |    emp_number
-------------------------------------------------------------------------------------------------------------
1     |      2020        |               1        |             3                 |        11111111
2     |      2020        |               2        |             5                 |         22222222
3     |      2020        |               3        |             3                 |         33333333

~~~~~Table ~~~~vac_picks~~~the final result of picks

Id    |   pick_year   |         date              |   emp_number
-----------------------------------------------------------------------------
1     |      2020        |    2020-05-15      |       11111111
2     |      2020        |    2020-02-20      |       22222222
3     |      2020        |    2020-12-25      |       33333333

 

Edited September 25, 2019 by mstoehr

[requinix]

vac_admin also needs to know the current round number. Let's pretend you call that "current_round_number".

You can determine which employee picks next by grabbing the vac_pick_order row with

pick_order = (current_pick_staff + current_round_number - 2) % (number of employees) + 1

So,
- The very first employee (current_pick_staff=1, current_round_number=1) is pick_order = (1 + 1 - 2) % 3 + 1 = 0%3 + 1 = 1
- The second employee (current_pick_staff=2, current_round_number=1) is pick_order = (2 + 1 - 2) % 3 + 1 = 1%3 + 1 = 2
- The third is pick_order = (3 + 1 - 2) % 3 + 1 = 2%3 + 1 = 3, and then you reset current_pick_staff=1 and increment current_round_number=2
The first round is 1, 2, 3.

- The fourth (current_pick_staff=1, current_round_number=2) is pick_order = (1 + 2 - 2) % 3 + 1 = 1%3 + 1 = 2
- The fifth is pick_order = (2 + 2 - 2) % 3 + 1 = 2%3 + 1 = 3
- The sixth is pick_order = (3 + 2 - 2) % 3 + 1 = 3%3 + 1 = 1
The second round is 2, 3, 1. And so on.

[mstoehr]

That's a great bit of code Thanks. But i still have a problem. As the loop continues staff run out of vacation weeks therefore they need to be skipped and the next person can pick in the round. How do i skip a staff member that has run out of days to pick?

The vac_pick_order -> vac_c_counter starts at the amount of vacation the staff is given and every week they pick is deducted from that count. When its 0 they should be skipped and the next person can pick.

$qq = ($row_admin['current_pick_staff'] + $row_admin['current_round_number'] - 2) % ($row_admin['total_staff']) + 1;
$sql = "SELECT * FROM vac_pick_order WHERE pick_year='$year' vac_c_counter>0 && pick_order='$qq'";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($result)){
if($row['vac_c_counter'] > 0){
$current_pick_emp_num = $row['emp_num'];
}ELSE{

GOTO NEXT STAFF??????

}
}

 

[requinix]

Use a loop that looks like this:

do {
	update the pick number, and if needed the round number

	fetch appropriate pick from table
	fetch information about the pick
} while (the selected employee does NOT have weeks available to use);

Important note: make sure that you have first verified there are any weeks available, for any employee. If there aren't then you can stop the process - and you definitely don't want to run the above code because it will never stop.
Technically you're still vulnerable to a race condition though.

The loop is to keep searching for an employee (which, according to the above sentence, you know there is at least one of) when the one you just looked at doesn't have any weeks to use. What's inside the loop should look something like what you already have...

I assume you're running this code when an employee has made their pick and you're setting up for the next person, right?

[mstoehr]

Thank you for your input.  Got it working now!!

[mstoehr]

$row_admin['total_staff'] = 5; //set in loop above
$current_pick_staff = 1; //changes on page load

AAAAWWWW:
$sql = "SELECT * FROM vac_pick_order WHERE pick_order ='$current_pick_staff'";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($result)){
if($row77['vac_c_counter'] > 0){                //<---- this is where the GOTO is needed if this goto the next staff member
$current_pick_emp_num = $row77['emp_num'];
}ELSE{
if ($current_pick_staff == $row_admin['total_staff']){
    $current_pick_staff = 1;
}ELSE{
    $current_pick_staff++;
}
$sqla = "UPDATE vac_admin SET  current_pick_staff='$current_pick_staff'";
if (mysqli_query($conn, $sqla)) {
    //echo "Record updated successfully a";
} else {
    echo "Error updating record: a " . mysqli_error($conn);
}
GOTO AAAAWWWW;
}

I think the GOTO is causing problems with my web host.It works fine on a local xampp server but not after uploading to Godaddy.  It seems to freeze the server. How can i loop back to the beginning changing "$current_pick_staff" up by one without GOTO. 

$current_pick_staff loops around 1->5 then back to 1  (1 2 3 4 5 1 2 3 4 5 and so on)