[statistics_reporting] moved functions to own file, improved language and variable naming

4 files changed, 83 insertions, 45 deletions

diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
new file mode 100644
index 00000000..5bfba04f
--- /dev/null
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -0,0 +1,64 @@
+<?php
+
+
+class StatisticReporting
+{
+	public static function getClientStatistics() {
+		$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' 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' GROUP BY machine.machineuuid
+												) t2 ON t1.uuid = t2.uuid");
+		return $res;
+	}
+
+	public static function getLocationStatistics() {
+		$res = Database::simpleQuery("SELECT t1.ln, timeSum, avgTime, offlineSum, loginCount FROM (
+													SELECT location.locationname AS 'ln', 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' GROUP By location.locationname
+												) t1 INNER JOIN (
+											 		SELECT location.locationname AS 'ln', 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' GROUP By location.locationname
+												) t2 ON t1.ln = t2.ln");
+		return $res;
+	}
+
+	public static function getUserStatistics() {
+		$res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY username ORDER BY 2 DESC");
+		return $res;
+	}
+
+	public static function getVMStatistics() {
+		$res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY data ORDER BY 2 DESC");
+		return $res;
+	}
+
+	public static function getOverallStatistics () {
+		$res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)), AVG(CAST(data AS UNSIGNED)), COUNT(*) FROM statistic WHERE typeid = '~session-length'");
+		return $res;
+	}
+
+	public static function getTotalOffline() {
+		$res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)) FROM statistic WHERE typeid='~offline-length'");
+		return $res;
+	}
+
+
+
+
+
+
+	public static function formatSeconds($seconds)
+	{
+		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
diff --git a/modules-available/statistics_reporting/lang/de/template-tags.json b/modules-available/statistics_reporting/lang/de/template-tags.json
index 2a9688b2..db3e9f11 100644
--- a/modules-available/statistics_reporting/lang/de/template-tags.json
+++ b/modules-available/statistics_reporting/lang/de/template-tags.json
@@ -4,10 +4,10 @@
     "lang_client": "Client",
     "lang_vm": "VM",
     "lang_totalLogins": "Gesamt Logins",
-    "lang_overallOfftime": "Gesamtzeit Offline",
+    "lang_overallOfftime": "Insgesamte Zeit Offline",
     "lang_totalOffTime": "Gesamtzeit Offline",
-    "lang_clientLogout": "Letzter Logout",
-    "lang_clientStart": "Letzter Client Start",
+    "lang_clientLogout": "Letzter VM Logout",
+    "lang_clientStart": "Letzter Client Boot",
     "lang_room": "Raum",
     "lang_offTime": "Zeit seit Logout",
     "lang_total": "Gesamt",
diff --git a/modules-available/statistics_reporting/lang/en/template-tags.json b/modules-available/statistics_reporting/lang/en/template-tags.json
index cfbf3ad8..7a7ff13a 100644
--- a/modules-available/statistics_reporting/lang/en/template-tags.json
+++ b/modules-available/statistics_reporting/lang/en/template-tags.json
@@ -5,9 +5,9 @@
     "lang_vm": "VM",
     "lang_totalLogins": "Total Logins",
     "lang_overallOfftime": "Overall time offline",
-    "lang_totalOffTime": "Time offline",
-    "lang_clientLogout": "Last Logout",
-    "lang_clientStart": "Last Client Start",
+    "lang_totalOffTime": "Total time offline",
+    "lang_clientLogout": "Last VM Logout",
+    "lang_clientStart": "Last Client Boot",
     "lang_room": "Room",
     "lang_offTime": "Time since offline",
     "lang_total": "Total",
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index 6d3c2ae3..aaf191df 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -24,79 +24,53 @@ class Page_Statistics_Reporting extends Page
 		// TODO: got empty machine with alot of logins. isn't shown in the inner-join hostname query below.
 		// $res = Database::simpleQuery("SELECT machineuuid as 'hostname', COUNT(*) AS 'count' FROM statistic WHERE typeid='~session-length' GROUP BY machineuuid ORDER BY 2 DESC");
 
-		// TODO: session-length liefert 3000 Einträge mehr als vmchooser-session?
+		// TODO: session-length liefert 3000 Einträge mehr als vmchooser-session? (logins ohne ausgewählter VM -> Session endet trotzdem mit session-length Meldung)
 
 		// TODO change AVG to median
 
 
 		// total time online, average time online, total  number of logins
-		$res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)), AVG(CAST(data AS UNSIGNED)), COUNT(*) FROM statistic WHERE typeid = '~session-length'");
+		$res = StatisticReporting::getOverallStatistics();
 		$row = $res->fetch(PDO::FETCH_NUM);
