MDL-22780 sync ext db enrol when user logs in - except admins in case something goes...
[moodle.git] / enrol / database / lib.php
1 <?php
3 // This file is part of Moodle - http://moodle.org/
4 //
5 // Moodle is free software: you can redistribute it and/or modify
6 // it under the terms of the GNU General Public License as published by
7 // the Free Software Foundation, either version 3 of the License, or
8 // (at your option) any later version.
9 //
10 // Moodle is distributed in the hope that it will be useful,
11 // but WITHOUT ANY WARRANTY; without even the implied warranty of
12 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
13 // GNU General Public License for more details.
14 //
15 // You should have received a copy of the GNU General Public License
16 // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
18 /**
19  * Database enrolment plugin.
20  *
21  * This plugin synchronises enrolment and roles with external database table.
22  *
23  * @package   enrol_database
24  * @copyright 2010 Petr Skoda {@link http://skodak.org}
25  * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
26  */
28 /**
29  * Database enrolment plugin implementation.
30  * @author  Petr Skoda - based on code by Martin Dougiamas, Martin Langhoff and others
31  * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
32  */
33 class enrol_database_plugin extends enrol_plugin {
34     /**
35      * Is it possible to delete enrol instance via standard UI?
36      *
37      * @param object $instance
38      * @return bool
39      */
40     public function instance_deleteable($instance) {
41         if (!enrol_is_enabled('database')) {
42             return true;
43         }
44         if (!$this->get_config('dbtype') or !$this->get_config('dbhost') or !$this->get_config('remoteenroltable') or !$this->get_config('remotecoursefield') or !$this->get_config('remoteuserfield')) {
45             return true;
46         }
48         //TODO: connect to external system and make sure no users are to be enrolled in this course
49         return false;
50     }
52     /**
53      * Forces synchronisation of user enrolments with external database,
54      * does not create new courses.
55      *
56      * @param object $user user record
57      * @return void
58      */
59     public function sync_user_enrolments($user) {
60         global $CFG, $DB;
62         // we do not create courses here intentionally because it requires full sync and is slow
63         if (!$this->get_config('dbtype') or !$this->get_config('dbhost') or !$this->get_config('remoteenroltable') or !$this->get_config('remotecoursefield') or !$this->get_config('remoteuserfield')) {
64             return;
65         }
67         $table            = $this->get_config('remoteenroltable');
68         $coursefield      = strtolower($this->get_config('remotecoursefield'));
69         $userfield        = strtolower($this->get_config('remoteuserfield'));
70         $rolefield        = strtolower($this->get_config('remoterolefield'));
72         $localrolefield   = $this->get_config('localrolefield');
73         $localuserfield   = $this->get_config('localuserfield');
74         $localcoursefiled = $this->get_config('localcoursefield');
76         $unenrolaction    = $this->get_config('unenrolaction');
77         $defaultrole      = $this->get_config('defaultrole');
79         $ignorehidden     = $this->get_config('ignorehiddencourses');
81         // create roles mapping
82         $allroles = get_all_roles();
83         if (!isset($allroles[$defaultrole])) {
84             $defaultrole = 0;
85         }
86         $roles = array();
87         foreach ($allroles as $role) {
88             $roles[$role->$localrolefield] = $role->id;
89         }
91         $enrols = array();
92         $instances = array();
94         $extdb = $this->db_init();
96         // read remote enrols and create instances
97         $sql = $this->db_get_sql($table, array($userfield=>$user->$localuserfield), array(), false);
99         if ($rs = $extdb->Execute($sql)) {
100             if (!$rs->EOF) {
101                 while ($fields = $rs->FetchRow()) {
102                     $fields = $this->db_decode($fields);
104                     if (empty($fields[$coursefield])) {
105                         // missing course info
106                         continue;
107                     }
108                     if (!$course = $DB->get_record('course', array($localcoursefiled=>$fields[$coursefield]), 'id,visible')) {
109                         continue;
110                     }
111                     if (!$course->visible and $ignorehidden) {
112                         continue;
113                     }
115                     if (empty($fields[$rolefield]) or !isset($roles[$fields[$rolefield]])) {
116                         if (!$defaultrole) {
117                             // role is mandatory
118                             continue;
119                         }
120                         $roleid = $defaultrole;
121                     } else {
122                         $roleid = $roles[$fields[$rolefield]];
123                     }
125                     if (empty($enrols[$course->id])) {
126                         $enrols[$course->id] = array();
127                     }
128                     $enrols[$course->id][] = $roleid;
130                     if ($instance = $DB->get_record('enrol', array('courseid'=>$course->id, 'enrol'=>'database'), '*', IGNORE_MULTIPLE)) {
131                         $instances[$course->id] = $instance;
132                         continue;
133                     }
135                     $enrolid = $this->add_instance($course);
136                     $instances[$course->id] = $DB->get_record('enrol', array('id'=>$enrolid));
137                 }
138             }
139             $rs->Close();
140             $extdb->Close();
141         } else {
142             // bad luck, something is wrong with the db connection
143             $extdb->Close();
144             return;
145         }
147         // enrol user into courses and sync roles
148         foreach ($enrols as $courseid => $roles) {
149             if (!isset($instances[$courseid])) {
150                 // ignored
151                 continue;
152             }
153             $instance = $instances[$courseid];
155             if ($e = $DB->get_record('user_enrolments', array('userid'=>$user->id, 'enrolid'=>$instance->id))) {
156                 // reenable enrolment when previously disable enrolment refreshed
157                 if ($e->status == ENROL_USER_SUSPENDED) {
158                     $DB->set_field('user_enrolments', 'status', ENROL_USER_ACTIVE, array('enrolid'=>$instance->id, 'userid'=>$user->id));
159                 }
160             } else {
161                 $roleid = reset($roles);
162                 $this->enrol_user($instance, $user->id, $roleid);
163             }
165             if (!$context = get_context_instance(CONTEXT_COURSE, $instance->courseid)) {
166                 //weird
167                 continue;
168             }
169             $current = $DB->get_records('role_assignments', array('contextid'=>$context->id, 'userid'=>$user->id, 'component'=>'enrol_database', 'itemid'=>$instance->id), '', 'id, roleid');
171             $existing = array();
172             foreach ($current as $r) {
173                 if (in_array($r->id, $roles)) {
174                     $existing[$r->roleid] = $r->roleid;
175                 } else {
176                     role_unassign($r->roleid, $user->id, $context->id, 'enrol_database', $instance->id);
177                 }
178             }
179             foreach ($roles as $rid) {
180                 if (!isset($existing[$rid])) {
181                     role_assign($rid, $user->id, $context->id, 'enrol_database', $instance->id);
182                 }
183             }
184         }
186         // unenrol as necessary
187         $sql = "SELECT e.*, c.visible AS cvisible, ue.status AS ustatus
188                   FROM {enrol} e
189                   JOIN {user_enrolments} ue ON ue.enrolid = e.id
190                   JOIN {course} c ON c.id = e.courseid
191                  WHERE ue.userid = :userid AND e.enrol = 'database'";
192         $rs = $DB->get_recordset_sql($sql, array('userid'=>$user->id));
193         foreach ($rs as $instance) {
194             if (!$instance->cvisible and $ignorehidden) {
195                 continue;
196             }
198             if (!$context = get_context_instance(CONTEXT_COURSE, $instance->courseid)) {
199                 //weird
200                 continue;
201             }
203             if (!empty($enrols[$instance->courseid])) {
204                 // we want this user enrolled
205                 continue;
206             }
208             // deal with enrolments removed from external table
209             if ($unenrolaction == ENROL_EXT_REMOVED_UNENROL) {
210                 // unenrol
211                 $this->unenrol_user($instance, $user->id);
213             } else if ($unenrolaction == ENROL_EXT_REMOVED_KEEP) {
214                 // keep - only adding enrolments
216             } else if ($unenrolaction == ENROL_EXT_REMOVED_SUSPEND or $unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
217                 // disable
218                 if ($instance->ustatus != ENROL_USER_SUSPENDED) {
219                     $DB->set_field('user_enrolments', 'status', ENROL_USER_SUSPENDED, array('enrolid'=>$instance->id, 'userid'=>$user->id));
220                 }
221                 if ($unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
222                     role_unassign_all(array('contextid'=>$context->id, 'userid'=>$user->id, 'component'=>'enrol_database', 'itemid'=>$instance->id));
223                 }
224             }
225         }
226         $rs->close();
227     }
229     /**
230      * Forces synchronisation of all enrolments with external database.
231      *
232      * @return void
233      */
234     public function sync_enrolments() {
235         global $CFG, $DB;
237         // we do not create courses here intentionally because it requires full sync and is slow
238         if (!$this->get_config('dbtype') or !$this->get_config('dbhost') or !$this->get_config('remoteenroltable') or !$this->get_config('remotecoursefield') or !$this->get_config('remoteuserfield')) {
239             return;
240         }
242         // we may need a lot of memory here
243         @set_time_limit(0);
244         @raise_memory_limit("512M");
246         $extdb = $this->db_init();
248         // second step is to sync instances and users
249         $table            = $this->get_config('remoteenroltable');
250         $coursefield      = strtolower($this->get_config('remotecoursefield'));
251         $userfield        = strtolower($this->get_config('remoteuserfield'));
252         $rolefield        = strtolower($this->get_config('remoterolefield'));
254         $localrolefield   = $this->get_config('localrolefield');
255         $localuserfield   = $this->get_config('localuserfield');
256         $localcoursefiled = $this->get_config('localcoursefield');
258         $unenrolaction    = $this->get_config('unenrolaction');
259         $defaultrole      = $this->get_config('defaultrole');
261         // create roles mapping
262         $allroles = get_all_roles();
263         if (!isset($allroles[$defaultrole])) {
264             $defaultrole = 0;
265         }
266         $roles = array();
267         foreach ($allroles as $role) {
268             $roles[$role->$localrolefield] = $role->id;
269         }
271         // first find all existing courses with enrol instance
272         $sql = "SELECT c.id, c.visible, c.$localcoursefiled AS mapping, e.id AS enrolid
273                   FROM {course} c
274                   JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'database')";
275         $existing = array();
276         $rs = $DB->get_recordset_sql($sql); // watch out for idnumber duplicates
277         foreach ($rs as $course) {
278             if (empty($course->mapping)) {
279                 continue;
280             }
281             $existing[$course->mapping] = $course;
282         }
283         $rs->close();
285         // add necessary enrol instances that are not present yet;
286         $sql = $this->db_get_sql($table, array(), array($coursefield), true);
287         if ($rs = $extdb->Execute($sql)) {
288             if (!$rs->EOF) {
289                 $sql = "SELECT c.id, c.visible
290                           FROM {course} c
291                           JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'database')
292                          WHERE c.$localcoursefiled = :mapping";
293                 $params = array();
294                 while ($mapping = $rs->FetchRow()) {
295                     $mapping = reset($mapping);
296                     $mapping = $this->db_decode($mapping);
297                     if (!empty($mapping) and !isset($existing[$mapping])) {
298                         $params['mapping'] = $mapping;
299                         if ($course = $DB->get_record_sql($sql, $params, IGNORE_MULTIPLE)) {
300                             $new = new object();
301                             $new->id      = $course->id;
302                             $new->visible = $course->visible;
303                             $new->mapping = $mapping;
304                             $new->enrolid = $this->add_instance($course);
305                             $existing[$mapping] = $new;
306                         }
307                     }
308                 }
309             }
310             $rs->Close();
311         } else {
312             debugging('Error while communicating with external enrolment database');
313             $extdb->Close();
314             return;
315         }
317         // sync enrolments
318         $ignorehidden = $this->get_config('ignorehiddencourses');
319         $fields = array($userfield);
320         if ($rolefield) {
321             $fields[] = $rolefield;
322         }
323         foreach ($existing as $course) {
324             if ($ignorehidden and !$course->visible) {
325                 continue;
326             }
327             if (!$instance = $DB->get_record('enrol', array('id'=>$course->enrolid))) {
328                 continue; //weird
329             }
330             $context = get_context_instance(CONTEXT_COURSE, $course->id);
332             // get current list of enrolled users with their roles
333             $current_roles  = array();
334             $current_status = array();
335             $user_mapping   = array();
336             $sql = "SELECT u.$localuserfield AS mapping, u.id, ue.status, ue.userid, ra.roleid
337                       FROM {user} u
338                       JOIN {user_enrolments} ue ON (ue.userid = u.id AND ue.enrolid = :enrolid)
339                       JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.itemid = ue.enrolid AND ra.component = 'enrol_database')
340                      WHERE u.deleted = 0";
341             $params = array('enrolid'=>$instance->id);
342             if ($localuserfield === 'username') {
343                 $sql .= " AND u.mnethostid = :mnethostid";
344                 $params['mnethostid'] = $CFG->mnet_localhost_id;
345             }
346             $rs = $DB->get_recordset_sql($sql, $params);
347             foreach ($rs as $ue) {
348                 $current_roles[$ue->userid][$ue->roleid] = $ue->roleid;
349                 $current_status[$ue->userid] = $ue->status;
350                 $user_mapping[$ue->mapping] = $ue->userid;
351             }
352             $rs->close();
354             // get list of users that need to be enrolled and their roles
355             $requested_roles = array();
356             $sql = $this->db_get_sql($table, array($coursefield=>$course->mapping), $fields);
357             if ($rs = $extdb->Execute($sql)) {
358                 if (!$rs->EOF) {
359                     if ($localuserfield === 'username') {
360                         $usersearch = array('mnethostid'=>$CFG->mnet_localhost_id, 'deleted' =>0);
361                     }
362                     while ($fields = $rs->FetchRow()) {
363                         $fields = array_change_key_case($fields, CASE_LOWER);
364                         if (empty($fields[$userfield])) {
365                             //user identification is mandatory!
366                         }
367                         $mapping = $fields[$userfield];
368                         if (!isset($user_mapping[$mapping])) {
369                             $usersearch[$localuserfield] = $mapping;
370                             if (!$user = $DB->get_record('user', $usersearch, 'id', IGNORE_MULTIPLE)) {
371                                 // user does not exist or was deleted
372                                 continue;
373                             }
374                             $user_mapping[$mapping] = $user->id;
375                             $userid = $user->id;
376                         } else {
377                             $userid = $user_mapping[$mapping];
378                         }
379                         if (empty($fields[$rolefield]) or !isset($roles[$fields[$rolefield]])) {
380                             if (!$defaultrole) {
381                                 // role is mandatory
382                                 continue;
383                             }
384                             $roleid = $defaultrole;
385                         } else {
386                             $roleid = $roles[$fields[$rolefield]];
387                         }
389                         $requested_roles[$userid][$roleid] = $roleid;
390                     }
391                 }
392                 $rs->Close();
393             } else {
394                 debugging('Error while communicating with external enrolment database');
395                 $extdb->Close();
396                 return;
397             }
398             unset($user_mapping);
400             // enrol all users and sync roles
401             foreach ($requested_roles as $userid=>$roles) {
402                 foreach ($roles as $roleid) {
403                     if (empty($current_roles[$userid])) {
404                         $this->enrol_user($instance, $userid, $roleid);
405                         $current_roles[$userid][$roleid] = $roleid;
406                         $current_status[$userid] = ENROL_USER_ACTIVE;
407                     }
408                 }
410                 // unassign removed roles
411                 foreach($current_roles[$userid] as $cr) {
412                     if (empty($roles[$cr])) {
413                         role_unassign($cr, $userid, $context->id, 'enrol_database', $instance->id);
414                         unset($current_roles[$userid][$cr]);
415                     }
416                 }
418                 // reenable enrolment when previously disable enrolment refreshed
419                 if ($current_status[$userid] == ENROL_USER_SUSPENDED) {
420                     $DB->set_field('user_enrolments', 'status', ENROL_USER_ACTIVE, array('enrolid'=>$instance->id, 'userid'=>$userid));
421                 }
422             }
424             // deal with enrolments removed from external table
425             if ($unenrolaction == ENROL_EXT_REMOVED_UNENROL) {
426                 // unenrol
427                 if (!empty($requested_roles)) {
428                     // we might get some error or connection problem, better not unenrol everybody
429                     foreach ($current_status as $userid=>$status) {
430                         if (isset($requested_roles[$userid])) {
431                             continue;
432                         }
433                         $this->unenrol_user($instance, $userid);
434                     }
435                 }
437             } else if ($unenrolaction == ENROL_EXT_REMOVED_KEEP) {
438                 // keep - only adding enrolments
440             } else if ($unenrolaction == ENROL_EXT_REMOVED_SUSPEND or $unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
441                 // disable
442                 foreach ($current_status as $userid=>$status) {
443                     if (isset($requested_roles[$userid])) {
444                         continue;
445                     }
446                     if ($status != ENROL_USER_SUSPENDED) {
447                         $DB->set_field('user_enrolments', 'status', ENROL_USER_SUSPENDED, array('enrolid'=>$instance->id, 'userid'=>$userid));
448                     }
449                     if ($unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
450                         role_unassign_all(array('contextid'=>$context->id, 'userid'=>$userid, 'component'=>'enrol_database', 'itemid'=>$instance->id));
451                     }
452                 }
453             }
454         }
456         // close db connection
457         $extdb->Close();
458     }
460     /**
461      * Performs a full sync with external database.
462      *
463      * First it creates new courses if necessary, then
464      * enrols and unenrols users.
465      * @return void
466      */
467     public function sync_courses() {
468         global $CFG, $DB;
470         // make sure we sync either enrolments or courses
471         if (!$this->get_config('dbtype') or !$this->get_config('dbhost') or $this->get_config('newcoursetable') or $this->get_config('newcoursefullname') or $this->get_config('newcourseshortname')) {
472             return;
473         }
475         // we may need a lot of memory here
476         @set_time_limit(0);
477         @raise_memory_limit("512M");
479         $extdb = $this->db_init();
481         // first create new courses
482         $table     = $this->get_config('newcoursetable');
483         $fullname  = strtolower($this->get_config('newcoursefullname'));
484         $shortname = strtolower($this->get_config('newcourseshortname'));
485         $idnumber  = strtolower($this->get_config('newcourseidnumber'));
486         $category  = strtolower($this->get_config('newcoursecategory'));
488         $fields = array($fullname, $shortname, $idnumber);
489         if ($category) {
490             $fields[] = $category;
491         }
492         if ($idnumber) {
493             $fields[] = $idnumber;
494         }
495         $sql = $this->db_get_sql($table, array(), $fields);
496         $createcourses = array();
497         if ($rs = $extdb->Execute($sql)) {
498             if (!$rs->EOF) {
499                 $courselist = array();
500                 while ($fields = $rs->FetchRow()) {
501                     $fields = array_change_key_case($fields, CASE_LOWER);
502                     if (empty($fields[$shortname]) or empty($fields[$fullname])) {
503                         //invalid record - these two are mandatory
504                         continue;
505                     }
506                     $fields = $this->db_decode($fields);
507                     if ($DB->record_exists('course', array('shortname'=>$fields[$shortname]))) {
508                         // already exists
509                         continue;
510                     }
511                     if ($idnumber and $DB->record_exists('course', array('idnumber'=>$fields[$idnumber]))) {
512                         // idnumber duplicates are not allowed
513                         continue;
514                     }
515                     if ($category and !$DB->record_exists('course_categories', array('id'=>$fields[$category]))) {
516                         // invalid category id, better to skip
517                         continue;
518                     }
519                     $course = new object();
520                     $course->fullname  = $fields[$fullname];
521                     $course->shortname = $fields[$shortname];
522                     $course->idnumber  = $idnumber ? $fields[$idnumber] : NULL;
523                     $course->category  = $category ? $fields[$category] : NULL;
524                     $createcourses[] = $course;
525                 }
526             }
527             $rs->Close();
528         } else {
529             debugging('Error while communicating with external enrolment database');
530             $extdb->Close();
531             return;
532         }
533         if ($createcourses) {
534             require_once("$CFG->dirroot/course/lib.php");
536             $template        = $this->get_config('templatecourse');
537             $defaultcategory = $this->get_config('defaultcategory');
539             if ($template) {
540                 if ($template = $DB->get_record('course', array('shortname'=>$template))) {
541                     unset($template->id);
542                     unset($template->fullname);
543                     unset($template->shortname);
544                     unset($template->idnumber);
545                 } else {
546                     $template = new object();
547                 }
548             } else {
549                 $template = new object();
550             }
551             if (!$DB->record_exists('course_categories', array('id'=>$defaultcategory))) {
552                 $categories = $DB->get_records('course_categories', array(), 'sortorder', 'id', 0, 1);
553                 $first = reset($categories);
554                 $defaultcategory = $first->id;
555             }
557             foreach ($createcourses as $fields) {
558                 $newcourse = clone($template);
559                 $newcourse->fullname  = $fields->fullname;
560                 $newcourse->shortname = $fields->shortname;
561                 $newcourse->idnumber  = $fields->idnumber;
562                 $newcourse->category  = $fields->category ? $fields->category : $defaultcategory;
564                 create_course($newcourse);
565             }
567             unset($createcourses);
568             unset($template);
569         }
571         // close db connection
572         $extdb->Close();
573     }
575     protected function db_get_sql($table, array $conditions, array $fields, $distinct = false, $sort = "") {
576         $fields = $fields ? implode(',', $fields) : "*";
577         $where = array();
578         if ($conditions) {
579             foreach ($conditions as $key=>$value) {
580                 $value = $this->db_encode($this->db_addslashes($value));
582                 $where[] = "$key = '$value'";
583             }
584         }
585         $where = $where ? "WHERE ".implode(" AND ", $where) : "";
586         $sort = $sort ? "ORDER BY $sort" : "";
587         $distinct = $distinct ? "DISTINCT" : "";
588         $sql = "SELECT $distinct $fields
589                   FROM $table
590                  $where
591                   $sort";
593         return $sql;
594     }
596     protected function db_init() {
597         global $CFG;
599         require_once($CFG->libdir.'/adodb/adodb.inc.php');
601         // Connect to the external database (forcing new connection)
602         $extdb = ADONewConnection($this->get_config('dbtype'));
603         if ($this->get_config('debugdb')) {
604             $extdb->debug = true;
605             ob_start(); //start output buffer to allow later use of the page headers
606         }
608         $extdb->Connect($this->get_config('dbhost'), $this->get_config('dbuser'), $this->get_config('dbpass'), $this->get_config('dbname'), true);
609         $extdb->SetFetchMode(ADODB_FETCH_ASSOC);
610         if ($this->get_config('dbsetupsql')) {
611             $extdb->Execute($this->get_config('dbsetupsql'));
612         }
613         return $extdb;
614     }
616     protected function db_addslashes($text) {
617         // using custom made function for now
618         if ($this->get_config('dbsybasequoting')) {
619             $text = str_replace('\\', '\\\\', $text);
620             $text = str_replace(array('\'', '"', "\0"), array('\\\'', '\\"', '\\0'), $text);
621         } else {
622             $text = str_replace("'", "''", $text);
623         }
624         return $text;
625     }
627     protected function db_encode($text) {
628         $dbenc = $this->get_config('dbencoding');
629         if (empty($dbenc) or $dbenc == 'utf-8') {
630             return $text;
631         }
632         if (is_array($text)) {
633             foreach($text as $k=>$value) {
634                 $text[$k] = $this->db_encode($value);
635             }
636             return $text;
637         } else {
638             return textlib_get_instance()->convert($text, 'utf-8', $dbenc);
639         }
640     }
642     protected function db_decode($text) {
643         $dbenc = $this->get_config('dbencoding');
644         if (empty($dbenc) or $dbenc == 'utf-8') {
645             return $text;
646         }
647         if (is_array($text)) {
648             foreach($text as $k=>$value) {
649                 $text[$k] = $this->db_decode($value);
650             }
651             return $text;
652         } else {
653             return textlib_get_instance()->convert($text, $dbenc, 'utf-8');
654         }
655     }