diff options
author | Udo Walter | 2016-11-28 18:27:32 +0100 |
---|---|---|
committer | Udo Walter | 2016-11-28 18:27:32 +0100 |
commit | b87930137acf88936fb541f53ab4dab1697e4d03 (patch) | |
tree | 694dfca308d04f32cc38d217fcac9e9f4900b19d /modules-available/statistics_reporting | |
parent | login count and offline time database queries (diff) | |
download | slx-admin-b87930137acf88936fb541f53ab4dab1697e4d03.tar.gz slx-admin-b87930137acf88936fb541f53ab4dab1697e4d03.tar.xz slx-admin-b87930137acf88936fb541f53ab4dab1697e4d03.zip |
new ui
Diffstat (limited to 'modules-available/statistics_reporting')
6 files changed, 246 insertions, 212 deletions
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 @@ -<div> - <table class="table table-condensed table-striped"> - <tr> - <th>{{lang_totalLogins}}</th> - </tr> - <tr> - <td>{{totalLogins}}</td> - </tr> - </table> -</div> - -<div> - <table class="table table-condensed table-striped"> - <tr> - <th>{{lang_overallOfftime}}</th> - </tr> - <tr> - <td>{{totalOfftime}}</td> - </tr> - </table> -</div> - - - -<div class="row"> - <div class="col-md-3"> - <table class="table table-condensed"> - <tr> - <th>{{lang_client}}</th> - <th>{{lang_totalOffTime}}</th> - </tr> - {{#totalOfflineTimeClient}} - <tr> - <td>{{client}}</td> - <td>{{offTime}}</td> - </tr> - {{/totalOfflineTimeClient }} - </table> +<div class="container-fluid"> + <div class="row"> + <div class="col-md-12"> + <table id="table-total" class="table table-condensed table-striped"> + <thead> + <tr> + <th class="text-left col-md-2"></th> + <th class="text-left column-totaltime">{{lang_totalTime}}</th> + <th class="text-left column-avgtime">{{lang_avgSessionLength}}</th> + <th class="text-left column-logincount">{{lang_totalLogins}}</th> + <th class="text-left column-timeoffline">{{lang_overallOfftime}}</th> + </tr> + </thead> + <tbody> + <tr> + <th class="text-left">{{lang_total}}</th> + <td class="text-left column-totaltime">{{time}}</td> + <td class="text-left column-avgtime">{{avgTime}}</td> + <td class="text-left column-logincount">{{totalLogins}}</td> + <td class="text-left column-timeoffline">{{totalOfftime}}</td> + </tr> + </tbody> + </table> + </div> </div> - - <div class="col-md-3"> - <table class="table table-condensed"> - <tr> - <th>{{lang_client}}</th> - <th>{{lang_clientLogout}}</th> - <th>{{lang_offTime}}</th> - </tr> - {{#lastLogout}} - <tr> - <td>{{client}}</td> - <td>{{lastlogout}}</td> - <td>{{howLongOff}}</td> - </tr> - {{/lastLogout}} - </table> + <div class="row"> + <div class="col-md-12"> + <table id="table-perlocation" class="table table-condensed table-striped"> + <thead> + <tr> + <th data-sort="string" class="text-left col-md-2">{{lang_location}}</th> + <th data-sort="int" data-sort-default="desc" class="text-left column-totaltime">{{lang_totalTime}}</th> + <th data-sort="int" data-sort-default="desc" class="text-left column-avgtime">{{lang_avgSessionLength}}</th> + <th data-sort="int" data-sort-default="desc" class="text-left column-logincount">{{lang_totalLogins}}</th> + <th data-sort="int" data-sort-default="desc" class="text-left column-timeoffline">{{lang_totalOffTime}}</th> + </tr> + </thead> + <tbody> + {{#perLocation}} + <tr> + <td class="text-left">{{location}}</td> + <td data-sort-value="{{timeInSeconds}}" class="text-left column-totaltime">{{time}}</td> + <td data-sort-value="{{avgTimeInSeconds}}" class="text-left column-avgtime">{{avgTime}}</td> + <td class="text-left column-logincount">{{loginCount}}</td> + <td data-sort-value="{{offlineTimeInSeconds}}" class="text-left column-timeoffline">{{offTime}}</td> + </tr> + {{/perLocation}} + </tbody> + </table> + </div> </div> - - <div class="col-md-3"> - <table class="table table-condensed"> - <tr> - <th>{{lang_client}}</th> - <th>{{lang_clientStart}}</th> - </tr> - {{#lastLogin}} - <tr> - <td>{{client}}</td> - <td>{{lastlogin}}</td> - </tr> - {{/lastLogin}} - </table> + <div class="row"> + <div class="col-md-12"> + <table id="table-perclient" class="table table-condensed table-striped"> + <thead> + <tr> + <th data-sort="string" class="text-left col-md-4">{{lang_hostname}}</th> + <th data-sort="int" data-sort-default="desc" class="text-left column-totaltime">{{lang_totalTime}}</th> + <th data-sort="int" data-sort-default="desc" class="text-left column-avgtime">{{lang_avgSessionLength}}</th> + <th data-sort="int" data-sort-default="desc" class="text-left column-logincount">{{lang_totalLogins}}</th> + <th data-sort="int" data-sort-default="desc" class="text-left column-timeoffline">{{lang_totalOffTime}}</th> + <th data-sort="int" data-sort-default="desc" class="text-left column-lastlogout">{{lang_clientLogout}}</th> + <th data-sort="int" data-sort-default="desc" class="text-left column-laststart">{{lang_clientStart}}</th> + </tr> + </thead> + <tbody> + {{#perClient}} + <tr> + <td class="text-left">{{hostname}}</td> + <td data-sort-value="{{timeInSeconds}}" class="text-left column-totaltime">{{time}}</td> + <td data-sort-value="{{avgTimeInSeconds}}" class="text-left column-avgtime">{{avgTime}}</td> + <td class="text-left column-logincount">{{loginCount}}</td> + <td data-sort-value="{{offlineTimeInSeconds}}" class="text-left column-timeoffline">{{offTime}}</td> + <td data-sort-value="{{lastLogoutUnixtime}}" class="text-left column-lastlogout">{{lastLogout}}</td> + <td data-sort-value="{{lastStartUnixtime}}" class="text-left column-laststart">{{lastStart}}</td> + </tr> + {{/perClient}} + </tbody> + </table> + </div> </div> - - <div class="col-md-3"> - <table class="table table-condensed"> - <tr> - <th>{{lang_room}}</th> - <th>{{lang_totalOffTime}}</th> - </tr> - {{#offTimeRoom}} - <tr> - <td>{{room}}</td> - <td>{{offTime}}</td> - </tr> - {{/offTimeRoom}} - </table> + <div class="row"> + <div class="col-md-12"> + <table id="table-peruser" class="table table-condensed table-striped"> + <thead> + <tr> + <th data-sort="string" class="text-left col-md-4">{{lang_user}}</th> + <th data-sort="int" data-sort-default="desc" class="text-left column-logincount">{{lang_totalLogins}}</th> + </tr> + </thead> + <tbody> + {{#perUser}} + <tr> + <td class="text-left">{{user}}</td> + <td class="text-left column-logincount">{{loginCount}}</td> + </tr> + {{/perUser}} + </tbody> + </table> + </div> </div> -</div> - - - - -<div class="row"> - <div class="col-md-4"> - <table class="table table-condensed"> - <tr> - <th>{{lang_vm}}</th> - <th>{{lang_countLogins}}</th> - </tr> - {{#vmLogins}} - <tr> - <td>{{vmname}}</td> - <td>{{numLogins}}</td> - </tr> - {{/vmLogins}} - </table> + <div class="row"> + <div class="col-md-12"> + <table id="table-pervm" class="table table-condensed table-striped"> + <thead> + <tr> + <th data-sort="string" class="text-left col-md-4">{{lang_vm}}</th> + <th data-sort="int" data-sort-default="desc" class="text-left column-logincount">{{lang_totalLogins}}</th> + </tr> + </thead> + <tbody> + {{#perVM}} + <tr> + <td class="text-left">{{vm}}</td> + <td class="text-left column-logincount">{{loginCount}}</td> + </tr> + {{/perVM}} + </tbody> + </table> + </div> </div> +</div> - <div class="col-md-4"> - <table class="table table-condensed table-striped"> - <tr> - <th>{{lang_client}}</th> - <th>{{lang_countLogins}}</th> - </tr> - {{#machineLogins}} - <tr> - <td>{{client}}</td> - <td>{{numLogins}}</td> - </tr> - {{/machineLogins}} - </table> - </div> - <div class="col-md-4"> - <table class="table table-condensed table-striped"> - <tr> - <th>{{lang_user}}</th> - <th>{{lang_countLogins}}</th> - </tr> - {{#userLogins}} - <tr> - <td>{{username}}</td> - <td>{{numLogins}}</td> - </tr> - {{/userLogins}} - </table> - </div> -</div> 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 @@ +<div class="container-fluid"> + <div class="row top-row form-inline"> + <div class="col-md-12 "> + <select id="select-table" onchange="chooseTable(this.value)" class="form-control" style="width:160px"> + <option value="total" selected="selected">{{lang_total}}</option> + <option value="perlocation">{{lang_perlocation}}</option> + <option value="perclient">{{lang_perclient}}</option> + <option value="peruser">{{lang_peruser}}</option> + <option value="pervm">{{lang_pervm}}</option> + </select> + <div class="btn-group"> + <button id="button-totaltime" type="button" class="column-toggle btn btn-primary" onclick="toggleButton('totaltime')">{{lang_totalTime}}</button> + <button id="button-avgtime" type="button" class="column-toggle btn btn-primary" onclick="toggleButton('avgtime')">{{lang_avgSessionLength}}</button> + <button id="button-logincount" type="button" class="column-toggle btn btn-primary" onclick="toggleButton('logincount')">{{lang_totalLogins}}</button> + <button id="button-timeoffline" type="button" class="column-toggle btn btn-primary" onclick="toggleButton('timeoffline')">{{lang_totalOffTime}}</button> + <button id="button-lastlogout" type="button" class="column-toggle btn btn-primary" onclick="toggleButton('lastlogout')">{{lang_clientLogout}}</button> + <button id="button-laststart" type="button" class="column-toggle btn btn-primary" onclick="toggleButton('laststart')">{{lang_clientStart}}</button> + </div> + </div> + </div> +</div> + + +<script type="application/javascript"> + document.addEventListener("DOMContentLoaded", function () { + chooseTable($('#select-table').val()); + $("table").stupidtable(); + }); + + function chooseTable(v) { + $("[id^=table-]").hide(); + $('#table-'+v).show(); + } + + function toggleButton(v) { + $button = $('#button-'+v); + if($button.hasClass('btn-default')) { + $button.removeClass('btn-default'); + $button.addClass('btn-primary'); + $('.column-'+v).show() + } else { + $button.removeClass('btn-primary'); + $button.addClass('btn-default'); + $('.column-'+v).hide() + } + } +</script>
\ No newline at end of file |