From 8a7d2cdb4c8e1727474709706f4d9314cb82097a Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Tue, 22 Nov 2016 12:17:04 +0100 Subject: [statistics_reporting] new empty module --- modules-available/statistics_reporting/api.inc.php | 7 +++++ modules-available/statistics_reporting/config.json | 3 +++ .../statistics_reporting/lang/de/module.json | 4 +++ .../lang/de/template-tags.json | 3 +++ .../statistics_reporting/lang/en/module.json | 4 +++ .../lang/en/template-tags.json | 3 +++ .../lang/pt/template-tags.json | 3 +++ .../statistics_reporting/page.inc.php | 30 ++++++++++++++++++++++ .../statistics_reporting/templates/_page.html | 4 +++ 9 files changed, 61 insertions(+) create mode 100644 modules-available/statistics_reporting/api.inc.php create mode 100644 modules-available/statistics_reporting/config.json create mode 100644 modules-available/statistics_reporting/lang/de/module.json create mode 100644 modules-available/statistics_reporting/lang/de/template-tags.json create mode 100644 modules-available/statistics_reporting/lang/en/module.json create mode 100644 modules-available/statistics_reporting/lang/en/template-tags.json create mode 100644 modules-available/statistics_reporting/lang/pt/template-tags.json create mode 100644 modules-available/statistics_reporting/page.inc.php create mode 100644 modules-available/statistics_reporting/templates/_page.html diff --git a/modules-available/statistics_reporting/api.inc.php b/modules-available/statistics_reporting/api.inc.php new file mode 100644 index 00000000..0d84ebce --- /dev/null +++ b/modules-available/statistics_reporting/api.inc.php @@ -0,0 +1,7 @@ + 'value', + 'number' => 123, + 'list' => array(1,2,3,4,5,6,'foo') +)); diff --git a/modules-available/statistics_reporting/config.json b/modules-available/statistics_reporting/config.json new file mode 100644 index 00000000..706412d0 --- /dev/null +++ b/modules-available/statistics_reporting/config.json @@ -0,0 +1,3 @@ +{ + "category":"main.content" +} diff --git a/modules-available/statistics_reporting/lang/de/module.json b/modules-available/statistics_reporting/lang/de/module.json new file mode 100644 index 00000000..166909c3 --- /dev/null +++ b/modules-available/statistics_reporting/lang/de/module.json @@ -0,0 +1,4 @@ +{ + "module_name": "Mein erstes Modul", + "page_title": "Mein erster Seitentitel" +} \ 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 new file mode 100644 index 00000000..ce98ce38 --- /dev/null +++ b/modules-available/statistics_reporting/lang/de/template-tags.json @@ -0,0 +1,3 @@ +{ + "lang_hello": "Hallo" +} \ 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 new file mode 100644 index 00000000..b2bcbb0c --- /dev/null +++ b/modules-available/statistics_reporting/lang/en/module.json @@ -0,0 +1,4 @@ +{ + "module_name": "My first module", + "page_title": "My first page title" +} \ 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 new file mode 100644 index 00000000..c30739e5 --- /dev/null +++ b/modules-available/statistics_reporting/lang/en/template-tags.json @@ -0,0 +1,3 @@ +{ + "lang_hello": "Hello" +} \ No newline at end of file diff --git a/modules-available/statistics_reporting/lang/pt/template-tags.json b/modules-available/statistics_reporting/lang/pt/template-tags.json new file mode 100644 index 00000000..e7981844 --- /dev/null +++ b/modules-available/statistics_reporting/lang/pt/template-tags.json @@ -0,0 +1,3 @@ +{ + "lang_hello": "Olá" +} \ No newline at end of file diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php new file mode 100644 index 00000000..ff73107a --- /dev/null +++ b/modules-available/statistics_reporting/page.inc.php @@ -0,0 +1,30 @@ + 'bar', + 'now' => date('d.m.Y H:i:s') + )); + } + +} diff --git a/modules-available/statistics_reporting/templates/_page.html b/modules-available/statistics_reporting/templates/_page.html new file mode 100644 index 00000000..dfc941ae --- /dev/null +++ b/modules-available/statistics_reporting/templates/_page.html @@ -0,0 +1,4 @@ +
+

{{lang_hello}}, {{foo}}

+ ** {{now}} ** +
\ No newline at end of file -- cgit v1.2.3-55-g7522 From e10ad1d38de5fa13781c3af17a4de8fe85644a0e Mon Sep 17 00:00:00 2001 From: Christian Hofmaier Date: Mon, 28 Nov 2016 15:41:15 +0100 Subject: login count and offline time database queries --- .../statistics_reporting/lang/de/module.json | 4 +- .../lang/de/template-tags.json | 12 +- .../statistics_reporting/lang/en/module.json | 4 +- .../lang/en/template-tags.json | 12 +- .../statistics_reporting/page.inc.php | 103 ++++++++++++++- .../statistics_reporting/templates/_page.html | 142 ++++++++++++++++++++- 6 files changed, 262 insertions(+), 15 deletions(-) diff --git a/modules-available/statistics_reporting/lang/de/module.json b/modules-available/statistics_reporting/lang/de/module.json index 166909c3..bdbfe4fd 100644 --- a/modules-available/statistics_reporting/lang/de/module.json +++ b/modules-available/statistics_reporting/lang/de/module.json @@ -1,4 +1,4 @@ { - "module_name": "Mein erstes Modul", - "page_title": "Mein erster Seitentitel" + "module_name": "Statistik Auswertung", + "page_title": "Statistik Auswertung" } \ No newline at end of file diff --git a/modules-available/statistics_reporting/lang/de/template-tags.json b/modules-available/statistics_reporting/lang/de/template-tags.json index ce98ce38..d9b034b4 100644 --- a/modules-available/statistics_reporting/lang/de/template-tags.json +++ b/modules-available/statistics_reporting/lang/de/template-tags.json @@ -1,3 +1,13 @@ { - "lang_hello": "Hallo" + "lang_user": "Nutzer", + "lang_countLogins": "Anzahl Logins", + "lang_client": "Client", + "lang_vm": "VM", + "lang_totalLogins": "Gesamt Logins", + "lang_overallOfftime": "Gesamtzeit Offline", + "lang_totalOffTime": "Gesamtzeit Offline", + "lang_clientLogout": "Letzter Logout", + "lang_clientStart": "Letzter Client Start", + "lang_room": "Raum", + "lang_offTime": "Zeit seit Logout" } \ No newline at end of file diff --git a/modules-available/statistics_reporting/lang/en/module.json b/modules-available/statistics_reporting/lang/en/module.json index b2bcbb0c..6b3f98f6 100644 --- a/modules-available/statistics_reporting/lang/en/module.json +++ b/modules-available/statistics_reporting/lang/en/module.json @@ -1,4 +1,4 @@ { - "module_name": "My first module", - "page_title": "My first page title" + "module_name": "Statistics Reporting", + "page_title": "Statistics Reporting" } \ No newline at end of file diff --git a/modules-available/statistics_reporting/lang/en/template-tags.json b/modules-available/statistics_reporting/lang/en/template-tags.json index c30739e5..2f33eaf5 100644 --- a/modules-available/statistics_reporting/lang/en/template-tags.json +++ b/modules-available/statistics_reporting/lang/en/template-tags.json @@ -1,3 +1,13 @@ { - "lang_hello": "Hello" + "lang_user": "User", + "lang_countLogins": "Number of Logins", + "lang_client": "Client", + "lang_vm": "VM", + "lang_totalLogins": "Total Logins", + "lang_overallOfftime": "Overall time offline", + "lang_totalOffTime": "Time offline", + "lang_clientLogout": "Last Logout", + "lang_clientStart": "Last Client Start", + "lang_room": "Room", + "lang_offTime": "Time since offline" } \ No newline at end of file diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php index ff73107a..3bd83034 100644 --- a/modules-available/statistics_reporting/page.inc.php +++ b/modules-available/statistics_reporting/page.inc.php @@ -1,6 +1,6 @@ 'bar', - 'now' => date('d.m.Y H:i:s') - )); + //counting the total number of logins + $res = Database::simpleQuery("SELECT COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name'"); + $row = $res->fetch(PDO::FETCH_ASSOC); + $datax = array('totalLogins' => $row['count']); + + //counting logins per vm/event + $res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY data ORDER BY 2 DESC"); + $datax[] = array('vmLogins' => array()); + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $datax['vmLogins'][] = array('vmname' => $row['data'], 'numLogins' => $row['count']); + } + + //counting the logins per user + $res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY username ORDER BY 2 DESC"); + $datax[] = array('userLogins' => array()); + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $datax['userLogins'][] = array('username' => $row['username'], 'numLogins' => $row['count']); + } + + // TODO: got empty machine with alot of logins. isn't shown in the inner-join hostname query below. + // $res = Database::simpleQuery("SELECT machineuuid as 'hostname', COUNT(*) AS 'count' FROM statistic WHERE typeid='~session-length' GROUP BY machineuuid ORDER BY 2 DESC"); + + // TODO: session-length liefert 3000 Einträge mehr als vmchooser-session? + //counting the logins per client + $res = Database::simpleQuery("SELECT machine.hostname AS 'hostname', COUNT(*) AS 'count' FROM statistic + INNER JOIN machine ON statistic.machineuuid=machine.machineuuid + WHERE typeid='~session-length' GROUP BY machine.hostname ORDER BY 2 DESC"); + $datax[] = array('machineLogins' => array()); + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $datax['machineLogins'][] = array('client' => $row['hostname'], 'numLogins' => $row['count']); + } + + + + //total time offline overall + $res = Database::simpleQUery("SELECT sum(cast(data AS UNSIGNED)) AS totalOfftime FROM statistic WHERE typeid='~offline-length'"); + $row = $res->fetch(PDO::FETCH_ASSOC); + $datay= array('totalOfftime' => $row['totalOfftime']); + + //total offline time per client + $res = Database::simpleQuery("SELECT machine.hostname AS 'hostname', statistic.data as time FROM statistic + INNER JOIN machine ON statistic.machineuuid=machine.machineuuid + WHERE typeid='~offline-length' GROUP BY machine.hostname ORDER BY cast(time AS UNSIGNED) DESC"); + $datay[] = array('totalOfflineTimeClient' => array()); + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $datay['totalOfflineTimeClient'][] = array('client' => $row['hostname'], 'offTime' => $row['time']); + } + + // last logout of client + $res = Database::simpleQuery("SELECT machine.hostname AS 'hostname', max(statistic.dateline) as datetime, statistic.data as loginTime + FROM statistic INNER JOIN machine ON statistic.machineuuid=machine.machineuuid + WHERE typeid='~session-length' GROUP BY machine.hostname ORDER BY datetime DESC"); + $datay[] = array('lastLogout' => array()); + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $datay['lastLogout'][] = array('client' => $row['hostname'], 'lastlogout' => date(DATE_RSS,$row['datetime']+$row['loginTime']), 'howLongOff' => (time() - ($row['datetime']+$row['loginTime']))); + } + + // last start of client + $res = Database::simpleQuery("SELECT machine.hostname AS 'hostname', max(statistic.dateline) as datetime FROM statistic + INNER JOIN machine ON statistic.machineuuid=machine.machineuuid + WHERE typeid='~offline-length' GROUP BY machine.hostname ORDER BY datetime DESC"); + $datay[] = array('lastLogin' => array()); + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $datay['lastLogin'][] = array('client' => $row['hostname'], 'lastlogin' => date(DATE_RSS,$row['datetime'])); + } + + //total time offline per room + $res = Database::simpleQuery("SELECT location.locationname AS 'room', statistic.data as time FROM statistic + INNER JOIN machine ON statistic.machineuuid=machine.machineuuid + INNER JOIN location ON machine.locationid=location.locationid + WHERE typeid='~offline-length' GROUP BY room ORDER BY cast(time AS UNSIGNED) DESC"); + $datay[] = array('offTimeRoom' => array()); + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $datay['offTimeRoom'][] = array('room' => $row['room'], 'offTime' => $this->formatSeconds($row['time'])); + } + + $data = array_merge($datax, $datay); + Render::addTemplate('_page', $data); } + + + function formatSeconds($seconds) { + $seconds = $seconds * 1; + + $minutes = floor($seconds / 60); + $hours = floor($minutes / 60); + $days = floor($hours / 24); + + $seconds = $seconds % 60; + $minutes = $minutes % 60; + $hours = $hours % 24; + + $format = '%u:%u:%02u:%02u'; + $time = sprintf($format, $days, $hours, $minutes, $seconds); + return rtrim($time, '0'); + } + + } diff --git a/modules-available/statistics_reporting/templates/_page.html b/modules-available/statistics_reporting/templates/_page.html index dfc941ae..101a124e 100644 --- a/modules-available/statistics_reporting/templates/_page.html +++ b/modules-available/statistics_reporting/templates/_page.html @@ -1,4 +1,138 @@ -
-

