From 200d92c8491d5060af5dd839aa82d1e51b058dd6 Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Fri, 2 Dec 2022 15:21:26 +0100
Subject: [statistics] Per-location usage stats; include active lecture count

---
 modules-available/locationinfo/api.inc.php         |  68 +-------------
 .../locationinfo/inc/locationinfo.inc.php          | 100 ++++++++++++++++++++
 modules-available/locations/inc/location.inc.php   |   2 +-
 modules-available/statistics/hooks/cron.inc.php    |  38 +++++++-
 .../statistics/inc/statisticsfilter.inc.php        |   6 +-
 .../statistics/inc/statisticsfilterset.inc.php     |  10 +-
 modules-available/statistics/install.inc.php       |  13 ++-
 modules-available/statistics/pages/summary.inc.php | 102 +++++++++++++++++----
 .../statistics/templates/summary.html              |  46 +++++++++-
 9 files changed, 283 insertions(+), 102 deletions(-)

diff --git a/modules-available/locationinfo/api.inc.php b/modules-available/locationinfo/api.inc.php
index 2fbf1e84..ab1f2358 100644
--- a/modules-available/locationinfo/api.inc.php
+++ b/modules-available/locationinfo/api.inc.php
@@ -36,7 +36,7 @@ function HandleParameters()
 		$output = getLocationTree($locationIds);
 	} elseif ($get === "calendar") {
 		$locationIds = LocationInfo::getLocationsOr404($uuid);
-		$output = getCalendar($locationIds);
+		$output = LocationInfo::getCalendar($locationIds);
 	}
 	if ($output !== false) {
 		Header('Content-Type: application/json; charset=utf-8');
@@ -153,69 +153,3 @@ function findLocations($locations, $idList)
 	}
 	return $ret;
 }
-
-// ########## <Calendar> ###########
-/**
- * Gets the calendar of the given ids.
- *
- * @param int[] $idList list with the location ids.
- * @return array Calendar.
- */
-function getCalendar($idList)
-{
-	if (empty($idList))
-		return [];
-
-	// Build SQL query for multiple ids.
-	$query = "SELECT l.locationid, l.serverid, l.serverlocationid, s.servertype, s.credentials
-				FROM `locationinfo_locationconfig` AS l
-				INNER JOIN locationinfo_coursebackend AS s ON (s.serverid = l.serverid)
-				WHERE l.locationid IN (:idlist)
-				ORDER BY s.servertype ASC";
-	$dbquery = Database::simpleQuery($query, array('idlist' => array_values($idList)));
-
-	$serverList = array();
-	foreach ($dbquery as $dbresult) {
-		if (!isset($serverList[$dbresult['serverid']])) {
-			$serverList[$dbresult['serverid']] = array(
-				'credentials' => (array)json_decode($dbresult['credentials'], true),
-				'type' => $dbresult['servertype'],
-				'idlist' => array()
-			);
-		}
-		$serverList[$dbresult['serverid']]['idlist'][] = $dbresult['locationid'];
-	}
-
-	$resultArray = array();
-	foreach ($serverList as $serverid => $server) {
-		$serverInstance = CourseBackend::getInstance($server['type']);
-		if ($serverInstance === false) {
-			EventLog::warning('Cannot fetch schedule for location (' . implode(', ', $server['idlist']) . ')'
-				. ': Backend type ' . $server['type'] . ' unknown. Disabling location.');
-			Database::exec("UPDATE locationinfo_locationconfig SET serverid = NULL WHERE locationid IN (:lid)",
-				array('lid' => $server['idlist']));
-			continue;
-		}
-		$credentialsOk = $serverInstance->setCredentials($serverid, $server['credentials']);
-
-		if ($credentialsOk) {
-			$calendarFromBackend = $serverInstance->fetchSchedule($server['idlist']);
-		} else {
-			$calendarFromBackend = array();
-		}
-
-		LocationInfo::setServerError($serverid, $serverInstance->getErrors());
-
-		if (is_array($calendarFromBackend)) {
-			foreach ($calendarFromBackend as $key => $value) {
-				$resultArray[] = array(
-					'id' => $key,
-					'calendar' => $value,
-				);
-			}
-		}
-	}
-	return $resultArray;
-}
-
-// ########## </Calendar> ##########
diff --git a/modules-available/locationinfo/inc/locationinfo.inc.php b/modules-available/locationinfo/inc/locationinfo.inc.php
index 9cc06f02..737f2275 100644
--- a/modules-available/locationinfo/inc/locationinfo.inc.php
+++ b/modules-available/locationinfo/inc/locationinfo.inc.php
@@ -134,4 +134,104 @@ class LocationInfo
 		return array();
 	}
 
