/home/techb158/workloadmatch.com/api/routes
Edit: /home/techb158/workloadmatch.com/api/routes/assignments.php (9931B)
prepare($sql);
if ($params) $stmt->bind_param($types, ...$params);
$stmt->execute();
$stmt->bind_result($total);
$stmt->fetch();
$stmt->close();
$sql = "SELECT tca.*, c.Course_Name, c.Course_Code, tp.First_Name AS Teacher_First, tp.Last_Name AS Teacher_Last, g.Group_Name
FROM teacher_course_assignments tca
LEFT JOIN Courses c ON c.Course_ID = tca.Course_ID
LEFT JOIN teacher_profile tp ON tp.Teacher_ID = tca.Teacher_ID
LEFT JOIN manager_group_name g ON g.Group_ID = tca.Group_ID
$whereClause
ORDER BY tca.Start_Date DESC LIMIT ? OFFSET ?";
$stmt = $mysqli->prepare($sql);
$bindParams = array_merge($params, [$perPage, $offset]);
$bindTypes = $types . 'ii';
if ($bindParams) $stmt->bind_param($bindTypes, ...$bindParams);
$stmt->execute();
$result = $stmt->get_result();
$items = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
Response::paginated($items, $total, $page, $perPage);
}
function getAssignment(string $id, mysqli $mysqli): void
{
Auth::requireLogin();
$stmt = $mysqli->prepare("
SELECT tca.*, c.Course_Name, c.Course_Code, tp.First_Name AS Teacher_First, tp.Last_Name AS Teacher_Last, g.Group_Name
FROM teacher_course_assignments tca
LEFT JOIN Courses c ON c.Course_ID = tca.Course_ID
LEFT JOIN teacher_profile tp ON tp.Teacher_ID = tca.Teacher_ID
LEFT JOIN manager_group_name g ON g.Group_ID = tca.Group_ID
WHERE tca.Teacher_Course_ID = ?
");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
if (!$item) Response::notFound('Assignment not found');
Response::success($item);
}
function createAssignment(?array $body, mysqli $mysqli): void
{
Auth::requireAnyRole(['admin_profile', 'master_profile', 'manager_profile']);
$teacherId = $body['Teacher_ID'] ?? null;
$courseId = $body['Course_ID'] ?? null;
$groupId = $body['Group_ID'] ?? null;
$scheduleId = $body['Schedule_ID'] ?? null;
$programId = $body['Program_ID'] ?? null;
$startDate = $body['Start_Date'] ?? null;
$endDate = $body['End_Date'] ?? null;
if (!$teacherId || !$courseId || !$groupId || !$scheduleId) {
Response::validationError(['Teacher_ID', 'Course_ID', 'Group_ID', 'Schedule_ID' => 'Required']);
}
// Check for conflicts
if (!empty($_GET['check_conflicts'])) {
$stmt = $mysqli->prepare("SELECT COUNT(*) FROM teacher_course_assignments WHERE Teacher_ID = ? AND Schedule_ID = ?");
$stmt->bind_param('ii', $teacherId, $scheduleId);
$stmt->execute();
$stmt->bind_result($conflictCount);
$stmt->fetch();
$stmt->close();
if ($conflictCount > 0) {
Response::error('Teacher is already assigned to this schedule', 409);
}
}
$stmt = $mysqli->prepare("INSERT INTO teacher_course_assignments (Teacher_ID, Course_ID, Group_ID, Schedule_ID, Program_ID, Start_Date, End_Date) VALUES (?, ?, ?, ?, ?, ?, ?)");
$stmt->bind_param('iiiiiss', $teacherId, $courseId, $groupId, $scheduleId, $programId, $startDate, $endDate);
$stmt->execute();
$newId = $stmt->insert_id;
$stmt->close();
// Mark schedule as assigned
$stmt = $mysqli->prepare("UPDATE schedule_course_for_group SET Assigned = 1 WHERE Schedule_ID = ?");
$stmt->bind_param('i', $scheduleId);
$stmt->execute();
$stmt->close();
log_activity($mysqli, 'create', 'assignment', $newId, "Teacher $teacherId / Course $courseId", 'Assignment created via API');
$stmt = $mysqli->prepare("SELECT * FROM teacher_course_assignments WHERE Teacher_Course_ID = ?");
$stmt->bind_param('i', $newId);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
Response::created($item);
}
function updateAssignment(?string $id, ?array $body, mysqli $mysqli): void
{
Auth::requireAnyRole(['admin_profile', 'master_profile', 'manager_profile']);
if (!$id) Response::error('Assignment ID required');
$stmt = $mysqli->prepare("SELECT * FROM teacher_course_assignments WHERE Teacher_Course_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$existing = $result->fetch_assoc();
$stmt->close();
if (!$existing) Response::notFound('Assignment not found');
$startDate = $body['Start_Date'] ?? $existing['Start_Date'];
$endDate = $body['End_Date'] ?? $existing['End_Date'];
$stmt = $mysqli->prepare("UPDATE teacher_course_assignments SET Start_Date = ?, End_Date = ? WHERE Teacher_Course_ID = ?");
$stmt->bind_param('ssi', $startDate, $endDate, $id);
$stmt->execute();
$stmt->close();
log_activity($mysqli, 'update', 'assignment', $id, 'Assignment dates updated via API');
$stmt = $mysqli->prepare("SELECT * FROM teacher_course_assignments WHERE Teacher_Course_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
Response::success($item, 'Assignment updated');
}
function deleteAssignment(?string $id, mysqli $mysqli): void
{
Auth::requireAnyRole(['admin_profile', 'master_profile', 'manager_profile']);
if (!$id) Response::error('Assignment ID required');
$stmt = $mysqli->prepare("SELECT Schedule_ID FROM teacher_course_assignments WHERE Teacher_Course_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($scheduleId);
$stmt->fetch();
$stmt->close();
if (!$scheduleId) Response::notFound('Assignment not found');
$stmt = $mysqli->prepare("DELETE FROM teacher_course_assignments WHERE Teacher_Course_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();
// Update schedule assigned status
$stmt = $mysqli->prepare("UPDATE schedule_course_for_group SET Assigned = 0 WHERE Schedule_ID = ? AND (SELECT COUNT(*) FROM teacher_course_assignments WHERE Schedule_ID = ?) = 0");
$stmt->bind_param('ii', $scheduleId, $scheduleId);
$stmt->execute();
$stmt->close();
log_activity($mysqli, 'delete', 'assignment', $id, 'Assignment deleted via API');
Response::success(null, 'Assignment deleted');
}
function getAssignmentConflicts(?string $teacherId, mysqli $mysqli): void
{
Auth::requireLogin();
if (!$teacherId) Response::error('Teacher ID required');
$stmt = $mysqli->prepare("
SELECT tca1.*, c1.Course_Name, c1.Course_Code, g1.Group_Name
FROM teacher_course_assignments tca1
JOIN Courses c1 ON c1.Course_ID = tca1.Course_ID
JOIN manager_group_name g1 ON g1.Group_ID = tca1.Group_ID
WHERE tca1.Teacher_ID = ?
AND EXISTS (
SELECT 1 FROM teacher_course_assignments tca2
WHERE tca2.Teacher_ID = tca1.Teacher_ID
AND tca2.Teacher_Course_ID != tca1.Teacher_Course_ID
AND tca2.Start_Date <= tca1.End_Date
AND tca2.End_Date >= tca1.Start_Date
)
ORDER BY tca1.Start_Date
");
$stmt->bind_param('i', $teacherId);
$stmt->execute();
$result = $stmt->get_result();
$items = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
Response::success($items);
}
function listAssignmentsBySchedule(string $scheduleId, mysqli $mysqli): void
{
Auth::requireLogin();
if (!$scheduleId) Response::error('Schedule ID required');
$stmt = $mysqli->prepare("
SELECT tca.*, tp.First_Name, tp.Last_Name, tp.Email
FROM teacher_course_assignments tca
JOIN teacher_profile tp ON tp.Teacher_ID = tca.Teacher_ID
WHERE tca.Schedule_ID = ?
");
$stmt->bind_param('i', $scheduleId);
$stmt->execute();
$result = $stmt->get_result();
$items = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
Response::success($items);
}