root
525 lines
18 KiB
PHP
525 lines
18 KiB
PHP
<?php
|
||
|
||
namespace App\Models;
|
||
|
||
use CodeIgniter\Model;
|
||
|
||
class StudentModel extends Model
|
||
{
|
||
protected $table = 'students';
|
||
protected $primaryKey = 'id'; // Primary key of the students table
|
||
protected $allowedFields = [
|
||
'school_id',
|
||
'firstname',
|
||
'lastname',
|
||
'dob',
|
||
'age',
|
||
'gender',
|
||
'registration_grade',
|
||
'photo_consent',
|
||
'is_new',
|
||
'parent_id',
|
||
'school_year',
|
||
'registration_date',
|
||
'tuition_paid',
|
||
'year_of_registration',
|
||
'rfid_tag',
|
||
'is_active'
|
||
];
|
||
protected $useTimestamps = false;
|
||
|
||
// File: app/Models/StudentModel.php
|
||
/**
|
||
* Get all students marked as new (is_new = 1).
|
||
*
|
||
* @return array
|
||
*/
|
||
public function getNewStudents(): array
|
||
{
|
||
return $this->where('is_new', 1)
|
||
->orderBy('lastname', 'ASC')
|
||
->orderBy('firstname', 'ASC')
|
||
->findAll();
|
||
}
|
||
|
||
public function getNewStudentsWithParents(): array
|
||
{
|
||
return $this->select([
|
||
'students.*',
|
||
'u.id AS parent_id',
|
||
'u.firstname AS parent_firstname',
|
||
'u.lastname AS parent_lastname',
|
||
'u.email AS parent_email',
|
||
'u.cellphone AS parent_phone',
|
||
])
|
||
->join('users u', 'u.id = students.parent_id', 'left')
|
||
->where('students.is_new', 1)
|
||
->orderBy('students.lastname', 'ASC')
|
||
->orderBy('students.firstname', 'ASC')
|
||
->findAll();
|
||
}
|
||
|
||
/**
|
||
* Fetch only new students (is_new=1) with parent info and a single emergency contact.
|
||
* - Parent fields: parent_firstname, parent_lastname, parent_email, parent_phone
|
||
* - Emergency fields: emergency_name, emergency_relationship, emergency_phone
|
||
*/
|
||
public function getNewStudentsWithParentsAndEmergency(): array
|
||
{
|
||
return $this->select([
|
||
'students.*',
|
||
'u.firstname AS parent_firstname',
|
||
'u.lastname AS parent_lastname',
|
||
'u.email AS parent_email',
|
||
'u.cellphone AS parent_phone',
|
||
|
||
// Pick one emergency contact (MIN() works fine if you group by student)
|
||
'MIN(ec.emergency_contact_name) AS emergency_name',
|
||
'MIN(ec.relation) AS emergency_relationship',
|
||
'MIN(ec.cellphone) AS emergency_phone',
|
||
])
|
||
->join('users u', 'u.id = students.parent_id', 'left')
|
||
->join('emergency_contacts ec', 'ec.parent_id = students.parent_id', 'left')
|
||
->where('students.is_new', 1) // strictly 1; change to 'Yes' if your data uses Yes/No
|
||
->groupBy('students.id')
|
||
->orderBy('students.lastname', 'ASC')
|
||
->orderBy('students.firstname', 'ASC')
|
||
->findAll();
|
||
}
|
||
|
||
/**
|
||
* Fetch students with parent + one emergency contact.
|
||
* @param null|int $isNew Pass 1 for only new, 0 for only not-new, null for both.
|
||
*/
|
||
/**
|
||
* Fetch students with parent + one emergency contact.
|
||
*
|
||
* @param null|int $isNew 1 = only new, 0 = only not-new, null = both
|
||
* @param null|string $schoolYear e.g. "2025-2026"; pass null or "all" for no filter
|
||
* @return array
|
||
*/
|
||
public function getStudentsWithParentsAndEmergency(?string $schoolYear = null, ?int $isNew = null): array
|
||
{
|
||
$builder = $this->select([
|
||
'students.*',
|
||
'u.firstname AS parent_firstname',
|
||
'u.lastname AS parent_lastname',
|
||
'u.email AS parent_email',
|
||
'u.cellphone AS parent_phone',
|
||
'MIN(ec.emergency_contact_name) AS emergency_name',
|
||
'MIN(ec.relation) AS emergency_relationship',
|
||
'MIN(ec.cellphone) AS emergency_phone',
|
||
])
|
||
->join('users u', 'u.id = students.parent_id', 'left')
|
||
->join('emergency_contacts ec', 'ec.parent_id = students.parent_id', 'left');
|
||
|
||
// is_new filter
|
||
if ($isNew === 0 || $isNew === 1) {
|
||
$builder->where('students.is_new', $isNew);
|
||
} else {
|
||
$builder->whereIn('students.is_new', [0, 1]); // both
|
||
}
|
||
|
||
// school_year filter (skip if null or "all")
|
||
if ($schoolYear !== null && strtolower($schoolYear) !== 'all') {
|
||
$builder->where('students.school_year', $schoolYear);
|
||
}
|
||
|
||
return $builder
|
||
->groupBy('students.id')
|
||
->orderBy('students.is_new', 'DESC') // new first
|
||
->orderBy('students.lastname', 'ASC')
|
||
->orderBy('students.firstname', 'ASC')
|
||
->findAll();
|
||
}
|
||
|
||
|
||
|
||
public function getStudentSchoolIdByStudentId(int $studentId): ?string
|
||
{
|
||
$result = $this->select('school_id')
|
||
->where('id', $studentId)
|
||
->first();
|
||
|
||
return $result['school_id'] ?? null;
|
||
}
|
||
|
||
/**
|
||
* Get all students with assignments, current semester, and school year.
|
||
*/
|
||
public function getStudentsWithAssignments()
|
||
{
|
||
// Retrieve school year and semester from the configuration table
|
||
$configTable = $this->db->table('configuration');
|
||
$schoolYear = $configTable->select('config_value')
|
||
->where('config_key', 'school_year')
|
||
->get()
|
||
->getRowArray()['config_value'];
|
||
|
||
$semester = $configTable->select('config_value')
|
||
->where('config_key', 'semester')
|
||
->get()
|
||
->getRowArray()['config_value'];
|
||
|
||
return $this->db->table('students')
|
||
->select('
|
||
students.id,
|
||
students.firstname,
|
||
students.lastname,
|
||
students.age,
|
||
users.email,
|
||
users.cellphone as phone,
|
||
students.registration_grade,
|
||
classSection.class_section_name as current_class,
|
||
"' . $schoolYear . '" as school_year,
|
||
"' . $semester . '" as semester
|
||
')
|
||
->join('users', 'users.id = students.parent_id', 'left')
|
||
->join('student_class', 'student_class.student_id = students.id', 'left')
|
||
->join('class_section', 'student_class.class_section_id = classSection.id', 'left')
|
||
->get()
|
||
->getResultArray();
|
||
}
|
||
|
||
/**
|
||
* Get students assigned to a specific teacher.
|
||
*
|
||
* @param int $teacherId
|
||
* @return array
|
||
*/
|
||
public function getStudentsByTeacher($teacherId)
|
||
{
|
||
return $this->db->table('students')
|
||
->select('
|
||
students.firstname,
|
||
students.lastname,
|
||
students.age,
|
||
classSection.class_section_name,
|
||
student_class.school_year
|
||
')
|
||
->join('student_class', 'students.id = student_class.student_id', 'left')
|
||
->join('teacher_class', 'student_class.class_section_id = teacher_class.class_section_id', 'left')
|
||
->join('class_section', 'student_class.class_section_id = classSection.id', 'left')
|
||
->where('teacher_class.teacher_id', $teacherId)
|
||
->get()
|
||
->getResultArray();
|
||
}
|
||
|
||
/**
|
||
* Get all students in a specific class section.
|
||
*
|
||
* @param int $classSectionId
|
||
* @return array
|
||
*/
|
||
public function getStudentsByClass($classSectionId)
|
||
{
|
||
return $this->db->table('students')
|
||
->join('student_class', 'students.id = student_class.student_id', 'left')
|
||
->where('student_class.class_section_id', $classSectionId)
|
||
->select('students.*, student_class.class_section_id')
|
||
->get()
|
||
->getResultArray();
|
||
}
|
||
|
||
/**
|
||
* Get all students.
|
||
*
|
||
* @return array
|
||
*/
|
||
public function getAllStudents()
|
||
{
|
||
return $this->findAll();
|
||
}
|
||
|
||
/**
|
||
* Get the total number of students.
|
||
*
|
||
* @return int
|
||
*/
|
||
public function getTotalStudents()
|
||
{
|
||
return $this->countAllResults();
|
||
}
|
||
|
||
|
||
public function getFullNameById($studentId)
|
||
{
|
||
// Fetch student record by ID
|
||
$student = $this->find($studentId);
|
||
|
||
// Return null if student not found
|
||
if (empty($student)) {
|
||
return null;
|
||
}
|
||
|
||
// Extract first and last name (support common variations)
|
||
$firstName = $student['firstname'] ?? $student['first_name'] ?? $student['first'] ?? '';
|
||
$lastName = $student['lastname'] ?? $student['last_name'] ?? $student['last'] ?? '';
|
||
|
||
// Return null if both names are empty
|
||
if ($firstName === '' && $lastName === '') {
|
||
return null;
|
||
}
|
||
|
||
// Return names separately
|
||
return [
|
||
'firstname' => $firstName,
|
||
'lastname' => $lastName,
|
||
];
|
||
}
|
||
/**
|
||
* Get the full name of a student.
|
||
*
|
||
* @param array $student
|
||
* @return string
|
||
*/
|
||
public function getFullName($student)
|
||
{
|
||
return ($student['firstname'] ?? '') . ' ' . ($student['lastname'] ?? '');
|
||
}
|
||
|
||
// In the StudentModel
|
||
public function getLastRegistrationDate($ParentId)
|
||
{
|
||
return $this->select('registration_date')
|
||
->where('parent_id', $ParentId)
|
||
->orderBy('registration_date', 'DESC') // Order by most recent registration date
|
||
->limit(1) // Get only the most recent registration
|
||
->first(); // Return the first record
|
||
}
|
||
|
||
public function getByClassAndYear($class_section_id, $school_year, ?string $semester = null): array
|
||
{
|
||
$studentClassModel = new \App\Models\StudentClassModel();
|
||
|
||
$builder = $studentClassModel
|
||
->select('student_id')
|
||
->where('class_section_id', $class_section_id)
|
||
->where('school_year', $school_year);
|
||
|
||
$studentIds = $builder->findAll();
|
||
|
||
$studentIds = array_column($studentIds, 'student_id');
|
||
|
||
if (empty($studentIds)) {
|
||
return [];
|
||
}
|
||
|
||
return $this->whereIn('id', $studentIds)
|
||
->where('is_active', 1)
|
||
->orderBy('firstname', 'ASC')
|
||
->orderBy('lastname', 'ASC')
|
||
->findAll();
|
||
}
|
||
|
||
public function getSchoolIdByStudentId($studentId)
|
||
{
|
||
return $this->select('school_id')
|
||
->where('id', $studentId)
|
||
->first()['school_id'] ?? null;
|
||
}
|
||
|
||
public function getSchoolIdsByParentId($parentId)
|
||
{
|
||
$results = $this->select('school_id')
|
||
->where('parent_id', $parentId)
|
||
->distinct()
|
||
->findAll();
|
||
|
||
return array_column($results, 'school_id');
|
||
}
|
||
|
||
public function getStudentIdsByParentId($parentId)
|
||
{
|
||
$results = $this->select('id')
|
||
->where('parent_id', $parentId)
|
||
->distinct()
|
||
->findAll();
|
||
|
||
return array_column($results, 'id');
|
||
}
|
||
|
||
public function getStudentsWithClassAndEnrollment()
|
||
{
|
||
return $this->select('
|
||
students.*,
|
||
student_class.class_section_id,
|
||
enrollments.enrollment_status,
|
||
enrollments.admission_status,
|
||
u.firstname AS parent_firstname,
|
||
u.lastname AS parent_lastname
|
||
')
|
||
->join('student_class', 'student_class.student_id = students.id', 'left')
|
||
->join('enrollments', 'enrollments.student_id = students.id', 'left')
|
||
->join('users u', 'u.id = students.parent_id', 'left')
|
||
// keep your original grouping as-is so behavior doesn't change
|
||
->groupBy('students.id, student_class.class_section_id, enrollments.enrollment_status, enrollments.admission_status')
|
||
->findAll();
|
||
}
|
||
|
||
/*
|
||
public function getStudentsWithClassAndEnrollment()
|
||
{
|
||
return $this->select('students.*, student_class.class_section_id, enrollments.enrollment_status, enrollments.admission_status')
|
||
->join('student_class', 'student_class.student_id = students.id', 'left')
|
||
->join('enrollments', 'enrollments.student_id = students.id', 'left')
|
||
->groupBy('students.id, student_class.class_section_id, enrollments.enrollment_status, enrollments.admission_status')
|
||
->findAll();
|
||
}
|
||
*/
|
||
|
||
/**
|
||
* Get student and teacher information based on class_section_id.
|
||
*
|
||
* @param int $classSectionId
|
||
* @param string|null $semester Deprecated/ignored (assignments now tracked per school year only).
|
||
* @param string|null $schoolYear
|
||
* @return array
|
||
*/
|
||
public function getStudentInfoByClassSectionId($classSectionId, ?string $semester = null, ?string $schoolYear = null): array
|
||
{
|
||
$classSectionId = (int) $classSectionId;
|
||
if ($classSectionId <= 0) {
|
||
return [];
|
||
}
|
||
|
||
// Start from this model's table (likely 'students')
|
||
$builder = $this->builder();
|
||
|
||
$builder
|
||
->select('students.id AS student_id, students.school_id, students.firstname, students.lastname, sc.class_section_id')
|
||
->join('student_class sc', 'sc.student_id = students.id', 'inner')
|
||
->where('sc.class_section_id', $classSectionId)
|
||
->where('students.is_active', 1);
|
||
|
||
// Optional term scoping (safe no-ops if null)
|
||
if ($schoolYear !== null) {
|
||
$builder->where('sc.school_year', $schoolYear);
|
||
}
|
||
|
||
// Prevent accidental dupes if student_class has multiple rows
|
||
$builder->distinct();
|
||
$builder->orderBy('students.lastname', 'ASC')
|
||
->orderBy('students.firstname', 'ASC');
|
||
|
||
$results = $builder->get()->getResultArray();
|
||
|
||
// 🔹 Instead of saving, just add `updated_by` to each result row
|
||
$userId = (int) (session()->get('user_id') ?? 0);
|
||
if (!empty($results)) {
|
||
foreach ($results as &$row) {
|
||
$row['updated_by'] = $userId; // add actor id for use downstream
|
||
}
|
||
unset($row); // good practice to break ref
|
||
}
|
||
|
||
return $results ?: [];
|
||
}
|
||
|
||
|
||
// In your existing StudentModel
|
||
public function getStudentBasic(int $studentId): ?array
|
||
{
|
||
return $this->select('id, firstname, lastname, grade')->find($studentId);
|
||
}
|
||
|
||
/**
|
||
* Returns students with:
|
||
* - class/enrollment/admission info
|
||
* - primary parent (guardian) info as aliases the view expects:
|
||
* parent_firstname, parent_lastname, parent_phone, parent_email
|
||
* - emergency contact info as: emergency_name, emergency_relationship, emergency_phone
|
||
*
|
||
* Adjust table/column names to your schema where noted.
|
||
*/
|
||
/*
|
||
public function getStudentsWithClassAndEnrollment(): array
|
||
{
|
||
$db = $this->db;
|
||
|
||
// ---- Notes: adjust these names if your schema differs ----
|
||
// users table fields:
|
||
// users.first_name, users.last_name, users.email, users.phone
|
||
// students table may already have emergency_* columns; if not, enable the alt join below.
|
||
// class/enrollment sources: adjust joins to your real tables/fields.
|
||
// ----------------------------------------------------------
|
||
|
||
$builder = $db->table('students s');
|
||
|
||
// Base student fields used by the view/controller
|
||
$builder->select([
|
||
's.id',
|
||
's.firstname',
|
||
's.lastname',
|
||
's.is_new',
|
||
's.dob',
|
||
's.registration_date',
|
||
's.photo_consent',
|
||
// If your students table stores class_section_id directly; otherwise replace with a subquery/join
|
||
's.class_section_id',
|
||
// Optional emergency columns ON students table (comment out if you use separate table)
|
||
's.emergency_name',
|
||
's.emergency_relationship',
|
||
's.emergency_phone',
|
||
]);
|
||
|
||
// ---------- Enrollment / Admission status ----------
|
||
// Replace these with your real sources:
|
||
// Example 1: statuses live on students (already selected) – then alias directly:
|
||
// $builder->select('s.enrollment_status, s.admission_status');
|
||
|
||
// Example 2: join enrollment/admission tables:
|
||
// $builder->join('enrollments e', 'e.student_id = s.id AND e.is_current = 1', 'left');
|
||
// $builder->select('e.status AS enrollment_status');
|
||
// $builder->join('admissions a', 'a.student_id = s.id', 'left');
|
||
// $builder->select('a.status AS admission_status');
|
||
|
||
// If you actually store on students, uncomment this line and remove the example joins above:
|
||
$builder->select('s.enrollment_status, s.admission_status');
|
||
|
||
// ---------- Class Section readable name ----------
|
||
// If you already have a helper to resolve the name, the controller uses it.
|
||
// But it’s faster to produce a label here:
|
||
// Example: class_sections (id, name), classes (name) etc. Adjust join/concat as needed.
|
||
$builder->join('class_sections cs', 'cs.id = s.class_section_id', 'left');
|
||
$builder->select("COALESCE(cs.name, 'Class not Assigned') AS class_section");
|
||
|
||
// ---------- Family/Guardian (primary parent) ----------
|
||
// Link student -> family via family_students
|
||
$builder->join('family_students fs', 'fs.student_id = s.id', 'left');
|
||
// Join to primary guardian for that family
|
||
$builder->join('family_guardians fg', 'fg.family_id = fs.family_id AND fg.is_primary = 1', 'left');
|
||
// Join to users for the guardian details (ADJUST field names!)
|
||
$builder->join('users pu', 'pu.id = fg.user_id', 'left');
|
||
|
||
// Produce the aliases your view expects
|
||
$builder->select([
|
||
'pu.first_name AS parent_firstname',
|
||
'pu.last_name AS parent_lastname',
|
||
'pu.email AS parent_email',
|
||
'pu.phone AS parent_phone',
|
||
]);
|
||
|
||
// ---------- OPTIONAL: If emergency contact lives in a separate table ----------
|
||
// Comment OUT the s.emergency_* selections above and use this instead:
|
||
// $builder->join('student_emergency_contacts sec', 'sec.student_id = s.id AND sec.is_primary = 1', 'left');
|
||
// $builder->select([
|
||
// 'sec.name AS emergency_name',
|
||
// 'sec.relationship AS emergency_relationship',
|
||
// 'sec.phone AS emergency_phone',
|
||
// ]);
|
||
|
||
// If you need only currently active / current semester, apply where here:
|
||
// $builder->where('s.is_active', 1);
|
||
|
||
// Avoid duplicate rows (e.g., if multiple guardians slipped in)
|
||
$builder->groupBy('s.id');
|
||
|
||
// Optional: order
|
||
$builder->orderBy('s.lastname', 'ASC')->orderBy('s.firstname', 'ASC');
|
||
|
||
return $builder->get()->getResultArray();
|
||
}
|
||
*/
|
||
}
|