[statistics_reporting] Query rewrite: Use iterative approach

Several simple test cases yielded incorrect results, also
sessions partially overlapping with the selected hours
were not handled properly. Iterative processing of logged events
instead of single humongous unmaintainable SQL query.

1 files changed, 66 insertions, 76 deletions

diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php
index ae4d5aa4..13d39502 100644
--- a/modules-available/statistics_reporting/inc/getdata.inc.php
+++ b/modules-available/statistics_reporting/inc/getdata.inc.php
@@ -10,24 +10,59 @@ class GetData
 	public static $lowerTimeBound = 0;
 	public static $upperTimeBound = 24;
 	public static $salt;
+	private static $TS_LIST = false;
+	private static $SECS_LIST = false;
+
+	private static function fillLocation(&$entry, $anonymize)
+	{
+		$locations = Location::getLocationsAssoc();
+		if ($anonymize) {
+			$entry['locationname'] = md5($entry['locationid'] . self::$salt);
+		} elseif (isset($locations[$entry['locationid']])) {
+			$entry['locationname'] = $locations[$entry['locationid']]['locationname'];
+			$entry['parentlocations'] = array_reduce($locations[$entry['locationid']]['parents'], function ($carry, $item) {
+				return $carry . sprintf("%04d", $item);
+			}) . sprintf("%04d", $entry['locationid']);
+		} else {
+			$entry['locationname'] = Dictionary::translate('notAssigned', true);
+		}
+		if ($anonymize) {
+			unset($entry['locationid']);
+		}
+	}
+
+	private static function addPrintables(&$entry)
+	{
+		if (self::$SECS_LIST === false) {
+			self::$SECS_LIST = ['totalTime', 'totalOffTime', 'totalIdleTime', 'totalSessionTime', 'totalStandbyTime', 'medianSessionLength'];
+		}
+		if (self::$TS_LIST === false) {
+			self::$TS_LIST = ['lastStart', 'lastLogout'];
+		}
+		$perc = isset($entry['totalTime']) && $entry['totalTime'] > 0;
+		foreach (self::$SECS_LIST as $k) {
+			if (isset($entry[$k])) {
+				$entry[$k . '_s'] = self::formatSeconds($entry[$k]);
+				if ($perc && $k !== 'totalTime') {
+					$entry[$k . '_p'] = round($entry[$k] / $entry['totalTime'] * 100);
+				}
+			}
+		}
+		foreach (self::$TS_LIST as $k) {
+			if (isset($entry[$k])) {
+				$entry[$k . '_s'] = Util::prettyTime($entry[$k]);
+			}
+		}
+	}
 
 	// total
 	public static function total($flags = 0) {
 		$printable = 0 !== ($flags & GETDATA_PRINTABLE);
 		// total time online, average time online, total  number of logins
-		$res = Queries::getOverallStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
-		$row = $res->fetch(PDO::FETCH_ASSOC);
-		$data = array('totalTime' =>  $row['sum'], 'medianSessionLength' =>  self::calcMedian($row['median']), 'longSessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']);
-
-		//total time offline
-		$res = Queries::getTotalOfflineStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
-		$row = $res->fetch(PDO::FETCH_ASSOC);
-		$data['totalOffTime'] = $row['timeOff'];
+		$data = Queries::getOverallStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
 
 		if ($printable) {
-			$data["totalTime_s"] = self::formatSeconds($data["totalTime"]);
-			$data["medianSessionLength_s"] = self::formatSeconds($data["medianSessionLength"]);
-			$data["totalOffTime_s"] = self::formatSeconds($data["totalOffTime"]);
+			self::addPrintables($data);
 		}
 		$data['uniqueUsers'] = Queries::getUniqueUserCount(self::$from, self::$to);
 
