[statistics_reporting] edited time bound possibility

2 files changed, 35 insertions, 45 deletions

diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index a1a209d8..69a4d516 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -4,83 +4,71 @@
 class StatisticReporting
 {
 
-	public static function getClientStatistics($cutOffTimeInSeconds) {
+	public static function getClientStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
 		$queryTime = time() - $cutOffTimeInSeconds;
-		// time bounds (8,22 means from 8 o clock to 22 o clock)
-		$lowerBound = 8;
-		$upperBound = 22;
 		$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, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound))
+													WHERE typeid = '~session-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
 													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, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound))
+													WHERE typeid = '~offline-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
 													GROUP BY machine.machineuuid
 												) t2 ON t1.uuid = t2.uuid");
 		return $res;
 	}
 
-	public static function getLocationStatistics($cutOffTimeInSeconds) {
+	public static function getLocationStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
 		$queryTime = time() - $cutOffTimeInSeconds;
-		// time bounds (8,22 means from 8 o clock to 22 o clock)
-		$lowerBound = 8;
-		$upperBound = 22;
 		$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, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound))
+													WHERE statistic.typeid = '~session-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
 													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, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound))
+													WHERE statistic.typeid = '~offline-length' AND dateline>=$queryTime AND ((FROM_UNIXTIME(dateline+statistic.data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
 													GROUP By location.locationname
 												) t2 ON t1.locName = t2.locName");
 		return $res;
 	}
 
-	public static function getUserStatistics($cutOffTimeInSeconds) {
+	// logins between bounds
+	public static function getUserStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
 		$queryTime = time() - $cutOffTimeInSeconds;
-		// time bounds (8,22 means from 8 o clock to 22 o clock)
-		$lowerBound = 8;
-		$upperBound = 22;
 		$res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline>=$queryTime 
-												AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound)) GROUP BY username ORDER BY 2 DESC");
+												AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) GROUP BY username ORDER BY 2 DESC");
 		return $res;
 	}
 
-	public static function getVMStatistics($cutOffTimeInSeconds) {
+	// vm logins between bounds
+	public static function getVMStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
 		$queryTime = time() - $cutOffTimeInSeconds;
-		// time bounds (8,22 means from 8 o clock to 22 o clock)
-		$lowerBound = 8;
-		$upperBound = 22;
 		$res = Database::simpleQuery("SELECT data, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline>=$queryTime 
-												AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound)) GROUP BY data ORDER BY 2 DESC");
+												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) {
+	// 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;
-		// time bounds (8,22 means from 8 o clock to 22 o clock)
-		$lowerBound = 8;
-		$upperBound = 22;
 		$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, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound))");
+												AND (((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))
+																											)
+												
+												");
 		return $res;
 	}
 
-	public static function getTotalOfflineStatistics($cutOffTimeInSeconds) {
+	public static function getTotalOfflineStatistics($cutOffTimeInSeconds, $lowerTimeBound = 0, $upperTimeBound = 24) {
 		$queryTime = time() - $cutOffTimeInSeconds;
-		// time bounds (8,22 means from 8 o clock to 22 o clock)
-		$lowerBound = 8;
-		$upperBound = 22;
 		$res = Database::simpleQuery("SELECT SUM(CAST(data AS UNSIGNED)) FROM statistic WHERE typeid='~offline-length' AND dateline>=$queryTime
-												AND ((FROM_UNIXTIME(dateline, '%H')*1 >= $lowerBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperBound))");
+												AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound))");
 		return $res;
 	}
 
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index e009317c..9417d2da 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -1,11 +1,9 @@
 <?php
 
+
 class Page_Statistics_Reporting extends Page
 {
 
-	private $cutOffTimer;
-
-
 	/**
 	 * Called before any page rendering happens - early hook to check parameters etc.
 	 */
@@ -24,24 +22,28 @@ class Page_Statistics_Reporting extends Page
 	 */
 	protected function doRender()
 	{
-		// timespan you want to see = Days selected * seconds per Day
+		// timespan you want to see = Days selected * seconds per Day (86400)
 		// default = 14 days
 		$cutOff = Request::get('cutoff', 14, 'int');
-		$chooserData = array('cutoff' => $cutOff);
-		$this->cutOffTimer = $cutOff * 86400;
+		$cutOffTimer = $cutOff * 86400;
+
+		$lowerTimeBound = Request::get('lower', 0, 'int');
+
+		$upperTimeBound = Request::get('upper', 24, 'int');
 
 
 		// total time online, average time online, total  number of logins
-		$res = StatisticReporting::getOverallStatistics($this->cutOffTimer);
+		$res = StatisticReporting::getOverallStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
 		$row = $res->fetch(PDO::FETCH_NUM);
 		$data = array('time' =>  StatisticReporting::formatSeconds($row[0]), 'avgTime' =>  StatisticReporting::formatSeconds($row[1]), 'totalLogins' => $row[2]);
+
 		//total time offline
-		$res = StatisticReporting::getTotalOfflineStatistics($this->cutOffTimer);
+		$res = StatisticReporting::getTotalOfflineStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
 		$row = $res->fetch(PDO::FETCH_NUM);
 		$data = array_merge($data, array('totalOfftime' => StatisticReporting::formatSeconds($row[0])));
 
 		// per location
-		$res = StatisticReporting::getLocationStatistics($this->cutOffTimer);
+		$res = StatisticReporting::getLocationStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
 		$data[] = array('perLocation' => array());
 		while ($row = $res->fetch(PDO::FETCH_NUM)) {
 			$data['perLocation'][] = array('location' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1],
@@ -49,7 +51,7 @@ class Page_Statistics_Reporting extends Page
 		}
 
 		// per client
-		$res = StatisticReporting::getClientStatistics($this->cutOffTimer);
+		$res = StatisticReporting::getClientStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
 		$data[] = array('perClient' => array());
 		while ($row = $res->fetch(PDO::FETCH_NUM)) {
 			$data['perClient'][] = array('hostname' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1],
@@ -58,20 +60,20 @@ class Page_Statistics_Reporting extends Page
 		}
 
 		// per user
-		$res = StatisticReporting::getUserStatistics($this->cutOffTimer);
+		$res = StatisticReporting::getUserStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
 		$data[] = array('perUser' => array());
 		while ($row = $res->fetch(PDO::FETCH_NUM)) {
 			$data['perUser'][] = array('user' => $row[0], 'loginCount' => $row[1]);
 		}
 
 		// per vm
-		$res = StatisticReporting::getVMStatistics($this->cutOffTimer);
+		$res = StatisticReporting::getVMStatistics($cutOffTimer, $lowerTimeBound, $upperTimeBound);
 		$data[] = array('perVM' => array());
 		while ($row = $res->fetch(PDO::FETCH_NUM)) {
 			$data['perVM'][] = array('vm' => $row[0], 'loginCount' => $row[1]);
 		}
 
-		Render::addTemplate('columnChooser', $chooserData);
+		Render::addTemplate('columnChooser');
 		Render::addTemplate('_page', $data);
 	}
 }