See Release Notes
Long Term Support Release
Differences Between: [Versions 400 and 401] [Versions 401 and 402] [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 declare(strict_types=1); 18 19 namespace core_reportbuilder\local\filters; 20 21 use DateTimeImmutable; 22 use lang_string; 23 use MoodleQuickForm; 24 use core_reportbuilder\local\helpers\database; 25 26 /** 27 * Date report filter 28 * 29 * This filter accepts a unix timestamp to perform date filtering on 30 * 31 * @package core_reportbuilder 32 * @copyright 2021 Paul Holden <paulh@moodle.com> 33 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 34 */ 35 class date extends base { 36 37 /** @var int Any value */ 38 public const DATE_ANY = 0; 39 40 /** @var int Non-empty (positive) value */ 41 public const DATE_NOT_EMPTY = 1; 42 43 /** @var int Empty (zero) value */ 44 public const DATE_EMPTY = 2; 45 46 /** @var int Date within defined range */ 47 public const DATE_RANGE = 3; 48 49 /** @var int Date in the last [X relative date unit(s)] */ 50 public const DATE_LAST = 4; 51 52 /** @var int Date in the previous [X relative date unit(s)] Kept for backwards compatibility */ 53 public const DATE_PREVIOUS = self::DATE_LAST; 54 55 /** @var int Date in current [relative date unit] */ 56 public const DATE_CURRENT = 5; 57 58 /** @var int Date in the next [X relative date unit(s)] */ 59 public const DATE_NEXT = 6; 60 61 /** @var int Date in the past */ 62 public const DATE_PAST = 7; 63 64 /** @var int Date in the future */ 65 public const DATE_FUTURE = 8; 66 67 /** @var int Relative date unit for a day */ 68 public const DATE_UNIT_DAY = 1; 69 70 /** @var int Relative date unit for a week */ 71 public const DATE_UNIT_WEEK = 2; 72 73 /** @var int Relative date unit for a month */ 74 public const DATE_UNIT_MONTH = 3; 75 76 /** @var int Relative date unit for a month */ 77 public const DATE_UNIT_YEAR = 4; 78 79 /** 80 * Return an array of operators available for this filter 81 * 82 * @return lang_string[] 83 */ 84 private function get_operators(): array { 85 $operators = [ 86 self::DATE_ANY => new lang_string('filterisanyvalue', 'core_reportbuilder'), 87 self::DATE_NOT_EMPTY => new lang_string('filterisnotempty', 'core_reportbuilder'), 88 self::DATE_EMPTY => new lang_string('filterisempty', 'core_reportbuilder'), 89 self::DATE_RANGE => new lang_string('filterrange', 'core_reportbuilder'), 90 self::DATE_LAST => new lang_string('filterdatelast', 'core_reportbuilder'), 91 self::DATE_CURRENT => new lang_string('filterdatecurrent', 'core_reportbuilder'), 92 self::DATE_NEXT => new lang_string('filterdatenext', 'core_reportbuilder'), 93 self::DATE_PAST => new lang_string('filterdatepast', 'core_reportbuilder'), 94 self::DATE_FUTURE => new lang_string('filterdatefuture', 'core_reportbuilder'), 95 ]; 96 97 return $this->filter->restrict_limited_operators($operators); 98 } 99 100 /** 101 * Setup form 102 * 103 * @param MoodleQuickForm $mform 104 */ 105 public function setup_form(MoodleQuickForm $mform): void { 106 // Operator selector. 107 $operatorlabel = get_string('filterfieldoperator', 'core_reportbuilder', $this->get_header()); 108 $typesnounit = [self::DATE_ANY, self::DATE_NOT_EMPTY, self::DATE_EMPTY, self::DATE_RANGE, 109 self::DATE_PAST, self::DATE_FUTURE]; 110 111 $elements[] = $mform->createElement('select', "{$this->name}_operator", $operatorlabel, $this->get_operators()); 112 $mform->setType("{$this->name}_operator", PARAM_INT); 113 $mform->setDefault("{$this->name}_operator", self::DATE_ANY); 114 115 // Value selector for last and next operators. 116 $valuelabel = get_string('filterfieldvalue', 'core_reportbuilder', $this->get_header()); 117 118 $elements[] = $mform->createElement('text', "{$this->name}_value", $valuelabel, ['size' => 3]); 119 $mform->setType("{$this->name}_value", PARAM_INT); 120 $mform->setDefault("{$this->name}_value", 1); 121 $mform->hideIf("{$this->name}_value", "{$this->name}_operator", 'in', array_merge($typesnounit, [self::DATE_CURRENT])); 122 123 // Unit selector for last and next operators. 124 $unitlabel = get_string('filterdurationunit', 'core_reportbuilder', $this->get_header()); 125 $units = [ 126 self::DATE_UNIT_DAY => get_string('filterdatedays', 'core_reportbuilder'), 127 self::DATE_UNIT_WEEK => get_string('filterdateweeks', 'core_reportbuilder'), 128 self::DATE_UNIT_MONTH => get_string('filterdatemonths', 'core_reportbuilder'), 129 self::DATE_UNIT_YEAR => get_string('filterdateyears', 'core_reportbuilder'), 130 ]; 131 132 $elements[] = $mform->createElement('select', "{$this->name}_unit", $unitlabel, $units); 133 $mform->setType("{$this->name}_unit", PARAM_INT); 134 $mform->setDefault("{$this->name}_unit", self::DATE_UNIT_DAY); 135 $mform->hideIf("{$this->name}_unit", "{$this->name}_operator", 'in', $typesnounit); 136 137 // Add operator/value/unit group. 138 $mform->addGroup($elements, "{$this->name}_group", '', '', false); 139 140 // Date selectors for range operator. 141 $mform->addElement('date_selector', "{$this->name}_from", get_string('filterdatefrom', 'core_reportbuilder'), 142 ['optional' => true]); 143 $mform->setType("{$this->name}_from", PARAM_INT); 144 $mform->setDefault("{$this->name}_from", 0); 145 $mform->hideIf("{$this->name}_from", "{$this->name}_operator", 'neq', self::DATE_RANGE); 146 147 $mform->addElement('date_selector', "{$this->name}_to", get_string('filterdateto', 'core_reportbuilder'), 148 ['optional' => true]); 149 $mform->setType("{$this->name}_to", PARAM_INT); 150 $mform->setDefault("{$this->name}_to", 0); 151 $mform->hideIf("{$this->name}_to", "{$this->name}_operator", 'neq', self::DATE_RANGE); 152 } 153 154 /** 155 * Return filter SQL 156 * 157 * @param array $values 158 * @return array 159 */ 160 public function get_sql_filter(array $values): array { 161 $fieldsql = $this->filter->get_field_sql(); 162 $params = $this->filter->get_field_params(); 163 164 $operator = (int) ($values["{$this->name}_operator"] ?? self::DATE_ANY); 165 $dateunitvalue = (int) ($values["{$this->name}_value"] ?? 1); 166 $dateunit = (int) ($values["{$this->name}_unit"] ?? self::DATE_UNIT_DAY); 167 168 switch ($operator) { 169 case self::DATE_NOT_EMPTY: 170 $sql = "COALESCE({$fieldsql}, 0) <> 0"; 171 break; 172 case self::DATE_EMPTY: 173 $sql = "COALESCE({$fieldsql}, 0) = 0"; 174 break; 175 case self::DATE_RANGE: 176 $sql = ''; 177 178 $datefrom = (int)($values["{$this->name}_from"] ?? 0); 179 $dateto = (int)($values["{$this->name}_to"] ?? 0); 180 181 [$paramdatefrom, $paramdateto] = database::generate_param_names(2); 182 183 if ($datefrom > 0 && $dateto > 0) { 184 $sql = "{$fieldsql} BETWEEN :{$paramdatefrom} AND :{$paramdateto}"; 185 $params[$paramdatefrom] = $datefrom; 186 $params[$paramdateto] = $dateto; 187 } else if ($datefrom > 0) { 188 $sql = "{$fieldsql} >= :{$paramdatefrom}"; 189 $params[$paramdatefrom] = $datefrom; 190 } else if ($dateto > 0) { 191 $sql = "{$fieldsql} < :{$paramdateto}"; 192 $params[$paramdateto] = $dateto; 193 } 194 195 break; 196 // Relative helper method can handle these three cases. 197 case self::DATE_LAST: 198 case self::DATE_CURRENT: 199 case self::DATE_NEXT: 200 201 // Last and next operators require a unit value greater than zero. 202 if ($operator !== self::DATE_CURRENT && $dateunitvalue === 0) { 203 return ['', []]; 204 } 205 206 // Generate parameters and SQL clause for the relative date comparison. 207 [$paramdatefrom, $paramdateto] = database::generate_param_names(2); 208 $sql = "{$fieldsql} BETWEEN :{$paramdatefrom} AND :{$paramdateto}"; 209 210 [ 211 $params[$paramdatefrom], 212 $params[$paramdateto], 213 ] = self::get_relative_timeframe($operator, $dateunitvalue, $dateunit); 214 215 break; 216 case self::DATE_PAST: 217 $param = database::generate_param_name(); 218 $sql = "{$fieldsql} < :{$param}"; 219 $params[$param] = time(); 220 break; 221 case self::DATE_FUTURE: 222 $param = database::generate_param_name(); 223 $sql = "{$fieldsql} > :{$param}"; 224 $params[$param] = time(); 225 break; 226 default: 227 // Invalid or inactive filter. 228 return ['', []]; 229 } 230 231 return [$sql, $params]; 232 } 233 234 /** 235 * Return start and end time of given relative date period 236 * 237 * @param int $operator One of the ::DATE_LAST/CURRENT/NEXT constants 238 * @param int $dateunitvalue Unit multiplier of the date unit 239 * @param int $dateunit One of the ::DATE_UNIT_DAY/WEEK/MONTH/YEAR constants 240 * @return int[] Timestamps representing the start/end of timeframe 241 */ 242 private static function get_relative_timeframe(int $operator, int $dateunitvalue, int $dateunit): array { 243 // Initialise start/end time to now. 244 $datestart = $dateend = new DateTimeImmutable(); 245 246 switch ($dateunit) { 247 case self::DATE_UNIT_DAY: 248 if ($operator === self::DATE_CURRENT) { 249 $datestart = $datestart->setTime(0, 0); 250 $dateend = $dateend->setTime(23, 59, 59); 251 } else if ($operator === self::DATE_LAST) { 252 $datestart = $datestart->modify("-{$dateunitvalue} day"); 253 } else if ($operator === self::DATE_NEXT) { 254 $dateend = $dateend->modify("+{$dateunitvalue} day"); 255 } 256 257 break; 258 case self::DATE_UNIT_WEEK: 259 if ($operator === self::DATE_CURRENT) { 260 // The first day of the week is determined by site calendar configuration/preferences. 261 $startweekday = \core_calendar\type_factory::get_calendar_instance()->get_starting_weekday(); 262 $weekdays = ['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat']; 263 264 // If calculated start of week is after today (today is Tues/start of week is Weds), move back a week. 265 $datestartnow = $datestart->getTimestamp(); 266 $datestart = $datestart->modify($weekdays[$startweekday] . ' this week')->setTime(0, 0); 267 if ($datestart->getTimestamp() > $datestartnow) { 268 $datestart = $datestart->modify('-1 week'); 269 } 270 271 $dateend = $datestart->modify('+6 day')->setTime(23, 59, 59); 272 } else if ($operator === self::DATE_LAST) { 273 $datestart = $datestart->modify("-{$dateunitvalue} week"); 274 } else if ($operator === self::DATE_NEXT) { 275 $dateend = $dateend->modify("+{$dateunitvalue} week"); 276 } 277 278 break; 279 case self::DATE_UNIT_MONTH: 280 if ($operator === self::DATE_CURRENT) { 281 $datestart = $datestart->modify('first day of this month')->setTime(0, 0); 282 $dateend = $dateend->modify('last day of this month')->setTime(23, 59, 59); 283 } else if ($operator === self::DATE_LAST) { 284 $datestart = $datestart->modify("-{$dateunitvalue} month"); 285 } else if ($operator === self::DATE_NEXT) { 286 $dateend = $dateend->modify("+{$dateunitvalue} month"); 287 } 288 289 break; 290 case self::DATE_UNIT_YEAR: 291 if ($operator === self::DATE_CURRENT) { 292 $datestart = $datestart->modify('first day of january this year')->setTime(0, 0); 293 $dateend = $dateend->modify('last day of december this year')->setTime(23, 59, 59); 294 } else if ($operator === self::DATE_LAST) { 295 $datestart = $datestart->modify("-{$dateunitvalue} year"); 296 } else if ($operator === self::DATE_NEXT) { 297 $dateend = $dateend->modify("+{$dateunitvalue} year"); 298 } 299 300 break; 301 } 302 303 return [ 304 $datestart->getTimestamp(), 305 $dateend->getTimestamp(), 306 ]; 307 } 308 309 /** 310 * Return sample filter values 311 * 312 * @return array 313 */ 314 public function get_sample_values(): array { 315 return [ 316 "{$this->name}_operator" => self::DATE_CURRENT, 317 "{$this->name}_unit" => self::DATE_UNIT_WEEK, 318 ]; 319 } 320 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body