/home/techb158/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/Manager/fetch_teacher_conflicts_only.php (7725B)
prepare("SELECT Time_Slot FROM teacher_profile WHERE Teacher_ID = ?");
$teacherSlotStmt->bind_param("i", $teacherID);
$teacherSlotStmt->execute();
$teacherSlotResult = $teacherSlotStmt->get_result();
$teacherSlotRow = $teacherSlotResult->fetch_assoc();
$teacherSlots = $teacherSlotRow ? trim($teacherSlotRow['Time_Slot']) : '';
$teacherSlotStmt->close();
// Query to get courses for this program
$query = "SELECT Course_ID, Course_Name FROM Courses WHERE Program_ID = ? ORDER BY Course_ID";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("i", $programID);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
$courseID = $row["Course_ID"];
// Check if an unassigned schedule exists for this course and group
$checkStmt = $mysqli->prepare("SELECT COUNT(*) FROM course_group_schedule_main
WHERE Program_ID = ? AND Course_ID = ? AND Group_ID = ? AND Assigned = 0");
if (!$checkStmt) {
die("Error preparing assignment check query: " . $mysqli->error);
}
$checkStmt->bind_param('iii', $programID, $courseID, $groupID);
$checkStmt->execute();
$checkStmt->bind_result($count);
$checkStmt->fetch();
$checkStmt->close();
if ($count > 0) {
// Check if teacher prefers this course
if ($teacherID > 0) {
$prefStmt = $mysqli->prepare("SELECT COUNT(*) FROM teacher_course_preferences
WHERE Teacher_ID = ? AND Course_ID = ?");
$prefStmt->bind_param("ii", $teacherID, $courseID);
$prefStmt->execute();
$prefStmt->bind_result($prefCount);
$prefStmt->fetch();
$prefStmt->close();
if ($prefCount == 0) {
continue; // Skip this course if not preferred
}
// Check if this exact course is already assigned to this teacher (duplicate)
$dupStmt = $mysqli->prepare("SELECT COUNT(*) FROM teacher_course_assignments
WHERE Program_ID = ? AND Course_ID = ? AND Group_ID = ? AND Teacher_ID = ?");
if ($dupStmt) {
$dupStmt->bind_param("iiii", $programID, $courseID, $groupID, $teacherID);
$dupStmt->execute();
$dupStmt->bind_result($dupCount);
$dupStmt->fetch();
$dupStmt->close();
if ($dupCount > 0) {
continue; // Already assigned, skip
}
}
}
// Fetch unassigned schedule entries, excluding conflicting and unavailability periods
$fetchStmt = $mysqli->prepare("
SELECT Reserve_Course, Time_Slot, Start_Date, End_Date, Start_Time, End_Time
FROM course_group_schedule_main cgsm
WHERE cgsm.Program_ID = ? AND cgsm.Course_ID = ? AND cgsm.Group_ID = ? AND cgsm.Assigned = 0
AND NOT EXISTS (
SELECT 1 FROM teacher_course_assignments tca
WHERE tca.Teacher_ID = ?
AND (cgsm.Start_Date <= tca.End_Date AND cgsm.End_Date >= tca.Start_Date)
AND (TRIM(tca.Time_Slot) = TRIM(cgsm.Time_Slot) OR tca.Group_ID = cgsm.Group_ID)
)
AND NOT EXISTS (
SELECT 1 FROM teacher_unavailability ua
WHERE ua.Teacher_ID = ? AND ua.status = 'confirmed'
AND cgsm.Start_Date <= ua.Unavailable_To
AND cgsm.End_Date >= ua.Unavailable_From
)
");
if (!$fetchStmt) {
echo "Error preparing schedule fetch query for Course ID $courseID: " . $mysqli->error . "
";
continue;
}
$fetchStmt->bind_param('iiiii', $programID, $courseID, $groupID, $teacherID, $teacherID);
$fetchStmt->execute();
$fetchStmt->bind_result($Reserve_Course, $Time_Slot, $Start_Date, $End_Date, $Start_Time, $End_Time);
$teacherSlotList = $teacherSlots !== '' ? array_map('trim', explode(',', $teacherSlots)) : [];
$slotFound = false;
$scheduleRows = [];
while ($fetchStmt->fetch()) {
// Skip if this course's time slot doesn't match the teacher's available slots
if (!empty($teacherSlotList) && !in_array($Time_Slot, $teacherSlotList)) {
continue;
}
$checkedReserve = ($Reserve_Course == 1) ? 'checked' : '';
$formattedStartTime = date("h:i A", strtotime($Start_Time));
$formattedEndTime = date("h:i A", strtotime($End_Time));
$scheduleRows[] = [
'checkedReserve' => $checkedReserve,
'Time_Slot' => $Time_Slot,
'Start_Date' => $Start_Date,
'End_Date' => $End_Date,
'Start_Time' => $formattedStartTime,
'End_Time' => $formattedEndTime,
];
$slotFound = true;
}
$fetchStmt->close();
if (!$slotFound) {
continue;
}
// Output one
per slot so the frontend renders a card for each
echo "";
foreach ($scheduleRows as $s) {
echo "
|
|
" . htmlspecialchars($row["Course_Name"]) . " |
|
|
|
|
|
";
}
}
}
$stmt->close();
$mysqli->close();
}
?>