root
505 lines
22 KiB
PHP
505 lines
22 KiB
PHP
<?php
|
|
|
|
namespace App\Controllers\View;
|
|
|
|
use App\Controllers\BaseController;
|
|
use App\Models\ConfigurationModel;
|
|
|
|
class TrophyController extends BaseController
|
|
{
|
|
private const PERCENTILE = 75.0;
|
|
|
|
protected ConfigurationModel $configModel;
|
|
|
|
public function __construct()
|
|
{
|
|
$this->configModel = new ConfigurationModel();
|
|
}
|
|
|
|
public function index()
|
|
{
|
|
$db = \Config\Database::connect();
|
|
|
|
$currentYear = $this->configModel->getConfig('school_year') ?? '';
|
|
$selectedYear = $this->request->getGet('school_year') ?? $currentYear;
|
|
|
|
$percentile = (float) ($this->request->getGet('percentile') ?? 75);
|
|
$percentile = max(1.0, min(99.0, $percentile));
|
|
|
|
// Available school years from class assignments so the current year can
|
|
// still appear even if fall scores are not fully entered yet.
|
|
$years = $db->table('student_class')
|
|
->select('school_year')
|
|
->distinct()
|
|
->orderBy('school_year', 'DESC')
|
|
->get()
|
|
->getResultArray();
|
|
$years = array_column($years, 'school_year');
|
|
|
|
// Build a fall-score-based projection. We start from class assignments so
|
|
// students without a recorded fall score still appear as "not yet
|
|
// projected" rather than disappearing from the page.
|
|
$rows = $db->table('student_class sc')
|
|
->select([
|
|
'sc.student_id',
|
|
'sc.class_section_id',
|
|
'MAX(ss.semester_score) AS fall_score',
|
|
'cs.class_section_name',
|
|
'CONCAT(s.firstname, " ", s.lastname) AS student_name',
|
|
's.school_id',
|
|
's.gender',
|
|
])
|
|
->join('classSection cs', 'cs.class_section_id = sc.class_section_id', 'left')
|
|
->join('students s', 's.id = sc.student_id', 'left')
|
|
->join(
|
|
'semester_scores ss',
|
|
'ss.student_id = sc.student_id'
|
|
. ' AND ss.class_section_id = sc.class_section_id'
|
|
. ' AND ss.school_year = ' . $db->escape($selectedYear)
|
|
. ' AND LOWER(ss.semester) = "fall"',
|
|
'left'
|
|
)
|
|
->where('sc.school_year', $selectedYear)
|
|
->orderBy('cs.class_section_name', 'ASC')
|
|
->orderBy('fall_score', 'DESC')
|
|
->orderBy('student_name', 'ASC')
|
|
->groupBy('sc.student_id, sc.class_section_id, cs.class_section_name, s.firstname, s.lastname, s.school_id')
|
|
->get()
|
|
->getResultArray();
|
|
|
|
// Group rows by class section
|
|
$sections = [];
|
|
foreach ($rows as $row) {
|
|
$sid = (int) ($row['class_section_id'] ?? 0);
|
|
$name = $row['class_section_name'] ?? 'Section ' . $sid;
|
|
if (!isset($sections[$sid])) {
|
|
$sections[$sid] = ['name' => $name, 'students' => []];
|
|
}
|
|
$sections[$sid]['students'][] = [
|
|
'student_id' => (int) $row['student_id'],
|
|
'name' => $row['student_name'],
|
|
'school_id' => $row['school_id'],
|
|
'gender' => $row['gender'] ?? '',
|
|
'fall_score' => is_numeric($row['fall_score']) ? (float) $row['fall_score'] : null,
|
|
];
|
|
}
|
|
|
|
// Calculate trophy thresholds from fall scores only, then project who is
|
|
// currently on track for a year-end trophy.
|
|
$classResults = [];
|
|
foreach ($sections as $sid => $section) {
|
|
usort($section['students'], static function (array $a, array $b): int {
|
|
$aScore = $a['fall_score'];
|
|
$bScore = $b['fall_score'];
|
|
|
|
if ($aScore === null && $bScore === null) {
|
|
return strcmp($a['name'], $b['name']);
|
|
}
|
|
|
|
if ($aScore === null) {
|
|
return 1;
|
|
}
|
|
|
|
if ($bScore === null) {
|
|
return -1;
|
|
}
|
|
|
|
return $bScore <=> $aScore ?: strcmp($a['name'], $b['name']);
|
|
});
|
|
|
|
$scores = array_column($section['students'], 'fall_score');
|
|
$calc = $this->calculateThreshold($scores, $percentile);
|
|
$threshold = $calc['threshold'];
|
|
|
|
$students = array_map(function (array $student) use ($threshold): array {
|
|
$student['projected_trophy'] = $threshold !== null
|
|
&& $student['fall_score'] !== null
|
|
&& $student['fall_score'] >= $threshold;
|
|
return $student;
|
|
}, $section['students']);
|
|
|
|
$scoredCount = count(array_filter(
|
|
array_column($students, 'fall_score'),
|
|
static fn ($score) => $score !== null
|
|
));
|
|
|
|
$boys = array_filter($students, static fn ($s) => strtolower($s['gender']) === 'male');
|
|
$girls = array_filter($students, static fn ($s) => strtolower($s['gender']) === 'female');
|
|
$trophyBoys = array_filter($boys, static fn ($s) => $s['projected_trophy']);
|
|
$trophyGirls = array_filter($girls, static fn ($s) => $s['projected_trophy']);
|
|
$nBoys = count($boys);
|
|
$nGirls = count($girls);
|
|
$nTrophyBoys = count($trophyBoys);
|
|
$nTrophyGirls = count($trophyGirls);
|
|
$total = count($students);
|
|
|
|
$classResults[] = [
|
|
'section_id' => $sid,
|
|
'section_name' => $section['name'],
|
|
'students' => $students,
|
|
'threshold' => $threshold,
|
|
'trophy_count' => $calc['winners'],
|
|
'student_count' => $total,
|
|
'scored_count' => $scoredCount,
|
|
'method' => $calc['method'],
|
|
'boys' => $nBoys,
|
|
'girls' => $nGirls,
|
|
'trophy_boys' => $nTrophyBoys,
|
|
'trophy_girls' => $nTrophyGirls,
|
|
'pct_boys' => $total > 0 ? round($nBoys / $total * 100) : 0,
|
|
'pct_girls' => $total > 0 ? round($nGirls / $total * 100) : 0,
|
|
'pct_trophy_boys' => $nBoys > 0 ? round($nTrophyBoys / $nBoys * 100) : 0,
|
|
'pct_trophy_girls' => $nGirls > 0 ? round($nTrophyGirls / $nGirls * 100) : 0,
|
|
'pct_trophy_total' => $total > 0 ? round($calc['winners'] / $total * 100): 0,
|
|
];
|
|
}
|
|
|
|
return view('administrator/trophy', [
|
|
'classResults' => $classResults,
|
|
'selectedYear' => $selectedYear,
|
|
'selectedPercentile'=> $percentile,
|
|
'years' => $years,
|
|
]);
|
|
}
|
|
|
|
public function winners()
|
|
{
|
|
$db = \Config\Database::connect();
|
|
|
|
$currentYear = $this->configModel->getConfig('school_year') ?? '';
|
|
$selectedYear = $this->request->getGet('school_year') ?? $currentYear;
|
|
$percentile = (float) ($this->request->getGet('percentile') ?? 75);
|
|
$percentile = max(1.0, min(99.0, $percentile));
|
|
|
|
$years = $db->table('student_class')
|
|
->select('school_year')->distinct()
|
|
->orderBy('school_year', 'DESC')
|
|
->get()->getResultArray();
|
|
$years = array_column($years, 'school_year');
|
|
|
|
$ey = $db->escape($selectedYear);
|
|
|
|
$rows = $db->table('student_class sc')
|
|
->select([
|
|
'sc.student_id',
|
|
'sc.class_section_id',
|
|
'cs.class_section_name',
|
|
'CONCAT(s.firstname, " ", s.lastname) AS student_name',
|
|
's.school_id',
|
|
's.gender',
|
|
'MAX(sf.semester_score) AS fall_score',
|
|
'MAX(sp.semester_score) AS spring_score',
|
|
])
|
|
->join('classSection cs', 'cs.class_section_id = sc.class_section_id', 'left')
|
|
->join('students s', 's.id = sc.student_id', 'left')
|
|
->join('semester_scores sf',
|
|
'sf.student_id = sc.student_id AND sf.class_section_id = sc.class_section_id'
|
|
. ' AND sf.school_year = ' . $ey . ' AND LOWER(sf.semester) = "fall"', 'left')
|
|
->join('semester_scores sp',
|
|
'sp.student_id = sc.student_id AND sp.class_section_id = sc.class_section_id'
|
|
. ' AND sp.school_year = ' . $ey . ' AND LOWER(sp.semester) = "spring"', 'left')
|
|
->where('sc.school_year', $selectedYear)
|
|
->orderBy('cs.class_section_name', 'ASC')
|
|
->orderBy('student_name', 'ASC')
|
|
->groupBy('sc.student_id, sc.class_section_id, cs.class_section_name, s.firstname, s.lastname, s.school_id, s.gender')
|
|
->get()->getResultArray();
|
|
|
|
$sections = [];
|
|
foreach ($rows as $row) {
|
|
$sid = (int) ($row['class_section_id'] ?? 0);
|
|
$name = $row['class_section_name'] ?? 'Section ' . $sid;
|
|
if (!isset($sections[$sid])) $sections[$sid] = ['name' => $name, 'students' => []];
|
|
$fall = is_numeric($row['fall_score']) ? (float) $row['fall_score'] : null;
|
|
$spring = is_numeric($row['spring_score']) ? (float) $row['spring_score'] : null;
|
|
$year = ($fall !== null && $spring !== null)
|
|
? round(($fall + $spring) / 2, 1)
|
|
: ($fall ?? $spring);
|
|
$sections[$sid]['students'][] = [
|
|
'name' => $row['student_name'],
|
|
'school_id' => $row['school_id'],
|
|
'gender' => $row['gender'] ?? '',
|
|
'fall_score' => $fall,
|
|
'spring_score' => $spring,
|
|
'year_score' => $year,
|
|
];
|
|
}
|
|
|
|
$classResults = [];
|
|
foreach ($sections as $section) {
|
|
usort($section['students'], static function ($a, $b) {
|
|
if ($a['fall_score'] === null && $b['fall_score'] === null) return strcmp($a['name'], $b['name']);
|
|
if ($a['fall_score'] === null) return 1;
|
|
if ($b['fall_score'] === null) return -1;
|
|
return $b['fall_score'] <=> $a['fall_score'] ?: strcmp($a['name'], $b['name']);
|
|
});
|
|
|
|
$scores = array_column($section['students'], 'fall_score');
|
|
$calc = $this->calculateThreshold($scores, $percentile);
|
|
$threshold = $calc['threshold'];
|
|
|
|
$allStudents = $section['students'];
|
|
$winners = array_values(array_filter($allStudents, function ($s) use ($threshold) {
|
|
return $threshold !== null && $s['fall_score'] !== null && $s['fall_score'] >= $threshold;
|
|
}));
|
|
|
|
if (empty($winners)) continue;
|
|
|
|
$boys = array_filter($allStudents, static fn($s) => strtolower($s['gender']) === 'male');
|
|
$girls = array_filter($allStudents, static fn($s) => strtolower($s['gender']) === 'female');
|
|
$trophyBoys = array_filter($winners, static fn($s) => strtolower($s['gender']) === 'male');
|
|
$trophyGirls = array_filter($winners, static fn($s) => strtolower($s['gender']) === 'female');
|
|
$n = count($allStudents);
|
|
$nBoys = count($boys);
|
|
$nGirls = count($girls);
|
|
$nTB = count($trophyBoys);
|
|
$nTG = count($trophyGirls);
|
|
|
|
$classResults[] = [
|
|
'section_name' => $section['name'],
|
|
'threshold' => $threshold,
|
|
'winners' => $winners,
|
|
'student_count' => $n,
|
|
'boys' => $nBoys,
|
|
'girls' => $nGirls,
|
|
'trophy_boys' => $nTB,
|
|
'trophy_girls' => $nTG,
|
|
'pct_boys' => $n > 0 ? round($nBoys / $n * 100) : 0,
|
|
'pct_girls' => $n > 0 ? round($nGirls / $n * 100) : 0,
|
|
'pct_trophy_boys' => $nBoys > 0 ? round($nTB / $nBoys * 100) : 0,
|
|
'pct_trophy_girls' => $nGirls > 0 ? round($nTG / $nGirls * 100) : 0,
|
|
'pct_trophy_total' => $n > 0 ? round(count($winners) / $n * 100) : 0,
|
|
];
|
|
}
|
|
|
|
return view('administrator/trophy_winners', [
|
|
'classResults' => $classResults,
|
|
'selectedYear' => $selectedYear,
|
|
'selectedPercentile' => $percentile,
|
|
'years' => $years,
|
|
]);
|
|
}
|
|
|
|
public function final()
|
|
{
|
|
$db = \Config\Database::connect();
|
|
|
|
$currentYear = $this->configModel->getConfig('school_year') ?? '';
|
|
$selectedYear = $this->request->getGet('school_year') ?? $currentYear;
|
|
$percentile = (float) ($this->request->getGet('percentile') ?? 75);
|
|
$percentile = max(1.0, min(99.0, $percentile));
|
|
|
|
$years = $db->table('student_class')
|
|
->select('school_year')->distinct()
|
|
->orderBy('school_year', 'DESC')
|
|
->get()->getResultArray();
|
|
$years = array_column($years, 'school_year');
|
|
|
|
$ey = $db->escape($selectedYear);
|
|
|
|
$rows = $db->table('student_class sc')
|
|
->select([
|
|
'sc.student_id',
|
|
'sc.class_section_id',
|
|
'cs.class_section_name',
|
|
'CONCAT(s.firstname, " ", s.lastname) AS student_name',
|
|
's.school_id',
|
|
's.gender',
|
|
'MAX(sf.semester_score) AS fall_score',
|
|
'MAX(sp.semester_score) AS spring_score',
|
|
])
|
|
->join('classSection cs', 'cs.class_section_id = sc.class_section_id', 'left')
|
|
->join('students s', 's.id = sc.student_id', 'left')
|
|
->join('semester_scores sf',
|
|
'sf.student_id = sc.student_id AND sf.class_section_id = sc.class_section_id'
|
|
. ' AND sf.school_year = ' . $ey . ' AND LOWER(sf.semester) = "fall"', 'left')
|
|
->join('semester_scores sp',
|
|
'sp.student_id = sc.student_id AND sp.class_section_id = sc.class_section_id'
|
|
. ' AND sp.school_year = ' . $ey . ' AND LOWER(sp.semester) = "spring"', 'left')
|
|
->where('sc.school_year', $selectedYear)
|
|
->orderBy('cs.class_section_name', 'ASC')
|
|
->orderBy('student_name', 'ASC')
|
|
->groupBy('sc.student_id, sc.class_section_id, cs.class_section_name, s.firstname, s.lastname, s.school_id, s.gender')
|
|
->get()->getResultArray();
|
|
|
|
$sections = [];
|
|
foreach ($rows as $row) {
|
|
$sid = (int) ($row['class_section_id'] ?? 0);
|
|
$name = $row['class_section_name'] ?? 'Section ' . $sid;
|
|
if (!isset($sections[$sid])) $sections[$sid] = ['name' => $name, 'students' => []];
|
|
|
|
$fall = is_numeric($row['fall_score']) ? (float) $row['fall_score'] : null;
|
|
$spring = is_numeric($row['spring_score']) ? (float) $row['spring_score'] : null;
|
|
$year = ($fall !== null && $spring !== null)
|
|
? round(($fall + $spring) / 2, 1)
|
|
: ($fall ?? $spring);
|
|
|
|
$sections[$sid]['students'][] = [
|
|
'name' => $row['student_name'],
|
|
'school_id' => $row['school_id'],
|
|
'gender' => $row['gender'] ?? '',
|
|
'fall_score' => $fall,
|
|
'spring_score' => $spring,
|
|
'year_score' => $year,
|
|
];
|
|
}
|
|
|
|
$classResults = [];
|
|
foreach ($sections as $section) {
|
|
$students = $section['students'];
|
|
|
|
$fallCalc = $this->calculateThreshold(array_column($students, 'fall_score'), $percentile);
|
|
$yearCalc = $this->calculateThreshold(array_column($students, 'year_score'), $percentile);
|
|
$fallThreshold = $fallCalc['threshold'];
|
|
$yearThreshold = $yearCalc['threshold'];
|
|
|
|
$annotated = array_map(static function (array $s) use ($fallThreshold, $yearThreshold): array {
|
|
$predicted = $fallThreshold !== null && $s['fall_score'] !== null && $s['fall_score'] >= $fallThreshold;
|
|
$actual = $yearThreshold !== null && $s['year_score'] !== null && $s['year_score'] >= $yearThreshold;
|
|
$status = match (true) {
|
|
$predicted && $actual => 'confirmed',
|
|
!$predicted && $actual => 'surprise',
|
|
$predicted && !$actual => 'missed',
|
|
default => 'none',
|
|
};
|
|
return $s + compact('predicted', 'actual', 'status');
|
|
}, $students);
|
|
|
|
usort($annotated, static function (array $a, array $b): int {
|
|
if ($a['year_score'] === null && $b['year_score'] === null) return strcmp($a['name'], $b['name']);
|
|
if ($a['year_score'] === null) return 1;
|
|
if ($b['year_score'] === null) return -1;
|
|
return $b['year_score'] <=> $a['year_score'] ?: strcmp($a['name'], $b['name']);
|
|
});
|
|
|
|
$nConfirmed = count(array_filter($annotated, static fn ($s) => $s['status'] === 'confirmed'));
|
|
$nSurprise = count(array_filter($annotated, static fn ($s) => $s['status'] === 'surprise'));
|
|
$nMissed = count(array_filter($annotated, static fn ($s) => $s['status'] === 'missed'));
|
|
$nPredicted = count(array_filter($annotated, static fn ($s) => $s['predicted']));
|
|
$nActual = count(array_filter($annotated, static fn ($s) => $s['actual']));
|
|
$n = count($annotated);
|
|
|
|
$classResults[] = [
|
|
'section_name' => $section['name'],
|
|
'students' => $annotated,
|
|
'student_count' => $n,
|
|
'fall_threshold' => $fallThreshold,
|
|
'year_threshold' => $yearThreshold,
|
|
'predicted_count' => $nPredicted,
|
|
'actual_count' => $nActual,
|
|
'confirmed' => $nConfirmed,
|
|
'surprises' => $nSurprise,
|
|
'missed' => $nMissed,
|
|
'accuracy' => $nPredicted > 0 ? round($nConfirmed / $nPredicted * 100) : ($nActual === 0 ? 100 : 0),
|
|
];
|
|
}
|
|
|
|
return view('administrator/trophy_final', [
|
|
'classResults' => $classResults,
|
|
'selectedYear' => $selectedYear,
|
|
'selectedPercentile' => $percentile,
|
|
'years' => $years,
|
|
]);
|
|
}
|
|
|
|
private function calculateThreshold(array $scores, float $percentile = 75.0): array
|
|
{
|
|
$scores = array_values(array_filter(
|
|
$scores,
|
|
static fn ($v) => is_numeric($v) && $v !== null
|
|
));
|
|
$scores = array_map('floatval', $scores);
|
|
sort($scores); // ascending
|
|
$n = count($scores);
|
|
|
|
if ($n === 0) {
|
|
return ['threshold' => null, 'winners' => 0, 'method' => 'empty'];
|
|
}
|
|
|
|
$minWinners = 3; // never fewer than 3, regardless of class size or score count
|
|
// Hard maximum: top (100 - percentile)% of class, but never below the minimum.
|
|
$maxWinners = max($minWinners, (int) floor($n * (1 - $percentile / 100)));
|
|
|
|
$threshold = $this->empiricalPercentile($scores, $percentile);
|
|
$winners = $this->countAtOrAbove($scores, $threshold);
|
|
|
|
if ($winners < $minWinners) {
|
|
// Too few qualify — REDUCE the threshold down to the score of the
|
|
// 3rd-ranked student so the minimum of 3 is always met.
|
|
// If fewer than 3 students have scores, award all of them.
|
|
$target = min($minWinners, $n);
|
|
$desc = array_reverse($scores); // descending
|
|
$threshold = $desc[$target - 1]; // score at rank $target
|
|
$winners = $this->countAtOrAbove($scores, $threshold); // ties included
|
|
return ['threshold' => $threshold, 'winners' => $winners, 'method' => 'min3_reduced'];
|
|
}
|
|
|
|
if ($winners <= $maxWinners) {
|
|
return ['threshold' => $threshold, 'winners' => $winners, 'method' => 'empirical_percentile'];
|
|
}
|
|
|
|
// Too many winners — raise threshold to respect the cap.
|
|
$result = $this->capByRank($scores, $maxWinners);
|
|
|
|
// capByRank may overshoot and drop below the minimum (e.g. bimodal scores
|
|
// where only 2 students are in the top cluster). Enforce min=3 here too.
|
|
if ($result['winners'] < $minWinners) {
|
|
$target = min($minWinners, $n);
|
|
$desc = array_reverse($scores);
|
|
$threshold = $desc[$target - 1];
|
|
$winners = $this->countAtOrAbove($scores, $threshold);
|
|
return ['threshold' => $threshold, 'winners' => $winners, 'method' => 'min3_after_cap'];
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Raise the threshold until at most $max students qualify.
|
|
* Returns the result without checking the minimum — caller must do that.
|
|
*/
|
|
private function capByRank(array $sortedScores, int $max): array
|
|
{
|
|
$desc = array_reverse($sortedScores);
|
|
$threshold = $desc[$max - 1];
|
|
$winners = $this->countAtOrAbove($sortedScores, $threshold);
|
|
|
|
if ($winners <= $max) {
|
|
return ['threshold' => $threshold, 'winners' => $winners, 'method' => 'capped_25pct'];
|
|
}
|
|
|
|
// Ties push it over — walk up through distinct scores.
|
|
$unique = array_values(array_unique(array_filter(
|
|
$sortedScores,
|
|
static fn ($s) => $s > $threshold
|
|
)));
|
|
sort($unique);
|
|
|
|
foreach ($unique as $candidate) {
|
|
$w = $this->countAtOrAbove($sortedScores, $candidate);
|
|
if ($w <= $max) {
|
|
return ['threshold' => $candidate, 'winners' => $w, 'method' => 'capped_25pct'];
|
|
}
|
|
}
|
|
|
|
// All scores are equal.
|
|
return ['threshold' => $sortedScores[0], 'winners' => count($sortedScores), 'method' => 'all_equal'];
|
|
}
|
|
|
|
private function empiricalPercentile(array $sortedScores, float $p): float
|
|
{
|
|
$n = count($sortedScores);
|
|
if ($n === 0) return 0.0;
|
|
$index = ($p / 100.0) * ($n - 1);
|
|
$lower = (int) floor($index);
|
|
$upper = (int) ceil($index);
|
|
if ($lower === $upper) return $sortedScores[$lower];
|
|
return $sortedScores[$lower] + ($index - $lower) * ($sortedScores[$upper] - $sortedScores[$lower]);
|
|
}
|
|
|
|
private function countAtOrAbove(array $scores, float $threshold): int
|
|
{
|
|
return count(array_filter($scores, static fn ($s) => $s >= $threshold));
|
|
}
|
|
|
|
}
|