I have an SQLite database that I use to track work requests. Some requests are time sensitive, i.e. they need to be completed within a certain number of days from the time they are approved. So I have an "Approved" and an "Implemented" column in the database to record the date I approved a request and the date it was implemented. All requests should be completed within 5 business days, so when I display the data on a webpage with PHP, I want to see the number of elapsed working days between the time I approved a request and the time it was implemented. To do so, I use some code provided by George John at Calculate business days with a slight modification. The code I use appears below:
function getWorkingDays($startDate, $endDate) { $begin = strtotime($startDate); $end = strtotime($endDate); if ($begin > $end) { return 0; } else { $no_days = 0; while ($begin <= $end) { $what_day = date("N", $begin); if (!in_array($what_day, [6,7]) ) // 6 and 7 are weekend $no_days++; $begin += 86400; // +1 day }; return $no_days - 1; } }
The $begin += 86400;
code adds one day to the value stored
in the begin
variable; 86,400 is the number of seconds in a day,
i.e., 86,400 seconds = 60 seconds/minute * 60 minutes/hour * 24 hours/day
= 1 day.
The slight modification I added to the code is to use
return $no_days - 1
at the end of the function, instead of
return $no_days
, since I don't want to include the day I
approved the request itself. E.g., if I approved a request on July 13, which
is a Thursday, and the request was implemented on July 18, which is a Tuesday,
I not only don't want to count July 15 and 16, which are Saturday and Sunday,
but I also don't want to count the approval date, Thursday, either, since
I often approve requests late in the evening. I just want to count the
number of working days after the approval date until the approval date.
If you wanted to include the start date in the count, you could drop the
minus one from return $no_days - 1
.
The code doesn't allow for holidays. Usually there won't be a holiday between the approval and implemenation dates and, if there is, I can mentally subtract that day or days, though I may add add code to subtract holidays later.
When I first included his code within my webpage, I saw the message
"strtotime(): It is not safe to
rely on the system's timezone settings", but I was able to resolve that
problem by modifying the /etc/php.ini
file on the
system (see the link for ways to resolve the problem).
The full code for the webpage which queries the database and displays the
working days between approval and implementation for each request is shown
below. The database file is SGRS.db
. It contains a
table
named Time_Sensitive
that holds information on the work requests
I track in the database.
<!DOCTYPE HTML> <html lang="en"> <head> <title>SGRS - Time Sensitive Requests</title> <meta charset="UTF-8"> <style type="text/css"> table { border: 1px outset black; } th { padding: 5px; border: solid 1px black; } td { padding: 5px; border: solid 1px black; } </style> </head> <body> <h1 style="text-align: center">SGRS - Time Sensitive Requests</h1> <?php function getWorkingDays($startDate, $endDate) { $begin = strtotime($startDate); $end = strtotime($endDate); if ($begin > $end) { return 0; } else { $no_days = 0; while ($begin <= $end) { $what_day = date("N", $begin); if (!in_array($what_day, [6,7]) ) // 6 and 7 are weekend $no_days++; $begin += 86400; // +1 day }; return $no_days - 1; } } $filename = "/Users/jasmith1/Documents/www/SGRS/SGRS.db"; $query_string = "SELECT * from Time_Sensitive ORDER BY Needed;"; $db_handle = new SQLite3($filename); echo "<table>\n"; echo "<tr><th>Request</th><th>Project</th><th>Requester</th>"; echo "<th>Submitted</th><th>Approved</th><th>Needed</th>"; echo "<th>Implemented</th><th>Business<br>Days</th><th>Implementer</th>"; echo "<th>Note</th></tr>\n"; $results = $db_handle->query($query_string); while ($row = $results->fetchArray()) { echo "<tr><td>" . $row['Request'] . "</td><td>" . $row['Project'] . "</td><td><a href=\"account.php?auid=" . $row['Requester_ID;'] . "\">" . $row['Requester_ID;'] . "</a></td><td>" . $row['Submitted'] . "</td><td>" . $row['Approved'] . "</td><td>" . $row['Needed'] . "</td><td>" . $row['Implemented'] . "</td><td>" . getWorkingDays($row['Approved'],$row['Implemented']) . "</td><td>" . $row['Implementer'] . "</td><td><pre style=\"white-space: pre-wrap\">" . $row['Note'] . "</pre></td></tr>\n"; } echo "</table>\n"; ?> </body> </html>
Related articles