[Summary view]

Differences Between: [Versions 310 and 403] [Versions 311 and 403] [Versions 39 and 403] [Versions 400 and 403] [Versions 401 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  /**
  18   * Raw event retrieval strategy.
  19   *
  20   * @package    core_calendar
  21   * @copyright  2017 Cameron Ball <cameron@cameron1729.xyz>
  22   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  23   */
  24  
  25  namespace core_calendar\local\event\strategies;
  26  
  27  defined('MOODLE_INTERNAL') || die();
  28  
  29  /**
  30   * Raw event retrieval strategy.
  31   *
  32   * This strategy is based on what used to be the calendar API's get_events function.
  33   *
  34   * @copyright 2017 Cameron Ball <cameron@cameron1729.xyz>
  35   * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  36   */
  37  class raw_event_retrieval_strategy implements raw_event_retrieval_strategy_interface {
  38  
  39      public function get_raw_events(
  40          array $usersfilter = null,
  41          array $groupsfilter = null,
  42          array $coursesfilter = null,
  43          array $categoriesfilter = null,
  44          array $whereconditions = null,
  45          array $whereparams = null,
  46          $ordersql = null,
  47          $offset = null,
  48          $limitnum = null,
  49          $ignorehidden = true
  50      ) {
  51          return $this->get_raw_events_legacy_implementation(
  52              !is_null($usersfilter) ? $usersfilter : true, // True means no filter in old implementation.
  53              !is_null($groupsfilter) ? $groupsfilter : true,
  54              !is_null($coursesfilter) ? $coursesfilter : true,
  55              !is_null($categoriesfilter) ? $categoriesfilter : true,
  56              $whereconditions,
  57              $whereparams,
  58              $ordersql,
  59              $offset,
  60              $limitnum,
  61              $ignorehidden
  62          );
  63      }
  64  
  65      /**
  66       * The legacy implementation with minor tweaks.
  67       *
  68       * @param array|int|boolean $users array of users, user id or boolean for all/no user events
  69       * @param array|int|boolean $groups array of groups, group id or boolean for all/no group events
  70       * @param array|int|boolean $courses array of courses, course id or boolean for all/no course events
  71       * @param array $whereconditions The conditions in the WHERE clause.
  72       * @param array $whereparams The parameters for the WHERE clause.
  73       * @param string $ordersql The ORDER BY clause.
  74       * @param int $offset Offset.
  75       * @param int $limitnum Limit.
  76       * @param boolean $ignorehidden whether to select only visible events or all events
  77       * @return array $events of selected events or an empty array if there aren't any (or there was an error)
  78       */
  79      protected function get_raw_events_legacy_implementation(
  80          $users,
  81          $groups,
  82          $courses,
  83          $categories,
  84          $whereconditions,
  85          $whereparams,
  86          $ordersql,
  87          $offset,
  88          $limitnum,
  89          $ignorehidden
  90      ) {
  91          global $DB;
  92  
  93          $params = array();
  94          // Quick test.
  95          if (empty($users) && empty($groups) && empty($courses) && empty($categories)) {
  96              return array();
  97          }
  98  
  99          if (is_numeric($users)) {
 100              $users = array($users);
 101          }
 102          if (is_numeric($groups)) {
 103              $groups = array($groups);
 104          }
 105          if (is_numeric($courses)) {
 106              $courses = array($courses);
 107          }
 108          if (is_numeric($categories)) {
 109              $categories = array($categories);
 110          }
 111  
 112          // Array of filter conditions. To be concatenated by the OR operator.
 113          $filters = [];
 114  
 115          // User filter.
 116          if (is_array($users) && !empty($users)) {
 117              // Events from a number of users.
 118              list($insqlusers, $inparamsusers) = $DB->get_in_or_equal($users, SQL_PARAMS_NAMED);
 119              $filters[] = "(e.userid $insqlusers AND e.courseid = 0 AND e.groupid = 0 AND e.categoryid = 0)";
 120              $params = array_merge($params, $inparamsusers);
 121          } else if ($users === true) {
 122              // Events from ALL users.
 123              $filters[] = "(e.userid != 0 AND e.courseid = 0 AND e.groupid = 0 AND e.categoryid = 0)";
 124          }
 125          // Boolean false (no users at all): We don't need to do anything.
 126  
 127          // Group filter.
 128          if (is_array($groups) && !empty($groups)) {
 129              // Events from a number of groups.
 130              list($insqlgroups, $inparamsgroups) = $DB->get_in_or_equal($groups, SQL_PARAMS_NAMED);
 131              $filters[] = "e.groupid $insqlgroups";
 132              $params = array_merge($params, $inparamsgroups);
 133          } else if ($groups === true) {
 134              // Events from ALL groups.
 135              $filters[] = "e.groupid != 0";
 136          }
 137          // Boolean false (no groups at all): We don't need to do anything.
 138  
 139          // Course filter.
 140          if (is_array($courses) && !empty($courses)) {
 141              list($insqlcourses, $inparamscourses) = $DB->get_in_or_equal($courses, SQL_PARAMS_NAMED);
 142              $filters[] = "(e.groupid = 0 AND e.courseid $insqlcourses)";
 143              $params = array_merge($params, $inparamscourses);
 144          } else if ($courses === true) {
 145              // Events from ALL courses.
 146              $filters[] = "(e.groupid = 0 AND e.courseid != 0)";
 147          }
 148  
 149          // Category filter.
 150          if (is_array($categories) && !empty($categories)) {
 151              list($insqlcategories, $inparamscategories) = $DB->get_in_or_equal($categories, SQL_PARAMS_NAMED);
 152              $filters[] = "(e.groupid = 0 AND e.courseid = 0 AND e.categoryid $insqlcategories)";
 153              $params = array_merge($params, $inparamscategories);
 154          } else if ($categories === true) {
 155              // Events from ALL categories.
 156              $filters[] = "(e.groupid = 0 AND e.courseid = 0 AND e.categoryid != 0)";
 157          }
 158  
 159          // Security check: if, by now, we have NOTHING in $whereclause, then it means
 160          // that NO event-selecting clauses were defined. Thus, we won't be returning ANY
 161          // events no matter what. Allowing the code to proceed might return a completely
 162          // valid query with only time constraints, thus selecting ALL events in that time frame!
 163          if (empty($filters)) {
 164              return array();
 165          }
 166  
 167          // Build our clause for the filters.
 168          $filterclause = implode(' OR ', $filters);
 169  
 170          // Array of where conditions for our query. To be concatenated by the AND operator.
 171          $whereconditions[] = "($filterclause)";
 172  
 173          // Show visible only.
 174          if ($ignorehidden) {
 175              $whereconditions[] = "(e.visible = 1)";
 176          }
 177  
 178          // Build the main query's WHERE clause.
 179          $whereclause = implode(' AND ', $whereconditions);
 180  
 181          // Build SQL subquery and conditions for filtered events based on priorities.
 182          $subquerytimeconditions = array_filter($whereconditions, function($condition) {
 183              return (strpos($condition, 'time') !== false);
 184          });
 185          $subquerywhere = '';
 186          $subqueryconditions = [];
 187          $subqueryparams = [];
 188          $allusercourses = [];
 189  
 190          if (is_array($users) && !empty($users)) {
 191              $userrecords = $DB->get_records_sql("SELECT * FROM {user} WHERE id $insqlusers", $inparamsusers);
 192              foreach ($userrecords as $userrecord) {
 193                  // Get the user's courses. Otherwise, get the default courses being shown by the calendar.
 194                  $usercourses = calendar_get_default_courses(null, 'id, category, groupmode, groupmodeforce',
 195                          false, $userrecord->id);
 196  
 197                  // Set calendar filters.
 198                  list($usercourses, $usergroups, $user) = calendar_set_filters($usercourses, true, $userrecord);
 199  
 200                  $filteredcourses = is_array($courses) ? $courses : [$courses];
 201                  $filteredcourses = array_filter($usercourses, function($course) use ($filteredcourses) {
 202                      return in_array($course, $filteredcourses);
 203                  });
 204  
 205                  $allusercourses = array_merge($allusercourses, $filteredcourses);
 206  
 207                  // Flag to indicate whether the query needs to exclude group overrides.
 208                  $viewgroupsonly = false;
 209  
 210                  if ($user) {
 211                      // Set filter condition for the user's events.
 212                      // Even though $user is a single scalar, we still use get_in_or_equal() because we are inside a loop.
 213                      list($inusers, $inuserparams) = $DB->get_in_or_equal($user, SQL_PARAMS_NAMED);
 214                      $condition = "(ev.userid $inusers AND ev.courseid = 0 AND ev.groupid = 0 AND ev.categoryid = 0)";
 215                      $subqueryconditions[] = $condition;
 216                      $subqueryparams = array_merge($subqueryparams, $inuserparams);
 217  
 218                      foreach ($usercourses as $courseid) {
 219                          if (has_capability('moodle/site:accessallgroups', \context_course::instance($courseid), $userrecord)) {
 220                              $usergroupmembership = groups_get_all_groups($courseid, $user, 0, 'g.id');
 221                              if (count($usergroupmembership) == 0) {
 222                                  $viewgroupsonly = true;
 223                                  break;
 224                              }
 225                          }
 226                      }
 227                  }
 228  
 229                  // Set filter condition for the user's group events.
 230                  if ($usergroups === true || $viewgroupsonly) {
 231                      // Fetch group events, but not group overrides.
 232                      $groupconditions = "(ev.groupid != 0 AND ev.eventtype = 'group')";
 233                  } else if (!empty($usergroups)) {
 234                      // Fetch group events and group overrides.
 235                      list($inusergroups, $inusergroupparams) = $DB->get_in_or_equal($usergroups, SQL_PARAMS_NAMED);
 236                      $groupconditions = "(ev.groupid $inusergroups)";
 237                      $subqueryparams = array_merge($subqueryparams, $inusergroupparams);
 238                  }
 239              }
 240          } else if ($users === true) {
 241              // Events from ALL users.
 242              $subqueryconditions[] = "(ev.userid != 0 AND ev.courseid = 0 AND ev.groupid = 0 AND ev.categoryid = 0)";
 243  
 244              if (is_array($groups)) {
 245                  // Events from a number of groups.
 246                  list($insqlgroups, $inparamsgroups) = $DB->get_in_or_equal($groups, SQL_PARAMS_NAMED);
 247                  $subqueryconditions[] = "ev.groupid $insqlgroups";
 248                  $subqueryparams = array_merge($subqueryparams, $inparamsgroups);
 249              } else if ($groups === true) {
 250                  // Events from ALL groups.
 251                  $subqueryconditions[] = "ev.groupid != 0";
 252              }
 253  
 254              if ($courses === true) {
 255                  // ALL course events. It's not needed to worry about users' access as $users = true.
 256                  $subqueryconditions[] = "(ev.groupid = 0 AND ev.courseid != 0 AND ev.categoryid = 0)";
 257              }
 258          }
 259  
 260          // Get courses to be used for the subquery.
 261          $subquerycourses = [];
 262          if (is_array($courses)) {
 263              $subquerycourses = $courses;
 264          }
 265          // Merge with user courses, if necessary.
 266          if (!empty($allusercourses)) {
 267              $subquerycourses = array_merge($subquerycourses, $allusercourses);
 268              // Make sure we remove duplicate values.
 269              $subquerycourses = array_unique($subquerycourses);
 270          }
 271  
 272          // Set subquery filter condition for the courses.
 273          if (!empty($subquerycourses)) {
 274              list($incourses, $incoursesparams) = $DB->get_in_or_equal($subquerycourses, SQL_PARAMS_NAMED);
 275              if (isset($groupconditions)) {
 276                  $groupconditions = $groupconditions." OR ";
 277              } else {
 278                  $groupconditions = '';
 279              }
 280              $condition = "($groupconditions(ev.groupid = 0 AND ev.courseid $incourses AND ev.categoryid = 0))";
 281              $subtimesparams = [];
 282              if (!empty($subquerytimeconditions)) {
 283                  $subtimes = $this->subquerytimeconditions("courses", $subquerytimeconditions, $whereparams);
 284                  $condition .= $subtimes['where'];
 285                  $subtimesparams = $subtimes['params'];
 286              }
 287              $subqueryconditions[] = $condition;
 288              $subqueryparams = array_merge($subqueryparams, $incoursesparams, $subtimesparams);
 289          }
 290  
 291          // Set subquery filter condition for the categories.
 292          if ($categories === true) {
 293              $subqueryconditions[] = "(ev.categoryid != 0 AND ev.eventtype = 'category')";
 294          } else if (!empty($categories)) {
 295              list($incategories, $incategoriesparams) = $DB->get_in_or_equal($categories, SQL_PARAMS_NAMED);
 296              $condition = "(ev.groupid = 0 AND ev.courseid = 0 AND ev.categoryid $incategories)";
 297              $subtimesparams = [];
 298              if (!empty($subquerytimeconditions)) {
 299                  $subtimes = $this->subquerytimeconditions("cats", $subquerytimeconditions, $whereparams);
 300                  $condition .= $subtimes['where'];
 301                  $subtimesparams = $subtimes['params'];
 302              }
 303              $subqueryconditions[] = $condition;
 304              $subqueryparams = array_merge($subqueryparams, $incategoriesparams, $subtimesparams);
 305          }
 306  
 307          // Build the WHERE condition for the sub-query.
 308          if (!empty($subqueryconditions)) {
 309              $unionstartquery = "SELECT modulename, instance, eventtype, priority
 310                                    FROM {event} ev
 311                                   WHERE ";
 312              $subqueryunion = '('.$unionstartquery . implode(" UNION $unionstartquery ", $subqueryconditions).')';
 313          } else {
 314              $subqueryunion = '{event}';
 315          }
 316  
 317          // Merge subquery parameters to the parameters of the main query.
 318          if (!empty($subqueryparams)) {
 319              $params = array_merge($params, $subqueryparams);
 320          }
 321  
 322          // Sub-query that fetches the list of unique events that were filtered based on priority.
 323          $subquery = "SELECT ev.modulename,
 324                              ev.instance,
 325                              ev.eventtype,
 326                              MIN(ev.priority) as priority
 327                         FROM $subqueryunion ev
 328                     GROUP BY ev.modulename, ev.instance, ev.eventtype";
 329  
 330          // Build the main query.
 331          $sql = "SELECT e.*, c.fullname AS coursefullname, c.shortname AS courseshortname
 332                    FROM {event} e
 333              INNER JOIN ($subquery) fe
 334                      ON e.modulename = fe.modulename
 335                         AND e.instance = fe.instance
 336                         AND e.eventtype = fe.eventtype
 337                         AND (e.priority = fe.priority OR (e.priority IS NULL AND fe.priority IS NULL))
 338               LEFT JOIN {modules} m
 339                      ON e.modulename = m.name
 340               LEFT JOIN {course} c
 341                      ON c.id = e.courseid
 342                   WHERE (m.visible = 1 OR m.visible IS NULL) AND $whereclause
 343                ORDER BY " . ($ordersql ? $ordersql : "e.timestart");
 344  
 345          if (!empty($whereparams)) {
 346              $params = array_merge($params, $whereparams);
 347          }
 348  
 349          $events = $DB->get_records_sql($sql, $params, $offset, $limitnum);
 350  
 351          return  $events === false ? [] : $events;
 352      }
 353  
 354      /**
 355       * Returns a query fragment and params, with time constraints applied
 356       *
 357       * @param  string $prefix
 358       * @param  array $conditions
 359       * @param  array $params
 360       * @return array [<where>, <params>]
 361       */
 362      protected function subquerytimeconditions(string $prefix, array $conditions, array $params): array {
 363          $outwhere = '';
 364          $outparams = [];
 365          // Most specific to least specific.
 366          $timeparams = ['timefromid', 'timefrom3', 'timefrom2', 'timefrom1', 'timefrom', 'timetoid', 'timeto2', 'timeto1', 'timeto'];
 367          $whereconditions = [];
 368          foreach ($conditions as $condition) {
 369              $where = $condition;
 370              // This query has been borrowed from the main WHERE clause, so the alias needs to be renamed to match the union.
 371              $where = str_replace('e.id', 'ev.id', $where);
 372              foreach ($timeparams as $timeparam) {
 373                  if (isset($params[$timeparam])) {
 374                      $where = str_replace(":{$timeparam}", ":{$prefix}{$timeparam}", $where);
 375                      $outparams["{$prefix}{$timeparam}"] = $params[$timeparam];
 376                  }
 377              }
 378              $whereconditions[] = $where;
 379          }
 380          if (count($whereconditions) > 0) {
 381              $outwhere = ' AND ' . implode(' AND ', $whereconditions);
 382          }
 383          return ['where' => $outwhere, 'params' => $outparams];
 384      }
 385  }