MDL-59369 enrol: Introduce data-action attribute for enrol action links
[moodle.git] / enrol / database / lib.php
1 <?php
2 // This file is part of Moodle - http://moodle.org/
3 //
4 // Moodle is free software: you can redistribute it and/or modify
5 // it under the terms of the GNU General Public License as published by
6 // the Free Software Foundation, either version 3 of the License, or
7 // (at your option) any later version.
8 //
9 // Moodle is distributed in the hope that it will be useful,
10 // but WITHOUT ANY WARRANTY; without even the implied warranty of
11 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
12 // GNU General Public License for more details.
13 //
14 // You should have received a copy of the GNU General Public License
15 // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
17 /**
18  * Database enrolment plugin.
19  *
20  * This plugin synchronises enrolment and roles with external database table.
21  *
22  * @package    enrol_database
23  * @copyright  2010 Petr Skoda {@link http://skodak.org}
24  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
25  */
27 defined('MOODLE_INTERNAL') || die();
29 /**
30  * Database enrolment plugin implementation.
31  * @author  Petr Skoda - based on code by Martin Dougiamas, Martin Langhoff and others
32  * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
33  */
34 class enrol_database_plugin extends enrol_plugin {
35     /**
36      * Is it possible to delete enrol instance via standard UI?
37      *
38      * @param stdClass $instance
39      * @return bool
40      */
41     public function can_delete_instance($instance) {
42         $context = context_course::instance($instance->courseid);
43         if (!has_capability('enrol/database:config', $context)) {
44             return false;
45         }
46         if (!enrol_is_enabled('database')) {
47             return true;
48         }
49         if (!$this->get_config('dbtype') or !$this->get_config('remoteenroltable') or !$this->get_config('remotecoursefield') or !$this->get_config('remoteuserfield')) {
50             return true;
51         }
53         //TODO: connect to external system and make sure no users are to be enrolled in this course
54         return false;
55     }
57     /**
58      * Is it possible to hide/show enrol instance via standard UI?
59      *
60      * @param stdClass $instance
61      * @return bool
62      */
63     public function can_hide_show_instance($instance) {
64         $context = context_course::instance($instance->courseid);
65         return has_capability('enrol/database:config', $context);
66     }
68     /**
69      * Does this plugin allow manual unenrolment of a specific user?
70      * Yes, but only if user suspended...
71      *
72      * @param stdClass $instance course enrol instance
73      * @param stdClass $ue record from user_enrolments table
74      *
75      * @return bool - true means user with 'enrol/xxx:unenrol' may unenrol this user, false means nobody may touch this user enrolment
76      */
77     public function allow_unenrol_user(stdClass $instance, stdClass $ue) {
78         if ($ue->status == ENROL_USER_SUSPENDED) {
79             return true;
80         }
82         return false;
83     }
85     /**
86      * Gets an array of the user enrolment actions.
87      *
88      * @param course_enrolment_manager $manager
89      * @param stdClass $ue A user enrolment object
90      * @return array An array of user_enrolment_actions
91      */
92     public function get_user_enrolment_actions(course_enrolment_manager $manager, $ue) {
93         $actions = array();
94         $context = $manager->get_context();
95         $instance = $ue->enrolmentinstance;
96         $params = $manager->get_moodlepage()->url->params();
97         $params['ue'] = $ue->id;
98         if ($this->allow_unenrol_user($instance, $ue) && has_capability('enrol/database:unenrol', $context)) {
99             $url = new moodle_url('/enrol/unenroluser.php', $params);
100             $actionparams = array('class' => 'unenrollink', 'rel' => $ue->id, 'data-action' => ENROL_ACTION_UNENROL);
101             $actions[] = new user_enrolment_action(new pix_icon('t/delete', ''), get_string('unenrol', 'enrol'), $url,
102                 $actionparams);
103         }
104         return $actions;
105     }
107     /**
108      * Forces synchronisation of user enrolments with external database,
109      * does not create new courses.
110      *
111      * @param stdClass $user user record
112      * @return void
113      */
114     public function sync_user_enrolments($user) {
115         global $CFG, $DB;
117         // We do not create courses here intentionally because it requires full sync and is slow.
118         if (!$this->get_config('dbtype') or !$this->get_config('remoteenroltable') or !$this->get_config('remotecoursefield') or !$this->get_config('remoteuserfield')) {
119             return;
120         }
122         $table            = $this->get_config('remoteenroltable');
123         $coursefield      = trim($this->get_config('remotecoursefield'));
124         $userfield        = trim($this->get_config('remoteuserfield'));
125         $rolefield        = trim($this->get_config('remoterolefield'));
126         $otheruserfield   = trim($this->get_config('remoteotheruserfield'));
128         // Lowercased versions - necessary because we normalise the resultset with array_change_key_case().
129         $coursefield_l    = strtolower($coursefield);
130         $userfield_l      = strtolower($userfield);
131         $rolefield_l      = strtolower($rolefield);
132         $otheruserfieldlower = strtolower($otheruserfield);
134         $localrolefield   = $this->get_config('localrolefield');
135         $localuserfield   = $this->get_config('localuserfield');
136         $localcoursefield = $this->get_config('localcoursefield');
138         $unenrolaction    = $this->get_config('unenrolaction');
139         $defaultrole      = $this->get_config('defaultrole');
141         $ignorehidden     = $this->get_config('ignorehiddencourses');
143         if (!is_object($user) or !property_exists($user, 'id')) {
144             throw new coding_exception('Invalid $user parameter in sync_user_enrolments()');
145         }
147         if (!property_exists($user, $localuserfield)) {
148             debugging('Invalid $user parameter in sync_user_enrolments(), missing '.$localuserfield);
149             $user = $DB->get_record('user', array('id'=>$user->id));
150         }
152         // Create roles mapping.
153         $allroles = get_all_roles();
154         if (!isset($allroles[$defaultrole])) {
155             $defaultrole = 0;
156         }
157         $roles = array();
158         foreach ($allroles as $role) {
159             $roles[$role->$localrolefield] = $role->id;
160         }
162         $roleassigns = array();
163         $enrols = array();
164         $instances = array();
166         if (!$extdb = $this->db_init()) {
167             // Can not connect to database, sorry.
168             return;
169         }
171         // Read remote enrols and create instances.
172         $sql = $this->db_get_sql($table, array($userfield=>$user->$localuserfield), array(), false);
174         if ($rs = $extdb->Execute($sql)) {
175             if (!$rs->EOF) {
176                 while ($fields = $rs->FetchRow()) {
177                     $fields = array_change_key_case($fields, CASE_LOWER);
178                     $fields = $this->db_decode($fields);
180                     if (empty($fields[$coursefield_l])) {
181                         // Missing course info.
182                         continue;
183                     }
184                     if (!$course = $DB->get_record('course', array($localcoursefield=>$fields[$coursefield_l]), 'id,visible')) {
185                         continue;
186                     }
187                     if (!$course->visible and $ignorehidden) {
188                         continue;
189                     }
191                     if (empty($fields[$rolefield_l]) or !isset($roles[$fields[$rolefield_l]])) {
192                         if (!$defaultrole) {
193                             // Role is mandatory.
194                             continue;
195                         }
196                         $roleid = $defaultrole;
197                     } else {
198                         $roleid = $roles[$fields[$rolefield_l]];
199                     }
201                     $roleassigns[$course->id][$roleid] = $roleid;
202                     if (empty($fields[$otheruserfieldlower])) {
203                         $enrols[$course->id][$roleid] = $roleid;
204                     }
206                     if ($instance = $DB->get_record('enrol', array('courseid'=>$course->id, 'enrol'=>'database'), '*', IGNORE_MULTIPLE)) {
207                         $instances[$course->id] = $instance;
208                         continue;
209                     }
211                     $enrolid = $this->add_instance($course);
212                     $instances[$course->id] = $DB->get_record('enrol', array('id'=>$enrolid));
213                 }
214             }
215             $rs->Close();
216             $extdb->Close();
217         } else {
218             // Bad luck, something is wrong with the db connection.
219             $extdb->Close();
220             return;
221         }
223         // Enrol user into courses and sync roles.
224         foreach ($roleassigns as $courseid => $roles) {
225             if (!isset($instances[$courseid])) {
226                 // Ignored.
227                 continue;
228             }
229             $instance = $instances[$courseid];
231             if (isset($enrols[$courseid])) {
232                 if ($e = $DB->get_record('user_enrolments', array('userid' => $user->id, 'enrolid' => $instance->id))) {
233                     // Reenable enrolment when previously disable enrolment refreshed.
234                     if ($e->status == ENROL_USER_SUSPENDED) {
235                         $this->update_user_enrol($instance, $user->id, ENROL_USER_ACTIVE);
236                     }
237                 } else {
238                     $roleid = reset($enrols[$courseid]);
239                     $this->enrol_user($instance, $user->id, $roleid, 0, 0, ENROL_USER_ACTIVE);
240                 }
241             }
243             if (!$context = context_course::instance($instance->courseid, IGNORE_MISSING)) {
244                 // Weird.
245                 continue;
246             }
247             $current = $DB->get_records('role_assignments', array('contextid'=>$context->id, 'userid'=>$user->id, 'component'=>'enrol_database', 'itemid'=>$instance->id), '', 'id, roleid');
249             $existing = array();
250             foreach ($current as $r) {
251                 if (isset($roles[$r->roleid])) {
252                     $existing[$r->roleid] = $r->roleid;
253                 } else {
254                     role_unassign($r->roleid, $user->id, $context->id, 'enrol_database', $instance->id);
255                 }
256             }
257             foreach ($roles as $rid) {
258                 if (!isset($existing[$rid])) {
259                     role_assign($rid, $user->id, $context->id, 'enrol_database', $instance->id);
260                 }
261             }
262         }
264         // Unenrol as necessary.
265         $sql = "SELECT e.*, c.visible AS cvisible, ue.status AS ustatus
266                   FROM {enrol} e
267                   JOIN {course} c ON c.id = e.courseid
268                   JOIN {role_assignments} ra ON ra.itemid = e.id
269              LEFT JOIN {user_enrolments} ue ON ue.enrolid = e.id AND ue.userid = ra.userid
270                  WHERE ra.userid = :userid AND e.enrol = 'database'";
271         $rs = $DB->get_recordset_sql($sql, array('userid' => $user->id));
272         foreach ($rs as $instance) {
273             if (!$instance->cvisible and $ignorehidden) {
274                 continue;
275             }
277             if (!$context = context_course::instance($instance->courseid, IGNORE_MISSING)) {
278                 // Very weird.
279                 continue;
280             }
282             if (!empty($enrols[$instance->courseid])) {
283                 // We want this user enrolled.
284                 continue;
285             }
287             // Deal with enrolments removed from external table
288             if ($unenrolaction == ENROL_EXT_REMOVED_UNENROL) {
289                 $this->unenrol_user($instance, $user->id);
291             } else if ($unenrolaction == ENROL_EXT_REMOVED_KEEP) {
292                 // Keep - only adding enrolments.
294             } else if ($unenrolaction == ENROL_EXT_REMOVED_SUSPEND or $unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
295                 // Suspend users.
296                 if ($instance->ustatus != ENROL_USER_SUSPENDED) {
297                     $this->update_user_enrol($instance, $user->id, ENROL_USER_SUSPENDED);
298                 }
299                 if ($unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
300                     if (!empty($roleassigns[$instance->courseid])) {
301                         // We want this "other user" to keep their roles.
302                         continue;
303                     }
304                     role_unassign_all(array('contextid'=>$context->id, 'userid'=>$user->id, 'component'=>'enrol_database', 'itemid'=>$instance->id));
305                 }
306             }
307         }
308         $rs->close();
309     }
311     /**
312      * Forces synchronisation of all enrolments with external database.
313      *
314      * @param progress_trace $trace
315      * @param null|int $onecourse limit sync to one course only (used primarily in restore)
316      * @return int 0 means success, 1 db connect failure, 2 db read failure
317      */
318     public function sync_enrolments(progress_trace $trace, $onecourse = null) {
319         global $CFG, $DB;
321         // We do not create courses here intentionally because it requires full sync and is slow.
322         if (!$this->get_config('dbtype') or !$this->get_config('remoteenroltable') or !$this->get_config('remotecoursefield') or !$this->get_config('remoteuserfield')) {
323             $trace->output('User enrolment synchronisation skipped.');
324             $trace->finished();
325             return 0;
326         }
328         $trace->output('Starting user enrolment synchronisation...');
330         if (!$extdb = $this->db_init()) {
331             $trace->output('Error while communicating with external enrolment database');
332             $trace->finished();
333             return 1;
334         }
336         // We may need a lot of memory here.
337         core_php_time_limit::raise();
338         raise_memory_limit(MEMORY_HUGE);
340         $table            = $this->get_config('remoteenroltable');
341         $coursefield      = trim($this->get_config('remotecoursefield'));
342         $userfield        = trim($this->get_config('remoteuserfield'));
343         $rolefield        = trim($this->get_config('remoterolefield'));
344         $otheruserfield   = trim($this->get_config('remoteotheruserfield'));
346         // Lowercased versions - necessary because we normalise the resultset with array_change_key_case().
347         $coursefield_l    = strtolower($coursefield);
348         $userfield_l      = strtolower($userfield);
349         $rolefield_l      = strtolower($rolefield);
350         $otheruserfieldlower = strtolower($otheruserfield);
352         $localrolefield   = $this->get_config('localrolefield');
353         $localuserfield   = $this->get_config('localuserfield');
354         $localcoursefield = $this->get_config('localcoursefield');
356         $unenrolaction    = $this->get_config('unenrolaction');
357         $defaultrole      = $this->get_config('defaultrole');
359         // Create roles mapping.
360         $allroles = get_all_roles();
361         if (!isset($allroles[$defaultrole])) {
362             $defaultrole = 0;
363         }
364         $roles = array();
365         foreach ($allroles as $role) {
366             $roles[$role->$localrolefield] = $role->id;
367         }
369         if ($onecourse) {
370             $sql = "SELECT c.id, c.visible, c.$localcoursefield AS mapping, c.shortname, e.id AS enrolid
371                       FROM {course} c
372                  LEFT JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'database')
373                      WHERE c.id = :id";
374             if (!$course = $DB->get_record_sql($sql, array('id'=>$onecourse))) {
375                 // Course does not exist, nothing to sync.
376                 return 0;
377             }
378             if (empty($course->mapping)) {
379                 // We can not map to this course, sorry.
380                 return 0;
381             }
382             if (empty($course->enrolid)) {
383                 $course->enrolid = $this->add_instance($course);
384             }
385             $existing = array($course->mapping=>$course);
387             // Feel free to unenrol everybody, no safety tricks here.
388             $preventfullunenrol = false;
389             // Course being restored are always hidden, we have to ignore the setting here.
390             $ignorehidden = false;
392         } else {
393             // Get a list of courses to be synced that are in external table.
394             $externalcourses = array();
395             $sql = $this->db_get_sql($table, array(), array($coursefield), true);
396             if ($rs = $extdb->Execute($sql)) {
397                 if (!$rs->EOF) {
398                     while ($mapping = $rs->FetchRow()) {
399                         $mapping = reset($mapping);
400                         $mapping = $this->db_decode($mapping);
401                         if (empty($mapping)) {
402                             // invalid mapping
403                             continue;
404                         }
405                         $externalcourses[$mapping] = true;
406                     }
407                 }
408                 $rs->Close();
409             } else {
410                 $trace->output('Error reading data from the external enrolment table');
411                 $extdb->Close();
412                 return 2;
413             }
414             $preventfullunenrol = empty($externalcourses);
415             if ($preventfullunenrol and $unenrolaction == ENROL_EXT_REMOVED_UNENROL) {
416                 $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);
417             }
419             // First find all existing courses with enrol instance.
420             $existing = array();
421             $sql = "SELECT c.id, c.visible, c.$localcoursefield AS mapping, e.id AS enrolid, c.shortname
422                       FROM {course} c
423                       JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'database')";
424             $rs = $DB->get_recordset_sql($sql); // Watch out for idnumber duplicates.
425             foreach ($rs as $course) {
426                 if (empty($course->mapping)) {
427                     continue;
428                 }
429                 $existing[$course->mapping] = $course;
430                 unset($externalcourses[$course->mapping]);
431             }
432             $rs->close();
434             // Add necessary enrol instances that are not present yet.
435             $params = array();
436             $localnotempty = "";
437             if ($localcoursefield !== 'id') {
438                 $localnotempty =  "AND c.$localcoursefield <> :lcfe";
439                 $params['lcfe'] = '';
440             }
441             $sql = "SELECT c.id, c.visible, c.$localcoursefield AS mapping, c.shortname
442                       FROM {course} c
443                  LEFT JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'database')
444                      WHERE e.id IS NULL $localnotempty";
445             $rs = $DB->get_recordset_sql($sql, $params);
446             foreach ($rs as $course) {
447                 if (empty($course->mapping)) {
448                     continue;
449                 }
450                 if (!isset($externalcourses[$course->mapping])) {
451                     // Course not synced or duplicate.
452                     continue;
453                 }
454                 $course->enrolid = $this->add_instance($course);
455                 $existing[$course->mapping] = $course;
456                 unset($externalcourses[$course->mapping]);
457             }
458             $rs->close();
460             // Print list of missing courses.
461             if ($externalcourses) {
462                 $list = implode(', ', array_keys($externalcourses));
463                 $trace->output("error: following courses do not exist - $list", 1);
464                 unset($list);
465             }
467             // Free memory.
468             unset($externalcourses);
470             $ignorehidden = $this->get_config('ignorehiddencourses');
471         }
473         // Sync user enrolments.
474         $sqlfields = array($userfield);
475         if ($rolefield) {
476             $sqlfields[] = $rolefield;
477         }
478         if ($otheruserfield) {
479             $sqlfields[] = $otheruserfield;
480         }
481         foreach ($existing as $course) {
482             if ($ignorehidden and !$course->visible) {
483                 continue;
484             }
485             if (!$instance = $DB->get_record('enrol', array('id'=>$course->enrolid))) {
486                 continue; // Weird!
487             }
488             $context = context_course::instance($course->id);
490             // Get current list of enrolled users with their roles.
491             $currentroles  = array();
492             $currentenrols = array();
493             $currentstatus = array();
494             $usermapping   = array();
495             $sql = "SELECT u.$localuserfield AS mapping, u.id AS userid, ue.status, ra.roleid
496                       FROM {user} u
497                       JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.component = 'enrol_database' AND ra.itemid = :enrolid)
498                  LEFT JOIN {user_enrolments} ue ON (ue.userid = u.id AND ue.enrolid = ra.itemid)
499                      WHERE u.deleted = 0";
500             $params = array('enrolid'=>$instance->id);
501             if ($localuserfield === 'username') {
502                 $sql .= " AND u.mnethostid = :mnethostid";
503                 $params['mnethostid'] = $CFG->mnet_localhost_id;
504             }
505             $rs = $DB->get_recordset_sql($sql, $params);
506             foreach ($rs as $ue) {
507                 $currentroles[$ue->userid][$ue->roleid] = $ue->roleid;
508                 $usermapping[$ue->mapping] = $ue->userid;
510                 if (isset($ue->status)) {
511                     $currentenrols[$ue->userid][$ue->roleid] = $ue->roleid;
512                     $currentstatus[$ue->userid] = $ue->status;
513                 }
514             }
515             $rs->close();
517             // Get list of users that need to be enrolled and their roles.
518             $requestedroles  = array();
519             $requestedenrols = array();
520             $sql = $this->db_get_sql($table, array($coursefield=>$course->mapping), $sqlfields);
521             if ($rs = $extdb->Execute($sql)) {
522                 if (!$rs->EOF) {
523                     $usersearch = array('deleted' => 0);
524                     if ($localuserfield === 'username') {
525                         $usersearch['mnethostid'] = $CFG->mnet_localhost_id;
526                     }
527                     while ($fields = $rs->FetchRow()) {
528                         $fields = array_change_key_case($fields, CASE_LOWER);
529                         if (empty($fields[$userfield_l])) {
530                             $trace->output("error: skipping user without mandatory $localuserfield in course '$course->mapping'", 1);
531                             continue;
532                         }
533                         $mapping = $fields[$userfield_l];
534                         if (!isset($usermapping[$mapping])) {
535                             $usersearch[$localuserfield] = $mapping;
536                             if (!$user = $DB->get_record('user', $usersearch, 'id', IGNORE_MULTIPLE)) {
537                                 $trace->output("error: skipping unknown user $localuserfield '$mapping' in course '$course->mapping'", 1);
538                                 continue;
539                             }
540                             $usermapping[$mapping] = $user->id;
541                             $userid = $user->id;
542                         } else {
543                             $userid = $usermapping[$mapping];
544                         }
545                         if (empty($fields[$rolefield_l]) or !isset($roles[$fields[$rolefield_l]])) {
546                             if (!$defaultrole) {
547                                 $trace->output("error: skipping user '$userid' in course '$course->mapping' - missing course and default role", 1);
548                                 continue;
549                             }
550                             $roleid = $defaultrole;
551                         } else {
552                             $roleid = $roles[$fields[$rolefield_l]];
553                         }
555                         $requestedroles[$userid][$roleid] = $roleid;
556                         if (empty($fields[$otheruserfieldlower])) {
557                             $requestedenrols[$userid][$roleid] = $roleid;
558                         }
559                     }
560                 }
561                 $rs->Close();
562             } else {
563                 $trace->output("error: skipping course '$course->mapping' - could not match with external database", 1);
564                 continue;
565             }
566             unset($usermapping);
568             // Enrol all users and sync roles.
569             foreach ($requestedenrols as $userid => $userroles) {
570                 foreach ($userroles as $roleid) {
571                     if (empty($currentenrols[$userid])) {
572                         $this->enrol_user($instance, $userid, $roleid, 0, 0, ENROL_USER_ACTIVE);
573                         $currentroles[$userid][$roleid] = $roleid;
574                         $currentenrols[$userid][$roleid] = $roleid;
575                         $currentstatus[$userid] = ENROL_USER_ACTIVE;
576                         $trace->output("enrolling: $userid ==> $course->shortname as ".$allroles[$roleid]->shortname, 1);
577                     }
578                 }
580                 // Reenable enrolment when previously disable enrolment refreshed.
581                 if ($currentstatus[$userid] == ENROL_USER_SUSPENDED) {
582                     $this->update_user_enrol($instance, $userid, ENROL_USER_ACTIVE);
583                     $trace->output("unsuspending: $userid ==> $course->shortname", 1);
584                 }
585             }
587             foreach ($requestedroles as $userid => $userroles) {
588                 // Assign extra roles.
589                 foreach ($userroles as $roleid) {
590                     if (empty($currentroles[$userid][$roleid])) {
591                         role_assign($roleid, $userid, $context->id, 'enrol_database', $instance->id);
592                         $currentroles[$userid][$roleid] = $roleid;
593                         $trace->output("assigning roles: $userid ==> $course->shortname as ".$allroles[$roleid]->shortname, 1);
594                     }
595                 }
597                 // Unassign removed roles.
598                 foreach ($currentroles[$userid] as $cr) {
599                     if (empty($userroles[$cr])) {
600                         role_unassign($cr, $userid, $context->id, 'enrol_database', $instance->id);
601                         unset($currentroles[$userid][$cr]);
602                         $trace->output("unsassigning roles: $userid ==> $course->shortname", 1);
603                     }
604                 }
606                 unset($currentroles[$userid]);
607             }
609             foreach ($currentroles as $userid => $userroles) {
610                 // These are roles that exist only in Moodle, not the external database
611                 // so make sure the unenrol actions will handle them by setting status.
612                 $currentstatus += array($userid => ENROL_USER_ACTIVE);
613             }
615             // Deal with enrolments removed from external table.
616             if ($unenrolaction == ENROL_EXT_REMOVED_UNENROL) {
617                 if (!$preventfullunenrol) {
618                     // Unenrol.
619                     foreach ($currentstatus as $userid => $status) {
620                         if (isset($requestedenrols[$userid])) {
621                             continue;
622                         }
623                         $this->unenrol_user($instance, $userid);
624                         $trace->output("unenrolling: $userid ==> $course->shortname", 1);
625                     }
626                 }
628             } else if ($unenrolaction == ENROL_EXT_REMOVED_KEEP) {
629                 // Keep - only adding enrolments.
631             } else if ($unenrolaction == ENROL_EXT_REMOVED_SUSPEND or $unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
632                 // Suspend enrolments.
633                 foreach ($currentstatus as $userid => $status) {
634                     if (isset($requestedenrols[$userid])) {
635                         continue;
636                     }
637                     if ($status != ENROL_USER_SUSPENDED) {
638                         $this->update_user_enrol($instance, $userid, ENROL_USER_SUSPENDED);
639                         $trace->output("suspending: $userid ==> $course->shortname", 1);
640                     }
641                     if ($unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
642                         if (isset($requestedroles[$userid])) {
643                             // We want this "other user" to keep their roles.
644                             continue;
645                         }
646                         role_unassign_all(array('contextid'=>$context->id, 'userid'=>$userid, 'component'=>'enrol_database', 'itemid'=>$instance->id));
648                         $trace->output("unsassigning all roles: $userid ==> $course->shortname", 1);
649                     }
650                 }
651             }
652         }
654         // Close db connection.
655         $extdb->Close();
657         $trace->output('...user enrolment synchronisation finished.');
658         $trace->finished();
660         return 0;
661     }
663     /**
664      * Performs a full sync with external database.
665      *
666      * First it creates new courses if necessary, then
667      * enrols and unenrols users.
668      *
669      * @param progress_trace $trace
670      * @return int 0 means success, 1 db connect failure, 4 db read failure
671      */
672     public function sync_courses(progress_trace $trace) {
673         global $CFG, $DB;
675         // Make sure we sync either enrolments or courses.
676         if (!$this->get_config('dbtype') or !$this->get_config('newcoursetable') or !$this->get_config('newcoursefullname') or !$this->get_config('newcourseshortname')) {
677             $trace->output('Course synchronisation skipped.');
678             $trace->finished();
679             return 0;
680         }
682         $trace->output('Starting course synchronisation...');
684         // We may need a lot of memory here.
685         core_php_time_limit::raise();
686         raise_memory_limit(MEMORY_HUGE);
688         if (!$extdb = $this->db_init()) {
689             $trace->output('Error while communicating with external enrolment database');
690             $trace->finished();
691             return 1;
692         }
694         $table     = $this->get_config('newcoursetable');
695         $fullname  = trim($this->get_config('newcoursefullname'));
696         $shortname = trim($this->get_config('newcourseshortname'));
697         $idnumber  = trim($this->get_config('newcourseidnumber'));
698         $category  = trim($this->get_config('newcoursecategory'));
700         // Lowercased versions - necessary because we normalise the resultset with array_change_key_case().
701         $fullname_l  = strtolower($fullname);
702         $shortname_l = strtolower($shortname);
703         $idnumber_l  = strtolower($idnumber);
704         $category_l  = strtolower($category);
706         $localcategoryfield = $this->get_config('localcategoryfield', 'id');
707         $defaultcategory    = $this->get_config('defaultcategory');
709         if (!$DB->record_exists('course_categories', array('id'=>$defaultcategory))) {
710             $trace->output("default course category does not exist!", 1);
711             $categories = $DB->get_records('course_categories', array(), 'sortorder', 'id', 0, 1);
712             $first = reset($categories);
713             $defaultcategory = $first->id;
714         }
716         $sqlfields = array($fullname, $shortname);
717         if ($category) {
718             $sqlfields[] = $category;
719         }
720         if ($idnumber) {
721             $sqlfields[] = $idnumber;
722         }
723         $sql = $this->db_get_sql($table, array(), $sqlfields, true);
724         $createcourses = array();
725         if ($rs = $extdb->Execute($sql)) {
726             if (!$rs->EOF) {
727                 while ($fields = $rs->FetchRow()) {
728                     $fields = array_change_key_case($fields, CASE_LOWER);
729                     $fields = $this->db_decode($fields);
730                     if (empty($fields[$shortname_l]) or empty($fields[$fullname_l])) {
731                         $trace->output('error: invalid external course record, shortname and fullname are mandatory: ' . json_encode($fields), 1); // Hopefully every geek can read JS, right?
732                         continue;
733                     }
734                     if ($DB->record_exists('course', array('shortname'=>$fields[$shortname_l]))) {
735                         // Already exists, skip.
736                         continue;
737                     }
738                     // Allow empty idnumber but not duplicates.
739                     if ($idnumber and $fields[$idnumber_l] !== '' and $fields[$idnumber_l] !== null and $DB->record_exists('course', array('idnumber'=>$fields[$idnumber_l]))) {
740                         $trace->output('error: duplicate idnumber, can not create course: '.$fields[$shortname_l].' ['.$fields[$idnumber_l].']', 1);
741                         continue;
742                     }
743                     $course = new stdClass();
744                     $course->fullname  = $fields[$fullname_l];
745                     $course->shortname = $fields[$shortname_l];
746                     $course->idnumber  = $idnumber ? $fields[$idnumber_l] : '';
747                     if ($category) {
748                         if (empty($fields[$category_l])) {
749                             // Empty category means use default.
750                             $course->category = $defaultcategory;
751                         } else if ($coursecategory = $DB->get_record('course_categories', array($localcategoryfield=>$fields[$category_l]), 'id')) {
752                             // Yay, correctly specified category!
753                             $course->category = $coursecategory->id;
754                             unset($coursecategory);
755                         } else {
756                             // Bad luck, better not continue because unwanted ppl might get access to course in different category.
757                             $trace->output('error: invalid category '.$localcategoryfield.', can not create course: '.$fields[$shortname_l], 1);
758                             continue;
759                         }
760                     } else {
761                         $course->category = $defaultcategory;
762                     }
763                     $createcourses[] = $course;
764                 }
765             }
766             $rs->Close();
767         } else {
768             $extdb->Close();
769             $trace->output('Error reading data from the external course table');
770             $trace->finished();
771             return 4;
772         }
773         if ($createcourses) {
774             require_once("$CFG->dirroot/course/lib.php");
776             $templatecourse = $this->get_config('templatecourse');
778             $template = false;
779             if ($templatecourse) {
780                 if ($template = $DB->get_record('course', array('shortname'=>$templatecourse))) {
781                     $template = fullclone(course_get_format($template)->get_course());
782                     if (!isset($template->numsections)) {
783                         $template->numsections = course_get_format($template)->get_last_section_number();
784                     }
785                     unset($template->id);
786                     unset($template->fullname);
787                     unset($template->shortname);
788                     unset($template->idnumber);
789                 } else {
790                     $trace->output("can not find template for new course!", 1);
791                 }
792             }
793             if (!$template) {
794                 $courseconfig = get_config('moodlecourse');
795                 $template = new stdClass();
796                 $template->summary        = '';
797                 $template->summaryformat  = FORMAT_HTML;
798                 $template->format         = $courseconfig->format;
799                 $template->newsitems      = $courseconfig->newsitems;
800                 $template->showgrades     = $courseconfig->showgrades;
801                 $template->showreports    = $courseconfig->showreports;
802                 $template->maxbytes       = $courseconfig->maxbytes;
803                 $template->groupmode      = $courseconfig->groupmode;
804                 $template->groupmodeforce = $courseconfig->groupmodeforce;
805                 $template->visible        = $courseconfig->visible;
806                 $template->lang           = $courseconfig->lang;
807                 $template->groupmodeforce = $courseconfig->groupmodeforce;
808             }
810             foreach ($createcourses as $fields) {
811                 $newcourse = clone($template);
812                 $newcourse->fullname  = $fields->fullname;
813                 $newcourse->shortname = $fields->shortname;
814                 $newcourse->idnumber  = $fields->idnumber;
815                 $newcourse->category  = $fields->category;
817                 // Detect duplicate data once again, above we can not find duplicates
818                 // in external data using DB collation rules...
819                 if ($DB->record_exists('course', array('shortname' => $newcourse->shortname))) {
820                     $trace->output("can not insert new course, duplicate shortname detected: ".$newcourse->shortname, 1);
821                     continue;
822                 } else if (!empty($newcourse->idnumber) and $DB->record_exists('course', array('idnumber' => $newcourse->idnumber))) {
823                     $trace->output("can not insert new course, duplicate idnumber detected: ".$newcourse->idnumber, 1);
824                     continue;
825                 }
826                 $c = create_course($newcourse);
827                 $trace->output("creating course: $c->id, $c->fullname, $c->shortname, $c->idnumber, $c->category", 1);
828             }
830             unset($createcourses);
831             unset($template);
832         }
834         // Close db connection.
835         $extdb->Close();
837         $trace->output('...course synchronisation finished.');
838         $trace->finished();
840         return 0;
841     }
843     protected function db_get_sql($table, array $conditions, array $fields, $distinct = false, $sort = "") {
844         $fields = $fields ? implode(',', $fields) : "*";
845         $where = array();
846         if ($conditions) {
847             foreach ($conditions as $key=>$value) {
848                 $value = $this->db_encode($this->db_addslashes($value));
850                 $where[] = "$key = '$value'";
851             }
852         }
853         $where = $where ? "WHERE ".implode(" AND ", $where) : "";
854         $sort = $sort ? "ORDER BY $sort" : "";
855         $distinct = $distinct ? "DISTINCT" : "";
856         $sql = "SELECT $distinct $fields
857                   FROM $table
858                  $where
859                   $sort";
861         return $sql;
862     }
864     /**
865      * Tries to make connection to the external database.
866      *
867      * @return null|ADONewConnection
868      */
869     protected function db_init() {
870         global $CFG;
872         require_once($CFG->libdir.'/adodb/adodb.inc.php');
874         // Connect to the external database (forcing new connection).
875         $extdb = ADONewConnection($this->get_config('dbtype'));
876         if ($this->get_config('debugdb')) {
877             $extdb->debug = true;
878             ob_start(); // Start output buffer to allow later use of the page headers.
879         }
881         // The dbtype my contain the new connection URL, so make sure we are not connected yet.
882         if (!$extdb->IsConnected()) {
883             $result = $extdb->Connect($this->get_config('dbhost'), $this->get_config('dbuser'), $this->get_config('dbpass'), $this->get_config('dbname'), true);
884             if (!$result) {
885                 return null;
886             }
887         }
889         $extdb->SetFetchMode(ADODB_FETCH_ASSOC);
890         if ($this->get_config('dbsetupsql')) {
891             $extdb->Execute($this->get_config('dbsetupsql'));
892         }
893         return $extdb;
894     }
896     protected function db_addslashes($text) {
897         // Use custom made function for now - it is better to not rely on adodb or php defaults.
898         if ($this->get_config('dbsybasequoting')) {
899             $text = str_replace('\\', '\\\\', $text);
900             $text = str_replace(array('\'', '"', "\0"), array('\\\'', '\\"', '\\0'), $text);
901         } else {
902             $text = str_replace("'", "''", $text);
903         }
904         return $text;
905     }
907     protected function db_encode($text) {
908         $dbenc = $this->get_config('dbencoding');
909         if (empty($dbenc) or $dbenc == 'utf-8') {
910             return $text;
911         }
912         if (is_array($text)) {
913             foreach($text as $k=>$value) {
914                 $text[$k] = $this->db_encode($value);
915             }
916             return $text;
917         } else {
918             return core_text::convert($text, 'utf-8', $dbenc);
919         }
920     }
922     protected function db_decode($text) {
923         $dbenc = $this->get_config('dbencoding');
924         if (empty($dbenc) or $dbenc == 'utf-8') {
925             return $text;
926         }
927         if (is_array($text)) {
928             foreach($text as $k=>$value) {
929                 $text[$k] = $this->db_decode($value);
930             }
931             return $text;
932         } else {
933             return core_text::convert($text, $dbenc, 'utf-8');
934         }
935     }
937     /**
938      * Automatic enrol sync executed during restore.
939      * @param stdClass $course course record
940      */
941     public function restore_sync_course($course) {
942         $trace = new null_progress_trace();
943         $this->sync_enrolments($trace, $course->id);
944     }
946     /**
947      * Restore instance and map settings.
948      *
949      * @param restore_enrolments_structure_step $step
950      * @param stdClass $data
951      * @param stdClass $course
952      * @param int $oldid
953      */
954     public function restore_instance(restore_enrolments_structure_step $step, stdClass $data, $course, $oldid) {
955         global $DB;
957         if ($instance = $DB->get_record('enrol', array('courseid'=>$course->id, 'enrol'=>$this->get_name()))) {
958             $instanceid = $instance->id;
959         } else {
960             $instanceid = $this->add_instance($course);
961         }
962         $step->set_mapping('enrol', $oldid, $instanceid);
963     }
965     /**
966      * Restore user enrolment.
967      *
968      * @param restore_enrolments_structure_step $step
969      * @param stdClass $data
970      * @param stdClass $instance
971      * @param int $oldinstancestatus
972      * @param int $userid
973      */
974     public function restore_user_enrolment(restore_enrolments_structure_step $step, $data, $instance, $userid, $oldinstancestatus) {
975         global $DB;
977         if ($this->get_config('unenrolaction') == ENROL_EXT_REMOVED_UNENROL) {
978             // Enrolments were already synchronised in restore_instance(), we do not want any suspended leftovers.
979             return;
980         }
981         if (!$DB->record_exists('user_enrolments', array('enrolid'=>$instance->id, 'userid'=>$userid))) {
982             $this->enrol_user($instance, $userid, null, 0, 0, ENROL_USER_SUSPENDED);
983         }
984     }
986     /**
987      * Restore role assignment.
988      *
989      * @param stdClass $instance
990      * @param int $roleid
991      * @param int $userid
992      * @param int $contextid
993      */
994     public function restore_role_assignment($instance, $roleid, $userid, $contextid) {
995         if ($this->get_config('unenrolaction') == ENROL_EXT_REMOVED_UNENROL or $this->get_config('unenrolaction') == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
996             // Role assignments were already synchronised in restore_instance(), we do not want any leftovers.
997             return;
998         }
999         role_assign($roleid, $userid, $contextid, 'enrol_'.$this->get_name(), $instance->id);
1000     }
1002     /**
1003      * Test plugin settings, print info to output.
1004      */
1005     public function test_settings() {
1006         global $CFG, $OUTPUT;
1008         // NOTE: this is not localised intentionally, admins are supposed to understand English at least a bit...
1010         raise_memory_limit(MEMORY_HUGE);
1012         $this->load_config();
1014         $enroltable = $this->get_config('remoteenroltable');
1015         $coursetable = $this->get_config('newcoursetable');
1017         if (empty($enroltable)) {
1018             echo $OUTPUT->notification('External enrolment table not specified.', 'notifyproblem');
1019         }
1021         if (empty($coursetable)) {
1022             echo $OUTPUT->notification('External course table not specified.', 'notifyproblem');
1023         }
1025         if (empty($coursetable) and empty($enroltable)) {
1026             return;
1027         }
1029         $olddebug = $CFG->debug;
1030         $olddisplay = ini_get('display_errors');
1031         ini_set('display_errors', '1');
1032         $CFG->debug = DEBUG_DEVELOPER;
1033         $olddebugdb = $this->config->debugdb;
1034         $this->config->debugdb = 1;
1035         error_reporting($CFG->debug);
1037         $adodb = $this->db_init();
1039         if (!$adodb or !$adodb->IsConnected()) {
1040             $this->config->debugdb = $olddebugdb;
1041             $CFG->debug = $olddebug;
1042             ini_set('display_errors', $olddisplay);
1043             error_reporting($CFG->debug);
1044             ob_end_flush();
1046             echo $OUTPUT->notification('Cannot connect the database.', 'notifyproblem');
1047             return;
1048         }
1050         if (!empty($enroltable)) {
1051             $rs = $adodb->Execute("SELECT *
1052                                      FROM $enroltable");
1053             if (!$rs) {
1054                 echo $OUTPUT->notification('Can not read external enrol table.', 'notifyproblem');
1056             } else if ($rs->EOF) {
1057                 echo $OUTPUT->notification('External enrol table is empty.', 'notifyproblem');
1058                 $rs->Close();
1060             } else {
1061                 $fields_obj = $rs->FetchObj();
1062                 $columns = array_keys((array)$fields_obj);
1064                 echo $OUTPUT->notification('External enrolment table contains following columns:<br />'.implode(', ', $columns), 'notifysuccess');
1065                 $rs->Close();
1066             }
1067         }
1069         if (!empty($coursetable)) {
1070             $rs = $adodb->Execute("SELECT *
1071                                      FROM $coursetable");
1072             if (!$rs) {
1073                 echo $OUTPUT->notification('Can not read external course table.', 'notifyproblem');
1075             } else if ($rs->EOF) {
1076                 echo $OUTPUT->notification('External course table is empty.', 'notifyproblem');
1077                 $rs->Close();
1079             } else {
1080                 $fields_obj = $rs->FetchObj();
1081                 $columns = array_keys((array)$fields_obj);
1083                 echo $OUTPUT->notification('External course table contains following columns:<br />'.implode(', ', $columns), 'notifysuccess');
1084                 $rs->Close();
1085             }
1086         }
1088         $adodb->Close();
1090         $this->config->debugdb = $olddebugdb;
1091         $CFG->debug = $olddebug;
1092         ini_set('display_errors', $olddisplay);
1093         error_reporting($CFG->debug);
1094         ob_end_flush();
1095     }