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(-) (limited to 'modules-available/statistics_reporting') 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