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    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.waiting_time',
46            'wayTime' => 'process.way_time',
47            'processingTime' => 'process.processing_time',
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(`waiting_time` * 60))'
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            'waiting_time' => ($warteZeit > 0) ? $warteZeit : 0,
174            'way_time' => ($process->getWayMinutes() > 0) ? $process->getWayMinutes() : 0,
175            'processing_time' => ($bearbeitungszeit > 0) ? $bearbeitungszeit : 0,
176            'AnzahlPersonen' => $process->getClients()->count(),
177            'is_ticketprinter' => $isTicketprinter
178        ]);
179    }
180
181    public function postProcess($data)
182    {
183        $date = $data[$this->getPrefixed("appointments__0__date")] ?? '';
184        $data[$this->getPrefixed("appointments__0__date")] = strtotime($date === null ? '' : $date);
185        $callTime = $data[$this->getPrefixed("queue__callTime")] ?? '';
186        $data[$this->getPrefixed("queue__callTime")] = strtotime($callTime === null ? '' : $callTime);
187        $arrivalTime = $data[$this->getPrefixed("queue__arrivalTime")] ?? '';
188        $data[$this->getPrefixed("queue__arrivalTime")] = strtotime($arrivalTime === null ? '' : $arrivalTime);
189        if (isset($data[$this->getPrefixed('__clientsCount')])) {
190            $clientsCount = $data[$this->getPrefixed('__clientsCount')];
191            unset($data[$this->getPrefixed('__clientsCount')]);
192            while ($clientsCount-- > 0) {
193                $data[$this->getPrefixed('clients__' . $clientsCount . '__familyName')] = 'Unknown';
194            }
195        }
196        return $data;
197    }
198
199    private function getArchivedServices(\BO\Zmsentities\Process $process)
200    {
201        if ($process->getRequests()->count() === 0) {
202            return '';
203        }
204
205        $services = $process->getRequests()->getFirst()->name;
206
207        if ($process->getRequests()->count() > 1) {
208            $services .= ' +' . ($process->getRequests()->count() - 1);
209        }
210
211        return $services;
212    }
213
214    public function addConditionOlderThanDate(\DateTimeInterface $dateTime)
215    {
216        // Assuming 'Datum' is the column name that holds the date of the record
217        // and you want to select records older than the specified $dateTime
218        $this->query->where('Datum', '<', $dateTime->format('Y-m-d'));
219        return $this;
220    }
221}