Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
69.26% |
320 / 462 |
|
50.00% |
13 / 26 |
CRAP | |
0.00% |
0 / 1 |
| ReportService | |
69.26% |
320 / 462 |
|
50.00% |
13 / 26 |
376.86 | |
0.00% |
0 / 1 |
| __construct | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
1 | |||
| getIndexData | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
1 | |||
| getExportData | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
1 | |||
| buildCsv | |
88.57% |
31 / 35 |
|
0.00% |
0 / 1 |
10.15 | |||
| buildPayload | |
100.00% |
7 / 7 |
|
100.00% |
1 / 1 |
1 | |||
| buildFilters | |
94.34% |
50 / 53 |
|
0.00% |
0 / 1 |
7.01 | |||
| resolveDates | |
58.33% |
7 / 12 |
|
0.00% |
0 / 1 |
3.65 | |||
| buildReport | |
60.00% |
6 / 10 |
|
0.00% |
0 / 1 |
1.06 | |||
| scopedChildren | |
85.71% |
12 / 14 |
|
0.00% |
0 / 1 |
4.05 | |||
| reportChildrenByLevel | |
100.00% |
24 / 24 |
|
100.00% |
1 / 1 |
5 | |||
| reportChildrenByCoach | |
0.00% |
0 / 24 |
|
0.00% |
0 / 1 |
30 | |||
| reportPromotionStatus | |
100.00% |
24 / 24 |
|
100.00% |
1 / 1 |
7 | |||
| reportHighAbsence | |
0.00% |
0 / 43 |
|
0.00% |
0 / 1 |
90 | |||
| reportEvaluations | |
0.00% |
0 / 31 |
|
0.00% |
0 / 1 |
12 | |||
| reportChildProgress | |
73.85% |
48 / 65 |
|
0.00% |
0 / 1 |
11.79 | |||
| latestFullEvaluations | |
94.44% |
17 / 18 |
|
0.00% |
0 / 1 |
4.00 | |||
| coachOptions | |
100.00% |
8 / 8 |
|
100.00% |
1 / 1 |
2 | |||
| levelOptions | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
1 | |||
| childOptions | |
100.00% |
7 / 7 |
|
100.00% |
1 / 1 |
2 | |||
| settingsMap | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
2 | |||
| buildExportUrls | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
3 | |||
| exportQueryFromFilters | |
100.00% |
8 / 8 |
|
100.00% |
1 / 1 |
1 | |||
| buildAppliedFilters | |
82.76% |
24 / 29 |
|
0.00% |
0 / 1 |
5.13 | |||
| findOptionLabel | |
83.33% |
5 / 6 |
|
0.00% |
0 / 1 |
4.07 | |||
| buildFileBasename | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| stringValue | |
60.00% |
3 / 5 |
|
0.00% |
0 / 1 |
6.60 | |||
| 1 | <?php |
| 2 | |
| 3 | namespace App\Services; |
| 4 | |
| 5 | use CodeIgniter\Shield\Entities\User; |
| 6 | use DateTimeImmutable; |
| 7 | |
| 8 | class ReportService |
| 9 | { |
| 10 | private AttendanceService $attendanceService; |
| 11 | private AuthorizationService $authorizationService; |
| 12 | |
| 13 | public function __construct() |
| 14 | { |
| 15 | helper(['academy', 'url']); |
| 16 | |
| 17 | $this->attendanceService = service('attendance'); |
| 18 | $this->authorizationService = service('authorization'); |
| 19 | } |
| 20 | |
| 21 | /** |
| 22 | * @param array<string, mixed> $input |
| 23 | * @return array<string, mixed> |
| 24 | */ |
| 25 | public function getIndexData(array $input, User $user): array |
| 26 | { |
| 27 | $payload = $this->buildPayload($input, (int) $user->id); |
| 28 | |
| 29 | return [ |
| 30 | 'pageTitle' => 'Rapoarte', |
| 31 | 'pageDescription' => 'Set de rapoarte operationale filtrabile pentru administratie, training si discutii de promovare.', |
| 32 | 'filters' => $payload['filters'], |
| 33 | 'report' => $payload['report'], |
| 34 | 'appliedFilters' => $payload['applied_filters'], |
| 35 | 'exportUrls' => $this->buildExportUrls($payload['filters']), |
| 36 | ]; |
| 37 | } |
| 38 | |
| 39 | /** |
| 40 | * @param array<string, mixed> $input |
| 41 | * @return array<string, mixed> |
| 42 | */ |
| 43 | public function getExportData(array $input, User $user): array |
| 44 | { |
| 45 | $payload = $this->buildPayload($input, (int) $user->id); |
| 46 | $generatedAt = new DateTimeImmutable('now'); |
| 47 | |
| 48 | return array_merge($payload, [ |
| 49 | 'generated_at_label' => $generatedAt->format('d.m.Y H:i'), |
| 50 | 'file_basename' => $this->buildFileBasename($payload['filters'], $generatedAt), |
| 51 | ]); |
| 52 | } |
| 53 | |
| 54 | /** |
| 55 | * @param array<string, mixed> $exportData |
| 56 | */ |
| 57 | public function buildCsv(array $exportData): string |
| 58 | { |
| 59 | $stream = fopen('php://temp', 'r+'); |
| 60 | if ($stream === false) { |
| 61 | return ''; |
| 62 | } |
| 63 | |
| 64 | fwrite($stream, "\xEF\xBB\xBF"); |
| 65 | |
| 66 | fputcsv($stream, [$exportData['report']['title'] ?? 'Raport'], ';'); |
| 67 | fputcsv($stream, ['Descriere', (string) ($exportData['report']['description'] ?? '')], ';'); |
| 68 | fputcsv($stream, ['Generat la', (string) ($exportData['generated_at_label'] ?? '')], ';'); |
| 69 | |
| 70 | foreach ((array) ($exportData['applied_filters'] ?? []) as $filter) { |
| 71 | fputcsv($stream, [(string) ($filter['label'] ?? ''), (string) ($filter['value'] ?? '')], ';'); |
| 72 | } |
| 73 | |
| 74 | if (! empty($exportData['report']['summary'])) { |
| 75 | fwrite($stream, PHP_EOL); |
| 76 | fputcsv($stream, ['Rezumat'], ';'); |
| 77 | foreach ($exportData['report']['summary'] as $item) { |
| 78 | fputcsv($stream, [ |
| 79 | (string) ($item['label'] ?? ''), |
| 80 | $this->stringValue($item['value'] ?? null), |
| 81 | (string) ($item['meta'] ?? ''), |
| 82 | ], ';'); |
| 83 | } |
| 84 | } |
| 85 | |
| 86 | fwrite($stream, PHP_EOL); |
| 87 | |
| 88 | $columns = (array) ($exportData['report']['columns'] ?? []); |
| 89 | $rows = (array) ($exportData['report']['rows'] ?? []); |
| 90 | |
| 91 | if ($rows === []) { |
| 92 | $columns = $columns !== [] ? $columns : ['Mesaj']; |
| 93 | fputcsv($stream, $columns, ';'); |
| 94 | fputcsv($stream, [(string) ($exportData['report']['empty'] ?? 'Nu exista date pentru export.')], ';'); |
| 95 | } else { |
| 96 | fputcsv($stream, $columns, ';'); |
| 97 | foreach ($rows as $row) { |
| 98 | $line = []; |
| 99 | foreach ($columns as $column) { |
| 100 | $line[] = $this->stringValue($row[$column] ?? '-'); |
| 101 | } |
| 102 | |
| 103 | fputcsv($stream, $line, ';'); |
| 104 | } |
| 105 | } |
| 106 | |
| 107 | rewind($stream); |
| 108 | $csv = stream_get_contents($stream) ?: ''; |
| 109 | fclose($stream); |
| 110 | |
| 111 | return $csv; |
| 112 | } |
| 113 | |
| 114 | /** |
| 115 | * @param array<string, mixed> $input |
| 116 | * @return array<string, mixed> |
| 117 | */ |
| 118 | private function buildPayload(array $input, int $userId): array |
| 119 | { |
| 120 | $filters = $this->buildFilters($input, $userId); |
| 121 | $report = $this->buildReport($filters); |
| 122 | |
| 123 | return [ |
| 124 | 'filters' => $filters, |
| 125 | 'report' => $report, |
| 126 | 'applied_filters' => $this->buildAppliedFilters($filters), |
| 127 | ]; |
| 128 | } |
| 129 | |
| 130 | /** |
| 131 | * @param array<string, mixed> $input |
| 132 | * @return array<string, mixed> |
| 133 | */ |
| 134 | private function buildFilters(array $input, int $userId): array |
| 135 | { |
| 136 | $settings = $this->settingsMap(); |
| 137 | $role = $this->authorizationService->getPrimaryRole($userId); |
| 138 | $lockedTrainerId = $role === 'coach' ? $this->authorizationService->getCoachIdForUser($userId) : null; |
| 139 | $selectedReport = (string) ($input['report'] ?? 'children-by-level'); |
| 140 | $selectedPeriod = (string) ($input['period'] ?? ($settings['default_dashboard_period'] ?? '30')); |
| 141 | if ($selectedPeriod !== 'month' && ! in_array((int) $selectedPeriod, [7, 30, 90], true)) { |
| 142 | $selectedPeriod = '30'; |
| 143 | } |
| 144 | [$dateStart, $dateEnd] = $this->resolveDates($selectedPeriod, new DateTimeImmutable('today')); |
| 145 | $selectedTrainer = $lockedTrainerId ?: (int) ($input['coach_id'] ?? 0); |
| 146 | $selectedLevel = (int) ($input['level_id'] ?? 0); |
| 147 | $selectedStatus = (string) ($input['child_status'] ?? 'all'); |
| 148 | $selectedChild = (int) ($input['child_id'] ?? 0); |
| 149 | |
| 150 | if (! in_array($selectedReport, ['children-by-level', 'children-by-coach', 'ready-for-promotion', 'almost-ready', 'high-absence', 'evaluations-period', 'child-progress'], true)) { |
| 151 | $selectedReport = 'children-by-level'; |
| 152 | } |
| 153 | |
| 154 | if (! in_array($selectedStatus, ['all', 'active', 'pause', 'left'], true)) { |
| 155 | $selectedStatus = 'all'; |
| 156 | } |
| 157 | |
| 158 | return [ |
| 159 | 'current_role' => $role, |
| 160 | 'coach_locked' => $lockedTrainerId !== null, |
| 161 | 'selected_report' => $selectedReport, |
| 162 | 'selected_period' => $selectedPeriod, |
| 163 | 'selected_coach_id' => $selectedTrainer, |
| 164 | 'selected_level_id' => $selectedLevel, |
| 165 | 'selected_child_status' => $selectedStatus, |
| 166 | 'selected_child_id' => $selectedChild, |
| 167 | 'date_start' => $dateStart, |
| 168 | 'date_end' => $dateEnd, |
| 169 | 'report_options' => [ |
| 170 | 'children-by-level' => 'Copii pe nivel', |
| 171 | 'children-by-coach' => 'Copii pe trainer', |
| 172 | 'ready-for-promotion' => 'Ready pentru avansare', |
| 173 | 'almost-ready' => 'Aproape gata', |
| 174 | 'high-absence' => 'Absente ridicate', |
| 175 | 'evaluations-period' => 'Evaluari pe perioada', |
| 176 | 'child-progress' => 'Progres pe copil', |
| 177 | ], |
| 178 | 'period_options' => [ |
| 179 | '7' => 'Ultimele 7 zile', |
| 180 | '30' => 'Ultimele 30 zile', |
| 181 | '90' => 'Ultimele 90 zile', |
| 182 | 'month' => 'Luna curenta', |
| 183 | ], |
| 184 | 'status_options' => [ |
| 185 | 'all' => 'Toate statusurile', |
| 186 | 'active' => 'Activ', |
| 187 | 'pause' => 'Pauza', |
| 188 | 'left' => 'Plecat', |
| 189 | ], |
| 190 | 'coach_options' => $this->coachOptions($lockedTrainerId), |
| 191 | 'level_options' => $this->levelOptions(), |
| 192 | 'child_options' => $this->childOptions($lockedTrainerId), |
| 193 | 'absence_alert_threshold' => (int) ($settings['absence_alert_threshold'] ?? 25), |
| 194 | ]; |
| 195 | } |
| 196 | |
| 197 | /** |
| 198 | * @return array{0: string, 1: string} |
| 199 | */ |
| 200 | private function resolveDates(string $selectedPeriod, DateTimeImmutable $today): array |
| 201 | { |
| 202 | if ($selectedPeriod === 'month') { |
| 203 | return [ |
| 204 | $today->modify('first day of this month')->format('Y-m-d'), |
| 205 | $today->format('Y-m-d'), |
| 206 | ]; |
| 207 | } |
| 208 | |
| 209 | $days = (int) $selectedPeriod; |
| 210 | if (! in_array($days, [7, 30, 90], true)) { |
| 211 | $days = 30; |
| 212 | } |
| 213 | |
| 214 | return [ |
| 215 | $today->modify('-' . ($days - 1) . ' days')->format('Y-m-d'), |
| 216 | $today->format('Y-m-d'), |
| 217 | ]; |
| 218 | } |
| 219 | |
| 220 | /** |
| 221 | * @param array<string, mixed> $filters |
| 222 | * @return array<string, mixed> |
| 223 | */ |
| 224 | private function buildReport(array $filters): array |
| 225 | { |
| 226 | $children = $this->scopedChildren($filters); |
| 227 | |
| 228 | return match ($filters['selected_report']) { |
| 229 | 'children-by-coach' => $this->reportChildrenByCoach($children), |
| 230 | 'ready-for-promotion' => $this->reportPromotionStatus($children, 'READY'), |
| 231 | 'almost-ready' => $this->reportPromotionStatus($children, 'ALMOST READY'), |
| 232 | 'high-absence' => $this->reportHighAbsence($children, $filters), |
| 233 | 'evaluations-period' => $this->reportEvaluations($children, $filters), |
| 234 | 'child-progress' => $this->reportChildProgress($children, $filters), |
| 235 | default => $this->reportChildrenByLevel($children), |
| 236 | }; |
| 237 | } |
| 238 | |
| 239 | /** |
| 240 | * @param array<string, mixed> $filters |
| 241 | * @return list<array<string, mixed>> |
| 242 | */ |
| 243 | private function scopedChildren(array $filters): array |
| 244 | { |
| 245 | $builder = db_connect() |
| 246 | ->table('children c') |
| 247 | ->select('c.id, c.full_name, c.status, levels.name as level_name, groups.name as group_name, coaches.full_name as coach_name') |
| 248 | ->join('academy_levels levels', 'levels.id = c.academy_level_id', 'left') |
| 249 | ->join('academy_groups groups', 'groups.id = c.academy_group_id', 'left') |
| 250 | ->join('coaches', 'coaches.id = c.primary_coach_id', 'left') |
| 251 | ->orderBy('c.full_name', 'ASC'); |
| 252 | |
| 253 | if ($filters['selected_coach_id'] > 0) { |
| 254 | $builder->where('c.primary_coach_id', $filters['selected_coach_id']); |
| 255 | } |
| 256 | |
| 257 | if ($filters['selected_level_id'] > 0) { |
| 258 | $builder->where('c.academy_level_id', $filters['selected_level_id']); |
| 259 | } |
| 260 | |
| 261 | if ($filters['selected_child_status'] !== 'all') { |
| 262 | $builder->where('c.status', $filters['selected_child_status']); |
| 263 | } |
| 264 | |
| 265 | return $builder->get()->getResultArray(); |
| 266 | } |
| 267 | |
| 268 | /** |
| 269 | * @param list<array<string, mixed>> $children |
| 270 | * @return array<string, mixed> |
| 271 | */ |
| 272 | private function reportChildrenByLevel(array $children): array |
| 273 | { |
| 274 | $grouped = []; |
| 275 | foreach ($children as $child) { |
| 276 | $level = $child['level_name'] ?: 'Neasignat'; |
| 277 | if (! isset($grouped[$level])) { |
| 278 | $grouped[$level] = ['level_name' => $level, 'children' => 0, 'active' => 0]; |
| 279 | } |
| 280 | |
| 281 | $grouped[$level]['children']++; |
| 282 | if ($child['status'] === 'active') { |
| 283 | $grouped[$level]['active']++; |
| 284 | } |
| 285 | } |
| 286 | ksort($grouped); |
| 287 | |
| 288 | return [ |
| 289 | 'title' => 'Copii pe nivel', |
| 290 | 'description' => 'Structura curenta a academiei pe niveluri active si statusuri operationale.', |
| 291 | 'columns' => ['Nivel', 'Total copii', 'Activi'], |
| 292 | 'rows' => array_values(array_map(static fn (array $row): array => [ |
| 293 | 'Nivel' => $row['level_name'], |
| 294 | 'Total copii' => (string) $row['children'], |
| 295 | 'Activi' => (string) $row['active'], |
| 296 | ], $grouped)), |
| 297 | 'summary' => [ |
| 298 | ['label' => 'Niveluri cu copii', 'value' => count($grouped), 'meta' => 'Doar niveluri cu date in selectie'], |
| 299 | ['label' => 'Copii in raport', 'value' => count($children), 'meta' => 'Total randuri incluse'], |
| 300 | ], |
| 301 | 'empty' => 'Nu exista copii pentru filtrele selectate.', |
| 302 | ]; |
| 303 | } |
| 304 | |
| 305 | /** |
| 306 | * @param list<array<string, mixed>> $children |
| 307 | * @return array<string, mixed> |
| 308 | */ |
| 309 | private function reportChildrenByCoach(array $children): array |
| 310 | { |
| 311 | $grouped = []; |
| 312 | foreach ($children as $child) { |
| 313 | $coach = $child['coach_name'] ?: 'Neasignat'; |
| 314 | if (! isset($grouped[$coach])) { |
| 315 | $grouped[$coach] = ['coach_name' => $coach, 'children' => 0, 'active' => 0]; |
| 316 | } |
| 317 | |
| 318 | $grouped[$coach]['children']++; |
| 319 | if ($child['status'] === 'active') { |
| 320 | $grouped[$coach]['active']++; |
| 321 | } |
| 322 | } |
| 323 | ksort($grouped); |
| 324 | |
| 325 | return [ |
| 326 | 'title' => 'Copii pe trainer', |
| 327 | 'description' => 'Incarcarea actuala pe trainer, folosind asignarea principala a copilului.', |
| 328 | 'columns' => ['Trainer', 'Total copii', 'Activi'], |
| 329 | 'rows' => array_values(array_map(static fn (array $row): array => [ |
| 330 | 'Trainer' => $row['coach_name'], |
| 331 | 'Total copii' => (string) $row['children'], |
| 332 | 'Activi' => (string) $row['active'], |
| 333 | ], $grouped)), |
| 334 | 'summary' => [ |
| 335 | ['label' => 'Traineri in selectie', 'value' => count($grouped), 'meta' => 'Cu cel putin un copil in raport'], |
| 336 | ['label' => 'Copii activi', 'value' => count(array_filter($children, static fn (array $child): bool => $child['status'] === 'active')), 'meta' => 'Activi in selectie'], |
| 337 | ], |
| 338 | 'empty' => 'Nu exista copii pentru filtrele selectate.', |
| 339 | ]; |
| 340 | } |
| 341 | |
| 342 | /** |
| 343 | * @param list<array<string, mixed>> $children |
| 344 | * @return array<string, mixed> |
| 345 | */ |
| 346 | private function reportPromotionStatus(array $children, string $targetStatus): array |
| 347 | { |
| 348 | $childIds = array_values(array_map(static fn (array $child): int => (int) $child['id'], $children)); |
| 349 | $latest = $this->latestFullEvaluations($childIds); |
| 350 | $rows = []; |
| 351 | |
| 352 | foreach ($children as $child) { |
| 353 | $evaluation = $latest[(int) $child['id']] ?? null; |
| 354 | if ($evaluation === null || $evaluation['final_status'] !== $targetStatus) { |
| 355 | continue; |
| 356 | } |
| 357 | |
| 358 | $rows[] = [ |
| 359 | 'Copil' => $child['full_name'], |
| 360 | 'Nivel' => $child['level_name'] ?: '-', |
| 361 | 'Trainer' => $child['coach_name'] ?: '-', |
| 362 | 'Data evaluarii' => $evaluation['evaluation_date'], |
| 363 | 'Scor final' => format_score($evaluation['final_score']), |
| 364 | ]; |
| 365 | } |
| 366 | |
| 367 | return [ |
| 368 | 'title' => $targetStatus === 'READY' ? 'Copii ready pentru avansare' : 'Copii aproape gata', |
| 369 | 'description' => 'Ultimul full evaluation este folosit ca sursa de adevar pentru statusul de promovare.', |
| 370 | 'columns' => ['Copil', 'Nivel', 'Trainer', 'Data evaluarii', 'Scor final'], |
| 371 | 'rows' => $rows, |
| 372 | 'summary' => [ |
| 373 | ['label' => 'Copii in raport', 'value' => count($rows), 'meta' => $targetStatus], |
| 374 | ], |
| 375 | 'empty' => 'Nu exista copii care sa corespunda acestui status in selectie.', |
| 376 | ]; |
| 377 | } |
| 378 | |
| 379 | /** |
| 380 | * @param list<array<string, mixed>> $children |
| 381 | * @param array<string, mixed> $filters |
| 382 | * @return array<string, mixed> |
| 383 | */ |
| 384 | private function reportHighAbsence(array $children, array $filters): array |
| 385 | { |
| 386 | $childIds = array_values(array_map(static fn (array $child): int => (int) $child['id'], $children)); |
| 387 | $rows = db_connect() |
| 388 | ->table('attendance_records ar') |
| 389 | ->select('ar.child_id, ar.status') |
| 390 | ->join('attendance_sessions sessions', 'sessions.id = ar.attendance_session_id') |
| 391 | ->whereIn('ar.child_id', $childIds === [] ? [0] : $childIds) |
| 392 | ->where('sessions.is_cancelled', 0) |
| 393 | ->where('sessions.session_date >=', $filters['date_start']) |
| 394 | ->where('sessions.session_date <=', $filters['date_end']) |
| 395 | ->get() |
| 396 | ->getResultArray(); |
| 397 | |
| 398 | $byChild = []; |
| 399 | foreach ($rows as $row) { |
| 400 | $childId = (int) $row['child_id']; |
| 401 | if (! isset($byChild[$childId])) { |
| 402 | $byChild[$childId] = ['present' => 0, 'recovery' => 0, 'absent_excused' => 0, 'absent_unexcused' => 0]; |
| 403 | } |
| 404 | |
| 405 | $byChild[$childId][$row['status']]++; |
| 406 | } |
| 407 | |
| 408 | $reportRows = []; |
| 409 | foreach ($children as $child) { |
| 410 | $breakdown = $byChild[(int) $child['id']] ?? ['present' => 0, 'recovery' => 0, 'absent_excused' => 0, 'absent_unexcused' => 0]; |
| 411 | if (array_sum($breakdown) === 0) { |
| 412 | continue; |
| 413 | } |
| 414 | |
| 415 | $attendance = $this->attendanceService->calculateRateFromBreakdown($breakdown); |
| 416 | $absence = 100 - $attendance; |
| 417 | |
| 418 | if ($absence < (float) $filters['absence_alert_threshold']) { |
| 419 | continue; |
| 420 | } |
| 421 | |
| 422 | $reportRows[] = [ |
| 423 | 'Copil' => $child['full_name'], |
| 424 | 'Grupa' => $child['group_name'] ?: '-', |
| 425 | 'Trainer' => $child['coach_name'] ?: '-', |
| 426 | 'Prezenta' => format_percentage($attendance, 1), |
| 427 | 'Absenta' => format_percentage($absence, 1), |
| 428 | ]; |
| 429 | } |
| 430 | |
| 431 | return [ |
| 432 | 'title' => 'Copii cu absente ridicate', |
| 433 | 'description' => 'Absenta este calculata in perioada filtrata, excluzand sesiunile anulate.', |
| 434 | 'columns' => ['Copil', 'Grupa', 'Trainer', 'Prezenta', 'Absenta'], |
| 435 | 'rows' => $reportRows, |
| 436 | 'summary' => [ |
| 437 | ['label' => 'Peste prag', 'value' => count($reportRows), 'meta' => 'Prag ' . format_percentage((float) $filters['absence_alert_threshold'], 0)], |
| 438 | ], |
| 439 | 'empty' => 'Nu exista copii cu absente peste prag in perioada filtrata.', |
| 440 | ]; |
| 441 | } |
| 442 | |
| 443 | /** |
| 444 | * @param list<array<string, mixed>> $children |
| 445 | * @param array<string, mixed> $filters |
| 446 | * @return array<string, mixed> |
| 447 | */ |
| 448 | private function reportEvaluations(array $children, array $filters): array |
| 449 | { |
| 450 | $childIds = array_values(array_map(static fn (array $child): int => (int) $child['id'], $children)); |
| 451 | $rows = db_connect() |
| 452 | ->table('evaluations e') |
| 453 | ->select('e.evaluation_date, e.final_score, e.final_status, children.full_name as child_name, types.name as type_name, coaches.full_name as evaluator_name') |
| 454 | ->join('children', 'children.id = e.child_id') |
| 455 | ->join('evaluation_types types', 'types.id = e.evaluation_type_id') |
| 456 | ->join('coaches', 'coaches.id = e.evaluator_coach_id', 'left') |
| 457 | ->whereIn('e.child_id', $childIds === [] ? [0] : $childIds) |
| 458 | ->where('e.evaluation_date >=', $filters['date_start']) |
| 459 | ->where('e.evaluation_date <=', $filters['date_end']) |
| 460 | ->orderBy('e.evaluation_date', 'DESC') |
| 461 | ->orderBy('e.id', 'DESC') |
| 462 | ->get() |
| 463 | ->getResultArray(); |
| 464 | |
| 465 | return [ |
| 466 | 'title' => 'Evaluari pe perioada', |
| 467 | 'description' => 'Lista cronologica de evaluari quick si full pentru perioada selectata.', |
| 468 | 'columns' => ['Data', 'Copil', 'Tip', 'Evaluator', 'Scor', 'Status'], |
| 469 | 'rows' => array_map(static fn (array $row): array => [ |
| 470 | 'Data' => $row['evaluation_date'], |
| 471 | 'Copil' => $row['child_name'], |
| 472 | 'Tip' => $row['type_name'], |
| 473 | 'Evaluator' => $row['evaluator_name'] ?: '-', |
| 474 | 'Scor' => format_score($row['final_score']), |
| 475 | 'Status' => status_label($row['final_status']), |
| 476 | ], $rows), |
| 477 | 'summary' => [ |
| 478 | ['label' => 'Evaluari in raport', 'value' => count($rows), 'meta' => $filters['date_start'] . ' - ' . $filters['date_end']], |
| 479 | ], |
| 480 | 'empty' => 'Nu exista evaluari in perioada selectata.', |
| 481 | ]; |
| 482 | } |
| 483 | |
| 484 | /** |
| 485 | * @param list<array<string, mixed>> $children |
| 486 | * @param array<string, mixed> $filters |
| 487 | * @return array<string, mixed> |
| 488 | */ |
| 489 | private function reportChildProgress(array $children, array $filters): array |
| 490 | { |
| 491 | $selectedChildId = (int) $filters['selected_child_id']; |
| 492 | if ($selectedChildId === 0 && $children !== []) { |
| 493 | $selectedChildId = (int) $children[0]['id']; |
| 494 | } |
| 495 | |
| 496 | if ($selectedChildId === 0) { |
| 497 | return [ |
| 498 | 'title' => 'Progres pe copil', |
| 499 | 'description' => 'Selecteaza un copil din filtru pentru a vedea istoricul evaluarilor si prezentei.', |
| 500 | 'columns' => [], |
| 501 | 'rows' => [], |
| 502 | 'summary' => [], |
| 503 | 'empty' => 'Nu exista copii disponibili pentru acest raport.', |
| 504 | ]; |
| 505 | } |
| 506 | |
| 507 | $selectedChild = array_values(array_filter($children, static fn (array $child): bool => (int) $child['id'] === $selectedChildId))[0] ?? null; |
| 508 | if ($selectedChild === null) { |
| 509 | return [ |
| 510 | 'title' => 'Progres pe copil', |
| 511 | 'description' => 'Copilul selectat nu este disponibil in aria curenta de acces.', |
| 512 | 'columns' => [], |
| 513 | 'rows' => [], |
| 514 | 'summary' => [], |
| 515 | 'empty' => 'Selectia curenta nu este valida.', |
| 516 | ]; |
| 517 | } |
| 518 | |
| 519 | $evaluations = db_connect() |
| 520 | ->table('evaluations e') |
| 521 | ->select('e.evaluation_date, e.final_score, e.final_status, types.name as type_name') |
| 522 | ->join('evaluation_types types', 'types.id = e.evaluation_type_id') |
| 523 | ->where('e.child_id', $selectedChildId) |
| 524 | ->orderBy('e.evaluation_date', 'DESC') |
| 525 | ->orderBy('e.id', 'DESC') |
| 526 | ->get() |
| 527 | ->getResultArray(); |
| 528 | |
| 529 | $attendanceRows = db_connect() |
| 530 | ->table('attendance_records ar') |
| 531 | ->select('ar.status') |
| 532 | ->join('attendance_sessions sessions', 'sessions.id = ar.attendance_session_id') |
| 533 | ->where('ar.child_id', $selectedChildId) |
| 534 | ->where('sessions.is_cancelled', 0) |
| 535 | ->where('sessions.session_date >=', $filters['date_start']) |
| 536 | ->where('sessions.session_date <=', $filters['date_end']) |
| 537 | ->get() |
| 538 | ->getResultArray(); |
| 539 | |
| 540 | $breakdown = ['present' => 0, 'recovery' => 0, 'absent_excused' => 0, 'absent_unexcused' => 0]; |
| 541 | foreach ($attendanceRows as $row) { |
| 542 | $status = (string) $row['status']; |
| 543 | if (isset($breakdown[$status])) { |
| 544 | $breakdown[$status]++; |
| 545 | } |
| 546 | } |
| 547 | |
| 548 | $avgScore = $evaluations === [] ? null : round(array_sum(array_map(static fn (array $row): float => (float) $row['final_score'], $evaluations)) / count($evaluations), 2); |
| 549 | |
| 550 | return [ |
| 551 | 'title' => 'Progres copil: ' . $selectedChild['full_name'], |
| 552 | 'description' => 'Istoric de evaluari si rata de prezenta in perioada selectata.', |
| 553 | 'columns' => ['Data', 'Tip', 'Scor', 'Status'], |
| 554 | 'rows' => array_map(static fn (array $row): array => [ |
| 555 | 'Data' => $row['evaluation_date'], |
| 556 | 'Tip' => $row['type_name'], |
| 557 | 'Scor' => format_score($row['final_score']), |
| 558 | 'Status' => status_label($row['final_status']), |
| 559 | ], $evaluations), |
| 560 | 'summary' => [ |
| 561 | ['label' => 'Copil selectat', 'value' => $selectedChild['full_name'], 'meta' => ($selectedChild['level_name'] ?: '-') . ' / ' . ($selectedChild['coach_name'] ?: '-')], |
| 562 | ['label' => 'Scor mediu', 'value' => format_score($avgScore), 'meta' => 'Toate evaluarile disponibile'], |
| 563 | ['label' => 'Prezenta', 'value' => format_percentage($this->attendanceService->calculateRateFromBreakdown($breakdown), 1), 'meta' => $filters['date_start'] . ' - ' . $filters['date_end']], |
| 564 | ], |
| 565 | 'empty' => 'Copilul selectat nu are evaluari inregistrate.', |
| 566 | 'detailUrl' => url_to('children.show', $selectedChildId), |
| 567 | ]; |
| 568 | } |
| 569 | |
| 570 | /** |
| 571 | * @param list<int> $childIds |
| 572 | * @return array<int, array<string, mixed>> |
| 573 | */ |
| 574 | private function latestFullEvaluations(array $childIds): array |
| 575 | { |
| 576 | if ($childIds === []) { |
| 577 | return []; |
| 578 | } |
| 579 | |
| 580 | $rows = db_connect() |
| 581 | ->table('evaluations e') |
| 582 | ->select('e.child_id, e.evaluation_date, e.final_score, e.final_status') |
| 583 | ->join('evaluation_types types', 'types.id = e.evaluation_type_id') |
| 584 | ->where('types.slug', 'full-evaluation') |
| 585 | ->whereIn('e.child_id', $childIds) |
| 586 | ->orderBy('e.evaluation_date', 'DESC') |
| 587 | ->orderBy('e.id', 'DESC') |
| 588 | ->get() |
| 589 | ->getResultArray(); |
| 590 | |
| 591 | $latest = []; |
| 592 | foreach ($rows as $row) { |
| 593 | $childId = (int) $row['child_id']; |
| 594 | if (! isset($latest[$childId])) { |
| 595 | $latest[$childId] = $row; |
| 596 | } |
| 597 | } |
| 598 | |
| 599 | return $latest; |
| 600 | } |
| 601 | |
| 602 | /** |
| 603 | * @return list<array<string, mixed>> |
| 604 | */ |
| 605 | private function coachOptions(?int $lockedTrainerId): array |
| 606 | { |
| 607 | $builder = db_connect() |
| 608 | ->table('coaches') |
| 609 | ->select('id, full_name') |
| 610 | ->where('is_active', 1) |
| 611 | ->orderBy('full_name', 'ASC'); |
| 612 | |
| 613 | if ($lockedTrainerId !== null) { |
| 614 | $builder->where('id', $lockedTrainerId); |
| 615 | } |
| 616 | |
| 617 | return $builder->get()->getResultArray(); |
| 618 | } |
| 619 | |
| 620 | /** |
| 621 | * @return list<array<string, mixed>> |
| 622 | */ |
| 623 | private function levelOptions(): array |
| 624 | { |
| 625 | return db_connect() |
| 626 | ->table('academy_levels') |
| 627 | ->select('id, name') |
| 628 | ->orderBy('sort_order', 'ASC') |
| 629 | ->get() |
| 630 | ->getResultArray(); |
| 631 | } |
| 632 | |
| 633 | /** |
| 634 | * @return list<array<string, mixed>> |
| 635 | */ |
| 636 | private function childOptions(?int $lockedTrainerId): array |
| 637 | { |
| 638 | $builder = db_connect() |
| 639 | ->table('children') |
| 640 | ->select('id, full_name') |
| 641 | ->orderBy('full_name', 'ASC'); |
| 642 | |
| 643 | if ($lockedTrainerId !== null) { |
| 644 | $builder->where('primary_coach_id', $lockedTrainerId); |
| 645 | } |
| 646 | |
| 647 | return $builder->get()->getResultArray(); |
| 648 | } |
| 649 | |
| 650 | /** |
| 651 | * @return array<string, string> |
| 652 | */ |
| 653 | private function settingsMap(): array |
| 654 | { |
| 655 | $rows = db_connect() |
| 656 | ->table('settings') |
| 657 | ->select('`key`, value') |
| 658 | ->get() |
| 659 | ->getResultArray(); |
| 660 | |
| 661 | $settings = []; |
| 662 | foreach ($rows as $row) { |
| 663 | $settings[$row['key']] = $row['value']; |
| 664 | } |
| 665 | |
| 666 | return $settings; |
| 667 | } |
| 668 | |
| 669 | /** |
| 670 | * @param array<string, mixed> $filters |
| 671 | * @return array<string, string> |
| 672 | */ |
| 673 | private function buildExportUrls(array $filters): array |
| 674 | { |
| 675 | $query = http_build_query($this->exportQueryFromFilters($filters)); |
| 676 | |
| 677 | return [ |
| 678 | 'csv' => url_to('reports.export', 'csv') . ($query !== '' ? '?' . $query : ''), |
| 679 | 'pdf' => url_to('reports.export', 'pdf') . ($query !== '' ? '?' . $query : ''), |
| 680 | ]; |
| 681 | } |
| 682 | |
| 683 | /** |
| 684 | * @param array<string, mixed> $filters |
| 685 | * @return array<string, int|string> |
| 686 | */ |
| 687 | private function exportQueryFromFilters(array $filters): array |
| 688 | { |
| 689 | return [ |
| 690 | 'report' => (string) $filters['selected_report'], |
| 691 | 'period' => (string) $filters['selected_period'], |
| 692 | 'coach_id' => (int) $filters['selected_coach_id'], |
| 693 | 'level_id' => (int) $filters['selected_level_id'], |
| 694 | 'child_status' => (string) $filters['selected_child_status'], |
| 695 | 'child_id' => (int) $filters['selected_child_id'], |
| 696 | ]; |
| 697 | } |
| 698 | |
| 699 | /** |
| 700 | * @param array<string, mixed> $filters |
| 701 | * @return list<array{label: string, value: string}> |
| 702 | */ |
| 703 | private function buildAppliedFilters(array $filters): array |
| 704 | { |
| 705 | $items = [ |
| 706 | [ |
| 707 | 'label' => 'Raport', |
| 708 | 'value' => (string) ($filters['report_options'][$filters['selected_report']] ?? $filters['selected_report']), |
| 709 | ], |
| 710 | [ |
| 711 | 'label' => 'Perioada', |
| 712 | 'value' => (string) ($filters['period_options'][$filters['selected_period']] ?? $filters['selected_period']), |
| 713 | ], |
| 714 | [ |
| 715 | 'label' => 'Interval', |
| 716 | 'value' => (string) $filters['date_start'] . ' - ' . (string) $filters['date_end'], |
| 717 | ], |
| 718 | ]; |
| 719 | |
| 720 | $coachName = $this->findOptionLabel((array) $filters['coach_options'], (int) $filters['selected_coach_id'], 'full_name'); |
| 721 | if ($coachName !== null) { |
| 722 | $items[] = ['label' => 'Trainer', 'value' => $coachName]; |
| 723 | } |
| 724 | |
| 725 | $levelName = $this->findOptionLabel((array) $filters['level_options'], (int) $filters['selected_level_id'], 'name'); |
| 726 | if ($levelName !== null) { |
| 727 | $items[] = ['label' => 'Nivel', 'value' => $levelName]; |
| 728 | } |
| 729 | |
| 730 | if ((string) $filters['selected_child_status'] !== 'all') { |
| 731 | $items[] = [ |
| 732 | 'label' => 'Status copil', |
| 733 | 'value' => (string) ($filters['status_options'][$filters['selected_child_status']] ?? $filters['selected_child_status']), |
| 734 | ]; |
| 735 | } |
| 736 | |
| 737 | $childName = $this->findOptionLabel((array) $filters['child_options'], (int) $filters['selected_child_id'], 'full_name'); |
| 738 | if ($childName !== null) { |
| 739 | $items[] = ['label' => 'Copil', 'value' => $childName]; |
| 740 | } |
| 741 | |
| 742 | return $items; |
| 743 | } |
| 744 | |
| 745 | /** |
| 746 | * @param list<array<string, mixed>> $options |
| 747 | */ |
| 748 | private function findOptionLabel(array $options, int $selectedId, string $labelKey): ?string |
| 749 | { |
| 750 | if ($selectedId <= 0) { |
| 751 | return null; |
| 752 | } |
| 753 | |
| 754 | foreach ($options as $option) { |
| 755 | if ((int) ($option['id'] ?? 0) === $selectedId) { |
| 756 | return (string) ($option[$labelKey] ?? ''); |
| 757 | } |
| 758 | } |
| 759 | |
| 760 | return null; |
| 761 | } |
| 762 | |
| 763 | /** |
| 764 | * @param array<string, mixed> $filters |
| 765 | */ |
| 766 | private function buildFileBasename(array $filters, DateTimeImmutable $generatedAt): string |
| 767 | { |
| 768 | return 'avh-kids-' . url_title((string) $filters['selected_report'], '-', true) . '-' . $generatedAt->format('Ymd-Hi'); |
| 769 | } |
| 770 | |
| 771 | private function stringValue(mixed $value): string |
| 772 | { |
| 773 | if ($value === null || $value === '') { |
| 774 | return '-'; |
| 775 | } |
| 776 | |
| 777 | if (is_scalar($value)) { |
| 778 | return (string) $value; |
| 779 | } |
| 780 | |
| 781 | return json_encode($value, JSON_UNESCAPED_UNICODE) ?: '-'; |
| 782 | } |
| 783 | } |