[Summary view] [Print] [Text view]

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