/**
* index action
* @param int $id
*/
public function index($id = 1)
{
$currentYear = Easol_SchoolConfiguration::getValue('CURRENT_SCHOOLYEAR');
$currentYear_default = Easol_SchoolConfiguration::setDefault('Year', $currentYear);
$query = "SELECT Student.StudentUSI, Student.FirstName, Student.LastSurname, GradeLevelType.Description, StudentCohortAssociation.CohortIdentifier from edfi.StudentSchoolAssociation\nINNER JOIN edfi.Student ON\n StudentSchoolAssociation.StudentUSI = Student.StudentUSI\nINNER JOIN edfi.GradeLevelDescriptor ON\n StudentSchoolAssociation.EntryGradeLevelDescriptorId = GradeLevelDescriptor.GradeLevelDescriptorId\nINNER JOIN edfi.GradeLevelType ON\n GradeLevelDescriptor.GradeLevelTypeId = GradeLevelType.GradeLevelTypeId\nLEFT JOIN edfi.StudentCohortAssociation ON\n StudentCohortAssociation.EducationOrganizationId = StudentSchoolAssociation.SchoolId AND StudentCohortAssociation.StudentUSI = StudentSchoolAssociation.StudentUSI\nWHERE\n StudentSchoolAssociation.SchoolId = '" . Easol_Authentication::userdata('SchoolId') . "'\n\n ";
$this->render("index", ['query' => $query, 'colOrderBy' => ['GradeLevelType.Description', 'Student.FirstName', 'Student.LastSurname', 'Student.StudentUSI', 'StudentCohortAssociation.CohortIdentifier'], 'filter' => ['dataBind' => true, 'bindIndex' => ['GradeLevel' => ['glue' => 'and'], 'Year' => ['glue' => 'and'], 'Cohort' => ['glue' => 'and']], 'bindSort' => ['Sort'], 'queryWhere' => false, 'fields' => ['Year' => ['range' => ['type' => 'dynamic', 'start' => $currentYear, 'end' => date('Y'), 'increament' => 1], 'searchColumn' => 'SchoolYear', 'searchColumnType' => 'int', 'queryBuilderColumn' => 'StudentSchoolAssociation.SchoolYear', 'default' => $this->input->get('filter[Year]') == null ? $currentYear_default : $this->input->get('filter[Year]'), 'label' => 'Year', 'type' => 'dropdown', 'bindDatabase' => true, 'prompt' => 'All Years'], 'GradeLevel' => ['query' => $this->db->query("SELECT * FROM edfi.GradeLevelType"), 'searchColumn' => 'GradeLevelTypeId', 'textColumn' => 'Description', 'indexColumn' => 'Description', 'queryBuilderColumn' => 'GradeLevelType.Description', 'label' => 'Grade Level', 'type' => 'dropdown', 'bindDatabase' => true, 'prompt' => 'All Grade Levels', 'default' => $this->input->get('filter[GradeLevel]') === false ? "" : $this->input->get('filter[GradeLevel]')], 'Cohort' => ['query' => $this->db->query("SELECT DISTINCT edfi.StudentCohortAssociation.CohortIdentifier FROM edfi.StudentCohortAssociation"), 'searchColumn' => 'CohortTypeId', 'textColumn' => 'CohortIdentifier', 'indexColumn' => 'CohortIdentifier', 'queryBuilderColumn' => 'StudentCohortAssociation.CohortIdentifier', 'label' => 'Cohort', 'type' => 'dropdown', 'bindDatabase' => false, 'prompt' => 'All Cohorts', 'default' => $this->input->get('filter[Cohort]')], 'Result' => ['range' => ['type' => 'set', 'set' => [10, 25, 50, 100, 200, 500]], 'default' => $this->input->get('filter[Result]') === false ? 3 : $this->input->get('filter[Result]'), 'label' => 'Results', 'type' => 'dropdown', 'bindDatabase' => false, 'fieldType' => 'pageSize'], 'Sort' => ['label' => 'Sort Column', 'type' => 'dataSort', 'bindDatabase' => true, 'fieldType' => 'dataSort', 'display' => 'false', 'columns' => ['FirstName' => 'Name', 'GradeLevelType.Description' => 'Grade Level', 'StudentCohortAssociation.CohortIdentifier' => 'Cohort'], 'defaultColumn' => $this->input->get('filter[Sort][column]'), 'sortTypes' => ['ASC' => 'Ascending', 'DESC' => 'Descending'], 'defaultSortType' => !$this->input->get('filter[Sort][type]') ? "ASC" : $this->input->get('filter[Sort][type]'), 'sortTypeLabel' => 'Sort Type']]], 'pagination' => ['pageSize' => EASOL_PAGINATION_PAGE_SIZE, 'currentPage' => $id, 'url' => 'student/index/@pageNo']]);
}
/**
* @return mixed
*/
public function getAttendance()
{
$query = "SELECT Section.ClassPeriodName, Section.LocalCourseCode, Section.id, Section.UniqueSectionCode, CodeValue, COUNT(*) as Days\nFROM edfi.StudentSectionAttendanceEvent\nINNER JOIN edfi.[Section] ON\n [Section].ClassPeriodName = StudentSectionAttendanceEvent.ClassPeriodName AND\n [Section].ClassroomIdentificationCode = StudentSectionAttendanceEvent.ClassroomIdentificationCode AND\n [Section].LocalCourseCode = StudentSectionAttendanceEvent.LocalCourseCode AND\n [Section].TermTypeId = StudentSectionAttendanceEvent.TermTypeId AND\n [Section].SchoolYear = StudentSectionAttendanceEvent.SchoolYear AND\n [Section].SchoolId = StudentSectionAttendanceEvent.SchoolId\nINNER JOIN edfi.AttendanceEventCategoryDescriptor ON AttendanceEventCategoryDescriptor.AttendanceEventCategoryDescriptorId = StudentSectionAttendanceEvent.AttendanceEventCategoryDescriptorId\nINNER JOIN edfi.AttendanceEventCategoryType ON AttendanceEventCategoryType.AttendanceEventCategoryTypeId = AttendanceEventCategoryDescriptor.AttendanceEventCategoryTypeId\nWHERE StudentUSI = [StudentUSI]\nAND (CodeValue = 'Excused Absence' OR CodeValue='Unexcused Absence') AND StudentSectionAttendanceEvent.TermTypeId = [TermTypeId] AND StudentSectionAttendanceEvent.SchoolYear = [SchoolYear]\nGROUP BY Section.ClassPeriodName, Section.LocalCourseCode, Section.id, Section.UniqueSectionCode, AttendanceEventCategoryType.CodeValue\nUNION\nSELECT Section.ClassPeriodName, Section.LocalCourseCode, Section.id, Section.UniqueSectionCode, CodeValue, COUNT(*) as Tardy\nFROM edfi.StudentSectionAttendanceEvent\nINNER JOIN edfi.[Section] ON\n [Section].ClassPeriodName = StudentSectionAttendanceEvent.ClassPeriodName AND\n [Section].ClassroomIdentificationCode = StudentSectionAttendanceEvent.ClassroomIdentificationCode AND\n [Section].LocalCourseCode = StudentSectionAttendanceEvent.LocalCourseCode AND\n [Section].TermTypeId = StudentSectionAttendanceEvent.TermTypeId AND\n [Section].SchoolYear = StudentSectionAttendanceEvent.SchoolYear AND\n [Section].SchoolId = StudentSectionAttendanceEvent.SchoolId\nINNER JOIN edfi.AttendanceEventCategoryDescriptor ON AttendanceEventCategoryDescriptor.AttendanceEventCategoryDescriptorId = StudentSectionAttendanceEvent.AttendanceEventCategoryDescriptorId\nINNER JOIN edfi.AttendanceEventCategoryType ON AttendanceEventCategoryType.AttendanceEventCategoryTypeId = AttendanceEventCategoryDescriptor.AttendanceEventCategoryTypeId\nWHERE StudentUSI = [StudentUSI]\nAND (CodeValue = 'Tardy') AND StudentSectionAttendanceEvent.TermTypeId = [TermTypeId] AND StudentSectionAttendanceEvent.SchoolYear = [SchoolYear]\nGROUP BY Section.ClassPeriodName, Section.LocalCourseCode, Section.id, Section.UniqueSectionCode, AttendanceEventCategoryType.CodeValue\nUNION\nSELECT Section.ClassPeriodName, Section.LocalCourseCode, Section.id, Section.UniqueSectionCode, CodeValue, COUNT(*) as Present\nFROM edfi.StudentSectionAttendanceEvent\nINNER JOIN edfi.[Section] ON\n [Section].ClassPeriodName = StudentSectionAttendanceEvent.ClassPeriodName AND\n [Section].ClassroomIdentificationCode = StudentSectionAttendanceEvent.ClassroomIdentificationCode AND\n [Section].LocalCourseCode = StudentSectionAttendanceEvent.LocalCourseCode AND\n [Section].TermTypeId = StudentSectionAttendanceEvent.TermTypeId AND\n [Section].SchoolYear = StudentSectionAttendanceEvent.SchoolYear AND\n [Section].SchoolId = StudentSectionAttendanceEvent.SchoolId\nINNER JOIN edfi.AttendanceEventCategoryDescriptor ON AttendanceEventCategoryDescriptor.AttendanceEventCategoryDescriptorId = StudentSectionAttendanceEvent.AttendanceEventCategoryDescriptorId\nINNER JOIN edfi.AttendanceEventCategoryType ON AttendanceEventCategoryType.AttendanceEventCategoryTypeId = AttendanceEventCategoryDescriptor.AttendanceEventCategoryTypeId\nWHERE StudentUSI = [StudentUSI]\nAND (CodeValue = 'In Attendance') AND StudentSectionAttendanceEvent.TermTypeId = [TermTypeId] AND StudentSectionAttendanceEvent.SchoolYear = [SchoolYear]\nGROUP BY Section.ClassPeriodName, Section.LocalCourseCode, Section.id, Section.UniqueSectionCode, AttendanceEventCategoryType.CodeValue";
$termId = Easol_SchoolConfiguration::getValue('CURRENT_TERMID');
$schoolYear = Easol_SchoolConfiguration::getValue('CURRENT_SCHOOLYEAR');
if ($termId && $schoolYear) {
$query = str_replace(['[StudentUSI]', '[TermTypeId]', '[SchoolYear]'], [$this->StudentUSI, $termId, $schoolYear], $query);
return $this->db->query($query, [$this->StudentUSI])->result();
}
return [];
}
public function index()
{
$data = array();
$data['filters'] = $_GET;
$data['currentYear'] = Easol_SchoolConfiguration::getValue('CURRENT_SCHOOLYEAR');
$data['currentYear_default'] = isset($data['filters']['year']) ? $data['filters']['year'] : Easol_SchoolConfiguration::setDefault('Year', $data['currentYear']);
$data['currentTerm'] = Easol_SchoolConfiguration::getValue('CURRENT_TERMID');
$data['currentTerm_default'] = isset($data['filters']['term']) ? $data['filters']['term'] : Easol_SchoolConfiguration::setDefault('Term', $data['currentTerm']);
$data['userCanFilter'] = Easol_SchoolConfiguration::canFilterByEducator();
// define required filters
$where = array();
$where['edfi.Grade.SchoolId'] = Easol_Authentication::userdata('SchoolId');
// define optional filters
$lookFor = array('educator' => 'edfi.StaffSectionAssociation.StaffUSI');
// If it's educator who is logged in, we force change Where param
if (!$data['userCanFilter']) {
$where[$lookFor['educator']] = Easol_Authentication::userdata('StaffUSI');
}
$this->db->select("Grade.LocalCourseCode, Course.CourseTitle, Section.UniqueSectionCode, Section.id, Grade.ClassPeriodName, \n Staff.FirstName, Staff.LastSurname, TermType.CodeValue, Grade.SchoolYear, \n sum(case when Grade.NumericGradeEarned >= 90 THEN 1 ELSE 0 END) as Numeric_A, \n sum(case when Grade.NumericGradeEarned >= 80 AND Grade.NumericGradeEarned < 90 THEN 1 ELSE 0 END) as Numeric_B,\n sum(case when Grade.NumericGradeEarned >= 70 AND Grade.NumericGradeEarned < 80 THEN 1 ELSE 0 END) as Numeric_C,\n sum(case when Grade.NumericGradeEarned >= 60 AND Grade.NumericGradeEarned < 70 THEN 1 ELSE 0 END) as Numeric_D,\n sum(case when Grade.NumericGradeEarned < 60 THEN 1 ELSE 0 END) as Numeric_F,\n sum(case when LEFT(Grade.LetterGradeEarned, 1) = 'A' THEN 1 ELSE 0 END) as Letter_A,\n sum(case when LEFT(Grade.LetterGradeEarned, 1) = 'B' THEN 1 ELSE 0 END) as Letter_B,\n sum(case when LEFT(Grade.LetterGradeEarned, 1) = 'C' THEN 1 ELSE 0 END) as Letter_C,\n sum(case when LEFT(Grade.LetterGradeEarned, 1) = 'D' THEN 1 ELSE 0 END) as Letter_D,\n sum(case when LEFT(Grade.LetterGradeEarned, 1) = 'F' THEN 1 ELSE 0 END) as Letter_F, \n count(*) as StudentCount");
$this->db->from('edfi.Grade');
$this->db->join('edfi.GradingPeriod', 'GradingPeriod.EducationOrganizationId = Grade.SchoolId AND GradingPeriod.BeginDate = Grade.BeginDate AND GradingPeriod.GradingPeriodDescriptorId = Grade.GradingPeriodDescriptorId', 'inner');
$this->db->join('edfi.StudentSectionAssociation', 'StudentSectionAssociation.StudentUSI = Grade.StudentUSI AND StudentSectionAssociation.SchoolId = Grade.SchoolId AND StudentSectionAssociation.LocalCourseCode = Grade.LocalCourseCode AND StudentSectionAssociation.TermTypeId = Grade.TermTypeId AND StudentSectionAssociation.SchoolYear = Grade.SchoolYear AND StudentSectionAssociation.TermTypeId = Grade.TermTypeId AND StudentSectionAssociation.ClassroomIdentificationCode = Grade.ClassroomIdentificationCode AND StudentSectionAssociation.ClassPeriodName = Grade.ClassPeriodName', 'inner');
$this->db->join('edfi.Section', 'Section.LocalCourseCode = StudentSectionAssociation.LocalCourseCode AND Section.SchoolYear = StudentSectionAssociation.SchoolYear AND Section.TermTypeId = StudentSectionAssociation.TermTypeId AND Section.SchoolId = StudentSectionAssociation.SchoolId AND Section.ClassPeriodName = StudentSectionAssociation.ClassPeriodName AND Section.ClassroomIdentificationCode = StudentSectionAssociation.ClassroomIdentificationCode', 'inner');
$this->db->join('edfi.StaffSectionAssociation', 'StaffSectionAssociation.SchoolId = Grade.SchoolId AND StaffSectionAssociation.LocalCourseCode = Grade.LocalCourseCode AND StaffSectionAssociation.TermTypeId = Grade.TermTypeId AND StaffSectionAssociation.SchoolYear = Grade.SchoolYear AND StaffSectionAssociation.TermTypeId = Grade.TermTypeId AND StaffSectionAssociation.ClassroomIdentificationCode = Grade.ClassroomIdentificationCode AND StaffSectionAssociation.ClassPeriodName = Grade.ClassPeriodName', 'inner');
$this->db->join('edfi.Staff', 'Staff.StaffUSI = StaffSectionAssociation.StaffUSI', 'inner');
$this->db->join('edfi.Course', 'edfi.Course.EducationOrganizationId = edfi.Grade.SchoolId AND edfi.Course.CourseCode = edfi.Grade.LocalCourseCode', 'inner');
$this->db->join('edfi.TermType', 'edfi.TermType.TermTypeId = edfi.Grade.TermTypeId', 'inner');
$this->db->group_by('Grade.LocalCourseCode,Course.CourseTitle, Section.UniqueSectionCode, Section.id,Grade.ClassPeriodName,TermType.CodeValue,Grade.SchoolYear,Staff.FirstName,Staff.LastSurname');
$this->db->order_by('Grade.LocalCourseCode , Grade.SchoolYear');
$data['results'] = $this->db->where($where)->get()->result();
// exit(print_r($this->db->last_query(), true));
foreach ($data['results'] as $k => $v) {
list($pCode, $pName) = explode(' - ', $v->ClassPeriodName);
$data['results'][$k]->Period = $pCode;
$data['results'][$k]->Educator = $v->FirstName . ' ' . $v->LastSurname;
}
$sql = "SELECT TermTypeId, CodeValue FROM edfi.TermType";
$data['terms'] = $this->db->query($sql)->result();
$data['years'] = range($data['currentYear'], date('Y'));
$sql = "SELECT CourseCode, CourseTitle FROM edfi.Course ORDER BY CourseTitle";
$data['courses'] = $this->db->query($sql)->result();
/* $sql = "SELECT * FROM edfi.GradeLevelType";
$data['gradelevels'] = $this->db->query($sql)->result();*/
$sql = "SELECT\n edfi.Staff.StaffUSI,\n CONCAT (edfi.Staff.FirstName,' ',\n edfi.Staff.LastSurname) as FullName\n FROM edfi.Staff\n LEFT JOIN edfi.StaffSchoolAssociation\n ON edfi.StaffSchoolAssociation.StaffUSI=edfi.Staff.StaffUSI\n ORDER By FirstName, LastSurname\n ";
$data['educators'] = $this->db->query($sql)->result();
$this->render("index", ['data' => $data]);
}
/**
* index action
*/
public function index()
{
$data = array();
$data['currentYear'] = Easol_SchoolConfiguration::getValue('CURRENT_SCHOOLYEAR');
$query = "(SELECT Student.StudentUSI, Student.FirstName, Student.LastSurname, GradeLevelType.CodeValue +'' as GradeLevel,\n GradeLevelType.GradeLevelTypeId, AttendanceEventCategoryType.CodeValue, COUNT(*) as Days,\n StudentSchoolAttendanceEvent.SchoolYear \n FROM edfi.StudentSchoolAttendanceEvent \n INNER JOIN edfi.AttendanceEventCategoryDescriptor ON AttendanceEventCategoryDescriptor.AttendanceEventCategoryDescriptorId = StudentSchoolAttendanceEvent.AttendanceEventCategoryDescriptorId\n INNER JOIN edfi.AttendanceEventCategoryType ON AttendanceEventCategoryType.AttendanceEventCategoryTypeId = AttendanceEventCategoryDescriptor.AttendanceEventCategoryTypeId\n INNER JOIN edfi.Student ON Student.StudentUSI = StudentSchoolAttendanceEvent.StudentUSI\n INNER JOIN edfi.StudentSchoolAssociation ON StudentSchoolAssociation.StudentUSI = StudentSchoolAttendanceEvent.StudentUSI AND StudentSchoolAssociation.SchoolId = StudentSchoolAttendanceEvent.SchoolId AND StudentSchoolAssociation.SchoolYear = StudentSchoolAttendanceEvent.SchoolYear\n INNER JOIN edfi.GradeLevelDescriptor ON GradeLevelDescriptor.GradeLevelDescriptorId = StudentSchoolAssociation.EntryGradeLevelDescriptorId\n INNER JOIN edfi.GradeLevelType ON GradeLevelType.GradeLevelTypeId = GradeLevelDescriptor.GradeLevelTypeId \n WHERE (AttendanceEventCategoryType.CodeValue IN ('In Attendance', 'Excused Absence', 'Unexcused Absence')) AND StudentSchoolAttendanceEvent.SchoolId = " . Easol_Authentication::userdata('SchoolId') . " \n GROUP BY Student.StudentUSI, Student.FirstName, Student.LastSurname, AttendanceEventCategoryType.CodeValue, GradeLevelType.CodeValue, StudentSchoolAttendanceEvent.SchoolYear,GradeLevelType.GradeLevelTypeId)\n ORDER BY StudentUSI";
$data['results'] = $this->db->query($query)->result();
foreach ($data['results'] as $k => $v) {
$data['results'][$v->StudentUSI][$v->SchoolYear]['StudentUSI'] = $v->StudentUSI;
$data['results'][$v->StudentUSI][$v->SchoolYear]['Name'] = $v->FirstName . ' ' . $v->LastSurname;
$data['results'][$v->StudentUSI][$v->SchoolYear]['GradeLevel'] = $v->GradeLevel;
$data['results'][$v->StudentUSI][$v->SchoolYear][$v->CodeValue] = $v->Days;
unset($data['results'][$k]);
}
$sql = "SELECT CodeValue FROM edfi.GradeLevelType";
$data['gradelevels'] = $this->db->query($sql)->result();
$data['years'] = range($data['currentYear'], date('Y'));
$this->render("index", ['data' => $data]);
}
/**
* index action
*/
public function index()
{
$currentYear = Easol_SchoolConfiguration::getValue('CURRENT_SCHOOLYEAR');
$currentYear_default = Easol_SchoolConfiguration::setDefault('Year', $currentYear);
$query = "SELECT\n AssessmentTitle,\n edfi.StudentAssessmentScoreResult.AcademicSubjectDescriptorId,\n edfi.StudentAssessmentScoreResult.AssessedGradeLevelDescriptorId,\n Version, AdministrationDate,\n edfi.AcademicSubjectType.CodeValue +'' as Subject,\n edfi.GradeLevelType.CodeValue +'' as Grade,\n\t\t\tAVG(CAST(StudentAssessmentScoreResult.Result as FLOAT)) as AverageResult,\n\t\t\tCOUNT(*) as StudentCount\n\t\t\tFROM edfi.StudentAssessmentScoreResult\n\t\t\tJOIN edfi.AcademicSubjectDescriptor ON edfi.AcademicSubjectDescriptor.AcademicSubjectDescriptorId = edfi.StudentAssessmentScoreResult\n\t\t\t.AcademicSubjectDescriptorId\n\t\t\tJOIN edfi.AcademicSubjectType ON edfi.AcademicSubjectType.AcademicSubjectTypeId = edfi.AcademicSubjectDescriptor\n\t\t\t.AcademicSubjectTypeId\n\n\t\t\tJOIN edfi.GradeLevelDescriptor ON edfi.GradeLevelDescriptor.GradeLevelDescriptorId = edfi.StudentAssessmentScoreResult\n\t\t\t.AssessedGradeLevelDescriptorId\n\t\t\tJOIN edfi.GradeLevelType ON edfi.GradeLevelType.GradeLevelTypeId = edfi.GradeLevelDescriptor\n\t\t\t.GradeLevelTypeId\n\n\t\t\tWHERE ISNUMERIC(StudentAssessmentScoreResult.Result) = 1\n\t\t\tGROUP BY AssessmentTitle,Version,AdministrationDate, edfi.StudentAssessmentScoreResult.AcademicSubjectDescriptorId, edfi.StudentAssessmentScoreResult.AssessedGradeLevelDescriptorId, edfi.AcademicSubjectType.CodeValue, edfi.GradeLevelType.CodeValue";
$query = $this->db->query($query);
$data = ['result' => $query->result()];
$filter = ['year' => ["" => "All Years"], 'subject' => ["" => "All Subjects"], 'grade' => ["" => "All Grade Levels"]];
foreach ($query->result() as $row) {
$filter['year'][date('Y', strtotime($row->AdministrationDate))] = date('Y', strtotime($row->AdministrationDate));
$filter['subject'][$row->Subject] = $row->Subject;
}
$query = $this->db->query("SELECT * FROM edfi.GradeLevelType ORDER BY GradeLevelTypeId ASC");
foreach ($query->result() as $row) {
$filter['grade'][$row->CodeValue] = $row->CodeValue;
}
$data['filter'] = $filter;
$this->render('index', $data);
}
public function index()
{
$data = array();
$data['filters'] = $_GET;
$data['currentYear'] = Easol_SchoolConfiguration::getValue('CURRENT_SCHOOLYEAR');
$data['currentYear_default'] = (isset($data['filters']['year']) and !empty($data['filters']['year'])) ? $data['filters']['year'] : Easol_SchoolConfiguration::setDefault('Year', $data['currentYear']);
$data['currentTerm'] = Easol_SchoolConfiguration::getValue('CURRENT_TERMID');
$data['currentTerm_default'] = (isset($data['filters']['term']) and !empty($data['filters']['term'])) ? $data['filters']['term'] : Easol_SchoolConfiguration::setDefault('Term', $data['currentTerm']);
$data['userCanFilter'] = Easol_SchoolConfiguration::canFilterByEducator();
// define required filters
$where = array('edfi.Grade.SchoolId' => Easol_Authentication::userdata('SchoolId'));
// define optional filters
$lookFor = array('educator' => 'edfi.StaffSectionAssociation.StaffUSI');
// If it's educator who is logged in, we force change Where param
if (!$data['userCanFilter']) {
$where[$lookFor['educator']] = Easol_Authentication::userdata('StaffUSI');
}
$this->db->select("Grade.LocalCourseCode, Section.UniqueSectionCode, Section.id, Section.SchoolYear, Grade.ClassPeriodName, Staff.FirstName, Staff.LastSurname, TermType.CodeValue");
$this->db->from('edfi.Grade');
$this->db->join('edfi.School', 'School.SchoolId = Grade.SchoolId', 'inner');
$this->db->join('edfi.GradingPeriod', 'GradingPeriod.EducationOrganizationId = Grade.SchoolId AND GradingPeriod.BeginDate = Grade.BeginDate AND GradingPeriod.GradingPeriodDescriptorId = Grade.GradingPeriodDescriptorId', 'inner');
$this->db->join('edfi.StudentSectionAssociation', 'StudentSectionAssociation.StudentUSI = Grade.StudentUSI AND StudentSectionAssociation.SchoolId = Grade.SchoolId AND StudentSectionAssociation.LocalCourseCode = Grade.LocalCourseCode AND StudentSectionAssociation.TermTypeId = Grade.TermTypeId AND StudentSectionAssociation.SchoolYear = Grade.SchoolYear AND StudentSectionAssociation.TermTypeId = Grade.TermTypeId AND StudentSectionAssociation.ClassroomIdentificationCode = Grade.ClassroomIdentificationCode AND StudentSectionAssociation.ClassPeriodName = Grade.ClassPeriodName', 'inner');
$this->db->join('edfi.Section', 'Section.LocalCourseCode = StudentSectionAssociation.LocalCourseCode AND Section.SchoolYear = StudentSectionAssociation.SchoolYear AND Section.TermTypeId = StudentSectionAssociation.TermTypeId AND Section.SchoolId = StudentSectionAssociation.SchoolId AND Section.ClassPeriodName = StudentSectionAssociation.ClassPeriodName AND Section.ClassroomIdentificationCode = StudentSectionAssociation.ClassroomIdentificationCode', 'inner');
$this->db->join('edfi.StaffSectionAssociation', 'StaffSectionAssociation.SchoolId = Grade.SchoolId AND StaffSectionAssociation.LocalCourseCode = Grade.LocalCourseCode AND StaffSectionAssociation.TermTypeId = Grade.TermTypeId AND StaffSectionAssociation.SchoolYear = Grade.SchoolYear AND StaffSectionAssociation.TermTypeId = Grade.TermTypeId AND StaffSectionAssociation.ClassroomIdentificationCode = Grade.ClassroomIdentificationCode AND StaffSectionAssociation.ClassPeriodName = Grade.ClassPeriodName', 'inner');
$this->db->join('edfi.Staff', 'Staff.StaffUSI = StaffSectionAssociation.StaffUSI', 'inner');
$this->db->join('edfi.Course', 'edfi.Course.EducationOrganizationId = edfi.Grade.SchoolId AND edfi.Course.CourseCode = edfi.Grade.LocalCourseCode', 'inner');
$this->db->join('edfi.TermType', 'edfi.TermType.TermTypeId = edfi.Grade.TermTypeId', 'inner');
$this->db->order_by('Grade.LocalCourseCode');
$data['results'] = $this->db->distinct()->where($where)->get()->result();
$meeting_times = array();
$sections = array();
if (!empty($data['results'])) {
foreach ($data['results'] as $k => $v) {
$data['results'][$v->id] = $v;
list($pCode, $pName) = explode(' - ', $v->ClassPeriodName);
$data['results'][$v->id]->Period = $pCode;
$data['results'][$v->id]->Educator = $v->FirstName . ' ' . $v->LastSurname;
unset($data['results'][$k]);
$sections[] = $v->id;
// get the section datetime intervals
$urldates = '';
$SchoolId = Easol_Authentication::userdata('SchoolId');
$ClassPeriodName = $v->ClassPeriodName;
$this->db->select("BellSchedule.date, BellScheduleMeetingTime.starttime, BellScheduleMeetingTime.endtime");
$this->db->from("edfi.BellSchedule");
$this->db->join('edfi.BellScheduleMeetingTime', 'BellScheduleMeetingTime.date = BellSchedule.date');
$this->db->where("BellSchedule.SchoolId = '{$SchoolId}' AND BellScheduleMeetingTime.ClassPeriodName = '{$ClassPeriodName}'");
$intervals = $this->db->get()->result();
if (!empty($intervals)) {
foreach ($intervals as $key => $value) {
$urldates .= '&date_begin[]=' . $value->date . 'T' . $value->starttime . '&date_end[]=' . $value->date . 'T' . $value->endtime;
}
}
$meeting_times[$v->id] = $urldates;
}
}
if (!empty($sections)) {
$this->db->select("EmailLookup.HashedEmail, Section.UniqueSectionCode, Section.id");
$this->db->from("edfi.Section");
$this->db->join("edfi.StudentSectionAssociation", "StudentSectionAssociation.SchoolId = Section.SchoolId AND \n StudentSectionAssociation.ClassPeriodName = Section.ClassPeriodName AND \n StudentSectionAssociation.ClassroomIdentificationCode = Section.ClassroomIdentificationCode AND \n StudentSectionAssociation.LocalCourseCode = Section.LocalCourseCode AND \n StudentSectionAssociation.SchoolYear = Section.SchoolYear");
$this->db->join("edfi.StudentElectronicMail", "StudentElectronicMail.StudentUSI = StudentSectionAssociation.StudentUSI");
$this->db->join('easol.EmailLookup', 'EmailLookup.email = StudentElectronicMail.ElectronicMailAddress');
$this->db->where("StudentElectronicMail.PrimaryEmailAddressIndicator", "1");
$this->db->where_in("Section.id", $sections);
// sort the, hashed, student emails by section.
$students = $this->db->distinct()->get()->result();
if (!empty($students)) {
foreach ($students as $key => $value) {
$data['results'][$value->id]->students[] = $value->HashedEmail;
}
}
if (!empty($data['results'])) {
foreach ($data['results'] as $section => $obj) {
// define the number of student records for the section.
$data['results'][$section]->StudentCount = count($obj->students) ? count($obj->students) : 0;
// get the sites api data for the section's students
$api_students = '';
if (isset($obj->students) and !empty($obj->students)) {
foreach ($obj->students as $key => $HashedEmail) {
$api_students .= $HashedEmail . ',';
}
}
if (isset($meeting_times[$section]) and !empty($meeting_times[$section])) {
$query = http_build_query(array('org_api_key' => $this->api_key, 'org_secret_key' => $this->api_pass, 'type' => 'detail', 'usernames' => $api_students));
$site = $this->api_url . 'sites?' . $query . $meeting_times[$section];
$response = json_decode(file_get_contents($site, true));
$times = array();
if (!empty($response->results)) {
foreach ($response->results as $student) {
foreach ($student->site_visits as $key => $site) {
$times[] = $site->total_time;
}
}
}
$data['results'][$section]->Average = !empty($times) ? gmdate('H:i:s', array_sum($times) / $data['results'][$section]->StudentCount) : 0;
}
}
}
}
$sql = "SELECT TermTypeId, CodeValue FROM edfi.TermType";
$data['terms'] = $this->db->query($sql)->result();
$data['years'] = range($data['currentYear'], date('Y'));
$sql = "SELECT CourseCode, CourseTitle FROM edfi.Course ORDER BY CourseTitle";
$data['courses'] = $this->db->query($sql)->result();
$sql = "SELECT\n edfi.Staff.StaffUSI,\n CONCAT (edfi.Staff.FirstName,' ',\n edfi.Staff.LastSurname) as FullName\n FROM edfi.Staff\n LEFT JOIN edfi.StaffSchoolAssociation\n ON edfi.StaffSchoolAssociation.StaffUSI=edfi.Staff.StaffUSI\n ORDER By FirstName, LastSurname\n ";
$data['educators'] = $this->db->query($sql)->result();
$this->render("index", ['data' => $data]);
}