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(-) (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 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