1 files changed, 62 insertions, 11 deletions

diff --git a/modules-available/statistics/inc/hardwarequery.inc.php b/modules-available/statistics/inc/hardwarequery.inc.php
index 7ccde2f6..6b5662d4 100644
--- a/modules-available/statistics/inc/hardwarequery.inc.php
+++ b/modules-available/statistics/inc/hardwarequery.inc.php
@@ -13,16 +13,18 @@ class HardwareQuery
 	 * @param string $type type form HardwareInfo
 	 * @param ?string $uuid
 	 */
-	public function __construct($type, $uuid = null, $connectedOnly = true)
+	public function __construct(string $type, $uuid = null, $connectedOnly = true)
 	{
 		if ($connectedOnly) {
-			$this->where[] = 'mxhw.disconnecttime = 0';
+			$this->joins['mxhw_join'] = "INNER JOIN machine_x_hw mxhw ON (mxhw.hwid = shw.hwid AND mxhw.disconnecttime = 0)";
+		} else {
+			$this->joins['mxhw_join'] = "INNER JOIN machine_x_hw mxhw ON (mxhw.hwid = shw.hwid)";
 		}
 		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->where[] = 'shw.hwtype = :hwtype';
 		$this->args['hwtype'] = $type;
 	}
 
@@ -44,7 +46,13 @@ class HardwareQuery
 		}
 	}
 
-	public function addWhere(bool $global, string $prop, string $op, string $value)
+	/**
+	 * @param bool $global
+	 * @param string $prop
+	 * @param string $op
+	 * @param string|string[] $value
+	 */
+	public function addWhere(bool $global, string $prop, string $op, $value)
 	{
 		if (isset($this->columns[$prop]))
 			return;
@@ -54,12 +62,24 @@ class HardwareQuery
 		$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)";
+			$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 addMachineWhere(string $column, string $op, $value)
+	{
+		if (isset($this->columns[$column]))
+			return;
+		$valueCol = ($op === '<' || $op === '>' || $op === '<=' || $op === '>=') ? 'numeric' : 'value';
+		$vid = $this->id();
+		$this->joins['machine'] = 'INNER JOIN machine m USING (machineuuid)';
+		$this->where[] = "m.$column $op :$vid";
+		$this->args[$vid] = $value;
+		$this->columns[$column] = "m.$column";
+	}
+
 	public function addCompare(bool $global1, string $prop1, string $op, string $global2, string $prop2)
 	{
 		$this->fillTableVars($global1, $srcTable1, $table1, $column1);
@@ -76,6 +96,17 @@ class HardwareQuery
 		$this->args[$pid1] = $prop1;
 		$this->args[$pid2] = $prop2;
 		$this->columns[$prop1] = "$tid1.`value` AS `$prop1`";
+		$this->columns[$prop2] = "$tid2.`value` AS `$prop2`";
+	}
+
+	public function addGlobalColumn(string $prop)
+	{
+		$this->addColumn(true, $prop);
+	}
+
+	public function addLocalColumn(string $prop)
+	{
+		$this->addColumn(false, $prop);
 	}
 
 	public function addColumn(bool $global, string $prop)
@@ -85,21 +116,41 @@ class HardwareQuery
 		$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->joins[$prop] = "LEFT JOIN $table $tid ON ($srcTable.$column = $tid.$column AND $tid.prop = :$pid)";
 		$this->args[$pid] = $prop;
 		$this->columns[$prop] = "$tid.`value` AS `$prop`";
 	}
 
+	public function addMachineColumn(string $column)
+	{
+		if (isset($this->columns[$column]))
+			return;
+		$this->joins['machine'] = 'INNER JOIN machine m USING (machineuuid)';
+		$this->columns[$column] = "m.$column";
+	}
+
 	/**
 	 * @return false|PDOStatement
 	 */
-	public function query()
+	public function query(string $groupBy = '')
 	{
-		$this->columns[] = 'mxhw.machineuuid';
-		$query = 'SELECT ' . implode(', ', $this->columns)
-			. ' FROM machine_x_hw mxhw '
+		$columns = $this->columns;
+		$columns[] = 'mxhw.machineuuid';
+		$columns[] = 'shw.hwid';
+		if (empty($groupBy) || $groupBy === '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";
+		}
+		$query = 'SELECT ' . implode(', ', $columns)
+			. ' FROM statistic_hw shw '
 			. implode(' ', $this->joins)
-			. ' WHERE ' . implode(' AND ', $this->where);
+			. ' WHERE ' . implode(' AND ', $this->where)
+			. $groupBy;
 		return Database::simpleQuery($query, $this->args);
 	}