From 9ccef127acd6dca0971e12726b019e2beaf7e4ce Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Thu, 12 Jan 2017 12:41:21 +0100 Subject: [statistics_reporting] moved some functions to a new class --- .../statistics_reporting/inc/getdata.inc.php | 98 ++++++++++++ .../statistics_reporting/inc/queries.inc.php | 153 ++++++++++++++++++ .../inc/statisticreporting.inc.php | 175 --------------------- .../statistics_reporting/page.inc.php | 55 +------ 4 files changed, 259 insertions(+), 222 deletions(-) create mode 100644 modules-available/statistics_reporting/inc/getdata.inc.php create mode 100644 modules-available/statistics_reporting/inc/queries.inc.php delete mode 100644 modules-available/statistics_reporting/inc/statisticreporting.inc.php (limited to 'modules-available') diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php new file mode 100644 index 00000000..a1df66d0 --- /dev/null +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -0,0 +1,98 @@ +fetch(PDO::FETCH_ASSOC); + $data = array('time' => self::formatSeconds($row['sum']), 'medianTime' => self::formatSeconds(self::calcMedian($row['median'])), 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); + + //total time offline + $res = Queries::getTotalOfflineStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $row = $res->fetch(PDO::FETCH_ASSOC); + $data = array_merge($data, array('totalOfftime' => self::formatSeconds($row['timeOff']))); + + return $data; + } + + // per location + public static function perLocation($anonymize = false) { + $res = Queries::getLocationStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $data = array(); + $loc = $anonymize ? 'locHash' : 'locName'; + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $median = self::calcMedian(self::calcMedian($row['medianTime'])); + $data[] = array('location' => $row[$loc], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'], + 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); + } + return $data; + } + + // per client + public static function perClient($anonymize = false) { + $res = Queries::getClientStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $data = array(); + $name = $anonymize ? 'clientHash' : 'hostname'; + $loc = $anonymize ? 'locHash' : 'locName'; + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $median = self::calcMedian(self::calcMedian($row['medianTime'])); + $data[] = array('hostname' => $row[$name], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'], + 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'lastStart' => date(DATE_RSS,$row['lastStart']), 'lastStartUnixtime' => $row['lastStart'], + 'lastLogout' => date(DATE_RSS,$row['lastLogout']), 'lastLogoutUnixtime' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row[$loc]); + } + return $data; + } + + // per user + public static function perUser($anonymize = false) { + $res = Queries::getUserStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $data = array(); + $user = $anonymize ? 'userHash' : 'name'; + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $data[] = array('user' => $row['name'], 'sessions' => $row['count']); + } + return $data; + } + + + // per vm + public static function perVM() { + $res = Queries::getVMStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $data = array(); + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $data[] = array('vm' => $row['name'], 'sessions' => $row['count']); + } + return $data; + } + + + + // Format $seconds into ".d .h .m .s" format (day, hour, minute, second) + private static function formatSeconds($seconds) + { + return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s'; + } + + // Calculate Median + private 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); + } +} \ No newline at end of file diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php new file mode 100644 index 00000000..23afceed --- /dev/null +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -0,0 +1,153 @@ + 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized) + public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); + $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(locId) AS locHash, MD5(t1.uuid) AS clientHash FROM ( + SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' + FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable + INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid + LEFT JOIN location ON machine.locationid = location.locationid + GROUP BY machine.machineuuid + ) 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, $cutOff)." offlineTable + INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid + GROUP BY machine.machineuuid + ) t2 + ON t1.uuid = t2.uuid"); + return $res; + } + + // Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec + public static function getLocationStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); + $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(locId) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( + SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions' + FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable + INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid + LEFT JOIN location ON machine.locationid = location.locationid + GROUP BY location.locationname + ) t1 + INNER JOIN ( + SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' + FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable + INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid + LEFT JOIN location ON machine.locationid = location.locationid + GROUP BY location.locationname + ) t2 + ON t1.locName = t2.locName"); + return $res; + } + + // User Data: Name, Name(anonymized), Number of Logins + public static function getUserStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(username)) AS userHash, COUNT(*) AS 'count' + FROM statistic + WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) + AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + GROUP BY username + ORDER BY 2 DESC"); + return $res; + } + + // Virtual Machine Data: Name, Number of Usages + public static function getVMStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + $res = Database::simpleQuery("SELECT data AS name, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) + AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + GROUP BY data + ORDER BY 2 DESC"); + return $res; + } + + //Total Data: Time Online, Median Time Online, Number of Sessions > 60Sec, Number of Sessions < 60Sec + public static function getOverallStatistics ($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + $res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, SUM(sessionTable.length >= 60) AS longSessions, SUM(sessionTable.length < 60) AS shortSessions + FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable"); + return $res; + } + + // Total Data(2): Time Offline + public static function getTotalOfflineStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + $res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED)) AS timeOff + FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable"); + return $res; + } + + // query string which provides table with time-cutoff and time-bounds + private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound, $cutOff) + { + $lowerFormat = "'%y-%m-%d $lowerTimeBound:00:00'"; + $upperFormat = "'%y-%m-%d ".($upperTimeBound-1).":59:59'"; + $queryString = " + select + + @startLower := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat)), + @startUpper := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)), + @endLower := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)), + @endUpper := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat)), + + (CAST(data AS SIGNED) + - IF( + dateline > @startUpper, + UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat) + INTERVAL 1 DAY) - dateline, + IF( + dateline < @startLower, + @startLower - dateline, + 0 + ) + ) + - IF( + dateline+data > @endUpper, + dateline+data - (@endUpper + 1), + IF( + dateline+data < @endLower, + dateline+data - (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat) - INTERVAL 1 DAY) + 1), + 0 + ) + ) + - ( TO_DAYS(FROM_UNIXTIME(dateline+data, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(dateline, '%y-%m-%d')) + - 2 + + IF(dateline <= @startUpper, 1, 0) + + IF(dateline+data >= @endLower, 1, 0) + ) * ((24 - ($upperTimeBound - $lowerTimeBound)) * 3600) + + - IF( + @leftBound := IF(dateline <= @startUpper, @startUpper, UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat) + INTERVAL 1 DAY)) + < @rightBound := IF(dateline+data >= @endLower, @endLower, UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat) - INTERVAL 1 DAY)), + IF( + @timeDiff := ( + (date_format(from_unixtime(@leftBound), '%H') - + date_format(convert_tz(from_unixtime(@leftBound), @@session.time_zone, '+00:00'), '%H') + 24) % 24 + - + (date_format(from_unixtime(@rightBound), '%H') - + date_format(convert_tz(from_unixtime(@rightBound), @@session.time_zone, '+00:00'), '%H') + 24) % 24) = 1 + AND ($lowerTimeBound >= 2 OR $upperTimeBound <= 2), + 3600, + IF(@timeDiff = -1 AND ($lowerTimeBound >= 3 OR $upperTimeBound <= 3), -3600, 0) + ), + 0 + ) + + ) as 'length', + dateline, + data, + machineuuid + + from statistic + where dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) and typeid = '$typeid' + and ( + (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat)) <= dateline and dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat))) + or + (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)) <= dateline+data and dateline+data <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat))) + ) + "; + return "(".$queryString.")"; + } +} + diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php deleted file mode 100644 index c209c5ef..00000000 --- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php +++ /dev/null @@ -1,175 +0,0 @@ - 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized) - public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { - $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); - $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(locId) AS locHash, MD5(t1.uuid) AS clientHash FROM ( - SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' - FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable - INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid - LEFT JOIN location ON machine.locationid = location.locationid - GROUP BY machine.machineuuid - ) 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, $cutOff)." offlineTable - INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid - GROUP BY machine.machineuuid - ) t2 - ON t1.uuid = t2.uuid"); - return $res; - } - - // Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec - public static function getLocationStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { - $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); - $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(locId) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( - SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions' - FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable - INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid - LEFT JOIN location ON machine.locationid = location.locationid - GROUP BY location.locationname - ) t1 - INNER JOIN ( - SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' - FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable - INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid - LEFT JOIN location ON machine.locationid = location.locationid - GROUP BY location.locationname - ) t2 - ON t1.locName = t2.locName"); - return $res; - } - - // User Data: Name, Name(anonymized), Number of Logins - public static function getUserStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { - $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(username)) AS userHash, COUNT(*) AS 'count' - FROM statistic - WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) - AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) - GROUP BY username - ORDER BY 2 DESC"); - return $res; - } - - // Virtual Machine Data: Name, Number of Usages - public static function getVMStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { - $res = Database::simpleQuery("SELECT data AS name, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) - AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) - GROUP BY data - ORDER BY 2 DESC"); - return $res; - } - - //Total Data: Time Online, Median Time Online, Number of Sessions > 60Sec, Number of Sessions < 60Sec - public static function getOverallStatistics ($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { - $res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, SUM(sessionTable.length >= 60) AS longSessions, SUM(sessionTable.length < 60) AS shortSessions - FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable"); - return $res; - } - - // Total Data(2): Time Offline - public static function getTotalOfflineStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { - $res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED)) AS timeOff - FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable"); - return $res; - } - - // Format $seconds into ".d .h .m .s" format (day, hour, minute, second) - 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'; - } - - // Calculate Median - 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); - } - - // query string which provides table with time-cutoff and time-bounds - private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound, $cutOff) - { - $lowerFormat = "'%y-%m-%d $lowerTimeBound:00:00'"; - $upperFormat = "'%y-%m-%d ".($upperTimeBound-1).":59:59'"; - $queryString = " - select - - @startLower := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat)), - @startUpper := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)), - @endLower := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)), - @endUpper := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat)), - - (CAST(data AS SIGNED) - - IF( - dateline > @startUpper, - UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat) + INTERVAL 1 DAY) - dateline, - IF( - dateline < @startLower, - @startLower - dateline, - 0 - ) - ) - - IF( - dateline+data > @endUpper, - dateline+data - (@endUpper + 1), - IF( - dateline+data < @endLower, - dateline+data - (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat) - INTERVAL 1 DAY) + 1), - 0 - ) - ) - - ( TO_DAYS(FROM_UNIXTIME(dateline+data, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(dateline, '%y-%m-%d')) - - 2 - + IF(dateline <= @startUpper, 1, 0) - + IF(dateline+data >= @endLower, 1, 0) - ) * ((24 - ($upperTimeBound - $lowerTimeBound)) * 3600) - - - IF( - @leftBound := IF(dateline <= @startUpper, @startUpper, UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat) + INTERVAL 1 DAY)) - < @rightBound := IF(dateline+data >= @endLower, @endLower, UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat) - INTERVAL 1 DAY)), - IF( - @timeDiff := ( - (date_format(from_unixtime(@leftBound), '%H') - - date_format(convert_tz(from_unixtime(@leftBound), @@session.time_zone, '+00:00'), '%H') + 24) % 24 - - - (date_format(from_unixtime(@rightBound), '%H') - - date_format(convert_tz(from_unixtime(@rightBound), @@session.time_zone, '+00:00'), '%H') + 24) % 24) = 1 - AND ($lowerTimeBound >= 2 OR $upperTimeBound <= 2), - 3600, - IF(@timeDiff = -1 AND ($lowerTimeBound >= 3 OR $upperTimeBound <= 3), -3600, 0) - ), - 0 - ) - - ) as 'length', - dateline, - data, - machineuuid - - from statistic - where dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) and typeid = '$typeid' - and ( - (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat)) <= dateline and dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat))) - or - (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)) <= dateline+data and dateline+data <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat))) - ) - "; - return "(".$queryString.")"; - } -} - diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php index e2c76b09..82c03acc 100644 --- a/modules-available/statistics_reporting/page.inc.php +++ b/modules-available/statistics_reporting/page.inc.php @@ -24,55 +24,16 @@ class Page_Statistics_Reporting extends Page { // timespan you want to see = Days selected * seconds per Day (86400) // default = 14 days - $cutOff = Request::get('cutoff', 14, 'int') - 1; + GetData::$cutOff = Request::get('cutoff', 14, 'int') - 1; + GetData::$lowerTimeBound = Request::get('lower', 0, 'int'); + GetData::$upperTimeBound = Request::get('upper', 24, 'int'); - $lowerTimeBound = Request::get('lower', 0, 'int'); + $data = array_merge(GetData::total(), array('perLocation' => array(), 'perClient' => array(), 'perUser' => array(), 'perVM' => array())); + $data['perLocation'] = GetData::perLocation(); + $data['perClient'] = GetData::perClient(); + $data['perUser'] = GetData::perUser(); + $data['perVM'] = GetData::perVM(); - $upperTimeBound = Request::get('upper', 24, 'int'); - - - // total time online, average time online, total number of logins - $res = StatisticReporting::getOverallStatistics($cutOff, $lowerTimeBound, $upperTimeBound); - $row = $res->fetch(PDO::FETCH_ASSOC); - $data = array('time' => StatisticReporting::formatSeconds($row['sum']), 'medianTime' => StatisticReporting::formatSeconds(StatisticReporting::calcMedian($row['median'])), 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); - - //total time offline - $res = StatisticReporting::getTotalOfflineStatistics($cutOff, $lowerTimeBound, $upperTimeBound); - $row = $res->fetch(PDO::FETCH_ASSOC); - $data = array_merge($data, array('totalOfftime' => StatisticReporting::formatSeconds($row['timeOff']))); - - // per location - $res = StatisticReporting::getLocationStatistics($cutOff, $lowerTimeBound, $upperTimeBound); - $data[] = array('perLocation' => array()); - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row['medianTime'])); - $data['perLocation'][] = array('location' => $row['locName'], 'time' => StatisticReporting::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'], - 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); - } - - // per client - $res = StatisticReporting::getClientStatistics($cutOff, $lowerTimeBound, $upperTimeBound); - $data[] = array('perClient' => array()); - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row['medianTime'])); - $data['perClient'][] = array('hostname' => $row['clientName'], 'time' => StatisticReporting::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'], - 'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'lastStart' => date(DATE_RSS,$row['lastStart']), 'lastStartUnixtime' => $row['lastStart'], - 'lastLogout' => date(DATE_RSS,$row['lastLogout']), 'lastLogoutUnixtime' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row['locName']); - } - - // per user - $res = StatisticReporting::getUserStatistics($cutOff, $lowerTimeBound, $upperTimeBound); - $data[] = array('perUser' => array()); - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $data['perUser'][] = array('user' => $row['name'], 'sessions' => $row['count']); - } - - // per vm - $res = StatisticReporting::getVMStatistics($cutOff, $lowerTimeBound, $upperTimeBound); - $data[] = array('perVM' => array()); - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $data['perVM'][] = array('vm' => $row['name'], 'sessions' => $row['count']); - } Render::addTemplate('columnChooser'); Render::addTemplate('_page', $data); -- cgit v1.2.3-55-g7522