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/>.
  16
  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  */
  26
  27 defined('MOODLE_INTERNAL') || die();
  28
  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         }
  52
  53         //TODO: connect to external system and make sure no users are to be enrolled in this course
  54         return false;
  55     }
  56
  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     }
  67
  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         }
  81
  82         return false;
  83     }
  84
  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;
  94
  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         }
  99
 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'));
 105
 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);
 111
 112         $localrolefield   = $this->get_config('localrolefield');
 113         $localuserfield   = $this->get_config('localuserfield');
 114         $localcoursefield = $this->get_config('localcoursefield');
 115
 116         $unenrolaction    = $this->get_config('unenrolaction');
 117         $defaultrole      = $this->get_config('defaultrole');
 118
 119         $ignorehidden     = $this->get_config('ignorehiddencourses');
 120
 121         if (!is_object($user) or !property_exists($user, 'id')) {
 122             throw new coding_exception('Invalid $user parameter in sync_user_enrolments()');
 123         }
 124
 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         }
 129
 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         }
 139
 140         $roleassigns = array();
 141         $enrols = array();
 142         $instances = array();
 143
 144         if (!$extdb = $this->db_init()) {
 145             // Can not connect to database, sorry.
 146             return;
 147         }
 148
 149         // Read remote enrols and create instances.
 150         $sql = $this->db_get_sql($table, array($userfield=>$user->$localuserfield), array(), false);
 151
 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);
 157
 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                     }
 168
 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                     }
 178
 179                     $roleassigns[$course->id][$roleid] = $roleid;
 180                     if (empty($fields[$otheruserfieldlower])) {
 181                         $enrols[$course->id][$roleid] = $roleid;
 182                     }
 183
 184                     if ($instance = $DB->get_record('enrol', array('courseid'=>$course->id, 'enrol'=>'database'), '*', IGNORE_MULTIPLE)) {
 185                         $instances[$course->id] = $instance;
 186                         continue;
 187                     }
 188
 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         }
 200
 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];
 208
 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             }
 220
 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');
 226
 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         }
 241
 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             }
 254
 255             if (!$context = context_course::instance($instance->courseid, IGNORE_MISSING)) {
 256                 // Very weird.
 257                 continue;
 258             }
 259
 260             if (!empty($enrols[$instance->courseid])) {
 261                 // We want this user enrolled.
 262                 continue;
 263             }
 264
 265             // Deal with enrolments removed from external table
 266             if ($unenrolaction == ENROL_EXT_REMOVED_UNENROL) {
 267                 $this->unenrol_user($instance, $user->id);
 268
 269             } else if ($unenrolaction == ENROL_EXT_REMOVED_KEEP) {
 270                 // Keep - only adding enrolments.
 271
 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     }
 288
 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;
 298
 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         }
 305
 306         $trace->output('Starting user enrolment synchronisation...');
 307
 308         if (!$extdb = $this->db_init()) {
 309             $trace->output('Error while communicating with external enrolment database');
 310             $trace->finished();
 311             return 1;
 312         }
 313
 314         // We may need a lot of memory here.
 315         core_php_time_limit::raise();
 316         raise_memory_limit(MEMORY_HUGE);
 317
 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'));
 323
 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);
 329
 330         $localrolefield   = $this->get_config('localrolefield');
 331         $localuserfield   = $this->get_config('localuserfield');
 332         $localcoursefield = $this->get_config('localcoursefield');
 333
 334         $unenrolaction    = $this->get_config('unenrolaction');
 335         $defaultrole      = $this->get_config('defaultrole');
 336
 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         }
 346
 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);
 364
 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;
 369
 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             }
 396
 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();
 411
 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();
 437
 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             }
 444
 445             // Free memory.
 446             unset($externalcourses);
 447
 448             $ignorehidden = $this->get_config('ignorehiddencourses');
 449         }
 450
 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);
 467
 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;
 487
 488                 if (isset($ue->status)) {
 489                     $currentenrols[$ue->userid][$ue->roleid] = $ue->roleid;
 490                     $currentstatus[$ue->userid] = $ue->status;
 491                 }
 492             }
 493             $rs->close();
 494
 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                         }
 532
 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);
 545
 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                 }
 557
 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             }
 564
 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                 }
 574
 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                 }
 583
 584                 unset($currentroles[$userid]);
 585             }
 586
 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             }
 592
 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                 }
 605
 606             } else if ($unenrolaction == ENROL_EXT_REMOVED_KEEP) {
 607                 // Keep - only adding enrolments.
 608
 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));
 625
 626                         $trace->output("unsassigning all roles: $userid ==> $course->shortname", 1);
 627                     }
 628                 }
 629             }
 630         }
 631
 632         // Close db connection.
 633         $extdb->Close();
 634
 635         $trace->output('...user enrolment synchronisation finished.');
 636         $trace->finished();
 637
 638         return 0;
 639     }
 640
 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;
 652
 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         }
 659
 660         $trace->output('Starting course synchronisation...');
 661
 662         // We may need a lot of memory here.
 663         core_php_time_limit::raise();
 664         raise_memory_limit(MEMORY_HUGE);
 665
 666         if (!$extdb = $this->db_init()) {
 667             $trace->output('Error while communicating with external enrolment database');
 668             $trace->finished();
 669             return 1;
 670         }
 671
 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'));
 677
 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);
 683
 684         $localcategoryfield = $this->get_config('localcategoryfield', 'id');
 685         $defaultcategory    = $this->get_config('defaultcategory');
 686
 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         }
 693
 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");
 753
 754             $templatecourse = $this->get_config('templatecourse');
 755
 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->numsections    = $courseconfig->numsections;
 778                 $template->newsitems      = $courseconfig->newsitems;
 779                 $template->showgrades     = $courseconfig->showgrades;
 780                 $template->showreports    = $courseconfig->showreports;
 781                 $template->maxbytes       = $courseconfig->maxbytes;
 782                 $template->groupmode      = $courseconfig->groupmode;
 783                 $template->groupmodeforce = $courseconfig->groupmodeforce;
 784                 $template->visible        = $courseconfig->visible;
 785                 $template->lang           = $courseconfig->lang;
 786                 $template->enablecompletion = $courseconfig->enablecompletion;
 787                 $template->groupmodeforce = $courseconfig->groupmodeforce;
 788                 $template->startdate      = usergetmidnight(time());
 789                 if ($courseconfig->courseenddateenabled) {
 790                     $template->enddate    = usergetmidnight(time()) + $courseconfig->courseduration;
 791                 }
 792             }
 793
 794             foreach ($createcourses as $fields) {
 795                 $newcourse = clone($template);
 796                 $newcourse->fullname  = $fields->fullname;
 797                 $newcourse->shortname = $fields->shortname;
 798                 $newcourse->idnumber  = $fields->idnumber;
 799                 $newcourse->category  = $fields->category;
 800
 801                 // Detect duplicate data once again, above we can not find duplicates
 802                 // in external data using DB collation rules...
 803                 if ($DB->record_exists('course', array('shortname' => $newcourse->shortname))) {
 804                     $trace->output("can not insert new course, duplicate shortname detected: ".$newcourse->shortname, 1);
 805                     continue;
 806                 } else if (!empty($newcourse->idnumber) and $DB->record_exists('course', array('idnumber' => $newcourse->idnumber))) {
 807                     $trace->output("can not insert new course, duplicate idnumber detected: ".$newcourse->idnumber, 1);
 808                     continue;
 809                 }
 810                 $c = create_course($newcourse);
 811                 $trace->output("creating course: $c->id, $c->fullname, $c->shortname, $c->idnumber, $c->category", 1);
 812             }
 813
 814             unset($createcourses);
 815             unset($template);
 816         }
 817
 818         // Close db connection.
 819         $extdb->Close();
 820
 821         $trace->output('...course synchronisation finished.');
 822         $trace->finished();
 823
 824         return 0;
 825     }
 826
 827     protected function db_get_sql($table, array $conditions, array $fields, $distinct = false, $sort = "") {
 828         $fields = $fields ? implode(',', $fields) : "*";
 829         $where = array();
 830         if ($conditions) {
 831             foreach ($conditions as $key=>$value) {
 832                 $value = $this->db_encode($this->db_addslashes($value));
 833
 834                 $where[] = "$key = '$value'";
 835             }
 836         }
 837         $where = $where ? "WHERE ".implode(" AND ", $where) : "";
 838         $sort = $sort ? "ORDER BY $sort" : "";
 839         $distinct = $distinct ? "DISTINCT" : "";
 840         $sql = "SELECT $distinct $fields
 841                   FROM $table
 842                  $where
 843                   $sort";
 844
 845         return $sql;
 846     }
 847
 848     /**
 849      * Tries to make connection to the external database.
 850      *
 851      * @return null|ADONewConnection
 852      */
 853     protected function db_init() {
 854         global $CFG;
 855
 856         require_once($CFG->libdir.'/adodb/adodb.inc.php');
 857
 858         // Connect to the external database (forcing new connection).
 859         $extdb = ADONewConnection($this->get_config('dbtype'));
 860         if ($this->get_config('debugdb')) {
 861             $extdb->debug = true;
 862             ob_start(); // Start output buffer to allow later use of the page headers.
 863         }
 864
 865         // The dbtype my contain the new connection URL, so make sure we are not connected yet.
 866         if (!$extdb->IsConnected()) {
 867             $result = $extdb->Connect($this->get_config('dbhost'), $this->get_config('dbuser'), $this->get_config('dbpass'), $this->get_config('dbname'), true);
 868             if (!$result) {
 869                 return null;
 870             }
 871         }
 872
 873         $extdb->SetFetchMode(ADODB_FETCH_ASSOC);
 874         if ($this->get_config('dbsetupsql')) {
 875             $extdb->Execute($this->get_config('dbsetupsql'));
 876         }
 877         return $extdb;
 878     }
 879
 880     protected function db_addslashes($text) {
 881         // Use custom made function for now - it is better to not rely on adodb or php defaults.
 882         if ($this->get_config('dbsybasequoting')) {
 883             $text = str_replace('\\', '\\\\', $text);
 884             $text = str_replace(array('\'', '"', "\0"), array('\\\'', '\\"', '\\0'), $text);
 885         } else {
 886             $text = str_replace("'", "''", $text);
 887         }
 888         return $text;
 889     }
 890
 891     protected function db_encode($text) {
 892         $dbenc = $this->get_config('dbencoding');
 893         if (empty($dbenc) or $dbenc == 'utf-8') {
 894             return $text;
 895         }
 896         if (is_array($text)) {
 897             foreach($text as $k=>$value) {
 898                 $text[$k] = $this->db_encode($value);
 899             }
 900             return $text;
 901         } else {
 902             return core_text::convert($text, 'utf-8', $dbenc);
 903         }
 904     }
 905
 906     protected function db_decode($text) {
 907         $dbenc = $this->get_config('dbencoding');
 908         if (empty($dbenc) or $dbenc == 'utf-8') {
 909             return $text;
 910         }
 911         if (is_array($text)) {
 912             foreach($text as $k=>$value) {
 913                 $text[$k] = $this->db_decode($value);
 914             }
 915             return $text;
 916         } else {
 917             return core_text::convert($text, $dbenc, 'utf-8');
 918         }
 919     }
 920
 921     /**
 922      * Automatic enrol sync executed during restore.
 923      * @param stdClass $course course record
 924      */
 925     public function restore_sync_course($course) {
 926         $trace = new null_progress_trace();
 927         $this->sync_enrolments($trace, $course->id);
 928     }
 929
 930     /**
 931      * Restore instance and map settings.
 932      *
 933      * @param restore_enrolments_structure_step $step
 934      * @param stdClass $data
 935      * @param stdClass $course
 936      * @param int $oldid
 937      */
 938     public function restore_instance(restore_enrolments_structure_step $step, stdClass $data, $course, $oldid) {
 939         global $DB;
 940
 941         if ($instance = $DB->get_record('enrol', array('courseid'=>$course->id, 'enrol'=>$this->get_name()))) {
 942             $instanceid = $instance->id;
 943         } else {
 944             $instanceid = $this->add_instance($course);
 945         }
 946         $step->set_mapping('enrol', $oldid, $instanceid);
 947     }
 948
 949     /**
 950      * Restore user enrolment.
 951      *
 952      * @param restore_enrolments_structure_step $step
 953      * @param stdClass $data
 954      * @param stdClass $instance
 955      * @param int $oldinstancestatus
 956      * @param int $userid
 957      */
 958     public function restore_user_enrolment(restore_enrolments_structure_step $step, $data, $instance, $userid, $oldinstancestatus) {
 959         global $DB;
 960
 961         if ($this->get_config('unenrolaction') == ENROL_EXT_REMOVED_UNENROL) {
 962             // Enrolments were already synchronised in restore_instance(), we do not want any suspended leftovers.
 963             return;
 964         }
 965         if (!$DB->record_exists('user_enrolments', array('enrolid'=>$instance->id, 'userid'=>$userid))) {
 966             $this->enrol_user($instance, $userid, null, 0, 0, ENROL_USER_SUSPENDED);
 967         }
 968     }
 969
 970     /**
 971      * Restore role assignment.
 972      *
 973      * @param stdClass $instance
 974      * @param int $roleid
 975      * @param int $userid
 976      * @param int $contextid
 977      */
 978     public function restore_role_assignment($instance, $roleid, $userid, $contextid) {
 979         if ($this->get_config('unenrolaction') == ENROL_EXT_REMOVED_UNENROL or $this->get_config('unenrolaction') == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
 980             // Role assignments were already synchronised in restore_instance(), we do not want any leftovers.
 981             return;
 982         }
 983         role_assign($roleid, $userid, $contextid, 'enrol_'.$this->get_name(), $instance->id);
 984     }
 985
 986     /**
 987      * Test plugin settings, print info to output.
 988      */
 989     public function test_settings() {
 990         global $CFG, $OUTPUT;
 991
 992         // NOTE: this is not localised intentionally, admins are supposed to understand English at least a bit...
 993
 994         raise_memory_limit(MEMORY_HUGE);
 995
 996         $this->load_config();
 997
 998         $enroltable = $this->get_config('remoteenroltable');
 999         $coursetable = $this->get_config('newcoursetable');
1000
1001         if (empty($enroltable)) {
1002             echo $OUTPUT->notification('External enrolment table not specified.', 'notifyproblem');
1003         }
1004
1005         if (empty($coursetable)) {
1006             echo $OUTPUT->notification('External course table not specified.', 'notifyproblem');
1007         }
1008
1009         if (empty($coursetable) and empty($enroltable)) {
1010             return;
1011         }
1012
1013         $olddebug = $CFG->debug;
1014         $olddisplay = ini_get('display_errors');
1015         ini_set('display_errors', '1');
1016         $CFG->debug = DEBUG_DEVELOPER;
1017         $olddebugdb = $this->config->debugdb;
1018         $this->config->debugdb = 1;
1019         error_reporting($CFG->debug);
1020
1021         $adodb = $this->db_init();
1022
1023         if (!$adodb or !$adodb->IsConnected()) {
1024             $this->config->debugdb = $olddebugdb;
1025             $CFG->debug = $olddebug;
1026             ini_set('display_errors', $olddisplay);
1027             error_reporting($CFG->debug);
1028             ob_end_flush();
1029
1030             echo $OUTPUT->notification('Cannot connect the database.', 'notifyproblem');
1031             return;
1032         }
1033
1034         if (!empty($enroltable)) {
1035             $rs = $adodb->Execute("SELECT *
1036                                      FROM $enroltable");
1037             if (!$rs) {
1038                 echo $OUTPUT->notification('Can not read external enrol table.', 'notifyproblem');
1039
1040             } else if ($rs->EOF) {
1041                 echo $OUTPUT->notification('External enrol table is empty.', 'notifyproblem');
1042                 $rs->Close();
1043
1044             } else {
1045                 $fields_obj = $rs->FetchObj();
1046                 $columns = array_keys((array)$fields_obj);
1047
1048                 echo $OUTPUT->notification('External enrolment table contains following columns:<br />'.implode(', ', $columns), 'notifysuccess');
1049                 $rs->Close();
1050             }
1051         }
1052
1053         if (!empty($coursetable)) {
1054             $rs = $adodb->Execute("SELECT *
1055                                      FROM $coursetable");
1056             if (!$rs) {
1057                 echo $OUTPUT->notification('Can not read external course table.', 'notifyproblem');
1058
1059             } else if ($rs->EOF) {
1060                 echo $OUTPUT->notification('External course table is empty.', 'notifyproblem');
1061                 $rs->Close();
1062
1063             } else {
1064                 $fields_obj = $rs->FetchObj();
1065                 $columns = array_keys((array)$fields_obj);
1066
1067                 echo $OUTPUT->notification('External course table contains following columns:<br />'.implode(', ', $columns), 'notifysuccess');
1068                 $rs->Close();
1069             }
1070         }
1071
1072         $adodb->Close();
1073
1074         $this->config->debugdb = $olddebugdb;
1075         $CFG->debug = $olddebug;
1076         ini_set('display_errors', $olddisplay);
1077         error_reporting($CFG->debug);
1078         ob_end_flush();
1079     }
1080 }