Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
89.38%
143 / 160
66.67%
10 / 15
 CRAP
0.00%
0 / 1
Useraccount
89.38%
143 / 160
66.67%
10 / 15
 31.08
0.00%
0 / 1
 permissionExists
92.31%
12 / 13
0.00%
0 / 1
 2.00
 getEntityMapping
100.00%
41 / 41
100.00%
1 / 1
 1
 addConditionLoginName
100.00%
2 / 2
100.00%
1 / 1
 1
 addConditionUserId
100.00%
2 / 2
100.00%
1 / 1
 1
 addConditionPassword
100.00%
2 / 2
100.00%
1 / 1
 1
 addConditionXauthKey
100.00%
3 / 3
100.00%
1 / 1
 1
 addConditionRoleName
100.00%
15 / 15
100.00%
1 / 1
 1
 addConditionSearch
0.00%
0 / 9
0.00%
0 / 1
 6
 reverseEntityMapping
100.00%
13 / 13
100.00%
1 / 1
 5
 postProcess
93.75%
15 / 16
0.00%
0 / 1
 7.01
 addConditionDepartmentIds
100.00%
9 / 9
100.00%
1 / 1
 1
 addConditionDepartmentIdsAndSearch
0.00%
0 / 4
0.00%
0 / 1
 6
 addConditionExcludeSuperusers
100.00%
15 / 15
100.00%
1 / 1
 1
 addOrderByName
100.00%
2 / 2
100.00%
1 / 1
 1
 addConditionWorkstationAccess
85.71%
12 / 14
0.00%
0 / 1
 3.03
