class.ilObjOrgUnitTree.php

Go to the documentation of this file.

<?php
/* Copyright (c) 1998-2013 ILIAS open source, Extended GPL, see docs/LICENSE */

class ilObjOrgUnitTree
{

    protected static $temporary_table_name = null;
    private static $instance;
    private $roles;
    private $role_to_orgu;
    private $staff;
    private $tree_childs;
    private $parent;
    private $db;


    private function __construct()
    {
        global $DIC;
        $ilDB = $DIC['ilDB'];
        $tree = $DIC['tree'];
        $this->db = $ilDB;
        $this->tree = $tree;
        $this->roles = array();
        $this->staff = array();
    }


    public static function _getInstance()
    {
        if (self::$instance === null) {
            self::$instance = new self();
        }

        return self::$instance;
    }


    public function getEmployees($ref_id, $recursive = false)
    {
        // return $this->getAssignements($ref_id, new ilOrgUnitPosition(1));
        return array_unique(($recursive ? $this->loadStaffRecursive("employee", $ref_id) : $this->loadStaff("employee", $ref_id)));
    }


    public function getAssignements($ref_id, ilOrgUnitPosition $ilOrgUnitPosition)
    {
        return ilOrgUnitUserAssignment::where(array( 'orgu_id'     => $ref_id,
                                                     'position_id' => $ilOrgUnitPosition->getId(),
        ))->getArray('id', 'user_id');
    }


    public function getSuperiors($ref_id, $recursive = false)
    {
        return array_unique(($recursive ? $this->loadStaffRecursive("superior", $ref_id) : $this->loadStaff("superior", $ref_id)));
    }


    private function loadStaff($title, $ref_id)
    {
        return $this->loadArrayOfStaff($title, array( $ref_id ));
    }


    private function loadStaffRecursive($title, $ref_id)
    {
        return $this->loadArrayOfStaff($title, $this->getAllChildren($ref_id));
    }


    private function loadArrayOfStaff($title, $ref_ids)
    {
        $this->loadRoles($title);
        $all_refs = $ref_ids;
        //take away ref_ids that are already loaded.
        foreach ($ref_ids as $id => $ref_id) {
            if (isset($this->staff[$title][$ref_id])) {
                unset($ref_ids[$id]);
            } else {
                $this->staff[$title][$ref_id] = array();
                $ref_ids[$id] = $this->roles[$title][$ref_id];
            }
        }

        //if there are still refs that need to be loaded, then do so.
        if (count($ref_ids)) {
            $q = "SELECT usr_id, rol_id FROM rbac_ua WHERE "
                 . $this->db->in("rol_id", $ref_ids, false, "integer");
            $set = $this->db->query($q);
            while ($res = $this->db->fetchAssoc($set)) {
                $orgu_ref = $this->role_to_orgu[$title][$res["rol_id"]];
                $this->staff[$title][$orgu_ref][] = $res["usr_id"];
            }
        }

        //collect * users.
        $all_users = array();
        foreach ($all_refs as $ref) {
            $all_users = array_merge($all_users, $this->staff[$title][$ref]);
        }

        return $all_users;
    }


    public function getAllChildren($ref_id)
    {
        $open = array( $ref_id );
        $closed = array();
        while (count($open)) {
            $ref = array_pop($open);
            $closed[] = $ref;
            foreach ($this->getChildren($ref) as $child) {
                if (!in_array($child, $open) && !in_array($child, $closed)) {
                    $open[] = $child;
                }
            }
        }

        return $closed;
    }