+	/**
+	 * Gets the calendar of the given ids.
+	 *
+	 * @param int[] $idList list with the location ids.
+	 * @return array Calendar.
+	 */
+	public static function getCalendar(array $idList, bool $forceCached = false): array
+	{
+		if (empty($idList))
+			return [];
+
+		$resultArray = array();
+
+		if ($forceCached) {
+			$res = Database::simpleQuery("SELECT locationid, calendar FROM locationinfo_locationconfig
+					WHERE Length(calendar) > 10 AND lastcalendarupdate > UNIX_TIMESTAMP() - 86400*3");
+			foreach ($res as $row) {
+				$resultArray[] = [
+					'id' => (int)$row['locationid'],
+					'calendar' => json_decode($row['calendar'], true),
+				];
+			}
+			return $resultArray;
+		}
+
+		// Build SQL query for multiple ids.
+		$query = "SELECT l.locationid, l.serverid, l.serverlocationid, s.servertype, s.credentials
+				FROM `locationinfo_locationconfig` AS l
+				INNER JOIN locationinfo_coursebackend AS s ON (s.serverid = l.serverid)
+				WHERE l.locationid IN (:idlist)
+				ORDER BY s.servertype ASC";
+		$dbquery = Database::simpleQuery($query, array('idlist' => array_values($idList)));
+
+		$serverList = array();
+		foreach ($dbquery as $dbresult) {
+			if (!isset($serverList[$dbresult['serverid']])) {
+				$serverList[$dbresult['serverid']] = array(
+					'credentials' => (array)json_decode($dbresult['credentials'], true),
+					'type' => $dbresult['servertype'],
+					'idlist' => array()
+				);
+			}
+			$serverList[$dbresult['serverid']]['idlist'][] = $dbresult['locationid'];
+		}
+
+		foreach ($serverList as $serverid => $server) {
+			$serverInstance = CourseBackend::getInstance($server['type']);
+			if ($serverInstance === false) {
+				EventLog::warning('Cannot fetch schedule for location (' . implode(', ', $server['idlist']) . ')'
+					. ': Backend type ' . $server['type'] . ' unknown. Disabling location.');
+				Database::exec("UPDATE locationinfo_locationconfig SET serverid = NULL WHERE locationid IN (:lid)",
+					array('lid' => $server['idlist']));
+				continue;
+			}
+			$credentialsOk = $serverInstance->setCredentials($serverid, $server['credentials']);
+
+			if ($credentialsOk) {
+				$calendarFromBackend = $serverInstance->fetchSchedule($server['idlist']);
+			} else {
+				$calendarFromBackend = array();
+			}
+
+			LocationInfo::setServerError($serverid, $serverInstance->getErrors());
+
+			if (is_array($calendarFromBackend)) {
+				foreach ($calendarFromBackend as $key => $value) {
+					$resultArray[] = array(
+						'id' => (int)$key,
+						'calendar' => $value,
+					);
+				}
+			}
+		}
+		return $resultArray;
+	}
+
+	public static function getAllCalendars(bool $forceCached): array
+	{
+		$locations = Database::queryColumnArray("SELECT locationid FROM location");
+		$calendars = [];
+		foreach (LocationInfo::getCalendar($locations, $forceCached) as $cal) {
+			if (empty($cal['calendar']))
+				continue;
+			$calendars[$cal['id']] = $cal['calendar'];
+		}
+		return $calendars;
+	}
+
+	public static function extractCurrentEvent(array $calendar): string
+	{
+		$NOW = time();
+		foreach ($calendar as $event) {
+			$start = strtotime($event['start']);
+			$end = strtotime($event['end']) + 60;
+			if ($NOW >= $start && $NOW <= $end)
+				return $event['title'];
+		}
+		return '';
+	}
+
 }
