Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
89.31%
142 / 159
66.67%
10 / 15
 CRAP
0.00%
0 / 1
Useraccount
89.31%
142 / 159
66.67%
10 / 15
 32.17
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%
12 / 12
100.00%
1 / 1
 6
 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        if (!$entity->isSuperUser() && isset($entity->departments) && 0 < $entity->departments->count()) {
251            $data['BehoerdenID'] = $entity->departments->getFirst()->id;
252        }
253        //default values because of strict mode
254        $data['notrufinitiierung'] = 0;
255        $data['notrufantwort'] = 0;
256
257        $data = array_filter($data, function ($value) {
258            return ($value !== null && $value !== false);
259        });
260        return $data;
261    }
262
263    #[\Override]
264    public function postProcess($data)
265    {
266        $data[$this->getPrefixed("lastLogin")] = ('0000-00-00' != $data[$this->getPrefixed("lastLogin")]) ?
267            strtotime($data[$this->getPrefixed("lastLogin")]) :
268            null;
269
270        $rolesKey = $this->getPrefixed('roles');
271        $rawRoles = $data[$rolesKey] ?? null;
272        if ($rawRoles === null || $rawRoles === '') {
273            $data[$rolesKey] = [];
274        } elseif (is_string($rawRoles)) {
275            $data[$rolesKey] = array_values(array_filter(array_map('trim', explode(',', $rawRoles)), function ($v) {
276                return $v !== '';
277            }));
278        }
279
280        $permissionsPrefix = $this->getPrefixed('permissions__');
281        foreach ($data as $key => $value) {
282            if (0 === strpos($key, $permissionsPrefix)) {
283                $data[$key] = (bool) $value;
284            }
285        }
286
287        return $data;
288    }
289
290    public function addConditionDepartmentIds(array $departmentIds): static
291    {
292        $this->setDistinctSelect();
293        $this->innerJoin(
294            new \BO\Zmsbackend\Query\Alias(static::TABLE_ASSIGNMENT, 'useraccount_department'),
295            'useraccount.NutzerID',
296            '=',
297            'useraccount_department.nutzerid'
298        );
299        $this->query->where('useraccount_department.behoerdenid', 'IN', $departmentIds);
300        return $this;
301    }
302
303    public function addConditionDepartmentIdsAndSearch(array $departmentIds, $queryString = null, bool $orWhere = false): self
304    {
305        $this->addConditionDepartmentIds($departmentIds);
306
307        if ($queryString) {
308            $this->addConditionSearch($queryString, $orWhere);
309        }
310
311        return $this;
312    }
313
314    public function addConditionExcludeSuperusers(): self
315    {
316        $this->setDistinctSelect();
317
318        $this->innerJoin(
319            new \BO\Zmsbackend\Query\Alias('user_role', 'exclude_superuser_user_role'),
320            'useraccount.NutzerID',
321            '=',
322            'exclude_superuser_user_role.user_id'
323        );
324
325        $this->innerJoin(
326            new \BO\Zmsbackend\Query\Alias('role', 'exclude_superuser_role'),
327            'exclude_superuser_user_role.role_id',
328            '=',
329            'exclude_superuser_role.id'
330        );
331
332        $this->query->where('exclude_superuser_role.name', '!=', 'system_admin');
333
334        return $this;
335    }
336
337    public function addOrderByName(): self
338    {
339        $this->query->orderBy('useraccount.Name', 'ASC');
340        return $this;
341    }
342
343    /**
344     * @SuppressWarnings(UnusedFormalParameter)
345     *
346     * @param false $isWorkstationSuperuser
347     */
348    public function addConditionWorkstationAccess($workstationUserId, array $workstationDepartmentIds, bool $isWorkstationSuperuser = false): self
349    {
350        // Superusers can access all useraccounts, no filtering needed
351        if ($isWorkstationSuperuser) {
352            return $this;
353        }
354
355        $this->addConditionExcludeSuperusers();
356
357        // If no departments, only exclude superusers (already done above)
358        if (empty($workstationDepartmentIds)) {
359            return $this;
360        }
361
362        // Ensure we have a join to nutzerzuordnung for target useraccounts
363        $this->setDistinctSelect();
364        $this->innerJoin(
365            new \BO\Zmsbackend\Query\Alias(static::TABLE_ASSIGNMENT, 'useraccount_department'),
366            'useraccount.NutzerID',
367            '=',
368            'useraccount_department.nutzerid'
369        );
370
371        // Target useraccount must share at least one department with workstation user
372        $this->query->where('useraccount_department.behoerdenid', 'IN', $workstationDepartmentIds);
373
374        return $this;
375    }
376}