login count and offline time database queries

6 files changed, 262 insertions, 15 deletions

diff --git a/modules-available/statistics_reporting/lang/de/module.json b/modules-available/statistics_reporting/lang/de/module.json
index 166909c3..bdbfe4fd 100644
--- a/modules-available/statistics_reporting/lang/de/module.json
+++ b/modules-available/statistics_reporting/lang/de/module.json
@@ -1,4 +1,4 @@
 {
-    "module_name": "Mein erstes Modul",
-    "page_title": "Mein erster Seitentitel"
+    "module_name": "Statistik Auswertung",
+    "page_title": "Statistik Auswertung"
 }
\ 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 ce98ce38..d9b034b4 100644
--- a/modules-available/statistics_reporting/lang/de/template-tags.json
+++ b/modules-available/statistics_reporting/lang/de/template-tags.json
@@ -1,3 +1,13 @@
 {
-    "lang_hello": "Hallo"
+    "lang_user": "Nutzer",
+    "lang_countLogins": "Anzahl Logins",
+    "lang_client": "Client",
+    "lang_vm": "VM",
+    "lang_totalLogins": "Gesamt Logins",
+    "lang_overallOfftime": "Gesamtzeit Offline",
+    "lang_totalOffTime": "Gesamtzeit Offline",
+    "lang_clientLogout": "Letzter Logout",
+    "lang_clientStart": "Letzter Client Start",
+    "lang_room": "Raum",
+    "lang_offTime": "Zeit seit Logout"
 }
\ No newline at end of file
diff --git a/modules-available/statistics_reporting/lang/en/module.json b/modules-available/statistics_reporting/lang/en/module.json
index b2bcbb0c..6b3f98f6 100644
--- a/modules-available/statistics_reporting/lang/en/module.json
+++ b/modules-available/statistics_reporting/lang/en/module.json
@@ -1,4 +1,4 @@
 {
-    "module_name": "My first module",
-    "page_title": "My first page title"
+    "module_name": "Statistics Reporting",
+    "page_title": "Statistics Reporting"
 }
\ 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 c30739e5..2f33eaf5 100644
--- a/modules-available/statistics_reporting/lang/en/template-tags.json
+++ b/modules-available/statistics_reporting/lang/en/template-tags.json
@@ -1,3 +1,13 @@
 {
-    "lang_hello": "Hello"
+    "lang_user": "User",
+    "lang_countLogins": "Number of Logins",
+    "lang_client": "Client",
+    "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_room": "Room",
+    "lang_offTime": "Time since offline"
 }
\ No newline at end of file
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index ff73107a..3bd83034 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -1,6 +1,6 @@
 <?php
 