diff --git a/modules-available/locations/inc/location.inc.php b/modules-available/locations/inc/location.inc.php
index 65ea06f5..db4580be 100644
--- a/modules-available/locations/inc/location.inc.php
+++ b/modules-available/locations/inc/location.inc.php
@@ -198,7 +198,7 @@ class Location
 	 * @param int[]|int $idList List of location ids
 	 * @return array list of passed locations plus their children
 	 */
-	public static function getRecursiveFlat($idList)
+	public static function getRecursiveFlat($idList): array
 	{
 		$ret = self::getRecursive($idList);
 		if (!empty($ret)) {
diff --git a/modules-available/statistics/hooks/cron.inc.php b/modules-available/statistics/hooks/cron.inc.php
index 7152b1da..d444c672 100644
--- a/modules-available/statistics/hooks/cron.inc.php
+++ b/modules-available/statistics/hooks/cron.inc.php
@@ -9,12 +9,42 @@ function logstats()
 		$join = 'LEFT JOIN runmode r USING (machineuuid)';
 		$where = 'AND (r.isclient IS NULL OR r.isclient <> 0)';
 	}
-	$known = Database::queryFirst("SELECT Count(*) AS val FROM machine m $join WHERE m.lastseen > $cutoff $where");
-	$on = Database::queryFirst("SELECT Count(*) AS val FROM machine m $join WHERE m.state IN ('IDLE', 'OCCUPIED') $where");
-	$used = Database::queryFirst("SELECT Count(*) AS val FROM machine m $join WHERE m.state = 'OCCUPIED' $where");
+	// Get total/online/in-use
+	$known = Database::queryKeyValueList("SELECT locationid, Count(*) AS val FROM machine m
+			$join WHERE m.lastseen > $cutoff $where
+			GROUP BY locationid");
+	$on = Database::queryKeyValueList("SELECT locationid, Count(*) AS val FROM machine m
+			$join WHERE m.state IN ('IDLE', 'OCCUPIED') $where
+			GROUP BY locationid");
+	$used = Database::queryKeyValueList("SELECT locationid, Count(*) AS val FROM machine m
+			$join WHERE m.state = 'OCCUPIED' $where
+			GROUP BY locationid");
+	// Get calendar data if available
+	if (Module::isAvailable('locationinfo')) {
+		// Refresh all calendars around 07:00
+		$calendars = LocationInfo::getAllCalendars(date('G') != 7 || date('i') >= 10);
+	}
+	// Mash together
+	$data = ['usage' => []];
+	foreach ($known as $lid => $val) {
+		$entry = ['t' => $val];
+		if (isset($on[$lid])) {
+			$entry['o'] = $on[$lid];
+		}
+		if (isset($used[$lid])) {
+			$entry['u'] = $used[$lid];
+		}
+		if (isset($calendars[$lid])) {
+			$title = LocationInfo::extractCurrentEvent($calendars[$lid]);
+			if (!empty($title)) {
+				$entry['event'] = $title;
+			}
+		}
+		$data['usage'][$lid] = $entry;
+	}
 	Database::exec("INSERT INTO statistic (dateline, typeid, clientip, username, data) VALUES (:now, '~stats', '', '', :vals)", array(
 		'now' => $NOW,
-		'vals' => $known['val'] . '#' . $on['val'] . '#' . $used['val'],
+		'vals' => json_encode($data),
 	));
 }
 
diff --git a/modules-available/statistics/inc/statisticsfilter.inc.php b/modules-available/statistics/inc/statisticsfilter.inc.php
index dfba4eb8..996364ae 100644
--- a/modules-available/statistics/inc/statisticsfilter.inc.php
+++ b/modules-available/statistics/inc/statisticsfilter.inc.php
@@ -208,7 +208,7 @@ abstract class StatisticsFilter
 			} elseif ($filter->type() === 'enum') {
 				$col['enum'] = true;
 				$col['values'] = $filter->values;
-				if ($bind !== false) {
+				if ($bind !== null) {
 					// Current value from GET
 					foreach ($col['values'] as &$value) {
 						if ($value['key'] == $bind->argument) {
@@ -218,7 +218,7 @@ abstract class StatisticsFilter
 				}
 			}
 			// current value from GET
-			if ($bind !== false) {
+			if ($bind !== null) {
 				$col['currentvalue'] = $bind->argument;
 				$col['checked'] = 'checked';
 				$showCount++;
@@ -228,7 +228,7 @@ abstract class StatisticsFilter
 			$col['op'] = $filter->ops;
 			foreach ($col['op'] as &$value) {
 				$value = ['op' => $value];
-				if ($bind !== false && $bind->op === $value['op']) {
+				if ($bind !== null && $bind->op === $value['op']) {
 					$value['selected'] = 'selected';
 				}
 			}
diff --git a/modules-available/statistics/inc/statisticsfilterset.inc.php b/modules-available/statistics/inc/statisticsfilterset.inc.php
index 086c4fae..90a39f7d 100644
--- a/modules-available/statistics/inc/statisticsfilterset.inc.php
+++ b/modules-available/statistics/inc/statisticsfilterset.inc.php
@@ -40,7 +40,7 @@ class StatisticsFilterSet
 
 	public function filterNonClients()
 	{
-		if (Module::get('runmode') === false || $this->hasFilter('IsClientStatisticsFilter') !== false)
+		if (Module::get('runmode') === false || $this->hasFilter('IsClientStatisticsFilter') !== null)
 			return;
 		$this->cache = false;
 		// Runmode module exists, add filter
@@ -49,7 +49,7 @@ class StatisticsFilterSet
 
 	/**
 	 * @param string $type filter type (class name)
-	 * @return false|DatabaseFilter The filter, false if not found
+	 * @return ?DatabaseFilter The filter, false if not found
 	 */
 	public function hasFilter(string $type)
 	{
@@ -58,18 +58,18 @@ class StatisticsFilterSet
 				return $filter;
 			}
 		}
-		return false;
+		return null;
 	}
 
 	/**
 	 * @param string $type filter type key/id
-	 * @return false|DatabaseFilter The filter, false if not found
+	 * @return ?DatabaseFilter The filter, false if not found
 	 */
 	public function hasFilterKey(string $type)
 	{
 		if (isset($this->filters[$type]))
 			return $this->filters[$type];
-		return false;
+		return null;
 	}
 
 	/**
diff --git a/modules-available/statistics/install.inc.php b/modules-available/statistics/install.inc.php
index cb36c47f..0afaa3c7 100644
--- a/modules-available/statistics/install.inc.php
+++ b/modules-available/statistics/install.inc.php
@@ -11,7 +11,7 @@ $res[] = tableCreate('statistic', "
   `clientip` varchar(40) NOT NULL,
   `machineuuid` char(36) CHARACTER SET ascii DEFAULT NULL,
   `username` varchar(30) NOT NULL,
-  `data` varchar(255) NOT NULL,
+  `data` BLOB NOT NULL,
   PRIMARY KEY (`logid`),
   KEY `dateline` (`dateline`),
   KEY `logtypeid` (`typeid`,`dateline`),
@@ -302,6 +302,7 @@ if (!tableHasColumn('machine', 'live_id45size')) {
 	}
 	$res[] = UPDATE_DONE;
 }
+
 // 2021-08-19 Enhanced machine property indexing
 if (stripos(tableColumnType('statistic_hw_prop', 'prop'), 'varchar(64)') === false) {
 	$ret = Database::exec("ALTER TABLE statistic_hw_prop
@@ -353,5 +354,15 @@ if (!tableHasColumn('machine', 'id45mb')) {
 	}
 	$res[] = UPDATE_DONE;
 }
+
+// 2022-11-22 Change data column of statistic table from varchar(255) to blob
+if (stripos(tableColumnType('statistic', 'data'), 'blob') === false) {
+	$ret = Database::exec("ALTER TABLE `statistic` MODIFY COLUMN `data` BLOB NOT NULL");
+	if ($ret === false) {
+		finalResponse(UPDATE_FAILED, 'Changing statistic.data to blob failed: ' . Database::lastError());
+	}
+	$res[] = UPDATE_DONE;
+}
+
 // Create response
 responseFromArray($res);
diff --git a/modules-available/statistics/pages/summary.inc.php b/modules-available/statistics/pages/summary.inc.php
index cf3e745d..53b028fc 100644
--- a/modules-available/statistics/pages/summary.inc.php
+++ b/modules-available/statistics/pages/summary.inc.php
@@ -44,7 +44,7 @@ class SubPage
 	/**
 	 * @param \StatisticsFilterSet $filterSet
 	 */
-	private static function showSummary($filterSet)
+	private static function showSummary(StatisticsFilterSet $filterSet)
 	{
 		$filterSet->makeFragments($where, $join, $args);
 		$known = Database::queryFirst("SELECT Count(*) AS val FROM machine m $join WHERE $where", $args);
@@ -56,36 +56,74 @@ class SubPage
 		} else {
 			$usedpercent = 0;
 		}
-		$data = array(
+		$data = [
 			'known' => $known['val'],
 			'online' => $on['val'],
 			'used' => $used['val'],
 			'usedpercent' => $usedpercent,
 			'badhdd' => $hdd['val'],
-		);
+		];
 		// Graph
+		// Get locations
+		$locFilter = $filterSet->hasFilter('LocationStatisticsFilter');
+		if ($locFilter === null || ($locFilter->op === '~' && (int)$locFilter->argument === 0)) {
+			$locations = null;
+			$op = null;
+		} elseif ($locFilter->op === '~') {
+			$locations = array_keys(Location::getRecursiveFlat($locFilter->argument));
+			$op = $locFilter->op;
+		} else {
+			$locations = [$locFilter->argument];
+			$op = $locFilter->op;
+		}
+		//error_log($op . ' ' . print_r($locations, true));
 		$cutoff = time() - 2 * 86400;
-		$res = Database::simpleQuery("SELECT dateline, data FROM statistic WHERE typeid = '~stats' AND dateline > $cutoff ORDER BY dateline ASC");
-		$labels = array();
-		$points1 = array('data' => array(), 'label' => 'Online', 'borderColor' => '#8eb');
-		$points2 = array('data' => array(), 'label' => 'In use', 'borderColor' => '#fa9');
+		$res = Database::simpleQuery("SELECT dateline, data FROM statistic
+				WHERE typeid = '~stats' AND dateline > $cutoff ORDER BY dateline DESC");
+		$labels = [];
+		$points1 = [];
+		$points2 = [];
+		$lectures = [];
+		// Get max from 6 consecutive values, which should be 6*5 = 30m
 		$sum = 0;
 		foreach ($res as $row) {
-			$x = explode('#', $row['data']);
-			if ($sum === 0) {
+			if ($row['data'][0] === '{') {
+				$x = json_decode($row['data'], true);
+				if (!is_array($x) || !isset($x['usage']))
+					continue;
+				$x = self::mangleStatsJson($x, $locations, $op);
+			} else if ($locations === null) {
+				$x = explode('#', $row['data']);
+				if (count($x) < 3)
+					continue;
+				$x[] = 0;
+			} else {
+				continue;
+			}
+			if ($sum % 4 === 0) {
 				$labels[] = date('H:i', $row['dateline']);
 			} else {
-				$x[1] = max($x[1], array_pop($points1['data']));
-				$x[2] = max($x[2], array_pop($points2['data']));
+				$x[1] = max($x[1], array_pop($points1));
+				$x[2] = max($x[2], array_pop($points2));
+				$x[3] += array_pop($lectures);
 			}
-			$points1['data'][] = $x[1];
-			$points2['data'][] = $x[2];
+			$points1[] = $x[1];
+			$points2[] = $x[2];
+			$lectures[] = $x[3];
 			++$sum;
-			if ($sum === 12) {
-				$sum = 0;
-			}
 		}
-		$data['json'] = json_encode(array('labels' => $labels, 'datasets' => array($points1, $points2)));
+		if (!empty($points1) && max(...$points1) > 0) {
+			$labels = array_reverse($labels);
+			$points1 = array_reverse($points1);
+			$points2 = array_reverse($points2);
+			$lectures = array_reverse($lectures);
+			$data['json'] = json_encode(['labels' => $labels,
+				'datasets' => [
+					['data' => $points1, 'label' => 'Online', 'borderColor' => '#8f3'],
+					['data' => $points2, 'label' => 'In use', 'borderColor' => '#e76'],
+				]]);
+			$data['markings'] = json_encode($lectures, true);
+		}
 		if (Module::get('runmode') !== false) {
 			$res = Database::queryFirst('SELECT Count(*) AS cnt FROM machine m INNER JOIN runmode r USING (machineuuid)'
 				. " $join WHERE $where", $args);
@@ -238,14 +276,15 @@ class SubPage
 	/**
 	 * @param \StatisticsFilterSet $filterSet
 	 */
-	private static function showLatestMachines($filterSet)
+	private static function showLatestMachines(StatisticsFilterSet $filterSet)
 	{
 		$filterSet->makeFragments($where, $join, $args);
 		$args['cutoff'] = ceil(time() / 3600) * 3600 - 86400 * 10;
 
 		$res = Database::simpleQuery("SELECT m.machineuuid, m.clientip, m.hostname, m.firstseen, m.mbram, m.kvmstate, m.id44mb
 				FROM machine m $join
-				WHERE firstseen > :cutoff AND $where ORDER BY firstseen DESC LIMIT 32", $args);
+				WHERE firstseen > :cutoff AND $where
+				ORDER BY firstseen DESC LIMIT 32", $args);
 		$rows = array();
 		$count = 0;
 		foreach ($res as $row) {
@@ -306,4 +345,29 @@ class SubPage
 		}
 	}
 
+	/**
+	 * @param array $json decoded json ~stats data
+	 * @param ?int[] $locations
+	 * @param ?string $op
+	 */
+	private static function mangleStatsJson(array $json, $locations, $op): array
+	{
+		// Total, On, InUse, Lectures
+		$retval = [0, 0, 0, 0];
+		foreach ($json['usage'] as $lid => $data) {
+			$lid = (int)$lid;
+			if ($locations === null
+					|| ($op === '!=' && !in_array($lid, $locations))
+					|| ($op !== '!=' && in_array($lid, $locations))) {
+				$retval[0] += $data['t'];
+				$retval[1] += $data['o'] ?? 0;
+				$retval[2] += $data['u'] ?? 0;
+				if (isset($data['event'])) {
+					$retval[3] += 1;
+				}
+			}
+		}
+		return $retval;
+	}
+
 }
diff --git a/modules-available/statistics/templates/summary.html b/modules-available/statistics/templates/summary.html
index a71d50c0..461b4423 100644
--- a/modules-available/statistics/templates/summary.html
+++ b/modules-available/statistics/templates/summary.html
@@ -23,18 +23,60 @@
 		</div>
 	</div>
 	<div>
+		{{#json}}
 		<canvas id="usagehist" style="width:100%;height:150px"></canvas>
 		<script type="text/javascript">
 			document.addEventListener("DOMContentLoaded", function() {
+
+				var markings = {{{markings}}};
+				var markMax = Math.max(...markings) * 3;
+
+				var oldDraw = Chart.prototype._drawDatasets;
+
+				Chart.prototype._drawDatasets = function () {
+					if (this.chartArea) {
+						var ctx = this.ctx;
+						var chartArea = this.chartArea;
+
+						var meta = this.getDatasetMeta(0);
+
+						ctx.save();
+						var end = Math.min(meta.data.length, markings.length) - 1;
+						for (var i = 0; i < end; ++i) {
+							var start = meta.data[i].x;
+							var stop = meta.data[i+1].x;
+							ctx.fillStyle = 'rgba(16, 64, 255, ' + (!!markings[i] * .05 + markings[i] / markMax) + ')';
+							ctx.fillRect(start, chartArea.top, stop - start, chartArea.bottom - chartArea.top);
+						}
+						ctx.restore();
+					}
+
+					// Perform regular chart draw
+					oldDraw.call(this);
+				};
+
 				var data = {{{json}}};
 				var sel = false;
 				new Chart(document.getElementById('usagehist').getContext('2d'), {type: 'line', data: data, options: {
+					responsive: true,
 					animation: false,
-					pointHitDetectionRadius: 5,
-					plugins: { legend: {position: 'left' }}
+					pointRadius: 0,
+					pointHitRadius: 6,
+					interaction: { mode: 'index' },
+					plugins: {
+						tooltip: { callbacks: { label: function(context) {
+									if (context.dataset.label === 'Courses')
+										return null;
+									return context.dataset.label + ': ' + context.parsed.y;
+								}
+							}},
+						legend: {position: 'left' },
+					}
 				}});
+
 			}, false);
 		</script>
+		{{/json}}
 	</div>
 </div>
 
-- 
cgit v1.2.3-55-g7522