lib/tablelib.php

   1 <?php
   2
   3 // This file is part of Moodle - http://moodle.org/
   4 //
   5 // Moodle is free software: you can redistribute it and/or modify
   6 // it under the terms of the GNU General Public License as published by
   7 // the Free Software Foundation, either version 3 of the License, or
   8 // (at your option) any later version.
   9 //
  10 // Moodle is distributed in the hope that it will be useful,
  11 // but WITHOUT ANY WARRANTY; without even the implied warranty of
  12 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13 // GNU General Public License for more details.
  14 //
  15 // You should have received a copy of the GNU General Public License
  16 // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  17
  18 /**
  19  * @package    core
  20  * @subpackage lib
  21  * @copyright  1999 onwards Martin Dougiamas  {@link http://moodle.com}
  22  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  23  */
  24
  25 defined('MOODLE_INTERNAL') || die();
  26
  27 /** TABLE_VAR_SORT = 1 */
  28 define('TABLE_VAR_SORT',   1);
  29 /** TABLE_VAR_HIDE = 2 */
  30 define('TABLE_VAR_HIDE',   2);
  31 /** TABLE_VAR_SHOW = 3 */
  32 define('TABLE_VAR_SHOW',   3);
  33 /** TABLE_VAR_IFIRST = 4 */
  34 define('TABLE_VAR_IFIRST', 4);
  35 /** TABLE_VAR_ILAST = 5 */
  36 define('TABLE_VAR_ILAST',  5);
  37 /** TABLE_VAR_PAGE = 6 */
  38 define('TABLE_VAR_PAGE',   6);
  39
  40 /** TABLE_P_TOP = 1 */
  41 define('TABLE_P_TOP',   1);
  42 /** TABLE_P_BOTTOM = 2 */
  43 define('TABLE_P_BOTTOM',  2);
  44
  45 /**
  46  * @package   moodlecore
  47  * @copyright 1999 onwards Martin Dougiamas  {@link http://moodle.com}
  48  * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  49  */
  50 class flexible_table {
  51
  52     var $uniqueid        = NULL;
  53     var $attributes      = array();
  54     var $headers         = array();
  55     var $columns         = array();
  56     var $column_style    = array();
  57     var $column_class    = array();
  58     var $column_suppress = array();
  59     var $column_nosort   = array('userpic');
  60     var $setup           = false;
  61     var $sess            = NULL;
  62     var $baseurl         = NULL;
  63     var $request         = array();
  64
  65     var $is_collapsible = false;
  66     var $is_sortable    = false;
  67     var $use_pages      = false;
  68     var $use_initials   = false;
  69
  70     var $maxsortkeys = 2;
  71     var $pagesize    = 30;
  72     var $currpage    = 0;
  73     var $totalrows   = 0;
  74     var $sort_default_column = NULL;
  75     var $sort_default_order  = SORT_ASC;
  76
  77     /**
  78      * Array of positions in which to display download controls.
  79      */
  80     var $showdownloadbuttonsat= array(TABLE_P_TOP);
  81
  82
  83     /**
  84      * @var string Key of field returned by db query that is the id field of the
  85      * user table or equivalent.
  86      */
  87     public $useridfield = 'id';
  88
  89     /**
  90      * @var string which download plugin to use. Default '' means none - print
  91      * html table with paging. Property set by is_downloading which typically
  92      * passes in cleaned data from $
  93      */
  94     var $download  = '';
  95
  96     /**
  97      * @var boolean whether data is downloadable from table. Determines whether
  98      * to display download buttons. Set by method downloadable().
  99      */
 100     var $downloadable = false;
 101
 102     /**
 103      * @var string which download plugin to use. Default '' means none - print
 104      * html table with paging.
 105      */
 106     var $defaultdownloadformat  = 'csv';
 107
 108     /**
 109      * @var boolean Has start output been called yet?
 110      */
 111     var $started_output = false;
 112
 113     var $exportclass = null;
 114
 115     /**
 116      * Constructor
 117      * @param int $uniqueid
 118      * @todo Document properly
 119      */
 120     function flexible_table($uniqueid) {
 121         $this->uniqueid = $uniqueid;
 122         $this->request  = array(
 123             TABLE_VAR_SORT    => 'tsort',
 124             TABLE_VAR_HIDE    => 'thide',
 125             TABLE_VAR_SHOW    => 'tshow',
 126             TABLE_VAR_IFIRST  => 'tifirst',
 127             TABLE_VAR_ILAST   => 'tilast',
 128             TABLE_VAR_PAGE    => 'page'
 129         );
 130     }
 131
 132     /**
 133      * Call this to pass the download type. Use :
 134      *         $download = optional_param('download', '', PARAM_ALPHA);
 135      * To get the download type. We assume that if you call this function with
 136      * params that this table's data is downloadable, so we call is_downloadable
 137      * for you (even if the param is '', which means no download this time.
 138      * Also you can call this method with no params to get the current set
 139      * download type.
 140      * @param string $download download type. One of csv, tsv, xhtml, ods, etc
 141      * @param string $filename filename for downloads without file extension.
 142      * @param string $sheettitle title for downloaded data.
 143      * @return string download type.  One of csv, tsv, xhtml, ods, etc
 144      */
 145     function is_downloading($download = null, $filename='', $sheettitle=''){
 146         if ($download!==null){
 147             $this->sheettitle = $sheettitle;
 148             $this->is_downloadable(true);
 149             $this->download = $download;
 150             $this->filename = clean_filename($filename);
 151             $this->export_class_instance();
 152         }
 153         return $this->download;
 154     }
 155
 156     function export_class_instance(&$exportclass=null){
 157         if (!is_null($exportclass)){
 158             $this->started_output = true;
 159             $this->exportclass =& $exportclass;
 160             $this->exportclass->table =& $this;
 161         } elseif (is_null($this->exportclass) && !empty($this->download)){
 162             $classname = 'table_'.$this->download.'_export_format';
 163             $this->exportclass = new $classname($this);
 164             if (!$this->exportclass->document_started()){
 165                 $this->exportclass->start_document($this->filename);
 166             }
 167         }
 168         return $this->exportclass;
 169     }
 170
 171
 172     /**
 173      * Probably don't need to call this directly. Calling is_downloading with a
 174      * param automatically sets table as downloadable.
 175      *
 176      * @param boolean $downloadable optional param to set whether data from
 177      * table is downloadable. If ommitted this function can be used to get
 178      * current state of table.
 179      * @return boolean whether table data is set to be downloadable.
 180      */
 181     function is_downloadable($downloadable = null){
 182         if ($downloadable !== null){
 183             $this->downloadable = $downloadable;
 184         }
 185         return $this->downloadable;
 186     }
 187
 188     /**
 189      * Where to show download buttons.
 190      * @param array $showat array of postions in which to show download buttons.
 191      * Containing TABLE_P_TOP and/or TABLE_P_BOTTOM
 192      */
 193     function show_download_buttons_at($showat){
 194         $this->showdownloadbuttonsat = $showat;
 195     }
 196
 197
 198     /**
 199      * Sets the is_sortable variable to the given boolean, sort_default_column to
 200      * the given string, and the sort_default_order to the given integer.
 201      * @param bool $bool
 202      * @param string $defaultcolumn
 203      * @param int $defaultorder
 204      * @return void
 205      */
 206     function sortable($bool, $defaultcolumn = NULL, $defaultorder = SORT_ASC) {
 207         $this->is_sortable = $bool;
 208         $this->sort_default_column = $defaultcolumn;
 209         $this->sort_default_order  = $defaultorder;
 210     }
 211
 212     /**
 213      * Do not sort using this column
 214      * @param string column name
 215      */
 216     function no_sorting($column) {
 217         $this->column_nosort[] = $column;
 218     }
 219
 220     /**
 221      * Is the column sortable?
 222      * @param string column name, null means table
 223      * @return bool
 224      */
 225     function is_sortable($column=null) {
 226         if (empty($column)) {
 227             return $this->is_sortable;
 228         }
 229         if (!$this->is_sortable) {
 230             return false;
 231         }
 232         return !in_array($column, $this->column_nosort);
 233     }
 234     /**
 235      * Sets the is_collapsible variable to the given boolean.
 236      * @param bool $bool
 237      * @return void
 238      */
 239     function collapsible($bool) {
 240         $this->is_collapsible = $bool;
 241     }
 242
 243     /**
 244      * Sets the use_pages variable to the given boolean.
 245      * @param bool $bool
 246      * @return void
 247      */
 248     function pageable($bool) {
 249         $this->use_pages = $bool;
 250     }
 251
 252     /**
 253      * Sets the use_initials variable to the given boolean.
 254      * @param bool $bool
 255      * @return void
 256      */
 257     function initialbars($bool) {
 258         $this->use_initials = $bool;
 259     }
 260
 261     /**
 262      * Sets the pagesize variable to the given integer, the totalrows variable
 263      * to the given integer, and the use_pages variable to true.
 264      * @param int $perpage
 265      * @param int $total
 266      * @return void
 267      */
 268     function pagesize($perpage, $total) {
 269         $this->pagesize  = $perpage;
 270         $this->totalrows = $total;
 271         $this->use_pages = true;
 272     }
 273
 274     /**
 275      * Assigns each given variable in the array to the corresponding index
 276      * in the request class variable.
 277      * @param array $variables
 278      * @return void
 279      */
 280     function set_control_variables($variables) {
 281         foreach($variables as $what => $variable) {
 282             if(isset($this->request[$what])) {
 283                 $this->request[$what] = $variable;
 284             }
 285         }
 286     }
 287
 288     /**
 289      * Gives the given $value to the $attribute index of $this->attributes.
 290      * @param string $attribute
 291      * @param mixed $value
 292      * @return void
 293      */
 294     function set_attribute($attribute, $value) {
 295         $this->attributes[$attribute] = $value;
 296     }
 297
 298     /**
 299      * What this method does is set the column so that if the same data appears in
 300      * consecutive rows, then it is not repeated.
 301      *
 302      * For example, in the quiz overview report, the fullname column is set to be suppressed, so
 303      * that when one student has made multiple attempts, their name is only printed in the row
 304      * for their first attempt.
 305      * @param integer $column the index of a column.
 306      */
 307     function column_suppress($column) {
 308         if(isset($this->column_suppress[$column])) {
 309             $this->column_suppress[$column] = true;
 310         }
 311     }
 312
 313     /**
 314      * Sets the given $column index to the given $classname in $this->column_class.
 315      * @param integer $column
 316      * @param string $classname
 317      * @return void
 318      */
 319     function column_class($column, $classname) {
 320         if(isset($this->column_class[$column])) {
 321             $this->column_class[$column] = ' '.$classname; // This space needed so that classnames don't run together in the HTML
 322         }
 323     }
 324
 325     /**
 326      * Sets the given $column index and $property index to the given $value in $this->column_style.
 327      * @param integer $column
 328      * @param string $property
 329      * @param mixed $value
 330      * @return void
 331      */
 332     function column_style($column, $property, $value) {
 333         if(isset($this->column_style[$column])) {
 334             $this->column_style[$column][$property] = $value;
 335         }
 336     }
 337
 338     /**
 339      * Sets all columns' $propertys to the given $value in $this->column_style.
 340      * @param integer $property
 341      * @param string $value
 342      * @return void
 343      */
 344     function column_style_all($property, $value) {
 345         foreach(array_keys($this->columns) as $column) {
 346             $this->column_style[$column][$property] = $value;
 347         }
 348     }
 349
 350     /**
 351      * Sets $this->reseturl to the given $url, and $this->baseurl to the given $url plus ? or &amp;
 352      * @param string $url the url with params needed to call up this page
 353      */
 354     function define_baseurl($url) {
 355         $this->reseturl = $url;
 356         if(!strpos($url, '?')) {
 357             $this->baseurl = $url.'?';
 358         }
 359         else {
 360             $this->baseurl = $url.'&amp;';
 361         }
 362     }
 363
 364     /**
 365      * @param array $columns an array of identifying names for columns. If
 366      * columns are sorted then column names must correspond to a field in sql.
 367      */
 368     function define_columns($columns) {
 369         $this->columns = array();
 370         $this->column_style = array();
 371         $this->column_class = array();
 372         $colnum = 0;
 373
 374         foreach($columns as $column) {
 375             $this->columns[$column]         = $colnum++;
 376             $this->column_style[$column]    = array();
 377             $this->column_class[$column]    = '';
 378             $this->column_suppress[$column] = false;
 379         }
 380     }
 381
 382     /**
 383      * @param array $headers numerical keyed array of displayed string titles
 384      * for each column.
 385      */
 386     function define_headers($headers) {
 387         $this->headers = $headers;
 388     }
 389
 390
 391
 392
 393     /**
 394      * Must be called after table is defined. Use methods above first. Cannot
 395      * use functions below till after calling this method.
 396      * @return type?
 397      */
 398     function setup() {
 399         global $SESSION, $CFG;
 400
 401         if(empty($this->columns) || empty($this->uniqueid)) {
 402             return false;
 403         }
 404
 405         if (!isset($SESSION->flextable)) {
 406             $SESSION->flextable = array();
 407         }
 408
 409         if(!isset($SESSION->flextable[$this->uniqueid])) {
 410             $SESSION->flextable[$this->uniqueid] = new stdClass;
 411             $SESSION->flextable[$this->uniqueid]->uniqueid = $this->uniqueid;
 412             $SESSION->flextable[$this->uniqueid]->collapse = array();
 413             $SESSION->flextable[$this->uniqueid]->sortby   = array();
 414             $SESSION->flextable[$this->uniqueid]->i_first  = '';
 415             $SESSION->flextable[$this->uniqueid]->i_last   = '';
 416         }
 417
 418         $this->sess = &$SESSION->flextable[$this->uniqueid];
 419
 420         if(!empty($_GET[$this->request[TABLE_VAR_SHOW]]) && isset($this->columns[$_GET[$this->request[TABLE_VAR_SHOW]]])) {
 421             // Show this column
 422             $this->sess->collapse[$_GET[$this->request[TABLE_VAR_SHOW]]] = false;
 423         }
 424         else if(!empty($_GET[$this->request[TABLE_VAR_HIDE]]) && isset($this->columns[$_GET[$this->request[TABLE_VAR_HIDE]]])) {
 425             // Hide this column
 426             $this->sess->collapse[$_GET[$this->request[TABLE_VAR_HIDE]]] = true;
 427             if(array_key_exists($_GET[$this->request[TABLE_VAR_HIDE]], $this->sess->sortby)) {
 428                 unset($this->sess->sortby[$_GET[$this->request[TABLE_VAR_HIDE]]]);
 429             }
 430         }
 431
 432         // Now, update the column attributes for collapsed columns
 433         foreach(array_keys($this->columns) as $column) {
 434             if(!empty($this->sess->collapse[$column])) {
 435                 $this->column_style[$column]['width'] = '10px';
 436             }
 437         }
 438
 439         if(
 440             !empty($_GET[$this->request[TABLE_VAR_SORT]]) && $this->is_sortable($_GET[$this->request[TABLE_VAR_SORT]]) &&
 441             (isset($this->columns[$_GET[$this->request[TABLE_VAR_SORT]]]) ||
 442                 (($_GET[$this->request[TABLE_VAR_SORT]] == 'firstname' || $_GET[$this->request[TABLE_VAR_SORT]] == 'lastname') && isset($this->columns['fullname']))
 443             ))
 444         {
 445             if(empty($this->sess->collapse[$_GET[$this->request[TABLE_VAR_SORT]]])) {
 446                 if(array_key_exists($_GET[$this->request[TABLE_VAR_SORT]], $this->sess->sortby)) {
 447                     // This key already exists somewhere. Change its sortorder and bring it to the top.
 448                     $sortorder = $this->sess->sortby[$_GET[$this->request[TABLE_VAR_SORT]]] == SORT_ASC ? SORT_DESC : SORT_ASC;
 449                     unset($this->sess->sortby[$_GET[$this->request[TABLE_VAR_SORT]]]);
 450                     $this->sess->sortby = array_merge(array($_GET[$this->request[TABLE_VAR_SORT]] => $sortorder), $this->sess->sortby);
 451                 }
 452                 else {
 453                     // Key doesn't exist, so just add it to the beginning of the array, ascending order
 454                     $this->sess->sortby = array_merge(array($_GET[$this->request[TABLE_VAR_SORT]] => SORT_ASC), $this->sess->sortby);
 455                 }
 456                 // Finally, make sure that no more than $this->maxsortkeys are present into the array
 457                 if(!empty($this->maxsortkeys) && ($sortkeys = count($this->sess->sortby)) > $this->maxsortkeys) {
 458                     while($sortkeys-- > $this->maxsortkeys) {
 459                         array_pop($this->sess->sortby);
 460                     }
 461                 }
 462             }
 463         }
 464
 465         // If we didn't sort just now, then use the default sort order if one is defined and the column exists
 466         if(empty($this->sess->sortby) && !empty($this->sort_default_column))  {
 467             $this->sess->sortby = array ($this->sort_default_column => ($this->sort_default_order == SORT_DESC ? SORT_DESC : SORT_ASC));
 468         }
 469
 470         if(isset($_GET[$this->request[TABLE_VAR_ILAST]])) {
 471             if(empty($_GET[$this->request[TABLE_VAR_ILAST]]) || is_numeric(strpos(get_string('alphabet', 'langconfig'), $_GET[$this->request[TABLE_VAR_ILAST]]))) {
 472                 $this->sess->i_last = $_GET[$this->request[TABLE_VAR_ILAST]];
 473             }
 474         }
 475
 476         if(isset($_GET[$this->request[TABLE_VAR_IFIRST]])) {
 477             if(empty($_GET[$this->request[TABLE_VAR_IFIRST]]) || is_numeric(strpos(get_string('alphabet', 'langconfig'), $_GET[$this->request[TABLE_VAR_IFIRST]]))) {
 478                 $this->sess->i_first = $_GET[$this->request[TABLE_VAR_IFIRST]];
 479             }
 480         }
 481
 482         if(empty($this->baseurl)) {
 483             $getcopy  = $_GET;
 484             unset($getcopy[$this->request[TABLE_VAR_SHOW]]);
 485             unset($getcopy[$this->request[TABLE_VAR_HIDE]]);
 486             unset($getcopy[$this->request[TABLE_VAR_SORT]]);
 487             unset($getcopy[$this->request[TABLE_VAR_IFIRST]]);
 488             unset($getcopy[$this->request[TABLE_VAR_ILAST]]);
 489             unset($getcopy[$this->request[TABLE_VAR_PAGE]]);
 490
 491             $strippedurl = strip_querystring(qualified_me());
 492
 493             if(!empty($getcopy)) {
 494                 $first = false;
 495                 $querystring = '';
 496                 foreach($getcopy as $var => $val) {
 497                     if(!$first) {
 498                         $first = true;
 499                         $querystring .= '?'.$var.'='.$val;
 500                     }
 501                     else {
 502                         $querystring .= '&amp;'.$var.'='.$val;
 503                     }
 504                 }
 505                 $this->reseturl =  $strippedurl.$querystring;
 506                 $querystring .= '&amp;';
 507             }
 508             else {
 509                 $this->reseturl =  $strippedurl;
 510                 $querystring = '?';
 511             }
 512
 513             $this->baseurl = strip_querystring(qualified_me()) . $querystring;
 514         }
 515
 516         // If it's "the first time" we 've been here, forget the previous initials filters
 517         if(qualified_me() == $this->reseturl) {
 518             $this->sess->i_first = '';
 519             $this->sess->i_last  = '';
 520         }
 521
 522         $this->currpage = optional_param($this->request[TABLE_VAR_PAGE], 0, PARAM_INT);
 523         $this->setup = true;
 524
 525     /// Always introduce the "flexible" class for the table if not specified
 526     /// No attributes, add flexible class
 527         if (empty($this->attributes)) {
 528             $this->attributes['class'] = 'flexible';
 529     /// No classes, add flexible class
 530         } else if (!isset($this->attributes['class'])) {
 531             $this->attributes['class'] = 'flexible';
 532     /// No flexible class in passed classes, add flexible class
 533         } else if (!in_array('flexible', explode(' ', $this->attributes['class']))) {
 534             $this->attributes['class'] = trim('flexible ' . $this->attributes['class']);
 535         }
 536     }
 537
 538     /**
 539      * Get the order by clause from the session, for the table with id $uniqueid.
 540      * @param string $uniqueid the identifier for a table.
 541      * @return SQL fragment that can be used in an ORDER BY clause.
 542      */
 543     public static function get_sort_for_table($uniqueid) {
 544         global $SESSION;
 545         if(empty($SESSION->flextable[$uniqueid])) {
 546            return '';
 547         }
 548
 549         $sess = &$SESSION->flextable[$uniqueid];
 550         if (empty($sess->sortby)) {
 551             return '';
 552         }
 553
 554         return self::construct_order_by($sess->sortby);
 555     }
 556
 557     /**
 558      * Prepare an an order by clause from the list of columns to be sorted.
 559      * @param array $cols column name => SORT_ASC or SORT_DESC
 560      * @return SQL fragment that can be used in an ORDER BY clause.
 561      */
 562     public static function construct_order_by($cols) {
 563         $bits = array();
 564
 565         foreach($cols as $column => $order) {
 566             if ($order == SORT_ASC) {
 567                 $bits[] = $column . ' ASC';
 568             } else {
 569                 $bits[] = $column . ' DESC';
 570             }
 571         }
 572
 573         return implode(', ', $bits);
 574     }
 575
 576     /**
 577      * @return SQL fragment that can be used in an ORDER BY clause.
 578      */
 579     public function get_sql_sort() {
 580         return self::construct_order_by($this->get_sort_columns());
 581     }
 582
 583     /**
 584      * Get the columns to sort by, in the form required by {@link construct_order_by()}.
 585      * @return array column name => SORT_... constant.
 586      */
 587     public function get_sort_columns() {
 588         if (!$this->setup) {
 589             throw new coding_exception('Cannot call get_sort_columns until you have called setup.');
 590         }
 591
 592         if (empty($this->sess->sortby)) {
 593             return array();
 594         }
 595
 596         return $this->sess->sortby;
 597     }
 598
 599     /**
 600      * @return integer the offset for LIMIT clause of SQL
 601      */
 602     function get_page_start() {
 603         if(!$this->use_pages) {
 604             return '';
 605         }
 606         return $this->currpage * $this->pagesize;
 607     }
 608
 609     /**
 610      * @return integer the pagesize for LIMIT clause of SQL
 611      */
 612     function get_page_size() {
 613         if(!$this->use_pages) {
 614             return '';
 615         }
 616         return $this->pagesize;
 617     }
 618
 619     /**
 620      * @return array - sql where, params array
 621      */
 622     function get_sql_where() {
 623         global $DB;
 624
 625         $conditions = array();
 626         $params = array();
 627
 628         if (isset($this->columns['fullname'])) {
 629             static $i = 0;
 630             $i++;
 631
 632             if (!empty($this->sess->i_first)) {
 633                 $conditions[] = $DB->sql_like('firstname', ':ifirstc'.$i, false, false);
 634                 $params['ifirstc'.$i] = $this->sess->i_first.'%';
 635             }
 636             if (!empty($this->sess->i_last)) {
 637                 $conditions[] = $DB->sql_like('lastname', ':ilastc'.$i, false, false);
 638                 $params['ilastc'.$i] = $this->sess->i_last.'%';
 639             }
 640         }
 641
 642         return array(implode(" AND ", $conditions), $params);
 643     }
 644
 645     /**
 646      * Add a row of data to the table. This function takes an array with
 647      * column names as keys.
 648      * It ignores any elements with keys that are not defined as columns. It
 649      * puts in empty strings into the row when there is no element in the passed
 650      * array corresponding to a column in the table. It puts the row elements in
 651      * the proper order.
 652      * @param $rowwithkeys array
 653      * @param string $classname CSS class name to add to this row's tr tag.
 654      */
 655     function add_data_keyed($rowwithkeys, $classname = ''){
 656         $this->add_data($this->get_row_from_keyed($rowwithkeys), $classname);
 657     }
 658
 659     /**
 660      * Add a seperator line to table.
 661      */
 662     function add_separator() {
 663         if(!$this->setup) {
 664             return false;
 665         }
 666         $this->add_data(NULL);
 667     }
 668
 669     /**
 670      * This method actually directly echoes the row passed to it now or adds it
 671      * to the download. If this is the first row and start_output has not
 672      * already been called this method also calls start_output to open the table
 673      * or send headers for the downloaded.
 674      * Can be used as before. print_html now calls finish_html to close table.
 675      *
 676      * @param array $row a numerically keyed row of data to add to the table.
 677      * @param string $classname CSS class name to add to this row's tr tag.
 678      * @return boolean success.
 679      */
 680     function add_data($row, $classname = '') {
 681         if(!$this->setup) {
 682             return false;
 683         }
 684         if (!$this->started_output){
 685             $this->start_output();
 686         }
 687         if ($this->exportclass!==null){
 688             if ($row === null){
 689                 $this->exportclass->add_seperator();
 690             } else {
 691                 $this->exportclass->add_data($row);
 692             }
 693         } else {
 694             $this->print_row($row, $classname);
 695         }
 696         return true;
 697     }
 698
 699
 700
 701     /**
 702      * You should call this to finish outputting the table data after adding
 703      * data to the table with add_data or add_data_keyed.
 704      *
 705      */
 706     function finish_output($closeexportclassdoc = true){
 707         if ($this->exportclass!==null){
 708             $this->exportclass->finish_table();
 709             if ($closeexportclassdoc){
 710                 $this->exportclass->finish_document();
 711             }
 712         }else{
 713             $this->finish_html();
 714         }
 715     }
 716
 717     /**
 718      * Hook that can be overridden in child classes to wrap a table in a form
 719      * for example. Called only when there is data to display and not
 720      * downloading.
 721      */
 722     function wrap_html_start(){
 723     }
 724
 725     /**
 726      * Hook that can be overridden in child classes to wrap a table in a form
 727      * for example. Called only when there is data to display and not
 728      * downloading.
 729      */
 730     function wrap_html_finish(){
 731     }
 732
 733
 734     /**
 735      *
 736      * @param array $row row of data from db used to make one row of the table.
 737      * @return array one row for the table, added using add_data_keyed method.
 738      */
 739     function format_row($row){
 740         $formattedrow = array();
 741         foreach (array_keys($this->columns) as $column){
 742             $colmethodname = 'col_'.$column;
 743             if (method_exists($this, $colmethodname)){
 744                 $formattedcolumn = $this->$colmethodname($row);
 745             } else {
 746                 $formattedcolumn = $this->other_cols($column, $row);
 747                 if ($formattedcolumn===NULL){
 748                     $formattedcolumn = $row->$column;
 749                 }
 750             }
 751             $formattedrow[$column] = $formattedcolumn;
 752         }
 753         return $formattedrow;
 754     }
 755
 756     /**
 757      * Fullname is treated as a special columname in tablelib and should always
 758      * be treated the same as the fullname of a user.
 759      * @uses $this->useridfield if the userid field is not expected to be id
 760      * then you need to override $this->useridfield to point at the correct
 761      * field for the user id.
 762      *
 763      */
 764     function col_fullname($row){
 765         global $COURSE, $CFG;
 766
 767         if (!$this->download){
 768             if ($COURSE->id == SITEID) {
 769                 return '<a href="'.$CFG->wwwroot.'/user/profile.php?id='.$row->{$this->useridfield}.  '">'.
 770                         fullname($row).'</a>';
 771             } else {
 772                 return '<a href="'.$CFG->wwwroot.'/user/view.php?id='.$row->{$this->useridfield}.
 773                         '&amp;course='.$COURSE->id.'">'.fullname($row).'</a>';
 774             }
 775         } else {
 776             return fullname($row);
 777         }
 778     }
 779
 780     /**
 781      * You can override this method in a child class. See the description of
 782      * build_table which calls this method.
 783      */
 784     function other_cols($column, $row){
 785         return NULL;
 786     }
 787
 788
 789     /**
 790      * Used from col_* functions when text is to be displayed. Does the
 791      * right thing - either converts text to html or strips any html tags
 792      * depending on if we are downloading and what is the download type. Params
 793      * are the same as format_text function in weblib.php but some default
 794      * options are changed.
 795      */
 796     function format_text($text, $format=FORMAT_MOODLE, $options=NULL, $courseid=NULL){
 797         if (!$this->is_downloading()){
 798             if (is_null($options)){
 799                 $options = new stdClass;
 800             }
 801             //some sensible defaults
 802             if (!isset($options->para)){
 803                 $options->para = false;
 804             }
 805             if (!isset($options->newlines)){
 806                 $options->newlines = false;
 807             }
 808             if (!isset($options->smiley)) {
 809                 $options->smiley = false;
 810             }
 811             if (!isset($options->filter)) {
 812                 $options->filter = false;
 813             }
 814             return format_text($text, $format, $options);
 815         } else {
 816             $eci =& $this->export_class_instance();
 817             return $eci->format_text($text, $format, $options, $courseid);
 818         }
 819     }
 820     /**
 821      * This method is deprecated although the old api is still supported.
 822      * @deprecated 1.9.2 - Jun 2, 2008
 823      */
 824     function print_html() {
 825         if(!$this->setup) {
 826             return false;
 827         }
 828         $this->finish_html();
 829     }
 830
 831     /**
 832      * This function is not part of the public api.
 833      * @return string initial of first name we are currently filtering by
 834      */
 835     function get_initial_first() {
 836         if(!$this->use_initials) {
 837             return NULL;
 838         }
 839
 840         return $this->sess->i_first;
 841     }
 842
 843     /**
 844      * This function is not part of the public api.
 845      * @return string initial of last name we are currently filtering by
 846      */
 847     function get_initial_last() {
 848         if(!$this->use_initials) {
 849             return NULL;
 850         }
 851
 852         return $this->sess->i_last;
 853     }
 854
 855     /**
 856      * This function is not part of the public api.
 857      */
 858     function print_initials_bar(){
 859         if ((!empty($this->sess->i_last) || !empty($this->sess->i_first) || $this->use_initials)
 860                     && isset($this->columns['fullname'])) {
 861
 862             $strall = get_string('all');
 863             $alpha  = explode(',', get_string('alphabet', 'langconfig'));
 864
 865             // Bar of first initials
 866
 867             echo '<div class="initialbar firstinitial">'.get_string('firstname').' : ';
 868             if(!empty($this->sess->i_first)) {
 869                 echo '<a href="'.$this->baseurl.$this->request[TABLE_VAR_IFIRST].'=">'.$strall.'</a>';
 870             } else {
 871                 echo '<strong>'.$strall.'</strong>';
 872             }
 873             foreach ($alpha as $letter) {
 874                 if (isset($this->sess->i_first) && $letter == $this->sess->i_first) {
 875                     echo ' <strong>'.$letter.'</strong>';
 876                 } else {
 877                     echo ' <a href="'.$this->baseurl.$this->request[TABLE_VAR_IFIRST].'='.$letter.'">'.$letter.'</a>';
 878                 }
 879             }
 880             echo '</div>';
 881
 882             // Bar of last initials
 883
 884             echo '<div class="initialbar lastinitial">'.get_string('lastname').' : ';
 885             if(!empty($this->sess->i_last)) {
 886                 echo '<a href="'.$this->baseurl.$this->request[TABLE_VAR_ILAST].'=">'.$strall.'</a>';
 887             } else {
 888                 echo '<strong>'.$strall.'</strong>';
 889             }
 890             foreach ($alpha as $letter) {
 891                 if (isset($this->sess->i_last) && $letter == $this->sess->i_last) {
 892                     echo ' <strong>'.$letter.'</strong>';
 893                 } else {
 894                     echo ' <a href="'.$this->baseurl.$this->request[TABLE_VAR_ILAST].'='.$letter.'">'.$letter.'</a>';
 895                 }
 896             }
 897             echo '</div>';
 898
 899         }
 900     }
 901
 902     /**
 903      * This function is not part of the public api.
 904      */
 905     function print_nothing_to_display(){
 906         global $OUTPUT;
 907         $this->print_initials_bar();
 908
 909         echo $OUTPUT->heading(get_string('nothingtodisplay'));
 910     }
 911
 912     /**
 913      * This function is not part of the public api.
 914      */
 915     function get_row_from_keyed($rowwithkeys){
 916         if (is_object($rowwithkeys)){
 917             $rowwithkeys = (array)$rowwithkeys;
 918         }
 919         $row = array();
 920         foreach (array_keys($this->columns) as $column){
 921             if (isset($rowwithkeys[$column])){
 922                 $row [] = $rowwithkeys[$column];
 923             } else {
 924                 $row[] ='';
 925             }
 926         }
 927         return $row;
 928     }
 929     /**
 930      * This function is not part of the public api.
 931      */
 932     function get_download_menu(){
 933         $allclasses= get_declared_classes();
 934         $exportclasses = array();
 935         foreach ($allclasses as $class){
 936             $matches = array();
 937             if (preg_match('/^table\_([a-z]+)\_export\_format$/', $class, $matches)){
 938                 $type = $matches[1];
 939                 $exportclasses[$type]= get_string("download$type", 'table');
 940             }
 941         }
 942         return $exportclasses;
 943     }
 944
 945     /**
 946      * This function is not part of the public api.
 947      */
 948     function download_buttons(){
 949         global $OUTPUT;
 950         if ($this->is_downloadable() && !$this->is_downloading()){
 951             $downloadoptions = $this->get_download_menu();
 952             $html = '<form action="'. $this->baseurl .'" method="post">';
 953             $html .= '<div class="mdl-align">';
 954             $html .= '<input type="submit" value="'.get_string('downloadas', 'table').'"/>';
 955             $html .= html_writer::select($downloadoptions, 'download', $this->defaultdownloadformat, false);
 956             $html .= '</div></form>';
 957
 958             return $html;
 959         } else {
 960             return '';
 961         }
 962     }
 963     /**
 964      * This function is not part of the public api.
 965      * You don't normally need to call this. It is called automatically when
 966      * needed when you start adding data to the table.
 967      *
 968      */
 969     function start_output(){
 970         $this->started_output = true;
 971         if ($this->exportclass!==null){
 972             $this->exportclass->start_table($this->sheettitle);
 973             $this->exportclass->output_headers($this->headers);
 974         } else {
 975             $this->start_html();
 976             $this->print_headers();
 977         }
 978     }
 979
 980     /**
 981      * This function is not part of the public api.
 982      */
 983     function print_row($row, $classname = '') {
 984         static $suppress_lastrow = NULL;
 985         static $oddeven = 1;
 986         $rowclasses = array('r' . $oddeven);
 987         $oddeven = $oddeven ? 0 : 1;
 988
 989         if ($classname) {
 990             $rowclasses[] = $classname;
 991         }
 992
 993         echo '<tr class="' . implode(' ', $rowclasses) . '">';
 994
 995         // If we have a separator, print it
 996         if ($row === NULL) {
 997             $colcount = count($this->columns);
 998             echo '<td colspan="'.$colcount.'"><div class="tabledivider"></div></td>';
 999         } else {
1000             $colbyindex = array_flip($this->columns);
1001             foreach ($row as $index => $data) {
1002                 $column = $colbyindex[$index];
1003                 echo '<td class="cell c'.$index.$this->column_class[$column].'"'.$this->make_styles_string($this->column_style[$column]).'>';
1004                 if (empty($this->sess->collapse[$column])) {
1005                     if ($this->column_suppress[$column] && $suppress_lastrow !== NULL && $suppress_lastrow[$index] === $data) {
1006                         echo '&nbsp;';
1007                     } else {
1008                         echo $data;
1009                     }
1010                 } else {
1011                     echo '&nbsp;';
1012                 }
1013                 echo '</td>';
1014             }
1015         }
1016
1017         echo '</tr>';
1018
1019         $suppress_enabled = array_sum($this->column_suppress);
1020         if ($suppress_enabled) {
1021             $suppress_lastrow = $row;
1022         }
1023     }
1024     /**
1025      * This function is not part of the public api.
1026      */
1027     function finish_html(){
1028         global $OUTPUT;
1029         if (!$this->started_output) {
1030             //no data has been added to the table.
1031             $this->print_nothing_to_display();
1032         } else {
1033             echo '</table>';
1034             echo html_writer::end_tag('div');
1035             $this->wrap_html_finish();
1036             // Paging bar
1037             if(in_array(TABLE_P_BOTTOM, $this->showdownloadbuttonsat)) {
1038                 echo $this->download_buttons();
1039             }
1040             if($this->use_pages) {
1041                 $pagingbar = new paging_bar($this->totalrows, $this->currpage, $this->pagesize, $this->baseurl);
1042                 $pagingbar->pagevar = $this->request[TABLE_VAR_PAGE];
1043                 echo $OUTPUT->render($pagingbar);
1044             }
1045         }
1046     }
1047     /**
1048      * This function is not part of the public api.
1049      */
1050     function print_headers(){
1051         global $CFG, $OUTPUT;
1052
1053         echo '<tr>';
1054         foreach($this->columns as $column => $index) {
1055             $icon_hide = '';
1056             $icon_sort = '';
1057
1058             if($this->is_collapsible) {
1059                 if(!empty($this->sess->collapse[$column])) {
1060                     // some headers contain < br/> tags, do not include in title
1061                     $icon_hide = ' <a href="'.$this->baseurl.$this->request[TABLE_VAR_SHOW].'='.$column.'"><img src="'.$OUTPUT->pix_url('t/switch_plus') . '" title="'.get_string('show').' '.strip_tags($this->headers[$index]).'" alt="'.get_string('show').'" /></a>';
1062                 }
1063                 else if($this->headers[$index] !== NULL) {
1064                     // some headers contain < br/> tags, do not include in title
1065                     $icon_hide = ' <a href="'.$this->baseurl.$this->request[TABLE_VAR_HIDE].'='.$column.'"><img src="'.$OUTPUT->pix_url('t/switch_minus') . '" title="'.get_string('hide').' '.strip_tags($this->headers[$index]).'" alt="'.get_string('hide').'" /></a>';
1066                 }
1067             }
1068
1069             $primary_sort_column = '';
1070             $primary_sort_order  = '';
1071             if(reset($this->sess->sortby)) {
1072                 $primary_sort_column = key($this->sess->sortby);
1073                 $primary_sort_order  = current($this->sess->sortby);
1074             }
1075
1076             switch($column) {
1077
1078                 case 'fullname':
1079                 if($this->is_sortable($column)) {
1080                     $icon_sort_first = $icon_sort_last = '';
1081                     if($primary_sort_column == 'firstname') {
1082                         $lsortorder = get_string('asc');
1083                         if($primary_sort_order == SORT_ASC) {
1084                             $icon_sort_first = ' <img src="'.$OUTPUT->pix_url('t/down') . '" alt="'.get_string('asc').'" />';
1085                             $fsortorder = get_string('asc');
1086                         }
1087                         else {
1088                             $icon_sort_first = ' <img src="'.$OUTPUT->pix_url('t/up') . '" alt="'.get_string('desc').'" />';
1089                             $fsortorder = get_string('desc');
1090                         }
1091                     }
1092                     else if($primary_sort_column == 'lastname') {
1093                         $fsortorder = get_string('asc');
1094                         if($primary_sort_order == SORT_ASC) {
1095                             $icon_sort_last = ' <img src="'.$OUTPUT->pix_url('t/down') . '" alt="'.get_string('asc').'" />';
1096                             $lsortorder = get_string('asc');
1097                         }
1098                         else {
1099                             $icon_sort_last = ' <img src="'.$OUTPUT->pix_url('t/up') . '" alt="'.get_string('desc').'" />';
1100                             $lsortorder = get_string('desc');
1101                         }
1102                     } else {
1103                         $fsortorder = get_string('asc');
1104                         $lsortorder = get_string('asc');
1105                     }
1106
1107                     $override = new stdClass();
1108                     $override->firstname = 'firstname';
1109                     $override->lastname = 'lastname';
1110                     $fullnamelanguage = get_string('fullnamedisplay', '', $override);
1111
1112                     if (($CFG->fullnamedisplay == 'firstname lastname') or
1113                         ($CFG->fullnamedisplay == 'firstname') or
1114                         ($CFG->fullnamedisplay == 'language' and $fullnamelanguage == 'firstname lastname' )) {
1115                         $this->headers[$index] = '<a href="'.$this->baseurl.$this->request[TABLE_VAR_SORT].'=firstname">'.get_string('firstname').get_accesshide(get_string('sortby').' '.get_string('firstname').' '.$fsortorder).'</a> '.$icon_sort_first.' / '.
1116                                                  '<a href="'.$this->baseurl.$this->request[TABLE_VAR_SORT].'=lastname">'.get_string('lastname').get_accesshide(get_string('sortby').' '.get_string('lastname').' '.$lsortorder).'</a> '.$icon_sort_last;
1117                     } else {
1118                         $this->headers[$index] = '<a href="'.$this->baseurl.$this->request[TABLE_VAR_SORT].'=lastname">'.get_string('lastname').get_accesshide(get_string('sortby').' '.get_string('lastname').' '.$lsortorder).'</a> '.$icon_sort_last.' / '.
1119                                                  '<a href="'.$this->baseurl.$this->request[TABLE_VAR_SORT].'=firstname">'.get_string('firstname').get_accesshide(get_string('sortby').' '.get_string('firstname').' '.$fsortorder).'</a> '.$icon_sort_first;
1120                     }
1121                 }
1122                 break;
1123
1124                 case 'userpic':
1125                     // do nothing, do not display sortable links
1126                 break;
1127
1128                 default:
1129                 if($this->is_sortable($column)) {
1130                     if($primary_sort_column == $column) {
1131                         if($primary_sort_order == SORT_ASC) {
1132                             $icon_sort = ' <img src="'.$OUTPUT->pix_url('t/down') . '" alt="'.get_string('asc').'" />';
1133                             $localsortorder = get_string('asc');
1134                         }
1135                         else {
1136                             $icon_sort = ' <img src="'.$OUTPUT->pix_url('t/up') . '" alt="'.get_string('desc').'" />';
1137                             $localsortorder = get_string('desc');
1138                         }
1139                     } else {
1140                         $localsortorder = get_string('asc');
1141                     }
1142                     $this->headers[$index] = '<a href="'.$this->baseurl.$this->request[TABLE_VAR_SORT].'='.$column.'">'.$this->headers[$index].get_accesshide(get_string('sortby').' '.$this->headers[$index].' '.$localsortorder).'</a>';
1143                 }
1144             }
1145
1146             if($this->headers[$index] === NULL) {
1147                 echo '<th class="header c'.$index.$this->column_class[$column].'" scope="col">&nbsp;</th>';
1148             }
1149             else if(!empty($this->sess->collapse[$column])) {
1150                 echo '<th class="header c'.$index.$this->column_class[$column].'" scope="col">'.$icon_hide.'</th>';
1151             }
1152             else {
1153                 // took out nowrap for accessibility, might need replacement
1154                 if (!is_array($this->column_style[$column])) {
1155                     // $usestyles = array('white-space:nowrap');
1156                     $usestyles = '';
1157                  } else {
1158                     // $usestyles = $this->column_style[$column]+array('white-space'=>'nowrap');
1159                     $usestyles = $this->column_style[$column];
1160                  }
1161                 echo '<th class="header c'.$index.$this->column_class[$column].'" '.$this->make_styles_string($usestyles).' scope="col">'.$this->headers[$index].$icon_sort.'<div class="commands">'.$icon_hide.'</div></th>';
1162             }
1163
1164         }
1165         echo '</tr>';
1166     }
1167
1168     /**
1169      * This function is not part of the public api.
1170      */
1171     function start_html(){
1172         global $OUTPUT;
1173         // Do we need to print initial bars?
1174         $this->print_initials_bar();
1175
1176         // Paging bar
1177         if($this->use_pages) {
1178             $pagingbar = new paging_bar($this->totalrows, $this->currpage, $this->pagesize, $this->baseurl);
1179             $pagingbar->pagevar = $this->request[TABLE_VAR_PAGE];
1180             echo $OUTPUT->render($pagingbar);
1181         }
1182
1183         if(in_array(TABLE_P_TOP, $this->showdownloadbuttonsat)) {
1184             echo $this->download_buttons();
1185         }
1186
1187         $this->wrap_html_start();
1188         // Start of main data table
1189
1190         echo html_writer::start_tag('div', array('class'=>'no-overflow'));
1191         echo '<table'.$this->make_attributes_string($this->attributes).'>';
1192
1193     }
1194
1195     /**
1196      * This function is not part of the public api.
1197      * @todo Document
1198      * @return type?
1199      */
1200     function make_styles_string(&$styles) {
1201         if(empty($styles)) {
1202             return '';
1203         }
1204
1205         $string = ' style="';
1206         foreach($styles as $property => $value) {
1207             $string .= $property.':'.$value.';';
1208         }
1209         $string .= '"';
1210         return $string;
1211     }
1212
1213     /**
1214      * This function is not part of the public api.
1215      * @todo Document
1216      * @return type?
1217      */
1218     function make_attributes_string(&$attributes) {
1219         if(empty($attributes)) {
1220             return '';
1221         }
1222
1223         $string = ' ';
1224         foreach($attributes as $attr => $value) {
1225             $string .= ($attr.'="'.$value.'" ');
1226         }
1227
1228         return $string;
1229     }
1230 }
1231
1232 /**
1233  * @package   moodlecore
1234  * @copyright 1999 onwards Martin Dougiamas  {@link http://moodle.com}
1235  * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1236  */
1237 class table_sql extends flexible_table{
1238
1239     public $countsql = NULL;
1240     public $countparams = NULL;
1241     /**
1242      * @var object sql for querying db. Has fields 'fields', 'from', 'where', 'params'.
1243      */
1244     public $sql = NULL;
1245     /**
1246      * @var array Data fetched from the db.
1247      */
1248     public $rawdata = NULL;
1249
1250     /**
1251      * @var boolean Overriding default for this.
1252      */
1253     public $is_sortable    = true;
1254     /**
1255      * @var boolean Overriding default for this.
1256      */
1257     public $is_collapsible = true;
1258
1259
1260     /**
1261      * @param string $uniqueid a string identifying this table.Used as a key in
1262      *                          session  vars.
1263      */
1264     function table_sql($uniqueid){
1265         parent::flexible_table($uniqueid);
1266         // some sensible defaults
1267         $this->set_attribute('cellspacing', '0');
1268         $this->set_attribute('class', 'generaltable generalbox');
1269     }
1270
1271     /**
1272      * Take the data returned from the db_query and go through all the rows
1273      * processing each col using either col_{columnname} method or other_cols
1274      * method or if other_cols returns NULL then put the data straight into the
1275      * table.
1276      */
1277     function build_table(){
1278         if ($this->rawdata){
1279             foreach($this->rawdata as $row){
1280                 $formattedrow = $this->format_row($row);
1281                 $this->add_data_keyed($formattedrow);
1282             }
1283         }
1284     }
1285
1286
1287     /**
1288      * This is only needed if you want to use different sql to count rows.
1289      * Used for example when perhaps all db JOINS are not needed when counting
1290      * records. You don't need to call this function the count_sql
1291      * will be generated automatically.
1292      *
1293      * We need to count rows returned by the db seperately to the query itself
1294      * as we need to know how many pages of data we have to display.
1295      */
1296     function set_count_sql($sql, array $params = NULL){
1297         $this->countsql = $sql;
1298         $this->countparams = $params;
1299     }
1300
1301     /**
1302      * Set the sql to query the db. Query will be :
1303      *      SELECT $fields FROM $from WHERE $where
1304      * Of course you can use sub-queries, JOINS etc. by putting them in the
1305      * appropriate clause of the query.
1306      */
1307     function set_sql($fields, $from, $where, array $params = NULL){
1308         $this->sql = new stdClass();
1309         $this->sql->fields = $fields;
1310         $this->sql->from = $from;
1311         $this->sql->where = $where;
1312         $this->sql->params = $params;
1313     }
1314
1315     /**
1316      * Query the db. Store results in the table object for use by build_table.
1317      *
1318      * @param integer $pagesize size of page for paginated displayed table.
1319      * @param boolean $useinitialsbar do you want to use the initials bar. Bar
1320      * will only be used if there is a fullname column defined for the table.
1321      */
1322     function query_db($pagesize, $useinitialsbar=true){
1323         global $DB;
1324         if (!$this->is_downloading()) {
1325             if ($this->countsql === NULL){
1326                 $this->countsql = 'SELECT COUNT(1) FROM '.$this->sql->from.' WHERE '.$this->sql->where;
1327             }
1328             if ($useinitialsbar && !$this->is_downloading()) {
1329                 $totalinitials = $DB->count_records_sql($this->countsql, $this->countparams);
1330                 $this->initialbars($totalinitials>$pagesize);
1331             }
1332
1333             list($wsql, $wparams) = $this->get_sql_where();
1334             if ($wsql) {
1335                 $this->countsql .= ' AND '.$wsql;
1336                 $this->countparams = array_merge($this->countparams, $wparams);
1337
1338                 $this->sql->where .= ' AND '.$wsql;
1339                 $this->sql->params = array_merge($this->sql->params, $wparams);
1340
1341                 $total  = $DB->count_records_sql($this->countsql, $this->countparams);
1342             } else {
1343                 $total = $totalinitials;
1344             }
1345
1346             $this->pagesize($pagesize, $total);
1347         }
1348
1349         // Fetch the attempts
1350         $sort = $this->get_sql_sort();
1351         $sort = $sort?" ORDER BY {$sort}":'';
1352         $sql = "SELECT {$this->sql->fields} FROM {$this->sql->from} WHERE {$this->sql->where}{$sort}";
1353         if (!$this->is_downloading()) {
1354             $this->rawdata = $DB->get_records_sql($sql, $this->sql->params, $this->get_page_start(), $this->get_page_size());
1355         } else {
1356             $this->rawdata = $DB->get_records_sql($sql, $this->sql->params);
1357         }
1358     }
1359
1360
1361     /**
1362      * Convenience method to call a number of methods for you to display the
1363      * table.
1364      */
1365     function out($pagesize, $useinitialsbar, $downloadhelpbutton=''){
1366         global $DB;
1367         if (!$this->columns){
1368             $onerow = $DB->get_record_sql("SELECT {$this->sql->fields} FROM {$this->sql->from} WHERE {$this->sql->where}", $this->sql->params);
1369             //if columns is not set then define columns as the keys of the rows returned
1370             //from the db.
1371             $this->define_columns(array_keys((array)$onerow));
1372             $this->define_headers(array_keys((array)$onerow));
1373         }
1374         $this->setup();
1375         $this->query_db($pagesize, $useinitialsbar);
1376         $this->build_table();
1377         $this->finish_output();
1378     }
1379 }
1380
1381 /**
1382  * @package   moodlecore
1383  * @copyright 1999 onwards Martin Dougiamas  {@link http://moodle.com}
1384  * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1385  */
1386 class table_default_export_format_parent{
1387     /**
1388      * @var flexible_table or child class reference pointing to table class
1389      * object from which to export data.
1390      */
1391     var $table;
1392
1393     /**
1394      * @var boolean output started. Keeps track of whether any output has been
1395      * started yet.
1396      */
1397     var $documentstarted = false;
1398     function table_default_export_format_parent(&$table){
1399         $this->table =& $table;
1400     }
1401
1402     function set_table(&$table){
1403         $this->table =& $table;
1404     }
1405
1406     function add_data($row) {
1407         return false;
1408     }
1409     function add_seperator() {
1410         return false;
1411     }
1412     function document_started(){
1413         return $this->documentstarted;
1414     }
1415     /**
1416      * Given text in a variety of format codings, this function returns
1417      * the text as safe HTML or as plain text dependent on what is appropriate
1418      * for the download format. The default removes all tags.
1419      */
1420     function format_text($text, $format=FORMAT_MOODLE, $options=NULL, $courseid=NULL){
1421         //use some whitespace to indicate where there was some line spacing.
1422         $text = str_replace(array('</p>', "\n", "\r"), '   ', $text);
1423         return strip_tags($text);
1424     }
1425 }
1426
1427 /**
1428  * @package   moodlecore
1429  * @copyright 1999 onwards Martin Dougiamas  {@link http://moodle.com}
1430  * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1431  */
1432 class table_spreadsheet_export_format_parent extends table_default_export_format_parent{
1433     var $rownum;
1434     var $workbook;
1435     var $worksheet;
1436     /**
1437      * @var object format object - format for normal table cells
1438      */
1439     var $formatnormal;
1440     /**
1441      * @var object format object - format for header table cells
1442      */
1443     var $formatheaders;
1444
1445     /**
1446      * should be overriden in child class.
1447      */
1448     var $fileextension;
1449
1450     /**
1451      * This method will be overridden in the child class.
1452      */
1453     function define_workbook(){
1454     }
1455     function start_document($filename){
1456         $filename = $filename.'.'.$this->fileextension;
1457         $this->define_workbook();
1458         // format types
1459         $this->formatnormal =& $this->workbook->add_format();
1460         $this->formatnormal->set_bold(0);
1461         $this->formatheaders =& $this->workbook->add_format();
1462         $this->formatheaders->set_bold(1);
1463         $this->formatheaders->set_align('center');
1464         // Sending HTTP headers
1465         $this->workbook->send($filename);
1466         $this->documentstarted = true;
1467     }
1468     function start_table($sheettitle){
1469         $this->worksheet =& $this->workbook->add_worksheet($sheettitle);
1470         $this->rownum=0;
1471     }
1472     function output_headers($headers){
1473         $colnum = 0;
1474         foreach ($headers as $item) {
1475             $this->worksheet->write($this->rownum,$colnum,$item,$this->formatheaders);
1476             $colnum++;
1477         }
1478         $this->rownum++;
1479     }
1480     function add_data($row){
1481         $colnum = 0;
1482         foreach($row as $item){
1483             $this->worksheet->write($this->rownum,$colnum,$item,$this->formatnormal);
1484             $colnum++;
1485         }
1486         $this->rownum++;
1487         return true;
1488     }
1489     function add_seperator() {
1490         $this->rownum++;
1491         return true;
1492     }
1493
1494     function finish_table(){
1495     }
1496     function finish_document(){
1497         $this->workbook->close();
1498         exit;
1499     }
1500 }
1501
1502 /**
1503  * @package   moodlecore
1504  * @copyright 1999 onwards Martin Dougiamas  {@link http://moodle.com}
1505  * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1506  */
1507 class table_excel_export_format extends table_spreadsheet_export_format_parent{
1508     var $fileextension = 'xls';
1509
1510     function define_workbook(){
1511         global $CFG;
1512         require_once("$CFG->libdir/excellib.class.php");
1513         // Creating a workbook
1514         $this->workbook = new MoodleExcelWorkbook("-");
1515     }
1516
1517 }
1518
1519 /**
1520  * @package   moodlecore
1521  * @copyright 1999 onwards Martin Dougiamas  {@link http://moodle.com}
1522  * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1523  */
1524 class table_ods_export_format extends table_spreadsheet_export_format_parent{
1525     var $fileextension = 'ods';
1526     function define_workbook(){
1527         global $CFG;
1528         require_once("$CFG->libdir/odslib.class.php");
1529         // Creating a workbook
1530         $this->workbook = new MoodleODSWorkbook("-");
1531     }
1532 }
1533
1534 /**
1535  * @package   moodlecore
1536  * @copyright 1999 onwards Martin Dougiamas  {@link http://moodle.com}
1537  * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1538  */
1539 class table_text_export_format_parent extends table_default_export_format_parent {
1540     protected $seperator = "\t";
1541     protected $mimetype = 'text/tab-separated-values';
1542     protected $ext = '.txt';
1543
1544     public function start_document($filename) {
1545         $this->filename = $filename . $this->ext;
1546         header('Content-Type: ' . $this->mimetype . '; charset=UTF-8');
1547         header('Content-Disposition: attachment; filename="' . $this->filename . '"');
1548         header('Expires: 0');
1549         header('Cache-Control: must-revalidate,post-check=0,pre-check=0');
1550         header('Pragma: public');
1551         $this->documentstarted = true;
1552     }
1553
1554     public function start_table($sheettitle) {
1555         //nothing to do here
1556     }
1557
1558     public function output_headers($headers) {
1559         echo $this->format_row($headers);
1560     }
1561
1562     public function add_data($row) {
1563         echo $this->format_row($row);
1564         return true;
1565     }
1566
1567     public function finish_table() {
1568         echo "\n\n";
1569     }
1570
1571     public function finish_document() {
1572         exit;
1573     }
1574
1575     /**
1576      * Format a row of data.
1577      * @param array $data
1578      */
1579     protected function format_row($data) {
1580         $escapeddata = array();
1581         foreach ($data as $value) {
1582             $escapeddata[] = '"' . str_replace('"', '""', $value) . '"';
1583         }
1584         return implode($this->seperator, $escapeddata) . "\n";
1585     }
1586 }
1587
1588 /**
1589  * @package   moodlecore
1590  * @copyright 1999 onwards Martin Dougiamas  {@link http://moodle.com}
1591  * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1592  */
1593 class table_tsv_export_format extends table_text_export_format_parent{
1594     protected $seperator = "\t";
1595     protected $mimetype = 'text/tab-separated-values';
1596     protected $ext = '.txt';
1597 }
1598
1599 /**
1600  * @package   moodlecore
1601  * @copyright 1999 onwards Martin Dougiamas  {@link http://moodle.com}
1602  * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1603  */
1604 class table_csv_export_format extends table_text_export_format_parent{
1605     protected $seperator = ",";
1606     protected $mimetype = 'text/csv';
1607     protected $ext = '.csv';
1608 }
1609
1610 /**
1611  * @package   moodlecore
1612  * @copyright 1999 onwards Martin Dougiamas  {@link http://moodle.com}
1613  * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1614  */
1615 class table_xhtml_export_format extends table_default_export_format_parent{
1616     function start_document($filename){
1617         header("Content-Type: application/download\n");
1618         header("Content-Disposition: attachment; filename=\"$filename.html\"");
1619         header("Expires: 0");
1620         header("Cache-Control: must-revalidate,post-check=0,pre-check=0");
1621         header("Pragma: public");
1622         //html headers
1623         echo <<<EOF
1624 <?xml version="1.0" encoding="UTF-8"?>
1625 <!DOCTYPE html
1626   PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
1627   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
1628
1629 <html xmlns="http://www.w3.org/1999/xhtml"
1630   xml:lang="en" lang="en">
1631 <head>
1632 <style type="text/css">/*<![CDATA[*/
1633
1634 .flexible th {
1635 white-space:normal;
1636 }
1637 th.header, td.header, div.header {
1638 border-color:#DDDDDD;
1639 background-color:lightGrey;
1640 }
1641 .flexible th {
1642 white-space:nowrap;
1643 }
1644 th {
1645 font-weight:bold;
1646 }
1647
1648 .generaltable {
1649 border-style:solid;
1650 }
1651 .generalbox {
1652 border-style:solid;
1653 }
1654 body, table, td, th {
1655 font-family:Arial,Verdana,Helvetica,sans-serif;
1656 font-size:100%;
1657 }
1658 td {
1659     border-style:solid;
1660     border-width:1pt;
1661 }
1662 table {
1663     border-collapse:collapse;
1664     border-spacing:0pt;
1665     width:80%;
1666     margin:auto;
1667 }
1668
1669 h1, h2{
1670     text-align:center;
1671 }
1672 .bold {
1673 font-weight:bold;
1674 }
1675 .mdl-align {
1676     text-align:center;
1677 }
1678
1679
1680 /*]]>*/</style>
1681 <title>$filename</title>
1682 </head>
1683 <body>
1684 EOF;
1685         $this->documentstarted = true;
1686     }
1687     function start_table($sheettitle){
1688         $this->table->sortable(false);
1689         $this->table->collapsible(false);
1690         echo "<h2>{$sheettitle}</h2>";
1691         $this->table->start_html();
1692     }
1693
1694
1695     function output_headers($headers){
1696         $this->table->print_headers();
1697     }
1698     function add_data($row){
1699         $this->table->print_row($row);
1700         return true;
1701     }
1702     function add_seperator() {
1703         $this->table->print_row(NULL);
1704         return true;
1705     }
1706     function finish_table(){
1707         $this->table->finish_html();
1708     }
1709     function finish_document(){
1710         echo "</body>\n</html>";
1711         exit;
1712     }
1713     function format_text($text, $format=FORMAT_MOODLE, $options=NULL, $courseid=NULL){
1714         if (is_null($options)){
1715             $options = new stdClass;
1716         }
1717         //some sensible defaults
1718         if (!isset($options->para)){
1719             $options->para = false;
1720         }
1721         if (!isset($options->newlines)){
1722             $options->newlines = false;
1723         }
1724         if (!isset($options->smiley)) {
1725             $options->smiley = false;
1726         }
1727         if (!isset($options->filter)) {
1728             $options->filter = false;
1729         }
1730         return format_text($text, $format, $options);
1731     }
1732 }