[statistics] Query builder WIP

3 files changed, 119 insertions, 4 deletions

diff --git a/modules-available/statistics/api.inc.php b/modules-available/statistics/api.inc.php
index 72fe4e7b..0a194f2e 100644
--- a/modules-available/statistics/api.inc.php
+++ b/modules-available/statistics/api.inc.php
@@ -1,6 +1,15 @@
 <?php
 
 if (Request::any('action') === 'test' && isLocalExecution()) {
+	$x = new HardwareQuery(HardwareInfo::PCI_DEVICE);
+	//$x->addCompare(false, 'Memory Slot Occupied', '>=', true, 'Memory Slot Count');
+	$x->addWhere(true, 'vendor', '=', '8086');
+	$x->addColumn(true, 'device');
+	$res = $x->query();
+	foreach ($res as $row) {
+		error_log(json_encode($row));
+	}
+	exit;
 	HardwareParser::parseMachine('0A5D9E23-80F4-9C43-912C-96D80AE7E80B',
 		file_get_contents('/tmp/bla.json'));
 	echo 'Kweries: ' . Database::getQueryCount();
diff --git a/modules-available/statistics/inc/hardwareparser.inc.php b/modules-available/statistics/inc/hardwareparser.inc.php
index f3fe9289..d356e226 100644
--- a/modules-available/statistics/inc/hardwareparser.inc.php
+++ b/modules-available/statistics/inc/hardwareparser.inc.php
@@ -630,10 +630,10 @@ class HardwareParser
 				$globalMainboardExtra['Memory Slot Count'] += $mem['Number Of Devices'];
 			}
 			if (isset($mem['Maximum Capacity'])) {
-				$globalMainboardExtra['Memory Maximum Capacity'] += HardwareParser::convertSize($mem['Maximum Capacity'], 'M', false);
+				$globalMainboardExtra['Memory Maximum Capacity'] += self::convertSize($mem['Maximum Capacity'], 'M', false);
 			}
 		}
-		$globalMainboardExtra['Memory Maximum Capacity'] = HardwareParser::convertSize($globalMainboardExtra['Memory Maximum Capacity'] . ' MB');
+		$globalMainboardExtra['Memory Maximum Capacity'] = self::convertSize($globalMainboardExtra['Memory Maximum Capacity'] . ' MB');
 		// BIOS section - need to cross-match this with mainboard or system model, as it doesn't have a meaningful
 		// identifier on its own
 		$bios = self::prepareDmiProperties(self::getDmiHandles($data, 0)[0]);
