From 9ccef127acd6dca0971e12726b019e2beaf7e4ce Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Thu, 12 Jan 2017 12:41:21 +0100 Subject: [statistics_reporting] moved some functions to a new class --- .../statistics_reporting/inc/queries.inc.php | 153 +++++++++++++++++++++ 1 file changed, 153 insertions(+) create mode 100644 modules-available/statistics_reporting/inc/queries.inc.php (limited to 'modules-available/statistics_reporting/inc/queries.inc.php') diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php new file mode 100644 index 00000000..23afceed --- /dev/null +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -0,0 +1,153 @@ + 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized) + public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); + $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(locId) AS locHash, MD5(t1.uuid) AS clientHash 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', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' + 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 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, $cutOff)." offlineTable + INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid + GROUP BY machine.machineuuid + ) t2 + ON t1.uuid = t2.uuid"); + return $res; + } + + // Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec + public static function getLocationStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); + $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(locId) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( + SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', 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, $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, '$notassigned') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' + 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 + ) t2 + ON t1.locName = t2.locName"); + return $res; + } + + // User Data: Name, Name(anonymized), Number of Logins + public static function getUserStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(username)) AS userHash, 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 username + ORDER BY 2 DESC"); + return $res; + } + + // Virtual Machine Data: Name, Number of Usages + public static function getVMStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + $res = Database::simpleQuery("SELECT data AS name, 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; + } + + //Total Data: Time Online, Median Time Online, Number of Sessions > 60Sec, Number of Sessions < 60Sec + 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, $cutOff)." sessionTable"); + return $res; + } + + // Total Data(2): Time Offline + public static function getTotalOfflineStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + $res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED)) AS timeOff + FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable"); + return $res; + } + + // query string which provides table with time-cutoff and time-bounds + 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)), + @endUpper := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat)), + + (CAST(data AS SIGNED) + - IF( + dateline > @startUpper, + UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat) + INTERVAL 1 DAY) - dateline, + IF( + dateline < @startLower, + @startLower - dateline, + 0 + ) + ) + - IF( + dateline+data > @endUpper, + dateline+data - (@endUpper + 1), + IF( + dateline+data < @endLower, + dateline+data - (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat) - INTERVAL 1 DAY) + 1), + 0 + ) + ) + - ( TO_DAYS(FROM_UNIXTIME(dateline+data, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(dateline, '%y-%m-%d')) + - 2 + + IF(dateline <= @startUpper, 1, 0) + + IF(dateline+data >= @endLower, 1, 0) + ) * ((24 - ($upperTimeBound - $lowerTimeBound)) * 3600) + + - IF( + @leftBound := IF(dateline <= @startUpper, @startUpper, UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat) + INTERVAL 1 DAY)) + < @rightBound := IF(dateline+data >= @endLower, @endLower, UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat) - INTERVAL 1 DAY)), + IF( + @timeDiff := ( + (date_format(from_unixtime(@leftBound), '%H') - + date_format(convert_tz(from_unixtime(@leftBound), @@session.time_zone, '+00:00'), '%H') + 24) % 24 + - + (date_format(from_unixtime(@rightBound), '%H') - + date_format(convert_tz(from_unixtime(@rightBound), @@session.time_zone, '+00:00'), '%H') + 24) % 24) = 1 + AND ($lowerTimeBound >= 2 OR $upperTimeBound <= 2), + 3600, + IF(@timeDiff = -1 AND ($lowerTimeBound >= 3 OR $upperTimeBound <= 3), -3600, 0) + ), + 0 + ) + + ) as 'length', + dateline, + data, + machineuuid + + from statistic + 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))) + ) + "; + return "(".$queryString.")"; + } +} + -- cgit v1.2.3-55-g7522 From 5da76e80a9fb64bfbda5897aa08fa1bc575dbd25 Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Fri, 13 Jan 2017 13:22:27 +0100 Subject: [statistics_reporting] improved cutoff in query functions + bugfix --- .../statistics_reporting/hooks/cron.inc.php | 3 + .../statistics_reporting/inc/getdata.inc.php | 17 ++-- .../statistics_reporting/inc/queries.inc.php | 91 +++++++++++++--------- .../statistics_reporting/page.inc.php | 5 +- 4 files changed, 70 insertions(+), 46 deletions(-) create mode 100644 modules-available/statistics_reporting/hooks/cron.inc.php (limited to 'modules-available/statistics_reporting/inc/queries.inc.php') diff --git a/modules-available/statistics_reporting/hooks/cron.inc.php b/modules-available/statistics_reporting/hooks/cron.inc.php new file mode 100644 index 00000000..b79883df --- /dev/null +++ b/modules-available/statistics_reporting/hooks/cron.inc.php @@ -0,0 +1,3 @@ +fetch(PDO::FETCH_ASSOC); $data = array('time' => self::formatSeconds($row['sum']), 'medianTime' => self::formatSeconds(self::calcMedian($row['median'])), 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); //total time offline - $res = Queries::getTotalOfflineStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $res = Queries::getTotalOfflineStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $row = $res->fetch(PDO::FETCH_ASSOC); $data = array_merge($data, array('totalOfftime' => self::formatSeconds($row['timeOff']))); @@ -24,7 +25,7 @@ class GetData // per location public static function perLocation($anonymize = false) { - $res = Queries::getLocationStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $res = Queries::getLocationStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); $loc = $anonymize ? 'locHash' : 'locName'; while ($row = $res->fetch(PDO::FETCH_ASSOC)) { @@ -37,9 +38,9 @@ class GetData // per client public static function perClient($anonymize = false) { - $res = Queries::getClientStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $res = Queries::getClientStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); - $name = $anonymize ? 'clientHash' : 'hostname'; + $name = $anonymize ? 'clientHash' : 'clientName'; $loc = $anonymize ? 'locHash' : 'locName'; while ($row = $res->fetch(PDO::FETCH_ASSOC)) { $median = self::calcMedian(self::calcMedian($row['medianTime'])); @@ -52,7 +53,7 @@ class GetData // per user public static function perUser($anonymize = false) { - $res = Queries::getUserStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $res = Queries::getUserStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); $user = $anonymize ? 'userHash' : 'name'; while ($row = $res->fetch(PDO::FETCH_ASSOC)) { @@ -64,7 +65,7 @@ class GetData // per vm public static function perVM() { - $res = Queries::getVMStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $res = Queries::getVMStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); while ($row = $res->fetch(PDO::FETCH_ASSOC)) { $data[] = array('vm' => $row['name'], 'sessions' => $row['count']); diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index 23afceed..23f5fb03 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -5,18 +5,18 @@ class Queries { // Client Data: Name, Time Online, Median Time Online, Time Offline, last start, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized) - public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(locId) AS locHash, MD5(t1.uuid) AS clientHash 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', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' - FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable + FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid 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, $cutOff)." offlineTable + FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid GROUP BY machine.machineuuid ) t2 @@ -25,18 +25,18 @@ class Queries } // Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec - public static function getLocationStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(locId) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', 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, $cutOff)." sessionTable + FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." 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, '$notassigned') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' - FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable + FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid GROUP BY location.locationname @@ -46,80 +46,96 @@ class Queries } // User Data: Name, Name(anonymized), Number of Logins - public static function getUserStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { - $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(username)) AS userHash, COUNT(*) AS 'count' + public static function getUserStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { + $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(username)) AS userHash, 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)) + WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND ( + (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0 + and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) AND (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) + or + (@daysDiff = 1 and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) OR (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) + or + @daysDiff >= 2 + ) GROUP BY username ORDER BY 2 DESC"); return $res; } // Virtual Machine Data: Name, Number of Usages - public static function getVMStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { - $res = Database::simpleQuery("SELECT data AS name, 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)) + public static function getVMStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { + $res = Database::simpleQuery("SELECT data AS name, COUNT(*) AS 'count' + FROM statistic + WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND ( + (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0 + and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) AND (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) + or + (@daysDiff = 1 and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) OR (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) + or + @daysDiff >= 2 + ) GROUP BY data ORDER BY 2 DESC"); return $res; } //Total Data: Time Online, Median Time Online, Number of Sessions > 60Sec, Number of Sessions < 60Sec - public static function getOverallStatistics ($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + public static function getOverallStatistics ($from, $to, $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, $cutOff)." sessionTable"); + FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable"); return $res; } // Total Data(2): Time Offline - public static function getTotalOfflineStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + public static function getTotalOfflineStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { $res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED)) AS timeOff - FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable"); + FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable"); return $res; } // query string which provides table with time-cutoff and time-bounds - private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound, $cutOff) + private static function getBoundedTableQueryString($typeid, $from, $to, $lowerTimeBound, $upperTimeBound) { $lowerFormat = "'%y-%m-%d $lowerTimeBound:00:00'"; $upperFormat = "'%y-%m-%d ".($upperTimeBound-1).":59:59'"; $queryString = " select + @start := IF(dateline < $from, $from, dateline), + @end := IF(dateline+data > $to, $to, dateline+data), @startLower := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat)), @startUpper := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)), @endLower := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)), @endUpper := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat)), - (CAST(data AS SIGNED) + (CAST(@end-@start AS SIGNED) - IF( - dateline > @startUpper, - UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat) + INTERVAL 1 DAY) - dateline, + @start > @startUpper, + UNIX_TIMESTAMP(FROM_UNIXTIME(@start, $lowerFormat) + INTERVAL 1 DAY) - @start, IF( - dateline < @startLower, - @startLower - dateline, + @start < @startLower, + @startLower - @start, 0 ) ) - IF( - dateline+data > @endUpper, - dateline+data - (@endUpper + 1), + @end > @endUpper, + @end - (@endUpper + 1), IF( - dateline+data < @endLower, - dateline+data - (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat) - INTERVAL 1 DAY) + 1), + @end < @endLower, + @end - (UNIX_TIMESTAMP(FROM_UNIXTIME(@end, $upperFormat) - INTERVAL 1 DAY) + 1), 0 ) ) - - ( TO_DAYS(FROM_UNIXTIME(dateline+data, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(dateline, '%y-%m-%d')) - - 2 - + IF(dateline <= @startUpper, 1, 0) - + IF(dateline+data >= @endLower, 1, 0) + - ( TO_DAYS(FROM_UNIXTIME(@end, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start, '%y-%m-%d')) + - 2 + + IF(@start <= @startUpper, 1, 0) + + IF(@end >= @endLower, 1, 0) ) * ((24 - ($upperTimeBound - $lowerTimeBound)) * 3600) - IF( - @leftBound := IF(dateline <= @startUpper, @startUpper, UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat) + INTERVAL 1 DAY)) - < @rightBound := IF(dateline+data >= @endLower, @endLower, UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat) - INTERVAL 1 DAY)), + @leftBound := IF(@start <= @startUpper, @startUpper, UNIX_TIMESTAMP(FROM_UNIXTIME(@start, $upperFormat) + INTERVAL 1 DAY)) + < @rightBound := IF(@end >= @endLower, @endLower, UNIX_TIMESTAMP(FROM_UNIXTIME(@end, $lowerFormat) - INTERVAL 1 DAY)), IF( @timeDiff := ( (date_format(from_unixtime(@leftBound), '%H') - @@ -133,18 +149,19 @@ class Queries ), 0 ) - ) as 'length', dateline, data, machineuuid from statistic - where dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) and typeid = '$typeid' + where dateline+data >= $from and dateline <= $to and typeid = '$typeid' and ( - (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat)) <= dateline and dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat))) + (@daysDiff := (TO_DAYS(FROM_UNIXTIME(dateline+data, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(dateline, '%y-%m-%d'))) = 0 and (dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)) and dateline+data >= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)))) or - (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)) <= dateline+data and dateline+data <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat))) + (@daysDiff = 1 and (dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)) or dateline+data >= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)))) + or + @daysDiff >= 2 ) "; return "(".$queryString.")"; diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php index 82c03acc..aa373f54 100644 --- a/modules-available/statistics_reporting/page.inc.php +++ b/modules-available/statistics_reporting/page.inc.php @@ -24,7 +24,10 @@ class Page_Statistics_Reporting extends Page { // timespan you want to see = Days selected * seconds per Day (86400) // default = 14 days - GetData::$cutOff = Request::get('cutoff', 14, 'int') - 1; + $date = new DateTime(date('Y-m-d 00:00:00')); + $date->sub(new DateInterval('P'.(Request::get('cutoff', 14, 'int') - 1).'D')); + GetData::$from = $date->getTimestamp(); + GetData::$to = time(); GetData::$lowerTimeBound = Request::get('lower', 0, 'int'); GetData::$upperTimeBound = Request::get('upper', 24, 'int'); -- cgit v1.2.3-55-g7522 From 4910fa0b9f5ca6b6063965cc516ced8d72f8be5b Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Tue, 17 Jan 2017 13:47:24 +0100 Subject: [statistics_reporting] added vm name anonymization --- modules-available/statistics_reporting/hooks/cron.inc.php | 2 +- modules-available/statistics_reporting/inc/getdata.inc.php | 5 +++-- modules-available/statistics_reporting/inc/queries.inc.php | 2 +- 3 files changed, 5 insertions(+), 4 deletions(-) (limited to 'modules-available/statistics_reporting/inc/queries.inc.php') diff --git a/modules-available/statistics_reporting/hooks/cron.inc.php b/modules-available/statistics_reporting/hooks/cron.inc.php index 57e64a84..9b32bc96 100644 --- a/modules-available/statistics_reporting/hooks/cron.inc.php +++ b/modules-available/statistics_reporting/hooks/cron.inc.php @@ -16,7 +16,7 @@ if ($nextReporting < $time && $allowReport) { $data['perLocation'] = GetData::perLocation(true); $data['perClient'] = GetData::perClient(true); $data['perUser'] = GetData::perUser(true); - $data['perVM'] = GetData::perVM(); + $data['perVM'] = GetData::perVM(true); $statisticsReport = json_encode($data); diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php index 880256d6..cfb63658 100644 --- a/modules-available/statistics_reporting/inc/getdata.inc.php +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -84,11 +84,12 @@ class GetData // per vm - public static function perVM() { + public static function perVM($anonymize = false) { $res = Queries::getVMStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); + $vm = $anonymize ? 'vmHash' : 'name'; while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $data[] = array('vm' => $row['name'], 'sessions' => $row['count']); + $data[] = array('vm' => $row[$vm], 'sessions' => $row['count']); } return $data; } diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index 23f5fb03..219640a6 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -64,7 +64,7 @@ class Queries // Virtual Machine Data: Name, Number of Usages public static function getVMStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { - $res = Database::simpleQuery("SELECT data AS name, COUNT(*) AS 'count' + $res = Database::simpleQuery("SELECT data AS name, MD5(data) AS vmHash, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND ( (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0 -- cgit v1.2.3-55-g7522 From 6e9d2635c9fe69923a4c30cb99ab6938bfbffe27 Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Tue, 17 Jan 2017 14:24:41 +0100 Subject: [statistics_reporting] improved anonymization --- .../statistics_reporting/hooks/cron.inc.php | 19 ++++++++----------- .../statistics_reporting/inc/getdata.inc.php | 2 +- .../statistics_reporting/inc/queries.inc.php | 17 +++++++++-------- 3 files changed, 18 insertions(+), 20 deletions(-) (limited to 'modules-available/statistics_reporting/inc/queries.inc.php') diff --git a/modules-available/statistics_reporting/hooks/cron.inc.php b/modules-available/statistics_reporting/hooks/cron.inc.php index 9b32bc96..bd427e64 100644 --- a/modules-available/statistics_reporting/hooks/cron.inc.php +++ b/modules-available/statistics_reporting/hooks/cron.inc.php @@ -9,8 +9,9 @@ if ($nextReporting < $time && $allowReport) { Property::set("nextReporting", strtotime("Sunday 23:59:59")); - GetData::$from = strtotime("Monday last week"); - GetData::$to = strtotime("Sunday last week 23:59:59"); + GetData::$from = strtotime("last sunday - 6 days"); + GetData::$to = strtotime("last sunday 23:59:59"); + GetData::$salt = bin2hex(random_bytes(20)); $data = array_merge(GetData::total(true), array('perLocation' => array(), 'perClient' => array(), 'perUser' => array(), 'perVM' => array())); $data['perLocation'] = GetData::perLocation(true); @@ -21,15 +22,11 @@ if ($nextReporting < $time && $allowReport) { $statisticsReport = json_encode($data); - $url = CONFIG_REPORTING_URL; + $params = array("action" => "statistics", "data" => $statisticsReport); - $curl = curl_init($url); - curl_setopt($curl, CURLOPT_RETURNTRANSFER, true); - curl_setopt($curl, CURLOPT_HTTPHEADER, array("Content-type: application/json")); - curl_setopt($curl, CURLOPT_POST, true); - curl_setopt($curl, CURLOPT_POSTFIELDS, $statisticsReport); + Download::asStringPost(CONFIG_REPORTING_URL, $params, 300, $code); - $json_response = curl_exec($curl); - - curl_close($curl); + if ($code != 200) { + EventLog::warning("Statistics Reporting: ".$code); + } } diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php index cfb63658..07a13a0c 100644 --- a/modules-available/statistics_reporting/inc/getdata.inc.php +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -6,7 +6,7 @@ class GetData public static $to; public static $lowerTimeBound = 0; public static $upperTimeBound = 24; - + public static $salt; // total public static function total($anonymize = false) { diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index 219640a6..77deb4f8 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -7,7 +7,7 @@ class Queries // Client Data: Name, Time Online, Median Time Online, Time Offline, last start, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized) public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); - $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(locId) AS locHash, MD5(t1.uuid) AS clientHash FROM ( + $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash 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', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid @@ -20,14 +20,15 @@ class Queries INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid GROUP BY machine.machineuuid ) t2 - ON t1.uuid = t2.uuid"); + ON t1.uuid = t2.uuid", array("salt" => GetData::$salt)); + return $res; } // Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); - $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(locId) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( + $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(CONCAT(locId, :salt)) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', 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', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid @@ -41,13 +42,13 @@ class Queries LEFT JOIN location ON machine.locationid = location.locationid GROUP BY location.locationname ) t2 - ON t1.locName = t2.locName"); + ON t1.locName = t2.locName", array("salt" => GetData::$salt)); return $res; } // User Data: Name, Name(anonymized), Number of Logins public static function getUserStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { - $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(username)) AS userHash, COUNT(*) AS 'count' + $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(CONCAT(username, :salt))) AS userHash, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND ( (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0 @@ -58,13 +59,13 @@ class Queries @daysDiff >= 2 ) GROUP BY username - ORDER BY 2 DESC"); + ORDER BY 2 DESC", array("salt" => GetData::$salt)); return $res; } // Virtual Machine Data: Name, Number of Usages public static function getVMStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { - $res = Database::simpleQuery("SELECT data AS name, MD5(data) AS vmHash, COUNT(*) AS 'count' + $res = Database::simpleQuery("SELECT data AS name, MD5(CONCAT(data, :salt)) AS vmHash, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND ( (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0 @@ -75,7 +76,7 @@ class Queries @daysDiff >= 2 ) GROUP BY data - ORDER BY 2 DESC"); + ORDER BY 2 DESC", array("salt" => GetData::$salt)); return $res; } -- cgit v1.2.3-55-g7522 From 2963be53876e57c0d403ffe5dfa978d7736a0c4b Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Thu, 19 Jan 2017 13:18:40 +0100 Subject: [statistics_reporting] i18n: Move tags used in code to separate file, delete unused tags --- .../statistics_reporting/inc/queries.inc.php | 4 +- .../statistics_reporting/lang/de/module.json | 1 + .../lang/de/template-tags.json | 44 ++++++++++------------ .../statistics_reporting/lang/en/module.json | 1 + .../lang/en/template-tags.json | 44 ++++++++++------------ 5 files changed, 42 insertions(+), 52 deletions(-) (limited to 'modules-available/statistics_reporting/inc/queries.inc.php') diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index 77deb4f8..2b2ebb13 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -6,7 +6,7 @@ class Queries // Client Data: Name, Time Online, Median Time Online, Time Offline, last start, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized) public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { - $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); + $notassigned = Dictionary::translate('notAssigned', true); $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash 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', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable @@ -27,7 +27,7 @@ class Queries // Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { - $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); + $notassigned = Dictionary::translate('notAssigned', true); $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(CONCAT(locId, :salt)) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', 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', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable diff --git a/modules-available/statistics_reporting/lang/de/module.json b/modules-available/statistics_reporting/lang/de/module.json index bdbfe4fd..86ed5f45 100644 --- a/modules-available/statistics_reporting/lang/de/module.json +++ b/modules-available/statistics_reporting/lang/de/module.json @@ -1,4 +1,5 @@ { "module_name": "Statistik Auswertung", + "notAssigned": "Nicht zugewiesen", "page_title": "Statistik Auswertung" } \ No newline at end of file diff --git a/modules-available/statistics_reporting/lang/de/template-tags.json b/modules-available/statistics_reporting/lang/de/template-tags.json index 73c39e1e..d4d7887a 100644 --- a/modules-available/statistics_reporting/lang/de/template-tags.json +++ b/modules-available/statistics_reporting/lang/de/template-tags.json @@ -1,34 +1,28 @@ { - "lang_user": "Nutzer", - "lang_countLogins": "Anzahl Logins", - "lang_client": "Client", - "lang_vm": "VM", - "lang_sessions": "Sitzungen", - "lang_longSessions": "Sitzungen \u2265 60s", - "lang_shortSessions": "Sitzungen < 60s", - "lang_overallOfftime": "Insgesamte Zeit Offline", - "lang_totalOffTime": "Gesamtzeit Offline", + "lang_apply": "Anwenden", "lang_clientLogout": "Letzter VM Logout", "lang_clientStart": "Letzter Client Boot", - "lang_room": "Raum", - "lang_offTime": "Zeit seit Logout", - "lang_total": "Gesamt", - "lang_perlocation": "Pro Raum", - "lang_perclient": "Pro Client", - "lang_peruser": "Pro Benutzer", - "lang_pervm": "Pro VM", "lang_hostname": "Client Name", - "lang_location": "Raum", - "lang_medianSessionLength": "Mittlere Sitzungsdauer", - "lang_totalTime": "Gesamte Zeit", "lang_last1": "Heute", - "lang_last2": "Letzten 2 Tage", - "lang_last7": "Letzten 7 Tage", "lang_last14": "Letzten 14 Tage", + "lang_last2": "Letzten 2 Tage", "lang_last30": "Letzten 30 Tage", + "lang_last7": "Letzten 7 Tage", "lang_last90": "Letzten 90 Tage", - "lang_apply": "Anwenden", - "lang_save": "Speichern", - "lang_notassigned": "NICHT ZUGEWIESEN", - "lang_reportingDescription": "Helfen Sie uns bwLehrpool, durch das wöchentliche, automatische Verschicken eines anonymisierten Statistikberichts, zu verbessern." + "lang_location": "Raum", + "lang_longSessions": "Sitzungen \u2265 60s", + "lang_medianSessionLength": "Mittlere Sitzungsdauer", + "lang_overallOfftime": "Insgesamte Zeit Offline", + "lang_perclient": "Pro Client", + "lang_perlocation": "Pro Raum", + "lang_peruser": "Pro Benutzer", + "lang_pervm": "Pro VM", + "lang_reportingDescription": "Helfen Sie uns bwLehrpool, durch das w\u00f6chentliche, automatische Verschicken eines anonymisierten Statistikberichts, zu verbessern.", + "lang_sessions": "Sitzungen", + "lang_shortSessions": "Sitzungen < 60s", + "lang_total": "Gesamt", + "lang_totalOffTime": "Gesamtzeit Offline", + "lang_totalTime": "Gesamte Zeit", + "lang_user": "Nutzer", + "lang_vm": "VM" } \ No newline at end of file diff --git a/modules-available/statistics_reporting/lang/en/module.json b/modules-available/statistics_reporting/lang/en/module.json index 6b3f98f6..5362fee2 100644 --- a/modules-available/statistics_reporting/lang/en/module.json +++ b/modules-available/statistics_reporting/lang/en/module.json @@ -1,4 +1,5 @@ { "module_name": "Statistics Reporting", + "notAssigned": "Not assigned", "page_title": "Statistics Reporting" } \ No newline at end of file diff --git a/modules-available/statistics_reporting/lang/en/template-tags.json b/modules-available/statistics_reporting/lang/en/template-tags.json index 90d59b38..0047c7a4 100644 --- a/modules-available/statistics_reporting/lang/en/template-tags.json +++ b/modules-available/statistics_reporting/lang/en/template-tags.json @@ -1,34 +1,28 @@ { - "lang_user": "User", - "lang_countLogins": "Number of Logins", - "lang_client": "Client", - "lang_vm": "VM", - "lang_sessions": "Sessions", - "lang_longSessions": "Sessions \u2265 60s", - "lang_shortSessions": "Sessions < 60s", - "lang_overallOfftime": "Overall time offline", - "lang_totalOffTime": "Total Time Offline", + "lang_apply": "Apply", "lang_clientLogout": "Last VM Logout", "lang_clientStart": "Last Client Boot", - "lang_room": "Room", - "lang_offTime": "Time since offline", - "lang_total": "Total", - "lang_perlocation": "Per Location", - "lang_perclient": "Per Client", - "lang_peruser": "Per User", - "lang_pervm": "Per VM", "lang_hostname": "Client Name", - "lang_location": "Location", - "lang_medianSessionLength": "Median Session Length", - "lang_totalTime": "Total Time", "lang_last1": "Today", - "lang_last2": "Last 2 days", - "lang_last7": "Last 7 days", "lang_last14": "Last 14 days", + "lang_last2": "Last 2 days", "lang_last30": "Last 30 days", + "lang_last7": "Last 7 days", "lang_last90": "Last 90 days", - "lang_apply": "Apply", - "lang_save": "Save", - "lang_notassigned": "NOT ASSIGNED", - "lang_reportingDescription": "Help us improve bwLehrpool by automatically sending an anonymized statistics report once per week." + "lang_location": "Location", + "lang_longSessions": "Sessions \u2265 60s", + "lang_medianSessionLength": "Median Session Length", + "lang_overallOfftime": "Overall time offline", + "lang_perclient": "Per Client", + "lang_perlocation": "Per Location", + "lang_peruser": "Per User", + "lang_pervm": "Per VM", + "lang_reportingDescription": "Help us improve bwLehrpool by automatically sending an anonymized statistics report once per week.", + "lang_sessions": "Sessions", + "lang_shortSessions": "Sessions < 60s", + "lang_total": "Total", + "lang_totalOffTime": "Total Time Offline", + "lang_totalTime": "Total Time", + "lang_user": "User", + "lang_vm": "VM" } \ No newline at end of file -- cgit v1.2.3-55-g7522 From 6a11659efea17d2f486d7cb7b62e09e00c7c1661 Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Mon, 6 Feb 2017 17:17:13 +0100 Subject: [statistics_reporting] fixed incorrect median --- modules-available/statistics_reporting/inc/queries.inc.php | 3 +++ 1 file changed, 3 insertions(+) (limited to 'modules-available/statistics_reporting/inc/queries.inc.php') diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index 2b2ebb13..a36584ee 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -7,6 +7,7 @@ class Queries // Client Data: Name, Time Online, Median Time Online, Time Offline, last start, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized) public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translate('notAssigned', true); + Database::exec("SET SESSION group_concat_max_len = 1000000000"); $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash 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', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable @@ -28,6 +29,7 @@ class Queries // Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translate('notAssigned', true); + Database::exec("SET SESSION group_concat_max_len = 1000000000"); $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(CONCAT(locId, :salt)) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', 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', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable @@ -82,6 +84,7 @@ class Queries //Total Data: Time Online, Median Time Online, Number of Sessions > 60Sec, Number of Sessions < 60Sec public static function getOverallStatistics ($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { + Database::exec("SET SESSION group_concat_max_len = 1000000000"); $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', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable"); return $res; -- cgit v1.2.3-55-g7522 From 5dd18528ebd23cbc8b6d86c988d398a0058d1bab Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Tue, 7 Feb 2017 14:55:23 +0100 Subject: [statistics_reporting] Use indexed fields for joins, return location id from GetData::* --- modules-available/statistics_reporting/inc/getdata.inc.php | 6 ++++++ modules-available/statistics_reporting/inc/queries.inc.php | 12 ++++++------ 2 files changed, 12 insertions(+), 6 deletions(-) (limited to 'modules-available/statistics_reporting/inc/queries.inc.php') diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php index b19af966..9604d652 100644 --- a/modules-available/statistics_reporting/inc/getdata.inc.php +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -49,6 +49,9 @@ class GetData 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'] ); + if (!$anonymize) { + $entry['locationId'] = $row['locId']; + } if ($printable) { $entry['time_s'] = self::formatSeconds($row['timeSum']); $entry['medianTime_s'] = self::formatSeconds($median); @@ -78,6 +81,9 @@ class GetData 'shortSessions' => $row['shortSessions'], 'location' => ($anonymize ? $row['locHash'] : $row['locName']), ); + if (!$anonymize) { + $entry['locationId'] = $row['locId']; + } if ($printable) { $entry['time_s'] = self::formatSeconds($row['timeSum']); $entry['medianTime_s'] = self::formatSeconds($median); diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index a36584ee..7a56414b 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -8,7 +8,7 @@ class Queries public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translate('notAssigned', true); Database::exec("SET SESSION group_concat_max_len = 1000000000"); - $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash FROM ( + $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locId, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash 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', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid @@ -30,21 +30,21 @@ class Queries public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translate('notAssigned', true); Database::exec("SET SESSION group_concat_max_len = 1000000000"); - $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(CONCAT(locId, :salt)) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( + $res = Database::simpleQuery("SELECT t1.locId, t1.locName AS locName, MD5(CONCAT(t1.locId, :salt)) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', 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', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid - GROUP BY location.locationname + GROUP BY location.locationid ) t1 INNER JOIN ( - SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' + SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid - GROUP BY location.locationname + GROUP BY location.locationid ) t2 - ON t1.locName = t2.locName", array("salt" => GetData::$salt)); + ON t1.locId = t2.locId", array("salt" => GetData::$salt)); return $res; } -- cgit v1.2.3-55-g7522 From 3a09daee1ebaf014536e14e82ca0fd4c2c92a41f Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Wed, 8 Feb 2017 13:38:04 +0100 Subject: [statistics_reporting] Consistent field naming across classes, templates etc. --- .../statistics_reporting/inc/getdata.inc.php | 46 +++++++++++----------- .../statistics_reporting/inc/queries.inc.php | 8 ++-- .../templates/table-client.html | 8 ++-- .../templates/table-location.html | 8 ++-- .../templates/table-total.html | 8 ++-- 5 files changed, 39 insertions(+), 39 deletions(-) (limited to 'modules-available/statistics_reporting/inc/queries.inc.php') diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php index 9604d652..83f118e6 100644 --- a/modules-available/statistics_reporting/inc/getdata.inc.php +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -17,17 +17,17 @@ class GetData // total time online, average time online, total number of logins $res = Queries::getOverallStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $row = $res->fetch(PDO::FETCH_ASSOC); - $data = array('time' => $row['sum'], 'medianTime' => self::calcMedian($row['median']), 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); + $data = array('totalTime' => $row['sum'], 'medianSessionLength' => self::calcMedian($row['median']), 'longSessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); //total time offline $res = Queries::getTotalOfflineStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $row = $res->fetch(PDO::FETCH_ASSOC); - $data = array_merge($data, array('totalOfftime' => $row['timeOff'])); + $data['totalOffTime'] = $row['timeOff']; if ($printable) { - $data["time_s"] = self::formatSeconds($data["time"]); - $data["medianTime_s"] = self::formatSeconds($data["medianTime"]); - $data["totalOfftime_s"] = self::formatSeconds($data["totalOfftime"]); + $data["totalTime_s"] = self::formatSeconds($data["totalTime"]); + $data["medianSessionLength_s"] = self::formatSeconds($data["medianSessionLength"]); + $data["totalOffTime_s"] = self::formatSeconds($data["totalOffTime"]); } return $data; @@ -40,22 +40,22 @@ class GetData $res = Queries::getLocationStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $median = self::calcMedian(self::calcMedian($row['medianTime'])); + $median = self::calcMedian(self::calcMedian($row['medianSessionLength'])); $entry = array( 'location' => ($anonymize ? $row['locHash'] : $row['locName']), - 'time' => $row['timeSum'], - 'medianTime' => $median, - 'offTime' => $row['offlineSum'], - 'sessions' => $row['longSessions'], + 'totalTime' => $row['timeSum'], + 'medianSessionLength' => $median, + 'totalOffTime' => $row['offlineSum'], + 'longSessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'] ); if (!$anonymize) { $entry['locationId'] = $row['locId']; } if ($printable) { - $entry['time_s'] = self::formatSeconds($row['timeSum']); - $entry['medianTime_s'] = self::formatSeconds($median); - $entry['offTime_s'] = self::formatSeconds($row['offlineSum']); + $entry['totalTime_s'] = self::formatSeconds($row['timeSum']); + $entry['medianSessionLength_s'] = self::formatSeconds($median); + $entry['totalOffTime_s'] = self::formatSeconds($row['offlineSum']); } $data[] = $entry; } @@ -69,15 +69,15 @@ class GetData $res = Queries::getClientStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $median = self::calcMedian(self::calcMedian($row['medianTime'])); + $median = self::calcMedian(self::calcMedian($row['medianSessionLength'])); $entry = array( 'hostname' => ($anonymize ? $row['clientHash'] : $row['clientName']), - 'time' => $row['timeSum'], - 'medianTime' => $median, - 'offTime' => $row['offlineSum'], + 'totalTime' => $row['timeSum'], + 'medianSessionLength' => $median, + 'totalOffTime' => $row['offlineSum'], 'lastStart' => $row['lastStart'], 'lastLogout' => $row['lastLogout'], - 'sessions' => $row['longSessions'], + 'longSessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'location' => ($anonymize ? $row['locHash'] : $row['locName']), ); @@ -85,11 +85,11 @@ class GetData $entry['locationId'] = $row['locId']; } if ($printable) { - $entry['time_s'] = self::formatSeconds($row['timeSum']); - $entry['medianTime_s'] = self::formatSeconds($median); - $entry['offTime_s'] = self::formatSeconds($row['offlineSum']); - $entry['lastStart_s'] = date(DATE_RSS, $row['lastStart']); - $entry['lastLogout_s'] = date(DATE_RSS, $row['lastLogout']); + $entry['totalTime_s'] = self::formatSeconds($row['timeSum']); + $entry['medianSessionLength_s'] = self::formatSeconds($median); + $entry['totalOffTime_s'] = self::formatSeconds($row['offlineSum']); + $entry['lastStart_s'] = date(DATE_ISO8601, $row['lastStart']); + $entry['lastLogout_s'] = date(DATE_ISO8601, $row['lastLogout']); } $data[] = $entry; } diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index 7a56414b..1d1d3a44 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -8,8 +8,8 @@ class Queries public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translate('notAssigned', true); Database::exec("SET SESSION group_concat_max_len = 1000000000"); - $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locId, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash 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', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' + $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianSessionLength, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locId, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash FROM ( + SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianSessionLength', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid @@ -30,8 +30,8 @@ class Queries public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translate('notAssigned', true); Database::exec("SET SESSION group_concat_max_len = 1000000000"); - $res = Database::simpleQuery("SELECT t1.locId, t1.locName AS locName, MD5(CONCAT(t1.locId, :salt)) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( - SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', 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' + $res = Database::simpleQuery("SELECT t1.locId, t1.locName AS locName, MD5(CONCAT(t1.locId, :salt)) AS locHash, timeSum, medianSessionLength, offlineSum, longSessions, shortSessions FROM ( + SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianSessionLength', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid diff --git a/modules-available/statistics_reporting/templates/table-client.html b/modules-available/statistics_reporting/templates/table-client.html index 2fb2f574..be504cef 100644 --- a/modules-available/statistics_reporting/templates/table-client.html +++ b/modules-available/statistics_reporting/templates/table-client.html @@ -17,11 +17,11 @@ {{hostname}} {{location}} - {{time_s}} - {{medianTime_s}} - {{sessions}} + {{totalTime_s}} + {{medianSessionLength_s}} + {{longSessions}} {{shortSessions}} - {{offTime_s}} + {{totalOffTime_s}} {{lastLogout_s}} {{lastStart_s}} diff --git a/modules-available/statistics_reporting/templates/table-location.html b/modules-available/statistics_reporting/templates/table-location.html index 9479b7af..ccac623d 100644 --- a/modules-available/statistics_reporting/templates/table-location.html +++ b/modules-available/statistics_reporting/templates/table-location.html @@ -13,11 +13,11 @@ {{#data}} {{location}} - {{time_s}} - {{medianTime_s}} - {{sessions}} + {{totalTime_s}} + {{medianSessionLength_s}} + {{longSessions}} {{shortSessions}} - {{offTime_s}} + {{totalOffTime_s}} {{/data}} diff --git a/modules-available/statistics_reporting/templates/table-total.html b/modules-available/statistics_reporting/templates/table-total.html index e59dda25..4048a178 100644 --- a/modules-available/statistics_reporting/templates/table-total.html +++ b/modules-available/statistics_reporting/templates/table-total.html @@ -12,11 +12,11 @@ {{lang_total}} - {{data.time_s}} - {{data.medianTime_s}} - {{data.sessions}} + {{data.totalTime_s}} + {{data.medianSessionLength_s}} + {{data.longSessions}} {{data.shortSessions}} - {{data.totalOfftime_s}} + {{data.totalOffTime_s}} -- cgit v1.2.3-55-g7522 From 2a1d52bbb27fc47093479e9152a4d026856ca84f Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Wed, 8 Feb 2017 15:41:51 +0100 Subject: [statistics_reporting] Fix 'Not assigned' row not showing up in locations grouping anymore --- modules-available/statistics_reporting/inc/queries.inc.php | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) (limited to 'modules-available/statistics_reporting/inc/queries.inc.php') diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index 1d1d3a44..bb96a46e 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -16,7 +16,7 @@ class Queries 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' + SELECT machine.machineuuid AS 'uuid', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum', MAX(offlineTable.dateline) AS 'lastStart' FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid GROUP BY machine.machineuuid @@ -35,14 +35,14 @@ class Queries FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid - GROUP BY location.locationid + GROUP BY machine.locationid ) t1 - INNER JOIN ( - SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' + LEFT JOIN ( + SELECT location.locationid AS 'locId', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid - GROUP BY location.locationid + GROUP BY machine.locationid ) t2 ON t1.locId = t2.locId", array("salt" => GetData::$salt)); return $res; -- cgit v1.2.3-55-g7522 From 1fe5e109bc29af02de89c6513744c4c8755d5422 Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Thu, 23 Feb 2017 17:44:39 +0100 Subject: [statistics_reporting] included currently offline clients in offline time + bugfixes + some comments --- .../statistics_reporting/inc/getdata.inc.php | 4 +- .../statistics_reporting/inc/queries.inc.php | 171 +++++++++++++-------- 2 files changed, 109 insertions(+), 66 deletions(-) (limited to 'modules-available/statistics_reporting/inc/queries.inc.php') diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php index 83f118e6..f65ee868 100644 --- a/modules-available/statistics_reporting/inc/getdata.inc.php +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -88,8 +88,8 @@ class GetData $entry['totalTime_s'] = self::formatSeconds($row['timeSum']); $entry['medianSessionLength_s'] = self::formatSeconds($median); $entry['totalOffTime_s'] = self::formatSeconds($row['offlineSum']); - $entry['lastStart_s'] = date(DATE_ISO8601, $row['lastStart']); - $entry['lastLogout_s'] = date(DATE_ISO8601, $row['lastLogout']); + $entry['lastStart_s'] = $row['lastStart'] == 0 ? "" : date(DATE_ISO8601, $row['lastStart']); + $entry['lastLogout_s'] = $row['lastLogout'] == 0 ? "" : date(DATE_ISO8601, $row['lastLogout']); } $data[] = $entry; } diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index bb96a46e..8cca9650 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -8,17 +8,17 @@ class Queries public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translate('notAssigned', true); Database::exec("SET SESSION group_concat_max_len = 1000000000"); - $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianSessionLength, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locId, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash FROM ( - SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianSessionLength', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' + $res = Database::simpleQuery("SELECT name AS clientName, timeSum, medianSessionLength, offlineSum, IFNULL(lastStart, 0) as lastStart, IFNULL(lastLogout, 0) as lastLogout, longSessions, shortSessions, locId, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash FROM ( + SELECT machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianSessionLength', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions', MAX(sessionTable.endInBound) AS 'lastLogout' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid - LEFT JOIN location ON machine.locationid = location.locationid GROUP BY machine.machineuuid ) t1 - INNER JOIN ( - SELECT machine.machineuuid AS 'uuid', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum', MAX(offlineTable.dateline) AS 'lastStart' + RIGHT JOIN ( + SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum', MAX(offlineTable.endInBound) AS 'lastStart', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid + LEFT JOIN location ON machine.locationid = location.locationid GROUP BY machine.machineuuid ) t2 ON t1.uuid = t2.uuid", array("salt" => GetData::$salt)); @@ -30,15 +30,15 @@ class Queries public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translate('notAssigned', true); Database::exec("SET SESSION group_concat_max_len = 1000000000"); - $res = Database::simpleQuery("SELECT t1.locId, t1.locName AS locName, MD5(CONCAT(t1.locId, :salt)) AS locHash, timeSum, medianSessionLength, offlineSum, longSessions, shortSessions FROM ( - SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianSessionLength', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions' + $res = Database::simpleQuery("SELECT t1.locId, locName AS locName, MD5(CONCAT(t1.locId, :salt)) AS locHash, timeSum, medianSessionLength, offlineSum, longSessions, shortSessions FROM ( + SELECT location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianSessionLength', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid GROUP BY machine.locationid ) t1 - LEFT JOIN ( - SELECT location.locationid AS 'locId', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' + RIGHT JOIN ( + SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid @@ -52,14 +52,8 @@ class Queries public static function getUserStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(CONCAT(username, :salt))) AS userHash, COUNT(*) AS 'count' FROM statistic - WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND ( - (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0 - and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) AND (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) - or - (@daysDiff = 1 and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) OR (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) - or - @daysDiff >= 2 - ) + WHERE typeid='.vmchooser-session-name' AND dateline >= $from and dateline <= $to + AND FROM_UNIXTIME(dateline, '%H') >= $lowerTimeBound AND FROM_UNIXTIME(dateline, '%H') < $upperTimeBound GROUP BY username ORDER BY 2 DESC", array("salt" => GetData::$salt)); return $res; @@ -69,14 +63,8 @@ class Queries public static function getVMStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { $res = Database::simpleQuery("SELECT data AS name, MD5(CONCAT(data, :salt)) AS vmHash, COUNT(*) AS 'count' FROM statistic - WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND ( - (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0 - and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) AND (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) - or - (@daysDiff = 1 and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) OR (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) - or - @daysDiff >= 2 - ) + WHERE typeid='.vmchooser-session-name' AND dateline >= $from and dateline <= $to + AND FROM_UNIXTIME(dateline, '%H') >= $lowerTimeBound AND FROM_UNIXTIME(dateline, '%H') < $upperTimeBound GROUP BY data ORDER BY 2 DESC", array("salt" => GetData::$salt)); return $res; @@ -100,72 +88,127 @@ class Queries // query string which provides table with time-cutoff and time-bounds private static function getBoundedTableQueryString($typeid, $from, $to, $lowerTimeBound, $upperTimeBound) { + // get Clients that are currently oflfine (the offline time is not yet recorded in the statistic table) + $union = $typeid == '~offline-length' ? + "union + select CAST(IF(lastseen < $from, $from, lastseen) as UNSIGNED) as start, $to as end, + '~offline-length' as typeid, machineuuid, 'machine' + from machine where lastseen <= $to and UNIX_TIMESTAMP() - lastseen >= 600" : ""; + + $lowerFormat = "'%y-%m-%d $lowerTimeBound:00:00'"; $upperFormat = "'%y-%m-%d ".($upperTimeBound-1).":59:59'"; $queryString = " select - - @start := IF(dateline < $from, $from, dateline), - @end := IF(dateline+data > $to, $to, dateline+data), - @startLower := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat)), - @startUpper := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)), - @endLower := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)), - @endUpper := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat)), - (CAST(@end-@start AS SIGNED) + # The whole length of the session/offline time. + (end-start + + # Now the time that is not within the daily time bounds get substracted. + # This includes the time before the first daily bound, the time after the last daily bound + # and the time between the daily bounds (if a session/offline time spans multiple days) + + # Time before the first daily bound gets substracted. - IF( - @start > @startUpper, - UNIX_TIMESTAMP(FROM_UNIXTIME(@start, $lowerFormat) + INTERVAL 1 DAY) - @start, + start > startUpper, + UNIX_TIMESTAMP(FROM_UNIXTIME(start, $lowerFormat) + INTERVAL 1 DAY) - start, IF( - @start < @startLower, - @startLower - @start, + start < startLower, + startLower - start, 0 ) ) + + # Time after the last daily bound gets substracted. - IF( - @end > @endUpper, - @end - (@endUpper + 1), + end > endUpper, + end - (endUpper + 1), IF( - @end < @endLower, - @end - (UNIX_TIMESTAMP(FROM_UNIXTIME(@end, $upperFormat) - INTERVAL 1 DAY) + 1), + end < endLower, + end - (UNIX_TIMESTAMP(FROM_UNIXTIME(end, $upperFormat) - INTERVAL 1 DAY) + 1), 0 ) ) - - ( TO_DAYS(FROM_UNIXTIME(@end, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start, '%y-%m-%d')) - - 2 - + IF(@start <= @startUpper, 1, 0) - + IF(@end >= @endLower, 1, 0) - ) * ((24 - ($upperTimeBound - $lowerTimeBound)) * 3600) + # Time between the daily bounds get substracted. + - ( daysDiff - 2 + + IF(start <= startUpper, 1, 0) + + IF(end >= endLower, 1, 0) + ) * ((24 - ($upperTimeBound - $lowerTimeBound)) * 3600) + + # If the session crossed a clock change (to/from daylight saving time), the last substraction may have substracted + # one hour too much or too little. This IF will correct this. - IF( - @leftBound := IF(@start <= @startUpper, @startUpper, UNIX_TIMESTAMP(FROM_UNIXTIME(@start, $upperFormat) + INTERVAL 1 DAY)) - < @rightBound := IF(@end >= @endLower, @endLower, UNIX_TIMESTAMP(FROM_UNIXTIME(@end, $lowerFormat) - INTERVAL 1 DAY)), + innerStart < innerEnd, IF( - @timeDiff := ( - (date_format(from_unixtime(@leftBound), '%H') - - date_format(convert_tz(from_unixtime(@leftBound), @@session.time_zone, '+00:00'), '%H') + 24) % 24 - - - (date_format(from_unixtime(@rightBound), '%H') - - date_format(convert_tz(from_unixtime(@rightBound), @@session.time_zone, '+00:00'), '%H') + 24) % 24) = 1 - AND ($lowerTimeBound >= 2 OR $upperTimeBound <= 2), + timeDiff = 1 AND ($lowerTimeBound >= 2 OR $upperTimeBound <= 2), 3600, - IF(@timeDiff = -1 AND ($lowerTimeBound >= 3 OR $upperTimeBound <= 3), -3600, 0) + IF(timeDiff = -1 AND ($lowerTimeBound >= 3 OR $upperTimeBound <= 3), -3600, 0) ), 0 ) + ) as 'length', - dateline, - data, + + IF(end < endUpper AND end > endLower AND end < $to, end, 0) as endInBound, + machineuuid - from statistic - where dateline+data >= $from and dateline <= $to and typeid = '$typeid' - and ( - (@daysDiff := (TO_DAYS(FROM_UNIXTIME(dateline+data, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(dateline, '%y-%m-%d'))) = 0 and (dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)) and dateline+data >= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)))) + + # These nested selects are necessary because some things need to be calculated before others. + # (e.g. start is needed to calculate startLower) + from ( + select + *, + + # timeDiff is the clock change between leftBound and Rightbound. ( 0 = no clock change) + ((CAST(date_format(from_unixtime(innerStart), '%H') as SIGNED) - + CAST(date_format(convert_tz(from_unixtime(innerStart), @@session.time_zone, '+00:00'), '%H') as SIGNED) + 24) % 24 + - + (CAST(date_format(from_unixtime(innerEnd), '%H') as SIGNED) - + CAST(date_format(convert_tz(from_unixtime(innerEnd), @@session.time_zone, '+00:00'), '%H') as SIGNED) + 24) % 24) as timeDiff + from ( + select + *, + + # innerStart and innerEnd are start and end but excluding the time before the first daily upper bound and after the last daily lower bound. + CAST(IF(start <= startUpper, startUpper, UNIX_TIMESTAMP(FROM_UNIXTIME(start, $upperFormat) + INTERVAL 1 DAY)) as UNSIGNED) as innerStart, + CAST(IF(end >= endLower, endLower, UNIX_TIMESTAMP(FROM_UNIXTIME(end, $lowerFormat) - INTERVAL 1 DAY)) as UNSIGNED) as innerEnd + from ( + select + *, + + # daysDiff = how many different days the start and end are apart (0 = start and end on the same day) + (TO_DAYS(FROM_UNIXTIME(end, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(start, '%y-%m-%d'))) as daysDiff, + + # startLower = lower daily time bound on the starting day + CAST(UNIX_TIMESTAMP(FROM_UNIXTIME(start, $lowerFormat)) as UNSIGNED) as startLower, + # startUpper = upper daily time bound on the starting day + CAST(UNIX_TIMESTAMP(FROM_UNIXTIME(start, $upperFormat)) as UNSIGNED) as startUpper, + # endLower = lower daily time bound on the ending day + CAST(UNIX_TIMESTAMP(FROM_UNIXTIME(end, $lowerFormat)) as UNSIGNED) as endLower, + # endUpper = upper daily time bound on the ending day + CAST(UNIX_TIMESTAMP(FROM_UNIXTIME(end, $upperFormat)) as UNSIGNED) as endUpper + from ( + # Statistic logs (combined with currently offline machines if offline times are requested) . + select CAST(IF(dateline < $from, $from, dateline) as UNSIGNED) as start, + CAST(IF(dateline+data > $to, $to, dateline+data) as UNSIGNED) as end, + typeid, machineuuid, 'statistic' + from statistic where dateline+data >= $from and dateline <= $to and typeid = '$typeid' + $union + ) t + ) t + ) t + ) t + + + # Filter out the session that are at least overlapping with the time bounds. + where ( + (daysDiff = 0 and (start <= UNIX_TIMESTAMP(FROM_UNIXTIME(start, $upperFormat)) and end >= UNIX_TIMESTAMP(FROM_UNIXTIME(end, $lowerFormat)))) or - (@daysDiff = 1 and (dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)) or dateline+data >= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)))) + (daysDiff = 1 and (start <= UNIX_TIMESTAMP(FROM_UNIXTIME(start, $upperFormat)) or end >= UNIX_TIMESTAMP(FROM_UNIXTIME(end, $lowerFormat)))) or - @daysDiff >= 2 + daysDiff >= 2 ) "; return "(".$queryString.")"; -- cgit v1.2.3-55-g7522 From 0d6aaf82f8c0e1f695fc170acf030a484dca1aaa Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Thu, 23 Feb 2017 18:04:53 +0100 Subject: [statistics_reporting] fixed column selector order --- modules-available/statistics_reporting/inc/queries.inc.php | 12 ++++++------ modules-available/statistics_reporting/page.inc.php | 4 ++-- 2 files changed, 8 insertions(+), 8 deletions(-) (limited to 'modules-available/statistics_reporting/inc/queries.inc.php') diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index 8cca9650..3e944c92 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -104,11 +104,11 @@ class Queries # The whole length of the session/offline time. (end-start - # Now the time that is not within the daily time bounds get substracted. + # Now the time that is not within the daily time bounds is subtracted. # This includes the time before the first daily bound, the time after the last daily bound # and the time between the daily bounds (if a session/offline time spans multiple days) - # Time before the first daily bound gets substracted. + # Time before the first daily bound is subtracted. - IF( start > startUpper, UNIX_TIMESTAMP(FROM_UNIXTIME(start, $lowerFormat) + INTERVAL 1 DAY) - start, @@ -119,7 +119,7 @@ class Queries ) ) - # Time after the last daily bound gets substracted. + # Time after the last daily bound is subtracted. - IF( end > endUpper, end - (endUpper + 1), @@ -130,13 +130,13 @@ class Queries ) ) - # Time between the daily bounds get substracted. + # Time between the daily bounds is subtracted. - ( daysDiff - 2 + IF(start <= startUpper, 1, 0) + IF(end >= endLower, 1, 0) ) * ((24 - ($upperTimeBound - $lowerTimeBound)) * 3600) - # If the session crossed a clock change (to/from daylight saving time), the last substraction may have substracted + # If the session crossed a clock change (to/from daylight saving time), the last subtraction may have subtracted # one hour too much or too little. This IF will correct this. - IF( innerStart < innerEnd, @@ -161,7 +161,7 @@ class Queries select *, - # timeDiff is the clock change between leftBound and Rightbound. ( 0 = no clock change) + # timeDiff is the clock change between innerStart and innerEnd. ( 0 = no clock change) ((CAST(date_format(from_unixtime(innerStart), '%H') as SIGNED) - CAST(date_format(convert_tz(from_unixtime(innerStart), @@session.time_zone, '+00:00'), '%H') as SIGNED) + 24) % 24 - diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php index 51c137f6..6bd908a5 100644 --- a/modules-available/statistics_reporting/page.inc.php +++ b/modules-available/statistics_reporting/page.inc.php @@ -13,8 +13,8 @@ class Page_Statistics_Reporting extends Page /** * @var array Names of columns that are being used by the various tables */ - private $COLUMNS = array('lastLogout', 'lastStart', 'location', 'longSessions', 'medianSessionLength', - 'sessions', 'shortSessions', 'totalOffTime', 'totalTime'); + private $COLUMNS = array('location', 'totalTime', 'medianSessionLength', 'sessions', 'longSessions', 'shortSessions', + 'totalOffTime', 'lastLogout', 'lastStart'); /** * @var array Names of the tables we can display -- cgit v1.2.3-55-g7522