[statistics_reporting] changed cutoffs

4 files changed, 28 insertions, 36 deletions

diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index 7b1dcfdc..626aa290 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -4,17 +4,16 @@
 class StatisticReporting
 {
 
-	public static function getClientStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
-		$queryTime = time() - $cutOffTimeInSeconds;
+	public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
 		$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', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout'
-													FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $queryTime)." sessionTable
+													FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable
 														INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
 													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, $queryTime)." offlineTable
+													FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable
 														INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
 													GROUP BY machine.machineuuid
 												) 	t2 
@@ -23,19 +22,17 @@ class StatisticReporting
 	}
 
 	// 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;
-
+	public static function getLocationStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
 		$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', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
-													FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $queryTime)." sessionTable
+													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, '') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum'
-													FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $queryTime)." offlineTable
+													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
@@ -45,35 +42,31 @@ class StatisticReporting
 	}
 
 	// logins between bounds
-	public static function getUserStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
-		$queryTime = time() - $cutOffTimeInSeconds;
+	public static function getUserStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
 		$res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' 
 												FROM statistic 
-												WHERE typeid='.vmchooser-session-name' AND dateline>=$queryTime 
+												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;
 	}
 
 	// vm logins between bounds
-	public static function getVMStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
-		$queryTime = time() - $cutOffTimeInSeconds;
-		$res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline>=$queryTime 
+	public static function getVMStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
+		$res = Database::simpleQuery("SELECT data, 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;
 	}
 
-	public static function getOverallStatistics ($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
-		$queryTime = time() - $cutOffTimeInSeconds;
+	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, $queryTime)." sessionTable");
+											 		FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable");
 		return $res;
 	}
 
-	public static function getTotalOfflineStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
-		$queryTime = time() - $cutOffTimeInSeconds;
+	public static function getTotalOfflineStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
 		$res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED))
-												FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $queryTime)." offlineTable");
+												FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable");
 		return $res;
 	}
 
@@ -97,13 +90,13 @@ class StatisticReporting
 		return round($median);
 	}
 
-	private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound, $cutoffTime)
+	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)),
@@ -157,7 +150,7 @@ class StatisticReporting
 			machineuuid
 			
 			from statistic
-			where dateline >= $cutoffTime and typeid = '$typeid' and (
+			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)))
 	 		)
diff --git a/modules-available/statistics_reporting/lang/de/template-tags.json b/modules-available/statistics_reporting/lang/de/template-tags.json
index 68b68250..207949a9 100644
--- a/modules-available/statistics_reporting/lang/de/template-tags.json
+++ b/modules-available/statistics_reporting/lang/de/template-tags.json
@@ -20,8 +20,8 @@
     "lang_location": "Raum",
     "lang_medianSessionLength": "Mittlere Sitzungsdauer",
     "lang_totalTime": "Gesamte Zeit",
-    "lang_last1": "Letzten 24 Stunden",
-    "lang_last2": "Letzten 48 Stunden",
+    "lang_last1": "Heute",
+    "lang_last2": "Letzten 2 Tage",
     "lang_last7": "Letzten 7 Tage",
     "lang_last14": "Letzten 14 Tage",
     "lang_last30": "Letzten 30 Tage",
diff --git a/modules-available/statistics_reporting/lang/en/template-tags.json b/modules-available/statistics_reporting/lang/en/template-tags.json
index b9e0c61d..e9430960 100644
--- a/modules-available/statistics_reporting/lang/en/template-tags.json
+++ b/modules-available/statistics_reporting/lang/en/template-tags.json
@@ -20,8 +20,8 @@
     "lang_location": "Location",
     "lang_medianSessionLength": "Median Session Length",
     "lang_totalTime": "Total Time",
-    "lang_last1": "Last 24 hours",
-    "lang_last2": "Last 48 hours",
+    "lang_last1": "Today",
+    "lang_last2": "Last 2 days",
     "lang_last7": "Last 7 days",
     "lang_last14": "Last 14 days",
     "lang_last30": "Last 30 days",
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index f0f56e87..a55ba33f 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -24,8 +24,7 @@ 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');
-		$cutOffTimer = $cutOff * 86400;
+		$cutOff = Request::get('cutoff', 14, 'int') - 1;
 
 		$lowerTimeBound = Request::get('lower', 0, 'int');
 
@@ -33,17 +32,17 @@ class Page_Statistics_Reporting extends Page
 
 
 		// total time online, average time online, total  number of logins
-		$res = StatisticReporting::getOverallStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
+		$res = StatisticReporting::getOverallStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
 		$row = $res->fetch(PDO::FETCH_NUM);
 		$data = array('time' =>  StatisticReporting::formatSeconds($row[0]), 'medianTime' =>  StatisticReporting::formatSeconds(StatisticReporting::calcMedian($row[1])), 'sessions' => $row[2], 'shortSessions' => $row[3]);
 
 		//total time offline
-		$res = StatisticReporting::getTotalOfflineStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
+		$res = StatisticReporting::getTotalOfflineStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
 		$row = $res->fetch(PDO::FETCH_NUM);
 		$data = array_merge($data, array('totalOfftime' => StatisticReporting::formatSeconds($row[0])));
 
 		// per location
-		$res = StatisticReporting::getLocationStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
+		$res = StatisticReporting::getLocationStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
 		$data[] = array('perLocation' => array());
 		while ($row = $res->fetch(PDO::FETCH_NUM)) {
 			$median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row[2]));
@@ -52,7 +51,7 @@ class Page_Statistics_Reporting extends Page
 		}
 
 		// per client
-		$res = StatisticReporting::getClientStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
+		$res = StatisticReporting::getClientStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
 		$data[] = array('perClient' => array());
 		while ($row = $res->fetch(PDO::FETCH_NUM)) {
 			$median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row[2]));
@@ -62,14 +61,14 @@ class Page_Statistics_Reporting extends Page
 		}
 
 		// per user
-		$res = StatisticReporting::getUserStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
+		$res = StatisticReporting::getUserStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
 		$data[] = array('perUser' => array());
 		while ($row = $res->fetch(PDO::FETCH_NUM)) {
 			$data['perUser'][] = array('user' => $row[0], 'sessions' => $row[1]);
 		}
 
 		// per vm
-		$res = StatisticReporting::getVMStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
+		$res = StatisticReporting::getVMStatistics($cutOff, $lowerTimeBound, $upperTimeBound);
 		$data[] = array('perVM' => array());
 		while ($row = $res->fetch(PDO::FETCH_NUM)) {
 			$data['perVM'][] = array('vm' => $row[0], 'sessions' => $row[1]);