class.ilOrgUnitUserAssignmentDBRepository.php

Go to the documentation of this file.

<?php

declare(strict_types=1);

use ILIAS\UI\Component\Table;
use ILIAS\Data\Range;
use ILIAS\Data\Order;

class ilOrgUnitUserAssignmentDBRepository implements OrgUnitUserAssignmentRepository, Table\DataRetrieval
{
    public const TABLE_NAME = 'il_orgu_ua';
    protected ilDBInterface $db;
    protected ilAppEventHandler $ilAppEventHandler;
    protected ilOrgUnitPositionDBRepository $positionRepo;

    public function __construct(ilDBInterface $db, ?ilAppEventHandler $handler = null)
    {
        $this->db = $db;

        if ($handler) {
            $this->ilAppEventHandler = $handler;
        } else {
            global $DIC;
            $this->ilAppEventHandler = $DIC->event();
        }
    }

    protected function getPositionRepo(): \ilOrgUnitPositionDBRepository
    {
        if (!isset($this->positionRepo)) {
            $dic = \ilOrgUnitLocalDIC::dic();
            $this->positionRepo = $dic["repo.Positions"];
        }

        return $this->positionRepo;
    }

    public function get(int $user_id, int $position_id, int $orgu_id): ilOrgUnitUserAssignment
    {
        $query = 'SELECT id, user_id, position_id, orgu_id FROM' . PHP_EOL
            . self::TABLE_NAME
            . ' WHERE ' . self::TABLE_NAME . '.user_id = ' . $this->db->quote($user_id, 'integer') . PHP_EOL
            . ' AND ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer') . PHP_EOL
            . ' AND ' . self::TABLE_NAME . '.orgu_id = ' . $this->db->quote($orgu_id, 'integer');

        $res = $this->db->query($query);
        if ($res->numRows() > 0) {
            $rec = $this->db->fetchAssoc($res);
            return (new ilOrgUnitUserAssignment((int) $rec['id']))
                ->withUserId((int) $rec['user_id'])
                ->withPositionId((int) $rec['position_id'])
                ->withOrguId((int) $rec['orgu_id']);
        }

