Jump to content

Set order for Staff Picking Vacation


mstoehr

Recommended Posts

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.

 

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by mstoehr
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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??????

}
}

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

$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)

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.