/home/techb158/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/Manager/fetch_lockup_courses_1.php (13638B)
'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
$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();
$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
$stmt = $mysqli->prepare("SELECT COUNT(*) FROM teacher_course_preferences WHERE Teacher_ID = ? AND Course_ID = ? AND Program_ID = ?");
$stmt->bind_param("iii", $Teacher_ID, $schedule['Course_ID'], $schedule['Program_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 (same time slot, overlapping dates)
$stmt = $mysqli->prepare("SELECT COUNT(*) FROM teacher_course_assignments WHERE Teacher_ID = ? AND Time_Slot = ? AND Start_Date <= ? AND End_Date >= ?");
$stmt->bind_param("isss", $Teacher_ID, $schedule['Time_Slot'], $schedule['End_Date'], $schedule['Start_Date']);
$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 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 teacher load capacity
$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();
// Use schedule's own dates to scope load check
$semStartStr = $schedule['Start_Date'];
$semEndStr = $schedule['End_Date'];
$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($assignedHours);
$stmt->fetch();
$stmt->close();
$courseHours = (int)$schedule['Course_Hours'];
if (($assignedHours + $courseHours) > $loadHours) {
echo json_encode(['error' => "Cannot assign: teacher would exceed load capacity ($loadHours hrs). Already assigned: $assignedHours hrs, course: $courseHours hrs."]);
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 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()) {
$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;
}
// 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 = '';
}
// Get courses from teacher's preferences, showing assigned/unassigned status
$courses = [];
$stmt = $mysqli->prepare("
SELECT s.Schedule_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, s.Course_ID,
CASE WHEN tca.Teacher_ID IS NOT NULL THEN 1 ELSE 0 END AS is_assigned
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
JOIN Programs p ON s.Program_ID = p.Program_ID
JOIN teacher_course_preferences tcp ON tcp.Teacher_ID = ?
AND tcp.Course_ID = s.Course_ID
AND tcp.Program_ID = s.Program_ID
LEFT JOIN teacher_course_assignments tca ON tca.Schedule_ID = s.Schedule_ID
AND tca.Teacher_ID = ?
WHERE s.Start_Date >= ? AND s.End_Date <= ?
AND s.Assigned = 0
AND FIND_IN_SET(s.Time_Slot, ?) > 0
AND NOT EXISTS (
SELECT 1 FROM teacher_course_assignments tca2
WHERE tca2.Schedule_ID = s.Schedule_ID
AND tca2.Teacher_ID != ?
)
ORDER BY tcp.Priority ASC, s.Start_Date ASC
");
$stmt->bind_param("iisssi", $Teacher_ID, $Teacher_ID, $semStartStr, $semEndStr, $teacherTimeSlot, $Teacher_ID);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
$row['Current_Load'] = (int)$currentHours;
$row['Load_Hours'] = (int)$loadHours;
$courses[] = $row;
}
$stmt->close();
// Pre-fetch teacher's existing assignments for conflict checking
$existingAssignments = [];
$stmt = $mysqli->prepare("SELECT Time_Slot, Start_Date, End_Date FROM teacher_course_assignments WHERE Teacher_ID = ? AND Start_Date <= ? AND End_Date >= ?");
$stmt->bind_param("iss", $Teacher_ID, $semEndStr, $semStartStr);
$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 = ?");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$resultUA = $stmt->get_result();
while ($rowUA = $resultUA->fetch_assoc()) {
$unavailPeriods[] = $rowUA;
}
$stmt->close();
// Validate each course
$teacherSlotList = array_map('trim', explode(',', $teacherTimeSlot));
foreach ($courses as &$course) {
if ($course['is_assigned'] == 1) {
$course['valid'] = true;
$course['message'] = 'Already assigned';
continue;
}
if (empty($teacherTimeSlot) || !in_array($course['Time_Slot'], $teacherSlotList)) {
$course['valid'] = false;
$course['message'] = 'Teacher is not available during this course period.';
continue;
}
// Check for schedule conflict with existing assignments
$hasConflict = false;
foreach ($existingAssignments as $ea) {
if ($ea['Time_Slot'] === $course['Time_Slot']) {
if ($course['Start_Date'] <= $ea['End_Date'] && $course['End_Date'] >= $ea['Start_Date']) {
$hasConflict = true;
break;
}
}
}
if ($hasConflict) {
$course['valid'] = false;
$course['message'] = 'Teacher already has another course assigned during this time slot.';
continue;
}
// Check if teacher is unavailable during this period
$isUnavailable = false;
foreach ($unavailPeriods as $ua) {
if ($course['Start_Date'] <= $ua['Unavailable_To'] && $course['End_Date'] >= $ua['Unavailable_From']) {
$isUnavailable = true;
break;
}
}
if ($isUnavailable) {
$course['valid'] = false;
$course['message'] = 'Teacher is not available during this course period.';
continue;
}
// Check load capacity
if (($currentHours + $course['Course_Hours']) > $loadHours) {
$course['valid'] = false;
$course['message'] = 'Teacher load capacity would be exceeded.';
continue;
}
$course['valid'] = true;
$course['message'] = 'Teacher is available and can be assigned to this course period.';
}
unset($course);
echo json_encode(['courses' => $courses], JSON_UNESCAPED_UNICODE);