c8881691134ae473a5f89758afc6c6215751d11f
[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      * Forces synchronisation of user enrolments with external database,
87      * does not create new courses.
88      *
89      * @param stdClass $user user record
90      * @return void
91      */
92     public function sync_user_enrolments($user) {
93         global $CFG, $DB;
95         // We do not create courses here intentionally because it requires full sync and is slow.
96         if (!$this->get_config('dbtype') or !$this->get_config('remoteenroltable') or !$this->get_config('remotecoursefield') or !$this->get_config('remoteuserfield')) {
97             return;
98         }
100         $table            = $this->get_config('remoteenroltable');
101         $coursefield      = trim($this->get_config('remotecoursefield'));
102         $userfield        = trim($this->get_config('remoteuserfield'));
103         $rolefield        = trim($this->get_config('remoterolefield'));
104         $otheruserfield   = trim($this->get_config('remoteotheruserfield'));
106         // Lowercased versions - necessary because we normalise the resultset with array_change_key_case().
107         $coursefield_l    = strtolower($coursefield);
108         $userfield_l      = strtolower($userfield);
109         $rolefield_l      = strtolower($rolefield);
110         $otheruserfieldlower = strtolower($otheruserfield);
112         $localrolefield   = $this->get_config('localrolefield');
113         $localuserfield   = $this->get_config('localuserfield');
114         $localcoursefield = $this->get_config('localcoursefield');
116         $unenrolaction    = $this->get_config('unenrolaction');
117         $defaultrole      = $this->get_config('defaultrole');
119         $ignorehidden     = $this->get_config('ignorehiddencourses');
121         if (!is_object($user) or !property_exists($user, 'id')) {
122             throw new coding_exception('Invalid $user parameter in sync_user_enrolments()');
123         }
125         if (!property_exists($user, $localuserfield)) {
126             debugging('Invalid $user parameter in sync_user_enrolments(), missing '.$localuserfield);
127             $user = $DB->get_record('user', array('id'=>$user->id));
128         }
130         // Create roles mapping.
131         $allroles = get_all_roles();
132         if (!isset($allroles[$defaultrole])) {
133             $defaultrole = 0;
134         }
135         $roles = array();
136         foreach ($allroles as $role) {
137             $roles[$role->$localrolefield] = $role->id;
138         }
140         $roleassigns = array();
141         $enrols = array();
142         $instances = array();
144         if (!$extdb = $this->db_init()) {
145             // Can not connect to database, sorry.
146             return;
147         }
149         // Read remote enrols and create instances.
150         $sql = $this->db_get_sql($table, array($userfield=>$user->$localuserfield), array(), false);
152         if ($rs = $extdb->Execute($sql)) {
153             if (!$rs->EOF) {
154                 while ($fields = $rs->FetchRow()) {
155                     $fields = array_change_key_case($fields, CASE_LOWER);
156                     $fields = $this->db_decode($fields);
158                     if (empty($fields[$coursefield_l])) {
159                         // Missing course info.
160                         continue;
161                     }
162                     if (!$course = $DB->get_record('course', array($localcoursefield=>$fields[$coursefield_l]), 'id,visible')) {
163                         continue;
164                     }
165                     if (!$course->visible and $ignorehidden) {
166                         continue;
167                     }
169                     if (empty($fields[$rolefield_l]) or !isset($roles[$fields[$rolefield_l]])) {
170                         if (!$defaultrole) {
171                             // Role is mandatory.
172                             continue;
173                         }
174                         $roleid = $defaultrole;
175                     } else {
176                         $roleid = $roles[$fields[$rolefield_l]];
177                     }
179                     $roleassigns[$course->id][$roleid] = $roleid;
180                     if (empty($fields[$otheruserfieldlower])) {
181                         $enrols[$course->id][$roleid] = $roleid;
182                     }
184                     if ($instance = $DB->get_record('enrol', array('courseid'=>$course->id, 'enrol'=>'database'), '*', IGNORE_MULTIPLE)) {
185                         $instances[$course->id] = $instance;
186                         continue;
187                     }
189                     $enrolid = $this->add_instance($course);
190                     $instances[$course->id] = $DB->get_record('enrol', array('id'=>$enrolid));
191                 }
192             }
193             $rs->Close();
194             $extdb->Close();
195         } else {
196             // Bad luck, something is wrong with the db connection.
197             $extdb->Close();
198             return;
199         }
201         // Enrol user into courses and sync roles.
202         foreach ($roleassigns as $courseid => $roles) {
203             if (!isset($instances[$courseid])) {
204                 // Ignored.
205                 continue;
206             }
207             $instance = $instances[$courseid];
209             if (isset($enrols[$courseid])) {
210                 if ($e = $DB->get_record('user_enrolments', array('userid' => $user->id, 'enrolid' => $instance->id))) {
211                     // Reenable enrolment when previously disable enrolment refreshed.
212                     if ($e->status == ENROL_USER_SUSPENDED) {
213                         $this->update_user_enrol($instance, $user->id, ENROL_USER_ACTIVE);
214                     }
215                 } else {
216                     $roleid = reset($enrols[$courseid]);
217                     $this->enrol_user($instance, $user->id, $roleid, 0, 0, ENROL_USER_ACTIVE);
218                 }
219             }
221             if (!$context = context_course::instance($instance->courseid, IGNORE_MISSING)) {
222                 // Weird.
223                 continue;
224             }
225             $current = $DB->get_records('role_assignments', array('contextid'=>$context->id, 'userid'=>$user->id, 'component'=>'enrol_database', 'itemid'=>$instance->id), '', 'id, roleid');
227             $existing = array();
228             foreach ($current as $r) {
229                 if (isset($roles[$r->roleid])) {
230                     $existing[$r->roleid] = $r->roleid;
231                 } else {
232                     role_unassign($r->roleid, $user->id, $context->id, 'enrol_database', $instance->id);
233                 }
234             }
235             foreach ($roles as $rid) {
236                 if (!isset($existing[$rid])) {
237                     role_assign($rid, $user->id, $context->id, 'enrol_database', $instance->id);
238                 }
239             }
240         }
242         // Unenrol as necessary.
243         $sql = "SELECT e.*, c.visible AS cvisible, ue.status AS ustatus
244                   FROM {enrol} e
245                   JOIN {course} c ON c.id = e.courseid
246                   JOIN {role_assignments} ra ON ra.itemid = e.id
247              LEFT JOIN {user_enrolments} ue ON ue.enrolid = e.id AND ue.userid = ra.userid
248                  WHERE ra.userid = :userid AND e.enrol = 'database'";
249         $rs = $DB->get_recordset_sql($sql, array('userid' => $user->id));
250         foreach ($rs as $instance) {
251             if (!$instance->cvisible and $ignorehidden) {
252                 continue;
253             }
255             if (!$context = context_course::instance($instance->courseid, IGNORE_MISSING)) {
256                 // Very weird.
257                 continue;
258             }
260             if (!empty($enrols[$instance->courseid])) {
261                 // We want this user enrolled.
262                 continue;
263             }
265             // Deal with enrolments removed from external table
266             if ($unenrolaction == ENROL_EXT_REMOVED_UNENROL) {
267                 $this->unenrol_user($instance, $user->id);
269             } else if ($unenrolaction == ENROL_EXT_REMOVED_KEEP) {
270                 // Keep - only adding enrolments.
272             } else if ($unenrolaction == ENROL_EXT_REMOVED_SUSPEND or $unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
273                 // Suspend users.
274                 if ($instance->ustatus != ENROL_USER_SUSPENDED) {
275                     $this->update_user_enrol($instance, $user->id, ENROL_USER_SUSPENDED);
276                 }
277                 if ($unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
278                     if (!empty($roleassigns[$instance->courseid])) {
279                         // We want this "other user" to keep their roles.
280                         continue;
281                     }
282                     role_unassign_all(array('contextid'=>$context->id, 'userid'=>$user->id, 'component'=>'enrol_database', 'itemid'=>$instance->id));
283                 }
284             }
285         }
286         $rs->close();
287     }
289     /**
290      * Forces synchronisation of all enrolments with external database.
291      *
292      * @param progress_trace $trace
293      * @param null|int $onecourse limit sync to one course only (used primarily in restore)
294      * @return int 0 means success, 1 db connect failure, 2 db read failure
295      */
296     public function sync_enrolments(progress_trace $trace, $onecourse = null) {
297         global $CFG, $DB;
299         // We do not create courses here intentionally because it requires full sync and is slow.
300         if (!$this->get_config('dbtype') or !$this->get_config('remoteenroltable') or !$this->get_config('remotecoursefield') or !$this->get_config('remoteuserfield')) {
301             $trace->output('User enrolment synchronisation skipped.');
302             $trace->finished();
303             return 0;
304         }
306         $trace->output('Starting user enrolment synchronisation...');
308         if (!$extdb = $this->db_init()) {
309             $trace->output('Error while communicating with external enrolment database');
310             $trace->finished();
311             return 1;
312         }
314         // We may need a lot of memory here.
315         core_php_time_limit::raise();
316         raise_memory_limit(MEMORY_HUGE);
318         $table            = $this->get_config('remoteenroltable');
319         $coursefield      = trim($this->get_config('remotecoursefield'));
320         $userfield        = trim($this->get_config('remoteuserfield'));
321         $rolefield        = trim($this->get_config('remoterolefield'));
322         $otheruserfield   = trim($this->get_config('remoteotheruserfield'));
324         // Lowercased versions - necessary because we normalise the resultset with array_change_key_case().
325         $coursefield_l    = strtolower($coursefield);
326         $userfield_l      = strtolower($userfield);
327         $rolefield_l      = strtolower($rolefield);
328         $otheruserfieldlower = strtolower($otheruserfield);
330         $localrolefield   = $this->get_config('localrolefield');
331         $localuserfield   = $this->get_config('localuserfield');
332         $localcoursefield = $this->get_config('localcoursefield');
334         $unenrolaction    = $this->get_config('unenrolaction');
335         $defaultrole      = $this->get_config('defaultrole');
337         // Create roles mapping.
338         $allroles = get_all_roles();
339         if (!isset($allroles[$defaultrole])) {
340             $defaultrole = 0;
341         }
342         $roles = array();
343         foreach ($allroles as $role) {
344             $roles[$role->$localrolefield] = $role->id;
345         }
347         if ($onecourse) {
348             $sql = "SELECT c.id, c.visible, c.$localcoursefield AS mapping, c.shortname, e.id AS enrolid
349                       FROM {course} c
350                  LEFT JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'database')
351                      WHERE c.id = :id";
352             if (!$course = $DB->get_record_sql($sql, array('id'=>$onecourse))) {
353                 // Course does not exist, nothing to sync.
354                 return 0;
355             }
356             if (empty($course->mapping)) {
357                 // We can not map to this course, sorry.
358                 return 0;
359             }
360             if (empty($course->enrolid)) {
361                 $course->enrolid = $this->add_instance($course);
362             }
363             $existing = array($course->mapping=>$course);
365             // Feel free to unenrol everybody, no safety tricks here.
366             $preventfullunenrol = false;
367             // Course being restored are always hidden, we have to ignore the setting here.
368             $ignorehidden = false;
370         } else {
371             // Get a list of courses to be synced that are in external table.
372             $externalcourses = array();
373             $sql = $this->db_get_sql($table, array(), array($coursefield), true);
374             if ($rs = $extdb->Execute($sql)) {
375                 if (!$rs->EOF) {
376                     while ($mapping = $rs->FetchRow()) {
377                         $mapping = reset($mapping);
378                         $mapping = $this->db_decode($mapping);
379                         if (empty($mapping)) {
380                             // invalid mapping
381                             continue;
382                         }
383                         $externalcourses[$mapping] = true;
384                     }
385                 }
386                 $rs->Close();
387             } else {
388                 $trace->output('Error reading data from the external enrolment table');
389                 $extdb->Close();
390                 return 2;
391             }
392             $preventfullunenrol = empty($externalcourses);
393             if ($preventfullunenrol and $unenrolaction == ENROL_EXT_REMOVED_UNENROL) {
394                 $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);
395             }
397             // First find all existing courses with enrol instance.
398             $existing = array();
399             $sql = "SELECT c.id, c.visible, c.$localcoursefield AS mapping, e.id AS enrolid, c.shortname
400                       FROM {course} c
401                       JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'database')";
402             $rs = $DB->get_recordset_sql($sql); // Watch out for idnumber duplicates.
403             foreach ($rs as $course) {
404                 if (empty($course->mapping)) {
405                     continue;
406                 }
407                 $existing[$course->mapping] = $course;
408                 unset($externalcourses[$course->mapping]);
409             }
410             $rs->close();
412             // Add necessary enrol instances that are not present yet.
413             $params = array();
414             $localnotempty = "";
415             if ($localcoursefield !== 'id') {
416                 $localnotempty =  "AND c.$localcoursefield <> :lcfe";
417                 $params['lcfe'] = '';
418             }
419             $sql = "SELECT c.id, c.visible, c.$localcoursefield AS mapping, c.shortname
420                       FROM {course} c
421                  LEFT JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'database')
422                      WHERE e.id IS NULL $localnotempty";
423             $rs = $DB->get_recordset_sql($sql, $params);
424             foreach ($rs as $course) {
425                 if (empty($course->mapping)) {
426                     continue;
427                 }
428                 if (!isset($externalcourses[$course->mapping])) {
429                     // Course not synced or duplicate.
430                     continue;
431                 }
432                 $course->enrolid = $this->add_instance($course);
433                 $existing[$course->mapping] = $course;
434                 unset($externalcourses[$course->mapping]);
435             }
436             $rs->close();
438             // Print list of missing courses.
439             if ($externalcourses) {
440                 $list = implode(', ', array_keys($externalcourses));
441                 $trace->output("error: following courses do not exist - $list", 1);
442                 unset($list);
443             }
445             // Free memory.
446             unset($externalcourses);
448             $ignorehidden = $this->get_config('ignorehiddencourses');
449         }
451         // Sync user enrolments.
452         $sqlfields = array($userfield);
453         if ($rolefield) {
454             $sqlfields[] = $rolefield;
455         }
456         if ($otheruserfield) {
457             $sqlfields[] = $otheruserfield;
458         }
459         foreach ($existing as $course) {
460             if ($ignorehidden and !$course->visible) {
461                 continue;
462             }
463             if (!$instance = $DB->get_record('enrol', array('id'=>$course->enrolid))) {
464                 continue; // Weird!
465             }
466             $context = context_course::instance($course->id);
468             // Get current list of enrolled users with their roles.
469             $currentroles  = array();
470             $currentenrols = array();
471             $currentstatus = array();
472             $usermapping   = array();
473             $sql = "SELECT u.$localuserfield AS mapping, u.id AS userid, ue.status, ra.roleid
474                       FROM {user} u
475                       JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.component = 'enrol_database' AND ra.itemid = :enrolid)
476                  LEFT JOIN {user_enrolments} ue ON (ue.userid = u.id AND ue.enrolid = ra.itemid)
477                      WHERE u.deleted = 0";
478             $params = array('enrolid'=>$instance->id);
479             if ($localuserfield === 'username') {
480                 $sql .= " AND u.mnethostid = :mnethostid";
481                 $params['mnethostid'] = $CFG->mnet_localhost_id;
482             }
483             $rs = $DB->get_recordset_sql($sql, $params);
484             foreach ($rs as $ue) {
485                 $currentroles[$ue->userid][$ue->roleid] = $ue->roleid;
486                 $usermapping[$ue->mapping] = $ue->userid;
488                 if (isset($ue->status)) {
489                     $currentenrols[$ue->userid][$ue->roleid] = $ue->roleid;
490                     $currentstatus[$ue->userid] = $ue->status;
491                 }
492             }
493             $rs->close();
495             // Get list of users that need to be enrolled and their roles.
496             $requestedroles  = array();
497             $requestedenrols = array();
498             $sql = $this->db_get_sql($table, array($coursefield=>$course->mapping), $sqlfields);
499             if ($rs = $extdb->Execute($sql)) {
500                 if (!$rs->EOF) {
501                     $usersearch = array('deleted' => 0);
502                     if ($localuserfield === 'username') {
503                         $usersearch['mnethostid'] = $CFG->mnet_localhost_id;
504                     }
505                     while ($fields = $rs->FetchRow()) {
506                         $fields = array_change_key_case($fields, CASE_LOWER);
507                         if (empty($fields[$userfield_l])) {
508                             $trace->output("error: skipping user without mandatory $localuserfield in course '$course->mapping'", 1);
509                             continue;
510                         }
511                         $mapping = $fields[$userfield_l];
512                         if (!isset($usermapping[$mapping])) {
513                             $usersearch[$localuserfield] = $mapping;
514                             if (!$user = $DB->get_record('user', $usersearch, 'id', IGNORE_MULTIPLE)) {
515                                 $trace->output("error: skipping unknown user $localuserfield '$mapping' in course '$course->mapping'", 1);
516                                 continue;
517                             }
518                             $usermapping[$mapping] = $user->id;
519                             $userid = $user->id;
520                         } else {
521                             $userid = $usermapping[$mapping];
522                         }
523                         if (empty($fields[$rolefield_l]) or !isset($roles[$fields[$rolefield_l]])) {
524                             if (!$defaultrole) {
525                                 $trace->output("error: skipping user '$userid' in course '$course->mapping' - missing course and default role", 1);
526                                 continue;
527                             }
528                             $roleid = $defaultrole;
529                         } else {
530                             $roleid = $roles[$fields[$rolefield_l]];
531                         }
533                         $requestedroles[$userid][$roleid] = $roleid;
534                         if (empty($fields[$otheruserfieldlower])) {
535                             $requestedenrols[$userid][$roleid] = $roleid;
536                         }
537                     }
538                 }
539                 $rs->Close();
540             } else {
541                 $trace->output("error: skipping course '$course->mapping' - could not match with external database", 1);
542                 continue;
543             }
544             unset($usermapping);
546             // Enrol all users and sync roles.
547             foreach ($requestedenrols as $userid => $userroles) {
548                 foreach ($userroles as $roleid) {
549                     if (empty($currentenrols[$userid])) {
550                         $this->enrol_user($instance, $userid, $roleid, 0, 0, ENROL_USER_ACTIVE);
551                         $currentroles[$userid][$roleid] = $roleid;
552                         $currentenrols[$userid][$roleid] = $roleid;
553                         $currentstatus[$userid] = ENROL_USER_ACTIVE;
554                         $trace->output("enrolling: $userid ==> $course->shortname as ".$allroles[$roleid]->shortname, 1);
555                     }
556                 }
558                 // Reenable enrolment when previously disable enrolment refreshed.
559                 if ($currentstatus[$userid] == ENROL_USER_SUSPENDED) {
560                     $this->update_user_enrol($instance, $userid, ENROL_USER_ACTIVE);
561                     $trace->output("unsuspending: $userid ==> $course->shortname", 1);
562                 }
563             }
565             foreach ($requestedroles as $userid => $userroles) {
566                 // Assign extra roles.
567                 foreach ($userroles as $roleid) {
568                     if (empty($currentroles[$userid][$roleid])) {
569                         role_assign($roleid, $userid, $context->id, 'enrol_database', $instance->id);
570                         $currentroles[$userid][$roleid] = $roleid;
571                         $trace->output("assigning roles: $userid ==> $course->shortname as ".$allroles[$roleid]->shortname, 1);
572                     }
573                 }
575                 // Unassign removed roles.
576                 foreach ($currentroles[$userid] as $cr) {
577                     if (empty($userroles[$cr])) {
578                         role_unassign($cr, $userid, $context->id, 'enrol_database', $instance->id);
579                         unset($currentroles[$userid][$cr]);
580                         $trace->output("unsassigning roles: $userid ==> $course->shortname", 1);
581                     }
582                 }
584                 unset($currentroles[$userid]);
585             }
587             foreach ($currentroles as $userid => $userroles) {
588                 // These are roles that exist only in Moodle, not the external database
589                 // so make sure the unenrol actions will handle them by setting status.
590                 $currentstatus += array($userid => ENROL_USER_ACTIVE);
591             }
593             // Deal with enrolments removed from external table.
594             if ($unenrolaction == ENROL_EXT_REMOVED_UNENROL) {
595                 if (!$preventfullunenrol) {
596                     // Unenrol.
597                     foreach ($currentstatus as $userid => $status) {
598                         if (isset($requestedenrols[$userid])) {
599                             continue;
600                         }
601                         $this->unenrol_user($instance, $userid);
602                         $trace->output("unenrolling: $userid ==> $course->shortname", 1);
603                     }
604                 }
606             } else if ($unenrolaction == ENROL_EXT_REMOVED_KEEP) {
607                 // Keep - only adding enrolments.
609             } else if ($unenrolaction == ENROL_EXT_REMOVED_SUSPEND or $unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
610                 // Suspend enrolments.
611                 foreach ($currentstatus as $userid => $status) {
612                     if (isset($requestedenrols[$userid])) {
613                         continue;
614                     }
615                     if ($status != ENROL_USER_SUSPENDED) {
616                         $this->update_user_enrol($instance, $userid, ENROL_USER_SUSPENDED);
617                         $trace->output("suspending: $userid ==> $course->shortname", 1);
618                     }
619                     if ($unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
620                         if (isset($requestedroles[$userid])) {
621                             // We want this "other user" to keep their roles.
622                             continue;
623                         }
624                         role_unassign_all(array('contextid'=>$context->id, 'userid'=>$userid, 'component'=>'enrol_database', 'itemid'=>$instance->id));
626                         $trace->output("unsassigning all roles: $userid ==> $course->shortname", 1);
627                     }
628                 }
629             }
630         }
632         // Close db connection.
633         $extdb->Close();
635         $trace->output('...user enrolment synchronisation finished.');
636         $trace->finished();
638         return 0;
639     }
641     /**
642      * Performs a full sync with external database.
643      *
644      * First it creates new courses if necessary, then
645      * enrols and unenrols users.
646      *
647      * @param progress_trace $trace
648      * @return int 0 means success, 1 db connect failure, 4 db read failure
649      */
650     public function sync_courses(progress_trace $trace) {
651         global $CFG, $DB;
653         // Make sure we sync either enrolments or courses.
654         if (!$this->get_config('dbtype') or !$this->get_config('newcoursetable') or !$this->get_config('newcoursefullname') or !$this->get_config('newcourseshortname')) {
655             $trace->output('Course synchronisation skipped.');
656             $trace->finished();
657             return 0;
658         }
660         $trace->output('Starting course synchronisation...');
662         // We may need a lot of memory here.
663         core_php_time_limit::raise();
664         raise_memory_limit(MEMORY_HUGE);
666         if (!$extdb = $this->db_init()) {
667             $trace->output('Error while communicating with external enrolment database');
668             $trace->finished();
669             return 1;
670         }
672         $table     = $this->get_config('newcoursetable');
673         $fullname  = trim($this->get_config('newcoursefullname'));
674         $shortname = trim($this->get_config('newcourseshortname'));
675         $idnumber  = trim($this->get_config('newcourseidnumber'));
676         $category  = trim($this->get_config('newcoursecategory'));
678         // Lowercased versions - necessary because we normalise the resultset with array_change_key_case().
679         $fullname_l  = strtolower($fullname);
680         $shortname_l = strtolower($shortname);
681         $idnumber_l  = strtolower($idnumber);
682         $category_l  = strtolower($category);
684         $localcategoryfield = $this->get_config('localcategoryfield', 'id');
685         $defaultcategory    = $this->get_config('defaultcategory');
687         if (!$DB->record_exists('course_categories', array('id'=>$defaultcategory))) {
688             $trace->output("default course category does not exist!", 1);
689             $categories = $DB->get_records('course_categories', array(), 'sortorder', 'id', 0, 1);
690             $first = reset($categories);
691             $defaultcategory = $first->id;
692         }
694         $sqlfields = array($fullname, $shortname);
695         if ($category) {
696             $sqlfields[] = $category;
697         }
698         if ($idnumber) {
699             $sqlfields[] = $idnumber;
700         }
701         $sql = $this->db_get_sql($table, array(), $sqlfields, true);
702         $createcourses = array();
703         if ($rs = $extdb->Execute($sql)) {
704             if (!$rs->EOF) {
705                 while ($fields = $rs->FetchRow()) {
706                     $fields = array_change_key_case($fields, CASE_LOWER);
707                     $fields = $this->db_decode($fields);
708                     if (empty($fields[$shortname_l]) or empty($fields[$fullname_l])) {
709                         $trace->output('error: invalid external course record, shortname and fullname are mandatory: ' . json_encode($fields), 1); // Hopefully every geek can read JS, right?
710                         continue;
711                     }
712                     if ($DB->record_exists('course', array('shortname'=>$fields[$shortname_l]))) {
713                         // Already exists, skip.
714                         continue;
715                     }
716                     // Allow empty idnumber but not duplicates.
717                     if ($idnumber and $fields[$idnumber_l] !== '' and $fields[$idnumber_l] !== null and $DB->record_exists('course', array('idnumber'=>$fields[$idnumber_l]))) {
718                         $trace->output('error: duplicate idnumber, can not create course: '.$fields[$shortname_l].' ['.$fields[$idnumber_l].']', 1);
719                         continue;
720                     }
721                     $course = new stdClass();
722                     $course->fullname  = $fields[$fullname_l];
723                     $course->shortname = $fields[$shortname_l];
724                     $course->idnumber  = $idnumber ? $fields[$idnumber_l] : '';
725                     if ($category) {
726                         if (empty($fields[$category_l])) {
727                             // Empty category means use default.
728                             $course->category = $defaultcategory;
729                         } else if ($coursecategory = $DB->get_record('course_categories', array($localcategoryfield=>$fields[$category_l]), 'id')) {
730                             // Yay, correctly specified category!
731                             $course->category = $coursecategory->id;
732                             unset($coursecategory);
733                         } else {
734                             // Bad luck, better not continue because unwanted ppl might get access to course in different category.
735                             $trace->output('error: invalid category '.$localcategoryfield.', can not create course: '.$fields[$shortname_l], 1);
736                             continue;
737                         }
738                     } else {
739                         $course->category = $defaultcategory;
740                     }
741                     $createcourses[] = $course;
742                 }
743             }
744             $rs->Close();
745         } else {
746             $extdb->Close();
747             $trace->output('Error reading data from the external course table');
748             $trace->finished();
749             return 4;
750         }
751         if ($createcourses) {
752             require_once("$CFG->dirroot/course/lib.php");
754             $templatecourse = $this->get_config('templatecourse');
756             $template = false;
757             if ($templatecourse) {
758                 if ($template = $DB->get_record('course', array('shortname'=>$templatecourse))) {
759                     $template = fullclone(course_get_format($template)->get_course());
760                     if (!isset($template->numsections)) {
761                         $template->numsections = course_get_format($template)->get_last_section_number();
762                     }
763                     unset($template->id);
764                     unset($template->fullname);
765                     unset($template->shortname);
766                     unset($template->idnumber);
767                 } else {
768                     $trace->output("can not find template for new course!", 1);
769                 }
770             }
771             if (!$template) {
772                 $courseconfig = get_config('moodlecourse');
773                 $template = new stdClass();
774                 $template->summary        = '';
775                 $template->summaryformat  = FORMAT_HTML;
776                 $template->format         = $courseconfig->format;
777                 $template->newsitems      = $courseconfig->newsitems;
778                 $template->showgrades     = $courseconfig->showgrades;
779                 $template->showreports    = $courseconfig->showreports;
780                 $template->maxbytes       = $courseconfig->maxbytes;
781                 $template->groupmode      = $courseconfig->groupmode;
782                 $template->groupmodeforce = $courseconfig->groupmodeforce;
783                 $template->visible        = $courseconfig->visible;
784                 $template->lang           = $courseconfig->lang;
785                 $template->groupmodeforce = $courseconfig->groupmodeforce;
786                 $template->startdate      = usergetmidnight(time());
787                 if ($courseconfig->courseenddateenabled) {
788                     $template->enddate    = usergetmidnight(time()) + $courseconfig->courseduration;
789                 }
790             }
792             foreach ($createcourses as $fields) {
793                 $newcourse = clone($template);
794                 $newcourse->fullname  = $fields->fullname;
795                 $newcourse->shortname = $fields->shortname;
796                 $newcourse->idnumber  = $fields->idnumber;
797                 $newcourse->category  = $fields->category;
799                 // Detect duplicate data once again, above we can not find duplicates
800                 // in external data using DB collation rules...
801                 if ($DB->record_exists('course', array('shortname' => $newcourse->shortname))) {
802                     $trace->output("can not insert new course, duplicate shortname detected: ".$newcourse->shortname, 1);
803                     continue;
804                 } else if (!empty($newcourse->idnumber) and $DB->record_exists('course', array('idnumber' => $newcourse->idnumber))) {
805                     $trace->output("can not insert new course, duplicate idnumber detected: ".$newcourse->idnumber, 1);
806                     continue;
807                 }
808                 $c = create_course($newcourse);
809                 $trace->output("creating course: $c->id, $c->fullname, $c->shortname, $c->idnumber, $c->category", 1);
810             }
812             unset($createcourses);
813             unset($template);
814         }
816         // Close db connection.
817         $extdb->Close();
819         $trace->output('...course synchronisation finished.');
820         $trace->finished();
822         return 0;
823     }
825     protected function db_get_sql($table, array $conditions, array $fields, $distinct = false, $sort = "") {
826         $fields = $fields ? implode(',', $fields) : "*";
827         $where = array();
828         if ($conditions) {
829             foreach ($conditions as $key=>$value) {
830                 $value = $this->db_encode($this->db_addslashes($value));
832                 $where[] = "$key = '$value'";
833             }
834         }
835         $where = $where ? "WHERE ".implode(" AND ", $where) : "";
836         $sort = $sort ? "ORDER BY $sort" : "";
837         $distinct = $distinct ? "DISTINCT" : "";
838         $sql = "SELECT $distinct $fields
839                   FROM $table
840                  $where
841                   $sort";
843         return $sql;
844     }
846     /**
847      * Tries to make connection to the external database.
848      *
849      * @return null|ADONewConnection
850      */
851     protected function db_init() {
852         global $CFG;
854         require_once($CFG->libdir.'/adodb/adodb.inc.php');
856         // Connect to the external database (forcing new connection).
857         $extdb = ADONewConnection($this->get_config('dbtype'));
858         if ($this->get_config('debugdb')) {
859             $extdb->debug = true;
860             ob_start(); // Start output buffer to allow later use of the page headers.
861         }
863         // The dbtype my contain the new connection URL, so make sure we are not connected yet.
864         if (!$extdb->IsConnected()) {
865             $result = $extdb->Connect($this->get_config('dbhost'), $this->get_config('dbuser'), $this->get_config('dbpass'), $this->get_config('dbname'), true);
866             if (!$result) {
867                 return null;
868             }
869         }
871         $extdb->SetFetchMode(ADODB_FETCH_ASSOC);
872         if ($this->get_config('dbsetupsql')) {
873             $extdb->Execute($this->get_config('dbsetupsql'));
874         }
875         return $extdb;
876     }
878     protected function db_addslashes($text) {
879         // Use custom made function for now - it is better to not rely on adodb or php defaults.
880         if ($this->get_config('dbsybasequoting')) {
881             $text = str_replace('\\', '\\\\', $text);
882             $text = str_replace(array('\'', '"', "\0"), array('\\\'', '\\"', '\\0'), $text);
883         } else {
884             $text = str_replace("'", "''", $text);
885         }
886         return $text;
887     }
889     protected function db_encode($text) {
890         $dbenc = $this->get_config('dbencoding');
891         if (empty($dbenc) or $dbenc == 'utf-8') {
892             return $text;
893         }
894         if (is_array($text)) {
895             foreach($text as $k=>$value) {
896                 $text[$k] = $this->db_encode($value);
897             }
898             return $text;
899         } else {
900             return core_text::convert($text, 'utf-8', $dbenc);
901         }
902     }
904     protected function db_decode($text) {
905         $dbenc = $this->get_config('dbencoding');
906         if (empty($dbenc) or $dbenc == 'utf-8') {
907             return $text;
908         }
909         if (is_array($text)) {
910             foreach($text as $k=>$value) {
911                 $text[$k] = $this->db_decode($value);
912             }
913             return $text;
914         } else {
915             return core_text::convert($text, $dbenc, 'utf-8');
916         }
917     }
919     /**
920      * Automatic enrol sync executed during restore.
921      * @param stdClass $course course record
922      */
923     public function restore_sync_course($course) {
924         $trace = new null_progress_trace();
925         $this->sync_enrolments($trace, $course->id);
926     }
928     /**
929      * Restore instance and map settings.
930      *
931      * @param restore_enrolments_structure_step $step
932      * @param stdClass $data
933      * @param stdClass $course
934      * @param int $oldid
935      */
936     public function restore_instance(restore_enrolments_structure_step $step, stdClass $data, $course, $oldid) {
937         global $DB;
939         if ($instance = $DB->get_record('enrol', array('courseid'=>$course->id, 'enrol'=>$this->get_name()))) {
940             $instanceid = $instance->id;
941         } else {
942             $instanceid = $this->add_instance($course);
943         }
944         $step->set_mapping('enrol', $oldid, $instanceid);
945     }
947     /**
948      * Restore user enrolment.
949      *
950      * @param restore_enrolments_structure_step $step
951      * @param stdClass $data
952      * @param stdClass $instance
953      * @param int $oldinstancestatus
954      * @param int $userid
955      */
956     public function restore_user_enrolment(restore_enrolments_structure_step $step, $data, $instance, $userid, $oldinstancestatus) {
957         global $DB;
959         if ($this->get_config('unenrolaction') == ENROL_EXT_REMOVED_UNENROL) {
960             // Enrolments were already synchronised in restore_instance(), we do not want any suspended leftovers.
961             return;
962         }
963         if (!$DB->record_exists('user_enrolments', array('enrolid'=>$instance->id, 'userid'=>$userid))) {
964             $this->enrol_user($instance, $userid, null, 0, 0, ENROL_USER_SUSPENDED);
965         }
966     }
968     /**
969      * Restore role assignment.
970      *
971      * @param stdClass $instance
972      * @param int $roleid
973      * @param int $userid
974      * @param int $contextid
975      */
976     public function restore_role_assignment($instance, $roleid, $userid, $contextid) {
977         if ($this->get_config('unenrolaction') == ENROL_EXT_REMOVED_UNENROL or $this->get_config('unenrolaction') == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
978             // Role assignments were already synchronised in restore_instance(), we do not want any leftovers.
979             return;
980         }
981         role_assign($roleid, $userid, $contextid, 'enrol_'.$this->get_name(), $instance->id);
982     }
984     /**
985      * Test plugin settings, print info to output.
986      */
987     public function test_settings() {
988         global $CFG, $OUTPUT;
990         // NOTE: this is not localised intentionally, admins are supposed to understand English at least a bit...
992         raise_memory_limit(MEMORY_HUGE);
994         $this->load_config();
996         $enroltable = $this->get_config('remoteenroltable');
997         $coursetable = $this->get_config('newcoursetable');
999         if (empty($enroltable)) {
1000             echo $OUTPUT->notification('External enrolment table not specified.', 'notifyproblem');
1001         }
1003         if (empty($coursetable)) {
1004             echo $OUTPUT->notification('External course table not specified.', 'notifyproblem');
1005         }
1007         if (empty($coursetable) and empty($enroltable)) {
1008             return;
1009         }
1011         $olddebug = $CFG->debug;
1012         $olddisplay = ini_get('display_errors');
1013         ini_set('display_errors', '1');
1014         $CFG->debug = DEBUG_DEVELOPER;
1015         $olddebugdb = $this->config->debugdb;
1016         $this->config->debugdb = 1;
1017         error_reporting($CFG->debug);
1019         $adodb = $this->db_init();
1021         if (!$adodb or !$adodb->IsConnected()) {
1022             $this->config->debugdb = $olddebugdb;
1023             $CFG->debug = $olddebug;
1024             ini_set('display_errors', $olddisplay);
1025             error_reporting($CFG->debug);
1026             ob_end_flush();
1028             echo $OUTPUT->notification('Cannot connect the database.', 'notifyproblem');
1029             return;
1030         }
1032         if (!empty($enroltable)) {
1033             $rs = $adodb->Execute("SELECT *
1034                                      FROM $enroltable");
1035             if (!$rs) {
1036                 echo $OUTPUT->notification('Can not read external enrol table.', 'notifyproblem');
1038             } else if ($rs->EOF) {
1039                 echo $OUTPUT->notification('External enrol table is empty.', 'notifyproblem');
1040                 $rs->Close();
1042             } else {
1043                 $fields_obj = $rs->FetchObj();
1044                 $columns = array_keys((array)$fields_obj);
1046                 echo $OUTPUT->notification('External enrolment table contains following columns:<br />'.implode(', ', $columns), 'notifysuccess');
1047                 $rs->Close();
1048             }
1049         }
1051         if (!empty($coursetable)) {
1052             $rs = $adodb->Execute("SELECT *
1053                                      FROM $coursetable");
1054             if (!$rs) {
1055                 echo $OUTPUT->notification('Can not read external course table.', 'notifyproblem');
1057             } else if ($rs->EOF) {
1058                 echo $OUTPUT->notification('External course table is empty.', 'notifyproblem');
1059                 $rs->Close();
1061             } else {
1062                 $fields_obj = $rs->FetchObj();
1063                 $columns = array_keys((array)$fields_obj);
1065                 echo $OUTPUT->notification('External course table contains following columns:<br />'.implode(', ', $columns), 'notifysuccess');
1066                 $rs->Close();
1067             }
1068         }
1070         $adodb->Close();
1072         $this->config->debugdb = $olddebugdb;
1073         $CFG->debug = $olddebug;
1074         ini_set('display_errors', $olddisplay);
1075         error_reporting($CFG->debug);
1076         ob_end_flush();
1077     }