diff options
author | Udo Walter | 2016-12-20 14:49:17 +0100 |
---|---|---|
committer | Udo Walter | 2016-12-20 14:49:17 +0100 |
commit | f616a8cc375c2228def671800be40fac2072b27b (patch) | |
tree | 2f84cbe3682f896fc1c88e1d72ffbe77c2048303 /modules-available/statistics_reporting | |
parent | [statistics_reporting] edited time bound possibility (diff) | |
download | slx-admin-f616a8cc375c2228def671800be40fac2072b27b.tar.gz slx-admin-f616a8cc375c2228def671800be40fac2072b27b.tar.xz slx-admin-f616a8cc375c2228def671800be40fac2072b27b.zip |
[statistics_reporting] added time bounds slider
Diffstat (limited to 'modules-available/statistics_reporting')
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 <lower but goes into bound 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 @@ <option value="peruser">{{lang_peruser}}</option> <option value="pervm">{{lang_pervm}}</option> </select> - <select id="select-cutoff" onchange="chooseCutoff(this.value)" class="form-control"> + <select id="select-cutoff" onchange="reloadPage()" class="form-control"> <option value="1">{{lang_last1}}</option> <option value="2">{{lang_last2}}</option> <option value="7">{{lang_last7}}</option> @@ -16,6 +16,12 @@ <option value="30">{{lang_last30}}</option> <option value="90">{{lang_last90}}</option> </select> + + <div id="slider"> + <div id="lower-handle" class="ui-slider-handle"></div> + <div id="upper-handle" class="ui-slider-handle"></div> + </div> + <button id="applybound" type="button" class="btn btn-sm btn-primary" onclick="reloadPage()">{{lang_apply}}</button> </div> <div class="col-md-12 buttonbar"> <button id="button-totaltime" type="button" class="column-toggle btn btn-primary" onclick="toggleButton('totaltime')">{{lang_totalTime}}</button> @@ -30,7 +36,30 @@ <script type="application/javascript"> document.addEventListener("DOMContentLoaded", function () { + var lowerHandle = $("#lower-handle"); + var upperHandle = $("#upper-handle"); + var lower = getQueryVariable("lower"); + var upper = getQueryVariable("upper"); + $( "#slider" ).slider({ + range: true, + min: 0, + max: 24, + values: [ lower ? lower : 8, upper ? upper : 22 ], + create: function() { + lowerHandle.text( $(this).slider("values")[0]+":00" ); + upperHandle.text( $(this).slider("values")[1]+":00" ); + }, + slide: function(event, ui) { + lowerHandle.text(ui.values[0]+":00"); + upperHandle.text(ui.values[1]+":00"); + }, + change: function(event, ui) { + $("#applybound").show() + } + }); + loadForm(); + var table = $("table").stupidtable(); table.on("aftertablesort", function (event, data) { var th = $(this).find("th"); @@ -52,9 +81,12 @@ } } - function chooseCutoff(v) { + function reloadPage() { saveForm(); - window.location.replace("?do=statistics_reporting&cutoff="+v); + var cutoff = $("#select-cutoff").val(); + var lower = $("#lower-handle").text().split(":")[0]; + var upper = $("#upper-handle").text().split(":")[0]; + window.location.replace("?do=statistics_reporting&cutoff="+cutoff+"&lower="+lower+"&upper="+upper); } function toggleButton(v) { @@ -85,10 +117,22 @@ chooseTable("total"); } - $('#select-cutoff').val("{{cutoff}}"); + var cutoff = getQueryVariable("cutoff"); + $('#select-cutoff').val(cutoff ? cutoff : 7); sessionStorage.getItem("buttons").split(" ").forEach(function(button) { toggleButton(button.substr(7)); }); } + + function getQueryVariable(variable) + { + var query = window.location.search.substring(1); + var vars = query.split("&"); + for (var i=0;i<vars.length;i++) { + var pair = vars[i].split("="); + if(pair[0] == variable){return pair[1];} + } + return(false); + } </script>
\ No newline at end of file |