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