[statistics_reporting] added time bounds slider

1 files changed, 97 insertions, 22 deletions

diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index 69a4d516..7652bbdc 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -7,14 +7,16 @@ 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 (
-													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+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
+													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
 													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+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
+													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");
 		return $res;
@@ -22,17 +24,20 @@ class StatisticReporting
 
 	public static function getLocationStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
 		$queryTime = time() - $cutOffTimeInSeconds;
+
 		$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+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
+													SELECT 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 
+														INNER JOIN location ON machine.locationid = location.locationid 
+													WHERE sessionTable.dateline >= $queryTime
 													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+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
+											 		SELECT 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 
+														INNER JOIN location ON machine.locationid = location.locationid 
+													WHERE offlineTable.dateline >= $queryTime
 													GROUP By location.locationname
 												) t2 ON t1.locName = t2.locName");
 		return $res;
@@ -57,18 +62,17 @@ class StatisticReporting
 	// AND(betweenBounds OR <lower but goes into bound OR > upper but begins in bound					)
 	public static function getOverallStatistics ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
 		$queryTime = time() - $cutOffTimeInSeconds;
-		$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+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
-																											)
-												
-												");
+		$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");
 		return $res;
 	}
 
 	public static function getTotalOfflineStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
 		$queryTime = time() - $cutOffTimeInSeconds;
-		$res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)) FROM statistic WHERE typeid='~offline-length' AND dateline>=$queryTime
-												AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))");
+		$res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED))
+												FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound)." offlineTable
+												WHERE offlineTable.dateline>=$queryTime");
 		return $res;
 	}
 
@@ -77,4 +81,75 @@ class StatisticReporting
 		return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s';
 	}
 
-}
\ No newline at end of file
+
+	private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound)
+	{
+		$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 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.")";
+	}
+
+
+}
+