diff options
Diffstat (limited to 'modules-available/statistics/inc/hardwarequery.inc.php')
-rw-r--r-- | modules-available/statistics/inc/hardwarequery.inc.php | 129 |
1 files changed, 53 insertions, 76 deletions
diff --git a/modules-available/statistics/inc/hardwarequery.inc.php b/modules-available/statistics/inc/hardwarequery.inc.php index 24b27190..525e05e8 100644 --- a/modules-available/statistics/inc/hardwarequery.inc.php +++ b/modules-available/statistics/inc/hardwarequery.inc.php @@ -10,10 +10,10 @@ class HardwareQuery private $columns = []; /** - * @param string $type type form HardwareInfo - * @param ?string $uuid + * @param string $type hardware type form HardwareInfo + * @param ?string $uuid If set, only return data for specific client */ - public function __construct(string $type, $uuid = null, $connectedOnly = true) + public function __construct(string $type, string $uuid = null, $connectedOnly = true) { if ($connectedOnly) { $this->joins['mxhw_join'] = "INNER JOIN machine_x_hw mxhw ON (mxhw.hwid = shw.hwid AND mxhw.disconnecttime = 0)"; @@ -30,50 +30,33 @@ class HardwareQuery 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'; - } + return 'b' . (++$this->id); } /** - * @param bool $global - * @param string $prop - * @param string $op - * @param string|string[] $value + * Add join of a virtual column (hw property) to an arbitrary table and column. + * @param bool $global Is the virtual column global or local to machine? + * @param string $prop Name of property/virtual column + * @param string $jTable Table to join on + * @param string $jColumn Column to join on + * @param string $condColumn optionally, another column from the joined table to match against $condVal + * @param string|array $condVal optionally, a literal, or array of literals, to match foreign column against + * @return void */ - public function addWhere(bool $global, string $prop, string $op, $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 addJoin(bool $global, string $prop, string $jTable, string $jColumn, string $condColumn = '', $condVal = null) + public function addForeignJoin(bool $global, string $prop, string $jTable, string $jColumn, string $condColumn = '', $condVal = null) { if (isset($this->columns[$jTable])) return; if (!isset($this->columns[$prop])) { - $this->fillTableVars($global, $srcTable, $table, $column); + if ($global) { + $srcTable = 'shw'; + $table = 'statistic_hw_prop'; + $column = 'hwid'; + } else { + $srcTable = 'mxhw'; + $table = 'machine_x_hw_prop'; + $column = 'machinehwid'; + } $tid = $this->id(); $pid = $this->id(); $this->joins[$prop] = "INNER JOIN $table $tid ON ($srcTable.$column = $tid.$column @@ -99,55 +82,33 @@ class HardwareQuery { 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->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) + public function addGlobalColumn(string $prop): HardwareQueryColumn { - $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`"; - $this->columns[$prop2] = "$tid2.`value` AS `$prop2`"; + return $this->addColumn(true, $prop); } - public function addGlobalColumn(string $prop) + public function addLocalColumn(string $prop): HardwareQueryColumn { - $this->addColumn(true, $prop); + return $this->addColumn(false, $prop); } - public function addLocalColumn(string $prop) + public function addColumn(bool $global, string $prop, string $alias = null): HardwareQueryColumn { - $this->addColumn(false, $prop); - } - - 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] = "LEFT JOIN $table $tid ON ($srcTable.$column = $tid.$column AND $tid.prop = :$pid)"; - $this->args[$pid] = $prop; - $this->columns[$prop] = "$tid.`value` AS `$prop`"; + return $this->columns[] = new HardwareQueryColumn($global, $prop, $alias); } + /** + * Join the machine table and add the given column from it to the SELECT + * @param string $column + * @return void + */ public function addMachineColumn(string $column) { if (isset($this->columns[$column])) @@ -161,9 +122,26 @@ class HardwareQuery */ public function query(string $groupBy = '') { - $columns = $this->columns; + return Database::simpleQuery($this->buildQuery($groupBy), $this->args); + } + + /** + * Build query string + * @param string $groupBy Column to group by + */ + public function buildQuery(string $groupBy = ''): string + { + $columns = []; + foreach ($this->columns as $column) { + if ($column instanceof HardwareQueryColumn) { + $column->generate($this->joins, $columns, $this->args); + } else { + $columns[] = $column; + } + } $columns[] = 'mxhw.machineuuid'; $columns[] = 'shw.hwid'; + // TODO: Untangle this implicit magic if (empty($groupBy) || $groupBy === 'mxhw.machinehwid') { $columns[] = 'mxhw.disconnecttime'; } else { @@ -173,12 +151,11 @@ class HardwareQuery $columns[] = 'Count(*) AS group_count'; $groupBy = " GROUP BY $groupBy"; } - $query = 'SELECT ' . implode(', ', $columns) + return 'SELECT ' . implode(', ', $columns) . ' FROM statistic_hw shw ' . implode(' ', $this->joins) . ' WHERE ' . implode(' AND ', $this->where) . $groupBy; - return Database::simpleQuery($query, $this->args); } }
\ No newline at end of file |