[statistics_reporting] improved readability, added hashed values to queries

2 files changed, 27 insertions, 27 deletions

diff --git a/modules-available/statistics_reporting/inc/statisticreporting.inc.php b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
index 39aa9377..c209c5ef 100644
--- a/modules-available/statistics_reporting/inc/statisticreporting.inc.php
+++ b/modules-available/statistics_reporting/inc/statisticreporting.inc.php
@@ -4,11 +4,11 @@
 class StatisticReporting
 {
 
-	// Client Data: Name, Time Online, Median Time Online, Time Offline, last start, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location
+	// Client Data: Name, Time Online, Median Time Online, Time Offline, last start, last logout, Last Time Booted, Number of Sessions > 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, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName 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'
+		$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
@@ -24,11 +24,11 @@ class StatisticReporting
 		return $res;
 	}
 
-	// Location Data: Name, Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec
+	// 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, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM (
-													SELECT IFNULL(location.locationname, '$notassigned') 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'
+		$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 
@@ -45,9 +45,9 @@ class StatisticReporting
 		return $res;
 	}
 
-	// User Data: Name, Number of Logins
+	// User Data: Name, Name(anonymized), Number of Logins
 	public static function getUserStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
-		$res = Database::simpleQuery("SELECT username, COUNT(*) AS 'count' 
+		$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))
@@ -58,7 +58,7 @@ class StatisticReporting
 
 	// Virtual Machine Data: Name, Number of Usages
 	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) 
+		$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");
@@ -74,7 +74,7 @@ class StatisticReporting
 
 	// Total Data(2): Time Offline
 	public static function getTotalOfflineStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) {
-		$res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED))
+		$res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED)) AS timeOff
 												FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable");
 		return $res;
 	}
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index c309bfb6..e2c76b09 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -33,45 +33,45 @@ class Page_Statistics_Reporting extends Page
 
 		// total time online, average time online, total  number of logins
 		$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]);
+		$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_NUM);
-		$data = array_merge($data, array('totalOfftime' => StatisticReporting::formatSeconds($row[0])));
+		$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_NUM)) {
-			$median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row[2]));
-			$data['perLocation'][] = array('location' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1],
-				'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'sessions' => $row[4], 'shortSessions' => $row[5]);
+		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_NUM)) {
-			$median = StatisticReporting::calcMedian(StatisticReporting::calcMedian($row[2]));
-			$data['perClient'][] = array('hostname' => $row[0], 'time' => StatisticReporting::formatSeconds($row[1]), 'timeInSeconds' => $row[1],
-				'medianTime' => StatisticReporting::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => StatisticReporting::formatSeconds($row[3]), 'offlineTimeInSeconds' => $row[3], 'lastStart' => date(DATE_RSS,$row[4]), 'lastStartUnixtime' => $row[4],
-				'lastLogout' => date(DATE_RSS,$row[5]), 'lastLogoutUnixtime' => $row[5], 'sessions' => $row[6], 'shortSessions' => $row[7], 'locationName' => $row[8]);
+		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_NUM)) {
-			$data['perUser'][] = array('user' => $row[0], 'sessions' => $row[1]);
+		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_NUM)) {
-			$data['perVM'][] = array('vm' => $row[0], 'sessions' => $row[1]);
+		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+			$data['perVM'][] = array('vm' => $row['name'], 'sessions' => $row['count']);
 		}
 
 		Render::addTemplate('columnChooser');