/home/techb158/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/Manager/fetch_lockup_courses_bak.php (8899B)
'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 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();
echo json_encode(['courses' => $courses], JSON_UNESCAPED_UNICODE);