    public function getOrgusWhereUserHasPermissionForOperation($operation)
    {
        global $DIC;
        $ilUser = $DIC['ilUser'];
        /*$q = "SELECT object_data.obj_id, object_reference.ref_id, object_data.title, object_data.type, rbac_pa.ops_id, rbac_operations.ops_id as op_id FROM object_data
        INNER JOIN rbac_operations ON rbac_operations.operation = ".$this->db->quote($operation, "text")."
        INNER JOIN rbac_ua ON rbac_ua.usr_id = ".$this->db->quote($ilUser->getId(), "integer")."
        INNER JOIN rbac_pa ON rbac_pa.rol_id = rbac_ua.rol_id AND rbac_pa.ops_id LIKE CONCAT('%', rbac_operations.ops_id, '%')
        INNER JOIN rbac_fa ON rbac_fa.rol_id = rbac_ua.rol_id
        INNER JOIN tree ON tree.child = rbac_fa.parent
        INNER JOIN object_reference ON object_reference.ref_id = tree.parent
        WHERE object_data.obj_id = object_reference.obj_id AND object_data.type = 'orgu'";*/

        $q = "SELECT object_data.obj_id, object_reference.ref_id, object_data.title, object_data.type, rbac_pa.ops_id, rbac_operations.ops_id as op_id FROM object_data
                INNER JOIN rbac_operations ON rbac_operations.operation = "
             . $this->db->quote($operation, "text") . "
                INNER JOIN rbac_ua ON rbac_ua.usr_id = " . $this->db->quote($ilUser->getId(), "integer") . "
                INNER JOIN rbac_pa ON rbac_pa.rol_id = rbac_ua.rol_id AND rbac_pa.ops_id LIKE CONCAT('%', rbac_operations.ops_id, '%')
                INNER JOIN object_reference ON object_reference.ref_id = rbac_pa.ref_id
                WHERE object_data.obj_id = object_reference.obj_id AND object_data.type = 'orgu'";

        $set = $this->db->query($q);
        $orgus = array();
        while ($res = $this->db->fetchAssoc($set)) {
            //this is needed as the table rbac_operations is not in the first normal form, thus this needs some additional checkings.
            $perm_check = unserialize($res['ops_id']);
            if (!in_array($res["op_id"], $perm_check)) {
                continue;
            }

            $orgus[] = $res["ref_id"];
        }

        return $orgus;
    }