-		$data = array('time' =>  $this->formatSeconds($row[0]), 'avgTime' =>  $this->formatSeconds($row[1]), 'totalLogins' => $row[2]);
+		$data = array('time' =>  StatisticReporting::formatSeconds($row[0]), 'avgTime' =>  StatisticReporting::formatSeconds($row[1]), 'totalLogins' => $row[2]);
 		//total time offline
-		$res = Database::simpleQUery("SELECT SUM(CAST(data AS UNSIGNED)) FROM statistic WHERE typeid='~offline-length'");
+		$res = StatisticReporting::getTotalOffline();
 		$row = $res->fetch(PDO::FETCH_NUM);
-		$data = array_merge($data, array('totalOfftime' => $this->formatSeconds($row[0])));
+		$data = array_merge($data, array('totalOfftime' => StatisticReporting::formatSeconds($row[0])));
 
 		// per location
-		$res = Database::simpleQuery("SELECT t1.ln, timeSum, avgTime, offlineSum, loginCount FROM (
-													SELECT location.locationname AS 'ln', 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' GROUP By location.locationname
-												) t1 INNER JOIN (
-											 		SELECT location.locationname AS 'ln', 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' GROUP By location.locationname
-												) t2 ON t1.ln = t2.ln");
+		$res = StatisticReporting::getLocationStatistics();
 		$data[] = array('perLocation' => array());
 		while ($row = $res->fetch(PDO::FETCH_NUM)) {
-			$data['perLocation'][] = array('location' => $row[0], 'time' => $this->formatSeconds($row[1]), 'timeInSeconds' => $row[1],
-				'avgTime' => $this->formatSeconds($row[2]), 'avgTimeInSeconds' => $row[2], 'offTime' => $this->formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4]);
+			$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]);
 		}
 
 		// per client
-		$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) AS 'lastLogout'
-													FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid
-													WHERE typeid = '~session-length' 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' GROUP BY machine.machineuuid
-												) t2 ON t1.uuid = t2.uuid");
+		$res = StatisticReporting::getClientStatistics();
 		$data[] = array('perClient' => array());
 		while ($row = $res->fetch(PDO::FETCH_NUM)) {
-			$data['perClient'][] = array('hostname' => $row[0], 'time' => $this->formatSeconds($row[1]), 'timeInSeconds' => $row[1],
-				'avgTime' => $this->formatSeconds($row[2]), 'avgTimeInSeconds' => $row[2], 'offTime' => $this->formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4],
+			$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],
 				'lastLogout' => date(DATE_RSS,$row[5]), 'lastLogoutUnixtime' => $row[5], 'lastStart' => date(DATE_RSS,$row[6]), 'lastStartUnixtime' => $row[6]);
 		}
 
 		// per user
-		$res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY username ORDER BY 2 DESC");
+		$res = StatisticReporting::getUserStatistics();
 		$data[] = array('perUser' => array());
 		while ($row = $res->fetch(PDO::FETCH_NUM)) {
 			$data['perUser'][] = array('user' => $row[0], 'loginCount' => $row[1]);
 		}
 
 		// per vm
-		$res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY data ORDER BY 2 DESC");
+		$res = StatisticReporting::getVMStatistics();
 		$data[] = array('perVM' => array());
 		while ($row = $res->fetch(PDO::FETCH_NUM)) {
 			$data['perVM'][] = array('vm' => $row[0], 'loginCount' => $row[1]);
 		}
 
 
-
-
 		Render::addTemplate('columnChooser');
 		Render::addTemplate('_page', $data);
 	}
-
-
-	protected function formatSeconds($seconds)
-	{
-		return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s';
-	}
 }