/** * Get completion summary info for a course * * Parameters - $departmentid = int; * $courseid = int; * * Return array(); * */ public static function get_course_summary_info($departmentid, $courseid = 0, $showsuspended) { global $DB; // Create a temporary table to hold the userids. $temptablename = 'tmp_csum_users_' . time(); $dbman = $DB->get_manager(); // Define table user to be created. $table = new xmldb_table($temptablename); $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); $table->add_field('userid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null); $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); $dbman->create_temp_table($table); // Populate it. $alldepartments = company::get_all_subdepartments($departmentid); if (count($alldepartments) > 0) { // Deal with suspended or not. if (empty($showsuspended)) { $suspendedsql = " AND suspended = 0 "; } else { $suspendedsql = ""; } $tempcreatesql = "INSERT INTO {" . $temptablename . "} (userid) SELECT userid from {company_users}\n WHERE departmentid IN (" . implode(',', array_keys($alldepartments)) . ") {$suspendedsql}"; } else { $tempcreatesql = ""; } $DB->execute($tempcreatesql); // All or one course? $courses = array(); if (!empty($courseid)) { $courses[$courseid] = new stdclass(); $courses[$courseid]->id = $courseid; } else { $courses = company::get_recursive_department_courses($departmentid); } // Process them! $returnarr = array(); foreach ($courses as $course) { $courseobj = new stdclass(); $courseobj->id = $course->courseid; $courseobj->numenrolled = $DB->count_records_sql("SELECT COUNT(cc.id) FROM {course_completions} cc\n JOIN {" . $temptablename . "} tt ON (cc.userid = tt.userid)\n WHERE\n cc.course = :course", array('course' => $course->courseid)); $courseobj->numnotstarted = $DB->count_records_sql("SELECT COUNT(cc.id) FROM {course_completions} cc\n JOIN {" . $temptablename . "} tt ON (cc.userid = tt.userid)\n WHERE\n cc.course = :course AND\n cc.timestarted = 0", array('course' => $course->courseid)); $courseobj->numstarted = $DB->count_records_sql("SELECT COUNT(cc.id) FROM {course_completions} cc\n JOIN {" . $temptablename . "} tt ON (cc.userid = tt.userid)\n WHERE\n cc.course = :course AND\n cc.timestarted != 0", array('course' => $course->courseid)); $courseobj->numcompleted = $DB->count_records_sql("SELECT COUNT(cc.id) FROM {course_completions} cc\n JOIN {" . $temptablename . "} tt ON (cc.userid = tt.userid)\n WHERE\n cc.course = :course AND\n cc.timecompleted IS NOT NULL", array('course' => $course->courseid)); if (!($courseobj->coursename = $DB->get_field('course', 'fullname', array('id' => $course->courseid)))) { continue; } $returnarr[$course->courseid] = $courseobj; } return $returnarr; }
public function find_courses($search) { global $CFG, $DB, $SITE; require_once $CFG->dirroot . '/local/iomad/lib/company.php'; // By default wherecondition retrieves all courses except the deleted, not confirmed and guest. list($wherecondition, $params) = $this->search_sql($search, 'c'); $params['companyid'] = $this->companyid; $params['siteid'] = $SITE->id; $userdepartment = company::get_userlevel($this->user); if (!($companycourses = $DB->get_records('company_course', array('companyid' => $this->companyid), null, 'courseid'))) { $companysql = " AND 1=0"; } else { $companysql = " AND c.id in (" . implode(',', array_keys($companycourses)) . ") "; } $deptids = company::get_recursive_department_courses($userdepartment->id); $departmentcondition = ""; if (!empty($deptids)) { foreach ($deptids as $deptid) { if (empty($departmentcondition)) { $departmentcondition = " AND cc.courseid in (" . $deptid->courseid; } else { $departmentcondition .= "," . $deptid->courseid; } } $departmentcondition .= ") "; } $currentcourses = enrol_get_users_courses($this->user->id, true, null, 'visible DESC, sortorder ASC'); if (!empty($currentcourses)) { $currentcoursesql = "AND c.id not in (" . implode(',', array_keys($currentcourses)) . ")"; } else { $currentcoursesql = ""; } if ($licensecourses = $DB->get_records('iomad_courses', array('licensed' => 1), null, 'courseid')) { $licensesql = " AND c.id not in (" . implode(',', array_keys($licensecourses)) . ")"; } else { $licensesql = ""; } $fields = 'SELECT ' . $this->required_fields_sql('c'); $countfields = 'SELECT COUNT(1)'; $distinctfields = 'SELECT DISTINCT ' . $this->required_fields_sql('c'); $distinctcountfields = 'SELECT COUNT(DISTINCT c.id) '; $sql = " FROM {course} c,\n {company_course} cc\n WHERE cc.courseid = c.id\n AND {$wherecondition}\n {$companysql}\n {$departmentcondition}\n {$currentcoursesql}\n {$licensesql}"; // Deal with shared courses. if ($this->shared) { if ($this->licenses) { $sharedsql = " FROM {course} c\n INNER JOIN {iomad_courses} pc\n ON c.id=pc.courseid\n WHERE pc.shared=1\n AND pc.licensed != 1"; $partialsharedsql = " FROM {course} c\n WHERE c.id IN (SELECT pc.courseid from {iomad_courses} pc\n INNER JOIN {company_shared_courses} csc ON pc.courseid=csc.courseid\n where pc.shared=2 AND pc.licensed !=1 AND csc.companyid = :companyid)"; } else { $sharedsql = " FROM {course} c INNER JOIN {iomad_courses} pc ON c.id=pc.courseid WHERE pc.shared=1"; $partialsharedsql = " FROM {course} c\n WHERE c.id IN (SELECT pc.courseid from {iomad_courses} pc\n INNER JOIN {company_shared_courses} csc ON pc.courseid=csc.courseid\n where pc.shared=2 AND csc.companyid = :companyid)"; } } else { $sharedsql = " FROM {course} c WHERE 1 = 2"; $partialsharedsql = " FROM {course} c WHERE 1 = 2"; } $order = ' ORDER BY c.fullname ASC'; if (!$this->is_validating()) { $potentialmemberscount = $DB->count_records_sql($countfields . $sql, $params) + $DB->count_records_sql($countfields . $sharedsql, $params) + $DB->count_records_sql($countfields . $partialsharedsql, $params); if ($potentialmemberscount > company_course_selector_base::MAX_COURSES_PER_PAGE) { return $this->too_many_results($search, $potentialmemberscount); } } $availablecourses = $DB->get_records_sql($fields . $sql . $order, $params) + $DB->get_records_sql($fields . $sharedsql . $order, $params) + $DB->get_records_sql($fields . $partialsharedsql . $order, $params); if (empty($availablecourses)) { return array(); } if ($search) { $groupname = get_string('potcoursesmatching', 'block_iomad_company_admin', $search); } else { $groupname = get_string('potcourses', 'block_iomad_company_admin'); } return array($groupname => $availablecourses); }
$select->formid = 'choosedepartment'; if (empty($dodownload)) { echo html_writer::tag('div', $OUTPUT->render($select), array('id' => 'iomad_department_selector')); $fwselectoutput = html_writer::tag('div', $OUTPUT->render($select), array('id' => 'iomad_company_selector')); echo "</br>"; } // Get the SCORM data. if (!($scormmod = $DB->get_record('modules', array('name' => 'scorm')))) { if (empty($dodownload)) { echo "<h1>" . get_string('scormnotinstalled', 'local_report_scorm_overview') . "<h1>"; echo $OUTPUT->footer(); } die; } // Get the department courses. if ($departmentcourses = company::get_recursive_department_courses($departmentid)) { $dcourselist = ""; foreach ($departmentcourses as $departmentcourse) { if (!empty($dcourselist)) { $dcourselist .= "," . $departmentcourse->courseid; } else { $dcourselist = $departmentcourse->courseid; } } $departmentsql = " AND c.id in (" . $dcourselist . ") "; } else { $departmentsql = ""; } // Get the company course instances. $sql = "SELECT DISTINCT c.id, c.fullname from {course_modules} cm, {course} c WHERE\n cm.module = " . $scormmod->id . " AND c.id = cm.course {$departmentsql}"; if (!($courselist = $DB->get_records_sql($sql))) {