/home/techb158/workloadmatch.com/Manager/Inc
Edit: /home/techb158/workloadmatch.com/Manager/Inc/TeacherAssignmentEngine.php (12487B)
db = $db;
}
public function loadUnassignedSchedules(int $programId, int $groupId, int $reserveCourse): array {
if ($groupId !== 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 = $this->db->prepare($sql);
$stmt->bind_param('iii', $programId, $groupId, $reserveCourse);
} else {
$sql = "
SELECT *
FROM course_group_schedule_main
WHERE Assigned = 0
AND Program_ID = ?
AND Reserve_Course = ?
ORDER BY Time_Slot
";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('ii', $programId, $reserveCourse);
}
if (!$stmt->execute()) {
throw new RuntimeException("Error fetching schedules: " . $this->db->error);
}
$schedules = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
return $schedules;
}
public function loadAvailableTeachers(int $programId): array {
$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 = $this->db->prepare($sql);
$stmt->bind_param('i', $programId);
$stmt->execute();
$allTeachers = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
foreach ($allTeachers as &$t) {
$t['Unavailability'] = [];
$stmt = $this->db->prepare("
SELECT Unavailable_From, Unavailable_To
FROM teacher_unavailability
WHERE Teacher_ID = ? AND status = 'confirmed'
");
$stmt->bind_param('i', $t['Teacher_ID']);
$stmt->execute();
$t['Unavailability'] = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
}
unset($t);
return $allTeachers;
}
public function calculateLoadHours(array &$teachers): void {
foreach ($teachers as &$t) {
$stmt = $this->db->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);
}
public function getPreferredCourses(int $teacherId): array {
$stmt = $this->db->prepare("
SELECT Course_ID
FROM teacher_course_preferences
WHERE Teacher_ID = ?
AND Priority IN (1,2)
");
$stmt->bind_param('i', $teacherId);
$stmt->execute();
$ids = array_column($stmt->get_result()->fetch_all(MYSQLI_ASSOC), 'Course_ID');
$stmt->close();
return $ids;
}
public function canAssign(array $teacher, array $sched): bool {
if ($teacher['User_Access'] == 0) return false;
$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;
}
}
$slots = array_map('trim', explode(',', $teacher['Availability']));
if (!in_array($sched['Time_Slot'], $slots, true)) return false;
$stm = $this->db->prepare("
SELECT COUNT(*) AS cnt
FROM teacher_course_assignments
WHERE Teacher_ID = ?
AND Time_Slot = ?
AND (? <= End_Date AND ? >= Start_Date)
");
$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();
$stm = $this->db->prepare("
SELECT COUNT(*) AS cnt
FROM teacher_course_assignments
WHERE Teacher_ID = ?
AND Group_ID = ?
AND ? BETWEEN Start_Date AND End_Date
");
$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();
$stm = $this->db->prepare("
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->bind_param('i', $teacher['Teacher_ID']);
$stm->execute();
$current = (int)$stm->get_result()->fetch_assoc()['hrs'];
$stm->close();
$stm = $this->db->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;
$prefs = $this->getPreferredCourses($teacher['Teacher_ID']);
$stm = $this->db->prepare("
SELECT End_Date
FROM teacher_course_assignments
WHERE Teacher_ID = ?
AND Group_ID = ?
ORDER BY End_Date DESC
LIMIT 1
");
$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);
if ($gap <= 2) {
if (empty($teacher['Top_Teacher_ID'])) {
$stm->close();
return false;
}
}
}
$stm->close();
return in_array($sched['Course_ID'], $prefs, true);
}
private function assignFromList(array &$list, int &$ptr, array $sched): bool {
$n = count($list);
if ($n === 0) return false;
for ($i = 0; $i < $n; $i++) {
$idx = ($ptr + $i) % $n;
$t = $list[$idx];
if (!$this->canAssign($t, $sched)) continue;
$chk = $this->db->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();
$ins = $this->db->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();
$upd = $this->db->prepare("UPDATE course_group_schedule_main SET Assigned = 1 WHERE Schedule_ID = ?");
$upd->bind_param('i', $sched['Schedule_ID']);
$upd->execute();
$upd->close();
$upd = $this->db->prepare("UPDATE schedule_course_for_group SET Assigned = 1 WHERE Schedule_ID = ?");
$upd->bind_param('i', $sched['Schedule_ID']);
$upd->execute();
$upd->close();
$ptr = ($idx + 1) % $n;
return true;
}
return false;
}
public function assignTeachers(int $programId, int $groupId, int $reserveCourse = 0): array {
$schedules = $this->loadUnassignedSchedules($programId, $groupId, $reserveCourse);
$allTeachers = $this->loadAvailableTeachers($programId);
$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;
}
}
$this->calculateLoadHours($premium);
$this->calculateLoadHours($qualified);
$this->calculateLoadHours($unqualified);
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']]);
$preferredByNonPremium = [];
foreach (array_merge($qualified, $unqualified) as $t) {
$preferredByNonPremium = array_merge($preferredByNonPremium, $this->getPreferredCourses($t['Teacher_ID']));
}
$preferredByNonPremium = array_unique($preferredByNonPremium);
$allCourseIDs = array_column($schedules, 'Course_ID');
$premiumOnlyCourses = array_diff($allCourseIDs, $preferredByNonPremium);
$ptrP = $ptrQ = $ptrU = 0;
$results = [];
foreach ($premiumOnlyCourses as $premiumOnlyId) {
foreach ($schedules as $i => $sched) {
if ($sched['Course_ID'] === $premiumOnlyId) {
$assigned = $this->assignFromList($premium, $ptrP, $sched);
$results[] = [
'schedule_id' => $sched['Schedule_ID'],
'course_id' => $sched['Course_ID'],
'group_id' => $sched['Group_ID'],
'assigned' => $assigned,
'tier' => 'premium'
];
unset($schedules[$i]);
break;
}
}
}
$schedules = array_values($schedules);
foreach ($schedules as $sched) {
$assigned = false;
$tier = null;
if ($this->assignFromList($premium, $ptrP, $sched)) {
$assigned = true;
$tier = 'premium';
} elseif ($this->assignFromList($qualified, $ptrQ, $sched)) {
$assigned = true;
$tier = 'qualified';
} elseif ($this->assignFromList($unqualified, $ptrU, $sched)) {
$assigned = true;
$tier = 'unqualified';
}
$results[] = [
'schedule_id' => $sched['Schedule_ID'],
'course_id' => $sched['Course_ID'],
'group_id' => $sched['Group_ID'],
'assigned' => $assigned,
'tier' => $tier
];
if (!$assigned) {
error_log("Schedule {$sched['Schedule_ID']} not assigned: no eligible teacher");
}
}
return $results;
}
}