Ego_User_Sql.php

gehe zur Dokumentation dieser Datei

<?php
require_once 'base/Ego_User_Search_Factory.php';
 
class Ego_User_Sql {
    private $table;
    private $db;
 
    public function __construct() {
        // Datenbanktabelle
        $this->table = 'egotec_user';
 
        // Datenbankverbindung
        $this->db = new_db_connection();
    }
 
    public function searchUsers(array $search, int $limit = -1, string $type = '', bool $checkRights = true, bool $filterSearch = true, bool $sortResult = true, int $from = 0, bool $returnTotal = false, array $sort_order = [], string $rights = ''): array {
        // SQL-Query für die Suche
        $query = '1=1';
        $bind = [];
 
        // Parameter aus der Suchmaske filtern
        if ($filterSearch) {
            $search = Ego_User_Search_Factory::filterSearch($search);
        }
 
        // Prüfen, ob mit den aktuellen Suchparametern überhaupt Benutzer gefunden werden können
        if ($type && !Ego_User_Search_Factory::checkSearchFilter($type, $search)) {
            return $returnTotal ? ['users' => [], 'hits' => 0] : [];
        }
 
        // 'user_id' kann über die SQL-Query gefiltert werden
        if ($search['user_id']) {
            $query .= " AND user_id = :user_id";
            $bind['user_id'] = $search['user_id'];
        }
 
        // 'username' kann über die SQL-Query gefiltert werden
        if ($search['username']) {
            $query .= " AND LOWER(egotec_user.username) LIKE :username";
            $bind['username'] = '%' . mb_strtolower($search['username']) . '%';
        }
 
        // 'username_char' kann über die SQL-Query gefiltert werden
        if ($search['username_char']) {
            $query .= " AND UPPER(egotec_user.username) LIKE :username_char";
            $bind['username_char'] = "{$search['username_char']}%";
        }
 
        // 'email' kann über die SQL-Query gefiltert werden
        if ($search['email']) {
            $query .= ' AND LOWER(egotec_user.email) LIKE :email';
            $bind['email'] = '%' . mb_strtolower($search['email']) . '%';
        }
 
        // 'multiple_login' kann über die SQL-Query gefiltert werden
        if ($search['multiple_login']) {
            $query .= ' AND egotec_user.multiple_login = :multiple_login';
            $bind['multiple_login'] = $search['multiple_login'];
        }
 
        $params = [];
 
        if ($rights !== '') {
            $params['join'] = 'egotec_user_group ON egotec_user_group.user_id = egotec_user.user_id';
            $query .= ' AND (';
 
            $query_parts = [];
 
            foreach (explode(';', $rights) as $group_role) {
                [$group, $role] = explode(',', $group_role);
 
                $query_parts[] = "(egotec_user_group.group_id = '$group' AND egotec_user_group.role_id = '$role')";
            }
 
            $query .= implode(' OR ', $query_parts) . ')';
        }
 
        $field = 'username';
        $order = 'asc';
 
        if ($sort_order && $sort_order['field'] && $sort_order['field'] !== '') {
            $field = $sort_order['field'];
        }
 
        if ($sort_order && $sort_order['order'] && $sort_order['order'] !== '') {
            $order = $sort_order['order'];
        }
 
        // Je nach Typ der Benutzersuche eine Datenbankabfrage machen
        switch ($type) {
            case 'admin':
                $query .= ' AND deleted = 0';
                $this->db->select(array_merge([
                    'fields' => 'egotec_user.user_id',
                    'table' => $this->table,
                    'where' => 'no_admin = 0 AND ' . $this->getActiveQuery() . ' AND ' . $query,
                    'bind' => $bind,
                    'order' => $field . ' ' . $order
                ], $params));
                break;
            case 'intranet':
                $query .= ' AND deleted = 0';
                $this->db->select(array_merge([
                    'fields' => 'egotec_user.user_id',
                    'table' => $this->table,
                    'where' => 'no_admin = 1 AND ' . $this->getActiveQuery() . ' AND ' . $query,
                    'bind' => $bind,
                    'order' => $field . ' ' . $order
                ], $params));
                break;
            case 'norelation':
                $query .= ' AND deleted = 0';
                $this->db->select(array_merge([
                    'fields' => 'egotec_user.user_id',
                    'table' => $this->table,
                    'where' => 'egotec_user_group.user_id IS NULL AND ' . $this->getActiveQuery() . ' AND ' . $query,
                    'bind' => $bind,
                    'join' => [
                        'egotec_user_group ON egotec_user.user_id = egotec_user_group.user_id'
                    ],
                    'order' => $field . ' ' . $order
                ], $params));
                break;
            case 'inactive':
                $query .= ' AND deleted = 0';
                $this->db->select(array_merge([
                    'fields' => 'egotec_user.user_id',
                    'table' => $this->table,
                    'where' => $this->getInactiveQuery() . ' AND ' . $query,
                    'bind' => $bind,
                    'order' => $field . ' ' . $order
                ], $params));
                break;
            case 'trash':
                $this->db->select(array_merge([
                    'fields' => 'egotec_user.user_id',
                    'table' => $this->table,
                    'where' => 'deleted = 1 AND ' . $query,
                    'bind' => $bind,
                    'order' => $field . ' ' . $order
                ], $params));
                break;
            default:
                $query .= ' AND deleted = 0';
                $this->db->select(array_merge([
                    'fields' => 'egotec_user.user_id',
                    'table' => $this->table,
                    'where' => $query. ' AND ' . (key_exists('user_inactive', $search) ? ($search['user_inactive'] === 0 ? $this->getActiveQuery() : $this->getInactiveQuery()) : '1=1'),
                    'bind' => $bind,
                    'order' => $field . ' ' . $order
                ], $params));
        }
 
        // Array für die Benutzer
        $users = [];
 
        // Limit logisch abfragen, da gefundene Benutzer noch durch die Filter ignoriert werden können
        $user_count = 0;
        if (!$from) {
            $from = 0;
        }
 
        while ($record = $this->db->nextRecord()) {
            $user = new User_SQL($record['user_id']);
            // Angegebene Filter prüfen
            if (
                !$this->checkFilters($search, $user) // Filter aus der Suchmaske überprüfen
                || ($checkRights && !Ego_User_Search_Factory::isAcceptedUser($user)) // Prüfen ob der aktuelle Benutzer Rechte auf den gefundenen hat
                || ($search['group_role'] && !Ego_User_Search_Factory::checkRightsFilter($search['group_role'], $user)) // In der Suchmaske eingetragene Rechte prüfen
            ) {
                continue;
            }
 
            $user_count++;
            if ($from && $user_count < ($from + 1)) {
                continue;
            }
 
            $users[] = $user;
 
            if ($limit > 0 && $user_count == (($from + 1) + $limit)) {
                break;
            }
        }
 
        return $users;
    }
 
