/**
* 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))) {