        $assignment = (new ilOrgUnitUserAssignment())
            ->withUserId($user_id)
            ->withPositionId($position_id)
            ->withOrguId($orgu_id);
        $assignment = $this->store($assignment);
        return $assignment;
    }

    public function find(int $user_id, int $position_id, int $orgu_id): ?ilOrgUnitUserAssignment
    {
        $query = 'SELECT id, user_id, position_id, orgu_id FROM' . PHP_EOL
            . self::TABLE_NAME
            . ' WHERE ' . self::TABLE_NAME . '.user_id = ' . $this->db->quote($user_id, 'integer') . PHP_EOL
            . ' AND ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer') . PHP_EOL
            . ' AND ' . self::TABLE_NAME . '.orgu_id = ' . $this->db->quote($orgu_id, 'integer');

        $res = $this->db->query($query);
        if ($res->numRows() === 0) {
            return null;
        }

        $rec = $this->db->fetchAssoc($res);
        return (new ilOrgUnitUserAssignment((int) $rec['id']))
            ->withUserId((int) $rec['user_id'])
            ->withPositionId((int) $rec['position_id'])
            ->withOrguId((int) $rec['orgu_id']);
    }

    public function store(ilOrgUnitUserAssignment $assignment): ilOrgUnitUserAssignment
    {
        if ($assignment->getId() === 0) {
            $assignment = $this->insert($assignment);
        } else {
            $this->update($assignment);
        }

        $this->raiseEvent('assignUserToPosition', $assignment);

        return $assignment;
    }

    protected function insert(ilOrgUnitUserAssignment $assignment): ilOrgUnitUserAssignment
    {
        $id = $this->db->nextId(self::TABLE_NAME);

        $values = [
            'id' => [ 'integer', $id ],
            'user_id' => [ 'integer', $assignment->getUserId() ],
            'position_id' => [ 'integer', $assignment->getPositionId() ],
            'orgu_id' => [ 'integer', $assignment->getOrguId() ]
        ];

        $this->db->insert(self::TABLE_NAME, $values);

        $ret = (new ilOrgUnitUserAssignment($id))
            ->withUserId($assignment->getUserId())
            ->withPositionId($assignment->getPositionId())
            ->withOrguId($assignment->getOrguId());

        return $ret;
    }

    protected function update(ilOrgUnitUserAssignment $assignment): void
    {
        $where = [ 'id' => [ 'integer', $assignment->getId() ] ];

        $values = [
            'user_id' => [ 'integer', $assignment->getUserId() ],
            'position_id' => [ 'integer', $assignment->getPositionId(),
            'orgu_id' => [ 'integer', $assignment->getOrguId() ]]
        ];

        $this->db->update(self::TABLE_NAME, $values, $where);
    }

    public function delete(ilOrgUnitUserAssignment $assignment): bool
    {
        if ($assignment->getId() === 0) {
            return false;
        }

        $query = 'DELETE FROM ' . self::TABLE_NAME . PHP_EOL
            . ' WHERE id = ' . $this->db->quote($assignment->getId(), 'integer');
        $rows = $this->db->manipulate($query);

        if ($rows > 0) {
            $this->raiseEvent('deassignUserFromPosition', $assignment);
            return true;
        }

        return false;
    }

    public function deleteByUser(int $user_id): bool
    {
        if ($user_id <= 0) {
            return false;
        }

        $query = 'DELETE FROM ' . self::TABLE_NAME . PHP_EOL
            . ' WHERE user_id = ' . $this->db->quote($user_id, 'integer');
        $rows = $this->db->manipulate($query);

        if ($rows > 0) {
            return true;
        }

        return false;
    }

    public function getByUsers(array $user_ids): array
    {
        $query = 'SELECT id, user_id, position_id, orgu_id FROM' . PHP_EOL
            . self::TABLE_NAME
            . ' WHERE ' . $this->db->in('user_id', $user_ids, false, 'integer');
        $res = $this->db->query($query);
        $ret = [];
        while ($rec = $this->db->fetchAssoc($res)) {
            $ret[] = (new ilOrgUnitUserAssignment((int) $rec['id']))
                ->withUserId((int) $rec['user_id'])
                ->withPositionId((int) $rec['position_id'])
                ->withOrguId((int) $rec['orgu_id']);
        }
        return $ret;
    }

    public function getByPosition(int $position_id): array
    {
        $query = 'SELECT id, user_id, position_id, orgu_id FROM' . PHP_EOL
            . self::TABLE_NAME
            . ' WHERE ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer');
        $res = $this->db->query($query);
        $ret = [];
        while ($rec = $this->db->fetchAssoc($res)) {
            $ret[] = (new ilOrgUnitUserAssignment((int) $rec['id']))
                ->withUserId((int) $rec['user_id'])
                ->withPositionId((int) $rec['position_id'])
                ->withOrguId((int) $rec['orgu_id']);
        }
        return $ret;
    }

    public function getByOrgUnit(int $orgu_id): array
    {
        $query = 'SELECT id, user_id, position_id, orgu_id FROM' . PHP_EOL
            . self::TABLE_NAME
            . ' WHERE ' . self::TABLE_NAME . '.orgu_id = ' . $this->db->quote($orgu_id, 'integer');
        $res = $this->db->query($query);
        $ret = [];
        while ($rec = $this->db->fetchAssoc($res)) {
            $ret[] = (new ilOrgUnitUserAssignment((int) $rec['id']))
                ->withUserId((int) $rec['user_id'])
                ->withPositionId((int) $rec['position_id'])
                ->withOrguId((int) $rec['orgu_id']);
        }
        return $ret;
    }


    public function getByUserAndPosition(int $user_id, int $position_id): array
    {
        $query = 'SELECT id, user_id, position_id, orgu_id FROM' . PHP_EOL
            . self::TABLE_NAME
            . ' WHERE ' . self::TABLE_NAME . '.user_id = ' . $this->db->quote($user_id, 'integer') . PHP_EOL
            . ' AND ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer');
        $res = $this->db->query($query);
        $ret = [];
        while ($rec = $this->db->fetchAssoc($res)) {
            $ret[] = (new ilOrgUnitUserAssignment((int) $rec['id']))
                ->withUserId((int) $rec['user_id'])
                ->withPositionId((int) $rec['position_id'])
                ->withOrguId((int) $rec['orgu_id']);
        }
        return $ret;
    }

    public function getUsersByOrgUnits(array $orgu_ids): array
    {
        $query = 'SELECT user_id FROM' . PHP_EOL
            . self::TABLE_NAME
            . ' WHERE ' . $this->db->in(self::TABLE_NAME . '.orgu_id', $orgu_ids, false, 'integer');
        $res = $this->db->query($query);
        $users = [];
        while ($rec = $this->db->fetchAssoc($res)) {
            $users[] = (int) $rec['user_id'];
        }
        return $users;
    }

    public function getUsersByPosition(int $position_id): array
    {
        $query = 'SELECT user_id FROM' . PHP_EOL
            . self::TABLE_NAME
            . ' WHERE ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer');
        $res = $this->db->query($query);
        $users = [];
        while ($rec = $this->db->fetchAssoc($res)) {
            $users[] = (int) $rec['user_id'];
        }
        return $users;
    }

    public function getUsersByOrgUnitsAndPosition(array $orgu_ids, int $position_id): array
    {
        $query = 'SELECT user_id FROM' . PHP_EOL
            . self::TABLE_NAME
            . ' WHERE ' . $this->db->in(self::TABLE_NAME . '.orgu_id', $orgu_ids, false, 'integer') . PHP_EOL
            . ' AND ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer');
        $res = $this->db->query($query);
        $users = [];
        while ($rec = $this->db->fetchAssoc($res)) {
            $users[] = (int) $rec['user_id'];
        }
        return $users;
    }

    public function getUsersByUserAndPosition(int $user_id, int $position_id, bool $recursive = false): array
    {
        $orgu_ids = $this->getOrgUnitsByUserAndPosition($user_id, $position_id, $recursive);

        $query = 'SELECT user_id FROM' . PHP_EOL
            . self::TABLE_NAME
            . ' WHERE ' . $this->db->in(self::TABLE_NAME . '.orgu_id', $orgu_ids, false, 'integer');
        $res = $this->db->query($query);
        $users = [];
        while ($rec = $this->db->fetchAssoc($res)) {
            $users[] = (int) $rec['user_id'];
        }
        return $users;
    }

    public function getFilteredUsersByUserAndPosition(int $user_id, int $position_id, int $position_filter_id, bool $recursive = false): array
    {
        $orgu_ids = $this->getOrgUnitsByUserAndPosition($user_id, $position_id, $recursive);

        $query = 'SELECT user_id FROM' . PHP_EOL
            . self::TABLE_NAME
            . ' WHERE ' . $this->db->in(self::TABLE_NAME . '.orgu_id', $orgu_ids, false, 'integer') . PHP_EOL
            . ' AND ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_filter_id, 'integer');
        $res = $this->db->query($query);
        $users = [];
        while ($rec = $this->db->fetchAssoc($res)) {
            $users[] = (int) $rec['user_id'];
        }
        return $users;
    }

    public function getOrgUnitsByUser(int $user_id): array
    {
        $query = 'SELECT orgu_id FROM' . PHP_EOL
            . self::TABLE_NAME
            . ' WHERE ' . self::TABLE_NAME . '.user_id = ' . $this->db->quote($user_id, 'integer');
        $res = $this->db->query($query);
        $orgu_ids = [];
        while ($rec = $this->db->fetchAssoc($res)) {
            $orgu_ids[] = (int) $rec['orgu_id'];
        }
        return $orgu_ids;
    }

    public function getOrgUnitsByUserAndPosition(int $user_id, int $position_id, bool $recursive = false): array
    {
        $query = 'SELECT orgu_id FROM' . PHP_EOL
            . self::TABLE_NAME
            . ' WHERE ' . self::TABLE_NAME . '.user_id = ' . $this->db->quote($user_id, 'integer') . PHP_EOL
            . ' AND ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer');
        $res = $this->db->query($query);
        $orgu_ids = [];
        while ($rec = $this->db->fetchAssoc($res)) {
            $orgu_ids[] = (int) $rec['orgu_id'];
        }

        if (!$recursive) {
            return $orgu_ids;
        }

        $recursive_orgu_ids = [];
        $tree = ilObjOrgUnitTree::_getInstance();
        foreach ($orgu_ids as $orgu_id) {
            $recursive_orgu_ids = array_merge($recursive_orgu_ids, $tree->getAllChildren($orgu_id));
        }
        return $recursive_orgu_ids;
    }

    public function getPositionsByUser(int $user_id): array
    {
        $query = 'SELECT DISTINCT position_id FROM' . PHP_EOL
            . self::TABLE_NAME
            . ' WHERE ' . self::TABLE_NAME . '.user_id = ' . $this->db->quote($user_id, 'integer');
        $res = $this->db->query($query);

        $positions = [];
        while ($rec = $this->db->fetchAssoc($res)) {
            $positions[] = $this->getPositionRepo()->getSingle((int) $rec['position_id'], 'id');
        }
        return $positions;
    }

    public function getSuperiorsByUsers(array $user_ids): array
    {
        $query = 'SELECT ' . PHP_EOL
            . ' ua.orgu_id AS orgu_id,' . PHP_EOL
            . ' ua.user_id AS empl,' . PHP_EOL
            . ' ua2.user_id as sup' . PHP_EOL
            . ' FROM' . PHP_EOL
            . self::TABLE_NAME . ' as ua,' . PHP_EOL
            . self::TABLE_NAME . ' as ua2' . PHP_EOL
            . ' WHERE ua.orgu_id = ua2.orgu_id' . PHP_EOL
            . ' AND ua.user_id <> ua2.user_id' . PHP_EOL
            . ' AND ua.position_id = ' . $this->db->quote(ilOrgUnitPosition::CORE_POSITION_EMPLOYEE, 'integer') . PHP_EOL
            . ' AND ua2.position_id = ' . $this->db->quote(ilOrgUnitPosition::CORE_POSITION_SUPERIOR, 'integer') . PHP_EOL
            . ' AND ' . $this->db->in('ua.user_id', $user_ids, false, 'integer');
        $res = $this->db->query($query);
        if ($res->numRows() === 0) {
            return [];
        }

        $ret = [];
        while ($rec = $this->db->fetchAssoc($res)) {
            $ret[$rec['empl']][] = $rec['sup'];
        }
        return $ret;
    }

    protected function raiseEvent(string $event, ilOrgUnitUserAssignment $assignment): void
    {
        $this->ilAppEventHandler->raise('components/ILIAS/OrgUnit', $event, array(
            'obj_id' => $assignment->getOrguId(),
            'usr_id' => $assignment->getUserId(),
            'position_id' => $assignment->getPositionId()
        ));
    }

    protected function getUserDataByOrgUnitsAndPosition(
        array $orgu_ids,
        int $position_id,
        bool $count_only = false,
        ?Range $range = null,
        ?Order $order = null
    ) {
        $sql_order_part = $order ? $order->join('ORDER BY', fn(...$o) => implode(' ', $o)) : '';
        $sql_range_part = $range ? sprintf('LIMIT %2$s OFFSET %1$s', ...$range->unpack()) : '';

        $query = 'SELECT usr_id, login, firstname, lastname, active, orgu_id' . PHP_EOL
            . ', GROUP_CONCAT (od.title SEPARATOR ", ") as orgu_title ' . PHP_EOL
            . 'FROM ' . self::TABLE_NAME . PHP_EOL
            . 'JOIN usr_data ud on ud.usr_id = user_id' . PHP_EOL
            . 'JOIN object_reference oref on oref.ref_id = orgu_id' . PHP_EOL
            . 'JOIN object_data od on od.obj_id = oref.obj_id' . PHP_EOL
            . 'WHERE ' . $this->db->in(self::TABLE_NAME . '.orgu_id', $orgu_ids, false, 'integer') . PHP_EOL
            . 'AND ' . self::TABLE_NAME . '.position_id = ' . $this->db->quote($position_id, 'integer') . PHP_EOL
            . 'group by login' . PHP_EOL
            . $sql_order_part . PHP_EOL
            . $sql_range_part . PHP_EOL
        ;
        $res = $this->db->query($query);

        if ($count_only) {
            return $this->db->numRows($res);
        }

        $users = [];
        while ($rec = $this->db->fetchAssoc($res)) {
            $rec['active'] = (bool) $rec['active'];
            $users[] = $rec;
        }
        return $users;
    }

    public function getTotalRowCount(
        ?array $filter_data,
        ?array $additional_parameters
    ): ?int {
        $orgu_ids = $additional_parameters['orgu_ids'];
        $position_id = $additional_parameters['position_id'];
        return $this->getUserDataByOrgUnitsAndPosition($orgu_ids, $position_id, true);
    }

    public function getRows(
        Table\DataRowBuilder $row_builder,
        array $visible_column_ids,
        Range $range,
        Order $order,
        ?array $filter_data,
        ?array $additional_parameters
    ): \Generator {
        $orgu_ids = $additional_parameters['orgu_ids'];
        $position_id = $additional_parameters['position_id'];
        $lp_visible = $additional_parameters['lp_visible_ref_ids'];
        $write_access = $additional_parameters['write_access'];

        foreach ($this->getUserDataByOrgUnitsAndPosition($orgu_ids, $position_id, false, $range, $order) as $record) {
            $row_id = implode('_', [(string) $position_id, (string) $record['usr_id']]);
            yield $row_builder->buildDataRow($row_id, $record)
                ->withDisabledAction(
                    'show_learning_progress',
                    !(
                        in_array($record['orgu_id'], $lp_visible)
                        && ilObjUserTracking::_enabledLearningProgress()
                        && ilObjUserTracking::_enabledUserRelatedData()
                    )
                )
                ->withDisabledAction(
                    'remove',
                    !$write_access
                );

        }
    }
}