diff options
Diffstat (limited to 'modules-available/statistics_reporting')
4 files changed, 83 insertions, 45 deletions
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 @@ +<?php + + +class StatisticReporting +{ + public static function getClientStatistics() { + $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' 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"); + return $res; + } + + public static function getLocationStatistics() { + $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"); + return $res; + } + + public static function getUserStatistics() { + $res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY username ORDER BY 2 DESC"); + return $res; + } + + public static function getVMStatistics() { + $res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' GROUP BY data ORDER BY 2 DESC"); + return $res; + } + + public static function getOverallStatistics () { + $res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)), AVG(CAST(data AS UNSIGNED)), COUNT(*) FROM statistic WHERE typeid = '~session-length'"); + return $res; + } + + public static function getTotalOffline() { + $res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)) FROM statistic WHERE typeid='~offline-length'"); + 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'; + } + +}
\ 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 2a9688b2..db3e9f11 100644 --- a/modules-available/statistics_reporting/lang/de/template-tags.json +++ b/modules-available/statistics_reporting/lang/de/template-tags.json @@ -4,10 +4,10 @@ "lang_client": "Client", "lang_vm": "VM", "lang_totalLogins": "Gesamt Logins", - "lang_overallOfftime": "Gesamtzeit Offline", + "lang_overallOfftime": "Insgesamte Zeit Offline", "lang_totalOffTime": "Gesamtzeit Offline", - "lang_clientLogout": "Letzter Logout", - "lang_clientStart": "Letzter Client Start", + "lang_clientLogout": "Letzter VM Logout", + "lang_clientStart": "Letzter Client Boot", "lang_room": "Raum", "lang_offTime": "Zeit seit Logout", "lang_total": "Gesamt", diff --git a/modules-available/statistics_reporting/lang/en/template-tags.json b/modules-available/statistics_reporting/lang/en/template-tags.json index cfbf3ad8..7a7ff13a 100644 --- a/modules-available/statistics_reporting/lang/en/template-tags.json +++ b/modules-available/statistics_reporting/lang/en/template-tags.json @@ -5,9 +5,9 @@ "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_totalOffTime": "Total time offline", + "lang_clientLogout": "Last VM Logout", + "lang_clientStart": "Last Client Boot", "lang_room": "Room", "lang_offTime": "Time since offline", "lang_total": "Total", diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php index 6d3c2ae3..aaf191df 100644 --- a/modules-available/statistics_reporting/page.inc.php +++ b/modules-available/statistics_reporting/page.inc.php @@ -24,79 +24,53 @@ class Page_Statistics_Reporting extends Page // 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? + // TODO: session-length liefert 3000 Einträge mehr als vmchooser-session? (logins ohne ausgewählter VM -> 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'; - } } |