From 2ab12fa0ce5fe4c916d5426e05656e61b4005b1e Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Thu, 22 Dec 2016 16:10:13 +0100 Subject: [statistics_reporting] changed cutoffs --- .../inc/statisticreporting.inc.php | 41 +++++++++------------- .../lang/de/template-tags.json | 4 +-- .../lang/en/template-tags.json | 4 +-- .../statistics_reporting/page.inc.php | 15 ++++---- 4 files changed, 28 insertions(+), 36 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 7b1dcfdc..626aa290 100644 --- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php +++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php @@ -4,17 +4,16 @@ class StatisticReporting { - public static function getClientStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { - $queryTime = time() - $cutOffTimeInSeconds; + public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { $res = Database::simpleQuery("SELECT t1.name, timeSum, medianTime, offlineSum, longSessions, lastLogout, lastStart, shortSessions FROM ( SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout' - FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $queryTime)." sessionTable + FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid GROUP BY machine.machineuuid ) t1 INNER JOIN ( SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum', MAX(offlineTable.dateline) AS 'lastStart' - FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $queryTime)." offlineTable + FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid GROUP BY machine.machineuuid ) t2 @@ -23,19 +22,17 @@ class StatisticReporting } // IFNULL(location.locationname, '') - emptry string can be replaced with anything (name of the null-ids in the table) - public static function getLocationStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { - $queryTime = time() - $cutOffTimeInSeconds; - + public static function getLocationStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { $res = Database::simpleQuery("SELECT t1.locName, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions' - FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $queryTime)." sessionTable + FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid GROUP BY location.locationname ) t1 INNER JOIN ( SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' - FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $queryTime)." offlineTable + FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid GROUP BY location.locationname @@ -45,35 +42,31 @@ class StatisticReporting } // logins between bounds - public static function getUserStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { - $queryTime = time() - $cutOffTimeInSeconds; + public static function getUserStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { $res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' FROM statistic - WHERE typeid='.vmchooser-session-name' AND dateline>=$queryTime + WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) GROUP BY username ORDER BY 2 DESC"); return $res; } // vm logins between bounds - public static function getVMStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { - $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline>=$queryTime + public static function getVMStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + $res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) GROUP BY data ORDER BY 2 DESC"); return $res; } - public static function getOverallStatistics ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { - $queryTime = time() - $cutOffTimeInSeconds; + public static function getOverallStatistics ($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { $res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, SUM(sessionTable.length >= 60) AS longSessions, SUM(sessionTable.length < 60) AS shortSessions - FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $queryTime)." sessionTable"); + FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable"); return $res; } - public static function getTotalOfflineStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { - $queryTime = time() - $cutOffTimeInSeconds; + public static function getTotalOfflineStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { $res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED)) - FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $queryTime)." offlineTable"); + FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable"); return $res; } @@ -97,13 +90,13 @@ class StatisticReporting return round($median); } - private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound, $cutoffTime) + private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound, $cutOff) { $lowerFormat = "'%y-%m-%d $lowerTimeBound:00:00'"; $upperFormat = "'%y-%m-%d ".($upperTimeBound-1).":59:59'"; $queryString = " select - + @startLower := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat)), @startUpper := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)), @endLower := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)), @@ -157,7 +150,7 @@ class StatisticReporting machineuuid from statistic - where dateline >= $cutoffTime and typeid = '$typeid' and ( + where dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) and typeid = '$typeid' and ( (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat)) <= dateline and dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat))) or (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)) <= dateline+data and dateline+data <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat))) ) diff --git a/modules-available/statistics_reporting/lang/de/template-tags.json b/modules-available/statistics_reporting/lang/de/template-tags.json index 68b68250..207949a9 100644 --- a/modules-available/statistics_reporting/lang/de/template-tags.json +++ b/modules-available/statistics_reporting/lang/de/template-tags.json @@ -20,8 +20,8 @@ "lang_location": "Raum", "lang_medianSessionLength": "Mittlere Sitzungsdauer", "lang_totalTime": "Gesamte Zeit", - "lang_last1": "Letzten 24 Stunden", - "lang_last2": "Letzten 48 Stunden", + "lang_last1": "Heute", + "lang_last2": "Letzten 2 Tage", "lang_last7": "Letzten 7 Tage", "lang_last14": "Letzten 14 Tage", "lang_last30": "Letzten 30 Tage", diff --git a/modules-available/statistics_reporting/lang/en/template-tags.json b/modules-available/statistics_reporting/lang/en/template-tags.json index b9e0c61d..e9430960 100644 --- a/modules-available/statistics_reporting/lang/en/template-tags.json +++ b/modules-available/statistics_reporting/lang/en/template-tags.json @@ -20,8 +20,8 @@ "lang_location": "Location", "lang_medianSessionLength": "Median Session Length", "lang_totalTime": "Total Time", - "lang_last1": "Last 24 hours", - "lang_last2": "Last 48 hours", + "lang_last1": "Today", + "lang_last2": "Last 2 days", "lang_last7": "Last 7 days", "lang_last14": "Last 14 days", "lang_last30": "Last 30 days", diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php index f0f56e87..a55ba33f 100644 --- a/modules-available/statistics_reporting/page.inc.php +++ b/modules-available/statistics_reporting/page.inc.php @@ -24,8 +24,7 @@ class Page_Statistics_Reporting extends Page { // timespan you want to see = Days selected * seconds per Day (86400) // default = 14 days - $cutOff = Request::get('cutoff', 14, 'int'); - $cutOffTimer = $cutOff * 86400; + $cutOff = Request::get('cutoff', 14, 'int') - 1; $lowerTimeBound = Request::get('lower', 0, 'int'); @@ -33,17 +32,17 @@ class Page_Statistics_Reporting extends Page // total time online, average time online, total number of logins - $res = StatisticReporting::getOverallStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound); + $res = StatisticReporting::getOverallStatistics($cutOff, $lowerTimeBound, $upperTimeBound); $row = $res->fetch(PDO::FETCH_NUM); $data = array('time' => StatisticReporting::formatSeconds($row[0]), 'medianTime' => StatisticReporting::formatSeconds(StatisticReporting::calcMedian($row[1])), 'sessions' => $row[2], 'shortSessions' => $row[3]); //total time offline - $res = StatisticReporting::getTotalOfflineStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound); + $res = StatisticReporting::getTotalOfflineStatistics($cutOff, $lowerTimeBound, $upperTimeBound); $row = $res->fetch(PDO::FETCH_NUM); $data = array_merge($data, array('totalOfftime' => StatisticReporting::formatSeconds($row[0]))); // per location - $res = StatisticReporting::getLocationStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound); + $res = StatisticReporting::getLocationStatistics($cutOff, $lowerTimeBound, $upperTimeBound); $data[] = array('perLocation' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { $median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row[2])); @@ -52,7 +51,7 @@ class Page_Statistics_Reporting extends Page } // per client - $res = StatisticReporting::getClientStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound); + $res = StatisticReporting::getClientStatistics($cutOff, $lowerTimeBound, $upperTimeBound); $data[] = array('perClient' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { $median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row[2])); @@ -62,14 +61,14 @@ class Page_Statistics_Reporting extends Page } // per user - $res = StatisticReporting::getUserStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound); + $res = StatisticReporting::getUserStatistics($cutOff, $lowerTimeBound, $upperTimeBound); $data[] = array('perUser' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { $data['perUser'][] = array('user' => $row[0], 'sessions' => $row[1]); } // per vm - $res = StatisticReporting::getVMStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound); + $res = StatisticReporting::getVMStatistics($cutOff, $lowerTimeBound, $upperTimeBound); $data[] = array('perVM' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { $data['perVM'][] = array('vm' => $row[0], 'sessions' => $row[1]); -- cgit v1.2.3-55-g7522