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]); }
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]); }