1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
|
<?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->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 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] = "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 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);
}
}
|
