ILIAS  trunk Revision v11.0_alpha-3011-gc6b235a2e85
class.ilOrgUnitUserAssignmentDBRepository.php
Go to the documentation of this file.
1<?php
2
19declare(strict_types=1);
20
24
26{
27 public const TABLE_NAME = 'il_orgu_ua';
28 protected ilDBInterface $db;
31
33 {
34 $this->db = $db;
35
36 if ($handler) {
38 } else {
39 global $DIC;
40 $this->ilAppEventHandler = $DIC->event();
41 }
42 }
43
45 {
46 if (!isset($this->positionRepo)) {
48 $this->positionRepo = $dic["repo.Positions"];
49 }
50
52 }
53
54 public function get(int $user_id, int $position_id, int $orgu_id): ilOrgUnitUserAssignment
55 {
56 $query = 'SELECT id, user_id, position_id, orgu_id FROM' . PHP_EOL
57 . self::TABLE_NAME
58 . ' WHERE ' . self::TABLE_NAME . '.user_id = ' . $this->db->quote($user_id, 'integer') . PHP_EOL
59 . ' AND ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer') . PHP_EOL
60 . ' AND ' . self::TABLE_NAME . '.orgu_id = ' . $this->db->quote($orgu_id, 'integer');
61
62 $res = $this->db->query($query);
63 if ($res->numRows() > 0) {
64 $rec = $this->db->fetchAssoc($res);
65 return (new ilOrgUnitUserAssignment((int) $rec['id']))
66 ->withUserId((int) $rec['user_id'])
67 ->withPositionId((int) $rec['position_id'])
68 ->withOrguId((int) $rec['orgu_id']);
69 }
70
71 $assignment = (new ilOrgUnitUserAssignment())
72 ->withUserId($user_id)
73 ->withPositionId($position_id)
74 ->withOrguId($orgu_id);
75 $assignment = $this->store($assignment);
76 return $assignment;
77 }
78
79 public function find(int $user_id, int $position_id, int $orgu_id): ?ilOrgUnitUserAssignment
80 {
81 $query = 'SELECT id, user_id, position_id, orgu_id FROM' . PHP_EOL
82 . self::TABLE_NAME
83 . ' WHERE ' . self::TABLE_NAME . '.user_id = ' . $this->db->quote($user_id, 'integer') . PHP_EOL
84 . ' AND ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer') . PHP_EOL
85 . ' AND ' . self::TABLE_NAME . '.orgu_id = ' . $this->db->quote($orgu_id, 'integer');
86
87 $res = $this->db->query($query);
88 if ($res->numRows() === 0) {
89 return null;
90 }
91
92 $rec = $this->db->fetchAssoc($res);
93 return (new ilOrgUnitUserAssignment((int) $rec['id']))
94 ->withUserId((int) $rec['user_id'])
95 ->withPositionId((int) $rec['position_id'])
96 ->withOrguId((int) $rec['orgu_id']);
97 }
98
100 {
101 if ($assignment->getId() === 0) {
102 $assignment = $this->insert($assignment);
103 } else {
104 $this->update($assignment);
105 }
106
107 $this->raiseEvent('assignUserToPosition', $assignment);
108
109 return $assignment;
110 }
111
113 {
114 $id = $this->db->nextId(self::TABLE_NAME);
115
116 $values = [
117 'id' => [ 'integer', $id ],
118 'user_id' => [ 'integer', $assignment->getUserId() ],
119 'position_id' => [ 'integer', $assignment->getPositionId() ],
120 'orgu_id' => [ 'integer', $assignment->getOrguId() ]
121 ];
122
123 $this->db->insert(self::TABLE_NAME, $values);
124
125 $ret = (new ilOrgUnitUserAssignment($id))
126 ->withUserId($assignment->getUserId())
127 ->withPositionId($assignment->getPositionId())
128 ->withOrguId($assignment->getOrguId());
129
130 return $ret;
131 }
132
133 protected function update(ilOrgUnitUserAssignment $assignment): void
134 {
135 $where = [ 'id' => [ 'integer', $assignment->getId() ] ];
136
137 $values = [
138 'user_id' => [ 'integer', $assignment->getUserId() ],
139 'position_id' => [ 'integer', $assignment->getPositionId(),
140 'orgu_id' => [ 'integer', $assignment->getOrguId() ]]
141 ];
142
143 $this->db->update(self::TABLE_NAME, $values, $where);
144 }
145
146 public function delete(ilOrgUnitUserAssignment $assignment): bool
147 {
148 if ($assignment->getId() === 0) {
149 return false;
150 }
151
152 $query = 'DELETE FROM ' . self::TABLE_NAME . PHP_EOL
153 . ' WHERE id = ' . $this->db->quote($assignment->getId(), 'integer');
154 $rows = $this->db->manipulate($query);
155
156 if ($rows > 0) {
157 $this->raiseEvent('deassignUserFromPosition', $assignment);
158 return true;
159 }
160
161 return false;
162 }
163
164 public function deleteByUser(int $user_id): bool
165 {
166 if ($user_id <= 0) {
167 return false;
168 }
169
170 $query = 'DELETE FROM ' . self::TABLE_NAME . PHP_EOL
171 . ' WHERE user_id = ' . $this->db->quote($user_id, 'integer');
172 $rows = $this->db->manipulate($query);
173
174 if ($rows > 0) {
175 return true;
176 }
177
178 return false;
179 }
180
181 public function getByUsers(array $user_ids): array
182 {
183 $query = 'SELECT id, user_id, position_id, orgu_id FROM' . PHP_EOL
184 . self::TABLE_NAME
185 . ' WHERE ' . $this->db->in('user_id', $user_ids, false, 'integer');
186 $res = $this->db->query($query);
187 $ret = [];
188 while ($rec = $this->db->fetchAssoc($res)) {
189 $ret[] = (new ilOrgUnitUserAssignment((int) $rec['id']))
190 ->withUserId((int) $rec['user_id'])
191 ->withPositionId((int) $rec['position_id'])
192 ->withOrguId((int) $rec['orgu_id']);
193 }
194 return $ret;
195 }
196
197 public function getByPosition(int $position_id): array
198 {
199 $query = 'SELECT id, user_id, position_id, orgu_id FROM' . PHP_EOL
200 . self::TABLE_NAME
201 . ' WHERE ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer');
202 $res = $this->db->query($query);
203 $ret = [];
204 while ($rec = $this->db->fetchAssoc($res)) {
205 $ret[] = (new ilOrgUnitUserAssignment((int) $rec['id']))
206 ->withUserId((int) $rec['user_id'])
207 ->withPositionId((int) $rec['position_id'])
208 ->withOrguId((int) $rec['orgu_id']);
209 }
210 return $ret;
211 }
212
213 public function getByOrgUnit(int $orgu_id): array
214 {
215 $query = 'SELECT id, user_id, position_id, orgu_id FROM' . PHP_EOL
216 . self::TABLE_NAME
217 . ' WHERE ' . self::TABLE_NAME . '.orgu_id = ' . $this->db->quote($orgu_id, 'integer');
218 $res = $this->db->query($query);
219 $ret = [];
220 while ($rec = $this->db->fetchAssoc($res)) {
221 $ret[] = (new ilOrgUnitUserAssignment((int) $rec['id']))
222 ->withUserId((int) $rec['user_id'])
223 ->withPositionId((int) $rec['position_id'])
224 ->withOrguId((int) $rec['orgu_id']);
225 }
226 return $ret;
227 }
228
229
230 public function getByUserAndPosition(int $user_id, int $position_id): array
231 {
232 $query = 'SELECT id, user_id, position_id, orgu_id FROM' . PHP_EOL
233 . self::TABLE_NAME
234 . ' WHERE ' . self::TABLE_NAME . '.user_id = ' . $this->db->quote($user_id, 'integer') . PHP_EOL
235 . ' AND ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer');
236 $res = $this->db->query($query);
237 $ret = [];
238 while ($rec = $this->db->fetchAssoc($res)) {
239 $ret[] = (new ilOrgUnitUserAssignment((int) $rec['id']))
240 ->withUserId((int) $rec['user_id'])
241 ->withPositionId((int) $rec['position_id'])
242 ->withOrguId((int) $rec['orgu_id']);
243 }
244 return $ret;
245 }
246
247 public function getUsersByOrgUnits(array $orgu_ids): array
248 {
249 $query = 'SELECT user_id FROM' . PHP_EOL
250 . self::TABLE_NAME
251 . ' WHERE ' . $this->db->in(self::TABLE_NAME . '.orgu_id', $orgu_ids, false, 'integer');
252 $res = $this->db->query($query);
253 $users = [];
254 while ($rec = $this->db->fetchAssoc($res)) {
255 $users[] = (int) $rec['user_id'];
256 }
257 return $users;
258 }
259
260 public function getUsersByPosition(int $position_id): array
261 {
262 $query = 'SELECT user_id FROM' . PHP_EOL
263 . self::TABLE_NAME
264 . ' WHERE ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer');
265 $res = $this->db->query($query);
266 $users = [];
267 while ($rec = $this->db->fetchAssoc($res)) {
268 $users[] = (int) $rec['user_id'];
269 }
270 return $users;
271 }
272
273 public function getUsersByOrgUnitsAndPosition(array $orgu_ids, int $position_id): array
274 {
275 $query = 'SELECT user_id FROM' . PHP_EOL
276 . self::TABLE_NAME
277 . ' WHERE ' . $this->db->in(self::TABLE_NAME . '.orgu_id', $orgu_ids, false, 'integer') . PHP_EOL
278 . ' AND ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer');
279 $res = $this->db->query($query);
280 $users = [];
281 while ($rec = $this->db->fetchAssoc($res)) {
282 $users[] = (int) $rec['user_id'];
283 }
284 return $users;
285 }
286
287 public function getUsersByUserAndPosition(int $user_id, int $position_id, bool $recursive = false): array
288 {
289 $orgu_ids = $this->getOrgUnitsByUserAndPosition($user_id, $position_id, $recursive);
290
291 $query = 'SELECT user_id FROM' . PHP_EOL
292 . self::TABLE_NAME
293 . ' WHERE ' . $this->db->in(self::TABLE_NAME . '.orgu_id', $orgu_ids, false, 'integer');
294 $res = $this->db->query($query);
295 $users = [];
296 while ($rec = $this->db->fetchAssoc($res)) {
297 $users[] = (int) $rec['user_id'];
298 }
299 return $users;
300 }
301
302 public function getFilteredUsersByUserAndPosition(int $user_id, int $position_id, int $position_filter_id, bool $recursive = false): array
303 {
304 $orgu_ids = $this->getOrgUnitsByUserAndPosition($user_id, $position_id, $recursive);
305
306 $query = 'SELECT user_id FROM' . PHP_EOL
307 . self::TABLE_NAME
308 . ' WHERE ' . $this->db->in(self::TABLE_NAME . '.orgu_id', $orgu_ids, false, 'integer') . PHP_EOL
309 . ' AND ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_filter_id, 'integer');
310 $res = $this->db->query($query);
311 $users = [];
312 while ($rec = $this->db->fetchAssoc($res)) {
313 $users[] = (int) $rec['user_id'];
314 }
315 return $users;
316 }
317
318 public function getOrgUnitsByUser(int $user_id): array
319 {
320 $query = 'SELECT orgu_id FROM' . PHP_EOL
321 . self::TABLE_NAME
322 . ' WHERE ' . self::TABLE_NAME . '.user_id = ' . $this->db->quote($user_id, 'integer');
323 $res = $this->db->query($query);
324 $orgu_ids = [];
325 while ($rec = $this->db->fetchAssoc($res)) {
326 $orgu_ids[] = (int) $rec['orgu_id'];
327 }
328 return $orgu_ids;
329 }
330
331 public function getOrgUnitsByUserAndPosition(int $user_id, int $position_id, bool $recursive = false): array
332 {
333 $query = 'SELECT orgu_id FROM' . PHP_EOL
334 . self::TABLE_NAME
335 . ' WHERE ' . self::TABLE_NAME . '.user_id = ' . $this->db->quote($user_id, 'integer') . PHP_EOL
336 . ' AND ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer');
337 $res = $this->db->query($query);
338 $orgu_ids = [];
339 while ($rec = $this->db->fetchAssoc($res)) {
340 $orgu_ids[] = (int) $rec['orgu_id'];
341 }
342
343 if (!$recursive) {
344 return $orgu_ids;
345 }
346
347 $recursive_orgu_ids = [];
349 foreach ($orgu_ids as $orgu_id) {
350 $recursive_orgu_ids = array_merge($recursive_orgu_ids, $tree->getAllChildren($orgu_id));
351 }
352 return $recursive_orgu_ids;
353 }
354
355 public function getPositionsByUser(int $user_id): array
356 {
357 $query = 'SELECT DISTINCT position_id FROM' . PHP_EOL
358 . self::TABLE_NAME
359 . ' WHERE ' . self::TABLE_NAME . '.user_id = ' . $this->db->quote($user_id, 'integer');
360 $res = $this->db->query($query);
361
362 $positions = [];
363 while ($rec = $this->db->fetchAssoc($res)) {
364 $positions[] = $this->getPositionRepo()->getSingle((int) $rec['position_id'], 'id');
365 }
366 return $positions;
367 }
368
369 public function getSuperiorsByUsers(array $user_ids): array
370 {
371 $query = 'SELECT ' . PHP_EOL
372 . ' ua.orgu_id AS orgu_id,' . PHP_EOL
373 . ' ua.user_id AS empl,' . PHP_EOL
374 . ' ua2.user_id as sup' . PHP_EOL
375 . ' FROM' . PHP_EOL
376 . self::TABLE_NAME . ' as ua,' . PHP_EOL
377 . self::TABLE_NAME . ' as ua2' . PHP_EOL
378 . ' WHERE ua.orgu_id = ua2.orgu_id' . PHP_EOL
379 . ' AND ua.user_id <> ua2.user_id' . PHP_EOL
380 . ' AND ua.position_id = ' . $this->db->quote(ilOrgUnitPosition::CORE_POSITION_EMPLOYEE, 'integer') . PHP_EOL
381 . ' AND ua2.position_id = ' . $this->db->quote(ilOrgUnitPosition::CORE_POSITION_SUPERIOR, 'integer') . PHP_EOL
382 . ' AND ' . $this->db->in('ua.user_id', $user_ids, false, 'integer');
383 $res = $this->db->query($query);
384 if ($res->numRows() === 0) {
385 return [];
386 }
387
388 $ret = [];
389 while ($rec = $this->db->fetchAssoc($res)) {
390 $ret[$rec['empl']][] = $rec['sup'];
391 }
392 return $ret;
393 }
394
395 protected function raiseEvent(string $event, ilOrgUnitUserAssignment $assignment): void
396 {
397 $this->ilAppEventHandler->raise('components/ILIAS/OrgUnit', $event, array(
398 'obj_id' => $assignment->getOrguId(),
399 'usr_id' => $assignment->getUserId(),
400 'position_id' => $assignment->getPositionId()
401 ));
402 }
403
405 array $orgu_ids,
406 int $position_id,
407 bool $count_only = false,
408 ?Range $range = null,
409 ?Order $order = null
410 ) {
411 $sql_order_part = $order ? $order->join('ORDER BY', fn(...$o) => implode(' ', $o)) : '';
412 $sql_range_part = $range ? sprintf('LIMIT %2$s OFFSET %1$s', ...$range->unpack()) : '';
413
414 $query = 'SELECT usr_id, login, firstname, lastname, active, orgu_id' . PHP_EOL
415 . ', GROUP_CONCAT (od.title SEPARATOR ", ") as orgu_title ' . PHP_EOL
416 . 'FROM ' . self::TABLE_NAME . PHP_EOL
417 . 'JOIN usr_data ud on ud.usr_id = user_id' . PHP_EOL
418 . 'JOIN object_reference oref on oref.ref_id = orgu_id' . PHP_EOL
419 . 'JOIN object_data od on od.obj_id = oref.obj_id' . PHP_EOL
420 . 'WHERE ' . $this->db->in(self::TABLE_NAME . '.orgu_id', $orgu_ids, false, 'integer') . PHP_EOL
421 . 'AND ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer') . PHP_EOL
422 . 'group by login' . PHP_EOL
423 . $sql_order_part . PHP_EOL
424 . $sql_range_part . PHP_EOL
425 ;
426 $res = $this->db->query($query);
427
428 if ($count_only) {
429 return $this->db->numRows($res);
430 }
431
432 $users = [];
433 while ($rec = $this->db->fetchAssoc($res)) {
434 $rec['active'] = (bool) $rec['active'];
435 $users[] = $rec;
436 }
437 return $users;
438 }
439
440 public function getTotalRowCount(
441 ?array $filter_data,
442 ?array $additional_parameters
443 ): ?int {
444 $orgu_ids = $additional_parameters['orgu_ids'];
445 $position_id = $additional_parameters['position_id'];
446 return $this->getUserDataByOrgUnitsAndPosition($orgu_ids, $position_id, true);
447 }
448
449 public function getRows(
450 Table\DataRowBuilder $row_builder,
451 array $visible_column_ids,
453 Order $order,
454 ?array $filter_data,
455 ?array $additional_parameters
456 ): \Generator {
457 $orgu_ids = $additional_parameters['orgu_ids'];
458 $position_id = $additional_parameters['position_id'];
459 $lp_visible = $additional_parameters['lp_visible_ref_ids'];
460 $write_access = $additional_parameters['write_access'];
461
462 foreach ($this->getUserDataByOrgUnitsAndPosition($orgu_ids, $position_id, false, $range, $order) as $record) {
463 $row_id = implode('_', [(string) $position_id, (string) $record['usr_id']]);
464 yield $row_builder->buildDataRow($row_id, $record)
465 ->withDisabledAction(
466 'show_learning_progress',
467 !(
468 in_array($record['orgu_id'], $lp_visible)
471 )
472 )
473 ->withDisabledAction(
474 'remove',
475 !$write_access
476 );
477
478 }
479 }
480}
$id
plugin.php for ilComponentBuildPluginInfoObjectiveTest::testAddPlugins
Definition: plugin.php:23
Both the subject and the direction need to be specified when expressing an order.
Definition: Order.php:29
A simple class to express a naive range of whole positive numbers.
Definition: Range.php:29
Global event handler.
raise(string $a_component, string $a_event, array $a_parameter=[])
Raise an event.
getByOrgUnit(int $orgu_id)
Get all assignments for an org-unit.
getPositionsByUser(int $user_id)
Get all positions a user is assigned to.
getFilteredUsersByUserAndPosition(int $user_id, int $position_id, int $position_filter_id, bool $recursive=false)
Get all users with position $position_filter_id from those org-units, where the user has position $po...
getUsersByUserAndPosition(int $user_id, int $position_id, bool $recursive=false)
Get all users from org-units where the user has a certain position i.e.
__construct(ilDBInterface $db, ?ilAppEventHandler $handler=null)
getSuperiorsByUsers(array $user_ids)
Get all superiors of one or more users $user_id => [ $superior_ids ].
store(ilOrgUnitUserAssignment $assignment)
Store assignment to db.
getUsersByPosition(int $position_id)
Get all users with a certain position.
getTotalRowCount(?array $filter_data, ?array $additional_parameters)
getUserDataByOrgUnitsAndPosition(array $orgu_ids, int $position_id, bool $count_only=false, ?Range $range=null, ?Order $order=null)
getByUserAndPosition(int $user_id, int $position_id)
Get assignments for a user in a dedicated position.
getByUsers(array $user_ids)
Get assignments for one or more users.
deleteByUser(int $user_id)
Delete all assignments for a user_id Returns false if no assignments were found.
getUsersByOrgUnits(array $orgu_ids)
Get all users for a given set of org-units.
getRows(Table\DataRowBuilder $row_builder, array $visible_column_ids, Range $range, Order $order, ?array $filter_data, ?array $additional_parameters)
raiseEvent(string $event, ilOrgUnitUserAssignment $assignment)
find(int $user_id, int $position_id, int $orgu_id)
Find assignment for user, position and org-unit Does not create new assigment, returns null if no ass...
getByPosition(int $position_id)
Get all assignments for a position.
getOrgUnitsByUser(int $user_id)
Get all org-units a user is assigned to.
getOrgUnitsByUserAndPosition(int $user_id, int $position_id, bool $recursive=false)
Get all org-units where a user has a dedicated position.
getUsersByOrgUnitsAndPosition(array $orgu_ids, int $position_id)
Get all users in a specific position for a given set of org-units.
Class ilOrgUnitUserAssignment.
Interface ilDBInterface.
$dic
Definition: ltiresult.php:33
$res
Definition: ltiservices.php:69
$handler
Definition: oai.php:29
global $DIC
Definition: shib_login.php:26