= 80% // Consistent Performer: Average >= 60% try { // This is a simple logic to auto-award badges for demonstration $stmt = $db->prepare(" SELECT l.id, AVG((m.marks_obtained / a.total_marks) * 100) as avg_score FROM learners l JOIN marks m ON l.id = m.learner_id JOIN assessments a ON m.assessment_id = a.id WHERE l.school_id = ? GROUP BY l.id "); $stmt->execute([$school_id]); $performances = $stmt->fetchAll(); foreach ($performances as $perf) { if ($perf['avg_score'] >= 80) { // Award Academic Star (Badge ID 1) $db->prepare("INSERT IGNORE INTO learner_badges (learner_id, badge_id) VALUES (?, 1)")->execute([$perf['id']]); } elseif ($perf['avg_score'] >= 60) { // Award Consistent Performer (Badge ID 2) $db->prepare("INSERT IGNORE INTO learner_badges (learner_id, badge_id) VALUES (?, 2)")->execute([$perf['id']]); } } } catch (Exception $e) { // Silently fail if something goes wrong with auto-awarding } // Fetch Leaderboard (Top learners by average score) $query = " SELECT l.id, l.full_name, l.grade, l.student_id, AVG((m.marks_obtained / a.total_marks) * 100) as average_score, COUNT(m.id) as assessments_count FROM learners l JOIN marks m ON l.id = m.learner_id JOIN assessments a ON m.assessment_id = a.id WHERE l.school_id = :school_id GROUP BY l.id ORDER BY average_score DESC LIMIT 20 "; $stmt = $db->prepare($query); $stmt->execute(['school_id' => $school_id]); $leaderboard = $stmt->fetchAll(); // Fetch Badges for these learners $badges_query = " SELECT lb.learner_id, b.name, b.icon, b.description FROM learner_badges lb JOIN badges b ON lb.badge_id = b.id WHERE lb.learner_id IN (SELECT id FROM learners WHERE school_id = ?) "; $stmt = $db->prepare($badges_query); $stmt->execute([$school_id]); $all_learner_badges = $stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC); $pageTitle = "Learner Leaderboard | SOMS"; include 'includes/header.php'; ?>

Academic Leaderboard

Celebrating excellence and consistent growth in our school.

Top 20
Current Rankings
$row): ?>
Rank Learner Grade Avg. Score Achievements
No assessment data available yet to generate rankings.
2
3
Grade
%
No badges yet