path: root/modules-available/statistics_reporting/inc/queries.inc.php

<?php


class Queries
{

	private static function keepKeys(&$array, $list)
	{
		foreach (array_keys($array) as $key) {
			if (!in_array($key, $list)) {
				unset($array[$key]);
			}
		}
	}

	public static function getClientStatistics(int $from, int $to, int $lowerTimeBound = 0, int $upperTimeBound = 24): array
	{
		$fromCutoff = $from - 86400 * 30;
		$res = Database::simpleQuery("SELECT m.machineuuid, m.hostname, m.clientip,
				m.locationid, m.firstseen -- , m.lastboot, m.logintime, m.state
 				FROM machine m WHERE firstseen <= $to AND lastseen > $fromCutoff"); // " WHERE lastseen >= :from", compact('from'));
		$machines = self::getStats3($res, $from, $to, $lowerTimeBound, $upperTimeBound);
		foreach ($machines as &$machine) {
			$machine['medianSessionLength'] = self::calcMedian($machine['sessions']);
			unset($machine['sessions']);
			$machine['clientName'] = $machine['hostname'] ?: $machine['clientip'];
		}
		return $machines;
	}

	public static function getLocationStatistics(int $from, int $to, int $lowerTimeBound = 0, int $upperTimeBound = 24): array
	{
		$fromCutoff = $from - 86400 * 30;
		$res = Database::simpleQuery("SELECT m.machineuuid, m.hostname, m.clientip,
				m.locationid, m.firstseen -- , m.lastboot, m.logintime, m.state
 				FROM machine m WHERE firstseen <= $to AND lastseen > $fromCutoff"); // " WHERE lastseen >= :from", compact('from'));
		$machines = self::getStats3($res, $from, $to, $lowerTimeBound, $upperTimeBound);
		$locations = [];
		$keys = ['locationid', 'totalTime', 'totalOffTime', 'totalSessionTime', 'totalStandbyTime', 'totalIdleTime', 'totalIdleTime', 'longSessions', 'shortSessions', 'sessions'];
		while (($machine = array_pop($machines)) !== null) {
			if (!isset($locations[$machine['locationid']])) {
				self::keepKeys($machine, $keys);
				$locations[$machine['locationid']] = $machine;
			} else {
				$l =& $locations[$machine['locationid']];
				$l['totalTime'] += $machine['totalTime'];
				$l['totalOffTime'] += $machine['totalOffTime'];
				$l['totalSessionTime'] += $machine['totalSessionTime'];
				$l['totalStandbyTime'] += $machine['totalStandbyTime'];
				$l['totalIdleTime'] += $machine['totalIdleTime'];
				$l['longSessions'] += $machine['longSessions'];
				$l['shortSessions'] += $machine['shortSessions'];
				$l['sessions'] = array_merge($l['sessions'], $machine['sessions']);
			}
		}
		foreach ($locations as &$location) {
			$location['medianSessionLength'] = self::calcMedian($location['sessions']);
			unset($location['sessions']);
		}
		return $locations;
	}

	public static function getOverallStatistics(int $from, int $to, $lowerTimeBound = 0, $upperTimeBound = 24)
	{
		$fromCutoff = $from - 86400 * 30;
		$res = Database::simpleQuery("SELECT m.machineuuid, m.hostname, m.clientip,
				m.locationid, m.firstseen -- , m.lastboot, m.logintime, m.state
 				FROM machine m WHERE firstseen <= $to AND lastseen > $fromCutoff"); // " WHERE lastseen >= :from", compact('from'));
		$machines = self::getStats3($res, $from, $to, $lowerTimeBound, $upperTimeBound);
		$total = false;
		$keys = ['totalTime', 'totalOffTime', 'totalSessionTime', 'totalStandbyTime', 'totalIdleTime', 'totalIdleTime', 'longSessions', 'shortSessions', 'sessions'];
		while (($machine = array_pop($machines)) !== null) {
			if ($total === false) {
				self::keepKeys($machine, $keys);
				$total = $machine;
			} else {
				$total['totalTime'] += $machine['totalTime'];
				$total['totalOffTime'] += $machine['totalOffTime'];
				$total['totalSessionTime'] += $machine['totalSessionTime'];
				$total['totalStandbyTime'] += $machine['totalStandbyTime'];
				$total['totalIdleTime'] += $machine['totalIdleTime'];
				$total['longSessions'] += $machine['longSessions'];
				$total['shortSessions'] += $machine['shortSessions'];
				$total['sessions'] = array_merge($total['sessions'], $machine['sessions']);
			}
		}
		$total['medianSessionLength'] = $total ? self::calcMedian($total['sessions']) : 0;
		unset($total['sessions']);
		return $total;
	}

	private static function getStats3(PDOStatement $res, int $from, int $to, int $lowerTimeBound, int $upperTimeBound): array
	{
		//$debug = false;
		if ($lowerTimeBound === 0 && $upperTimeBound === 24 || $upperTimeBound <= $lowerTimeBound) {
			$bounds = false;
		} else {
			$bounds = [$lowerTimeBound, $upperTimeBound];
		}
		$machines = array();
		foreach ($res as $row) {
			$row['firstseen'] = max($row['firstseen'], $from);
			$row += array(
				'totalTime' => self::timeDiff($row['firstseen'], $to, $bounds),
				'totalOffTime' => 0,
				'totalSessionTime' => 0,
				'totalStandbyTime' => 0,
				'sessions' => [],
				'lastStart' => 0,
				'lastLogout' => 0,
				'longSessions' => 0,
				'shortSessions' => 0,
				'active' => false,
			);
			$machines[$row['machineuuid']] = $row;
		}
		// Don't filter by typeid in the query, still faster by being able to use the machineuuid/dateline index and filtering later
		$startTimestamp = $from - 86400; // Start 24h early to catch sessions already in progress
		$dups = [];
		$skipCount = 0;
		$BATCH_SIZE = 1000;
		// Fetch in batches of 1000 rows (for current 50 machines)
		do {
			$res = Database::simpleQuery("SELECT logid, dateline, typeid, machineuuid, data
					FROM statistic WHERE dateline >= :last AND dateline <= :to AND machineuuid IS NOT NULL
					ORDER BY dateline ASC, logid ASC LIMIT $BATCH_SIZE OFFSET $skipCount",
				['last' => $startTimestamp, 'to' => $to]);
			$lastStartTimestamp = $startTimestamp;
			$startTimestamp = false;
			$count = 0;
			$lastDups = $dups;
			$dups = [];
			foreach ($res as $row) {
				$count += 1; // Update count first, as we use it as a condition in outer loop. No continue before this
				settype($row['logid'], 'int');
				// Update for next query
				$startTimestamp = $row['dateline'];
				// Ignore dups, we query >= last dateline as we can have multiple events at the same second, but
				// only some of them got returned because of LIMIT. Skip here because excluding in query directly
				// would make the query itself rather inefficient. We also cannot use logid > X because the logid
				// is not strictly ascending with time, as dateline gets backdated to event start on insert.
				// In case we have 1000 rows with identical timestamp, we need to start skipping lines in the result,
				// otherwise we'd loop infinitely.
				$dups[$row['logid']] = 1;
				if (isset($lastDups[$row['logid']]))
					continue; // Already seen in last batch
				if (!isset($machines[$row['machineuuid']]))
					continue; // Not interested in this machine
				if ($row['typeid'] !== '~offline-length' && $row['typeid'] !== '~suspend-length' && $row['typeid'] !== '~session-length')
					continue;
				settype($row['dateline'], 'int');
				settype($row['data'], 'int');
				if ($row['data'] <= 0)
					continue;
				// Clamp to $from and $to
				if ($row['dateline'] < $from) {
					$diff = $row['dateline'] + $row['data'] - $from;
					if ($diff <= 0)
						continue;
					$row['dateline'] += $diff;
					$row['data'] -= $diff;
				}
				if ($row['dateline'] + $row['data'] > $to) {
					$row['data'] = $to - $row['dateline'];
					if ($row['data'] < 0)
						continue;
				}
				$machine =& $machines[$row['machineuuid']];
				// Process event if applicable
				if ($row['typeid'] === '~session-length') { // SESSION timespan
					$row['typeid'] = 'totalSessionTime';
					$machine['lastLogout'] = $row['dateline'] + $row['data'];
				} elseif ($row['typeid'] === '~offline-length') { // OFFLINE timespan
					$row['typeid'] = 'totalOffTime';
					$machine['lastStart'] = $row['dateline'] + $row['data'];
				} else { // STANDBY timespan
					$row['typeid'] = 'totalStandbyTime';
				}
				self::addTime($machine, $row, $bounds);
			}
			if ($startTimestamp === $lastStartTimestamp) {
				$skipCount += $BATCH_SIZE;
			} else {
				$skipCount = 0;
			}
		} while ($count === $BATCH_SIZE); // Check if we need to fetch more rows for current batch
		foreach ($machines as &$machine) {
			if (!$machine['active']) {
				$machine['totalOffTime'] = $machine['totalTime'];
			}
			$machine['totalIdleTime'] = $machine['totalTime'] - ($machine['totalOffTime'] + $machine['totalStandbyTime'] + $machine['totalSessionTime']);
		}
		return $machines;
	}

	private static function addTime(&$machine, $row, $bounds)
	{
		// First event, handle difference
		if (!$machine['active'] && $row['dateline'] + $row['data']  >= $machine['firstseen']) {
			if ($row['dateline'] > $machine['firstseen']) {
				$s = $machine['firstseen'];
				$e = $row['dateline'];
				/*
				if ($debug) {
					error_log('Initial offline time += ' . self::timeDiff($s, $e, $bounds, true));
				}
				*/
				$machine['totalOffTime'] += self::timeDiff($s, $e, $bounds);
				$machine['active'] = true;
				if ($machine['lastStart'] < $row['dateline']) {
					$machine['lastStart'] = $row['dateline'];
				}
			} else {
				// Not offline at beginning of period, do nothing
				$machine['active'] = true;
			}
		}
		// Current row
		if ($bounds === false) {
			// Simple case: No bounds
			$machine[$row['typeid']] += $row['data'];
		} else {
			$start = $row['dateline'];
			$end = $row['dateline'] + $row['data'];
			/*
			if ($debug) {
				error_log('Adding ' . $row['typeid'] . ' += ' . self::timeDiff($start, $end, $bounds, true));
			}
			*/
			$machine[$row['typeid']] += self::timeDiff($start, $end, $bounds);
		}
		$sh = date('G', $start ?? 0);
		if ($row['typeid'] === 'totalSessionTime' && ($bounds === false || ($sh >= $bounds[0] && $sh < $bounds[1]))) {
			if ($row['data'] >= 60) {
				$machine['longSessions'] += 1;
				$machine['sessions'][] = $row['data'];
			} else {
				$machine['shortSessions'] += 1;
			}
		}
	}

	private static function timeDiff($start, $end, $bounds)
	{
		// Put given timespan into bounds
		/*
		if ($debug) {
			$os = $start;
			$oe = $end;
		}
		*/
		if ($bounds !== false) {
			// Put start time into bounds
			if ($start !== null) {
				$sh = date('G', $start);
				if ($sh < $bounds[0]) {
					$start = strtotime($bounds[0] . ':00:00', $start);
				} elseif ($sh >= $bounds[1]) {
					$start = strtotime($bounds[0] . ':00:00 +1day', $start);
				}
			}
			// Put end time into bounds
			if ($end !== null && $end > $start) {
				$eh = date('G', $end);
				if ($eh < $bounds[0]) {
					$end = strtotime($bounds[1] . ':00:00 -1day', $end);
				} elseif ($eh >= $bounds[1]) {
					$end = strtotime($bounds[1] . ':00:00', $end);
				}
			}
		}
		if ($end !== null && $start !== null && $end < $start) {
			$end = $start;
		}
		/*
		if ($debug) {
			if ($start >= $end) {
				error_log('END < START: ' . date('d.m.Y H:i:s', $start) . ' - ' . date('d.m.Y H:i:s', $end));
			} else {
				if ($os != $start) {
					error_log('Corrected start: ' . date('d.m.Y H:i:s', $os) . ' to ' . date('d.m.Y H:i:s', $start));
				}
				if ($oe != $end) {
					error_log('Corrected end  : ' . date('d.m.Y H:i:s', $oe) . ' to ' . date('d.m.Y H:i:s', $end));
				}
			}
		}
		*/
		// Calc time excluding out of range hours
		return ($end - $start) - self::getIgnoredTime($start, $end, $bounds);
	}

	private static function getIgnoredTime($start, $end, $bounds)
	{
		if ($bounds === false || $start >= $end)
			return 0;
		$end = strtotime('00:00:00', $end);
		if ($start >= $end)
			return 0;
		/*
		if ($debug) {
			error_log('From ' . date('d.m.Y H:i:s', $start) . ' to ' . date('d.m.Y H:i:s', $end) . ' = ' . ceil(($end - $start) / 86400) * (24 - ($bounds[1] - $bounds[0])));
		}
		*/
		return (int)ceil(($end - $start) / 86400) * (24 - ($bounds[1] - $bounds[0])) * 3600;
	}

	/**
	 * Get median of array.
	 * @param int[] $array list of values
	 * @return int The median
	 */
	private static function calcMedian(array $array): int
	{
		if (empty($array))
			return 0;
		sort($array, SORT_NUMERIC);
		$count = count($array); //total numbers in array
		$middleval = (int)floor(($count-1) / 2); // find the middle value, or the lowest middle value
		if($count % 2 === 1) { // odd number, middle is the median
			return $array[$middleval];
		}
		// even number, calculate avg of 2 medians
		$low = $array[$middleval];
		$high = $array[$middleval+1];
		return (int)round(($low+$high) / 2);
	}

	// User Data: Name, Name(anonymized), Number of Logins
	public static function getUserStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) {
		return Database::simpleQuery("SELECT username AS name, COUNT(*) AS 'count'
												FROM statistic 
												WHERE typeid='.vmchooser-session-name' AND dateline >= $from and dateline <= $to
													AND FROM_UNIXTIME(dateline, '%H') >= $lowerTimeBound AND FROM_UNIXTIME(dateline, '%H') < $upperTimeBound
												GROUP BY username");
	}

	// Virtual Machine Data: Name, Number of Usages
	public static function getVMStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) {
		return Database::simpleQuery("SELECT data AS name, COUNT(*) AS 'count'
											 	FROM statistic
												WHERE typeid='.vmchooser-session-name' AND dateline >= $from and dateline <= $to
													AND FROM_UNIXTIME(dateline, '%H') >= $lowerTimeBound AND FROM_UNIXTIME(dateline, '%H') < $upperTimeBound
												GROUP BY data");
	}

	public static function getDozmodStats(int $from, int $to): array
	{
		if (Module::get('dozmod') === false)
			return ['disabled' => true];

		$return = [];
		$return['vms'] = Database::queryFirst("SELECT Count(*) AS `total`, Sum(If(createtime >= $from, 1, 0)) AS `new`,
			Sum(If(updatetime >= $from, 1, 0)) AS `updated`, Sum(If(latestversionid IS NOT NULL, 1, 0)) AS `valid`
			FROM sat.imagebase
			WHERE createtime <= $to");
		$return['lectures'] = Database::queryFirst("SELECT Count(*) AS `total`, Sum(If(createtime >= $from, 1, 0)) AS `new`,
			Sum(If(updatetime >= $from, 1, 0)) AS `updated`,
			Sum(If((($from BETWEEN starttime AND endtime) OR ($to BETWEEN starttime AND endtime)) AND isenabled <> 0, 1, 0)) AS `valid`
			FROM sat.lecture
			WHERE createtime <= $to");
		$return['users'] = Database::queryFirst("SELECT Count(*) AS `total`, Count(DISTINCT organizationid) AS `organizations`
			FROM sat.user
			WHERE lastlogin >= $from");
		return $return;
	}

	public static function getExamStats(int $from, int $to): array
	{
		if (Module::get('exams') === false)
			return ['disabled' => true];
		$return = [];
		$eres = Database::simpleQuery("SELECT starttime, endtime, GROUP_CONCAT(exl.locationid) AS `locs` FROM exams
				LEFT JOIN exams_x_location exl USING (examid)
				WHERE starttime < $to AND endtime > $from
				GROUP BY examid");
		foreach ($eres as $row) {
			// Get all boot events
			$data = ['from' => $row['starttime'], 'to' => $row['endtime']];
			if (empty($row['locs'])) {
				$exam = Database::queryFirst("SELECT Count(*) AS `event`, Avg(s.data) AS length FROM statistic s
						WHERE typeid = '~session-length'
						AND dateline BETWEEN :from AND :to", $data);
			} else {
				$data['locs'] = explode(',', $row['locs']);
				$exam = Database::queryFirst("SELECT Count(*) AS `sessions`, Avg(s.data) AS length FROM statistic s
						INNER JOIN machine m USING (machineuuid)
						WHERE typeid = '~session-length' AND m.locationid IN (:locs)
						AND dateline BETWEEN :from AND :to", $data);
			}
			settype($exam['length'], 'int');
			settype($exam['sessions'], 'int');
			$exam['duration'] = $row['endtime'] - $row['starttime'];
			$return[] = $exam;
		}
		return $return;
	}

	public static function getAggregatedMachineStats(int $from): array
	{
		$return = array();
		$return['location'] = Database::queryAll("SELECT MD5(CONCAT(locationid, :salt)) AS `location`, Count(*) AS `count`
			FROM machine
			WHERE lastseen >= $from
			GROUP BY locationid",
			array('salt' => GetData::$salt));
		$prev = 0;
		$str = ' ';
		foreach ([0.5, 1, 1.5, 2, 3, 4, 6, 8, 10, 12, 16, 20, 24, 28, 32, 40, 48, 64, 72, 80, 88, 96, 128, 192, 256, 512, 768, 1024, 1536, 2048, 3072, 4096] as $val) {
			$str .= 'WHEN mbram < ' . round(($val + $prev) * 512) . " THEN '" . $prev . "' ";
			$prev = $val;
		}
		$return['ram'] = Database::queryAll("SELECT CASE $str ELSE 'OVER 9000' END AS `gbram`, Count(*) AS `total`
			FROM machine
			WHERE lastseen >= $from
			GROUP BY gbram");
		foreach (array('cpumodel', 'systemmodel', 'realcores', 'kvmstate') as $key) {
			$return[$key] = Database::queryAll("SELECT $key, Count(*) AS `total`
				FROM machine
				WHERE lastseen >= $from
				GROUP BY $key");
		}
		// Legacy CPU
		$f = Database::queryFirst("SELECT Count(*) AS `total`
			FROM machine m
			INNER JOIN machine_x_hw mxh USING (machineuuid)
			INNER JOIN statistic_hw_prop hwp ON (hwp.hwid = mxh.hwid AND hwp.prop = 'vmx-legacy' AND hwp.value <> 0)
			WHERE m.lastseen >= $from");
		if (is_array($f)) {
			$return['vmx-legacy'] = $f['total'];
		}
		return $return;
	}

	/**
	 * @param int $from start timestamp
	 * @param int $to end timestamp
	 * @return int count of user active in timespan
	 */
	public static function getUniqueUserCount(int $from, int $to): int
	{
		$res = Database::queryFirst("SELECT Count(DISTINCT username) as `total`
			FROM statistic
			WHERE (dateline BETWEEN $from AND $to) AND typeid = '.vmchooser-session-name'");
		return (int)$res['total'];
	}

	public static function getBaseSystemStats(int $from, int $to)
	{
		return Database::queryAll("SELECT `data` AS `system`, Count(*) AS `count`
			FROM statistic
			WHERE (dateline BETWEEN $from AND $to) AND typeid = 'boot-system'
			GROUP BY `system`");
	}

	public static function getRunmodeStats(int $from, int $to)
	{
		return Database::queryAll("SELECT `data` AS `mode`, Count(*) AS `count`
			FROM statistic
			WHERE (dateline BETWEEN $from AND $to) AND typeid = 'boot-runmode'
			GROUP BY `mode`");
	}

}