[statistics_reporting] added time bounds slider

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