@@ -38,63 +73,29 @@ class GetData
 	public static function perLocation($flags = 0) {
 		$anonymize = 0 !== ($flags & GETDATA_ANONYMOUS);
 		$printable = 0 !== ($flags & GETDATA_PRINTABLE);
-		$res = Queries::getLocationStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
-		$data = array();
-		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
-			self::nullToZero($row);
-			$median = self::calcMedian(self::calcMedian($row['medianSessionLength']));
-			$entry = array(
-				'location' => ($anonymize ? $row['locHash'] : $row['locName']),
-				'totalTime' => $row['timeSum'],
-				'medianSessionLength' => $median,
-				'totalOffTime' => $row['offlineSum'],
-				'longSessions' => $row['longSessions'],
-				'shortSessions' => $row['shortSessions']
-			);
-			if (!$anonymize) {
-				$entry['locationId'] = $row['locId'];
-			}
+		$data = Queries::getLocationStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
+		foreach ($data as &$entry) {
+			//self::nullToZero($entry);
+			self::fillLocation($entry, $anonymize);
 			if ($printable) {
-				$entry['totalTime_s'] = self::formatSeconds($row['timeSum']);
-				$entry['medianSessionLength_s'] = self::formatSeconds($median);
-				$entry['totalOffTime_s'] = self::formatSeconds($row['offlineSum']);
+				self::addPrintables($entry);
 			}
-			$data[] = $entry;
 		}
 		return $data;
 	}
 
 	// per client
-	public static function perClient($flags = 0) {
+	public static function perClient($flags = 0, $new = false) {
 		$anonymize = 0 !== ($flags & GETDATA_ANONYMOUS);
 		$printable = 0 !== ($flags & GETDATA_PRINTABLE);
-		$res = Queries::getClientStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
-		$data = array();
-		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
-			self::nullToZero($row);
-			$median = self::calcMedian(self::calcMedian($row['medianSessionLength']));
-			$entry = array(
-				'hostname' => ($anonymize ? $row['clientHash'] : $row['clientName']),
-				'totalTime' => $row['timeSum'],
-				'medianSessionLength' => $median,
-				'totalOffTime' => $row['offlineSum'],
-				'lastStart' => $row['lastStart'],
-				'lastLogout' => $row['lastLogout'],
-				'longSessions' => $row['longSessions'],
-				'shortSessions' => $row['shortSessions'],
-				'location' => ($anonymize ? $row['locHash'] : $row['locName']),
-			);
-			if (!$anonymize) {
-				$entry['locationId'] = $row['locId'];
-			}
+		$data = Queries::getClientStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
+		foreach ($data as &$entry) {
+			//self::nullToZero($entry);
+			$entry['hostname'] = ($anonymize ? md5($entry['clientName'] . self::$salt) : $entry['clientName']);
+			self::fillLocation($entry, $anonymize);
 			if ($printable) {
-				$entry['totalTime_s'] = self::formatSeconds($row['timeSum']);
-				$entry['medianSessionLength_s'] = self::formatSeconds($median);
-				$entry['totalOffTime_s'] = self::formatSeconds($row['offlineSum']);
-				$entry['lastStart_s'] = $row['lastStart'] == 0 ? "" : date(DATE_ISO8601, $row['lastStart']);
-				$entry['lastLogout_s'] = $row['lastLogout'] == 0 ? "" : date(DATE_ISO8601, $row['lastLogout']);
+				self::addPrintables($entry);
 			}
-			$data[] = $entry;
 		}
 		return $data;
 	}
@@ -104,9 +105,11 @@ class GetData
 		$anonymize = 0 !== ($flags & GETDATA_ANONYMOUS);
 		$res = Queries::getUserStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
 		$data = array();
-		$user = $anonymize ? 'userHash' : 'name';
 		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
-			$data[] = array('user' => $row[$user], 'sessions' => $row['count']);
+			if ($anonymize && $row['name'] !== 'anonymous') {
+				$row['name'] = md5($row['name'] . self::$salt);
+			}
+			$data[] = array('user' => $row['name'], 'sessions' => $row['count']);
 		}
 		return $data;
 	}
@@ -117,10 +120,12 @@ class GetData
 		$anonymize = 0 !== ($flags & GETDATA_ANONYMOUS);
 		$res = Queries::getVMStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound);
 		$data = array();
-		$vm = $anonymize ? 'vmHash' : 'name';
 		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
 			self::nullToZero($row);
-			$data[] = array('vm' => $row[$vm], 'sessions' => $row['count']);
+			if ($anonymize) {
+				$row['name'] = md5($row['name'] . self::$salt);
+			}
+			$data[] = array('vm' => $row['name'], 'sessions' => $row['count']);
 		}
 		return $data;
 	}
@@ -140,19 +145,4 @@ class GetData
 		return sprintf('%dd, %02d:%02d:%02d', $seconds / (3600*24), ($seconds % (3600*24)) / 3600, ($seconds%3600) / 60, $seconds%60);
 	}
 
-	// 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