/home/techb158/workloadmatch.com/workloadmatch.com/Manager/Inc
Edit: /home/techb158/workloadmatch.com/workloadmatch.com/Manager/Inc/Assignment.php (74235B)
prepare("
SELECT IFNULL(SUM(c.Course_Time), 0) AS hrs
FROM Teacher_Course_Assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
WHERE tca.Teacher_ID = ?
");
$stmt->bind_param('i', $t['Teacher_ID']);
$stmt->execute();
$t['Current_Load'] = (int) $stmt->get_result()->fetch_assoc()['hrs'];
$stmt->close();
}
unset($t);
}
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['Program_ID'], $_POST['Group_ID'])) {
$Program_ID = (int) $_POST['Program_ID'];
$Group_ID = (int) $_POST['Group_ID'];
$Reserve_Course = isset($_POST['Reserve_Course']) ? 1 : 0;
// ----------------------------------------
// STEP 1: Load all unassigned schedules
// ----------------------------------------
if ($Group_ID !== 0) {
$sql = "
SELECT *
FROM Course_Group_Schedule_Main
WHERE Assigned = 0
AND Program_ID = ?
AND Group_ID = ?
AND Reserve_Course = ?
ORDER BY Time_Slot
";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('iii', $Program_ID, $Group_ID, $Reserve_Course);
} else {
$sql = "
SELECT *
FROM Course_Group_Schedule_Main
WHERE Assigned = 0
AND Program_ID = ?
AND Reserve_Course = ?
ORDER BY Time_Slot
";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('ii', $Program_ID, $Reserve_Course);
}
if (!$stmt->execute()) {
die("Error fetching schedules: " . $mysqli->error);
}
$schedules = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// ----------------------------------------
// STEP 2: Load all teachers and unavailability
// ----------------------------------------
$sql = "
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 = ?
";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $Program_ID);
$stmt->execute();
$allTeachers = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// attach unavailability slots to each teacher
foreach ($allTeachers as &$t) {
$t['Unavailability'] = [];
$stmt = $mysqli->prepare("
SELECT Unavailable_From, Unavailable_To
FROM Teacher_Unavailability
WHERE Teacher_ID = ?
");
$stmt->bind_param('i', $t['Teacher_ID']);
$stmt->execute();
$t['Unavailability'] = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
}
unset($t);
// ----------------------------------------
// STEP 3: Split into premium / qualified / unqualified
// ----------------------------------------
$premium = $qualified = $unqualified = [];
foreach ($allTeachers as $t) {
if (!empty($t['Top_Teacher_ID'])) {
$premium[] = $t;
} elseif ($t['Seniority_ID'] == 1) {
$qualified[] = $t;
} else {
$unqualified[] = $t;
}
}
// ----------------------------------------
// RANDOMIZE each group so assignment is fair
// ----------------------------------------
//shuffle($premium);
//shuffle($qualified);
//shuffle($unqualified);
///current load to balance better
calculateLoadHours($premium);
calculateLoadHours($qualified);
calculateLoadHours($unqualified);
// Sort by least load (then by Teacher_ID to stabilize tie-breaks)
usort($premium, fn($a, $b) => [$a['Current_Load'], $a['Teacher_ID']] <=> [$b['Current_Load'], $b['Teacher_ID']]);
usort($qualified, fn($a, $b) => [$a['Current_Load'], $a['Teacher_ID']] <=> [$b['Current_Load'], $b['Teacher_ID']]);
usort($unqualified, fn($a, $b) => [$a['Current_Load'], $a['Teacher_ID']] <=> [$b['Current_Load'], $b['Teacher_ID']]);
// ----------------------------------------
// HELPER: fetch preferred courses for a teacher
// ----------------------------------------
function getPreferredCourses($teacherID) {
global $mysqli;
$sql = "
SELECT Course_ID
FROM Teacher_Course_Preferences
WHERE Teacher_ID = ?
AND Priority IN (1,2)
";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $teacherID);
$stmt->execute();
$ids = array_column($stmt->get_result()->fetch_all(MYSQLI_ASSOC), 'Course_ID');
$stmt->close();
return $ids;
}
// ----------------------------------------
// FIND: courses only premium teachers prefer
// ----------------------------------------
$preferredByNonPremium = [];
foreach (array_merge($qualified, $unqualified) as $t) {
$preferredByNonPremium = array_merge(
$preferredByNonPremium,
getPreferredCourses($t['Teacher_ID'])
);
}
$preferredByNonPremium = array_unique($preferredByNonPremium);
$allCourseIDs = array_column($schedules, 'Course_ID');
$premiumOnlyCourses = array_diff($allCourseIDs, $preferredByNonPremium);
// ----------------------------------------
// CAN_ASSIGN: checks availability / load / prefs / conflicts
// ----------------------------------------
$canAssign = function(array $teacher, array $sched) use ($mysqli): bool {
// 1) User access
if ($teacher['User_Access'] == 0) return false;
// 2) Unavailability overlap
$schStart = strtotime($sched['Start_Date']);
$schEnd = strtotime($sched['End_Date']);
foreach ($teacher['Unavailability'] as $u) {
if ($schStart <= strtotime($u['Unavailable_To']) && $schEnd >= strtotime($u['Unavailable_From'])) {
return false;
}
}
// 3) Timeslot match
$slots = array_map('trim', explode(',', $teacher['Availability']));
if (!in_array($sched['Time_Slot'], $slots, true)) return false;
// 4) Conflict: overlapping assignments
$sql = "
SELECT COUNT(*) AS cnt
FROM Teacher_Course_Assignments
WHERE Teacher_ID = ?
AND Time_Slot = ?
AND (? <= End_Date AND ? >= Start_Date)
";
$stm = $mysqli->prepare($sql);
$stm->bind_param('isss', $teacher['Teacher_ID'], $sched['Time_Slot'], $sched['Start_Date'], $sched['End_Date']);
$stm->execute();
if ($stm->get_result()->fetch_assoc()['cnt'] > 0) {
$stm->close();
return false;
}
$stm->close();
// 5) NEW RULE: no multiple classes with same Group_ID on same day
$sql = "
SELECT COUNT(*) AS cnt
FROM Teacher_Course_Assignments
WHERE Teacher_ID = ?
AND Group_ID = ?
AND ? BETWEEN Start_Date AND End_Date
";
$stm = $mysqli->prepare($sql);
$stm->bind_param('iis', $teacher['Teacher_ID'], $sched['Group_ID'], $sched['Start_Date']);
$stm->execute();
if ($stm->get_result()->fetch_assoc()['cnt'] > 0) {
$stm->close();
return false;
}
$stm->close();
// 6) Load Hours Check
$sql = "
SELECT IFNULL(SUM(c.Course_Time), 0) AS hrs
FROM Teacher_Course_Assignments tca
JOIN Courses c USING(Course_ID)
WHERE tca.Teacher_ID = ?
";
$stm = $mysqli->prepare($sql);
$stm->bind_param('i', $teacher['Teacher_ID']);
$stm->execute();
$current = (int) $stm->get_result()->fetch_assoc()['hrs'];
$stm->close();
$stm = $mysqli->prepare("SELECT Course_Time FROM Courses WHERE Course_ID = ?");
$stm->bind_param('i', $sched['Course_ID']);
$stm->execute();
$toAdd = (int) $stm->get_result()->fetch_assoc()['Course_Time'];
$stm->close();
if ($current + $toAdd > $teacher['Load_Hours']) return false;
// 7) Preferences
$prefs = getPreferredCourses($teacher['Teacher_ID']);
//// 8) Limit: no more than 3 courses with the same group
//$sql = "
// SELECT COUNT(DISTINCT Course_ID) AS cnt
// FROM Teacher_Course_Assignments
// WHERE Teacher_ID = ?
// AND Group_ID = ?
//";
//$stm = $mysqli->prepare($sql);
//$stm->bind_param('ii', $teacher['Teacher_ID'], $sched['Group_ID']);
//$stm->execute();
//if ($stm->get_result()->fetch_assoc()['cnt'] >= 3) {
// $stm->close();
// return false;
//}
//$stm->close();
// 9) Prevent consecutive assignments with same group
//Explanation:
//If the teacher just finished a course with that group (End_Date close to new Start_Date)
//And there’s less than 3 days gap
//➡️ Do not assign the next course
$sql = "
SELECT End_Date
FROM Teacher_Course_Assignments
WHERE Teacher_ID = ?
AND Group_ID = ?
ORDER BY End_Date DESC
LIMIT 1
";
$stm = $mysqli->prepare($sql);
$stm->bind_param('ii', $teacher['Teacher_ID'], $sched['Group_ID']);
$stm->execute();
$res = $stm->get_result();
if ($res->num_rows > 0) {
$lastEnd = $res->fetch_assoc()['End_Date'];
$gap = (strtotime($sched['Start_Date']) - strtotime($lastEnd)) / (60 * 60 * 24);
//💡 Don't Skip the back-to-back rule also Premiums.
//if ($gap <= 2) {
// $stm->close();
// return false;
//}
//💡 Skip the back-to-back rule only for Premiums.
if ($gap <= 2) {
// Allow Premium teachers to bypass the rule
if (isset($teacher['Top_Teacher_ID']) && !empty($teacher['Top_Teacher_ID'])) {
// Allow assignment Premium can proceed
} else {
$stm->close();
return false;
}
}
}
$stm->close();
// ✅ If all checks pass, return true
return in_array($sched['Course_ID'], $prefs, true);
};
// ----------------------------------------
// ASSIGN_WRAPPER: round-robin assign
// ----------------------------------------
function assignFromList(array &$list, int &$ptr, array $sched, int $Reserve_Course): bool {
global $canAssign, $mysqli;
$n = count($list);
if ($n === 0) {
return false;
}
for ($i = 0; $i < $n; $i++) {
$idx = ($ptr + $i) % $n;
$t = $list[$idx];
if (! $canAssign($t, $sched)) {
continue;
}
// restriction check
$chk = $mysqli->prepare("
SELECT 1
FROM Teacher_Restricted_Groups
WHERE Teacher_ID = ?
AND Group_ID = ?
");
$chk->bind_param('ii', $t['Teacher_ID'], $sched['Group_ID']);
$chk->execute();
if ($chk->get_result()->num_rows > 0) {
$chk->close();
continue;
}
$chk->close();
// insert assignment
$ins = $mysqli->prepare("
INSERT INTO Teacher_Course_Assignments
(Teacher_ID, Course_ID, Group_ID, Program_ID,
Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW())
");
$ins->bind_param(
'iiiiisss',
$t['Teacher_ID'],
$sched['Course_ID'],
$sched['Group_ID'],
$sched['Program_ID'],
$sched['Schedule_ID'],
$sched['Time_Slot'],
$sched['Start_Date'],
$sched['End_Date']
);
$ins->execute();
$ins->close();
// mark schedule assigned
$upd = $mysqli->prepare("
UPDATE Course_Group_Schedule_Main
SET Assigned = 1
WHERE Schedule_ID = ?
");
$upd->bind_param('i', $sched['Schedule_ID']);
$upd->execute();
$upd->close();
$upd = $mysqli->prepare("
UPDATE Schedule_Course_for_Group
SET Assigned = 1
WHERE Schedule_ID = ?
");
$upd->bind_param('i', $sched['Schedule_ID']);
$upd->execute();
$upd->close();
// advance pointer
$ptr = ($idx + 1) % $n;
return true;
}
return false;
}
// ----------------------------------------
// PHASE 1: assign premium-only courses
// ----------------------------------------
$ptrP = $ptrQ = $ptrU = 0;
foreach ($schedules as $i => $sched) {
if (in_array($sched['Course_ID'], $premiumOnlyCourses, true)) {
assignFromList($premium, $ptrP, $sched, $sched['Reserve_Course']);
// remove from list so PHASE 2 won't try it again
unset($schedules[$i]);
}
}
// reindex
$schedules = array_values($schedules);
// ----------------------------------------
// PHASE 2: assign all remaining courses
// ----------------------------------------
foreach ($schedules as $sched) {
$assigned = assignFromList($premium, $ptrP, $sched, $sched['Reserve_Course']) ||
assignFromList($qualified, $ptrQ, $sched, $sched['Reserve_Course']) ||
assignFromList($unqualified, $ptrU, $sched, $sched['Reserve_Course']);
if (! $assigned) {
// log/report any that failed
error_log("Schedule {$sched['Schedule_ID']} not assigned: no eligible teacher");
}
}
// ----------------------------------------
// DONE: redirect with success message
// ----------------------------------------
header('Location: generateschedule.php?error=Schedule generated successfully!');
exit;
}
/********Good Code Orginal****************
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['Program_ID'], $_POST['Group_ID'])) {
$Program_ID = $_POST['Program_ID'];
$Group_ID = $_POST['Group_ID'];
$Reserve_Course = isset($_POST['Reserve_Course']) ? 1 : 0;
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 || !$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];
// ✅ Step 2: Check if the teacher can be assigned (availability, preference, etc.)
if ($canAssign($teacher, $schedule)) {
// ✅ Step 1: Check if the teacher is restricted from this group
$restrictionCheck = $mysqli->prepare("SELECT 1 FROM Teacher_Restricted_Groups WHERE Teacher_ID = ? AND Group_ID = ?");
//$restrictionCheck = $mysqli->prepare("SELECT 1 FROM Teacher_Restricted_Groups WHERE Teacher_ID = ? AND Group_ID = ?");
if (!$restrictionCheck) {
die("Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error);
}
$restrictionCheck->bind_param("ii", $teacher['Teacher_ID'], $schedule['Group_ID']);
$restrictionCheck->execute();
$restrictionCheck->store_result();
// If the teacher is restricted, skip to the next
if ($restrictionCheck->num_rows > 0) {
$restrictionCheck->close();
continue;
}
$restrictionCheck->close();
$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();
}
*/
?>