[statistics_reporting] changed average to median

6 files changed, 34 insertions, 18 deletions

diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index 9212a09c..d98ec168 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -6,8 +6,8 @@ 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, shortSessions FROM (
-													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'
+		$res = Database::simpleQuery("SELECT t1.name, timeSum, medianTime, offlineSum, loginCount, lastLogout, lastStart, shortSessions FROM (
+													SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', 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 AND sessionTable.data >= 60
@@ -37,8 +37,8 @@ class StatisticReporting
 	public static function getLocationStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
 		$queryTime = time() - $cutOffTimeInSeconds;
 
-		$res = Database::simpleQuery("SELECT t1.locName, timeSum, avgTime, offlineSum, loginCount, shortSessions FROM (
-													SELECT IFNULL(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'
+		$res = Database::simpleQuery("SELECT t1.locName, timeSum, medianTime, offlineSum, loginCount, shortSessions FROM (
+													SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', COUNT(sessionTable.length) AS 'loginCount'
 													FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable
 												   	INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid 
 														LEFT JOIN location ON machine.locationid = location.locationid 
@@ -87,8 +87,8 @@ class StatisticReporting
 
 	public static function getOverallStatistics ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
 		$queryTime = time() - $cutOffTimeInSeconds;
-		$res = Database::simpleQuery("SELECT sum, avg, countLong, countShort FROM
-												(	SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, AVG(CAST(sessionTable.length AS UNSIGNED)) AS avg, COUNT(*) AS countLong
+		$res = Database::simpleQuery("SELECT sum, median, countLong, countShort FROM
+												(	SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, COUNT(*) AS countLong
 											 		FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable
 											 		WHERE sessionTable.dateline>=$queryTime AND sessionTable.data >= 60
 											 	) 	t1 
@@ -113,6 +113,20 @@ class StatisticReporting
 		return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s';
 	}
 
+	public static function calcMedian($string) {
+		$arr = explode(",", $string);
+		sort($arr, SORT_NUMERIC);
+		$count = count($arr); //total numbers in array
+		$middleval = floor(($count-1)/2); // find the middle value, or the lowest middle value
+		if($count % 2) { // odd number, middle is the median
+			$median = $arr[(int) $middleval];
+		} else { // even number, calculate avg of 2 medians
+			$low = $arr[(int) $middleval];
+			$high = $arr[(int) $middleval+1];
+			$median = (($low+$high)/2);
+		}
+		return round($median);
+	}
 
 	private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound)
 	{
diff --git a/modules-available/statistics_reporting/lang/de/template-tags.json b/modules-available/statistics_reporting/lang/de/template-tags.json
index f2500db0..c5494205 100644
--- a/modules-available/statistics_reporting/lang/de/template-tags.json
+++ b/modules-available/statistics_reporting/lang/de/template-tags.json
@@ -17,7 +17,7 @@
     "lang_pervm": "Pro VM",
     "lang_hostname": "Client Name",
     "lang_location": "Raum",
-    "lang_avgSessionLength": "Durchschnittliche Sitzungsdauer",
+    "lang_medianSessionLength": "Mittlere Sitzungsdauer",
     "lang_totalTime": "Gesamte Zeit",
     "lang_last1": "Letzten 24 Stunden",
     "lang_last2": "Letzten 48 Stunden",
diff --git a/modules-available/statistics_reporting/lang/en/template-tags.json b/modules-available/statistics_reporting/lang/en/template-tags.json
index 8fa97719..de91621f 100644
--- a/modules-available/statistics_reporting/lang/en/template-tags.json
+++ b/modules-available/statistics_reporting/lang/en/template-tags.json
@@ -17,7 +17,7 @@
     "lang_pervm": "Per VM",
     "lang_hostname": "Client Name",
     "lang_location": "Location",
-    "lang_avgSessionLength": "Average Session Length",
+    "lang_medianSessionLength": "Median Session Length",
     "lang_totalTime": "Total Time",
     "lang_last1": "Last 24 hours",
     "lang_last2": "Last 48 hours",
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index 9417d2da..0dd4f243 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -35,7 +35,7 @@ class Page_Statistics_Reporting extends Page
 		// total time online, average time online, total  number of logins
 		$res = StatisticReporting::getOverallStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
 		$row = $res->fetch(PDO::FETCH_NUM);
-		$data = array('time' =>  StatisticReporting::formatSeconds($row[0]), 'avgTime' =>  StatisticReporting::formatSeconds($row[1]), 'totalLogins' => $row[2]);
+		$data = array('time' =>  StatisticReporting::formatSeconds($row[0]), 'medianTime' =>  StatisticReporting::formatSeconds(StatisticReporting::calcMedian($row[1])), 'totalLogins' => $row[2]);
 
 		//total time offline
 		$res = StatisticReporting::getTotalOfflineStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
@@ -46,16 +46,18 @@ class Page_Statistics_Reporting extends Page
 		$res = StatisticReporting::getLocationStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
 		$data[] = array('perLocation' => array());
 		while ($row = $res->fetch(PDO::FETCH_NUM)) {
+			$median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row[2]));
 			$data['perLocation'][] = array('location' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1],
-				'avgTime' => StatisticReporting::formatSeconds($row[2]), 'avgTimeInSeconds' => $row[2], 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4]);
+				'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4]);
 		}
 
 		// per client
 		$res = StatisticReporting::getClientStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
 		$data[] = array('perClient' => array());
 		while ($row = $res->fetch(PDO::FETCH_NUM)) {
+			$median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row[2]));
 			$data['perClient'][] = array('hostname' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1],
-				'avgTime' => StatisticReporting::formatSeconds($row[2]), 'avgTimeInSeconds' => $row[2], 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4],
+				'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4],
 				'lastLogout' => date(DATE_RSS,$row[5]), 'lastLogoutUnixtime' => $row[5], 'lastStart' => date(DATE_RSS,$row[6]), 'lastStartUnixtime' => $row[6]);
 		}
 
