/home/techb158/workloadmatch.com/api/routes
Edit: /home/techb158/workloadmatch.com/api/routes/courses.php (7225B)
prepare($sql);
if ($params) $stmt->bind_param($types, ...$params);
$stmt->execute();
$stmt->bind_result($total);
$stmt->fetch();
$stmt->close();
$sql = "SELECT c.*, p.Program_Name FROM Courses c LEFT JOIN Programs p ON p.Program_ID = c.Program_ID $whereClause ORDER BY $sort $dir 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 getCourse(string $id, mysqli $mysqli): void
{
Auth::requireLogin();
$stmt = $mysqli->prepare("SELECT c.*, p.Program_Name FROM Courses c LEFT JOIN Programs p ON p.Program_ID = c.Program_ID WHERE c.Course_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
if (!$item) Response::notFound('Course not found');
Response::success($item);
}
function createCourse(?array $body, mysqli $mysqli): void
{
Auth::requireAnyRole(['admin_profile', 'master_profile', 'manager_profile']);
$programId = $body['Program_ID'] ?? null;
$courseName = trim($body['Course_Name'] ?? '');
$courseCode = trim($body['Course_Code'] ?? '');
$courseTime = trim($body['Course_Time'] ?? '');
if (!$programId || !$courseName) {
Response::validationError(['Program_ID' => 'Required', 'Course_Name' => 'Required']);
}
// Check for duplicate
$stmt = $mysqli->prepare("SELECT Course_ID FROM Courses WHERE Program_ID = ? AND Course_Name = ?");
$stmt->bind_param('is', $programId, $courseName);
$stmt->execute();
$stmt->store_result();
if ($stmt->num_rows > 0) {
$stmt->close();
Response::error('Course with this name already exists in the program', 409);
}
$stmt->close();
$dateTime = date('Y/m/d H:i:s');
$stmt = $mysqli->prepare("INSERT INTO Courses (Program_ID, Course_Name, Course_Code, Course_Time, Date_Time) VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param('issss', $programId, $courseName, $courseCode, $courseTime, $dateTime);
$stmt->execute();
$newId = $stmt->insert_id;
$stmt->close();
log_activity($mysqli, 'create', 'course', $newId, $courseName, 'Course created via API');
$stmt = $mysqli->prepare("SELECT c.*, p.Program_Name FROM Courses c LEFT JOIN Programs p ON p.Program_ID = c.Program_ID WHERE c.Course_ID = ?");
$stmt->bind_param('i', $newId);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
Response::created($item);
}
function updateCourse(?string $id, ?array $body, mysqli $mysqli): void
{
Auth::requireAnyRole(['admin_profile', 'master_profile', 'manager_profile']);
if (!$id) Response::error('Course ID required');
$stmt = $mysqli->prepare("SELECT * FROM Courses WHERE Course_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$existing = $result->fetch_assoc();
$stmt->close();
if (!$existing) Response::notFound('Course not found');
$programId = $body['Program_ID'] ?? $existing['Program_ID'];
$courseName = trim($body['Course_Name'] ?? $existing['Course_Name']);
$courseCode = trim($body['Course_Code'] ?? $existing['Course_Code']);
$courseTime = trim($body['Course_Time'] ?? $existing['Course_Time']);
$stmt = $mysqli->prepare("UPDATE Courses SET Program_ID = ?, Course_Name = ?, Course_Code = ?, Course_Time = ? WHERE Course_ID = ?");
$stmt->bind_param('isssi', $programId, $courseName, $courseCode, $courseTime, $id);
$stmt->execute();
$stmt->close();
log_activity($mysqli, 'update', 'course', $id, $courseName, 'Course updated via API');
$stmt = $mysqli->prepare("SELECT c.*, p.Program_Name FROM Courses c LEFT JOIN Programs p ON p.Program_ID = c.Program_ID WHERE c.Course_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
Response::success($item, 'Course updated');
}
function deleteCourse(?string $id, mysqli $mysqli): void
{
Auth::requireAnyRole(['admin_profile', 'master_profile']);
if (!$id) Response::error('Course ID required');
$stmt = $mysqli->prepare("SELECT Course_Name FROM Courses WHERE Course_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($name);
$stmt->fetch();
$stmt->close();
if (!$name) Response::notFound('Course not found');
// Check for related schedules
$stmt = $mysqli->prepare("SELECT COUNT(*) FROM schedule_course_for_group WHERE Course_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($count);
$stmt->fetch();
$stmt->close();
if ($count > 0) {
Response::error("Cannot delete course: $count schedule(s) reference it", 409);
}
$stmt = $mysqli->prepare("DELETE FROM Courses WHERE Course_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();
log_activity($mysqli, 'delete', 'course', $id, $name, 'Course deleted via API');
Response::success(null, 'Course deleted');
}