/home/techb158/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/Manager/find_replacement.php (18313B)
prepare("SELECT * FROM teacher_absences WHERE Absence_ID = ?");
$stmt->bind_param("i", $absence_id);
$stmt->execute();
$absence = $stmt->get_result()->fetch_assoc();
$stmt->close();
if (!$absence) {
die("Absence record not found.");
}
$absent_teacher_id = $absence['Teacher_ID'];
$absence_from = $absence['Absence_From'];
$absence_to = $absence['Absence_To'];
// Get affected schedules
$scheduleQuery = "SELECT * FROM teacher_course_assignments
WHERE Teacher_ID = ?
AND Start_Date <= ?
AND End_Date >= ?";
$stmt = $mysqli->prepare($scheduleQuery);
$stmt->bind_param("iss", $absent_teacher_id, $absence_to, $absence_from);
$stmt->execute();
$affectedSchedules = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
if (!$stmt) {
die("Prepare failed: (" . $mysqli->error . ") " . $mysqli->error);
}
// Prepare for schedule detail query (Course_Name and Group_Name)
/* $scheduleDetailsQuery = $mysqli->prepare("SELECT cgs.Schedule_ID, c.Course_Name, mg.Group_Name
FROM course_group_schedule cgs
JOIN Courses c ON cgs.Course_ID = c.Course_ID
JOIN manager_group_name mg ON cgs.Group_ID = mg.Group_ID
WHERE cgs.Schedule_ID = ?"); */
$scheduleDetailsQuery = $mysqli->prepare("SELECT cgs.Schedule_ID, c.Course_Name, mg.Group_Name
FROM course_group_schedule_main cgs
JOIN Courses c ON cgs.Course_ID = c.Course_ID
JOIN manager_group_name mg ON cgs.Group_ID = mg.Group_ID
WHERE cgs.Schedule_ID = ?");
if (!$scheduleDetailsQuery) {
die("Prepare failed (Schedule Details): (" . $mysqli->error . ") " . $mysqli->error);
}
// Get all teachers
$teachers = [];
$res = $mysqli->query("SELECT Teacher_ID, First_Name, Last_Name, Time_Slot, Load_Hours, User_Access FROM teacher_profile WHERE User_Access = 1");
while ($row = $res->fetch_assoc()) {
$teachers[$row['Teacher_ID']] = $row;
}
// Filter available teachers per schedule
$availableReplacers = [];
foreach ($affectedSchedules as $schedule) {
foreach ($teachers as $teacher) {
if ($teacher['Teacher_ID'] == $schedule['Teacher_ID']) continue;
// Check time slot match
$slots = array_map('trim', explode(',', $teacher['Time_Slot']));
if (!in_array($schedule['Time_Slot'], $slots)) continue;
// Check course preference
$prefQuery = "SELECT COUNT(*) AS pref FROM teacher_course_preferences WHERE Teacher_ID = ? AND Course_ID = ?";
$stmtPref = $mysqli->prepare($prefQuery);
$stmtPref->bind_param("ii", $teacher['Teacher_ID'], $schedule['Course_ID']);
$stmtPref->execute();
$prefResult = $stmtPref->get_result()->fetch_assoc();
$stmtPref->close();
if ($prefResult['pref'] == 0) continue;
// Check schedule conflict (any time slot)
$checkQuery = "SELECT COUNT(*) AS cnt FROM teacher_course_assignments
WHERE Teacher_ID = ?
AND (? <= End_Date AND ? >= Start_Date)
AND NOT (Course_ID = ? AND Group_ID = ? AND Program_ID = ?)";
$stmtCheck = $mysqli->prepare($checkQuery);
$stmtCheck->bind_param("issiii", $teacher['Teacher_ID'], $schedule['Start_Date'], $schedule['End_Date'], $schedule['Course_ID'], $schedule['Group_ID'], $schedule['Program_ID']);
$stmtCheck->execute();
$count = $stmtCheck->get_result()->fetch_assoc()['cnt'];
$stmtCheck->close();
if ($count == 0) {
$availableReplacers[$schedule['Schedule_ID']][] = $teacher;
}
}
}
// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['replace_teacher_id'], $_POST['schedule_id'])) {
$replacing_teacher_id = intval($_POST['replace_teacher_id']);
$schedule_id = intval($_POST['schedule_id']);
$check = $mysqli->prepare("SELECT COUNT(*) as cnt FROM teacher_replacement_assignments WHERE Schedule_ID = ?");
$check->bind_param("i", $schedule_id);
$check->execute();
$exists = $check->get_result()->fetch_assoc()['cnt'];
$check->close();
if ($exists > 0) {
$error = "A replacement for this schedule already exists.";
} else {
$stmt = $mysqli->prepare("INSERT INTO teacher_replacement_assignments
(Absent_Teacher_ID, Replacing_Teacher_ID, Schedule_ID, Replacement_From, Replacement_To)
VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param("iiiss", $absent_teacher_id, $replacing_teacher_id, $schedule_id, $absence_from, $absence_to);
$stmt->execute();
$stmt->close();
if (!$stmt) {
die("Prepare failed: (" . $mysqli->error . ") " . $mysqli->error);
}
//$success = "Replacement assigned successfully.";
$success = "Replacement assigned successfully.";
set_flash_msg('Replacement assigned successfully.', 'warning');
header("Location: report_absence.php");
exit;
}
}
?>
Find Replacement
".htmlspecialchars($success, ENT_QUOTES, 'UTF-8')."
"; ?>
".htmlspecialchars($error, ENT_QUOTES, 'UTF-8')."
"; ?>
bind_param("i", $schedule_id);
$scheduleDetailsQuery->execute();
$result = $scheduleDetailsQuery->get_result();
if ($row = $result->fetch_assoc()) {
$course_name = $row['Course_Name'];
$group_name = $row['Group_Name'];
}
?>