UserDataRepository.php

Go to the documentation of this file.

<?php

declare(strict_types=1);

namespace ILIAS\Certificate\API\Repository;

use ILIAS\Certificate\API\Data\UserCertificateDto;
use ILIAS\Certificate\API\Filter\UserDataFilter;
use ilCtrlInterface;
use ilDBConstants;
use ilUserCertificateApiGUI;
use ilDBInterface;

class UserDataRepository
{
    private readonly string $defaultTitle;

    public function __construct(
        private readonly ilDBInterface $database,
        private readonly ilCtrlInterface $ctrl,
        ?string $defaultTitle = null
    ) {
        if (null === $defaultTitle) {
            global $DIC;
            $defaultTitle = $DIC->language()->txt('certificate_no_object_title');
        }
        $this->defaultTitle = $defaultTitle;
    }

    public function getUserData(UserDataFilter $filter, array $ilCtrlStack): array
    {
        $sql = 'SELECT
    cert.pattern_certificate_id,
    cert.obj_id,
    cert.usr_id,
    cert.user_name,
    cert.acquired_timestamp,
    cert.currently_active,
    cert.id,
    cert.title,
    cert.ref_id,
    usr_data.firstname,
    usr_data.lastname,
    usr_data.email,
    usr_data.login,
    usr_data.second_email
FROM
' . $this->getQuery($filter);

        $query = $this->database->query($sql);

        if ([] !== $ilCtrlStack) {
            $ilCtrlStack[] = ilUserCertificateApiGUI::class;
        }

        $result = [];
        while ($row = $this->database->fetchAssoc($query)) {
            $id = (int) $row['id'];

            if (isset($result[$id])) {
                $result[$id]->addRefId((int) $row['ref_id']);
                continue;
            }

            $link = '';
            if ([] !== $ilCtrlStack) {
                $this->ctrl->setParameterByClass(ilUserCertificateApiGUI::class, 'certificate_id', $id);
                $link = $this->ctrl->getLinkTargetByClass($ilCtrlStack, ilUserCertificateApiGUI::CMD_DOWNLOAD);
                $this->ctrl->clearParametersByClass(ilUserCertificateApiGUI::class);
            }

            $dataObject = new UserCertificateDto(
                $id,
                $row['title'] ?? $this->defaultTitle,
                (int) $row['obj_id'],
                (int) $row['acquired_timestamp'],
                (int) $row['usr_id'],
                $row['firstname'],
                $row['lastname'],
                $row['login'],
                (string) $row['email'],
                (string) $row['second_email'],
                [(int) $row['ref_id']],
                $link
            );

            $result[$id] = $dataObject;
        }

        if ($filter->getLimitOffset() !== null && $filter->getLimitCount() !== null) {
            $result = array_slice($result, $filter->getLimitOffset(), $filter->getLimitCount(), true);
        }

        return $result;
    }

    public function getUserCertificateDataMaxCount(UserDataFilter $filter): int
    {
        $sql = 'SELECT
    COUNT(id) AS count
FROM
' . $this->getQuery($filter, true);

        $query = $this->database->query($sql);

        return (int) $this->database->fetchAssoc($query)["count"];
    }

    private function getQuery(UserDataFilter $filter, bool $max_count_only = false): string
    {
        $sql = '(
SELECT 
  il_cert_user_cert.pattern_certificate_id,
  il_cert_user_cert.obj_id,
  il_cert_user_cert.usr_id,
  il_cert_user_cert.user_name,
  il_cert_user_cert.acquired_timestamp,
  il_cert_user_cert.currently_active,
  il_cert_user_cert.id,
  object_data.title,
  object_reference.ref_id
FROM il_cert_user_cert
INNER JOIN object_data ON object_data.obj_id = il_cert_user_cert.obj_id
INNER JOIN object_reference ON object_reference.obj_id = il_cert_user_cert.obj_id';

        if ($filter->shouldIncludeDeletedObjects()) {
            $sql .= '
UNION
SELECT 
  il_cert_user_cert.pattern_certificate_id,
  il_cert_user_cert.obj_id,
  il_cert_user_cert.usr_id,
  il_cert_user_cert.user_name,
  il_cert_user_cert.acquired_timestamp,
  il_cert_user_cert.currently_active,
  il_cert_user_cert.id,
  object_data_del.title,
  NULL AS ref_id
FROM il_cert_user_cert
INNER JOIN object_data_del ON object_data_del.obj_id = il_cert_user_cert.obj_id';
        } else {
            $sql .= '
WHERE object_reference.deleted IS NULL';
        }

        $sql .= '
) AS cert
INNER JOIN usr_data ON usr_data.usr_id = cert.usr_id
INNER JOIN il_orgu_ua ON il_orgu_ua.user_id = cert.usr_id
' . $this->createWhereCondition($filter);

        if (!$max_count_only) {
            $sql .= $this->createOrderByClause($filter);
        }

