[statistics] Add PCI device filter option

10 files changed, 72 insertions, 21 deletions

diff --git a/modules-available/statistics/inc/hardwarequery.inc.php b/modules-available/statistics/inc/hardwarequery.inc.php
index 00a9bbc9..93793e58 100644
--- a/modules-available/statistics/inc/hardwarequery.inc.php
+++ b/modules-available/statistics/inc/hardwarequery.inc.php
@@ -120,7 +120,7 @@ class HardwareQuery
 	/**
 	 * @return false|PDOStatement
 	 */
-	public function query(string $groupBy = '')
+	public function query($groupBy = '')
 	{
 		return Database::simpleQuery($this->buildQuery($groupBy), $this->args);
 	}
@@ -129,13 +129,17 @@ class HardwareQuery
 	 * Build query string
 	 * @param string $groupBy Column to group by
 	 */
-	public function buildQuery(string $groupBy = ''): string
+	public function buildQuery($groupBy = ''): string
 	{
-		$groupConcat = !empty($groupBy) && $groupBy !== 'mxhw.machinehwid';
+		if (empty($groupBy)) {
+			$groupBy = [];
+		} elseif (!is_array($groupBy)) {
+			$groupBy = [$groupBy];
+		}
 		$columns = [];
 		foreach ($this->columns as $column) {
 			if ($column instanceof HardwareQueryColumn) {
-				$column->generate($this->joins, $columns, $this->args, $groupConcat);
+				$column->generate($this->joins, $columns, $this->args, $groupBy);
 			} else {
 				$columns[] = $column;
 			}
@@ -143,14 +147,14 @@ class HardwareQuery
 		$columns[] = 'mxhw.machineuuid';
 		$columns[] = 'shw.hwid';
 		// TODO: Untangle this implicit magic
-		if (empty($groupBy) || $groupBy === 'mxhw.machinehwid') {
+		if (empty($groupBy) || $groupBy[0] === 'mxhw.machinehwid') {
 			$columns[] = 'mxhw.disconnecttime';
 		} else {
 			$columns[] = 'Sum(If(mxhw.disconnecttime = 0, 1, 0)) AS connected_count';
 		}
 		if (!empty($groupBy)) {
 			$columns[] = 'Count(*) AS group_count';
-			$groupBy = " GROUP BY $groupBy";
+			$groupBy = " GROUP BY " . implode(', ', $groupBy);
 		}
 		return 'SELECT ' . implode(', ', $columns)
 			. ' FROM statistic_hw shw '
diff --git a/modules-available/statistics/inc/hardwarequerycolumn.inc.php b/modules-available/statistics/inc/hardwarequerycolumn.inc.php
index d073530b..5e16bcd9 100644
--- a/modules-available/statistics/inc/hardwarequerycolumn.inc.php
+++ b/modules-available/statistics/inc/hardwarequerycolumn.inc.php
@@ -30,9 +30,9 @@ class HardwareQueryColumn
 	/**
 	 * Add necessary conditions, joins, columns to final SQL arrays. To be called
 	 * from HardwareQuery::buildQuery().
-	 * @param bool $groupConcat whether to add distinct GROUP_CONCAT to column.
+	 * @param array if column name is in this array, add as distinct GROUP_CONCAT to column.
 	 */
-	public function generate(array &$joins, array &$columns, array &$params, bool $groupConcat)
+	public function generate(array &$joins, array &$columns, array &$params, array $groupConcat = [])
 	{
 		if ($this->global) {
 			$srcTable = 'shw';
@@ -53,7 +53,7 @@ class HardwareQueryColumn
 		// INNER JOIN, so the result will be empty if the condition doesn't match.
 		$type = count($this->conditions) === 1 ? 'LEFT' : 'INNER';
 		$joins[] = "$type JOIN $table $tid ON (" . implode(' AND ', $this->conditions) . ")";
-		if ($groupConcat) {
+		if (!in_array($this->alias, $groupConcat)) {
 			$columns[] = "Group_Concat(DISTINCT $tid.`value` SEPARATOR ', ') AS `{$this->alias}`";
 		} else {
 			$columns[] = "$tid.`value` AS `{$this->alias}`";
diff --git a/modules-available/statistics/inc/statisticsfilter.inc.php b/modules-available/statistics/inc/statisticsfilter.inc.php
index a4e96294..1c3df9af 100644
--- a/modules-available/statistics/inc/statisticsfilter.inc.php
+++ b/modules-available/statistics/inc/statisticsfilter.inc.php
@@ -253,6 +253,7 @@ abstract class StatisticsFilter
 			'live_memfree' => new SimpleStatisticsFilter('live_memfree', self::OP_ORDINAL, 'MiB'),
 			'live_tmpfree' => new SimpleStatisticsFilter('live_tmpfree', self::OP_ORDINAL, 'MiB'),
 			'standbycrash' => new StandbyCrashStatisticsFilter(),
+			'pcidev' => new PciDeviceStatisticsFilter(),
 		];
 		if (Module::isAvailable('locations')) {
 			self::$columns['location'] = new LocationStatisticsFilter();
@@ -617,15 +618,50 @@ class IsClientStatisticsFilter extends StatisticsFilter
 	public function whereClause(string $operator, $argument, array &$args, array &$joins): string
 	{
 		if ($argument) {
-			$joins[] = ' LEFT JOIN runmode USING (machineuuid)';
+			$joins[] = ' LEFT JOIN runmode ON (m.machineuuid = runmode.machineuuid)';
 			return "(runmode.isclient <> 0 OR runmode.isclient IS NULL)";
 		}
-		$joins[] = ' INNER JOIN runmode USING (machineuuid)';
+		$joins[] = ' INNER JOIN runmode ON (m.machineuuid = runmode.machineuuid)';
 		return "runmode.isclient = 0";
 	}
 
 }
 
+class PciDeviceStatisticsFilter extends StatisticsFilter
+{
+
+	public function __construct()
+	{
+		parent::__construct(null, ['=']);
+	}
+
+	public function whereClause(string $operator, $argument, array &$args, array &$joins): string
+	{
+		if (!preg_match('/^([0-9a-f]{4})(?::([0-9a-f]{4}))?$/i', $argument, $out)) {
+			Message::addError('invalid-pciid', $argument);
+			return '0';
+		}
+		$vendor = $out[1];
+		$device = $out[2] ?? '';
+		// basic join for hw_x_machine
+		$joins[] = ' INNER JOIN machine_x_hw mxhw ON (mxhw.disconnecttime = 0 AND mxhw.machineuuid = m.machineuuid)';
+		$key = $key = self::getNewKey('foo');
+		$joins[] = " INNER JOIN statistic_hw shw ON (mxhw.hwid = shw.hwid AND shw.hwtype = :$key)";
+		$args[$key] = HardwareInfo::PCI_DEVICE;
+		$_ = [];
+		$c = new HardwareQueryColumn(true, 'vendor');
+		$c->addCondition($operator, $vendor);
+		$c->generate($joins, $_, $args);
+		if (!empty($device)) {
+			$c = new HardwareQueryColumn(true, 'device');
+			$c->addCondition($operator, $device);
+			$c->generate($joins, $_, $args);
+		}
+		return '1';
+	}
+
+}
+
 class DatabaseFilter
 {
 	/** @var StatisticsFilter
diff --git a/modules-available/statistics/lang/de/filters.json b/modules-available/statistics/lang/de/filters.json
index 6e659ed5..30390e6f 100644
--- a/modules-available/statistics/lang/de/filters.json
+++ b/modules-available/statistics/lang/de/filters.json
@@ -17,6 +17,7 @@
     "logintime": "Letzter Login",
     "macaddr": "MAC-Adresse",
     "machineuuid": "System-UUID",
+    "pcidev": "PCI-Ger\u00e4t",
     "realcores": "CPU-Kerne (real)",
     "runtime": "Laufzeit (Stunden)",
     "standbycrash": "Crashes im Standby",
diff --git a/modules-available/statistics/lang/de/template-tags.json b/modules-available/statistics/lang/de/template-tags.json
index b76c0be5..cd2967a0 100644
--- a/modules-available/statistics/lang/de/template-tags.json
+++ b/modules-available/statistics/lang/de/template-tags.json
@@ -80,7 +80,8 @@
     "lang_partitionSize": "Gr\u00f6\u00dfe",
     "lang_pcmodel": "PC-Modell",
     "lang_pendingSectors": "Potentiell defekte Sektoren",
-    "lang_persistentPart": "ID45",
+    "lang_persistentPart": "Persistent",
+    "lang_persistentPartID": "ID45",
     "lang_powerOnTime": "Betriebszeit",
     "lang_projector": "Beamer",
     "lang_projectors": "Beamer",
@@ -124,7 +125,8 @@
     "lang_sureDeletePermanent": "M\u00f6chten Sie diese(n) Rechner wirklich unwiderruflich aus der Datenbank entfernen?\r\n\r\nWichtig: L\u00f6schen verhindert nicht, dass ein Rechner nach erneutem Starten von bwLehrpool wieder in die Datenbank aufgenommen wird.",
     "lang_sureReplaceNoUndo": "Wollen Sie die Daten der ausgew\u00e4hlten Rechner \u00fcbertragen? Diese Aktion kann nicht r\u00fcckg\u00e4ngig gemacht werden.",
     "lang_swap": "Swap",
-    "lang_tempPart": "Temp. Partition",
+    "lang_tempPart": "Tempor\u00e4r",
+    "lang_tempPartID": "ID44",
     "lang_tempPartStats": "Tempor\u00e4re Partition",
     "lang_thoseAreProjectors": "Diese Modellnamen werden als Beamer behandelt, auch wenn die EDID-Informationen des Ger\u00e4tes anderes berichten.",
     "lang_timebarDesc": "Visuelle Darstellung der letzten Tage. Rote Abschnitte zeigen, wann der Rechner belegt war, gr\u00fcne, wann er nicht verwendet wurde, aber eingeschaltet war. Die leicht abgedunkelten Abschnitte markieren N\u00e4chte (22 bis 8 Uhr).",
diff --git a/modules-available/statistics/lang/en/filters.json b/modules-available/statistics/lang/en/filters.json
index ba93a979..2fb80fd3 100644
--- a/modules-available/statistics/lang/en/filters.json
+++ b/modules-available/statistics/lang/en/filters.json
@@ -17,6 +17,7 @@
     "logintime": "Last login",
     "macaddr": "MAC address",
     "machineuuid": "System UUID",
+    "pcidev": "PCI device",
     "realcores": "CPU cores (real)",
     "runtime": "Uptime (hours)",
     "standbycrash": "Crashes in Standby",
diff --git a/modules-available/statistics/lang/en/template-tags.json b/modules-available/statistics/lang/en/template-tags.json
index 64171a7f..1889c2c6 100644
--- a/modules-available/statistics/lang/en/template-tags.json
+++ b/modules-available/statistics/lang/en/template-tags.json
@@ -80,7 +80,8 @@
     "lang_partitionSize": "Size",
     "lang_pcmodel": "System model",
     "lang_pendingSectors": "Sectors pending reallocation",
-    "lang_persistentPart": "ID45",
+    "lang_persistentPart": "Persistent",
+    "lang_persistentPartID": "ID45",
     "lang_powerOnTime": "Power on time",
     "lang_projector": "Projector",
     "lang_projectors": "Projectors",
@@ -124,7 +125,8 @@
     "lang_sureDeletePermanent": "Are your sure you want to delete the selected machine(s) from the database? This cannot be undone.\r\n\r\nNote: Deleting machines from the database does not prevent booting up bwLehrpool again, which would recreate their respective database entries.",
     "lang_sureReplaceNoUndo": "Are you sure you want to replace the selected machine pairs? This action cannot be undone.",
     "lang_swap": "swap",
-    "lang_tempPart": "Temp. partition",
+    "lang_tempPart": "Temporary",
+    "lang_tempPartID": "ID44",
     "lang_tempPartStats": "Temporary partition",
     "lang_thoseAreProjectors": "These model names will always be treated as beamers, even if the device's EDID data says otherwise.",
     "lang_timebarDesc": "Visual representation of the last few days. Red parts mark periods where the client was occupied, green parts where the client was idle. Dimmed parts mark nights (10pm to 8am).",
diff --git a/modules-available/statistics/pages/list.inc.php b/modules-available/statistics/pages/list.inc.php
index d3c8069e..4d4da2b6 100644
--- a/modules-available/statistics/pages/list.inc.php
+++ b/modules-available/statistics/pages/list.inc.php
@@ -36,14 +36,14 @@ class SubPage
 		if (Module::isAvailable('runmode')) {
 			$xtra .= ', runmode.module AS rmmodule, runmode.isclient';
 			if (strpos($join, 'runmode') === false) {
-				$join .= ' LEFT JOIN runmode USING (machineuuid) ';
+				$join .= ' LEFT JOIN runmode ON (m.machineuuid = runmode.machineuuid) ';
 			}
 		}
 		$res = Database::simpleQuery("SELECT m.machineuuid, m.locationid, m.macaddr, m.clientip, m.lastseen,
 			m.logintime, m.state, m.currentuser, m.currentrunmode, m.realcores, m.mbram, m.kvmstate, m.cpumodel, m.id44mb,
        	m.hostname, m.notes IS NOT NULL AS hasnotes,
 			m.badsectors, Count(s.machineuuid) AS confvars $xtra FROM machine m
-			LEFT JOIN setting_machine s USING (machineuuid)
+			LEFT JOIN setting_machine s ON (m.machineuuid = s.machineuuid)
 			$join WHERE $where GROUP BY m.machineuuid", $args);
 		$rows = array();
 		$singleMachine = 'none';
diff --git a/modules-available/statistics/pages/machine.inc.php b/modules-available/statistics/pages/machine.inc.php
index 4e8f9f34..0d91b017 100644
--- a/modules-available/statistics/pages/machine.inc.php
+++ b/modules-available/statistics/pages/machine.inc.php
@@ -508,11 +508,15 @@ class SubPage
 				$hdd[$row['prop']] = $row['numeric'] ?? $row['value'];
 			}
 		}
+		$result = [];
 		foreach ($hdds as $k => &$hdd) {
+			if (substr($hdd['dev'] ?? '/dev/sr', 0, 7) === '/dev/sr')
+				continue;
 			$hdd['devid'] = 'k' . $k;
 			$hdd['partitions'] = array_values($hdd['partitions']);
+			$result[] = $hdd;
 		}
-		return array_values($hdds);
+		return $result;
 	}
 
 	private static function mangleHdd(array &$hdd)
@@ -702,4 +706,4 @@ class SubPage
 		return HardwareInfo::GPT[$type] ?? $type;
 	}
 
-}
\ No newline at end of file
+}
diff --git a/modules-available/statistics/pages/summary.inc.php b/modules-available/statistics/pages/summary.inc.php
index 4599b8b6..1adad7bd 100644
--- a/modules-available/statistics/pages/summary.inc.php
+++ b/modules-available/statistics/pages/summary.inc.php
@@ -247,8 +247,9 @@ class SubPage
 		$filterSet->makeFragments($where, $join, $args);
 		$args['cutoff'] = ceil(time() / 3600) * 3600 - 86400 * 10;
 
-		$res = Database::simpleQuery("SELECT machineuuid, clientip, hostname, firstseen, mbram, kvmstate, id44mb FROM machine m $join"
-			. " WHERE firstseen > :cutoff AND $where ORDER BY firstseen DESC LIMIT 32", $args);
+		$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);
 		$rows = array();
 		$count = 0;
 		foreach ($res as $row) {