    public function getOrgusWhereUserHasPermissionForOperationId($operation_id)
    {
        global $DIC;
        $ilUser = $DIC['ilUser'];
        $q = "SELECT object_data.obj_id, object_data.title, object_data.type, rbac_pa.ops_id FROM object_data
                INNER JOIN rbac_ua ON rbac_ua.usr_id = " . $this->db->quote($ilUser->getId(), "integer") . "
                INNER JOIN rbac_pa ON rbac_pa.rol_id = rbac_ua.rol_id AND rbac_pa.ops_id LIKE CONCAT('%', "
             . $this->db->quote($operation_id, "integer") . ", '%')
                INNER JOIN rbac_fa ON rbac_fa.rol_id = rbac_ua.rol_id
                INNER JOIN tree ON tree.child = rbac_fa.parent
                INNER JOIN object_reference ON object_reference.ref_id = tree.parent
                WHERE object_data.obj_id = object_reference.obj_id AND object_data.type = 'orgu'";

        $set = $this->db->query($q);
        $orgus = array();
        while ($res = $this->db->fetchAssoc($set)) {
            //this is needed as the table rbac_operations is not in the first normal form, thus this needs some additional checkings.
            $perm_check = unserialize($res['ops_id']);
            if (!in_array($res["ops_id"], $perm_check)) {
                continue;
            }

            $orgus[] = $res["obj_id"];
        }

        return $orgus;
    }


    private function getChildren($ref_id)
    {
        $this->loadChildren($ref_id);

        return $this->tree_childs[$ref_id];
    }


    private function loadChildren($ref_id)
    {
        if (!$this->tree_childs[$ref_id]) {
            $children = array();
            foreach ($this->tree->getChilds($ref_id) as $child) {
                if ($child["type"] == "orgu") {
                    $children[] = $child["child"];
                }
            }
            $this->tree_childs[$ref_id] = $children;
        };
    }


    public function getAllOrgunitsOnLevelX($level)
    {
        $levels = array( 0 => array( ilObjOrgUnit::getRootOrgRefId() ) );
        $current_level = 0;
        while ($current_level < $level) {
            $new_level = array();
            foreach ($levels[$current_level] as $orgu_ref) {
                $new_level = array_merge($this->getChildren($orgu_ref), $new_level);
            }
            $new_level = array_unique($new_level);
            $levels[$current_level + 1] = $new_level;
            $current_level++;
        }

        return $levels[$level];
    }


    public function getEmployeesUnderUser($user_id, $recursive = true)
    {
        //querry for all orgu where user_id is superior.
        $q = "SELECT orgu.obj_id, refr.ref_id FROM object_data orgu
                INNER JOIN object_reference refr ON refr.obj_id = orgu.obj_id
                                INNER JOIN object_data roles ON roles.title LIKE CONCAT('il_orgu_superior_',refr.ref_id)
                                INNER JOIN rbac_ua rbac ON rbac.usr_id = " . $this->db->quote($user_id, "integer") . " AND roles.obj_id = rbac.rol_id
                                WHERE orgu.type = 'orgu'";
        $set = $this->db->query($q);
        $orgu_ref_ids = array();
        while ($res = $this->db->fetchAssoc($set)) {
            $orgu_ref_ids[] = $res['ref_id'];
        }
        $employees = array();
        foreach ($orgu_ref_ids as $orgu_ref_id) {
            $employees = array_merge($employees, $this->getEmployees($orgu_ref_id, $recursive));
        }

        return $employees;
    }


    public function getSuperiorsOfUser($user_id, $recursive = true)
    {
        //querry for all orgu where user_id is superior.
        $q = "SELECT orgu.obj_id, refr.ref_id FROM object_data orgu
                INNER JOIN object_reference refr ON refr.obj_id = orgu.obj_id
                                INNER JOIN object_data roles ON roles.title LIKE CONCAT('il_orgu_employee_',refr.ref_id) OR roles.title LIKE CONCAT('il_orgu_superior_',refr.ref_id)
                                INNER JOIN rbac_ua rbac ON rbac.usr_id = " . $this->db->quote($user_id, "integer") . " AND roles.obj_id = rbac.rol_id
                                WHERE orgu.type = 'orgu'";
        $set = $this->db->query($q);
        $orgu_ref_ids = array();
        while ($res = $this->db->fetchAssoc($set)) {
            $orgu_ref_ids[] = $res['ref_id'];
        }
        $superiors = array();
        foreach ($orgu_ref_ids as $orgu_ref_id) {
            $superiors = array_merge($superiors, $this->getSuperiors($orgu_ref_id, $recursive));
        }

        return $superiors;
    }


    public function getLevelXOfUser($user_id, $level)
    {
        $q = "SELECT object_reference.ref_id FROM rbac_ua
                                JOIN rbac_fa ON rbac_fa.rol_id = rbac_ua.rol_id
                                JOIN object_reference ON rbac_fa.parent = object_reference.ref_id
                                JOIN object_data ON object_data.obj_id = object_reference.obj_id
                        WHERE rbac_ua.usr_id = " . $this->db->quote($user_id, 'integer')
             . " AND object_data.type = 'orgu';";

        $set = $this->db->query($q);
        $orgu_ref_ids = array();
        while ($res = $this->db->fetchAssoc($set)) {
            $orgu_ref_ids[] = $res['ref_id'];
        }
        $orgus_on_level_x = array();
        foreach ($orgu_ref_ids as $orgu_ref_id) {
            try {
                $orgus_on_level_x[] = $this->getLevelXOfTreenode($orgu_ref_id, $level);
            } catch (Exception $e) {
                // this means the user is assigned to a orgu above the given level. just dont add it to the list.
            }
        }

        return array_unique($orgus_on_level_x);
    }


    public function getOrgUnitOfUser($user_id, $ref_id = 0)
    {
        $q = "SELECT object_reference.ref_id FROM rbac_ua
                                JOIN rbac_fa ON rbac_fa.rol_id = rbac_ua.rol_id
                                JOIN object_reference ON rbac_fa.parent = object_reference.ref_id
                                JOIN object_data ON object_data.obj_id = object_reference.obj_id
                        WHERE rbac_ua.usr_id = " . $this->db->quote($user_id, 'integer')
             . " AND object_data.type = 'orgu'";

        $set = $this->db->query($q);
        $orgu_ref_ids = array();
        while ($res = $this->db->fetchAssoc($set)) {
            $orgu_ref_ids[] = $res['ref_id'];
        }
        $orgu_ref_ids = array_unique($orgu_ref_ids);
        if ($ref_id) {
            $childernOrgIds = $this->getAllChildren($ref_id);
            foreach ($orgu_ref_ids as $k => $refId) {
                if (!in_array($refId, $childernOrgIds)) {
                    unset($orgu_ref_ids[$k]);
                }
            }
        }

        return $orgu_ref_ids;
    }


    public function buildTempTableWithUsrAssignements($temporary_table_name = 'orgu_usr_assignements')
    {
        if (self::$temporary_table_name == $temporary_table_name) {
            return true;
        }
        if (self::$temporary_table_name === null) {
            $this->dropTempTable($temporary_table_name);
            self::$temporary_table_name = $temporary_table_name;
        } elseif ($temporary_table_name != self::$temporary_table_name) {
            throw new ilException('there is already a temporary table for org-unit assignement: '
                                  . self::$temporary_table_name);
        }

        $q = "CREATE TEMPORARY TABLE IF NOT EXISTS " . $temporary_table_name . " AS (
                                SELECT DISTINCT object_reference.ref_id AS ref_id, il_orgu_ua.user_id AS user_id, orgu_path_storage.path AS path
                                        FROM il_orgu_ua
                    JOIN object_reference ON object_reference.ref_id = il_orgu_ua.orgu_id
                                        JOIN object_data ON object_data.obj_id = object_reference.obj_id
                                        JOIN orgu_path_storage ON orgu_path_storage.ref_id = object_reference.ref_id
                                WHERE object_data.type = 'orgu' AND object_reference.deleted IS NULL
                        );";
        $this->db->manipulate($q);

        return true;
    }


