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