-class Page_LocationInfo extends Page
+class Page_Statistics_Reporting extends Page
 {
 
 	/**
@@ -21,10 +21,103 @@ class Page_LocationInfo extends Page
 	 */
 	protected function doRender()
 	{
-		Render::addTemplate('_page', array(
-			'foo' => 'bar',
-			'now' => date('d.m.Y H:i:s')
-		));
+		//counting the total  number of logins
+		$res = Database::simpleQuery("SELECT COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name'");
+		$row = $res->fetch(PDO::FETCH_ASSOC);
+		$datax = array('totalLogins' => $row['count']);
+
+		//counting logins per vm/event
+		$res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY data ORDER BY 2 DESC");
+		$datax[] = array('vmLogins' => array());
+		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+			$datax['vmLogins'][] = array('vmname' => $row['data'], 'numLogins' => $row['count']);
+		}
+
+		//counting the logins per user
+		$res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY username ORDER BY 2 DESC");
+		$datax[] = array('userLogins' => array());
+		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+			$datax['userLogins'][] = array('username' => $row['username'], 'numLogins' => $row['count']);
+		}
+
+		// 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?
+		//counting the logins per client
+		$res = Database::simpleQuery("SELECT machine.hostname AS 'hostname', COUNT(*) AS 'count' FROM statistic 
+											 	INNER JOIN machine ON statistic.machineuuid=machine.machineuuid
+												WHERE typeid='~session-length' GROUP BY machine.hostname ORDER BY 2 DESC");
+		$datax[] = array('machineLogins' => array());
+		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+			$datax['machineLogins'][] = array('client' => $row['hostname'], 'numLogins' => $row['count']);
+		}
+
+
+
+		//total time offline overall
+		$res = Database::simpleQUery("SELECT sum(cast(data AS UNSIGNED)) AS totalOfftime FROM statistic WHERE typeid='~offline-length'");
+		$row = $res->fetch(PDO::FETCH_ASSOC);
+		$datay= array('totalOfftime' => $row['totalOfftime']);
+
+		//total offline time per client
+		$res = Database::simpleQuery("SELECT machine.hostname AS 'hostname', statistic.data as time FROM statistic 
+											 	INNER JOIN machine ON statistic.machineuuid=machine.machineuuid
+												WHERE typeid='~offline-length' GROUP BY machine.hostname ORDER BY cast(time AS UNSIGNED) DESC");
+		$datay[] = array('totalOfflineTimeClient' => array());
+		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+			$datay['totalOfflineTimeClient'][] = array('client' => $row['hostname'], 'offTime' => $row['time']);
+		}
+
+		// last logout of client
+		$res = Database::simpleQuery("SELECT machine.hostname AS 'hostname', max(statistic.dateline) as datetime, statistic.data as loginTime
+												FROM statistic INNER JOIN machine ON statistic.machineuuid=machine.machineuuid
+												WHERE typeid='~session-length' GROUP BY machine.hostname ORDER BY datetime DESC");
+		$datay[] = array('lastLogout' => array());
+		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+			$datay['lastLogout'][] = array('client' => $row['hostname'], 'lastlogout' => date(DATE_RSS,$row['datetime']+$row['loginTime']), 'howLongOff' => (time() - ($row['datetime']+$row['loginTime'])));
+		}
+
+		// last start of client
+		$res = Database::simpleQuery("SELECT machine.hostname AS 'hostname', max(statistic.dateline) as datetime FROM statistic 
+											 	INNER JOIN machine ON statistic.machineuuid=machine.machineuuid
+												WHERE typeid='~offline-length' GROUP BY machine.hostname ORDER BY datetime DESC");
+		$datay[] = array('lastLogin' => array());
+		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+			$datay['lastLogin'][] = array('client' => $row['hostname'], 'lastlogin' => date(DATE_RSS,$row['datetime']));
+		}
+
+		//total time offline per room
+		$res = Database::simpleQuery("SELECT location.locationname AS 'room', statistic.data as time FROM statistic 
+											 	INNER JOIN machine ON statistic.machineuuid=machine.machineuuid
+											 	INNER JOIN location ON machine.locationid=location.locationid
+												WHERE typeid='~offline-length' GROUP BY room ORDER BY cast(time AS UNSIGNED) DESC");
+		$datay[] = array('offTimeRoom' => array());
+		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+			$datay['offTimeRoom'][] = array('room' => $row['room'], 'offTime' => $this->formatSeconds($row['time']));
+		}
+
+		$data = array_merge($datax, $datay);
+		Render::addTemplate('_page', $data);
 	}
 
+
+
+	function formatSeconds($seconds) {
+		$seconds = $seconds * 1;
+
+		$minutes = floor($seconds / 60);
+		$hours = floor($minutes / 60);
+		$days = floor($hours / 24);
+
+		$seconds = $seconds % 60;
+		$minutes = $minutes % 60;
+		$hours = $hours % 24;
+
+		$format = '%u:%u:%02u:%02u';
+		$time = sprintf($format, $days, $hours, $minutes, $seconds);
+		return rtrim($time, '0');
+	}
+
+
 }
