So, in our company we have branches. Each branch has been given a set of tasks they must complete on a daily/weekly/monthly basis.
I've written a script that summarizes which tasks have been completed for every single branch for the current month. Now, this can take a long time depending on how much data there is. (Usually increases as the month goes on)
At the start of the month, everything works fine. Towards the middle/end of the month is when the problems start happening (usually because there's more data to check, due to there being more days).
This is the code that calls the function to summarize everything (which is also the page that throws the 500 error):
<?php
ini_set('max_execution_time', 3600);
require_once('../../Connections/newConn.php');
require_once('../../Library/new/branchTaskSummary.php');
header('Content-Type: application/json');
$summary = array();
if(isset($_GET['month']) && isset($_GET['year']) && isset($_GET['update'])) {
global $conn;
$update = $_GET['update'] == 1;
$month = $_GET['month'];
$year = $_GET['year'];
if(!$update) {
$query = $conn->prepare("SELECT data_json, DATE_FORMAT(last_updated, '%d/%m/%Y %H:%i') AS last_updated FROM branchtasksummarydata WHERE month = ? AND year = ?");
$query->bind_param('ii', $month, $year);
$query->execute();
$result = $query->get_result();
if($result->num_rows > 0) {
$summary = $result->fetch_assoc();
} else {
$summary = summariseEverything($month, $year);
}
} else {
$summary = summariseEverything($month, $year);
}
}
echo json_encode($summary);
?>
It calls a function called summarizeEverything which is where all the magic happens.
Here is the code for that function:
function summariseEverything($month, $year) {
global $conn;
$now = new DateTime();
$data = array();
$daysInMonth = cal_days_in_month(CAL_GREGORIAN, $month, $year);
$start = sprintf("%s-%s-%s", $year, $month, "01");
$end = sprintf("%s-%s-%s", $year, $month, $daysInMonth);
//Get all branches
$branches = getBranches();
//Get all tasks
$dailyTasks = getAllTasks(1, $start, $end);
$weeklyTasks = getAllTasks(2, $start, $end);
$monthlyTasks = getAllTasks(3, $start, $end);
//Get dates
$dailyDates = Calendar::getDaysBetween($start, $end);
$weeklyDates = Calendar::getWeeksBetween($start, $end);
$monthlyDates = Calendar::getMonthsBetween($start, $end);
foreach($branches as $branch) {
$instance = array("id" => $branch["id"], "name" => $branch["name"], "area" => $branch["area"], "finance_area" => $branch["finance_area"], "company" => $branch["company"], "icb" => $branch["icb"], "tasks" => array());
$instance["tasks"]["completed"] = 0;
$instance["tasks"]["daily"]["completed"] = 0;
$instance["tasks"]["weekly"]["completed"] = 0;
$instance["tasks"]["monthly"]["completed"] = 0;
//Do daily tasks.
foreach($dailyTasks as $task) {
foreach($dailyDates as $date) {
$dateObj = new DateTime($date);
if($dateObj > $now) {
break;
}
$isWorkingDay = true;
Calendar::isBankHoliday($dateObj->format('Y-m-d'), &$Colour, &$Msg, &$isWorkingDay);
Calendar::isWeekend($dateObj->format('Y-m-d'), &$Colour, &$Msg, &$isWorkingDay);
$dayName = $dateObj->format("l");
$isOpen = branchIsOpen($branch, $dayName);
if($isWorkingDay && $isOpen) {
$instance["tasks"]["total"] += 1;
$instance["tasks"]["daily"]["total"] += 1;
$instance["tasks"]["daily"][$task["department"]]["total"] += 1;
$instance["tasks"]["daily"][$task["department"]][$task["id"]]["total"] += 1;
$completed = taskCompleted($branch["id"], $task["id"], $date." 00:00:00", $date." 23:59:59");
if($completed) {
$instance["tasks"]["completed"] += 1;
$instance["tasks"]["daily"]["completed"] += 1;
$instance["tasks"]["daily"][$task["department"]]["completed"] += 1;
$instance["tasks"]["daily"][$task["department"]][$task["id"]]["completed"] += 1;
}
}
}
if(!$instance["tasks"]["daily"][$task["department"]][$task["id"]]["completed"]) {
$instance["tasks"]["daily"][$task["department"]][$task["id"]]["completed"] = 0;
}
if(!$instance["tasks"]["daily"][$task["department"]]["completed"]) {
$instance["tasks"]["daily"][$task["department"]]["completed"] = 0;
}
}
//Do weekly tasks.
foreach($weeklyTasks as $task) {
foreach($weeklyDates as $date) {
$instance["tasks"]["total"] += 1;
$instance["tasks"]["weekly"]["total"] += 1;
$instance["tasks"]["weekly"][$task["department"]]["total"] += 1;
$instance["tasks"]["weekly"][$task["department"]][$task["id"]]["total"] += 1;
$completed = taskCompleted($branch["id"], $task["id"], $date["start"], $date["end"]);
if($completed) {
$instance["tasks"]["completed"] += 1;
$instance["tasks"]["weekly"]["completed"] += 1;
$instance["tasks"]["weekly"][$task["department"]]["completed"] += 1;
$instance["tasks"]["weekly"][$task["department"]][$task["id"]]["completed"] += 1;
}
}
if(!$instance["tasks"]["weekly"][$task["department"]][$task["id"]]["completed"]) {
$instance["tasks"]["weekly"][$task["department"]][$task["id"]]["completed"] = 0;
}
if(!$instance["tasks"]["weekly"][$task["department"]]["completed"]) {
$instance["tasks"]["weekly"][$task["department"]]["completed"] = 0;
}
}
//Do monthly tasks.
foreach($monthlyTasks as $task) {
foreach($monthlyDates as $date) {
$instance["tasks"]["total"] += 1;
$instance["tasks"]["monthly"]["total"] += 1;
$instance["tasks"]["monthly"][$task["department"]]["total"] += 1;
$instance["tasks"]["monthly"][$task["department"]][$task["id"]]["total"] += 1;
$completed = taskCompleted($branch["id"], $task["id"], $date["start"], $date["end"]);
if($completed) {
$instance["tasks"]["completed"] += 1;
$instance["tasks"]["monthly"]["completed"] += 1;
$instance["tasks"]["monthly"][$task["department"]]["completed"] += 1;
$instance["tasks"]["monthly"][$task["department"]][$task["id"]]["completed"] += 1;
}
}
if(!$instance["tasks"]["monthly"][$task["department"]][$task["id"]]["completed"]) {
$instance["tasks"]["monthly"][$task["department"]][$task["id"]]["completed"] = 0;
}
if(!$instance["tasks"]["monthly"][$task["department"]]["completed"]) {
$instance["tasks"]["monthly"][$task["department"]]["completed"] = 0;
}
}
$data["branches"][] = $instance;
}
$json = json_encode($data);
//Check if data exists for this month & year before inserting into DB.
$query = $conn->prepare("SELECT summaryPK FROM branchtasksummarydata WHERE month = ? AND year = ?");
$query->bind_param('ii', $month, $year);
$query->execute();
$result = $query->get_result();
$exists = $result->num_rows > 0;
if($exists) {
//Data already exists, so let's update it.
$query = $conn->prepare("UPDATE branchtasksummarydata SET data_json = ?, last_updated = ? WHERE month = ? AND year = ?");
$query->bind_param('ssii', $json, $now->format("Y-m-d H:i:s"), $month, $year);
$query->execute();
} else {
//No data exists, so we will insert it.
$query = $conn->prepare("INSERT INTO branchtasksummarydata (year, month, last_updated, data_json) VALUES(?, ?, ?, ?)");
$query->bind_param('iiss', $year, $month, $now->format("Y-m-d H:i:s"), $json);
$query->execute();
}
return array("data_json" => $json, "last_updated" => $now->format("d/m/Y H:i"));
}
Now when I call this function, it will hang for around 6/7 minutes before giving me an internal server error.
The funny thing is, I test it on my local machine and it works fine. However, on the live server, it throws the error.
EDIT
After coming back to try and fix this issue, I turned on any error logging I could and it just gives me these with each iteration of looping through branches:
Notice: Undefined index: total in C:\WS\JMIS\Library
ew\branchTaskSummary.php on line 61
Notice: Undefined index: total in C:\WS\JMIS\Library
ew\branchTaskSummary.php on line 62
Notice: Undefined offset: 9 in C:\WS\JMIS\Library
ew\branchTaskSummary.php on line 63
Notice: Undefined index: total in C:\WS\JMIS\Library
ew\branchTaskSummary.php on line 63
Notice: Undefined offset: 12 in C:\WS\JMIS\Library
ew\branchTaskSummary.php on line 64
Notice: Undefined index: total in C:\WS\JMIS\Library
ew\branchTaskSummary.php on line 64
Without the logging, it throws a 500 internal server error. I cannot for the life of me figure out what's going on! The code works, I've tested it on multiple local systems. Yet doesn't seem to work on the live server which I do not have access to.
