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 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 TABLE = 'nutzer';
46    const 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 QUERY_DELETE_USER_ROLES = '
72        DELETE FROM user_role WHERE user_id = ?
73    ';
74
75    const 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)
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    #[\Override]
128    public function getEntityMapping()
129    {
130        return [
131            'id' => 'useraccount.Name',
132            'password' => 'useraccount.Passworthash',
133            'lastLogin' => 'useraccount.lastUpdate',
134            'roles' => self::expression(
135                '(SELECT GROUP_CONCAT(DISTINCT r.name ORDER BY r.name SEPARATOR \',\') '
136                . 'FROM user_role ur '
137                . 'JOIN role r ON r.id = ur.role_id '
138                . 'WHERE ur.user_id = useraccount.NutzerID)'
139            ),
140            'permissions__appointment' => $this->permissionExists('appointment'),
141            'permissions__availability' => $this->permissionExists('availability'),
142            'permissions__calldisplay' => $this->permissionExists('calldisplay'),
143            'permissions__capacityreport' => $this->permissionExists('capacityreport'),
144            'permissions__cherrypick' => $this->permissionExists('cherrypick'),
145            'permissions__cluster' => $this->permissionExists('cluster'),
146            'permissions__config' => $this->permissionExists('config'),
147            'permissions__counter' => $this->permissionExists('counter'),
148            'permissions__customersearch' => $this->permissionExists('customersearch'),
149            'permissions__dayoff' => $this->permissionExists('dayoff'),
150            'permissions__department' => $this->permissionExists('department'),
151            'permissions__emergency' => $this->permissionExists('emergency'),
152            'permissions__finishedqueue' => $this->permissionExists('finishedqueue'),
153            'permissions__finishedqueuepast' => $this->permissionExists('finishedqueuepast'),
154            'permissions__jurisdiction' => $this->permissionExists('jurisdiction'),
155            'permissions__logs' => $this->permissionExists('logs'),
156            'permissions__mailtemplates' => $this->permissionExists('mailtemplates'),
157            'permissions__missedqueue' => $this->permissionExists('missedqueue'),
158            'permissions__openqueue' => $this->permissionExists('openqueue'),
159            'permissions__organisation' => $this->permissionExists('organisation'),
160            'permissions__overviewcalendar' => $this->permissionExists('overviewcalendar'),
161            'permissions__parkedqueue' => $this->permissionExists('parkedqueue'),
162            'permissions__restrictedscope' => $this->permissionExists('restrictedscope'),
163            'permissions__scope' => $this->permissionExists('scope'),
164            'permissions__source' => $this->permissionExists('source'),
165            'permissions__statistic' => $this->permissionExists('statistic'),
166            'permissions__ticketprinter' => $this->permissionExists('ticketprinter'),
167            'permissions__useraccount' => $this->permissionExists('useraccount'),
168            'permissions__waitingqueue' => $this->permissionExists('waitingqueue'),
169            'permissions__superuser' => $this->permissionExists('superuser'),
170        ];
171    }
172
173    public function addConditionLoginName($loginName)
174    {
175        $this->query->where('useraccount.Name', '=', $loginName);
176        return $this;
177    }
178
179    public function addConditionUserId($userId)
180    {
181        $this->query->where('useraccount.NutzerID', '=', $userId);
182        return $this;
183    }
184
185    public function addConditionPassword($password)
186    {
187        $this->query->where('useraccount.Passworthash', '=', $password);
188        return $this;
189    }
190
191    public function addConditionXauthKey($xAuthKey)
192    {
193        $this->query->where('useraccount.SessionID', '=', $xAuthKey);
194        $this->query->where('useraccount.SessionExpiry', '>', date('Y-m-d H:i:s', time() - App::SESSION_DURATION));
195        return $this;
196    }
197
198    public function addConditionRoleName(string $roleName): self
199    {
200        $this->setDistinctSelect();
201
202        $this->innerJoin(
203            new \BO\Zmsbackend\Query\Alias('user_role', 'useraccount_role'),
204            'useraccount.NutzerID',
205            '=',
206            'useraccount_role.user_id'
207        );
208
209        $this->innerJoin(
210            new \BO\Zmsbackend\Query\Alias('role', 'useraccount_role_name'),
211            'useraccount_role.role_id',
212            '=',
213            'useraccount_role_name.id'
214        );
215
216        $this->query->where('useraccount_role_name.name', '=', $roleName);
217
218        return $this;
219    }
220
221    public function addConditionSearch($queryString, $orWhere = false)
222    {
223        $condition = function (\BO\Zmsbackend\Query\Builder\ConditionBuilder $query) use ($queryString) {
224            $queryString = trim($queryString);
225            $query->orWith('useraccount.NutzerID', 'LIKE', "%$queryString%");
226            $query->orWith('useraccount.Name', 'LIKE', "%$queryString%");
227        };
228        if ($orWhere) {
229            $this->query->orWhere($condition);
230        } else {
231            $this->query->where($condition);
232        }
233        return $this;
234    }
235
236    public function reverseEntityMapping(\BO\Zmsentities\Useraccount $entity)
237    {
238        $data = array();
239        $data['Name'] = $entity->id;
240        $data['Passworthash'] = (isset($entity->password)) ? $entity->password : null;
241        $data['BehoerdenID'] = 0;
242        if (!$entity->isSuperUser() && isset($entity->departments) && 0 < $entity->departments->count()) {
243            $data['BehoerdenID'] = $entity->departments->getFirst()->id;
244        }
245        //default values because of strict mode
246        $data['notrufinitiierung'] = 0;
247        $data['notrufantwort'] = 0;
248
249        $data = array_filter($data, function ($value) {
250            return ($value !== null && $value !== false);
251        });
252        return $data;
253    }
254
255    #[\Override]
256    public function postProcess($data)
257    {
258        $data[$this->getPrefixed("lastLogin")] = ('0000-00-00' != $data[$this->getPrefixed("lastLogin")]) ?
259            strtotime($data[$this->getPrefixed("lastLogin")]) :
260            null;
261
262        $rolesKey = $this->getPrefixed('roles');
263        $rawRoles = $data[$rolesKey] ?? null;
264        if ($rawRoles === null || $rawRoles === '') {
265            $data[$rolesKey] = [];
266        } elseif (is_string($rawRoles)) {
267            $data[$rolesKey] = array_values(array_filter(array_map('trim', explode(',', $rawRoles)), function ($v) {
268                return $v !== '';
269            }));
270        }
271
272        $permissionsPrefix = $this->getPrefixed('permissions__');
273        foreach ($data as $key => $value) {
274            if (0 === strpos($key, $permissionsPrefix)) {
275                $data[$key] = (bool) $value;
276            }
277        }
278
279        return $data;
280    }
281
282    public function addConditionDepartmentIds(array $departmentIds)
283    {
284        $this->setDistinctSelect();
285        $this->innerJoin(
286            new \BO\Zmsbackend\Query\Alias(static::TABLE_ASSIGNMENT, 'useraccount_department'),
287            'useraccount.NutzerID',
288            '=',
289            'useraccount_department.nutzerid'
290        );
291        $this->query->where('useraccount_department.behoerdenid', 'IN', $departmentIds);
292        return $this;
293    }
294
295    public function addConditionDepartmentIdsAndSearch(array $departmentIds, $queryString = null, $orWhere = false): self
296    {
297        $this->addConditionDepartmentIds($departmentIds);
298
299        if ($queryString) {
300            $this->addConditionSearch($queryString, $orWhere);
301        }
302
303        return $this;
304    }
305
306    public function addConditionExcludeSuperusers(): self
307    {
308        $this->setDistinctSelect();
309
310        $this->innerJoin(
311            new \BO\Zmsbackend\Query\Alias('user_role', 'exclude_superuser_user_role'),
312            'useraccount.NutzerID',
313            '=',
314            'exclude_superuser_user_role.user_id'
315        );
316
317        $this->innerJoin(
318            new \BO\Zmsbackend\Query\Alias('role', 'exclude_superuser_role'),
319            'exclude_superuser_user_role.role_id',
320            '=',
321            'exclude_superuser_role.id'
322        );
323
324        $this->query->where('exclude_superuser_role.name', '!=', 'system_admin');
325
326        return $this;
327    }
328
329    public function addOrderByName(): self
330    {
331        $this->query->orderBy('useraccount.Name', 'ASC');
332        return $this;
333    }
334
335    /**
336     * @SuppressWarnings(UnusedFormalParameter)
337     */
338    public function addConditionWorkstationAccess($workstationUserId, array $workstationDepartmentIds, $isWorkstationSuperuser = false): self
339    {
340        // Superusers can access all useraccounts, no filtering needed
341        if ($isWorkstationSuperuser) {
342            return $this;
343        }
344
345        $this->addConditionExcludeSuperusers();
346
347        // If no departments, only exclude superusers (already done above)
348        if (empty($workstationDepartmentIds)) {
349            return $this;
350        }
351
352        // Ensure we have a join to nutzerzuordnung for target useraccounts
353        $this->setDistinctSelect();
354        $this->innerJoin(
355            new \BO\Zmsbackend\Query\Alias(static::TABLE_ASSIGNMENT, 'useraccount_department'),
356            'useraccount.NutzerID',
357            '=',
358            'useraccount_department.nutzerid'
359        );
360
361        // Target useraccount must share at least one department with workstation user
362        $this->query->where('useraccount_department.behoerdenid', 'IN', $workstationDepartmentIds);
363
364        return $this;
365    }
366}