path: root/modules-available/statistics/inc/hardwarequery.inc.php

<?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(string $type, $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 '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';
		}
	}

	/**
	 * @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;
		$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)
	{
		if (isset($this->columns[$jTable]))
			return;
		if (!isset($this->columns[$prop])) {
			$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`";
		}
		$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;
		$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);
		$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`";
	}

	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)
	{
		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`";
	}

	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(string $groupBy = '')
	{
		$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)
			. $groupBy;
		return Database::simpleQuery($query, $this->args);
	}

}