/home/techb158/workloadmatch.com/api/routes
Edit: /home/techb158/workloadmatch.com/api/routes/schedules.php (11042B)
= ?';
$params[] = $_GET['start_date'];
$types .= 's';
}
if (!empty($_GET['end_date'])) {
$wheres[] = 's.End_Date <= ?';
$params[] = $_GET['end_date'];
$types .= 's';
}
// Auto-filter for managers
$userType = Auth::getUserType();
if ($userType === 'manager_profile') {
$wheres[] = 's.Group_ID IN (SELECT Group_ID FROM manager_group_name WHERE Manager_ID = ?)';
$params[] = Auth::getEffectiveManagerId();
$types .= 'i';
}
$whereClause = $wheres ? 'WHERE ' . implode(' AND ', $wheres) : '';
$total = 0;
$sql = "SELECT COUNT(*) FROM schedule_course_for_group s $whereClause";
$stmt = $mysqli->prepare($sql);
if ($params) $stmt->bind_param($types, ...$params);
$stmt->execute();
$stmt->bind_result($total);
$stmt->fetch();
$stmt->close();
$sql = "SELECT s.*, c.Course_Name, c.Course_Code, g.Group_Name, p.Program_Name
FROM schedule_course_for_group s
LEFT JOIN Courses c ON c.Course_ID = s.Course_ID
LEFT JOIN manager_group_name g ON g.Group_ID = s.Group_ID
LEFT JOIN Programs p ON p.Program_ID = s.Program_ID
$whereClause
ORDER BY s.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();
// Get teacher assignments for each schedule
foreach ($items as &$item) {
$s = $mysqli->prepare("SELECT tca.Teacher_ID, tp.First_Name, tp.Last_Name FROM teacher_course_assignments tca JOIN teacher_profile tp ON tp.Teacher_ID = tca.Teacher_ID WHERE tca.Schedule_ID = ?");
$s->bind_param('i', $item['Schedule_ID']);
$s->execute();
$r = $s->get_result();
$item['teachers'] = $r->fetch_all(MYSQLI_ASSOC);
$s->close();
}
Response::paginated($items, $total, $page, $perPage);
}
function getSchedule(string $id, mysqli $mysqli): void
{
Auth::requireLogin();
$stmt = $mysqli->prepare("
SELECT s.*, c.Course_Name, c.Course_Code, g.Group_Name, p.Program_Name
FROM schedule_course_for_group s
LEFT JOIN Courses c ON c.Course_ID = s.Course_ID
LEFT JOIN manager_group_name g ON g.Group_ID = s.Group_ID
LEFT JOIN Programs p ON p.Program_ID = s.Program_ID
WHERE s.Schedule_ID = ?
");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
if (!$item) Response::notFound('Schedule not found');
// Get assigned teachers
$s = $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 = ?");
$s->bind_param('i', $id);
$s->execute();
$r = $s->get_result();
$item['teachers'] = $r->fetch_all(MYSQLI_ASSOC);
$s->close();
Response::success($item);
}
function createSchedule(?array $body, mysqli $mysqli): void
{
Auth::requireAnyRole(['admin_profile', 'master_profile', 'manager_profile']);
$courseId = $body['Course_ID'] ?? null;
$groupId = $body['Group_ID'] ?? null;
$programId = $body['Program_ID'] ?? null;
$startDate = $body['Start_Date'] ?? null;
$endDate = $body['End_Date'] ?? null;
$assigned = (int)($body['Assigned'] ?? 0);
if (!$courseId || !$groupId || !$startDate || !$endDate) {
Response::validationError(['Course_ID', 'Group_ID', 'Start_Date', 'End_Date' => 'Required']);
}
$stmt = $mysqli->prepare("INSERT INTO schedule_course_for_group (Course_ID, Group_ID, Program_ID, Assigned, Start_Date, End_Date) VALUES (?, ?, ?, ?, ?, ?)");
$stmt->bind_param('iiiiss', $courseId, $groupId, $programId, $assigned, $startDate, $endDate);
$stmt->execute();
$newId = $stmt->insert_id;
$stmt->close();
log_activity($mysqli, 'create', 'schedule', $newId, "Course $courseId / Group $groupId", 'Schedule created via API');
$stmt = $mysqli->prepare("SELECT * FROM schedule_course_for_group WHERE Schedule_ID = ?");
$stmt->bind_param('i', $newId);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
Response::created($item);
}
function updateSchedule(?string $id, ?array $body, mysqli $mysqli): void
{
Auth::requireAnyRole(['admin_profile', 'master_profile', 'manager_profile']);
if (!$id) Response::error('Schedule ID required');
$stmt = $mysqli->prepare("SELECT * FROM schedule_course_for_group WHERE Schedule_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$existing = $result->fetch_assoc();
$stmt->close();
if (!$existing) Response::notFound('Schedule not found');
$courseId = $body['Course_ID'] ?? $existing['Course_ID'];
$groupId = $body['Group_ID'] ?? $existing['Group_ID'];
$programId = $body['Program_ID'] ?? $existing['Program_ID'];
$startDate = $body['Start_Date'] ?? $existing['Start_Date'];
$endDate = $body['End_Date'] ?? $existing['End_Date'];
$assigned = (int)($body['Assigned'] ?? $existing['Assigned']);
$stmt = $mysqli->prepare("UPDATE schedule_course_for_group SET Course_ID = ?, Group_ID = ?, Program_ID = ?, Assigned = ?, Start_Date = ?, End_Date = ? WHERE Schedule_ID = ?");
$stmt->bind_param('iiiissi', $courseId, $groupId, $programId, $assigned, $startDate, $endDate, $id);
$stmt->execute();
$stmt->close();
log_activity($mysqli, 'update', 'schedule', $id, "Course $courseId", 'Schedule updated via API');
$stmt = $mysqli->prepare("SELECT * FROM schedule_course_for_group WHERE Schedule_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
Response::success($item, 'Schedule updated');
}
function deleteSchedule(?string $id, mysqli $mysqli): void
{
Auth::requireAnyRole(['admin_profile', 'master_profile']);
if (!$id) Response::error('Schedule ID required');
$stmt = $mysqli->prepare("SELECT * FROM schedule_course_for_group WHERE Schedule_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$existing = $result->fetch_assoc();
$stmt->close();
if (!$existing) Response::notFound('Schedule not found');
// Delete associated assignments
$stmt = $mysqli->prepare("DELETE FROM teacher_course_assignments WHERE Schedule_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();
$stmt = $mysqli->prepare("DELETE FROM schedule_course_for_group WHERE Schedule_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();
log_activity($mysqli, 'delete', 'schedule', $id, 'Schedule deleted via API');
Response::success(null, 'Schedule deleted');
}
function listSchedulesByGroup(?string $groupId, mysqli $mysqli): void
{
Auth::requireLogin();
if (!$groupId) Response::error('Group ID required');
$stmt = $mysqli->prepare("
SELECT s.*, c.Course_Name, c.Course_Code, c.Course_Time
FROM schedule_course_for_group s
JOIN Courses c ON c.Course_ID = s.Course_ID
WHERE s.Group_ID = ?
ORDER BY s.Start_Date ASC
");
$stmt->bind_param('i', $groupId);
$stmt->execute();
$result = $stmt->get_result();
$items = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
foreach ($items as &$item) {
$s = $mysqli->prepare("SELECT t.Teacher_ID, t.First_Name, t.Last_Name FROM teacher_course_assignments tca JOIN teacher_profile t ON t.Teacher_ID = tca.Teacher_ID WHERE tca.Schedule_ID = ?");
$s->bind_param('i', $item['Schedule_ID']);
$s->execute();
$r = $s->get_result();
$item['teachers'] = $r->fetch_all(MYSQLI_ASSOC);
$s->close();
}
Response::success($items);
}
function listSchedulesByCourse(?string $courseId, mysqli $mysqli): void
{
Auth::requireLogin();
if (!$courseId) Response::error('Course ID required');
$stmt = $mysqli->prepare("
SELECT s.*, g.Group_Name, p.Program_Name
FROM schedule_course_for_group s
JOIN manager_group_name g ON g.Group_ID = s.Group_ID
LEFT JOIN Programs p ON p.Program_ID = s.Program_ID
WHERE s.Course_ID = ?
ORDER BY s.Start_Date ASC
");
$stmt->bind_param('i', $courseId);
$stmt->execute();
$result = $stmt->get_result();
$items = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
Response::success($items);
}
function generateSchedule(?array $body, mysqli $mysqli): void
{
Auth::requireAnyRole(['master_profile', 'manager_profile']);
Response::success(null, 'Schedule generation is handled by the existing AI_Generate_Schedule algorithm. Use POST /api/schedules/generate with appropriate parameters.');
}