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') 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