lib/statslib.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 stats
  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 /** THESE CONSTANTS ARE USED FOR THE REPORTING PAGE. */
  28
  29 define('STATS_REPORT_LOGINS',1); // double impose logins and unique logins on a line graph. site course only.
  30 define('STATS_REPORT_READS',2); // double impose student reads and teacher reads on a line graph.
  31 define('STATS_REPORT_WRITES',3); // double impose student writes and teacher writes on a line graph.
  32 define('STATS_REPORT_ACTIVITY',4); // 2+3 added up, teacher vs student.
  33 define('STATS_REPORT_ACTIVITYBYROLE',5); // all activity, reads vs writes, selected by role.
  34
  35 // user level stats reports.
  36 define('STATS_REPORT_USER_ACTIVITY',7);
  37 define('STATS_REPORT_USER_ALLACTIVITY',8);
  38 define('STATS_REPORT_USER_LOGINS',9);
  39 define('STATS_REPORT_USER_VIEW',10);  // this is the report you see on the user profile.
  40
  41 // admin only ranking stats reports
  42 define('STATS_REPORT_ACTIVE_COURSES',11);
  43 define('STATS_REPORT_ACTIVE_COURSES_WEIGHTED',12);
  44 define('STATS_REPORT_PARTICIPATORY_COURSES',13);
  45 define('STATS_REPORT_PARTICIPATORY_COURSES_RW',14);
  46
  47 // start after 0 = show dailies.
  48 define('STATS_TIME_LASTWEEK',1);
  49 define('STATS_TIME_LAST2WEEKS',2);
  50 define('STATS_TIME_LAST3WEEKS',3);
  51 define('STATS_TIME_LAST4WEEKS',4);
  52
  53 // start after 10 = show weeklies
  54 define('STATS_TIME_LAST2MONTHS',12);
  55
  56 define('STATS_TIME_LAST3MONTHS',13);
  57 define('STATS_TIME_LAST4MONTHS',14);
  58 define('STATS_TIME_LAST5MONTHS',15);
  59 define('STATS_TIME_LAST6MONTHS',16);
  60
  61 // start after 20 = show monthlies
  62 define('STATS_TIME_LAST7MONTHS',27);
  63 define('STATS_TIME_LAST8MONTHS',28);
  64 define('STATS_TIME_LAST9MONTHS',29);
  65 define('STATS_TIME_LAST10MONTHS',30);
  66 define('STATS_TIME_LAST11MONTHS',31);
  67 define('STATS_TIME_LASTYEAR',32);
  68
  69 // different modes for what reports to offer
  70 define('STATS_MODE_GENERAL',1);
  71 define('STATS_MODE_DETAILED',2);
  72 define('STATS_MODE_RANKED',3); // admins only - ranks courses
  73
  74 // Output string when nodebug is on
  75 define('STATS_PLACEHOLDER_OUTPUT', '.');
  76
  77 /**
  78  * Print daily cron progress
  79  * @param string $ident
  80  */
  81 function stats_progress($ident) {
  82     static $start = 0;
  83     static $init  = 0;
  84
  85     if ($ident == 'init') {
  86         $init = $start = microtime(true);
  87         return;
  88     }
  89
  90     $elapsed = round(microtime(true) - $start);
  91     $start   = microtime(true);
  92
  93     if (debugging('', DEBUG_ALL)) {
  94         mtrace("$ident:$elapsed ", '');
  95     } else {
  96         mtrace(STATS_PLACEHOLDER_OUTPUT, '');
  97     }
  98 }
  99
 100 /**
 101  * Execute individual daily statistics queries
 102  *
 103  * @param string $sql The query to run
 104  * @return boolean success
 105  */
 106 function stats_run_query($sql, $parameters = array()) {
 107     global $DB;
 108
 109     try {
 110         $DB->execute($sql, $parameters);
 111     } catch (dml_exception $e) {
 112
 113        if (debugging('', DEBUG_ALL)) {
 114            mtrace($e->getMessage());
 115        }
 116        return false;
 117     }
 118     return true;
 119 }
 120
 121 /**
 122  * Execute daily statistics gathering
 123  *
 124  * @param int $maxdays maximum number of days to be processed
 125  * @return boolean success
 126  */
 127 function stats_cron_daily($maxdays=1) {
 128     global $CFG, $DB;
 129     require_once($CFG->libdir.'/adminlib.php');
 130
 131     $now = time();
 132
 133     $fpcontext = context_course::instance(SITEID, MUST_EXIST);
 134
 135     // read last execution date from db
 136     if (!$timestart = get_config(NULL, 'statslastdaily')) {
 137         $timestart = stats_get_base_daily(stats_get_start_from('daily'));
 138         set_config('statslastdaily', $timestart);
 139     }
 140
 141     // calculate scheduled time
 142     $scheduledtime = stats_get_base_daily() + $CFG->statsruntimestarthour*60*60 + $CFG->statsruntimestartminute*60;
 143
 144     // Note: This will work fine for sites running cron each 4 hours or less (hopefully, 99.99% of sites). MDL-16709
 145     // check to make sure we're due to run, at least 20 hours after last run
 146     if (isset($CFG->statslastexecution) && ((time() - 20*60*60) < $CFG->statslastexecution)) {
 147         mtrace("...preventing stats to run, last execution was less than 20 hours ago.");
 148         return false;
 149     // also check that we are a max of 4 hours after scheduled time, stats won't run after that
 150     } else if (time() > $scheduledtime + 4*60*60) {
 151         mtrace("...preventing stats to run, more than 4 hours since scheduled time.");
 152         return false;
 153     } else {
 154         set_config('statslastexecution', time()); /// Grab this execution as last one
 155     }
 156
 157     $nextmidnight = stats_get_next_day_start($timestart);
 158
 159     // are there any days that need to be processed?
 160     if ($now < $nextmidnight) {
 161         return true; // everything ok and up-to-date
 162     }
 163
 164
 165     $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
 166
 167     if (!set_cron_lock('statsrunning', $now + $timeout)) {
 168         return false;
 169     }
 170
 171     // first delete entries that should not be there yet
 172     $DB->delete_records_select('stats_daily',      "timeend > $timestart");
 173     $DB->delete_records_select('stats_user_daily', "timeend > $timestart");
 174
 175     // Read in a few things we'll use later
 176     $viewactions = stats_get_action_names('view');
 177     $postactions = stats_get_action_names('post');
 178
 179     $guest           = (int)$CFG->siteguest;
 180     $guestrole       = (int)$CFG->guestroleid;
 181     $defaultfproleid = (int)$CFG->defaultfrontpageroleid;
 182
 183     mtrace("Running daily statistics gathering, starting at $timestart:");
 184     cron_trace_time_and_memory();
 185
 186     $days  = 0;
 187     $total = 0;
 188     $failed  = false; // failed stats flag
 189     $timeout = false;
 190
 191     if (!stats_temp_table_create()) {
 192         $days = 1;
 193         $failed = true;
 194     }
 195     mtrace('Temporary tables created');
 196
 197     if(!stats_temp_table_setup()) {
 198         $days = 1;
 199         $failed = true;
 200     }
 201     mtrace('Enrolments calculated');
 202
 203     $totalactiveusers = $DB->count_records('user', array('deleted' => '0'));
 204
 205     while (!$failed && ($now > $nextmidnight)) {
 206         if ($days >= $maxdays) {
 207             $timeout = true;
 208             break;
 209         }
 210
 211         $days++;
 212         core_php_time_limit::raise($timeout - 200);
 213
 214         if ($days > 1) {
 215             // move the lock
 216             set_cron_lock('statsrunning', time() + $timeout, true);
 217         }
 218
 219         $daystart = time();
 220
 221         stats_progress('init');
 222
 223         if (!stats_temp_table_fill($timestart, $nextmidnight)) {
 224             $failed = true;
 225             break;
 226         }
 227
 228         // Find out if any logs available for this day
 229         $sql = "SELECT 'x' FROM {temp_log1} l";
 230         $logspresent = $DB->get_records_sql($sql, null, 0, 1);
 231
 232         if ($logspresent) {
 233             // Insert blank record to force Query 10 to generate additional row when no logs for
 234             // the site with userid 0 exist.  Added for backwards compatibility.
 235             $DB->insert_record('temp_log1', array('userid' => 0, 'course' => SITEID, 'action' => ''));
 236         }
 237
 238         // Calculate the number of active users today
 239         $sql = 'SELECT COUNT(DISTINCT u.id)
 240                   FROM {user} u
 241                   JOIN {temp_log1} l ON l.userid = u.id
 242                  WHERE u.deleted = 0';
 243         $dailyactiveusers = $DB->count_records_sql($sql);
 244
 245         stats_progress('0');
 246
 247         // Process login info first
 248         // Note: PostgreSQL doesn't like aliases in HAVING clauses
 249         $sql = "INSERT INTO {temp_stats_user_daily}
 250                             (stattype, timeend, courseid, userid, statsreads)
 251
 252                 SELECT 'logins', $nextmidnight AS timeend, ".SITEID." AS courseid,
 253                         userid, COUNT(id) AS statsreads
 254                   FROM {temp_log1} l
 255                  WHERE action = 'login'
 256               GROUP BY userid
 257                 HAVING COUNT(id) > 0";
 258
 259         if ($logspresent && !stats_run_query($sql)) {
 260             $failed = true;
 261             break;
 262         }
 263         $DB->update_temp_table_stats();
 264
 265         stats_progress('1');
 266
 267         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 268
 269                 SELECT 'logins' AS stattype, $nextmidnight AS timeend, ".SITEID." AS courseid, 0,
 270                        COALESCE(SUM(statsreads), 0) as stat1, COUNT('x') as stat2
 271                   FROM {temp_stats_user_daily}
 272                  WHERE stattype = 'logins' AND timeend = $nextmidnight";
 273
 274         if ($logspresent && !stats_run_query($sql)) {
 275             $failed = true;
 276             break;
 277         }
 278         stats_progress('2');
 279
 280
 281         // Enrolments and active enrolled users
 282         //
 283         // Unfortunately, we do not know how many users were registered
 284         // at given times in history :-(
 285         // - stat1: enrolled users
 286         // - stat2: enrolled users active in this period
 287         // - SITEID is special case here, because it's all about default enrolment
 288         //   in that case, we'll count non-deleted users.
 289         //
 290
 291         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 292
 293                 SELECT 'enrolments' as stattype, $nextmidnight as timeend, courseid, roleid,
 294                         COUNT(DISTINCT userid) as stat1, 0 as stat2
 295                   FROM {temp_enroled}
 296               GROUP BY courseid, roleid";
 297
 298         if (!stats_run_query($sql)) {
 299             $failed = true;
 300             break;
 301         }
 302         stats_progress('3');
 303
 304         // Set stat2 to the number distinct users with role assignments in the course that were active
 305         // using table alias in UPDATE does not work in pg < 8.2
 306         $sql = "UPDATE {temp_stats_daily}
 307                    SET stat2 = (
 308
 309                     SELECT COUNT(DISTINCT userid)
 310                       FROM {temp_enroled} te
 311                      WHERE roleid = {temp_stats_daily}.roleid
 312                        AND courseid = {temp_stats_daily}.courseid
 313                        AND EXISTS (
 314
 315                         SELECT 'x'
 316                           FROM {temp_log1} l
 317                          WHERE l.course = {temp_stats_daily}.courseid
 318                            AND l.userid = te.userid
 319                                   )
 320                                )
 321                  WHERE {temp_stats_daily}.stattype = 'enrolments'
 322                    AND {temp_stats_daily}.timeend = $nextmidnight
 323                    AND {temp_stats_daily}.courseid IN (
 324
 325                     SELECT DISTINCT course FROM {temp_log2})";
 326
 327         if ($logspresent && !stats_run_query($sql, array('courselevel'=>CONTEXT_COURSE))) {
 328             $failed = true;
 329             break;
 330         }
 331         stats_progress('4');
 332
 333         // Now get course total enrolments (roleid==0) - except frontpage
 334         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 335
 336                 SELECT 'enrolments', $nextmidnight AS timeend, te.courseid AS courseid, 0 AS roleid,
 337                        COUNT(DISTINCT userid) AS stat1, 0 AS stat2
 338                   FROM {temp_enroled} te
 339               GROUP BY courseid
 340                 HAVING COUNT(DISTINCT userid) > 0";
 341
 342         if ($logspresent && !stats_run_query($sql)) {
 343             $failed = true;
 344             break;
 345         }
 346         stats_progress('5');
 347
 348         // Set stat 2 to the number of enrolled users who were active in the course
 349         $sql = "UPDATE {temp_stats_daily}
 350                    SET stat2 = (
 351
 352                     SELECT COUNT(DISTINCT te.userid)
 353                       FROM {temp_enroled} te
 354                      WHERE te.courseid = {temp_stats_daily}.courseid
 355                        AND EXISTS (
 356
 357                         SELECT 'x'
 358                           FROM {temp_log1} l
 359                          WHERE l.course = {temp_stats_daily}.courseid
 360                            AND l.userid = te.userid
 361                                   )
 362                                )
 363
 364                  WHERE {temp_stats_daily}.stattype = 'enrolments'
 365                    AND {temp_stats_daily}.timeend = $nextmidnight
 366                    AND {temp_stats_daily}.roleid = 0
 367                    AND {temp_stats_daily}.courseid IN (
 368
 369                     SELECT l.course
 370                       FROM {temp_log2} l
 371                      WHERE l.course <> ".SITEID.")";
 372
 373         if ($logspresent && !stats_run_query($sql, array())) {
 374             $failed = true;
 375             break;
 376         }
 377         stats_progress('6');
 378
 379         // Frontpage(==site) enrolments total
 380         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 381
 382                 SELECT 'enrolments', $nextmidnight, ".SITEID.", 0, $totalactiveusers AS stat1,
 383                        $dailyactiveusers AS stat2" .
 384                 $DB->sql_null_from_clause();
 385
 386         if ($logspresent && !stats_run_query($sql)) {
 387             $failed = true;
 388             break;
 389         }
 390         // The steps up until this point, all add to {temp_stats_daily} and don't use new tables.
 391         // There is no point updating statistics as they won't be used until the DELETE below.
 392         $DB->update_temp_table_stats();
 393
 394         stats_progress('7');
 395
 396         // Default frontpage role enrolments are all site users (not deleted)
 397         if ($defaultfproleid) {
 398             // first remove default frontpage role counts if created by previous query
 399             $sql = "DELETE
 400                       FROM {temp_stats_daily}
 401                      WHERE stattype = 'enrolments'
 402                        AND courseid = ".SITEID."
 403                        AND roleid = $defaultfproleid
 404                        AND timeend = $nextmidnight";
 405
 406             if ($logspresent && !stats_run_query($sql)) {
 407                 $failed = true;
 408                 break;
 409             }
 410             stats_progress('8');
 411
 412             $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 413
 414                     SELECT 'enrolments', $nextmidnight, ".SITEID.", $defaultfproleid,
 415                            $totalactiveusers AS stat1, $dailyactiveusers AS stat2" .
 416                     $DB->sql_null_from_clause();
 417
 418             if ($logspresent && !stats_run_query($sql)) {
 419                 $failed = true;
 420                 break;
 421             }
 422             stats_progress('9');
 423
 424         } else {
 425             stats_progress('x');
 426             stats_progress('x');
 427         }
 428
 429
 430         /// individual user stats (including not-logged-in) in each course, this is slow - reuse this data if possible
 431         list($viewactionssql, $params1) = $DB->get_in_or_equal($viewactions, SQL_PARAMS_NAMED, 'view');
 432         list($postactionssql, $params2) = $DB->get_in_or_equal($postactions, SQL_PARAMS_NAMED, 'post');
 433         $sql = "INSERT INTO {temp_stats_user_daily} (stattype, timeend, courseid, userid, statsreads, statswrites)
 434
 435                 SELECT 'activity' AS stattype, $nextmidnight AS timeend, course AS courseid, userid,
 436                        SUM(CASE WHEN action $viewactionssql THEN 1 ELSE 0 END) AS statsreads,
 437                        SUM(CASE WHEN action $postactionssql THEN 1 ELSE 0 END) AS statswrites
 438                   FROM {temp_log1} l
 439               GROUP BY userid, course";
 440
 441         if ($logspresent && !stats_run_query($sql, array_merge($params1, $params2))) {
 442             $failed = true;
 443             break;
 444         }
 445         stats_progress('10');
 446
 447
 448         /// How many view/post actions in each course total
 449         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 450
 451                 SELECT 'activity' AS stattype, $nextmidnight AS timeend, c.id AS courseid, 0,
 452                        SUM(CASE WHEN l.action $viewactionssql THEN 1 ELSE 0 END) AS stat1,
 453                        SUM(CASE WHEN l.action $postactionssql THEN 1 ELSE 0 END) AS stat2
 454                   FROM {course} c, {temp_log1} l
 455                  WHERE l.course = c.id
 456               GROUP BY c.id";
 457
 458         if ($logspresent && !stats_run_query($sql, array_merge($params1, $params2))) {
 459             $failed = true;
 460             break;
 461         }
 462         stats_progress('11');
 463
 464
 465         /// how many view actions for each course+role - excluding guests and frontpage
 466
 467         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 468
 469                 SELECT 'activity', $nextmidnight AS timeend, courseid, roleid, SUM(statsreads), SUM(statswrites)
 470                   FROM (
 471
 472                     SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
 473                       FROM {temp_stats_user_daily} sud, (
 474
 475                         SELECT DISTINCT te.userid, te.roleid, te.courseid
 476                           FROM {temp_enroled} te
 477                          WHERE te.roleid <> $guestrole
 478                            AND te.userid <> $guest
 479                                                         ) pl
 480
 481                      WHERE sud.userid = pl.userid
 482                        AND sud.courseid = pl.courseid
 483                        AND sud.timeend = $nextmidnight
 484                        AND sud.stattype='activity'
 485                        ) inline_view
 486
 487               GROUP BY courseid, roleid
 488                 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
 489
 490         if ($logspresent && !stats_run_query($sql, array('courselevel'=>CONTEXT_COURSE))) {
 491             $failed = true;
 492             break;
 493         }
 494         stats_progress('12');
 495
 496         /// how many view actions from guests only in each course - excluding frontpage
 497         /// normal users may enter course with temporary guest access too
 498
 499         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 500
 501                 SELECT 'activity', $nextmidnight AS timeend, courseid, $guestrole AS roleid,
 502                        SUM(statsreads), SUM(statswrites)
 503                   FROM (
 504
 505                     SELECT sud.courseid, sud.statsreads, sud.statswrites
 506                       FROM {temp_stats_user_daily} sud
 507                      WHERE sud.timeend = $nextmidnight
 508                        AND sud.courseid <> ".SITEID."
 509                        AND sud.stattype='activity'
 510                        AND (sud.userid = $guest OR sud.userid NOT IN (
 511
 512                         SELECT userid
 513                           FROM {temp_enroled} te
 514                          WHERE te.courseid = sud.courseid
 515                                                                      ))
 516                        ) inline_view
 517
 518               GROUP BY courseid
 519                 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
 520
 521         if ($logspresent && !stats_run_query($sql, array())) {
 522             $failed = true;
 523             break;
 524         }
 525         stats_progress('13');
 526
 527
 528         /// How many view actions for each role on frontpage - excluding guests, not-logged-in and default frontpage role
 529         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 530
 531                 SELECT 'activity', $nextmidnight AS timeend, courseid, roleid,
 532                        SUM(statsreads), SUM(statswrites)
 533                   FROM (
 534                     SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
 535                       FROM {temp_stats_user_daily} sud, (
 536
 537                         SELECT DISTINCT ra.userid, ra.roleid, c.instanceid AS courseid
 538                           FROM {role_assignments} ra
 539                           JOIN {context} c ON c.id = ra.contextid
 540                          WHERE ra.contextid = :fpcontext
 541                            AND ra.roleid <> $defaultfproleid
 542                            AND ra.roleid <> $guestrole
 543                            AND ra.userid <> $guest
 544                                                    ) pl
 545                      WHERE sud.userid = pl.userid
 546                        AND sud.courseid = pl.courseid
 547                        AND sud.timeend = $nextmidnight
 548                        AND sud.stattype='activity'
 549                        ) inline_view
 550
 551               GROUP BY courseid, roleid
 552                 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
 553
 554         if ($logspresent && !stats_run_query($sql, array('fpcontext'=>$fpcontext->id))) {
 555             $failed = true;
 556             break;
 557         }
 558         stats_progress('14');
 559
 560
 561         // How many view actions for default frontpage role on frontpage only
 562         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 563
 564                 SELECT 'activity', timeend, courseid, $defaultfproleid AS roleid,
 565                        SUM(statsreads), SUM(statswrites)
 566                   FROM (
 567                     SELECT sud.timeend AS timeend, sud.courseid, sud.statsreads, sud.statswrites
 568                       FROM {temp_stats_user_daily} sud
 569                      WHERE sud.timeend = :nextm
 570                        AND sud.courseid = :siteid
 571                        AND sud.stattype='activity'
 572                        AND sud.userid <> $guest
 573                        AND sud.userid <> 0
 574                        AND sud.userid NOT IN (
 575
 576                         SELECT ra.userid
 577                           FROM {role_assignments} ra
 578                          WHERE ra.roleid <> $guestrole
 579                            AND ra.roleid <> $defaultfproleid
 580                            AND ra.contextid = :fpcontext)
 581                        ) inline_view
 582
 583               GROUP BY timeend, courseid
 584                 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
 585
 586         if ($logspresent && !stats_run_query($sql, array('fpcontext'=>$fpcontext->id, 'siteid'=>SITEID, 'nextm'=>$nextmidnight))) {
 587             $failed = true;
 588             break;
 589         }
 590         $DB->update_temp_table_stats();
 591         stats_progress('15');
 592
 593         // How many view actions for guests or not-logged-in on frontpage
 594         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 595
 596                 SELECT stattype, timeend, courseid, $guestrole AS roleid,
 597                        SUM(statsreads) AS stat1, SUM(statswrites) AS stat2
 598                   FROM (
 599                     SELECT sud.stattype, sud.timeend, sud.courseid,
 600                            sud.statsreads, sud.statswrites
 601                       FROM {temp_stats_user_daily} sud
 602                      WHERE (sud.userid = $guest OR sud.userid = 0)
 603                        AND sud.timeend = $nextmidnight
 604                        AND sud.courseid = ".SITEID."
 605                        AND sud.stattype='activity'
 606                        ) inline_view
 607                  GROUP BY stattype, timeend, courseid
 608                  HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
 609
 610         if ($logspresent && !stats_run_query($sql)) {
 611             $failed = true;
 612             break;
 613         }
 614         stats_progress('16');
 615
 616         stats_temp_table_clean();
 617
 618         stats_progress('out');
 619
 620         // remember processed days
 621         set_config('statslastdaily', $nextmidnight);
 622         $elapsed = time()-$daystart;
 623         mtrace("  finished until $nextmidnight: ".userdate($nextmidnight)." (in $elapsed s)");
 624         $total += $elapsed;
 625
 626         $timestart    = $nextmidnight;
 627         $nextmidnight = stats_get_next_day_start($nextmidnight);
 628     }
 629
 630     stats_temp_table_drop();
 631
 632     set_cron_lock('statsrunning', null);
 633
 634     if ($failed) {
 635         $days--;
 636         mtrace("...error occurred, completed $days days of statistics in {$total} s.");
 637         return false;
 638
 639     } else if ($timeout) {
 640         mtrace("...stopping early, reached maximum number of $maxdays days ({$total} s) - will continue next time.");
 641         return false;
 642
 643     } else {
 644         mtrace("...completed $days days of statistics in {$total} s.");
 645         return true;
 646     }
 647 }
 648
 649
 650 /**
 651  * Execute weekly statistics gathering
 652  * @return boolean success
 653  */
 654 function stats_cron_weekly() {
 655     global $CFG, $DB;
 656     require_once($CFG->libdir.'/adminlib.php');
 657
 658     $now = time();
 659
 660     // read last execution date from db
 661     if (!$timestart = get_config(NULL, 'statslastweekly')) {
 662         $timestart = stats_get_base_daily(stats_get_start_from('weekly'));
 663         set_config('statslastweekly', $timestart);
 664     }
 665
 666     $nextstartweek = stats_get_next_week_start($timestart);
 667
 668     // are there any weeks that need to be processed?
 669     if ($now < $nextstartweek) {
 670         return true; // everything ok and up-to-date
 671     }
 672
 673     $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
 674
 675     if (!set_cron_lock('statsrunning', $now + $timeout)) {
 676         return false;
 677     }
 678
 679     // fisrt delete entries that should not be there yet
 680     $DB->delete_records_select('stats_weekly',      "timeend > $timestart");
 681     $DB->delete_records_select('stats_user_weekly', "timeend > $timestart");
 682
 683     mtrace("Running weekly statistics gathering, starting at $timestart:");
 684     cron_trace_time_and_memory();
 685
 686     $weeks = 0;
 687     while ($now > $nextstartweek) {
 688         core_php_time_limit::raise($timeout - 200);
 689         $weeks++;
 690
 691         if ($weeks > 1) {
 692             // move the lock
 693             set_cron_lock('statsrunning', time() + $timeout, true);
 694         }
 695
 696         $stattimesql = "timeend > $timestart AND timeend <= $nextstartweek";
 697
 698         $weekstart = time();
 699         stats_progress('init');
 700
 701     /// process login info first
 702         $sql = "INSERT INTO {stats_user_weekly} (stattype, timeend, courseid, userid, statsreads)
 703
 704                 SELECT 'logins', timeend, courseid, userid, SUM(statsreads)
 705                   FROM (
 706                            SELECT $nextstartweek AS timeend, courseid, userid, statsreads
 707                              FROM {stats_user_daily} sd
 708                             WHERE stattype = 'logins' AND $stattimesql
 709                        ) inline_view
 710               GROUP BY timeend, courseid, userid
 711                 HAVING SUM(statsreads) > 0";
 712
 713         $DB->execute($sql);
 714
 715         stats_progress('1');
 716
 717         $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
 718
 719                 SELECT 'logins' AS stattype, $nextstartweek AS timeend, ".SITEID." as courseid, 0,
 720                        COALESCE((SELECT SUM(statsreads)
 721                                    FROM {stats_user_weekly} s1
 722                                   WHERE s1.stattype = 'logins' AND timeend = $nextstartweek), 0) AS nstat1,
 723                        (SELECT COUNT('x')
 724                           FROM {stats_user_weekly} s2
 725                          WHERE s2.stattype = 'logins' AND timeend = $nextstartweek) AS nstat2" .
 726                 $DB->sql_null_from_clause();
 727
 728         $DB->execute($sql);
 729
 730         stats_progress('2');
 731
 732     /// now enrolments averages
 733         $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
 734
 735                 SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . "
 736                   FROM (
 737                            SELECT $nextstartweek AS ntimeend, courseid, roleid, stat1, stat2
 738                              FROM {stats_daily} sd
 739                             WHERE stattype = 'enrolments' AND $stattimesql
 740                        ) inline_view
 741               GROUP BY ntimeend, courseid, roleid";
 742
 743         $DB->execute($sql);
 744
 745         stats_progress('3');
 746
 747     /// activity read/write averages
 748         $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
 749
 750                 SELECT 'activity', ntimeend, courseid, roleid, SUM(stat1), SUM(stat2)
 751                   FROM (
 752                            SELECT $nextstartweek AS ntimeend, courseid, roleid, stat1, stat2
 753                              FROM {stats_daily}
 754                             WHERE stattype = 'activity' AND $stattimesql
 755                        ) inline_view
 756               GROUP BY ntimeend, courseid, roleid";
 757
 758         $DB->execute($sql);
 759
 760         stats_progress('4');
 761
 762     /// user read/write averages
 763         $sql = "INSERT INTO {stats_user_weekly} (stattype, timeend, courseid, userid, statsreads, statswrites)
 764
 765                 SELECT 'activity', ntimeend, courseid, userid, SUM(statsreads), SUM(statswrites)
 766                   FROM (
 767                            SELECT $nextstartweek AS ntimeend, courseid, userid, statsreads, statswrites
 768                              FROM {stats_user_daily}
 769                             WHERE stattype = 'activity' AND $stattimesql
 770                        ) inline_view
 771               GROUP BY ntimeend, courseid, userid";
 772
 773         $DB->execute($sql);
 774
 775         stats_progress('5');
 776
 777         set_config('statslastweekly', $nextstartweek);
 778         $elapsed = time()-$weekstart;
 779         mtrace(" finished until $nextstartweek: ".userdate($nextstartweek) ." (in $elapsed s)");
 780
 781         $timestart     = $nextstartweek;
 782         $nextstartweek = stats_get_next_week_start($nextstartweek);
 783     }
 784
 785     set_cron_lock('statsrunning', null);
 786     mtrace("...completed $weeks weeks of statistics.");
 787     return true;
 788 }
 789
 790 /**
 791  * Execute monthly statistics gathering
 792  * @return boolean success
 793  */
 794 function stats_cron_monthly() {
 795     global $CFG, $DB;
 796     require_once($CFG->libdir.'/adminlib.php');
 797
 798     $now = time();
 799
 800     // read last execution date from db
 801     if (!$timestart = get_config(NULL, 'statslastmonthly')) {
 802         $timestart = stats_get_base_monthly(stats_get_start_from('monthly'));
 803         set_config('statslastmonthly', $timestart);
 804     }
 805
 806     $nextstartmonth = stats_get_next_month_start($timestart);
 807
 808     // are there any months that need to be processed?
 809     if ($now < $nextstartmonth) {
 810         return true; // everything ok and up-to-date
 811     }
 812
 813     $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
 814
 815     if (!set_cron_lock('statsrunning', $now + $timeout)) {
 816         return false;
 817     }
 818
 819     // fisr delete entries that should not be there yet
 820     $DB->delete_records_select('stats_monthly', "timeend > $timestart");
 821     $DB->delete_records_select('stats_user_monthly', "timeend > $timestart");
 822
 823     $startmonth = stats_get_base_monthly($now);
 824
 825
 826     mtrace("Running monthly statistics gathering, starting at $timestart:");
 827     cron_trace_time_and_memory();
 828
 829     $months = 0;
 830     while ($now > $nextstartmonth) {
 831         core_php_time_limit::raise($timeout - 200);
 832         $months++;
 833
 834         if ($months > 1) {
 835             // move the lock
 836             set_cron_lock('statsrunning', time() + $timeout, true);
 837         }
 838
 839         $stattimesql = "timeend > $timestart AND timeend <= $nextstartmonth";
 840
 841         $monthstart = time();
 842         stats_progress('init');
 843
 844     /// process login info first
 845         $sql = "INSERT INTO {stats_user_monthly} (stattype, timeend, courseid, userid, statsreads)
 846
 847                 SELECT 'logins', timeend, courseid, userid, SUM(statsreads)
 848                   FROM (
 849                            SELECT $nextstartmonth AS timeend, courseid, userid, statsreads
 850                              FROM {stats_user_daily} sd
 851                             WHERE stattype = 'logins' AND $stattimesql
 852                        ) inline_view
 853               GROUP BY timeend, courseid, userid
 854                 HAVING SUM(statsreads) > 0";
 855
 856         $DB->execute($sql);
 857
 858         stats_progress('1');
 859
 860         $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
 861
 862                 SELECT 'logins' AS stattype, $nextstartmonth AS timeend, ".SITEID." as courseid, 0,
 863                        COALESCE((SELECT SUM(statsreads)
 864                                    FROM {stats_user_monthly} s1
 865                                   WHERE s1.stattype = 'logins' AND timeend = $nextstartmonth), 0) AS nstat1,
 866                        (SELECT COUNT('x')
 867                           FROM {stats_user_monthly} s2
 868                          WHERE s2.stattype = 'logins' AND timeend = $nextstartmonth) AS nstat2" .
 869                 $DB->sql_null_from_clause();
 870
 871         $DB->execute($sql);
 872
 873         stats_progress('2');
 874
 875     /// now enrolments averages
 876         $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
 877
 878                 SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . "
 879                   FROM (
 880                            SELECT $nextstartmonth AS ntimeend, courseid, roleid, stat1, stat2
 881                              FROM {stats_daily} sd
 882                             WHERE stattype = 'enrolments' AND $stattimesql
 883                        ) inline_view
 884               GROUP BY ntimeend, courseid, roleid";
 885
 886         $DB->execute($sql);
 887
 888         stats_progress('3');
 889
 890     /// activity read/write averages
 891         $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
 892
 893                 SELECT 'activity', ntimeend, courseid, roleid, SUM(stat1), SUM(stat2)
 894                   FROM (
 895                            SELECT $nextstartmonth AS ntimeend, courseid, roleid, stat1, stat2
 896                              FROM {stats_daily}
 897                             WHERE stattype = 'activity' AND $stattimesql
 898                        ) inline_view
 899               GROUP BY ntimeend, courseid, roleid";
 900
 901         $DB->execute($sql);
 902
 903         stats_progress('4');
 904
 905     /// user read/write averages
 906         $sql = "INSERT INTO {stats_user_monthly} (stattype, timeend, courseid, userid, statsreads, statswrites)
 907
 908                 SELECT 'activity', ntimeend, courseid, userid, SUM(statsreads), SUM(statswrites)
 909                   FROM (
 910                            SELECT $nextstartmonth AS ntimeend, courseid, userid, statsreads, statswrites
 911                              FROM {stats_user_daily}
 912                             WHERE stattype = 'activity' AND $stattimesql
 913                        ) inline_view
 914               GROUP BY ntimeend, courseid, userid";
 915
 916         $DB->execute($sql);
 917
 918         stats_progress('5');
 919
 920         set_config('statslastmonthly', $nextstartmonth);
 921         $elapsed = time() - $monthstart;
 922         mtrace(" finished until $nextstartmonth: ".userdate($nextstartmonth) ." (in $elapsed s)");
 923
 924         $timestart      = $nextstartmonth;
 925         $nextstartmonth = stats_get_next_month_start($nextstartmonth);
 926     }
 927
 928     set_cron_lock('statsrunning', null);
 929     mtrace("...completed $months months of statistics.");
 930     return true;
 931 }
 932
 933 /**
 934  * Return starting date of stats processing
 935  * @param string $str name of table - daily, weekly or monthly
 936  * @return int timestamp
 937  */
 938 function stats_get_start_from($str) {
 939     global $CFG, $DB;
 940
 941     // are there any data in stats table? Should not be...
 942     if ($timeend = $DB->get_field_sql('SELECT MAX(timeend) FROM {stats_'.$str.'}')) {
 943         return $timeend;
 944     }
 945     // decide what to do based on our config setting (either all or none or a timestamp)
 946     switch ($CFG->statsfirstrun) {
 947         case 'all':
 948             $manager = get_log_manager();
 949             $stores = $manager->get_readers();
 950             $firstlog = false;
 951             foreach ($stores as $store) {
 952                 if ($store instanceof \core\log\sql_internal_reader) {
 953                     $logtable = $store->get_internal_log_table_name();
 954                     if (!$logtable) {
 955                         continue;
 956                     }
 957                     $first = $DB->get_field_sql("SELECT MIN(timecreated) FROM {{$logtable}}");
 958                     if ($first and (!$firstlog or $firstlog > $first)) {
 959                         $firstlog = $first;
 960                     }
 961                 }
 962             }
 963
 964             $first = $DB->get_field_sql('SELECT MIN(time) FROM {log}');
 965             if ($first and (!$firstlog or $firstlog > $first)) {
 966                 $firstlog = $first;
 967             }
 968
 969             if ($firstlog) {
 970                 return $firstlog;
 971             }
 972
 973         default:
 974             if (is_numeric($CFG->statsfirstrun)) {
 975                 return time() - $CFG->statsfirstrun;
 976             }
 977             // not a number? use next instead
 978         case 'none':
 979             return strtotime('-3 day', time());
 980     }
 981 }
 982
 983 /**
 984  * Start of day
 985  * @param int $time timestamp
 986  * @return start of day
 987  */
 988 function stats_get_base_daily($time=0) {
 989     global $CFG;
 990
 991     if (empty($time)) {
 992         $time = time();
 993     }
 994     if ($CFG->timezone == 99) {
 995         $time = strtotime(date('d-M-Y', $time));
 996         return $time;
 997     } else {
 998         $offset = get_timezone_offset($CFG->timezone);
 999         $gtime = $time + $offset;
1000         $gtime = intval($gtime / (60*60*24)) * 60*60*24;
1001         return $gtime - $offset;
1002     }
1003 }
1004
1005 /**
1006  * Start of week
1007  * @param int $time timestamp
1008  * @return start of week
1009  */
1010 function stats_get_base_weekly($time=0) {
1011     global $CFG;
1012
1013     $time = stats_get_base_daily($time);
1014     $startday = $CFG->calendar_startwday;
1015     if ($CFG->timezone == 99) {
1016         $thisday = date('w', $time);
1017     } else {
1018         $offset = get_timezone_offset($CFG->timezone);
1019         $gtime = $time + $offset;
1020         $thisday = gmdate('w', $gtime);
1021     }
1022     if ($thisday > $startday) {
1023         $time = $time - (($thisday - $startday) * 60*60*24);
1024     } else if ($thisday < $startday) {
1025         $time = $time - ((7 + $thisday - $startday) * 60*60*24);
1026     }
1027     return $time;
1028 }
1029
1030 /**
1031  * Start of month
1032  * @param int $time timestamp
1033  * @return start of month
1034  */
1035 function stats_get_base_monthly($time=0) {
1036     global $CFG;
1037
1038     if (empty($time)) {
1039         $time = time();
1040     }
1041     if ($CFG->timezone == 99) {
1042         return strtotime(date('1-M-Y', $time));
1043
1044     } else {
1045         $time = stats_get_base_daily($time);
1046         $offset = get_timezone_offset($CFG->timezone);
1047         $gtime = $time + $offset;
1048         $day = gmdate('d', $gtime);
1049         if ($day == 1) {
1050             return $time;
1051         }
1052         return $gtime - (($day-1) * 60*60*24);
1053     }
1054 }
1055
1056 /**
1057  * Start of next day
1058  * @param int $time timestamp
1059  * @return start of next day
1060  */
1061 function stats_get_next_day_start($time) {
1062     $next = stats_get_base_daily($time);
1063     $next = $next + 60*60*26;
1064     $next = stats_get_base_daily($next);
1065     if ($next <= $time) {
1066         //DST trouble - prevent infinite loops
1067         $next = $next + 60*60*24;
1068     }
1069     return $next;
1070 }
1071
1072 /**
1073  * Start of next week
1074  * @param int $time timestamp
1075  * @return start of next week
1076  */
1077 function stats_get_next_week_start($time) {
1078     $next = stats_get_base_weekly($time);
1079     $next = $next + 60*60*24*9;
1080     $next = stats_get_base_weekly($next);
1081     if ($next <= $time) {
1082         //DST trouble - prevent infinite loops
1083         $next = $next + 60*60*24*7;
1084     }
1085     return $next;
1086 }
1087
1088 /**
1089  * Start of next month
1090  * @param int $time timestamp
1091  * @return start of next month
1092  */
1093 function stats_get_next_month_start($time) {
1094     $next = stats_get_base_monthly($time);
1095     $next = $next + 60*60*24*33;
1096     $next = stats_get_base_monthly($next);
1097     if ($next <= $time) {
1098         //DST trouble - prevent infinite loops
1099         $next = $next + 60*60*24*31;
1100     }
1101     return $next;
1102 }
1103
1104 /**
1105  * Remove old stats data
1106  */
1107 function stats_clean_old() {
1108     global $DB;
1109     mtrace("Running stats cleanup tasks...");
1110     cron_trace_time_and_memory();
1111     $deletebefore =  stats_get_base_monthly();
1112
1113     // delete dailies older than 3 months (to be safe)
1114     $deletebefore = strtotime('-3 months', $deletebefore);
1115     $DB->delete_records_select('stats_daily',      "timeend < $deletebefore");
1116     $DB->delete_records_select('stats_user_daily', "timeend < $deletebefore");
1117
1118     // delete weeklies older than 9  months (to be safe)
1119     $deletebefore = strtotime('-6 months', $deletebefore);
1120     $DB->delete_records_select('stats_weekly',      "timeend < $deletebefore");
1121     $DB->delete_records_select('stats_user_weekly', "timeend < $deletebefore");
1122
1123     // don't delete monthlies
1124
1125     mtrace("...stats cleanup finished");
1126 }
1127
1128 function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) {
1129     global $CFG, $DB;
1130
1131     $param = new stdClass();
1132     $param->params = array();
1133
1134     if ($time < 10) { // dailies
1135         // number of days to go back = 7* time
1136         $param->table = 'daily';
1137         $param->timeafter = strtotime("-".($time*7)." days",stats_get_base_daily());
1138     } elseif ($time < 20) { // weeklies
1139         // number of weeks to go back = time - 10 * 4 (weeks) + base week
1140         $param->table = 'weekly';
1141         $param->timeafter = strtotime("-".(($time - 10)*4)." weeks",stats_get_base_weekly());
1142     } else { // monthlies.
1143         // number of months to go back = time - 20 * months + base month
1144         $param->table = 'monthly';
1145         $param->timeafter = strtotime("-".($time - 20)." months",stats_get_base_monthly());
1146     }
1147
1148     $param->extras = '';
1149
1150     switch ($report) {
1151     // ******************** STATS_MODE_GENERAL ******************** //
1152     case STATS_REPORT_LOGINS:
1153         $param->fields = 'timeend,sum(stat1) as line1,sum(stat2) as line2';
1154         $param->fieldscomplete = true;
1155         $param->stattype = 'logins';
1156         $param->line1 = get_string('statslogins');
1157         $param->line2 = get_string('statsuniquelogins');
1158         if ($courseid == SITEID) {
1159             $param->extras = 'GROUP BY timeend';
1160         }
1161         break;
1162
1163     case STATS_REPORT_READS:
1164         $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat1 as line1';
1165         $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
1166         $param->aggregategroupby = 'roleid';
1167         $param->stattype = 'activity';
1168         $param->crosstab = true;
1169         $param->extras = 'GROUP BY timeend,roleid,stat1';
1170         if ($courseid == SITEID) {
1171             $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1) as line1';
1172             $param->extras = 'GROUP BY timeend,roleid';
1173         }
1174         break;
1175
1176     case STATS_REPORT_WRITES:
1177         $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat2 as line1';
1178         $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
1179         $param->aggregategroupby = 'roleid';
1180         $param->stattype = 'activity';
1181         $param->crosstab = true;
1182         $param->extras = 'GROUP BY timeend,roleid,stat2';
1183         if ($courseid == SITEID) {
1184             $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat2) as line1';
1185             $param->extras = 'GROUP BY timeend,roleid';
1186         }
1187         break;
1188
1189     case STATS_REPORT_ACTIVITY:
1190         $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1+stat2) as line1';
1191         $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
1192         $param->aggregategroupby = 'roleid';
1193         $param->stattype = 'activity';
1194         $param->crosstab = true;
1195         $param->extras = 'GROUP BY timeend,roleid';
1196         if ($courseid == SITEID) {
1197             $param->extras = 'GROUP BY timeend,roleid';
1198         }
1199         break;
1200
1201     case STATS_REPORT_ACTIVITYBYROLE;
1202         $param->fields = 'stat1 AS line1, stat2 AS line2';
1203         $param->stattype = 'activity';
1204         $rolename = $DB->get_field('role','name', array('id'=>$roleid));
1205         $param->line1 = $rolename . get_string('statsreads');
1206         $param->line2 = $rolename . get_string('statswrites');
1207         if ($courseid == SITEID) {
1208             $param->extras = 'GROUP BY timeend';
1209         }
1210         break;
1211
1212     // ******************** STATS_MODE_DETAILED ******************** //
1213     case STATS_REPORT_USER_ACTIVITY:
1214         $param->fields = 'statsreads as line1, statswrites as line2';
1215         $param->line1 = get_string('statsuserreads');
1216         $param->line2 = get_string('statsuserwrites');
1217         $param->stattype = 'activity';
1218         break;
1219
1220     case STATS_REPORT_USER_ALLACTIVITY:
1221         $param->fields = 'statsreads+statswrites as line1';
1222         $param->line1 = get_string('statsuseractivity');
1223         $param->stattype = 'activity';
1224         break;
1225
1226     case STATS_REPORT_USER_LOGINS:
1227         $param->fields = 'statsreads as line1';
1228         $param->line1 = get_string('statsuserlogins');
1229         $param->stattype = 'logins';
1230         break;
1231
1232     case STATS_REPORT_USER_VIEW:
1233         $param->fields = 'statsreads as line1, statswrites as line2, statsreads+statswrites as line3';
1234         $param->line1 = get_string('statsuserreads');
1235         $param->line2 = get_string('statsuserwrites');
1236         $param->line3 = get_string('statsuseractivity');
1237         $param->stattype = 'activity';
1238         break;
1239
1240     // ******************** STATS_MODE_RANKED ******************** //
1241     case STATS_REPORT_ACTIVE_COURSES:
1242         $param->fields = 'sum(stat1+stat2) AS line1';
1243         $param->stattype = 'activity';
1244         $param->orderby = 'line1 DESC';
1245         $param->line1 = get_string('useractivity');
1246         $param->graphline = 'line1';
1247         break;
1248
1249     case STATS_REPORT_ACTIVE_COURSES_WEIGHTED:
1250         $threshold = 0;
1251         if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
1252             $threshold = $CFG->statsuserthreshold;
1253         }
1254         $param->fields = '';
1255         $param->sql = 'SELECT activity.courseid, activity.all_activity AS line1, enrolments.highest_enrolments AS line2,
1256                         activity.all_activity / enrolments.highest_enrolments as line3
1257                        FROM (
1258                             SELECT courseid, sum(stat1+stat2) AS all_activity
1259                               FROM {stats_'.$param->table.'}
1260                              WHERE stattype=\'activity\' AND timeend >= '.(int)$param->timeafter.' AND roleid = 0 GROUP BY courseid
1261                        ) activity
1262                        INNER JOIN
1263                             (
1264                             SELECT courseid, max(stat1) AS highest_enrolments
1265                               FROM {stats_'.$param->table.'}
1266                              WHERE stattype=\'enrolments\' AND timeend >= '.(int)$param->timeafter.' AND stat1 > '.(int)$threshold.'
1267                           GROUP BY courseid
1268                       ) enrolments
1269                       ON (activity.courseid = enrolments.courseid)
1270                       ORDER BY line3 DESC';
1271         $param->line1 = get_string('useractivity');
1272         $param->line2 = get_string('users');
1273         $param->line3 = get_string('activityweighted');
1274         $param->graphline = 'line3';
1275         break;
1276
1277     case STATS_REPORT_PARTICIPATORY_COURSES:
1278         $threshold = 0;
1279         if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
1280             $threshold = $CFG->statsuserthreshold;
1281         }
1282         $param->fields = '';
1283         $param->sql = 'SELECT courseid, ' . $DB->sql_ceil('avg(all_enrolments)') . ' as line1, ' .
1284                          $DB->sql_ceil('avg(active_enrolments)') . ' as line2, avg(proportion_active) AS line3
1285                        FROM (
1286                            SELECT courseid, timeend, stat2 as active_enrolments,
1287                                   stat1 as all_enrolments, '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' AS proportion_active
1288                              FROM {stats_'.$param->table.'}
1289                             WHERE stattype=\'enrolments\' AND roleid = 0 AND stat1 > '.(int)$threshold.'
1290                        ) aq
1291                        WHERE timeend >= '.(int)$param->timeafter.'
1292                        GROUP BY courseid
1293                        ORDER BY line3 DESC';
1294
1295         $param->line1 = get_string('users');
1296         $param->line2 = get_string('activeusers');
1297         $param->line3 = get_string('participationratio');
1298         $param->graphline = 'line3';
1299         break;
1300
1301     case STATS_REPORT_PARTICIPATORY_COURSES_RW:
1302         $param->fields = '';
1303         $param->sql =  'SELECT courseid, sum(views) AS line1, sum(posts) AS line2,
1304                            avg(proportion_active) AS line3
1305                          FROM (
1306                            SELECT courseid, timeend, stat1 as views, stat2 AS posts,
1307                                   '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' as proportion_active
1308                              FROM {stats_'.$param->table.'}
1309                             WHERE stattype=\'activity\' AND roleid = 0 AND stat1 > 0
1310                        ) aq
1311                        WHERE timeend >= '.(int)$param->timeafter.'
1312                        GROUP BY courseid
1313                        ORDER BY line3 DESC';
1314         $param->line1 = get_string('views');
1315         $param->line2 = get_string('posts');
1316         $param->line3 = get_string('participationratio');
1317         $param->graphline = 'line3';
1318         break;
1319     }
1320
1321     /*
1322     if ($courseid == SITEID && $mode != STATS_MODE_RANKED) { // just aggregate all courses.
1323         $param->fields = preg_replace('/(?:sum)([a-zA-Z0-9+_]*)\W+as\W+([a-zA-Z0-9_]*)/i','sum($1) as $2',$param->fields);
1324         $param->extras = ' GROUP BY timeend'.((!empty($param->aggregategroupby)) ? ','.$param->aggregategroupby : '');
1325     }
1326     */
1327     //TODO must add the SITEID reports to the rest of the reports.
1328     return $param;
1329 }
1330
1331 function stats_get_view_actions() {
1332     return array('view','view all','history');
1333 }
1334
1335 function stats_get_post_actions() {
1336     return array('add','delete','edit','add mod','delete mod','edit section'.'enrol','loginas','new','unenrol','update','update mod');
1337 }
1338
1339 function stats_get_action_names($str) {
1340     global $CFG, $DB;
1341
1342     $mods = $DB->get_records('modules');
1343     $function = 'stats_get_'.$str.'_actions';
1344     $actions = $function();
1345     foreach ($mods as $mod) {
1346         $file = $CFG->dirroot.'/mod/'.$mod->name.'/lib.php';
1347         if (!is_readable($file)) {
1348             continue;
1349         }
1350         require_once($file);
1351         $function = $mod->name.'_get_'.$str.'_actions';
1352         if (function_exists($function)) {
1353             $mod_actions = $function();
1354             if (is_array($mod_actions)) {
1355                 $actions = array_merge($actions, $mod_actions);
1356             }
1357         }
1358     }
1359
1360     // The array_values() forces a stack-like array
1361     // so we can later loop over safely...
1362     $actions =  array_values(array_unique($actions));
1363     $c = count($actions);
1364     for ($n=0;$n<$c;$n++) {
1365         $actions[$n] = $actions[$n];
1366     }
1367     return $actions;
1368 }
1369
1370 function stats_get_time_options($now,$lastweekend,$lastmonthend,$earliestday,$earliestweek,$earliestmonth) {
1371
1372     $now = stats_get_base_daily(time());
1373     // it's really important that it's TIMEEND in the table. ie, tuesday 00:00:00 is monday night.
1374     // so we need to take a day off here (essentially add a day to $now
1375     $now += 60*60*24;
1376
1377     $timeoptions = array();
1378
1379     if ($now - (60*60*24*7) >= $earliestday) {
1380         $timeoptions[STATS_TIME_LASTWEEK] = get_string('numweeks','moodle',1);
1381     }
1382     if ($now - (60*60*24*14) >= $earliestday) {
1383         $timeoptions[STATS_TIME_LAST2WEEKS] = get_string('numweeks','moodle',2);
1384     }
1385     if ($now - (60*60*24*21) >= $earliestday) {
1386         $timeoptions[STATS_TIME_LAST3WEEKS] = get_string('numweeks','moodle',3);
1387     }
1388     if ($now - (60*60*24*28) >= $earliestday) {
1389         $timeoptions[STATS_TIME_LAST4WEEKS] = get_string('numweeks','moodle',4);// show dailies up to (including) here.
1390     }
1391     if ($lastweekend - (60*60*24*56) >= $earliestweek) {
1392         $timeoptions[STATS_TIME_LAST2MONTHS] = get_string('nummonths','moodle',2);
1393     }
1394     if ($lastweekend - (60*60*24*84) >= $earliestweek) {
1395         $timeoptions[STATS_TIME_LAST3MONTHS] = get_string('nummonths','moodle',3);
1396     }
1397     if ($lastweekend - (60*60*24*112) >= $earliestweek) {
1398         $timeoptions[STATS_TIME_LAST4MONTHS] = get_string('nummonths','moodle',4);
1399     }
1400     if ($lastweekend - (60*60*24*140) >= $earliestweek) {
1401         $timeoptions[STATS_TIME_LAST5MONTHS] = get_string('nummonths','moodle',5);
1402     }
1403     if ($lastweekend - (60*60*24*168) >= $earliestweek) {
1404         $timeoptions[STATS_TIME_LAST6MONTHS] = get_string('nummonths','moodle',6); // show weeklies up to (including) here
1405     }
1406     if (strtotime('-7 months',$lastmonthend) >= $earliestmonth) {
1407         $timeoptions[STATS_TIME_LAST7MONTHS] = get_string('nummonths','moodle',7);
1408     }
1409     if (strtotime('-8 months',$lastmonthend) >= $earliestmonth) {
1410         $timeoptions[STATS_TIME_LAST8MONTHS] = get_string('nummonths','moodle',8);
1411     }
1412     if (strtotime('-9 months',$lastmonthend) >= $earliestmonth) {
1413         $timeoptions[STATS_TIME_LAST9MONTHS] = get_string('nummonths','moodle',9);
1414     }
1415     if (strtotime('-10 months',$lastmonthend) >= $earliestmonth) {
1416         $timeoptions[STATS_TIME_LAST10MONTHS] = get_string('nummonths','moodle',10);
1417     }
1418     if (strtotime('-11 months',$lastmonthend) >= $earliestmonth) {
1419         $timeoptions[STATS_TIME_LAST11MONTHS] = get_string('nummonths','moodle',11);
1420     }
1421     if (strtotime('-1 year',$lastmonthend) >= $earliestmonth) {
1422         $timeoptions[STATS_TIME_LASTYEAR] = get_string('lastyear');
1423     }
1424
1425     $years = (int)date('y', $now) - (int)date('y', $earliestmonth);
1426     if ($years > 1) {
1427         for($i = 2; $i <= $years; $i++) {
1428             $timeoptions[$i*12+20] = get_string('numyears', 'moodle', $i);
1429         }
1430     }
1431
1432     return $timeoptions;
1433 }
1434
1435 function stats_get_report_options($courseid,$mode) {
1436     global $CFG, $DB;
1437
1438     $reportoptions = array();
1439
1440     switch ($mode) {
1441     case STATS_MODE_GENERAL:
1442         $reportoptions[STATS_REPORT_ACTIVITY] = get_string('statsreport'.STATS_REPORT_ACTIVITY);
1443         if ($courseid != SITEID && $context = context_course::instance($courseid)) {
1444             $sql = 'SELECT r.id, r.name FROM {role} r JOIN {stats_daily} s ON s.roleid = r.id WHERE s.courseid = :courseid GROUP BY r.id, r.name';
1445             if ($roles = $DB->get_records_sql($sql, array('courseid' => $courseid))) {
1446                 foreach ($roles as $role) {
1447                     $reportoptions[STATS_REPORT_ACTIVITYBYROLE.$role->id] = get_string('statsreport'.STATS_REPORT_ACTIVITYBYROLE). ' '.$role->name;
1448                 }
1449             }
1450         }
1451         $reportoptions[STATS_REPORT_READS] = get_string('statsreport'.STATS_REPORT_READS);
1452         $reportoptions[STATS_REPORT_WRITES] = get_string('statsreport'.STATS_REPORT_WRITES);
1453         if ($courseid == SITEID) {
1454             $reportoptions[STATS_REPORT_LOGINS] = get_string('statsreport'.STATS_REPORT_LOGINS);
1455         }
1456
1457         break;
1458     case STATS_MODE_DETAILED:
1459         $reportoptions[STATS_REPORT_USER_ACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ACTIVITY);
1460         $reportoptions[STATS_REPORT_USER_ALLACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ALLACTIVITY);
1461         if (has_capability('report/stats:view', context_system::instance())) {
1462             $site = get_site();
1463             $reportoptions[STATS_REPORT_USER_LOGINS] = get_string('statsreport'.STATS_REPORT_USER_LOGINS);
1464         }
1465         break;
1466     case STATS_MODE_RANKED:
1467         if (has_capability('report/stats:view', context_system::instance())) {
1468             $reportoptions[STATS_REPORT_ACTIVE_COURSES] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES);
1469             $reportoptions[STATS_REPORT_ACTIVE_COURSES_WEIGHTED] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES_WEIGHTED);
1470             $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES);
1471             $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES_RW] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES_RW);
1472         }
1473         break;
1474     }
1475
1476     return $reportoptions;
1477 }
1478
1479 /**
1480  * Fix missing entries in the statistics.
1481  *
1482  * This creates a dummy stat when nothing happened during a day/week/month.
1483  *
1484  * @param array $stats array of statistics.
1485  * @param int $timeafter unused.
1486  * @param string $timestr type of statistics to generate (dayly, weekly, monthly).
1487  * @param boolean $line2
1488  * @param boolean $line3
1489  * @return array of fixed statistics.
1490  */
1491 function stats_fix_zeros($stats,$timeafter,$timestr,$line2=true,$line3=false) {
1492
1493     if (empty($stats)) {
1494         return;
1495     }
1496
1497     $timestr = str_replace('user_','',$timestr); // just in case.
1498
1499     // Gets the current user base time.
1500     $fun = 'stats_get_base_'.$timestr;
1501     $now = $fun();
1502
1503     // Extract the ending time of the statistics.
1504     $actualtimes = array();
1505     $actualtimeshour = null;
1506     foreach ($stats as $statid => $s) {
1507         // Normalise the month date to the 1st if for any reason it's set to later. But we ignore
1508         // anything above or equal to 29 because sometimes we get the end of the month. Also, we will
1509         // set the hours of the result to all of them, that way we prevent DST differences.
1510         if ($timestr == 'monthly') {
1511             $day = date('d', $s->timeend);
1512             if (date('d', $s->timeend) > 1 && date('d', $s->timeend) < 29) {
1513                 $day = 1;
1514             }
1515             if (is_null($actualtimeshour)) {
1516                 $actualtimeshour = date('H', $s->timeend);
1517             }
1518             $s->timeend = mktime($actualtimeshour, 0, 0, date('m', $s->timeend), $day, date('Y', $s->timeend));
1519         }
1520         $stats[$statid] = $s;
1521         $actualtimes[] = $s->timeend;
1522     }
1523
1524     $actualtimesvalues = array_values($actualtimes);
1525     $timeafter = array_pop($actualtimesvalues);
1526
1527     // Generate a base timestamp for each possible month/week/day.
1528     $times = array();
1529     while ($timeafter < $now) {
1530         $times[] = $timeafter;
1531         if ($timestr == 'daily') {
1532             $timeafter = stats_get_next_day_start($timeafter);
1533         } else if ($timestr == 'weekly') {
1534             $timeafter = stats_get_next_week_start($timeafter);
1535         } else if ($timestr == 'monthly') {
1536             // We can't just simply +1 month because the 31st Jan + 1 month = 2nd of March.
1537             $year = date('Y', $timeafter);
1538             $month = date('m', $timeafter);
1539             $day = date('d', $timeafter);
1540             $dayofnextmonth = $day;
1541             if ($day >= 29) {
1542                 $daysinmonth = date('n', mktime(0, 0, 0, $month+1, 1, $year));
1543                 if ($day > $daysinmonth) {
1544                     $dayofnextmonth = $daysinmonth;
1545                 }
1546             }
1547             $timeafter = mktime($actualtimeshour, 0, 0, $month+1, $dayofnextmonth, $year);
1548         } else {
1549             // This will put us in a never ending loop.
1550             return $stats;
1551         }
1552     }
1553
1554     // Add the base timestamp to the statistics if not present.
1555     foreach ($times as $count => $time) {
1556         if (!in_array($time,$actualtimes) && $count != count($times) -1) {
1557             $newobj = new StdClass;
1558             $newobj->timeend = $time;
1559             $newobj->id = 0;
1560             $newobj->roleid = 0;
1561             $newobj->line1 = 0;
1562             if (!empty($line2)) {
1563                 $newobj->line2 = 0;
1564             }
1565             if (!empty($line3)) {
1566                 $newobj->line3 = 0;
1567             }
1568             $newobj->zerofixed = true;
1569             $stats[] = $newobj;
1570         }
1571     }
1572
1573     usort($stats,"stats_compare_times");
1574     return $stats;
1575 }
1576
1577 // helper function to sort arrays by $obj->timeend
1578 function stats_compare_times($a,$b) {
1579    if ($a->timeend == $b->timeend) {
1580        return 0;
1581    }
1582    return ($a->timeend > $b->timeend) ? -1 : 1;
1583 }
1584
1585 function stats_check_uptodate($courseid=0) {
1586     global $CFG, $DB;
1587
1588     if (empty($courseid)) {
1589         $courseid = SITEID;
1590     }
1591
1592     $latestday = stats_get_start_from('daily');
1593
1594     if ((time() - 60*60*24*2) < $latestday) { // we're ok
1595         return NULL;
1596     }
1597
1598     $a = new stdClass();
1599     $a->daysdone = $DB->get_field_sql("SELECT COUNT(DISTINCT(timeend)) FROM {stats_daily}");
1600
1601     // how many days between the last day and now?
1602     $a->dayspending = ceil((stats_get_base_daily() - $latestday)/(60*60*24));
1603
1604     if ($a->dayspending == 0 && $a->daysdone != 0) {
1605         return NULL; // we've only just started...
1606     }
1607
1608     //return error as string
1609     return get_string('statscatchupmode','error',$a);
1610 }
1611
1612 /**
1613  * Create temporary tables to speed up log generation
1614  */
1615 function stats_temp_table_create() {
1616     global $CFG, $DB;
1617
1618     $dbman = $DB->get_manager(); // We are going to use database_manager services
1619
1620     stats_temp_table_drop();
1621
1622     $tables = array();
1623
1624     /// Define tables user to be created
1625     $table = new xmldb_table('temp_stats_daily');
1626     $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1627     $table->add_field('courseid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1628     $table->add_field('timeend', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1629     $table->add_field('roleid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1630     $table->add_field('stattype', XMLDB_TYPE_CHAR, 20, null, XMLDB_NOTNULL, null, 'activity');
1631     $table->add_field('stat1', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1632     $table->add_field('stat2', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1633     $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1634     $table->add_index('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid'));
1635     $table->add_index('timeend', XMLDB_INDEX_NOTUNIQUE, array('timeend'));
1636     $table->add_index('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid'));
1637     $tables['temp_stats_daily'] = $table;
1638
1639     $table = new xmldb_table('temp_stats_user_daily');
1640     $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1641     $table->add_field('courseid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1642     $table->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1643     $table->add_field('roleid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1644     $table->add_field('timeend', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1645     $table->add_field('statsreads', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1646     $table->add_field('statswrites', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1647     $table->add_field('stattype', XMLDB_TYPE_CHAR, 30, null, XMLDB_NOTNULL, null, null);
1648     $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1649     $table->add_index('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid'));
1650     $table->add_index('userid', XMLDB_INDEX_NOTUNIQUE, array('userid'));
1651     $table->add_index('timeend', XMLDB_INDEX_NOTUNIQUE, array('timeend'));
1652     $table->add_index('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid'));
1653     $tables['temp_stats_user_daily'] = $table;
1654
1655     $table = new xmldb_table('temp_enroled');
1656     $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1657     $table->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1658     $table->add_field('courseid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1659     $table->add_field('roleid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null);
1660     $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1661     $table->add_index('userid', XMLDB_INDEX_NOTUNIQUE, array('userid'));
1662     $table->add_index('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid'));
1663     $table->add_index('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid'));
1664     $tables['temp_enroled'] = $table;
1665
1666
1667     $table = new xmldb_table('temp_log1');
1668     $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1669     $table->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1670     $table->add_field('course', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1671     $table->add_field('action', XMLDB_TYPE_CHAR, 40, null, XMLDB_NOTNULL, null, null);
1672     $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1673     $table->add_index('action', XMLDB_INDEX_NOTUNIQUE, array('action'));
1674     $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
1675     $table->add_index('user', XMLDB_INDEX_NOTUNIQUE, array('userid'));
1676     $table->add_index('usercourseaction', XMLDB_INDEX_NOTUNIQUE, array('userid','course','action'));
1677     $tables['temp_log1'] = $table;
1678
1679     /// temp_log2 is exactly the same as temp_log1.
1680     $tables['temp_log2'] = clone $tables['temp_log1'];
1681     $tables['temp_log2']->setName('temp_log2');
1682
1683     try {
1684
1685         foreach ($tables as $table) {
1686             $dbman->create_temp_table($table);
1687         }
1688
1689     } catch (Exception $e) {
1690         mtrace('Temporary table creation failed: '. $e->getMessage());
1691         return false;
1692     }
1693
1694     return true;
1695 }
1696
1697 /**
1698  * Deletes summary logs table for stats calculation
1699  */
1700 function stats_temp_table_drop() {
1701     global $DB;
1702
1703     $dbman = $DB->get_manager();
1704
1705     $tables = array('temp_log1', 'temp_log2', 'temp_stats_daily', 'temp_stats_user_daily', 'temp_enroled');
1706
1707     foreach ($tables as $name) {
1708
1709         if ($dbman->table_exists($name)) {
1710             $table = new xmldb_table($name);
1711
1712             try {
1713                 $dbman->drop_table($table);
1714             } catch (Exception $e) {
1715                 mtrace("Error occured while dropping temporary tables!");
1716             }
1717         }
1718     }
1719 }
1720
1721 /**
1722  * Fills the temporary stats tables with new data
1723  *
1724  * This function is meant to be called once at the start of stats generation
1725  *
1726  * @param int timestart timestamp of the start time of logs view
1727  * @param int timeend timestamp of the end time of logs view
1728  * @return bool success (true) or failure(false)
1729  */
1730 function stats_temp_table_setup() {
1731     global $DB;
1732
1733     $sql = "INSERT INTO {temp_enroled} (userid, courseid, roleid)
1734
1735                SELECT ue.userid, e.courseid, ra.roleid
1736                 FROM {role_assignments} ra
1737                 JOIN {context} c ON (c.id = ra.contextid AND c.contextlevel = :courselevel)
1738                 JOIN {enrol} e ON e.courseid = c.instanceid
1739                 JOIN {user_enrolments} ue ON (ue.enrolid = e.id AND ue.userid = ra.userid)";
1740
1741     return stats_run_query($sql, array('courselevel' => CONTEXT_COURSE));
1742 }
1743
1744 /**
1745  * Fills the temporary stats tables with new data
1746  *
1747  * This function is meant to be called to get a new day of data
1748  *
1749  * @param int timestamp of the start time of logs view
1750  * @param int timestamp of the end time of logs view
1751  * @return bool success (true) or failure(false)
1752  */
1753 function stats_temp_table_fill($timestart, $timeend) {
1754     global $DB;
1755
1756     // First decide from where we want the data.
1757
1758     $params = array('timestart' => $timestart,
1759                     'timeend' => $timeend,
1760                     'participating' => \core\event\base::LEVEL_PARTICIPATING,
1761                     'teaching' => \core\event\base::LEVEL_TEACHING,
1762                     'loginevent1' => '\core\event\user_loggedin',
1763                     'loginevent2' => '\core\event\user_loggedin',
1764     );
1765
1766     $filled = false;
1767     $manager = get_log_manager();
1768     $stores = $manager->get_readers();
1769     foreach ($stores as $store) {
1770         if ($store instanceof \core\log\sql_internal_reader) {
1771             $logtable = $store->get_internal_log_table_name();
1772             if (!$logtable) {
1773                 continue;
1774             }
1775
1776             $sql = "SELECT COUNT('x')
1777                       FROM {{$logtable}}
1778                      WHERE timecreated >= :timestart AND timecreated < :timeend";
1779
1780             if (!$DB->get_field_sql($sql, $params)) {
1781                 continue;
1782             }
1783
1784             // Let's fake the old records using new log data.
1785             // We want only data relevant to educational process
1786             // done by real users.
1787
1788             $sql = "INSERT INTO {temp_log1} (userid, course, action)
1789
1790             SELECT userid,
1791                    CASE
1792                       WHEN courseid IS NULL THEN ".SITEID."
1793                       WHEN courseid = 0 THEN ".SITEID."
1794                       ELSE courseid
1795                    END,
1796                    CASE
1797                        WHEN eventname = :loginevent1 THEN 'login'
1798                        WHEN crud = 'r' THEN 'view'
1799                        ELSE 'update'
1800                    END
1801               FROM {{$logtable}}
1802              WHERE timecreated >= :timestart AND timecreated < :timeend
1803                    AND (origin = 'web' OR origin = 'ws')
1804                    AND (edulevel = :participating OR edulevel = :teaching OR eventname = :loginevent2)";
1805
1806             $DB->execute($sql, $params);
1807             $filled = true;
1808         }
1809     }
1810
1811     if (!$filled) {
1812         // Fallback to legacy data.
1813         $sql = "INSERT INTO {temp_log1} (userid, course, action)
1814
1815             SELECT userid, course, action
1816               FROM {log}
1817              WHERE time >= :timestart AND time < :timeend";
1818
1819         $DB->execute($sql, $params);
1820     }
1821
1822     $sql = 'INSERT INTO {temp_log2} (userid, course, action)
1823
1824             SELECT userid, course, action FROM {temp_log1}';
1825
1826     $DB->execute($sql);
1827
1828     // We have just loaded all the temp tables, collect statistics for that.
1829     $DB->update_temp_table_stats();
1830
1831     return true;
1832 }
1833
1834
1835 /**
1836  * Deletes summary logs table for stats calculation
1837  *
1838  * @return bool success (true) or failure(false)
1839  */
1840 function stats_temp_table_clean() {
1841     global $DB;
1842
1843     $sql = array();
1844
1845     $sql['up1'] = 'INSERT INTO {stats_daily} (courseid, roleid, stattype, timeend, stat1, stat2)
1846
1847                    SELECT courseid, roleid, stattype, timeend, stat1, stat2 FROM {temp_stats_daily}';
1848
1849     $sql['up2'] = 'INSERT INTO {stats_user_daily}
1850                                (courseid, userid, roleid, timeend, statsreads, statswrites, stattype)
1851
1852                    SELECT courseid, userid, roleid, timeend, statsreads, statswrites, stattype
1853                      FROM {temp_stats_user_daily}';
1854
1855     foreach ($sql as $id => $query) {
1856         if (! stats_run_query($query)) {
1857             mtrace("Error during table cleanup!");
1858             return false;
1859         }
1860     }
1861
1862     $tables = array('temp_log1', 'temp_log2', 'temp_stats_daily', 'temp_stats_user_daily');
1863
1864     foreach ($tables as $name) {
1865         $DB->delete_records($name);
1866     }
1867
1868     return true;
1869 }