enrol/database/lib.php

   1 <?php
   2 // This file is part of Moodle - http://moodle.org/
   3 //
   4 // Moodle is free software: you can redistribute it and/or modify
   5 // it under the terms of the GNU General Public License as published by
   6 // the Free Software Foundation, either version 3 of the License, or
   7 // (at your option) any later version.
   8 //
   9 // Moodle is distributed in the hope that it will be useful,
  10 // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12 // GNU General Public License for more details.
  13 //
  14 // You should have received a copy of the GNU General Public License
  15 // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16
  17 /**
  18  * Database enrolment plugin.
  19  *
  20  * This plugin synchronises enrolment and roles with external database table.
  21  *
  22  * @package    enrol_database
  23  * @copyright  2010 Petr Skoda {@link http://skodak.org}
  24  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  25  */
  26
  27 defined('MOODLE_INTERNAL') || die();
  28
  29 /**
  30  * Database enrolment plugin implementation.
  31  * @author  Petr Skoda - based on code by Martin Dougiamas, Martin Langhoff and others
  32  * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  33  */
  34 class enrol_database_plugin extends enrol_plugin {
  35     /**
  36      * Is it possible to delete enrol instance via standard UI?
  37      *
  38      * @param stdClass $instance
  39      * @return bool
  40      */
  41     public function instance_deleteable($instance) {
  42         if (!enrol_is_enabled('database')) {
  43             return true;
  44         }
  45         if (!$this->get_config('dbtype') or !$this->get_config('remoteenroltable') or !$this->get_config('remotecoursefield') or !$this->get_config('remoteuserfield')) {
  46             return true;
  47         }
  48
  49         //TODO: connect to external system and make sure no users are to be enrolled in this course
  50         return false;
  51     }
  52
  53     /**
  54      * Does this plugin allow manual unenrolment of a specific user?
  55      * Yes, but only if user suspended...
  56      *
  57      * @param stdClass $instance course enrol instance
  58      * @param stdClass $ue record from user_enrolments table
  59      *
  60      * @return bool - true means user with 'enrol/xxx:unenrol' may unenrol this user, false means nobody may touch this user enrolment
  61      */
  62     public function allow_unenrol_user(stdClass $instance, stdClass $ue) {
  63         if ($ue->status == ENROL_USER_SUSPENDED) {
  64             return true;
  65         }
  66
  67         return false;
  68     }
  69
  70     /**
  71      * Gets an array of the user enrolment actions.
  72      *
  73      * @param course_enrolment_manager $manager
  74      * @param stdClass $ue A user enrolment object
  75      * @return array An array of user_enrolment_actions
  76      */
  77     public function get_user_enrolment_actions(course_enrolment_manager $manager, $ue) {
  78         $actions = array();
  79         $context = $manager->get_context();
  80         $instance = $ue->enrolmentinstance;
  81         $params = $manager->get_moodlepage()->url->params();
  82         $params['ue'] = $ue->id;
  83         if ($this->allow_unenrol_user($instance, $ue) && has_capability('enrol/database:unenrol', $context)) {
  84             $url = new moodle_url('/enrol/unenroluser.php', $params);
  85             $actions[] = new user_enrolment_action(new pix_icon('t/delete', ''), get_string('unenrol', 'enrol'), $url, array('class'=>'unenrollink', 'rel'=>$ue->id));
  86         }
  87         return $actions;
  88     }
  89
  90     /**
  91      * Forces synchronisation of user enrolments with external database,
  92      * does not create new courses.
  93      *
  94      * @param stdClass $user user record
  95      * @return void
  96      */
  97     public function sync_user_enrolments($user) {
  98         global $CFG, $DB;
  99
 100         // We do not create courses here intentionally because it requires full sync and is slow.
 101         if (!$this->get_config('dbtype') or !$this->get_config('remoteenroltable') or !$this->get_config('remotecoursefield') or !$this->get_config('remoteuserfield')) {
 102             return;
 103         }
 104
 105         $table            = $this->get_config('remoteenroltable');
 106         $coursefield      = trim($this->get_config('remotecoursefield'));
 107         $userfield        = trim($this->get_config('remoteuserfield'));
 108         $rolefield        = trim($this->get_config('remoterolefield'));
 109
 110         // Lowercased versions - necessary because we normalise the resultset with array_change_key_case().
 111         $coursefield_l    = strtolower($coursefield);
 112         $userfield_l      = strtolower($userfield);
 113         $rolefield_l      = strtolower($rolefield);
 114
 115         $localrolefield   = $this->get_config('localrolefield');
 116         $localuserfield   = $this->get_config('localuserfield');
 117         $localcoursefield = $this->get_config('localcoursefield');
 118
 119         $unenrolaction    = $this->get_config('unenrolaction');
 120         $defaultrole      = $this->get_config('defaultrole');
 121
 122         $ignorehidden     = $this->get_config('ignorehiddencourses');
 123
 124         if (!is_object($user) or !property_exists($user, 'id')) {
 125             throw new coding_exception('Invalid $user parameter in sync_user_enrolments()');
 126         }
 127
 128         if (!property_exists($user, $localuserfield)) {
 129             debugging('Invalid $user parameter in sync_user_enrolments(), missing '.$localuserfield);
 130             $user = $DB->get_record('user', array('id'=>$user->id));
 131         }
 132
 133         // Create roles mapping.
 134         $allroles = get_all_roles();
 135         if (!isset($allroles[$defaultrole])) {
 136             $defaultrole = 0;
 137         }
 138         $roles = array();
 139         foreach ($allroles as $role) {
 140             $roles[$role->$localrolefield] = $role->id;
 141         }
 142
 143         $enrols = array();
 144         $instances = array();
 145
 146         if (!$extdb = $this->db_init()) {
 147             // Can not connect to database, sorry.
 148             return;
 149         }
 150
 151         // Read remote enrols and create instances.
 152         $sql = $this->db_get_sql($table, array($userfield=>$user->$localuserfield), array(), false);
 153
 154         if ($rs = $extdb->Execute($sql)) {
 155             if (!$rs->EOF) {
 156                 while ($fields = $rs->FetchRow()) {
 157                     $fields = array_change_key_case($fields, CASE_LOWER);
 158                     $fields = $this->db_decode($fields);
 159
 160                     if (empty($fields[$coursefield_l])) {
 161                         // Missing course info.
 162                         continue;
 163                     }
 164                     if (!$course = $DB->get_record('course', array($localcoursefield=>$fields[$coursefield_l]), 'id,visible')) {
 165                         continue;
 166                     }
 167                     if (!$course->visible and $ignorehidden) {
 168                         continue;
 169                     }
 170
 171                     if (empty($fields[$rolefield_l]) or !isset($roles[$fields[$rolefield_l]])) {
 172                         if (!$defaultrole) {
 173                             // Role is mandatory.
 174                             continue;
 175                         }
 176                         $roleid = $defaultrole;
 177                     } else {
 178                         $roleid = $roles[$fields[$rolefield_l]];
 179                     }
 180
 181                     if (empty($enrols[$course->id])) {
 182                         $enrols[$course->id] = array();
 183                     }
 184                     $enrols[$course->id][] = $roleid;
 185
 186                     if ($instance = $DB->get_record('enrol', array('courseid'=>$course->id, 'enrol'=>'database'), '*', IGNORE_MULTIPLE)) {
 187                         $instances[$course->id] = $instance;
 188                         continue;
 189                     }
 190
 191                     $enrolid = $this->add_instance($course);
 192                     $instances[$course->id] = $DB->get_record('enrol', array('id'=>$enrolid));
 193                 }
 194             }
 195             $rs->Close();
 196             $extdb->Close();
 197         } else {
 198             // Bad luck, something is wrong with the db connection.
 199             $extdb->Close();
 200             return;
 201         }
 202
 203         // Enrol user into courses and sync roles.
 204         foreach ($enrols as $courseid => $roles) {
 205             if (!isset($instances[$courseid])) {
 206                 // Ignored.
 207                 continue;
 208             }
 209             $instance = $instances[$courseid];
 210
 211             if ($e = $DB->get_record('user_enrolments', array('userid'=>$user->id, 'enrolid'=>$instance->id))) {
 212                 // Reenable enrolment when previously disable enrolment refreshed.
 213                 if ($e->status == ENROL_USER_SUSPENDED) {
 214                     $this->update_user_enrol($instance, $user->id, ENROL_USER_ACTIVE);
 215                 }
 216             } else {
 217                 $roleid = reset($roles);
 218                 $this->enrol_user($instance, $user->id, $roleid, 0, 0, ENROL_USER_ACTIVE);
 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 (in_array($r->roleid, $roles)) {
 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 {user_enrolments} ue ON ue.enrolid = e.id
 246                   JOIN {course} c ON c.id = e.courseid
 247                  WHERE ue.userid = :userid AND e.enrol = 'database'";
 248         $rs = $DB->get_recordset_sql($sql, array('userid'=>$user->id));
 249         foreach ($rs as $instance) {
 250             if (!$instance->cvisible and $ignorehidden) {
 251                 continue;
 252             }
 253
 254             if (!$context = context_course::instance($instance->courseid, IGNORE_MISSING)) {
 255                 // Very weird.
 256                 continue;
 257             }
 258
 259             if (!empty($enrols[$instance->courseid])) {
 260                 // We want this user enrolled.
 261                 continue;
 262             }
 263
 264             // Deal with enrolments removed from external table
 265             if ($unenrolaction == ENROL_EXT_REMOVED_UNENROL) {
 266                 $this->unenrol_user($instance, $user->id);
 267
 268             } else if ($unenrolaction == ENROL_EXT_REMOVED_KEEP) {
 269                 // Keep - only adding enrolments.
 270
 271             } else if ($unenrolaction == ENROL_EXT_REMOVED_SUSPEND or $unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
 272                 // Suspend users.
 273                 if ($instance->ustatus != ENROL_USER_SUSPENDED) {
 274                     $this->update_user_enrol($instance, $user->id, ENROL_USER_SUSPENDED);
 275                 }
 276                 if ($unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
 277                     role_unassign_all(array('contextid'=>$context->id, 'userid'=>$user->id, 'component'=>'enrol_database', 'itemid'=>$instance->id));
 278                 }
 279             }
 280         }
 281         $rs->close();
 282     }
 283
 284     /**
 285      * Forces synchronisation of all enrolments with external database.
 286      *
 287      * @param progress_trace $trace
 288      * @param null|int $onecourse limit sync to one course only (used primarily in restore)
 289      * @return int 0 means success, 1 db connect failure, 2 db read failure
 290      */
 291     public function sync_enrolments(progress_trace $trace, $onecourse = null) {
 292         global $CFG, $DB;
 293
 294         // We do not create courses here intentionally because it requires full sync and is slow.
 295         if (!$this->get_config('dbtype') or !$this->get_config('remoteenroltable') or !$this->get_config('remotecoursefield') or !$this->get_config('remoteuserfield')) {
 296             $trace->output('User enrolment synchronisation skipped.');
 297             $trace->finished();
 298             return 0;
 299         }
 300
 301         $trace->output('Starting user enrolment synchronisation...');
 302
 303         if (!$extdb = $this->db_init()) {
 304             $trace->output('Error while communicating with external enrolment database');
 305             $trace->finished();
 306             return 1;
 307         }
 308
 309         // We may need a lot of memory here.
 310         @set_time_limit(0);
 311         raise_memory_limit(MEMORY_HUGE);
 312
 313         $table            = $this->get_config('remoteenroltable');
 314         $coursefield      = trim($this->get_config('remotecoursefield'));
 315         $userfield        = trim($this->get_config('remoteuserfield'));
 316         $rolefield        = trim($this->get_config('remoterolefield'));
 317
 318         // Lowercased versions - necessary because we normalise the resultset with array_change_key_case().
 319         $coursefield_l    = strtolower($coursefield);
 320         $userfield_l      = strtolower($userfield);
 321         $rolefield_l      = strtolower($rolefield);
 322
 323         $localrolefield   = $this->get_config('localrolefield');
 324         $localuserfield   = $this->get_config('localuserfield');
 325         $localcoursefield = $this->get_config('localcoursefield');
 326
 327         $unenrolaction    = $this->get_config('unenrolaction');
 328         $defaultrole      = $this->get_config('defaultrole');
 329
 330         // Create roles mapping.
 331         $allroles = get_all_roles();
 332         if (!isset($allroles[$defaultrole])) {
 333             $defaultrole = 0;
 334         }
 335         $roles = array();
 336         foreach ($allroles as $role) {
 337             $roles[$role->$localrolefield] = $role->id;
 338         }
 339
 340         if ($onecourse) {
 341             $sql = "SELECT c.id, c.visible, c.$localcoursefield AS mapping, c.shortname, e.id AS enrolid
 342                       FROM {course} c
 343                  LEFT JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'database')
 344                      WHERE c.id = :id";
 345             if (!$course = $DB->get_record_sql($sql, array('id'=>$onecourse))) {
 346                 // Course does not exist, nothing to sync.
 347                 return 0;
 348             }
 349             if (empty($course->mapping)) {
 350                 // We can not map to this course, sorry.
 351                 return 0;
 352             }
 353             if (empty($course->enrolid)) {
 354                 $course->enrolid = $this->add_instance($course);
 355             }
 356             $existing = array($course->mapping=>$course);
 357
 358             // Feel free to unenrol everybody, no safety tricks here.
 359             $preventfullunenrol = false;
 360             // Course being restored are always hidden, we have to ignore the setting here.
 361             $ignorehidden = false;
 362
 363         } else {
 364             // Get a list of courses to be synced that are in external table.
 365             $externalcourses = array();
 366             $sql = $this->db_get_sql($table, array(), array($coursefield), true);
 367             if ($rs = $extdb->Execute($sql)) {
 368                 if (!$rs->EOF) {
 369                     while ($mapping = $rs->FetchRow()) {
 370                         $mapping = reset($mapping);
 371                         $mapping = $this->db_decode($mapping);
 372                         if (empty($mapping)) {
 373                             // invalid mapping
 374                             continue;
 375                         }
 376                         $externalcourses[$mapping] = true;
 377                     }
 378                 }
 379                 $rs->Close();
 380             } else {
 381                 $trace->output('Error reading data from the external enrolment table');
 382                 $extdb->Close();
 383                 return 2;
 384             }
 385             $preventfullunenrol = empty($externalcourses);
 386             if ($preventfullunenrol and $unenrolaction == ENROL_EXT_REMOVED_UNENROL) {
 387                 $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);
 388             }
 389
 390             // First find all existing courses with enrol instance.
 391             $existing = array();
 392             $sql = "SELECT c.id, c.visible, c.$localcoursefield AS mapping, e.id AS enrolid, c.shortname
 393                       FROM {course} c
 394                       JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'database')";
 395             $rs = $DB->get_recordset_sql($sql); // Watch out for idnumber duplicates.
 396             foreach ($rs as $course) {
 397                 if (empty($course->mapping)) {
 398                     continue;
 399                 }
 400                 $existing[$course->mapping] = $course;
 401                 unset($externalcourses[$course->mapping]);
 402             }
 403             $rs->close();
 404
 405             // Add necessary enrol instances that are not present yet.
 406             $params = array();
 407             $localnotempty = "";
 408             if ($localcoursefield !== 'id') {
 409                 $localnotempty =  "AND c.$localcoursefield <> :lcfe";
 410                 $params['lcfe'] = $DB->sql_empty();
 411             }
 412             $sql = "SELECT c.id, c.visible, c.$localcoursefield AS mapping, c.shortname
 413                       FROM {course} c
 414                  LEFT JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'database')
 415                      WHERE e.id IS NULL $localnotempty";
 416             $rs = $DB->get_recordset_sql($sql, $params);
 417             foreach ($rs as $course) {
 418                 if (empty($course->mapping)) {
 419                     continue;
 420                 }
 421                 if (!isset($externalcourses[$course->mapping])) {
 422                     // Course not synced or duplicate.
 423                     continue;
 424                 }
 425                 $course->enrolid = $this->add_instance($course);
 426                 $existing[$course->mapping] = $course;
 427                 unset($externalcourses[$course->mapping]);
 428             }
 429             $rs->close();
 430
 431             // Print list of missing courses.
 432             if ($externalcourses) {
 433                 $list = implode(', ', array_keys($externalcourses));
 434                 $trace->output("error: following courses do not exist - $list", 1);
 435                 unset($list);
 436             }
 437
 438             // Free memory.
 439             unset($externalcourses);
 440
 441             $ignorehidden = $this->get_config('ignorehiddencourses');
 442         }
 443
 444         // Sync user enrolments.
 445         $sqlfields = array($userfield);
 446         if ($rolefield) {
 447             $sqlfields[] = $rolefield;
 448         }
 449         foreach ($existing as $course) {
 450             if ($ignorehidden and !$course->visible) {
 451                 continue;
 452             }
 453             if (!$instance = $DB->get_record('enrol', array('id'=>$course->enrolid))) {
 454                 continue; // Weird!
 455             }
 456             $context = context_course::instance($course->id);
 457
 458             // Get current list of enrolled users with their roles.
 459             $current_roles  = array();
 460             $current_status = array();
 461             $user_mapping   = array();
 462             $sql = "SELECT u.$localuserfield AS mapping, u.id, ue.status, ue.userid, ra.roleid
 463                       FROM {user} u
 464                       JOIN {user_enrolments} ue ON (ue.userid = u.id AND ue.enrolid = :enrolid)
 465                       JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.itemid = ue.enrolid AND ra.component = 'enrol_database')
 466                      WHERE u.deleted = 0";
 467             $params = array('enrolid'=>$instance->id);
 468             if ($localuserfield === 'username') {
 469                 $sql .= " AND u.mnethostid = :mnethostid";
 470                 $params['mnethostid'] = $CFG->mnet_localhost_id;
 471             }
 472             $rs = $DB->get_recordset_sql($sql, $params);
 473             foreach ($rs as $ue) {
 474                 $current_roles[$ue->userid][$ue->roleid] = $ue->roleid;
 475                 $current_status[$ue->userid] = $ue->status;
 476                 $user_mapping[$ue->mapping] = $ue->userid;
 477             }
 478             $rs->close();
 479
 480             // Get list of users that need to be enrolled and their roles.
 481             $requested_roles = array();
 482             $sql = $this->db_get_sql($table, array($coursefield=>$course->mapping), $sqlfields);
 483             if ($rs = $extdb->Execute($sql)) {
 484                 if (!$rs->EOF) {
 485                     $usersearch = array('deleted' => 0);
 486                     if ($localuserfield === 'username') {
 487                         $usersearch['mnethostid'] = $CFG->mnet_localhost_id;
 488                     }
 489                     while ($fields = $rs->FetchRow()) {
 490                         $fields = array_change_key_case($fields, CASE_LOWER);
 491                         if (empty($fields[$userfield_l])) {
 492                             $trace->output("error: skipping user without mandatory $localuserfield in course '$course->mapping'", 1);
 493                             continue;
 494                         }
 495                         $mapping = $fields[$userfield_l];
 496                         if (!isset($user_mapping[$mapping])) {
 497                             $usersearch[$localuserfield] = $mapping;
 498                             if (!$user = $DB->get_record('user', $usersearch, 'id', IGNORE_MULTIPLE)) {
 499                                 $trace->output("error: skipping unknown user $localuserfield '$mapping' in course '$course->mapping'", 1);
 500                                 continue;
 501                             }
 502                             $user_mapping[$mapping] = $user->id;
 503                             $userid = $user->id;
 504                         } else {
 505                             $userid = $user_mapping[$mapping];
 506                         }
 507                         if (empty($fields[$rolefield_l]) or !isset($roles[$fields[$rolefield_l]])) {
 508                             if (!$defaultrole) {
 509                                 $trace->output("error: skipping user '$userid' in course '$course->mapping' - missing course and default role", 1);
 510                                 continue;
 511                             }
 512                             $roleid = $defaultrole;
 513                         } else {
 514                             $roleid = $roles[$fields[$rolefield_l]];
 515                         }
 516
 517                         $requested_roles[$userid][$roleid] = $roleid;
 518                     }
 519                 }
 520                 $rs->Close();
 521             } else {
 522                 $trace->output("error: skipping course '$course->mapping' - could not match with external database", 1);
 523                 continue;
 524             }
 525             unset($user_mapping);
 526
 527             // Enrol all users and sync roles.
 528             foreach ($requested_roles as $userid=>$userroles) {
 529                 foreach ($userroles as $roleid) {
 530                     if (empty($current_roles[$userid])) {
 531                         $this->enrol_user($instance, $userid, $roleid, 0, 0, ENROL_USER_ACTIVE);
 532                         $current_roles[$userid][$roleid] = $roleid;
 533                         $current_status[$userid] = ENROL_USER_ACTIVE;
 534                         $trace->output("enrolling: $userid ==> $course->shortname as ".$allroles[$roleid]->shortname, 1);
 535                     }
 536                 }
 537
 538                 // Assign extra roles.
 539                 foreach ($userroles as $roleid) {
 540                     if (empty($current_roles[$userid][$roleid])) {
 541                         role_assign($roleid, $userid, $context->id, 'enrol_database', $instance->id);
 542                         $current_roles[$userid][$roleid] = $roleid;
 543                         $trace->output("assigning roles: $userid ==> $course->shortname as ".$allroles[$roleid]->shortname, 1);
 544                     }
 545                 }
 546
 547                 // Unassign removed roles.
 548                 foreach($current_roles[$userid] as $cr) {
 549                     if (empty($userroles[$cr])) {
 550                         role_unassign($cr, $userid, $context->id, 'enrol_database', $instance->id);
 551                         unset($current_roles[$userid][$cr]);
 552                         $trace->output("unsassigning roles: $userid ==> $course->shortname", 1);
 553                     }
 554                 }
 555
 556                 // Reenable enrolment when previously disable enrolment refreshed.
 557                 if ($current_status[$userid] == ENROL_USER_SUSPENDED) {
 558                     $this->update_user_enrol($instance, $userid, ENROL_USER_ACTIVE);
 559                     $trace->output("unsuspending: $userid ==> $course->shortname", 1);
 560                 }
 561             }
 562
 563             // Deal with enrolments removed from external table.
 564             if ($unenrolaction == ENROL_EXT_REMOVED_UNENROL) {
 565                 if (!$preventfullunenrol) {
 566                     // Unenrol.
 567                     foreach ($current_status as $userid=>$status) {
 568                         if (isset($requested_roles[$userid])) {
 569                             continue;
 570                         }
 571                         $this->unenrol_user($instance, $userid);
 572                         $trace->output("unenrolling: $userid ==> $course->shortname", 1);
 573                     }
 574                 }
 575
 576             } else if ($unenrolaction == ENROL_EXT_REMOVED_KEEP) {
 577                 // Keep - only adding enrolments.
 578
 579             } else if ($unenrolaction == ENROL_EXT_REMOVED_SUSPEND or $unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
 580                 // Suspend enrolments.
 581                 foreach ($current_status as $userid=>$status) {
 582                     if (isset($requested_roles[$userid])) {
 583                         continue;
 584                     }
 585                     if ($status != ENROL_USER_SUSPENDED) {
 586                         $this->update_user_enrol($instance, $userid, ENROL_USER_SUSPENDED);
 587                         $trace->output("suspending: $userid ==> $course->shortname", 1);
 588                     }
 589                     if ($unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
 590                         role_unassign_all(array('contextid'=>$context->id, 'userid'=>$userid, 'component'=>'enrol_database', 'itemid'=>$instance->id));
 591                         $trace->output("unsassigning all roles: $userid ==> $course->shortname", 1);
 592                     }
 593                 }
 594             }
 595         }
 596
 597         // Close db connection.
 598         $extdb->Close();
 599
 600         $trace->output('...user enrolment synchronisation finished.');
 601         $trace->finished();
 602
 603         return 0;
 604     }
 605
 606     /**
 607      * Performs a full sync with external database.
 608      *
 609      * First it creates new courses if necessary, then
 610      * enrols and unenrols users.
 611      *
 612      * @param progress_trace $trace
 613      * @return int 0 means success, 1 db connect failure, 4 db read failure
 614      */
 615     public function sync_courses(progress_trace $trace) {
 616         global $CFG, $DB;
 617
 618         // Make sure we sync either enrolments or courses.
 619         if (!$this->get_config('dbtype') or !$this->get_config('newcoursetable') or !$this->get_config('newcoursefullname') or !$this->get_config('newcourseshortname')) {
 620             $trace->output('Course synchronisation skipped.');
 621             $trace->finished();
 622             return 0;
 623         }
 624
 625         $trace->output('Starting course synchronisation...');
 626
 627         // We may need a lot of memory here.
 628         @set_time_limit(0);
 629         raise_memory_limit(MEMORY_HUGE);
 630
 631         if (!$extdb = $this->db_init()) {
 632             $trace->output('Error while communicating with external enrolment database');
 633             $trace->finished();
 634             return 1;
 635         }
 636
 637         $table     = $this->get_config('newcoursetable');
 638         $fullname  = trim($this->get_config('newcoursefullname'));
 639         $shortname = trim($this->get_config('newcourseshortname'));
 640         $idnumber  = trim($this->get_config('newcourseidnumber'));
 641         $category  = trim($this->get_config('newcoursecategory'));
 642
 643         // Lowercased versions - necessary because we normalise the resultset with array_change_key_case().
 644         $fullname_l  = strtolower($fullname);
 645         $shortname_l = strtolower($shortname);
 646         $idnumber_l  = strtolower($idnumber);
 647         $category_l  = strtolower($category);
 648
 649         $localcategoryfield = $this->get_config('localcategoryfield', 'id');
 650         $defaultcategory    = $this->get_config('defaultcategory');
 651
 652         if (!$DB->record_exists('course_categories', array('id'=>$defaultcategory))) {
 653             $trace->output("default course category does not exist!", 1);
 654             $categories = $DB->get_records('course_categories', array(), 'sortorder', 'id', 0, 1);
 655             $first = reset($categories);
 656             $defaultcategory = $first->id;
 657         }
 658
 659         $sqlfields = array($fullname, $shortname);
 660         if ($category) {
 661             $sqlfields[] = $category;
 662         }
 663         if ($idnumber) {
 664             $sqlfields[] = $idnumber;
 665         }
 666         $sql = $this->db_get_sql($table, array(), $sqlfields, true);
 667         $createcourses = array();
 668         if ($rs = $extdb->Execute($sql)) {
 669             if (!$rs->EOF) {
 670                 while ($fields = $rs->FetchRow()) {
 671                     $fields = array_change_key_case($fields, CASE_LOWER);
 672                     $fields = $this->db_decode($fields);
 673                     if (empty($fields[$shortname_l]) or empty($fields[$fullname_l])) {
 674                         $trace->output('error: invalid external course record, shortname and fullname are mandatory: ' . json_encode($fields), 1); // Hopefully every geek can read JS, right?
 675                         continue;
 676                     }
 677                     if ($DB->record_exists('course', array('shortname'=>$fields[$shortname_l]))) {
 678                         // Already exists, skip.
 679                         continue;
 680                     }
 681                     // Allow empty idnumber but not duplicates.
 682                     if ($idnumber and $fields[$idnumber_l] !== '' and $fields[$idnumber_l] !== null and $DB->record_exists('course', array('idnumber'=>$fields[$idnumber_l]))) {
 683                         $trace->output('error: duplicate idnumber, can not create course: '.$fields[$shortname_l].' ['.$fields[$idnumber_l].']', 1);
 684                         continue;
 685                     }
 686                     $course = new stdClass();
 687                     $course->fullname  = $fields[$fullname_l];
 688                     $course->shortname = $fields[$shortname_l];
 689                     $course->idnumber  = $idnumber ? $fields[$idnumber_l] : '';
 690                     if ($category) {
 691                         if (empty($fields[$category_l])) {
 692                             // Empty category means use default.
 693                             $course->category = $defaultcategory;
 694                         } else if ($coursecategory = $DB->get_record('course_categories', array($localcategoryfield=>$fields[$category_l]), 'id')) {
 695                             // Yay, correctly specified category!
 696                             $course->category = $coursecategory->id;
 697                             unset($coursecategory);
 698                         } else {
 699                             // Bad luck, better not continue because unwanted ppl might get access to course in different category.
 700                             $trace->output('error: invalid category '.$localcategoryfield.', can not create course: '.$fields[$shortname_l], 1);
 701                             continue;
 702                         }
 703                     } else {
 704                         $course->category = $defaultcategory;
 705                     }
 706                     $createcourses[] = $course;
 707                 }
 708             }
 709             $rs->Close();
 710         } else {
 711             $extdb->Close();
 712             $trace->output('Error reading data from the external course table');
 713             $trace->finished();
 714             return 4;
 715         }
 716         if ($createcourses) {
 717             require_once("$CFG->dirroot/course/lib.php");
 718
 719             $templatecourse = $this->get_config('templatecourse');
 720
 721             $template = false;
 722             if ($templatecourse) {
 723                 if ($template = $DB->get_record('course', array('shortname'=>$templatecourse))) {
 724                     $template = fullclone(course_get_format($template)->get_course());
 725                     unset($template->id);
 726                     unset($template->fullname);
 727                     unset($template->shortname);
 728                     unset($template->idnumber);
 729                 } else {
 730                     $trace->output("can not find template for new course!", 1);
 731                 }
 732             }
 733             if (!$template) {
 734                 $courseconfig = get_config('moodlecourse');
 735                 $template = new stdClass();
 736                 $template->summary        = '';
 737                 $template->summaryformat  = FORMAT_HTML;
 738                 $template->format         = $courseconfig->format;
 739                 $template->newsitems      = $courseconfig->newsitems;
 740                 $template->showgrades     = $courseconfig->showgrades;
 741                 $template->showreports    = $courseconfig->showreports;
 742                 $template->maxbytes       = $courseconfig->maxbytes;
 743                 $template->groupmode      = $courseconfig->groupmode;
 744                 $template->groupmodeforce = $courseconfig->groupmodeforce;
 745                 $template->visible        = $courseconfig->visible;
 746                 $template->lang           = $courseconfig->lang;
 747                 $template->groupmodeforce = $courseconfig->groupmodeforce;
 748             }
 749
 750             foreach ($createcourses as $fields) {
 751                 $newcourse = clone($template);
 752                 $newcourse->fullname  = $fields->fullname;
 753                 $newcourse->shortname = $fields->shortname;
 754                 $newcourse->idnumber  = $fields->idnumber;
 755                 $newcourse->category  = $fields->category;
 756
 757                 // Detect duplicate data once again, above we can not find duplicates
 758                 // in external data using DB collation rules...
 759                 if ($DB->record_exists('course', array('shortname' => $newcourse->shortname))) {
 760                     $trace->output("can not insert new course, duplicate shortname detected: ".$newcourse->shortname, 1);
 761                     continue;
 762                 } else if (!empty($newcourse->idnumber) and $DB->record_exists('course', array('idnumber' => $newcourse->idnumber))) {
 763                     $trace->output("can not insert new course, duplicate idnumber detected: ".$newcourse->idnumber, 1);
 764                     continue;
 765                 }
 766                 $c = create_course($newcourse);
 767                 $trace->output("creating course: $c->id, $c->fullname, $c->shortname, $c->idnumber, $c->category", 1);
 768             }
 769
 770             unset($createcourses);
 771             unset($template);
 772         }
 773
 774         // Close db connection.
 775         $extdb->Close();
 776
 777         $trace->output('...course synchronisation finished.');
 778         $trace->finished();
 779
 780         return 0;
 781     }
 782
 783     protected function db_get_sql($table, array $conditions, array $fields, $distinct = false, $sort = "") {
 784         $fields = $fields ? implode(',', $fields) : "*";
 785         $where = array();
 786         if ($conditions) {
 787             foreach ($conditions as $key=>$value) {
 788                 $value = $this->db_encode($this->db_addslashes($value));
 789
 790                 $where[] = "$key = '$value'";
 791             }
 792         }
 793         $where = $where ? "WHERE ".implode(" AND ", $where) : "";
 794         $sort = $sort ? "ORDER BY $sort" : "";
 795         $distinct = $distinct ? "DISTINCT" : "";
 796         $sql = "SELECT $distinct $fields
 797                   FROM $table
 798                  $where
 799                   $sort";
 800
 801         return $sql;
 802     }
 803
 804     /**
 805      * Tries to make connection to the external database.
 806      *
 807      * @return null|ADONewConnection
 808      */
 809     protected function db_init() {
 810         global $CFG;
 811
 812         require_once($CFG->libdir.'/adodb/adodb.inc.php');
 813
 814         // Connect to the external database (forcing new connection).
 815         $extdb = ADONewConnection($this->get_config('dbtype'));
 816         if ($this->get_config('debugdb')) {
 817             $extdb->debug = true;
 818             ob_start(); // Start output buffer to allow later use of the page headers.
 819         }
 820
 821         // The dbtype my contain the new connection URL, so make sure we are not connected yet.
 822         if (!$extdb->IsConnected()) {
 823             $result = $extdb->Connect($this->get_config('dbhost'), $this->get_config('dbuser'), $this->get_config('dbpass'), $this->get_config('dbname'), true);
 824             if (!$result) {
 825                 return null;
 826             }
 827         }
 828
 829         $extdb->SetFetchMode(ADODB_FETCH_ASSOC);
 830         if ($this->get_config('dbsetupsql')) {
 831             $extdb->Execute($this->get_config('dbsetupsql'));
 832         }
 833         return $extdb;
 834     }
 835
 836     protected function db_addslashes($text) {
 837         // Use custom made function for now - it is better to not rely on adodb or php defaults.
 838         if ($this->get_config('dbsybasequoting')) {
 839             $text = str_replace('\\', '\\\\', $text);
 840             $text = str_replace(array('\'', '"', "\0"), array('\\\'', '\\"', '\\0'), $text);
 841         } else {
 842             $text = str_replace("'", "''", $text);
 843         }
 844         return $text;
 845     }
 846
 847     protected function db_encode($text) {
 848         $dbenc = $this->get_config('dbencoding');
 849         if (empty($dbenc) or $dbenc == 'utf-8') {
 850             return $text;
 851         }
 852         if (is_array($text)) {
 853             foreach($text as $k=>$value) {
 854                 $text[$k] = $this->db_encode($value);
 855             }
 856             return $text;
 857         } else {
 858             return textlib::convert($text, 'utf-8', $dbenc);
 859         }
 860     }
 861
 862     protected function db_decode($text) {
 863         $dbenc = $this->get_config('dbencoding');
 864         if (empty($dbenc) or $dbenc == 'utf-8') {
 865             return $text;
 866         }
 867         if (is_array($text)) {
 868             foreach($text as $k=>$value) {
 869                 $text[$k] = $this->db_decode($value);
 870             }
 871             return $text;
 872         } else {
 873             return textlib::convert($text, $dbenc, 'utf-8');
 874         }
 875     }
 876
 877     /**
 878      * Automatic enrol sync executed during restore.
 879      * @param stdClass $course course record
 880      */
 881     public function restore_sync_course($course) {
 882         $trace = new null_progress_trace();
 883         $this->sync_enrolments($trace, $course->id);
 884     }
 885
 886     /**
 887      * Restore instance and map settings.
 888      *
 889      * @param restore_enrolments_structure_step $step
 890      * @param stdClass $data
 891      * @param stdClass $course
 892      * @param int $oldid
 893      */
 894     public function restore_instance(restore_enrolments_structure_step $step, stdClass $data, $course, $oldid) {
 895         global $DB;
 896
 897         if ($instance = $DB->get_record('enrol', array('courseid'=>$course->id, 'enrol'=>$this->get_name()))) {
 898             $instanceid = $instance->id;
 899         } else {
 900             $instanceid = $this->add_instance($course);
 901         }
 902         $step->set_mapping('enrol', $oldid, $instanceid);
 903     }
 904
 905     /**
 906      * Restore user enrolment.
 907      *
 908      * @param restore_enrolments_structure_step $step
 909      * @param stdClass $data
 910      * @param stdClass $instance
 911      * @param int $oldinstancestatus
 912      * @param int $userid
 913      */
 914     public function restore_user_enrolment(restore_enrolments_structure_step $step, $data, $instance, $userid, $oldinstancestatus) {
 915         global $DB;
 916
 917         if ($this->get_config('unenrolaction') == ENROL_EXT_REMOVED_UNENROL) {
 918             // Enrolments were already synchronised in restore_instance(), we do not want any suspended leftovers.
 919             return;
 920         }
 921         if (!$DB->record_exists('user_enrolments', array('enrolid'=>$instance->id, 'userid'=>$userid))) {
 922             $this->enrol_user($instance, $userid, null, 0, 0, ENROL_USER_SUSPENDED);
 923         }
 924     }
 925
 926     /**
 927      * Restore role assignment.
 928      *
 929      * @param stdClass $instance
 930      * @param int $roleid
 931      * @param int $userid
 932      * @param int $contextid
 933      */
 934     public function restore_role_assignment($instance, $roleid, $userid, $contextid) {
 935         if ($this->get_config('unenrolaction') == ENROL_EXT_REMOVED_UNENROL or $this->get_config('unenrolaction') == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
 936             // Role assignments were already synchronised in restore_instance(), we do not want any leftovers.
 937             return;
 938         }
 939         role_assign($roleid, $userid, $contextid, 'enrol_'.$this->get_name(), $instance->id);
 940     }
 941 }