root
2252 lines
93 KiB
PHP
2252 lines
93 KiB
PHP
<?php
|
|
|
|
namespace App\Controllers\View;
|
|
|
|
use App\Models\CalendarModel;
|
|
use App\Models\AttendanceDataModel;
|
|
use App\Models\ReportCardAcknowledgementModel;
|
|
use App\Services\SemesterRangeService;
|
|
|
|
class ReportCardsController extends PrintablesBaseController
|
|
{
|
|
protected $calendarModel;
|
|
protected $semesterRangeService;
|
|
protected $ackModel;
|
|
|
|
public function __construct()
|
|
{
|
|
parent::__construct();
|
|
$this->calendarModel = new CalendarModel();
|
|
$this->semesterRangeService = new SemesterRangeService($this->configModel);
|
|
$this->ackModel = new ReportCardAcknowledgementModel();
|
|
}
|
|
|
|
public function report_card()
|
|
{
|
|
return view('printables_reports/report_card', [
|
|
'schoolYear' => (string)($this->schoolYear ?? ''),
|
|
'semester' => (string)($this->semester ?? ''),
|
|
]);
|
|
}
|
|
|
|
/**
|
|
* API: report card meta for UI hydration
|
|
* GET params: school_year
|
|
* Returns: { ok, schoolYears, selectedYear, students:[{id,firstname,lastname}], classSections:[{id,name}] }
|
|
*/
|
|
public function reportCardMeta()
|
|
{
|
|
$year = trim((string)($this->request->getGet('school_year') ?? $this->schoolYear ?? ''));
|
|
$sem = trim((string)($this->request->getGet('semester') ?? $this->semester ?? ''));
|
|
if ($year === '') {
|
|
// fallback to latest present in classSection or semester_score
|
|
$yr = $this->db->table('classSection')->select('school_year')->orderBy('school_year', 'DESC')->get(1)->getRowArray();
|
|
$year = (string)($yr['school_year'] ?? '');
|
|
}
|
|
|
|
// Build schoolYears from classSection and semester_scores
|
|
$schoolYears = [];
|
|
try {
|
|
$q1 = $this->db->table('classSection')->select('DISTINCT school_year', false)->where('school_year IS NOT NULL', null, false)->orderBy('school_year', 'DESC')->get()->getResultArray();
|
|
$schoolYears = array_values(array_filter(array_map(static fn($r) => (string)($r['school_year'] ?? ''), $q1)));
|
|
} catch (\Throwable $e) {
|
|
}
|
|
try {
|
|
$q2 = $this->db->table('semester_scores')->select('DISTINCT school_year', false)->where('school_year IS NOT NULL', null, false)->orderBy('school_year', 'DESC')->get()->getResultArray();
|
|
foreach ($q2 as $r) {
|
|
$val = (string)($r['school_year'] ?? '');
|
|
if ($val !== '' && !in_array($val, $schoolYears, true)) $schoolYears[] = $val;
|
|
}
|
|
} catch (\Throwable $e) {
|
|
}
|
|
rsort($schoolYears);
|
|
|
|
// Semesters available for the selected year (from scores + rosters), with defaults
|
|
$semesters = [];
|
|
try {
|
|
$rs = $this->db->table('semester_scores')
|
|
->select('DISTINCT semester', false)
|
|
->where('school_year', $year)
|
|
->where('semester IS NOT NULL', null, false)
|
|
->orderBy('semester', 'ASC')
|
|
->get()->getResultArray();
|
|
$semesters = array_values(array_filter(array_map(static fn($r) => (string)($r['semester'] ?? ''), $rs)));
|
|
} catch (\Throwable $e) {
|
|
}
|
|
// Ensure standard options are present even if data is missing (so Spring can be selected)
|
|
$defaults = array_values(array_filter([(string)($this->semester ?? ''), 'Fall', 'Spring'], static fn($v) => $v !== ''));
|
|
foreach ($defaults as $d) {
|
|
if (!in_array($d, $semesters, true)) $semesters[] = $d;
|
|
}
|
|
$semesters = array_values(array_unique($semesters));
|
|
// Preserve existing selected semester if provided, otherwise pick the first
|
|
if ($sem === '' && !empty($semesters)) {
|
|
$sem = $semesters[0];
|
|
} elseif ($sem !== '' && !in_array($sem, $semesters, true)) {
|
|
array_unshift($semesters, $sem);
|
|
$semesters = array_values(array_unique($semesters));
|
|
}
|
|
|
|
// Students: prefer those with scores in selected year (+semester if provided); fallback to all
|
|
$students = [];
|
|
try {
|
|
$builder = $this->db->table('semester_scores ss')
|
|
->select('s.id, s.firstname, s.lastname, GROUP_CONCAT(DISTINCT cs.class_section_name ORDER BY cs.class_section_name SEPARATOR ", ") AS class_section_name', false)
|
|
->join('students s', 's.id = ss.student_id', 'inner')
|
|
->join('student_class sc', 'sc.student_id = s.id AND sc.school_year = ss.school_year', 'left')
|
|
->join('classSection cs', 'cs.class_section_id = sc.class_section_id', 'left')
|
|
->where('s.is_active', 1)
|
|
->where('ss.school_year', $year);
|
|
if ($sem !== '') {
|
|
$this->applySemesterFilter($builder, $sem, 'ss.semester');
|
|
}
|
|
$stuRows = $builder
|
|
->groupBy('s.id, s.firstname, s.lastname')
|
|
->orderBy('class_section_name', 'ASC')
|
|
->orderBy('s.firstname', 'ASC')
|
|
->orderBy('s.lastname', 'ASC')
|
|
->get()
|
|
->getResultArray();
|
|
if (!empty($stuRows)) $students = $stuRows;
|
|
} catch (\Throwable $e) {
|
|
}
|
|
if (empty($students)) {
|
|
$fallback = $this->studentModel
|
|
->select('students.id, students.firstname, students.lastname, GROUP_CONCAT(DISTINCT cs.class_section_name ORDER BY cs.class_section_name SEPARATOR ", ") AS class_section_name', false)
|
|
->join('student_class sc', 'sc.student_id = students.id', 'left')
|
|
->join('classSection cs', 'cs.class_section_id = sc.class_section_id', 'left');
|
|
if ($year !== '') {
|
|
$fallback->where('sc.school_year', $year);
|
|
}
|
|
$students = $fallback
|
|
->where('students.is_active', 1)
|
|
->groupBy('students.id, students.firstname, students.lastname')
|
|
->orderBy('class_section_name', 'ASC')
|
|
->orderBy('students.firstname', 'ASC')
|
|
->orderBy('students.lastname', 'ASC')
|
|
->findAll();
|
|
}
|
|
|
|
// Class sections with at least one student for the selected year/semester
|
|
$classSections = [];
|
|
try {
|
|
$builder = $this->db->table('classSection cs')
|
|
->select('cs.id, cs.class_section_id, cs.class_section_name, cs.school_year')
|
|
->join('student_class sc', 'sc.class_section_id = cs.class_section_id', 'inner')
|
|
->join('students s', 's.id = sc.student_id', 'inner')
|
|
->where('s.is_active', 1)
|
|
->where('sc.school_year', $year);
|
|
$classSections = $builder
|
|
->groupBy('cs.id, cs.class_section_id, cs.class_section_name, cs.school_year')
|
|
->orderBy('cs.class_section_name', 'ASC')
|
|
->get()
|
|
->getResultArray();
|
|
|
|
// If no matches for the semester filter, retry for the year only
|
|
if (empty($classSections) && $sem !== '') {
|
|
$classSections = $this->db->table('classSection cs')
|
|
->select('cs.id, cs.class_section_id, cs.class_section_name, cs.school_year')
|
|
->join('student_class sc', 'sc.class_section_id = cs.class_section_id', 'inner')
|
|
->join('students s', 's.id = sc.student_id', 'inner')
|
|
->where('s.is_active', 1)
|
|
->where('sc.school_year', $year)
|
|
->groupBy('cs.id, cs.class_section_id, cs.class_section_name, cs.school_year')
|
|
->orderBy('cs.class_section_name', 'ASC')
|
|
->get()
|
|
->getResultArray();
|
|
}
|
|
} catch (\Throwable $e) {
|
|
}
|
|
|
|
return $this->response->setJSON([
|
|
'ok' => true,
|
|
'schoolYears' => $schoolYears,
|
|
'selectedYear' => $year,
|
|
'semesters' => $semesters,
|
|
'selectedSemester' => $sem,
|
|
'students' => $students,
|
|
'classSections' => array_map(static fn($r) => [
|
|
'id' => (int)($r['id'] ?? 0),
|
|
'class_section_id' => (int)($r['class_section_id'] ?? ($r['id'] ?? 0)),
|
|
'class_section_name' => (string)($r['class_section_name'] ?? 'Section'),
|
|
'school_year' => (string)($r['school_year'] ?? ''),
|
|
], $classSections),
|
|
'csrf_token' => csrf_token(),
|
|
'csrf_hash' => csrf_hash(),
|
|
]);
|
|
}
|
|
|
|
/**
|
|
* API: report card completeness check for a class section.
|
|
* GET params: class_section_id, school_year, semester
|
|
* Returns: { ok, summary, students:[{id,name,missing[],warnings[],complete}] }
|
|
*/
|
|
public function reportCardCompleteness()
|
|
{
|
|
$year = trim((string)($this->request->getGet('school_year') ?? $this->schoolYear ?? ''));
|
|
$sem = trim((string)($this->request->getGet('semester') ?? $this->semester ?? ''));
|
|
$sectionInput = (int)($this->request->getGet('class_section_id') ?? 0);
|
|
|
|
if ($year === '' || $sectionInput <= 0) {
|
|
return $this->response->setJSON([
|
|
'ok' => false,
|
|
'message' => 'Missing school year or class section.',
|
|
]);
|
|
}
|
|
|
|
$sectionCode = $sectionInput;
|
|
$sectionId = null;
|
|
$sectionName = '';
|
|
try {
|
|
$row = $this->db->table('classSection')
|
|
->select('id, class_section_id, class_section_name')
|
|
->where('class_section_id', $sectionInput)
|
|
->limit(1)
|
|
->get()
|
|
->getRowArray();
|
|
if ($row) {
|
|
$sectionId = (int)$row['id'];
|
|
$sectionCode = (int)$row['class_section_id'];
|
|
$sectionName = (string)($row['class_section_name'] ?? '');
|
|
} else {
|
|
$row = $this->db->table('classSection')
|
|
->select('id, class_section_id, class_section_name')
|
|
->where('id', $sectionInput)
|
|
->limit(1)
|
|
->get()
|
|
->getRowArray();
|
|
if ($row) {
|
|
$sectionId = (int)$row['id'];
|
|
$sectionCode = (int)$row['class_section_id'];
|
|
$sectionName = (string)($row['class_section_name'] ?? '');
|
|
}
|
|
}
|
|
} catch (\Throwable $e) {
|
|
}
|
|
|
|
if ($sectionName === '') {
|
|
$sectionName = (string)($this->resolveClassName($this->db, $sectionCode) ?? '');
|
|
}
|
|
|
|
$students = $this->fetchStudentsByClass($sectionCode, $year);
|
|
if (empty($students) && $sectionId && $sectionId !== $sectionCode) {
|
|
$students = $this->fetchStudentsByClass($sectionId, $year);
|
|
}
|
|
$rosterSemester = $sem;
|
|
$usedFallback = false;
|
|
if (empty($students) && $sem !== '') {
|
|
$students = $this->fetchStudentsByClass($sectionCode, $year);
|
|
if (empty($students) && $sectionId && $sectionId !== $sectionCode) {
|
|
$students = $this->fetchStudentsByClass($sectionId, $year);
|
|
}
|
|
$rosterSemester = '';
|
|
$usedFallback = !empty($students);
|
|
}
|
|
|
|
if (empty($students)) {
|
|
return $this->response->setJSON([
|
|
'ok' => true,
|
|
'message' => 'No students found for the selected class and year.',
|
|
'summary' => [
|
|
'total' => 0,
|
|
'complete' => 0,
|
|
'incomplete' => 0,
|
|
'warnings' => 0,
|
|
],
|
|
'students' => [],
|
|
'roster_semester' => $rosterSemester,
|
|
'used_fallback' => $usedFallback,
|
|
]);
|
|
}
|
|
|
|
$studentIds = array_values(array_filter(array_map(
|
|
static fn($s) => (int)($s['id'] ?? 0),
|
|
$students
|
|
), static fn($v) => $v > 0));
|
|
|
|
if (empty($studentIds)) {
|
|
return $this->response->setJSON([
|
|
'ok' => false,
|
|
'message' => 'No valid students found.',
|
|
]);
|
|
}
|
|
|
|
$scoreRows = $this->semesterScoreModel
|
|
->whereIn('student_id', $studentIds)
|
|
->where('school_year', $year);
|
|
if ($sem !== '') {
|
|
$this->applySemesterFilter($scoreRows, $sem, 'semester');
|
|
}
|
|
$scoreRows = $scoreRows
|
|
->orderBy('updated_at', 'DESC')
|
|
->orderBy('id', 'DESC')
|
|
->findAll();
|
|
|
|
$scoreByStudent = [];
|
|
foreach ($scoreRows as $row) {
|
|
$sid = (int)($row['student_id'] ?? 0);
|
|
if ($sid > 0 && !isset($scoreByStudent[$sid])) {
|
|
$scoreByStudent[$sid] = $row;
|
|
}
|
|
}
|
|
|
|
$semLower = strtolower(trim($sem));
|
|
$isFirst = in_array($semLower, ['fall', 'first', 'semester 1', '1'], true);
|
|
$isSecond = in_array($semLower, ['spring', 'second', 'semester 2', '2'], true);
|
|
|
|
$firstScoreByStudent = [];
|
|
if ($isSecond) {
|
|
$otherRows = $this->semesterScoreModel
|
|
->whereIn('student_id', $studentIds)
|
|
->where('school_year', $year);
|
|
if ($sem !== '') {
|
|
$this->applySemesterExclusion($otherRows, $sem, 'semester');
|
|
}
|
|
$otherRows = $otherRows
|
|
->orderBy('updated_at', 'DESC')
|
|
->orderBy('id', 'DESC')
|
|
->findAll();
|
|
foreach ($otherRows as $row) {
|
|
$sid = (int)($row['student_id'] ?? 0);
|
|
if ($sid > 0 && !isset($firstScoreByStudent[$sid])) {
|
|
$firstScoreByStudent[$sid] = $row;
|
|
}
|
|
}
|
|
}
|
|
|
|
$examCommentTypes = $isSecond
|
|
? ['final']
|
|
: ($isFirst ? ['midterm'] : ['midterm', 'final']);
|
|
$wantedCommentTypes = array_merge($examCommentTypes, ['ptap', 'attendance']);
|
|
$hasCommentReview = $this->db->fieldExists('comment_review', 'score_comments');
|
|
$hasCommentSemester = $this->db->fieldExists('semester', 'score_comments');
|
|
$hasCommentUpdatedAt = $this->db->fieldExists('updated_at', 'score_comments');
|
|
$commentSelectParts = ['student_id', 'score_type', 'comment'];
|
|
if ($hasCommentReview) {
|
|
$commentSelectParts[] = 'comment_review';
|
|
}
|
|
if ($hasCommentSemester) {
|
|
$commentSelectParts[] = 'semester';
|
|
}
|
|
|
|
// Do NOT filter comments by score_type or semester here.
|
|
// A single bad row like "Attendance Comment", "attendance-comment", or a blank semester
|
|
// should not make one student look incomplete while the PDF can still print the comment.
|
|
$commentBuilder = $this->scoreCommentModel
|
|
->select(implode(', ', $commentSelectParts))
|
|
->whereIn('student_id', $studentIds)
|
|
->where('school_year', $year);
|
|
if ($hasCommentUpdatedAt) {
|
|
$commentBuilder->orderBy('updated_at', 'DESC');
|
|
}
|
|
$commentRows = [];
|
|
try {
|
|
$commentRows = $commentBuilder->findAll();
|
|
} catch (\Throwable $e) {
|
|
}
|
|
|
|
$cleanComment = static function ($value): string {
|
|
$text = (string)($value ?? '');
|
|
$text = str_replace("\xc2\xa0", ' ', $text); // normalize non-breaking spaces
|
|
return trim($text);
|
|
};
|
|
|
|
$normalizeCommentType = static function ($value) use ($cleanComment): string {
|
|
$type = strtolower($cleanComment($value));
|
|
$type = preg_replace('/[^a-z0-9]+/', '_', $type);
|
|
$type = trim((string)$type, '_');
|
|
|
|
$map = [
|
|
'attendance_comment' => 'attendance',
|
|
'attendance_comments' => 'attendance',
|
|
'attendance' => 'attendance',
|
|
'attendence' => 'attendance',
|
|
'attendence_comment' => 'attendance',
|
|
'attendence_comments' => 'attendance',
|
|
'ptap_comment' => 'ptap',
|
|
'ptap_comments' => 'ptap',
|
|
'ptap' => 'ptap',
|
|
'midterm_comment' => 'midterm',
|
|
'midterm_comments' => 'midterm',
|
|
'midterm' => 'midterm',
|
|
'final_comment' => 'final',
|
|
'final_comments' => 'final',
|
|
'final' => 'final',
|
|
];
|
|
|
|
return $map[$type] ?? $type;
|
|
};
|
|
|
|
$normalizeSemester = static function ($value) use ($cleanComment): string {
|
|
$v = strtolower($cleanComment($value));
|
|
$v = preg_replace('/[^a-z0-9]+/', ' ', $v);
|
|
$v = trim((string)$v);
|
|
if (in_array($v, ['fall', 'first', 'first semester', 'semester 1', '1'], true)) {
|
|
return 'fall';
|
|
}
|
|
if (in_array($v, ['spring', 'second', 'second semester', 'semester 2', '2'], true)) {
|
|
return 'spring';
|
|
}
|
|
return $v;
|
|
};
|
|
$wantedSemester = $normalizeSemester($sem);
|
|
|
|
$commentsByStudent = [];
|
|
$commentPriorityByStudent = [];
|
|
foreach ($commentRows as $row) {
|
|
$sid = (int)($row['student_id'] ?? 0);
|
|
if ($sid <= 0) {
|
|
continue;
|
|
}
|
|
|
|
$typeRaw = $normalizeCommentType($row['score_type'] ?? '');
|
|
if ($typeRaw === '' || !in_array($typeRaw, $wantedCommentTypes, true)) {
|
|
continue;
|
|
}
|
|
|
|
$rawVal = $cleanComment($row['comment'] ?? '');
|
|
if ($typeRaw === 'attendance') {
|
|
// Attendance comments are stored in score_comments.comment.
|
|
// Do not use comment_review here, because it can be blank while the PDF comment exists.
|
|
$commentVal = $rawVal;
|
|
} else {
|
|
// For non-attendance comments, prefer reviewed text but fall back to the saved comment.
|
|
$reviewVal = $hasCommentReview ? $cleanComment($row['comment_review'] ?? '') : '';
|
|
$commentVal = $reviewVal !== '' ? $reviewVal : $rawVal;
|
|
}
|
|
if ($commentVal === '') {
|
|
continue;
|
|
}
|
|
|
|
$rowSemester = $hasCommentSemester ? $normalizeSemester($row['semester'] ?? '') : '';
|
|
$priority = 1;
|
|
if ($wantedSemester !== '' && $rowSemester === $wantedSemester) {
|
|
$priority = 3;
|
|
} elseif ($rowSemester === '') {
|
|
$priority = 2;
|
|
}
|
|
|
|
$existingPriority = $commentPriorityByStudent[$sid][$typeRaw] ?? 0;
|
|
if (!isset($commentsByStudent[$sid][$typeRaw]) || $priority >= $existingPriority) {
|
|
$commentsByStudent[$sid][$typeRaw] = $commentVal;
|
|
$commentPriorityByStudent[$sid][$typeRaw] = $priority;
|
|
}
|
|
}
|
|
|
|
$isNumeric = static fn($v) => $v !== null && $v !== '' && is_numeric($v);
|
|
|
|
$ackMap = [];
|
|
try {
|
|
$ackRows = $this->ackModel
|
|
->whereIn('student_id', $studentIds)
|
|
->where('school_year', $year)
|
|
->where('semester', $sem)
|
|
->orderBy('updated_at', 'DESC')
|
|
->findAll();
|
|
foreach ($ackRows as $row) {
|
|
$sid = (int) ($row['student_id'] ?? 0);
|
|
if ($sid > 0 && ! isset($ackMap[$sid])) {
|
|
$ackMap[$sid] = $row;
|
|
}
|
|
}
|
|
} catch (\Throwable $e) {
|
|
$ackMap = [];
|
|
}
|
|
|
|
$results = [];
|
|
$completeCount = 0;
|
|
$warningCount = 0;
|
|
foreach ($students as $student) {
|
|
$sid = (int)($student['id'] ?? 0);
|
|
$name = trim(((string)($student['firstname'] ?? '')) . ' ' . ((string)($student['lastname'] ?? '')));
|
|
$missing = [];
|
|
$warnings = [];
|
|
|
|
if ($sectionName === '') {
|
|
$missing[] = 'Grade/Section';
|
|
}
|
|
|
|
$score = $sid > 0 ? ($scoreByStudent[$sid] ?? null) : null;
|
|
$examScore = null;
|
|
$attendanceScore = null;
|
|
$ptapScore = null;
|
|
$semesterScore = null;
|
|
$hasAllComponents = false;
|
|
|
|
if (!$score) {
|
|
$missing[] = 'Score record';
|
|
} else {
|
|
$attendanceScore = $score['attendance_score'] ?? null;
|
|
$ptapScore = $score['ptap_score'] ?? null;
|
|
$semesterScore = $score['semester_score'] ?? null;
|
|
|
|
$components = [
|
|
$score['homework_avg'] ?? null,
|
|
$score['quiz_avg'] ?? null,
|
|
$score['project_avg'] ?? null,
|
|
$score['test_avg'] ?? null,
|
|
];
|
|
$hasAllComponents = !in_array(false, array_map($isNumeric, $components), true);
|
|
|
|
if ($isSecond) {
|
|
$examScore = $score['final_exam_score'] ?? null;
|
|
if (!$isNumeric($examScore) && $isNumeric($score['midterm_exam_score'] ?? null)) {
|
|
$examScore = $score['midterm_exam_score'];
|
|
$warnings[] = 'Final exam stored as midterm';
|
|
}
|
|
} elseif ($isFirst) {
|
|
$examScore = $score['midterm_exam_score'] ?? null;
|
|
} else {
|
|
$examScore = $score['final_exam_score'] ?? ($score['midterm_exam_score'] ?? null);
|
|
}
|
|
|
|
if (!$isNumeric($examScore)) {
|
|
$missing[] = $isSecond ? 'Final exam score' : ($isFirst ? 'Midterm exam score' : 'Exam score');
|
|
}
|
|
if (!$isNumeric($attendanceScore)) {
|
|
$missing[] = 'Attendance score';
|
|
}
|
|
if (!$isNumeric($ptapScore)) {
|
|
if ($hasAllComponents) {
|
|
$warnings[] = 'PTAP computed from components';
|
|
} else {
|
|
$missing[] = 'PTAP score';
|
|
}
|
|
}
|
|
|
|
$canComputeSemester = $isNumeric($examScore) && $isNumeric($attendanceScore) && ($isNumeric($ptapScore) || $hasAllComponents);
|
|
if (!$isNumeric($semesterScore)) {
|
|
if ($canComputeSemester) {
|
|
$warnings[] = 'Semester score computed';
|
|
} else {
|
|
$missing[] = 'Semester score';
|
|
}
|
|
}
|
|
|
|
if ($isSecond) {
|
|
$firstScore = $firstScoreByStudent[$sid]['semester_score'] ?? null;
|
|
if (!$isNumeric($firstScore)) {
|
|
$missing[] = 'First semester score';
|
|
}
|
|
}
|
|
}
|
|
|
|
$commentKey = $isSecond ? 'final' : ($isFirst ? 'midterm' : '');
|
|
$commentLabel = $isSecond ? 'Final' : ($isFirst ? 'Midterm' : 'Exam');
|
|
$commentSet = $commentsByStudent[$sid] ?? [];
|
|
$examComment = $commentKey !== ''
|
|
? ($commentSet[$commentKey] ?? '')
|
|
: ($commentSet['final'] ?? ($commentSet['midterm'] ?? ''));
|
|
if (trim((string)$examComment) === '') {
|
|
$missing[] = $commentLabel . ' comment';
|
|
}
|
|
if (trim((string)($commentSet['ptap'] ?? '')) === '') {
|
|
$missing[] = 'PTAP comment';
|
|
}
|
|
|
|
// Keep completeness consistent with the PDF report generation:
|
|
// the report can auto-generate an attendance comment from attendance_score.
|
|
if (trim((string)($commentSet['attendance'] ?? '')) === '' && $isNumeric($attendanceScore)) {
|
|
$autoAttendance = attendance_comment_from_score(
|
|
(float)$attendanceScore,
|
|
trim((string)($student['firstname'] ?? ''))
|
|
);
|
|
if ($autoAttendance !== null && trim((string)$autoAttendance) !== '') {
|
|
$commentSet['attendance'] = $autoAttendance;
|
|
$warnings[] = 'Attendance comment computed from attendance score';
|
|
}
|
|
}
|
|
|
|
if (trim((string)($commentSet['attendance'] ?? '')) === '') {
|
|
$missing[] = 'Attendance comment';
|
|
}
|
|
|
|
$isComplete = empty($missing);
|
|
if ($isComplete) {
|
|
$completeCount++;
|
|
}
|
|
if (!empty($warnings)) {
|
|
$warningCount++;
|
|
}
|
|
|
|
$ack = $sid > 0 ? ($ackMap[$sid] ?? null) : null;
|
|
$results[] = [
|
|
'id' => $sid,
|
|
'name' => $name !== '' ? $name : 'N/A',
|
|
'missing' => $missing,
|
|
'warnings' => $warnings,
|
|
'complete' => $isComplete,
|
|
'viewed_at' => $ack['viewed_at'] ?? null,
|
|
'signed_at' => $ack['signed_at'] ?? null,
|
|
'signed_name' => $ack['signed_name'] ?? null,
|
|
];
|
|
}
|
|
|
|
$total = count($results);
|
|
$incomplete = $total - $completeCount;
|
|
|
|
return $this->response->setJSON([
|
|
'ok' => true,
|
|
'summary' => [
|
|
'total' => $total,
|
|
'complete' => $completeCount,
|
|
'incomplete' => $incomplete,
|
|
'warnings' => $warningCount,
|
|
],
|
|
'students' => $results,
|
|
'class_section_name' => $sectionName,
|
|
'class_section_id' => $sectionCode,
|
|
'school_year' => $year,
|
|
'semester' => $sem,
|
|
'roster_semester' => $rosterSemester,
|
|
'used_fallback' => $usedFallback,
|
|
]);
|
|
}
|
|
|
|
/**
|
|
* API: report card parent acknowledgement status
|
|
* GET params: student_id, school_year, semester
|
|
*/
|
|
public function reportCardAcknowledgement()
|
|
{
|
|
$studentId = (int) ($this->request->getGet('student_id') ?? 0);
|
|
if ($studentId <= 0) {
|
|
return $this->response->setJSON(['ok' => false, 'error' => 'Missing student_id'])->setStatusCode(400);
|
|
}
|
|
$schoolYear = trim((string) ($this->request->getGet('school_year') ?? $this->schoolYear ?? ''));
|
|
$semester = trim((string) ($this->request->getGet('semester') ?? $this->semester ?? ''));
|
|
|
|
$row = $this->ackModel
|
|
->where('student_id', $studentId)
|
|
->where('school_year', $schoolYear)
|
|
->where('semester', $semester)
|
|
->orderBy('updated_at', 'DESC')
|
|
->first();
|
|
|
|
return $this->response->setJSON([
|
|
'ok' => true,
|
|
'student_id' => $studentId,
|
|
'school_year' => $schoolYear,
|
|
'semester' => $semester,
|
|
'viewed_at' => $row['viewed_at'] ?? null,
|
|
'signed_at' => $row['signed_at'] ?? null,
|
|
'signed_name' => $row['signed_name'] ?? null,
|
|
]);
|
|
}
|
|
|
|
protected function fetchStudentsByClass(int $sectionId, ?string $schoolYear)
|
|
{
|
|
$b = $this->studentClassModel
|
|
->select('s.id, s.firstname, s.lastname, s.registration_grade, s.gender')
|
|
->join('students s', 's.id = student_class.student_id', 'inner')
|
|
->where('student_class.class_section_id', $sectionId)
|
|
->where('s.is_active', 1)
|
|
->orderBy('s.firstname', 'ASC')
|
|
->orderBy('s.lastname', 'ASC');
|
|
|
|
if (!empty($schoolYear)) {
|
|
$b->where('student_class.school_year', $schoolYear);
|
|
}
|
|
|
|
return $b->findAll();
|
|
}
|
|
|
|
protected function formatReportPDF($pdf, $data)
|
|
{
|
|
$w = $pdf->GetPageWidth();
|
|
// Layout helpers (mirroring the shared StudentReportPDF style but using DB data)
|
|
$pdf->SetMargins(10, 10, 10);
|
|
$bottomMarginVal = 3;
|
|
$pdf->SetAutoPageBreak(true, $bottomMarginVal);
|
|
$pdf->AddPage('P', 'Letter');
|
|
|
|
$leftMargin = 10;
|
|
|
|
$leftLogo = $this->firstExisting([
|
|
FCPATH . 'images/Isgl_logo.png',
|
|
FCPATH . 'images/isgl_logo.png',
|
|
FCPATH . 'assets/images/isgl_logo.png',
|
|
FCPATH . 'assets/images/isgl.png',
|
|
]);
|
|
$rightLogo = $this->firstExisting([
|
|
FCPATH . 'images/logo.png',
|
|
FCPATH . 'assets/images/logo.png',
|
|
FCPATH . 'assets/images/school_logo.png',
|
|
]);
|
|
|
|
if ($leftLogo) { $pdf->Image($leftLogo, 5, 5, 32); }
|
|
if ($rightLogo) { $pdf->Image($rightLogo, $w - 33, 5, 32); }
|
|
|
|
$pdf->SetFont('Times', 'B', 24);
|
|
$pdf->Cell(0, 12, 'Al Rahma Sunday School', 0, 1, 'C');
|
|
$pdf->Cell(0, 12, 'Student Report Card', 0, 1, 'C');
|
|
$pdf->Ln(4);
|
|
|
|
$numFmt = static function ($val) {
|
|
if ($val === null || $val === '') return 'N/A';
|
|
if (is_numeric($val)) return number_format((float)$val, 1);
|
|
return (string)$val;
|
|
};
|
|
$splitText = static function ($text, int $limit = 55): array {
|
|
$text = trim((string)$text);
|
|
if ($text === '') {
|
|
return [' ', '']; // keep an extra blank line for spacing
|
|
}
|
|
$lines = explode("\n", wordwrap($text, $limit));
|
|
$lines[] = ''; // extra empty line after the comment
|
|
return $lines;
|
|
};
|
|
$drawLabeledCell = static function (\FPDF $pdf, string $label, string $value, float $width = 95) {
|
|
$pdf->SetFont('Helvetica', 'B', 11);
|
|
$pdf->Cell($width, 10, $label, 1, 0);
|
|
$pdf->SetFont('Helvetica', '', 12);
|
|
$pdf->Cell($width, 10, $value, 1, 1);
|
|
};
|
|
$firstNonNull = static function (...$items) {
|
|
foreach ($items as $item) {
|
|
if ($item !== null) {
|
|
return $item;
|
|
}
|
|
}
|
|
return null;
|
|
};
|
|
$wrapLinesToWidth = static function (\FPDF $pdf, string $text, float $width) {
|
|
$text = trim((string)$text);
|
|
if ($text === '') {
|
|
return [' '];
|
|
}
|
|
if ($width <= 0) {
|
|
return [$text];
|
|
}
|
|
$segments = preg_split("/\\r?\\n+/", $text);
|
|
$lines = [];
|
|
foreach ($segments as $seg) {
|
|
$seg = trim($seg);
|
|
if ($seg === '') {
|
|
$lines[] = ' ';
|
|
continue;
|
|
}
|
|
$words = preg_split('/\\s+/u', $seg);
|
|
$current = '';
|
|
foreach ($words as $word) {
|
|
$trial = trim($current === '' ? $word : ($current . ' ' . $word));
|
|
if ($pdf->GetStringWidth($trial) <= $width) {
|
|
$current = $trial;
|
|
} else {
|
|
if ($current !== '') $lines[] = $current;
|
|
$current = $word;
|
|
}
|
|
}
|
|
if ($current !== '') $lines[] = $current;
|
|
}
|
|
return $lines;
|
|
};
|
|
$draw3ColumnRow = static function (\FPDF $pdf, string $label, $score, $feedback) use ($wrapLinesToWidth) {
|
|
$w1 = 55;
|
|
$w2 = 30;
|
|
$w3 = 110; // comments column width
|
|
$commentWrapW = $w3 - 4; // allow more horizontal space for comments
|
|
$lineHeight = 6;
|
|
|
|
// Wrap using the same font settings as the comment cell to avoid odd line breaks
|
|
$pdf->SetFont('Helvetica', '', 11);
|
|
$cellMargin = 1.0; // FPDF default internal cell margin (protected property)
|
|
$wrapWidth = max(1, $commentWrapW - (2 * $cellMargin));
|
|
$lines = $wrapLinesToWidth($pdf, (string)$feedback, $wrapWidth);
|
|
if (trim((string)$feedback) !== '') {
|
|
$lines[] = ' ';
|
|
}
|
|
$linesCount = max(1, count($lines));
|
|
$rowHeight = max($lineHeight, $linesCount * $lineHeight);
|
|
$textOffsetY = ($rowHeight - ($linesCount * $lineHeight)) / 2;
|
|
|
|
$startX = $pdf->GetX();
|
|
$startY = $pdf->GetY();
|
|
$labelPad = 2;
|
|
|
|
// Label cell
|
|
$pdf->SetFont('Helvetica', 'B', 11);
|
|
$pdf->SetXY($startX + $labelPad, $startY + $textOffsetY);
|
|
$pdf->Cell($w1 - $labelPad, $lineHeight, $label, 0, 0);
|
|
|
|
// Score cell
|
|
$pdf->SetFont('Helvetica', '', 12);
|
|
$scoreStr = is_numeric($score) ? number_format((float)$score, 1) : ((string)$score !== '' ? (string)$score : 'N/A');
|
|
$pdf->SetXY($startX + $w1, $startY + $textOffsetY);
|
|
$pdf->Cell($w2, $lineHeight, $scoreStr, 0, 0, 'C');
|
|
|
|
// Comments cell
|
|
$commentX = $pdf->GetX();
|
|
$pdf->SetFont('Helvetica', '', 11);
|
|
$pdf->SetXY($commentX + 2, $startY + $textOffsetY);
|
|
$pdf->MultiCell($commentWrapW, $lineHeight, implode("\n", $lines), 0, 'L');
|
|
|
|
// Advance to next row
|
|
$pdf->SetXY($startX, $startY + $rowHeight);
|
|
};
|
|
$drawInfoRows = static function (\FPDF $pdf, array $rows, float $height = 12, ?float $startY = null) use ($leftMargin) {
|
|
$leftW = 130; // Teacher/Name column widened by 10
|
|
$rightW = 65; // School Year column reduced by 10
|
|
$startX = $leftMargin; // ensure all rows start at left margin
|
|
$currentY = $startY !== null ? $startY : $pdf->GetY();
|
|
$lineH = 6;
|
|
$wrapLines = static function (string $text, float $width) use ($pdf) {
|
|
$text = trim($text);
|
|
if ($text === '') {
|
|
return [' '];
|
|
}
|
|
$segments = preg_split('/\\r?\\n+/', $text);
|
|
$out = [];
|
|
foreach ($segments as $seg) {
|
|
$seg = trim($seg);
|
|
if ($seg === '') {
|
|
// Preserve intentional blank line
|
|
$out[] = ' ';
|
|
continue;
|
|
}
|
|
$words = preg_split('/\\s+/u', $seg);
|
|
$current = '';
|
|
foreach ($words as $word) {
|
|
$trial = $current === '' ? $word : ($current . ' ' . $word);
|
|
if ($pdf->GetStringWidth($trial) <= ($width - 2)) {
|
|
$current = $trial;
|
|
} else {
|
|
if ($current !== '') {
|
|
$out[] = $current;
|
|
}
|
|
$current = $word;
|
|
}
|
|
}
|
|
if ($current !== '') {
|
|
$out[] = $current;
|
|
}
|
|
}
|
|
return !empty($out) ? $out : [' '];
|
|
};
|
|
|
|
if ($startY !== null) {
|
|
$pdf->SetY($startY);
|
|
}
|
|
foreach ($rows as $row) {
|
|
if (!is_array($row) || count($row) < 4) {
|
|
continue;
|
|
}
|
|
[$labelLeft, $valueLeft, $labelRight, $valueRight] = $row;
|
|
$y = $currentY;
|
|
$labelY = $y+2; // align titles and contents vertically
|
|
$isTeacherRow = stripos($labelLeft, 'teacher') !== false;
|
|
if ($isTeacherRow) {
|
|
$labelY += 1;
|
|
}
|
|
// For teacher rows, respect the explicit line breaks (two names per line) without extra wrapping
|
|
$leftLines = $isTeacherRow
|
|
? array_values(array_filter(array_map('rtrim', explode("\n", (string)$valueLeft)), static fn($v) => $v !== ''))
|
|
: $wrapLines((string)$valueLeft, $leftW - 6);
|
|
$rightLines = $wrapLines((string)$valueRight, $rightW - 6);
|
|
$rowHeight = max($height, max(count($leftLines), count($rightLines)) * $lineH + 4);
|
|
// If teacher names span multiple lines, add a small breathing room (half line) after the second line
|
|
if (stripos($labelLeft, 'teacher') !== false && count($leftLines) > 1) {
|
|
$rowHeight += ($lineH / 4);
|
|
}
|
|
$pdf->SetXY($startX, $y);
|
|
$pdf->Rect($startX, $y, $leftW, $rowHeight);
|
|
$pdf->Rect($startX + $leftW, $y, $rightW, $rowHeight);
|
|
$pdf->SetFont('Helvetica', 'B', 11);
|
|
$pdf->SetXY($startX + 3, $labelY);
|
|
$pdf->Write($lineH - 1, $labelLeft);
|
|
$pdf->SetFont('Arial', '', 11);
|
|
$labelLeftWidth = $pdf->GetStringWidth($labelLeft);
|
|
$pad = 2; // small gap after the label
|
|
$valLeftWidth = $leftW - 6 - $labelLeftWidth - $pad;
|
|
if ($valLeftWidth < 10) {
|
|
$valLeftWidth = $leftW - 6 - $pad;
|
|
}
|
|
$pdf->SetXY($startX + 3 + $labelLeftWidth + $pad, $labelY);
|
|
if (count($leftLines) === 1) {
|
|
$pdf->Write($lineH - 1, $leftLines[0]);
|
|
} else {
|
|
$pdf->MultiCell($valLeftWidth, $lineH, implode("\n", $leftLines), 0, 'L');
|
|
}
|
|
$pdf->SetFont('Helvetica', 'B', 11);
|
|
$pdf->SetXY($startX + $leftW + 3, $labelY);
|
|
$pdf->Write($lineH - 1, $labelRight);
|
|
$pdf->SetFont('Arial', '', 11);
|
|
$labelRightWidth = $pdf->GetStringWidth($labelRight);
|
|
$valRightWidth = $rightW - 6 - $labelRightWidth - $pad;
|
|
if ($valRightWidth < 10) {
|
|
$valRightWidth = $rightW - 6 - $pad;
|
|
}
|
|
$pdf->SetXY($startX + $leftW + 3 + $labelRightWidth + $pad, $labelY);
|
|
if (count($rightLines) === 1) {
|
|
$pdf->Write($lineH - 1, $rightLines[0]);
|
|
} else {
|
|
$pdf->MultiCell($valRightWidth, $lineH, implode("\n", $rightLines), 0, 'L');
|
|
}
|
|
$currentY += $rowHeight;
|
|
$pdf->SetY($currentY);
|
|
}
|
|
return $currentY;
|
|
};
|
|
|
|
$semRaw = (string)($data['score']['semester'] ?? ($data['selected_semester'] ?? ''));
|
|
$semNum = null;
|
|
$semFriendly = 'Second Semester';
|
|
$s = strtolower(trim($semRaw));
|
|
if ($s === 'fall' || $s === 'first' || $s === 'semester 1' || $s === '1') { $semNum = 1; $semFriendly = 'First Semester'; }
|
|
elseif ($s === 'spring' || $s === 'second' || $s === 'semester 2' || $s === '2') { $semNum = 2; $semFriendly = 'Second Semester'; }
|
|
|
|
// Teacher names (support multiple main teachers + wrap to a second line if needed)
|
|
$teacherNamesList = $data['teacher_names'] ?? [];
|
|
if (empty($teacherNamesList) && !empty($data['teacher_name'])) {
|
|
$teacherNamesList = [trim((string)$data['teacher_name'])];
|
|
}
|
|
$teacherNamesList = array_values(array_filter(
|
|
array_map('trim', $teacherNamesList),
|
|
static fn($v) => $v !== '' && strcasecmp($v, 'N/A') !== 0
|
|
));
|
|
$tas = array_values(array_filter(
|
|
array_map(static fn($v) => trim((string)$v), $data['ta_names'] ?? []),
|
|
static fn($v) => $v !== '' && strcasecmp($v, 'N/A') !== 0
|
|
));
|
|
$allTeacherNames = array_values(array_unique(array_filter(
|
|
array_merge($teacherNamesList, $tas),
|
|
static fn($v) => $v !== ''
|
|
)));
|
|
$teacherNameLines = [];
|
|
$teacherCount = count($allTeacherNames);
|
|
for ($i = 0; $i < $teacherCount; $i += 2) {
|
|
$pair = array_slice($allTeacherNames, $i, 2);
|
|
$isLastPair = ($i + 2) >= $teacherCount;
|
|
if (count($pair) === 2) {
|
|
$teacherNameLines[] = implode($isLastPair ? ' & ' : ', ', $pair);
|
|
} else {
|
|
$teacherNameLines[] = $pair[0];
|
|
}
|
|
}
|
|
if (empty($teacherNameLines)) {
|
|
$teacherNameLines[] = 'N/A';
|
|
}
|
|
$teacherNames = implode("\n", $teacherNameLines);
|
|
|
|
$schoolYear = (string)($data['score']['school_year'] ?? ($data['score']['year'] ?? 'N/A'));
|
|
if (preg_match('/^(\\d{4})-(\\d{4})$/', $schoolYear, $m)) {
|
|
$schoolYear = $m[1] . '/' . $m[2];
|
|
}
|
|
$studentName = trim(($data['student']['firstname'] ?? '') . ' ' . ($data['student']['lastname'] ?? ''));
|
|
$gradeLabel = (string)($data['grade'] ?? ($data['class_section_name'] ?? 'N/A'));
|
|
$today = (string)($data['report_date_display'] ?? date('m-d-Y'));
|
|
$termRank = trim((string)($data['term_rank_display'] ?? ''));
|
|
$firstSemScore = $data['first_semester_score'] ?? null;
|
|
$secondSemScore = $data['second_semester_score'] ?? ($data['total_score'] ?? null);
|
|
$finalAverage = $data['final_average'] ?? null;
|
|
$comments = $data['comments'] ?? [];
|
|
|
|
$infoRow = function ($labelLeft, $valueLeft, $labelRight, $valueRight, $height = 12) use ($pdf) {
|
|
$x = $pdf->GetX();
|
|
$y = $pdf->GetY();
|
|
$leftW = 115;
|
|
$rightW = 68;
|
|
$pdf->Rect($x, $y, $leftW, $height);
|
|
$pdf->Rect($x + $leftW, $y, $rightW, $height);
|
|
$pdf->SetFont('Helvetica', 'B', 11);
|
|
$pdf->SetXY($x + 3, $y + 3);
|
|
$pdf->Write(5, $labelLeft);
|
|
$pdf->SetFont('Arial', '', 11);
|
|
$pdf->Write(5, $valueLeft);
|
|
$pdf->SetFont('Helvetica', 'B', 11);
|
|
$pdf->SetXY($x + $leftW + 3, $y + 3);
|
|
$pdf->Write(5, $labelRight);
|
|
$pdf->SetFont('Arial', '', 11);
|
|
$pdf->Write(5, $valueRight);
|
|
$pdf->Ln($height);
|
|
};
|
|
|
|
// Student + teacher + grade (three stacked rows, edges touching)
|
|
$afterInfoY = $drawInfoRows($pdf, [
|
|
["Teachers' Names: ", ' ' . ($teacherNames !== '' ? $teacherNames : 'N/A'), 'School Year: ', ' ' . $schoolYear],
|
|
['Student Name: ', ' ' . ($studentName !== '' ? $studentName : 'N/A'), 'Grade: ', ' ' . ($gradeLabel !== '' ? $gradeLabel : 'N/A')],
|
|
['Term: ', ' ' . $semFriendly, 'Date: ', ' ' . $today],
|
|
]);
|
|
$pdf->SetY($afterInfoY);
|
|
// No extra spacing to keep rectangles touching the next block
|
|
|
|
$pdf->Ln(2);
|
|
// Scores and comments rows
|
|
$finalExamScore = $data['final_exam_score'] ?? null;
|
|
if ($finalExamScore === null) {
|
|
if ($semNum === 1) {
|
|
$finalExamScore = $data['score']['midterm_exam_score'] ?? null;
|
|
} elseif ($semNum === 2) {
|
|
$finalExamScore = $data['score']['final_exam_score'] ?? ($data['score']['midterm_exam_score'] ?? null);
|
|
} else {
|
|
$finalExamScore = $data['score']['final_exam_score'] ?? ($data['score']['midterm_exam_score'] ?? null);
|
|
}
|
|
}
|
|
$ptapScore = $data['ptap'] ?? $data['score']['ptap_score'] ?? null;
|
|
$attendance = $data['score']['attendance_score'] ?? null;
|
|
|
|
$examLabel = ($semNum === 1) ? 'Midterm Exam*' : 'Final Exam*';
|
|
// Header for subject table
|
|
$pdf->SetFont('Helvetica', 'B', 11);
|
|
$headerX = $pdf->GetX();
|
|
$headerY = $pdf->GetY();
|
|
$pdf->Cell(55, 15, 'Subject', 1, 0, 'C');
|
|
$scoreHeader = $semNum === 1 ? "1st Semester\nScore (/100)" : "2nd Semester\nScore (/100)";
|
|
$pdf->SetXY($headerX + 55, $headerY);
|
|
$pdf->MultiCell(30, 7.5, $scoreHeader, 1, 'C');
|
|
$pdf->SetXY($headerX + 85, $headerY);
|
|
$pdf->Cell(110, 15, "Teachers' Comments", 1, 1, 'C');
|
|
|
|
// Match comment to the correct row
|
|
$examComment = $semNum === 1
|
|
? $firstNonNull($comments['midterm'] ?? null, $data['score']['midterm_comment'] ?? null, $data['score']['comment'] ?? null)
|
|
: $firstNonNull($comments['final'] ?? null, $data['score']['final_comment'] ?? null, $data['score']['comment'] ?? null);
|
|
$ptapComment = $firstNonNull($comments['ptap'] ?? null, $data['score']['ptap_comment'] ?? null);
|
|
$attendanceComment = $firstNonNull(
|
|
$comments['attendance'] ?? null,
|
|
$comments['attendance_comment'] ?? null,
|
|
$data['score']['attendance_comment'] ?? null
|
|
);
|
|
|
|
// Add a blank line before the exam row to separate header from data
|
|
$pdf->Ln(2);
|
|
$draw3ColumnRow($pdf, $examLabel, $numFmt($finalExamScore), $examComment ?? '');
|
|
$draw3ColumnRow($pdf, 'PTAP**', $numFmt($ptapScore), $ptapComment ?? '');
|
|
$attendanceComment = $firstNonNull(
|
|
$comments['attendance'] ?? null,
|
|
$comments['attendance_comment'] ?? null,
|
|
$data['score']['attendance_comment'] ?? null
|
|
);
|
|
$draw3ColumnRow($pdf, 'Attendance***', $numFmt($attendance), $attendanceComment ?? '');
|
|
|
|
// Draw outer box and vertical dividers around the score/comment block (no horizontal separators)
|
|
$rowsStartX = $headerX;
|
|
$rowsStartY = $headerY + 15; // header height
|
|
$rowsEndY = $pdf->GetY();
|
|
$blockHeight = $rowsEndY - $rowsStartY;
|
|
$blockWidth = 195; // 55 + 30 + 110
|
|
$pdf->Rect($rowsStartX, $rowsStartY, $blockWidth, $blockHeight);
|
|
$pdf->Line($rowsStartX + 55, $rowsStartY, $rowsStartX + 55, $rowsStartY + $blockHeight);
|
|
$pdf->Line($rowsStartX + 85, $rowsStartY, $rowsStartX + 85, $rowsStartY + $blockHeight);
|
|
|
|
// Attendance summary (absences / tardies)
|
|
$totalDays = $data['total_attendance_days'] ?? $data['score']['total_semester_days'] ?? $data['score']['total_days'] ?? '-';
|
|
$absences = $data['score']['total_absences'] ?? $data['score']['absences'] ?? '0';
|
|
$tardies = $data['score']['total_tardies'] ?? $data['score']['tardies'] ?? '0';
|
|
|
|
$pdf->Ln(2);
|
|
// Attendance summary on a single row (three columns)
|
|
$statW = 65;
|
|
$statH = 12;
|
|
$startX = $pdf->GetX();
|
|
$startY = $pdf->GetY();
|
|
$stats = [
|
|
['Total Semester Days: ', (string)$totalDays],
|
|
['Total Absences: ', (string)$absences],
|
|
['Tardies (Unjustified): ', (string)$tardies],
|
|
];
|
|
foreach ($stats as $i => [$lbl, $val]) {
|
|
$x = $startX + ($i * $statW);
|
|
$pdf->Rect($x, $startY, $statW, $statH);
|
|
$pdf->SetXY($x + 2, $startY + 3);
|
|
$pdf->SetFont('Helvetica', 'B', 11);
|
|
$pdf->Write(5, $lbl . ' ');
|
|
$pdf->SetFont('Helvetica', '', 11);
|
|
$pdf->Write(5, $val);
|
|
}
|
|
$pdf->SetY($startY + $statH);
|
|
|
|
// Semester scores + final
|
|
$effectiveSecond = $secondSemScore ?? $data['score']['semester_score'] ?? $data['total_score'] ?? null;
|
|
$finalScoreVal = $finalAverage;
|
|
if (!is_numeric($finalScoreVal)) {
|
|
$finalScoreVal = (is_numeric($firstSemScore) && is_numeric($effectiveSecond))
|
|
? number_format(((float)$firstSemScore + (float)$effectiveSecond) / 2, 1)
|
|
: $numFmt($effectiveSecond);
|
|
}
|
|
// Keep grade label and value within bordered cells
|
|
$gradeCellWidth = 65; // match Total Semester Days column width
|
|
$gradeCellHeight = 12;
|
|
$gradeX = $pdf->GetX();
|
|
$gradeY = $pdf->GetY();
|
|
$gradePad = max(0, 2 - (4 * 0.3528)); // reduce indentation by 4pt
|
|
|
|
$drawGradeCell = static function (
|
|
\FPDF $pdf,
|
|
float $x,
|
|
float $y,
|
|
float $w,
|
|
float $h,
|
|
string $label,
|
|
string $value,
|
|
float $pad
|
|
) {
|
|
$pdf->Rect($x, $y, $w, $h);
|
|
$pdf->SetXY($x + $pad, $y + 3);
|
|
$pdf->SetFont('Helvetica', 'B', 11);
|
|
$pdf->Write(5, $label . ' ');
|
|
$labelWidth = $pdf->GetStringWidth($label . ' ');
|
|
$pdf->SetFont('Helvetica', '', 12);
|
|
$pdf->SetXY($x + 2 + $labelWidth, $y + 3);
|
|
$pdf->Write(5, $value);
|
|
};
|
|
$drawRankCell = static function (
|
|
\FPDF $pdf,
|
|
float $x,
|
|
float $y,
|
|
float $w,
|
|
float $h,
|
|
string $rankValue,
|
|
float $pad
|
|
) {
|
|
$rankValue = trim($rankValue) !== '' ? trim($rankValue) : 'N/A';
|
|
|
|
$pdf->Rect($x, $y, $w, $h);
|
|
$pdf->SetXY($x + $pad, $y + 3);
|
|
$pdf->SetFont('Helvetica', 'B', 11);
|
|
$pdf->Write(5, ' Class Rank: ');
|
|
|
|
$labelWidth = $pdf->GetStringWidth(' Class Rank: ');
|
|
$pdf->SetFont('Helvetica', '', 12);
|
|
$pdf->SetXY($x + 2 + $labelWidth, $y + 3);
|
|
$pdf->Write(5, $rankValue);
|
|
};
|
|
if ($semNum === 2) {
|
|
$firstLabel = ' 1st Semester Grade:';
|
|
$firstValue = $numFmt($firstSemScore) . '/100';
|
|
|
|
$secondLabel = ' 2nd Semester Grade:';
|
|
$secondValue = $numFmt($effectiveSecond) . '/100';
|
|
|
|
$drawGradeCell($pdf, $gradeX, $gradeY, $gradeCellWidth, $gradeCellHeight, $firstLabel, $firstValue, $gradePad);
|
|
$drawGradeCell($pdf, $gradeX + $gradeCellWidth, $gradeY, $gradeCellWidth, $gradeCellHeight, $secondLabel, $secondValue, $gradePad);
|
|
} else {
|
|
$gradeLabel = ' 1st Semester Grade:';
|
|
$gradeValue = $numFmt($effectiveSecond) . '/100';
|
|
|
|
$drawGradeCell($pdf, $gradeX, $gradeY, $gradeCellWidth, $gradeCellHeight, $gradeLabel, $gradeValue, $gradePad);
|
|
|
|
// Fall: Ranking goes to the right of 1st Semester Grade.
|
|
$drawRankCell($pdf, $gradeX + $gradeCellWidth, $gradeY, $gradeCellWidth, $gradeCellHeight, $termRank, $gradePad);
|
|
}
|
|
|
|
$pdf->SetY($gradeY + $gradeCellHeight);
|
|
|
|
$finalScoreRowHeight = 12;
|
|
$finalScoreEndY = null;
|
|
|
|
// Show Final Score only for second semester / Spring.
|
|
if ($semNum === 2) {
|
|
$finalLabel = 'Final Score****:';
|
|
$finalValue = (is_string($finalScoreVal) ? $finalScoreVal : $numFmt($finalScoreVal)) . '/100';
|
|
|
|
$finalCellWidth = 65;
|
|
$finalCellHeight = $finalScoreRowHeight;
|
|
$finalX = $pdf->GetX();
|
|
$finalY = $pdf->GetY();
|
|
$finalPad = max(0, 2 - (4 * 0.3528));
|
|
|
|
$drawGradeCell($pdf, $finalX, $finalY, $finalCellWidth, $finalCellHeight, ' ' . $finalLabel, $finalValue, $finalPad);
|
|
|
|
// Spring: Ranking goes to the right of Final Score.
|
|
$drawRankCell($pdf, $finalX + $finalCellWidth, $finalY, $finalCellWidth, $finalCellHeight, $termRank, $finalPad);
|
|
|
|
$pdf->SetY($finalY + $finalCellHeight);
|
|
$finalScoreEndY = $finalY + $finalCellHeight;
|
|
}
|
|
|
|
$scoresEndY = $pdf->GetY();
|
|
|
|
// Legend anchored near bottom with a 3mm buffer (dynamic placement)
|
|
$examLegendLabel = ($semNum === 1) ? 'Midterm Exam' : 'Final Exam';
|
|
$legendLines = [
|
|
"(*) {$examLegendLabel} (Weight: 60% of semester score)",
|
|
"(**) PTAP - Participation, Tests, Assignments, Projects (Weight: 20%)",
|
|
"(***) Attendance = ({$totalDays} - Total Absences + One Sick Day) / {$totalDays} ) x 100 (Weight: 20%)",
|
|
];
|
|
if ($semNum === 2) {
|
|
$legendLines[] = "(****) Final Score = (1st Semester Score + 2nd Semester Score) / 2";
|
|
}
|
|
$legendLines[] = ['text' => "If you have any questions, please contact the school via email at alrahma.isgl@gmail.com", 'bold' => true];
|
|
$lineHeight = 4;
|
|
$legendHeight = count($legendLines) * $lineHeight;
|
|
$bottomPad = 0.1; // distance from bottom of the page
|
|
|
|
// Respect the configured bottom margin so legend stays on-page
|
|
$targetY = $pdf->GetPageHeight() - $bottomMarginVal - $bottomPad - $legendHeight;
|
|
$targetY = max($pdf->GetY(), $targetY); // avoid overlapping prior content
|
|
$legendY = $targetY;
|
|
|
|
// Two-column layout: legend on left, signature block on right
|
|
$marginL = 10;
|
|
$sigWidth = 40;
|
|
$sigHeight = $finalScoreRowHeight;
|
|
$sigX = $w - $sigWidth - 15;
|
|
$legendWidth = $sigX - $marginL - 5; // leave gutter before signature
|
|
|
|
// Legend (left column)
|
|
$pdf->SetXY($marginL, $legendY);
|
|
foreach ($legendLines as $line) {
|
|
$isBold = is_array($line) ? ($line['bold'] ?? false) : false;
|
|
$text = is_array($line) ? ($line['text'] ?? '') : $line;
|
|
$pdf->SetFont('Arial', $isBold ? 'B' : '', 8);
|
|
$pdf->MultiCell($legendWidth, $lineHeight, $text, 0, 'L');
|
|
}
|
|
$legendEndY = $pdf->GetY();
|
|
|
|
// Signature block (right column), bottom-aligned to the score block
|
|
$pdf->SetFont('Times', 'BU', 16);
|
|
$labelHeight = $finalScoreRowHeight;
|
|
|
|
$sigPath = $this->firstExisting([
|
|
FCPATH . 'images/signature.png',
|
|
FCPATH . 'assets/images/signature.png',
|
|
WRITEPATH . 'report_assets/signature.png',
|
|
]);
|
|
$signatureBlockHeight = $labelHeight + ($sigPath ? $sigHeight : 0);
|
|
$anchorEndY = ($semNum === 2 && $finalScoreEndY !== null) ? $finalScoreEndY : $scoresEndY;
|
|
$sigGap = 2;
|
|
$sigY = $anchorEndY + $sigGap;
|
|
|
|
$pdf->SetXY($sigX, $sigY);
|
|
$pdf->Cell($sigWidth + 5, $labelHeight, "School Official", 0, 0, "R");
|
|
|
|
$signatureEndY = $sigY + $labelHeight;
|
|
if ($sigPath) {
|
|
$imgY = $sigY + $labelHeight;
|
|
$pdf->Image($sigPath, $sigX+3, $imgY-2, $sigWidth+2, $sigHeight+5);
|
|
$signatureEndY = $imgY + $sigHeight;
|
|
}
|
|
|
|
// Advance cursor to the lower of legend or signature blocks plus small padding
|
|
$pdf->SetY(max($legendEndY, $signatureEndY) + 4);
|
|
}
|
|
|
|
public function generateSingleReport($studentId)
|
|
{
|
|
// Prevent Debug Toolbar from corrupting PDF
|
|
if (ENVIRONMENT !== 'production') {
|
|
// Turn off toolbar manually by unsetting the collector
|
|
unset(service('toolbar')->collectors['CodeIgniter\Debug\Toolbar\Collectors\Timers']);
|
|
}
|
|
|
|
$download = $this->request->getGet('download') === '1';
|
|
$schoolYear = $this->request->getGet('school_year') ?? $this->schoolYear;
|
|
$semester = $this->request->getGet('semester') ?? $this->semester;
|
|
$reportDate = $this->sanitizeReportDate($this->request->getGet('report_date'));
|
|
$data = $this->prepareStudentReportData((int)$studentId, (string)$schoolYear, (string)$semester);
|
|
|
|
if (!$data) {
|
|
return "Student not found or missing scores.";
|
|
}
|
|
$data['report_date'] = $reportDate['iso'];
|
|
$data['report_date_display'] = $reportDate['display'];
|
|
|
|
$pdf = new \FPDF('P', 'mm', 'Letter');
|
|
$this->formatReportPDF($pdf, $data);
|
|
|
|
$filename = 'ReportCard_' . $data['student']['lastname'] . '.pdf';
|
|
|
|
// Important: Clean output buffer to prevent PDF corruption
|
|
if (ob_get_length()) {
|
|
ob_end_clean();
|
|
}
|
|
$pdfContent = $pdf->Output('S');
|
|
$disposition = $download ? 'attachment' : 'inline';
|
|
return $this->response
|
|
->setHeader('Content-Type', 'application/pdf')
|
|
->setHeader('Content-Disposition', $disposition . '; filename="' . $filename . '"')
|
|
->setHeader('Cache-Control', 'private, max-age=0, must-revalidate')
|
|
->setHeader('Pragma', 'public')
|
|
->setHeader('Content-Length', (string) strlen($pdfContent))
|
|
->setBody($pdfContent);
|
|
}
|
|
|
|
public function generateClassReport($classSectionId)
|
|
{
|
|
// Disable toolbar output to prevent PDF corruption
|
|
if (ENVIRONMENT !== 'production') {
|
|
unset(service('toolbar')->collectors['CodeIgniter\Debug\Toolbar\Collectors\Timers']);
|
|
}
|
|
|
|
|
|
$download = $this->request->getGet('download') === '1';
|
|
$schoolYear = $this->request->getGet('school_year') ?? $this->schoolYear;
|
|
$semester = $this->request->getGet('semester') ?? $this->semester;
|
|
$reportDate = $this->sanitizeReportDate($this->request->getGet('report_date'));
|
|
|
|
$q = $this->semesterScoreModel
|
|
->select('semester_scores.*')
|
|
->join('students s', 's.id = semester_scores.student_id', 'inner')
|
|
->where('s.is_active', 1)
|
|
->where('semester_scores.class_section_id', (int)$classSectionId);
|
|
if (!empty($schoolYear)) $q->where('semester_scores.school_year', (string)$schoolYear);
|
|
if (!empty($semester)) {
|
|
$this->applySemesterFilter($q, (string)$semester, 'semester_scores.semester');
|
|
}
|
|
$scores = $q
|
|
->orderBy('s.firstname', 'ASC')
|
|
->orderBy('s.lastname', 'ASC')
|
|
->findAll();
|
|
|
|
if (!$scores) {
|
|
return "No students found for this class section.";
|
|
}
|
|
|
|
$pdf = new \FPDF('P', 'mm', 'Letter');
|
|
|
|
foreach ($scores as $score) {
|
|
$studentId = $score['student_id'];
|
|
|
|
$data = $this->prepareStudentReportData((int)$studentId, (string)$schoolYear, (string)$semester);
|
|
|
|
if ($data) {
|
|
$data['report_date'] = $reportDate['iso'];
|
|
$data['report_date_display'] = $reportDate['display'];
|
|
$this->formatReportPDF($pdf, $data);
|
|
}
|
|
}
|
|
|
|
$filename = 'ClassReport_Section_' . $classSectionId . '.pdf';
|
|
|
|
// Clean any existing output buffer
|
|
if (ob_get_length()) {
|
|
ob_end_clean();
|
|
}
|
|
$pdfContent = $pdf->Output('S');
|
|
$disposition = $download ? 'attachment' : 'inline';
|
|
return $this->response
|
|
->setHeader('Content-Type', 'application/pdf')
|
|
->setHeader('Content-Disposition', $disposition . '; filename="' . $filename . '"')
|
|
->setHeader('Cache-Control', 'private, max-age=0, must-revalidate')
|
|
->setHeader('Pragma', 'public')
|
|
->setHeader('Content-Length', (string) strlen($pdfContent))
|
|
->setBody($pdfContent);
|
|
}
|
|
|
|
|
|
protected function normalizeSemester(?string $input): string
|
|
{
|
|
$s = strtolower(trim((string)$input));
|
|
if ($s === 'fall' || $s === 'first' || str_contains($s, 'fall') || str_contains($s, '1')) return 'fall';
|
|
if ($s === 'spring' || $s === 'second' || str_contains($s, 'spring') || str_contains($s, '2')) return 'spring';
|
|
return '';
|
|
}
|
|
|
|
protected function semesterVariants(?string $semester): array
|
|
{
|
|
$raw = trim((string)$semester);
|
|
if ($raw === '') {
|
|
return [];
|
|
}
|
|
|
|
$norm = $this->normalizeSemester($raw);
|
|
if ($norm === 'fall') {
|
|
$vals = ['Fall', 'fall', 'First', 'first', 'Semester 1', 'semester 1', 'Semester1', 'semester1', 'Sem 1', 'sem 1', '1', '01', 'S1', 's1'];
|
|
} elseif ($norm === 'spring') {
|
|
$vals = ['Spring', 'spring', 'Second', 'second', 'Semester 2', 'semester 2', 'Semester2', 'semester2', 'Sem 2', 'sem 2', '2', '02', 'S2', 's2'];
|
|
} else {
|
|
$vals = [$raw];
|
|
}
|
|
$vals[] = $raw;
|
|
|
|
return array_values(array_unique($vals));
|
|
}
|
|
|
|
protected function applySemesterFilter($builder, ?string $semester, string $field = 'semester'): void
|
|
{
|
|
$vals = $this->semesterVariants($semester);
|
|
if (!empty($vals)) {
|
|
$builder->whereIn($field, $vals);
|
|
}
|
|
}
|
|
|
|
protected function applySemesterExclusion($builder, ?string $semester, string $field = 'semester'): void
|
|
{
|
|
$vals = $this->semesterVariants($semester);
|
|
if (!empty($vals)) {
|
|
$builder->whereNotIn($field, $vals);
|
|
}
|
|
}
|
|
|
|
protected function listSundays(string $startDate, string $endDate): array
|
|
{
|
|
try {
|
|
$start = new \DateTimeImmutable($startDate);
|
|
} catch (\Throwable) {
|
|
return [];
|
|
}
|
|
try {
|
|
$end = new \DateTimeImmutable($endDate);
|
|
} catch (\Throwable) {
|
|
return [];
|
|
}
|
|
|
|
$sundays = [];
|
|
$cursor = $start;
|
|
$w = (int)$cursor->format('w');
|
|
if ($w !== 0) {
|
|
$cursor = $cursor->modify('next sunday');
|
|
}
|
|
|
|
while ($cursor <= $end) {
|
|
$sundays[] = $cursor->format('Y-m-d');
|
|
$cursor = $cursor->modify('+7 days');
|
|
}
|
|
return $sundays;
|
|
}
|
|
|
|
protected function resolveAnchorSunday(): string
|
|
{
|
|
$tzName = (string) (config('School')->attendance['timezone'] ?? user_timezone());
|
|
try {
|
|
$tzObj = new \DateTimeZone($tzName ?: 'UTC');
|
|
} catch (\Throwable) {
|
|
try {
|
|
$tzObj = new \DateTimeZone(user_timezone() ?: 'UTC');
|
|
} catch (\Throwable) {
|
|
$tzObj = new \DateTimeZone('UTC');
|
|
}
|
|
}
|
|
try {
|
|
$now = new \DateTime('now', $tzObj);
|
|
} catch (\Throwable) {
|
|
$now = new \DateTime('now');
|
|
}
|
|
$weekday = (int)$now->format('w');
|
|
if ($weekday === 0) {
|
|
return $now->format('Y-m-d');
|
|
}
|
|
return $now->modify('next sunday')->format('Y-m-d');
|
|
}
|
|
protected function prepareStudentReportData(int $studentId, ?string $schoolYear = null, ?string $semester = null)
|
|
{
|
|
// 0) Student
|
|
$student = $this->studentModel->find($studentId);
|
|
if (!$student || (int)($student['is_active'] ?? 1) !== 1) return null;
|
|
|
|
helper('attendance_comment');
|
|
|
|
// 1) Resolve year/semester
|
|
$refYear = $schoolYear ?: ($this->schoolYear ?? '');
|
|
$refSemester = $semester ?: ($this->semester ?? '');
|
|
|
|
// 2) Grade(s) for the year (best effort)
|
|
$grade = $this->studentClassModel->getClassSectionsByStudentId($studentId, $refYear);
|
|
|
|
// 3) Pull the semester score (filtered if provided)
|
|
$qb = $this->semesterScoreModel->where('student_id', $studentId);
|
|
if ($refYear !== '') $qb->where('school_year', $refYear);
|
|
if ($refSemester !== '') {
|
|
$this->applySemesterFilter($qb, $refSemester, 'semester');
|
|
}
|
|
$score = $qb->orderBy('updated_at', 'DESC')->orderBy('id', 'DESC')->first();
|
|
if (!$score) return null;
|
|
|
|
// ----------------------------
|
|
// Resolve section PK and code
|
|
// ----------------------------
|
|
$sectionCode = (int)($score['class_section_id'] ?? 0); // may be code or PK (legacy)
|
|
$sectionId = null;
|
|
|
|
if ($sectionCode > 0) {
|
|
// Try as CODE first
|
|
$row = $this->db->table('classSection')
|
|
->select('id, class_section_id, class_section_name')
|
|
->where('class_section_id', $sectionCode)->limit(1)->get()->getRowArray();
|
|
if ($row) {
|
|
$sectionId = (int)$row['id'];
|
|
$sectionCode = (int)$row['class_section_id'];
|
|
} else {
|
|
// Maybe score stored PK directly
|
|
$row2 = $this->db->table('classSection')
|
|
->select('id, class_section_id, class_section_name')
|
|
->where('id', $sectionCode)->limit(1)->get()->getRowArray();
|
|
if ($row2) {
|
|
$sectionId = (int)$row2['id'];
|
|
$sectionCode = (int)$row2['class_section_id'];
|
|
}
|
|
}
|
|
}
|
|
|
|
if (!$sectionId || $sectionCode <= 0) {
|
|
// Fallback to latest student_class for same year
|
|
try {
|
|
$scRow = $this->db->table('student_class')
|
|
->select('class_section_id')
|
|
->where('student_id', $studentId)
|
|
->where('school_year', (string)($score['school_year'] ?? $refYear))
|
|
->orderBy('COALESCE(updated_at, created_at)', 'DESC', false)
|
|
->limit(1)->get()->getRowArray();
|
|
if (!empty($scRow['class_section_id'])) {
|
|
$sectionCode = (int)$scRow['class_section_id'];
|
|
$row = $this->db->table('classSection')
|
|
->select('id')->where('class_section_id', $sectionCode)
|
|
->limit(1)->get()->getRowArray();
|
|
if ($row) $sectionId = (int)$row['id'];
|
|
}
|
|
} catch (\Throwable $e) { /* ignore */ }
|
|
}
|
|
|
|
// ----------------------------
|
|
// Teacher & TA lookup (single source of truth)
|
|
// ----------------------------
|
|
$teacherName = 'N/A';
|
|
$teacherNamesList = [];
|
|
$taNames = [];
|
|
|
|
// Prefer the section CODE (what teacher_class stores) and fall back to PK if needed
|
|
if ($sectionCode > 0) {
|
|
$names = $this->resolveTeacherAndTAs((int)$sectionCode, $refYear, $refSemester);
|
|
$teacherName = $names['teacher_name'];
|
|
$taNames = $names['ta_names'];
|
|
$teacherNamesList = $names['teacher_names'] ?? [];
|
|
}
|
|
// If code lookup failed or is empty, try the PK (legacy data may store either)
|
|
if (($teacherName === 'N/A' || empty($taNames)) && $sectionId) {
|
|
$names = $this->resolveTeacherAndTAs((int)$sectionId, $refYear, $refSemester);
|
|
if ($teacherName === 'N/A') {
|
|
$teacherName = $names['teacher_name'];
|
|
}
|
|
if (empty($taNames)) {
|
|
$taNames = $names['ta_names'];
|
|
}
|
|
if (empty($teacherNamesList) && !empty($names['teacher_names'])) {
|
|
$teacherNamesList = $names['teacher_names'];
|
|
}
|
|
}
|
|
|
|
$taNames = array_values(array_unique(array_filter($taNames)));
|
|
$taLine = implode(', ', $taNames);
|
|
|
|
// Fallback: if no teacher assignment found, try the user who updated the score
|
|
if (($teacherName === 'N/A' || trim((string)$teacherName) === '') && !empty($score['updated_by'])) {
|
|
try {
|
|
$updId = (int)$score['updated_by'];
|
|
if ($updId > 0) {
|
|
$u = $this->userModel->find($updId);
|
|
if ($u) {
|
|
$fallbackName = trim(((string)($u['firstname'] ?? '')) . ' ' . ((string)($u['lastname'] ?? '')));
|
|
if ($fallbackName !== '') {
|
|
log_message(
|
|
'warning',
|
|
'ReportCard: No teacher assignment found; using updated_by as teacher. section_id={sectionId} section_code={sectionCode} year={year} sem={sem} updated_by={uid} name={name}',
|
|
[
|
|
'sectionId' => $sectionId,
|
|
'sectionCode' => $sectionCode,
|
|
'year' => (string)($score['school_year'] ?? $refYear),
|
|
'sem' => (string)($score['semester'] ?? $refSemester),
|
|
'uid' => $updId,
|
|
'name' => $fallbackName,
|
|
]
|
|
);
|
|
$teacherName = $fallbackName;
|
|
}
|
|
}
|
|
}
|
|
} catch (\Throwable $e) {
|
|
// ignore fallback errors
|
|
}
|
|
}
|
|
|
|
// ----------------------------
|
|
// Class section name (display)
|
|
// ----------------------------
|
|
$classSectionName = 'N/A';
|
|
if ($sectionId) {
|
|
$cs = $this->classSectionModel->find($sectionId);
|
|
if (!empty($cs['class_section_name'])) $classSectionName = $cs['class_section_name'];
|
|
} elseif ($sectionCode > 0) {
|
|
$cs = $this->db->table('classSection')->select('class_section_name')
|
|
->where('class_section_id', $sectionCode)->limit(1)->get()->getRowArray();
|
|
if (!empty($cs['class_section_name'])) $classSectionName = $cs['class_section_name'];
|
|
}
|
|
|
|
// ----------------------------
|
|
// Names & scores
|
|
// ----------------------------
|
|
$studentName = trim(($student['firstname'] ?? '') . ' ' . ($student['lastname'] ?? ''));
|
|
$studentFirstName = trim((string)($student['firstname'] ?? ''));
|
|
|
|
$num = fn($v) => is_numeric($v) ? (float)$v : null;
|
|
$hw = $num($score['homework_avg'] ?? null) ?? 0.0;
|
|
$qz = $num($score['quiz_avg'] ?? null) ?? 0.0;
|
|
$prj = $num($score['project_avg'] ?? null) ?? 0.0;
|
|
$tst = $num($score['test_avg'] ?? null) ?? 0.0;
|
|
$semesterForAttendance = $refSemester !== '' ? $refSemester : (string)($score['semester'] ?? '');
|
|
$normSemester = $this->normalizeSemester($semesterForAttendance);
|
|
if ($normSemester === 'spring') {
|
|
$finalExam = $num($score['final_exam_score'] ?? null);
|
|
if ($finalExam === null) {
|
|
$finalExam = $num($score['midterm_exam_score'] ?? null);
|
|
}
|
|
} elseif ($normSemester === 'fall') {
|
|
$finalExam = $num($score['midterm_exam_score'] ?? null);
|
|
} else {
|
|
$finalExam = $num($score['final_exam_score'] ?? $score['midterm_exam_score'] ?? null);
|
|
}
|
|
$att = $num($score['attendance_score'] ?? null);
|
|
|
|
$ptap = $num($score['ptap_score'] ?? null);
|
|
if ($ptap === null) {
|
|
$ptap = round((($hw + $qz + $prj + $tst) / 4), 1);
|
|
}
|
|
$ptap = max(0, min(100, $ptap));
|
|
|
|
$secondSemesterScore = $num($score['semester_score'] ?? null);
|
|
if ($secondSemesterScore === null && $finalExam !== null && $att !== null) {
|
|
$secondSemesterScore = round(($finalExam * 0.6) + ($ptap * 0.2) + ($att * 0.2), 1);
|
|
}
|
|
if ($secondSemesterScore !== null) {
|
|
$secondSemesterScore = max(0, min(100, $secondSemesterScore));
|
|
}
|
|
|
|
// Pick up comments from score_comments if available
|
|
$commentMap = [];
|
|
try {
|
|
$commentBuilder = $this->scoreCommentModel
|
|
->where('student_id', $studentId)
|
|
->where('school_year', $refYear)
|
|
->whereIn('score_type', ['final', 'midterm', 'ptap', 'attendance', 'attendance_comment']);
|
|
if ($refSemester !== '') {
|
|
$this->applySemesterFilter($commentBuilder, $refSemester, 'semester');
|
|
}
|
|
$commentRows = $commentBuilder->findAll();
|
|
foreach ($commentRows as $row) {
|
|
$typeRaw = strtolower(trim((string)($row['score_type'] ?? '')));
|
|
if ($typeRaw === '') {
|
|
continue;
|
|
}
|
|
// Normalize common variants to a single key
|
|
if ($typeRaw === 'attendance_comment') {
|
|
$typeRaw = 'attendance';
|
|
}
|
|
$rawComment = trim((string)($row['comment'] ?? ''));
|
|
if ($typeRaw === 'attendance') {
|
|
// Attendance comments must come from score_comments.comment.
|
|
$commentVal = $rawComment;
|
|
} else {
|
|
$reviewVal = trim((string)($row['comment_review'] ?? ''));
|
|
$commentVal = $this->db->fieldExists('comment_review', 'score_comments') && $reviewVal !== ''
|
|
? $reviewVal
|
|
: $rawComment;
|
|
}
|
|
if ($commentVal === '') {
|
|
continue;
|
|
}
|
|
$commentMap[$typeRaw] = $commentVal;
|
|
}
|
|
} catch (\Throwable $e) {
|
|
// ignore comment failures
|
|
}
|
|
|
|
// Expose attendance comment alongside scores for easier PDF fallback
|
|
if (isset($commentMap['attendance']) && !isset($score['attendance_comment'])) {
|
|
$score['attendance_comment'] = $commentMap['attendance'];
|
|
}
|
|
|
|
// If attendance comment is still missing, auto-generate from attendance score for the PDF
|
|
$attendanceCommentVal = $commentMap['attendance'] ?? $score['attendance_comment'] ?? null;
|
|
if ((!is_string($attendanceCommentVal) || trim($attendanceCommentVal) === '') && $att !== null) {
|
|
$autoAttendance = attendance_comment_from_score($att, $studentFirstName ?? '');
|
|
if ($autoAttendance !== null) {
|
|
$commentMap['attendance'] = $autoAttendance;
|
|
if (!isset($score['attendance_comment'])) {
|
|
$score['attendance_comment'] = $autoAttendance;
|
|
}
|
|
}
|
|
}
|
|
|
|
// First semester score (for second-semester reports) by looking for any other term in the same year
|
|
$firstSemesterScore = null;
|
|
try {
|
|
$other = $this->semesterScoreModel
|
|
->where('student_id', $studentId)
|
|
->where('school_year', $refYear);
|
|
if ($refSemester !== '') {
|
|
$this->applySemesterExclusion($other, $refSemester, 'semester');
|
|
}
|
|
$other = $other
|
|
->orderBy('updated_at', 'DESC')
|
|
->orderBy('id', 'DESC')
|
|
->first();
|
|
if ($other && is_numeric($other['semester_score'] ?? null)) {
|
|
$firstSemesterScore = (float)$other['semester_score'];
|
|
}
|
|
} catch (\Throwable $e) {
|
|
// ignore
|
|
}
|
|
if ($firstSemesterScore === null && in_array(strtolower(trim((string)$refSemester)), ['fall', 'first', '1', 'semester 1'], true)) {
|
|
$firstSemesterScore = $secondSemesterScore;
|
|
}
|
|
|
|
$rankingScore = $secondSemesterScore;
|
|
if ($normSemester === 'spring' && is_numeric($firstSemesterScore) && is_numeric($secondSemesterScore)) {
|
|
$rankingScore = round(((float)$firstSemesterScore + (float)$secondSemesterScore) / 2, 1);
|
|
}
|
|
|
|
$termRanking = $this->calculateTermRanking(
|
|
$studentId,
|
|
$sectionCode,
|
|
$sectionId,
|
|
$refYear,
|
|
$refSemester !== '' ? $refSemester : (string)($score['semester'] ?? ''),
|
|
$rankingScore
|
|
);
|
|
|
|
// Total semester days from attendance records (max total_attendance within same term)
|
|
$totalSemesterDays = null;
|
|
|
|
if ($refYear !== '' && $normSemester !== '') {
|
|
$semRange = $this->semesterRangeService->getSemesterRange($refYear, ucfirst($normSemester));
|
|
if ($semRange) {
|
|
try {
|
|
// Fetch all events for the year (no semester filter) so that
|
|
// no-school events stored with a different semester label or
|
|
// NULL semester are still picked up. Date-range filtering below.
|
|
$events = $this->calendarModel->getEventsBySchoolYearAndSemester($refYear, null);
|
|
$noSchoolDays = [];
|
|
foreach ($events as $event) {
|
|
if (empty($event['no_school'])) {
|
|
continue;
|
|
}
|
|
$dateStr = substr((string)($event['date'] ?? ''), 0, 10);
|
|
if ($dateStr === '' || $dateStr < $semRange[0] || $dateStr > $semRange[1]) {
|
|
continue;
|
|
}
|
|
$noSchoolDays[$dateStr] = true;
|
|
}
|
|
$sundays = $this->listSundays($semRange[0], $semRange[1]);
|
|
$limit = $semRange[1];
|
|
// Only cap at "today" while the semester is still in progress.
|
|
// Once the semester end date has passed, count all its Sundays.
|
|
if ($semRange[1] > date('Y-m-d')) {
|
|
$anchor = $this->resolveAnchorSunday();
|
|
if ($anchor !== '' && $anchor >= $semRange[0]) {
|
|
$limit = min($anchor, $semRange[1]);
|
|
}
|
|
}
|
|
$totalSemesterDays = 0;
|
|
foreach ($sundays as $date) {
|
|
if ($date > $limit) {
|
|
continue;
|
|
}
|
|
if (!empty($noSchoolDays[$date])) {
|
|
continue;
|
|
}
|
|
$totalSemesterDays++;
|
|
}
|
|
} catch (\Throwable $e) {
|
|
log_message('error', 'Failed to count sundays for report card: ' . $e->getMessage());
|
|
$totalSemesterDays = null;
|
|
}
|
|
}
|
|
}
|
|
|
|
if ($totalSemesterDays === null) {
|
|
try {
|
|
$attRows = $this->attendanceRecordModel
|
|
->select('total_attendance')
|
|
->where('student_id', $studentId)
|
|
->where('school_year', $refYear);
|
|
if ($semesterForAttendance !== '') {
|
|
$this->applySemesterFilter($attRows, $semesterForAttendance, 'semester');
|
|
}
|
|
$vals = array_values(array_filter(array_map(
|
|
static fn($r) => isset($r['total_attendance']) ? (int)$r['total_attendance'] : null,
|
|
$attRows->findAll()
|
|
), static fn($v) => $v !== null));
|
|
if (!empty($vals)) {
|
|
$totalSemesterDays = max($vals);
|
|
}
|
|
} catch (\Throwable $e) {
|
|
// ignore attendance lookup errors
|
|
}
|
|
}
|
|
|
|
// Load attendance sums (absences, lates)
|
|
$attendanceTotals = [
|
|
'total_absences' => 0,
|
|
'total_tardies' => 0,
|
|
];
|
|
try {
|
|
$attendanceDataModel = new AttendanceDataModel();
|
|
$absences = $attendanceDataModel
|
|
->where('student_id', $studentId)
|
|
->where('school_year', $refYear)
|
|
->where('status', 'absent');
|
|
if ($semesterForAttendance !== '') {
|
|
$this->applySemesterFilter($absences, $semesterForAttendance, 'semester');
|
|
}
|
|
$attendanceTotals['total_absences'] = $absences->countAllResults();
|
|
|
|
$lates = $attendanceDataModel
|
|
->where('student_id', $studentId)
|
|
->where('school_year', $refYear)
|
|
->where('status', 'late');
|
|
if ($semesterForAttendance !== '') {
|
|
$this->applySemesterFilter($lates, $semesterForAttendance, 'semester');
|
|
}
|
|
$attendanceTotals['total_tardies'] = $lates->countAllResults();
|
|
} catch (\Throwable $e) {
|
|
// ignore attendance lookup errors
|
|
}
|
|
|
|
if ($score) {
|
|
$score['total_absences'] = $attendanceTotals['total_absences'];
|
|
$score['total_tardies'] = $attendanceTotals['total_tardies'];
|
|
}
|
|
|
|
$finalAverage = null;
|
|
if (is_numeric($firstSemesterScore) && is_numeric($secondSemesterScore)) {
|
|
$finalAverage = number_format(((float)$firstSemesterScore + (float)$secondSemesterScore) / 2, 1);
|
|
}
|
|
|
|
return [
|
|
'student' => $student,
|
|
'student_name' => ($studentName !== '' ? $studentName : 'N/A'),
|
|
'teacher_name' => ($teacherName !== '' ? $teacherName : 'N/A'),
|
|
'teacher_names' => array_values(array_unique(array_filter($teacherNamesList))),
|
|
'ta_names' => $taNames,
|
|
'ta_names_line' => ($taLine !== '' ? $taLine : 'N/A'),
|
|
'class_section_name' => $classSectionName,
|
|
'class_section_code' => $sectionCode ?: null,
|
|
'class_section_id' => $sectionId ?: null,
|
|
'selected_semester' => $refSemester,
|
|
'grade' => $grade,
|
|
'score' => $score,
|
|
'ptap' => $ptap,
|
|
'final_exam_score' => $finalExam,
|
|
'second_semester_score' => $secondSemesterScore,
|
|
'first_semester_score' => $firstSemesterScore,
|
|
'final_average' => $finalAverage,
|
|
'term_rank' => $termRanking,
|
|
'term_rank_display' => $termRanking['display'] ?? null,
|
|
'comments' => $commentMap,
|
|
'total_score' => $secondSemesterScore,
|
|
'total_attendance_days' => $totalSemesterDays,
|
|
];
|
|
}
|
|
|
|
private function calculateTermRanking(
|
|
int $studentId,
|
|
int $sectionCode,
|
|
?int $sectionId,
|
|
string $schoolYear,
|
|
?string $semester,
|
|
?float $studentScore
|
|
): ?array {
|
|
if ($studentId <= 0 || $schoolYear === '' || $studentScore === null) {
|
|
return null;
|
|
}
|
|
|
|
$sectionIds = array_values(array_unique(array_filter([
|
|
$sectionCode > 0 ? $sectionCode : null,
|
|
$sectionId && $sectionId > 0 ? $sectionId : null,
|
|
])));
|
|
|
|
if (empty($sectionIds)) {
|
|
return null;
|
|
}
|
|
|
|
/*
|
|
* Rank must be limited to the actual selected class roster.
|
|
* Do NOT rank by every semester_scores row that happens to share class_section_id,
|
|
* because old/mismatched score rows can inflate the denominator (for example 18 students
|
|
* in 3-A showing as "out of 32"). The roster is the source of truth for class size.
|
|
*/
|
|
$rosterRows = $this->fetchStudentsByClass($sectionCode, $schoolYear);
|
|
if (empty($rosterRows) && $sectionId && $sectionId !== $sectionCode) {
|
|
$rosterRows = $this->fetchStudentsByClass($sectionId, $schoolYear);
|
|
}
|
|
|
|
$rosterByStudent = [];
|
|
foreach ($rosterRows as $row) {
|
|
$sid = (int)($row['id'] ?? 0);
|
|
if ($sid <= 0) {
|
|
continue;
|
|
}
|
|
$rosterByStudent[$sid] = [
|
|
'firstname' => trim((string)($row['firstname'] ?? '')),
|
|
'lastname' => trim((string)($row['lastname'] ?? '')),
|
|
];
|
|
}
|
|
|
|
if (empty($rosterByStudent) || !isset($rosterByStudent[$studentId])) {
|
|
return null;
|
|
}
|
|
|
|
$rosterStudentIds = array_keys($rosterByStudent);
|
|
|
|
$semesterForRank = trim((string)$semester);
|
|
$rankByFinalScore = $this->normalizeSemester($semesterForRank) === 'spring';
|
|
|
|
$builder = $this->db->table('semester_scores ss')
|
|
->select('ss.student_id, ss.semester, ss.semester_score, ss.updated_at, ss.id')
|
|
->where('ss.school_year', $schoolYear)
|
|
->whereIn('ss.student_id', $rosterStudentIds)
|
|
->orderBy('ss.updated_at', 'DESC')
|
|
->orderBy('ss.id', 'DESC');
|
|
|
|
if ($semesterForRank !== '') {
|
|
$this->applySemesterFilter($builder, $semesterForRank, 'ss.semester');
|
|
}
|
|
|
|
$rows = $builder->get()->getResultArray();
|
|
|
|
if (empty($rows)) {
|
|
return null;
|
|
}
|
|
|
|
$scoresByStudent = [];
|
|
|
|
foreach ($rows as $row) {
|
|
$sid = (int)($row['student_id'] ?? 0);
|
|
|
|
if ($sid <= 0 || !isset($rosterByStudent[$sid]) || isset($scoresByStudent[$sid])) {
|
|
continue;
|
|
}
|
|
|
|
$scoreVal = $row['semester_score'] ?? null;
|
|
|
|
if (!is_numeric($scoreVal)) {
|
|
continue;
|
|
}
|
|
|
|
$rawScore = (float)$scoreVal;
|
|
|
|
$scoresByStudent[$sid] = [
|
|
'student_id' => $sid,
|
|
'score' => $rawScore,
|
|
'rank_score' => round($rawScore, 1),
|
|
'firstname' => $rosterByStudent[$sid]['firstname'],
|
|
'lastname' => $rosterByStudent[$sid]['lastname'],
|
|
];
|
|
}
|
|
|
|
if (empty($scoresByStudent) || !isset($scoresByStudent[$studentId])) {
|
|
return null;
|
|
}
|
|
|
|
// For Spring, rank by final year average:
|
|
// (first semester score + second semester score) / 2.
|
|
if ($rankByFinalScore) {
|
|
$studentIds = array_keys($scoresByStudent);
|
|
|
|
$firstRowsBuilder = $this->db->table('semester_scores ss')
|
|
->select('ss.student_id, ss.semester, ss.semester_score, ss.updated_at, ss.id')
|
|
->where('ss.school_year', $schoolYear)
|
|
->whereIn('ss.student_id', $studentIds)
|
|
->orderBy('ss.updated_at', 'DESC')
|
|
->orderBy('ss.id', 'DESC');
|
|
|
|
if ($semesterForRank !== '') {
|
|
$this->applySemesterExclusion($firstRowsBuilder, $semesterForRank, 'ss.semester');
|
|
}
|
|
|
|
$firstRows = $firstRowsBuilder->get()->getResultArray();
|
|
|
|
$firstScoresByStudent = [];
|
|
|
|
foreach ($firstRows as $row) {
|
|
$sid = (int)($row['student_id'] ?? 0);
|
|
|
|
if ($sid <= 0 || !isset($rosterByStudent[$sid]) || isset($firstScoresByStudent[$sid])) {
|
|
continue;
|
|
}
|
|
|
|
$scoreVal = $row['semester_score'] ?? null;
|
|
|
|
if (!is_numeric($scoreVal)) {
|
|
continue;
|
|
}
|
|
|
|
$firstScoresByStudent[$sid] = (float)$scoreVal;
|
|
}
|
|
|
|
foreach ($scoresByStudent as $sid => &$rankRow) {
|
|
if (!isset($firstScoresByStudent[$sid])) {
|
|
unset($scoresByStudent[$sid]);
|
|
continue;
|
|
}
|
|
|
|
$avg = ((float)$firstScoresByStudent[$sid] + (float)$rankRow['score']) / 2;
|
|
|
|
$rankRow['score'] = $avg;
|
|
$rankRow['rank_score'] = round($avg, 1);
|
|
}
|
|
|
|
unset($rankRow);
|
|
|
|
if (empty($scoresByStudent) || !isset($scoresByStudent[$studentId])) {
|
|
return null;
|
|
}
|
|
|
|
// Force the selected student to use the exact score already computed for the report.
|
|
// Then rank using the displayed precision: one decimal.
|
|
$scoresByStudent[$studentId]['score'] = (float)$studentScore;
|
|
$scoresByStudent[$studentId]['rank_score'] = round((float)$studentScore, 1);
|
|
} else {
|
|
// Fall: also force selected student to match the report-card computed score.
|
|
$scoresByStudent[$studentId]['score'] = (float)$studentScore;
|
|
$scoresByStudent[$studentId]['rank_score'] = round((float)$studentScore, 1);
|
|
}
|
|
|
|
$rankable = array_values($scoresByStudent);
|
|
|
|
usort($rankable, static function (array $a, array $b): int {
|
|
// Highest displayed/rank score first.
|
|
$scoreCmp = $b['rank_score'] <=> $a['rank_score'];
|
|
|
|
if ($scoreCmp !== 0) {
|
|
return $scoreCmp;
|
|
}
|
|
|
|
// Tie-breakers only control display order.
|
|
// They do NOT change rank.
|
|
$lastCmp = strcasecmp($a['lastname'], $b['lastname']);
|
|
|
|
if ($lastCmp !== 0) {
|
|
return $lastCmp;
|
|
}
|
|
|
|
$firstCmp = strcasecmp($a['firstname'], $b['firstname']);
|
|
|
|
if ($firstCmp !== 0) {
|
|
return $firstCmp;
|
|
}
|
|
|
|
return $a['student_id'] <=> $b['student_id'];
|
|
});
|
|
|
|
$position = null;
|
|
$previousScore = null;
|
|
|
|
foreach ($rankable as $index => $row) {
|
|
$currentScore = (float)$row['rank_score'];
|
|
|
|
// Competition ranking:
|
|
// 1, 1, 3, 4...
|
|
// Same score = same rank.
|
|
if ($previousScore === null || abs($currentScore - $previousScore) > 0.0001) {
|
|
$position = $index + 1;
|
|
$previousScore = $currentScore;
|
|
}
|
|
|
|
if ((int)$row['student_id'] === $studentId) {
|
|
$total = count($rankable);
|
|
|
|
return [
|
|
'position' => $position,
|
|
'total' => $total,
|
|
'score' => $currentScore,
|
|
'display' => $this->formatOrdinal($position) . ' out of ' . $total,
|
|
];
|
|
}
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
private function formatOrdinal(?int $value): string
|
|
{
|
|
$n = (int)$value;
|
|
if ($n <= 0) {
|
|
return '';
|
|
}
|
|
|
|
$mod100 = $n % 100;
|
|
if ($mod100 >= 11 && $mod100 <= 13) {
|
|
return $n . 'th';
|
|
}
|
|
|
|
return match ($n % 10) {
|
|
1 => $n . 'st',
|
|
2 => $n . 'nd',
|
|
3 => $n . 'rd',
|
|
default => $n . 'th',
|
|
};
|
|
}
|
|
|
|
/**
|
|
* Resolve teacher & TA names for a class section (by PK or code),
|
|
* relaxing semester/year if needed. Returns ['teacher_name' => string, 'ta_names' => string[]].
|
|
*/
|
|
private function resolveTeacherAndTAs(int $sectionIdOrCode, ?string $schoolYear = null, ?string $semester = null): array
|
|
{
|
|
$teacherName = 'N/A';
|
|
$teacherNames = [];
|
|
$taNames = [];
|
|
|
|
$withPrefix = static function (array $row): string {
|
|
$first = trim((string)($row['firstname'] ?? ''));
|
|
$last = trim((string)($row['lastname'] ?? ''));
|
|
$gender = strtolower(trim((string)($row['gender'] ?? '')));
|
|
$name = trim($first . ' ' . $last);
|
|
if ($name === '') return '';
|
|
$prefix = ($gender === 'female') ? 'Sr ' : (($gender === 'male') ? 'Br ' : '');
|
|
return trim($prefix . $name);
|
|
};
|
|
|
|
$normalizePos = static function (?string $pos): string {
|
|
$p = strtolower(trim((string)$pos));
|
|
if ($p === 'teacher' || str_contains($p, 'main') || str_contains($p, 'co-teach')) return 'main';
|
|
if ($p === 'ta' || str_contains($p, 'assist')) return 'ta';
|
|
return 'other';
|
|
};
|
|
|
|
$consume = static function (array $rows) use (&$teacherName, &$teacherNames, &$taNames, $normalizePos, $withPrefix) {
|
|
$any = false;
|
|
$first = null;
|
|
foreach ($rows as $r) {
|
|
$full = $withPrefix($r);
|
|
if ($full === '') continue;
|
|
if ($first === null) $first = $full;
|
|
$any = true;
|
|
|
|
$cls = $normalizePos($r['position'] ?? null);
|
|
if ($cls === 'main') {
|
|
if ($teacherName === 'N/A') {
|
|
$teacherName = $full;
|
|
}
|
|
$teacherNames[] = $full;
|
|
} elseif ($cls === 'ta') {
|
|
$taNames[] = $full;
|
|
}
|
|
}
|
|
if ($teacherName === 'N/A' && $any && $first) {
|
|
$teacherName = $first;
|
|
$teacherNames[] = $first;
|
|
}
|
|
};
|
|
|
|
// 1) Try your model if available
|
|
if (method_exists($this->teacherClassModel, 'getTeacherTABySection')) {
|
|
$rows = $this->teacherClassModel->getTeacherTABySection($sectionIdOrCode, (string)$semester, (string)$schoolYear) ?? [];
|
|
if (!empty($rows)) {
|
|
if (isset($rows['teacher_names']) || isset($rows['teacher_name']) || isset($rows['tas'])) {
|
|
$aggTeacherNames = $rows['teacher_names'] ?? [];
|
|
if (empty($aggTeacherNames) && !empty($rows['teacher_name'])) {
|
|
$aggTeacherNames = [$rows['teacher_name']];
|
|
}
|
|
foreach ($aggTeacherNames as $nm) {
|
|
$nm = trim((string)$nm);
|
|
if ($nm !== '') {
|
|
if ($teacherName === 'N/A') $teacherName = $nm;
|
|
$teacherNames[] = $nm;
|
|
}
|
|
}
|
|
foreach (($rows['tas'] ?? []) as $taRow) {
|
|
if (is_array($taRow) && isset($taRow['name'])) {
|
|
$taNm = trim((string)$taRow['name']);
|
|
if ($taNm !== '') $taNames[] = $taNm;
|
|
} elseif (is_string($taRow)) {
|
|
$taNm = trim($taRow);
|
|
if ($taNm !== '') $taNames[] = $taNm;
|
|
}
|
|
}
|
|
} else {
|
|
$consume($rows);
|
|
}
|
|
}
|
|
}
|
|
|
|
// If still unknown, do robust DB queries that cover PK/code variants and relaxed filters
|
|
if ($teacherName === 'N/A') {
|
|
$ids = [$sectionIdOrCode];
|
|
|
|
$runner = function (array $ids, string $yr, string $sm) {
|
|
$qb = $this->db->table('teacher_class tc')
|
|
->select('tc.teacher_id, tc.position, u.firstname, u.lastname, u.gender')
|
|
->join('users u', 'u.id = tc.teacher_id', 'inner')
|
|
->whereIn('tc.class_section_id', array_map('intval', $ids))
|
|
->orderBy("CASE WHEN LOWER(tc.position) IN ('main','teacher','main teacher') THEN 0 ELSE 1 END", 'ASC', false)
|
|
->orderBy('COALESCE(tc.updated_at, tc.id)', 'DESC', false);
|
|
if ($yr !== '') $qb->where('tc.school_year', $yr);
|
|
return $qb->get()->getResultArray();
|
|
};
|
|
|
|
$yr = (string)($schoolYear ?? '');
|
|
$sm = (string)($semester ?? '');
|
|
|
|
$rows = $runner($ids, $yr, $sm);
|
|
if (empty($rows)) $rows = $runner($ids, $yr, '');
|
|
if (empty($rows)) $rows = $runner($ids, '', '');
|
|
if (!empty($rows)) $consume($rows);
|
|
}
|
|
|
|
// 3) Final bridge via name-join if PK/code are mixed in tc
|
|
if ($teacherName === 'N/A') {
|
|
// Resolve section name from either PK or code
|
|
$csName = null;
|
|
$byId = $this->db->table('classSection')->select('class_section_name')->where('id', $sectionIdOrCode)->get()->getRowArray();
|
|
if (!empty($byId['class_section_name'])) {
|
|
$csName = $byId['class_section_name'];
|
|
} else {
|
|
$byCode = $this->db->table('classSection')->select('class_section_name')->where('class_section_id', $sectionIdOrCode)->get()->getRowArray();
|
|
if (!empty($byCode['class_section_name'])) $csName = $byCode['class_section_name'];
|
|
}
|
|
|
|
if ($csName) {
|
|
$base = $this->db->table('teacher_class tc')
|
|
->select('tc.teacher_id, tc.position, u.firstname, u.lastname, u.gender')
|
|
->join('users u', 'u.id = tc.teacher_id', 'inner')
|
|
// unescaped join to allow OR; CI4: escape=false is the 4th arg
|
|
->join('classSection cs', 'cs.id = tc.class_section_id OR cs.class_section_id = tc.class_section_id', 'inner', false)
|
|
->where('cs.class_section_name', $csName)
|
|
->orderBy("CASE WHEN LOWER(tc.position) IN ('main','teacher','main teacher') THEN 0 ELSE 1 END", 'ASC', false)
|
|
->orderBy('COALESCE(tc.updated_at, tc.id)', 'DESC', false);
|
|
|
|
$yr = (string)($schoolYear ?? '');
|
|
$sm = (string)($semester ?? '');
|
|
|
|
$qb1 = clone $base;
|
|
if ($yr !== '') $qb1->where('tc.school_year', $yr);
|
|
$rows = $qb1->get()->getResultArray();
|
|
|
|
if (empty($rows) && $yr !== '') {
|
|
$qb2 = clone $base;
|
|
$qb2->where('tc.school_year', $yr);
|
|
$rows = $qb2->get()->getResultArray();
|
|
}
|
|
if (empty($rows)) $rows = $base->get()->getResultArray();
|
|
|
|
if (!empty($rows)) $consume($rows);
|
|
}
|
|
}
|
|
|
|
// Clean names
|
|
$taNames = array_values(array_unique(array_filter(array_map('trim', $taNames))));
|
|
$teacherNames = array_values(array_unique(array_filter(array_map('trim', $teacherNames))));
|
|
if (empty($teacherNames) && $teacherName !== 'N/A') {
|
|
$teacherNames[] = $teacherName;
|
|
}
|
|
|
|
return [
|
|
'teacher_name' => $teacherName,
|
|
'teacher_names' => $teacherNames,
|
|
'ta_names' => $taNames,
|
|
];
|
|
}
|
|
|
|
/**
|
|
* Normalize a report date (YYYY-MM-DD) from input; falls back to today.
|
|
* Returns ['iso' => 'Y-m-d', 'display' => 'F j, Y'].
|
|
*/
|
|
private function sanitizeReportDate($input): array
|
|
{
|
|
$raw = trim((string)$input);
|
|
$ts = null;
|
|
if ($raw !== '') {
|
|
$ts = strtotime($raw);
|
|
}
|
|
if ($ts === false || $ts === null) {
|
|
$ts = time();
|
|
}
|
|
$iso = date('Y-m-d', $ts);
|
|
$display = date('F j, Y', $ts);
|
|
return ['iso' => $iso, 'display' => $display];
|
|
}
|
|
|
|
}
|