Lines 96.58% 113 / 117
Methods 96.55% 28 / 29
Classes 0.00% 0 / 1
Name Lines Methods CRAP
 [BO\Zmsbackend\Query\Base] __construct 88.23% 30 / 34 0.00% 0 / 1 10.16
 [BO\Zmsbackend\Query\Base] __toString n/a 0 / 0 n/a 0 / 0 4
 [BO\Zmsbackend\Query\Base] getName 100.00% 1 / 1 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] addSelect 100.00% 4 / 4 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] setDistinctSelect 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] setResolveLevel 100.00% 3 / 3 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] getResolveLevel 100.00% 3 / 3 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] getAlias 100.00% 5 / 5 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] getTablename 100.00% 3 / 3 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] addTable 100.00% 4 / 4 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] addTableAlias 100.00% 4 / 4 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] addRequiredJoins 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] addResolvedReferences 100.00% 11 / 11 100.00% 1 / 1 3
 [BO\Zmsbackend\Query\Base] setWithEntities 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] addJoin 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] leftJoin 100.00% 5 / 5 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] innerJoin 100.00% 5 / 5 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] getSql 100.00% 2 / 2 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] getParameters 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] getReferenceMapping 100.00% 2 / 2 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] expression 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] addEntityMapping 100.00% 3 / 3 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] getPrefixed 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] getPrefixedList 100.00% 4 / 4 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] addReferenceMapping 100.00% 3 / 3 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] addLimit 100.00% 4 / 4 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] addValues 100.00% 2 / 2 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] postProcess 100.00% 1 / 1 100.00% 1 / 1 1
 [BO\Zmsbackend\Query\Base] postProcessJoins 100.00% 4 / 4 100.00% 1 / 1 2
 [BO\Zmsbackend\Query\Base] shouldLoadEntity 100.00% 3 / 3 100.00% 1 / 1 2
5class ExchangeClientscope extends \BO\Zmsbackend\Query\Base
6{
7    /**
8     * @var String TABLE mysql table reference
9     */
10    const string TABLE = 'statistik';
11
12    const string BATABLE = 'buergeranliegen';
13
14    const QUERY_READ_REPORT = '
15    SELECT
16        MIN(subjectid) as subjectid,
17        date,
18        SUM(clientscount) as clientscount,
19        SUM(missed) as missed,
20        SUM(withappointment) as withappointment,
21        SUM(missedwithappointment) as missedwithappointment,
22        SUM(requestcount) as requestcount,
23        SUM(ticketprinter) as ticketprinter,
24        SUM(ticketprintermissed) as ticketprintermissed
25
26    FROM (
27          SELECT
28            StandortID as subjectid,          
29            IFNULL(DATE_FORMAT(`Datum`, :groupby), 0) as date,
30            SUM(IF(`nicht_erschienen`=0,AnzahlPersonen,0)) as clientscount,
31            SUM(IF(`nicht_erschienen`=1,AnzahlPersonen,0)) as missed,
32            SUM(IF(`nicht_erschienen`=0 AND mitTermin=1,AnzahlPersonen,0)) as withappointment,
33            SUM(IF(`nicht_erschienen`=1 AND mitTermin=1,AnzahlPersonen,0)) as missedwithappointment,
34            0 AS requestcount,
35            SUM(IF(`nicht_erschienen`=0 AND mitTermin=0 AND is_ticketprinter=1,AnzahlPersonen,0)) as ticketprinter,
36            SUM(IF(`nicht_erschienen`=1 AND mitTermin=0 AND is_ticketprinter=1,AnzahlPersonen,0)) as ticketprintermissed
37            FROM ' . \BO\Zmsbackend\Process\Repository\ProcessStatusArchived::TABLE . '
38            WHERE `StandortID` IN (:scopeids) AND `Datum` BETWEEN :datestart AND :dateend
39              GROUP BY date
40
41      UNION ALL
42          SELECT
43            StandortID as subjectid,
44            IFNULL(DATE_FORMAT(`Datum`, :groupby), 0) as date,
45            0 AS clientscount,
46            0 AS missed,
47            0 AS withappointment,
48            0 AS missedwithappointment,
49            COUNT(IF(ba.AnliegenID > 0, ba.AnliegenID, null)) as requestcount,
50            0 AS ticketprinter,
51            0 AS ticketprintermissed
52            FROM ' . \BO\Zmsbackend\Process\Repository\ProcessStatusArchived::TABLE . ' a
53              LEFT JOIN ' . self::BATABLE . ' as ba ON a.BuergerarchivID = ba.BuergerarchivID
54            WHERE `StandortID` IN (:scopeids) AND `Datum` BETWEEN :datestart AND :dateend AND nicht_erschienen=0
55            GROUP BY date
56      ) as unionresult
57      GROUP BY date;  
58    ';
59
60    const QUERY_SUBJECTS = '
61      SELECT
62          scope.`StandortID` as subject,
63          periodstart,
64          periodend,
65          CONCAT(scope.`Bezeichnung`, " ", scope.`standortinfozeile`) AS description
66      FROM ' . \BO\Zmsbackend\Query\Scope::TABLE . ' AS scope
67          INNER JOIN
68            (
69              SELECT
70                s.standortid as scopeid,
71                MIN(s.`datum`) AS periodstart,
72                MAX(s.`datum`) AS periodend
73              FROM ' . self::TABLE . ' s
74              group by scopeid
75            )
76          maxAndminDate ON maxAndminDate.`scopeid` = scope.`StandortID`
77      GROUP BY scope.`StandortID`
78      ORDER BY scope.`StandortID` ASC
79    ';
80
81    const QUERY_PERIODLIST_MONTH = '
82        SELECT date
83        FROM ' . \BO\Zmsbackend\Query\Scope::TABLE . ' AS scope
84            INNER JOIN (
85              SELECT
86                `StandortID`,
87                DATE_FORMAT(`Datum`,"%Y-%m") AS date
88              FROM ' . self::TABLE . '
89            ) s ON scope.`StandortID` = s.`standortid`
90        WHERE scope.`StandortID` = :scopeid
91        GROUP BY date
92        ORDER BY date ASC
93    ';
94}