diff --git a/modules-available/statistics_reporting/templates/_page.html b/modules-available/statistics_reporting/templates/_page.html
index dfc941ae..101a124e 100644
--- a/modules-available/statistics_reporting/templates/_page.html
+++ b/modules-available/statistics_reporting/templates/_page.html
@@ -1,4 +1,138 @@
-<div style="border:5px solid red">
-	<h1>{{lang_hello}}, {{foo}}</h1>
-	** {{now}} **
-</div>
\ No newline at end of file
+<div>
+	<table class="table table-condensed table-striped">
+		<tr>
+			<th>{{lang_totalLogins}}</th>
+		</tr>
+		<tr>
+			<td>{{totalLogins}}</td>
+		</tr>
+	</table>
+</div>
+
+<div>
+	<table class="table table-condensed table-striped">
+		<tr>
+			<th>{{lang_overallOfftime}}</th>
+		</tr>
+		<tr>
+			<td>{{totalOfftime}}</td>
+		</tr>
+	</table>
+</div>
+
+
+
+<div class="row">
+	<div class="col-md-3">
+		<table class="table table-condensed">
+			<tr>
+				<th>{{lang_client}}</th>
+				<th>{{lang_totalOffTime}}</th>
+			</tr>
+			{{#totalOfflineTimeClient}}
+			<tr>
+				<td>{{client}}</td>
+				<td>{{offTime}}</td>
+			</tr>
+			{{/totalOfflineTimeClient	}}
+		</table>
+	</div>
+
+	<div class="col-md-3">
+		<table class="table table-condensed">
+			<tr>
+				<th>{{lang_client}}</th>
+				<th>{{lang_clientLogout}}</th>
+				<th>{{lang_offTime}}</th>
+			</tr>
+			{{#lastLogout}}
+			<tr>
+				<td>{{client}}</td>
+				<td>{{lastlogout}}</td>
+				<td>{{howLongOff}}</td>
+			</tr>
+			{{/lastLogout}}
+		</table>
+	</div>
+
+	<div class="col-md-3">
+		<table class="table table-condensed">
+			<tr>
+				<th>{{lang_client}}</th>
+				<th>{{lang_clientStart}}</th>
+			</tr>
+			{{#lastLogin}}
+			<tr>
+				<td>{{client}}</td>
+				<td>{{lastlogin}}</td>
+			</tr>
+			{{/lastLogin}}
+		</table>
+	</div>
+
+	<div class="col-md-3">
+		<table class="table table-condensed">
+			<tr>
+				<th>{{lang_room}}</th>
+				<th>{{lang_totalOffTime}}</th>
+			</tr>
+			{{#offTimeRoom}}
+			<tr>
+				<td>{{room}}</td>
+				<td>{{offTime}}</td>
+			</tr>
+			{{/offTimeRoom}}
+		</table>
+	</div>
+</div>
+
+
+
+
+<div class="row">
+	<div class="col-md-4">
+		<table class="table table-condensed">
+			<tr>
+				<th>{{lang_vm}}</th>
+				<th>{{lang_countLogins}}</th>
+			</tr>
+			{{#vmLogins}}
+			<tr>
+				<td>{{vmname}}</td>
+				<td>{{numLogins}}</td>
+			</tr>
+			{{/vmLogins}}
+		</table>
+	</div>
+
+	<div class="col-md-4">
+		<table class="table table-condensed table-striped">
+			<tr>
+				<th>{{lang_client}}</th>
+				<th>{{lang_countLogins}}</th>
+			</tr>
+			{{#machineLogins}}
+			<tr>
+				<td>{{client}}</td>
+				<td>{{numLogins}}</td>
+			</tr>
+			{{/machineLogins}}
+		</table>
+	</div>
+
+	<div class="col-md-4">
+		<table class="table table-condensed table-striped">
+			<tr>
+				<th>{{lang_user}}</th>
+				<th>{{lang_countLogins}}</th>
+			</tr>
+			{{#userLogins}}
+			<tr>
+				<td>{{username}}</td>
+				<td>{{numLogins}}</td>
+			</tr>
+			{{/userLogins}}
+		</table>
+	</div>
+</div>
+