/home/techb158/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/Manager/fetch_lockup_courses.php (14587B)
'Unauthorized']);
exit;
}
$Manager_ID = get_effective_id();
$action = $_POST['action'] ?? '';
// Handle assignment action
if ($action === 'assign') {
$Teacher_ID = (int)($_POST['Teacher_ID'] ?? 0);
$Schedule_ID = (int)($_POST['Schedule_ID'] ?? 0);
if (!$Teacher_ID || !$Schedule_ID) {
echo json_encode(['error' => 'Missing parameters']);
exit;
}
// Get schedule details
$stmt = $mysqli->prepare("
SELECT s.Group_ID, s.Course_ID, s.Program_ID, c.Course_Name, s.Time_Slot, s.Start_Date, s.End_Date,
s.Course_Hours, g.Group_Name
FROM course_group_schedule_main s
JOIN Courses c ON s.Course_ID = c.Course_ID
JOIN manager_group_name g ON s.Group_ID = g.Group_ID
WHERE s.Schedule_ID = ?
");
$stmt->bind_param("i", $Schedule_ID);
$stmt->execute();
$schedule = $stmt->get_result()->fetch_assoc();
$stmt->close();
if (!$schedule) {
echo json_encode(['error' => 'Schedule not found']);
exit;
}
// Check teacher's time slot availability (same as fetch_teacher_conflicts_only.php: skip if no slots)
$stmt = $mysqli->prepare("SELECT Time_Slot FROM teacher_profile WHERE Teacher_ID = ?");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$stmt->bind_result($teacherTS);
$stmt->fetch();
$stmt->close();
$teacherTS = trim($teacherTS ?? '');
if ($teacherTS !== '') {
$teacherSlotList = array_map('trim', explode(',', $teacherTS));
if (!in_array($schedule['Time_Slot'], $teacherSlotList)) {
echo json_encode(['error' => 'Teacher is not available during this course period.']);
exit;
}
}
// Check if teacher prefers this course (same as fetch_teacher_conflicts_only.php: Course_ID only)
$stmt = $mysqli->prepare("SELECT COUNT(*) FROM teacher_course_preferences WHERE Teacher_ID = ? AND Course_ID = ?");
$stmt->bind_param("ii", $Teacher_ID, $schedule['Course_ID']);
$stmt->execute();
$stmt->bind_result($prefCount);
$stmt->fetch();
$stmt->close();
if ($prefCount == 0) {
echo json_encode(['error' => 'This course period does not match the teacher\'s preferred time slot.']);
exit;
}
// Check for scheduling conflict (allow same course/group/program for AM/PM slots)
$stmt = $mysqli->prepare("SELECT COUNT(*) FROM teacher_course_assignments WHERE Teacher_ID = ? AND (? <= End_Date AND ? >= Start_Date) AND NOT (Course_ID = ? AND Group_ID = ? AND Program_ID = ?)");
$stmt->bind_param("issiii", $Teacher_ID, $schedule['End_Date'], $schedule['Start_Date'], $schedule['Course_ID'], $schedule['Group_ID'], $schedule['Program_ID']);
$stmt->execute();
$stmt->bind_result($conflictCount);
$stmt->fetch();
$stmt->close();
if ($conflictCount > 0) {
echo json_encode(['error' => 'Teacher already has another course assigned during this time slot.']);
exit;
}
// Check if teacher is unavailable during this period
$stmt = $mysqli->prepare("SELECT COUNT(*) FROM teacher_unavailability WHERE Teacher_ID = ? AND status = 'confirmed' AND Unavailable_From <= ? AND Unavailable_To >= ?");
$stmt->bind_param("iss", $Teacher_ID, $schedule['End_Date'], $schedule['Start_Date']);
$stmt->execute();
$stmt->bind_result($unavailCount);
$stmt->fetch();
$stmt->close();
if ($unavailCount > 0) {
echo json_encode(['error' => 'Teacher is not available during this course period.']);
exit;
}
// Check if already assigned to this schedule
$stmt = $mysqli->prepare("SELECT COUNT(*) FROM teacher_course_assignments WHERE Schedule_ID = ? AND Teacher_ID = ?");
$stmt->bind_param("ii", $Schedule_ID, $Teacher_ID);
$stmt->execute();
$stmt->bind_result($alreadyAssigned);
$stmt->fetch();
$stmt->close();
if ($alreadyAssigned > 0) {
echo json_encode(['error' => 'This course is already assigned to this teacher.']);
exit;
}
// Insert assignment
$stmt = $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())");
$stmt->bind_param("iiiiisss", $Teacher_ID, $schedule['Course_ID'], $schedule['Group_ID'], $schedule['Program_ID'], $Schedule_ID, $schedule['Time_Slot'], $schedule['Start_Date'], $schedule['End_Date']);
if ($stmt->execute()) {
// Update course_group_schedule_main
$stmt1 = $mysqli->prepare("UPDATE course_group_schedule_main SET Assigned = 1 WHERE Schedule_ID = ?");
$stmt1->bind_param("i", $Schedule_ID);
$stmt1->execute();
$stmt1->close();
// Update schedule_course_for_group
$stmt2 = $mysqli->prepare("UPDATE schedule_course_for_group SET Assigned = 1 WHERE Schedule_ID = ?");
$stmt2->bind_param("i", $Schedule_ID);
$stmt2->execute();
$stmt2->close();
echo json_encode(['status' => 'success', 'message' => 'Course assigned successfully.']);
} else {
echo json_encode(['error' => 'Failed to assign: ' . $stmt->error]);
}
$stmt->close();
exit;
}
// Handle unassign action
if ($action === 'unassign') {
$Teacher_ID = (int)($_POST['Teacher_ID'] ?? 0);
$Schedule_ID = (int)($_POST['Schedule_ID'] ?? 0);
if (!$Teacher_ID || !$Schedule_ID) {
echo json_encode(['error' => 'Missing parameters']);
exit;
}
$stmt = $mysqli->prepare("DELETE FROM teacher_course_assignments WHERE Schedule_ID = ? AND Teacher_ID = ?");
$stmt->bind_param("ii", $Schedule_ID, $Teacher_ID);
if ($stmt->execute()) {
$stmt1 = $mysqli->prepare("UPDATE course_group_schedule_main SET Assigned = 0 WHERE Schedule_ID = ?");
$stmt1->bind_param("i", $Schedule_ID);
$stmt1->execute();
$stmt1->close();
$stmt2 = $mysqli->prepare("UPDATE schedule_course_for_group SET Assigned = 0 WHERE Schedule_ID = ?");
$stmt2->bind_param("i", $Schedule_ID);
$stmt2->execute();
$stmt2->close();
echo json_encode(['status' => 'success', 'message' => 'Course unassigned successfully.']);
} else {
echo json_encode(['error' => 'Failed to unassign: ' . $stmt->error]);
}
$stmt->close();
exit;
}
// Debug: return all unassigned schedules
if ($action === 'debug_all') {
$courses = [];
$stmt = $mysqli->prepare("SELECT s.Schedule_ID, c.Course_ID, c.Course_Name, s.Time_Slot, s.Start_Date, s.End_Date, COALESCE(g.Group_Name, 'MISSING_GROUP') AS Group_Name, s.Course_Hours, s.Group_ID, s.Program_ID FROM course_group_schedule_main s JOIN Courses c ON s.Course_ID = c.Course_ID LEFT JOIN manager_group_name g ON s.Group_ID = g.Group_ID WHERE s.Assigned = 0 ORDER BY s.Start_Date ASC");
$stmt->execute();
$resultAll = $stmt->get_result();
while ($row = $resultAll->fetch_assoc()) {
$courses[] = $row;
}
$stmt->close();
echo json_encode(['debug_count' => count($courses), 'courses' => $courses]);
exit;
}
// Load matching courses
$Teacher_ID = (int)($_POST['Teacher_ID'] ?? 0);
$semesterYear = (int)($_POST['semester_year'] ?? 0);
if (!$Teacher_ID || !$semesterYear) {
echo json_encode(['error' => 'Missing parameters', 'courses' => []]);
exit;
}
// Semester dates
$augustFirst = new DateTime("$semesterYear-08-01");
$dayOfWeek = (int)$augustFirst->format('N');
$daysUntilMonday = ($dayOfWeek === 1) ? 0 : (8 - $dayOfWeek) % 7;
$semesterStart = clone $augustFirst;
$semesterStart->modify("+$daysUntilMonday days");
$semesterEnd = new DateTime(($semesterYear + 1) . '-06-30');
$semStartStr = $semesterStart->format('Y-m-d');
$semEndStr = $semesterEnd->format('Y-m-d');
// Get teacher's load
$stmt = $mysqli->prepare("SELECT Load_Hours FROM teacher_profile WHERE Teacher_ID = ?");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$stmt->bind_result($loadHours);
$stmt->fetch();
$stmt->close();
// Get current assigned hours for this teacher (current semester only)
$stmt = $mysqli->prepare("
SELECT IFNULL(SUM(uni.Course_Hours), 0) AS AssignedHours
FROM (
SELECT tca.Teacher_ID, cgs.Program_ID, cgs.Group_ID, cgs.Course_ID, MAX(cgs.Course_Hours) AS Course_Hours
FROM teacher_course_assignments tca
JOIN course_group_schedule_main cgs ON tca.Schedule_ID = cgs.Schedule_ID
WHERE tca.Teacher_ID = ?
AND cgs.Start_Date >= ? AND cgs.End_Date <= ?
GROUP BY tca.Teacher_ID, cgs.Program_ID, cgs.Group_ID, cgs.Course_ID
) AS uni
");
$stmt->bind_param("iss", $Teacher_ID, $semStartStr, $semEndStr);
$stmt->execute();
$stmt->bind_result($currentHours);
$stmt->fetch();
$stmt->close();
// Get teacher's available Time_Slot
$stmt = $mysqli->prepare("SELECT Time_Slot FROM teacher_profile WHERE Teacher_ID = ?");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$stmt->bind_result($teacherTimeSlot);
$stmt->fetch();
$stmt->close();
if ($teacherTimeSlot === null || trim($teacherTimeSlot) === '') {
$teacherTimeSlot = '';
}
// Pre-fetch teacher's preferred Course_IDs (matching fetch_teacher_conflicts_only.php: Course_ID only, no Program_ID)
$preferredCourseIDs = [];
$stmt = $mysqli->prepare("SELECT DISTINCT Course_ID FROM teacher_course_preferences WHERE Teacher_ID = ?");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$resultPref = $stmt->get_result();
while ($rowPref = $resultPref->fetch_assoc()) {
$preferredCourseIDs[] = $rowPref['Course_ID'];
}
$stmt->close();
// Pre-fetch ALL teacher's existing assignments for conflict checking (no date filter, matching fetch_teacher_conflicts_only.php)
$existingAssignments = [];
$stmt = $mysqli->prepare("SELECT Schedule_ID, Time_Slot, Start_Date, End_Date FROM teacher_course_assignments WHERE Teacher_ID = ?");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$resultEA = $stmt->get_result();
while ($rowEA = $resultEA->fetch_assoc()) {
$existingAssignments[] = $rowEA;
}
$stmt->close();
// Pre-fetch teacher's unavailability periods
$unavailPeriods = [];
$stmt = $mysqli->prepare("SELECT Unavailable_From, Unavailable_To FROM teacher_unavailability WHERE Teacher_ID = ? AND status = 'confirmed'");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$resultUA = $stmt->get_result();
while ($rowUA = $resultUA->fetch_assoc()) {
$unavailPeriods[] = $rowUA;
}
$stmt->close();
// Get all courses: unassigned schedules + schedules already assigned to this teacher
$courses = [];
$stmt = $mysqli->prepare("
SELECT s.Schedule_ID, c.Course_ID, c.Course_Name, s.Time_Slot, s.Start_Date, s.End_Date,
g.Group_Name, p.Program_Name, s.Course_Hours,
s.Group_ID, s.Program_ID
FROM course_group_schedule_main s
JOIN Courses c ON s.Course_ID = c.Course_ID
LEFT JOIN manager_group_name g ON s.Group_ID = g.Group_ID
LEFT JOIN Programs p ON s.Program_ID = p.Program_ID
WHERE s.Assigned = 0
OR s.Schedule_ID IN (SELECT Schedule_ID FROM teacher_course_assignments WHERE Teacher_ID = ?)
ORDER BY s.Start_Date ASC
");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$resultAll = $stmt->get_result();
$teacherSlotList = array_map('trim', explode(',', $teacherTimeSlot));
$debug_skipped = ['pref' => [], 'timeslot' => [], 'conflict' => [], 'unavail' => []];
$debug_included = [];
while ($row = $resultAll->fetch_assoc()) {
$scheduleID = (int)$row['Schedule_ID'];
$courseID = (int)$row['Course_ID'];
$cname = $row['Course_Name'] . ' (#' . $courseID . ')';
// Determine if THIS teacher is already assigned to this schedule
$is_assigned = 0;
foreach ($existingAssignments as $ea) {
if ((int)$ea['Schedule_ID'] === $scheduleID) {
$is_assigned = 1;
break;
}
}
// Already assigned courses: always show with Unassign (skip validation)
if ($is_assigned == 1) {
$row['is_assigned'] = 1;
$row['Current_Load'] = (int)$currentHours;
$row['Load_Hours'] = (int)$loadHours;
$row['valid'] = true;
$row['message'] = 'Already assigned';
$courses[] = $row;
continue;
}
// For new assignments: run validation matching fetch_teacher_conflicts_only.php
// Check 1: Teacher preference (same as fetch_teacher_conflicts_only.php: Course_ID only)
if (!in_array($courseID, $preferredCourseIDs)) {
$debug_skipped['pref'][] = $cname;
continue;
}
// Check 2: Time slot match (same as fetch_teacher_conflicts_only.php: skip if teacher has no slots)
if ($teacherTimeSlot !== '') {
if (!in_array($row['Time_Slot'], $teacherSlotList)) {
$debug_skipped['timeslot'][] = $cname;
continue;
}
}
// Check 3: Conflict with existing assignments (any time slot, overlapping dates)
$hasConflict = false;
foreach ($existingAssignments as $ea) {
if ($row['Start_Date'] <= $ea['End_Date'] && $row['End_Date'] >= $ea['Start_Date']) {
$hasConflict = true;
break;
}
}
if ($hasConflict) {
$debug_skipped['conflict'][] = $cname;
continue;
}
// Check 4: Teacher unavailability (matching fetch_teacher_conflicts_only.php)
$isUnavailable = false;
foreach ($unavailPeriods as $ua) {
if ($row['Start_Date'] <= $ua['Unavailable_To'] && $row['End_Date'] >= $ua['Unavailable_From']) {
$isUnavailable = true;
break;
}
}
if ($isUnavailable) {
$debug_skipped['unavail'][] = $cname;
continue;
}
// Passed all checks
$debug_included[] = $cname;
$row['is_assigned'] = 0;
$row['Current_Load'] = (int)$currentHours;
$row['Load_Hours'] = (int)$loadHours;
$row['valid'] = true;
$row['message'] = 'Teacher is available and can be assigned to this course period.';
$courses[] = $row;
}
$stmt->close();
echo json_encode([
'courses' => $courses,
'_debug' => [
'pref_skipped' => $debug_skipped['pref'],
'timeslot_skipped' => $debug_skipped['timeslot'],
'conflict_skipped' => $debug_skipped['conflict'],
'unavail_skipped' => $debug_skipped['unavail'],
'included' => $debug_included
],
'_pref_count' => count($preferredCourseIDs),
'_total_sql' => $resultAll->num_rows
], JSON_UNESCAPED_UNICODE);