Exemple #1
0
 /**
  * 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']]);
 }
Exemple #2
0
 /**
  * @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 [];
 }
Exemple #3
0
 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]);
 }
Exemple #4
0
 /**
  * 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]);
 }
Exemple #5
0
 /**
  * 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);
 }
Exemple #6
0
 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]);
 }