report/stats/locallib.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  * Reports implementation
  19  *
  20  * @package    report
  21  * @subpackage stats
  22  * @copyright  1999 onwards Martin Dougiamas (http://dougiamas.com)
  23  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  24  */
  25
  26 defined('MOODLE_INTERNAL') || die;
  27
  28 require_once(__DIR__.'/lib.php');
  29 require_once($CFG->dirroot.'/lib/statslib.php');
  30
  31 function report_stats_mode_menu($course, $mode, $time, $url) {
  32     global $CFG, $OUTPUT;
  33     /*
  34     $reportoptions = stats_get_report_options($course->id, $mode);
  35     $timeoptions = report_stats_timeoptions($mode);
  36     if (empty($timeoptions)) {
  37         print_error('nostatstodisplay', '', $CFG->wwwroot.'/course/view.php?id='.$course->id);
  38     }
  39     */
  40
  41     $options = array();
  42     $options[STATS_MODE_GENERAL] = get_string('statsmodegeneral');
  43     $options[STATS_MODE_DETAILED] = get_string('statsmodedetailed');
  44     if (has_capability('report/stats:view', context_system::instance())) {
  45         $options[STATS_MODE_RANKED] = get_string('reports');
  46     }
  47     $popupurl = $url."?course=$course->id&time=$time";
  48     $select = new single_select(new moodle_url($popupurl), 'mode', $options, $mode, null);
  49     $select->set_label(get_string('reports'), array('class' => 'accesshide'));
  50     $select->formid = 'switchmode';
  51     return $OUTPUT->render($select);
  52 }
  53
  54 function report_stats_timeoptions($mode) {
  55     global $CFG, $DB;
  56
  57     if ($mode == STATS_MODE_DETAILED) {
  58         $earliestday = $DB->get_field_sql('SELECT MIN(timeend) FROM {stats_user_daily}');
  59         $earliestweek = $DB->get_field_sql('SELECT MIN(timeend) FROM {stats_user_weekly}');
  60         $earliestmonth = $DB->get_field_sql('SELECT MIN(timeend) FROM {stats_user_monthly}');
  61     } else {
  62         $earliestday = $DB->get_field_sql('SELECT MIN(timeend) FROM {stats_daily}');
  63         $earliestweek = $DB->get_field_sql('SELECT MIN(timeend) FROM {stats_weekly}');
  64         $earliestmonth = $DB->get_field_sql('SELECT MIN(timeend) FROM {stats_monthly}');
  65     }
  66
  67
  68     if (empty($earliestday)) $earliestday = time();
  69     if (empty($earliestweek)) $earliestweek = time();
  70     if (empty($earliestmonth)) $earliestmonth = time();
  71
  72     $now = stats_get_base_daily();
  73     $lastweekend = stats_get_base_weekly();
  74     $lastmonthend = stats_get_base_monthly();
  75
  76     return stats_get_time_options($now,$lastweekend,$lastmonthend,$earliestday,$earliestweek,$earliestmonth);
  77 }
  78
  79 function report_stats_report($course, $report, $mode, $user, $roleid, $time) {
  80     global $CFG, $DB, $OUTPUT;
  81
  82     if ($user) {
  83         $userid = $user->id;
  84     } else {
  85         $userid = 0;
  86     }
  87
  88     $fields = 'c.id,c.shortname,c.visible';
  89     $ccselect = ', ' . context_helper::get_preload_record_columns_sql('ctx');
  90     $ccjoin = "LEFT JOIN {context} ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel)";
  91     $sortstatement = 'ORDER BY c.shortname';
  92
  93     $sql = "SELECT $fields $ccselect FROM {course} c $ccjoin $sortstatement";
  94
  95     $params = array();
  96     $params['contextlevel'] = CONTEXT_COURSE;
  97
  98     $courses = $DB->get_recordset_sql($sql, $params);
  99
 100     $courseoptions = array();
 101
 102     foreach ($courses as $c) {
 103         context_helper::preload_from_record($c);
 104         $context = context_course::instance($c->id);
 105
 106         if (has_capability('report/stats:view', $context)) {
 107             if (isset($c->visible) && $c->visible <= 0) {
 108                 // For hidden courses, require visibility check.
 109                 if (!has_capability('moodle/course:viewhiddencourses', $context)) {
 110                     continue;
 111                 }
 112             }
 113             $courseoptions[$c->id] = format_string($c->shortname, true, array('context' => $context));
 114         }
 115     }
 116
 117     $courses->close();
 118
 119     $reportoptions = stats_get_report_options($course->id, $mode);
 120     $timeoptions = report_stats_timeoptions($mode);
 121     if (empty($timeoptions)) {
 122         print_error('nostatstodisplay', '', $CFG->wwwroot.'/course/view.php?id='.$course->id);
 123     }
 124
 125     $users = array();
 126     $table = new html_table();
 127     $table->width = 'auto';
 128
 129     if ($mode == STATS_MODE_DETAILED) {
 130         $param = stats_get_parameters($time, null, $course->id, $mode, $roleid); // We only care about the table and the time string (if we have time).
 131
 132         list($sort, $moreparams) = users_order_by_sql('u');
 133         $moreparams['courseid'] = $course->id;
 134         $fields = user_picture::fields('u', array('idnumber'));
 135         $sql = "SELECT DISTINCT $fields
 136                   FROM {stats_user_{$param->table}} s
 137                   JOIN {user} u ON u.id = s.userid
 138                  WHERE courseid = :courseid";
 139         if (!empty($param->stattype)) {
 140             $sql .= " AND stattype = :stattype";
 141             $moreparams['stattype'] = $param->stattype;
 142         }
 143         if (!empty($time)) {
 144             $sql .= " AND timeend >= :timeafter";
 145             $moreparams['timeafter'] = $param->timeafter;
 146         }
 147         $sql .= " ORDER BY {$sort}";
 148
 149         if (!$us = $DB->get_records_sql($sql, array_merge($param->params, $moreparams))) {
 150             print_error('nousers');
 151         }
 152         foreach ($us as $u) {
 153             $users[$u->id] = fullname($u, true);
 154         }
 155
 156         $table->align = array('left','left','left','left','left','left','left','left');
 157         $table->data[] = array(html_writer::label(get_string('course'), 'menucourse'), html_writer::select($courseoptions, 'course', $course->id, false),
 158                                html_writer::label(get_string('users'), 'menuuserid'), html_writer::select($users, 'userid', $userid, false),
 159                                html_writer::label(get_string('statsreporttype'), 'menureport'), html_writer::select($reportoptions, 'report', ($report == 5) ? $report.$roleid : $report, false),
 160                                html_writer::label(get_string('statstimeperiod'), 'menutime'), html_writer::select($timeoptions, 'time', $time, false),
 161                                '<input type="submit" class="btn btn-secondary" value="'.get_string('view').'" />');
 162     } else if ($mode == STATS_MODE_RANKED) {
 163         $table->align = array('left','left','left','left','left','left');
 164         $table->data[] = array(html_writer::label(get_string('statsreporttype'), 'menureport'), html_writer::select($reportoptions, 'report', ($report == 5) ? $report.$roleid : $report, false),
 165                                html_writer::label(get_string('statstimeperiod'), 'menutime'), html_writer::select($timeoptions, 'time', $time, false),
 166                                '<input type="submit" class="btn btn-secondary" value="'.get_string('view').'" />');
 167     } else if ($mode == STATS_MODE_GENERAL) {
 168         $table->align = array('left','left','left','left','left','left','left');
 169         $table->data[] = array(html_writer::label(get_string('course'), 'menucourse'), html_writer::select($courseoptions, 'course', $course->id, false),
 170                                html_writer::label(get_string('statsreporttype'), 'menureport'), html_writer::select($reportoptions, 'report', ($report == 5) ? $report.$roleid : $report, false),
 171                                html_writer::label(get_string('statstimeperiod'), 'menutime'), html_writer::select($timeoptions, 'time', $time, false),
 172                                '<input type="submit" class="btn btn-secondary" value="'.get_string('view').'" />');
 173     }
 174
 175     echo '<form action="index.php" method="post">'."\n"
 176         .'<div>'."\n"
 177         .'<input type="hidden" name="mode" value="'.$mode.'" />'."\n";
 178
 179     echo html_writer::table($table);
 180
 181     echo '</div>';
 182     echo '</form>';
 183
 184     // Display the report if:
 185     //  - A report has been selected.
 186     //  - A time frame has been provided
 187     //  - If the mode is not detailed OR a valid user has been selected.
 188     if (!empty($report) && !empty($time) && ($mode !== STATS_MODE_DETAILED || !empty($userid))) {
 189         if ($report == STATS_REPORT_LOGINS && $course->id != SITEID) {
 190             print_error('reportnotavailable');
 191         }
 192
 193         $param = stats_get_parameters($time, $report, $course->id, $mode, $roleid);
 194
 195         if ($mode == STATS_MODE_DETAILED) {
 196             $param->table = 'user_'.$param->table;
 197         }
 198
 199         if (!empty($param->sql)) {
 200             $sql = $param->sql;
 201         } else {
 202             //TODO: lceanup this ugly mess
 203             $sql = 'SELECT '.((empty($param->fieldscomplete)) ? 'id,roleid,timeend,' : '').$param->fields
 204                 .' FROM {stats_'.$param->table.'} WHERE '
 205                 .(($course->id == SITEID) ? '' : ' courseid = '.$course->id.' AND ')
 206                 .((!empty($userid)) ? ' userid = '.$userid.' AND ' : '')
 207                 .((!empty($roleid)) ? ' roleid = '.$roleid.' AND ' : '')
 208                 . ((!empty($param->stattype)) ? ' stattype = \''.$param->stattype.'\' AND ' : '')
 209                 .' timeend >= '.$param->timeafter
 210                 .' '.$param->extras
 211                 .' ORDER BY timeend DESC';
 212         }
 213
 214         $stats = $DB->get_records_sql($sql);
 215
 216         if (empty($stats)) {
 217             echo $OUTPUT->notification(get_string('statsnodata'));
 218
 219         } else {
 220
 221             $stats = stats_fix_zeros($stats,$param->timeafter,$param->table,(!empty($param->line2)));
 222
 223             $rolename = '';
 224             $userdisplayname = '';
 225             $coursecontext = context_course::instance($course->id);
 226
 227             if (!empty($roleid) && $role = $DB->get_record('role', ['id' => $roleid])) {
 228                 $rolename = ' ' . role_get_name($role, $coursecontext);
 229             }
 230             if (!empty($user)) {
 231                 $userdisplayname = ' ' . fullname($user, true);
 232             }
 233             echo $OUTPUT->heading(
 234                 format_string($course->shortname) .
 235                 ' - ' .
 236                 get_string('statsreport' . $report) .
 237                 $rolename .
 238                 $userdisplayname
 239             );
 240
 241             if ($mode == STATS_MODE_DETAILED) {
 242                 report_stats_print_chart($course->id, $report, $time, $mode, $userid);
 243             } else {
 244                 report_stats_print_chart($course->id, $report, $time, $mode, null, $roleid);
 245             }
 246
 247             $table = new html_table();
 248             $table->align = array('left','center','center','center');
 249             $param->table = str_replace('user_','',$param->table);
 250             switch ($param->table) {
 251                 case 'daily'  : $period = get_string('day'); break;
 252                 case 'weekly' : $period = get_string('week'); break;
 253                 case 'monthly': $period = get_string('month', 'form'); break;
 254                 default : $period = '';
 255             }
 256             $table->head = array(get_string('periodending','moodle',$period));
 257             if (empty($param->crosstab)) {
 258                 $table->head[] = $param->line1;
 259                 if (!empty($param->line2)) {
 260                     $table->head[] = $param->line2;
 261                 }
 262             }
 263             if (!file_exists($CFG->dirroot.'/report/log/index.php')) {
 264                 // bad luck, we can not link other report
 265             } else if (empty($param->crosstab)) {
 266                 foreach  ($stats as $stat) {
 267                     $a = array(userdate($stat->timeend - DAYSECS, get_string('strftimedate'), $CFG->timezone),
 268                             $stat->line1);
 269                     if (isset($stat->line2)) {
 270                         $a[] = $stat->line2;
 271                     }
 272                     if (empty($CFG->loglifetime) || ($stat->timeend-(60*60*24)) >= (time()-60*60*24*$CFG->loglifetime)) {
 273                         if (has_capability('report/log:view', context_course::instance($course->id))) {
 274                             $a[] = '<a href="'.$CFG->wwwroot.'/report/log/index.php?id='.
 275                                 $course->id.'&amp;chooselog=1&amp;showusers=1&amp;showcourses=1&amp;user='
 276                                 .$userid.'&amp;date='.usergetmidnight($stat->timeend-(60*60*24)).'">'
 277                                 .get_string('course').' ' .get_string('logs').'</a>&nbsp;';
 278                         } else {
 279                             $a[] = '';
 280                         }
 281                     }
 282                     $table->data[] = $a;
 283                 }
 284             } else {
 285                 $data = array();
 286                 $roles = array();
 287                 $times = array();
 288                 $missedlines = array();
 289                 $coursecontext = context_course::instance($course->id);
 290                 $rolenames = get_viewable_roles($coursecontext);
 291                 foreach ($stats as $stat) {
 292                     if (!empty($stat->zerofixed)) {
 293                         $missedlines[] = $stat->timeend;
 294                     }
 295                     $data[$stat->timeend][$stat->roleid] = $stat->line1;
 296                     if ($stat->roleid != 0) {
 297                         if (!array_key_exists($stat->roleid, $roles) && array_key_exists($stat->roleid, $rolenames)) {
 298                             $roles[$stat->roleid] = $rolenames[$stat->roleid];
 299                         }
 300                     } else {
 301                         if (!array_key_exists($stat->roleid,$roles)) {
 302                             $roles[$stat->roleid] = get_string('all');
 303                         }
 304                     }
 305                     if (!array_key_exists($stat->timeend,$times)) {
 306                         $times[$stat->timeend] = userdate($stat->timeend - DAYSECS, get_string('strftimedate'),
 307                                 $CFG->timezone);
 308                     }
 309                 }
 310
 311                 foreach ($data as $time => $rolesdata) {
 312                     if (in_array($time,$missedlines)) {
 313                         $rolesdata = array();
 314                         foreach ($roles as $roleid => $guff) {
 315                             $rolesdata[$roleid] = 0;
 316                         }
 317                     }
 318                     else {
 319                         foreach (array_keys($roles) as $r) {
 320                             if (!array_key_exists($r, $rolesdata)) {
 321                                 $rolesdata[$r] = 0;
 322                             }
 323                         }
 324                     }
 325                     krsort($rolesdata);
 326                     $row = array_merge(array($times[$time]),$rolesdata);
 327                     if (empty($CFG->loglifetime) || ($stat->timeend-(60*60*24)) >= (time()-60*60*24*$CFG->loglifetime)) {
 328                         if (has_capability('report/log:view', context_course::instance($course->id))) {
 329                             $row[] = '<a href="'.$CFG->wwwroot.'/report/log/index.php?id='
 330                                 .$course->id.'&amp;chooselog=1&amp;showusers=1&amp;showcourses=1&amp;user='.$userid
 331                                 .'&amp;date='.usergetmidnight($time-(60*60*24)).'">'
 332                                 .get_string('course').' ' .get_string('logs').'</a>&nbsp;';
 333                         } else {
 334                             $row[] = '';
 335                         }
 336                     }
 337                     $table->data[] = $row;
 338                 }
 339                 krsort($roles);
 340                 $table->head = array_merge($table->head,$roles);
 341             }
 342             $table->head[] = get_string('logs');
 343             //if (!empty($lastrecord)) {
 344                 //$lastrecord[] = $lastlink;
 345                 //$table->data[] = $lastrecord;
 346             //}
 347             echo html_writer::table($table);
 348         }
 349     }
 350 }
 351
 352 /**
 353  * Fetch statistics data and generate a line chart.
 354  *
 355  * The statistic chart can be view, posts separated by roles and dates.
 356  *
 357  * @param int $courseid course id.
 358  * @param int $report the report type constant eg. STATS_REPORT_LOGINS as defined on statslib.
 359  * @param int $time timestamp of the selected time period.
 360  * @param int $mode the report mode, eg. STATS_MODE_DETAILED as defined on statslib.
 361  * @param int $userid selected user id.
 362  * @param int $roleid selected role id.
 363  */
 364 function report_stats_print_chart($courseid, $report, $time, $mode, $userid = 0, $roleid = 0) {
 365     global $DB, $CFG, $OUTPUT;
 366
 367     $course = $DB->get_record("course", array("id" => $courseid), '*', MUST_EXIST);
 368     $coursecontext = context_course::instance($course->id);
 369
 370     stats_check_uptodate($course->id);
 371
 372     $param = stats_get_parameters($time, $report, $course->id, $mode, $roleid);
 373
 374     if (!empty($userid)) {
 375         $param->table = 'user_' . $param->table;
 376     }
 377
 378     // TODO: cleanup this ugly mess.
 379     $sql = 'SELECT '.((empty($param->fieldscomplete)) ? 'id,roleid,timeend,' : '').$param->fields
 380         .' FROM {stats_'.$param->table.'} WHERE '
 381         .(($course->id == SITEID) ? '' : ' courseid = '.$course->id.' AND ')
 382         .((!empty($userid)) ? ' userid = '.$userid.' AND ' : '')
 383         .((!empty($roleid)) ? ' roleid = '.$roleid.' AND ' : '')
 384         . ((!empty($param->stattype)) ? ' stattype = \''.$param->stattype.'\' AND ' : '')
 385         .' timeend >= '.$param->timeafter
 386         .' '.$param->extras
 387         .' ORDER BY timeend DESC';
 388     $stats = $DB->get_records_sql($sql, $param->params);
 389     $stats = stats_fix_zeros($stats, $param->timeafter, $param->table, (!empty($param->line2)),
 390             (!empty($param->line3)));
 391     $stats = array_reverse($stats);
 392
 393     $chart = new \core\chart_line();
 394     if (empty($param->crosstab)) {
 395         $data = [];
 396         $times = [];
 397         foreach ($stats as $stat) {
 398             // Build the array of formatted times indexed by timestamp used as labels.
 399             if (!array_key_exists($stat->timeend, $times)) {
 400                 $times[$stat->timeend] = userdate($stat->timeend - DAYSECS, get_string('strftimedate'), $CFG->timezone);
 401
 402                 // Just add the data if the time hasn't been added yet.
 403                 // The number of lines of data must match the number of labels.
 404                 $data['line1'][] = $stat->line1;
 405                 if (isset($stat->line2)) {
 406                     $data['line2'][] = $stat->line2;
 407                 }
 408                 if (isset($stat->line3)) {
 409                     $data['line3'][] = $stat->line3;
 410                 }
 411             }
 412         }
 413         foreach ($data as $line => $serie) {
 414             $series = new \core\chart_series($param->{$line}, array_values($serie));
 415             $chart->add_series($series);
 416         }
 417     } else {
 418         $data = array();
 419         $times = array();
 420         $roles = array();
 421         $missedlines = array();
 422         $rolenames = get_viewable_roles($coursecontext);
 423
 424         foreach ($stats as $stat) {
 425             $data[$stat->roleid][$stat->timeend] = $stat->line1;
 426             if (!empty($stat->zerofixed)) {
 427                 $missedlines[] = $stat->timeend;
 428             }
 429             if ($stat->roleid != 0) {
 430                 if (!array_key_exists($stat->roleid, $roles) && array_key_exists($stat->roleid, $rolenames)) {
 431                     $roles[$stat->roleid] = $rolenames[$stat->roleid];
 432                 }
 433             } else {
 434                 if (!array_key_exists($stat->roleid, $roles)) {
 435                     $roles[$stat->roleid] = get_string('all');
 436                 }
 437             }
 438
 439             // Build the array of formatted times indexed by timestamp used as labels.
 440             if (!array_key_exists($stat->timeend, $times)) {
 441                 $times[$stat->timeend] = userdate($stat->timeend - DAYSECS, get_string('strftimedate'), $CFG->timezone);
 442             }
 443         }
 444         // Fill empty days with zero to avoid chart errors.
 445         foreach (array_keys($times) as $t) {
 446             foreach ($data as $roleid => $stuff) {
 447                 if (!array_key_exists($t, $stuff)) {
 448                     $data[$roleid][$t] = 0;
 449                 }
 450             }
 451         }
 452         krsort($roles);
 453         foreach ($roles as $roleid => $rolename) {
 454             ksort($data[$roleid]);
 455             $series = new \core\chart_series($rolename, array_values($data[$roleid]));
 456             $chart->add_series($series);
 457         }
 458     }
 459     $chart->set_labels(array_values($times));
 460     echo $OUTPUT->render_chart($chart, false);
 461 }