/home/techb158/workloadmatch.com/workloadmatch.com/BackUp/Manager
Edit: /home/techb158/workloadmatch.com/workloadmatch.com/BackUp/Manager/Available_Teacher_Report.php (7691B)
0
AND NOT EXISTS (
SELECT 1
FROM teacher_course_assignments tca
WHERE tca.Teacher_ID = tp.Teacher_ID
AND tca.Time_Slot = ?
AND tca.Program_ID = ?
AND (? <= tca.End_Date AND ? >= tca.Start_Date)
)
ORDER BY tp.First_Name ASC
";
*/
$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->bind_param("ssiss", $Time_Slot, $Time_Slot, $Program_ID, $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
$absentQuery = "
SELECT 1 FROM teacher_absences
WHERE Teacher_ID = ?
AND (? BETWEEN Absence_From AND Absence_To)
";
$stmtAbsent = $mysqli->prepare($absentQuery);
$stmtAbsent->bind_param("is", $teacherID, $startDate);
$stmtAbsent->execute();
$isAbsent = $stmtAbsent->get_result()->num_rows > 0;
$stmtAbsent->close();
// Check if teacher is replacing in same slot
$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 (? BETWEEN tra.Replacement_From AND tra.Replacement_To)
AND cg.Time_Slot = ?
";
$stmtReplace = $mysqli->prepare($replaceQuery);
$stmtReplace->bind_param("iss", $teacherID, $startDate, $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();
}
/**
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
if (isset($_POST['Program_ID']) || isset($_POST['Time_Slot']) || isset($_POST['Start_Date']) || isset($_POST['End_Date'])) {
$Program_ID = $_POST['Program_ID'];
$Time_Slot = $_POST['Time_Slot']; // e.g., "Morning"
$startDate = $_POST['Start_Date']; // e.g., "2025-08-01"
$endDate = $_POST['End_Date']; // e.g., "2025-08-31"
// Query: Select teachers from teacher_profile that have the given Time_Slot in their Availability
// (which is stored as a comma-separated list) and who do not have a conflicting assignment in teacher_course_assignments.
$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.Program_ID = ?
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);
}
// Bind parameters: Time_Slot is a string; Program_ID is an integer; startDate and endDate are strings.
$stmt->bind_param("ssiss", $Time_Slot, $Time_Slot, $Program_ID, $startDate, $endDate);
$stmt->execute();
$result = $stmt->get_result();
//echo "
Available Teachers for Program " . htmlspecialchars($Program_ID) . " with Time Slot '" . htmlspecialchars($Time_Slot) . "' from " . htmlspecialchars($startDate) . " to " . htmlspecialchars($endDate) . "
";
//echo "
";
//echo "
// | Teacher Name |
// Availability |
// Total Assigned Hours |
//
";
while ($row = $result->fetch_assoc()) {
$teacherID = $row['Teacher_ID'];
$teacherName = htmlspecialchars($row['First_Name'] . " " . $row['Last_Name']);
$availability = htmlspecialchars($row['Availability']);
// Get the teacher's total assigned hours from teacher_course_assignments joined with Courses.
$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();
} else {
echo "Error preparing load query: " . $mysqli->error;
}
echo "
| " . $teacherName . " |
" . $availability . " |
" . htmlspecialchars($totalAssigned) . " hours |
";
}
//echo "
";
$stmt->close();
}
**/
?>