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