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