Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.10.x will end 8 November 2021 (12 months).
  • Bug fixes for security issues in 3.10.x will end 9 May 2022 (18 months).
  • PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.

Differences Between: [Versions 310 and 402] [Versions 310 and 403]

   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  }