From f616a8cc375c2228def671800be40fac2072b27b Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Tue, 20 Dec 2016 14:49:17 +0100 Subject: [statistics_reporting] added time bounds slider --- .../inc/statisticreporting.inc.php | 119 +++++++++++++++++---- 1 file changed, 97 insertions(+), 22 deletions(-) (limited to 'modules-available/statistics_reporting/inc/statisticreporting.inc.php') diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php index 69a4d516..7652bbdc 100644 --- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php +++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php @@ -7,14 +7,16 @@ class StatisticReporting public static function getClientStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; $res = Database::simpleQuery("SELECT t1.name, timeSum, avgTime, offlineSum, loginCount, lastLogout, lastStart FROM ( - SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(statistic.data AS UNSIGNED)) AS 'timeSum', AVG(CAST(statistic.data AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount', MAX(statistic.dateline + (statistic.data *1)) AS 'lastLogout' - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - WHERE typeid = '~session-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', AVG(CAST(sessionTable.length AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount', MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout' + FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable + INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid + WHERE sessionTable.dateline>=$queryTime GROUP BY machine.machineuuid ) t1 INNER JOIN ( - SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(statistic.data AS UNSIGNED)) AS 'offlineSum', MAX(statistic.dateline) AS 'lastStart' - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - WHERE typeid = '~offline-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + 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)." offlineTable + INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid + WHERE offlineTable.dateline>=$queryTime GROUP BY machine.machineuuid ) t2 ON t1.uuid = t2.uuid"); return $res; @@ -22,17 +24,20 @@ class StatisticReporting public static function getLocationStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; + $res = Database::simpleQuery("SELECT t1.locName, timeSum, avgTime, offlineSum, loginCount FROM ( - SELECT location.locationname AS 'locName', SUM(CAST(statistic.data AS UNSIGNED)) AS 'timeSum', AVG(CAST(statistic.data AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount' - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - INNER JOIN location ON machine.locationid = location.locationid - WHERE statistic.typeid = '~session-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + SELECT location.locationname AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', AVG(CAST(sessionTable.length AS UNSIGNED)) AS 'avgTime', COUNT(sessionTable.length) AS 'loginCount' + FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable + INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid + INNER JOIN location ON machine.locationid = location.locationid + WHERE sessionTable.dateline >= $queryTime GROUP By location.locationname ) t1 INNER JOIN ( - SELECT location.locationname AS 'locName', SUM(CAST(statistic.data AS UNSIGNED)) AS 'offlineSum' - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - INNER JOIN location ON machine.locationid = location.locationid - WHERE statistic.typeid = '~offline-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + SELECT location.locationname AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' + FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound)." offlineTable + INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid + INNER JOIN location ON machine.locationid = location.locationid + WHERE offlineTable.dateline >= $queryTime GROUP By location.locationname ) t2 ON t1.locName = t2.locName"); return $res; @@ -57,18 +62,17 @@ class StatisticReporting // AND(betweenBounds OR upper but begins in bound ) public static function getOverallStatistics ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)), AVG(CAST(data AS UNSIGNED)), COUNT(*) FROM statistic WHERE typeid = '~session-length' AND dateline>=$queryTime - AND (((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) - ) - - "); + $res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)), AVG(CAST(sessionTable.length AS UNSIGNED)), COUNT(*) + FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable + WHERE sessionTable.dateline>=$queryTime"); return $res; } public static function getTotalOfflineStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)) FROM statistic WHERE typeid='~offline-length' AND dateline>=$queryTime - AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))"); + $res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED)) + FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound)." offlineTable + WHERE offlineTable.dateline>=$queryTime"); return $res; } @@ -77,4 +81,75 @@ class StatisticReporting return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s'; } -} \ No newline at end of file + + private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound) + { + $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 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