From b8e32e237af996a598207fec6ca2268628d91d69 Mon Sep 17 00:00:00 2001 From: Christian Hofmaier Date: Tue, 6 Dec 2016 15:56:04 +0100 Subject: [statistics_reporting] Added time bounds and time cutoff possibility. --- .../inc/statisticreporting.inc.php | 74 +++++++++++++++------- .../statistics_reporting/page.inc.php | 29 +++++---- 2 files changed, 66 insertions(+), 37 deletions(-) (limited to 'modules-available/statistics_reporting') diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php index 5bfba04f..a1a209d8 100644 --- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php +++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php @@ -3,59 +3,87 @@ class StatisticReporting { - public static function getClientStatistics() { + + public static function getClientStatistics($cutOffTimeInSeconds) { + $queryTime = time() - $cutOffTimeInSeconds; + // time bounds (8,22 means from 8 o clock to 22 o clock) + $lowerBound = 8; + $upperBound = 22; $res = Database::simpleQuery("SELECT t1.name, timeSum, avgTime, offlineSum, loginCount, lastLogout, lastStart FROM ( SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(statistic.data AS UNSIGNED)) AS 'timeSum', AVG(CAST(statistic.data AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount', MAX(statistic.dateline + (statistic.data *1)) AS 'lastLogout' FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - WHERE typeid = '~session-length' GROUP BY machine.machineuuid + WHERE typeid = '~session-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound)) + GROUP BY machine.machineuuid ) t1 INNER JOIN ( SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(statistic.data AS UNSIGNED)) AS 'offlineSum', MAX(statistic.dateline) AS 'lastStart' FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - WHERE typeid = '~offline-length' GROUP BY machine.machineuuid + WHERE typeid = '~offline-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound)) + GROUP BY machine.machineuuid ) t2 ON t1.uuid = t2.uuid"); return $res; } - public static function getLocationStatistics() { - $res = Database::simpleQuery("SELECT t1.ln, timeSum, avgTime, offlineSum, loginCount FROM ( - SELECT location.locationname AS 'ln', SUM(CAST(statistic.data AS UNSIGNED)) AS 'timeSum', AVG(CAST(statistic.data AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount' + public static function getLocationStatistics($cutOffTimeInSeconds) { + $queryTime = time() - $cutOffTimeInSeconds; + // time bounds (8,22 means from 8 o clock to 22 o clock) + $lowerBound = 8; + $upperBound = 22; + $res = Database::simpleQuery("SELECT t1.locName, timeSum, avgTime, offlineSum, loginCount FROM ( + SELECT location.locationname AS 'locName', SUM(CAST(statistic.data AS UNSIGNED)) AS 'timeSum', AVG(CAST(statistic.data AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount' FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid INNER JOIN location ON machine.locationid = location.locationid - WHERE statistic.typeid = '~session-length' GROUP By location.locationname + WHERE statistic.typeid = '~session-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound)) + GROUP By location.locationname ) t1 INNER JOIN ( - SELECT location.locationname AS 'ln', SUM(CAST(statistic.data AS UNSIGNED)) AS 'offlineSum' + SELECT location.locationname AS 'locName', SUM(CAST(statistic.data AS UNSIGNED)) AS 'offlineSum' FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid INNER JOIN location ON machine.locationid = location.locationid - WHERE statistic.typeid = '~offline-length' GROUP By location.locationname - ) t2 ON t1.ln = t2.ln"); + WHERE statistic.typeid = '~offline-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound)) + GROUP By location.locationname + ) t2 ON t1.locName = t2.locName"); return $res; } - public static function getUserStatistics() { - $res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY username ORDER BY 2 DESC"); + public static function getUserStatistics($cutOffTimeInSeconds) { + $queryTime = time() - $cutOffTimeInSeconds; + // time bounds (8,22 means from 8 o clock to 22 o clock) + $lowerBound = 8; + $upperBound = 22; + $res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline>=$queryTime + AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound)) GROUP BY username ORDER BY 2 DESC"); return $res; } - public static function getVMStatistics() { - $res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY data ORDER BY 2 DESC"); + public static function getVMStatistics($cutOffTimeInSeconds) { + $queryTime = time() - $cutOffTimeInSeconds; + // time bounds (8,22 means from 8 o clock to 22 o clock) + $lowerBound = 8; + $upperBound = 22; + $res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline>=$queryTime + AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound)) GROUP BY data ORDER BY 2 DESC"); return $res; } - public static function getOverallStatistics () { - $res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)), AVG(CAST(data AS UNSIGNED)), COUNT(*) FROM statistic WHERE typeid = '~session-length'"); + public static function getOverallStatistics ($cutOffTimeInSeconds) { + $queryTime = time() - $cutOffTimeInSeconds; + // time bounds (8,22 means from 8 o clock to 22 o clock) + $lowerBound = 8; + $upperBound = 22; + $res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)), AVG(CAST(data AS UNSIGNED)), COUNT(*) FROM statistic WHERE typeid = '~session-length' AND dateline>=$queryTime + AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound))"); return $res; } - public static function getTotalOffline() { - $res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)) FROM statistic WHERE typeid='~offline-length'"); + public static function getTotalOfflineStatistics($cutOffTimeInSeconds) { + $queryTime = time() - $cutOffTimeInSeconds; + // time bounds (8,22 means from 8 o clock to 22 o clock) + $lowerBound = 8; + $upperBound = 22; + $res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)) FROM statistic WHERE typeid='~offline-length' AND dateline>=$queryTime + AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound))"); return $res; } - - - - - public static function formatSeconds($seconds) { return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s'; diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php index aaf191df..e009317c 100644 --- a/modules-available/statistics_reporting/page.inc.php +++ b/modules-available/statistics_reporting/page.inc.php @@ -3,6 +3,9 @@ class Page_Statistics_Reporting extends Page { + private $cutOffTimer; + + /** * Called before any page rendering happens - early hook to check parameters etc. */ @@ -21,25 +24,24 @@ class Page_Statistics_Reporting extends Page */ protected function doRender() { - // TODO: got empty machine with alot of logins. isn't shown in the inner-join hostname query below. - // $res = Database::simpleQuery("SELECT machineuuid as 'hostname', COUNT(*) AS 'count' FROM statistic WHERE typeid='~session-length' GROUP BY machineuuid ORDER BY 2 DESC"); - - // TODO: session-length liefert 3000 Einträge mehr als vmchooser-session? (logins ohne ausgewählter VM -> Session endet trotzdem mit session-length Meldung) - - // TODO change AVG to median + // timespan you want to see = Days selected * seconds per Day + // default = 14 days + $cutOff = Request::get('cutoff', 14, 'int'); + $chooserData = array('cutoff' => $cutOff); + $this->cutOffTimer = $cutOff * 86400; // total time online, average time online, total number of logins - $res = StatisticReporting::getOverallStatistics(); + $res = StatisticReporting::getOverallStatistics($this->cutOffTimer); $row = $res->fetch(PDO::FETCH_NUM); $data = array('time' => StatisticReporting::formatSeconds($row[0]), 'avgTime' => StatisticReporting::formatSeconds($row[1]), 'totalLogins' => $row[2]); //total time offline - $res = StatisticReporting::getTotalOffline(); + $res = StatisticReporting::getTotalOfflineStatistics($this->cutOffTimer); $row = $res->fetch(PDO::FETCH_NUM); $data = array_merge($data, array('totalOfftime' => StatisticReporting::formatSeconds($row[0]))); // per location - $res = StatisticReporting::getLocationStatistics(); + $res = StatisticReporting::getLocationStatistics($this->cutOffTimer); $data[] = array('perLocation' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { $data['perLocation'][] = array('location' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1], @@ -47,7 +49,7 @@ class Page_Statistics_Reporting extends Page } // per client - $res = StatisticReporting::getClientStatistics(); + $res = StatisticReporting::getClientStatistics($this->cutOffTimer); $data[] = array('perClient' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { $data['perClient'][] = array('hostname' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1], @@ -56,21 +58,20 @@ class Page_Statistics_Reporting extends Page } // per user - $res = StatisticReporting::getUserStatistics(); + $res = StatisticReporting::getUserStatistics($this->cutOffTimer); $data[] = array('perUser' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { $data['perUser'][] = array('user' => $row[0], 'loginCount' => $row[1]); } // per vm - $res = StatisticReporting::getVMStatistics(); + $res = StatisticReporting::getVMStatistics($this->cutOffTimer); $data[] = array('perVM' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { $data['perVM'][] = array('vm' => $row[0], 'loginCount' => $row[1]); } - - Render::addTemplate('columnChooser'); + Render::addTemplate('columnChooser', $chooserData); Render::addTemplate('_page', $data); } } -- cgit v1.2.3-55-g7522