1<?php
2
3namespace BO\Zmsbackend\Useraccount\Repository;
4
5use BO\Slim\Application as App;
6
7class Useraccount extends \BO\Zmsbackend\Query\Base implements \BO\Zmsbackend\Query\MappingInterface
8{
9    private const array VALID_PERMISSION_NAMES = [
10        'appointment',
11        'availability',
12        'calldisplay',
13        'capacityreport',
14        'cherrypick',
15        'cluster',
16        'config',
17        'counter',
18        'customersearch',
19        'dayoff',
20        'department',
21        'emergency',
22        'finishedqueue',
23        'finishedqueuepast',
24        'jurisdiction',
25        'logs',
26        'mailtemplates',
27        'missedqueue',
28        'openqueue',
29        'organisation',
30        'overviewcalendar',
31        'parkedqueue',
32        'restrictedscope',
33        'scope',
34        'source',
35        'statistic',
36        'ticketprinter',
37        'useraccount',
38        'waitingqueue',
39        'superuser',
40    ];
41
42    /**
43     * @var String TABLE mysql table reference
44     */
45    const string TABLE = 'nutzer';
46    const string TABLE_ASSIGNMENT = 'nutzerzuordnung';
47
48    const QUERY_READ_ID_BY_USERNAME = '
49        SELECT user.`NutzerID` AS id
50        FROM ' . self::TABLE . ' user
51        WHERE
52            user.`Name`=?
53    ';
54
55    const QUERY_WRITE_ASSIGNED_DEPARTMENTS = '
56        REPLACE INTO
57            ' . self::TABLE_ASSIGNMENT . '
58        SET
59            nutzerid=?,
60            behoerdenid=?
61    ';
62
63    const QUERY_DELETE_ASSIGNED_DEPARTMENTS = '
64        DELETE FROM
65            ' . self::TABLE_ASSIGNMENT . '
66        WHERE
67            nutzerid=?
68        ORDER BY behoerdenid
69    ';
70
71    const string QUERY_DELETE_USER_ROLES = '
72        DELETE FROM user_role WHERE user_id = ?
73    ';
74
75    const string QUERY_INSERT_USER_ROLES_BY_NAME = '
76        INSERT INTO user_role (user_id, role_id)
77        SELECT ?, r.id FROM role r WHERE r.name IN (:roleNames)
78    ';
79
80    const QUERY_READ_SUPERUSER_DEPARTMENTS = '
81        SELECT behoerde.`BehoerdenID` AS id
82        FROM ' . \BO\Zmsbackend\Department\Repository\Department::TABLE . '
83        ORDER BY behoerde.Name
84    ';
85
86    const QUERY_READ_ASSIGNED_DEPARTMENTS = '
87        SELECT userAssignment.`behoerdenid` AS id
88        FROM ' . self::TABLE_ASSIGNMENT . ' userAssignment
89        LEFT JOIN ' . self::TABLE . ' useraccount ON useraccount.Name = :useraccountName
90        WHERE
91            useraccount.`NutzerID` = userAssignment.`nutzerid`
92        ORDER BY userAssignment.`behoerdenid`
93    ';
94
95    const QUERY_READ_ASSIGNED_DEPARTMENTS_FOR_ALL = '
96        SELECT useraccount.Name as useraccountName,
97            userAssignment.`behoerdenid` AS id
98        FROM ' . self::TABLE_ASSIGNMENT . ' userAssignment
99        LEFT JOIN ' . self::TABLE . ' useraccount ON useraccount.NutzerID = userAssignment.nutzerid
100        WHERE
101            useraccount.Name IN (:useraccountNames)
102        ORDER BY useraccount.Name, userAssignment.`behoerdenid`
103    ';
104
105    /**
106     * Build an SQL expression that checks whether the current useraccount has
107     * a permission via user_role -> role_permission -> permission.
108     */
109    protected function permissionExists(string $permissionName): \BO\Zmsbackend\Query\Builder\Expression
110    {
111        if (!in_array($permissionName, self::VALID_PERMISSION_NAMES, true)) {
112            throw new \InvalidArgumentException("Invalid permission name: $permissionName");
113        }
114        $quoted = "'" . $permissionName . "'";
115        return self::expression(
116            'EXISTS('
117            . 'SELECT 1 '
118            . 'FROM user_role ur '
119            . 'JOIN role_permission rp ON rp.role_id = ur.role_id '
120            . 'JOIN permission p ON p.id = rp.permission_id '
121            . 'WHERE ur.user_id = useraccount.NutzerID '
122            . 'AND p.name = ' . $quoted
123            . ')'
124        );
125    }
126
127    /**
128     * @return (\BO\Zmsbackend\Query\Builder\Expression|mixed|string)[]
129     *
130     */
131    #[\Override]
132    public function getEntityMapping()
133    {
134        return [
135            'id' => 'useraccount.Name',
136            'password' => 'useraccount.Passworthash',
137            'lastLogin' => 'useraccount.lastUpdate',
138            'roles' => self::expression(
139                '(SELECT GROUP_CONCAT(DISTINCT r.name ORDER BY r.name SEPARATOR \',\') '
140                . 'FROM user_role ur '
141                . 'JOIN role r ON r.id = ur.role_id '
142                . 'WHERE ur.user_id = useraccount.NutzerID)'
143            ),
144            'permissions__appointment' => $this->permissionExists('appointment'),
145            'permissions__availability' => $this->permissionExists('availability'),
146            'permissions__calldisplay' => $this->permissionExists('calldisplay'),
147            'permissions__capacityreport' => $this->permissionExists('capacityreport'),
148            'permissions__cherrypick' => $this->permissionExists('cherrypick'),
149            'permissions__cluster' => $this->permissionExists('cluster'),
150            'permissions__config' => $this->permissionExists('config'),
151            'permissions__counter' => $this->permissionExists('counter'),
152            'permissions__customersearch' => $this->permissionExists('customersearch'),
153            'permissions__dayoff' => $this->permissionExists('dayoff'),
154            'permissions__department' => $this->permissionExists('department'),
155            'permissions__emergency' => $this->permissionExists('emergency'),
156            'permissions__finishedqueue' => $this->permissionExists('finishedqueue'),
157            'permissions__finishedqueuepast' => $this->permissionExists('finishedqueuepast'),
158            'permissions__jurisdiction' => $this->permissionExists('jurisdiction'),
159            'permissions__logs' => $this->permissionExists('logs'),
160            'permissions__mailtemplates' => $this->permissionExists('mailtemplates'),
161            'permissions__missedqueue' => $this->permissionExists('missedqueue'),
162            'permissions__openqueue' => $this->permissionExists('openqueue'),
163            'permissions__organisation' => $this->permissionExists('organisation'),
164            'permissions__overviewcalendar' => $this->permissionExists('overviewcalendar'),
165            'permissions__parkedqueue' => $this->permissionExists('parkedqueue'),
166            'permissions__restrictedscope' => $this->permissionExists('restrictedscope'),
167            'permissions__scope' => $this->permissionExists('scope'),
168            'permissions__source' => $this->permissionExists('source'),
169            'permissions__statistic' => $this->permissionExists('statistic'),
170            'permissions__ticketprinter' => $this->permissionExists('ticketprinter'),
171            'permissions__useraccount' => $this->permissionExists('useraccount'),
172            'permissions__waitingqueue' => $this->permissionExists('waitingqueue'),
173            'permissions__superuser' => $this->permissionExists('superuser'),
174        ];
175    }
176
177    public function addConditionLoginName($loginName): static
178    {
179        $this->query->where('useraccount.Name', '=', $loginName);
180        return $this;
181    }
182
183    public function addConditionUserId($userId): static
184    {
185        $this->query->where('useraccount.NutzerID', '=', $userId);
186        return $this;
187    }
188
189    public function addConditionPassword($password): static
190    {
191        $this->query->where('useraccount.Passworthash', '=', $password);
192        return $this;
193    }
194
195    public function addConditionXauthKey(string $xAuthKey): static
196    {
197        $this->query->where('useraccount.SessionID', '=', $xAuthKey);
198        $this->query->where('useraccount.SessionExpiry', '>', date('Y-m-d H:i:s', time() - App::SESSION_DURATION));
199        return $this;
200    }
201
202    public function addConditionRoleName(string $roleName): self
203    {
204        $this->setDistinctSelect();
205
206        $this->innerJoin(
207            new \BO\Zmsbackend\Query\Alias('user_role', 'useraccount_role'),
208            'useraccount.NutzerID',
209            '=',
210            'useraccount_role.user_id'
211        );
212
213        $this->innerJoin(
214            new \BO\Zmsbackend\Query\Alias('role', 'useraccount_role_name'),
215            'useraccount_role.role_id',
216            '=',
217            'useraccount_role_name.id'
218        );
219
220        $this->query->where('useraccount_role_name.name', '=', $roleName);
221
222        return $this;
223    }
224
225    public function addConditionSearch($queryString, bool $orWhere = false): static
226    {
227        $condition = function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($queryString) {
228            $queryString = trim($queryString);
229            $query->orWith('useraccount.NutzerID', 'LIKE', "%$queryString%");
230            $query->orWith('useraccount.Name', 'LIKE', "%$queryString%");
231        };
232        if ($orWhere) {
233            $this->query->orWhere($condition);
234        } else {
235            $this->query->where($condition);
236        }
237        return $this;
238    }
239
240    /**
241     * @return (int|mixed)[]
242     *
243     */
244    public function reverseEntityMapping(\BO\Zmsentities\Useraccount $entity): array
245    {
246        $data = array();
247        $data['Name'] = $entity->id;
248        $data['Passworthash'] = (isset($entity->password)) ? $entity->password : null;
249        $data['BehoerdenID'] = 0;
250        $department = $entity->getDepartmentList()->getFirst();
251        if (!$entity->isSuperUser() && $department !== null) {
252            $data['BehoerdenID'] = $department->id;
253        }
254        //default values because of strict mode
255        $data['notrufinitiierung'] = 0;
256        $data['notrufantwort'] = 0;
257
258        $data = array_filter($data, function ($value) {
259            return ($value !== null && $value !== false);
260        });
261        return $data;
262    }
263
264    #[\Override]
265    public function postProcess($data)
266    {
267        $data[$this->getPrefixed("lastLogin")] = ('0000-00-00' != $data[$this->getPrefixed("lastLogin")]) ?
268            strtotime($data[$this->getPrefixed("lastLogin")]) :
269            null;
270
271        $rolesKey = $this->getPrefixed('roles');
272        $rawRoles = $data[$rolesKey] ?? null;
273        if ($rawRoles === null || $rawRoles === '') {
274            $data[$rolesKey] = [];
275        } elseif (is_string($rawRoles)) {
276            $data[$rolesKey] = array_values(array_filter(array_map('trim', explode(',', $rawRoles)), function ($v) {
277                return $v !== '';
278            }));
279        }
280
281        $permissionsPrefix = $this->getPrefixed('permissions__');
282        foreach ($data as $key => $value) {
283            if (0 === strpos($key, $permissionsPrefix)) {
284                $data[$key] = (bool) $value;
285            }
286        }
287
288        return $data;
289    }
290
291    public function addConditionDepartmentIds(array $departmentIds): static
292    {
293        $this->setDistinctSelect();
294        $this->innerJoin(
295            new \BO\Zmsbackend\Query\Alias(static::TABLE_ASSIGNMENT, 'useraccount_department'),
296            'useraccount.NutzerID',
297            '=',
298            'useraccount_department.nutzerid'
299        );
300        $this->query->where('useraccount_department.behoerdenid', 'IN', $departmentIds);
301        return $this;
302    }
303
304    public function addConditionDepartmentIdsAndSearch(array $departmentIds, $queryString = null, bool $orWhere = false): self
305    {
306        $this->addConditionDepartmentIds($departmentIds);
307
308        if ($queryString) {
309            $this->addConditionSearch($queryString, $orWhere);
310        }
311
312        return $this;
313    }
314
315    public function addConditionExcludeSuperusers(): self
316    {
317        $this->setDistinctSelect();
318
319        $this->innerJoin(
320            new \BO\Zmsbackend\Query\Alias('user_role', 'exclude_superuser_user_role'),
321            'useraccount.NutzerID',
322            '=',
323            'exclude_superuser_user_role.user_id'
324        );
325
326        $this->innerJoin(
327            new \BO\Zmsbackend\Query\Alias('role', 'exclude_superuser_role'),
328            'exclude_superuser_user_role.role_id',
329            '=',
330            'exclude_superuser_role.id'
331        );
332
333        $this->query->where('exclude_superuser_role.name', '!=', 'system_admin');
334
335        return $this;
336    }
337
338    public function addOrderByName(): self
339    {
340        $this->query->orderBy('useraccount.Name', 'ASC');
341        return $this;
342    }
343
344    /**
345     * @SuppressWarnings(UnusedFormalParameter)
346     *
347     * @param false $isWorkstationSuperuser
348     */
349    public function addConditionWorkstationAccess($workstationUserId, array $workstationDepartmentIds, bool $isWorkstationSuperuser = false): self
350    {
351        // Superusers can access all useraccounts, no filtering needed
352        if ($isWorkstationSuperuser) {
353            return $this;
354        }
355
356        $this->addConditionExcludeSuperusers();
357
358        // If no departments, only exclude superusers (already done above)
359        if (empty($workstationDepartmentIds)) {
360            return $this;
361        }
362
363        // Ensure we have a join to nutzerzuordnung for target useraccounts
364        $this->setDistinctSelect();
365        $this->innerJoin(
366            new \BO\Zmsbackend\Query\Alias(static::TABLE_ASSIGNMENT, 'useraccount_department'),
367            'useraccount.NutzerID',
368            '=',
369            'useraccount_department.nutzerid'
370        );
371
372        // Target useraccount must share at least one department with workstation user
373        $this->query->where('useraccount_department.behoerdenid', 'IN', $workstationDepartmentIds);
374
375        return $this;
376    }
377}