diff options
Diffstat (limited to 'modules-available/statistics/inc/hardwarequery.inc.php')
| -rw-r--r-- | modules-available/statistics/inc/hardwarequery.inc.php | 169 |
1 files changed, 169 insertions, 0 deletions
diff --git a/modules-available/statistics/inc/hardwarequery.inc.php b/modules-available/statistics/inc/hardwarequery.inc.php new file mode 100644 index 00000000..6b1b5043 --- /dev/null +++ b/modules-available/statistics/inc/hardwarequery.inc.php @@ -0,0 +1,169 @@ +<?php + +class HardwareQuery +{ + + private $id = 0; + private $joins = []; + private $where = []; + private $args = []; + private $columns = []; + + /** + * @param string $type hardware type form HardwareInfo + * @param ?string $uuid If set, only return data for specific client + */ + 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)"; + } 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->where[] = 'shw.hwtype = :hwtype'; + $this->args['hwtype'] = $type; + } + + private function id(): string + { + return 'b' . (++$this->id); + } + + /** + * 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 addForeignJoin(bool $global, string $prop, string $jTable, string $jColumn, string $condColumn = '', $condVal = null) + { + if (isset($this->columns["$jTable.$prop"])) + return; + 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 + AND $tid.prop = :$pid)"; + $this->args[$pid] = $prop; + $this->columns[$prop] = "$tid.`value` AS `$prop`"; + $jtid = $this->id(); + $cond = ''; + if (!empty($condColumn)) { + $vid = $this->id(); + if (is_array($condVal)) { + $cond = " AND $jtid.`$condColumn` IN (:$vid)"; + } else { + $cond = " AND $jtid.`$condColumn` = :$vid"; + } + $this->args[$vid] = $condVal; + } + $this->joins[$jTable] = "INNER JOIN $jTable $jtid ON ($jtid.$jColumn = $tid.`value` $cond)"; + } + + public function addMachineWhere(string $column, string $op, $value) + { + if (isset($this->columns[$column])) + return; + $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 addGlobalColumn(string $prop): HardwareQueryColumn + { + return $this->addColumn(true, $prop); + } + + public function addLocalColumn(string $prop): HardwareQueryColumn + { + return $this->addColumn(false, $prop); + } + + public function addColumn(bool $global, string $prop, string $alias = null): HardwareQueryColumn + { + return $this->columns[] = new HardwareQueryColumn($global, $prop, $alias); + } + + /** + * Join the machine table and add the given column from it to the SELECT + */ + public function addMachineColumn(string $column): void + { + 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($groupBy = '') + { + return Database::simpleQuery($this->buildQuery($groupBy), $this->args); + } + + /** + * Build query string + * @param string[]|string $groupBy Column to group by + */ + public function buildQuery($groupBy = ''): string + { + if (empty($groupBy)) { + $groupBy = []; + } elseif (!is_array($groupBy)) { + $groupBy = [$groupBy]; + } + foreach ($groupBy as &$gb) { + if ($gb[0] !== '`') { + $gb = "`$gb`"; + } + } + $columns = []; + foreach ($this->columns as $column) { + if ($column instanceof HardwareQueryColumn) { + $column->generate($this->joins, $columns, $this->args, $groupBy); + } else { + $columns[] = $column; + } + } + $columns[] = 'mxhw.machineuuid'; + $columns[] = 'shw.hwid'; + // TODO: Untangle this implicit magic + 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 " . implode(', ', $groupBy); + } else { + $groupBy = ''; + } + return 'SELECT ' . implode(', ', $columns) + . ' FROM statistic_hw shw ' + . implode(' ', $this->joins) + . ' WHERE ' . implode(' AND ', $this->where) + . $groupBy; + } + +} |