{{lang_hello}}, {{foo}}

- ** {{now}} ** -
\ No newline at end of file +
+ + + + + + + +
{{lang_totalLogins}}
{{totalLogins}}
+
+ +
+ + + + + + + +
{{lang_overallOfftime}}
{{totalOfftime}}
+
+ + + +
+
+ + + + + + {{#totalOfflineTimeClient}} + + + + + {{/totalOfflineTimeClient }} +
{{lang_client}}{{lang_totalOffTime}}
{{client}}{{offTime}}
+
+ +
+ + + + + + + {{#lastLogout}} + + + + + + {{/lastLogout}} +
{{lang_client}}{{lang_clientLogout}}{{lang_offTime}}
{{client}}{{lastlogout}}{{howLongOff}}
+
+ +
+ + + + + + {{#lastLogin}} + + + + + {{/lastLogin}} +
{{lang_client}}{{lang_clientStart}}
{{client}}{{lastlogin}}
+
+ +
+ + + + + + {{#offTimeRoom}} + + + + + {{/offTimeRoom}} +
{{lang_room}}{{lang_totalOffTime}}
{{room}}{{offTime}}
+
+
+ + + + +
+
+ + + + + + {{#vmLogins}} + + + + + {{/vmLogins}} +
{{lang_vm}}{{lang_countLogins}}
{{vmname}}{{numLogins}}
+
+ +
+ + + + + + {{#machineLogins}} + + + + + {{/machineLogins}} +
{{lang_client}}{{lang_countLogins}}
{{client}}{{numLogins}}
+
+ +
+ + + + + + {{#userLogins}} + + + + + {{/userLogins}} +
{{lang_user}}{{lang_countLogins}}
{{username}}{{numLogins}}
+
+
+ -- cgit v1.2.3-55-g7522 From b87930137acf88936fb541f53ab4dab1697e4d03 Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Mon, 28 Nov 2016 18:27:32 +0100 Subject: new ui --- .../lang/de/template-tags.json | 11 +- .../lang/en/template-tags.json | 11 +- .../statistics_reporting/page.inc.php | 139 +++++------- modules-available/statistics_reporting/style.css | 3 + .../statistics_reporting/templates/_page.html | 247 ++++++++++----------- .../templates/columnChooser.html | 47 ++++ 6 files changed, 246 insertions(+), 212 deletions(-) create mode 100644 modules-available/statistics_reporting/style.css create mode 100644 modules-available/statistics_reporting/templates/columnChooser.html diff --git a/modules-available/statistics_reporting/lang/de/template-tags.json b/modules-available/statistics_reporting/lang/de/template-tags.json index d9b034b4..2a9688b2 100644 --- a/modules-available/statistics_reporting/lang/de/template-tags.json +++ b/modules-available/statistics_reporting/lang/de/template-tags.json @@ -9,5 +9,14 @@ "lang_clientLogout": "Letzter Logout", "lang_clientStart": "Letzter Client Start", "lang_room": "Raum", - "lang_offTime": "Zeit seit Logout" + "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_avgSessionLength": "Durchschnittliche Sitzungsdauer", + "lang_totalTime": "Gesamte Zeit" } \ 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 2f33eaf5..cfbf3ad8 100644 --- a/modules-available/statistics_reporting/lang/en/template-tags.json +++ b/modules-available/statistics_reporting/lang/en/template-tags.json @@ -9,5 +9,14 @@ "lang_clientLogout": "Last Logout", "lang_clientStart": "Last Client Start", "lang_room": "Room", - "lang_offTime": "Time since offline" + "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_avgSessionLength": "Average Session Length", + "lang_totalTime": "Total Time" } \ 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 3bd83034..6d3c2ae3 100644 --- a/modules-available/statistics_reporting/page.inc.php +++ b/modules-available/statistics_reporting/page.inc.php @@ -21,103 +21,82 @@ class Page_Statistics_Reporting extends Page */ protected function doRender() { - //counting the total number of logins - $res = Database::simpleQuery("SELECT COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name'"); - $row = $res->fetch(PDO::FETCH_ASSOC); - $datax = array('totalLogins' => $row['count']); - - //counting logins per vm/event - $res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY data ORDER BY 2 DESC"); - $datax[] = array('vmLogins' => array()); - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $datax['vmLogins'][] = array('vmname' => $row['data'], 'numLogins' => $row['count']); - } - - //counting the logins per user - $res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY username ORDER BY 2 DESC"); - $datax[] = array('userLogins' => array()); - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $datax['userLogins'][] = array('username' => $row['username'], 'numLogins' => $row['count']); - } - // TODO: got empty machine with alot of logins. isn't shown in the inner-join hostname query below. // $res = Database::simpleQuery("SELECT machineuuid as 'hostname', COUNT(*) AS 'count' FROM statistic WHERE typeid='~session-length' GROUP BY machineuuid ORDER BY 2 DESC"); // TODO: session-length liefert 3000 Einträge mehr als vmchooser-session? - //counting the logins per client - $res = Database::simpleQuery("SELECT machine.hostname AS 'hostname', COUNT(*) AS 'count' FROM statistic - INNER JOIN machine ON statistic.machineuuid=machine.machineuuid - WHERE typeid='~session-length' GROUP BY machine.hostname ORDER BY 2 DESC"); - $datax[] = array('machineLogins' => array()); - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $datax['machineLogins'][] = array('client' => $row['hostname'], 'numLogins' => $row['count']); - } - - - - //total time offline overall - $res = Database::simpleQUery("SELECT sum(cast(data AS UNSIGNED)) AS totalOfftime FROM statistic WHERE typeid='~offline-length'"); - $row = $res->fetch(PDO::FETCH_ASSOC); - $datay= array('totalOfftime' => $row['totalOfftime']); - //total offline time per client - $res = Database::simpleQuery("SELECT machine.hostname AS 'hostname', statistic.data as time FROM statistic - INNER JOIN machine ON statistic.machineuuid=machine.machineuuid - WHERE typeid='~offline-length' GROUP BY machine.hostname ORDER BY cast(time AS UNSIGNED) DESC"); - $datay[] = array('totalOfflineTimeClient' => array()); - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $datay['totalOfflineTimeClient'][] = array('client' => $row['hostname'], 'offTime' => $row['time']); + // TODO change AVG to median + + + // total time online, average time online, total number of logins + $res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)), AVG(CAST(data AS UNSIGNED)), COUNT(*) FROM statistic WHERE typeid = '~session-length'"); + $row = $res->fetch(PDO::FETCH_NUM); + $data = array('time' => $this->formatSeconds($row[0]), 'avgTime' => $this->formatSeconds($row[1]), 'totalLogins' => $row[2]); + //total time offline + $res = Database::simpleQUery("SELECT SUM(CAST(data AS UNSIGNED)) FROM statistic WHERE typeid='~offline-length'"); + $row = $res->fetch(PDO::FETCH_NUM); + $data = array_merge($data, array('totalOfftime' => $this->formatSeconds($row[0]))); + + // per location + $res = Database::simpleQuery("SELECT t1.ln, timeSum, avgTime, offlineSum, loginCount FROM ( + SELECT location.locationname AS 'ln', SUM(CAST(statistic.data AS UNSIGNED)) AS 'timeSum', AVG(CAST(statistic.data AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount' + FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid + INNER JOIN location ON machine.locationid = location.locationid + WHERE statistic.typeid = '~session-length' GROUP By location.locationname + ) t1 INNER JOIN ( + SELECT location.locationname AS 'ln', SUM(CAST(statistic.data AS UNSIGNED)) AS 'offlineSum' + FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid + INNER JOIN location ON machine.locationid = location.locationid + WHERE statistic.typeid = '~offline-length' GROUP By location.locationname + ) t2 ON t1.ln = t2.ln"); + $data[] = array('perLocation' => array()); + while ($row = $res->fetch(PDO::FETCH_NUM)) { + $data['perLocation'][] = array('location' => $row[0], 'time' => $this->formatSeconds($row[1]), 'timeInSeconds' => $row[1], + 'avgTime' => $this->formatSeconds($row[2]), 'avgTimeInSeconds' => $row[2], 'offTime' => $this->formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4]); } - // last logout of client - $res = Database::simpleQuery("SELECT machine.hostname AS 'hostname', max(statistic.dateline) as datetime, statistic.data as loginTime - FROM statistic INNER JOIN machine ON statistic.machineuuid=machine.machineuuid - WHERE typeid='~session-length' GROUP BY machine.hostname ORDER BY datetime DESC"); - $datay[] = array('lastLogout' => array()); - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $datay['lastLogout'][] = array('client' => $row['hostname'], 'lastlogout' => date(DATE_RSS,$row['datetime']+$row['loginTime']), 'howLongOff' => (time() - ($row['datetime']+$row['loginTime']))); + // per client + $res = Database::simpleQuery("SELECT t1.name, timeSum, avgTime, offlineSum, loginCount, lastLogout, lastStart FROM ( + SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(statistic.data AS UNSIGNED)) AS 'timeSum', AVG(CAST(statistic.data AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount', MAX(statistic.dateline) AS 'lastLogout' + FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid + WHERE typeid = '~session-length' GROUP BY machine.machineuuid + ) t1 INNER JOIN ( + SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(statistic.data AS UNSIGNED)) AS 'offlineSum', MAX(statistic.dateline) AS 'lastStart' + FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid + WHERE typeid = '~offline-length' GROUP BY machine.machineuuid + ) t2 ON t1.uuid = t2.uuid"); + $data[] = array('perClient' => array()); + while ($row = $res->fetch(PDO::FETCH_NUM)) { + $data['perClient'][] = array('hostname' => $row[0], 'time' => $this->formatSeconds($row[1]), 'timeInSeconds' => $row[1], + 'avgTime' => $this->formatSeconds($row[2]), 'avgTimeInSeconds' => $row[2], 'offTime' => $this->formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4], + 'lastLogout' => date(DATE_RSS,$row[5]), 'lastLogoutUnixtime' => $row[5], 'lastStart' => date(DATE_RSS,$row[6]), 'lastStartUnixtime' => $row[6]); } - // last start of client - $res = Database::simpleQuery("SELECT machine.hostname AS 'hostname', max(statistic.dateline) as datetime FROM statistic - INNER JOIN machine ON statistic.machineuuid=machine.machineuuid - WHERE typeid='~offline-length' GROUP BY machine.hostname ORDER BY datetime DESC"); - $datay[] = array('lastLogin' => array()); - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $datay['lastLogin'][] = array('client' => $row['hostname'], 'lastlogin' => date(DATE_RSS,$row['datetime'])); + // per user + $res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY username ORDER BY 2 DESC"); + $data[] = array('perUser' => array()); + while ($row = $res->fetch(PDO::FETCH_NUM)) { + $data['perUser'][] = array('user' => $row[0], 'loginCount' => $row[1]); } - //total time offline per room - $res = Database::simpleQuery("SELECT location.locationname AS 'room', statistic.data as time FROM statistic - INNER JOIN machine ON statistic.machineuuid=machine.machineuuid - INNER JOIN location ON machine.locationid=location.locationid - WHERE typeid='~offline-length' GROUP BY room ORDER BY cast(time AS UNSIGNED) DESC"); - $datay[] = array('offTimeRoom' => array()); - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $datay['offTimeRoom'][] = array('room' => $row['room'], 'offTime' => $this->formatSeconds($row['time'])); + // per vm + $res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY data ORDER BY 2 DESC"); + $data[] = array('perVM' => array()); + while ($row = $res->fetch(PDO::FETCH_NUM)) { + $data['perVM'][] = array('vm' => $row[0], 'loginCount' => $row[1]); } - $data = array_merge($datax, $datay); - Render::addTemplate('_page', $data); - } - - function formatSeconds($seconds) { - $seconds = $seconds * 1; - $minutes = floor($seconds / 60); - $hours = floor($minutes / 60); - $days = floor($hours / 24); - - $seconds = $seconds % 60; - $minutes = $minutes % 60; - $hours = $hours % 24; - - $format = '%u:%u:%02u:%02u'; - $time = sprintf($format, $days, $hours, $minutes, $seconds); - return rtrim($time, '0'); + Render::addTemplate('columnChooser'); + Render::addTemplate('_page', $data); } + protected function formatSeconds($seconds) + { + return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s'; + } } diff --git a/modules-available/statistics_reporting/style.css b/modules-available/statistics_reporting/style.css new file mode 100644 index 00000000..f0ee4fd3 --- /dev/null +++ b/modules-available/statistics_reporting/style.css @@ -0,0 +1,3 @@ +.top-row { + margin-bottom: 20px; +} \ 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 101a124e..720fb37a 100644 --- a/modules-available/statistics_reporting/templates/_page.html +++ b/modules-available/statistics_reporting/templates/_page.html @@ -1,138 +1,125 @@ -
- - - - - - - -
{{lang_totalLogins}}
{{totalLogins}}
-
- -
- - - - - - - -
{{lang_overallOfftime}}
{{totalOfftime}}
-
- - - -
-
- - - - - - {{#totalOfflineTimeClient}} - - - - - {{/totalOfflineTimeClient }} -
{{lang_client}}{{lang_totalOffTime}}
{{client}}{{offTime}}
+
+
+
+ + + + + + + + + + + + + + + + + + + +
{{lang_totalTime}}{{lang_avgSessionLength}}{{lang_totalLogins}}{{lang_overallOfftime}}
{{lang_total}}{{time}}{{avgTime}}{{totalLogins}}{{totalOfftime}}
+
- -
- - - - - - - {{#lastLogout}} - - - - - - {{/lastLogout}} -
{{lang_client}}{{lang_clientLogout}}{{lang_offTime}}
{{client}}{{lastlogout}}{{howLongOff}}
+
+
+ + + + + + + + + + + + {{#perLocation}} + + + + + + + + {{/perLocation}} + +
{{lang_location}}{{lang_totalTime}}{{lang_avgSessionLength}}{{lang_totalLogins}}{{lang_totalOffTime}}
{{location}}{{time}}{{avgTime}}{{loginCount}}{{offTime}}
+
- -
- - - - - - {{#lastLogin}} - - - - - {{/lastLogin}} -
{{lang_client}}{{lang_clientStart}}
{{client}}{{lastlogin}}
+
+
+ + + + + + + + + + + + + + {{#perClient}} + + + + + + + + + + {{/perClient}} + +
{{lang_hostname}}{{lang_totalTime}}{{lang_avgSessionLength}}{{lang_totalLogins}}{{lang_totalOffTime}}{{lang_clientLogout}}{{lang_clientStart}}
{{hostname}}{{time}}{{avgTime}}{{loginCount}}{{offTime}}{{lastLogout}}{{lastStart}}
+
- -
- - - - - - {{#offTimeRoom}} - - - - - {{/offTimeRoom}} -
{{lang_room}}{{lang_totalOffTime}}
{{room}}{{offTime}}
+
+
+ + + + + + + + + {{#perUser}} + + + + + {{/perUser}} + +
{{lang_user}}{{lang_totalLogins}}
{{user}}{{loginCount}}
+
-
- - - - -
-
- - - - - - {{#vmLogins}} - - - - - {{/vmLogins}} -
{{lang_vm}}{{lang_countLogins}}
{{vmname}}{{numLogins}}
+
+
+ + + + + + + + + {{#perVM}} + + + + + {{/perVM}} + +
{{lang_vm}}{{lang_totalLogins}}
{{vm}}{{loginCount}}
+
+
-
- - - - - - {{#machineLogins}} - - - - - {{/machineLogins}} -
{{lang_client}}{{lang_countLogins}}
{{client}}{{numLogins}}
-
-
- - - - - - {{#userLogins}} - - - - - {{/userLogins}} -
{{lang_user}}{{lang_countLogins}}
{{username}}{{numLogins}}
-
-
diff --git a/modules-available/statistics_reporting/templates/columnChooser.html b/modules-available/statistics_reporting/templates/columnChooser.html new file mode 100644 index 00000000..62e20a4a --- /dev/null +++ b/modules-available/statistics_reporting/templates/columnChooser.html @@ -0,0 +1,47 @@ +
+
+
+ +
+ + + + + + +
+
+
+
+ + + \ No newline at end of file -- cgit v1.2.3-55-g7522 From a250d56e61eeddf34fb8542babe1889de3475400 Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Tue, 29 Nov 2016 16:24:47 +0100 Subject: added javascript table sorting plugin (stupidtable) --- modules-available/js_stupidtable/clientscript.js | 30 ++++++++++++++++++++++ modules-available/js_stupidtable/config.json | 1 + modules-available/statistics_reporting/config.json | 3 ++- 3 files changed, 33 insertions(+), 1 deletion(-) create mode 100644 modules-available/js_stupidtable/clientscript.js create mode 100644 modules-available/js_stupidtable/config.json diff --git a/modules-available/js_stupidtable/clientscript.js b/modules-available/js_stupidtable/clientscript.js new file mode 100644 index 00000000..bfbc9112 --- /dev/null +++ b/modules-available/js_stupidtable/clientscript.js @@ -0,0 +1,30 @@ +/* + Stupid jQuery table plugin. + + https://github.com/joequery/Stupid-Table-Plugin + + Copyright (c) 2012 Joseph McCullough + + Permission is hereby granted, free of charge, to any person obtaining a copy + of this software and associated documentation files (the "Software"), to deal + in the Software without restriction, including without limitation the rights + to use, copy, modify, merge, publish, distribute, sublicense, and/or sell + copies of the Software, and to permit persons to whom the Software is + furnished to do so, subject to the following conditions: + + The above copyright notice and this permission notice shall be included in all + copies or substantial portions of the Software. + + THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR + IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, + FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE + AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER + LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, + OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE + SOFTWARE. + */ + +(function(c){c.fn.stupidtable=function(b){return this.each(function(){var a=c(this);b=b||{};b=c.extend({},c.fn.stupidtable.default_sort_fns,b);a.data("sortFns",b);a.on("click.stupidtable","thead th",function(){c(this).stupidsort()})})};c.fn.stupidsort=function(b){var a=c(this),g=0,f=c.fn.stupidtable.dir,e=a.closest("table"),k=a.data("sort")||null;if(null!==k){a.parents("tr").find("th").slice(0,c(this).index()).each(function(){var a=c(this).attr("colspan")||1;g+=parseInt(a,10)});var d;1==arguments.length? + d=b:(d=b||a.data("sort-default")||f.ASC,a.data("sort-dir")&&(d=a.data("sort-dir")===f.ASC?f.DESC:f.ASC));if(a.data("sort-dir")!==d)return a.data("sort-dir",d),e.trigger("beforetablesort",{column:g,direction:d}),e.css("display"),setTimeout(function(){var b=[],l=e.data("sortFns")[k],h=e.children("tbody").children("tr");h.each(function(a,d){var e=c(d).children().eq(g),f=e.data("sort-value");"undefined"===typeof f&&(f=e.text(),e.data("sort-value",f));b.push([f,d])});b.sort(function(a,b){return l(a[0], + b[0])});d!=f.ASC&&b.reverse();h=c.map(b,function(a){return a[1]});e.children("tbody").append(h);e.find("th").data("sort-dir",null).removeClass("sorting-desc sorting-asc");a.data("sort-dir",d).addClass("sorting-"+d);e.trigger("aftertablesort",{column:g,direction:d});e.css("display")},10),a}};c.fn.updateSortVal=function(b){var a=c(this);a.is("[data-sort-value]")&&a.attr("data-sort-value",b);a.data("sort-value",b);return a};c.fn.stupidtable.dir={ASC:"asc",DESC:"desc"};c.fn.stupidtable.default_sort_fns= + {"int":function(b,a){return parseInt(b,10)-parseInt(a,10)},"float":function(b,a){return parseFloat(b)-parseFloat(a)},string:function(b,a){return b.toString().localeCompare(a.toString())},"string-ins":function(b,a){b=b.toString().toLocaleLowerCase();a=a.toString().toLocaleLowerCase();return b.localeCompare(a)}}})(jQuery); diff --git a/modules-available/js_stupidtable/config.json b/modules-available/js_stupidtable/config.json new file mode 100644 index 00000000..9e26dfee --- /dev/null +++ b/modules-available/js_stupidtable/config.json @@ -0,0 +1 @@ +{} \ No newline at end of file diff --git a/modules-available/statistics_reporting/config.json b/modules-available/statistics_reporting/config.json index 706412d0..8115acf2 100644 --- a/modules-available/statistics_reporting/config.json +++ b/modules-available/statistics_reporting/config.json @@ -1,3 +1,4 @@ { - "category":"main.content" + "category": "main.content", + "dependencies": [ "js_stupidtable"] } -- cgit v1.2.3-55-g7522 From f9f365ffbc7457f706f759a40b7dd16e3ea8d753 Mon Sep 17 00:00:00 2001 From: Christian Hofmaier Date: Thu, 1 Dec 2016 16:32:12 +0100 Subject: [statistics_reporting] moved functions to own file, improved language and variable naming --- .../inc/statisticreporting.inc.php | 64 ++++++++++++++++++++++ .../lang/de/template-tags.json | 6 +- .../lang/en/template-tags.json | 6 +- .../statistics_reporting/page.inc.php | 52 +++++------------- 4 files changed, 83 insertions(+), 45 deletions(-) create mode 100644 modules-available/statistics_reporting/inc/statisticreporting.inc.php diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php new file mode 100644 index 00000000..5bfba04f --- /dev/null +++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php @@ -0,0 +1,64 @@ + Session endet trotzdem mit session-length Meldung) // TODO change AVG to median // total time online, average time online, total number of logins - $res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)), AVG(CAST(data AS UNSIGNED)), COUNT(*) FROM statistic WHERE typeid = '~session-length'"); + $res = StatisticReporting::getOverallStatistics(); $row = $res->fetch(PDO::FETCH_NUM); - $data = array('time' => $this->formatSeconds($row[0]), 'avgTime' => $this->formatSeconds($row[1]), 'totalLogins' => $row[2]); + $data = array('time' => StatisticReporting::formatSeconds($row[0]), 'avgTime' => StatisticReporting::formatSeconds($row[1]), 'totalLogins' => $row[2]); //total time offline - $res = Database::simpleQUery("SELECT SUM(CAST(data AS UNSIGNED)) FROM statistic WHERE typeid='~offline-length'"); + $res = StatisticReporting::getTotalOffline(); $row = $res->fetch(PDO::FETCH_NUM); - $data = array_merge($data, array('totalOfftime' => $this->formatSeconds($row[0]))); + $data = array_merge($data, array('totalOfftime' => StatisticReporting::formatSeconds($row[0]))); // per location - $res = Database::simpleQuery("SELECT t1.ln, timeSum, avgTime, offlineSum, loginCount FROM ( - SELECT location.locationname AS 'ln', SUM(CAST(statistic.data AS UNSIGNED)) AS 'timeSum', AVG(CAST(statistic.data AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount' - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - INNER JOIN location ON machine.locationid = location.locationid - WHERE statistic.typeid = '~session-length' GROUP By location.locationname - ) t1 INNER JOIN ( - SELECT location.locationname AS 'ln', SUM(CAST(statistic.data AS UNSIGNED)) AS 'offlineSum' - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - INNER JOIN location ON machine.locationid = location.locationid - WHERE statistic.typeid = '~offline-length' GROUP By location.locationname - ) t2 ON t1.ln = t2.ln"); + $res = StatisticReporting::getLocationStatistics(); $data[] = array('perLocation' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { - $data['perLocation'][] = array('location' => $row[0], 'time' => $this->formatSeconds($row[1]), 'timeInSeconds' => $row[1], - 'avgTime' => $this->formatSeconds($row[2]), 'avgTimeInSeconds' => $row[2], 'offTime' => $this->formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4]); + $data['perLocation'][] = array('location' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1], + 'avgTime' => StatisticReporting::formatSeconds($row[2]), 'avgTimeInSeconds' => $row[2], 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4]); } // per client - $res = Database::simpleQuery("SELECT t1.name, timeSum, avgTime, offlineSum, loginCount, lastLogout, lastStart FROM ( - SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(statistic.data AS UNSIGNED)) AS 'timeSum', AVG(CAST(statistic.data AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount', MAX(statistic.dateline) AS 'lastLogout' - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - WHERE typeid = '~session-length' GROUP BY machine.machineuuid - ) t1 INNER JOIN ( - SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(statistic.data AS UNSIGNED)) AS 'offlineSum', MAX(statistic.dateline) AS 'lastStart' - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - WHERE typeid = '~offline-length' GROUP BY machine.machineuuid - ) t2 ON t1.uuid = t2.uuid"); + $res = StatisticReporting::getClientStatistics(); $data[] = array('perClient' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { - $data['perClient'][] = array('hostname' => $row[0], 'time' => $this->formatSeconds($row[1]), 'timeInSeconds' => $row[1], - 'avgTime' => $this->formatSeconds($row[2]), 'avgTimeInSeconds' => $row[2], 'offTime' => $this->formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4], + $data['perClient'][] = array('hostname' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1], + 'avgTime' => StatisticReporting::formatSeconds($row[2]), 'avgTimeInSeconds' => $row[2], 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4], 'lastLogout' => date(DATE_RSS,$row[5]), 'lastLogoutUnixtime' => $row[5], 'lastStart' => date(DATE_RSS,$row[6]), 'lastStartUnixtime' => $row[6]); } // per user - $res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY username ORDER BY 2 DESC"); + $res = StatisticReporting::getUserStatistics(); $data[] = array('perUser' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { $data['perUser'][] = array('user' => $row[0], 'loginCount' => $row[1]); } // per vm - $res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY data ORDER BY 2 DESC"); + $res = StatisticReporting::getVMStatistics(); $data[] = array('perVM' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { $data['perVM'][] = array('vm' => $row[0], 'loginCount' => $row[1]); } - - Render::addTemplate('columnChooser'); Render::addTemplate('_page', $data); } - - - protected function formatSeconds($seconds) - { - return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s'; - } } -- cgit v1.2.3-55-g7522 From 5f9bdcdc785e465d12e29e7bf85aed41dfd3b086 Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Mon, 5 Dec 2016 15:17:55 +0100 Subject: [statistics_reporting] added sorting indicators --- modules-available/statistics_reporting/style.css | 4 ++ .../templates/columnChooser.html | 49 ++++++++++++++-------- 2 files changed, 36 insertions(+), 17 deletions(-) diff --git a/modules-available/statistics_reporting/style.css b/modules-available/statistics_reporting/style.css index f0ee4fd3..6cb6de51 100644 --- a/modules-available/statistics_reporting/style.css +++ b/modules-available/statistics_reporting/style.css @@ -1,3 +1,7 @@ .top-row { margin-bottom: 20px; +} + +.buttonbar button { + margin-bottom: 4px; } \ No newline at end of file diff --git a/modules-available/statistics_reporting/templates/columnChooser.html b/modules-available/statistics_reporting/templates/columnChooser.html index 62e20a4a..ccc82a50 100644 --- a/modules-available/statistics_reporting/templates/columnChooser.html +++ b/modules-available/statistics_reporting/templates/columnChooser.html @@ -1,21 +1,21 @@
-
- -
- - - - - - -
+
+
+ + + + + +
@@ -24,23 +24,38 @@ \ No newline at end of file -- cgit v1.2.3-55-g7522 From 8c57a58760c716fa2ad8387ef8ba4eaac8b741a6 Mon Sep 17 00:00:00 2001 From: Christian Hofmaier Date: Mon, 12 Dec 2016 18:30:28 +0100 Subject: [statistics_reporting] edited time bound possibility --- .../inc/statisticreporting.inc.php | 52 +++++++++------------- .../statistics_reporting/page.inc.php | 28 ++++++------ 2 files changed, 35 insertions(+), 45 deletions(-) diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php index a1a209d8..69a4d516 100644 --- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php +++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php @@ -4,83 +4,71 @@ class StatisticReporting { - public static function getClientStatistics($cutOffTimeInSeconds) { + public static function getClientStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - // time bounds (8,22 means from 8 o clock to 22 o clock) - $lowerBound = 8; - $upperBound = 22; $res = Database::simpleQuery("SELECT t1.name, timeSum, avgTime, offlineSum, loginCount, lastLogout, lastStart FROM ( SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(statistic.data AS UNSIGNED)) AS 'timeSum', AVG(CAST(statistic.data AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount', MAX(statistic.dateline + (statistic.data *1)) AS 'lastLogout' FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - WHERE typeid = '~session-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound)) + WHERE typeid = '~session-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) GROUP BY machine.machineuuid ) t1 INNER JOIN ( SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(statistic.data AS UNSIGNED)) AS 'offlineSum', MAX(statistic.dateline) AS 'lastStart' FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - WHERE typeid = '~offline-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound)) + WHERE typeid = '~offline-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) GROUP BY machine.machineuuid ) t2 ON t1.uuid = t2.uuid"); return $res; } - public static function getLocationStatistics($cutOffTimeInSeconds) { + public static function getLocationStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - // time bounds (8,22 means from 8 o clock to 22 o clock) - $lowerBound = 8; - $upperBound = 22; $res = Database::simpleQuery("SELECT t1.locName, timeSum, avgTime, offlineSum, loginCount FROM ( SELECT location.locationname AS 'locName', SUM(CAST(statistic.data AS UNSIGNED)) AS 'timeSum', AVG(CAST(statistic.data AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount' FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid INNER JOIN location ON machine.locationid = location.locationid - WHERE statistic.typeid = '~session-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound)) + WHERE statistic.typeid = '~session-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) GROUP By location.locationname ) t1 INNER JOIN ( SELECT location.locationname AS 'locName', SUM(CAST(statistic.data AS UNSIGNED)) AS 'offlineSum' FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid INNER JOIN location ON machine.locationid = location.locationid - WHERE statistic.typeid = '~offline-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound)) + WHERE statistic.typeid = '~offline-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) GROUP By location.locationname ) t2 ON t1.locName = t2.locName"); return $res; } - public static function getUserStatistics($cutOffTimeInSeconds) { + // logins between bounds + public static function getUserStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - // time bounds (8,22 means from 8 o clock to 22 o clock) - $lowerBound = 8; - $upperBound = 22; $res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline>=$queryTime - AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound)) GROUP BY username ORDER BY 2 DESC"); + AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) GROUP BY username ORDER BY 2 DESC"); return $res; } - public static function getVMStatistics($cutOffTimeInSeconds) { + // vm logins between bounds + public static function getVMStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - // time bounds (8,22 means from 8 o clock to 22 o clock) - $lowerBound = 8; - $upperBound = 22; $res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline>=$queryTime - AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound)) 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; } - public static function getOverallStatistics ($cutOffTimeInSeconds) { + // AND(betweenBounds OR upper but begins in bound ) + public static function getOverallStatistics ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - // time bounds (8,22 means from 8 o clock to 22 o clock) - $lowerBound = 8; - $upperBound = 22; $res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)), AVG(CAST(data AS UNSIGNED)), COUNT(*) FROM statistic WHERE typeid = '~session-length' AND dateline>=$queryTime - AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound))"); + AND (((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + ) + + "); return $res; } - public static function getTotalOfflineStatistics($cutOffTimeInSeconds) { + public static function getTotalOfflineStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - // time bounds (8,22 means from 8 o clock to 22 o clock) - $lowerBound = 8; - $upperBound = 22; $res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)) FROM statistic WHERE typeid='~offline-length' AND dateline>=$queryTime - AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound))"); + AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))"); return $res; } diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php index e009317c..9417d2da 100644 --- a/modules-available/statistics_reporting/page.inc.php +++ b/modules-available/statistics_reporting/page.inc.php @@ -1,11 +1,9 @@ $cutOff); - $this->cutOffTimer = $cutOff * 86400; + $cutOffTimer = $cutOff * 86400; + + $lowerTimeBound = Request::get('lower', 0, 'int'); + + $upperTimeBound = Request::get('upper', 24, 'int'); // total time online, average time online, total number of logins - $res = StatisticReporting::getOverallStatistics($this->cutOffTimer); + $res = StatisticReporting::getOverallStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound); $row = $res->fetch(PDO::FETCH_NUM); $data = array('time' => StatisticReporting::formatSeconds($row[0]), 'avgTime' => StatisticReporting::formatSeconds($row[1]), 'totalLogins' => $row[2]); + //total time offline - $res = StatisticReporting::getTotalOfflineStatistics($this->cutOffTimer); + $res = StatisticReporting::getTotalOfflineStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound); $row = $res->fetch(PDO::FETCH_NUM); $data = array_merge($data, array('totalOfftime' => StatisticReporting::formatSeconds($row[0]))); // per location - $res = StatisticReporting::getLocationStatistics($this->cutOffTimer); + $res = StatisticReporting::getLocationStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound); $data[] = array('perLocation' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { $data['perLocation'][] = array('location' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1], @@ -49,7 +51,7 @@ class Page_Statistics_Reporting extends Page } // per client - $res = StatisticReporting::getClientStatistics($this->cutOffTimer); + $res = StatisticReporting::getClientStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound); $data[] = array('perClient' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { $data['perClient'][] = array('hostname' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1], @@ -58,20 +60,20 @@ class Page_Statistics_Reporting extends Page } // per user - $res = StatisticReporting::getUserStatistics($this->cutOffTimer); + $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]); } // per vm - $res = StatisticReporting::getVMStatistics($this->cutOffTimer); + $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]); } - Render::addTemplate('columnChooser', $chooserData); + Render::addTemplate('columnChooser'); Render::addTemplate('_page', $data); } } -- cgit v1.2.3-55-g7522 From f616a8cc375c2228def671800be40fac2072b27b Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Tue, 20 Dec 2016 14:49:17 +0100 Subject: [statistics_reporting] added time bounds slider --- modules-available/statistics_reporting/config.json | 2 +- .../inc/statisticreporting.inc.php | 119 +++++++++++++++++---- .../lang/de/template-tags.json | 3 +- .../lang/en/template-tags.json | 7 +- modules-available/statistics_reporting/style.css | 24 ++++- .../templates/columnChooser.html | 52 ++++++++- 6 files changed, 175 insertions(+), 32 deletions(-) diff --git a/modules-available/statistics_reporting/config.json b/modules-available/statistics_reporting/config.json index 8115acf2..1a9c7fe7 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"] + "dependencies": [ "js_stupidtable", "js_jqueryui" ] } diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php index 69a4d516..7652bbdc 100644 --- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php +++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php @@ -7,14 +7,16 @@ class StatisticReporting public static function getClientStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; $res = Database::simpleQuery("SELECT t1.name, timeSum, avgTime, offlineSum, loginCount, lastLogout, lastStart FROM ( - SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(statistic.data AS UNSIGNED)) AS 'timeSum', AVG(CAST(statistic.data AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount', MAX(statistic.dateline + (statistic.data *1)) AS 'lastLogout' - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - WHERE typeid = '~session-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', AVG(CAST(sessionTable.length AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount', MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout' + FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." 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(statistic.data AS UNSIGNED)) AS 'offlineSum', MAX(statistic.dateline) AS 'lastStart' - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - WHERE typeid = '~offline-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + 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 + INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid + WHERE offlineTable.dateline>=$queryTime GROUP BY machine.machineuuid ) t2 ON t1.uuid = t2.uuid"); return $res; @@ -22,17 +24,20 @@ class StatisticReporting public static function getLocationStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; + $res = Database::simpleQuery("SELECT t1.locName, timeSum, avgTime, offlineSum, loginCount FROM ( - SELECT location.locationname AS 'locName', SUM(CAST(statistic.data AS UNSIGNED)) AS 'timeSum', AVG(CAST(statistic.data AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount' - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - INNER JOIN location ON machine.locationid = location.locationid - WHERE statistic.typeid = '~session-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + SELECT location.locationname AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', AVG(CAST(sessionTable.length AS UNSIGNED)) AS 'avgTime', COUNT(sessionTable.length) AS 'loginCount' + FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable + INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid + INNER JOIN location ON machine.locationid = location.locationid + WHERE sessionTable.dateline >= $queryTime GROUP By location.locationname ) t1 INNER JOIN ( - SELECT location.locationname AS 'locName', SUM(CAST(statistic.data AS UNSIGNED)) AS 'offlineSum' - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - INNER JOIN location ON machine.locationid = location.locationid - WHERE statistic.typeid = '~offline-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + SELECT location.locationname AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' + FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound)." offlineTable + INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid + INNER JOIN location ON machine.locationid = location.locationid + WHERE offlineTable.dateline >= $queryTime GROUP By location.locationname ) t2 ON t1.locName = t2.locName"); return $res; @@ -57,18 +62,17 @@ class StatisticReporting // AND(betweenBounds OR upper but begins in bound ) public static function getOverallStatistics ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)), AVG(CAST(data AS UNSIGNED)), COUNT(*) FROM statistic WHERE typeid = '~session-length' AND dateline>=$queryTime - AND (((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) - ) - - "); + $res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)), AVG(CAST(sessionTable.length AS UNSIGNED)), COUNT(*) + FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable + WHERE sessionTable.dateline>=$queryTime"); return $res; } public static function getTotalOfflineStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)) FROM statistic WHERE typeid='~offline-length' AND dateline>=$queryTime - AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))"); + $res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED)) + FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound)." offlineTable + WHERE offlineTable.dateline>=$queryTime"); return $res; } @@ -77,4 +81,75 @@ class StatisticReporting return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s'; } -} \ No newline at end of file + + private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound) + { + $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 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/lang/de/template-tags.json b/modules-available/statistics_reporting/lang/de/template-tags.json index 521b0607..f2500db0 100644 --- a/modules-available/statistics_reporting/lang/de/template-tags.json +++ b/modules-available/statistics_reporting/lang/de/template-tags.json @@ -24,5 +24,6 @@ "lang_last7": "Letzten 7 Tage", "lang_last14": "Letzten 14 Tage", "lang_last30": "Letzten 30 Tage", - "lang_last90": "Letzten 90 Tage" + "lang_last90": "Letzten 90 Tage", + "lang_apply": "Anwenden" } \ 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 c3900e31..8fa97719 100644 --- a/modules-available/statistics_reporting/lang/en/template-tags.json +++ b/modules-available/statistics_reporting/lang/en/template-tags.json @@ -5,7 +5,7 @@ "lang_vm": "VM", "lang_totalLogins": "Total Logins", "lang_overallOfftime": "Overall time offline", - "lang_totalOffTime": "Total time offline", + "lang_totalOffTime": "Total Time Offline", "lang_clientLogout": "Last VM Logout", "lang_clientStart": "Last Client Boot", "lang_room": "Room", @@ -18,11 +18,12 @@ "lang_hostname": "Client Name", "lang_location": "Location", "lang_avgSessionLength": "Average Session Length", - "lang_totalTime": "Gesamte Zeit", + "lang_totalTime": "Total Time", "lang_last1": "Last 24 hours", "lang_last2": "Last 48 hours", "lang_last7": "Last 7 days", "lang_last14": "Last 14 days", "lang_last30": "Last 30 days", - "lang_last90": "Last 90 days" + "lang_last90": "Last 90 days", + "lang_apply": "Apply" } \ No newline at end of file diff --git a/modules-available/statistics_reporting/style.css b/modules-available/statistics_reporting/style.css index b0875a7d..d7fa7a2f 100644 --- a/modules-available/statistics_reporting/style.css +++ b/modules-available/statistics_reporting/style.css @@ -2,7 +2,7 @@ margin-bottom: 10px; } -.top-row select, .top-row .btn-group { +.top-row > * { margin-right: 10px; margin-bottom: 10px; } @@ -13,4 +13,26 @@ .buttonbar { margin-bottom: 20px; +} + +#slider { + display: inline-block; + width: 160px; + margin-left: 20px; + margin-bottom: 3px; + margin-right: 20px; +} + +#lower-handle, #upper-handle { + width: 3em; + height: 1.6em; + top: 50%; + margin-top: -.8em; + margin-left: -1.5em; + text-align: center; + line-height: 1.6em; +} + +#applybound { + display: none; } \ No newline at end of file diff --git a/modules-available/statistics_reporting/templates/columnChooser.html b/modules-available/statistics_reporting/templates/columnChooser.html index 9e6f51d2..319f9702 100644 --- a/modules-available/statistics_reporting/templates/columnChooser.html +++ b/modules-available/statistics_reporting/templates/columnChooser.html @@ -8,7 +8,7 @@ - @@ -16,6 +16,12 @@ + +
+
+
+
+
@@ -30,7 +36,30 @@ \ No newline at end of file -- cgit v1.2.3-55-g7522 From b8e485583f7df8e1c911215832194d69cc564574 Mon Sep 17 00:00:00 2001 From: Christian Hofmaier Date: Tue, 20 Dec 2016 15:23:28 +0100 Subject: [statistics_reporting] filtered sessions with short length (<60secs), logins on machines without locationid are now shown --- .../inc/statisticreporting.inc.php | 52 +++++++++++++++++----- .../statistics_reporting/page.inc.php | 2 +- 2 files changed, 42 insertions(+), 12 deletions(-) diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php index 7652bbdc..0e6039bb 100644 --- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php +++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php @@ -10,7 +10,7 @@ class StatisticReporting SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', AVG(CAST(sessionTable.length AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount', MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout' FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid - WHERE sessionTable.dateline>=$queryTime + WHERE sessionTable.dateline>=$queryTime AND sessionTable.data >= 60 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' @@ -26,17 +26,17 @@ class StatisticReporting $queryTime = time() - $cutOffTimeInSeconds; $res = Database::simpleQuery("SELECT t1.locName, timeSum, avgTime, offlineSum, loginCount FROM ( - SELECT location.locationname AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', AVG(CAST(sessionTable.length AS UNSIGNED)) AS 'avgTime', COUNT(sessionTable.length) AS 'loginCount' + SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', AVG(CAST(sessionTable.length AS UNSIGNED)) AS 'avgTime', COUNT(sessionTable.length) AS 'loginCount' FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid - INNER JOIN location ON machine.locationid = location.locationid - WHERE sessionTable.dateline >= $queryTime + LEFT JOIN location ON machine.locationid = location.locationid + WHERE sessionTable.dateline >= $queryTime AND sessionTable.data >= 60 GROUP By location.locationname ) t1 INNER JOIN ( - SELECT location.locationname AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' + SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid - INNER JOIN location ON machine.locationid = location.locationid + LEFT JOIN location ON machine.locationid = location.locationid WHERE offlineTable.dateline >= $queryTime GROUP By location.locationname ) t2 ON t1.locName = t2.locName"); @@ -46,8 +46,11 @@ class StatisticReporting // logins between bounds public static function getUserStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline>=$queryTime - AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) GROUP BY username ORDER BY 2 DESC"); + $res = Database::simpleQuery("SELECT username, data, COUNT(*) AS 'count' + FROM statistic + WHERE typeid='.vmchooser-session-name' AND dateline>=$queryTime + AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + GROUP BY username, data ORDER BY 3 DESC"); return $res; } @@ -64,7 +67,7 @@ class StatisticReporting $queryTime = time() - $cutOffTimeInSeconds; $res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)), AVG(CAST(sessionTable.length AS UNSIGNED)), COUNT(*) FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable - WHERE sessionTable.dateline>=$queryTime"); + WHERE sessionTable.dateline>=$queryTime AND sessionTable.data >= 60"); return $res; } @@ -76,6 +79,35 @@ class StatisticReporting return $res; } + // TODO: Short Sessions in Tabelle einbauen + public static function getShortSessionsTotal($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { + $queryTime = time() - $cutOffTimeInSeconds; + $res = Database::simpleQuery("SELECT COUNT(*) FROM statistic WHERE typeid='~session-length' AND data < 60 AND dateline>=$queryTime + AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))"); + return $res; + } + + public static function getShortSessionsPerLocation ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { + $queryTime = time() - $cutOffTimeInSeconds; + $res = Database::simpleQuery("SELECT location.locationname, COUNT(*) + FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid + INNER JOIN location ON machine.locationid = location.locationid + WHERE statistic.typeid='~session-length' AND statistic.data < 60 AND dateline>=$queryTime + AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + GROUP BY location.locationname"); + return $res; + } + + public static function getShortSessionsPerClient ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { + $queryTime = time() - $cutOffTimeInSeconds; + $res = Database::simpleQuery("SELECT machine.hostname, COUNT(*) + FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid + WHERE statistic.typeid='~session-length' AND statistic.data < 60 AND dateline>=$queryTime + AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + GROUP BY machine.hostname"); + return $res; + } + public static function formatSeconds($seconds) { return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s'; @@ -149,7 +181,5 @@ class StatisticReporting "; return "(".$queryString.")"; } - - } diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php index 9417d2da..6392dd19 100644 --- a/modules-available/statistics_reporting/page.inc.php +++ b/modules-available/statistics_reporting/page.inc.php @@ -63,7 +63,7 @@ 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], 'vm' => $row[1], 'loginCount' => $row[2]); } // per vm -- cgit v1.2.3-55-g7522 From b5b20494741a721eae44c1afea8369437d8ce97e Mon Sep 17 00:00:00 2001 From: Christian Hofmaier Date: Tue, 20 Dec 2016 16:09:55 +0100 Subject: [statistics_reporting] deleted vm per user (for udo) after adding it last commit --- modules-available/statistics_reporting/inc/statisticreporting.inc.php | 4 ++-- modules-available/statistics_reporting/page.inc.php | 2 +- 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 0e6039bb..c9fe1c64 100644 --- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php +++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php @@ -46,11 +46,11 @@ class StatisticReporting // logins between bounds public static function getUserStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT username, data, COUNT(*) AS 'count' + $res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) - GROUP BY username, data ORDER BY 3 DESC"); + GROUP BY username ORDER BY 2 DESC"); return $res; } diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php index 6392dd19..9417d2da 100644 --- a/modules-available/statistics_reporting/page.inc.php +++ b/modules-available/statistics_reporting/page.inc.php @@ -63,7 +63,7 @@ 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], 'vm' => $row[1], 'loginCount' => $row[2]); + $data['perUser'][] = array('user' => $row[0], 'loginCount' => $row[1]); } // per vm -- cgit v1.2.3-55-g7522 From 5ddedfac17caf4a994da1f52b49ceb9fffc834bd Mon Sep 17 00:00:00 2001 From: Christian Hofmaier Date: Thu, 22 Dec 2016 13:53:36 +0100 Subject: [statistics_reporting] added short sessions to Queries --- .../inc/statisticreporting.inc.php | 82 +++++++++++----------- 1 file changed, 41 insertions(+), 41 deletions(-) diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php index c9fe1c64..9212a09c 100644 --- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php +++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php @@ -6,40 +6,63 @@ class StatisticReporting public static function getClientStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT t1.name, timeSum, avgTime, offlineSum, loginCount, lastLogout, lastStart FROM ( + $res = Database::simpleQuery("SELECT t1.name, timeSum, avgTime, offlineSum, loginCount, lastLogout, lastStart, shortSessions FROM ( SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', AVG(CAST(sessionTable.length AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount', MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout' 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 - ) t1 INNER JOIN ( + ) 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 INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid WHERE offlineTable.dateline>=$queryTime GROUP BY machine.machineuuid - ) t2 ON t1.uuid = t2.uuid"); + ) 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 + "); return $res; } + // 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; - $res = Database::simpleQuery("SELECT t1.locName, timeSum, avgTime, offlineSum, loginCount FROM ( + $res = Database::simpleQuery("SELECT t1.locName, timeSum, avgTime, offlineSum, loginCount, shortSessions FROM ( SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', AVG(CAST(sessionTable.length AS UNSIGNED)) AS 'avgTime', COUNT(sessionTable.length) AS 'loginCount' 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 - ) t1 INNER JOIN ( + 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 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"); + 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"); return $res; } @@ -62,12 +85,18 @@ class StatisticReporting return $res; } - // AND(betweenBounds OR upper but begins in bound ) public static function getOverallStatistics ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)), AVG(CAST(sessionTable.length AS UNSIGNED)), COUNT(*) - FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable - WHERE sessionTable.dateline>=$queryTime AND sessionTable.data >= 60"); + $res = Database::simpleQuery("SELECT sum, avg, countLong, countShort FROM + ( SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, AVG(CAST(sessionTable.length AS UNSIGNED)) AS avg, COUNT(*) AS countLong + 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 + FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable + WHERE sessionTable.dateline>=$queryTime AND sessionTable.data < 60 + ) t2"); return $res; } @@ -79,35 +108,6 @@ class StatisticReporting return $res; } - // TODO: Short Sessions in Tabelle einbauen - public static function getShortSessionsTotal($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { - $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT COUNT(*) FROM statistic WHERE typeid='~session-length' AND data < 60 AND dateline>=$queryTime - AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))"); - return $res; - } - - public static function getShortSessionsPerLocation ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { - $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT location.locationname, COUNT(*) - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - INNER JOIN location ON machine.locationid = location.locationid - WHERE statistic.typeid='~session-length' AND statistic.data < 60 AND dateline>=$queryTime - AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) - GROUP BY location.locationname"); - return $res; - } - - public static function getShortSessionsPerClient ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { - $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT machine.hostname, COUNT(*) - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - WHERE statistic.typeid='~session-length' AND statistic.data < 60 AND dateline>=$queryTime - AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) - GROUP BY machine.hostname"); - return $res; - } - public static function formatSeconds($seconds) { return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s'; -- cgit v1.2.3-55-g7522 From f74dfc9488cf4733b18a11b90307183bafbc01f8 Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Thu, 22 Dec 2016 13:59:12 +0100 Subject: [statistics_reporting] changed average to median --- .../inc/statisticreporting.inc.php | 26 +++++++++++++++++----- .../lang/de/template-tags.json | 2 +- .../lang/en/template-tags.json | 2 +- .../statistics_reporting/page.inc.php | 8 ++++--- .../statistics_reporting/templates/_page.html | 12 +++++----- .../templates/columnChooser.html | 2 +- 6 files changed, 34 insertions(+), 18 deletions(-) diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php index 9212a09c..d98ec168 100644 --- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php +++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php @@ -6,8 +6,8 @@ class StatisticReporting public static function getClientStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT t1.name, timeSum, avgTime, offlineSum, loginCount, lastLogout, lastStart, shortSessions FROM ( - SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', AVG(CAST(sessionTable.length AS UNSIGNED)) AS 'avgTime', COUNT(*) AS 'loginCount', MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout' + $res = Database::simpleQuery("SELECT t1.name, timeSum, medianTime, offlineSum, loginCount, lastLogout, lastStart, shortSessions FROM ( + SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', COUNT(*) AS 'loginCount', MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout' FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid WHERE sessionTable.dateline>=$queryTime AND sessionTable.data >= 60 @@ -37,8 +37,8 @@ class StatisticReporting public static function getLocationStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT t1.locName, timeSum, avgTime, offlineSum, loginCount, shortSessions FROM ( - SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', AVG(CAST(sessionTable.length AS UNSIGNED)) AS 'avgTime', COUNT(sessionTable.length) AS 'loginCount' + $res = Database::simpleQuery("SELECT t1.locName, timeSum, medianTime, offlineSum, loginCount, shortSessions FROM ( + SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', COUNT(sessionTable.length) AS 'loginCount' FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid @@ -87,8 +87,8 @@ class StatisticReporting public static function getOverallStatistics ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT sum, avg, countLong, countShort FROM - ( SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, AVG(CAST(sessionTable.length AS UNSIGNED)) AS avg, COUNT(*) AS countLong + $res = Database::simpleQuery("SELECT sum, median, countLong, countShort FROM + ( SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, COUNT(*) AS countLong FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable WHERE sessionTable.dateline>=$queryTime AND sessionTable.data >= 60 ) t1 @@ -113,6 +113,20 @@ class StatisticReporting return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s'; } + public static function calcMedian($string) { + $arr = explode(",", $string); + sort($arr, SORT_NUMERIC); + $count = count($arr); //total numbers in array + $middleval = floor(($count-1)/2); // find the middle value, or the lowest middle value + if($count % 2) { // odd number, middle is the median + $median = $arr[(int) $middleval]; + } else { // even number, calculate avg of 2 medians + $low = $arr[(int) $middleval]; + $high = $arr[(int) $middleval+1]; + $median = (($low+$high)/2); + } + return round($median); + } private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound) { diff --git a/modules-available/statistics_reporting/lang/de/template-tags.json b/modules-available/statistics_reporting/lang/de/template-tags.json index f2500db0..c5494205 100644 --- a/modules-available/statistics_reporting/lang/de/template-tags.json +++ b/modules-available/statistics_reporting/lang/de/template-tags.json @@ -17,7 +17,7 @@ "lang_pervm": "Pro VM", "lang_hostname": "Client Name", "lang_location": "Raum", - "lang_avgSessionLength": "Durchschnittliche Sitzungsdauer", + "lang_medianSessionLength": "Mittlere Sitzungsdauer", "lang_totalTime": "Gesamte Zeit", "lang_last1": "Letzten 24 Stunden", "lang_last2": "Letzten 48 Stunden", diff --git a/modules-available/statistics_reporting/lang/en/template-tags.json b/modules-available/statistics_reporting/lang/en/template-tags.json index 8fa97719..de91621f 100644 --- a/modules-available/statistics_reporting/lang/en/template-tags.json +++ b/modules-available/statistics_reporting/lang/en/template-tags.json @@ -17,7 +17,7 @@ "lang_pervm": "Per VM", "lang_hostname": "Client Name", "lang_location": "Location", - "lang_avgSessionLength": "Average Session Length", + "lang_medianSessionLength": "Median Session Length", "lang_totalTime": "Total Time", "lang_last1": "Last 24 hours", "lang_last2": "Last 48 hours", diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php index 9417d2da..0dd4f243 100644 --- a/modules-available/statistics_reporting/page.inc.php +++ b/modules-available/statistics_reporting/page.inc.php @@ -35,7 +35,7 @@ class Page_Statistics_Reporting extends Page // total time online, average time online, total number of logins $res = StatisticReporting::getOverallStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound); $row = $res->fetch(PDO::FETCH_NUM); - $data = array('time' => StatisticReporting::formatSeconds($row[0]), 'avgTime' => StatisticReporting::formatSeconds($row[1]), 'totalLogins' => $row[2]); + $data = array('time' => StatisticReporting::formatSeconds($row[0]), 'medianTime' => StatisticReporting::formatSeconds(StatisticReporting::calcMedian($row[1])), 'totalLogins' => $row[2]); //total time offline $res = StatisticReporting::getTotalOfflineStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound); @@ -46,16 +46,18 @@ class Page_Statistics_Reporting extends Page $res = StatisticReporting::getLocationStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound); $data[] = array('perLocation' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { + $median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row[2])); $data['perLocation'][] = array('location' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1], - 'avgTime' => StatisticReporting::formatSeconds($row[2]), 'avgTimeInSeconds' => $row[2], 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4]); + 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4]); } // per client $res = StatisticReporting::getClientStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound); $data[] = array('perClient' => array()); while ($row = $res->fetch(PDO::FETCH_NUM)) { + $median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row[2])); $data['perClient'][] = array('hostname' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1], - 'avgTime' => StatisticReporting::formatSeconds($row[2]), 'avgTimeInSeconds' => $row[2], 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4], + 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'loginCount' => $row[4], 'lastLogout' => date(DATE_RSS,$row[5]), 'lastLogoutUnixtime' => $row[5], 'lastStart' => date(DATE_RSS,$row[6]), 'lastStartUnixtime' => $row[6]); } diff --git a/modules-available/statistics_reporting/templates/_page.html b/modules-available/statistics_reporting/templates/_page.html index 720fb37a..7cfb424c 100644 --- a/modules-available/statistics_reporting/templates/_page.html +++ b/modules-available/statistics_reporting/templates/_page.html @@ -6,7 +6,7 @@ {{lang_totalTime}} - {{lang_avgSessionLength}} + {{lang_medianSessionLength}} {{lang_totalLogins}} {{lang_overallOfftime}} @@ -15,7 +15,7 @@ {{lang_total}} {{time}} - {{avgTime}} + {{medianTime}} {{totalLogins}} {{totalOfftime}} @@ -30,7 +30,7 @@ {{lang_location}} {{lang_totalTime}} - {{lang_avgSessionLength}} + {{lang_medianSessionLength}} {{lang_totalLogins}} {{lang_totalOffTime}} @@ -40,7 +40,7 @@ {{location}} {{time}} - {{avgTime}} + {{medianTime}} {{loginCount}} {{offTime}} @@ -56,7 +56,7 @@ {{lang_hostname}} {{lang_totalTime}} - {{lang_avgSessionLength}} + {{lang_medianSessionLength}} {{lang_totalLogins}} {{lang_totalOffTime}} {{lang_clientLogout}} @@ -68,7 +68,7 @@ {{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 @@ {{lang_total}} {{time}} {{medianTime}} - {{totalLogins}} + {{sessions}} + {{shortSessions}} {{totalOfftime}} @@ -31,7 +33,8 @@ {{lang_location}} {{lang_totalTime}} {{lang_medianSessionLength}} - {{lang_totalLogins}} + {{lang_sessions}} + {{lang_shortSessions}} {{lang_totalOffTime}} @@ -41,7 +44,8 @@ {{location}} {{time}} {{medianTime}} - {{loginCount}} + {{sessions}} + {{shortSessions}} {{offTime}} {{/perLocation}} @@ -57,7 +61,8 @@ {{lang_hostname}} {{lang_totalTime}} {{lang_medianSessionLength}} - {{lang_totalLogins}} + {{lang_sessions}} + {{lang_shortSessions}} {{lang_totalOffTime}} {{lang_clientLogout}} {{lang_clientStart}} @@ -69,7 +74,8 @@ {{hostname}} {{time}} {{medianTime}} - {{loginCount}} + {{sessions}} + {{shortSessions}} {{offTime}} {{lastLogout}} {{lastStart}} @@ -85,14 +91,14 @@ {{lang_user}} - {{lang_totalLogins}} + {{lang_sessions}} {{#perUser}} {{user}} - {{loginCount}} + {{sessions}} {{/perUser}} @@ -105,14 +111,14 @@ {{lang_vm}} - {{lang_totalLogins}} + {{lang_sessions}} {{#perVM}} {{vm}} - {{loginCount}} + {{sessions}} {{/perVM}} diff --git a/modules-available/statistics_reporting/templates/columnChooser.html b/modules-available/statistics_reporting/templates/columnChooser.html index d79f542b..49c9a75d 100644 --- a/modules-available/statistics_reporting/templates/columnChooser.html +++ b/modules-available/statistics_reporting/templates/columnChooser.html @@ -26,7 +26,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}}
-
-
-
-
- - - - - - - - - - - - - {{#perLocation}} - - - - - - - - - {{/perLocation}} - -
{{lang_location}}{{lang_totalTime}}{{lang_medianSessionLength}}{{lang_longSessions}}{{lang_shortSessions}}{{lang_totalOffTime}}
{{location}}{{time_s}}{{medianTime_s}}{{sessions}}{{shortSessions}}{{offTime_s}}
-
-
-
-
- - - - - - - - - - - - - - - - {{#perClient}} - - - - - - - - - - - - {{/perClient}} - -
{{lang_hostname}}{{lang_location}}{{lang_totalTime}}{{lang_medianSessionLength}}{{lang_longSessions}}{{lang_shortSessions}}{{lang_totalOffTime}}{{lang_clientLogout}}{{lang_clientStart}}
{{hostname}}{{location}}{{time_s}}{{medianTime_s}}{{sessions}}{{shortSessions}}{{offTime_s}}{{lastLogout_s}}{{lastStart_s}}
-
-
-
-
- - - - - - - - - {{#perUser}} - - - - - {{/perUser}} - -
{{lang_user}}{{lang_sessions}}
{{user}}{{sessions}}
-
-
-
-
- - - - - - - - - {{#perVM}} - - - - - {{/perVM}} - -
{{lang_vm}}{{lang_sessions}}
{{vm}}{{sessions}}
-
-
-
+ + + + + + + + + + + + + + + + + + + + + +
{{lang_totalTime}}{{lang_medianSessionLength}}{{lang_longSessions}}{{lang_shortSessions}}{{lang_overallOfftime}}
{{lang_total}}{{time_s}}{{medianTime_s}}{{sessions}}{{shortSessions}}{{totalOfftime_s}}
+ + + + + + + + + + + + + {{#perLocation}} + + + + + + + + + {{/perLocation}} + +
{{lang_location}}{{lang_totalTime}}{{lang_medianSessionLength}}{{lang_longSessions}}{{lang_shortSessions}}{{lang_totalOffTime}}
{{location}}{{time_s}}{{medianTime_s}}{{sessions}}{{shortSessions}}{{offTime_s}}
+ + + + + + + + + + + + + + + + {{#perClient}} + + + + + + + + + + + + {{/perClient}} + +
{{lang_hostname}}{{lang_location}}{{lang_totalTime}}{{lang_medianSessionLength}}{{lang_longSessions}}{{lang_shortSessions}}{{lang_totalOffTime}}{{lang_clientLogout}}{{lang_clientStart}}
{{hostname}}{{location}}{{time_s}}{{medianTime_s}}{{sessions}}{{shortSessions}}{{offTime_s}}{{lastLogout_s}}{{lastStart_s}}
+ + + + + + + + + {{#perUser}} + + + + + {{/perUser}} + +
{{lang_user}}{{lang_sessions}}
{{user}}{{sessions}}
+ + + + + + + + + {{#perVM}} + + + + + {{/perVM}} + +
{{lang_vm}}{{lang_sessions}}
{{vm}}{{sessions}}
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 @@
- +
@@ -38,7 +38,6 @@
-