Search moodle.org's
Developer Documentation

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.
  •    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  }