/home/techb158/workloadmatch.com/api/routes
Edit: /home/techb158/workloadmatch.com/api/routes/reports.php (13696B)
prepare("
SELECT c.Course_ID, c.Course_Name, c.Course_Code, p.Program_Name,
s.Schedule_ID, s.Group_ID, g.Group_Name, s.Start_Date, s.End_Date, s.Assigned,
tca.Teacher_ID, tp.First_Name AS Teacher_First, tp.Last_Name AS Teacher_Last
FROM Courses c
JOIN Programs p ON p.Program_ID = c.Program_ID
LEFT JOIN schedule_course_for_group s ON s.Course_ID = c.Course_ID
LEFT JOIN manager_group_name g ON g.Group_ID = s.Group_ID
LEFT JOIN teacher_course_assignments tca ON tca.Schedule_ID = s.Schedule_ID
LEFT JOIN teacher_profile tp ON tp.Teacher_ID = tca.Teacher_ID
$whereClause
ORDER BY c.Course_Name, s.Start_Date
");
if ($params) $stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();
$rows = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// Group by course
$grouped = [];
foreach ($rows as $row) {
$cid = $row['Course_ID'];
if (!isset($grouped[$cid])) {
$grouped[$cid] = [
'course_id' => $cid,
'course_name' => $row['Course_Name'],
'course_code' => $row['Course_Code'],
'program' => $row['Program_Name'],
'schedules' => [],
];
}
if ($row['Schedule_ID']) {
$grouped[$cid]['schedules'][] = [
'schedule_id' => $row['Schedule_ID'],
'group' => $row['Group_Name'],
'start_date' => $row['Start_Date'],
'end_date' => $row['End_Date'],
'assigned' => $row['Assigned'],
'teacher' => $row['Teacher_First'] ? ($row['Teacher_First'] . ' ' . $row['Teacher_Last']) : null,
];
}
}
Response::success(array_values($grouped));
}
function reportScheduleByGroup(mysqli $mysqli): void
{
$programId = $_GET['program_id'] ?? null;
$wheres = [];
$params = [];
$types = '';
if ($programId) {
$wheres[] = 'g.Program_ID = ?';
$params[] = (int)$programId;
$types .= 'i';
}
if (Auth::getUserType() === 'manager_profile') {
$wheres[] = 'g.Manager_ID = ?';
$params[] = Auth::getEffectiveManagerId();
$types .= 'i';
}
$whereClause = $wheres ? 'WHERE ' . implode(' AND ', $wheres) : '';
$stmt = $mysqli->prepare("
SELECT g.Group_ID, g.Group_Name, g.Group_Year, p.Program_Name,
s.Schedule_ID, s.Course_ID, c.Course_Name, c.Course_Code,
s.Start_Date, s.End_Date, s.Assigned,
tca.Teacher_ID, tp.First_Name AS Teacher_First, tp.Last_Name AS Teacher_Last
FROM manager_group_name g
JOIN Programs p ON p.Program_ID = g.Program_ID
LEFT JOIN schedule_course_for_group s ON s.Group_ID = g.Group_ID
LEFT JOIN Courses c ON c.Course_ID = s.Course_ID
LEFT JOIN teacher_course_assignments tca ON tca.Schedule_ID = s.Schedule_ID
LEFT JOIN teacher_profile tp ON tp.Teacher_ID = tca.Teacher_ID
$whereClause
ORDER BY g.Group_Name, s.Start_Date
");
if ($params) $stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();
$rows = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
$grouped = [];
foreach ($rows as $row) {
$gid = $row['Group_ID'];
if (!isset($grouped[$gid])) {
$grouped[$gid] = [
'group_id' => $gid,
'group_name' => $row['Group_Name'],
'group_year' => $row['Group_Year'],
'program' => $row['Program_Name'],
'schedules' => [],
];
}
if ($row['Schedule_ID']) {
$grouped[$gid]['schedules'][] = [
'schedule_id' => $row['Schedule_ID'],
'course' => $row['Course_Name'] . ($row['Course_Code'] ? " ({$row['Course_Code']})" : ''),
'start_date' => $row['Start_Date'],
'end_date' => $row['End_Date'],
'assigned' => $row['Assigned'],
'teacher' => $row['Teacher_First'] ? ($row['Teacher_First'] . ' ' . $row['Teacher_Last']) : null,
];
}
}
Response::success(array_values($grouped));
}
function reportScheduleByTeacher(mysqli $mysqli): void
{
$teacherId = $_GET['teacher_id'] ?? null;
$stmt = $mysqli->prepare("
SELECT t.Teacher_ID, t.First_Name, t.Last_Name, t.Email,
tca.Teacher_Course_ID, tca.Course_ID, c.Course_Name, c.Course_Code,
tca.Group_ID, g.Group_Name, tca.Schedule_ID,
tca.Start_Date, tca.End_Date
FROM teacher_profile t
LEFT JOIN teacher_course_assignments tca ON tca.Teacher_ID = t.Teacher_ID
LEFT JOIN Courses c ON c.Course_ID = tca.Course_ID
LEFT JOIN manager_group_name g ON g.Group_ID = tca.Group_ID
WHERE ($teacherId IS NULL OR t.Teacher_ID = ?)
ORDER BY t.Last_Name, t.First_Name, tca.Start_Date
");
if ($teacherId) {
$stmt->bind_param('i', $teacherId);
}
$stmt->execute();
$result = $stmt->get_result();
$rows = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
$grouped = [];
foreach ($rows as $row) {
$tid = $row['Teacher_ID'];
if (!isset($grouped[$tid])) {
$grouped[$tid] = [
'teacher_id' => $tid,
'name' => $row['First_Name'] . ' ' . $row['Last_Name'],
'email' => $row['Email'],
'assignments' => [],
];
}
if ($row['Teacher_Course_ID']) {
$grouped[$tid]['assignments'][] = [
'assignment_id' => $row['Teacher_Course_ID'],
'course' => $row['Course_Name'] . ($row['Course_Code'] ? " ({$row['Course_Code']})" : ''),
'group' => $row['Group_Name'],
'start_date' => $row['Start_Date'],
'end_date' => $row['End_Date'],
];
}
}
Response::success(array_values($grouped));
}
function reportTeacherWorkload(mysqli $mysqli): void
{
$managerId = null;
if (Auth::getUserType() === 'manager_profile') {
$managerId = Auth::getEffectiveManagerId();
} elseif (!empty($_GET['manager_id'])) {
$managerId = (int)$_GET['manager_id'];
}
$wheres = [];
$params = [];
$types = '';
if ($managerId) {
$wheres[] = 't.Manager_ID = ?';
$params[] = $managerId;
$types .= 'i';
}
$whereClause = $wheres ? 'WHERE ' . implode(' AND ', $wheres) : '';
$stmt = $mysqli->prepare("
SELECT t.Teacher_ID, t.First_Name, t.Last_Name, t.Load_Hours,
COUNT(tca.Teacher_Course_ID) AS course_count,
COALESCE(SUM(DATEDIFF(tca.End_Date, tca.Start_Date) + 1), 0) AS total_days,
t.Seniority_ID
FROM teacher_profile t
LEFT JOIN teacher_course_assignments tca ON tca.Teacher_ID = t.Teacher_ID AND tca.Start_Date >= COALESCE(NULL, '2000-01-01')
$whereClause
GROUP BY t.Teacher_ID
ORDER BY t.Last_Name, t.First_Name
");
if ($params) $stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();
$items = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
Response::success($items);
}
function reportUnassignedCourses(mysqli $mysqli): void
{
$wheres = ['scg.Assigned = 0'];
$params = [];
$types = '';
if (Auth::getUserType() === 'manager_profile') {
$wheres[] = 'mgn.Manager_ID = ?';
$params[] = Auth::getEffectiveManagerId();
$types .= 'i';
}
$whereClause = 'WHERE ' . implode(' AND ', $wheres);
$stmt = $mysqli->prepare("
SELECT scg.Schedule_ID, scg.Course_ID, c.Course_Name, c.Course_Code,
scg.Group_ID, g.Group_Name, g.Group_Year, p.Program_Name,
scg.Start_Date, scg.End_Date
FROM schedule_course_for_group scg
JOIN Courses c ON c.Course_ID = scg.Course_ID
JOIN manager_group_name g ON g.Group_ID = scg.Group_ID
LEFT JOIN Programs p ON p.Program_ID = scg.Program_ID
JOIN manager_group_name mgn ON mgn.Group_ID = scg.Group_ID
$whereClause
ORDER BY scg.Start_Date
");
if ($params) $stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();
$items = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
Response::success([
'count' => count($items),
'items' => $items,
]);
}
function reportTeacherAvailability(mysqli $mysqli): void
{
$startDate = $_GET['start_date'] ?? date('Y-m-d');
$endDate = $_GET['end_date'] ?? date('Y-m-d', strtotime('+30 days'));
$stmt = $mysqli->prepare("
SELECT t.Teacher_ID, t.First_Name, t.Last_Name,
tca.Course_ID, c.Course_Name, tca.Start_Date AS Assignment_Start, tca.End_Date AS Assignment_End
FROM teacher_profile t
LEFT JOIN teacher_course_assignments tca ON tca.Teacher_ID = t.Teacher_ID
AND tca.Start_Date <= ? AND tca.End_Date >= ?
LEFT JOIN Courses c ON c.Course_ID = tca.Course_ID
ORDER BY t.Last_Name, t.First_Name
");
$stmt->bind_param('ss', $endDate, $startDate);
$stmt->execute();
$result = $stmt->get_result();
$rows = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
$teachers = [];
foreach ($rows as $row) {
$tid = $row['Teacher_ID'];
if (!isset($teachers[$tid])) {
$teachers[$tid] = [
'teacher_id' => $tid,
'name' => $row['First_Name'] . ' ' . $row['Last_Name'],
'has_conflict' => false,
'assignments' => [],
];
}
if ($row['Course_ID']) {
$teachers[$tid]['assignments'][] = [
'course' => $row['Course_Name'],
'start' => $row['Assignment_Start'],
'end' => $row['Assignment_End'],
];
}
}
// Determine availability
$busyTeachers = [];
$availableTeachers = [];
foreach ($teachers as $t) {
if (empty($t['assignments'])) {
$t['status'] = 'available';
$availableTeachers[] = $t;
} else {
$t['status'] = 'assigned';
$busyTeachers[] = $t;
}
}
Response::success([
'available' => $availableTeachers,
'assigned' => $busyTeachers,
]);
}
function reportAssignmentsSummary(mysqli $mysqli): void
{
$stmt = $mysqli->query("
SELECT
(SELECT COUNT(*) FROM teacher_profile) AS total_teachers,
(SELECT COUNT(*) FROM Courses) AS total_courses,
(SELECT COUNT(*) FROM manager_group_name) AS total_groups,
(SELECT COUNT(*) FROM schedule_course_for_group) AS total_schedules,
(SELECT COUNT(*) FROM schedule_course_for_group WHERE Assigned = 1) AS assigned_schedules,
(SELECT COUNT(*) FROM schedule_course_for_group WHERE Assigned = 0) AS unassigned_schedules,
(SELECT COUNT(*) FROM teacher_course_assignments) AS total_assignments,
(SELECT COUNT(*) FROM Programs) AS total_programs
");
$summary = $stmt->fetch_assoc();
$stmt->close();
Response::success($summary);
}
function reportProgramSummary(mysqli $mysqli): void
{
$stmt = $mysqli->query("
SELECT p.Program_ID, p.Program_Name,
(SELECT COUNT(*) FROM Courses WHERE Program_ID = p.Program_ID) AS course_count,
(SELECT COUNT(*) FROM manager_group_name WHERE Program_ID = p.Program_ID) AS group_count,
(SELECT COUNT(*) FROM teacher_profile WHERE Program_ID = p.Program_ID) AS teacher_count
FROM Programs p
ORDER BY p.Program_Name
");
$items = $stmt->fetch_all(MYSQLI_ASSOC);
$stmt->close();
Response::success($items);
}