Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
81.25%
91 / 112
61.54%
8 / 13
 CRAP
0.00%
0 / 1
ProcessStatusArchived
81.25%
91 / 112
61.54%
8 / 13
 38.75
0.00%
0 / 1
 getEntityMapping
100.00%
27 / 27
100.00%
1 / 1
 1
 addConditionArchiveId
100.00%
2 / 2
100.00%
1 / 1
 1
 addConditionScopeId
100.00%
2 / 2
100.00%
1 / 1
 1
 addConditionScopeIds
0.00%
0 / 6
0.00%
0 / 1
 6
 addConditionTime
100.00%
2 / 2
100.00%
1 / 1
 1
 addConditionTimes
0.00%
0 / 6
0.00%
0 / 1
 6
 addConditionIsMissed
100.00%
2 / 2
100.00%
1 / 1
 1
 addConditionWithAppointment
100.00%
2 / 2
100.00%
1 / 1
 1
 addJoinStatisticFailed
100.00%
15 / 15
100.00%
1 / 1
 1
 addValuesNewArchive
78.57%
22 / 28
0.00%
0 / 1
 12.19
 postProcess
100.00%
12 / 12
100.00%
1 / 1
 6
 getArchivedServices
83.33%
5 / 6
0.00%
0 / 1
 3.04
 addConditionOlderThanDate
0.00%
0 / 2
0.00%
0 / 1
 2
