/home/techb158/workloadmatch.com/workloadmatch.com/BackUp/Manager/Inc
Edit: /home/techb158/workloadmatch.com/workloadmatch.com/BackUp/Manager/Inc/Assignment - Copy.php (60402B)
prepare($scheduleQuery);
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Reserve_Course);
} else {
$scheduleQuery = "SELECT * FROM Course_Group_Schedule
WHERE Assigned = 0
AND Program_ID = ?
AND Reserve_Course = ?
ORDER BY Time_Slot";
$stmt = $mysqli->prepare($scheduleQuery);
$stmt->bind_param("ii", $Program_ID, $Reserve_Course);
}
if (!$stmt || !$stmt->execute()) {
die("Error preparing/executing schedule query: " . $mysqli->error);
}
$schedulesResult = $stmt->get_result();
$schedules = [];
while ($row = $schedulesResult->fetch_assoc()) {
$schedules[] = $row;
}
$stmt->close();
$teacherQuery = "
SELECT
tp.Teacher_ID,
tp.First_Name,
tp.Last_Name,
tp.Seniority_ID,
tp.Time_Slot AS Availability,
tp.Load_Hours,
tp.User_Access,
ttl.Top_Teacher_ID,
ttl.Teacher_Level
FROM Teacher_Profile tp
LEFT JOIN Top_Teacher_List ttl
ON tp.Teacher_ID = ttl.Teacher_ID
AND ttl.Program_ID = ?
WHERE 1
ORDER BY tp.First_Name ASC, tp.Last_Name ASC
";
$stmt = $mysqli->prepare($teacherQuery);
$stmt->bind_param("i", $Program_ID);
$stmt->execute();
$result = $stmt->get_result();
$allTeachers = [];
while ($row = $result->fetch_assoc()) {
$allTeachers[] = $row;
}
$stmt->close();
foreach ($allTeachers as &$teacher) {
$teacher['Unavailability'] = [];
$stmt = $mysqli->prepare("SELECT Unavailable_From, Unavailable_To FROM Teacher_Unavailability WHERE Teacher_ID = ?");
$stmt->bind_param("i", $teacher['Teacher_ID']);
$stmt->execute();
$res = $stmt->get_result();
while ($unav = $res->fetch_assoc()) {
$teacher['Unavailability'][] = $unav;
}
$stmt->close();
}
$premiumTeachers = $qualifiedTeachers = $unqualifiedTeachers = [];
foreach ($allTeachers as $teacher) {
if (!empty($teacher['Top_Teacher_ID'])) {
$premiumTeachers[] = $teacher;
} elseif ($teacher['Seniority_ID'] == 1) {
$qualifiedTeachers[] = $teacher;
} else {
$unqualifiedTeachers[] = $teacher;
}
}
$canAssign = function($teacher, $schedule) use ($mysqli) {
if (isset($teacher['User_Access']) && $teacher['User_Access'] == 0) {
return false;
}
foreach ($teacher['Unavailability'] as $unav) {
$start = strtotime($unav['Unavailable_From']);
$end = strtotime($unav['Unavailable_To']);
$schStart = strtotime($schedule['Start_Date']);
$schEnd = strtotime($schedule['End_Date']);
if (($schStart <= $end) && ($schEnd >= $start)) {
return false;
}
}
$availableSlots = array_map('trim', explode(',', $teacher['Availability']));
if (!in_array($schedule['Time_Slot'], $availableSlots)) {
return false;
}
$conflictQuery = "SELECT COUNT(*) AS cnt FROM Teacher_Course_Assignments
WHERE Teacher_ID = ?
AND Time_Slot = ?
AND (? <= End_Date AND ? >= Start_Date)";
$conflictStmt = $mysqli->prepare($conflictQuery);
if (!$conflictStmt) return false;
$conflictStmt->bind_param("isss", $teacher['Teacher_ID'], $schedule['Time_Slot'], $schedule['Start_Date'], $schedule['End_Date']);
$conflictStmt->execute();
$conflictResult = $conflictStmt->get_result();
$conflictRow = $conflictResult->fetch_assoc();
$conflictStmt->close();
if ($conflictRow['cnt'] > 0) return false;
$currentLoadQuery = "SELECT IFNULL(SUM(c.Course_Time), 0) AS total_hours
FROM Teacher_Course_Assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
WHERE tca.Teacher_ID = ?";
$currentLoadStmt = $mysqli->prepare($currentLoadQuery);
$currentLoadStmt->bind_param("i", $teacher['Teacher_ID']);
$currentLoadStmt->execute();
$currentLoadResult = $currentLoadStmt->get_result();
$currentLoad = $currentLoadResult->fetch_assoc()['total_hours'];
$currentLoadStmt->close();
$courseQuery = "SELECT Course_Time FROM Courses WHERE Course_ID = ?";
$courseStmt = $mysqli->prepare($courseQuery);
$courseStmt->bind_param("i", $schedule['Course_ID']);
$courseStmt->execute();
$newCourseTime = $courseStmt->get_result()->fetch_assoc()['Course_Time'];
$courseStmt->close();
if (($currentLoad + $newCourseTime) > $teacher['Load_Hours']) return false;
$prefQuery = "SELECT * FROM Teacher_Course_Preferences
WHERE Teacher_ID = ?
AND Course_ID = ?
AND Priority IN (1,2)";
$prefStmt = $mysqli->prepare($prefQuery);
$prefStmt->bind_param("ii", $teacher['Teacher_ID'], $schedule['Course_ID']);
$prefStmt->execute();
$prefResult = $prefStmt->get_result();
$prefStmt->close();
return $prefResult->num_rows > 0;
};
$assignTeachers = function($teacherList, &$ptr, $schedule) use ($canAssign, $mysqli) {
$cnt = count($teacherList);
for ($i = 0; $i < $cnt; $i++) {
$currentIndex = ($ptr + $i) % $cnt;
$teacher = $teacherList[$currentIndex];
if ($canAssign($teacher, $schedule)) {
$insertQuery = "INSERT INTO Teacher_Course_Assignments
(Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$insertStmt = $mysqli->prepare($insertQuery);
$assignedAt = date('Y-m-d H:i:s');
$insertStmt->bind_param("iiiiissss",
$teacher['Teacher_ID'],
$schedule['Course_ID'],
$schedule['Group_ID'],
$schedule['Program_ID'],
$schedule['Schedule_ID'],
$schedule['Time_Slot'],
$schedule['Start_Date'],
$schedule['End_Date'],
$assignedAt
);
$insertStmt->execute();
$insertStmt->close();
$updateQuery = "UPDATE Course_Group_Schedule SET Assigned = 1 WHERE Schedule_ID = ?";
$updateStmt = $mysqli->prepare($updateQuery);
$updateStmt->bind_param("i", $schedule['Schedule_ID']);
$updateStmt->execute();
$updateStmt->close();
$ptr = ($currentIndex + 1) % $cnt;
return true;
}
}
return false;
};
$ptrPremium = $ptrQualified = $ptrUnqualified = 0;
foreach ($schedules as $schedule) {
$assigned = $assignTeachers($premiumTeachers, $ptrPremium, $schedule)
|| $assignTeachers($qualifiedTeachers, $ptrQualified, $schedule)
|| $assignTeachers($unqualifiedTeachers, $ptrUnqualified, $schedule);
}
header('Location: generateschedule.php?error=Schedule generated successfully!');
exit();
}
/*
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['Program_ID'], $_POST['Group_ID'])) {
// Get Program ID and Group ID from the submitted form.
$Program_ID = $_POST['Program_ID'];
$Group_ID = $_POST['Group_ID'];
$Reserve_Course = isset($_POST['Reserve_Course']) ? 1 : 0;
// -----------------------------
// STEP 1: Get all unassigned schedules for this Program.
// -----------------------------
if ($Group_ID != 0) {
$scheduleQuery = "SELECT * FROM Course_Group_Schedule
WHERE Assigned = 0
AND Program_ID = ?
AND Group_ID = ?
AND Reserve_Course = ?
ORDER BY Time_Slot";
$stmt = $mysqli->prepare($scheduleQuery);
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Reserve_Course);
} else {
$scheduleQuery = "SELECT * FROM Course_Group_Schedule
WHERE Assigned = 0
AND Program_ID = ?
AND Reserve_Course = ?
ORDER BY Time_Slot";
$stmt = $mysqli->prepare($scheduleQuery);
$stmt->bind_param("ii", $Program_ID, $Reserve_Course);
}
if (!$stmt) {
die("Error preparing schedule query: " . $mysqli->error);
}
if (!$stmt->execute()) {
die("Error executing schedule query: " . $stmt->error);
}
$schedulesResult = $stmt->get_result();
$schedules = [];
while ($row = $schedulesResult->fetch_assoc()) {
$schedules[] = $row;
}
$stmt->close();
// -----------------------------
// STEP 2: Get teachers for this Program.
// Combine data from Teacher_Profile and Top_Teacher_List (if any) and include new fields:
// User_Access, Unavailable_From, and Unavailable_To.
// -----------------------------
$teacherQuery = "
SELECT
tp.Teacher_ID,
tp.First_Name,
tp.Last_Name,
tp.Seniority_ID,
tp.Time_Slot AS Availability,
tp.Load_Hours,
tp.User_Access,
tp.Unavailable_From,
tp.Unavailable_To,
ttl.Top_Teacher_ID,
ttl.Teacher_Level
FROM Teacher_Profile tp
LEFT JOIN Top_Teacher_List ttl
ON tp.Teacher_ID = ttl.Teacher_ID
AND ttl.Program_ID = ?
WHERE 1
ORDER BY tp.First_Name ASC, tp.Last_Name ASC
";
$stmt = $mysqli->prepare($teacherQuery);
if (!$stmt) {
die("Error preparing teacher query: " . $mysqli->error);
}
$stmt->bind_param("i", $Program_ID);
$stmt->execute();
$result = $stmt->get_result();
$allTeachers = [];
while ($row = $result->fetch_assoc()) {
$allTeachers[] = $row;
}
$stmt->close();
// Separate teachers into three groups.
$premiumTeachers = [];
$qualifiedTeachers = [];
$unqualifiedTeachers = [];
foreach ($allTeachers as $teacher) {
if (!empty($teacher['Top_Teacher_ID'])) {
$premiumTeachers[] = $teacher;
} elseif ($teacher['Seniority_ID'] == 1) {
$qualifiedTeachers[] = $teacher;
} elseif ($teacher['Seniority_ID'] == 2) {
$unqualifiedTeachers[] = $teacher;
}
}
$cntPremium = count($premiumTeachers);
$cntQualified = count($qualifiedTeachers);
$cntUnqualified = count($unqualifiedTeachers);
$ptrPremium = 0;
$ptrQualified = 0;
$ptrUnqualified = 0;
// -----------------------------
// STEP 3: Loop through each unassigned schedule and try to assign a teacher.
// -----------------------------
// Define a helper function (as an anonymous function) for assignment checks.
$canAssign = function($teacher, $schedule) use ($mysqli, $Program_ID) {
// 1. Skip if teacher's User_Access is 0.
if (isset($teacher['User_Access']) && $teacher['User_Access'] == 0) {
return false;
}
// 2. Exclude teacher if their unavailable period overlaps the schedule.
if (isset($teacher['Unavailable_From'], $teacher['Unavailable_To']) &&
!empty($teacher['Unavailable_From']) && !empty($teacher['Unavailable_To'])) {
$teacherUnavailableFrom = strtotime($teacher['Unavailable_From']);
$teacherUnavailableTo = strtotime($teacher['Unavailable_To']);
$scheduleStart = strtotime($schedule['Start_Date']);
$scheduleEnd = strtotime($schedule['End_Date']);
// Overlap exists if the schedule starts before or on the teacher's unavailable end
// and ends after or on the teacher's unavailable start.
if (($scheduleStart <= $teacherUnavailableTo) && ($scheduleEnd >= $teacherUnavailableFrom)) {
return false;
}
}
// 3. Check teacher availability against the schedule's Time_Slot.
$availableSlots = array_map('trim', explode(',', $teacher['Availability']));
if (!in_array($schedule['Time_Slot'], $availableSlots)) {
return false;
}
// 4. Check for conflicts: ensure teacher does not have an overlapping assignment.
$conflictQuery = "SELECT COUNT(*) AS cnt FROM Teacher_Course_Assignments
WHERE Teacher_ID = ?
AND Time_Slot = ?
AND (? <= End_Date AND ? >= Start_Date)";
$conflictStmt = $mysqli->prepare($conflictQuery);
if (!$conflictStmt) {
echo "Error preparing conflict query: " . $mysqli->error . "
";
return false;
}
$conflictStmt->bind_param("isss", $teacher['Teacher_ID'], $schedule['Time_Slot'], $schedule['Start_Date'], $schedule['End_Date']);
if (!$conflictStmt->execute()) {
echo "Error executing conflict query: " . $conflictStmt->error . "
";
$conflictStmt->close();
return false;
}
$conflictResult = $conflictStmt->get_result();
$conflictRow = $conflictResult->fetch_assoc();
$conflictStmt->close();
if ($conflictRow['cnt'] > 0) {
return false;
}
// 5. Check load hours capacity.
$currentLoadQuery = "SELECT IFNULL(SUM(c.Course_Time), 0) AS total_hours
FROM Teacher_Course_Assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
WHERE tca.Teacher_ID = ?";
$currentLoadStmt = $mysqli->prepare($currentLoadQuery);
if (!$currentLoadStmt) {
echo "Error preparing current load query: " . $mysqli->error . "
";
return false;
}
$currentLoadStmt->bind_param("i", $teacher['Teacher_ID']);
$currentLoadStmt->execute();
$currentLoadResult = $currentLoadStmt->get_result();
$currentLoadRow = $currentLoadResult->fetch_assoc();
$currentLoadStmt->close();
$currentLoad = $currentLoadRow['total_hours'];
// 6. Get the new course's hours.
$courseQuery = "SELECT Course_Time FROM Courses WHERE Course_ID = ?";
$courseStmt = $mysqli->prepare($courseQuery);
if (!$courseStmt) {
echo "Error preparing course query: " . $mysqli->error . "
";
return false;
}
$courseStmt->bind_param("i", $schedule['Course_ID']);
$courseStmt->execute();
$courseResult = $courseStmt->get_result();
$courseData = $courseResult->fetch_assoc();
$newCourseTime = $courseData['Course_Time'];
$courseStmt->close();
if (($currentLoad + $newCourseTime) > $teacher['Load_Hours']) {
return false;
}
// 7. Check teacher's course preferences.
$prefQuery = "SELECT * FROM Teacher_Course_Preferences
WHERE Teacher_ID = ?
AND Course_ID = ?
AND Priority IN (1,2)
ORDER BY Priority ASC";
$prefStmt = $mysqli->prepare($prefQuery);
if (!$prefStmt) {
echo "Error preparing preference query: " . $mysqli->error . "
";
return false;
}
$prefStmt->bind_param("ii", $teacher['Teacher_ID'], $schedule['Course_ID']);
$prefStmt->execute();
$prefResult = $prefStmt->get_result();
$preferences = [];
while ($prefRow = $prefResult->fetch_assoc()) {
$preferences[] = $prefRow;
}
$prefStmt->close();
if (empty($preferences)) {
return false;
}
// If all conditions are met, the teacher can be assigned.
return true;
};
// -----------------------------
// STEP 4: Process each schedule, assigning teachers from premium, then qualified, then unqualified groups.
// -----------------------------
foreach ($schedules as $schedule) {
$assigned = false;
// Attempt assignment from premium teachers.
if ($cntPremium > 0) {
for ($i = 0; $i < $cntPremium; $i++) {
$currentIndex = ($ptrPremium + $i) % $cntPremium;
$teacher = $premiumTeachers[$currentIndex];
if ($canAssign($teacher, $schedule)) {
// Insert assignment.
$insertQuery = "INSERT INTO Teacher_Course_Assignments
(Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$insertStmt = $mysqli->prepare($insertQuery);
if (!$insertStmt) {
echo "Error preparing insert query: " . $mysqli->error . "
";
continue;
}
$assignedAt = date('Y-m-d H:i:s');
$insertStmt->bind_param("iiiiissss",
$teacher['Teacher_ID'],
$schedule['Course_ID'],
$schedule['Group_ID'],
$schedule['Program_ID'],
$schedule['Schedule_ID'],
$schedule['Time_Slot'],
$schedule['Start_Date'],
$schedule['End_Date'],
$assignedAt
);
if (!$insertStmt->execute()) {
echo "Error executing insert query: " . $insertStmt->error . "
";
$insertStmt->close();
continue;
}
$insertStmt->close();
// Mark the schedule as assigned.
$updateQuery = "UPDATE Course_Group_Schedule SET Assigned = 1 WHERE Schedule_ID = ?";
$updateStmt = $mysqli->prepare($updateQuery);
if (!$updateStmt) {
echo "Error preparing update query: " . $mysqli->error . "
";
continue;
}
$updateStmt->bind_param("i", $schedule['Schedule_ID']);
if (!$updateStmt->execute()) {
echo "Error executing update query: " . $updateStmt->error . "
";
$updateStmt->close();
continue;
}
$updateStmt->close();
$ptrPremium = ($currentIndex + 1) % $cntPremium;
$assigned = true;
break;
}
}
}
// If not assigned by premium, try qualified teachers.
if (!$assigned && $cntQualified > 0) {
for ($i = 0; $i < $cntQualified; $i++) {
$currentIndex = ($ptrQualified + $i) % $cntQualified;
$teacher = $qualifiedTeachers[$currentIndex];
if ($canAssign($teacher, $schedule)) {
$insertQuery = "INSERT INTO Teacher_Course_Assignments
(Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$insertStmt = $mysqli->prepare($insertQuery);
if (!$insertStmt) {
echo "Error preparing insert query: " . $mysqli->error . "
";
continue;
}
$assignedAt = date('Y-m-d H:i:s');
$insertStmt->bind_param("iiiiissss",
$teacher['Teacher_ID'],
$schedule['Course_ID'],
$schedule['Group_ID'],
$schedule['Program_ID'],
$schedule['Schedule_ID'],
$schedule['Time_Slot'],
$schedule['Start_Date'],
$schedule['End_Date'],
$assignedAt
);
if (!$insertStmt->execute()) {
echo "Error executing insert query: " . $insertStmt->error . "
";
$insertStmt->close();
continue;
}
$insertStmt->close();
$updateQuery = "UPDATE Course_Group_Schedule SET Assigned = 1 WHERE Schedule_ID = ?";
$updateStmt = $mysqli->prepare($updateQuery);
if (!$updateStmt) {
echo "Error preparing update query: " . $mysqli->error . "
";
continue;
}
$updateStmt->bind_param("i", $schedule['Schedule_ID']);
if (!$updateStmt->execute()) {
echo "Error executing update query: " . $updateStmt->error . "
";
$updateStmt->close();
continue;
}
$updateStmt->close();
$ptrQualified = ($currentIndex + 1) % $cntQualified;
$assigned = true;
break;
}
}
}
// Finally, if still not assigned, try unqualified teachers.
if (!$assigned && $cntUnqualified > 0) {
for ($i = 0; $i < $cntUnqualified; $i++) {
$currentIndex = ($ptrUnqualified + $i) % $cntUnqualified;
$teacher = $unqualifiedTeachers[$currentIndex];
if ($canAssign($teacher, $schedule)) {
$insertQuery = "INSERT INTO Teacher_Course_Assignments
(Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$insertStmt = $mysqli->prepare($insertQuery);
if (!$insertStmt) {
echo "Error preparing insert query: " . $mysqli->error . "
";
continue;
}
$assignedAt = date('Y-m-d H:i:s');
$insertStmt->bind_param("iiiiissss",
$teacher['Teacher_ID'],
$schedule['Course_ID'],
$schedule['Group_ID'],
$schedule['Program_ID'],
$schedule['Schedule_ID'],
$schedule['Time_Slot'],
$schedule['Start_Date'],
$schedule['End_Date'],
$assignedAt
);
if (!$insertStmt->execute()) {
echo "Error executing insert query: " . $insertStmt->error . "
";
$insertStmt->close();
continue;
}
$insertStmt->close();
$updateQuery = "UPDATE Course_Group_Schedule SET Assigned = 1 WHERE Schedule_ID = ?";
$updateStmt = $mysqli->prepare($updateQuery);
if (!$updateStmt) {
echo "Error preparing update query: " . $mysqli->error . "
";
continue;
}
$updateStmt->bind_param("i", $schedule['Schedule_ID']);
if (!$updateStmt->execute()) {
echo "Error executing update query: " . $updateStmt->error . "
";
$updateStmt->close();
continue;
}
$updateStmt->close();
$ptrUnqualified = ($currentIndex + 1) % $cntUnqualified;
$assigned = true;
break;
}
}
}
}
// After processing, redirect or show a success message.
header('Location: generateschedule.php?error=Schedule generated successfully!');
exit();
}
*/
/*
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['Program_ID'], $_POST['Group_ID'])) {
// Get Program ID and Group ID from the submitted form
$Program_ID = $_POST['Program_ID'];
$Group_ID = $_POST['Group_ID'];
$Reserve_Course = isset($_POST['Reserve_Course']) ? 1 : 0;
// -----------------------------
// STEP 1: Get all unassigned schedules for this Program
// -----------------------------
if ($Group_ID != 0) {
$scheduleQuery = "SELECT * FROM Course_Group_Schedule
WHERE Assigned = 0
AND Program_ID = ?
AND Group_ID = ?
AND Reserve_Course = ?
ORDER BY Time_Slot";
$stmt = $mysqli->prepare($scheduleQuery);
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Reserve_Course);
} else {
$scheduleQuery = "SELECT * FROM Course_Group_Schedule
WHERE Assigned = 0
AND Program_ID = ?
AND Reserve_Course = ?
ORDER BY Time_Slot";
$stmt = $mysqli->prepare($scheduleQuery);
$stmt->bind_param("ii", $Program_ID, $Reserve_Course);
}
if (!$stmt) {
die("Error preparing schedule query: " . $mysqli->error);
}
if (!$stmt->execute()) {
die("Error executing schedule query: " . $stmt->error);
}
$schedulesResult = $stmt->get_result();
$schedules = [];
while ($row = $schedulesResult->fetch_assoc()) {
$schedules[] = $row;
}
$stmt->close();
// -----------------------------
// STEP 2: Get teachers for this Program by combining Teacher_Profile and Top_Teacher_List.
// Add User_Access field to prevent assigning courses to teachers who have User_Access = 0.
// -----------------------------
// Added User_Access field
$teacherQuery = "
SELECT
tp.Teacher_ID,
tp.First_Name,
tp.Last_Name,
tp.Seniority_ID,
tp.Time_Slot AS Availability,
tp.Load_Hours,
tp.User_Access,
ttl.Top_Teacher_ID,
ttl.Teacher_Level
FROM Teacher_Profile tp
LEFT JOIN Top_Teacher_List ttl
ON tp.Teacher_ID = ttl.Teacher_ID
AND ttl.Program_ID = ?
WHERE 1
ORDER BY tp.First_Name ASC, tp.Last_Name ASC
";
$stmt = $mysqli->prepare($teacherQuery);
if (!$stmt) {
die("Error preparing teacher query: " . $mysqli->error);
}
$stmt->bind_param("i", $Program_ID);
$stmt->execute();
$result = $stmt->get_result();
$allTeachers = [];
while ($row = $result->fetch_assoc()) {
$allTeachers[] = $row;
}
$stmt->close();
// Separate teachers into groups.
$premiumTeachers = [];
$qualifiedTeachers = [];
$unqualifiedTeachers = [];
foreach ($allTeachers as $teacher) {
// Note: if Teacher_User_Access = 0, the teacher is not eligible;
// The check is performed later in the assignment function.
if (!empty($teacher['Top_Teacher_ID'])) {
$premiumTeachers[] = $teacher;
} elseif ($teacher['Seniority_ID'] == 1) {
$qualifiedTeachers[] = $teacher;
} elseif ($teacher['Seniority_ID'] == 2) {
$unqualifiedTeachers[] = $teacher;
}
}
// Initialize round-robin pointers for each group.
$cntPremium = count($premiumTeachers);
$cntQualified = count($qualifiedTeachers);
$cntUnqualified = count($unqualifiedTeachers);
$ptrPremium = 0;
$ptrQualified = 0;
$ptrUnqualified = 0;
// -----------------------------
// STEP 3: Loop through each unassigned schedule and try to assign a teacher
// -----------------------------
// Helper inline function: for each teacher, perform assignment checks.
// (Availability, conflicts, load hours, preferences)
$canAssign = function($teacher, $schedule) use ($mysqli, $Program_ID) {
// New check: Do not assign courses if the teacher's User_Access equals 0.
if (isset($teacher['User_Access']) && $teacher['User_Access'] == 0) {
return false;
}
// Check availability: Teacher's Availability field is a comma-separated list.
$availableSlots = array_map('trim', explode(',', $teacher['Availability']));
if (!in_array($schedule['Time_Slot'], $availableSlots)) {
return false;
}
// Check conflicts: any overlapping assignment in the same time slot.
$conflictQuery = "SELECT COUNT(*) AS cnt FROM Teacher_Course_Assignments
WHERE Teacher_ID = ?
AND Time_Slot = ?
AND (? <= End_Date AND ? >= Start_Date)";
$conflictStmt = $mysqli->prepare($conflictQuery);
if (!$conflictStmt) {
echo "Error preparing conflict query: " . $mysqli->error . "
";
return false;
}
$conflictStmt->bind_param("isss", $teacher['Teacher_ID'], $schedule['Time_Slot'], $schedule['Start_Date'], $schedule['End_Date']);
if (!$conflictStmt->execute()) {
echo "Error executing conflict query: " . $conflictStmt->error . "
";
$conflictStmt->close();
return false;
}
$conflictResult = $conflictStmt->get_result();
$conflictRow = $conflictResult->fetch_assoc();
$conflictStmt->close();
if ($conflictRow['cnt'] > 0) {
return false;
}
// Check load hours capacity.
// 1. Get teacher's current assigned hours.
$currentLoadQuery = "SELECT IFNULL(SUM(c.Course_Time), 0) AS total_hours
FROM Teacher_Course_Assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
WHERE tca.Teacher_ID = ?";
$currentLoadStmt = $mysqli->prepare($currentLoadQuery);
if (!$currentLoadStmt) {
echo "Error preparing current load query: " . $mysqli->error . "
";
return false;
}
$currentLoadStmt->bind_param("i", $teacher['Teacher_ID']);
$currentLoadStmt->execute();
$currentLoadResult = $currentLoadStmt->get_result();
$currentLoadRow = $currentLoadResult->fetch_assoc();
$currentLoadStmt->close();
$currentLoad = $currentLoadRow['total_hours'];
// 2. Get new course's hours.
$courseQuery = "SELECT Course_Time FROM Courses WHERE Course_ID = ?";
$courseStmt = $mysqli->prepare($courseQuery);
if (!$courseStmt) {
echo "Error preparing course query: " . $mysqli->error . "
";
return false;
}
$courseStmt->bind_param("i", $schedule['Course_ID']);
$courseStmt->execute();
$courseResult = $courseStmt->get_result();
$courseData = $courseResult->fetch_assoc();
$newCourseTime = $courseData['Course_Time'];
$courseStmt->close();
if (($currentLoad + $newCourseTime) > $teacher['Load_Hours']) {
return false;
}
// Check teacher's course preferences.
$prefQuery = "SELECT * FROM Teacher_Course_Preferences
WHERE Teacher_ID = ?
AND Course_ID = ?
AND Priority IN (1,2)
ORDER BY Priority ASC";
$prefStmt = $mysqli->prepare($prefQuery);
if (!$prefStmt) {
echo "Error preparing preference query: " . $mysqli->error . "
";
return false;
}
$prefStmt->bind_param("ii", $teacher['Teacher_ID'], $schedule['Course_ID']);
$prefStmt->execute();
$prefResult = $prefStmt->get_result();
$preferences = [];
while ($prefRow = $prefResult->fetch_assoc()) {
$preferences[] = $prefRow;
}
$prefStmt->close();
if (empty($preferences)) {
return false;
}
return true;
}; // end of anonymous function
// Process each schedule entry and assign a teacher based on the groups, in a round-robin fashion.
foreach ($schedules as $schedule) {
$assigned = false;
// Try premium teachers first.
if ($cntPremium > 0) {
for ($i = 0; $i < $cntPremium; $i++) {
$currentIndex = ($ptrPremium + $i) % $cntPremium;
$teacher = $premiumTeachers[$currentIndex];
if ($canAssign($teacher, $schedule)) {
// Insert assignment.
$insertQuery = "INSERT INTO Teacher_Course_Assignments
(Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$insertStmt = $mysqli->prepare($insertQuery);
if (!$insertStmt) {
echo "Error preparing insert query: " . $mysqli->error . "
";
continue;
}
$assignedAt = date('Y-m-d H:i:s');
$insertStmt->bind_param("iiiiissss",
$teacher['Teacher_ID'],
$schedule['Course_ID'],
$schedule['Group_ID'],
$schedule['Program_ID'],
$schedule['Schedule_ID'],
$schedule['Time_Slot'],
$schedule['Start_Date'],
$schedule['End_Date'],
$assignedAt
);
if (!$insertStmt->execute()) {
echo "Error executing insert query: " . $insertStmt->error . "
";
$insertStmt->close();
continue;
}
$insertStmt->close();
// Mark schedule as assigned.
$updateQuery = "UPDATE Course_Group_Schedule SET Assigned = 1 WHERE Schedule_ID = ?";
$updateStmt = $mysqli->prepare($updateQuery);
if (!$updateStmt) {
echo "Error preparing update query: " . $mysqli->error . "
";
continue;
}
$updateStmt->bind_param("i", $schedule['Schedule_ID']);
if (!$updateStmt->execute()) {
echo "Error executing update query: " . $updateStmt->error . "
";
$updateStmt->close();
continue;
}
$updateStmt->close();
$ptrPremium = ($currentIndex + 1) % $cntPremium;
$assigned = true;
break;
}
}
}
// If not assigned by premium, try qualified teachers.
if (!$assigned && $cntQualified > 0) {
for ($i = 0; $i < $cntQualified; $i++) {
$currentIndex = ($ptrQualified + $i) % $cntQualified;
$teacher = $qualifiedTeachers[$currentIndex];
if ($canAssign($teacher, $schedule)) {
$insertQuery = "INSERT INTO Teacher_Course_Assignments
(Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$insertStmt = $mysqli->prepare($insertQuery);
if (!$insertStmt) {
echo "Error preparing insert query: " . $mysqli->error . "
";
continue;
}
$assignedAt = date('Y-m-d H:i:s');
$insertStmt->bind_param("iiiiissss",
$teacher['Teacher_ID'],
$schedule['Course_ID'],
$schedule['Group_ID'],
$schedule['Program_ID'],
$schedule['Schedule_ID'],
$schedule['Time_Slot'],
$schedule['Start_Date'],
$schedule['End_Date'],
$assignedAt
);
if (!$insertStmt->execute()) {
echo "Error executing insert query: " . $insertStmt->error . "
";
$insertStmt->close();
continue;
}
$insertStmt->close();
$updateQuery = "UPDATE Course_Group_Schedule SET Assigned = 1 WHERE Schedule_ID = ?";
$updateStmt = $mysqli->prepare($updateQuery);
if (!$updateStmt) {
echo "Error preparing update query: " . $mysqli->error . "
";
continue;
}
$updateStmt->bind_param("i", $schedule['Schedule_ID']);
if (!$updateStmt->execute()) {
echo "Error executing update query: " . $updateStmt->error . "
";
$updateStmt->close();
continue;
}
$updateStmt->close();
$ptrQualified = ($currentIndex + 1) % $cntQualified;
$assigned = true;
break;
}
}
}
// If still not assigned, try unqualified teachers.
if (!$assigned && $cntUnqualified > 0) {
for ($i = 0; $i < $cntUnqualified; $i++) {
$currentIndex = ($ptrUnqualified + $i) % $cntUnqualified;
$teacher = $unqualifiedTeachers[$currentIndex];
if ($canAssign($teacher, $schedule)) {
$insertQuery = "INSERT INTO Teacher_Course_Assignments
(Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$insertStmt = $mysqli->prepare($insertQuery);
if (!$insertStmt) {
echo "Error preparing insert query: " . $mysqli->error . "
";
continue;
}
$assignedAt = date('Y-m-d H:i:s');
$insertStmt->bind_param("iiiiissss",
$teacher['Teacher_ID'],
$schedule['Course_ID'],
$schedule['Group_ID'],
$schedule['Program_ID'],
$schedule['Schedule_ID'],
$schedule['Time_Slot'],
$schedule['Start_Date'],
$schedule['End_Date'],
$assignedAt
);
if (!$insertStmt->execute()) {
echo "Error executing insert query: " . $insertStmt->error . "
";
$insertStmt->close();
continue;
}
$insertStmt->close();
$updateQuery = "UPDATE Course_Group_Schedule SET Assigned = 1 WHERE Schedule_ID = ?";
$updateStmt = $mysqli->prepare($updateQuery);
if (!$updateStmt) {
echo "Error preparing update query: " . $mysqli->error . "
";
continue;
}
$updateStmt->bind_param("i", $schedule['Schedule_ID']);
if (!$updateStmt->execute()) {
echo "Error executing update query: " . $updateStmt->error . "
";
$updateStmt->close();
continue;
}
$updateStmt->close();
$ptrUnqualified = ($currentIndex + 1) % $cntUnqualified;
$assigned = true;
break;
}
}
}
}
// After processing, redirect or show a success message.
header('Location: generateschedule.php?error=Schedule generated successfully!');
exit();
}
*/
/**
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['Program_ID'], $_POST['Group_ID'])) {
// Get Program ID and Group ID from the submitted form
$Program_ID = $_POST['Program_ID'];
$Group_ID = $_POST['Group_ID'];
$Reserve_Course = isset($_POST['Reserve_Course']) ? 1 : 0;
// For demonstration, using fixed Program_ID and Group_ID (in your actual system, these come from the submitted form)
//$Program_ID = 1;
//$Group_ID = 13;
// -----------------------------
// STEP 1: Get all unassigned schedules for this Program
// -----------------------------
if($Group_ID != 0)
{
$scheduleQuery = "SELECT * FROM Course_Group_Schedule
WHERE Assigned = 0
AND Program_ID = ?
AND Group_ID = ?
AND Reserve_Course = ?
ORDER BY Time_Slot";
$stmt = $mysqli->prepare($scheduleQuery);
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Reserve_Course);
}else{
$scheduleQuery = "SELECT * FROM Course_Group_Schedule
WHERE Assigned = 0
AND Program_ID = ?
AND Reserve_Course = ?
ORDER BY Time_Slot";
$stmt = $mysqli->prepare($scheduleQuery);
$stmt->bind_param("ii", $Program_ID, $Reserve_Course);
}
if (!$stmt) {
die("Error preparing schedule query: " . $mysqli->error);
}
if (!$stmt->execute()) {
die("Error executing schedule query: " . $stmt->error);
}
$schedulesResult = $stmt->get_result();
$schedules = [];
while ($row = $schedulesResult->fetch_assoc()) {
$schedules[] = $row;
}
$stmt->close();
// -----------------------------
// STEP 2: Get teachers for this Program, combining Teacher_Profile and Top_Teacher_List.
// Ordering: Premium (top) teachers first, then teachers with Seniority_ID = 1 (qualified),
// then teachers with Seniority_ID = 2 (unqualified).
// -----------------------------
//$teacherQuery = "
// SELECT
// tp.Teacher_ID,
// tp.First_Name,
// tp.Last_Name,
// tp.Seniority_ID,
// tp.Time_Slot AS Availability,
// tp.Load_Hours,
// ttl.Top_Teacher_ID,
// ttl.Teacher_Level
// FROM Teacher_Profile tp
// LEFT JOIN Top_Teacher_List ttl
// ON tp.Teacher_ID = ttl.Teacher_ID
// AND ttl.Program_ID = ?
// WHERE 1
// ORDER BY
// CASE
// WHEN ttl.Top_Teacher_ID IS NOT NULL THEN 1
// WHEN tp.Seniority_ID = 1 THEN 2
// WHEN tp.Seniority_ID = 2 THEN 3
// ELSE 4
// END,
// tp.First_Name ASC,
// tp.Last_Name ASC
//";
//$stmt = $mysqli->prepare($teacherQuery);
//if (!$stmt) {
// die("Error preparing teacher query: " . $mysqli->error);
//}
//$stmt->bind_param("i", $Program_ID);
//if (!$stmt->execute()) {
// die("Error executing teacher query: " . $stmt->error);
//}
//$result = $stmt->get_result();
//$teachers = [];
//while ($row = $result->fetch_assoc()) {
// $teachers[] = $row;
//}
//$stmt->close();
//// Debug: Optional print teacher list
//foreach ($teachers as $teacher) {
// $isPremium = !empty($teacher['Top_Teacher_ID']) ? "Premium (Level: " . $teacher['Teacher_Level'] . ")" : "";
// echo $teacher['First_Name'] . " " . $teacher['Last_Name'] . " - Seniority: " . $teacher['Seniority_ID'] . " " . $isPremium . " - Load Hours: " . $teacher['Load_Hours'] . "
";
//}
// STEP 2: Get teachers for this Program by combining Teacher_Profile and Top_Teacher_List.
// (No filtering on Teacher_Profile by Program_ID because it's not in that table.)
$teacherQuery = "
SELECT
tp.Teacher_ID,
tp.First_Name,
tp.Last_Name,
tp.Seniority_ID,
tp.Time_Slot AS Availability,
tp.Load_Hours,
ttl.Top_Teacher_ID,
ttl.Teacher_Level
FROM Teacher_Profile tp
LEFT JOIN Top_Teacher_List ttl
ON tp.Teacher_ID = ttl.Teacher_ID
AND ttl.Program_ID = ?
WHERE 1
ORDER BY tp.First_Name ASC, tp.Last_Name ASC
";
$stmt = $mysqli->prepare($teacherQuery);
if (!$stmt) {
die("Error preparing teacher query: " . $mysqli->error);
}
$stmt->bind_param("i", $Program_ID);
$stmt->execute();
$result = $stmt->get_result();
$allTeachers = [];
while ($row = $result->fetch_assoc()) {
$allTeachers[] = $row;
}
$stmt->close();
// Initialize the round-robin pointer
$teacherCount = count($teachers);
$teacherIndex = 0;
// -----------------------------
// STEP 3: Loop through each unassigned schedule and try to assign a teacher
// -----------------------------
// Separate teachers into groups
$premiumTeachers = [];
$qualifiedTeachers = [];
$unqualifiedTeachers = [];
foreach ($allTeachers as $teacher) {
// Note: if Teacher_User_Access = 0, the teacher is not eligible;
// The check is performed later in the assignment function.
if (!empty($teacher['Top_Teacher_ID'])) {
$premiumTeachers[] = $teacher;
} elseif ($teacher['Seniority_ID'] == 1) {
$qualifiedTeachers[] = $teacher;
} elseif ($teacher['Seniority_ID'] == 2) {
$unqualifiedTeachers[] = $teacher;
}
}
// Initialize round-robin pointers for each group
$ptrPremium = 0;
$ptrQualified = 0;
$ptrUnqualified = 0;
$cntPremium = count($premiumTeachers);
$cntQualified = count($qualifiedTeachers);
$cntUnqualified = count($unqualifiedTeachers);
//// (Optional) Debug output: print ordered teacher groups
//echo "
Premium Teachers:
";
//foreach ($premiumTeachers as $t) {
// echo $t['First_Name']." ".$t['Last_Name']." (Level: ".$t['Teacher_Level'].")
";
//}
//echo "
Qualified Teachers:
";
//foreach ($qualifiedTeachers as $t) {
// echo $t['First_Name']." ".$t['Last_Name']." (Seniority: ".$t['Seniority_ID'].")
";
//}
//echo "
Unqualified Teachers:
";
//foreach ($unqualifiedTeachers as $t) {
// echo $t['First_Name']." ".$t['Last_Name']." (Seniority: ".$t['Seniority_ID'].")
";
//}
//echo "
";
// Helper inline: For each teacher, perform assignment checks.
// (Availability, conflict, load hours, preferences)
foreach ($schedules as $schedule) {
$assigned = false;
// Function to check if teacher can be assigned
// (We inline the checks here.)
$canAssign = function($teacher, $schedule) use ($mysqli, $Program_ID) {
// Check availability: Teacher's Availability field is a comma-separated list.
$availableSlots = array_map('trim', explode(',', $teacher['Availability']));
if (!in_array($schedule['Time_Slot'], $availableSlots)) {
return false;
}
// Check conflicts: any overlapping assignment in the same time slot.
$conflictQuery = "SELECT COUNT(*) AS cnt FROM Teacher_Course_Assignments
WHERE Teacher_ID = ?
AND Time_Slot = ?
AND (? <= End_Date AND ? >= Start_Date)";
$conflictStmt = $mysqli->prepare($conflictQuery);
if (!$conflictStmt) {
echo "Error preparing conflict query: " . $mysqli->error . "
";
return false;
}
$conflictStmt->bind_param("isss", $teacher['Teacher_ID'], $schedule['Time_Slot'], $schedule['Start_Date'], $schedule['End_Date']);
if (!$conflictStmt->execute()) {
echo "Error executing conflict query: " . $conflictStmt->error . "
";
$conflictStmt->close();
return false;
}
$conflictResult = $conflictStmt->get_result();
$conflictRow = $conflictResult->fetch_assoc();
$conflictCount = $conflictRow['cnt'];
$conflictStmt->close();
if ($conflictCount > 0) {
return false;
}
// Check load hours capacity:
// 1. Get teacher's current assigned hours.
$currentLoadQuery = "SELECT IFNULL(SUM(c.Course_Time), 0) AS total_hours
FROM Teacher_Course_Assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
WHERE tca.Teacher_ID = ?";
$currentLoadStmt = $mysqli->prepare($currentLoadQuery);
if (!$currentLoadStmt) {
echo "Error preparing current load query: " . $mysqli->error . "
";
return false;
}
$currentLoadStmt->bind_param("i", $teacher['Teacher_ID']);
$currentLoadStmt->execute();
$currentLoadResult = $currentLoadStmt->get_result();
$currentLoadRow = $currentLoadResult->fetch_assoc();
$currentLoad = $currentLoadRow['total_hours'];
$currentLoadStmt->close();
// 2. Get new course's hours.
$courseQuery = "SELECT Course_Time FROM Courses WHERE Course_ID = ?";
$courseStmt = $mysqli->prepare($courseQuery);
if (!$courseStmt) {
echo "Error preparing course query: " . $mysqli->error . "
";
return false;
}
$courseStmt->bind_param("i", $schedule['Course_ID']);
$courseStmt->execute();
$courseResult = $courseStmt->get_result();
$courseData = $courseResult->fetch_assoc();
$newCourseTime = $courseData['Course_Time'];
$courseStmt->close();
if (($currentLoad + $newCourseTime) > $teacher['Load_Hours']) {
return false;
}
// Check teacher's course preferences.
$prefQuery = "SELECT * FROM Teacher_Course_Preferences
WHERE Teacher_ID = ?
AND Course_ID = ?
AND Priority IN (1,2)
ORDER BY Priority ASC";
$prefStmt = $mysqli->prepare($prefQuery);
if (!$prefStmt) {
echo "Error preparing preference query: " . $mysqli->error . "
";
return false;
}
$prefStmt->bind_param("ii", $teacher['Teacher_ID'], $schedule['Course_ID']);
$prefStmt->execute();
$prefResult = $prefStmt->get_result();
$preferences = [];
while ($prefRow = $prefResult->fetch_assoc()) {
$preferences[] = $prefRow;
}
$prefStmt->close();
if (empty($preferences)) {
return false;
}
return true;
}; // end of anonymous function
// Try to assign from premium teachers first
if ($cntPremium > 0) {
for ($i = 0; $i < $cntPremium; $i++) {
$currentIndex = ($ptrPremium + $i) % $cntPremium;
$teacher = $premiumTeachers[$currentIndex];
if ($canAssign($teacher, $schedule)) {
// Assign this teacher
// Insert assignment
$insertQuery = "INSERT INTO Teacher_Course_Assignments
(Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$insertStmt = $mysqli->prepare($insertQuery);
if (!$insertStmt) {
echo "Error preparing insert query: " . $mysqli->error . "
";
continue;
}
$assignedAt = date('Y-m-d H:i:s');
$insertStmt->bind_param("iiiiissss",
$teacher['Teacher_ID'],
$schedule['Course_ID'],
$schedule['Group_ID'],
$schedule['Program_ID'],
$schedule['Schedule_ID'],
$schedule['Time_Slot'],
$schedule['Start_Date'],
$schedule['End_Date'],
$assignedAt
);
if (!$insertStmt->execute()) {
echo "Error executing insert query: " . $insertStmt->error . "
";
$insertStmt->close();
continue;
}
$insertStmt->close();
// Mark schedule as assigned.
$updateQuery = "UPDATE Course_Group_Schedule SET Assigned = 1 WHERE Schedule_ID = ?";
$updateStmt = $mysqli->prepare($updateQuery);
if (!$updateStmt) {
echo "Error preparing update query: " . $mysqli->error . "
";
continue;
}
$updateStmt->bind_param("i", $schedule['Schedule_ID']);
if (!$updateStmt->execute()) {
echo "Error executing update query: " . $updateStmt->error . "
";
$updateStmt->close();
continue;
}
$updateStmt->close();
// Update pointer for premium group.
$ptrPremium = ($currentIndex + 1) % $cntPremium;
$assigned = true;
break;
}
}
}
// If not assigned by premium, try qualified teachers.
if (!$assigned && $cntQualified > 0) {
for ($i = 0; $i < $cntQualified; $i++) {
$currentIndex = ($ptrQualified + $i) % $cntQualified;
$teacher = $qualifiedTeachers[$currentIndex];
if ($canAssign($teacher, $schedule)) {
$insertQuery = "INSERT INTO Teacher_Course_Assignments
(Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$insertStmt = $mysqli->prepare($insertQuery);
if (!$insertStmt) {
echo "Error preparing insert query: " . $mysqli->error . "
";
continue;
}
$assignedAt = date('Y-m-d H:i:s');
$insertStmt->bind_param("iiiiissss",
$teacher['Teacher_ID'],
$schedule['Course_ID'],
$schedule['Group_ID'],
$schedule['Program_ID'],
$schedule['Schedule_ID'],
$schedule['Time_Slot'],
$schedule['Start_Date'],
$schedule['End_Date'],
$assignedAt
);
if (!$insertStmt->execute()) {
echo "Error executing insert query: " . $insertStmt->error . "
";
$insertStmt->close();
continue;
}
$insertStmt->close();
$updateQuery = "UPDATE Course_Group_Schedule SET Assigned = 1 WHERE Schedule_ID = ?";
$updateStmt = $mysqli->prepare($updateQuery);
if (!$updateStmt) {
echo "Error preparing update query: " . $mysqli->error . "
";
continue;
}
$updateStmt->bind_param("i", $schedule['Schedule_ID']);
if (!$updateStmt->execute()) {
echo "Error executing update query: " . $updateStmt->error . "
";
$updateStmt->close();
continue;
}
$updateStmt->close();
$ptrQualified = ($currentIndex + 1) % $cntQualified;
$assigned = true;
break;
}
}
}
// If still not assigned, try unqualified teachers.
if (!$assigned && $cntUnqualified > 0) {
for ($i = 0; $i < $cntUnqualified; $i++) {
$currentIndex = ($ptrUnqualified + $i) % $cntUnqualified;
$teacher = $unqualifiedTeachers[$currentIndex];
if ($canAssign($teacher, $schedule)) {
$insertQuery = "INSERT INTO Teacher_Course_Assignments
(Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$insertStmt = $mysqli->prepare($insertQuery);
if (!$insertStmt) {
echo "Error preparing insert query: " . $mysqli->error . "
";
continue;
}
$assignedAt = date('Y-m-d H:i:s');
$insertStmt->bind_param("iiiiissss",
$teacher['Teacher_ID'],
$schedule['Course_ID'],
$schedule['Group_ID'],
$schedule['Program_ID'],
$schedule['Schedule_ID'],
$schedule['Time_Slot'],
$schedule['Start_Date'],
$schedule['End_Date'],
$assignedAt
);
if (!$insertStmt->execute()) {
echo "Error executing insert query: " . $insertStmt->error . "
";
$insertStmt->close();
continue;
}
$insertStmt->close();
$updateQuery = "UPDATE Course_Group_Schedule SET Assigned = 1 WHERE Schedule_ID = ?";
$updateStmt = $mysqli->prepare($updateQuery);
if (!$updateStmt) {
echo "Error preparing update query: " . $mysqli->error . "
";
continue;
}
$updateStmt->bind_param("i", $schedule['Schedule_ID']);
if (!$updateStmt->execute()) {
echo "Error executing update query: " . $updateStmt->error . "
";
$updateStmt->close();
continue;
}
$updateStmt->close();
$ptrUnqualified = ($currentIndex + 1) % $cntUnqualified;
$assigned = true;
break;
}
}
}
}
// After processing, redirect or show a success message
header('Location: generateschedule.php?error=Schedule generated successfully!');
exit();
}
*/
?>