    public function getChars($type = ''): array {
        $chars = [];
 
        // Parameter für den Datenbankselect
        $params = [
            'fields' => 'LEFT(UPPER(username), 1) AS charx, username',
            'table' => $this->table,
            'group' => 'username',
            'order' => 'charx ASC'
        ];
 
        // Je nach Benutzersuche die Query erweitern
        switch ($type) {
            case 'admin':
                $params['where'] = 'no_admin = 0 AND deleted = 0 AND ' . $this->getActiveQuery();
                break;
            case 'intranet':
                $params['where'] = 'no_admin = 1 AND deleted = 0 AND ' . $this->getActiveQuery();
                break;
            case 'norelation':
                $params['where'] = 'deleted = 0 AND egotec_user_group.user_id IS NULL AND ' . $this->getActiveQuery();
                $params['join'] = 'egotec_user_group ON (egotec_user.user_id = egotec_user_group.user_id)';
                break;
            case 'inactive':
                $params['where'] = 'deleted = 0 AND ' . $this->getInactiveQuery();
                break;
            case '':// Trash
                $params['where'] = 'deleted = 1';
        }
 
        // Datenbankabfrage
        $this->db->select($params);
 
        // Ein Array aufbauen, das die Anfangsbuchstaben, sowie die Anzahl an Benutzern mit diesem Anfangsbuchstaben enthält
        while ($record = $this->db->nextRecord()) {
            $chars[$record['charx']] = $chars[$record['charx']] ? $chars[$record['charx']] = [
                'char' => $record['charx'],
                'number' => $chars[$record['charx']]['number'] + 1
            ] : [
                'char' => $record['charx'],
                'number' => 1
            ];
        }
 
        return $chars;
    }
 
