enrol/database/lib.php

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