        return $sql;
    }

    private function createOrderByClause(UserDataFilter $filter): string
    {
        $sorts = $filter->getSorts();

        if (empty($sorts)) {
            return '';
        }

        $orders = [];

        foreach ($sorts as [$key, $direction]) {
            $direction = $direction === UserDataFilter::SORT_DIRECTION_DESC ? ' DESC' : ' ASC';

            switch (true) {
                case ($key === UserDataFilter::SORT_FIELD_USR_LOGIN):
                    $orders[] = 'usr_data.login' . $direction;
                    break;

                case ($key === UserDataFilter::SORT_FIELD_USR_FIRSTNAME):
                    $orders[] = 'usr_data.firstname' . $direction;
                    break;

                case ($key === UserDataFilter::SORT_FIELD_USR_LASTNAME):
                    $orders[] = 'usr_data.lastname' . $direction;
                    break;

                case ($key === UserDataFilter::SORT_FIELD_OBJ_TITLE):
                    $orders[] = 'cert.title' . $direction;
                    break;

                case ($key === UserDataFilter::SORT_FIELD_ISSUE_TIMESTAMP):
                    $orders[] = 'cert.acquired_timestamp' . $direction;
                    break;

                case ($key === UserDataFilter::SORT_FIELD_USR_EMAIL):
                    $orders[] = 'usr_data.email' . $direction;
                    break;

                default:
                    break;
            }
        }

        return ' ORDER BY ' . implode(', ', $orders);
    }

    private function createWhereCondition(UserDataFilter $filter): string
    {
        $wheres = [];

        $userIds = $filter->getUserIds();
        if (!empty($userIds)) {
            $wheres[] = $this->database->in('cert.usr_id', $userIds, false, ilDBConstants::T_INTEGER);
        }

        $objIds = $filter->getObjIds();
        if (!empty($objIds)) {
            $wheres[] = $this->database->in('cert.obj_id', $objIds, false, ilDBConstants::T_INTEGER);
        }

        $firstName = $filter->getUserFirstName();
        if (!empty($firstName)) {
            $wheres[] = $this->database->like('usr_data.firstname', ilDBConstants::T_TEXT, '%' . $firstName . '%');
        }

        $lastName = $filter->getUserLastName();
        if (!empty($lastName)) {
            $wheres[] = $this->database->like('usr_data.lastname', ilDBConstants::T_TEXT, '%' . $lastName . '%');
        }

        $login = $filter->getUserLogin();
        if (!empty($login)) {
            $wheres[] = $this->database->like('usr_data.login', ilDBConstants::T_TEXT, '%' . $login . '%');
        }

        $userEmail = $filter->getUserEmail();
        if (!empty($userEmail)) {
            $wheres[] = '(' . $this->database->like('usr_data.email', ilDBConstants::T_TEXT, '%' . $userEmail . '%')
                . ' OR ' . $this->database->like('usr_data.second_email', ilDBConstants::T_TEXT, '%' . $userEmail . '%')
                . ')';
        }

        $userIdentification = $filter->getUserIdentification();
        if (!empty($userIdentification)) {
            $wheres[] = '(' . $this->database->like('usr_data.login', ilDBConstants::T_TEXT, '%' . $userIdentification . '%')
                . ' OR ' . $this->database->like('usr_data.firstname', ilDBConstants::T_TEXT, '%' . $userIdentification . '%')
                . ' OR ' . $this->database->like('usr_data.lastname', ilDBConstants::T_TEXT, '%' . $userIdentification . '%')
                . ' OR ' . $this->database->like('usr_data.email', ilDBConstants::T_TEXT, '%' . $userIdentification . '%')
                . ' OR ' . $this->database->like('usr_data.second_email', ilDBConstants::T_TEXT, '%' . $userIdentification . '%')
                . ')';
        }

        $issuedBeforeTimestamp = $filter->getIssuedBeforeTimestamp();
        if ($issuedBeforeTimestamp !== null) {
            $wheres[] = 'cert.acquired_timestamp < ' . $this->database->quote(
                $issuedBeforeTimestamp,
                ilDBConstants::T_INTEGER
            );
        }

        $issuedAfterTimestamp = $filter->getIssuedAfterTimestamp();
        if ($issuedAfterTimestamp !== null) {
            $wheres[] = 'cert.acquired_timestamp > ' . $this->database->quote(
                $issuedAfterTimestamp,
                ilDBConstants::T_INTEGER
            );
        }

        $title = $filter->getObjectTitle();
        if (!empty($title)) {
            $wheres[] = $this->database->like('cert.title', ilDBConstants::T_TEXT, '%' . $title . '%');
        }

        $onlyCertActive = $filter->isOnlyCertActive();
        if ($onlyCertActive) {
            $wheres[] = 'cert.currently_active = ' . $this->database->quote(1, ilDBConstants::T_INTEGER);
        }

        $orgUnitIds = $filter->getOrgUnitIds();
        if ($orgUnitIds) {
            $wheres[] = $this->database->in('il_orgu_ua.orgu_id', $orgUnitIds, false, ilDBConstants::T_INTEGER);
        }

        if (empty($wheres)) {
            return '';
        }

        return 'WHERE ' . implode(' AND ', $wheres);
    }
}