From e10ad1d38de5fa13781c3af17a4de8fe85644a0e Mon Sep 17 00:00:00 2001
From: Christian Hofmaier
Date: Mon, 28 Nov 2016 15:41:15 +0100
Subject: login count and offline time database queries
---
.../statistics_reporting/lang/de/module.json | 4 +-
.../lang/de/template-tags.json | 12 +-
.../statistics_reporting/lang/en/module.json | 4 +-
.../lang/en/template-tags.json | 12 +-
.../statistics_reporting/page.inc.php | 103 ++++++++++++++-
.../statistics_reporting/templates/_page.html | 142 ++++++++++++++++++++-
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 @@
'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 @@
-
-
{{lang_hello}}, {{foo}}
- ** {{now}} **
-
\ No newline at end of file
+
+
+
+ {{lang_totalLogins}} |
+
+
+ {{totalLogins}} |
+
+
+
+
+
+
+
+ {{lang_overallOfftime}} |
+
+
+ {{totalOfftime}} |
+
+
+
+
+
+
+
+
+
+
+ {{lang_client}} |
+ {{lang_totalOffTime}} |
+
+ {{#totalOfflineTimeClient}}
+
+ {{client}} |
+ {{offTime}} |
+
+ {{/totalOfflineTimeClient }}
+
+
+
+
+
+
+ {{lang_client}} |
+ {{lang_clientLogout}} |
+ {{lang_offTime}} |
+
+ {{#lastLogout}}
+
+ {{client}} |
+ {{lastlogout}} |
+ {{howLongOff}} |
+
+ {{/lastLogout}}
+
+
+
+
+
+
+ {{lang_client}} |
+ {{lang_clientStart}} |
+
+ {{#lastLogin}}
+
+ {{client}} |
+ {{lastlogin}} |
+
+ {{/lastLogin}}
+
+
+
+
+
+
+ {{lang_room}} |
+ {{lang_totalOffTime}} |
+
+ {{#offTimeRoom}}
+
+ {{room}} |
+ {{offTime}} |
+
+ {{/offTimeRoom}}
+
+
+
+
+
+
+
+
+
+
+
+ {{lang_vm}} |
+ {{lang_countLogins}} |
+
+ {{#vmLogins}}
+
+ {{vmname}} |
+ {{numLogins}} |
+
+ {{/vmLogins}}
+
+
+
+
+
+
+ {{lang_client}} |
+ {{lang_countLogins}} |
+
+ {{#machineLogins}}
+
+ {{client}} |
+ {{numLogins}} |
+
+ {{/machineLogins}}
+
+
+
+
+
+
+ {{lang_user}} |
+ {{lang_countLogins}} |
+
+ {{#userLogins}}
+
+ {{username}} |
+ {{numLogins}} |
+
+ {{/userLogins}}
+
+
+
+
--
cgit v1.2.3-55-g7522