diff --git a/modules-available/statistics_reporting/templates/_page.html b/modules-available/statistics_reporting/templates/_page.html
index 720fb37a..7cfb424c 100644
--- a/modules-available/statistics_reporting/templates/_page.html
+++ b/modules-available/statistics_reporting/templates/_page.html
@@ -6,7 +6,7 @@
 					<tr>
 						<th class="text-left col-md-2"></th>
 						<th class="text-left column-totaltime">{{lang_totalTime}}</th>
-						<th class="text-left column-avgtime">{{lang_avgSessionLength}}</th>
+						<th class="text-left column-mediantime">{{lang_medianSessionLength}}</th>
 						<th class="text-left column-logincount">{{lang_totalLogins}}</th>
 						<th class="text-left column-timeoffline">{{lang_overallOfftime}}</th>
 					</tr>
@@ -15,7 +15,7 @@
 					<tr>
 						<th class="text-left">{{lang_total}}</th>
 						<td class="text-left column-totaltime">{{time}}</td>
-						<td class="text-left column-avgtime">{{avgTime}}</td>
+						<td class="text-left column-mediantime">{{medianTime}}</td>
 						<td class="text-left column-logincount">{{totalLogins}}</td>
 						<td class="text-left column-timeoffline">{{totalOfftime}}</td>
 					</tr>
@@ -30,7 +30,7 @@
 					<tr>
 						<th data-sort="string" class="text-left col-md-2">{{lang_location}}</th>
 						<th data-sort="int" data-sort-default="desc" class="text-left column-totaltime">{{lang_totalTime}}</th>