@@ -652,7 +652,7 @@ class HardwareParser
 		}
 		// Using the general helper function
 		$ramModCount = self::updateHwTypeFromDmi($uuid, $data, 17, HardwareInfo::RAM_MODULE, function (array $flat): bool {
-			return HardwareParser::convertSize(($flat['Size'] ?? 0), '', false) > 65 * 1024 * 1024;
+			return self::convertSize(($flat['Size'] ?? 0), '', false) > 65 * 1024 * 1024;
 		},
 			['Locator'],
 			['Data Width',
@@ -845,7 +845,7 @@ class HardwareParser
 	 */
 	private static function fixManufacturer(string $in): string
 	{
-		$in = HardwareParser::decodeJedec($in);
+		$in = self::decodeJedec($in);
 		switch (strtolower($in)) {
 		case 'advanced micro devices, inc.':
 		case 'advanced micro devices':
diff --git a/modules-available/statistics/inc/hardwarequery.inc.php b/modules-available/statistics/inc/hardwarequery.inc.php
new file mode 100644
index 00000000..7ccde2f6
--- /dev/null
+++ b/modules-available/statistics/inc/hardwarequery.inc.php
@@ -0,0 +1,106 @@
+<?php
+
+class HardwareQuery
+{
+
+	private $id = 0;
+	private $joins = [];
+	private $where = [];
+	private $args = [];
+	private $columns = [];
+
+	/**
+	 * @param string $type type form HardwareInfo
+	 * @param ?string $uuid
+	 */
+	public function __construct($type, $uuid = null, $connectedOnly = true)
+	{
+		if ($connectedOnly) {
+			$this->where[] = 'mxhw.disconnecttime = 0';
+		}
+		if ($uuid !== null) {
+			$this->where[] = 'mxhw.machineuuid = :uuid';
+			$this->args['uuid'] = $uuid;
+		}
+		$this->joins[] = "INNER JOIN statistic_hw shw ON (mxhw.hwid = shw.hwid AND shw.hwtype = :hwtype)";
+		$this->args['hwtype'] = $type;
+	}
+
+	private function id(): string
+	{
+		return 't' . (++$this->id);
+	}
+
+	private function fillTableVars(bool $global, &$srcTable, &$table, &$column)
+	{
+		if ($global) {
+			$srcTable = 'shw';
+			$table = 'statistic_hw_prop';
+			$column = 'hwid';
+		} else {
+			$srcTable = 'mxhw';
+			$table = 'machine_x_hw_prop';
+			$column = 'machinehwid';
+		}
+	}
+
+	public function addWhere(bool $global, string $prop, string $op, string $value)
+	{
+		if (isset($this->columns[$prop]))
+			return;
+		$this->fillTableVars($global, $srcTable, $table, $column);
+		$tid = $this->id();
+		$pid = $this->id();
+		$vid = $this->id();
+		$valueCol = ($op === '<' || $op === '>' || $op === '<=' || $op === '>=') ? 'numeric' : 'value';
+		$this->joins[$prop] = "INNER JOIN $table $tid ON ($srcTable.$column = $tid.$column AND
+			$tid.prop = :$pid AND $tid.`$valueCol` $op :$vid)";
+		$this->args[$pid] = $prop;
+		$this->args[$vid] = $value;
+		$this->columns[$prop] = "$tid.`value` AS `$prop`";
+	}
+
+	public function addCompare(bool $global1, string $prop1, string $op, string $global2, string $prop2)
+	{
+		$this->fillTableVars($global1, $srcTable1, $table1, $column1);
+		$this->fillTableVars($global2, $srcTable2, $table2, $column2);
+		$tid1 = $this->id();
+		$pid1 = $this->id();
+		$tid2 = $this->id();
+		$pid2 = $this->id();
+		$valueCol = ($op === '<' || $op === '>' || $op === '<=' || $op === '>=') ? 'numeric' : 'value';
+		$this->joins[] = "INNER JOIN $table1 $tid1 ON ($srcTable1.$column1 = $tid1.$column1 AND
+			$tid1.prop = :$pid1)";
+		$this->joins[] = "INNER JOIN $table2 $tid2 ON ($srcTable2.$column2 = $tid2.$column2 AND
+			$tid2.prop = :$pid2 AND $tid1.`$valueCol` $op $tid2.`$valueCol`)";
+		$this->args[$pid1] = $prop1;
+		$this->args[$pid2] = $prop2;
+		$this->columns[$prop1] = "$tid1.`value` AS `$prop1`";
+	}
+
+	public function addColumn(bool $global, string $prop)
+	{
+		if (isset($this->columns[$prop]))
+			return;
+		$this->fillTableVars($global, $srcTable, $table, $column);
+		$tid = $this->id();
+		$pid = $this->id();
+		$this->joins[$prop] = "INNER JOIN $table $tid ON ($srcTable.$column = $tid.$column AND $tid.prop = :$pid)";
+		$this->args[$pid] = $prop;
+		$this->columns[$prop] = "$tid.`value` AS `$prop`";
+	}
+
+	/**
+	 * @return false|PDOStatement
+	 */
+	public function query()
+	{
+		$this->columns[] = 'mxhw.machineuuid';
+		$query = 'SELECT ' . implode(', ', $this->columns)
+			. ' FROM machine_x_hw mxhw '
+			. implode(' ', $this->joins)
+			. ' WHERE ' . implode(' AND ', $this->where);
+		return Database::simpleQuery($query, $this->args);
+	}
+
+}
\ No newline at end of file