{{hostname}} |
{{time}} |
- {{avgTime}} |
+ {{medianTime}} |
{{loginCount}} |
{{offTime}} |
{{lastLogout}} |
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 @@
-
+
--
cgit v1.2.3-55-g7522
From 47a8eebcda2c841193420e6e7bb5ec847940f753 Mon Sep 17 00:00:00 2001
From: Christian Hofmaier
Date: Thu, 22 Dec 2016 14:03:29 +0100
Subject: [statistics_reporting] better variable names
---
.../statistics_reporting/inc/statisticreporting.inc.php | 6 +++---
modules-available/statistics_reporting/page.inc.php | 6 +++---
2 files changed, 6 insertions(+), 6 deletions(-)
diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index d98ec168..2c4acaf1 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -87,13 +87,13 @@ class StatisticReporting
public static function getOverallStatistics ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
$queryTime = time() - $cutOffTimeInSeconds;
- $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
+ $res = Database::simpleQuery("SELECT sum, median, longSessions, shortSessions FROM
+ ( SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, COUNT(*) AS longSessions
FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable
WHERE sessionTable.dateline>=$queryTime AND sessionTable.data >= 60
) t1
INNER JOIN
- ( SELECT COUNT(sessionTable.length) as countShort
+ ( SELECT COUNT(sessionTable.length) as shortSessions
FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable
WHERE sessionTable.dateline>=$queryTime AND sessionTable.data < 60
) t2");
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index 0dd4f243..de13cdb3 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]), 'medianTime' => StatisticReporting::formatSeconds(StatisticReporting::calcMedian($row[1])), 'totalLogins' => $row[2]);
+ $data = array('time' => StatisticReporting::formatSeconds($row[0]), 'medianTime' => StatisticReporting::formatSeconds(StatisticReporting::calcMedian($row[1])), 'totalLogins' => $row[2], 'shortSessions' => $row[3]);
//total time offline
$res = StatisticReporting::getTotalOfflineStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
@@ -48,7 +48,7 @@ class Page_Statistics_Reporting extends Page
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],
- 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, '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], 'shortSessions' => $row[5]);
}
// per client
@@ -58,7 +58,7 @@ class Page_Statistics_Reporting extends Page
$median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row[2]));
$data['perClient'][] = array('hostname' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1],
'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]);
+ 'lastLogout' => date(DATE_RSS,$row[5]), 'lastLogoutUnixtime' => $row[5], 'lastStart' => date(DATE_RSS,$row[6]), 'lastStartUnixtime' => $row[6], 'shortSessions' => $row[7]);
}
// per user
--
cgit v1.2.3-55-g7522
From c6339b83c643c943bae1a4f9e0fe346a1d724368 Mon Sep 17 00:00:00 2001
From: Christian Hofmaier
Date: Thu, 22 Dec 2016 15:00:25 +0100
Subject: [statistics_reporting] improved short session queries
---
.../inc/statisticreporting.inc.php | 45 +++++-----------------
1 file changed, 10 insertions(+), 35 deletions(-)
diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index 2c4acaf1..560ac2c2 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -6,11 +6,11 @@ class StatisticReporting
public static function getClientStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
$queryTime = time() - $cutOffTimeInSeconds;
- $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'
+ $res = Database::simpleQuery("SELECT t1.name, timeSum, medianTime, offlineSum, longSessions, 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(sessionTable.length >= 60) AS 'longSessions', COUNT(sessionTable.length < 60) AS 'shortSessions',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
+ WHERE sessionTable.dateline>=$queryTime
GROUP BY machine.machineuuid
) t1
INNER JOIN (
@@ -20,16 +20,7 @@ class StatisticReporting
WHERE offlineTable.dateline>=$queryTime
GROUP BY machine.machineuuid
) t2
- ON t1.uuid = t2.uuid
- INNER JOIN (
- SELECT machine.machineuuid AS 'uuid', COUNT(*) AS shortSessions
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable
- INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
- WHERE sessionTable.dateline>=$queryTime AND sessionTable.data < 60
- GROUP BY machine.machineuuid
- ) t3
- ON t2.uuid = t3.uuid
- ");
+ ON t1.uuid = t2.uuid");
return $res;
}
@@ -37,12 +28,12 @@ class StatisticReporting
public static function getLocationStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
$queryTime = time() - $cutOffTimeInSeconds;
- $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'
+ $res = Database::simpleQuery("SELECT t1.locName, timeSum, medianTime, offlineSum, longSessions, 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 >= 60) AS 'longSessions', COUNT(sessionTable.length < 60) AS 'shortSessions'
FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
- WHERE sessionTable.dateline >= $queryTime AND sessionTable.data >= 60
+ WHERE sessionTable.dateline >= $queryTime
GROUP BY location.locationname
) t1
INNER JOIN (
@@ -53,16 +44,7 @@ class StatisticReporting
WHERE offlineTable.dateline >= $queryTime
GROUP BY location.locationname
) t2
- ON t1.locName = t2.locName
- INNER JOIN (
- SELECT IFNULL(location.locationname, '') AS 'locName', COUNT(sessionTable.length) AS shortSessions
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable
- INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
- LEFT JOIN location ON machine.locationid = location.locationid
- WHERE sessionTable.dateline >= $queryTime AND sessionTable.data < 60
- GROUP BY location.locationname
- ) t3
- ON t2.locName = t3.locName");
+ ON t1.locName = t2.locName");
return $res;
}
@@ -87,16 +69,9 @@ class StatisticReporting
public static function getOverallStatistics ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
$queryTime = time() - $cutOffTimeInSeconds;
- $res = Database::simpleQuery("SELECT sum, median, longSessions, shortSessions FROM
- ( SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, COUNT(*) AS longSessions
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable
- WHERE sessionTable.dateline>=$queryTime AND sessionTable.data >= 60
- ) t1
- INNER JOIN
- ( SELECT COUNT(sessionTable.length) as shortSessions
+ $res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, COUNT(sessionTable.length >= 60) AS longSessions, COUNT(sessionTable.length < 60) AS shortSessions
FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable
- WHERE sessionTable.dateline>=$queryTime AND sessionTable.data < 60
- ) t2");
+ WHERE sessionTable.dateline>=$queryTime");
return $res;
}
--
cgit v1.2.3-55-g7522
From b9e1d1a3780d0cbb26e84a17106805895795edf9 Mon Sep 17 00:00:00 2001
From: Christian Hofmaier
Date: Thu, 22 Dec 2016 15:07:02 +0100
Subject: [statistics_reporting] improved short session queries (bugfix)
---
.../statistics_reporting/inc/statisticreporting.inc.php | 6 +++---
1 file changed, 3 insertions(+), 3 deletions(-)
diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index 560ac2c2..afa3e7ac 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -7,7 +7,7 @@ class StatisticReporting
public static function getClientStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
$queryTime = time() - $cutOffTimeInSeconds;
$res = Database::simpleQuery("SELECT t1.name, timeSum, medianTime, offlineSum, longSessions, 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(sessionTable.length >= 60) AS 'longSessions', COUNT(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout'
+ SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',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
@@ -29,7 +29,7 @@ class StatisticReporting
$queryTime = time() - $cutOffTimeInSeconds;
$res = Database::simpleQuery("SELECT t1.locName, timeSum, medianTime, offlineSum, longSessions, 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 >= 60) AS 'longSessions', COUNT(sessionTable.length < 60) AS 'shortSessions'
+ SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
@@ -69,7 +69,7 @@ class StatisticReporting
public static function getOverallStatistics ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
$queryTime = time() - $cutOffTimeInSeconds;
- $res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, COUNT(sessionTable.length >= 60) AS longSessions, COUNT(sessionTable.length < 60) AS shortSessions
+ $res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, SUM(sessionTable.length >= 60) AS longSessions, SUM(sessionTable.length < 60) AS shortSessions
FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable
WHERE sessionTable.dateline>=$queryTime");
return $res;
--
cgit v1.2.3-55-g7522
From 3a2dae96362b1187ae4b9d89904d23b6a7da87cb Mon Sep 17 00:00:00 2001
From: Udo Walter
Date: Thu, 22 Dec 2016 15:08:36 +0100
Subject: [statistics_reporting] added short sessions to ui
---
.../lang/de/template-tags.json | 3 ++-
.../lang/en/template-tags.json | 3 ++-
.../statistics_reporting/page.inc.php | 10 ++++-----
.../statistics_reporting/templates/_page.html | 26 +++++++++++++---------
.../templates/columnChooser.html | 3 ++-
5 files changed, 27 insertions(+), 18 deletions(-)
diff --git a/modules-available/statistics_reporting/lang/de/template-tags.json b/modules-available/statistics_reporting/lang/de/template-tags.json
index c5494205..68b68250 100644
--- a/modules-available/statistics_reporting/lang/de/template-tags.json
+++ b/modules-available/statistics_reporting/lang/de/template-tags.json
@@ -3,7 +3,8 @@
"lang_countLogins": "Anzahl Logins",
"lang_client": "Client",
"lang_vm": "VM",
- "lang_totalLogins": "Gesamt Logins",
+ "lang_sessions": "Sitzungen \u2265 60s",
+ "lang_shortSessions": "Sitzungen < 60s",
"lang_overallOfftime": "Insgesamte Zeit Offline",
"lang_totalOffTime": "Gesamtzeit Offline",
"lang_clientLogout": "Letzter VM Logout",
diff --git a/modules-available/statistics_reporting/lang/en/template-tags.json b/modules-available/statistics_reporting/lang/en/template-tags.json
index de91621f..b9e0c61d 100644
--- a/modules-available/statistics_reporting/lang/en/template-tags.json
+++ b/modules-available/statistics_reporting/lang/en/template-tags.json
@@ -3,7 +3,8 @@
"lang_countLogins": "Number of Logins",
"lang_client": "Client",
"lang_vm": "VM",
- "lang_totalLogins": "Total Logins",
+ "lang_sessions": "Sessions \u2265 60s",
+ "lang_shortSessions": "Sessions < 60s",
"lang_overallOfftime": "Overall time offline",
"lang_totalOffTime": "Total Time Offline",
"lang_clientLogout": "Last VM Logout",
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index de13cdb3..f0f56e87 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]), 'medianTime' => StatisticReporting::formatSeconds(StatisticReporting::calcMedian($row[1])), 'totalLogins' => $row[2], 'shortSessions' => $row[3]);
+ $data = array('time' => StatisticReporting::formatSeconds($row[0]), 'medianTime' => StatisticReporting::formatSeconds(StatisticReporting::calcMedian($row[1])), 'sessions' => $row[2], 'shortSessions' => $row[3]);
//total time offline
$res = StatisticReporting::getTotalOfflineStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
@@ -48,7 +48,7 @@ class Page_Statistics_Reporting extends Page
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],
- 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4], 'shortSessions' => $row[5]);
+ 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'sessions' => $row[4], 'shortSessions' => $row[5]);
}
// per client
@@ -57,7 +57,7 @@ class Page_Statistics_Reporting extends Page
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],
- 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, '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], 'sessions' => $row[4],
'lastLogout' => date(DATE_RSS,$row[5]), 'lastLogoutUnixtime' => $row[5], 'lastStart' => date(DATE_RSS,$row[6]), 'lastStartUnixtime' => $row[6], 'shortSessions' => $row[7]);
}
@@ -65,14 +65,14 @@ class Page_Statistics_Reporting extends Page
$res = StatisticReporting::getUserStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
$data[] = array('perUser' => array());
while ($row = $res->fetch(PDO::FETCH_NUM)) {
- $data['perUser'][] = array('user' => $row[0], 'loginCount' => $row[1]);
+ $data['perUser'][] = array('user' => $row[0], 'sessions' => $row[1]);
}
// per vm
$res = StatisticReporting::getVMStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
$data[] = array('perVM' => array());
while ($row = $res->fetch(PDO::FETCH_NUM)) {
- $data['perVM'][] = array('vm' => $row[0], 'loginCount' => $row[1]);
+ $data['perVM'][] = array('vm' => $row[0], 'sessions' => $row[1]);
}
Render::addTemplate('columnChooser');
diff --git a/modules-available/statistics_reporting/templates/_page.html b/modules-available/statistics_reporting/templates/_page.html
index 7cfb424c..d2aaecde 100644
--- a/modules-available/statistics_reporting/templates/_page.html
+++ b/modules-available/statistics_reporting/templates/_page.html
@@ -7,7 +7,8 @@
|
{{lang_totalTime}} |
{{lang_medianSessionLength}} |
- {{lang_totalLogins}} |
+ {{lang_sessions}} |
+ {{lang_shortSessions}} |
{{lang_overallOfftime}} |
@@ -16,7 +17,8 @@
-
+
+
--
cgit v1.2.3-55-g7522
From 184e4396341ade3608c5c11e3467627be3addd51 Mon Sep 17 00:00:00 2001
From: Udo Walter
Date: Thu, 22 Dec 2016 15:15:18 +0100
Subject: [statistics_reporting] performance improvements
---
.../inc/statisticreporting.inc.php | 22 ++++++++--------------
1 file changed, 8 insertions(+), 14 deletions(-)
diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index afa3e7ac..7b1dcfdc 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -8,16 +8,14 @@ class StatisticReporting
$queryTime = time() - $cutOffTimeInSeconds;
$res = Database::simpleQuery("SELECT t1.name, timeSum, medianTime, offlineSum, longSessions, 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', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout'
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable
+ FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $queryTime)." 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(offlineTable.length AS UNSIGNED)) AS 'offlineSum', MAX(offlineTable.dateline) AS 'lastStart'
- FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound)." offlineTable
+ FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $queryTime)." offlineTable
INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
- WHERE offlineTable.dateline>=$queryTime
GROUP BY machine.machineuuid
) t2
ON t1.uuid = t2.uuid");
@@ -30,18 +28,16 @@ class StatisticReporting
$res = Database::simpleQuery("SELECT t1.locName, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM (
SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable
+ FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $queryTime)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
- WHERE sessionTable.dateline >= $queryTime
GROUP BY location.locationname
) t1
INNER JOIN (
SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum'
- FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound)." offlineTable
+ FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $queryTime)." offlineTable
INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
- WHERE offlineTable.dateline >= $queryTime
GROUP BY location.locationname
) t2
ON t1.locName = t2.locName");
@@ -70,16 +66,14 @@ class StatisticReporting
public static function getOverallStatistics ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
$queryTime = time() - $cutOffTimeInSeconds;
$res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, SUM(sessionTable.length >= 60) AS longSessions, SUM(sessionTable.length < 60) AS shortSessions
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable
- WHERE sessionTable.dateline>=$queryTime");
+ FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $queryTime)." sessionTable");
return $res;
}
public static function getTotalOfflineStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
$queryTime = time() - $cutOffTimeInSeconds;
$res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED))
- FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound)." offlineTable
- WHERE offlineTable.dateline>=$queryTime");
+ FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $queryTime)." offlineTable");
return $res;
}
@@ -103,7 +97,7 @@ class StatisticReporting
return round($median);
}
- private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound)
+ private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound, $cutoffTime)
{
$lowerFormat = "'%y-%m-%d $lowerTimeBound:00:00'";
$upperFormat = "'%y-%m-%d ".($upperTimeBound-1).":59:59'";
@@ -163,7 +157,7 @@ class StatisticReporting
machineuuid
from statistic
- where typeid = '$typeid' and (
+ where dateline >= $cutoffTime and 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)))
)
--
cgit v1.2.3-55-g7522
From 2ab12fa0ce5fe4c916d5426e05656e61b4005b1e Mon Sep 17 00:00:00 2001
From: Udo Walter
Date: Thu, 22 Dec 2016 16:10:13 +0100
Subject: [statistics_reporting] changed cutoffs
---
.../inc/statisticreporting.inc.php | 41 +++++++++-------------
.../lang/de/template-tags.json | 4 +--
.../lang/en/template-tags.json | 4 +--
.../statistics_reporting/page.inc.php | 15 ++++----
4 files changed, 28 insertions(+), 36 deletions(-)
diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index 7b1dcfdc..626aa290 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -4,17 +4,16 @@
class StatisticReporting
{
- public static function getClientStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $queryTime = time() - $cutOffTimeInSeconds;
+ public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT t1.name, timeSum, medianTime, offlineSum, longSessions, 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', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout'
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $queryTime)." sessionTable
+ FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
GROUP BY machine.machineuuid
) t1
INNER JOIN (
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, $queryTime)." offlineTable
+ FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable
INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
GROUP BY machine.machineuuid
) t2
@@ -23,19 +22,17 @@ class StatisticReporting
}
// IFNULL(location.locationname, '') - emptry string can be replaced with anything (name of the null-ids in the table)
- public static function getLocationStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $queryTime = time() - $cutOffTimeInSeconds;
-
+ public static function getLocationStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT t1.locName, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM (
SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $queryTime)." sessionTable
+ FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
GROUP BY location.locationname
) t1
INNER JOIN (
SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum'
- FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $queryTime)." offlineTable
+ FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable
INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
GROUP BY location.locationname
@@ -45,35 +42,31 @@ class StatisticReporting
}
// logins between bounds
- public static function getUserStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $queryTime = time() - $cutOffTimeInSeconds;
+ public static function getUserStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count'
FROM statistic
- WHERE typeid='.vmchooser-session-name' AND dateline>=$queryTime
+ WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY)
AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
GROUP BY username ORDER BY 2 DESC");
return $res;
}
// vm logins between bounds
- public static function getVMStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $queryTime = time() - $cutOffTimeInSeconds;
- $res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline>=$queryTime
+ public static function getVMStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
+ $res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY)
AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) GROUP BY data ORDER BY 2 DESC");
return $res;
}
- public static function getOverallStatistics ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $queryTime = time() - $cutOffTimeInSeconds;
+ public static function getOverallStatistics ($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, SUM(sessionTable.length >= 60) AS longSessions, SUM(sessionTable.length < 60) AS shortSessions
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $queryTime)." sessionTable");
+ FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable");
return $res;
}
- public static function getTotalOfflineStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $queryTime = time() - $cutOffTimeInSeconds;
+ public static function getTotalOfflineStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED))
- FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $queryTime)." offlineTable");
+ FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable");
return $res;
}
@@ -97,13 +90,13 @@ class StatisticReporting
return round($median);
}
- private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound, $cutoffTime)
+ private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound, $cutOff)
{
$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)),
@@ -157,7 +150,7 @@ class StatisticReporting
machineuuid
from statistic
- where dateline >= $cutoffTime and typeid = '$typeid' and (
+ where dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) and 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)))
)
diff --git a/modules-available/statistics_reporting/lang/de/template-tags.json b/modules-available/statistics_reporting/lang/de/template-tags.json
index 68b68250..207949a9 100644
--- a/modules-available/statistics_reporting/lang/de/template-tags.json
+++ b/modules-available/statistics_reporting/lang/de/template-tags.json
@@ -20,8 +20,8 @@
"lang_location": "Raum",
"lang_medianSessionLength": "Mittlere Sitzungsdauer",
"lang_totalTime": "Gesamte Zeit",
- "lang_last1": "Letzten 24 Stunden",
- "lang_last2": "Letzten 48 Stunden",
+ "lang_last1": "Heute",
+ "lang_last2": "Letzten 2 Tage",
"lang_last7": "Letzten 7 Tage",
"lang_last14": "Letzten 14 Tage",
"lang_last30": "Letzten 30 Tage",
diff --git a/modules-available/statistics_reporting/lang/en/template-tags.json b/modules-available/statistics_reporting/lang/en/template-tags.json
index b9e0c61d..e9430960 100644
--- a/modules-available/statistics_reporting/lang/en/template-tags.json
+++ b/modules-available/statistics_reporting/lang/en/template-tags.json
@@ -20,8 +20,8 @@
"lang_location": "Location",
"lang_medianSessionLength": "Median Session Length",
"lang_totalTime": "Total Time",
- "lang_last1": "Last 24 hours",
- "lang_last2": "Last 48 hours",
+ "lang_last1": "Today",
+ "lang_last2": "Last 2 days",
"lang_last7": "Last 7 days",
"lang_last14": "Last 14 days",
"lang_last30": "Last 30 days",
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index f0f56e87..a55ba33f 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -24,8 +24,7 @@ class Page_Statistics_Reporting extends Page
{
// timespan you want to see = Days selected * seconds per Day (86400)
// default = 14 days
- $cutOff = Request::get('cutoff', 14, 'int');
- $cutOffTimer = $cutOff * 86400;
+ $cutOff = Request::get('cutoff', 14, 'int') - 1;
$lowerTimeBound = Request::get('lower', 0, 'int');
@@ -33,17 +32,17 @@ class Page_Statistics_Reporting extends Page
// total time online, average time online, total number of logins
- $res = StatisticReporting::getOverallStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
+ $res = StatisticReporting::getOverallStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
$row = $res->fetch(PDO::FETCH_NUM);
$data = array('time' => StatisticReporting::formatSeconds($row[0]), 'medianTime' => StatisticReporting::formatSeconds(StatisticReporting::calcMedian($row[1])), 'sessions' => $row[2], 'shortSessions' => $row[3]);
//total time offline
- $res = StatisticReporting::getTotalOfflineStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
+ $res = StatisticReporting::getTotalOfflineStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
$row = $res->fetch(PDO::FETCH_NUM);
$data = array_merge($data, array('totalOfftime' => StatisticReporting::formatSeconds($row[0])));
// per location
- $res = StatisticReporting::getLocationStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
+ $res = StatisticReporting::getLocationStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
$data[] = array('perLocation' => array());
while ($row = $res->fetch(PDO::FETCH_NUM)) {
$median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row[2]));
@@ -52,7 +51,7 @@ class Page_Statistics_Reporting extends Page
}
// per client
- $res = StatisticReporting::getClientStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
+ $res = StatisticReporting::getClientStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
$data[] = array('perClient' => array());
while ($row = $res->fetch(PDO::FETCH_NUM)) {
$median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row[2]));
@@ -62,14 +61,14 @@ class Page_Statistics_Reporting extends Page
}
// per user
- $res = StatisticReporting::getUserStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
+ $res = StatisticReporting::getUserStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
$data[] = array('perUser' => array());
while ($row = $res->fetch(PDO::FETCH_NUM)) {
$data['perUser'][] = array('user' => $row[0], 'sessions' => $row[1]);
}
// per vm
- $res = StatisticReporting::getVMStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
+ $res = StatisticReporting::getVMStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
$data[] = array('perVM' => array());
while ($row = $res->fetch(PDO::FETCH_NUM)) {
$data['perVM'][] = array('vm' => $row[0], 'sessions' => $row[1]);
--
cgit v1.2.3-55-g7522
From e61bb374f6bd7eddd6c7efdc80b835a88a1156e7 Mon Sep 17 00:00:00 2001
From: Christian Hofmaier
Date: Fri, 23 Dec 2016 16:01:58 +0100
Subject: [statistics_reporting] location for clienttable
---
.../statistics_reporting/inc/statisticreporting.inc.php | 5 +++--
modules-available/statistics_reporting/page.inc.php | 2 +-
2 files changed, 4 insertions(+), 3 deletions(-)
diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index 626aa290..c821701b 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -5,10 +5,11 @@ class StatisticReporting
{
public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $res = Database::simpleQuery("SELECT t1.name, timeSum, medianTime, offlineSum, longSessions, 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', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout'
+ $res = Database::simpleQuery("SELECT t1.name, timeSum, medianTime, offlineSum, longSessions, lastLogout, lastStart, shortSessions, locName FROM (
+ SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '') AS 'locName'
FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
+ LEFT JOIN location ON machine.locationid = location.locationid
GROUP BY machine.machineuuid
) t1
INNER JOIN (
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index a55ba33f..8bd593f4 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -57,7 +57,7 @@ class Page_Statistics_Reporting extends Page
$median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row[2]));
$data['perClient'][] = array('hostname' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1],
'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'sessions' => $row[4],
- 'lastLogout' => date(DATE_RSS,$row[5]), 'lastLogoutUnixtime' => $row[5], 'lastStart' => date(DATE_RSS,$row[6]), 'lastStartUnixtime' => $row[6], 'shortSessions' => $row[7]);
+ 'lastLogout' => date(DATE_RSS,$row[5]), 'lastLogoutUnixtime' => $row[5], 'lastStart' => date(DATE_RSS,$row[6]), 'lastStartUnixtime' => $row[6], 'shortSessions' => $row[7], 'locationName' => $row[8]);
}
// per user
--
cgit v1.2.3-55-g7522
From 199fa6ebf1758a75c527964edb7147006f7e148b Mon Sep 17 00:00:00 2001
From: Christian Hofmaier
Date: Fri, 23 Dec 2016 16:17:52 +0100
Subject: [statistics_reporting] replaced empty location string
---
.../statistics_reporting/inc/statisticreporting.inc.php | 8 ++++----
1 file changed, 4 insertions(+), 4 deletions(-)
diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index c821701b..9941b078 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -6,7 +6,7 @@ class StatisticReporting
public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT t1.name, timeSum, medianTime, offlineSum, longSessions, lastLogout, lastStart, shortSessions, locName FROM (
- SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '') AS 'locName'
+ SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, 'NOT ASSIGNED') AS 'locName'
FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
@@ -22,17 +22,17 @@ class StatisticReporting
return $res;
}
- // IFNULL(location.locationname, '') - emptry string can be replaced with anything (name of the null-ids in the table)
+ // IFNULL(location.locationname, 'NOT ASSIGNED') - NOT ASSIGNED string can be replaced with anything (name of the null-ids in the table)
public static function getLocationStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT t1.locName, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM (
- SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
+ SELECT IFNULL(location.locationname, 'NOT ASSIGNED') AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
GROUP BY location.locationname
) t1
INNER JOIN (
- SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum'
+ SELECT IFNULL(location.locationname, 'NOT ASSIGNED') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum'
FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable
INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
--
cgit v1.2.3-55-g7522
From 8c9c28df0401f0bc89ab1cfc05332e6e1727d0d3 Mon Sep 17 00:00:00 2001
From: Udo Walter
Date: Fri, 23 Dec 2016 17:24:05 +0100
Subject: [statistics_reporting] added location column in client table
---
.../statistics_reporting/inc/statisticreporting.inc.php | 8 +++++---
.../statistics_reporting/lang/de/template-tags.json | 3 ++-
.../statistics_reporting/lang/en/template-tags.json | 3 ++-
modules-available/statistics_reporting/templates/_page.html | 4 +++-
.../statistics_reporting/templates/columnChooser.html | 12 ++++++++++++
5 files changed, 24 insertions(+), 6 deletions(-)
diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index 9941b078..b75c6eaa 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -5,8 +5,9 @@ class StatisticReporting
{
public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
+ $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
$res = Database::simpleQuery("SELECT t1.name, timeSum, medianTime, offlineSum, longSessions, lastLogout, lastStart, shortSessions, locName FROM (
- SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, 'NOT ASSIGNED') AS 'locName'
+ SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName'
FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
@@ -24,15 +25,16 @@ class StatisticReporting
// IFNULL(location.locationname, 'NOT ASSIGNED') - NOT ASSIGNED string can be replaced with anything (name of the null-ids in the table)
public static function getLocationStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
+ $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
$res = Database::simpleQuery("SELECT t1.locName, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM (
- SELECT IFNULL(location.locationname, 'NOT ASSIGNED') AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
+ SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
GROUP BY location.locationname
) t1
INNER JOIN (
- SELECT IFNULL(location.locationname, 'NOT ASSIGNED') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum'
+ SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum'
FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable
INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
diff --git a/modules-available/statistics_reporting/lang/de/template-tags.json b/modules-available/statistics_reporting/lang/de/template-tags.json
index 207949a9..841e87bd 100644
--- a/modules-available/statistics_reporting/lang/de/template-tags.json
+++ b/modules-available/statistics_reporting/lang/de/template-tags.json
@@ -26,5 +26,6 @@
"lang_last14": "Letzten 14 Tage",
"lang_last30": "Letzten 30 Tage",
"lang_last90": "Letzten 90 Tage",
- "lang_apply": "Anwenden"
+ "lang_apply": "Anwenden",
+ "lang_notassigned": "NICHT ZUGEWIESEN"
}
\ 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 e9430960..73b3c2fc 100644
--- a/modules-available/statistics_reporting/lang/en/template-tags.json
+++ b/modules-available/statistics_reporting/lang/en/template-tags.json
@@ -26,5 +26,6 @@
"lang_last14": "Last 14 days",
"lang_last30": "Last 30 days",
"lang_last90": "Last 90 days",
- "lang_apply": "Apply"
+ "lang_apply": "Apply",
+ "lang_notassigned": "NOT ASSIGNED"
}
\ No newline at end of file
diff --git a/modules-available/statistics_reporting/templates/_page.html b/modules-available/statistics_reporting/templates/_page.html
index d2aaecde..fbdde6d5 100644
--- a/modules-available/statistics_reporting/templates/_page.html
+++ b/modules-available/statistics_reporting/templates/_page.html
@@ -41,7 +41,7 @@
{{#perLocation}}
- {{location}} |
+ {{location}} |
{{time}} |
{{medianTime}} |
{{sessions}} |
@@ -59,6 +59,7 @@
{{lang_hostname}} |
+ {{lang_location}} |
{{lang_totalTime}} |
{{lang_medianSessionLength}} |
{{lang_sessions}} |
@@ -72,6 +73,7 @@
{{#perClient}}
{{hostname}} |
+ {{locationName}} |
{{time}} |
{{medianTime}} |
{{sessions}} |
diff --git a/modules-available/statistics_reporting/templates/columnChooser.html b/modules-available/statistics_reporting/templates/columnChooser.html
index 49c9a75d..06cd6770 100644
--- a/modules-available/statistics_reporting/templates/columnChooser.html
+++ b/modules-available/statistics_reporting/templates/columnChooser.html
@@ -24,6 +24,7 @@
+
@@ -69,9 +70,20 @@
var arrow = data.direction === dir.ASC ? "down" : "up";
th.eq(data.column).append(' ');
});
+
+ $(".locationLink").click(function(e) {
+ e.preventDefault();
+ $('#select-table').val('perlocation');
+ chooseTable('perlocation');
+ var target = $(".locationName:contains('"+$(this).text()+"'):first");
+ $(window).scrollTop(target.offset().top - $(window).height()/2);
+ target.parent().css( "background-color", "#f8ff99" );
+ return false;
+ });
});
function chooseTable(v) {
+ $("tr").removeAttr('style');
$("[id^=table-]").hide();
$('#table-'+v).show();
$("[id^=button-]").hide();
--
cgit v1.2.3-55-g7522
From d7eaf72343acc41f648f2c1ed6ff1845d9b514c2 Mon Sep 17 00:00:00 2001
From: Christian Hofmaier
Date: Thu, 29 Dec 2016 15:10:43 +0100
Subject: [statistics_reporting] added comments and improved readability
---
.../inc/statisticreporting.inc.php | 31 +++++++++++++++-------
.../statistics_reporting/page.inc.php | 4 +--
2 files changed, 23 insertions(+), 12 deletions(-)
diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index b75c6eaa..689af37d 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -4,9 +4,10 @@
class StatisticReporting
{
+ // Client Data: Name, Time Online, Median Time Online, Time Offline, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location
public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
- $res = Database::simpleQuery("SELECT t1.name, timeSum, medianTime, offlineSum, longSessions, lastLogout, lastStart, shortSessions, locName FROM (
+ $res = Database::simpleQuery("SELECT t1.name, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName FROM (
SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName'
FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
@@ -23,7 +24,7 @@ class StatisticReporting
return $res;
}
- // IFNULL(location.locationname, 'NOT ASSIGNED') - NOT ASSIGNED string can be replaced with anything (name of the null-ids in the table)
+ // Location Data: Name, Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec
public static function getLocationStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
$res = Database::simpleQuery("SELECT t1.locName, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM (
@@ -44,40 +45,47 @@ class StatisticReporting
return $res;
}
- // logins between bounds
+ // User Data: Name, Number of Logins
public static function getUserStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count'
FROM statistic
WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY)
AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
- GROUP BY username ORDER BY 2 DESC");
+ GROUP BY username
+ ORDER BY 2 DESC");
return $res;
}
- // vm logins between bounds
+ // Virtual Machine Data: Name, Number of Usages
public static function getVMStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY)
- AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) GROUP BY data ORDER BY 2 DESC");
+ AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
+ GROUP BY data
+ ORDER BY 2 DESC");
return $res;
}
+ //Total Data: Time Online, Median Time Online, Number of Sessions > 60Sec, Number of Sessions < 60Sec
public static function getOverallStatistics ($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, SUM(sessionTable.length >= 60) AS longSessions, SUM(sessionTable.length < 60) AS shortSessions
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable");
+ FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable");
return $res;
}
+ // Total Data(2): Time Offline
public static function getTotalOfflineStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED))
FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable");
return $res;
}
+ // Format $seconds into ".d .h .m .s" format (day, hour, minute, second)
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';
}
+ // Calculate Median
public static function calcMedian($string) {
$arr = explode(",", $string);
sort($arr, SORT_NUMERIC);
@@ -93,6 +101,7 @@ class StatisticReporting
return round($median);
}
+ // query string which provides table with time-cutoff and time-bounds
private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound, $cutOff)
{
$lowerFormat = "'%y-%m-%d $lowerTimeBound:00:00'";
@@ -153,9 +162,11 @@ class StatisticReporting
machineuuid
from statistic
- where dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) and 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)))
+ where dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) and 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/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index 8bd593f4..13446ed9 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -56,8 +56,8 @@ class Page_Statistics_Reporting extends Page
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],
- 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'sessions' => $row[4],
- 'lastLogout' => date(DATE_RSS,$row[5]), 'lastLogoutUnixtime' => $row[5], 'lastStart' => date(DATE_RSS,$row[6]), 'lastStartUnixtime' => $row[6], 'shortSessions' => $row[7], 'locationName' => $row[8]);
+ 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'lastStartUnixtime' => $row[4],
+ 'lastLogout' => date(DATE_RSS,$row[5]), 'lastLogoutUnixtime' => $row[5], 'lastStart' => date(DATE_RSS,$row[6]), 'sessions' => $row[6], 'shortSessions' => $row[7], 'locationName' => $row[8]);
}
// per user
--
cgit v1.2.3-55-g7522
From 3234034aa5f8c886b45b0905a9072aed942c383b Mon Sep 17 00:00:00 2001
From: Udo Walter
Date: Thu, 29 Dec 2016 15:11:23 +0100
Subject: [statistics_reporting] small label changes
---
modules-available/statistics_reporting/lang/de/template-tags.json | 3 ++-
modules-available/statistics_reporting/lang/en/template-tags.json | 3 ++-
modules-available/statistics_reporting/templates/_page.html | 6 +++---
modules-available/statistics_reporting/templates/columnChooser.html | 1 +
4 files changed, 8 insertions(+), 5 deletions(-)
diff --git a/modules-available/statistics_reporting/lang/de/template-tags.json b/modules-available/statistics_reporting/lang/de/template-tags.json
index 841e87bd..7dcec880 100644
--- a/modules-available/statistics_reporting/lang/de/template-tags.json
+++ b/modules-available/statistics_reporting/lang/de/template-tags.json
@@ -3,7 +3,8 @@
"lang_countLogins": "Anzahl Logins",
"lang_client": "Client",
"lang_vm": "VM",
- "lang_sessions": "Sitzungen \u2265 60s",
+ "lang_sessions": "Sitzungen",
+ "lang_longSessions": "Sitzungen \u2265 60s",
"lang_shortSessions": "Sitzungen < 60s",
"lang_overallOfftime": "Insgesamte Zeit Offline",
"lang_totalOffTime": "Gesamtzeit Offline",
diff --git a/modules-available/statistics_reporting/lang/en/template-tags.json b/modules-available/statistics_reporting/lang/en/template-tags.json
index 73b3c2fc..366a10de 100644
--- a/modules-available/statistics_reporting/lang/en/template-tags.json
+++ b/modules-available/statistics_reporting/lang/en/template-tags.json
@@ -3,7 +3,8 @@
"lang_countLogins": "Number of Logins",
"lang_client": "Client",
"lang_vm": "VM",
- "lang_sessions": "Sessions \u2265 60s",
+ "lang_sessions": "Sessions",
+ "lang_longSessions": "Sessions \u2265 60s",
"lang_shortSessions": "Sessions < 60s",
"lang_overallOfftime": "Overall time offline",
"lang_totalOffTime": "Total Time Offline",
diff --git a/modules-available/statistics_reporting/templates/_page.html b/modules-available/statistics_reporting/templates/_page.html
index fbdde6d5..649ef2d3 100644
--- a/modules-available/statistics_reporting/templates/_page.html
+++ b/modules-available/statistics_reporting/templates/_page.html
@@ -7,7 +7,7 @@
|
{{lang_totalTime}} |
{{lang_medianSessionLength}} |
- {{lang_sessions}} |
+ {{lang_longSessions}} |
{{lang_shortSessions}} |
{{lang_overallOfftime}} |
@@ -33,7 +33,7 @@
{{lang_location}} |
{{lang_totalTime}} |
{{lang_medianSessionLength}} |
- {{lang_sessions}} |
+ {{lang_longSessions}} |
{{lang_shortSessions}} |
{{lang_totalOffTime}} |
@@ -62,7 +62,7 @@
{{lang_location}} |
{{lang_totalTime}} |
{{lang_medianSessionLength}} |
- {{lang_sessions}} |
+ {{lang_longSessions}} |
{{lang_shortSessions}} |
{{lang_totalOffTime}} |
{{lang_clientLogout}} |
diff --git a/modules-available/statistics_reporting/templates/columnChooser.html b/modules-available/statistics_reporting/templates/columnChooser.html
index 06cd6770..09551fc1 100644
--- a/modules-available/statistics_reporting/templates/columnChooser.html
+++ b/modules-available/statistics_reporting/templates/columnChooser.html
@@ -28,6 +28,7 @@
+
--
cgit v1.2.3-55-g7522
From dc709dbe45774f88b8abc97a425a97942e9ba3c0 Mon Sep 17 00:00:00 2001
From: Udo Walter
Date: Thu, 29 Dec 2016 16:01:06 +0100
Subject: [statistics_reporting] bugfix
---
modules-available/statistics_reporting/templates/_page.html | 6 +++---
1 file changed, 3 insertions(+), 3 deletions(-)
diff --git a/modules-available/statistics_reporting/templates/_page.html b/modules-available/statistics_reporting/templates/_page.html
index 649ef2d3..37c3cf90 100644
--- a/modules-available/statistics_reporting/templates/_page.html
+++ b/modules-available/statistics_reporting/templates/_page.html
@@ -17,7 +17,7 @@
{{lang_total}} |
{{time}} |
{{medianTime}} |
- {{sessions}} |
+ {{sessions}} |
{{shortSessions}} |
{{totalOfftime}} |
@@ -44,7 +44,7 @@
{{location}} |
{{time}} |
{{medianTime}} |
- {{sessions}} |
+ {{sessions}} |
{{shortSessions}} |
{{offTime}} |
@@ -76,7 +76,7 @@
{{locationName}} |
{{time}} |
{{medianTime}} |
- {{sessions}} |
+ {{sessions}} |
{{shortSessions}} |
{{offTime}} |
{{lastLogout}} |
--
cgit v1.2.3-55-g7522
From 18a646aaf1032946574091f5be03347b3cf09855 Mon Sep 17 00:00:00 2001
From: Christian Hofmaier
Date: Mon, 9 Jan 2017 15:11:30 +0100
Subject: [statistics_reporting] bugfix
---
modules-available/statistics_reporting/inc/statisticreporting.inc.php | 2 +-
modules-available/statistics_reporting/page.inc.php | 4 ++--
2 files changed, 3 insertions(+), 3 deletions(-)
diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index 689af37d..39aa9377 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -4,7 +4,7 @@
class StatisticReporting
{
- // Client Data: Name, Time Online, Median Time Online, Time Offline, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location
+ // Client Data: Name, Time Online, Median Time Online, Time Offline, last start, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location
public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
$res = Database::simpleQuery("SELECT t1.name, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName FROM (
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index 13446ed9..c309bfb6 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -56,8 +56,8 @@ class Page_Statistics_Reporting extends Page
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],
- 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'lastStartUnixtime' => $row[4],
- 'lastLogout' => date(DATE_RSS,$row[5]), 'lastLogoutUnixtime' => $row[5], 'lastStart' => date(DATE_RSS,$row[6]), 'sessions' => $row[6], 'shortSessions' => $row[7], 'locationName' => $row[8]);
+ 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'lastStart' => date(DATE_RSS,$row[4]), 'lastStartUnixtime' => $row[4],
+ 'lastLogout' => date(DATE_RSS,$row[5]), 'lastLogoutUnixtime' => $row[5], 'sessions' => $row[6], 'shortSessions' => $row[7], 'locationName' => $row[8]);
}
// per user
--
cgit v1.2.3-55-g7522
From d7fa39c85738fb998c79a866a7f0ed36b9403a87 Mon Sep 17 00:00:00 2001
From: Christian Hofmaier
Date: Mon, 9 Jan 2017 16:48:45 +0100
Subject: [statistics_reporting] improved readability, added hashed values to
queries
---
.../inc/statisticreporting.inc.php | 20 ++++++-------
.../statistics_reporting/page.inc.php | 34 +++++++++++-----------
2 files changed, 27 insertions(+), 27 deletions(-)
diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index 39aa9377..c209c5ef 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -4,11 +4,11 @@
class StatisticReporting
{
- // Client Data: Name, Time Online, Median Time Online, Time Offline, last start, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location
+ // Client Data: Name, Time Online, Median Time Online, Time Offline, last start, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized)
public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
- $res = Database::simpleQuery("SELECT t1.name, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName FROM (
- SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName'
+ $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(locId) AS locHash, MD5(t1.uuid) AS clientHash FROM (
+ SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId'
FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
@@ -24,11 +24,11 @@ class StatisticReporting
return $res;
}
- // Location Data: Name, Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec
+ // Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec
public static function getLocationStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
$notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
- $res = Database::simpleQuery("SELECT t1.locName, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM (
- SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
+ $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(locId) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM (
+ SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
@@ -45,9 +45,9 @@ class StatisticReporting
return $res;
}
- // User Data: Name, Number of Logins
+ // User Data: Name, Name(anonymized), Number of Logins
public static function getUserStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count'
+ $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(username)) AS userHash, COUNT(*) AS 'count'
FROM statistic
WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY)
AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
@@ -58,7 +58,7 @@ class StatisticReporting
// Virtual Machine Data: Name, Number of Usages
public static function getVMStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY)
+ $res = Database::simpleQuery("SELECT data AS name, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY)
AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
GROUP BY data
ORDER BY 2 DESC");
@@ -74,7 +74,7 @@ class StatisticReporting
// Total Data(2): Time Offline
public static function getTotalOfflineStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED))
+ $res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED)) AS timeOff
FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable");
return $res;
}
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index c309bfb6..e2c76b09 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -33,45 +33,45 @@ class Page_Statistics_Reporting extends Page
// total time online, average time online, total number of logins
$res = StatisticReporting::getOverallStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
- $row = $res->fetch(PDO::FETCH_NUM);
- $data = array('time' => StatisticReporting::formatSeconds($row[0]), 'medianTime' => StatisticReporting::formatSeconds(StatisticReporting::calcMedian($row[1])), 'sessions' => $row[2], 'shortSessions' => $row[3]);
+ $row = $res->fetch(PDO::FETCH_ASSOC);
+ $data = array('time' => StatisticReporting::formatSeconds($row['sum']), 'medianTime' => StatisticReporting::formatSeconds(StatisticReporting::calcMedian($row['median'])), 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']);
//total time offline
$res = StatisticReporting::getTotalOfflineStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
- $row = $res->fetch(PDO::FETCH_NUM);
- $data = array_merge($data, array('totalOfftime' => StatisticReporting::formatSeconds($row[0])));
+ $row = $res->fetch(PDO::FETCH_ASSOC);
+ $data = array_merge($data, array('totalOfftime' => StatisticReporting::formatSeconds($row['timeOff'])));
// per location
$res = StatisticReporting::getLocationStatistics($cutOff, $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],
- 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'sessions' => $row[4], 'shortSessions' => $row[5]);
+ while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+ $median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row['medianTime']));
+ $data['perLocation'][] = array('location' => $row['locName'], 'time' => StatisticReporting::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'],
+ 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']);
}
// per client
$res = StatisticReporting::getClientStatistics($cutOff, $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],
- 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'lastStart' => date(DATE_RSS,$row[4]), 'lastStartUnixtime' => $row[4],
- 'lastLogout' => date(DATE_RSS,$row[5]), 'lastLogoutUnixtime' => $row[5], 'sessions' => $row[6], 'shortSessions' => $row[7], 'locationName' => $row[8]);
+ while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+ $median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row['medianTime']));
+ $data['perClient'][] = array('hostname' => $row['clientName'], 'time' => StatisticReporting::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'],
+ 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'lastStart' => date(DATE_RSS,$row['lastStart']), 'lastStartUnixtime' => $row['lastStart'],
+ 'lastLogout' => date(DATE_RSS,$row['lastLogout']), 'lastLogoutUnixtime' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row['locName']);
}
// per user
$res = StatisticReporting::getUserStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
$data[] = array('perUser' => array());
- while ($row = $res->fetch(PDO::FETCH_NUM)) {
- $data['perUser'][] = array('user' => $row[0], 'sessions' => $row[1]);
+ while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+ $data['perUser'][] = array('user' => $row['name'], 'sessions' => $row['count']);
}
// per vm
$res = StatisticReporting::getVMStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
$data[] = array('perVM' => array());
- while ($row = $res->fetch(PDO::FETCH_NUM)) {
- $data['perVM'][] = array('vm' => $row[0], 'sessions' => $row[1]);
+ while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+ $data['perVM'][] = array('vm' => $row['name'], 'sessions' => $row['count']);
}
Render::addTemplate('columnChooser');
--
cgit v1.2.3-55-g7522
From 9ccef127acd6dca0971e12726b019e2beaf7e4ce Mon Sep 17 00:00:00 2001
From: Udo Walter
Date: Thu, 12 Jan 2017 12:41:21 +0100
Subject: [statistics_reporting] moved some functions to a new class
---
.../statistics_reporting/inc/getdata.inc.php | 98 ++++++++++++
.../statistics_reporting/inc/queries.inc.php | 153 ++++++++++++++++++
.../inc/statisticreporting.inc.php | 175 ---------------------
.../statistics_reporting/page.inc.php | 55 +------
4 files changed, 259 insertions(+), 222 deletions(-)
create mode 100644 modules-available/statistics_reporting/inc/getdata.inc.php
create mode 100644 modules-available/statistics_reporting/inc/queries.inc.php
delete mode 100644 modules-available/statistics_reporting/inc/statisticreporting.inc.php
diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php
new file mode 100644
index 00000000..a1df66d0
--- /dev/null
+++ b/modules-available/statistics_reporting/inc/getdata.inc.php
@@ -0,0 +1,98 @@
+fetch(PDO::FETCH_ASSOC);
+ $data = array('time' => self::formatSeconds($row['sum']), 'medianTime' => self::formatSeconds(self::calcMedian($row['median'])), 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']);
+
+ //total time offline
+ $res = Queries::getTotalOfflineStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound);
+ $row = $res->fetch(PDO::FETCH_ASSOC);
+ $data = array_merge($data, array('totalOfftime' => self::formatSeconds($row['timeOff'])));
+
+ return $data;
+ }
+
+ // per location
+ public static function perLocation($anonymize = false) {
+ $res = Queries::getLocationStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound);
+ $data = array();
+ $loc = $anonymize ? 'locHash' : 'locName';
+ while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+ $median = self::calcMedian(self::calcMedian($row['medianTime']));
+ $data[] = array('location' => $row[$loc], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'],
+ 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']);
+ }
+ return $data;
+ }
+
+ // per client
+ public static function perClient($anonymize = false) {
+ $res = Queries::getClientStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound);
+ $data = array();
+ $name = $anonymize ? 'clientHash' : 'hostname';
+ $loc = $anonymize ? 'locHash' : 'locName';
+ while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+ $median = self::calcMedian(self::calcMedian($row['medianTime']));
+ $data[] = array('hostname' => $row[$name], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'],
+ 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'lastStart' => date(DATE_RSS,$row['lastStart']), 'lastStartUnixtime' => $row['lastStart'],
+ 'lastLogout' => date(DATE_RSS,$row['lastLogout']), 'lastLogoutUnixtime' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row[$loc]);
+ }
+ return $data;
+ }
+
+ // per user
+ public static function perUser($anonymize = false) {
+ $res = Queries::getUserStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound);
+ $data = array();
+ $user = $anonymize ? 'userHash' : 'name';
+ while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+ $data[] = array('user' => $row['name'], 'sessions' => $row['count']);
+ }
+ return $data;
+ }
+
+
+ // per vm
+ public static function perVM() {
+ $res = Queries::getVMStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound);
+ $data = array();
+ while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+ $data[] = array('vm' => $row['name'], 'sessions' => $row['count']);
+ }
+ return $data;
+ }
+
+
+
+ // Format $seconds into ".d .h .m .s" format (day, hour, minute, second)
+ private static function formatSeconds($seconds)
+ {
+ return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s';
+ }
+
+ // Calculate Median
+ private 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);
+ }
+}
\ No newline at end of file
diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php
new file mode 100644
index 00000000..23afceed
--- /dev/null
+++ b/modules-available/statistics_reporting/inc/queries.inc.php
@@ -0,0 +1,153 @@
+ 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized)
+ public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
+ $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
+ $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(locId) AS locHash, MD5(t1.uuid) AS clientHash FROM (
+ SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId'
+ FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
+ INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
+ LEFT JOIN location ON machine.locationid = location.locationid
+ GROUP BY machine.machineuuid
+ ) t1
+ INNER JOIN (
+ 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, $cutOff)." offlineTable
+ INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
+ GROUP BY machine.machineuuid
+ ) t2
+ ON t1.uuid = t2.uuid");
+ return $res;
+ }
+
+ // Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec
+ public static function getLocationStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
+ $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
+ $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(locId) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM (
+ SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
+ FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
+ INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
+ LEFT JOIN location ON machine.locationid = location.locationid
+ GROUP BY location.locationname
+ ) t1
+ INNER JOIN (
+ SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum'
+ FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable
+ INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
+ LEFT JOIN location ON machine.locationid = location.locationid
+ GROUP BY location.locationname
+ ) t2
+ ON t1.locName = t2.locName");
+ return $res;
+ }
+
+ // User Data: Name, Name(anonymized), Number of Logins
+ public static function getUserStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
+ $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(username)) AS userHash, COUNT(*) AS 'count'
+ FROM statistic
+ WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY)
+ AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
+ GROUP BY username
+ ORDER BY 2 DESC");
+ return $res;
+ }
+
+ // Virtual Machine Data: Name, Number of Usages
+ public static function getVMStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
+ $res = Database::simpleQuery("SELECT data AS name, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY)
+ AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
+ GROUP BY data
+ ORDER BY 2 DESC");
+ return $res;
+ }
+
+ //Total Data: Time Online, Median Time Online, Number of Sessions > 60Sec, Number of Sessions < 60Sec
+ public static function getOverallStatistics ($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
+ $res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, SUM(sessionTable.length >= 60) AS longSessions, SUM(sessionTable.length < 60) AS shortSessions
+ FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable");
+ return $res;
+ }
+
+ // Total Data(2): Time Offline
+ public static function getTotalOfflineStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
+ $res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED)) AS timeOff
+ FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable");
+ return $res;
+ }
+
+ // query string which provides table with time-cutoff and time-bounds
+ private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound, $cutOff)
+ {
+ $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 dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) and 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/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
deleted file mode 100644
index c209c5ef..00000000
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ /dev/null
@@ -1,175 +0,0 @@
- 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized)
- public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
- $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(locId) AS locHash, MD5(t1.uuid) AS clientHash FROM (
- SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId'
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
- INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
- LEFT JOIN location ON machine.locationid = location.locationid
- GROUP BY machine.machineuuid
- ) t1
- INNER JOIN (
- 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, $cutOff)." offlineTable
- INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
- GROUP BY machine.machineuuid
- ) t2
- ON t1.uuid = t2.uuid");
- return $res;
- }
-
- // Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec
- public static function getLocationStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
- $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(locId) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM (
- SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
- INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
- LEFT JOIN location ON machine.locationid = location.locationid
- GROUP BY location.locationname
- ) t1
- INNER JOIN (
- SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum'
- FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable
- INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
- LEFT JOIN location ON machine.locationid = location.locationid
- GROUP BY location.locationname
- ) t2
- ON t1.locName = t2.locName");
- return $res;
- }
-
- // User Data: Name, Name(anonymized), Number of Logins
- public static function getUserStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(username)) AS userHash, COUNT(*) AS 'count'
- FROM statistic
- WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY)
- AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
- GROUP BY username
- ORDER BY 2 DESC");
- return $res;
- }
-
- // Virtual Machine Data: Name, Number of Usages
- public static function getVMStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $res = Database::simpleQuery("SELECT data AS name, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY)
- AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
- GROUP BY data
- ORDER BY 2 DESC");
- return $res;
- }
-
- //Total Data: Time Online, Median Time Online, Number of Sessions > 60Sec, Number of Sessions < 60Sec
- public static function getOverallStatistics ($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, SUM(sessionTable.length >= 60) AS longSessions, SUM(sessionTable.length < 60) AS shortSessions
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable");
- return $res;
- }
-
- // Total Data(2): Time Offline
- public static function getTotalOfflineStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED)) AS timeOff
- FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable");
- return $res;
- }
-
- // Format $seconds into ".d .h .m .s" format (day, hour, minute, second)
- 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';
- }
-
- // Calculate Median
- 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);
- }
-
- // query string which provides table with time-cutoff and time-bounds
- private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound, $cutOff)
- {
- $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 dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) and 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/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index e2c76b09..82c03acc 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -24,55 +24,16 @@ class Page_Statistics_Reporting extends Page
{
// timespan you want to see = Days selected * seconds per Day (86400)
// default = 14 days
- $cutOff = Request::get('cutoff', 14, 'int') - 1;
+ GetData::$cutOff = Request::get('cutoff', 14, 'int') - 1;
+ GetData::$lowerTimeBound = Request::get('lower', 0, 'int');
+ GetData::$upperTimeBound = Request::get('upper', 24, 'int');
- $lowerTimeBound = Request::get('lower', 0, 'int');
+ $data = array_merge(GetData::total(), array('perLocation' => array(), 'perClient' => array(), 'perUser' => array(), 'perVM' => array()));
+ $data['perLocation'] = GetData::perLocation();
+ $data['perClient'] = GetData::perClient();
+ $data['perUser'] = GetData::perUser();
+ $data['perVM'] = GetData::perVM();
- $upperTimeBound = Request::get('upper', 24, 'int');
-
-
- // total time online, average time online, total number of logins
- $res = StatisticReporting::getOverallStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
- $row = $res->fetch(PDO::FETCH_ASSOC);
- $data = array('time' => StatisticReporting::formatSeconds($row['sum']), 'medianTime' => StatisticReporting::formatSeconds(StatisticReporting::calcMedian($row['median'])), 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']);
-
- //total time offline
- $res = StatisticReporting::getTotalOfflineStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
- $row = $res->fetch(PDO::FETCH_ASSOC);
- $data = array_merge($data, array('totalOfftime' => StatisticReporting::formatSeconds($row['timeOff'])));
-
- // per location
- $res = StatisticReporting::getLocationStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
- $data[] = array('perLocation' => array());
- while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
- $median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row['medianTime']));
- $data['perLocation'][] = array('location' => $row['locName'], 'time' => StatisticReporting::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'],
- 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']);
- }
-
- // per client
- $res = StatisticReporting::getClientStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
- $data[] = array('perClient' => array());
- while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
- $median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row['medianTime']));
- $data['perClient'][] = array('hostname' => $row['clientName'], 'time' => StatisticReporting::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'],
- 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'lastStart' => date(DATE_RSS,$row['lastStart']), 'lastStartUnixtime' => $row['lastStart'],
- 'lastLogout' => date(DATE_RSS,$row['lastLogout']), 'lastLogoutUnixtime' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row['locName']);
- }
-
- // per user
- $res = StatisticReporting::getUserStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
- $data[] = array('perUser' => array());
- while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
- $data['perUser'][] = array('user' => $row['name'], 'sessions' => $row['count']);
- }
-
- // per vm
- $res = StatisticReporting::getVMStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
- $data[] = array('perVM' => array());
- while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
- $data['perVM'][] = array('vm' => $row['name'], 'sessions' => $row['count']);
- }
Render::addTemplate('columnChooser');
Render::addTemplate('_page', $data);
--
cgit v1.2.3-55-g7522
From 5da76e80a9fb64bfbda5897aa08fa1bc575dbd25 Mon Sep 17 00:00:00 2001
From: Udo Walter
Date: Fri, 13 Jan 2017 13:22:27 +0100
Subject: [statistics_reporting] improved cutoff in query functions + bugfix
---
.../statistics_reporting/hooks/cron.inc.php | 3 +
.../statistics_reporting/inc/getdata.inc.php | 17 ++--
.../statistics_reporting/inc/queries.inc.php | 91 +++++++++++++---------
.../statistics_reporting/page.inc.php | 5 +-
4 files changed, 70 insertions(+), 46 deletions(-)
create mode 100644 modules-available/statistics_reporting/hooks/cron.inc.php
diff --git a/modules-available/statistics_reporting/hooks/cron.inc.php b/modules-available/statistics_reporting/hooks/cron.inc.php
new file mode 100644
index 00000000..b79883df
--- /dev/null
+++ b/modules-available/statistics_reporting/hooks/cron.inc.php
@@ -0,0 +1,3 @@
+fetch(PDO::FETCH_ASSOC);
$data = array('time' => self::formatSeconds($row['sum']), 'medianTime' => self::formatSeconds(self::calcMedian($row['median'])), 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']);
//total time offline
- $res = Queries::getTotalOfflineStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound);
+ $res = Queries::getTotalOfflineStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
$row = $res->fetch(PDO::FETCH_ASSOC);
$data = array_merge($data, array('totalOfftime' => self::formatSeconds($row['timeOff'])));
@@ -24,7 +25,7 @@ class GetData
// per location
public static function perLocation($anonymize = false) {
- $res = Queries::getLocationStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound);
+ $res = Queries::getLocationStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
$data = array();
$loc = $anonymize ? 'locHash' : 'locName';
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
@@ -37,9 +38,9 @@ class GetData
// per client
public static function perClient($anonymize = false) {
- $res = Queries::getClientStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound);
+ $res = Queries::getClientStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
$data = array();
- $name = $anonymize ? 'clientHash' : 'hostname';
+ $name = $anonymize ? 'clientHash' : 'clientName';
$loc = $anonymize ? 'locHash' : 'locName';
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
$median = self::calcMedian(self::calcMedian($row['medianTime']));
@@ -52,7 +53,7 @@ class GetData
// per user
public static function perUser($anonymize = false) {
- $res = Queries::getUserStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound);
+ $res = Queries::getUserStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
$data = array();
$user = $anonymize ? 'userHash' : 'name';
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
@@ -64,7 +65,7 @@ class GetData
// per vm
public static function perVM() {
- $res = Queries::getVMStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound);
+ $res = Queries::getVMStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
$data = array();
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
$data[] = array('vm' => $row['name'], 'sessions' => $row['count']);
diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php
index 23afceed..23f5fb03 100644
--- a/modules-available/statistics_reporting/inc/queries.inc.php
+++ b/modules-available/statistics_reporting/inc/queries.inc.php
@@ -5,18 +5,18 @@ class Queries
{
// Client Data: Name, Time Online, Median Time Online, Time Offline, last start, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized)
- public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
+ public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) {
$notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
$res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(locId) AS locHash, MD5(t1.uuid) AS clientHash FROM (
SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId'
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
+ FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
GROUP BY machine.machineuuid
) t1
INNER JOIN (
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, $cutOff)." offlineTable
+ FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable
INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
GROUP BY machine.machineuuid
) t2
@@ -25,18 +25,18 @@ class Queries
}
// Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec
- public static function getLocationStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
+ public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) {
$notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
$res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(locId) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM (
SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
+ FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
GROUP BY location.locationname
) t1
INNER JOIN (
SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum'
- FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable
+ FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable
INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
GROUP BY location.locationname
@@ -46,80 +46,96 @@ class Queries
}
// User Data: Name, Name(anonymized), Number of Logins
- public static function getUserStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(username)) AS userHash, COUNT(*) AS 'count'
+ public static function getUserStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) {
+ $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(username)) AS userHash, COUNT(*) AS 'count'
FROM statistic
- WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY)
- AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
+ WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND (
+ (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0
+ and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) AND (FROM_UNIXTIME(@start, '%H') < $upperTimeBound))
+ or
+ (@daysDiff = 1 and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) OR (FROM_UNIXTIME(@start, '%H') < $upperTimeBound))
+ or
+ @daysDiff >= 2
+ )
GROUP BY username
ORDER BY 2 DESC");
return $res;
}
// Virtual Machine Data: Name, Number of Usages
- public static function getVMStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $res = Database::simpleQuery("SELECT data AS name, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY)
- AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
+ public static function getVMStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) {
+ $res = Database::simpleQuery("SELECT data AS name, COUNT(*) AS 'count'
+ FROM statistic
+ WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND (
+ (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0
+ and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) AND (FROM_UNIXTIME(@start, '%H') < $upperTimeBound))
+ or
+ (@daysDiff = 1 and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) OR (FROM_UNIXTIME(@start, '%H') < $upperTimeBound))
+ or
+ @daysDiff >= 2
+ )
GROUP BY data
ORDER BY 2 DESC");
return $res;
}
//Total Data: Time Online, Median Time Online, Number of Sessions > 60Sec, Number of Sessions < 60Sec
- public static function getOverallStatistics ($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
+ public static function getOverallStatistics ($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, SUM(sessionTable.length >= 60) AS longSessions, SUM(sessionTable.length < 60) AS shortSessions
- FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable");
+ FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable");
return $res;
}
// Total Data(2): Time Offline
- public static function getTotalOfflineStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
+ public static function getTotalOfflineStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED)) AS timeOff
- FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable");
+ FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable");
return $res;
}
// query string which provides table with time-cutoff and time-bounds
- private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound, $cutOff)
+ private static function getBoundedTableQueryString($typeid, $from, $to, $lowerTimeBound, $upperTimeBound)
{
$lowerFormat = "'%y-%m-%d $lowerTimeBound:00:00'";
$upperFormat = "'%y-%m-%d ".($upperTimeBound-1).":59:59'";
$queryString = "
select
+ @start := IF(dateline < $from, $from, dateline),
+ @end := IF(dateline+data > $to, $to, dateline+data),
@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)
+ (CAST(@end-@start AS SIGNED)
- IF(
- dateline > @startUpper,
- UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat) + INTERVAL 1 DAY) - dateline,
+ @start > @startUpper,
+ UNIX_TIMESTAMP(FROM_UNIXTIME(@start, $lowerFormat) + INTERVAL 1 DAY) - @start,
IF(
- dateline < @startLower,
- @startLower - dateline,
+ @start < @startLower,
+ @startLower - @start,
0
)
)
- IF(
- dateline+data > @endUpper,
- dateline+data - (@endUpper + 1),
+ @end > @endUpper,
+ @end - (@endUpper + 1),
IF(
- dateline+data < @endLower,
- dateline+data - (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat) - INTERVAL 1 DAY) + 1),
+ @end < @endLower,
+ @end - (UNIX_TIMESTAMP(FROM_UNIXTIME(@end, $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)
+ - ( TO_DAYS(FROM_UNIXTIME(@end, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start, '%y-%m-%d'))
+ - 2
+ + IF(@start <= @startUpper, 1, 0)
+ + IF(@end >= @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)),
+ @leftBound := IF(@start <= @startUpper, @startUpper, UNIX_TIMESTAMP(FROM_UNIXTIME(@start, $upperFormat) + INTERVAL 1 DAY))
+ < @rightBound := IF(@end >= @endLower, @endLower, UNIX_TIMESTAMP(FROM_UNIXTIME(@end, $lowerFormat) - INTERVAL 1 DAY)),
IF(
@timeDiff := (
(date_format(from_unixtime(@leftBound), '%H') -
@@ -133,18 +149,19 @@ class Queries
),
0
)
-
) as 'length',
dateline,
data,
machineuuid
from statistic
- where dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) and typeid = '$typeid'
+ where dateline+data >= $from and dateline <= $to and typeid = '$typeid'
and (
- (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat)) <= dateline and dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)))
+ (@daysDiff := (TO_DAYS(FROM_UNIXTIME(dateline+data, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(dateline, '%y-%m-%d'))) = 0 and (dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)) and dateline+data >= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat))))
or
- (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)) <= dateline+data and dateline+data <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat)))
+ (@daysDiff = 1 and (dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)) or dateline+data >= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat))))
+ or
+ @daysDiff >= 2
)
";
return "(".$queryString.")";
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index 82c03acc..aa373f54 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -24,7 +24,10 @@ class Page_Statistics_Reporting extends Page
{
// timespan you want to see = Days selected * seconds per Day (86400)
// default = 14 days
- GetData::$cutOff = Request::get('cutoff', 14, 'int') - 1;
+ $date = new DateTime(date('Y-m-d 00:00:00'));
+ $date->sub(new DateInterval('P'.(Request::get('cutoff', 14, 'int') - 1).'D'));
+ GetData::$from = $date->getTimestamp();
+ GetData::$to = time();
GetData::$lowerTimeBound = Request::get('lower', 0, 'int');
GetData::$upperTimeBound = Request::get('upper', 24, 'int');
--
cgit v1.2.3-55-g7522
From 3df046508f58bbfff51c5cad33f42af4e378bea7 Mon Sep 17 00:00:00 2001
From: Udo Walter
Date: Fri, 13 Jan 2017 16:19:33 +0100
Subject: [statistics_reporting] improved anonymization
---
.../statistics_reporting/inc/getdata.inc.php | 50 +++++++++++++++-------
.../statistics_reporting/page.inc.php | 5 +--
2 files changed, 36 insertions(+), 19 deletions(-)
diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php
index 8628d9db..880256d6 100644
--- a/modules-available/statistics_reporting/inc/getdata.inc.php
+++ b/modules-available/statistics_reporting/inc/getdata.inc.php
@@ -13,12 +13,18 @@ class GetData
// total time online, average time online, total number of logins
$res = Queries::getOverallStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
$row = $res->fetch(PDO::FETCH_ASSOC);
- $data = array('time' => self::formatSeconds($row['sum']), 'medianTime' => self::formatSeconds(self::calcMedian($row['median'])), 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']);
+ $data = array('time' => $row['sum'], 'medianTime' => self::calcMedian($row['median']), 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']);
//total time offline
$res = Queries::getTotalOfflineStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
$row = $res->fetch(PDO::FETCH_ASSOC);
- $data = array_merge($data, array('totalOfftime' => self::formatSeconds($row['timeOff'])));
+ $data = array_merge($data, array('totalOfftime' => $row['timeOff']));
+
+ if (!$anonymize) {
+ $data["time"] = self::formatSeconds($data["time"]);
+ $data["medianTime"] = self::formatSeconds($data["time"]);
+ $data["totalOfftime"] = self::formatSeconds($data["time"]);
+ }
return $data;
}
@@ -27,11 +33,19 @@ class GetData
public static function perLocation($anonymize = false) {
$res = Queries::getLocationStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
$data = array();
- $loc = $anonymize ? 'locHash' : 'locName';
- while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
- $median = self::calcMedian(self::calcMedian($row['medianTime']));
- $data[] = array('location' => $row[$loc], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'],
- 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']);
+ if (!$anonymize) {
+ while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+ $median = self::calcMedian(self::calcMedian($row['medianTime']));
+ $data[] = array('location' => $row['locName'], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'],
+ 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']),
+ 'offlineTimeInSeconds' => $row['offlineSum'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']);
+ }
+ } else {
+ while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+ $median = self::calcMedian(self::calcMedian($row['medianTime']));
+ $data[] = array('location' => $row['locHash'], 'time' => $row['timeSum'], 'medianTime' => $median, 'offTime' => $row['offlineSum'],
+ 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']);
+ }
}
return $data;
}
@@ -40,13 +54,19 @@ class GetData
public static function perClient($anonymize = false) {
$res = Queries::getClientStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
$data = array();
- $name = $anonymize ? 'clientHash' : 'clientName';
- $loc = $anonymize ? 'locHash' : 'locName';
- while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
- $median = self::calcMedian(self::calcMedian($row['medianTime']));
- $data[] = array('hostname' => $row[$name], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'],
- 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'lastStart' => date(DATE_RSS,$row['lastStart']), 'lastStartUnixtime' => $row['lastStart'],
- 'lastLogout' => date(DATE_RSS,$row['lastLogout']), 'lastLogoutUnixtime' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row[$loc]);
+ if (!$anonymize) {
+ while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+ $median = self::calcMedian(self::calcMedian($row['medianTime']));
+ $data[] = array('hostname' => $row['clientName'], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'],
+ 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'lastStart' => date(DATE_RSS, $row['lastStart']), 'lastStartUnixtime' => $row['lastStart'],
+ 'lastLogout' => date(DATE_RSS, $row['lastLogout']), 'lastLogoutUnixtime' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row['locName']);
+ }
+ } else {
+ while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+ $median = self::calcMedian(self::calcMedian($row['medianTime']));
+ $data[] = array('hostname' => $row['clientHash'], 'time' => $row['timeSum'], 'medianTime' => $median, 'offTime' => $row['offlineSum'], 'lastStart' => $row['lastStart'],
+ 'lastLogout' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row['locHash']);
+ }
}
return $data;
}
@@ -57,7 +77,7 @@ class GetData
$data = array();
$user = $anonymize ? 'userHash' : 'name';
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
- $data[] = array('user' => $row['name'], 'sessions' => $row['count']);
+ $data[] = array('user' => $row[$user], 'sessions' => $row['count']);
}
return $data;
}
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index aa373f54..941bf12f 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -24,9 +24,7 @@ class Page_Statistics_Reporting extends Page
{
// timespan you want to see = Days selected * seconds per Day (86400)
// default = 14 days
- $date = new DateTime(date('Y-m-d 00:00:00'));
- $date->sub(new DateInterval('P'.(Request::get('cutoff', 14, 'int') - 1).'D'));
- GetData::$from = $date->getTimestamp();
+ GetData::$from = strtotime("- ".(Request::get('cutoff', 14, 'int') - 1)." days 00:00:00");
GetData::$to = time();
GetData::$lowerTimeBound = Request::get('lower', 0, 'int');
GetData::$upperTimeBound = Request::get('upper', 24, 'int');
@@ -37,7 +35,6 @@ class Page_Statistics_Reporting extends Page
$data['perUser'] = GetData::perUser();
$data['perVM'] = GetData::perVM();
-
Render::addTemplate('columnChooser');
Render::addTemplate('_page', $data);
}
--
cgit v1.2.3-55-g7522
From 9cd078d34d5f3172e8e770fce3c3ec7d6445a460 Mon Sep 17 00:00:00 2001
From: Udo Walter
Date: Mon, 16 Jan 2017 18:59:46 +0100
Subject: [statistics_reporting] added ui to disable the reporting
---
.../lang/de/template-tags.json | 4 +-
.../lang/en/template-tags.json | 4 +-
.../statistics_reporting/page.inc.php | 45 ++++++++++++++-------
modules-available/statistics_reporting/style.css | 4 ++
.../templates/columnChooser.html | 46 +++++++++++++++++++++-
5 files changed, 85 insertions(+), 18 deletions(-)
diff --git a/modules-available/statistics_reporting/lang/de/template-tags.json b/modules-available/statistics_reporting/lang/de/template-tags.json
index 7dcec880..73c39e1e 100644
--- a/modules-available/statistics_reporting/lang/de/template-tags.json
+++ b/modules-available/statistics_reporting/lang/de/template-tags.json
@@ -28,5 +28,7 @@
"lang_last30": "Letzten 30 Tage",
"lang_last90": "Letzten 90 Tage",
"lang_apply": "Anwenden",
- "lang_notassigned": "NICHT ZUGEWIESEN"
+ "lang_save": "Speichern",
+ "lang_notassigned": "NICHT ZUGEWIESEN",
+ "lang_reportingDescription": "Helfen Sie uns bwLehrpool, durch das wöchentliche, automatische Verschicken eines anonymisierten Statistikberichts, zu verbessern."
}
\ 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 366a10de..90d59b38 100644
--- a/modules-available/statistics_reporting/lang/en/template-tags.json
+++ b/modules-available/statistics_reporting/lang/en/template-tags.json
@@ -28,5 +28,7 @@
"lang_last30": "Last 30 days",
"lang_last90": "Last 90 days",
"lang_apply": "Apply",
- "lang_notassigned": "NOT ASSIGNED"
+ "lang_save": "Save",
+ "lang_notassigned": "NOT ASSIGNED",
+ "lang_reportingDescription": "Help us improve bwLehrpool by automatically sending an anonymized statistics report once per week."
}
\ 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 941bf12f..1428848a 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -4,6 +4,8 @@
class Page_Statistics_Reporting extends Page
{
+ private $action = false;
+
/**
* Called before any page rendering happens - early hook to check parameters etc.
*/
@@ -15,6 +17,8 @@ class Page_Statistics_Reporting extends Page
Message::addError('main.no-permission');
Util::redirect('?do=Main'); // does not return
}
+
+ $this->action = Request::any('action', 'show', 'string');
}
/**
@@ -22,20 +26,31 @@ class Page_Statistics_Reporting extends Page
*/
protected function doRender()
{
- // timespan you want to see = Days selected * seconds per Day (86400)
- // default = 14 days
- GetData::$from = strtotime("- ".(Request::get('cutoff', 14, 'int') - 1)." days 00:00:00");
- GetData::$to = time();
- GetData::$lowerTimeBound = Request::get('lower', 0, 'int');
- GetData::$upperTimeBound = Request::get('upper', 24, 'int');
-
- $data = array_merge(GetData::total(), array('perLocation' => array(), 'perClient' => array(), 'perUser' => array(), 'perVM' => array()));
- $data['perLocation'] = GetData::perLocation();
- $data['perClient'] = GetData::perClient();
- $data['perUser'] = GetData::perUser();
- $data['perVM'] = GetData::perVM();
-
- Render::addTemplate('columnChooser');
- Render::addTemplate('_page', $data);
+ if ($this->action === 'show') {
+ // timespan you want to see. default = last 7 days
+ GetData::$from = strtotime("- " . (Request::get('cutoff', 14, 'int') - 1) . " days 00:00:00");
+ GetData::$to = time();
+ GetData::$lowerTimeBound = Request::get('lower', 0, 'int');
+ GetData::$upperTimeBound = Request::get('upper', 24, 'int');
+
+ $data = array_merge(GetData::total(), array('perLocation' => array(), 'perClient' => array(), 'perUser' => array(), 'perVM' => array()));
+ $data['perLocation'] = GetData::perLocation();
+ $data['perClient'] = GetData::perClient();
+ $data['perUser'] = GetData::perUser();
+ $data['perVM'] = GetData::perVM();
+
+ Render::addTemplate('columnChooser');
+ Render::addTemplate('_page', $data);
+ }
+ }
+
+ protected function doAjax()
+ {
+ $this->action = Request::any('action', false, 'string');
+ if ($this->action === 'setReporting') {
+ Property::set("reportingStatus", Request::get('reporting', "on", 'string'));
+ } elseif ($this->action === 'getReporting') {
+ echo Property::get("reportingStatus", "on");
+ }
}
}
diff --git a/modules-available/statistics_reporting/style.css b/modules-available/statistics_reporting/style.css
index d7fa7a2f..4f314b14 100644
--- a/modules-available/statistics_reporting/style.css
+++ b/modules-available/statistics_reporting/style.css
@@ -7,6 +7,10 @@
margin-bottom: 10px;
}
+.top-row #button-settings {
+ margin-right: 0;
+}
+
.buttonbar button {
margin-bottom: 4px;
}
diff --git a/modules-available/statistics_reporting/templates/columnChooser.html b/modules-available/statistics_reporting/templates/columnChooser.html
index 09551fc1..47fef2a5 100644
--- a/modules-available/statistics_reporting/templates/columnChooser.html
+++ b/modules-available/statistics_reporting/templates/columnChooser.html
@@ -22,6 +22,7 @@
+
@@ -37,6 +38,30 @@
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
\ No newline at end of file
--
cgit v1.2.3-55-g7522
From 4badbadf0131bf6b4b62d8e337ff6020e66b35ff Mon Sep 17 00:00:00 2001
From: Christian Hofmaier
Date: Tue, 17 Jan 2017 13:04:13 +0100
Subject: [statistics_reporting] added cronjob for weekly report
---
config.php.example | 2 ++
.../statistics_reporting/hooks/cron.inc.php | 34 +++++++++++++++++++++-
2 files changed, 35 insertions(+), 1 deletion(-)
diff --git a/config.php.example b/config.php.example
index 82fd1b77..41949fc8 100644
--- a/config.php.example
+++ b/config.php.example
@@ -30,6 +30,8 @@ define('CONFIG_PROXY_CONF', '/opt/openslx/proxy/config');
define('CONFIG_DOZMOD_URL', 'http://127.0.0.1:9080');
define('CONFIG_DOZMOD_EXPIRE', 60);
+define('CONFIG_REPORTING_URL', 'https://bwlp-masterserver.ruf.uni-freiburg.de/rpc/');
+
// Sort order for menu
// Optional - if missing, will be sorted by module id (internal name)
// Here it is also possible to assign a module to a different category,
diff --git a/modules-available/statistics_reporting/hooks/cron.inc.php b/modules-available/statistics_reporting/hooks/cron.inc.php
index b79883df..57e64a84 100644
--- a/modules-available/statistics_reporting/hooks/cron.inc.php
+++ b/modules-available/statistics_reporting/hooks/cron.inc.php
@@ -1,3 +1,35 @@
array(), 'perClient' => array(), 'perUser' => array(), 'perVM' => array()));
+ $data['perLocation'] = GetData::perLocation(true);
+ $data['perClient'] = GetData::perClient(true);
+ $data['perUser'] = GetData::perUser(true);
+ $data['perVM'] = GetData::perVM();
+
+
+ $statisticsReport = json_encode($data);
+
+ $url = CONFIG_REPORTING_URL;
+
+ $curl = curl_init($url);
+ curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
+ curl_setopt($curl, CURLOPT_HTTPHEADER, array("Content-type: application/json"));
+ curl_setopt($curl, CURLOPT_POST, true);
+ curl_setopt($curl, CURLOPT_POSTFIELDS, $statisticsReport);
+
+ $json_response = curl_exec($curl);
+
+ curl_close($curl);
+}
--
cgit v1.2.3-55-g7522
From 4910fa0b9f5ca6b6063965cc516ced8d72f8be5b Mon Sep 17 00:00:00 2001
From: Udo Walter
Date: Tue, 17 Jan 2017 13:47:24 +0100
Subject: [statistics_reporting] added vm name anonymization
---
modules-available/statistics_reporting/hooks/cron.inc.php | 2 +-
modules-available/statistics_reporting/inc/getdata.inc.php | 5 +++--
modules-available/statistics_reporting/inc/queries.inc.php | 2 +-
3 files changed, 5 insertions(+), 4 deletions(-)
diff --git a/modules-available/statistics_reporting/hooks/cron.inc.php b/modules-available/statistics_reporting/hooks/cron.inc.php
index 57e64a84..9b32bc96 100644
--- a/modules-available/statistics_reporting/hooks/cron.inc.php
+++ b/modules-available/statistics_reporting/hooks/cron.inc.php
@@ -16,7 +16,7 @@ if ($nextReporting < $time && $allowReport) {
$data['perLocation'] = GetData::perLocation(true);
$data['perClient'] = GetData::perClient(true);
$data['perUser'] = GetData::perUser(true);
- $data['perVM'] = GetData::perVM();
+ $data['perVM'] = GetData::perVM(true);
$statisticsReport = json_encode($data);
diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php
index 880256d6..cfb63658 100644
--- a/modules-available/statistics_reporting/inc/getdata.inc.php
+++ b/modules-available/statistics_reporting/inc/getdata.inc.php
@@ -84,11 +84,12 @@ class GetData
// per vm
- public static function perVM() {
+ public static function perVM($anonymize = false) {
$res = Queries::getVMStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
$data = array();
+ $vm = $anonymize ? 'vmHash' : 'name';
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
- $data[] = array('vm' => $row['name'], 'sessions' => $row['count']);
+ $data[] = array('vm' => $row[$vm], 'sessions' => $row['count']);
}
return $data;
}
diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php
index 23f5fb03..219640a6 100644
--- a/modules-available/statistics_reporting/inc/queries.inc.php
+++ b/modules-available/statistics_reporting/inc/queries.inc.php
@@ -64,7 +64,7 @@ class Queries
// Virtual Machine Data: Name, Number of Usages
public static function getVMStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $res = Database::simpleQuery("SELECT data AS name, COUNT(*) AS 'count'
+ $res = Database::simpleQuery("SELECT data AS name, MD5(data) AS vmHash, COUNT(*) AS 'count'
FROM statistic
WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND (
(@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0
--
cgit v1.2.3-55-g7522
From 6e9d2635c9fe69923a4c30cb99ab6938bfbffe27 Mon Sep 17 00:00:00 2001
From: Udo Walter
Date: Tue, 17 Jan 2017 14:24:41 +0100
Subject: [statistics_reporting] improved anonymization
---
.../statistics_reporting/hooks/cron.inc.php | 19 ++++++++-----------
.../statistics_reporting/inc/getdata.inc.php | 2 +-
.../statistics_reporting/inc/queries.inc.php | 17 +++++++++--------
3 files changed, 18 insertions(+), 20 deletions(-)
diff --git a/modules-available/statistics_reporting/hooks/cron.inc.php b/modules-available/statistics_reporting/hooks/cron.inc.php
index 9b32bc96..bd427e64 100644
--- a/modules-available/statistics_reporting/hooks/cron.inc.php
+++ b/modules-available/statistics_reporting/hooks/cron.inc.php
@@ -9,8 +9,9 @@ if ($nextReporting < $time && $allowReport) {
Property::set("nextReporting", strtotime("Sunday 23:59:59"));
- GetData::$from = strtotime("Monday last week");
- GetData::$to = strtotime("Sunday last week 23:59:59");
+ GetData::$from = strtotime("last sunday - 6 days");
+ GetData::$to = strtotime("last sunday 23:59:59");
+ GetData::$salt = bin2hex(random_bytes(20));
$data = array_merge(GetData::total(true), array('perLocation' => array(), 'perClient' => array(), 'perUser' => array(), 'perVM' => array()));
$data['perLocation'] = GetData::perLocation(true);
@@ -21,15 +22,11 @@ if ($nextReporting < $time && $allowReport) {
$statisticsReport = json_encode($data);
- $url = CONFIG_REPORTING_URL;
+ $params = array("action" => "statistics", "data" => $statisticsReport);
- $curl = curl_init($url);
- curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
- curl_setopt($curl, CURLOPT_HTTPHEADER, array("Content-type: application/json"));
- curl_setopt($curl, CURLOPT_POST, true);
- curl_setopt($curl, CURLOPT_POSTFIELDS, $statisticsReport);
+ Download::asStringPost(CONFIG_REPORTING_URL, $params, 300, $code);
- $json_response = curl_exec($curl);
-
- curl_close($curl);
+ if ($code != 200) {
+ EventLog::warning("Statistics Reporting: ".$code);
+ }
}
diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php
index cfb63658..07a13a0c 100644
--- a/modules-available/statistics_reporting/inc/getdata.inc.php
+++ b/modules-available/statistics_reporting/inc/getdata.inc.php
@@ -6,7 +6,7 @@ class GetData
public static $to;
public static $lowerTimeBound = 0;
public static $upperTimeBound = 24;
-
+ public static $salt;
// total
public static function total($anonymize = false) {
diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php
index 219640a6..77deb4f8 100644
--- a/modules-available/statistics_reporting/inc/queries.inc.php
+++ b/modules-available/statistics_reporting/inc/queries.inc.php
@@ -7,7 +7,7 @@ class Queries
// Client Data: Name, Time Online, Median Time Online, Time Offline, last start, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized)
public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) {
$notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
- $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(locId) AS locHash, MD5(t1.uuid) AS clientHash FROM (
+ $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash FROM (
SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId'
FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
@@ -20,14 +20,15 @@ class Queries
INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
GROUP BY machine.machineuuid
) t2
- ON t1.uuid = t2.uuid");
+ ON t1.uuid = t2.uuid", array("salt" => GetData::$salt));
+
return $res;
}
// Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec
public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) {
$notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
- $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(locId) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM (
+ $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(CONCAT(locId, :salt)) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM (
SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
@@ -41,13 +42,13 @@ class Queries
LEFT JOIN location ON machine.locationid = location.locationid
GROUP BY location.locationname
) t2
- ON t1.locName = t2.locName");
+ ON t1.locName = t2.locName", array("salt" => GetData::$salt));
return $res;
}
// User Data: Name, Name(anonymized), Number of Logins
public static function getUserStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(username)) AS userHash, COUNT(*) AS 'count'
+ $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(CONCAT(username, :salt))) AS userHash, COUNT(*) AS 'count'
FROM statistic
WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND (
(@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0
@@ -58,13 +59,13 @@ class Queries
@daysDiff >= 2
)
GROUP BY username
- ORDER BY 2 DESC");
+ ORDER BY 2 DESC", array("salt" => GetData::$salt));
return $res;
}
// Virtual Machine Data: Name, Number of Usages
public static function getVMStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) {
- $res = Database::simpleQuery("SELECT data AS name, MD5(data) AS vmHash, COUNT(*) AS 'count'
+ $res = Database::simpleQuery("SELECT data AS name, MD5(CONCAT(data, :salt)) AS vmHash, COUNT(*) AS 'count'
FROM statistic
WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND (
(@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0
@@ -75,7 +76,7 @@ class Queries
@daysDiff >= 2
)
GROUP BY data
- ORDER BY 2 DESC");
+ ORDER BY 2 DESC", array("salt" => GetData::$salt));
return $res;
}
--
cgit v1.2.3-55-g7522
From a73053c6394bf2be930bbf05709d2b7628c628e5 Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Thu, 19 Jan 2017 13:08:52 +0100
Subject: [statistics_reporting] Delete unused api.inc.php
---
modules-available/statistics_reporting/api.inc.php | 7 -------
1 file changed, 7 deletions(-)
delete mode 100644 modules-available/statistics_reporting/api.inc.php
diff --git a/modules-available/statistics_reporting/api.inc.php b/modules-available/statistics_reporting/api.inc.php
deleted file mode 100644
index 0d84ebce..00000000
--- a/modules-available/statistics_reporting/api.inc.php
+++ /dev/null
@@ -1,7 +0,0 @@
- 'value',
- 'number' => 123,
- 'list' => array(1,2,3,4,5,6,'foo')
-));
--
cgit v1.2.3-55-g7522
From c76f3fd2ad3711327c4b9bc844ae927cb86416a0 Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Thu, 19 Jan 2017 13:09:15 +0100
Subject: [statistics_reporting] intdiv() is PHP7 only
---
modules-available/statistics_reporting/inc/getdata.inc.php | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php
index 07a13a0c..b0a3171f 100644
--- a/modules-available/statistics_reporting/inc/getdata.inc.php
+++ b/modules-available/statistics_reporting/inc/getdata.inc.php
@@ -99,7 +99,7 @@ class GetData
// Format $seconds into ".d .h .m .s" format (day, hour, minute, second)
private static function formatSeconds($seconds)
{
- return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s';
+ return sprintf('%dd, %02d:%02d:%02d', $seconds / (3600*24), ($seconds % (3600*24)) / 3600, ($seconds%3600) / 60, $seconds%60);
}
// Calculate Median
--
cgit v1.2.3-55-g7522
From b5fd09f393b08f09a72f2851dc7b24e683fc9cfc Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Thu, 19 Jan 2017 13:09:33 +0100
Subject: [statistics_reporting] Check if user is logged in in doAjax()
---
modules-available/statistics_reporting/page.inc.php | 14 +++++++++-----
1 file changed, 9 insertions(+), 5 deletions(-)
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index 1428848a..27d478dc 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -46,11 +46,15 @@ class Page_Statistics_Reporting extends Page
protected function doAjax()
{
- $this->action = Request::any('action', false, 'string');
- if ($this->action === 'setReporting') {
- Property::set("reportingStatus", Request::get('reporting', "on", 'string'));
- } elseif ($this->action === 'getReporting') {
- echo Property::get("reportingStatus", "on");
+ if (!User::isLoggedIn()) {
+ echo "No.";
+ } else {
+ $this->action = Request::any('action', false, 'string');
+ if ($this->action === 'setReporting') {
+ Property::set("reportingStatus", Request::get('reporting', "on", 'string'));
+ } elseif ($this->action === 'getReporting') {
+ echo Property::get("reportingStatus", "on");
+ }
}
}
}
--
cgit v1.2.3-55-g7522
From 235fdf085a9f927e6ddc2addb55bb250ccfb26e6 Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Thu, 19 Jan 2017 13:15:04 +0100
Subject: [statistics_reporting] config.json: Depends on statistics and
locations
---
modules-available/statistics_reporting/config.json | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/modules-available/statistics_reporting/config.json b/modules-available/statistics_reporting/config.json
index 1a9c7fe7..f9627cdb 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", "js_jqueryui" ]
+ "dependencies": [ "statistics", "locations", "js_stupidtable", "js_jqueryui" ]
}
--
cgit v1.2.3-55-g7522
From 2963be53876e57c0d403ffe5dfa978d7736a0c4b Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Thu, 19 Jan 2017 13:18:40 +0100
Subject: [statistics_reporting] i18n: Move tags used in code to separate file,
delete unused tags
---
.../statistics_reporting/inc/queries.inc.php | 4 +-
.../statistics_reporting/lang/de/module.json | 1 +
.../lang/de/template-tags.json | 44 ++++++++++------------
.../statistics_reporting/lang/en/module.json | 1 +
.../lang/en/template-tags.json | 44 ++++++++++------------
5 files changed, 42 insertions(+), 52 deletions(-)
diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php
index 77deb4f8..2b2ebb13 100644
--- a/modules-available/statistics_reporting/inc/queries.inc.php
+++ b/modules-available/statistics_reporting/inc/queries.inc.php
@@ -6,7 +6,7 @@ class Queries
// Client Data: Name, Time Online, Median Time Online, Time Offline, last start, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized)
public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) {
- $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
+ $notassigned = Dictionary::translate('notAssigned', true);
$res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash FROM (
SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId'
FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable
@@ -27,7 +27,7 @@ class Queries
// Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec
public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) {
- $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned');
+ $notassigned = Dictionary::translate('notAssigned', true);
$res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(CONCAT(locId, :salt)) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM (
SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable
diff --git a/modules-available/statistics_reporting/lang/de/module.json b/modules-available/statistics_reporting/lang/de/module.json
index bdbfe4fd..86ed5f45 100644
--- a/modules-available/statistics_reporting/lang/de/module.json
+++ b/modules-available/statistics_reporting/lang/de/module.json
@@ -1,4 +1,5 @@
{
"module_name": "Statistik Auswertung",
+ "notAssigned": "Nicht zugewiesen",
"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 73c39e1e..d4d7887a 100644
--- a/modules-available/statistics_reporting/lang/de/template-tags.json
+++ b/modules-available/statistics_reporting/lang/de/template-tags.json
@@ -1,34 +1,28 @@
{
- "lang_user": "Nutzer",
- "lang_countLogins": "Anzahl Logins",
- "lang_client": "Client",
- "lang_vm": "VM",
- "lang_sessions": "Sitzungen",
- "lang_longSessions": "Sitzungen \u2265 60s",
- "lang_shortSessions": "Sitzungen < 60s",
- "lang_overallOfftime": "Insgesamte Zeit Offline",
- "lang_totalOffTime": "Gesamtzeit Offline",
+ "lang_apply": "Anwenden",
"lang_clientLogout": "Letzter VM Logout",
"lang_clientStart": "Letzter Client Boot",
- "lang_room": "Raum",
- "lang_offTime": "Zeit seit Logout",
- "lang_total": "Gesamt",
- "lang_perlocation": "Pro Raum",
- "lang_perclient": "Pro Client",
- "lang_peruser": "Pro Benutzer",
- "lang_pervm": "Pro VM",
"lang_hostname": "Client Name",
- "lang_location": "Raum",
- "lang_medianSessionLength": "Mittlere Sitzungsdauer",
- "lang_totalTime": "Gesamte Zeit",
"lang_last1": "Heute",
- "lang_last2": "Letzten 2 Tage",
- "lang_last7": "Letzten 7 Tage",
"lang_last14": "Letzten 14 Tage",
+ "lang_last2": "Letzten 2 Tage",
"lang_last30": "Letzten 30 Tage",
+ "lang_last7": "Letzten 7 Tage",
"lang_last90": "Letzten 90 Tage",
- "lang_apply": "Anwenden",
- "lang_save": "Speichern",
- "lang_notassigned": "NICHT ZUGEWIESEN",
- "lang_reportingDescription": "Helfen Sie uns bwLehrpool, durch das wöchentliche, automatische Verschicken eines anonymisierten Statistikberichts, zu verbessern."
+ "lang_location": "Raum",
+ "lang_longSessions": "Sitzungen \u2265 60s",
+ "lang_medianSessionLength": "Mittlere Sitzungsdauer",
+ "lang_overallOfftime": "Insgesamte Zeit Offline",
+ "lang_perclient": "Pro Client",
+ "lang_perlocation": "Pro Raum",
+ "lang_peruser": "Pro Benutzer",
+ "lang_pervm": "Pro VM",
+ "lang_reportingDescription": "Helfen Sie uns bwLehrpool, durch das w\u00f6chentliche, automatische Verschicken eines anonymisierten Statistikberichts, zu verbessern.",
+ "lang_sessions": "Sitzungen",
+ "lang_shortSessions": "Sitzungen < 60s",
+ "lang_total": "Gesamt",
+ "lang_totalOffTime": "Gesamtzeit Offline",
+ "lang_totalTime": "Gesamte Zeit",
+ "lang_user": "Nutzer",
+ "lang_vm": "VM"
}
\ 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 6b3f98f6..5362fee2 100644
--- a/modules-available/statistics_reporting/lang/en/module.json
+++ b/modules-available/statistics_reporting/lang/en/module.json
@@ -1,4 +1,5 @@
{
"module_name": "Statistics Reporting",
+ "notAssigned": "Not assigned",
"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 90d59b38..0047c7a4 100644
--- a/modules-available/statistics_reporting/lang/en/template-tags.json
+++ b/modules-available/statistics_reporting/lang/en/template-tags.json
@@ -1,34 +1,28 @@
{
- "lang_user": "User",
- "lang_countLogins": "Number of Logins",
- "lang_client": "Client",
- "lang_vm": "VM",
- "lang_sessions": "Sessions",
- "lang_longSessions": "Sessions \u2265 60s",
- "lang_shortSessions": "Sessions < 60s",
- "lang_overallOfftime": "Overall time offline",
- "lang_totalOffTime": "Total Time Offline",
+ "lang_apply": "Apply",
"lang_clientLogout": "Last VM Logout",
"lang_clientStart": "Last Client Boot",
- "lang_room": "Room",
- "lang_offTime": "Time since offline",
- "lang_total": "Total",
- "lang_perlocation": "Per Location",
- "lang_perclient": "Per Client",
- "lang_peruser": "Per User",
- "lang_pervm": "Per VM",
"lang_hostname": "Client Name",
- "lang_location": "Location",
- "lang_medianSessionLength": "Median Session Length",
- "lang_totalTime": "Total Time",
"lang_last1": "Today",
- "lang_last2": "Last 2 days",
- "lang_last7": "Last 7 days",
"lang_last14": "Last 14 days",
+ "lang_last2": "Last 2 days",
"lang_last30": "Last 30 days",
+ "lang_last7": "Last 7 days",
"lang_last90": "Last 90 days",
- "lang_apply": "Apply",
- "lang_save": "Save",
- "lang_notassigned": "NOT ASSIGNED",
- "lang_reportingDescription": "Help us improve bwLehrpool by automatically sending an anonymized statistics report once per week."
+ "lang_location": "Location",
+ "lang_longSessions": "Sessions \u2265 60s",
+ "lang_medianSessionLength": "Median Session Length",
+ "lang_overallOfftime": "Overall time offline",
+ "lang_perclient": "Per Client",
+ "lang_perlocation": "Per Location",
+ "lang_peruser": "Per User",
+ "lang_pervm": "Per VM",
+ "lang_reportingDescription": "Help us improve bwLehrpool by automatically sending an anonymized statistics report once per week.",
+ "lang_sessions": "Sessions",
+ "lang_shortSessions": "Sessions < 60s",
+ "lang_total": "Total",
+ "lang_totalOffTime": "Total Time Offline",
+ "lang_totalTime": "Total Time",
+ "lang_user": "User",
+ "lang_vm": "VM"
}
\ No newline at end of file
--
cgit v1.2.3-55-g7522
From 886e13e1af47ba6488ba3c5146d96e48e08403ad Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Thu, 19 Jan 2017 16:03:06 +0100
Subject: [statistics_reporting] Overhaul remote reporting structure; default
to off
---
inc/util.inc.php | 52 ++++++++++++++
.../statistics_reporting/hooks/cron.inc.php | 37 ++++------
.../statistics_reporting/inc/remotereport.inc.php | 84 ++++++++++++++++++++++
.../statistics_reporting/page.inc.php | 22 +++---
.../templates/columnChooser.html | 4 +-
5 files changed, 166 insertions(+), 33 deletions(-)
create mode 100644 modules-available/statistics_reporting/inc/remotereport.inc.php
diff --git a/inc/util.inc.php b/inc/util.inc.php
index 671028ed..d454d18d 100644
--- a/inc/util.inc.php
+++ b/inc/util.inc.php
@@ -365,4 +365,56 @@ SADFACE;
exit(0);
}
+ /**
+ * Return a binary string of given length, containing
+ * random bytes. If $secure is given, only methods of
+ * obtaining cryptographically strong random bytes will
+ * be used, otherwise, weaker methods might be used.
+ *
+ * @param int $length number of bytes to return
+ * @param bool $secure true = only use strong random sources
+ * @return string|bool string of requested length, false on error
+ */
+ public static function randomBytes($length, $secure)
+ {
+ if (function_exists('random_bytes')) {
+ return random_bytes($length);
+ }
+ if ($secure) {
+ if (function_exists('openssl_random_pseudo_bytes')) {
+ $bytes = openssl_random_pseudo_bytes($length, $ok);
+ if ($bytes !== false && $ok) {
+ return $bytes;
+ }
+ }
+ $file = '/dev/random';
+ } else {
+ $file = '/dev/urandom';
+ }
+ $fh = @fopen($file, 'r');
+ if ($fh !== false) {
+ $bytes = fread($fh, $length);
+ while ($bytes !== false && strlen($bytes) < $length) {
+ $new = fread($fh, $length - strlen($bytes));
+ if ($new === false) {
+ $bytes = false;
+ break;
+ }
+ $bytes .= $new;
+ }
+ fclose($fh);
+ if ($bytes !== false) {
+ return $bytes;
+ }
+ }
+ if ($secure) {
+ return false;
+ }
+ $bytes = '';
+ while ($length > 0) {
+ $bytes .= chr(mt_rand(0, 255));
+ }
+ return $bytes;
+ }
+
}
diff --git a/modules-available/statistics_reporting/hooks/cron.inc.php b/modules-available/statistics_reporting/hooks/cron.inc.php
index bd427e64..a48f74c2 100644
--- a/modules-available/statistics_reporting/hooks/cron.inc.php
+++ b/modules-available/statistics_reporting/hooks/cron.inc.php
@@ -1,32 +1,23 @@
"statistics", "data" => $statisticsReport);
- $data = array_merge(GetData::total(true), array('perLocation' => array(), 'perClient' => array(), 'perUser' => array(), 'perVM' => array()));
- $data['perLocation'] = GetData::perLocation(true);
- $data['perClient'] = GetData::perClient(true);
- $data['perUser'] = GetData::perUser(true);
- $data['perVM'] = GetData::perVM(true);
+ $result = Download::asStringPost(CONFIG_REPORTING_URL, $params, 30, $code);
-
- $statisticsReport = json_encode($data);
-
- $params = array("action" => "statistics", "data" => $statisticsReport);
-
- Download::asStringPost(CONFIG_REPORTING_URL, $params, 300, $code);
-
- if ($code != 200) {
- EventLog::warning("Statistics Reporting: ".$code);
+ if ($code != 200) {
+ EventLog::warning("Statistics Reporting failed: " . $code, $result);
+ }
}
-}
+}
\ No newline at end of file
diff --git a/modules-available/statistics_reporting/inc/remotereport.inc.php b/modules-available/statistics_reporting/inc/remotereport.inc.php
new file mode 100644
index 00000000..0bf4e7e2
--- /dev/null
+++ b/modules-available/statistics_reporting/inc/remotereport.inc.php
@@ -0,0 +1,84 @@
+action = Request::any('action', false, 'string');
- if ($this->action === 'setReporting') {
- Property::set("reportingStatus", Request::get('reporting', "on", 'string'));
- } elseif ($this->action === 'getReporting') {
- echo Property::get("reportingStatus", "on");
+ $this->action = Request::any('action', false, 'string');
+ if ($this->action === 'setReporting') {
+ if (!User::isLoggedIn()) {
+ die("No.");
+ }
+ $state = Request::post('reporting', false, 'string');
+ if ($state === false) {
+ die('Missing setting value.');
}
+ RemoteReport::setReportingEnabled($state);
+ } elseif ($this->action === 'getReporting') {
+ echo RemoteReport::isReportingEnabled() ? 'on' : '';
+ } else {
+ echo 'Invalid action.';
}
}
+
}
diff --git a/modules-available/statistics_reporting/templates/columnChooser.html b/modules-available/statistics_reporting/templates/columnChooser.html
index 47fef2a5..513bc36b 100644
--- a/modules-available/statistics_reporting/templates/columnChooser.html
+++ b/modules-available/statistics_reporting/templates/columnChooser.html
@@ -189,8 +189,8 @@
function saveSettings() {
$.ajax({
url: '?do=statistics_reporting',
- type: 'GET',
- data: { action: "setReporting", reporting: $("#checkbox-reporting").is(":checked") ? "on" : "off" }
+ type: 'POST',
+ data: { action: "setReporting", reporting: $("#checkbox-reporting").is(":checked") ? "on" : "off", token: TOKEN }
});
}
\ No newline at end of file
--
cgit v1.2.3-55-g7522
From cb13c655c5394a3226fc355497af9907b1e84b83 Mon Sep 17 00:00:00 2001
From: Udo Walter
Date: Tue, 31 Jan 2017 12:32:21 +0100
Subject: [statistics_reporting] fixed total showing wrong median and offline
time
---
modules-available/statistics_reporting/inc/getdata.inc.php | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php
index b0a3171f..1a3c81dc 100644
--- a/modules-available/statistics_reporting/inc/getdata.inc.php
+++ b/modules-available/statistics_reporting/inc/getdata.inc.php
@@ -22,8 +22,8 @@ class GetData
if (!$anonymize) {
$data["time"] = self::formatSeconds($data["time"]);
- $data["medianTime"] = self::formatSeconds($data["time"]);
- $data["totalOfftime"] = self::formatSeconds($data["time"]);
+ $data["medianTime"] = self::formatSeconds($data["medianTime"]);
+ $data["totalOfftime"] = self::formatSeconds($data["totalOfftime"]);
}
return $data;
--
cgit v1.2.3-55-g7522
From 5c6b40b28ead1157260e4abc2e697a696f69520e Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Tue, 31 Jan 2017 15:45:28 +0100
Subject: [statistics_reporting] Make getdata functions options flag based in
preparation of data export
---
.../statistics_reporting/inc/getdata.inc.php | 87 ++++++++++++++--------
.../statistics_reporting/inc/remotereport.inc.php | 10 +--
.../statistics_reporting/page.inc.php | 10 +--
.../statistics_reporting/templates/_page.html | 24 +++---
4 files changed, 76 insertions(+), 55 deletions(-)
diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php
index 1a3c81dc..b19af966 100644
--- a/modules-available/statistics_reporting/inc/getdata.inc.php
+++ b/modules-available/statistics_reporting/inc/getdata.inc.php
@@ -1,5 +1,8 @@
fetch(PDO::FETCH_ASSOC);
@@ -20,59 +24,75 @@ class GetData
$row = $res->fetch(PDO::FETCH_ASSOC);
$data = array_merge($data, array('totalOfftime' => $row['timeOff']));
- if (!$anonymize) {
- $data["time"] = self::formatSeconds($data["time"]);
- $data["medianTime"] = self::formatSeconds($data["medianTime"]);
- $data["totalOfftime"] = self::formatSeconds($data["totalOfftime"]);
+ if ($printable) {
+ $data["time_s"] = self::formatSeconds($data["time"]);
+ $data["medianTime_s"] = self::formatSeconds($data["medianTime"]);
+ $data["totalOfftime_s"] = self::formatSeconds($data["totalOfftime"]);
}
return $data;
}
// per location
- public static function perLocation($anonymize = false) {
+ public static function perLocation($flags = 0) {
+ $anonymize = 0 !== ($flags & GETDATA_ANONYMOUS);
+ $printable = 0 !== ($flags & GETDATA_PRINTABLE);
$res = Queries::getLocationStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
$data = array();
- if (!$anonymize) {
- while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
- $median = self::calcMedian(self::calcMedian($row['medianTime']));
- $data[] = array('location' => $row['locName'], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'],
- 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']),
- 'offlineTimeInSeconds' => $row['offlineSum'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']);
- }
- } else {
- while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
- $median = self::calcMedian(self::calcMedian($row['medianTime']));
- $data[] = array('location' => $row['locHash'], 'time' => $row['timeSum'], 'medianTime' => $median, 'offTime' => $row['offlineSum'],
- 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']);
+ while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+ $median = self::calcMedian(self::calcMedian($row['medianTime']));
+ $entry = array(
+ 'location' => ($anonymize ? $row['locHash'] : $row['locName']),
+ 'time' => $row['timeSum'],
+ 'medianTime' => $median,
+ 'offTime' => $row['offlineSum'],
+ 'sessions' => $row['longSessions'],
+ 'shortSessions' => $row['shortSessions']
+ );
+ if ($printable) {
+ $entry['time_s'] = self::formatSeconds($row['timeSum']);
+ $entry['medianTime_s'] = self::formatSeconds($median);
+ $entry['offTime_s'] = self::formatSeconds($row['offlineSum']);
}
+ $data[] = $entry;
}
return $data;
}
// per client
- public static function perClient($anonymize = false) {
+ public static function perClient($flags = 0) {
+ $anonymize = 0 !== ($flags & GETDATA_ANONYMOUS);
+ $printable = 0 !== ($flags & GETDATA_PRINTABLE);
$res = Queries::getClientStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
$data = array();
- if (!$anonymize) {
- while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
- $median = self::calcMedian(self::calcMedian($row['medianTime']));
- $data[] = array('hostname' => $row['clientName'], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'],
- 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'lastStart' => date(DATE_RSS, $row['lastStart']), 'lastStartUnixtime' => $row['lastStart'],
- 'lastLogout' => date(DATE_RSS, $row['lastLogout']), 'lastLogoutUnixtime' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row['locName']);
- }
- } else {
- while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
- $median = self::calcMedian(self::calcMedian($row['medianTime']));
- $data[] = array('hostname' => $row['clientHash'], 'time' => $row['timeSum'], 'medianTime' => $median, 'offTime' => $row['offlineSum'], 'lastStart' => $row['lastStart'],
- 'lastLogout' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row['locHash']);
+ while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+ $median = self::calcMedian(self::calcMedian($row['medianTime']));
+ $entry = array(
+ 'hostname' => ($anonymize ? $row['clientHash'] : $row['clientName']),
+ 'time' => $row['timeSum'],
+ 'medianTime' => $median,
+ 'offTime' => $row['offlineSum'],
+ 'lastStart' => $row['lastStart'],
+ 'lastLogout' => $row['lastLogout'],
+ 'sessions' => $row['longSessions'],
+ 'shortSessions' => $row['shortSessions'],
+ 'location' => ($anonymize ? $row['locHash'] : $row['locName']),
+ );
+ if ($printable) {
+ $entry['time_s'] = self::formatSeconds($row['timeSum']);
+ $entry['medianTime_s'] = self::formatSeconds($median);
+ $entry['offTime_s'] = self::formatSeconds($row['offlineSum']);
+ $entry['lastStart_s'] = date(DATE_RSS, $row['lastStart']);
+ $entry['lastLogout_s'] = date(DATE_RSS, $row['lastLogout']);
}
+ $data[] = $entry;
}
return $data;
}
// per user
- public static function perUser($anonymize = false) {
+ public static function perUser($flags = 0) {
+ $anonymize = 0 !== ($flags & GETDATA_ANONYMOUS);
$res = Queries::getUserStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
$data = array();
$user = $anonymize ? 'userHash' : 'name';
@@ -84,7 +104,8 @@ class GetData
// per vm
- public static function perVM($anonymize = false) {
+ public static function perVM($flags = 0) {
+ $anonymize = 0 !== ($flags & GETDATA_ANONYMOUS);
$res = Queries::getVMStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
$data = array();
$vm = $anonymize ? 'vmHash' : 'name';
diff --git a/modules-available/statistics_reporting/inc/remotereport.inc.php b/modules-available/statistics_reporting/inc/remotereport.inc.php
index 0bf4e7e2..e00e6758 100644
--- a/modules-available/statistics_reporting/inc/remotereport.inc.php
+++ b/modules-available/statistics_reporting/inc/remotereport.inc.php
@@ -71,11 +71,11 @@ class RemoteReport
GetData::$from = $from;
GetData::$to = $to;
GetData::$salt = bin2hex(Util::randomBytes(20));
- $data = GetData::total(true);
- $data['perLocation'] = GetData::perLocation(true);
- $data['perClient'] = GetData::perClient(true);
- $data['perUser'] = GetData::perUser(true);
- $data['perVM'] = GetData::perVM(true);
+ $data = GetData::total(GETDATA_ANONYMOUS);
+ $data['perLocation'] = GetData::perLocation(GETDATA_ANONYMOUS);
+ $data['perClient'] = GetData::perClient(GETDATA_ANONYMOUS);
+ $data['perUser'] = GetData::perUser(GETDATA_ANONYMOUS);
+ $data['perVM'] = GetData::perVM(GETDATA_ANONYMOUS);
$data['tsFrom'] = $from;
$data['tsTo'] = $to;
return $data;
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index 0309be68..052d813d 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -33,11 +33,11 @@ class Page_Statistics_Reporting extends Page
GetData::$lowerTimeBound = Request::get('lower', 0, 'int');
GetData::$upperTimeBound = Request::get('upper', 24, 'int');
- $data = array_merge(GetData::total(), array('perLocation' => array(), 'perClient' => array(), 'perUser' => array(), 'perVM' => array()));
- $data['perLocation'] = GetData::perLocation();
- $data['perClient'] = GetData::perClient();
- $data['perUser'] = GetData::perUser();
- $data['perVM'] = GetData::perVM();
+ $data = GetData::total(GETDATA_PRINTABLE);
+ $data['perLocation'] = GetData::perLocation(GETDATA_PRINTABLE);
+ $data['perClient'] = GetData::perClient(GETDATA_PRINTABLE);
+ $data['perUser'] = GetData::perUser(GETDATA_PRINTABLE);
+ $data['perVM'] = GetData::perVM(GETDATA_PRINTABLE);
Render::addTemplate('columnChooser');
Render::addTemplate('_page', $data);
diff --git a/modules-available/statistics_reporting/templates/_page.html b/modules-available/statistics_reporting/templates/_page.html
index 37c3cf90..39726d51 100644
--- a/modules-available/statistics_reporting/templates/_page.html
+++ b/modules-available/statistics_reporting/templates/_page.html
@@ -15,11 +15,11 @@
{{lang_total}} |
- {{time}} |
- {{medianTime}} |
+ {{time_s}} |
+ {{medianTime_s}} |
{{sessions}} |
{{shortSessions}} |
- {{totalOfftime}} |
+ {{totalOfftime_s}} |
@@ -42,11 +42,11 @@
{{#perLocation}}
{{location}} |
- {{time}} |
- {{medianTime}} |
+ {{time_s}} |
+ {{medianTime_s}} |
{{sessions}} |
{{shortSessions}} |
- {{offTime}} |
+ {{offTime_s}} |
{{/perLocation}}
@@ -73,14 +73,14 @@
{{#perClient}}
{{hostname}} |
- {{locationName}} |
- {{time}} |
- {{medianTime}} |
+ {{location}} |
+ {{time_s}} |
+ {{medianTime_s}} |
{{sessions}} |
{{shortSessions}} |
- {{offTime}} |
- {{lastLogout}} |
- {{lastStart}} |
+ {{offTime_s}} |
+ {{lastLogout_s}} |
+ {{lastStart_s}} |
{{/perClient}}
--
cgit v1.2.3-55-g7522
From 820f80d7321c9de822fbb8f57c6160bf2d29d1a8 Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Wed, 1 Feb 2017 11:28:54 +0100
Subject: [statistics_reporting] Make code match specification
---
modules-available/statistics_reporting/page.inc.php | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index 052d813d..81d44e15 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -28,7 +28,7 @@ class Page_Statistics_Reporting extends Page
{
if ($this->action === 'show') {
// timespan you want to see. default = last 7 days
- GetData::$from = strtotime("- " . (Request::get('cutoff', 14, 'int') - 1) . " days 00:00:00");
+ GetData::$from = strtotime("- " . (Request::get('cutoff', 7, 'int') - 1) . " days 00:00:00");
GetData::$to = time();
GetData::$lowerTimeBound = Request::get('lower', 0, 'int');
GetData::$upperTimeBound = Request::get('upper', 24, 'int');
--
cgit v1.2.3-55-g7522
From ac4dba11670f93289ca6318c3c06b49bf4ec0811 Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Wed, 1 Feb 2017 12:02:09 +0100
Subject: [statistics_reporting] Fix loadForm() and further js interaction if
sessionStorage is still empty
---
.../statistics_reporting/templates/columnChooser.html | 9 ++++++---
1 file changed, 6 insertions(+), 3 deletions(-)
diff --git a/modules-available/statistics_reporting/templates/columnChooser.html b/modules-available/statistics_reporting/templates/columnChooser.html
index 513bc36b..cfaf086c 100644
--- a/modules-available/statistics_reporting/templates/columnChooser.html
+++ b/modules-available/statistics_reporting/templates/columnChooser.html
@@ -159,9 +159,12 @@
var cutoff = getQueryVariable("cutoff");
$('#select-cutoff').val(cutoff ? cutoff : 7);
- sessionStorage.getItem("buttons").split(" ").forEach(function(button) {
- toggleButton(button.substr(7));
- });
+ var buttons = sessionStorage.getItem("buttons");
+ if (buttons) {
+ buttons.split(" ").forEach(function (button) {
+ toggleButton(button.substr(7));
+ });
+ }
}
function getQueryVariable(variable)
--
cgit v1.2.3-55-g7522
From 51ce2ee32af69eb7e18c716605ad060a21c0de01 Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Wed, 1 Feb 2017 13:45:51 +0100
Subject: [statistics_reporting] Fix sort arrow direction
---
modules-available/statistics_reporting/templates/columnChooser.html | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/modules-available/statistics_reporting/templates/columnChooser.html b/modules-available/statistics_reporting/templates/columnChooser.html
index cfaf086c..86903583 100644
--- a/modules-available/statistics_reporting/templates/columnChooser.html
+++ b/modules-available/statistics_reporting/templates/columnChooser.html
@@ -93,7 +93,7 @@
var th = $(this).find("th");
th.find(".arrow").remove();
var dir = $.fn.stupidtable.dir;
- var arrow = data.direction === dir.ASC ? "down" : "up";
+ var arrow = data.direction === dir.ASC ? "up" : "down";
th.eq(data.column).append('
');
});
--
cgit v1.2.3-55-g7522
From c53f811e83b6cf7124e2d5f26554276436f3b7cd Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Wed, 1 Feb 2017 15:58:19 +0100
Subject: [statistics_reporting] Show hand-cursor for sortable columns
---
modules-available/statistics_reporting/style.css | 6 +-
.../statistics_reporting/templates/_page.html | 238 ++++++++++-----------
.../templates/columnChooser.html | 3 +-
3 files changed, 112 insertions(+), 135 deletions(-)
diff --git a/modules-available/statistics_reporting/style.css b/modules-available/statistics_reporting/style.css
index 4f314b14..81dc74b0 100644
--- a/modules-available/statistics_reporting/style.css
+++ b/modules-available/statistics_reporting/style.css
@@ -37,6 +37,6 @@
line-height: 1.6em;
}
-#applybound {
- display: none;
-}
\ No newline at end of file
+th[data-sort] {
+ cursor: pointer;
+}
diff --git a/modules-available/statistics_reporting/templates/_page.html b/modules-available/statistics_reporting/templates/_page.html
index 39726d51..1f4ac52c 100644
--- a/modules-available/statistics_reporting/templates/_page.html
+++ b/modules-available/statistics_reporting/templates/_page.html
@@ -1,133 +1,111 @@
-
-
-
-
-
-
- |
- {{lang_totalTime}} |
- {{lang_medianSessionLength}} |
- {{lang_longSessions}} |
- {{lang_shortSessions}} |
- {{lang_overallOfftime}} |
-
-
-
-
- {{lang_total}} |
- {{time_s}} |
- {{medianTime_s}} |
- {{sessions}} |
- {{shortSessions}} |
- {{totalOfftime_s}} |
-
-
-
-
-
-
-
-
-
-
- {{lang_location}} |
- {{lang_totalTime}} |
- {{lang_medianSessionLength}} |
- {{lang_longSessions}} |
- {{lang_shortSessions}} |
- {{lang_totalOffTime}} |
-
-
-
- {{#perLocation}}
-
- {{location}} |
- {{time_s}} |
- {{medianTime_s}} |
- {{sessions}} |
- {{shortSessions}} |
- {{offTime_s}} |
-
- {{/perLocation}}
-
-
-
-
-
-
-
-
-
- {{lang_hostname}} |
- {{lang_location}} |
- {{lang_totalTime}} |
- {{lang_medianSessionLength}} |
- {{lang_longSessions}} |
- {{lang_shortSessions}} |
- {{lang_totalOffTime}} |
- {{lang_clientLogout}} |
- {{lang_clientStart}} |
-
-
-
- {{#perClient}}
-
- {{hostname}} |
- {{location}} |
- {{time_s}} |
- {{medianTime_s}} |
- {{sessions}} |
- {{shortSessions}} |
- {{offTime_s}} |
- {{lastLogout_s}} |
- {{lastStart_s}} |
-
- {{/perClient}}
-
-
-
-
-
-
-
-
-
- {{lang_user}} |
- {{lang_sessions}} |
-
-
-
- {{#perUser}}
-
- {{user}} |
- {{sessions}} |
-
- {{/perUser}}
-
-
-
-
-
-
-
-
-
- {{lang_vm}} |
- {{lang_sessions}} |
-
-
-
- {{#perVM}}
-
- {{vm}} |
- {{sessions}} |
-
- {{/perVM}}
-
-
-
-
-
+
+
+
+ |
+ {{lang_totalTime}} |
+ {{lang_medianSessionLength}} |
+ {{lang_longSessions}} |
+ {{lang_shortSessions}} |
+ {{lang_overallOfftime}} |
+
+
+
+
+ {{lang_total}} |
+ {{time_s}} |
+ {{medianTime_s}} |
+ {{sessions}} |
+ {{shortSessions}} |
+ {{totalOfftime_s}} |
+
+
+
+
+
+
+ {{lang_location}} |
+ {{lang_totalTime}} |
+ {{lang_medianSessionLength}} |
+ {{lang_longSessions}} |
+ {{lang_shortSessions}} |
+ {{lang_totalOffTime}} |
+
+
+
+ {{#perLocation}}
+
+ {{location}} |
+ {{time_s}} |
+ {{medianTime_s}} |
+ {{sessions}} |
+ {{shortSessions}} |
+ {{offTime_s}} |
+
+ {{/perLocation}}
+
+
+
+
+
+ {{lang_hostname}} |
+ {{lang_location}} |
+ {{lang_totalTime}} |
+ {{lang_medianSessionLength}} |
+ {{lang_longSessions}} |
+ {{lang_shortSessions}} |
+ {{lang_totalOffTime}} |
+ {{lang_clientLogout}} |
+ {{lang_clientStart}} |
+
+
+
+ {{#perClient}}
+
+ {{hostname}} |
+ {{location}} |
+ {{time_s}} |
+ {{medianTime_s}} |
+ {{sessions}} |
+ {{shortSessions}} |
+ {{offTime_s}} |
+ {{lastLogout_s}} |
+ {{lastStart_s}} |
+
+ {{/perClient}}
+
+
+
+
+
+ {{lang_user}} |
+ {{lang_sessions}} |
+
+
+
+ {{#perUser}}
+
+ {{user}} |
+ {{sessions}} |
+
+ {{/perUser}}
+
+
+
+
+
+ {{lang_vm}} |
+ {{lang_sessions}} |
+
+
+
+ {{#perVM}}
+
+ {{vm}} |
+ {{sessions}} |
+
+ {{/perVM}}
+
+
diff --git a/modules-available/statistics_reporting/templates/columnChooser.html b/modules-available/statistics_reporting/templates/columnChooser.html
index 86903583..c51250bd 100644
--- a/modules-available/statistics_reporting/templates/columnChooser.html
+++ b/modules-available/statistics_reporting/templates/columnChooser.html
@@ -21,7 +21,7 @@
-