    public function dropTempTable($temporary_table_name)
    {
        if (self::$temporary_table_name === null
            || $temporary_table_name != self::$temporary_table_name) {
            return false;
        }
        $q = "DROP TABLE IF EXISTS " . $temporary_table_name;
        $this->db->manipulate($q);

        self::$temporary_table_name = null;

        return true;
    }


    public function getTitles($org_refs)
    {
        $names = array();
        foreach ($org_refs as $org_unit) {
            $names[$org_unit] = ilObject::_lookupTitle(ilObject::_lookupObjId($org_unit));
        }

        return $names;
    }


    public function getEmployeeRoles()
    {
        $this->loadRoles("employee");

        return $this->roles["employee"];
    }


    public function getSuperiorRoles()
    {
        $this->loadRoles("superior");

        return $this->roles["superior"];
    }


    private function loadRoles($role)
    {
        if ($this->roles[$role] == null) {
            $this->loadRolesQuery($role);
        }
    }


    public function flushCache()
    {
        $this->roles = null;
    }


    private function loadRolesQuery($role)
    {
        $this->roles[$role] = array();
        $q = "SELECT obj_id, title FROM object_data WHERE type = 'role' AND title LIKE 'il_orgu_"
             . $role . "%'";
        $set = $this->db->query($q);
        while ($res = $this->db->fetchAssoc($set)) {
            $orgu_ref = $this->getRefIdFromRoleTitle($res["title"]);
            $this->roles[$role][$orgu_ref] = $res["obj_id"];
            $this->role_to_orgu[$role][$res["obj_id"]] = $orgu_ref;
        }
    }


    private function getRefIdFromRoleTitle($role_title)
    {
        $array = explode("_", $role_title);

        return $array[count($array) - 1];
    }


    public function getLevelXOfTreenode($orgu_ref, $level)
    {
        $line = array( $orgu_ref );
        $current_ref = $orgu_ref;
        while ($current_ref != ilObjOrgUnit::getRootOrgRefId()) {
            $current_ref = $this->getParent($current_ref);
            if ($current_ref) {
                $line[] = $current_ref;
            } else {
                break;
            }
            if (count($line) > 100) {
                throw new Exception("There's either a non valid call of the getLevelXOfTreenode in ilObjOrgUnitTree or your nesting of orgunits is higher than 100 units, which isn't encouraged");
            }
        }
        $line = array_reverse($line);
        if (count($line) > $level) {
            return $line[$level];
        } else {
            throw new Exception("you want to fetch level " . $level
                                . " but the line to the length of the line is only " . count($line)
                                . ". The line of the given org unit is: " . print_r($line, true));
        }
    }


    public function getParent($orgu_ref)
    {
        if (!$this->parent[$orgu_ref]) {
            $this->parent[$orgu_ref] = $this->tree->getParentId($orgu_ref);
        }

        return $this->parent[$orgu_ref];
    }
}