lib/statslib.php

   1 <?php
   2
   3     // THESE CONSTANTS ARE USED FOR THE REPORTING PAGE.
   4
   5     define('STATS_REPORT_LOGINS',1); // double impose logins and unqiue logins on a line graph. site course only.
   6     define('STATS_REPORT_READS',2); // double impose student reads and teacher reads on a line graph.
   7     define('STATS_REPORT_WRITES',3); // double impose student writes and teacher writes on a line graph.
   8     define('STATS_REPORT_ACTIVITY',4); // 2+3 added up, teacher vs student.
   9     define('STATS_REPORT_ACTIVITYBYROLE',5); // all activity, reads vs writes, seleted by role.
  10
  11     // user level stats reports.
  12     define('STATS_REPORT_USER_ACTIVITY',7);
  13     define('STATS_REPORT_USER_ALLACTIVITY',8);
  14     define('STATS_REPORT_USER_LOGINS',9);
  15     define('STATS_REPORT_USER_VIEW',10);  // this is the report you see on the user profile.
  16
  17     // admin only ranking stats reports
  18     define('STATS_REPORT_ACTIVE_COURSES',11);
  19     define('STATS_REPORT_ACTIVE_COURSES_WEIGHTED',12);
  20     define('STATS_REPORT_PARTICIPATORY_COURSES',13);
  21     define('STATS_REPORT_PARTICIPATORY_COURSES_RW',14);
  22
  23     // start after 0 = show dailies.
  24     define('STATS_TIME_LASTWEEK',1);
  25     define('STATS_TIME_LAST2WEEKS',2);
  26     define('STATS_TIME_LAST3WEEKS',3);
  27     define('STATS_TIME_LAST4WEEKS',4);
  28
  29     // start after 10 = show weeklies
  30     define('STATS_TIME_LAST2MONTHS',12);
  31
  32     define('STATS_TIME_LAST3MONTHS',13);
  33     define('STATS_TIME_LAST4MONTHS',14);
  34     define('STATS_TIME_LAST5MONTHS',15);
  35     define('STATS_TIME_LAST6MONTHS',16);
  36
  37     // start after 20 = show monthlies
  38     define('STATS_TIME_LAST7MONTHS',27);
  39     define('STATS_TIME_LAST8MONTHS',28);
  40     define('STATS_TIME_LAST9MONTHS',29);
  41     define('STATS_TIME_LAST10MONTHS',30);
  42     define('STATS_TIME_LAST11MONTHS',31);
  43     define('STATS_TIME_LASTYEAR',32);
  44
  45     // different modes for what reports to offer
  46     define('STATS_MODE_GENERAL',1);
  47     define('STATS_MODE_DETAILED',2);
  48     define('STATS_MODE_RANKED',3); // admins only - ranks courses
  49
  50     // return codes - whether to rerun
  51     define('STATS_RUN_COMPLETE',1);
  52     define('STATS_RUN_ABORTED',0);
  53
  54 function stats_cron_daily () {
  55     global $CFG;
  56
  57     if (empty($CFG->enablestats)) {
  58         return STATS_RUN_ABORTED;
  59     }
  60
  61     if (!$timestart = stats_get_start_from('daily')) {
  62         return STATS_RUN_ABORTED;
  63     }
  64
  65
  66     $midnight = stats_getmidnight(time());
  67
  68     // check to make sure we're due to run, at least one day after last run
  69     if (isset($CFG->statslastdaily) and ((time() - 24*60*60) < $CFG->statslastdaily)) {
  70         return STATS_RUN_ABORTED;
  71     }
  72
  73     mtrace("Running daily statistics gathering...");
  74     set_config('statslastdaily',time());
  75
  76     $return = STATS_RUN_COMPLETE; // optimistic
  77
  78     static $daily_modules;
  79
  80     if (empty($daily_modules)) {
  81         $daily_modules = array();
  82         $mods = get_records("modules");
  83         foreach ($mods as $mod) {
  84             $file = $CFG->dirroot.'/mod/'.$mod->name.'/lib.php';
  85             if (!is_readable($file)) {
  86                 continue;
  87             }
  88             require_once($file);
  89             $fname = $mod->name.'_get_daily_stats';
  90             if (function_exists($fname)) {
  91                 $daily_modules[$mod] = $fname;
  92             }
  93         }
  94     }
  95
  96     $nextmidnight = stats_get_next_dayend($timestart);
  97
  98     if (!$courses = get_records('course','','','','id,1')) {
  99         return STATS_RUN_ABORTED;
 100     }
 101
 102     $days = 0;
 103     mtrace("starting at $timestart");
 104     while ($midnight > $nextmidnight && $timestart < $nextmidnight) {
 105
 106         $timesql = " (l.time > $timestart AND l.time < $nextmidnight) ";
 107         begin_sql();
 108         foreach ($courses as $course) {
 109             //do this first.
 110             if ($course->id == SITEID) {
 111                 $stat = new StdClass;
 112                 $stat->courseid = $course->id;
 113                 $stat->timeend = $nextmidnight;
 114                 $stat->roleid = 0; // all users
 115                 $stat->stattype = 'logins';
 116                 $sql = 'SELECT count(l.id) FROM '.$CFG->prefix.'log l WHERE l.action = \'login\' AND '.$timesql;
 117                 $stat->stat1 = count_records_sql($sql);
 118                 $sql = 'SELECT COUNT(DISTINCT(l.userid)) FROM '.$CFG->prefix.'log l WHERE l.action = \'login\' AND '.$timesql;
 119                 $stat->stat2 = count_records_sql($sql);
 120                 insert_record('stats_daily',$stat,false); // don't worry about the return id, we don't need it.
 121
 122                 // and now user logins...
 123                 $sql = 'SELECT l.userid,count(l.id) as count FROM '.$CFG->prefix.'log l WHERE action = \'login\' AND '.$timesql.' GROUP BY userid';
 124
 125                 if ($logins = get_records_sql($sql)) {
 126                     foreach ($logins as $l) {
 127                         $stat->statsreads = $l->count;
 128                         $stat->userid = $l->userid;
 129                         $stat->timeend = $nextmidnight;
 130                         $stat->courseid = SITEID;
 131                         $stat->statswrites = 0;
 132                         $stat->stattype = 'logins';
 133                         $stat->roleid = 0;
 134                         insert_record('stats_user_daily',$stat,false);
 135                     }
 136                 }
 137             }
 138
 139             $context = get_context_instance(CONTEXT_COURSE, $course->id);
 140             if (!$roles = get_roles_on_exact_context($context)) {
 141                 // no roles.. nothing to log.
 142                 continue;
 143             }
 144
 145             $primary_roles = sql_primary_role_subselect();  // In dmllib.php
 146             foreach ($roles as $role) {
 147                 // ENROLMENT FIRST....
 148                 // ALL users with this role...
 149                 $stat = new StdClass;
 150                 $stat->courseid = $course->id;
 151                 $stat->roleid = $role->id;
 152                 $stat->timeend = $nextmidnight;
 153                 $stat->stattype = 'enrolments';
 154                 $sql = 'SELECT COUNT(DISTINCT prs.userid) FROM ('.$primary_roles.') prs WHERE prs.primary_roleid='.$role->id.
 155                     ' AND prs.courseid='.$course->id.' AND prs.contextlevel = '.CONTEXT_COURSE;
 156                 $stat->stat1 = count_records_sql($sql);
 157
 158                 $sql = 'SELECT COUNT(DISTINCT prs.userid) FROM ('.$primary_roles.') prs
 159                         INNER JOIN '.$CFG->prefix.'log l ON (prs.userid=l.userid AND l.course=prs.courseid)
 160                         WHERE prs.primary_roleid='.$role->id.' AND prs.courseid='.$course->id.'
 161                         AND prs.contextlevel = '.CONTEXT_COURSE.' AND '.$timesql;
 162
 163                 $stat->stat2 = count_records_sql($sql);
 164                 insert_record('stats_daily',$stat,false); // don't worry about the return id, we don't need it.
 165
 166                 // ACTIVITY
 167
 168                 $stat = new StdClass;
 169                 $stat->courseid = $course->id;
 170                 $stat->roleid = $role->id;
 171                 $stat->timeend = $nextmidnight;
 172                 $stat->stattype = 'activity';
 173
 174                 $sql = 'SELECT COUNT(DISTINCT l.id) FROM ('.$primary_roles.') prs
 175                         INNER JOIN '.$CFG->prefix.'log l ON (prs.userid=l.userid
 176                         AND l.course=prs.courseid) WHERE prs.primary_roleid='.$role->id.'
 177                         AND prs.courseid='.$course->id.' AND prs.contextlevel = '.CONTEXT_COURSE.'
 178                          AND '.$timesql.' '.stats_get_action_sql_in('view');
 179                 $stat->stat1 = count_records_sql($sql);
 180
 181                 $sql = 'SELECT COUNT(DISTINCT l.id) FROM ('.$primary_roles.') prs
 182                         INNER JOIN '.$CFG->prefix.'log l ON (prs.userid=l.userid  AND l.course=prs.courseid)
 183                         WHERE prs.primary_roleid='.$role->id.' AND prs.courseid='.$course->id.'
 184                         AND prs.contextlevel = '.CONTEXT_COURSE.' AND '.$timesql.' '.stats_get_action_sql_in('post');
 185                 $stat->stat2 = count_records_sql($sql);
 186
 187                 insert_record('stats_daily',$stat,false); // don't worry about the return id, we don't need it.
 188             }
 189
 190             $users = stats_get_course_users($course,$timesql);
 191             foreach ($users as $user) {
 192                 stats_do_daily_user_cron($course,$user,$user->primaryrole,$timesql,$nextmidnight,$daily_modules);
 193             }
 194         }
 195         commit_sql();
 196         $timestart = $nextmidnight;
 197         $nextmidnight = stats_get_next_dayend($nextmidnight);
 198         $days++;
 199
 200         if (!stats_check_runtime()) {
 201             mtrace("Stopping early! reached maxruntime");
 202             $return = STATS_RUN_ABORTED;
 203             break;
 204         }
 205     }
 206     mtrace("got up to ".$timestart);
 207     mtrace("Completed $days days");
 208     return $return;
 209
 210 }
 211
 212
 213 function stats_cron_weekly () {
 214
 215     global $CFG;
 216
 217     if (empty($CFG->enablestats)) {
 218         STATS_RUN_ABORTED;
 219     }
 220
 221     if (!$timestart = stats_get_start_from('weekly')) {
 222         return STATS_RUN_ABORTED;
 223     }
 224
 225     // check to make sure we're due to run, at least one week after last run
 226     $sunday = stats_get_base_weekly();
 227
 228     if (isset($CFG->statslastweekly) and ((time() - (7*24*60*60)) <= $CFG->statslastweekly)) {
 229         return STATS_RUN_ABORTED;
 230     }
 231
 232     mtrace("Running weekly statistics gathering...");
 233     set_config('statslastweekly',time());
 234
 235     $return = STATS_RUN_COMPLETE; // optimistic
 236
 237     static $weekly_modules;
 238
 239     if (empty($weekly_modules)) {
 240         $weekly_modules = array();
 241         $mods = get_records("modules");
 242         foreach ($mods as $mod) {
 243             $file = $CFG->dirroot.'/mod/'.$mod->name.'/lib.php';
 244             if (!is_readable($file)) {
 245                 continue;
 246             }
 247             require_once($file);
 248             $fname = $mod->name.'_get_weekly_stats';
 249             if (function_exists($fname)) {
 250                 $weekly_modules[$mod] = $fname;
 251             }
 252         }
 253     }
 254
 255     $nextsunday = stats_get_next_weekend($timestart);
 256
 257     if (!$courses = get_records('course','','','','id,1')) {
 258         return STATS_RUN_ABORTED;
 259     }
 260
 261     $weeks = 0;
 262     mtrace("starting at $timestart");
 263     while ($sunday > $nextsunday && $timestart < $nextsunday) {
 264
 265         $timesql = " (timeend > $timestart AND timeend < $nextsunday) ";
 266         begin_sql();
 267         foreach ($courses as $course) {
 268
 269             // enrolment first
 270             $sql = 'SELECT roleid, ceil(avg(stat1)) AS stat1, ceil(avg(stat2)) AS stat2
 271                     FROM '.$CFG->prefix.'stats_daily
 272                     WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'enrolments\'
 273                     GROUP BY roleid';
 274
 275             if ($rolestats = get_records_sql($sql)) {
 276                 foreach ($rolestats as $stat) {
 277                     $stat->courseid = $course->id;
 278                     $stat->timeend = $nextsunday;
 279                     $stat->stattype = 'enrolments';
 280
 281                     insert_record('stats_weekly',$stat,false); // don't worry about the return id, we don't need it.
 282                 }
 283             }
 284
 285             // activity
 286             $sql = 'SELECT roleid, sum(stat1) AS stat1, sum(stat2) as stat2
 287                     FROM '.$CFG->prefix.'stats_daily
 288                     WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'activity\'
 289                     GROUP BY roleid';
 290
 291             if ($rolestats = get_records_sql($sql)) {
 292                 foreach ($rolestats as $stat) {
 293                     $stat->courseid = $course->id;
 294                     $stat->timeend = $nextsunday;
 295                     $stat->stattype = 'activity';
 296                     unset($stat->id);
 297
 298                     insert_record('stats_weekly',$stat,false); // don't worry about the return id, we don't need it.
 299                 }
 300             }
 301
 302             // logins
 303             if ($course->id == SITEID) {
 304                 $sql = 'SELECT sum(stat1) AS stat1
 305                     FROM '.$CFG->prefix.'stats_daily
 306                     WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'logins\'';
 307
 308                 if ($stat = get_record_sql($sql)) {
 309                     if (empty($stat->stat1)) {
 310                         $stat->stat1 = 0;
 311                     }
 312                     $stat->courseid = $course->id;
 313                     $stat->roleid = 0;
 314                     $stat->timeend = $nextsunday;
 315                     $stat->stattype = 'logins';
 316                     $sql = 'SELECT COUNT(DISTINCT(l.userid)) FROM '.$CFG->prefix.'log l WHERE l.action = \'login\' AND '
 317                         .str_replace('timeend','time',$timesql);
 318                     $stat->stat2 = count_records_sql($sql);
 319
 320                     insert_record('stats_weekly',$stat,false); // don't worry about the return id, we don't need it.
 321                 }
 322             }
 323
 324             $users = stats_get_course_users($course,$timesql);
 325             foreach ($users as $user) {
 326                 stats_do_aggregate_user_cron($course,$user,$user->primaryrole,$timesql,$nextsunday,'weekly',$weekly_modules);
 327             }
 328         }
 329         stats_do_aggregate_user_login_cron($timesql,$nextsunday,'weekly');
 330         commit_sql();
 331         $timestart = $nextsunday;
 332         $nextsunday = stats_get_next_weekend($nextsunday);
 333         $weeks++;
 334
 335         if (!stats_check_runtime()) {
 336             mtrace("Stopping early! reached maxruntime");
 337             $return = STATS_RUN_ABORTED;
 338             break;
 339         }
 340     }
 341     mtrace("got up to ".$timestart);
 342     mtrace("Completed $weeks weeks");
 343     return $return;
 344 }
 345
 346
 347 function stats_cron_monthly () {
 348     global $CFG;
 349
 350     if (empty($CFG->enablestats)) {
 351         return STATS_RUN_ABORTED;
 352     }
 353
 354     if (!$timestart = stats_get_start_from('monthly')) {
 355         return STATS_RUN_ABORTED;
 356     }
 357
 358     // check to make sure we're due to run, at least one month after last run
 359     $monthend = stats_get_base_monthly();
 360
 361     if (isset($CFG->statslastmonthly) and ((time() - (31*24*60*60)) <= $CFG->statslastmonthly)) {
 362         return STATS_RUN_ABORTED;
 363     }
 364
 365     mtrace("Running monthly statistics gathering...");
 366     set_config('statslastmonthly',time());
 367
 368     $return = STATS_RUN_COMPLETE; // optimistic
 369
 370     static $monthly_modules;
 371
 372     if (empty($monthly_modules)) {
 373         $monthly_modules = array();
 374         $mods = get_records("modules");
 375         foreach ($mods as $mod) {
 376             $file = $CFG->dirroot.'/mod/'.$mod->name.'/lib.php';
 377             if (!is_readable($file)) {
 378                 continue;
 379             }
 380             require_once($file);
 381             $fname = $mod->name.'_get_monthly_stats';
 382             if (function_exists($fname)) {
 383                 $monthly_modules[$mod] = $fname;
 384             }
 385         }
 386     }
 387
 388     $nextmonthend = stats_get_next_monthend($timestart);
 389
 390     if (!$courses = get_records('course','','','','id,1')) {
 391         return STATS_RUN_ABORTED;
 392     }
 393
 394     $months = 0;
 395     mtrace("starting from $timestart");
 396     while ($monthend > $nextmonthend && $timestart < $nextmonthend) {
 397
 398         $timesql = " (timeend > $timestart AND timeend < $nextmonthend) ";
 399         begin_sql();
 400         foreach ($courses as $course) {
 401
 402             // enrolment first
 403             $sql = 'SELECT roleid, ceil(avg(stat1)) AS stat1, ceil(avg(stat2)) AS stat2
 404                     FROM '.$CFG->prefix.'stats_daily
 405                     WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'enrolments\'
 406                     GROUP BY roleid';
 407
 408             if ($rolestats = get_records_sql($sql)) {
 409                 foreach ($rolestats as $stat) {
 410                     $stat->courseid = $course->id;
 411                     $stat->timeend = $nextmonthend;
 412                     $stat->stattype = 'enrolments';
 413
 414                     insert_record('stats_monthly',$stat,false); // don't worry about the return id, we don't need it.
 415                 }
 416             }
 417
 418             // activity
 419             $sql = 'SELECT roleid, sum(stat1) AS stat1, sum(stat2) as stat2
 420                     FROM '.$CFG->prefix.'stats_daily
 421                     WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'activity\'
 422                     GROUP BY roleid';
 423
 424             if ($rolestats = get_records_sql($sql)) {
 425                 foreach ($rolestats as $stat) {
 426                     $stat->courseid = $course->id;
 427                     $stat->timeend = $nextmonthend;
 428                     $stat->stattype = 'activity';
 429                     unset($stat->id);
 430
 431                     insert_record('stats_monthly',$stat,false); // don't worry about the return id, we don't need it.
 432                 }
 433             }
 434
 435             // logins
 436             if ($course->id == SITEID) {
 437                 $sql = 'SELECT sum(stat1) AS stat1
 438                     FROM '.$CFG->prefix.'stats_daily
 439                     WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'logins\'';
 440
 441                 if ($stat = get_record_sql($sql)) {
 442                     if (empty($stat->stat1)) {
 443                         $stat->stat1 = 0;
 444                     }
 445                     $stat->courseid = $course->id;
 446                     $stat->roleid = 0;
 447                     $stat->timeend = $nextmonthend;
 448                     $stat->stattype = 'logins';
 449                     $sql = 'SELECT COUNT(DISTINCT(l.userid)) FROM '.$CFG->prefix.'log l WHERE l.action = \'login\' AND '
 450                         .str_replace('timeend','time',$timesql);
 451                     $stat->stat2 = count_records_sql($sql);
 452
 453                     insert_record('stats_monthly',$stat,false); // don't worry about the return id, we don't need it.
 454                 }
 455             }
 456
 457             $users = stats_get_course_users($course,$timesql);
 458             foreach ($users as $user) {
 459                 stats_do_aggregate_user_cron($course,$user,$user->primaryrole,$timesql,$nextmonthend,'monthly',$monthly_modules);
 460             }
 461
 462         }
 463         stats_do_aggregate_user_login_cron($timesql,$nextmonthend,'monthly');
 464         commit_sql();
 465         $timestart = $nextmonthend;
 466         $nextmonthend = stats_get_next_monthend($timestart);
 467         $months++;
 468         if (!stats_check_runtime()) {
 469             mtrace("Stopping early! reached maxruntime");
 470             break;
 471             $return = STATS_RUN_ABORTED;
 472         }
 473     }
 474     mtrace("got up to $timestart");
 475     mtrace("Completed $months months");
 476     return $return;
 477 }
 478
 479 function stats_get_start_from($str) {
 480     global $CFG;
 481
 482     // if it's not our first run, just return the most recent.
 483     if ($timeend = get_field_sql('SELECT timeend FROM '.$CFG->prefix.'stats_'.$str.' ORDER BY timeend DESC')) {
 484         return $timeend;
 485     }
 486
 487     // decide what to do based on our config setting (either all or none or a timestamp)
 488     $function = 'stats_get_base_'.$str;
 489     switch ($CFG->statsfirstrun) {
 490         case 'all':
 491             return $function(get_field_sql('SELECT time FROM '.$CFG->prefix.'log ORDER BY time'));
 492             break;
 493         case 'none':
 494             return $function(strtotime('-1 day',time()));
 495             break;
 496         default:
 497             if (is_numeric($CFG->statsfirstrun)) {
 498                 return $function(time() - $CFG->statsfirstrun);
 499             }
 500             return false;
 501             break;
 502     }
 503 }
 504
 505 function stats_get_base_daily($time=0) {
 506     if (empty($time)) {
 507         $time = time();
 508     }
 509     return stats_getmidnight($time);
 510 }
 511
 512 function stats_get_base_weekly($time=0) {
 513     if (empty($time)) {
 514         $time = time();
 515     }
 516     // if we're currently a monday, last monday will take us back a week
 517     $str = 'last monday';
 518     if (date('D',$time) == 'Mon')
 519         $str = 'now';
 520
 521     return stats_getmidnight(strtotime($str,$time));
 522 }
 523
 524 function stats_get_base_monthly($time=0) {
 525     if (empty($time)) {
 526         $time = time();
 527     }
 528     return stats_getmidnight(strtotime(date('1-M-Y',$time)));
 529 }
 530
 531 function stats_get_next_monthend($lastmonth) {
 532     return stats_getmidnight(strtotime(date('1-M-Y',$lastmonth).' +1 month'));
 533 }
 534
 535 function stats_get_next_weekend($lastweek) {
 536     return stats_getmidnight(strtotime('+1 week',$lastweek));
 537 }
 538
 539 function stats_get_next_dayend($lastday) {
 540     return stats_getmidnight(strtotime('+1 day',$lastday));
 541 }
 542
 543 function stats_clean_old() {
 544     mtrace("Running stats cleanup tasks... ");
 545     // delete dailies older than 2 months (to be safe)
 546     $deletebefore = stats_get_next_monthend(strtotime('-2 months',time()));
 547     delete_records_select('stats_daily',"timeend < $deletebefore");
 548     delete_records_select('stats_user_daily',"timeend < $deletebefore");
 549
 550     // delete weeklies older than 8 months (to be safe)
 551     $deletebefore = stats_get_next_monthend(strtotime('-8 months',time()));
 552     delete_records_select('stats_weekly',"timeend < $deletebefore");
 553     delete_records_select('stats_user_weekly',"timeend < $deletebefore");
 554
 555     // don't delete monthlies
 556 }
 557
 558 function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) {
 559     global $CFG,$db;
 560     if ($time < 10) { // dailies
 561         // number of days to go back = 7* time
 562         $param->table = 'daily';
 563         $param->timeafter = strtotime("-".($time*7)." days",stats_get_base_daily());
 564     } elseif ($time < 20) { // weeklies
 565         // number of weeks to go back = time - 10 * 4 (weeks) + base week
 566         $param->table = 'weekly';
 567         $param->timeafter = strtotime("-".(($time - 10)*4)." weeks",stats_get_base_weekly());
 568     } else { // monthlies.
 569         // number of months to go back = time - 20 * months + base month
 570         $param->table = 'monthly';
 571         $param->timeafter = strtotime("-".($time - 20)." months",stats_get_base_monthly());
 572     }
 573
 574     $param->extras = '';
 575
 576     // compatibility - if we're in postgres, cast to real for some reports.
 577     $real = '';
 578     if ($CFG->dbfamily == 'postgres') {
 579         $real = '::real';
 580     }
 581
 582     switch ($report) {
 583     // ******************** STATS_MODE_GENERAL ******************** //
 584     case STATS_REPORT_LOGINS:
 585         $param->fields = 'timeend,sum(stat1) as line1,sum(stat2) as line2';
 586         $param->fieldscomplete = true;
 587         $param->stattype = 'logins';
 588         $param->line1 = get_string('statslogins');
 589         $param->line2 = get_string('statsuniquelogins');
 590         if ($courseid == SITEID) {
 591             $param->extras = 'GROUP BY timeend';
 592         }
 593         break;
 594
 595     case STATS_REPORT_READS:
 596         $param->fields = $db->Concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat1 as line1';
 597         $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
 598         $param->aggregategroupby = 'roleid';
 599         $param->stattype = 'activity';
 600         $param->crosstab = true;
 601         $param->extras = 'GROUP BY timeend,roleid,stat1';
 602         if ($courseid == SITEID) {
 603             $param->fields = $db->Concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1) as line1';
 604             $param->extras = 'GROUP BY timeend,roleid';
 605         }
 606         break;
 607
 608     case STATS_REPORT_WRITES:
 609         $param->fields = $db->Concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat2 as line1';
 610         $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
 611         $param->aggregategroupby = 'roleid';
 612         $param->stattype = 'activity';
 613         $param->crosstab = true;
 614         $param->extras = 'GROUP BY timeend,roleid,stat2';
 615         if ($courseid == SITEID) {
 616             $param->fields = $db->Concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat2) as line1';
 617             $param->extras = 'GROUP BY timeend,roleid';
 618         }
 619         break;
 620
 621     case STATS_REPORT_ACTIVITY:
 622         $param->fields = $db->Concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1+stat2) as line1';
 623         $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
 624         $param->aggregategroupby = 'roleid';
 625         $param->stattype = 'activity';
 626         $param->crosstab = true;
 627         $param->extras = 'GROUP BY timeend,roleid';
 628         if ($courseid == SITEID) {
 629             $param->extras = 'GROUP BY timeend,roleid';
 630         }
 631         break;
 632
 633     case STATS_REPORT_ACTIVITYBYROLE;
 634         $param->fields = 'stat1 AS line1, stat2 AS line2';
 635         $param->stattype = 'activity';
 636         $rolename = get_field('role','name','id',$roleid);
 637         $param->line1 = $rolename . get_string('statsreads');
 638         $param->line2 = $rolename . get_string('statswrites');
 639         if ($courseid == SITEID) {
 640             $param->extras = 'GROUP BY timeend';
 641         }
 642         break;
 643
 644     // ******************** STATS_MODE_DETAILED ******************** //
 645     case STATS_REPORT_USER_ACTIVITY:
 646         $param->fields = 'statsreads as line1, statswrites as line2';
 647         $param->line1 = get_string('statsuserreads');
 648         $param->line2 = get_string('statsuserwrites');
 649         $param->stattype = 'activity';
 650         break;
 651
 652     case STATS_REPORT_USER_ALLACTIVITY:
 653         $param->fields = 'statsreads+statswrites as line1';
 654         $param->line1 = get_string('statsuseractivity');
 655         $param->stattype = 'activity';
 656         break;
 657
 658     case STATS_REPORT_USER_LOGINS:
 659         $param->fields = 'statsreads as line1';
 660         $param->line1 = get_string('statsuserlogins');
 661         $param->stattype = 'logins';
 662         break;
 663
 664     case STATS_REPORT_USER_VIEW:
 665         $param->fields = 'statsreads as line1, statswrites as line2, statsreads+statswrites as line3';
 666         $param->line1 = get_string('statsuserreads');
 667         $param->line2 = get_string('statsuserwrites');
 668         $param->line3 = get_string('statsuseractivity');
 669         $param->stattype = 'activity';
 670         break;
 671
 672     // ******************** STATS_MODE_RANKED ******************** //
 673     case STATS_REPORT_ACTIVE_COURSES:
 674         $param->fields = 'sum(stat1+stat2) AS line1';
 675         $param->stattype = 'activity';
 676         $param->orderby = 'line1 DESC';
 677         $param->line1 = get_string('activity');
 678         $param->graphline = 'line1';
 679         break;
 680
 681     case STATS_REPORT_ACTIVE_COURSES_WEIGHTED:
 682         $threshold = 0;
 683         if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
 684             $threshold = $CFG->statsuserthreshold;
 685         }
 686         $param->fields = '';
 687         $param->sql = 'SELECT activity.courseid, activity.all_activity AS line1, enrolments.highest_enrolments AS line2,
 688                         activity.all_activity / enrolments.highest_enrolments as line3
 689                        FROM (
 690                             SELECT courseid, sum(stat1+stat2) AS all_activity
 691                             FROM '.$CFG->prefix.'stats_'.$param->table.'
 692                             WHERE stattype=\'activity\' AND timeend >= '.$param->timeafter.'
 693                             GROUP BY courseid
 694                        ) activity
 695                        INNER JOIN
 696                             (
 697                             SELECT courseid, max(stat1) AS highest_enrolments
 698                             FROM '.$CFG->prefix.'stats_'.$param->table.'
 699                             WHERE stattype=\'enrolments\' AND timeend >= '.$param->timeafter.'
 700                             GROUP BY courseid
 701                       ) enrolments
 702                       ON (activity.courseid = enrolments.courseid)
 703                       WhERE enrolments.highest_enrolments > '.$threshold.'
 704                       ORDER BY line3 DESC';
 705         $param->line1 = get_string('activity');
 706         $param->line2 = get_string('users');
 707         $param->line3 = get_string('activityweighted');
 708         $param->graphline = 'line3';
 709         break;
 710
 711     case STATS_REPORT_PARTICIPATORY_COURSES:
 712         $threshold = 0;
 713         if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
 714             $threshold = $CFG->statsuserthreshold;
 715         }
 716         $param->fields = '';
 717         $param->sql = 'SELECT courseid, ceil(avg(all_enrolments)) as line1,
 718                          ceil(avg(active_enrolments)) as line2, avg(proportion_active) AS line3
 719                        FROM (
 720                            SELECT courseid, timeend, sum(stat2) as active_enrolments,
 721                               sum(stat1) as all_enrolments, sum(stat2)'.$real.'/sum(stat1)'.$real.' as proportion_active
 722                            FROM '.$CFG->prefix.'stats_'.$param->table.' WHERE stattype=\'enrolments\'
 723                            GROUP BY courseid, timeend
 724                            HAVING sum(stat1) > '.$threshold.'
 725                        ) aq
 726                        WHERE timeend >= '.$param->timeafter.'
 727                        GROUP BY courseid
 728                        ORDER BY line3 DESC';
 729
 730         $param->line1 = get_string('users');
 731         $param->line2 = get_string('activeusers');
 732         $param->line3 = get_string('participationratio');
 733         $param->graphline = 'line3';
 734         break;
 735
 736     case STATS_REPORT_PARTICIPATORY_COURSES_RW:
 737         $param->fields = '';
 738         $param->sql =  'SELECT courseid, sum(views) AS line1, sum(posts) AS line2,
 739                            avg(proportion_active) AS line3
 740                          FROM (
 741                            SELECT courseid, timeend,sum(stat1) as views, sum(stat2) AS posts,
 742                             sum(stat2)'.$real.'/sum(stat1)'.$real.' as proportion_active
 743                            FROM '.$CFG->prefix.'stats_'.$param->table.' WHERE stattype=\'activity\'
 744                            GROUP BY courseid, timeend
 745                            HAVING sum(stat1) > 0
 746                        ) aq
 747                        WHERE timeend >= '.$param->timeafter.'
 748                        GROUP BY courseid
 749                        ORDER BY line3 DESC';
 750         $param->line1 = get_string('views');
 751         $param->line2 = get_string('posts');
 752         $param->line3 = get_string('participationratio');
 753         $param->graphline = 'line3';
 754         break;
 755     }
 756
 757     /*
 758     if ($courseid == SITEID && $mode != STATS_MODE_RANKED) { // just aggregate all courses.
 759         $param->fields = preg_replace('/(?:sum)([a-zA-Z0-9+_]*)\W+as\W+([a-zA-Z0-9_]*)/i','sum($1) as $2',$param->fields);
 760         $param->extras = ' GROUP BY timeend'.((!empty($param->aggregategroupby)) ? ','.$param->aggregategroupby : '');
 761     }
 762     */
 763     //TODO must add the SITEID reports to the rest of the reports.
 764     return $param;
 765 }
 766
 767 function stats_get_view_actions() {
 768     return array('view','view all','history');
 769 }
 770
 771 function stats_get_post_actions() {
 772     return array('add','delete','edit','add mod','delete mod','edit section'.'enrol','loginas','new','unenrol','update','update mod');
 773 }
 774
 775 function stats_get_action_sql_in($str) {
 776     global $CFG;
 777
 778     $mods = get_records('modules');
 779     $function = 'stats_get_'.$str.'_actions';
 780     $actions = $function();
 781     foreach ($mods as $mod) {
 782         $file = $CFG->dirroot.'/mod/'.$mod->name.'/lib.php';
 783         if (!is_readable($file)) {
 784             continue;
 785         }
 786         require_once($file);
 787         $function = $mod->name.'_get_'.$str.'_actions';
 788         if (function_exists($function)) {
 789             $actions = array_merge($actions,$function());
 790         }
 791     }
 792     $actions = array_unique($actions);
 793     if (empty($actions)) {
 794         return ' ';
 795     } else if (count($actions) == 1) {
 796         return ' AND l.action = '.array_pop($actions).' ';
 797     } else {
 798         return ' AND l.action IN (\''.implode('\',\'',$actions).'\') ';
 799     }
 800 }
 801
 802
 803 function stats_get_course_users($course,$timesql) {
 804     global $CFG;
 805
 806     $timesql = str_replace('timeend','l.time',$timesql);
 807
 808     $sql = "SELECT userid, primaryrole FROM (
 809                 SELECT active_course_users.userid,
 810                     (SELECT roleid FROM {$CFG->prefix}role_assignments outer_r_a INNER JOIN {$CFG->prefix}role outer_r ON outer_r_a.roleid=outer_r.id
 811                         INNER JOIN {$CFG->prefix}context c ON outer_r_a.contextid = c.id
 812                         WHERE c.instanceid=".$course->id." AND c.contextlevel = ".CONTEXT_COURSE." AND outer_r_a.userid=active_course_users.userid
 813                         AND NOT EXISTS (SELECT 1 FROM {$CFG->prefix}role_assignments inner_r_a
 814                             INNER JOIN {$CFG->prefix}role inner_r ON inner_r_a.roleid = inner_r.id
 815                             WHERE inner_r.sortorder < outer_r.sortorder
 816                             AND inner_r_a.userid = outer_r_a.userid
 817                             AND inner_r_a.contextid = outer_r_a.contextid
 818                         )
 819                     ) AS primaryrole
 820                     FROM (SELECT DISTINCT userid FROM {$CFG->prefix}log l WHERE course=".$course->id." AND ".$timesql." )
 821                     active_course_users
 822                 ) foo WHERE primaryrole IS NOT NULL";
 823     if (!$users = get_records_sql($sql)) {
 824         $users = array();
 825     }
 826
 827     return $users;
 828
 829 }
 830
 831 function stats_do_daily_user_cron($course,$user,$roleid,$timesql,$timeend,$mods) {
 832
 833     global $CFG;
 834
 835     $stat = new StdClass;
 836     $stat->userid   = $user->userid;
 837     $stat->roleid   = $roleid;
 838     $stat->courseid = $course->id;
 839     $stat->stattype = 'activity';
 840     $stat->timeend  = $timeend;
 841
 842     $sql = 'SELECT COUNT(l.id) FROM '.$CFG->prefix.'log l WHERE l.userid = '.$user->userid
 843         .' AND  l.course = '.$course->id
 844         .' AND '.$timesql .' '.stats_get_action_sql_in('view');
 845
 846     $stat->statsreads  = count_records_sql($sql);
 847
 848     $sql = 'SELECT COUNT(l.id) FROM '.$CFG->prefix.'log l WHERE l.userid = '.$user->userid
 849         .' AND l.course = '.$course->id
 850         .' AND '.$timesql.' '.stats_get_action_sql_in('post');
 851
 852     $stat->statswrites = count_records_sql($sql);
 853
 854     insert_record('stats_user_daily',$stat,false);
 855
 856     // now ask the modules if they want anything.
 857     foreach ($mods as $mod => $fname) {
 858         mtrace('  doing daily statistics for '.$mod->name);
 859         $fname($course,$user,$timeend,$roleid);
 860     }
 861 }
 862
 863 function stats_do_aggregate_user_cron($course,$user,$roleid,$timesql,$timeend,$timestr,$mods) {
 864
 865     global $CFG;
 866
 867     $stat = new StdClass;
 868     $stat->userid   = $user->userid;
 869     $stat->roleid   = $roleid;
 870     $stat->courseid = $course->id;
 871     $stat->stattype = 'activity';
 872     $stat->timeend  = $timeend;
 873
 874     $sql = 'SELECT sum(statsreads) as statsreads, sum(statswrites) as statswrites FROM '.$CFG->prefix.'stats_user_daily WHERE courseid = '.$course->id.' AND '.$timesql
 875         ." AND roleid=".$roleid." AND userid = ".$stat->userid." AND stattype='activity'"; // add on roleid in case they have teacher and student records.
 876
 877     $r = get_record_sql($sql);
 878     $stat->statsreads = (empty($r->statsreads)) ? 0 : $r->statsreads;
 879     $stat->statswrites = (empty($r->statswrites)) ? 0 : $r->statswrites;
 880
 881     insert_record('stats_user_'.$timestr,$stat,false);
 882
 883     // now ask the modules if they want anything.
 884     foreach ($mods as $mod => $fname) {
 885         mtrace('  doing '.$timestr.' statistics for '.$mod->name);
 886         $fname($course,$user,$timeend,$roleid);
 887     }
 888 }
 889
 890 function stats_do_aggregate_user_login_cron($timesql,$timeend,$timestr) {
 891     global $CFG;
 892
 893     $sql = 'SELECT userid,roleid,sum(statsreads) as statsreads, sum(statswrites) as writes FROM '.$CFG->prefix.'stats_user_daily WHERE stattype = \'logins\' AND '.$timesql.' GROUP BY userid,roleid';
 894
 895     if ($users = get_records_sql($sql)) {
 896         foreach ($users as $stat) {
 897             $stat->courseid = SITEID;
 898             $stat->timeend = $timeend;
 899             $stat->stattype = 'logins';
 900
 901             insert_record('stats_user_'.$timestr,$stat,false);
 902         }
 903     }
 904 }
 905
 906
 907 function stats_get_time_options($now,$lastweekend,$lastmonthend,$earliestday,$earliestweek,$earliestmonth) {
 908
 909     $now = stats_get_base_daily(time());
 910     // it's really important that it's TIMEEND in the table. ie, tuesday 00:00:00 is monday night.
 911     // so we need to take a day off here (essentially add a day to $now
 912     $now += 60*60*24;
 913
 914     $timeoptions = array();
 915
 916     if ($now - (60*60*24*7) >= $earliestday) {
 917         $timeoptions[STATS_TIME_LASTWEEK] = get_string('numweeks','moodle',1);
 918     }
 919     if ($now - (60*60*24*14) >= $earliestday) {
 920         $timeoptions[STATS_TIME_LAST2WEEKS] = get_string('numweeks','moodle',2);
 921     }
 922     if ($now - (60*60*24*21) >= $earliestday) {
 923         $timeoptions[STATS_TIME_LAST3WEEKS] = get_string('numweeks','moodle',3);
 924     }
 925     if ($now - (60*60*24*28) >= $earliestday) {
 926         $timeoptions[STATS_TIME_LAST4WEEKS] = get_string('numweeks','moodle',4);// show dailies up to (including) here.
 927     }
 928     if ($lastweekend - (60*60*24*56) >= $earliestweek) {
 929         $timeoptions[STATS_TIME_LAST2MONTHS] = get_string('nummonths','moodle',2);
 930     }
 931     if ($lastweekend - (60*60*24*84) >= $earliestweek) {
 932         $timeoptions[STATS_TIME_LAST3MONTHS] = get_string('nummonths','moodle',3);
 933     }
 934     if ($lastweekend - (60*60*24*112) >= $earliestweek) {
 935         $timeoptions[STATS_TIME_LAST4MONTHS] = get_string('nummonths','moodle',4);
 936     }
 937     if ($lastweekend - (60*60*24*140) >= $earliestweek) {
 938         $timeoptions[STATS_TIME_LAST5MONTHS] = get_string('nummonths','moodle',5);
 939     }
 940     if ($lastweekend - (60*60*24*168) >= $earliestweek) {
 941         $timeoptions[STATS_TIME_LAST6MONTHS] = get_string('nummonths','moodle',6); // show weeklies up to (including) here
 942     }
 943     if (strtotime('-7 months',$lastmonthend) >= $earliestmonth) {
 944         $timeoptions[STATS_TIME_LAST7MONTHS] = get_string('nummonths','moodle',7);
 945     }
 946     if (strtotime('-8 months',$lastmonthend) >= $earliestmonth) {
 947         $timeoptions[STATS_TIME_LAST8MONTHS] = get_string('nummonths','moodle',8);
 948     }
 949     if (strtotime('-9 months',$lastmonthend) >= $earliestmonth) {
 950         $timeoptions[STATS_TIME_LAST9MONTHS] = get_string('nummonths','moodle',9);
 951     }
 952     if (strtotime('-10 months',$lastmonthend) >= $earliestmonth) {
 953         $timeoptions[STATS_TIME_LAST10MONTHS] = get_string('nummonths','moodle',10);
 954     }
 955     if (strtotime('-11 months',$lastmonthend) >= $earliestmonth) {
 956         $timeoptions[STATS_TIME_LAST11MONTHS] = get_string('nummonths','moodle',11);
 957     }
 958     if (strtotime('-1 year',$lastmonthend) >= $earliestmonth) {
 959         $timeoptions[STATS_TIME_LASTYEAR] = get_string('lastyear');
 960     }
 961
 962     return $timeoptions;
 963 }
 964
 965 function stats_get_report_options($courseid,$mode) {
 966     global $CFG;
 967
 968     $reportoptions = array();
 969
 970     switch ($mode) {
 971     case STATS_MODE_GENERAL:
 972         $reportoptions[STATS_REPORT_ACTIVITY] = get_string('statsreport'.STATS_REPORT_ACTIVITY);
 973         if ($courseid != SITEID && $context = get_context_instance(CONTEXT_COURSE, $courseid)) {
 974             $sql = 'SELECT r.id,r.name FROM '.$CFG->prefix.'role r JOIN '.$CFG->prefix.'stats_daily s ON s.roleid = r.id WHERE s.courseid = '.$courseid;
 975             if ($roles = get_records_sql($sql)) {
 976                 foreach ($roles as $role) {
 977                     $reportoptions[STATS_REPORT_ACTIVITYBYROLE.$role->id] = get_string('statsreport'.STATS_REPORT_ACTIVITYBYROLE). ' '.$role->name;
 978                 }
 979             }
 980         }
 981         $reportoptions[STATS_REPORT_READS] = get_string('statsreport'.STATS_REPORT_READS);
 982         $reportoptions[STATS_REPORT_WRITES] = get_string('statsreport'.STATS_REPORT_WRITES);
 983         if ($courseid == SITEID) {
 984             $reportoptions[STATS_REPORT_LOGINS] = get_string('statsreport'.STATS_REPORT_LOGINS);
 985         }
 986
 987         break;
 988     case STATS_MODE_DETAILED:
 989         $reportoptions[STATS_REPORT_USER_ACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ACTIVITY);
 990         $reportoptions[STATS_REPORT_USER_ALLACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ALLACTIVITY);
 991         if (has_capability('moodle/site:viewreports', get_context_instance(CONTEXT_SYSTEM, SITEID))) {
 992             $site = get_site();
 993             $reportoptions[STATS_REPORT_USER_LOGINS] = get_string('statsreport'.STATS_REPORT_USER_LOGINS);
 994         }
 995         break;
 996     case STATS_MODE_RANKED:
 997         if (has_capability('moodle/site:viewreports', get_context_instance(CONTEXT_SYSTEM, SITEID))) {
 998             $reportoptions[STATS_REPORT_ACTIVE_COURSES] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES);
 999             $reportoptions[STATS_REPORT_ACTIVE_COURSES_WEIGHTED] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES_WEIGHTED);
1000             $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES);
1001             $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES_RW] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES_RW);
1002         }
1003      break;
1004     }
1005
1006     return $reportoptions;
1007 }
1008
1009 function stats_fix_zeros($stats,$timeafter,$timestr,$line2=true,$line3=false) {
1010
1011     if (empty($stats)) {
1012         return;
1013     }
1014
1015     $timestr = str_replace('user_','',$timestr); // just in case.
1016     $fun = 'stats_get_base_'.$timestr;
1017
1018     $now = $fun();
1019
1020     $times = array();
1021     // add something to timeafter since it is our absolute base
1022     $actualtimes = array();
1023     foreach ($stats as $s) {
1024         $actualtimes[] = $s->timeend;
1025     }
1026
1027     $timeafter = array_pop(array_values($actualtimes));
1028
1029     while ($timeafter < $now) {
1030         $times[] = $timeafter;
1031         if ($timestr == 'daily') {
1032             $timeafter = stats_get_next_dayend($timeafter);
1033         } else if ($timestr == 'weekly') {
1034             $timeafter = stats_get_next_weekend($timeafter);
1035         } else if ($timestr == 'monthly') {
1036             $timeafter = stats_get_next_monthend($timeafter);
1037         } else {
1038             return $stats; // this will put us in a never ending loop.
1039         }
1040     }
1041
1042     foreach ($times as $count => $time) {
1043         if (!in_array($time,$actualtimes) && $count != count($times) -1) {
1044             $newobj = new StdClass;
1045             $newobj->timeend = $time;
1046             $newobj->id = 0;
1047             $newobj->roleid = 0;
1048             $newobj->line1 = 0;
1049             if (!empty($line2)) {
1050                 $newobj->line2 = 0;
1051             }
1052             if (!empty($line3)) {
1053                 $newobj->line3 = 0;
1054             }
1055             $newobj->zerofixed = true;
1056             $stats[] = $newobj;
1057         }
1058     }
1059
1060     usort($stats,"stats_compare_times");
1061     return $stats;
1062
1063 }
1064
1065 // helper function to sort arrays by $obj->timeend
1066 function stats_compare_times($a,$b) {
1067    if ($a->timeend == $b->timeend) {
1068        return 0;
1069    }
1070    return ($a->timeend > $b->timeend) ? -1 : 1;
1071 }
1072
1073 function stats_check_runtime() {
1074     global $CFG;
1075
1076     if (empty($CFG->statsmaxruntime)) {
1077         return true;
1078     }
1079
1080     if ((time() - $CFG->statsrunning) < $CFG->statsmaxruntime) {
1081         return true;
1082     }
1083
1084     return false; // we've gone over!
1085
1086 }
1087
1088 function stats_check_uptodate($courseid=0) {
1089     global $CFG;
1090
1091     if (empty($courseid)) {
1092         $courseid = SITEID;
1093     }
1094
1095     $latestday = stats_get_start_from('daily');
1096
1097     if ((time() - 60*60*24*2) < $latestday) { // we're ok
1098         return NULL;
1099     }
1100
1101     $a = new object();
1102     $a->daysdone = get_field_sql("SELECT count(distinct(timeend)) from {$CFG->prefix}stats_daily");
1103
1104     // how many days between the last day and now?
1105     $a->dayspending = ceil((stats_get_base_daily() - $latestday)/(60*60*24));
1106
1107     if ($a->dayspending == 0 && $a->daysdone != 0) {
1108         return NULL; // we've only just started...
1109     }
1110
1111     //return error as string
1112     return get_string('statscatchupmode','error',$a);
1113 }
1114
1115
1116 // copied from usergetmidnight, but we ignore dst
1117 function stats_getmidnight($date, $timezone=99) {
1118     $timezone = get_user_timezone_offset($timezone);
1119     $userdate = getdate($date);
1120     return make_timestamp($userdate['year'], $userdate['mon'], $userdate['mday'], 0, 0, 0, $timezone,false ); // ignore dst for this.
1121 }
1122
1123 function stats_getdate($time, $timezone=99) {
1124
1125     $timezone = get_user_timezone_offset($timezone);
1126
1127     if (abs($timezone) > 13) {    // Server time
1128         return getdate($time);
1129     }
1130
1131     // There is no gmgetdate so we use gmdate instead
1132     $time += intval((float)$timezone * HOURSECS);
1133     $datestring = strftime('%S_%M_%H_%d_%m_%Y_%w_%j_%A_%B', $time);
1134     list(
1135         $getdate['seconds'],
1136         $getdate['minutes'],
1137         $getdate['hours'],
1138         $getdate['mday'],
1139         $getdate['mon'],
1140         $getdate['year'],
1141         $getdate['wday'],
1142         $getdate['yday'],
1143         $getdate['weekday'],
1144         $getdate['month']
1145     ) = explode('_', $datestring);
1146
1147     return $getdate;
1148 }
1149
1150
1151 function stats_upgrade_for_roles_wrapper() {
1152     global $CFG;
1153     if (!empty($CFG->statsrolesupgraded)) {
1154         return true;
1155     }
1156
1157     $result = begin_sql();
1158
1159     $result = $result && stats_upgrade_user_table_for_roles('daily');
1160     $result = $result && stats_upgrade_user_table_for_roles('weekly');
1161     $result = $result && stats_upgrade_user_table_for_roles('monthly');
1162
1163     $result = $result && stats_upgrade_table_for_roles('daily');
1164     $result = $result && stats_upgrade_table_for_roles('weekly');
1165     $result = $result && stats_upgrade_table_for_roles('monthly');
1166
1167
1168     $result = $result && commit_sql();
1169
1170     if (!empty($result)) {
1171         set_config('statsrolesupgraded',time());
1172     }
1173
1174     return $result;
1175 }
1176
1177 /**
1178  * Upgrades a prefix_stats_user_* table for the new role based permission
1179  * system.
1180  *
1181  * @param string $period  daily, weekly or monthly: the stat period to upgrade
1182  * @return boolean @todo maybe something else (error message) depending on
1183  * how this will be called.
1184  */
1185 function stats_upgrade_user_table_for_roles ($period) {
1186     global $CFG;
1187     static $teacher_role_id, $student_role_id;
1188
1189     if (!in_array($period, array('daily', 'weekly', 'monthly'))) {
1190         error_log('stats upgrade:  invalid period: ' . $period);
1191         return false;
1192     }
1193
1194     if (!$teacher_role_id) {
1195         $role            = get_roles_with_capability('moodle/legacy:editingteacher', CAP_ALLOW);
1196         $role            = array_keys($role);
1197         $teacher_role_id = $role[0];
1198         $role            = get_roles_with_capability('moodle/legacy:student', CAP_ALLOW);
1199         $role            = array_keys($role);
1200         $student_role_id = $role[0];
1201     }
1202
1203     if (empty($teacher_role_id) || empty($student_role_id)) {
1204         error_log("Couldn't find legacy roles for teacher or student");
1205         return false;
1206     }
1207
1208     $status = true;
1209
1210     $status = $status && execute_sql("UPDATE {$CFG->prefix}stats_user_{$period}
1211         SET roleid = $teacher_role_id
1212         WHERE roleid = 1");
1213     $status = $status && execute_sql("UPDATE {$CFG->prefix}stats_user_{$period}
1214         SET roleid = $student_role_id
1215         WHERE roleid = 2");
1216
1217     return $status;
1218 }
1219
1220 /**
1221  * Upgrades a prefix_stats_* table for the new role based permission system.
1222  *
1223  * @param string $period  daily, weekly or monthly: the stat period to upgrade
1224  * @return boolean        @todo depends on how this will be called
1225  */
1226 function stats_upgrade_table_for_roles ($period) {
1227     global $CFG;
1228     static $teacher_role_id, $student_role_id;
1229
1230     if (!in_array($period, array('daily', 'weekly', 'monthly'))) {
1231         return false;
1232     }
1233
1234     if (!$teacher_role_id) {
1235         $role            = get_roles_with_capability('moodle/legacy:teacher', CAP_ALLOW);
1236         $role            = array_keys($role);
1237         $teacher_role_id = $role[0];
1238         $role            = get_roles_with_capability('moodle/legacy:student', CAP_ALLOW);
1239         $role            = array_keys($role);
1240         $student_role_id = $role[0];
1241     }
1242
1243     if (empty($teacher_role_id) || empty($student_role_id)) {
1244         error_log("Couldn't find legacy roles for teacher or student");
1245         return false;
1246     }
1247
1248     execute_sql("CREATE TABLE {$CFG->prefix}stats_{$period}_tmp AS
1249         SELECT * FROM {$CFG->prefix}stats_{$period}");
1250
1251     $table = new XMLDBTable('stats_' . $period);
1252     if (!drop_table($table)) {
1253         return false;
1254     }
1255
1256     // Create a new stats table
1257     // @todo this definition I have made blindly by looking at how definitions are
1258     // made, it needs work to make sure it works properly
1259     require_once("$CFG->libdir/xmldb/classes/XMLDBTable.class.php");
1260
1261     $table = new XMLDBTable('stats_' . $period);
1262     $table->addFieldInfo('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1263         XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1264
1265     $table->addFieldInfo('courseid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1266         XMLDB_NOTNULL, null, null, null, null);
1267
1268     $table->addFieldInfo('roleid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1269         XMLDB_NOTNULL, null, null, null, null);
1270     $table->addFieldInfo('timeend', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1271         XMLDB_NOTNULL, null, null, null, null);
1272     $table->addFieldInfo('stattype', XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL,
1273         null, XMLDB_ENUM, array('enrolments', 'activity', 'logins'), 'activity');
1274     $table->addFieldInfo('stat1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1275         XMLDB_NOTNULL, null, null, null, null);
1276     $table->addFieldInfo('stat2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1277         XMLDB_NOTNULL, null, null, null, null);
1278
1279     /// Adding keys to table stats_daily
1280     $table->addKeyInfo('primary', XMLDB_KEY_PRIMARY, array('id'));
1281
1282     /// Adding indexes to table stats_daily
1283     $table->addIndexInfo('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid'));
1284     $table->addIndexInfo('timeend', XMLDB_INDEX_NOTUNIQUE, array('timeend'));
1285     $table->addIndexInfo('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid'));
1286
1287     if (!create_table($table)) {
1288         return false;
1289     }
1290
1291     //
1292     // Now insert the data from the temporary table into the new one
1293     //
1294
1295     // Student enrolments
1296     execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
1297        (courseid, roleid, timeend, stattype, stat1, stat2)
1298        SELECT courseid, $student_role_id, timeend, 'enrolments', students, activestudents
1299        FROM {$CFG->prefix}stats_{$period}_tmp");
1300
1301     // Teacher enrolments
1302     execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
1303        (courseid, roleid, timeend, stattype, stat1, stat2)
1304        SELECT courseid, $teacher_role_id, timeend, 'enrolments', teachers, activeteachers
1305        FROM {$CFG->prefix}stats_{$period}_tmp");
1306
1307     // Student activity
1308     execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
1309        (courseid, roleid, timeend, stattype, stat1, stat2)
1310        SELECT courseid, $student_role_id, timeend, 'activity', studentreads, studentwrites
1311        FROM {$CFG->prefix}stats_{$period}_tmp");
1312
1313     // Teacher activity
1314     execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
1315        (courseid, roleid, timeend, stattype, stat1, stat2)
1316        SELECT courseid, $teacher_role_id, timeend, 'activity', teacherreads, teacherwrites
1317        FROM {$CFG->prefix}stats_{$period}_tmp");
1318
1319     // Logins
1320     execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
1321        (courseid, roleid, timeend, stattype, stat1, stat2)
1322        SELECT courseid, 0, timeend, 'logins', logins, uniquelogins
1323        FROM {$CFG->prefix}stats_{$period}_tmp WHERE courseid = ".SITEID);
1324
1325     // Drop the temporary table
1326     $table = new XMLDBTable('stats_' . $period . '_tmp');
1327     if (!drop_table($table)) {
1328         return false;
1329     }
1330
1331     return true;
1332 }
1333
1334 ?>