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 --- modules-available/statistics_reporting/config.json | 2 +- .../inc/statisticreporting.inc.php | 119 +++++++++++++++++---- .../lang/de/template-tags.json | 3 +- .../lang/en/template-tags.json | 7 +- modules-available/statistics_reporting/style.css | 24 ++++- .../templates/columnChooser.html | 52 ++++++++- 6 files changed, 175 insertions(+), 32 deletions(-) diff --git a/modules-available/statistics_reporting/config.json b/modules-available/statistics_reporting/config.json index 8115acf2..1a9c7fe7 100644 --- a/modules-available/statistics_reporting/config.json +++ b/modules-available/statistics_reporting/config.json @@ -1,4 +1,4 @@ { "category": "main.content", - "dependencies": [ "js_stupidtable"] + "dependencies": [ "js_stupidtable", "js_jqueryui" ] } 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.")"; + } + + +} + diff --git a/modules-available/statistics_reporting/lang/de/template-tags.json b/modules-available/statistics_reporting/lang/de/template-tags.json index 521b0607..f2500db0 100644 --- a/modules-available/statistics_reporting/lang/de/template-tags.json +++ b/modules-available/statistics_reporting/lang/de/template-tags.json @@ -24,5 +24,6 @@ "lang_last7": "Letzten 7 Tage", "lang_last14": "Letzten 14 Tage", "lang_last30": "Letzten 30 Tage", - "lang_last90": "Letzten 90 Tage" + "lang_last90": "Letzten 90 Tage", + "lang_apply": "Anwenden" } \ 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 c3900e31..8fa97719 100644 --- a/modules-available/statistics_reporting/lang/en/template-tags.json +++ b/modules-available/statistics_reporting/lang/en/template-tags.json @@ -5,7 +5,7 @@ "lang_vm": "VM", "lang_totalLogins": "Total Logins", "lang_overallOfftime": "Overall time offline", - "lang_totalOffTime": "Total time offline", + "lang_totalOffTime": "Total Time Offline", "lang_clientLogout": "Last VM Logout", "lang_clientStart": "Last Client Boot", "lang_room": "Room", @@ -18,11 +18,12 @@ "lang_hostname": "Client Name", "lang_location": "Location", "lang_avgSessionLength": "Average Session Length", - "lang_totalTime": "Gesamte Zeit", + "lang_totalTime": "Total Time", "lang_last1": "Last 24 hours", "lang_last2": "Last 48 hours", "lang_last7": "Last 7 days", "lang_last14": "Last 14 days", "lang_last30": "Last 30 days", - "lang_last90": "Last 90 days" + "lang_last90": "Last 90 days", + "lang_apply": "Apply" } \ No newline at end of file diff --git a/modules-available/statistics_reporting/style.css b/modules-available/statistics_reporting/style.css index b0875a7d..d7fa7a2f 100644 --- a/modules-available/statistics_reporting/style.css +++ b/modules-available/statistics_reporting/style.css @@ -2,7 +2,7 @@ margin-bottom: 10px; } -.top-row select, .top-row .btn-group { +.top-row > * { margin-right: 10px; margin-bottom: 10px; } @@ -13,4 +13,26 @@ .buttonbar { margin-bottom: 20px; +} + +#slider { + display: inline-block; + width: 160px; + margin-left: 20px; + margin-bottom: 3px; + margin-right: 20px; +} + +#lower-handle, #upper-handle { + width: 3em; + height: 1.6em; + top: 50%; + margin-top: -.8em; + margin-left: -1.5em; + text-align: center; + line-height: 1.6em; +} + +#applybound { + display: none; } \ No newline at end of file diff --git a/modules-available/statistics_reporting/templates/columnChooser.html b/modules-available/statistics_reporting/templates/columnChooser.html index 9e6f51d2..319f9702 100644 --- a/modules-available/statistics_reporting/templates/columnChooser.html +++ b/modules-available/statistics_reporting/templates/columnChooser.html @@ -8,7 +8,7 @@ - @@ -16,6 +16,12 @@ + +
+
+
+
+
@@ -30,7 +36,30 @@ \ No newline at end of file -- cgit v1.2.3-55-g7522