1<?php
2
3namespace BO\Zmsdb\Query;
4
5/**
6*
7* @SuppressWarnings(TooManyPublicMethods)
8* @SuppressWarnings(Complexity)
9 */
10class ProcessStatusArchived extends Base implements MappingInterface
11{
12    /**
13     *
14     * @var String TABLE mysql table reference
15     */
16    public const TABLE = 'buergerarchiv';
17    public const STATISTIC_TABLE = 'statistik';
18    public const ALIAS = 'process';
19
20    const QUERY_INSERT_IN_STATISTIC = '
21        INSERT INTO ' . self::STATISTIC_TABLE . ' SET
22            lastbuergerarchivid = :archiveId,
23            termin = :withAppointment,
24            datum = :date,
25            anliegenid = :requestId,
26            info_dl_id = :providerId,
27            standortid = :scopeId,
28            clusterid = :clusterId,
29            behoerdenid = :departmentId,
30            organisationsid = :organisationId,
31            kundenid = :ownerId,
32            bearbeitungszeit = :processingTime
33    ';
34
35    public function getEntityMapping()
36    {
37        return [
38            'archiveId' => 'process.BuergerarchivID',
39            'status' => self::expression('"archived"'),
40            'appointments__0__date' => self::expression(
41                'CONCAT(`process`.`Datum`, " ", `process`.`Timestamp`)'
42            ),
43            'scope__id' => 'process.StandortID',
44            '__clientsCount' => 'process.AnzahlPersonen',
45            'waitingTime' => 'process.wartezeit',
46            'wayTime' => 'process.wegezeit',
47            'processingTime' => 'process.bearbeitungszeit',
48            'name' => 'process.name',
49            'services' => 'process.dienstleistungen',
50            'queue__arrivalTime' => self::expression(
51                'CONCAT(`process`.`Datum`, " 00:00:00")'
52            ),
53            'queue__callTime' => self::expression(
54                'CONCAT(`process`.`Datum`, " ", SEC_TO_TIME(`wartezeit`))'
55            ),
56            'queue__withAppointment' => 'process.mitTermin',
57            'withAppointment' => 'process.mitTermin',
58            'queue__status' => self::expression(
59                'IF(`process`.`nicht_erschienen`,
60                    "missed",
61                    "finished"
62                )'
63            ),
64            'isTicketprinter' => 'process.is_ticketprinter',
65        ];
66    }
67
68    public function addConditionArchiveId($archiveId)
69    {
70        $this->query->where('process.BuergerarchivID', '=', $archiveId);
71        return $this;
72    }
73
74    public function addConditionScopeId($scopeId)
75    {
76        $this->query->where('process.StandortID', '=', $scopeId);
77        return $this;
78    }
79
80    public function addConditionScopeIds($scopeIds)
81    {
82        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $condition) use ($scopeIds) {
83            foreach ($scopeIds as $scopeId) {
84                $condition
85                    ->orWith('process.StandortID', '=', $scopeId);
86            }
87        });
88        return $this;
89    }
90
91
92    public function addConditionTime(\DateTimeInterface $now)
93    {
94        $this->query->where('process.Datum', '=', $now->format('Y-m-d'));
95        return $this;
96    }
97
98    public function addConditionTimes(array $dateTimes)
99    {
100        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $condition) use ($dateTimes) {
101            foreach ($dateTimes as $dateTime) {
102                $condition
103                    ->orWith('process.Datum', '=', $dateTime->format('Y-m-d'));
104            }
105        });
106        return $this;
107    }
108
109    public function addConditionIsMissed($missed)
110    {
111        $this->query->where('process.nicht_erschienen', '=', $missed);
112        return $this;
113    }
114
115    public function addConditionWithAppointment($withAppointment)
116    {
117        $this->query->where('process.mitTermin', '=', $withAppointment);
118        return $this;
119    }
120
121    public function addJoinStatisticFailed($dateTime, \BO\Zmsentities\Scope $scope)
122    {
123        //use existing index with StandortID and Datum
124        $this->leftJoin(
125            new Alias(self::STATISTIC_TABLE, 'statistic'),
126            self::expression('
127                statistic.StandortID = process.StandortID
128                AND statistic.Datum = process.Datum
129                AND `statistic`.`lastbuergerarchivid` = `process`.`BuergerarchivID`
130            ')
131        );
132        $this->query->where(function (\BO\Zmsdb\Query\Builder\ConditionBuilder $query) use ($dateTime, $scope) {
133            $query->andWith(
134                self::expression('statistic.lastbuergerarchivid IS NULL AND process.Datum'),
135                '>',
136                $dateTime->format('Y-m-d')
137            );
138            $query->andWith('process.nicht_erschienen', '=', 0);
139            $query->andWith('process.StandortID', '=', $scope->id);
140        });
141        return $this;
142    }
143    public function addValuesNewArchive(\BO\Zmsentities\Process $process, \DateTimeInterface $now)
144    {
145        $processingTimeStr = $process->getProcessingTime();
146        $bearbeitungszeit = null;
147
148        if (!empty($processingTimeStr)) {
149            list($hours, $minutes, $seconds) = explode(':', $processingTimeStr);
150            $totalMinutes = (double) ($hours * 60 + $minutes + $seconds / 60);
151            $bearbeitungszeit = $totalMinutes;
152        }
153
154        $waitingTimeStr = $process->getWaitingTime();
155        $warteZeit = null;
156
157        if (!empty($waitingTimeStr)) {
158            list($hours, $minutes, $seconds) = explode(':', $waitingTimeStr);
159            $totalMinutes = (double) ($hours * 60 + $minutes + $seconds / 60);
160            $warteZeit = $totalMinutes;
161        }
162
163        $isTicketprinter = isset($process->isTicketprinter) && $process->isTicketprinter ? 1 : 0;
164
165        $this->addValues([
166            'StandortID' => $process->scope['id'],
167            'name' => $process->getFirstClient()['familyName'],
168            'dienstleistungen' => $this->getArchivedServices($process),
169            'Datum' => $process->getFirstAppointment()->toDateTime()->format('Y-m-d'),
170            'mitTermin' => ($process->toQueue($now)->withAppointment) ? 1 : 0,
171            'nicht_erschienen' => ('missed' == $process->queue['status']) ? 1 : 0,
172            'Timestamp' => $process->getArrivalTime()->format('H:i:s'),
173            'wartezeit' => ($process->getWaitedSeconds() > 0) ? $process->getWaitedMinutes() : 0,
174            'wegezeit' => ($process->getWaySeconds() > 0) ? $process->getWayMinutes() : 0,
175            'bearbeitungszeit' => ($bearbeitungszeit > 0) ? $bearbeitungszeit : 0,
176            'wartezeit' => ($warteZeit > 0) ? $warteZeit : 0,
177            'AnzahlPersonen' => $process->getClients()->count(),
178            'is_ticketprinter' => $isTicketprinter
179        ]);
180    }
181
182    public function postProcess($data)
183    {
184        $date = $data[$this->getPrefixed("appointments__0__date")] ?? '';
185        $data[$this->getPrefixed("appointments__0__date")] = strtotime($date === null ? '' : $date);
186        $callTime = $data[$this->getPrefixed("queue__callTime")] ?? '';
187        $data[$this->getPrefixed("queue__callTime")] = strtotime($callTime === null ? '' : $callTime);
188        $arrivalTime = $data[$this->getPrefixed("queue__arrivalTime")] ?? '';
189        $data[$this->getPrefixed("queue__arrivalTime")] = strtotime($arrivalTime === null ? '' : $arrivalTime);
190        if (isset($data[$this->getPrefixed('__clientsCount')])) {
191            $clientsCount = $data[$this->getPrefixed('__clientsCount')];
192            unset($data[$this->getPrefixed('__clientsCount')]);
193            while ($clientsCount-- > 0) {
194                $data[$this->getPrefixed('clients__' . $clientsCount . '__familyName')] = 'Unknown';
195            }
196        }
197        return $data;
198    }
199
200    private function getArchivedServices(\BO\Zmsentities\Process $process)
201    {
202        if ($process->getRequests()->count() === 0) {
203            return '';
204        }
205
206        $services = $process->getRequests()->getFirst()->name;
207
208        if ($process->getRequests()->count() > 1) {
209            $services .= ' +' . ($process->getRequests()->count() - 1);
210        }
211
212        return $services;
213    }
214
215    public function addConditionOlderThanDate(\DateTimeInterface $dateTime)
216    {
217        // Assuming 'Datum' is the column name that holds the date of the record
218        // and you want to select records older than the specified $dateTime
219        $this->query->where('Datum', '<', $dateTime->format('Y-m-d'));
220        return $this;
221    }
222}