reportbuilder/classes/local/filters/date.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 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 = "{$fieldsql} IS NOT NULL AND {$fieldsql} <> 0";
 171                 break;
 172             case self::DATE_EMPTY:
 173                 $sql = "{$fieldsql} IS NULL OR {$fieldsql} = 0";
 174                 break;
 175             case self::DATE_RANGE:
 176                 $clauses = [];
 177
 178                 $datefrom = (int)($values["{$this->name}_from"] ?? 0);
 179                 if ($datefrom > 0) {
 180                     $paramdatefrom = database::generate_param_name();
 181                     $clauses[] = "{$fieldsql} >= :{$paramdatefrom}";
 182                     $params[$paramdatefrom] = $datefrom;
 183                 }
 184
 185                 $dateto = (int)($values["{$this->name}_to"] ?? 0);
 186                 if ($dateto > 0) {
 187                     $paramdateto = database::generate_param_name();
 188                     $clauses[] = "{$fieldsql} < :{$paramdateto}";
 189                     $params[$paramdateto] = $dateto;
 190                 }
 191
 192                 $sql = implode(' AND ', $clauses);
 193
 194                 break;
 195             // Relative helper method can handle these three cases.
 196             case self::DATE_LAST:
 197             case self::DATE_CURRENT:
 198             case self::DATE_NEXT:
 199
 200                 // Last and next operators require a unit value greater than zero.
 201                 if ($operator !== self::DATE_CURRENT && $dateunitvalue === 0) {
 202                     return ['', []];
 203                 }
 204
 205                 // Generate parameters and SQL clause for the relative date comparison.
 206                 [$paramdatefrom, $paramdateto] = database::generate_param_names(2);
 207                 $sql = "{$fieldsql} >= :{$paramdatefrom} AND {$fieldsql} <= :{$paramdateto}";
 208
 209                 [
 210                     $params[$paramdatefrom],
 211                     $params[$paramdateto],
 212                 ] = self::get_relative_timeframe($operator, $dateunitvalue, $dateunit);
 213
 214                 break;
 215             case self::DATE_PAST:
 216                 $param = database::generate_param_name();
 217                 $sql = "{$fieldsql} < :{$param}";
 218                 $params[$param] = time();
 219                 break;
 220             case self::DATE_FUTURE:
 221                 $param = database::generate_param_name();
 222                 $sql = "{$fieldsql} > :{$param}";
 223                 $params[$param] = time();
 224                 break;
 225             default:
 226                 // Invalid or inactive filter.
 227                 return ['', []];
 228         }
 229
 230         return [$sql, $params];
 231     }
 232
 233     /**
 234      * Return start and end time of given relative date period
 235      *
 236      * @param int $operator One of the ::DATE_LAST/CURRENT/NEXT constants
 237      * @param int $dateunitvalue Unit multiplier of the date unit
 238      * @param int $dateunit One of the ::DATE_UNIT_DAY/WEEK/MONTH/YEAR constants
 239      * @return int[] Timestamps representing the start/end of timeframe
 240      */
 241     private static function get_relative_timeframe(int $operator, int $dateunitvalue, int $dateunit): array {
 242         // Initialise start/end time to now.
 243         $datestart = $dateend = new DateTimeImmutable();
 244
 245         switch ($dateunit) {
 246             case self::DATE_UNIT_DAY:
 247                 if ($operator === self::DATE_CURRENT) {
 248                     $datestart = $datestart->setTime(0, 0);
 249                     $dateend = $dateend->setTime(23, 59, 59);
 250                 } else if ($operator === self::DATE_LAST) {
 251                     $datestart = $datestart->modify("-{$dateunitvalue} day");
 252                 } else if ($operator === self::DATE_NEXT) {
 253                     $dateend = $dateend->modify("+{$dateunitvalue} day");
 254                 }
 255
 256                 break;
 257             case self::DATE_UNIT_WEEK:
 258                 if ($operator === self::DATE_CURRENT) {
 259                     $datestart = $datestart->modify('monday this week')->setTime(0, 0);
 260                     $dateend = $dateend->modify('sunday this week')->setTime(23, 59, 59);
 261                 } else if ($operator === self::DATE_LAST) {
 262                     $datestart = $datestart->modify("-{$dateunitvalue} week");
 263                 } else if ($operator === self::DATE_NEXT) {
 264                     $dateend = $dateend->modify("+{$dateunitvalue} week");
 265                 }
 266
 267                 break;
 268             case self::DATE_UNIT_MONTH:
 269                 if ($operator === self::DATE_CURRENT) {
 270                     $datestart = $datestart->modify('first day of this month')->setTime(0, 0);
 271                     $dateend = $dateend->modify('last day of this month')->setTime(23, 59, 59);
 272                 } else if ($operator === self::DATE_LAST) {
 273                     $datestart = $datestart->modify("-{$dateunitvalue} month");
 274                 } else if ($operator === self::DATE_NEXT) {
 275                     $dateend = $dateend->modify("+{$dateunitvalue} month");
 276                 }
 277
 278                 break;
 279             case self::DATE_UNIT_YEAR:
 280                 if ($operator === self::DATE_CURRENT) {
 281                     $datestart = $datestart->modify('first day of january this year')->setTime(0, 0);
 282                     $dateend = $dateend->modify('last day of december this year')->setTime(23, 59, 59);
 283                 } else if ($operator === self::DATE_LAST) {
 284                     $datestart = $datestart->modify("-{$dateunitvalue} year");
 285                 } else if ($operator === self::DATE_NEXT) {
 286                     $dateend = $dateend->modify("+{$dateunitvalue} year");
 287                 }
 288
 289                 break;
 290         }
 291
 292         return [
 293             $datestart->getTimestamp(),
 294             $dateend->getTimestamp(),
 295         ];
 296     }
 297
 298     /**
 299      * Return sample filter values
 300      *
 301      * @return array
 302      */
 303     public function get_sample_values(): array {
 304         return [
 305             "{$this->name}_operator" => self::DATE_CURRENT,
 306             "{$this->name}_unit" => self::DATE_UNIT_WEEK,
 307         ];
 308     }
 309 }