/home/techb158/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/Manager/Available_Teacher_Report.php (3554B)
Please select Time Slot, Start Date, and End Date. | ';
exit;
}
$query = "
SELECT tp.Teacher_ID, tp.First_Name, tp.Last_Name, tp.Time_Slot AS Availability
FROM teacher_profile tp
WHERE FIND_IN_SET(?, tp.Time_Slot) > 0
AND NOT EXISTS (
SELECT 1
FROM teacher_course_assignments tca
WHERE tca.Teacher_ID = tp.Teacher_ID
AND tca.Time_Slot = ?
AND (? <= tca.End_Date AND ? >= tca.Start_Date)
)
ORDER BY tp.First_Name ASC
";
$stmt = $mysqli->prepare($query);
if (!$stmt) {
die("Error preparing query: " . $mysqli->error);
}
$stmt->bind_param("ssss", $Time_Slot, $Time_Slot, $startDate, $endDate);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
$teacherID = $row['Teacher_ID'];
$teacherName = htmlspecialchars($row['First_Name'] . " " . $row['Last_Name']);
$availability = htmlspecialchars($row['Availability']);
$totalAssigned = 0;
$queryLoad = "
SELECT IFNULL(SUM(c.Course_Time), 0) AS total_assigned
FROM teacher_course_assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
WHERE tca.Teacher_ID = ?
";
$stmtLoad = $mysqli->prepare($queryLoad);
if ($stmtLoad) {
$stmtLoad->bind_param("i", $teacherID);
$stmtLoad->execute();
$resultLoad = $stmtLoad->get_result();
if ($rowLoad = $resultLoad->fetch_assoc()) {
$totalAssigned = $rowLoad['total_assigned'];
}
$stmtLoad->close();
}
// Check if teacher is absent during any part of the date range
$absentQuery = "
SELECT 1 FROM teacher_absences
WHERE Teacher_ID = ?
AND (? <= Absence_To AND ? >= Absence_From)
";
$stmtAbsent = $mysqli->prepare($absentQuery);
$stmtAbsent->bind_param("iss", $teacherID, $startDate, $endDate);
$stmtAbsent->execute();
$isAbsent = $stmtAbsent->get_result()->num_rows > 0;
$stmtAbsent->close();
// Check if teacher is replacing in same slot during any part of the range
$replaceQuery = "
SELECT 1 FROM teacher_replacement_assignments tra
JOIN course_group_schedule_main cg ON tra.Schedule_ID = cg.Schedule_ID
WHERE tra.Replacing_Teacher_ID = ?
AND (? <= tra.Replacement_To AND ? >= tra.Replacement_From)
AND cg.Time_Slot = ?
";
$stmtReplace = $mysqli->prepare($replaceQuery);
$stmtReplace->bind_param("isss", $teacherID, $startDate, $endDate, $Time_Slot);
$stmtReplace->execute();
$isReplacing = $stmtReplace->get_result()->num_rows > 0;
$stmtReplace->close();
$status = $isAbsent ? "Absent" : ($isReplacing ? "Has Replacement" : "Available");
$rowStyle = $isReplacing ? "style='background-color:#d1e7dd;'" : ($isAbsent ? "style='background-color:#f8d7da;'" : "");
echo "
| $teacherName |
$availability |
" . htmlspecialchars($totalAssigned) . " hours |
$status |
";
}
$stmt->close();