I am querying a SQL database to return open work order operations.
My query is producing the Work Order number, operation status and due date.
I am trying to figure out how to iterate through the array that is returned and:
- Gather the sum of operations due within a week
- Gather the sum of operations due in the second week
- Continue to do this until I have made it through all of the entries, ongoing for as many weeks as necessary.
My SQL query looks something like:
SELECT * FROM OPERATION WHERE RESOURCE_ID = '280LASERS' ORDER BY DUE_DATE;
It will return something like:
W/O # | Setup Hours | Run Hours | Due Date W159769 | 0.5 | 15.0 | 03/01/2020 W159770 | 1.5 | 9.0 | 04/01/2020 W159771 | 0.75 | 81.0 | 05/01/2020
Either way, what I am trying to accomplish is, query the database, step through my result and get the sum foreach week.
While NOW+7days <= DUE_DATE; While NOW+14days <= DUE_DATE…
Week One = 15.5 Hours; Week Two = 10.5 Hours; Week Three = 81.75 Hours
EDIT: I apologize for my mess of a question, this is one of the more intense tasks I have tried to accomplish with SQL and PHP.
We are trying to get a better handle on our capacity, and reporting on our capacity.
I am hoping to be able to run a query that pulls all of the ‘280LASERS’ Operations and have some sort of root value (Like todays Date) to compare the DUE_DATE against.
My plan is to sort by DUE_DATE and get the SUM(SETUP_HRS + RUN_HRS) until DUE_DATE is greater than (TODAY() + 7) then, get the SUM(SETUP_HRS + RUN_HRS) until DUE_DATE is greater than (TODAY() + 14) then …
I can’t achieve this with static variables because the number of weeks can go from 6 weeks out, to more than 30 weeks out, simply depending on the DUE_DATE of the furthest out order.
I am so close I can taste it, I would really like to share my code, and the output… but feel I have blown this page up and it is a hot mess. Would it be acceptable for me to delete everything above this and reshare my code as it is, as well as the output I am getting.
Advertisement
Answer
so I’m back, I’ll add a better commented code, here: can’t edit the old answer, since I deleted my account and forgot to cancel 😐
Anyways, you asked how to manipulate the data. It’s a simple array and all the inner arrays are sums from the start of the week to the end. Now, you could store them with different keys, I just used the default assigning because of simplicity.
$results=array(
array( 'due_date'=>'12/02/2020', 'run_hours'=>12.4, 'setup_hours'=>2.4, ), // 2020-02-12 00:00:00
array( 'due_date'=>'15/02/2020', 'run_hours'=>10.4, 'setup_hours'=>1.4, ), // 2020-02-15 00:00:00
array( 'due_date'=>'18/02/2020', 'run_hours'=>8.4, 'setup_hours'=>3.4, ), // 2020-02-18 00:00:00
array( 'due_date'=>'20/02/2020', 'run_hours'=>2.4, 'setup_hours'=>1.4, ), // 2020-02-20 00:00:00
array( 'due_date'=>'21/02/2020', 'run_hours'=>9.4, 'setup_hours'=>1.4, ), // 2020-02-21 00:00:00
array( 'due_date'=>'24/02/2020', 'run_hours'=>12.4, 'setup_hours'=>1.4, ), // 2020-02-24 00:00:00
array( 'due_date'=>'26/02/2020', 'run_hours'=>11.3, 'setup_hours'=>1.4, ), // 2020-02-26 00:00:00
array( 'due_date'=>'29/02/2020', 'run_hours'=>4.4, 'setup_hours'=>2.4, ), // 2020-02-29 00:00:00
array( 'due_date'=>'02/03/2020', 'run_hours'=>5.7, 'setup_hours'=>4, ), // 2020-03-02 00:00:00
array( 'due_date'=>'04/03/2020', 'run_hours'=>11.5, 'setup_hours'=>3.4, ), // 2020-03-04 00:00:00
array( 'due_date'=>'06/03/2020', 'run_hours'=>7.3, 'setup_hours'=>1.4, ), // 2020-03-06 00:00:00
array( 'due_date'=>'08/03/2020', 'run_hours'=>9.6, 'setup_hours'=>1.4, ), // 2020-03-08 00:00:00
array( 'due_date'=>'12/03/2020', 'run_hours'=>14.7, 'setup_hours'=>1.4, ), // 2020-03-12 00:00:00
array( 'due_date'=>'15/03/2020', 'run_hours'=>12.5, 'setup_hours'=>1.4, ), // 2020-03-15 00:00:00
array( 'due_date'=>'19/03/2020', 'run_hours'=>4.4, 'setup_hours'=>1.4, ), // 2020-03-19 00:00:00
array( 'due_date'=>'21/03/2020', 'run_hours'=>5.6, 'setup_hours'=>4, ), // 2020-03-21 00:00:00
array( 'due_date'=>'24/03/2020', 'run_hours'=>11.4, 'setup_hours'=>1.4, ), // 2020-03-24 00:00:00
array( 'due_date'=>'29/03/2020', 'run_hours'=>7.4, 'setup_hours'=>1.4, ), // 2020-03-29 00:00:00
array( 'due_date'=>'01/04/2020', 'run_hours'=>9.4, 'setup_hours'=>1.4, ), // 2020-04-01 00:00:00
// some far off weeks
array( 'due_date'=>'18/06/2020', 'run_hours'=>9.4, 'setup_hours'=>1.4, ),
array( 'due_date'=>'21/06/2020', 'run_hours'=>9.4, 'setup_hours'=>1.4, ),
array( 'due_date'=>'09/07/2020', 'run_hours'=>9.4, 'setup_hours'=>1.4, ),
array( 'due_date'=>'12/08/2020', 'run_hours'=>9.4, 'setup_hours'=>1.4, ),
);
$time=strtotime(date('Y-m-d')); // get time in same
// wrapping time in strtotime and date trims the seconds to the desired format
$one_week=60*60*24*7;
$sums=array();
foreach($results as $row){
/* php 5.3+ this block of code gets the time of the date, this conversions are made in case a custom non standard date format is made, alternatively one can use strtotime with the correct date format
$date = DateTime::createFromFormat('d/m/Y', $row['due_date']);//use your format and values
if(!$date){
echo 'Not a valid format';
break;
}
$entry_time = strtotime(date('Y-m-d',$date->getTimestamp()));
// if your date format doesnt have hours minutes and seconds then timestamp will add the current h,min,s,
// this may not be desired, so this wrapping it in strtotime and date trims the values
*/
// WARNING: If the format is right weeks will be way off
$entry_time = strtotime($row['due_date']); // if due_date is a valid format, see PHP docs for more information
if (!$entry_time) {
echo "Not a valid date format";
break;
}
$entry_work_hours=$row['run_hours']+$row['setup_hours'];
// if the entry time is by some reason smaller then the current time save it to a special past_due container
if ($entry_time < $time) {
// if a past_due container exists add the sum, otherwise create a past_due container
if (isset($sums['past_due'])) {
$sums['past_due']['sum']+=$entry_work_hours;
} else {
$sums['past_due']= array(
'sum' => $entry_work_hours,
'start' => $row['due_date'], // the earliest event
'end' => date('d/m/Y',$time), // current time, if $entry_time is bigger or equal we're talking about entries that are yet to happen
);
}
} else if ( $entry_time >= $time ){
// getting the future_dues array, every object holds an array/map, that holds the sum, the start of the week and when the week ends
// endings are exclusive ie. if an entry_data falls on the end date it goes to the start of the next container
if (isset($sums['future_dues'])) {
$future_dues=$sums['future_dues'];
} else {
$future_dues = array(
array(
'sum' => 0,
'start' => $time,
'end' => $time+$one_week
)
);
}
// get the last week container, and save the key so we can reassign it back to the $sums array on the right spot
$last_index = count($future_dues)-1;
$future_due = $future_dues[$last_index];
// manipulate the week data
// if the entry time is smaller then the current end of the week add to the sum, otherwise add a new week interval container
if ($entry_time < $future_due['end']) {
$future_due['sum']+=$entry_work_hours;
// reassign week container
$future_dues[$last_index]=$future_due;
} else {
$last_week_end = $future_due['end'];
$new_end = $last_week_end + $one_week;
//do a while loop to get the next week end in which the work is done
while ($new_end < $entry_time) {
// skip this part if empty weeks are not desired
$future_dues[] = array(
'sum' => 0,
'start' => $last_week_end,
'end' => $new_end
);
$last_week_end = $new_end;
$new_end = $new_end + $one_week;
// echo "$new_end < $entry_time".'<br>';
}
// add a new week container, the start of the week is the end of the previous one and the end is 7 days from that
$future_dues[]=array(
'sum' => $entry_work_hours,
'start' => $last_week_end,
'end' => $new_end
);
}
// reassign the whole week containers container to the array
$sums['future_dues']=$future_dues;
}
}
// convert time back to dates
foreach ($sums['future_dues'] as $key => &$due) {
$due['start']=date('d/m/Y',$due['start']);
$due['end']=date('d/m/Y',$due['end']);
}
// use $sums to display the values you need, use:
// echo "<pre>";
// print_r($sums);
// echo "</pre>";
// to better understand how data is stored
echo "<pre>"; // use pre tags to have a nice inline values, this can be rewriten into a table
$past_due=$sums['past_due'];
//past due is a single container
$time_prefix="Time: ";
$working_hours_prefix="Working hours: ";
$time = $time_prefix.$sums['past_due']['start']." - ".$sums['past_due']['end'];
echo $time."<br>";
echo $working_hours_prefix.str_pad($sums['past_due']['sum'],abs(strlen($time)-strlen($working_hours_prefix)),' ',STR_PAD_LEFT);
// make it inline with the time
echo "<br><br>";
$due_dates=$sums['future_dues'];
foreach($due_dates as $week_container){
$time = $time_prefix.$week_container['start']." - ".$week_container['end'];
echo $time."<br>";
echo $working_hours_prefix.str_pad($week_container['sum'],abs(strlen($time)-strlen($working_hours_prefix)),' ',STR_PAD_LEFT);
echo "<br><br>";
//echo $week_container['sum']; /// if you want to show the sum
//echo $week_container['start']; /// if you want to show the start
//echo $week_container['end']; /// if you want to show the end
}
echo "</pre>";
// above is a bit abstracted but it esencially does this
echo "<br><br>";
echo "<br><br>";
$past_due=$sums['past_due'];
$past_start = $sums['past_due']['start'];
$past_end = $sums['past_due']['end'];
$past_sum = $sums['past_due']['sum'];
echo "Time: $past_start - $past_end<br>";
echo "Working hours: $past_sum"; // previous case adds breaks to be inline
echo "<br><br>";
$due_dates=$sums['future_dues'];
foreach($due_dates as $week_container){
$week_start = $week_container['start'];
$week_end = $week_container['end'];
$week_sum = $week_container['sum'];
echo "Time: $week_start - $week_end<br>";
echo "Working hours: $week_sum"; // previous case adds breaks to be inline
echo "<br><br>";
}
Edit: A new while loop was added to account for empty weeks. Note d/m/Y is not strtotime recognised format and it will be read as m/d/Y. To convert it refer to this question.
Edit-2: To answer your comment. Ok so the thing about the spans is that I made them so that if the span went from 2020-01-01 to 2020-01-08 and the second one from 2020-01-08 to 2020-01-15 where should the working hours of 2020-01-08 go to week 1 or week 2? When you corrected $entry_time < $future_due[‘end’] to $entry_time <= $future_due[‘end’] this means that the count is added to week 1, while the original solution would have added it to week 2 as the starting date.
You can try and add 8 days and then subtract one if you wanted the containers to span between 2020-01-01 and 2020-01-08 and 2020-01-09 and 2020-01-16 and have both endings be inclusive. Now, I’m not going to write this part since it really depends on how YOU want define your endings.
And your question if you can change the time to something else then the current time? Sure, just change this line.
$time=strtotime(date('Y-m-d'));
//to
$time=__TIME__YOU_WANT_IN_SECONDS__;
//or
$time = strtotime(__THE_DATE_YOU_WANT__); // eg. 01/01/2020
// now this is the time to compare all other dates to