Inherited from BO\Zmsbackend\Query\Base

96    public function __construct(
97        $queryType,
98        $prefix = '',
99        string|false $name = false,
100        mixed $resolveLevel = null,
101        array $withEntities = []
102    ) {
103        $this->prefix = $prefix;
104        $this->name = $name;
105        $this->withEntities = $withEntities;
106        $this->setResolveLevel($resolveLevel);
107        $dialect = new MySQL();
108        if (self::SELECT === $queryType) {
109            $this->query = new Select($dialect);
110            $this->addSelect();
111        } elseif (self::INSERT === $queryType) {
112            $this->query = new Insert($dialect);
113            $this->addTable();
114        } elseif (self::UPDATE === $queryType) {
115            $this->query = new Update($dialect);
116            $this->addTableAlias();
117        } elseif (self::REPLACE === $queryType) {
118            $this->query = new INSERT($dialect);
119            $this->query->queryBaseStatement('REPLACE INTO');
120            $this->addTable();
121        } elseif (self::DELETE === $queryType) {
122            $this->query = new Delete($dialect);
123            $this->query->queryBaseStatement('DELETE ' . $this::getAlias() . ' FROM');
124            $this->addTableAlias();
125        } elseif ($queryType instanceof self) {
126            $this->query = $queryType->query;
127            // Share join-alias state with the parent query (intentional by-ref).
128            /** @psalm-suppress UnsupportedPropertyReferenceUsage */
129            $this->joinedAliasList =& $queryType->joinedAliasList;
130            $this->resolveLevel = $queryType->resolveLevel - 1;
131        } elseif ($queryType instanceof QueryBuilder) {
132            $this->query = $queryType;
133        } else {
134            throw new \InvalidArgumentException(
135                'Unsupported query type for ' . static::class . ': '
136                . (is_object($queryType) ? $queryType::class : gettype($queryType))
137            );
138        }
139        if ($this->query instanceof Select) {
140            $this->addRequiredJoins();
141        }
142    }
147    public function __toString()
148    {
149        if ($this->name) {
150            $name = $this->name . '_' . $this->prefix . (string) $this->resolveLevel;
151            if (!isset(static::$sqlCache[$name])) {
152                static::$sqlCache[$name] = $this->getSql();
153            }
154            return static::$sqlCache[$name];
155        }
156        if ($this->currentSqlString) {
157            $sql = $this->currentSqlString;
158        } else {
159            $sql = $this->getSql();
160        }
161        return $sql;
162    }
164    public function getName()
165    {
166        return $this->name ? $this->name : get_class($this);
167    }
174    protected function addSelect(): static
175    {
176        $table = $this::getTablename();
177        $alias = $this::getAlias();
178        $this->query->from($table, $alias);
179        return $this;
180    }
182    public function setDistinctSelect(): void
183    {
184        $this->query->queryBaseStatement('SELECT DISTINCT');
185    }
187    public function setResolveLevel(int|null $resolveLevel): static
188    {
189        if ($resolveLevel !== null) {
190            $this->resolveLevel = $resolveLevel;
191        }
192        return $this;
193    }
195    public function getResolveLevel()
196    {
197        if (null === $this->resolveLevel) {
198            throw new \Exception("Required setting for resolveReferenceLevel missing in " . get_class($this));
199        }
200        return $this->resolveLevel;
201    }
210    public static function getAlias()
211    {
212        $class = get_called_class();
213        $alias = constant($class . '::ALIAS');
214        if (null === $alias) {
215            $alias = lcfirst(preg_replace('#^.*\\\#', '', $class));
216        }
217        return $alias;
218    }
225    public static function getTablename()
226    {
227        $class = get_called_class();
228        $table = constant($class . '::TABLE');
229        return $table;
230    }
237    protected function addTable(): static
238    {
239        $table = $this::getTablename();
240        $alias = $this::getAlias();
241        $this->query->table($table, $alias);
242        return $this;
243    }
250    protected function addTableAlias(): static
251    {
252        $table = $this::getTablename();
253        $alias = $this::getAlias();
254        $this->query->table(self::expression($table . ' ' . $alias));
255        return $this;
256    }
264    protected function addRequiredJoins()
265    {
266    }
273    public function addResolvedReferences($depth): static
274    {
275        $this->setResolveLevel($depth);
276        if ($depth > 0) {
277            $queryList = $this->addJoin();
278            foreach ($queryList as $query) {
279                $query->setResolveLevel($depth);
280                $query->setWithEntities($this->withEntities);
281                $query->addResolvedReferences($depth - 1);
282                $query->addEntityMapping();
283            }
284            $this->joinedQueryList = $queryList;
285        } else {
286            $this->addReferenceMapping();
287        }
288        return $this;
289    }
294    public function setWithEntities($withEntities = []): void
295    {
296        $this->withEntities = $withEntities;
297    }
304    protected function addJoin()
305    {
306        return [];
307    }
309    protected function leftJoin(Alias $alias, string|Expression|null $left = null, string|null $operator = null, string|null $right = null): QueryBuilder
310    {
311        $aliasId = $alias->getAliasIdentifier();
312        if (!in_array($aliasId, $this->joinedAliasList)) {
313            $this->joinedAliasList[] = $aliasId;
314            $this->query->leftJoin($alias, $left, $operator, $right);
315        } else {
316            //throw new \Exception("Tried to add Alias ".$aliasId);
317        }
318        return $this->query;
319    }
321    protected function innerJoin(Alias $alias, string|null $left = null, string|null $operator = null, string|null $right = null): QueryBuilder
322    {
323        $aliasId = $alias->getAliasIdentifier();
324        if (!in_array($aliasId, $this->joinedAliasList)) {
325            $this->joinedAliasList[] = $aliasId;
326            $this->query->join($alias, $left, $operator, $right);
327        }
328        return $this->query;
329    }
337    public function getSql()
338    {
339        $this->currentSqlString = (string)$this->query;
340        return $this->currentSqlString;
341    }
348    public function getParameters()
349    {
350        return $this->query->params();
351    }
357    public function getReferenceMapping()
358    {
359        return [
360        ];
361    }
368    protected static function expression(string $string)
369    {
370        return new Expression($string);
371    }
379    public function addEntityMapping(mixed $type = null): static
380    {
381        // Concrete query classes provide getEntityMapping(); not all declare MappingInterface.
382        /** @psalm-suppress UndefinedMethod, TooManyArguments */
383        $entityMapping = $this->getPrefixedList($this->getEntityMapping($type));
384        $this->query->select($entityMapping);
385        return $this;
386    }
388    protected function getPrefixed($prefix): string
389    {
390        return $this->prefix . $prefix;
391    }
393    protected function getPrefixedList(array $unprefixedList): array
394    {
395        $prefixed = [];
396        foreach ($unprefixedList as $key => $value) {
397            $prefixed[$this->getPrefixed($key)] = $value;
398        }
399        return $prefixed;
400    }
405    protected function addReferenceMapping(): static
406    {
407        $referenceMapping = $this->getPrefixedList($this->getReferenceMapping());
408        $this->query->select($referenceMapping);
409        return $this;
410    }
412    public function addLimit(int $count, ?int $offset = null): static
413    {
414        $this->query->limit($count);
415        if ($offset) {
416            $this->query->offset($offset);
417        }
418        return $this;
419    }
424    public function addValues(array $values): static
425    {
426        $this->query->values($values);
427        return $this;
428    }
434    public function postProcess($data)
435    {
436        return $data;
437    }
445    public function postProcessJoins(array $data)
446    {
447        $data = $this->postProcess($data);
448        foreach ($this->joinedQueryList as $query) {
449            $data = $query->postProcess($data);
450        }
451        return $data;
452    }
454    public function shouldLoadEntity(string $name): bool
455    {
456        if (empty($this->withEntities)) {
457            return true;
458        }
459
460        return in_array($name, $this->withEntities);
461    }