[Summary view]

Differences Between: [Versions 401 and 403] [Versions 402 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_course\reportbuilder\local\entities;
  20  
  21  use core_reportbuilder\local\entities\base;
  22  use core_course\reportbuilder\local\formatters\completion as completion_formatter;
  23  use core_reportbuilder\local\filters\boolean_select;
  24  use core_reportbuilder\local\filters\date;
  25  use core_reportbuilder\local\helpers\database;
  26  use core_reportbuilder\local\helpers\format;
  27  use core_reportbuilder\local\report\column;
  28  use core_reportbuilder\local\report\filter;
  29  use completion_criteria_completion;
  30  use completion_info;
  31  use html_writer;
  32  use lang_string;
  33  use stdClass;
  34  
  35  /**
  36   * Course completion entity implementation
  37   *
  38   * @package     core_course
  39   * @copyright   2022 David Matamoros <davidmc@moodle.com>
  40   * @license     http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  41   */
  42  class completion extends base {
  43  
  44      /**
  45       * Database tables that this entity uses and their default aliases
  46       *
  47       * @return array
  48       */
  49      protected function get_default_table_aliases(): array {
  50          return [
  51              'course_completion' => 'ccomp',
  52              'course' => 'c',
  53              'grade_grades' => 'gg',
  54              'grade_items' => 'gi',
  55              'user' => 'u',
  56          ];
  57      }
  58  
  59      /**
  60       * The default title for this entity in the list of columns/conditions/filters in the report builder
  61       *
  62       * @return lang_string
  63       */
  64      protected function get_default_entity_title(): lang_string {
  65          return new lang_string('coursecompletion', 'completion');
  66      }
  67  
  68      /**
  69       * Initialise the entity
  70       *
  71       * @return base
  72       */
  73      public function initialise(): base {
  74          foreach ($this->get_all_columns() as $column) {
  75              $this->add_column($column);
  76          }
  77  
  78          // All the filters defined by the entity can also be used as conditions.
  79          foreach ($this->get_all_filters() as $filter) {
  80              $this
  81                  ->add_filter($filter)
  82                  ->add_condition($filter);
  83          }
  84  
  85          return $this;
  86      }
  87  
  88      /**
  89       * Returns list of all available columns
  90       *
  91       * @return column[]
  92       */
  93      protected function get_all_columns(): array {
  94          $coursecompletion = $this->get_table_alias('course_completion');
  95          $course = $this->get_table_alias('course');
  96          $grade = $this->get_table_alias('grade_grades');
  97          $gradeitem = $this->get_table_alias('grade_items');
  98          $user = $this->get_table_alias('user');
  99  
 100          // Completed column.
 101          $columns[] = (new column(
 102              'completed',
 103              new lang_string('completed', 'completion'),
 104              $this->get_entity_name()
 105          ))
 106              ->add_joins($this->get_joins())
 107              ->set_type(column::TYPE_BOOLEAN)
 108              ->add_field("CASE WHEN {$coursecompletion}.timecompleted > 0 THEN 1 ELSE 0 END", 'completed')
 109              ->add_field("{$user}.id", 'userid')
 110              ->set_is_sortable(true)
 111              ->add_callback(static function(bool $value, stdClass $row): string {
 112                  if (!$row->userid) {
 113                      return '';
 114                  }
 115                  return format::boolean_as_text($value);
 116              });
 117  
 118          // Completion criteria column.
 119          $criterias = database::generate_alias();
 120          $columns[] = (new column(
 121              'criteria',
 122              new lang_string('criteria', 'core_completion'),
 123              $this->get_entity_name()
 124          ))
 125              ->add_joins($this->get_joins())
 126              // Determine whether any criteria exist for the course. We also group per course, rather than report each separately.
 127              ->add_join("LEFT JOIN (
 128                              SELECT DISTINCT course FROM {course_completion_criteria}
 129                         ) {$criterias} ON {$criterias}.course = {$course}.id")
 130              ->set_type(column::TYPE_TEXT)
 131              // Select enough fields to determine user criteria for the course.
 132              ->add_field("{$criterias}.course", 'courseid')
 133              ->add_field("{$course}.enablecompletion")
 134              ->add_field("{$user}.id", 'userid')
 135              ->set_disabled_aggregation_all()
 136              ->add_callback(static function($id, stdClass $record): string {
 137                  if (!$record->courseid) {
 138                      return '';
 139                  }
 140  
 141                  $info = new completion_info((object) ['id' => $record->courseid, 'enablecompletion' => $record->enablecompletion]);
 142                  if ($info->get_aggregation_method() == COMPLETION_AGGREGATION_ALL) {
 143                      $title = get_string('criteriarequiredall', 'core_completion');
 144                  } else {
 145                      $title = get_string('criteriarequiredany', 'core_completion');
 146                  }
 147  
 148                  // Map all completion data to their criteria summaries.
 149                  $items = array_map(static function(completion_criteria_completion $completion): string {
 150                      $criteria = $completion->get_criteria();
 151  
 152                      return get_string('criteriasummary', 'core_completion', [
 153                          'type' => $criteria->get_details($completion)['type'],
 154                          'summary' => $criteria->get_title_detailed(),
 155                      ]);
 156                  }, $info->get_completions((int) $record->userid));
 157  
 158                  return $title . html_writer::alist($items);
 159              });
 160  
 161          // Progress percentage column.
 162          $columns[] = (new column(
 163              'progresspercent',
 164              new lang_string('progress', 'completion'),
 165              $this->get_entity_name()
 166          ))
 167              ->add_joins($this->get_joins())
 168              ->set_type(column::TYPE_TEXT)
 169              ->add_field("{$course}.id", 'courseid')
 170              ->add_field("{$user}.id", 'userid')
 171              ->set_is_sortable(false)
 172              ->add_callback([completion_formatter::class, 'completion_progress']);
 173  
 174          // Time enrolled.
 175          $columns[] = (new column(
 176              'timeenrolled',
 177              new lang_string('timeenrolled', 'enrol'),
 178              $this->get_entity_name()
 179          ))
 180              ->add_joins($this->get_joins())
 181              ->set_type(column::TYPE_TIMESTAMP)
 182              ->add_field("{$coursecompletion}.timeenrolled")
 183              ->set_is_sortable(true)
 184              ->add_callback([format::class, 'userdate']);
 185  
 186          // Time started.
 187          $columns[] = (new column(
 188              'timestarted',
 189              new lang_string('timestarted', 'enrol'),
 190              $this->get_entity_name()
 191          ))
 192              ->add_joins($this->get_joins())
 193              ->set_type(column::TYPE_TIMESTAMP)
 194              ->add_field("{$coursecompletion}.timestarted")
 195              ->set_is_sortable(true)
 196              ->add_callback([format::class, 'userdate']);
 197  
 198          // Time completed.
 199          $columns[] = (new column(
 200              'timecompleted',
 201              new lang_string('timecompleted', 'completion'),
 202              $this->get_entity_name()
 203          ))
 204              ->add_joins($this->get_joins())
 205              ->set_type(column::TYPE_TIMESTAMP)
 206              ->add_field("{$coursecompletion}.timecompleted")
 207              ->set_is_sortable(true)
 208              ->add_callback([format::class, 'userdate']);
 209  
 210          // Time reaggregated.
 211          $columns[] = (new column(
 212              'reaggregate',
 213              new lang_string('timereaggregated', 'enrol'),
 214              $this->get_entity_name()
 215          ))
 216              ->add_joins($this->get_joins())
 217              ->set_type(column::TYPE_TIMESTAMP)
 218              ->add_field("{$coursecompletion}.reaggregate")
 219              ->set_is_sortable(true)
 220              ->add_callback([format::class, 'userdate']);
 221  
 222          // Days taking course (days since course start date until completion or until current date if not completed).
 223          $currenttime = time();
 224          $columns[] = (new column(
 225              'dayscourse',
 226              new lang_string('daystakingcourse', 'course'),
 227              $this->get_entity_name()
 228          ))
 229              ->add_joins($this->get_joins())
 230              ->set_type(column::TYPE_INTEGER)
 231              ->add_field("(
 232                  CASE
 233                      WHEN {$coursecompletion}.timecompleted > 0 THEN
 234                          {$coursecompletion}.timecompleted
 235                      ELSE
 236                          {$currenttime}
 237                  END - {$course}.startdate) / " . DAYSECS, 'dayscourse')
 238              ->add_field("{$user}.id", 'userid')
 239              ->set_is_sortable(true)
 240              ->add_callback([completion_formatter::class, 'get_days']);
 241  
 242          // Days since last completion (days since last enrolment date until completion or until current date if not completed).
 243          $columns[] = (new column(
 244              'daysuntilcompletion',
 245              new lang_string('daysuntilcompletion', 'completion'),
 246              $this->get_entity_name()
 247          ))
 248              ->add_joins($this->get_joins())
 249              ->set_type(column::TYPE_INTEGER)
 250              ->add_field("(
 251                  CASE
 252                      WHEN {$coursecompletion}.timecompleted > 0 THEN
 253                          {$coursecompletion}.timecompleted
 254                      ELSE
 255                          {$currenttime}
 256                  END - {$coursecompletion}.timeenrolled) / " . DAYSECS, 'daysuntilcompletion')
 257              ->add_field("{$user}.id", 'userid')
 258              ->set_is_sortable(true)
 259              ->add_callback([completion_formatter::class, 'get_days']);
 260  
 261          // Student course grade.
 262          $columns[] = (new column(
 263              'grade',
 264              new lang_string('gradenoun'),
 265              $this->get_entity_name()
 266          ))
 267              ->add_joins($this->get_joins())
 268              ->add_join("
 269                  LEFT JOIN {grade_items} {$gradeitem}
 270                         ON ({$gradeitem}.itemtype = 'course' AND {$course}.id = {$gradeitem}.courseid)
 271              ")
 272              ->add_join("
 273                  LEFT JOIN {grade_grades} {$grade}
 274                         ON ({$user}.id = {$grade}.userid AND {$gradeitem}.id = {$grade}.itemid)
 275              ")
 276              ->set_type(column::TYPE_FLOAT)
 277              ->add_fields("{$grade}.finalgrade")
 278              ->set_is_sortable(true)
 279              ->add_callback(function(?float $value): string {
 280                  if ($value === null) {
 281                      return '';
 282                  }
 283                  return format_float($value, 2);
 284              });
 285  
 286          return $columns;
 287      }
 288  
 289      /**
 290       * Return list of all available filters
 291       *
 292       * @return filter[]
 293       */
 294      protected function get_all_filters(): array {
 295          $coursecompletion = $this->get_table_alias('course_completion');
 296  
 297          // Completed status filter.
 298          $filters[] = (new filter(
 299              boolean_select::class,
 300              'completed',
 301              new lang_string('completed', 'completion'),
 302              $this->get_entity_name(),
 303              "CASE WHEN {$coursecompletion}.timecompleted > 0 THEN 1 ELSE 0 END"
 304          ))
 305              ->add_joins($this->get_joins());
 306  
 307          // Time completed filter.
 308          $filters[] = (new filter(
 309              date::class,
 310              'timecompleted',
 311              new lang_string('timecompleted', 'completion'),
 312              $this->get_entity_name(),
 313              "{$coursecompletion}.timecompleted"
 314          ))
 315              ->add_joins($this->get_joins())
 316              ->set_limited_operators([
 317                  date::DATE_ANY,
 318                  date::DATE_NOT_EMPTY,
 319                  date::DATE_EMPTY,
 320                  date::DATE_RANGE,
 321                  date::DATE_LAST,
 322                  date::DATE_CURRENT,
 323              ]);
 324  
 325          // Time enrolled/started filter and condition.
 326          $fields = ['timeenrolled', 'timestarted'];
 327          foreach ($fields as $field) {
 328              $filters[] = (new filter(
 329                  date::class,
 330                  $field,
 331                  new lang_string($field, 'enrol'),
 332                  $this->get_entity_name(),
 333                  "{$coursecompletion}.{$field}"
 334              ))
 335                  ->add_joins($this->get_joins())
 336                  ->set_limited_operators([
 337                      date::DATE_ANY,
 338                      date::DATE_NOT_EMPTY,
 339                      date::DATE_EMPTY,
 340                      date::DATE_RANGE,
 341                      date::DATE_LAST,
 342                      date::DATE_CURRENT,
 343                  ]);
 344          }
 345  
 346          // Time reaggregated filter and condition.
 347          $filters[] = (new filter(
 348              date::class,
 349              'reaggregate',
 350              new lang_string('timereaggregated', 'enrol'),
 351              $this->get_entity_name(),
 352              "{$coursecompletion}.reaggregate"
 353          ))
 354              ->add_joins($this->get_joins())
 355              ->set_limited_operators([
 356                  date::DATE_ANY,
 357                  date::DATE_NOT_EMPTY,
 358                  date::DATE_EMPTY,
 359                  date::DATE_RANGE,
 360                  date::DATE_LAST,
 361                  date::DATE_CURRENT,
 362              ]);
 363  
 364          return $filters;
 365      }
 366  }