mod/feedback/classes/responses_table.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 /**
  18  * Contains class mod_feedback_responses_table
  19  *
  20  * @package   mod_feedback
  21  * @copyright 2016 Marina Glancy
  22  * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  23  */
  24
  25 defined('MOODLE_INTERNAL') || die();
  26
  27 global $CFG;
  28 require_once($CFG->libdir . '/tablelib.php');
  29
  30 /**
  31  * Class mod_feedback_responses_table
  32  *
  33  * @package   mod_feedback
  34  * @copyright 2016 Marina Glancy
  35  * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  36  */
  37 class mod_feedback_responses_table extends table_sql {
  38
  39     /**
  40      * Maximum number of feedback questions to display in the "Show responses" table
  41      */
  42     const PREVIEWCOLUMNSLIMIT = 10;
  43
  44     /**
  45      * Maximum number of feedback questions answers to retrieve in one SQL query.
  46      * Mysql has a limit of 60, we leave 1 for joining with users table.
  47      */
  48     const TABLEJOINLIMIT = 59;
  49
  50     /**
  51      * When additional queries are needed to retrieve more than TABLEJOINLIMIT questions answers, do it in chunks every x rows.
  52      * Value too small will mean too many DB queries, value too big may cause memory overflow.
  53      */
  54     const ROWCHUNKSIZE = 100;
  55
  56     /** @var mod_feedback_structure */
  57     protected $feedbackstructure;
  58
  59     /** @var int */
  60     protected $grandtotal = null;
  61
  62     /** @var bool */
  63     protected $showall = false;
  64
  65     /** @var string */
  66     protected $showallparamname = 'showall';
  67
  68     /** @var string */
  69     protected $downloadparamname = 'download';
  70
  71     /** @var int number of columns that were not retrieved in the main SQL query
  72      * (no more than TABLEJOINLIMIT tables with values can be joined). */
  73     protected $hasmorecolumns = 0;
  74
  75     /** @var bool whether we are building this table for a external function */
  76     protected $buildforexternal = false;
  77
  78     /** @var array the data structure containing the table data for the external function */
  79     protected $dataforexternal = [];
  80
  81     /**
  82      * Constructor
  83      *
  84      * @param mod_feedback_structure $feedbackstructure
  85      * @param int $group retrieve only users from this group (optional)
  86      */
  87     public function __construct(mod_feedback_structure $feedbackstructure, $group = 0) {
  88         $this->feedbackstructure = $feedbackstructure;
  89
  90         parent::__construct('feedback-showentry-list-' . $feedbackstructure->get_cm()->instance);
  91
  92         $this->showall = optional_param($this->showallparamname, 0, PARAM_BOOL);
  93         $this->define_baseurl(new moodle_url('/mod/feedback/show_entries.php',
  94             ['id' => $this->feedbackstructure->get_cm()->id]));
  95         if ($courseid = $this->feedbackstructure->get_courseid()) {
  96             $this->baseurl->param('courseid', $courseid);
  97         }
  98         if ($this->showall) {
  99             $this->baseurl->param($this->showallparamname, $this->showall);
 100         }
 101
 102         $name = format_string($feedbackstructure->get_feedback()->name);
 103         $this->is_downloadable(true);
 104         $this->is_downloading(optional_param($this->downloadparamname, 0, PARAM_ALPHA),
 105                 $name, get_string('responses', 'feedback'));
 106         $this->useridfield = 'userid';
 107         $this->init($group);
 108     }
 109
 110     /**
 111      * Initialises table
 112      * @param int $group retrieve only users from this group (optional)
 113      */
 114     protected function init($group = 0) {
 115
 116         $tablecolumns = array('userpic', 'fullname');
 117         $tableheaders = array(get_string('userpic'), get_string('fullnameuser'));
 118
 119         $extrafields = get_extra_user_fields($this->get_context());
 120         $ufields = user_picture::fields('u', $extrafields, $this->useridfield);
 121         $fields = 'c.id, c.timemodified as completed_timemodified, c.courseid, '.$ufields;
 122         $from = '{feedback_completed} c '
 123                 . 'JOIN {user} u ON u.id = c.userid AND u.deleted = :notdeleted';
 124         $where = 'c.anonymous_response = :anon
 125                 AND c.feedback = :instance';
 126         if ($this->feedbackstructure->get_courseid()) {
 127             $where .= ' AND c.courseid = :courseid';
 128         }
 129
 130         if ($this->is_downloading()) {
 131             // When downloading data:
 132             // Remove 'userpic' from downloaded data.
 133             array_shift($tablecolumns);
 134             array_shift($tableheaders);
 135
 136             // Add all identity fields as separate columns.
 137             foreach ($extrafields as $field) {
 138                 $fields .= ", u.{$field}";
 139                 $tablecolumns[] = $field;
 140                 $tableheaders[] = get_user_field_name($field);
 141             }
 142         }
 143
 144         if ($this->feedbackstructure->get_feedback()->course == SITEID && !$this->feedbackstructure->get_courseid()) {
 145             $tablecolumns[] = 'courseid';
 146             $tableheaders[] = get_string('course');
 147         }
 148
 149         $tablecolumns[] = 'completed_timemodified';
 150         $tableheaders[] = get_string('date');
 151
 152         $this->define_columns($tablecolumns);
 153         $this->define_headers($tableheaders);
 154
 155         $this->sortable(true, 'lastname', SORT_ASC);
 156         $this->collapsible(true);
 157         $this->set_attribute('id', 'showentrytable');
 158
 159         $params = array();
 160         $params['anon'] = FEEDBACK_ANONYMOUS_NO;
 161         $params['instance'] = $this->feedbackstructure->get_feedback()->id;
 162         $params['notdeleted'] = 0;
 163         $params['courseid'] = $this->feedbackstructure->get_courseid();
 164
 165         $group = (empty($group)) ? groups_get_activity_group($this->feedbackstructure->get_cm(), true) : $group;
 166         if ($group) {
 167             $where .= ' AND c.userid IN (SELECT g.userid FROM {groups_members} g WHERE g.groupid = :group)';
 168             $params['group'] = $group;
 169         }
 170
 171         $this->set_sql($fields, $from, $where, $params);
 172         $this->set_count_sql("SELECT COUNT(c.id) FROM $from WHERE $where", $params);
 173     }
 174
 175     /**
 176      * Current context
 177      * @return context_module
 178      */
 179     protected function get_context() {
 180         return context_module::instance($this->feedbackstructure->get_cm()->id);
 181     }
 182
 183     /**
 184      * Allows to set the display column value for all columns without "col_xxxxx" method.
 185      * @param string $column column name
 186      * @param stdClass $row current record result of SQL query
 187      */
 188     public function other_cols($column, $row) {
 189         if (preg_match('/^val(\d+)$/', $column, $matches)) {
 190             $items = $this->feedbackstructure->get_items();
 191             $itemobj = feedback_get_item_class($items[$matches[1]]->typ);
 192             return trim($itemobj->get_printval($items[$matches[1]], (object) ['value' => $row->$column] ));
 193         }
 194         return $row->$column;
 195     }
 196
 197     /**
 198      * Prepares column userpic for display
 199      * @param stdClass $row
 200      * @return string
 201      */
 202     public function col_userpic($row) {
 203         global $OUTPUT;
 204         $user = user_picture::unalias($row, [], $this->useridfield);
 205         return $OUTPUT->user_picture($user, array('courseid' => $this->feedbackstructure->get_cm()->course));
 206     }
 207
 208     /**
 209      * Prepares column deleteentry for display
 210      * @param stdClass $row
 211      * @return string
 212      */
 213     public function col_deleteentry($row) {
 214         global $OUTPUT;
 215         $deleteentryurl = new moodle_url($this->baseurl, ['delete' => $row->id, 'sesskey' => sesskey()]);
 216         $deleteaction = new confirm_action(get_string('confirmdeleteentry', 'feedback'));
 217         return $OUTPUT->action_icon($deleteentryurl,
 218             new pix_icon('t/delete', get_string('delete_entry', 'feedback')), $deleteaction);
 219     }
 220
 221     /**
 222      * Returns a link for viewing a single response
 223      * @param stdClass $row
 224      * @return \moodle_url
 225      */
 226     protected function get_link_single_entry($row) {
 227         return new moodle_url($this->baseurl, ['userid' => $row->{$this->useridfield}, 'showcompleted' => $row->id]);
 228     }
 229
 230     /**
 231      * Prepares column completed_timemodified for display
 232      * @param stdClass $student
 233      * @return string
 234      */
 235     public function col_completed_timemodified($student) {
 236         if ($this->is_downloading()) {
 237             return userdate($student->completed_timemodified);
 238         } else {
 239             return html_writer::link($this->get_link_single_entry($student),
 240                     userdate($student->completed_timemodified));
 241         }
 242     }
 243
 244     /**
 245      * Prepares column courseid for display
 246      * @param array $row
 247      * @return string
 248      */
 249     public function col_courseid($row) {
 250         $courses = $this->feedbackstructure->get_completed_courses();
 251         $name = '';
 252         if (isset($courses[$row->courseid])) {
 253             $name = $courses[$row->courseid];
 254             if (!$this->is_downloading()) {
 255                 $name = html_writer::link(course_get_url($row->courseid), $name);
 256             }
 257         }
 258         return $name;
 259     }
 260
 261     /**
 262      * Adds common values to the table that do not change the number or order of entries and
 263      * are only needed when outputting or downloading data.
 264      */
 265     protected function add_all_values_to_output() {
 266         $tablecolumns = array_keys($this->columns);
 267         $tableheaders = $this->headers;
 268
 269         $items = $this->feedbackstructure->get_items(true);
 270         if (!$this->is_downloading() && !$this->buildforexternal) {
 271             // In preview mode do not show all columns or the page becomes unreadable.
 272             // The information message will be displayed to the teacher that the rest of the data can be viewed when downloading.
 273             $items = array_slice($items, 0, self::PREVIEWCOLUMNSLIMIT, true);
 274         }
 275
 276         $columnscount = 0;
 277         $this->hasmorecolumns = max(0, count($items) - self::TABLEJOINLIMIT);
 278
 279         // Add feedback response values.
 280         foreach ($items as $nr => $item) {
 281             if ($columnscount++ < self::TABLEJOINLIMIT) {
 282                 // Mysql has a limit on the number of tables in the join, so we only add limited number of columns here,
 283                 // the rest will be added in {@link self::build_table()} and {@link self::build_table_chunk()} functions.
 284                 $this->sql->fields .= ", v{$nr}.value AS val{$nr}";
 285                 $this->sql->from .= " LEFT OUTER JOIN {feedback_value} v{$nr} " .
 286                     "ON v{$nr}.completed = c.id AND v{$nr}.item = :itemid{$nr}";
 287                 $this->sql->params["itemid{$nr}"] = $item->id;
 288             }
 289
 290             $tablecolumns[] = "val{$nr}";
 291             $itemobj = feedback_get_item_class($item->typ);
 292             $tableheaders[] = $itemobj->get_display_name($item);
 293         }
 294
 295         // Add 'Delete entry' column.
 296         if (!$this->is_downloading() && has_capability('mod/feedback:deletesubmissions', $this->get_context())) {
 297             $tablecolumns[] = 'deleteentry';
 298             $tableheaders[] = '';
 299         }
 300
 301         $this->define_columns($tablecolumns);
 302         $this->define_headers($tableheaders);
 303     }
 304
 305     /**
 306      * Query the db. Store results in the table object for use by build_table.
 307      *
 308      * @param int $pagesize size of page for paginated displayed table.
 309      * @param bool $useinitialsbar do you want to use the initials bar. Bar
 310      * will only be used if there is a fullname column defined for the table.
 311      */
 312     public function query_db($pagesize, $useinitialsbar=true) {
 313         global $DB;
 314         $this->totalrows = $grandtotal = $this->get_total_responses_count();
 315         if (!$this->is_downloading()) {
 316             $this->initialbars($useinitialsbar);
 317
 318             list($wsql, $wparams) = $this->get_sql_where();
 319             if ($wsql) {
 320                 $this->countsql .= ' AND '.$wsql;
 321                 $this->countparams = array_merge($this->countparams, $wparams);
 322
 323                 $this->sql->where .= ' AND '.$wsql;
 324                 $this->sql->params = array_merge($this->sql->params, $wparams);
 325
 326                 $this->totalrows  = $DB->count_records_sql($this->countsql, $this->countparams);
 327             }
 328
 329             if ($this->totalrows > $pagesize) {
 330                 $this->pagesize($pagesize, $this->totalrows);
 331             }
 332         }
 333
 334         if ($sort = $this->get_sql_sort()) {
 335             $sort = "ORDER BY $sort";
 336         }
 337         $sql = "SELECT
 338                 {$this->sql->fields}
 339                 FROM {$this->sql->from}
 340                 WHERE {$this->sql->where}
 341                 {$sort}";
 342
 343         if (!$this->is_downloading()) {
 344             $this->rawdata = $DB->get_recordset_sql($sql, $this->sql->params, $this->get_page_start(), $this->get_page_size());
 345         } else {
 346             $this->rawdata = $DB->get_recordset_sql($sql, $this->sql->params);
 347         }
 348     }
 349
 350     /**
 351      * Returns total number of reponses (without any filters applied)
 352      * @return int
 353      */
 354     public function get_total_responses_count() {
 355         global $DB;
 356         if ($this->grandtotal === null) {
 357             $this->grandtotal = $DB->count_records_sql($this->countsql, $this->countparams);
 358         }
 359         return $this->grandtotal;
 360     }
 361
 362     /**
 363      * Defines columns
 364      * @param array $columns an array of identifying names for columns. If
 365      * columns are sorted then column names must correspond to a field in sql.
 366      */
 367     public function define_columns($columns) {
 368         parent::define_columns($columns);
 369         foreach ($this->columns as $column => $column) {
 370             // Automatically assign classes to columns.
 371             $this->column_class[$column] = ' ' . $column;
 372         }
 373     }
 374
 375     /**
 376      * Convenience method to call a number of methods for you to display the
 377      * table.
 378      * @param int $pagesize
 379      * @param bool $useinitialsbar
 380      * @param string $downloadhelpbutton
 381      */
 382     public function out($pagesize, $useinitialsbar, $downloadhelpbutton='') {
 383         $this->add_all_values_to_output();
 384         parent::out($pagesize, $useinitialsbar, $downloadhelpbutton);
 385     }
 386
 387     /**
 388      * Displays the table
 389      */
 390     public function display() {
 391         global $OUTPUT;
 392         groups_print_activity_menu($this->feedbackstructure->get_cm(), $this->baseurl->out());
 393         $grandtotal = $this->get_total_responses_count();
 394         if (!$grandtotal) {
 395             echo $OUTPUT->box(get_string('nothingtodisplay'), 'generalbox nothingtodisplay');
 396             return;
 397         }
 398
 399         if (count($this->feedbackstructure->get_items(true)) > self::PREVIEWCOLUMNSLIMIT) {
 400             echo $OUTPUT->notification(get_string('questionslimited', 'feedback', self::PREVIEWCOLUMNSLIMIT), 'info');
 401         }
 402
 403         $this->out($this->showall ? $grandtotal : FEEDBACK_DEFAULT_PAGE_COUNT,
 404                 $grandtotal > FEEDBACK_DEFAULT_PAGE_COUNT);
 405
 406         // Toggle 'Show all' link.
 407         if ($this->totalrows > FEEDBACK_DEFAULT_PAGE_COUNT) {
 408             if (!$this->use_pages) {
 409                 echo html_writer::div(html_writer::link(new moodle_url($this->baseurl, [$this->showallparamname => 0]),
 410                         get_string('showperpage', '', FEEDBACK_DEFAULT_PAGE_COUNT)), 'showall');
 411             } else {
 412                 echo html_writer::div(html_writer::link(new moodle_url($this->baseurl, [$this->showallparamname => 1]),
 413                         get_string('showall', '', $this->totalrows)), 'showall');
 414             }
 415         }
 416     }
 417
 418     /**
 419      * Returns links to previous/next responses in the list
 420      * @param stdClass $record
 421      * @return array array of three elements [$prevresponseurl, $returnurl, $nextresponseurl]
 422      */
 423     public function get_reponse_navigation_links($record) {
 424         $this->setup();
 425         $grandtotal = $this->get_total_responses_count();
 426         $this->query_db($grandtotal);
 427         $lastrow = $thisrow = $nextrow = null;
 428         $counter = 0;
 429         $page = 0;
 430         while ($this->rawdata->valid()) {
 431             $row = $this->rawdata->current();
 432             if ($row->id == $record->id) {
 433                 $page = $this->showall ? 0 : floor($counter / FEEDBACK_DEFAULT_PAGE_COUNT);
 434                 $thisrow = $row;
 435                 $this->rawdata->next();
 436                 $nextrow = $this->rawdata->valid() ? $this->rawdata->current() : null;
 437                 break;
 438             }
 439             $lastrow = $row;
 440             $this->rawdata->next();
 441             $counter++;
 442         }
 443         $this->rawdata->close();
 444         if (!$thisrow) {
 445             $lastrow = null;
 446         }
 447         return [
 448             $lastrow ? $this->get_link_single_entry($lastrow) : null,
 449             new moodle_url($this->baseurl, [$this->request[TABLE_VAR_PAGE] => $page]),
 450             $nextrow ? $this->get_link_single_entry($nextrow) : null,
 451         ];
 452     }
 453
 454     /**
 455      * Download the data.
 456      */
 457     public function download() {
 458         \core\session\manager::write_close();
 459         $this->out($this->get_total_responses_count(), false);
 460         exit;
 461     }
 462
 463     /**
 464      * Take the data returned from the db_query and go through all the rows
 465      * processing each col using either col_{columnname} method or other_cols
 466      * method or if other_cols returns NULL then put the data straight into the
 467      * table.
 468      *
 469      * This overwrites the parent method because full SQL query may fail on Mysql
 470      * because of the limit in the number of tables in the join. Therefore we only
 471      * join 59 tables in the main query and add the rest here.
 472      *
 473      * @return void
 474      */
 475     public function build_table() {
 476         if ($this->rawdata instanceof \Traversable && !$this->rawdata->valid()) {
 477             return;
 478         }
 479         if (!$this->rawdata) {
 480             return;
 481         }
 482
 483         $columnsgroups = [];
 484         if ($this->hasmorecolumns) {
 485             $items = $this->feedbackstructure->get_items(true);
 486             $notretrieveditems = array_slice($items, self::TABLEJOINLIMIT, $this->hasmorecolumns, true);
 487             $columnsgroups = array_chunk($notretrieveditems, self::TABLEJOINLIMIT, true);
 488         }
 489
 490         $chunk = [];
 491         foreach ($this->rawdata as $row) {
 492             if ($this->hasmorecolumns) {
 493                 $chunk[$row->id] = $row;
 494                 if (count($chunk) >= self::ROWCHUNKSIZE) {
 495                     $this->build_table_chunk($chunk, $columnsgroups);
 496                     $chunk = [];
 497                 }
 498             } else {
 499                 if ($this->buildforexternal) {
 500                     $this->add_data_for_external($row);
 501                 } else {
 502                     $this->add_data_keyed($this->format_row($row), $this->get_row_class($row));
 503                 }
 504             }
 505         }
 506         $this->build_table_chunk($chunk, $columnsgroups);
 507     }
 508
 509     /**
 510      * Retrieve additional columns. Database engine may have a limit on number of joins.
 511      *
 512      * @param array $rows Array of rows with already retrieved data, new values will be added to this array
 513      * @param array $columnsgroups array of arrays of columns. Each element has up to self::TABLEJOINLIMIT items. This
 514      *     is easy to calculate but because we can call this method many times we calculate it once and pass by
 515      *     reference for performance reasons
 516      */
 517     protected function build_table_chunk(&$rows, &$columnsgroups) {
 518         global $DB;
 519         if (!$rows) {
 520             return;
 521         }
 522
 523         foreach ($columnsgroups as $columnsgroup) {
 524             $fields = 'c.id';
 525             $from = '{feedback_completed} c';
 526             $params = [];
 527             foreach ($columnsgroup as $nr => $item) {
 528                 $fields .= ", v{$nr}.value AS val{$nr}";
 529                 $from .= " LEFT OUTER JOIN {feedback_value} v{$nr} " .
 530                     "ON v{$nr}.completed = c.id AND v{$nr}.item = :itemid{$nr}";
 531                 $params["itemid{$nr}"] = $item->id;
 532             }
 533             list($idsql, $idparams) = $DB->get_in_or_equal(array_keys($rows), SQL_PARAMS_NAMED);
 534             $sql = "SELECT $fields FROM $from WHERE c.id ".$idsql;
 535             $results = $DB->get_records_sql($sql, $params + $idparams);
 536             foreach ($results as $result) {
 537                 foreach ($result as $key => $value) {
 538                     $rows[$result->id]->{$key} = $value;
 539                 }
 540             }
 541         }
 542
 543         foreach ($rows as $row) {
 544             if ($this->buildforexternal) {
 545                 $this->add_data_for_external($row);
 546             } else {
 547                 $this->add_data_keyed($this->format_row($row), $this->get_row_class($row));
 548             }
 549         }
 550     }
 551
 552     /**
 553      * Returns html code for displaying "Download" button if applicable.
 554      */
 555     public function download_buttons() {
 556         global $OUTPUT;
 557
 558         if ($this->is_downloadable() && !$this->is_downloading()) {
 559             return $OUTPUT->download_dataformat_selector(get_string('downloadas', 'table'),
 560                     $this->baseurl->out_omit_querystring(), $this->downloadparamname, $this->baseurl->params());
 561         } else {
 562             return '';
 563         }
 564     }
 565
 566     /**
 567      * Return user responses data ready for the external function.
 568      *
 569      * @param stdClass $row the table row containing the responses
 570      * @return array returns the responses ready to be used by an external function
 571      * @since Moodle 3.3
 572      */
 573     protected function get_responses_for_external($row) {
 574         $responses = [];
 575         foreach ($row as $el => $val) {
 576             // Get id from column name.
 577             if (preg_match('/^val(\d+)$/', $el, $matches)) {
 578                 $id = $matches[1];
 579
 580                 $responses[] = [
 581                     'id' => $id,
 582                     'name' => $this->headers[$this->columns[$el]],
 583                     'printval' => $this->other_cols($el, $row),
 584                     'rawval' => $val,
 585                 ];
 586             }
 587         }
 588         return $responses;
 589     }
 590
 591     /**
 592      * Add data for the external structure that will be returned.
 593      *
 594      * @param stdClass $row a database query record row
 595      * @since Moodle 3.3
 596      */
 597     protected function add_data_for_external($row) {
 598         $this->dataforexternal[] = [
 599             'id' => $row->id,
 600             'courseid' => $row->courseid,
 601             'userid' => $row->userid,
 602             'fullname' => fullname($row),
 603             'timemodified' => $row->completed_timemodified,
 604             'responses' => $this->get_responses_for_external($row),
 605         ];
 606     }
 607
 608     /**
 609      * Exports the table as an external structure handling pagination.
 610      *
 611      * @param int $page page number (for pagination)
 612      * @param int $perpage elements per page
 613      * @since Moodle 3.3
 614      * @return array returns the table ready to be used by an external function
 615      */
 616     public function export_external_structure($page = 0, $perpage = 0) {
 617
 618         $this->buildforexternal = true;
 619         $this->add_all_values_to_output();
 620         // Set-up.
 621         $this->setup();
 622         // Override values, if needed.
 623         if ($perpage > 0) {
 624             $this->pageable = true;
 625             $this->currpage = $page;
 626             $this->pagesize = $perpage;
 627         } else {
 628             $this->pagesize = $this->get_total_responses_count();
 629         }
 630         $this->query_db($this->pagesize, false);
 631         $this->build_table();
 632         $this->close_recordset();
 633         return $this->dataforexternal;
 634     }
 635 }