[statistics] Per-location usage stats; include active lecture count

6 files changed, 181 insertions, 34 deletions

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>