From 5ddedfac17caf4a994da1f52b49ceb9fffc834bd Mon Sep 17 00:00:00 2001 From: Christian Hofmaier Date: Thu, 22 Dec 2016 13:53:36 +0100 Subject: [statistics_reporting] added short sessions to Queries --- .../inc/statisticreporting.inc.php | 82 +++++++++++----------- 1 file changed, 41 insertions(+), 41 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 c9fe1c64..9212a09c 100644 --- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php +++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php @@ -6,40 +6,63 @@ 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 FROM ( + $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' 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 - ) t1 INNER JOIN ( + ) t1 + INNER JOIN ( SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum', MAX(offlineTable.dateline) AS 'lastStart' FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid WHERE offlineTable.dateline>=$queryTime GROUP BY machine.machineuuid - ) t2 ON t1.uuid = t2.uuid"); + ) 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 + "); return $res; } + // IFNULL(location.locationname, '') - emptry string can be replaced with anything (name of the null-ids in the table) public static function getLocationStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT t1.locName, timeSum, avgTime, offlineSum, loginCount FROM ( + $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' 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 - ) t1 INNER JOIN ( + GROUP BY location.locationname + ) t1 + INNER JOIN ( SELECT IFNULL(location.locationname, '') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid WHERE offlineTable.dateline >= $queryTime - GROUP By location.locationname - ) t2 ON t1.locName = t2.locName"); + 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"); return $res; } @@ -62,12 +85,18 @@ class StatisticReporting return $res; } - // AND(betweenBounds OR upper but begins in bound ) public static function getOverallStatistics ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)), AVG(CAST(sessionTable.length AS UNSIGNED)), COUNT(*) - FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable - WHERE sessionTable.dateline>=$queryTime AND sessionTable.data >= 60"); + $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 + FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable + WHERE sessionTable.dateline>=$queryTime AND sessionTable.data >= 60 + ) t1 + INNER JOIN + ( SELECT COUNT(sessionTable.length) as countShort + FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound)." sessionTable + WHERE sessionTable.dateline>=$queryTime AND sessionTable.data < 60 + ) t2"); return $res; } @@ -79,35 +108,6 @@ class StatisticReporting return $res; } - // TODO: Short Sessions in Tabelle einbauen - public static function getShortSessionsTotal($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { - $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT COUNT(*) FROM statistic WHERE typeid='~session-length' AND data < 60 AND dateline>=$queryTime - AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))"); - return $res; - } - - public static function getShortSessionsPerLocation ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { - $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT location.locationname, COUNT(*) - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - INNER JOIN location ON machine.locationid = location.locationid - WHERE statistic.typeid='~session-length' AND statistic.data < 60 AND dateline>=$queryTime - AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) - GROUP BY location.locationname"); - return $res; - } - - public static function getShortSessionsPerClient ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) { - $queryTime = time() - $cutOffTimeInSeconds; - $res = Database::simpleQuery("SELECT machine.hostname, COUNT(*) - FROM statistic INNER JOIN machine ON statistic.machineuuid = machine.machineuuid - WHERE statistic.typeid='~session-length' AND statistic.data < 60 AND dateline>=$queryTime - AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) - GROUP BY machine.hostname"); - 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'; -- cgit v1.2.3-55-g7522