/** * Mark users as started if the config option is set * * @return void */ function completion_cron_mark_started() { global $CFG, $DB; if (debugging()) { mtrace('Marking users as started'); } if (!empty($CFG->progresstrackedroles)) { $roles = ' AND ra.roleid IN (' . $CFG->progresstrackedroles . ')'; } else { // This causes it to default to everyone (if there is no student role) $roles = ''; } /** * A quick explaination of this horrible looking query * * It's purpose is to locate all the active participants * of a course with course completion enabled. * * We also only want the users with no course_completions * record as this functions job is to create the missing * ones :) * * We want to record the user's enrolment start time for the * course. This gets tricky because there can be multiple * enrolment plugins active in a course, hence the possibility * of multiple records for each couse/user in the results */ $sql = "\n SELECT\n c.id AS course,\n u.id AS userid,\n crc.id AS completionid,\n ue.timestart AS timeenrolled,\n ue.timecreated\n FROM\n {user} u\n INNER JOIN\n {user_enrolments} ue\n ON ue.userid = u.id\n INNER JOIN\n {enrol} e\n ON e.id = ue.enrolid\n INNER JOIN\n {course} c\n ON c.id = e.courseid\n INNER JOIN\n {role_assignments} ra\n ON ra.userid = u.id\n LEFT JOIN\n {course_completions} crc\n ON crc.course = c.id\n AND crc.userid = u.id\n WHERE\n c.enablecompletion = 1\n AND crc.timeenrolled IS NULL\n AND ue.status = 0\n AND e.status = 0\n AND u.deleted = 0\n AND ue.timestart < ?\n AND (ue.timeend > ? OR ue.timeend = 0)\n {$roles}\n ORDER BY\n course,\n userid\n "; // Check if result is empty $now = time(); if (!($rs = $DB->get_recordset_sql($sql, array($now, $now, $now, $now)))) { return; } /** * An explaination of the following loop * * We are essentially doing a group by in the code here (as I can't find * a decent way of doing it in the sql). * * Since there can be multiple enrolment plugins for each course, we can have * multiple rows for each particpant in the query result. This isn't really * a problem until you combine it with the fact that the enrolment plugins * can save the enrol start time in either timestart or timeenrolled. * * The purpose of this loop is to find the earliest enrolment start time for * each participant in each course. */ $prev = null; while ($rs->valid() || $prev) { $current = $rs->current(); if (!isset($current->course)) { $current = false; } else { // Not all enrol plugins fill out timestart correctly, so use whichever // is non-zero $current->timeenrolled = max($current->timecreated, $current->timeenrolled); } // If we are at the last record, // or we aren't at the first and the record is for a diff user/course if ($prev && (!$rs->valid() || ($current->course != $prev->course || $current->userid != $prev->userid))) { $completion = new completion_completion(); $completion->userid = $prev->userid; $completion->course = $prev->course; $completion->timeenrolled = (string) $prev->timeenrolled; $completion->timestarted = 0; $completion->reaggregate = time(); if ($prev->completionid) { $completion->id = $prev->completionid; } $completion->mark_enrolled(); if (debugging()) { mtrace('Marked started user ' . $prev->userid . ' in course ' . $prev->course); } } elseif ($prev && $current) { // Use oldest timeenrolled $current->timeenrolled = min($current->timeenrolled, $prev->timeenrolled); } // Move current record to previous $prev = $current; // Move to next record $rs->next(); } $rs->close(); }
/** * Forces synchronisation of all enrolments with external database. * * @param progress_trace $trace * @param null|int $onecourse limit sync to one course only (used primarily in restore) * @return int 0 means success, 1 db connect failure, 2 db read failure */ public function sync_enrolments(progress_trace $trace, $onecourse = null) { global $CFG, $DB; // We do not create courses here intentionally because it requires full sync and is slow. if (!$this->get_config('dbtype') or !$this->get_config('remoteenroltable') or !$this->get_config('remotecoursefield') or !$this->get_config('remoteuserfield')) { $trace->output('User enrolment synchronisation skipped.'); $trace->finished(); return 0; } $trace->output('Starting user enrolment synchronisation...'); if (!($extdb = $this->db_init())) { $trace->output('Error while communicating with external enrolment database'); $trace->finished(); return 1; } // We may need a lot of memory here. core_php_time_limit::raise(); raise_memory_limit(MEMORY_HUGE); $table = $this->get_config('remoteenroltable'); $coursefield = trim($this->get_config('remotecoursefield')); $userfield = trim($this->get_config('remoteuserfield')); $rolefield = trim($this->get_config('remoterolefield')); $otheruserfield = trim($this->get_config('remoteotheruserfield')); $coursestatusfield = trim($this->get_config('remotecoursestatusfield')); $coursestatuscurrentfield = trim($this->get_config('remotecoursestatuscurrentfield')); $coursestatuscompletedfield = trim($this->get_config('remotecoursestatuscompletedfield')); $coursegradefield = trim($this->get_config('remotecoursegradefield')); $courseenroldatefield = trim($this->get_config('remotecourseenroldatefield')); $coursecompletiondatefield = trim($this->get_config('remotecoursecompletiondatefield')); // Lowercased versions - necessary because we normalise the resultset with array_change_key_case(). $coursefield_l = strtolower($coursefield); $userfield_l = strtolower($userfield); $rolefield_l = strtolower($rolefield); $otheruserfieldlower = strtolower($otheruserfield); $coursestatusfield_l = strtolower($coursestatusfield); $coursestatuscurrentfield_l = strtolower($coursestatuscurrentfield); $coursestatuscompletedfield_l = strtolower($coursestatuscompletedfield); $coursegradefield_l = strtolower($coursegradefield); $courseenroldatefield_l = strtolower($courseenroldatefield); $coursecompletiondatefield_l = strtolower($coursecompletiondatefield); $localrolefield = $this->get_config('localrolefield'); $localuserfield = $this->get_config('localuserfield'); $localcoursefield = $this->get_config('localcoursefield'); $unenrolaction = $this->get_config('unenrolaction'); $defaultrole = $this->get_config('defaultrole'); // Create roles mapping. $allroles = get_all_roles(); if (!isset($allroles[$defaultrole])) { $defaultrole = 0; } $roles = array(); foreach ($allroles as $role) { $roles[$role->{$localrolefield}] = $role->id; } if ($onecourse) { $sql = "SELECT c.id, c.visible, c.{$localcoursefield} AS mapping, c.shortname, e.id AS enrolid\n FROM {course} c\n LEFT JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'self')\n WHERE c.id = :id"; if (!($course = $DB->get_record_sql($sql, array('id' => $onecourse)))) { // Course does not exist, nothing to sync. return 0; } if (empty($course->mapping)) { // We can not map to this course, sorry. return 0; } if (empty($course->enrolid)) { $course->enrolid = $this->add_instance($course); } $existing = array($course->mapping => $course); // Feel free to unenrol everybody, no safety tricks here. $preventfullunenrol = false; // Course being restored are always hidden, we have to ignore the setting here. $ignorehidden = false; } else { // Get a list of courses to be synced that are in external table. $externalcourses = array(); $sql = $this->db_get_sql($table, array(), array($coursefield), true); if ($rs = $extdb->Execute($sql)) { if (!$rs->EOF) { while ($mapping = $rs->FetchRow()) { $mapping = reset($mapping); $mapping = $this->db_decode($mapping); if (empty($mapping)) { // invalid mapping continue; } $externalcourses[$mapping] = true; } } $rs->Close(); } else { $trace->output('Error reading data from the external enrolment table'); $extdb->Close(); return 2; } $preventfullunenrol = empty($externalcourses); if ($preventfullunenrol and $unenrolaction == ENROL_EXT_REMOVED_UNENROL) { $trace->output('Preventing unenrolment of all current users, because it might result in major data loss, there has to be at least one record in external enrol table, sorry.', 1); } // First find all existing courses with enrol instance. $existing = array(); $sql = "SELECT c.id, c.visible, c.{$localcoursefield} AS mapping, e.id AS enrolid, c.shortname\n FROM {course} c\n JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'self')"; $rs = $DB->get_recordset_sql($sql); // Watch out for idnumber duplicates. foreach ($rs as $course) { if (empty($course->mapping)) { continue; } $existing[$course->mapping] = $course; unset($externalcourses[$course->mapping]); } $rs->close(); // Add necessary enrol instances that are not present yet. $params = array(); $localnotempty = ""; if ($localcoursefield !== 'id') { $localnotempty = "AND c.{$localcoursefield} <> :lcfe"; $params['lcfe'] = ''; } $sql = "SELECT c.id, c.visible, c.{$localcoursefield} AS mapping, c.shortname\n FROM {course} c\n LEFT JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'self')\n WHERE e.id IS NULL {$localnotempty}"; $rs = $DB->get_recordset_sql($sql, $params); foreach ($rs as $course) { if (empty($course->mapping)) { continue; } if (!isset($externalcourses[$course->mapping])) { // Course not synced or duplicate. continue; } $course->enrolid = $this->add_instance($course); $existing[$course->mapping] = $course; unset($externalcourses[$course->mapping]); } $rs->close(); // Print list of missing courses. if ($externalcourses) { $list = implode(', ', array_keys($externalcourses)); $trace->output("error: following courses do not exist - {$list}", 1); unset($list); } // Free memory. unset($externalcourses); $ignorehidden = $this->get_config('ignorehiddencourses'); } // Sync user enrolments. $sqlfields = array($userfield); if ($rolefield) { $sqlfields[] = $rolefield; } if ($otheruserfield) { $sqlfields[] = $otheruserfield; } if ($coursestatusfield) { $sqlfields[] = $coursestatusfield; } if ($coursegradefield) { $sqlfields[] = $coursegradefield; } if ($courseenroldatefield) { $sqlfields[] = $courseenroldatefield; } if ($coursecompletiondatefield) { $sqlfields[] = $coursecompletiondatefield; } foreach ($existing as $course) { if ($ignorehidden and !$course->visible) { continue; } if (!($instance = $DB->get_record('enrol', array('id' => $course->enrolid)))) { continue; // Weird! } $context = context_course::instance($course->id); // Get current list of enrolled users with their roles. $currentroles = array(); $currentenrols = array(); $currentstatus = array(); $usermapping = array(); $completioninfo = array(); $sql = "SELECT u.{$localuserfield} AS mapping, u.id AS userid, ue.status, ra.roleid\n FROM {user} u\n JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.component = '' AND ra.itemid = :enrolid)\n LEFT JOIN {user_enrolments} ue ON (ue.userid = u.id AND ue.enrolid = ra.itemid)\n WHERE u.deleted = 0"; $params = array('enrolid' => $instance->id); if ($localuserfield === 'username') { $sql .= " AND u.mnethostid = :mnethostid"; $params['mnethostid'] = $CFG->mnet_localhost_id; } $rs = $DB->get_recordset_sql($sql, $params); foreach ($rs as $ue) { $currentroles[$ue->userid][$ue->roleid] = $ue->roleid; $usermapping[$ue->mapping] = $ue->userid; if (isset($ue->status)) { $currentenrols[$ue->userid][$ue->roleid] = $ue->roleid; $currentstatus[$ue->userid] = $ue->status; } } $rs->close(); // Get list of users that need to be enrolled and their roles. $requestedroles = array(); $requestedenrols = array(); $sql = $this->db_get_sql($table, array($coursefield => $course->mapping), $sqlfields); if ($rs = $extdb->Execute($sql)) { if (!$rs->EOF) { $usersearch = array('deleted' => 0); if ($localuserfield === 'username') { $usersearch['mnethostid'] = $CFG->mnet_localhost_id; } while ($fields = $rs->FetchRow()) { $fields = array_change_key_case($fields, CASE_LOWER); if (empty($fields[$userfield_l])) { $trace->output("error: skipping user without mandatory {$localuserfield} in course '{$course->mapping}'", 1); continue; } $mapping = $fields[$userfield_l]; if (!isset($usermapping[$mapping])) { $usersearch[$localuserfield] = $mapping; if (!($user = $DB->get_record('user', $usersearch, 'id', IGNORE_MULTIPLE))) { $trace->output("error: skipping unknown user {$localuserfield} '{$mapping}' in course '{$course->mapping}'", 1); continue; } $usermapping[$mapping] = $user->id; $userid = $user->id; } else { $userid = $usermapping[$mapping]; } if (empty($fields[$rolefield_l]) or !isset($roles[$fields[$rolefield_l]])) { if (!$defaultrole) { $trace->output("error: skipping user '{$userid}' in course '{$course->mapping}' - missing course and default role", 1); continue; } $roleid = $defaultrole; } else { $roleid = $roles[$fields[$rolefield_l]]; } $requestedroles[$userid][$roleid] = $roleid; if (empty($fields[$otheruserfieldlower])) { $requestedenrols[$userid][$roleid] = $roleid; } if (!empty($coursestatusfield_l)) { // Get status, grade, and completion date info only if the status field is defined. if (empty($fields[$coursestatusfield_l])) { // Assume that if the status field is empty, the course is still in progress. $completioninfo[$userid]['status'] = $coursestatuscurrentfield_l; } else { $completioninfo[$userid]['status'] = $fields[$coursestatusfield_l]; } $completioninfo[$userid]['courseid'] = $course->id; if (!empty($fields[$coursegradefield_l])) { $completioninfo[$userid]['grade'] = $fields[$coursegradefield_l]; } if (!empty($fields[$courseenroldatefield_l])) { $completioninfo[$userid]['enroldate'] = $fields[$courseenroldatefield_l]; } if (!empty($fields[$coursecompletiondatefield_l])) { $completioninfo[$userid]['completiondate'] = $fields[$coursecompletiondatefield_l]; } } } } $rs->Close(); } else { $trace->output("error: skipping course '{$course->mapping}' - could not match with external database", 1); continue; } unset($usermapping); // Enrol all users and sync roles. foreach ($requestedenrols as $userid => $userroles) { foreach ($userroles as $roleid) { if (empty($currentenrols[$userid])) { $this->enrol_user($instance, $userid, $roleid, 0, 0, ENROL_USER_ACTIVE); $currentroles[$userid][$roleid] = $roleid; $currentenrols[$userid][$roleid] = $roleid; $currentstatus[$userid] = ENROL_USER_ACTIVE; $trace->output("enrolling: {$userid} ==> {$course->shortname} as " . $allroles[$roleid]->shortname, 1); } } // Reenable enrolment when previously disable enrolment refreshed. if ($currentstatus[$userid] == ENROL_USER_SUSPENDED) { $this->update_user_enrol($instance, $userid, ENROL_USER_ACTIVE); $trace->output("unsuspending: {$userid} ==> {$course->shortname}", 1); } } foreach ($requestedroles as $userid => $userroles) { // Assign extra roles. foreach ($userroles as $roleid) { if (empty($currentroles[$userid][$roleid])) { role_assign($roleid, $userid, $context->id, ''); $currentroles[$userid][$roleid] = $roleid; $trace->output("assigning roles: {$userid} ==> {$course->shortname} as " . $allroles[$roleid]->shortname, 1); } } // Unassign removed roles. foreach ($currentroles[$userid] as $cr) { if (empty($userroles[$cr])) { role_unassign($cr, $userid, $context->id, ''); unset($currentroles[$userid][$cr]); $trace->output("unsassigning roles: {$userid} ==> {$course->shortname}", 1); } } unset($currentroles[$userid]); } foreach ($currentroles as $userid => $userroles) { // These are roles that exist only in Moodle, not the external database // so make sure the unenrol actions will handle them by setting status. $currentstatus += array($userid => ENROL_USER_ACTIVE); } // Handle course completions and final exam grades. foreach ($completioninfo as $userid => $cinfo) { if ($cinfo['status'] == $coursestatuscompletedfield_l) { // Update/create final exam grade then create course completion if course is flagged as complete for the user. require_once "{$CFG->libdir}/gradelib.php"; require_once $CFG->dirroot . '/completion/completion_completion.php'; //Get course shortname (needed to find final exam grade item id) if ($cm = $DB->get_record('course', array('id' => $cinfo['courseid']))) { $courseshortname = trim($cm->shortname); } else { $trace->output('Error: Unable to find course shortname or record for courseid ' . $cinfo['courseid'] . " for userid " . $userid . ". Course completion will be ignored."); continue; } $finalexamname = $courseshortname . ": Final Exam"; if ($gi = $DB->get_record('grade_items', array('itemname' => $finalexamname, 'courseid' => $cinfo['courseid']))) { // Get the grade_item record for the course final exam. $currentgrade = ""; //Now get the current final exam grade for user if present. $grading_info = grade_get_grades($cinfo['courseid'], $gi->itemtype, $gi->itemmodule, $gi->iteminstance, $userid); if (!empty($grading_info->items)) { $item = $grading_info->items[0]; if (isset($item->grades[$userid]->grade)) { $currentgrade = $item->grades[$userid]->grade + 0; $currentgrade = $currentgrade * 10; } } $trace->output('Old grade for courseid ' . $cinfo['courseid'] . " and userid " . $userid . " is " . $currentgrade . "."); } else { $trace->output('Error: Unable to get final exam record for courseid ' . $cinfo['courseid'] . " and userid " . $userid . ". Course completion will be ignored."); continue; } if (isset($cinfo['grade'])) { if ($cinfo['grade'] > $currentgrade || empty($currentgrade)) { // If imported grade is larger update the final exam grade $grade = array(); $grade['userid'] = $userid; $grade['rawgrade'] = $cinfo['grade'] / 10; //learn.saylor.org is currently using rawmaxgrade of 10.0000 grade_update('mod/quiz', $cinfo['courseid'], $gi->itemtype, $gi->itemmodule, $gi->iteminstance, $gi->itemnumber, $grade); $trace->output('Updating grade for courseid ' . $cinfo['courseid'] . " and userid " . $userid . " to " . $grade['rawgrade'] . "."); } else { if (!empty($currentgrade) && $currentgrade >= $cinfo['grade']) { $trace->output("Current grade for final exam for courseid " . $cinfo['courseid'] . " and userid " . $userid . " is larger or equal to the imported grade. Not updating grade."); continue; } else { debugging("Unable to determine if there is a current final exam grade for courseid " . $cinfo['courseid'] . " and userid " . $userid . " or whether it is less than the imported grade."); continue; } } //Mark course as complete. Create completion_completion object to handle completion info for that user and course. $cparams = array('userid' => $userid, 'course' => $cinfo['courseid']); $cc = new completion_completion($cparams); if ($cc->is_complete()) { continue; //Skip adding completion info for this course if the user has already completed this course. Possibility that his grade gets bumped up. } if (isset($cinfo['completiondate'])) { $completeddatestamp = strtotime($cinfo['completiondate']); //Convert the date string to a unix time stamp. } else { $completeddatestamp = time(); //If not set, just use the current date. } if (isset($cinfo['enroldate'])) { $enroldatestamp = strtotime($cinfo['enroldate']); //Convert the date string to a unix time stamp. } else { $enroldatestamp = $completeddatestamp; } $cc->mark_enrolled($enroldatestamp); $cc->mark_inprogress($enroldatestamp); $cc->mark_complete($completeddatestamp); $trace->output('Setting completion data for userid ' . $userid . ' and courseid ' . $cinfo['courseid'] . "."); } else { if (!isset($cinfo['grade'])) { $trace->output("Error: No grade info in external db for completed course " . $cinfo['courseid'] . " for user " . $userid . "."); } } } } // Deal with enrolments removed from external table. if ($unenrolaction == ENROL_EXT_REMOVED_UNENROL) { if (!$preventfullunenrol) { // Unenrol. foreach ($currentstatus as $userid => $status) { if (isset($requestedenrols[$userid])) { continue; } $this->unenrol_user($instance, $userid); $trace->output("unenrolling: {$userid} ==> {$course->shortname}", 1); } } } else { if ($unenrolaction == ENROL_EXT_REMOVED_KEEP) { // Keep - only adding enrolments. } else { if ($unenrolaction == ENROL_EXT_REMOVED_SUSPEND or $unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) { // Suspend enrolments. foreach ($currentstatus as $userid => $status) { if (isset($requestedenrols[$userid])) { continue; } if ($status != ENROL_USER_SUSPENDED) { $this->update_user_enrol($instance, $userid, ENROL_USER_SUSPENDED); $trace->output("suspending: {$userid} ==> {$course->shortname}", 1); } if ($unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) { if (isset($requestedroles[$userid])) { // We want this "other user" to keep their roles. continue; } role_unassign_all(array('contextid' => $context->id, 'userid' => $userid, 'component' => '', 'itemid' => $instance->id)); $trace->output("unsassigning all roles: {$userid} ==> {$course->shortname}", 1); } } } } } } // Close db connection. $extdb->Close(); $trace->output('...user enrolment synchronisation finished.'); $trace->finished(); return 0; }