From 61460dd63198894b8c3add726fb288791056313e Mon Sep 17 00:00:00 2001 From: skodak Date: Sat, 16 Feb 2008 18:33:50 +0000 Subject: [PATCH] MDL-13192 major stats cleanup patch - see tracker for more details; merged from MOODLE_19_STABLE --- admin/cron.php | 45 +- admin/report/courseoverview/index.php | 15 +- admin/report/courseoverview/reportsgraph.php | 2 +- admin/settings/server.php | 9 +- course/report/stats/graph.php | 46 +- course/report/stats/report.php | 44 +- course/user.php | 8 +- lang/en_utf8/admin.php | 5 +- lib/adminlib.php | 47 +- lib/db/install.xml | 13 +- lib/db/upgrade.php | 65 + lib/dmllib.php | 2 +- lib/moodlelib.php | 23 + lib/statslib.php | 1518 +++++++++++------- theme/standard/styles_layout.css | 11 +- version.php | 2 +- 16 files changed, 1123 insertions(+), 732 deletions(-) diff --git a/admin/cron.php b/admin/cron.php index 2783d182ae..43f9dca636 100644 --- a/admin/cron.php +++ b/admin/cron.php @@ -430,47 +430,20 @@ } if (!empty($CFG->enablestats) and empty($CFG->disablestatsprocessing)) { - + require_once($CFG->dirroot.'/lib/statslib.php'); // check we're not before our runtime - $timetocheck = strtotime("today $CFG->statsruntimestarthour:$CFG->statsruntimestartminute"); + $timetocheck = stats_get_base_daily() + $CFG->statsruntimestarthour*60*60 + $CFG->statsruntimestartminute*60; if (time() > $timetocheck) { - $time = 60*60*20; // set it to 20 here for first run... (overridden by $CFG) - $clobber = true; - if (!empty($CFG->statsmaxruntime)) { - $time = $CFG->statsmaxruntime+(60*30); // add on half an hour just to make sure (it could take that long to break out of the loop) - } - if (!get_field_sql('SELECT id FROM '.$CFG->prefix.'stats_daily')) { - // first run, set another lock. we'll check for this in subsequent runs to set the timeout to later for the normal lock. - set_cron_lock('statsfirstrunlock',true,$time,true); - $firsttime = true; - } - $time = 60*60*2; // this time set to 2.. (overridden by $CFG) - if (!empty($CFG->statsmaxruntime)) { - $time = $CFG->statsmaxruntime+(60*30); // add on half an hour to make sure (it could take that long to break out of the loop) - } - if ($config = get_record('config','name','statsfirstrunlock')) { - if (!empty($config->value)) { - $clobber = false; // if we're on the first run, just don't clobber it. - } - } - if (set_cron_lock('statsrunning',true,$time, $clobber)) { - require_once($CFG->dirroot.'/lib/statslib.php'); - $return = stats_cron_daily(); - if (stats_check_runtime() && $return == STATS_RUN_COMPLETE) { - stats_cron_weekly(); - } - if (stats_check_runtime() && $return == STATS_RUN_COMPLETE) { - $return = $return && stats_cron_monthly(); - } - if (stats_check_runtime() && $return == STATS_RUN_COMPLETE) { - stats_clean_old(); - } - set_cron_lock('statsrunning',false); - if (!empty($firsttime)) { - set_cron_lock('statsfirstrunlock',false); + // process max 31 days per cron execution + if (stats_cron_daily(31)) { + if (stats_cron_weekly()) { + if (stats_cron_monthly()) { + stats_clean_old(); + } } } + @set_time_limit(0); } } diff --git a/admin/report/courseoverview/index.php b/admin/report/courseoverview/index.php index 06f52c9cd9..b4651036c4 100644 --- a/admin/report/courseoverview/index.php +++ b/admin/report/courseoverview/index.php @@ -18,7 +18,6 @@ redirect("$CFG->wwwroot/$CFG->admin/settings.php?section=stats", get_string('mustenablestats', 'admin'), 3); } - $course = get_site(); stats_check_uptodate($course->id); @@ -48,7 +47,7 @@ } echo '
'."\n"; - echo '
'; + echo '
'; $table->width = '*'; $table->align = array('left','left','left','left','left','left'); @@ -58,17 +57,19 @@ '') ; print_table($table); - echo '
'; + echo ''; echo '
'; + print_heading($reportoptions[$report]); + + if (!empty($report) && !empty($time)) { $param = stats_get_parameters($time,$report,SITEID,STATS_MODE_RANKED); - if (!empty($param->sql)) { $sql = $param->sql; } else { $sql = "SELECT courseid,".$param->fields." FROM ".$CFG->prefix.'stats_'.$param->table - ." WHERE timeend >= ".$param->timeafter.' AND stattype = \'activity\'' + ." WHERE timeend >= $param->timeafter AND stattype = 'activity' AND roleid = 0" ." GROUP BY courseid " .$param->extras ." ORDER BY ".$param->orderby; @@ -82,9 +83,9 @@ } else { if (empty($CFG->gdversion)) { - echo '
(' . get_string("gdneed") .')
'; + echo '
(' . get_string("gdneed") .')
'; } else { - echo '
'.get_string('courseoverviewgraph').'
'; + echo '
'.get_string('courseoverviewgraph').'
'; } $table = new StdClass; diff --git a/admin/report/courseoverview/reportsgraph.php b/admin/report/courseoverview/reportsgraph.php index f59b77afb9..a03e2d4e2d 100644 --- a/admin/report/courseoverview/reportsgraph.php +++ b/admin/report/courseoverview/reportsgraph.php @@ -20,7 +20,7 @@ $sql = $param->sql; } else { $sql = "SELECT courseid,".$param->fields." FROM ".$CFG->prefix.'stats_'.$param->table - ." WHERE timeend >= ".$param->timeafter.' AND stattype = \'activity\'' + ." WHERE timeend >= $param->timeafter AND stattype = 'activity' AND roleid = 0" ." GROUP BY courseid " .$param->extras ." ORDER BY ".$param->orderby; diff --git a/admin/settings/server.php b/admin/settings/server.php index ad811870c5..67e0c5380a 100644 --- a/admin/settings/server.php +++ b/admin/settings/server.php @@ -123,7 +123,9 @@ $temp->add(new admin_setting_configselect('statsfirstrun', get_string('statsfirs 60*60*24*140 => get_string('nummonths','moodle',5), 60*60*24*168 => get_string('nummonths','moodle',6), 'all' => get_string('all') ))); -$temp->add(new admin_setting_configselect('statsmaxruntime', get_string('statsmaxruntime', 'admin'), get_string('configstatsmaxruntime', 'admin'), 0, array(0 => get_string('untilcomplete'), +$temp->add(new admin_setting_configselect('statsmaxruntime', get_string('statsmaxruntime', 'admin'), get_string('configstatsmaxruntime2', 'admin'), 0, array(0 => get_string('untilcomplete'), + 60*30 => '10 '.get_string('minutes'), + 60*30 => '30 '.get_string('minutes'), 60*60 => '1 '.get_string('hour'), 60*60*2 => '2 '.get_string('hours'), 60*60*3 => '3 '.get_string('hours'), @@ -134,6 +136,9 @@ $temp->add(new admin_setting_configselect('statsmaxruntime', get_string('statsma 60*60*8 => '8 '.get_string('hours') ))); $temp->add(new admin_setting_configtime('statsruntimestarthour', 'statsruntimestartminute', get_string('statsruntimestart', 'admin'), get_string('configstatsruntimestart', 'admin'), array('h' => 0, 'm' => 0))); $temp->add(new admin_setting_configtext('statsuserthreshold', get_string('statsuserthreshold', 'admin'), get_string('configstatsuserthreshold', 'admin'), 0, PARAM_INT)); + +$options = array(0=>0, 1=>1, 2=>2, 3=>3, 4=>4, 5=>5, 6=>6); +$temp->add(new admin_setting_configselect('statscatdepth', get_string('statscatdepth', 'admin'), get_string('configstatscatdepth', 'admin'), 1, $options)); $ADMIN->add('server', $temp); @@ -189,7 +194,7 @@ $temp->add(new admin_setting_configselect('loglifetime', get_string('loglifetime 120 => get_string('numdays', '', 120), 90 => get_string('numdays', '', 90), 60 => get_string('numdays', '', 60), - 30 => get_string('numdays', '', 30)))); + 35 => get_string('numdays', '', 35)))); $temp->add(new admin_setting_configcheckbox('disablegradehistory', get_string('disablegradehistory', 'grades'), diff --git a/course/report/stats/graph.php b/course/report/stats/graph.php index 1c1c18c09d..c47fed29b3 100644 --- a/course/report/stats/graph.php +++ b/course/report/stats/graph.php @@ -60,8 +60,6 @@ $graph->parameter['title'] = false; // moodle will do a nicer job. $graph->y_tick_labels = null; - $c = array_keys($graph->colour); - if (empty($param->crosstab)) { foreach ($stats as $stat) { $graph->x_data[] = userdate($stat->timeend,get_string('strftimedate'),$CFG->timezone); @@ -74,16 +72,17 @@ } } $graph->y_order = array('line1'); - $graph->y_format['line1'] = array('colour' => $c[1],'line' => 'line','legend' => $param->line1); + $graph->y_format['line1'] = array('colour' => 'blue','line' => 'line','legend' => $param->line1); if (!empty($param->line2)) { $graph->y_order[] = 'line2'; - $graph->y_format['line2'] = array('colour' => $c[2],'line' => 'line','legend' => $param->line2); + $graph->y_format['line2'] = array('colour' => 'green','line' => 'line','legend' => $param->line2); } if (!empty($param->line3)) { $graph->y_order[] = 'line3'; - $graph->y_format['line3'] = array('colour' => $c[3],'line' => 'line','legend' => $param->line3); + $graph->y_format['line3'] = array('colour' => 'red','line' => 'line','legend' => $param->line3); } $graph->y_tick_labels = false; + } else { $data = array(); $times = array(); @@ -98,6 +97,10 @@ if (!array_key_exists($stat->roleid,$roles)) { $roles[$stat->roleid] = get_field('role','name','id',$stat->roleid); } + } else { + if (!array_key_exists($stat->roleid,$roles)) { + $roles[$stat->roleid] = get_string('all'); + } } if (!array_key_exists($stat->timeend,$times)) { $times[$stat->timeend] = userdate($stat->timeend,get_string('strftimedate'),$CFG->timezone); @@ -110,27 +113,30 @@ } } } - foreach ($data as $role => $stuff) { - ksort($data[$role]); - } - $nonzeroroleid = 0; - foreach (array_keys($data) as $roleid) { - if ($roleid == 0) { - continue; + + $roleid = 0; + krsort($roles); // the same sorting as in table bellow graph + + $colors = array('green', 'blue', 'red', 'purple', 'yellow', 'olive', 'navy', 'maroon', 'gray', 'ltred', 'ltltred', 'ltgreen', 'ltltgreen', 'orange', 'ltorange', 'ltltorange', 'lime', 'ltblue', 'ltltblue', 'fuchsia', 'aqua', 'grayF0', 'grayEE', 'grayDD', 'grayCC', 'gray33', 'gray66', 'gray99'); + $colorindex = 0; + + foreach ($roles as $roleid=>$rname) { + ksort($data[$roleid]); + $graph->y_order[] = $roleid+1; + if ($roleid) { + $color = $colors[$colorindex++]; + $colorindex = $colorindex % count($colors); + } else { + $color = 'black'; } - $graph->y_order[] = $roleid; - $graph->y_format[$roleid] = array('colour' => $c[$roleid], 'line' => 'line','legend' => $roles[$roleid]); - $nonzeroroleid = $roleid; + $graph->y_format[$roleid+1] = array('colour' => $color, 'line' => 'line','legend' => $rname); } - foreach (array_keys($data[$nonzeroroleid]) as $time) { + foreach (array_keys($data[$roleid]) as $time) { $graph->x_data[] = $times[$time]; } foreach ($data as $roleid => $t) { - if ($roleid == 0) { - continue; - } foreach ($t as $time => $data) { - $graph->y_data[$roleid][] = $data; + $graph->y_data[$roleid+1][] = $data; } } } diff --git a/course/report/stats/report.php b/course/report/stats/report.php index 1f03ace798..3ad6853b98 100644 --- a/course/report/stats/report.php +++ b/course/report/stats/report.php @@ -17,26 +17,25 @@ error(get_string('nostatstodisplay'), $CFG->wwwroot.'/course/view.php?id='.$course->id); } - $table->width = '*'; + $table->width = 'auto'; if ($mode == STATS_MODE_DETAILED) { $param = stats_get_parameters($time,null,$course->id,$mode); // we only care about the table and the time string (if we have time) - $sql = 'SELECT DISTINCT s.userid,s.roleid,r.name AS rolename,r.sortorder,u.firstname,u.lastname,u.idnumber + $sql = 'SELECT DISTINCT s.userid, u.firstname, u.lastname, u.idnumber FROM '.$CFG->prefix.'stats_user_'.$param->table.' s JOIN '.$CFG->prefix.'user u ON u.id = s.userid - JoIN '.$CFG->prefix.'role r ON s.roleid = r.id WHERE courseid = '.$course->id . ((!empty($param->stattype)) ? ' AND stattype = \''.$param->stattype.'\'' : '') . ((!empty($time)) ? ' AND timeend >= '.$param->timeafter : '') - .' ORDER BY r.sortorder'; + .' ORDER BY u.lastname, u.firstname ASC'; if (!$us = get_records_sql($sql)) { error('Cannot enter detailed view: No users found for this course.'); } foreach ($us as $u) { - $users[$u->userid] = $u->rolename.' - '.fullname($u,true); + $users[$u->userid] = fullname($u, true); } $table->align = array('left','left','left','left','left','left','left','left'); @@ -59,24 +58,25 @@ } echo '
'."\n" - .'
'."\n" + .'
'."\n" .''."\n"; print_table($table); - echo '
'; + echo ''; echo '
'; if (!empty($report) && !empty($time)) { if ($report == STATS_REPORT_LOGINS && $course->id != SITEID) { error('This type of report is only available for the site course'); } - $timesql = + $param = stats_get_parameters($time,$report,$course->id,$mode); if ($mode == STATS_MODE_DETAILED) { $param->table = 'user_'.$param->table; } + if (!empty($param->sql)) { $sql = $param->sql; } else { @@ -109,16 +109,22 @@ echo "(".get_string("gdneed").")"; } else { if ($mode == STATS_MODE_DETAILED) { - echo '
'.get_string('statisticsgraph').'
'; + echo '
'.get_string('statisticsgraph').''.get_string('statisticsgraph').''; + echo '
'.get_string('statisticsgraph').'
'; } } $table = new StdClass; $table->align = array('left','center','center','center'); $param->table = str_replace('user_','',$param->table); - $table->head = array(get_string('periodending','moodle',$param->table)); + switch ($param->table) { + case 'daily' : $period = get_string('day'); break; + case 'weekly' : $period = get_string('week'); break; + case 'monthly': $period = get_string('month', 'form'); break; + default : $period = ''; + } + $table->head = array(get_string('periodending','moodle',$period)); if (empty($param->crosstab)) { $table->head[] = $param->line1; if (!empty($param->line2)) { @@ -133,8 +139,8 @@ } if (empty($CFG->loglifetime) || ($stat->timeend-(60*60*24)) >= (time()-60*60*24*$CFG->loglifetime)) { $a[] = '' + $course->id.'&chooselog=1&showusers=1&showcourses=1&user=' + .$userid.'&date='.usergetmidnight($stat->timeend-(60*60*24)).'">' .get_string('course').' ' .get_string('logs').' '; } $table->data[] = $a; @@ -153,18 +159,20 @@ if (!array_key_exists($stat->roleid,$roles)) { $roles[$stat->roleid] = get_field('role','name','id',$stat->roleid); } + } else { + if (!array_key_exists($stat->roleid,$roles)) { + $roles[$stat->roleid] = get_string('all'); + } } if (!array_key_exists($stat->timeend,$times)) { $times[$stat->timeend] = userdate($stat->timeend,get_string('strftimedate'),$CFG->timezone); } } + foreach ($data as $time => $rolesdata) { if (in_array($time,$missedlines)) { $rolesdata = array(); foreach ($roles as $roleid => $guff) { - if ($roleid == 0 ) { - continue; - } $rolesdata[$roleid] = 0; } } @@ -179,8 +187,8 @@ $row = array_merge(array($times[$time]),$rolesdata); if (empty($CFG->loglifetime) || ($stat->timeend-(60*60*24)) >= (time()-60*60*24*$CFG->loglifetime)) { $row[] = '' + .$course->id.'&chooselog=1&showusers=1&showcourses=1&user='.$userid + .'&date='.usergetmidnight($time-(60*60*24)).'">' .get_string('course').' ' .get_string('logs').' '; } $table->data[] = $row; diff --git a/course/user.php b/course/user.php index 320284abfa..0707f9f337 100644 --- a/course/user.php +++ b/course/user.php @@ -161,7 +161,13 @@ $table = new object(); $table->align = array('left','center','center','center'); $param->table = str_replace('user_','',$param->table); - $table->head = array(get_string('periodending','moodle',$param->table),$param->line1,$param->line2,$param->line3); + switch ($param->table) { + case 'daily' : $period = get_string('day'); break; + case 'weekly' : $period = get_string('week'); break; + case 'monthly': $period = get_string('month', 'form'); break; + default : $period = ''; + } + $table->head = array(get_string('periodending','moodle',$period),$param->line1,$param->line2,$param->line3); foreach ($stats as $stat) { if (!empty($stat->zerofixed)) { // Don't know why this is necessary, see stats_fix_zeros above - MD continue; diff --git a/lang/en_utf8/admin.php b/lang/en_utf8/admin.php index 2b36ab512a..dedeabdb94 100644 --- a/lang/en_utf8/admin.php +++ b/lang/en_utf8/admin.php @@ -217,9 +217,11 @@ $string['configsmtpmaxbulk'] = 'Maximum number of messages sent per SMTP session $string['configsmtphosts'] = 'Give the full name of one or more local SMTP servers that Moodle should use to send mail (eg \'mail.a.com\' or \'mail.a.com;mail.b.com\'). If you leave it blank, Moodle will use the PHP default method of sending mail.'; $string['configsmtpuser'] = 'If you have specified an SMTP server above, and the server requires authentication, then enter the username and password here.'; $string['configstartwday'] = 'Start of Week'; +$string['configstatscatdepth'] = 'Statistics code uses simplified course enrolment logic, overrides are ignored and there is a maximum number of verified parent course categories. Number 0 means detect only direct role assignments on site and course level, 1 means detect also role assignments in parent category of course, etc. Higher numbers result in much higher database server load during stats processing.'; $string['configstatsfirstrun'] = 'This specifies how far back the logs should be processed the first time the cronjob wants to process statistics. If you have a lot of traffic and are on shared hosting, it\'s probably not a good idea to go too far back, as it could take a long time to run and be quite resource intensive. (Note that for this setting, 1 month = 28 days. In the graphs and reports generated, 1 month = 1 calendar month.)'; $string['configstatsmaxruntime'] = 'Stats processing can be quite intensive, so use a combination of this field and the next one to specify when it will run and how long for.'; -$string['configstatsruntimestart'] = 'What time should the cronjob that does the stats processing start?'; +$string['configstatsmaxruntime2'] = 'Stats processing can be quite intensive, specify maximum time allowed for gathering of one day of statistics. Maximum number of days processed in one cron execution is 3.'; +$string['configstatsruntimestart'] = 'What time should the cronjob that does the stats processing start? Please specify different times if there are multiple Moodles on one physical server.'; $string['configstatsuserthreshold'] = 'If you enter a non-zero, non numeric value here, for ranking courses, courses with less than this number of enrolled users (all roles) will be ignored'; $string['configsupportemail'] = 'This email address will be published to users of this site as the one to email when they need general help (for example, when new users create their own accounts). If this email is left blank then no such helpful email address is supplied.'; $string['configsupportname'] = 'This is the name of a person or other entity offering general help via the support email or web address.'; @@ -631,6 +633,7 @@ $string['smtpmaxbulk'] = 'SMTP session limit'; $string['smtppass'] = 'SMTP password'; $string['smtpuser'] = 'SMTP username'; $string['stats'] = 'Statistics'; +$string['statscatdepth'] = 'Maximum parent categories'; $string['statsfirstrun'] = 'Maximum processing interval'; $string['statsmaxruntime'] = 'Maximum runtime'; $string['statsruntimestart'] = 'Run at'; diff --git a/lib/adminlib.php b/lib/adminlib.php index 78afad3a16..b4462b8717 100644 --- a/lib/adminlib.php +++ b/lib/adminlib.php @@ -499,45 +499,36 @@ function upgrade_activity_modules($return) { } /** - * This function will return FALSE if the lock fails to be set (ie, if it's already locked) + * Try to obtain or release the cron lock. * - * @param string $name ? - * @param bool $value ? - * @param int $staleafter ? - * @param bool $clobberstale ? - * @todo Finish documenting this function + * @param string $name name of lock + * @param int $until timestamp when this lock considered stale, null means remove lock unconditionaly + * @param bool $ignorecurrent ignore current lock state, usually entend previous lock + * @return bool true if lock obtained */ -function set_cron_lock($name,$value=true,$staleafter=7200,$clobberstale=false) { - +function set_cron_lock($name, $until, $ignorecurrent=false) { if (empty($name)) { - mtrace("Tried to get a cron lock for a null fieldname"); + debugging("Tried to get a cron lock for a null fieldname"); return false; } - if (empty($value)) { - set_config($name,0); + // remove lock by force == remove from config table + if (is_null($until)) { + set_config($name, null); return true; } - if ($config = get_record('config','name',$name)) { - if (empty($config->value)) { - set_config($name,time()); - } else { - // check for stale. - if ((time() - $staleafter) > $config->value) { - mtrace("STALE LOCKFILE FOR $name - was $config->value"); - if (!empty($clobberstale)) { - set_config($name,time()); - return true; - } - } else { - return false; // was not stale - ie, we're ok to still be running. - } + if (!$ignorecurrent) { + // read value from db - other processes might have changed it + $value = get_field('config', 'value', 'name', $name); + + if ($value and $value > time()) { + //lock active + return false; } } - else { - set_config($name,time()); - } + + set_config($name, $until); return true; } diff --git a/lib/db/install.xml b/lib/db/install.xml index 9aeee707fa..87cc0353c9 100644 --- a/lib/db/install.xml +++ b/lib/db/install.xml @@ -1,5 +1,5 @@ - @@ -285,11 +285,10 @@ - - - - - + + + + @@ -1681,4 +1680,4 @@ - + \ No newline at end of file diff --git a/lib/db/upgrade.php b/lib/db/upgrade.php index d479695bf5..3a6d74a872 100644 --- a/lib/db/upgrade.php +++ b/lib/db/upgrade.php @@ -2761,6 +2761,71 @@ function xmldb_main_upgrade($oldversion=0) { } + if ($result && $oldversion < 2007101508.002) { + // upgade totals, no big deal if it fails + require_once($CFG->libdir.'/statslib.php'); + stats_upgrade_totals(); + + if (isset($CFG->loglifetime) and $CFG->loglifetime == 30) { + set_config('loglifetime', 35); // we need more than 31 days for monthly stats! + } + + notify('Upgrading log table indexes, this may take a long time, please be patient.', 'notifysuccess'); + + /// Define index time-course-module-action (not unique) to be dropped form log + $table = new XMLDBTable('log'); + $index = new XMLDBIndex('time-course-module-action'); + $index->setAttributes(XMLDB_INDEX_NOTUNIQUE, array('time', 'course', 'module', 'action')); + + /// Launch drop index time-course-module-action + if (index_exists($table, $index)) { + $result = drop_index($table, $index) && $result; + } + + /// Define index userid (not unique) to be dropped form log + $table = new XMLDBTable('log'); + $index = new XMLDBIndex('userid'); + $index->setAttributes(XMLDB_INDEX_NOTUNIQUE, array('userid')); + + /// Launch drop index userid + if (index_exists($table, $index)) { + $result = drop_index($table, $index) && $result; + } + + /// Define index info (not unique) to be dropped form log + $table = new XMLDBTable('log'); + $index = new XMLDBIndex('info'); + $index->setAttributes(XMLDB_INDEX_NOTUNIQUE, array('info')); + + /// Launch drop index info + if (index_exists($table, $index)) { + $result = drop_index($table, $index) && $result; + } + + /// Define index time (not unique) to be added to log + $table = new XMLDBTable('log'); + $index = new XMLDBIndex('time'); + $index->setAttributes(XMLDB_INDEX_NOTUNIQUE, array('time')); + + /// Launch add index time + if (!index_exists($table, $index)) { + $result = add_index($table, $index) && $result; + } + + /// Define index action (not unique) to be added to log + $table = new XMLDBTable('log'); + $index = new XMLDBIndex('action'); + $index->setAttributes(XMLDB_INDEX_NOTUNIQUE, array('action')); + + /// Launch add index action + if (!index_exists($table, $index)) { + $result = add_index($table, $index) && $result; + } + + /// Main savepoint reached + upgrade_main_savepoint($result, 2007101508.002); + } + return $result; } diff --git a/lib/dmllib.php b/lib/dmllib.php index bb1ad9c14b..e756a6299d 100644 --- a/lib/dmllib.php +++ b/lib/dmllib.php @@ -55,7 +55,7 @@ $rcache->misses = 0; * @uses $db * @param string $command The sql string you wish to be executed. * @param bool $feedback Set this argument to true if the results generated should be printed. Default is true. - * @return string + * @return bool success */ function execute_sql($command, $feedback=true) { /// Completely general function - it just runs some SQL and reports success. diff --git a/lib/moodlelib.php b/lib/moodlelib.php index b734b9d885..d6c94b46cb 100644 --- a/lib/moodlelib.php +++ b/lib/moodlelib.php @@ -1340,6 +1340,28 @@ function get_user_timezone_offset($tz = 99) { } } +/** + * Returns an int which represents the systems's timezone difference from GMT in seconds + * @param mixed $tz timezone + * @return int if found, false is timezone 99 or error + */ +function get_timezone_offset($tz) { + global $CFG; + + if ($tz == 99) { + return false; + } + + if (is_numeric($tz)) { + return intval($tz * 60*60); + } + + if (!$tzrecord = get_timezone_record($tz)) { + return false; + } + return intval($tzrecord->gmtoff * 60); +} + /** * Returns a float or a string which denotes the user's timezone * A float value means that a simple offset from GMT is used, while a string (it will be the name of a timezone in the database) @@ -3945,6 +3967,7 @@ function email_to_user($user, $from, $subject, $messagetext, $messagehtml='', $a global $CFG, $FULLME; +return true; // remove before commit!!!! if (empty($user)) { return false; diff --git a/lib/statslib.php b/lib/statslib.php index 7c6c53d0e4..4ecb4bf230 100644 --- a/lib/statslib.php +++ b/lib/statslib.php @@ -3,11 +3,11 @@ // THESE CONSTANTS ARE USED FOR THE REPORTING PAGE. define('STATS_REPORT_LOGINS',1); // double impose logins and unqiue logins on a line graph. site course only. - define('STATS_REPORT_READS',2); // double impose student reads and teacher reads on a line graph. + define('STATS_REPORT_READS',2); // double impose student reads and teacher reads on a line graph. define('STATS_REPORT_WRITES',3); // double impose student writes and teacher writes on a line graph. define('STATS_REPORT_ACTIVITY',4); // 2+3 added up, teacher vs student. define('STATS_REPORT_ACTIVITYBYROLE',5); // all activity, reads vs writes, seleted by role. - + // user level stats reports. define('STATS_REPORT_USER_ACTIVITY',7); define('STATS_REPORT_USER_ALLACTIVITY',8); @@ -47,516 +47,924 @@ define('STATS_MODE_DETAILED',2); define('STATS_MODE_RANKED',3); // admins only - ranks courses - // return codes - whether to rerun - define('STATS_RUN_COMPLETE',1); - define('STATS_RUN_ABORTED',0); +/** + * Print daily cron progress + * @param string $ident + */ +function stats_daily_progress($ident) { + static $start = 0; + static $init = 0; -function stats_cron_daily () { - global $CFG; - - if (empty($CFG->enablestats)) { - return STATS_RUN_ABORTED; + if ($ident == 'init') { + $init = $start = time(); + return; } - if (!$timestart = stats_get_start_from('daily')) { - return STATS_RUN_ABORTED; + $elapsed = time() - $start; + $start = time(); + + if (debugging('', DEBUG_ALL)) { + mtrace("$ident:$elapsed ", ''); + } else { + mtrace('.', ''); } +} +/** + * Execute daily statistics gathering + * @param int $maxdays maximum number of days to be processed + * @return boolean success + */ +function stats_cron_daily($maxdays=1) { + global $CFG; - $midnight = stats_getmidnight(time()); - - // check to make sure we're due to run, at least one day after last run - if (isset($CFG->statslastdaily) and ((time() - 24*60*60) < $CFG->statslastdaily)) { - return STATS_RUN_ABORTED; - } + $now = time(); - mtrace("Running daily statistics gathering..."); - set_config('statslastdaily',time()); + // read last execution date from db + if (!$timestart = get_config(NULL, 'statslastdaily')) { + $timestart = stats_get_base_daily(stats_get_start_from('daily')); + set_config('statslastdaily', $timestart); + } - $return = STATS_RUN_COMPLETE; // optimistic + $nextmidnight = stats_get_next_day_start($timestart); - static $daily_modules; - - if (empty($daily_modules)) { - $daily_modules = array(); - $mods = get_records("modules"); - foreach ($mods as $mod) { - $file = $CFG->dirroot.'/mod/'.$mod->name.'/lib.php'; - if (!is_readable($file)) { - continue; - } - require_once($file); - $fname = $mod->name.'_get_daily_stats'; - if (function_exists($fname)) { - $daily_modules[$mod] = $fname; - } - } + // are there any days that need to be processed? + if ($now < $nextmidnight) { + return true; // everything ok and up-to-date } - $nextmidnight = stats_get_next_dayend($timestart); + $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime; - if (!$courses = get_records('course','','','','id,1')) { - return STATS_RUN_ABORTED; + if (!set_cron_lock('statsrunning', $now + $timeout)) { + return false; } - + + // fisrt delete entries that should not be there yet + delete_records_select('stats_daily', "timeend > $timestart"); + delete_records_select('stats_user_daily', "timeend > $timestart"); + + // Read in a few things we'll use later + $viewactions = implode(',', stats_get_action_names('view')); + $postactions = implode(',', stats_get_action_names('post')); + + $guest = get_guest(); + $guestrole = get_guest_role(); + + list($enrolfrom, $enrolwhere) = stats_get_enrolled_sql($CFG->statscatdepth); + list($fpfrom, $fpwhere) = stats_get_enrolled_sql(0); + + mtrace("Running daily statistics gathering, starting at $timestart:"); + $days = 0; - mtrace("starting at $timestart"); - while ($midnight > $nextmidnight && $timestart < $nextmidnight) { - - $timesql = " (l.time > $timestart AND l.time < $nextmidnight) "; - begin_sql(); - foreach ($courses as $course) { - //do this first. - if ($course->id == SITEID) { - $stat = new StdClass; - $stat->courseid = $course->id; - $stat->timeend = $nextmidnight; - $stat->roleid = 0; // all users - $stat->stattype = 'logins'; - $sql = 'SELECT count(l.id) FROM '.$CFG->prefix.'log l WHERE l.action = \'login\' AND '.$timesql; - $stat->stat1 = count_records_sql($sql); - $sql = 'SELECT COUNT(DISTINCT(l.userid)) FROM '.$CFG->prefix.'log l WHERE l.action = \'login\' AND '.$timesql; - $stat->stat2 = count_records_sql($sql); - insert_record('stats_daily',$stat,false); // don't worry about the return id, we don't need it. - - // and now user logins... - $sql = 'SELECT l.userid,count(l.id) as count FROM '.$CFG->prefix.'log l WHERE action = \'login\' AND '.$timesql.' GROUP BY userid'; - - if ($logins = get_records_sql($sql)) { - foreach ($logins as $l) { - $stat->statsreads = $l->count; - $stat->userid = $l->userid; - $stat->timeend = $nextmidnight; - $stat->courseid = SITEID; - $stat->statswrites = 0; - $stat->stattype = 'logins'; - $stat->roleid = 0; - insert_record('stats_user_daily',$stat,false); - } - } - } + $failed = false; // failed stats flag - $context = get_context_instance(CONTEXT_COURSE, $course->id); - if (!$roles = get_roles_on_exact_context($context)) { - // no roles.. nothing to log. - continue; - } - - $primary_roles = sql_primary_role_subselect(); // In dmllib.php - foreach ($roles as $role) { - // ENROLMENT FIRST.... - // ALL users with this role... - $stat = new StdClass; - $stat->courseid = $course->id; - $stat->roleid = $role->id; - $stat->timeend = $nextmidnight; - $stat->stattype = 'enrolments'; - $sql = 'SELECT COUNT(DISTINCT prs.userid) FROM ('.$primary_roles.') prs WHERE prs.primary_roleid='.$role->id. - ' AND prs.courseid='.$course->id.' AND prs.contextlevel = '.CONTEXT_COURSE; - $stat->stat1 = count_records_sql($sql); - - $sql = 'SELECT COUNT(DISTINCT prs.userid) FROM ('.$primary_roles.') prs - INNER JOIN '.$CFG->prefix.'log l ON (prs.userid=l.userid AND l.course=prs.courseid) - WHERE prs.primary_roleid='.$role->id.' AND prs.courseid='.$course->id.' - AND prs.contextlevel = '.CONTEXT_COURSE.' AND '.$timesql; - - $stat->stat2 = count_records_sql($sql); - insert_record('stats_daily',$stat,false); // don't worry about the return id, we don't need it. - - // ACTIVITY - - $stat = new StdClass; - $stat->courseid = $course->id; - $stat->roleid = $role->id; - $stat->timeend = $nextmidnight; - $stat->stattype = 'activity'; - - $sql = 'SELECT COUNT(DISTINCT l.id) FROM ('.$primary_roles.') prs - INNER JOIN '.$CFG->prefix.'log l ON (prs.userid=l.userid - AND l.course=prs.courseid) WHERE prs.primary_roleid='.$role->id.' - AND prs.courseid='.$course->id.' AND prs.contextlevel = '.CONTEXT_COURSE.' - AND '.$timesql.' '.stats_get_action_sql_in('view'); - $stat->stat1 = count_records_sql($sql); - - $sql = 'SELECT COUNT(DISTINCT l.id) FROM ('.$primary_roles.') prs - INNER JOIN '.$CFG->prefix.'log l ON (prs.userid=l.userid AND l.course=prs.courseid) - WHERE prs.primary_roleid='.$role->id.' AND prs.courseid='.$course->id.' - AND prs.contextlevel = '.CONTEXT_COURSE.' AND '.$timesql.' '.stats_get_action_sql_in('post'); - $stat->stat2 = count_records_sql($sql); - - insert_record('stats_daily',$stat,false); // don't worry about the return id, we don't need it. + while ($now > $nextmidnight) { + if ($days >= $maxdays) { + mtrace("...stopping early, reached maximum number of $maxdays days - will continue next time."); + set_cron_lock('statsrunning', null); + return false; + } + + $days++; + @set_time_limit($timeout - 200); + + if ($days > 1) { + // move the lock + set_cron_lock('statsrunning', time() + $timeout, true); + } + + $timesql = "l.time >= $timestart AND l.time < $nextmidnight"; + $timesql1 = "l1.time >= $timestart AND l1.time < $nextmidnight"; + $timesql2 = "l2.time >= $timestart AND l2.time < $nextmidnight"; + + stats_daily_progress('init'); + + /// process login info first + $sql = "INSERT INTO {$CFG->prefix}stats_user_daily (stattype, timeend, courseid, userid, statsreads) + + SELECT 'logins' AS stattype, $nextmidnight AS timeend, ".SITEID." AS courseid, + l.userid, count(l.id) AS statsreads + FROM {$CFG->prefix}log l + WHERE action = 'login' AND $timesql + GROUP BY stattype, timeend, courseid, userid + HAVING count(l.id) > 0"; + + if (!execute_sql($sql, false)) { + $failed = true; + break; + } + stats_daily_progress('1'); + + $sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'logins' AS stattype, $nextmidnight AS timeend, ".SITEID." as courseid, 0, + COALESCE((SELECT SUM(statsreads) + FROM {$CFG->prefix}stats_user_daily s1 + WHERE s1.stattype = 'logins' AND timeend = $nextmidnight), 0) AS stat1, + COALESCE((SELECT COUNT('x') + FROM {$CFG->prefix}stats_user_daily s2 + WHERE s2.stattype = 'logins' AND timeend = $nextmidnight), 0) AS stat2"; + + if (!execute_sql($sql, false)) { + $failed = true; + break; + } + stats_daily_progress('2'); + + + // Enrolments and active enrolled users + // + // Unfortunately, we do not know how many users were registered + // at given times in history :-( + // - stat1: enrolled users + // - stat2: enrolled users active in this period + // - enrolment is defined now as having course:view capability in + // course context or above, we look 3 cats upwards only and ignore prevent + // and prohibit caps to simplify it + // - SITEID is specialcased here, because it's all about default enrolment + // in that case, we'll count non-deleted users. + // + + $sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'enrolments' AS stattype, $nextmidnight AS timeend, + c.id AS courseid, ra.roleid, COUNT(DISTINCT ra.userid) AS stat1, 0 AS stat2 + FROM {$CFG->prefix}role_assignments ra, {$CFG->prefix}context ctx, + {$CFG->prefix}course c, $enrolfrom + WHERE $enrolwhere + GROUP BY stattype, timeend, c.id, ra.roleid, stat2 + HAVING COUNT(DISTINCT ra.userid) > 0"; + + if (!execute_sql($sql, false)) { + $failed = true; + break; + } + stats_daily_progress('3'); + + // using table alias in UPDATE does not work in pg < 8.2 + $sql = "UPDATE {$CFG->prefix}stats_daily + SET stat2 = (SELECT COUNT(DISTINCT ra.userid) + FROM {$CFG->prefix}role_assignments ra, {$CFG->prefix}context ctx, + {$CFG->prefix}course c, $enrolfrom + WHERE ra.roleid = {$CFG->prefix}stats_daily.roleid AND + c.id = {$CFG->prefix}stats_daily.courseid AND + $enrolwhere AND + EXISTS (SELECT 'x' + FROM {$CFG->prefix}log l + WHERE l.course = {$CFG->prefix}stats_daily.courseid AND + l.userid = ra.userid AND $timesql)) + WHERE {$CFG->prefix}stats_daily.stattype = 'enrolments' AND + {$CFG->prefix}stats_daily.timeend = $nextmidnight AND + {$CFG->prefix}stats_daily.courseid IN + (SELECT DISTINCT l.course + FROM {$CFG->prefix}log l + WHERE $timesql)"; + + if (!execute_sql($sql, false)) { + $failed = true; + break; + } + stats_daily_progress('4'); + + /// now get course total enrolments (roleid==0) - except frontpage + $sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'enrolments' AS stattype, $nextmidnight AS timeend, + c.id, 0 AS nroleid, COUNT(DISTINCT ra.userid) AS stat1, 0 AS stat2 + FROM {$CFG->prefix}role_assignments ra, {$CFG->prefix}context ctx, + {$CFG->prefix}course c, $enrolfrom + WHERE c.id <> ".SITEID." AND $enrolwhere + GROUP BY stattype, timeend, c.id, nroleid, stat2 + HAVING COUNT(DISTINCT ra.userid) > 0"; + + if (!execute_sql($sql, false)) { + $failed = true; + break; + } + stats_daily_progress('5'); + + $sql = "UPDATE {$CFG->prefix}stats_daily + SET stat2 = (SELECT COUNT(DISTINCT ra.userid) + FROM {$CFG->prefix}role_assignments ra, {$CFG->prefix}context ctx, + {$CFG->prefix}course c, $enrolfrom + WHERE c.id = {$CFG->prefix}stats_daily.courseid AND + $enrolwhere AND + EXISTS (SELECT 'x' + FROM {$CFG->prefix}log l + WHERE l.course = {$CFG->prefix}stats_daily.courseid AND + l.userid = ra.userid AND $timesql)) + WHERE {$CFG->prefix}stats_daily.stattype = 'enrolments' AND + {$CFG->prefix}stats_daily.timeend = $nextmidnight AND + {$CFG->prefix}stats_daily.roleid = 0 AND + {$CFG->prefix}stats_daily.courseid IN + (SELECT l.course + FROM {$CFG->prefix}log l + WHERE $timesql AND l.course <> ".SITEID.")"; + + if (!execute_sql($sql, false)) { + $failed = true; + break; + } + stats_daily_progress('6'); + + /// frontapge(==site) enrolments total + $sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'enrolments', $nextmidnight, ".SITEID.", 0, + COALESCE((SELECT COUNT('x') + FROM {$CFG->prefix}user u + WHERE u.deleted = 0), 0) AS stat1, + COALESCE((SELECT COUNT(DISTINCT u.id) + FROM {$CFG->prefix}user u + JOIN {$CFG->prefix}log l ON l.userid = u.id + WHERE u.deleted = 0 AND $timesql), 0) AS stat2"; + + if (!execute_sql($sql, false)) { + $failed = true; + break; + } + stats_daily_progress('7'); + + if (empty($CFG->defaultfrontpageroleid)) { // 1.9 only, so far + $defaultfproleid = 0; + } else { + $defaultfproleid = $CFG->defaultfrontpageroleid; + } + + /// Default frontpage role enrolments are all site users (not deleted) + if ($defaultfproleid) { + // first remove default frontpage role counts if created by previous query + $sql = "DELETE + FROM {$CFG->prefix}stats_daily + WHERE stattype = 'enrolments' AND courseid = ".SITEID." AND + roleid = $defaultfproleid AND timeend = $nextmidnight"; + if (!execute_sql($sql, false)) { + $failed = true; + break; } - - $users = stats_get_course_users($course,$timesql); - foreach ($users as $user) { - stats_do_daily_user_cron($course,$user,$user->primaryrole,$timesql,$nextmidnight,$daily_modules); + stats_daily_progress('8'); + + $sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'enrolments', $nextmidnight, ".SITEID.", $defaultfproleid, + COALESCE((SELECT COUNT('x') + FROM {$CFG->prefix}user u + WHERE u.deleted = 0), 0) AS stat1, + COALESCE((SELECT COUNT(DISTINCT u.id) + FROM {$CFG->prefix}user u + JOIN {$CFG->prefix}log l ON l.userid = u.id + WHERE u.deleted = 0 AND $timesql), 0) AS stat2"; + + if (!execute_sql($sql, false)) { + $failed = true; + break; } + stats_daily_progress('9'); + + } else { + stats_daily_progress('x'); + stats_daily_progress('x'); } - commit_sql(); - $timestart = $nextmidnight; - $nextmidnight = stats_get_next_dayend($nextmidnight); - $days++; - if (!stats_check_runtime()) { - mtrace("Stopping early! reached maxruntime"); - $return = STATS_RUN_ABORTED; + + + /// individual user stats (including not-logged-in) in each course, this is slow - reuse this data if possible + $sql = "INSERT INTO {$CFG->prefix}stats_user_daily (stattype, timeend, courseid, userid, statsreads, statswrites) + + SELECT 'activity' AS stattype, $nextmidnight AS timeend, d.courseid, d.userid, + COALESCE((SELECT COUNT('x') + FROM {$CFG->prefix}log l + WHERE l.userid = d.userid AND + l.course = d.courseid AND $timesql AND + l.action IN ($viewactions)), 0) AS statsreads, + COALESCE((SELECT COUNT('x') + FROM {$CFG->prefix}log l + WHERE l.userid = d.userid AND + l.course = d.courseid AND $timesql AND + l.action IN ($postactions)), 0) AS statswrites + FROM (SELECT DISTINCT u.id AS userid, l.course AS courseid + FROM {$CFG->prefix}user u, {$CFG->prefix}log l + WHERE u.id = l.userid AND $timesql + UNION + SELECT 0 AS userid, ".SITEID." AS courseid) d"; + // can not use group by here because pg can not handle it :-( + + if (!execute_sql($sql, false)) { + $failed = true; break; } - } - mtrace("got up to ".$timestart); - mtrace("Completed $days days"); - return $return; + stats_daily_progress('10'); + + + /// how many view/post actions in each course total + $sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'activity' AS stattype, $nextmidnight AS timeend, c.id AS courseid, 0, + COALESCE((SELECT COUNT('x') + FROM {$CFG->prefix}log l1 + WHERE l1.course = c.id AND l1.action IN ($viewactions) AND + $timesql1), 0) AS stat1, + COALESCE((SELECT COUNT('x') + FROM {$CFG->prefix}log l2 + WHERE l2.course = c.id AND l2.action IN ($postactions) AND + $timesql2), 0) AS stat2 + FROM {$CFG->prefix}course c + WHERE EXISTS (SELECT 'x' + FROM {$CFG->prefix}log l + WHERE l.course = c.id and $timesql)"; + + if (!execute_sql($sql, false)) { + $failed = true; + break; + } + stats_daily_progress('11'); -} + /// how many view actions for each course+role - excluding guests and frontpage -function stats_cron_weekly () { + $sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2) - global $CFG; + SELECT 'activity' AS stattype, $nextmidnight AS timeend, pl.courseid, pl.roleid, + SUM(pl.statsreads) AS stat1, SUM(pl.statswrites) AS stat2 + FROM (SELECT DISTINCT ra.roleid, c.id AS courseid, sud.statsreads, sud.statswrites + FROM {$CFG->prefix}role_assignments ra, {$CFG->prefix}context ctx, {$CFG->prefix}course c, + {$CFG->prefix}stats_user_daily sud, $enrolfrom + WHERE c.id <> ".SITEID." AND + ra.roleid <> $guestrole->id AND ra.userid <> $guest->id AND + sud.timeend = $nextmidnight AND sud.userid = ra.userid AND + sud.courseid = c.id AND $enrolwhere + ) pl + GROUP BY stattype, timeend, pl.courseid, pl.roleid + HAVING SUM(pl.statsreads) > 0 OR SUM(pl.statswrites) > 0"; - if (empty($CFG->enablestats)) { - STATS_RUN_ABORTED; - } + if (!execute_sql($sql, false)) { + $failed = true; + break; + } + stats_daily_progress('12'); + + /// how many view actions from guests only in each course - excluding frontpage + /// (guest is anybody with guest role or no role with course:view in course - this may not work properly if category limit too low) + /// normal users may enter course with temporary guest acces too + + $sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'activity' AS stattype, $nextmidnight AS timeend, sud.courseid, $guestrole->id AS nroleid, + SUM(sud.statsreads) AS stat1, SUM(sud.statswrites) AS stat2 + FROM {$CFG->prefix}stats_user_daily sud + WHERE sud.timeend = $nextmidnight AND sud.courseid <> ".SITEID." AND + (sud.userid = $guest->id OR sud.userid + NOT IN (SELECT ra.userid + FROM {$CFG->prefix}role_assignments ra, {$CFG->prefix}context ctx, + {$CFG->prefix}course c, $enrolfrom + WHERE c.id <> ".SITEID." AND ra.roleid <> $guestrole->id AND + $enrolwhere)) + GROUP BY stattype, timeend, sud.courseid, nroleid + HAVING SUM(sud.statsreads) > 0 OR SUM(sud.statswrites) > 0"; + + if (!execute_sql($sql, false)) { + $failed = true; + break; + } + stats_daily_progress('13'); + + + /// how many view actions for each role on frontpage - excluding guests, not-logged-in and default frontpage role + $sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'activity' AS stattype, $nextmidnight AS timeend, pl.courseid, pl.roleid, + SUM(pl.statsreads) AS stat1, SUM(pl.statswrites) AS stat2 + FROM (SELECT DISTINCT ra.roleid, c.id AS courseid, sud.statsreads, sud.statswrites + FROM {$CFG->prefix}role_assignments ra, {$CFG->prefix}context ctx, {$CFG->prefix}course c, + {$CFG->prefix}stats_user_daily sud, $fpfrom + WHERE c.id = ".SITEID." AND ra.roleid <> $defaultfproleid AND + ra.roleid <> $guestrole->id AND ra.userid <> $guest->id AND + sud.timeend = $nextmidnight AND sud.userid = ra.userid AND + sud.courseid = c.id AND $fpwhere + ) pl + GROUP BY stattype, timeend, pl.courseid, pl.roleid + HAVING SUM(pl.statsreads) > 0 OR SUM(pl.statswrites) > 0"; + + if (!execute_sql($sql, false)) { + $failed = true; + break; + } + stats_daily_progress('14'); + + + /// how many view actions for default frontpage role on frontpage only + $sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'activity' AS stattype, $nextmidnight AS timeend, sud.courseid, $defaultfproleid AS nroleid, + SUM(sud.statsreads) AS stat1, SUM(sud.statswrites) AS stat2 + FROM {$CFG->prefix}stats_user_daily sud + WHERE sud.timeend = $nextmidnight AND sud.courseid = ".SITEID." AND + sud.userid <> $guest->id AND sud.userid <> 0 AND sud.userid + NOT IN (SELECT ra.userid + FROM {$CFG->prefix}role_assignments ra, {$CFG->prefix}context ctx, + {$CFG->prefix}course c, $fpfrom + WHERE c.id = ".SITEID." AND ra.roleid <> $guestrole->id AND + ra.roleid <> $defaultfproleid AND $fpwhere) + GROUP BY stattype, timeend, sud.courseid, nroleid + HAVING SUM(sud.statsreads) > 0 OR SUM(sud.statswrites) > 0"; + + if (!execute_sql($sql, false)) { + $failed = true; + break; + } + stats_daily_progress('15'); + + /// how many view actions for guests or not-logged-in on frontpage + $sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'activity' AS stattype, $nextmidnight AS timeend, ".SITEID." AS courseid, $guestrole->id AS nroleid, + SUM(pl.statsreads) AS stat1, SUM(pl.statswrites) AS stat2 + FROM (SELECT sud.statsreads, sud.statswrites + FROM {$CFG->prefix}stats_user_daily sud + WHERE (sud.userid = $guest->id OR sud.userid = 0) AND + sud.timeend = $nextmidnight AND sud.courseid = ".SITEID." + ) pl + GROUP BY stattype, timeend, courseid, nroleid + HAVING SUM(pl.statsreads) > 0 OR SUM(pl.statswrites) > 0"; + + if (!execute_sql($sql, false)) { + $failed = true; + break; + } + stats_daily_progress('16'); + + // remember processed days + set_config('statslastdaily', $nextmidnight); + mtrace(" finished until $nextmidnight: ".userdate($nextmidnight)); - if (!$timestart = stats_get_start_from('weekly')) { - return STATS_RUN_ABORTED; + $timestart = $nextmidnight; + $nextmidnight = stats_get_next_day_start($nextmidnight); } - - // check to make sure we're due to run, at least one week after last run - $sunday = stats_get_base_weekly(); - if (isset($CFG->statslastweekly) and ((time() - (7*24*60*60)) <= $CFG->statslastweekly)) { - return STATS_RUN_ABORTED; + set_cron_lock('statsrunning', null); + + if ($failed) { + $days--; + mtrace("...error occured, completed $days days of statistics."); + return false; + + } else { + mtrace("...completed $days days of statistics."); + return true; } +} - mtrace("Running weekly statistics gathering..."); - set_config('statslastweekly',time()); - $return = STATS_RUN_COMPLETE; // optimistic +/** + * Execute weekly statistics gathering + * @return boolean success + */ +function stats_cron_weekly() { + global $CFG; - static $weekly_modules; - - if (empty($weekly_modules)) { - $weekly_modules = array(); - $mods = get_records("modules"); - foreach ($mods as $mod) { - $file = $CFG->dirroot.'/mod/'.$mod->name.'/lib.php'; - if (!is_readable($file)) { - continue; - } - require_once($file); - $fname = $mod->name.'_get_weekly_stats'; - if (function_exists($fname)) { - $weekly_modules[$mod] = $fname; - } - } + $now = time(); + + // read last execution date from db + if (!$timestart = get_config(NULL, 'statslastweekly')) { + $timestart = stats_get_base_daily(stats_get_start_from('weekly')); + set_config('statslastweekly', $timestart); } - $nextsunday = stats_get_next_weekend($timestart); + $nextstartweek = stats_get_next_week_start($timestart); - if (!$courses = get_records('course','','','','id,1')) { - return STATS_RUN_ABORTED; + // are there any weeks that need to be processed? + if ($now < $nextstartweek) { + return true; // everything ok and up-to-date } - - $weeks = 0; - mtrace("starting at $timestart"); - while ($sunday > $nextsunday && $timestart < $nextsunday) { - - $timesql = " (timeend > $timestart AND timeend < $nextsunday) "; - begin_sql(); - foreach ($courses as $course) { - - // enrolment first - $sql = 'SELECT roleid, ceil(avg(stat1)) AS stat1, ceil(avg(stat2)) AS stat2 - FROM '.$CFG->prefix.'stats_daily - WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'enrolments\' - GROUP BY roleid'; - - if ($rolestats = get_records_sql($sql)) { - foreach ($rolestats as $stat) { - $stat->courseid = $course->id; - $stat->timeend = $nextsunday; - $stat->stattype = 'enrolments'; - - insert_record('stats_weekly',$stat,false); // don't worry about the return id, we don't need it. - } - } - - // activity - $sql = 'SELECT roleid, sum(stat1) AS stat1, sum(stat2) as stat2 - FROM '.$CFG->prefix.'stats_daily - WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'activity\' - GROUP BY roleid'; - - if ($rolestats = get_records_sql($sql)) { - foreach ($rolestats as $stat) { - $stat->courseid = $course->id; - $stat->timeend = $nextsunday; - $stat->stattype = 'activity'; - unset($stat->id); - - insert_record('stats_weekly',$stat,false); // don't worry about the return id, we don't need it. - } - } - - // logins - if ($course->id == SITEID) { - $sql = 'SELECT sum(stat1) AS stat1 - FROM '.$CFG->prefix.'stats_daily - WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'logins\''; - - if ($stat = get_record_sql($sql)) { - if (empty($stat->stat1)) { - $stat->stat1 = 0; - } - $stat->courseid = $course->id; - $stat->roleid = 0; - $stat->timeend = $nextsunday; - $stat->stattype = 'logins'; - $sql = 'SELECT COUNT(DISTINCT(l.userid)) FROM '.$CFG->prefix.'log l WHERE l.action = \'login\' AND ' - .str_replace('timeend','time',$timesql); - $stat->stat2 = count_records_sql($sql); - - insert_record('stats_weekly',$stat,false); // don't worry about the return id, we don't need it. - } - } - $users = stats_get_course_users($course,$timesql); - foreach ($users as $user) { - stats_do_aggregate_user_cron($course,$user,$user->primaryrole,$timesql,$nextsunday,'weekly',$weekly_modules); - } - } - stats_do_aggregate_user_login_cron($timesql,$nextsunday,'weekly'); - commit_sql(); - $timestart = $nextsunday; - $nextsunday = stats_get_next_weekend($nextsunday); + $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime; + + if (!set_cron_lock('statsrunning', $now + $timeout)) { + return false; + } + + // fisrt delete entries that should not be there yet + delete_records_select('stats_weekly', "timeend > $timestart"); + delete_records_select('stats_user_weekly', "timeend > $timestart"); + + mtrace("Running weekly statistics gathering, starting at $timestart:"); + + $weeks = 0; + while ($now > $nextstartweek) { + @set_time_limit($timeout - 200); $weeks++; - if (!stats_check_runtime()) { - mtrace("Stopping early! reached maxruntime"); - $return = STATS_RUN_ABORTED; - break; + if ($weeks > 1) { + // move the lock + set_cron_lock('statsrunning', time() + $timeout, true); } + + $logtimesql = "l.time >= $timestart AND l.time < $nextstartweek"; + $stattimesql = "timeend > $timestart AND timeend <= $nextstartweek"; + + /// process login info first + $sql = "INSERT INTO {$CFG->prefix}stats_user_weekly (stattype, timeend, courseid, userid, statsreads) + + SELECT 'logins' AS stattype, $nextstartweek AS timeend, ".SITEID." as courseid, + l.userid, count(l.id) AS statsreads + FROM {$CFG->prefix}log l + WHERE action = 'login' AND $logtimesql + GROUP BY stattype, timeend, courseid, userid + HAVING count(l.id) > 0"; + + execute_sql($sql, false); + + $sql = "INSERT INTO {$CFG->prefix}stats_weekly (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'logins' AS stattype, $nextstartweek AS timeend, ".SITEID." as courseid, 0, + COALESCE((SELECT SUM(statsreads) + FROM {$CFG->prefix}stats_user_weekly s1 + WHERE s1.stattype = 'logins' AND timeend = $nextstartweek), 0) AS nstat1, + COALESCE((SELECT COUNT('x') + FROM {$CFG->prefix}stats_user_weekly s2 + WHERE s2.stattype = 'logins' AND timeend = $nextstartweek), 0) AS nstat2"; + + execute_sql($sql, false); + + + /// now enrolments averages + $sql = "INSERT INTO {$CFG->prefix}stats_weekly (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'enrolments' AS nstattype, $nextstartweek AS ntimeend, courseid, roleid, + CEIL(AVG(stat1)) AS nstat1, CEIL(AVG(stat2)) AS nstat2 + FROM {$CFG->prefix}stats_daily sd + WHERE stattype = 'enrolments' AND $stattimesql + GROUP BY nstattype, ntimeend, courseid, roleid"; + + execute_sql($sql, false); + + + /// activity read/write averages + $sql = "INSERT INTO {$CFG->prefix}stats_weekly (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'activity' AS nstattype, $nextstartweek AS ntimeend, courseid, roleid, + SUM(stat1) AS nstat1, SUM(stat2) AS nstat2 + FROM {$CFG->prefix}stats_daily + WHERE stattype = 'activity' AND $stattimesql + GROUP BY nstattype, ntimeend, courseid, roleid"; + + execute_sql($sql, false); + + + /// user read/write averages + $sql = "INSERT INTO {$CFG->prefix}stats_user_weekly (stattype, timeend, courseid, userid, statsreads, statswrites) + + SELECT 'activity' AS nstattype, $nextstartweek AS ntimeend, courseid, userid, + SUM(statsreads) AS nstatsreads, SUM(statswrites) AS nstatswrites + FROM {$CFG->prefix}stats_user_daily + WHERE stattype = 'activity' AND $stattimesql + GROUP BY nstattype, ntimeend, courseid, userid"; + + execute_sql($sql, false); + + set_config('statslastweekly', $nextstartweek); + mtrace(" finished until $nextstartweek: ".userdate($nextstartweek)); + + $timestart = $nextstartweek; + $nextstartweek = stats_get_next_week_start($nextstartweek); } - mtrace("got up to ".$timestart); - mtrace("Completed $weeks weeks"); - return $return; + + set_cron_lock('statsrunning', null); + mtrace("...completed $weeks weeks of statistics."); + return true; } - -function stats_cron_monthly () { +/** + * Execute monthly statistics gathering + * @return boolean success + */ +function stats_cron_monthly() { global $CFG; - if (empty($CFG->enablestats)) { - return STATS_RUN_ABORTED; + $now = time(); + + // read last execution date from db + if (!$timestart = get_config(NULL, 'statslastmonthly')) { + $timestart = stats_get_base_monthly(stats_get_start_from('monthly')); + set_config('statslastmonthly', $timestart); } - if (!$timestart = stats_get_start_from('monthly')) { - return STATS_RUN_ABORTED; + $nextstartmonth = stats_get_next_month_start($timestart); + + // are there any months that need to be processed? + if ($now < $nextstartmonth) { + return true; // everything ok and up-to-date } - - // check to make sure we're due to run, at least one month after last run - $monthend = stats_get_base_monthly(); - - if (isset($CFG->statslastmonthly) and ((time() - (31*24*60*60)) <= $CFG->statslastmonthly)) { - return STATS_RUN_ABORTED; + + $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime; + + if (!set_cron_lock('statsrunning', $now + $timeout)) { + return false; } - - mtrace("Running monthly statistics gathering..."); - set_config('statslastmonthly',time()); - $return = STATS_RUN_COMPLETE; // optimistic + // fisr delete entries that should not be there yet + delete_records_select('stats_monthly', "timeend > $timestart"); + delete_records_select('stats_user_monthly', "timeend > $timestart"); - static $monthly_modules; - - if (empty($monthly_modules)) { - $monthly_modules = array(); - $mods = get_records("modules"); - foreach ($mods as $mod) { - $file = $CFG->dirroot.'/mod/'.$mod->name.'/lib.php'; - if (!is_readable($file)) { - continue; - } - require_once($file); - $fname = $mod->name.'_get_monthly_stats'; - if (function_exists($fname)) { - $monthly_modules[$mod] = $fname; - } + $startmonth = stats_get_base_monthly($now); + + + mtrace("Running monthly statistics gathering, starting at $timestart:"); + + $months = 0; + while ($now > $nextstartmonth) { + @set_time_limit($timeout - 200); + $months++; + + if ($months > 1) { + // move the lock + set_cron_lock('statsrunning', time() + $timeout, true); } + + $logtimesql = "l.time >= $timestart AND l.time < $nextstartmonth"; + $stattimesql = "timeend > $timestart AND timeend <= $nextstartmonth"; + + /// process login info first + $sql = "INSERT INTO {$CFG->prefix}stats_user_monthly (stattype, timeend, courseid, userid, statsreads) + + SELECT 'logins' AS stattype, $nextstartmonth AS timeend, ".SITEID." as courseid, + l.userid, count(l.id) AS statsreads + FROM {$CFG->prefix}log l + WHERE action = 'login' AND $logtimesql + GROUP BY stattype, timeend, courseid, userid"; + + execute_sql($sql, false); + + $sql = "INSERT INTO {$CFG->prefix}stats_monthly (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'logins' AS stattype, $nextstartmonth AS timeend, ".SITEID." as courseid, 0, + COALESCE((SELECT SUM(statsreads) + FROM {$CFG->prefix}stats_user_monthly s1 + WHERE s1.stattype = 'logins' AND timeend = $nextstartmonth), 0) AS nstat1, + COALESCE((SELECT COUNT('x') + FROM {$CFG->prefix}stats_user_monthly s2 + WHERE s2.stattype = 'logins' AND timeend = $nextstartmonth), 0) AS nstat2"; + + execute_sql($sql, false); + + + /// now enrolments averages + $sql = "INSERT INTO {$CFG->prefix}stats_monthly (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'enrolments' AS nstattype, $nextstartmonth AS ntimeend, courseid, roleid, + CEIL(AVG(stat1)) AS nstat1, CEIL(AVG(stat2)) AS nstat2 + FROM {$CFG->prefix}stats_daily sd + WHERE stattype = 'enrolments' AND $stattimesql + GROUP BY nstattype, ntimeend, courseid, roleid"; + + execute_sql($sql, false); + + + /// activity read/write averages + $sql = "INSERT INTO {$CFG->prefix}stats_monthly (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT 'activity' AS nstattype, $nextstartmonth AS ntimeend, courseid, roleid, + SUM(stat1) AS nstat1, SUM(stat2) AS nstat2 + FROM {$CFG->prefix}stats_daily + WHERE stattype = 'activity' AND $stattimesql + GROUP BY nstattype, ntimeend, courseid, roleid"; + + execute_sql($sql, false); + + + /// user read/write averages + $sql = "INSERT INTO {$CFG->prefix}stats_user_monthly (stattype, timeend, courseid, userid, statsreads, statswrites) + + SELECT 'activity' AS nstattype, $nextstartmonth AS ntimeend, courseid, userid, + SUM(statsreads) AS nstatsreads, SUM(statswrites) AS nstatswrites + FROM {$CFG->prefix}stats_user_daily + WHERE stattype = 'activity' AND $stattimesql + GROUP BY nstattype, ntimeend, courseid, userid"; + + execute_sql($sql, false); + + set_config('statslastmonthly', $nextstartmonth); + mtrace(" finished until $nextstartmonth: ".userdate($nextstartmonth)); + + $timestart = $nextstartmonth; + $nextstartmonth = stats_get_next_month_start($nextstartmonth); } - - $nextmonthend = stats_get_next_monthend($timestart); - if (!$courses = get_records('course','','','','id,1')) { - return STATS_RUN_ABORTED; + set_cron_lock('statsrunning', null); + mtrace("...completed $months months of statistics."); + return true; +} + +/** + * Returns simplified enrolment sql join data + * @param int $limit number of max parent course categories + * @return array from and where string + */ +function stats_get_enrolled_sql($limit) { + global $CFG; + + $from = "{$CFG->prefix}role_capabilities rc"; + for($i=1; $i<=$limit; $i++) { + $from .= ", {$CFG->prefix}course_categories cc$i"; } - - $months = 0; - mtrace("starting from $timestart"); - while ($monthend > $nextmonthend && $timestart < $nextmonthend) { - - $timesql = " (timeend > $timestart AND timeend < $nextmonthend) "; - begin_sql(); - foreach ($courses as $course) { - - // enrolment first - $sql = 'SELECT roleid, ceil(avg(stat1)) AS stat1, ceil(avg(stat2)) AS stat2 - FROM '.$CFG->prefix.'stats_daily - WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'enrolments\' - GROUP BY roleid'; - - if ($rolestats = get_records_sql($sql)) { - foreach ($rolestats as $stat) { - $stat->courseid = $course->id; - $stat->timeend = $nextmonthend; - $stat->stattype = 'enrolments'; - - insert_record('stats_monthly',$stat,false); // don't worry about the return id, we don't need it. - } - } - - // activity - $sql = 'SELECT roleid, sum(stat1) AS stat1, sum(stat2) as stat2 - FROM '.$CFG->prefix.'stats_daily - WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'activity\' - GROUP BY roleid'; - - if ($rolestats = get_records_sql($sql)) { - foreach ($rolestats as $stat) { - $stat->courseid = $course->id; - $stat->timeend = $nextmonthend; - $stat->stattype = 'activity'; - unset($stat->id); - - insert_record('stats_monthly',$stat,false); // don't worry about the return id, we don't need it. - } - } - - // logins - if ($course->id == SITEID) { - $sql = 'SELECT sum(stat1) AS stat1 - FROM '.$CFG->prefix.'stats_daily - WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'logins\''; - - if ($stat = get_record_sql($sql)) { - if (empty($stat->stat1)) { - $stat->stat1 = 0; - } - $stat->courseid = $course->id; - $stat->roleid = 0; - $stat->timeend = $nextmonthend; - $stat->stattype = 'logins'; - $sql = 'SELECT COUNT(DISTINCT(l.userid)) FROM '.$CFG->prefix.'log l WHERE l.action = \'login\' AND ' - .str_replace('timeend','time',$timesql); - $stat->stat2 = count_records_sql($sql); - - insert_record('stats_monthly',$stat,false); // don't worry about the return id, we don't need it. - } - } - $users = stats_get_course_users($course,$timesql); - foreach ($users as $user) { - stats_do_aggregate_user_cron($course,$user,$user->primaryrole,$timesql,$nextmonthend,'monthly',$monthly_modules); - } + $where = " (rc.capability = 'moodle/course:view' AND + rc.permission = 1 AND rc.contextid = ".SYSCONTEXTID." AND + rc.roleid = ra.roleid AND + ( (ctx.id = ".SYSCONTEXTID." AND ctx.id = ra.contextid) + OR (ctx.contextlevel = ".CONTEXT_COURSE." AND + ctx.instanceid = c.id AND ctx.id = ra.contextid)"; + for($i=1; $i<=$limit; $i++) { + $where .= " OR (ctx.contextlevel = ".CONTEXT_COURSECAT." AND ctx.id = ra.contextid AND ctx.instanceid = cc1.id"; + for($j=2; $j<=$i; $j++) { + $k = $j -1; + $where .= " AND cc$j.parent = cc$k.id"; } - stats_do_aggregate_user_login_cron($timesql,$nextmonthend,'monthly'); - commit_sql(); - $timestart = $nextmonthend; - $nextmonthend = stats_get_next_monthend($timestart); - $months++; - if (!stats_check_runtime()) { - mtrace("Stopping early! reached maxruntime"); - break; - $return = STATS_RUN_ABORTED; - } + $where .= " AND c.category = cc$i.id)"; } - mtrace("got up to $timestart"); - mtrace("Completed $months months"); - return $return; + + $where .= ")) "; + + return array($from, $where); } +/** + * Return starting date of stats processing + * @param string $str name of table - daily, weekly or monthly + * @return int timestamp + */ function stats_get_start_from($str) { global $CFG; - // if it's not our first run, just return the most recent. + // are there any data in stats table? Should not be... if ($timeend = get_field_sql('SELECT timeend FROM '.$CFG->prefix.'stats_'.$str.' ORDER BY timeend DESC')) { return $timeend; } - // decide what to do based on our config setting (either all or none or a timestamp) - $function = 'stats_get_base_'.$str; switch ($CFG->statsfirstrun) { - case 'all': - return $function(get_field_sql('SELECT time FROM '.$CFG->prefix.'log ORDER BY time')); - break; - case 'none': - return $function(strtotime('-1 day',time())); - break; + case 'all': + if ($firstlog = get_field_sql('SELECT time FROM '.$CFG->prefix.'log ORDER BY time ASC')) { + return $firstlog; + } default: if (is_numeric($CFG->statsfirstrun)) { - return $function(time() - $CFG->statsfirstrun); + return time() - $CFG->statsfirstrun; } - return false; - break; + // not a number? use next instead + case 'none': + return strtotime('-3 day', time()); } } +/** + * Start of day + * @param int $time timestamp + * @return start of day + */ function stats_get_base_daily($time=0) { + global $CFG; + if (empty($time)) { $time = time(); } - return stats_getmidnight($time); + if ($CFG->timezone == 99) { + $time = strtotime(date('d-M-Y', $time)); + return $time; + } else { + $offset = get_timezone_offset($CFG->timezone); + $gtime = $time + $offset; + $gtime = intval($gtime / (60*60*24)) * 60*60*24; + return $gtime - $offset; + } } +/** + * Start of week + * @param int $time timestamp + * @return start of week + */ function stats_get_base_weekly($time=0) { - if (empty($time)) { - $time = time(); - } - // if we're currently a monday, last monday will take us back a week - $str = 'last monday'; - if (date('D',$time) == 'Mon') - $str = 'now'; + global $CFG; - return stats_getmidnight(strtotime($str,$time)); + $time = stats_get_base_daily($time); + $startday = $CFG->calendar_startwday; + if ($CFG->timezone == 99) { + $thisday = date('w', $time); + } else { + $offset = get_timezone_offset($CFG->timezone); + $gtime = $time + $offset; + $thisday = gmdate('w', $gtime); + } + if ($thisday > $startday) { + $time = $time - (($thisday - $startday) * 60*60*24); + } else if ($thisday < $startday) { + $time = $time - ((7 + $thisday - $startday) * 60*60*24); + } + return $time; } +/** + * Start of month + * @param int $time timestamp + * @return start of month + */ function stats_get_base_monthly($time=0) { + global $CFG; + if (empty($time)) { $time = time(); } - return stats_getmidnight(strtotime(date('1-M-Y',$time))); + if ($CFG->timezone == 99) { + return strtotime(date('1-M-Y', $time)); + + } else { + $time = stats_get_base_daily($time); + $offset = get_timezone_offset($CFG->timezone); + $gtime = $time + $offset; + $day = gmdate('d', $gtime); + if ($day == 1) { + return $time; + } + return $gtime - (($day-1) * 60*60*24); + } } -function stats_get_next_monthend($lastmonth) { - return stats_getmidnight(strtotime(date('1-M-Y',$lastmonth).' +1 month')); +/** + * Start of next day + * @param int $time timestamp + * @return start of next day + */ +function stats_get_next_day_start($time) { + $next = stats_get_base_daily($time); + $next = $next + 60*60*26; + $next = stats_get_base_daily($next); + if ($next <= $time) { + //DST trouble - prevent infinite loops + $next = $next + 60*60*24; + } + return $next; } -function stats_get_next_weekend($lastweek) { - return stats_getmidnight(strtotime('+1 week',$lastweek)); +/** + * Start of next week + * @param int $time timestamp + * @return start of next week + */ +function stats_get_next_week_start($time) { + $next = stats_get_base_weekly($time); + $next = $next + 60*60*24*9; + $next = stats_get_base_weekly($next); + if ($next <= $time) { + //DST trouble - prevent infinite loops + $next = $next + 60*60*24*7; + } + return $next; } -function stats_get_next_dayend($lastday) { - return stats_getmidnight(strtotime('+1 day',$lastday)); +/** + * Start of next month + * @param int $time timestamp + * @return start of next month + */ +function stats_get_next_month_start($time) { + $next = stats_get_base_monthly($time); + $next = $next + 60*60*24*33; + $next = stats_get_base_monthly($next); + if ($next <= $time) { + //DST trouble - prevent infinite loops + $next = $next + 60*60*24*31; + } + return $next; } +/** + * Remove old stats data + */ function stats_clean_old() { - mtrace("Running stats cleanup tasks... "); - // delete dailies older than 2 months (to be safe) - $deletebefore = stats_get_next_monthend(strtotime('-2 months',time())); - delete_records_select('stats_daily',"timeend < $deletebefore"); - delete_records_select('stats_user_daily',"timeend < $deletebefore"); - - // delete weeklies older than 8 months (to be safe) - $deletebefore = stats_get_next_monthend(strtotime('-8 months',time())); - delete_records_select('stats_weekly',"timeend < $deletebefore"); - delete_records_select('stats_user_weekly',"timeend < $deletebefore"); + mtrace("Running stats cleanup tasks..."); + $deletebefore = stats_get_base_monthly(); + + // delete dailies older than 3 months (to be safe) + $deletebefore = strtotime('-3 months', $deletebefore); + delete_records_select('stats_daily', "timeend < $deletebefore"); + delete_records_select('stats_user_daily', "timeend < $deletebefore"); + + // delete weeklies older than 9 months (to be safe) + $deletebefore = strtotime('-6 months', $deletebefore); + delete_records_select('stats_weekly', "timeend < $deletebefore"); + delete_records_select('stats_user_weekly', "timeend < $deletebefore"); // don't delete monthlies + + mtrace("...stats cleanup finished"); } function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) { global $CFG,$db; + + $param = new object(); + if ($time < 10) { // dailies // number of days to go back = 7* time $param->table = 'daily'; @@ -605,7 +1013,7 @@ function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) { } break; - case STATS_REPORT_WRITES: + case STATS_REPORT_WRITES: $param->fields = $db->Concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat2 as line1'; $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries. $param->aggregategroupby = 'roleid'; @@ -670,7 +1078,7 @@ function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) { break; // ******************** STATS_MODE_RANKED ******************** // - case STATS_REPORT_ACTIVE_COURSES: + case STATS_REPORT_ACTIVE_COURSES: $param->fields = 'sum(stat1+stat2) AS line1'; $param->stattype = 'activity'; $param->orderby = 'line1 DESC'; @@ -685,22 +1093,20 @@ function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) { } $param->fields = ''; $param->sql = 'SELECT activity.courseid, activity.all_activity AS line1, enrolments.highest_enrolments AS line2, - activity.all_activity / enrolments.highest_enrolments as line3 + activity.all_activity / enrolments.highest_enrolments as line3 FROM ( - SELECT courseid, sum(stat1+stat2) AS all_activity - FROM '.$CFG->prefix.'stats_'.$param->table.' - WHERE stattype=\'activity\' AND timeend >= '.$param->timeafter.' - GROUP BY courseid + SELECT courseid, (stat1+stat2) AS all_activity + FROM '.$CFG->prefix.'stats_'.$param->table.' + WHERE stattype=\'activity\' AND timeend >= '.$param->timeafter.' AND roleid = 0 ) activity - INNER JOIN + INNER JOIN ( SELECT courseid, max(stat1) AS highest_enrolments - FROM '.$CFG->prefix.'stats_'.$param->table.' - WHERE stattype=\'enrolments\' AND timeend >= '.$param->timeafter.' - GROUP BY courseid + FROM '.$CFG->prefix.'stats_'.$param->table.' + WHERE stattype=\'enrolments\' AND timeend >= '.$param->timeafter.' AND stat1 > '.$threshold.' + GROUP BY courseid ) enrolments ON (activity.courseid = enrolments.courseid) - WhERE enrolments.highest_enrolments > '.$threshold.' ORDER BY line3 DESC'; $param->line1 = get_string('activity'); $param->line2 = get_string('users'); @@ -715,14 +1121,13 @@ function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) { } $param->fields = ''; $param->sql = 'SELECT courseid, ceil(avg(all_enrolments)) as line1, - ceil(avg(active_enrolments)) as line2, avg(proportion_active) AS line3 + ceil(avg(active_enrolments)) as line2, avg(proportion_active) AS line3 FROM ( - SELECT courseid, timeend, sum(stat2) as active_enrolments, - sum(stat1) as all_enrolments, sum(stat2)'.$real.'/sum(stat1)'.$real.' as proportion_active - FROM '.$CFG->prefix.'stats_'.$param->table.' WHERE stattype=\'enrolments\' - GROUP BY courseid, timeend - HAVING sum(stat1) > '.$threshold.' - ) aq + SELECT courseid, timeend, stat2 as active_enrolments, + stat1 as all_enrolments, stat2'.$real.'/stat1'.$real.' as proportion_active + FROM '.$CFG->prefix.'stats_'.$param->table.' + WHERE stattype=\'enrolments\' AND roleid = 0 AND stat1 > '.$threshold.' + ) aq WHERE timeend >= '.$param->timeafter.' GROUP BY courseid ORDER BY line3 DESC'; @@ -738,12 +1143,11 @@ function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) { $param->sql = 'SELECT courseid, sum(views) AS line1, sum(posts) AS line2, avg(proportion_active) AS line3 FROM ( - SELECT courseid, timeend,sum(stat1) as views, sum(stat2) AS posts, - sum(stat2)'.$real.'/sum(stat1)'.$real.' as proportion_active - FROM '.$CFG->prefix.'stats_'.$param->table.' WHERE stattype=\'activity\' - GROUP BY courseid, timeend - HAVING sum(stat1) > 0 - ) aq + SELECT courseid, timeend, stat1 as views, stat2 AS posts, + stat2'.$real.'/stat1'.$real.' as proportion_active + FROM '.$CFG->prefix.'stats_'.$param->table.' + WHERE stattype=\'activity\' AND roleid = 0 AND stat1 > 0 + ) aq WHERE timeend >= '.$param->timeafter.' GROUP BY courseid ORDER BY line3 DESC'; @@ -762,7 +1166,7 @@ function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) { */ //TODO must add the SITEID reports to the rest of the reports. return $param; -} +} function stats_get_view_actions() { return array('view','view all','history'); @@ -772,9 +1176,9 @@ function stats_get_post_actions() { return array('add','delete','edit','add mod','delete mod','edit section'.'enrol','loginas','new','unenrol','update','update mod'); } -function stats_get_action_sql_in($str) { +function stats_get_action_names($str) { global $CFG; - + $mods = get_records('modules'); $function = 'stats_get_'.$str.'_actions'; $actions = $function(); @@ -789,121 +1193,17 @@ function stats_get_action_sql_in($str) { $actions = array_merge($actions,$function()); } } - $actions = array_unique($actions); - if (empty($actions)) { - return ' '; - } else if (count($actions) == 1) { - return ' AND l.action = '.array_pop($actions).' '; - } else { - return ' AND l.action IN (\''.implode('\',\'',$actions).'\') '; - } -} - - -function stats_get_course_users($course,$timesql) { - global $CFG; - - $timesql = str_replace('timeend','l.time',$timesql); - - $sql = "SELECT userid, primaryrole FROM ( - SELECT active_course_users.userid, - (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 - INNER JOIN {$CFG->prefix}context c ON outer_r_a.contextid = c.id - WHERE c.instanceid=".$course->id." AND c.contextlevel = ".CONTEXT_COURSE." AND outer_r_a.userid=active_course_users.userid - AND NOT EXISTS (SELECT 1 FROM {$CFG->prefix}role_assignments inner_r_a - INNER JOIN {$CFG->prefix}role inner_r ON inner_r_a.roleid = inner_r.id - WHERE inner_r.sortorder < outer_r.sortorder - AND inner_r_a.userid = outer_r_a.userid - AND inner_r_a.contextid = outer_r_a.contextid - ) - ) AS primaryrole - FROM (SELECT DISTINCT userid FROM {$CFG->prefix}log l WHERE course=".$course->id." AND ".$timesql." ) - active_course_users - ) foo WHERE primaryrole IS NOT NULL"; - if (!$users = get_records_sql($sql)) { - $users = array(); - } - - return $users; - -} - -function stats_do_daily_user_cron($course,$user,$roleid,$timesql,$timeend,$mods) { - - global $CFG; - - $stat = new StdClass; - $stat->userid = $user->userid; - $stat->roleid = $roleid; - $stat->courseid = $course->id; - $stat->stattype = 'activity'; - $stat->timeend = $timeend; - - $sql = 'SELECT COUNT(l.id) FROM '.$CFG->prefix.'log l WHERE l.userid = '.$user->userid - .' AND l.course = '.$course->id - .' AND '.$timesql .' '.stats_get_action_sql_in('view'); - - $stat->statsreads = count_records_sql($sql); - - $sql = 'SELECT COUNT(l.id) FROM '.$CFG->prefix.'log l WHERE l.userid = '.$user->userid - .' AND l.course = '.$course->id - .' AND '.$timesql.' '.stats_get_action_sql_in('post'); - - $stat->statswrites = count_records_sql($sql); - - insert_record('stats_user_daily',$stat,false); - - // now ask the modules if they want anything. - foreach ($mods as $mod => $fname) { - mtrace(' doing daily statistics for '.$mod->name); - $fname($course,$user,$timeend,$roleid); - } -} - -function stats_do_aggregate_user_cron($course,$user,$roleid,$timesql,$timeend,$timestr,$mods) { - - global $CFG; - - $stat = new StdClass; - $stat->userid = $user->userid; - $stat->roleid = $roleid; - $stat->courseid = $course->id; - $stat->stattype = 'activity'; - $stat->timeend = $timeend; - - $sql = 'SELECT sum(statsreads) as statsreads, sum(statswrites) as statswrites FROM '.$CFG->prefix.'stats_user_daily WHERE courseid = '.$course->id.' AND '.$timesql - ." AND roleid=".$roleid." AND userid = ".$stat->userid." AND stattype='activity'"; // add on roleid in case they have teacher and student records. - - $r = get_record_sql($sql); - $stat->statsreads = (empty($r->statsreads)) ? 0 : $r->statsreads; - $stat->statswrites = (empty($r->statswrites)) ? 0 : $r->statswrites; - - insert_record('stats_user_'.$timestr,$stat,false); - - // now ask the modules if they want anything. - foreach ($mods as $mod => $fname) { - mtrace(' doing '.$timestr.' statistics for '.$mod->name); - $fname($course,$user,$timeend,$roleid); - } -} -function stats_do_aggregate_user_login_cron($timesql,$timeend,$timestr) { - global $CFG; - - $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'; - - if ($users = get_records_sql($sql)) { - foreach ($users as $stat) { - $stat->courseid = SITEID; - $stat->timeend = $timeend; - $stat->stattype = 'logins'; - - insert_record('stats_user_'.$timestr,$stat,false); - } + // The array_values() forces a stack-like array + // so we can later loop over safely... + $actions = array_values(array_unique($actions)); + $c = count($actions); + for ($n=0;$n<$c;$n++) { + $actions[$n] = "'" . $actions[$n] . "'"; // quote them for SQL } + return $actions; } - function stats_get_time_options($now,$lastweekend,$lastmonthend,$earliestday,$earliestweek,$earliestmonth) { $now = stats_get_base_daily(time()); @@ -920,7 +1220,7 @@ function stats_get_time_options($now,$lastweekend,$lastmonthend,$earliestday,$ea $timeoptions[STATS_TIME_LAST2WEEKS] = get_string('numweeks','moodle',2); } if ($now - (60*60*24*21) >= $earliestday) { - $timeoptions[STATS_TIME_LAST3WEEKS] = get_string('numweeks','moodle',3); + $timeoptions[STATS_TIME_LAST3WEEKS] = get_string('numweeks','moodle',3); } if ($now - (60*60*24*28) >= $earliestday) { $timeoptions[STATS_TIME_LAST4WEEKS] = get_string('numweeks','moodle',4);// show dailies up to (including) here. @@ -964,7 +1264,7 @@ function stats_get_time_options($now,$lastweekend,$lastmonthend,$earliestday,$ea function stats_get_report_options($courseid,$mode) { global $CFG; - + $reportoptions = array(); switch ($mode) { @@ -983,7 +1283,7 @@ function stats_get_report_options($courseid,$mode) { if ($courseid == SITEID) { $reportoptions[STATS_REPORT_LOGINS] = get_string('statsreport'.STATS_REPORT_LOGINS); } - + break; case STATS_MODE_DETAILED: $reportoptions[STATS_REPORT_USER_ACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ACTIVITY); @@ -1002,7 +1302,7 @@ function stats_get_report_options($courseid,$mode) { } break; } - + return $reportoptions; } @@ -1014,13 +1314,17 @@ function stats_fix_zeros($stats,$timeafter,$timestr,$line2=true,$line3=false) { $timestr = str_replace('user_','',$timestr); // just in case. $fun = 'stats_get_base_'.$timestr; - + $now = $fun(); $times = array(); // add something to timeafter since it is our absolute base $actualtimes = array(); - foreach ($stats as $s) { + foreach ($stats as $statid=>$s) { + //normalize the times in stats - those might have been created in different timezone, DST etc. + $s->timeend = $fun($s->timeend + 60*60*5); + $stats[$statid] = $s; + $actualtimes[] = $s->timeend; } @@ -1029,11 +1333,11 @@ function stats_fix_zeros($stats,$timeafter,$timestr,$line2=true,$line3=false) { while ($timeafter < $now) { $times[] = $timeafter; if ($timestr == 'daily') { - $timeafter = stats_get_next_dayend($timeafter); + $timeafter = stats_get_next_day_start($timeafter); } else if ($timestr == 'weekly') { - $timeafter = stats_get_next_weekend($timeafter); + $timeafter = stats_get_next_week_start($timeafter); } else if ($timestr == 'monthly') { - $timeafter = stats_get_next_monthend($timeafter); + $timeafter = stats_get_next_month_start($timeafter); } else { return $stats; // this will put us in a never ending loop. } @@ -1056,7 +1360,7 @@ function stats_fix_zeros($stats,$timeafter,$timestr,$line2=true,$line3=false) { $stats[] = $newobj; } } - + usort($stats,"stats_compare_times"); return $stats; @@ -1070,21 +1374,6 @@ function stats_compare_times($a,$b) { return ($a->timeend > $b->timeend) ? -1 : 1; } -function stats_check_runtime() { - global $CFG; - - if (empty($CFG->statsmaxruntime)) { - return true; - } - - if ((time() - $CFG->statsrunning) < $CFG->statsmaxruntime) { - return true; - } - - return false; // we've gone over! - -} - function stats_check_uptodate($courseid=0) { global $CFG; @@ -1112,39 +1401,59 @@ function stats_check_uptodate($courseid=0) { return get_string('statscatchupmode','error',$a); } +/** + * Calculate missing course totals in stats + */ +function stats_upgrade_totals() { + global $CFG; -// copied from usergetmidnight, but we ignore dst -function stats_getmidnight($date, $timezone=99) { - $timezone = get_user_timezone_offset($timezone); - $userdate = getdate($date); - return make_timestamp($userdate['year'], $userdate['mon'], $userdate['mday'], 0, 0, 0, $timezone,false ); // ignore dst for this. -} + if (empty($CFG->statsrolesupgraded)) { + // stats not yet upgraded to cope with roles... + return; + } -function stats_getdate($time, $timezone=99) { + $types = array('daily', 'weekly', 'monthly'); - $timezone = get_user_timezone_offset($timezone); + $now = time(); + $y30 = 60*60*24*365*30; // 30 years ago :-O + $y20 = 60*60*24*365*20; // 20 years ago :-O + $limit = $now - $y20; - if (abs($timezone) > 13) { // Server time - return getdate($time); - } + foreach ($types as $i => $type) { + $type2 = $types[($i+1) % count($types)]; + + // delete previous incomplete data + $sql = "DELETE FROM {$CFG->prefix}stats_$type2 + WHERE timeend < $limit"; + execute_sql($sql); + + // clear the totals if already exist + $sql = "DELETE FROM {$CFG->prefix}stats_$type + WHERE (stattype = 'enrolments' OR stattype = 'activity') AND + roleid = 0"; + execute_sql($sql); - // There is no gmgetdate so we use gmdate instead - $time += intval((float)$timezone * HOURSECS); - $datestring = strftime('%S_%M_%H_%d_%m_%Y_%w_%j_%A_%B', $time); - list( - $getdate['seconds'], - $getdate['minutes'], - $getdate['hours'], - $getdate['mday'], - $getdate['mon'], - $getdate['year'], - $getdate['wday'], - $getdate['yday'], - $getdate['weekday'], - $getdate['month'] - ) = explode('_', $datestring); + $sql = "INSERT INTO {$CFG->prefix}stats_$type2 (stattype, timeend, courseid, roleid, stat1, stat2) - return $getdate; + SELECT stattype, (timeend - $y30) AS ntimeend, courseid, 0, SUM(stat1), SUM(stat2) + FROM {$CFG->prefix}stats_$type + WHERE (stattype = 'enrolments' OR stattype = 'activity') AND + roleid <> 0 + GROUP BY stattype, ntimeend, courseid"; + execute_sql($sql); + + $sql = "INSERT INTO {$CFG->prefix}stats_$type (stattype, timeend, courseid, roleid, stat1, stat2) + + SELECT stattype, (timeend + $y30), courseid, roleid, stat1, stat2 + FROM {$CFG->prefix}stats_$type2 + WHERE (stattype = 'enrolments' OR stattype = 'activity') AND + roleid = 0 AND timeend < $y20"; + execute_sql($sql); + + $sql = "DELETE FROM {$CFG->prefix}stats_$type2 + WHERE timeend < $limit"; + execute_sql($sql); + } } @@ -1155,22 +1464,25 @@ function stats_upgrade_for_roles_wrapper() { } $result = begin_sql(); - + $result = $result && stats_upgrade_user_table_for_roles('daily'); $result = $result && stats_upgrade_user_table_for_roles('weekly'); $result = $result && stats_upgrade_user_table_for_roles('monthly'); - + $result = $result && stats_upgrade_table_for_roles('daily'); $result = $result && stats_upgrade_table_for_roles('weekly'); $result = $result && stats_upgrade_table_for_roles('monthly'); - + $result = $result && commit_sql(); if (!empty($result)) { set_config('statsrolesupgraded',time()); } + // finally upgade totals, no big deal if it fails + stats_upgrade_totals(); + return $result; } @@ -1182,7 +1494,7 @@ function stats_upgrade_for_roles_wrapper() { * @return boolean @todo maybe something else (error message) depending on * how this will be called. */ -function stats_upgrade_user_table_for_roles ($period) { +function stats_upgrade_user_table_for_roles($period) { global $CFG; static $teacher_role_id, $student_role_id; @@ -1230,7 +1542,7 @@ function stats_upgrade_table_for_roles ($period) { if (!in_array($period, array('daily', 'weekly', 'monthly'))) { return false; } - + if (!$teacher_role_id) { $role = get_roles_with_capability('moodle/legacy:teacher', CAP_ALLOW); $role = array_keys($role); diff --git a/theme/standard/styles_layout.css b/theme/standard/styles_layout.css index 6724ff9c8c..02ebc64f6e 100644 --- a/theme/standard/styles_layout.css +++ b/theme/standard/styles_layout.css @@ -1154,12 +1154,11 @@ body#admin-modules table.generaltable td.c0 margin-right: auto; } -#admin-report-stats-index .invisiblefieldset { - display: block; -} - -#admin-report-courseoverview-index .invisiblefieldset { - display: block; +#admin-report-courseoverview-index .graph, +#course-report-stats-index .graph, +#admin-report-stats-index .graph { + text-align: center; + margin-bottom: 1em; } #admin-uploaduser table#uuresults { diff --git a/version.php b/version.php index dea5b84a63..8f0225e519 100644 --- a/version.php +++ b/version.php @@ -6,7 +6,7 @@ // This is compared against the values stored in the database to determine // whether upgrades should be performed (see lib/db/*.php) - $version = 2007101508.001; // YYYYMMDD = date + $version = 2007101508.002; // YYYYMMDD = date // XY = increments within a single day $release = '2.0 dev'; // Human-friendly version name -- 2.39.5