    private function getActiveQuery():string {
        $now = date('Y-m-d H:i:s');
        return "((release_from = '0000-00-00 00:00:00' OR release_from <= '$now')" // Kein Fregabedatum und Freigabedatum von in der Vergangenheit
            . " AND (release_until = '0000-00-00 00:00:00' OR release_until >= '$now')" // Kein Fregabedatum und Freigabedatum bis in der Zukunft
            . " AND extra NOT LIKE '%\\\"user_inactive\\\";s:1:\\\"1\\\";%'" // Sucht nach Benutzern, die nicht user_inactive="1" im serialisierten extra-feld haben
            . " AND extra NOT LIKE '%\\\"user_inactive\\\";i:1;%')"; // Sucht nach Bentzern, die nicht user_inactive=1 im serialisierten extra-feld haben
    }
 
    private function getInactiveQuery(): string {
        $now = date('Y-m-d H:i:s');
        return "((release_from != '0000-00-00 00:00:00' AND release_from >= '$now')" // Nicht kein Fregabedatum und Freigabedatum von in der Zukunft
            . " OR (release_until != '0000-00-00 00:00:00' AND release_until <= '$now')" // Nicht kein Fregabedatum und Freigabedatum bis in der Vergangenheit
            . " OR extra LIKE '%\\\"user_inactive\\\";s:1:\\\"1\\\";%'" // Sucht nach user_inactive="1" im serialisierten extra-feld
            . " OR extra LIKE '%\\\"user_inactive\\\";i:1;%')"; // Sucht nach user_inactive=1 im serialisierten extra-feld
    }
 
    private function checkFilters(array $filters, User_SQL $user): bool {
        $result = (!$filters['abteilung'] || strpos(mb_strtolower($user->extra['abteilung']), mb_strtolower($filters['abteilung'])) !== false)
            && (!$filters['anrede'] || strpos(mb_strtolower($user->extra['anrede']), mb_strtolower($filters['anrede'])) !== false)
            && (!$filters['branche'] || strpos(mb_strtolower($user->extra['branche']), mb_strtolower($filters['branche'])) !== false)
            && (!$filters['name'] || strpos(mb_strtolower($user->extra['name']), mb_strtolower($filters['name'])) !== false)
            && (!$filters['vorname'] || strpos(mb_strtolower($user->extra['vorname']), mb_strtolower($filters['vorname'])) !== false)
            && (!$filters['position'] || strpos(mb_strtolower($user->extra['position']), mb_strtolower($filters['position'])) !== false)
            && (!$filters['strasse'] || strpos(mb_strtolower($user->extra['strasse']), mb_strtolower($filters['strasse'])) !== false)
            && (!$filters['firma'] || strpos(mb_strtolower($user->extra['firma']), mb_strtolower($filters['firma'])) !== false)
            && (!$filters['plz'] || strpos(mb_strtolower($user->extra['plz']), mb_strtolower($filters['plz'])) !== false)
            && (!$filters['telefon'] || strpos(mb_strtolower($user->extra['telefon']), mb_strtolower($filters['telefon'])) !== false)
            && (!$filters['ort'] || strpos(mb_strtolower($user->extra['ort']), mb_strtolower($filters['ort'])) !== false)
            && (!$filters['mobil'] || strpos(mb_strtolower($user->extra['mobil']), mb_strtolower($filters['mobil'])) !== false)
            && (!$filters['land'] || strpos(mb_strtolower($user->extra['land']), mb_strtolower($filters['land'])) !== false)
            && (!$filters['liveserver'] || $user->extra['liveserver'] == $filters['liveserver'])
            && (!$filters['no_singlesignon'] || $user->extra['no_singlesignon'] == $filters['no_singlesignon'])
            && (!$filters['categories'] || strpos($user->extra['categories'], $filters['categories']) !== false)
            && (!$filters['auth_persist_token'] || strpos($user->extra['auth_persist_token'], $filters['auth_persist_token']) !== false)
            && (!$filters['tfa_secret'] || !empty($user->extra['tfa_secret']));
 
        // Kundenspezifische Filter
        if (
            $result
            && (
                function_exists('user_search_fields')
                || Ego_System::file_exists($user_search_fields = ($GLOBALS['egotec_conf']['var_dir'] . 'lib/admin/user_search_fields.php'))
            )
        ) {
            if (isset($user_search_fields)) {
                require_once $user_search_fields;
            }
            if (function_exists('user_search_fields') && !user_search_fields($user, $filters)) {
                $result = false;
            }
        }
 
        return $result;
    }
}