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