[Summary view]

Differences Between: [Versions 311 and 402] [Versions 311 and 403]

   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   * Privacy class for requesting user data.
  18   *
  19   * @package    core_calendar
  20   * @copyright  2018 Zig Tan <zig@moodle.com>
  21   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  22   */
  23  
  24  namespace core_calendar\privacy;
  25  
  26  defined('MOODLE_INTERNAL') || die();
  27  
  28  use \core_privacy\local\metadata\collection;
  29  use \core_privacy\local\request\approved_contextlist;
  30  use \core_privacy\local\request\context;
  31  use \core_privacy\local\request\contextlist;
  32  use \core_privacy\local\request\transform;
  33  use \core_privacy\local\request\writer;
  34  use \core_privacy\local\request\userlist;
  35  use \core_privacy\local\request\approved_userlist;
  36  
  37  /**
  38   * Privacy Subsystem for core_calendar implementing metadata, plugin, and user_preference providers.
  39   *
  40   * @package    core_calendar
  41   * @copyright  2018 Zig Tan <zig@moodle.com>
  42   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  43   */
  44  class provider implements
  45          \core_privacy\local\metadata\provider,
  46          \core_privacy\local\request\plugin\provider,
  47          \core_privacy\local\request\core_userlist_provider,
  48          \core_privacy\local\request\user_preference_provider
  49  {
  50  
  51      /**
  52       * Provides meta data that is stored about a user with core_calendar.
  53       *
  54       * @param  collection $collection A collection of meta data items to be added to.
  55       * @return  collection Returns the collection of metadata.
  56       */
  57      public static function get_metadata(collection $collection) : collection {
  58          // The calendar 'event' table contains user data.
  59          $collection->add_database_table(
  60              'event',
  61              [
  62                  'name' => 'privacy:metadata:calendar:event:name',
  63                  'description' => 'privacy:metadata:calendar:event:description',
  64                  'eventtype' => 'privacy:metadata:calendar:event:eventtype',
  65                  'timestart' => 'privacy:metadata:calendar:event:timestart',
  66                  'timeduration' => 'privacy:metadata:calendar:event:timeduration',
  67              ],
  68              'privacy:metadata:calendar:event'
  69          );
  70  
  71          // The calendar 'event_subscriptions' table contains user data.
  72          $collection->add_database_table(
  73              'event_subscriptions',
  74              [
  75                  'name' => 'privacy:metadata:calendar:event_subscriptions:name',
  76                  'url' => 'privacy:metadata:calendar:event_subscriptions:url',
  77                  'eventtype' => 'privacy:metadata:calendar:event_subscriptions:eventtype',
  78              ],
  79              'privacy:metadata:calendar:event_subscriptions'
  80          );
  81  
  82          // The calendar user preference setting 'calendar_savedflt'.
  83          $collection->add_user_preference(
  84              'calendar_savedflt',
  85              'privacy:metadata:calendar:preferences:calendar_savedflt'
  86          );
  87  
  88          return $collection;
  89      }
  90  
  91      /**
  92       * Get the list of contexts that contain calendar user information for the specified user.
  93       *
  94       * @param   int $userid The user to search.
  95       * @return  contextlist   $contextlist  The contextlist containing the list of contexts used in this plugin.
  96       */
  97      public static function get_contexts_for_userid(int $userid) : contextlist {
  98          $contextlist = new contextlist();
  99  
 100          // Calendar Events can exist at Site, Course Category, Course, Course Group, User, or Course Modules contexts.
 101          $params = [
 102              'sitecontext'        => CONTEXT_SYSTEM,
 103              'categorycontext'    => CONTEXT_COURSECAT,
 104              'coursecontext'      => CONTEXT_COURSE,
 105              'groupcontext'       => CONTEXT_COURSE,
 106              'usercontext'        => CONTEXT_USER,
 107              'cuserid'            => $userid,
 108              'modulecontext'      => CONTEXT_MODULE,
 109              'muserid'            => $userid
 110          ];
 111  
 112          // Get contexts of Calendar Events for the owner.
 113          $sql = "SELECT ctx.id
 114                    FROM {context} ctx
 115                    JOIN {event} e ON
 116                         (e.eventtype = 'site' AND ctx.contextlevel = :sitecontext) OR
 117                         (e.categoryid = ctx.instanceid AND e.eventtype = 'category' AND ctx.contextlevel = :categorycontext) OR
 118                         (e.courseid = ctx.instanceid AND e.eventtype = 'course' AND ctx.contextlevel = :coursecontext) OR
 119                         (e.courseid = ctx.instanceid AND e.eventtype = 'group' AND ctx.contextlevel = :groupcontext) OR
 120                         (e.userid = ctx.instanceid AND e.eventtype = 'user' AND ctx.contextlevel = :usercontext)
 121                   WHERE e.userid = :cuserid
 122                   UNION
 123                  SELECT ctx.id
 124                    FROM {context} ctx
 125                    JOIN {course_modules} cm ON cm.id = ctx.instanceid AND ctx.contextlevel = :modulecontext
 126                    JOIN {modules} m ON m.id = cm.module
 127                    JOIN {event} e ON e.modulename = m.name AND e.courseid = cm.course AND e.instance = cm.instance
 128                   WHERE e.userid = :muserid";
 129          $contextlist->add_from_sql($sql, $params);
 130  
 131          // Calendar Subscriptions can exist at Site, Course Category, Course, Course Group, or User contexts.
 132          $params = [
 133              'sitecontext'       => CONTEXT_SYSTEM,
 134              'categorycontext'   => CONTEXT_COURSECAT,
 135              'coursecontext'     => CONTEXT_COURSE,
 136              'groupcontext'      => CONTEXT_COURSE,
 137              'usercontext'       => CONTEXT_USER,
 138              'userid'            => $userid
 139          ];
 140  
 141          // Get contexts for Calendar Subscriptions for the owner.
 142          $sql = "SELECT ctx.id
 143                    FROM {context} ctx
 144                    JOIN {event_subscriptions} s ON
 145                         (s.eventtype = 'site' AND ctx.contextlevel = :sitecontext) OR
 146                         (s.categoryid = ctx.instanceid AND s.eventtype = 'category' AND ctx.contextlevel = :categorycontext) OR
 147                         (s.courseid = ctx.instanceid AND s.eventtype = 'course' AND ctx.contextlevel = :coursecontext) OR
 148                         (s.courseid = ctx.instanceid AND s.eventtype = 'group' AND ctx.contextlevel = :groupcontext) OR
 149                         (s.userid = ctx.instanceid AND s.eventtype = 'user' AND ctx.contextlevel = :usercontext)
 150                   WHERE s.userid = :userid";
 151          $contextlist->add_from_sql($sql, $params);
 152  
 153          // Return combined contextlist for Calendar Events & Calendar Subscriptions.
 154          return $contextlist;
 155      }
 156  
 157      /**
 158       * Get the list of users within a specific context.
 159       *
 160       * @param userlist $userlist The userlist containing the list of users who have data in this context/plugin combination.
 161       */
 162      public static function get_users_in_context(userlist $userlist) {
 163          global $DB;
 164  
 165          $context = $userlist->get_context();
 166  
 167          // Calendar Events can exist at Site (CONTEXT_SYSTEM), Course Category (CONTEXT_COURSECAT),
 168          // Course and Course Group (CONTEXT_COURSE), User (CONTEXT_USER), or Course Modules (CONTEXT_MODULE) contexts.
 169          if ($context->contextlevel == CONTEXT_MODULE) {
 170              $params = ['cmid' => $context->instanceid];
 171  
 172              $sql = "SELECT e.userid
 173                        FROM {course_modules} cm
 174                        JOIN {modules} m ON m.id = cm.module
 175                        JOIN {event} e ON e.modulename = m.name AND e.courseid = cm.course AND e.instance = cm.instance
 176                       WHERE cm.id = :cmid";
 177  
 178              $userlist->add_from_sql('userid', $sql, $params);
 179          } else if ($context->contextlevel == CONTEXT_SYSTEM) {
 180              // Get contexts of Calendar Events for the owner.
 181              $sql = "SELECT userid FROM {event} WHERE eventtype = 'site'";
 182              $userlist->add_from_sql('userid', $sql, []);
 183  
 184              // Get contexts for Calendar Subscriptions for the owner.
 185              $sql = "SELECT userid FROM {event_subscriptions} WHERE eventtype = 'site'";
 186              $userlist->add_from_sql('userid', $sql, []);
 187          } else if (in_array($context->contextlevel, [CONTEXT_COURSECAT, CONTEXT_COURSE, CONTEXT_USER])) {
 188              $eventfields = [
 189                  CONTEXT_COURSECAT   => 'categoryid',
 190                  CONTEXT_COURSE      => 'courseid',
 191                  CONTEXT_USER        => 'userid'
 192              ];
 193              $eventfield = $eventfields[$context->contextlevel];
 194  
 195              $eventtypes = [
 196                  CONTEXT_COURSECAT   => 'category',
 197                  CONTEXT_COURSE      => ['course' , 'group'],
 198                  CONTEXT_USER        => 'user'
 199              ];
 200              list($eventtypesql, $eventtypeparams) = $DB->get_in_or_equal($eventtypes[$context->contextlevel], SQL_PARAMS_NAMED);
 201  
 202              $params = $eventtypeparams + ['instanceid' => $context->instanceid];
 203  
 204              // Get contexts of Calendar Events for the owner.
 205              $sql = "SELECT userid
 206                        FROM {event}
 207                       WHERE eventtype $eventtypesql
 208                             AND $eventfield = :instanceid";
 209              $userlist->add_from_sql('userid', $sql, $params);
 210  
 211              // Get contexts for Calendar Subscriptions for the owner.
 212              $sql = "SELECT userid
 213                        FROM {event_subscriptions}
 214                       WHERE eventtype $eventtypesql
 215                             AND $eventfield = :instanceid";
 216              $userlist->add_from_sql('userid', $sql, $params);
 217          }
 218      }
 219  
 220      /**
 221       * Export all user data for the specified user, in the specified contexts.
 222       *
 223       * @param   approved_contextlist $contextlist The approved contexts to export information for.
 224       */
 225      public static function export_user_data(approved_contextlist $contextlist) {
 226          if (empty($contextlist)) {
 227              return;
 228          }
 229  
 230          self::export_user_calendar_event_data($contextlist);
 231          self::export_user_calendar_subscription_data($contextlist);
 232      }
 233  
 234      /**
 235       * Export all user preferences for the plugin.
 236       *
 237       * @param   int $userid The userid of the user whose data is to be exported.
 238       */
 239      public static function export_user_preferences(int $userid) {
 240          $calendarsavedflt = get_user_preferences('calendar_savedflt', null, $userid);
 241  
 242          if (null !== $calendarsavedflt) {
 243              writer::export_user_preference(
 244                  'core_calendar',
 245                  'calendarsavedflt',
 246                  $calendarsavedflt,
 247                  get_string('privacy:metadata:calendar:preferences:calendar_savedflt', 'core_calendar')
 248              );
 249          }
 250      }
 251  
 252      /**
 253       * Delete all Calendar Event and Calendar Subscription data for all users in the specified context.
 254       *
 255       * @param   context $context Transform the specific context to delete data for.
 256       */
 257      public static function delete_data_for_all_users_in_context(\context $context) {
 258          // Delete all Calendar Events in the specified context in batches.
 259          if ($eventids = array_keys(self::get_calendar_event_ids_by_context($context))) {
 260              self::delete_batch_records('event', 'id', $eventids);
 261          }
 262  
 263          // Delete all Calendar Subscriptions in the specified context in batches.
 264          if ($subscriptionids = array_keys(self::get_calendar_subscription_ids_by_context($context))) {
 265              self::delete_batch_records('event_subscriptions', 'id', $subscriptionids);
 266          }
 267      }
 268  
 269      /**
 270       * Delete multiple users within a single context.
 271       *
 272       * @param approved_userlist $userlist The approved context and user information to delete information for.
 273       */
 274      public static function delete_data_for_users(approved_userlist $userlist) {
 275          $context = $userlist->get_context();
 276          $userids = $userlist->get_userids();
 277  
 278          $allowedcontexts = [
 279              CONTEXT_SYSTEM,
 280              CONTEXT_COURSECAT,
 281              CONTEXT_COURSE,
 282              CONTEXT_MODULE,
 283              CONTEXT_USER
 284          ];
 285  
 286          if (!in_array($context->contextlevel, $allowedcontexts)) {
 287              return;
 288          }
 289  
 290          if (empty($userids)) {
 291              return;
 292          }
 293  
 294          // Delete all Calendar Events in the specified context in batches.
 295          if ($eventids = array_keys(self::get_calendar_event_ids_by_context($context, $userids))) {
 296              self::delete_batch_records('event', 'id', $eventids);
 297          }
 298  
 299          // Delete all Calendar Subscriptions in the specified context in batches.
 300          if ($subscriptionids = array_keys(self::get_calendar_subscription_ids_by_context($context, $userids))) {
 301              self::delete_batch_records('event_subscriptions', 'id', $subscriptionids);
 302          }
 303      }
 304  
 305      /**
 306       * Delete all user data for the specified user, in the specified contexts.
 307       *
 308       * @param   approved_contextlist $contextlist The approved contexts and user information to delete information for.
 309       */
 310      public static function delete_data_for_user(approved_contextlist $contextlist) {
 311          if (empty($contextlist)) {
 312              return;
 313          }
 314  
 315          // Delete all Calendar Events for the owner and specified contexts in batches.
 316          $eventdetails = self::get_calendar_event_details_by_contextlist($contextlist);
 317          $eventids = [];
 318          foreach ($eventdetails as $eventdetail) {
 319              $eventids[] = $eventdetail->eventid;
 320          }
 321          $eventdetails->close();
 322          self::delete_batch_records('event', 'id', $eventids);
 323  
 324          // Delete all Calendar Subscriptions for the owner and specified contexts in batches.
 325          $subscriptiondetails = self::get_calendar_subscription_details_by_contextlist($contextlist);
 326          $subscriptionids = [];
 327          foreach ($subscriptiondetails as $subscriptiondetail) {
 328              $subscriptionids[] = $subscriptiondetail->subscriptionid;
 329          }
 330          $subscriptiondetails->close();
 331          self::delete_batch_records('event_subscriptions', 'id', $subscriptionids);
 332      }
 333  
 334      /**
 335       * Helper function to export Calendar Events data by a User's contextlist.
 336       *
 337       * @param approved_contextlist $contextlist
 338       * @throws \coding_exception
 339       */
 340      protected static function export_user_calendar_event_data(approved_contextlist $contextlist) {
 341          // Calendar Events can exist at Site, Course Category, Course, Course Group, User, or Course Modules contexts.
 342          $eventdetails = self::get_calendar_event_details_by_contextlist($contextlist);
 343  
 344          // Multiple Calendar Events of the same eventtype and time can exist for a context, so collate them for export.
 345          $eventrecords = [];
 346          foreach ($eventdetails as $eventdetail) {
 347              // Create an array key based on the contextid, eventtype, and time.
 348              $key = $eventdetail->contextid . $eventdetail->eventtype . $eventdetail->timestart;
 349  
 350              if (array_key_exists($key, $eventrecords) === false) {
 351                  $eventrecords[$key] = [ $eventdetail ];
 352              } else {
 353                  $eventrecords[$key] = array_merge($eventrecords[$key], [$eventdetail]);
 354              }
 355          }
 356          $eventdetails->close();
 357  
 358          // Export Calendar Event data.
 359          foreach ($eventrecords as $eventrecord) {
 360              $index = (count($eventrecord) > 1) ? 1 : 0;
 361  
 362              foreach ($eventrecord as $event) {
 363                  // Export the events using the structure Calendar/Events/{datetime}/{eventtype}-event.json.
 364                  $subcontexts = [
 365                      get_string('calendar', 'calendar'),
 366                      get_string('events', 'calendar'),
 367                      date('c', $event->timestart)
 368                  ];
 369                  $name = $event->eventtype . '-event';
 370  
 371                  // Use name {eventtype}-event-{index}.json if multiple eventtypes and time exists at the same context.
 372                  if ($index != 0) {
 373                      $name .= '-' . $index;
 374                      $index++;
 375                  }
 376  
 377                  $eventdetails = (object) [
 378                      'name' => $event->name,
 379                      'description' => $event->description,
 380                      'location' => $event->location,
 381                      'eventtype' => $event->eventtype,
 382                      'timestart' => transform::datetime($event->timestart),
 383                      'timeduration' => $event->timeduration
 384                  ];
 385  
 386                  $context = \context::instance_by_id($event->contextid);
 387                  writer::with_context($context)->export_related_data($subcontexts, $name, $eventdetails);
 388              }
 389          }
 390      }
 391  
 392      /**
 393       * Helper function to export Calendar Subscriptions data by a User's contextlist.
 394       *
 395       * @param approved_contextlist $contextlist
 396       * @throws \coding_exception
 397       */
 398      protected static function export_user_calendar_subscription_data(approved_contextlist $contextlist) {
 399          // Calendar Subscriptions can exist at Site, Course Category, Course, Course Group, or User contexts.
 400          $subscriptiondetails = self::get_calendar_subscription_details_by_contextlist($contextlist);
 401  
 402          // Multiple Calendar Subscriptions of the same eventtype can exist for a context, so collate them for export.
 403          $subscriptionrecords = [];
 404          foreach ($subscriptiondetails as $subscriptiondetail) {
 405              // Create an array key based on the contextid and eventtype.
 406              $key = $subscriptiondetail->contextid . $subscriptiondetail->eventtype;
 407  
 408              if (array_key_exists($key, $subscriptionrecords) === false) {
 409                  $subscriptionrecords[$key] = [ $subscriptiondetail ];
 410              } else {
 411                  $subscriptionrecords[$key] = array_merge($subscriptionrecords[$key], [$subscriptiondetail]);
 412              }
 413          }
 414          $subscriptiondetails->close();
 415  
 416          // Export Calendar Subscription data.
 417          foreach ($subscriptionrecords as $subscriptionrecord) {
 418              $index = (count($subscriptionrecord) > 1) ? 1 : 0;
 419  
 420              foreach ($subscriptionrecord as $subscription) {
 421                  // Export the events using the structure Calendar/Subscriptions/{eventtype}-subscription.json.
 422                  $subcontexts = [
 423                      get_string('calendar', 'calendar'),
 424                      get_string('subscriptions', 'calendar')
 425                  ];
 426                  $name = $subscription->eventtype . '-subscription';
 427  
 428                  // Use name {eventtype}-subscription-{index}.json if multiple eventtypes exists at the same context.
 429                  if ($index != 0) {
 430                      $name .= '-' . $index;
 431                      $index++;
 432                  }
 433  
 434                  $context = \context::instance_by_id($subscription->contextid);
 435                  writer::with_context($context)->export_related_data($subcontexts, $name, $subscription);
 436              }
 437          }
 438      }
 439  
 440      /**
 441       * Helper function to return all Calendar Event id results for a specified context and optionally
 442       * included user list.
 443       *
 444       * @param \context $context
 445       * @param array $userids
 446       * @return array|null
 447       * @throws \dml_exception
 448       */
 449      protected static function get_calendar_event_ids_by_context(\context $context, $userids = array()) {
 450          global $DB;
 451  
 452          // Calendar Events can exist at Site (CONTEXT_SYSTEM), Course Category (CONTEXT_COURSECAT),
 453          // Course and Course Group (CONTEXT_COURSE), User (CONTEXT_USER), or Course Modules (CONTEXT_MODULE) contexts.
 454          if (!in_array($context->contextlevel, [CONTEXT_SYSTEM, CONTEXT_COURSECAT, CONTEXT_COURSE, CONTEXT_USER, CONTEXT_MODULE])) {
 455              return [];
 456          }
 457  
 458          $whereusersql = '';
 459          $userparams = array();
 460          if (!empty($userids)) {
 461              list($usersql, $userparams) = $DB->get_in_or_equal($userids, SQL_PARAMS_NAMED);
 462              $whereusersql = "AND e.userid {$usersql}";
 463          }
 464  
 465          if ($context->contextlevel == CONTEXT_MODULE) { // Course Module events.
 466              $params = ['cmid' => $context->instanceid];
 467  
 468              // Get Calendar Events for the specified Course Module context.
 469              $sql = "SELECT DISTINCT e.id AS eventid
 470                        FROM {course_modules} cm
 471                        JOIN {modules} m ON m.id = cm.module
 472                        JOIN {event} e ON e.modulename = m.name AND e.courseid = cm.course AND e.instance = cm.instance
 473                       WHERE cm.id = :cmid
 474                             $whereusersql";
 475          } else if ($context->contextlevel == CONTEXT_SYSTEM) { // Site events.
 476              $params = [];
 477              $sql = "SELECT DISTINCT e.id AS eventid
 478                        FROM {event} e
 479                       WHERE e.eventtype = 'site'
 480                             $whereusersql";
 481          } else { // The rest.
 482              $eventfields = [
 483                  CONTEXT_COURSECAT   => 'categoryid',
 484                  CONTEXT_COURSE      => 'courseid',
 485                  CONTEXT_USER        => 'userid'
 486              ];
 487              $eventfield = $eventfields[$context->contextlevel];
 488  
 489              $eventtypes = [
 490                  CONTEXT_COURSECAT   => 'category',
 491                  CONTEXT_COURSE      => ['course' , 'group'],
 492                  CONTEXT_USER        => 'user'
 493              ];
 494              list($eventtypesql, $eventtypeparams) = $DB->get_in_or_equal($eventtypes[$context->contextlevel], SQL_PARAMS_NAMED);
 495  
 496              $params = $eventtypeparams + ['instanceid' => $context->instanceid];
 497  
 498              // Get Calendar Events for the specified Moodle context.
 499              $sql = "SELECT DISTINCT e.id AS eventid
 500                        FROM {event} e
 501                       WHERE e.eventtype $eventtypesql
 502                             AND e.{$eventfield} = :instanceid
 503                             $whereusersql";
 504          }
 505  
 506          $params += $userparams;
 507  
 508          return $DB->get_records_sql($sql, $params);
 509      }
 510  
 511      /**
 512       * Helper function to return all Calendar Subscription id results for a specified context and optionally
 513       * included user list.
 514       *
 515       * @param \context $context
 516       * @param array $userids
 517       * @return array
 518       * @throws \dml_exception
 519       */
 520      protected static function get_calendar_subscription_ids_by_context(\context $context, $userids = array()) {
 521          global $DB;
 522  
 523          // Calendar Subscriptions can exist at Site (CONTEXT_SYSTEM), Course Category (CONTEXT_COURSECAT),
 524          // Course and Course Group (CONTEXT_COURSE), or User (CONTEXT_USER) contexts.
 525          if (!in_array($context->contextlevel, [CONTEXT_SYSTEM, CONTEXT_COURSECAT, CONTEXT_COURSE, CONTEXT_USER])) {
 526              return [];
 527          }
 528  
 529          $whereusersql = '';
 530          $userparams = array();
 531          if (!empty($userids)) {
 532              list($usersql, $userparams) = $DB->get_in_or_equal($userids, SQL_PARAMS_NAMED);
 533              $whereusersql = "AND s.userid {$usersql}";
 534          }
 535  
 536          if ($context->contextlevel == CONTEXT_SYSTEM) {
 537              $params = [];
 538  
 539              // Get Calendar Subscriptions for the system context.
 540              $sql = "SELECT DISTINCT s.id AS subscriptionid
 541                        FROM {event_subscriptions} s
 542                       WHERE s.eventtype = 'site'
 543                             $whereusersql";
 544          } else {
 545              $eventfields = [
 546                  CONTEXT_COURSECAT   => 'categoryid',
 547                  CONTEXT_COURSE      => 'courseid',
 548                  CONTEXT_USER        => 'userid'
 549              ];
 550              $eventfield = $eventfields[$context->contextlevel];
 551  
 552              $eventtypes = [
 553                  CONTEXT_COURSECAT   => 'category',
 554                  CONTEXT_COURSE      => ['course' , 'group'],
 555                  CONTEXT_USER        => 'user'
 556              ];
 557              list($eventtypesql, $eventtypeparams) = $DB->get_in_or_equal($eventtypes[$context->contextlevel], SQL_PARAMS_NAMED);
 558  
 559              $params = $eventtypeparams + ['instanceid' => $context->instanceid];
 560  
 561              // Get Calendar Subscriptions for the specified context.
 562              $sql = "SELECT DISTINCT s.id AS subscriptionid
 563                        FROM {event_subscriptions} s
 564                       WHERE s.eventtype $eventtypesql
 565                             AND s.{$eventfield} = :instanceid
 566                             $whereusersql";
 567          }
 568  
 569          $params += $userparams;
 570  
 571          return $DB->get_records_sql($sql, $params);
 572      }
 573  
 574      /**
 575       * Helper function to return the Calendar Events for a given user and context list.
 576       *
 577       * @param approved_contextlist $contextlist
 578       * @return array
 579       * @throws \coding_exception
 580       * @throws \dml_exception
 581       */
 582      protected static function get_calendar_event_details_by_contextlist(approved_contextlist $contextlist) {
 583          global $DB;
 584  
 585          $userid = $contextlist->get_user()->id;
 586  
 587          list($contextsql1, $contextparams1) = $DB->get_in_or_equal($contextlist->get_contextids(), SQL_PARAMS_NAMED);
 588          list($contextsql2, $contextparams2) = $DB->get_in_or_equal($contextlist->get_contextids(), SQL_PARAMS_NAMED);
 589  
 590          // Calendar Events can exist at Site, Course Category, Course, Course Group, User, or Course Modules contexts.
 591          $params = [
 592              'sitecontext'       => CONTEXT_SYSTEM,
 593              'categorycontext'   => CONTEXT_COURSECAT,
 594              'coursecontext'     => CONTEXT_COURSE,
 595              'groupcontext'      => CONTEXT_COURSE,
 596              'usercontext'       => CONTEXT_USER,
 597              'cuserid'           => $userid,
 598              'modulecontext'     => CONTEXT_MODULE,
 599              'muserid'           => $userid
 600          ];
 601          $params += $contextparams1;
 602          $params += $contextparams2;
 603  
 604          // Get Calendar Events details for the approved contexts and the owner.
 605          $sql = "SELECT ctxid as contextid,
 606                         details.id as eventid,
 607                         details.name as name,
 608                         details.description as description,
 609                         details.location as location,
 610                         details.eventtype as eventtype,
 611                         details.timestart as timestart,
 612                         details.timeduration as timeduration
 613                    FROM (
 614                            SELECT e.id AS id,
 615                                   ctx.id AS ctxid
 616                              FROM {context} ctx
 617                        INNER JOIN {event} e ON
 618                                   (e.eventtype = 'site' AND ctx.contextlevel = :sitecontext) OR
 619                                   (e.categoryid = ctx.instanceid AND e.eventtype = 'category' AND ctx.contextlevel = :categorycontext) OR
 620                                   (e.courseid = ctx.instanceid AND e.eventtype = 'course' AND ctx.contextlevel = :coursecontext) OR
 621                                   (e.courseid = ctx.instanceid AND e.eventtype = 'group' AND ctx.contextlevel = :groupcontext) OR
 622                                   (e.userid = ctx.instanceid AND e.eventtype = 'user' AND ctx.contextlevel = :usercontext)
 623                             WHERE e.userid = :cuserid
 624                               AND ctx.id {$contextsql1}
 625                             UNION
 626                            SELECT e.id AS id,
 627                                   ctx.id AS ctxid
 628                              FROM {context} ctx
 629                        INNER JOIN {course_modules} cm ON cm.id = ctx.instanceid AND ctx.contextlevel = :modulecontext
 630                        INNER JOIN {modules} m ON m.id = cm.module
 631                        INNER JOIN {event} e ON e.modulename = m.name AND e.courseid = cm.course AND e.instance = cm.instance
 632                             WHERE e.userid = :muserid
 633                               AND ctx.id {$contextsql2}
 634                    ) ids
 635                    JOIN {event} details ON details.id = ids.id
 636                ORDER BY ids.id";
 637  
 638          return $DB->get_recordset_sql($sql, $params);
 639      }
 640  
 641      /**
 642       * Helper function to return the Calendar Subscriptions for a given user and context list.
 643       *
 644       * @param approved_contextlist $contextlist
 645       * @return array
 646       * @throws \coding_exception
 647       * @throws \dml_exception
 648       */
 649      protected static function get_calendar_subscription_details_by_contextlist(approved_contextlist $contextlist) {
 650          global $DB;
 651  
 652          $user = $contextlist->get_user();
 653  
 654          list($contextsql, $contextparams) = $DB->get_in_or_equal($contextlist->get_contextids(), SQL_PARAMS_NAMED);
 655  
 656          $params = [
 657              'sitecontext' => CONTEXT_SYSTEM,
 658              'categorycontext' => CONTEXT_COURSECAT,
 659              'coursecontext' => CONTEXT_COURSE,
 660              'groupcontext' => CONTEXT_COURSE,
 661              'usercontext' => CONTEXT_USER,
 662              'userid' => $user->id
 663          ];
 664          $params += $contextparams;
 665  
 666          // Get Calendar Subscriptions for the approved contexts and the owner.
 667          $sql = "SELECT DISTINCT
 668                         c.id as contextid,
 669                         s.id as subscriptionid,
 670                         s.name as name,
 671                         s.url as url,
 672                         s.eventtype as eventtype
 673                    FROM {context} c
 674              INNER JOIN {event_subscriptions} s ON
 675                         (s.eventtype = 'site' AND c.contextlevel = :sitecontext) OR
 676                         (s.categoryid = c.instanceid AND s.eventtype = 'category' AND c.contextlevel = :categorycontext) OR
 677                         (s.courseid = c.instanceid AND s.eventtype = 'course' AND c.contextlevel = :coursecontext) OR
 678                         (s.courseid = c.instanceid AND s.eventtype = 'group' AND c.contextlevel = :groupcontext) OR
 679                         (s.userid = c.instanceid AND s.eventtype = 'user' AND c.contextlevel = :usercontext)
 680                   WHERE s.userid = :userid
 681                     AND c.id {$contextsql}";
 682  
 683          return $DB->get_recordset_sql($sql, $params);
 684      }
 685  
 686      /**
 687       * Helper function to delete records in batches in order to minimise amount of deletion queries.
 688       *
 689       * @param string    $tablename  The table name to delete from.
 690       * @param string    $field      The table column field name to delete records by.
 691       * @param array     $values     The table column field values to delete records by.
 692       * @throws \dml_exception
 693       */
 694      protected static function delete_batch_records($tablename, $field, $values) {
 695          global $DB;
 696  
 697          // Batch deletion with an upper limit of 2000 records to minimise the number of deletion queries.
 698          $batchrecords = array_chunk($values, 2000);
 699  
 700          foreach ($batchrecords as $batchrecord) {
 701              $DB->delete_records_list($tablename, $field, $batchrecord);
 702          }
 703      }
 704  }