-						<th data-sort="int" data-sort-default="desc" class="text-left column-avgtime">{{lang_avgSessionLength}}</th>
+						<th data-sort="int" data-sort-default="desc" class="text-left column-mediantime">{{lang_medianSessionLength}}</th>
 						<th data-sort="int" data-sort-default="desc" class="text-left column-logincount">{{lang_totalLogins}}</th>
 						<th data-sort="int" data-sort-default="desc" class="text-left column-timeoffline">{{lang_totalOffTime}}</th>
 					</tr>
@@ -40,7 +40,7 @@
 					<tr>
 						<td class="text-left">{{location}}</td>
 						<td data-sort-value="{{timeInSeconds}}" class="text-left column-totaltime">{{time}}</td>
-						<td data-sort-value="{{avgTimeInSeconds}}" class="text-left column-avgtime">{{avgTime}}</td>
+						<td data-sort-value="{{medianTimeInSeconds}}" class="text-left column-mediantime">{{medianTime}}</td>
 						<td class="text-left column-logincount">{{loginCount}}</td>
 						<td data-sort-value="{{offlineTimeInSeconds}}" class="text-left column-timeoffline">{{offTime}}</td>
 					</tr>
@@ -56,7 +56,7 @@
 					<tr>
 						<th data-sort="string" class="text-left col-md-4">{{lang_hostname}}</th>
 						<th data-sort="int" data-sort-default="desc" class="text-left column-totaltime">{{lang_totalTime}}</th>
-						<th data-sort="int" data-sort-default="desc" class="text-left column-avgtime">{{lang_avgSessionLength}}</th>
+						<th data-sort="int" data-sort-default="desc" class="text-left column-mediantime">{{lang_medianSessionLength}}</th>
 						<th data-sort="int" data-sort-default="desc" class="text-left column-logincount">{{lang_totalLogins}}</th>
 						<th data-sort="int" data-sort-default="desc" class="text-left column-timeoffline">{{lang_totalOffTime}}</th>
 						<th data-sort="int" data-sort-default="desc" class="text-left column-lastlogout">{{lang_clientLogout}}</th>
@@ -68,7 +68,7 @@
 					<tr>
 						<td class="text-left">{{hostname}}</td>
 						<td data-sort-value="{{timeInSeconds}}" class="text-left column-totaltime">{{time}}</td>
-						<td data-sort-value="{{avgTimeInSeconds}}" class="text-left column-avgtime">{{avgTime}}</td>
+						<td data-sort-value="{{medianTimeInSeconds}}" class="text-left column-mediantime">{{medianTime}}</td>
 						<td class="text-left column-logincount">{{loginCount}}</td>
 						<td data-sort-value="{{offlineTimeInSeconds}}" class="text-left column-timeoffline">{{offTime}}</td>
 						<td data-sort-value="{{lastLogoutUnixtime}}" class="text-left column-lastlogout">{{lastLogout}}</td>
diff --git a/modules-available/statistics_reporting/templates/columnChooser.html b/modules-available/statistics_reporting/templates/columnChooser.html
index 319f9702..d79f542b 100644
--- a/modules-available/statistics_reporting/templates/columnChooser.html
+++ b/modules-available/statistics_reporting/templates/columnChooser.html
@@ -25,7 +25,7 @@
 		</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>
-			<button id="button-avgtime" type="button" class="column-toggle btn btn-primary" onclick="toggleButton('avgtime')">{{lang_avgSessionLength}}</button>
+			<button id="button-mediantime" type="button" class="column-toggle btn btn-primary" onclick="toggleButton('mediantime')">{{lang_medianSessionLength}}</button>
 			<button id="button-logincount" type="button" class="column-toggle btn btn-primary" onclick="toggleButton('logincount')">{{lang_totalLogins}}</button>
 			<button id="button-timeoffline" type="button" class="column-toggle btn btn-primary" onclick="toggleButton('timeoffline')">{{lang_totalOffTime}}</button>
 			<button id="button-lastlogout" type="button" class="column-toggle btn btn-primary" onclick="toggleButton('lastlogout')">{{lang_clientLogout}}</button>