requireRole(['super_admin', 'admin']);
$db = DB();
// Fetch summary stats
$stats = [
'total_students' => $db->count('students'),
'passed_students' => (int) $db->fetchColumn("SELECT COUNT(*) FROM students WHERE result_declared = 1 AND percentage >= 40 AND remark != 'Fail'"),
'failed_students' => (int) $db->fetchColumn("SELECT COUNT(*) FROM students WHERE result_declared = 1 AND (percentage < 40 OR remark = 'Fail' OR remark = '*')"),
'pending_results' => $db->count('students', ['result_declared' => 0]),
];
// Enrollment by Category
$categoryStats = $db->fetchAll("
SELECT course_categories.name as category, COUNT(students.id) as count
FROM courses
JOIN course_categories ON courses.category_id = course_categories.id
JOIN students ON courses.id = students.course_id
GROUP BY course_categories.name
ORDER BY count DESC
");
// Recent Admissions (last 6 months)
$monthlyAdmissions = $db->fetchAll("
SELECT DATE_FORMAT(created_at, '%b %Y') as month, COUNT(*) as count
FROM students
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
GROUP BY month
ORDER BY MIN(created_at) ASC
");
?>
Analytical Reports - <?= APP_SHORT_NAME ?>