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