Найти сотрудников в детских ветких - PHP

Я создаю проект с организационной диаграммой, используя Codeigniter + MySQL + Active Record.

Есть отделы, перечисленные в качестве дерева организации, Персонал для информации о лицах, Персонал Роли и Staff_Departments, где я храню соответствие: Отдел - Персонал - Роль

Вы можете увидеть структуру ниже:

Отделы (parent_id используется для построения дерева)

Персонал (исходная информация о персонале)

Роли персонала (самый низкий вес, самый высокий в иерархии)

Отделы сотрудников (в каком отделе - Кто - какая роль)

На более позднем этапе Персонал, вероятно, будет принадлежать двум или более отделам с разными ролями. Вот почему я использовал отдельный стол Staff_departments для многих-ко-многим. В этом случае пусть это будет просто и предположим, что 1 сотрудник принадлежит 1 департаменту.

То, что я пытаюсь сделать:

Менеджер (роль weight = 0 || role_id = 1) в отделе может просматривать Персонал (Сотрудники и Супервизоры), которые работают в своем отделе И все сотрудники (сотрудники и надзиратели) из департаментов, которые являются детьми его отдела. Глубина дерева неизвестна.
Наблюдатель может просматривать Персонал (только Сотрудники), которые работают только в своем Департаменте.
Сотрудник может видеть только себя.

Для супервизоров и сотрудников процесс прост, поэтому я думаю, что я в порядке с этим. Для менеджеров, вероятно, я должен что-то делать рекурсивно, но я борюсь каждый раз, когда начинаю писать некоторые строки кода.

Моя идея состоит в том, чтобы в моем контроллере была функция find_related_staff ($ staff_id) {} , в которой я передам идентификатор сотрудника, который вошел в систему, и он вернет массив с идентификаторами связанного с ним персонала. Единственное, что у меня есть, это идентификатор штата, который вошел в систему.

Если менеджер возвращает идентификаторы менеджеров, надзорных органов и сотрудников, связанных с его отделом и менеджерами, надзорными органами и сотрудниками из Детских департаментов его отдела.

Если супервизор возвращает идентификаторы наблюдателей и сотрудников, связанных только в его отделе.

Если Employee возвращает свой идентификатор

Любая идея о том, как достичь этого?

Ответ 1

Хорошо, я думаю, что для того, чтобы все было легче понять, нам нужно разбить вашу проблему на мелкие кусочки (и я сосредоточу внимание только на разделе, в котором вы говорите, что вам действительно нужна помощь: рекурсия менеджеров).

Сначала мы получаем текущий отдел, связанный с аутентификацией пользователя. Как вы уже сказали, у вас есть только идентификатор персонала, который сейчас подписывается, поэтому мы начнем с этого. Пусть говорят, что идентификатор пользователя присваивается переменной $user_id.

$user_department = $this->db->get_where('staff_departments', ['staff_id' => $user_id])->row();

Теперь, когда у нас есть отдел, мы проверяем, какая роль пользователя в этом отделе. Мы добавим эту информацию в объект $user_department:

$user_department->role = $this->db->get_where('staff_roles', ['role_id' => $user_department->role_id])->row();

Можно проверить вес роли пользователя, не так ли? Если это 0, мы знаем, что это менеджер в этом отделе, поэтому мы рекурсивно найдем вложенные отделы и их информацию о персонале. Согласно вашей логике, мы можем проверить, здесь, если пользователь также является супервизором и, если необходимо, перерасти. Вот так:

if ($user_department->role->role_weight <= 1) {
    // the user is a supervisor OR a manager, but both those can see, at least, the current department staff information
    $user_department->staff = $this->db->get_where('staff_departments', ['department_id' => $user_department->department_id]);

    // now is the user a manager? If so, let find nested departments
    if ($user_department->role->role_weight === 0) {
        $user_department->childs = $this->getChildDepartmentsAndStaffOf($user_department->department_id);
    }
}

Как вы можете заметить, есть функция, которая будет называться рекурсивно. Это должно быть что-то в этом роде:

public function getChildDepartmentsAndStaffOf($department_id)
{
    $child_departments = $this->db->get_where('departments', ['parent_id' => $department_id]);

    if (! $child_departments) {
        return null;
    }

    foreach ($child_departments as &$department) {
        $department->staff = $this->db->get_where('staff_departments', ['department_id' => $department->department_id]);

        $department->childs = $this->getChildDepartmentsAndStaffOf($department->department_id);
    }

    return $child_departments;
}

Теперь у вас есть структура, которую вы хотите. Я знаю, что это может быть реорганизовано, но я думаю, этого достаточно, чтобы получить ваш ответ и указать на правильный путь.

Надеюсь, я немного помог.

Ответ 2

Да, чтобы это сделать, вы должны использовать рекурсивные процедуры. (Я использую MySQL 5.6.19)

Я создал некоторые тестовые данные перед хранимыми процедурами:

Примеры данных на основе ваших требований к запросу:

create table departments
(
    id int not null primary key auto_increment,
    parent_id int,
    department_name varchar(100)
);

insert into departments (id,parent_id,department_name)
values
(1,0,'Test A'),
(2,1,'Test B'),
(3,2,'Test C');


create table staff
(
    id int not null primary key auto_increment,
    ip_address varchar(100),
    username varchar(100)
);

insert into staff values
(1,'127.0.0.1','ats'),
(2,'127.0.0.1','admin'),
(3,'127.0.0.1','george'),
(4,'127.0.0.1','jhon')
;

create table staff_roles
(
    role_id int not null primary key auto_increment,
    role_name varchar(100),
    role_height int
);

insert into staff_roles values
(1,'Manager',0),
(2,'Supervisor',1),
(3,'Employee',2)
;

create table staff_departments
(
    staff_department_id int not null primary key auto_increment,
    department_id int,
    staff_id int,
    role_id int
);

insert into staff_departments values
(1,1,2,1),
(2,2,1,2),
(3,3,3,3),
(4,3,4,3);

Время создания хранимых процедур:

find_related_staff - это процедура, которая принимает параметр staff_id, в соответствии с этим значением найдет таблицу role_id in staff_departments.

Переменная @result будет накапливать конечный результат в виде значений, разделенных запятыми.
find_recursive - это процедура поиска в дочерних подразделениях и получение staff_id в переменной @result;

Код процедуры:

delimiter $$
drop procedure if exists find_related_staff$$
create procedure  find_related_staff(p_id int)
begin
    declare p_role_id int;
    declare p_department_id int;
    declare p_return varchar(255) default '';
    declare p_role varchar(100);

    select d.role_id, d.department_id, r.role_name
        into p_role_id,p_department_id, p_role
        from staff_departments d
        inner join staff_roles r on d.role_id = r.role_id
        where d.staff_id = p_id
        limit 1;

    case p_role_id
    when 3 then -- employee (return the same id)
            set @result = p_id;

        when 2 then -- supervisor 

            select group_concat(s.staff_id)
        into @result
        from staff_departments s
        where 
              s.role_id = 3
              and s.department_id in 
                 ( select d.id 
                   from departments d
                   where d.parent_id = p_department_id )
              and s.role_id <> p_id;


        when 1 then -- manager (complex recursive query)

            select coalesce(group_concat(s.staff_id),'')
              into @result
            from staff_departments s
            where 
              s.department_id =  p_department_id
              and s.staff_id <>  p_id;

           -- here we go!
           call find_recursive(p_department_id);
    end case;

    select @result as result, p_role as role;
end $$
delimiter ;

delimiter $$
drop procedure if exists find_recursive$$
create procedure  find_recursive(p_dept_id int)
begin
    declare done int default false;
    declare p_department int default false;

    declare tmp_result varchar(255) default '';
    -- cursor for all depend departments
    declare c_departments cursor for
        select s.department_id
        from staff_departments s
        where 
              s.department_id in 
          ( select d.id 
                from departments d
                where d.parent_id = p_dept_id );

    declare continue handler for not found set done = true;


    -- getting current departmens
    set tmp_result = 
        (select coalesce(group_concat(s.staff_id),'')
            from staff_departments s
            where 
              s.department_id in 
              ( select d.id 
                from departments d
                where d.parent_id = p_dept_id ));


    if length(tmp_result) > 0 then

        if length(@result) > 0 then
            set @result = concat(@result,',',tmp_result);
        else
            set @result = tmp_result;
        end if;

        open c_departments;

        read_loop: loop
            fetch c_departments into  p_department;
            if done then
              leave read_loop;
            end if;

        call find_recursive(p_department);

        end loop;
        close c_departments;            

    end if;

end $$
delimiter ;

Тестирование:

Важно: Максимальное значение глубины рекурсии по умолчанию равно 0, мы должны изменить это значение:

SET max_sp_recursion_depth=255;

Теперь у вас есть следующая конфигурация в таблице staff_departments:

+---------------------+---------------+----------+---------+
| staff_department_id | department_id | staff_id | role_id |
+---------------------+---------------+----------+---------+
|                   1 |             1 |        2 |       1 |
|                   2 |             2 |        1 |       2 |
|                   3 |             3 |        3 |       3 |
|                   4 |             3 |        4 |       3 |
+---------------------+---------------+----------+---------+

Запуск каждого случая:

call find_related_staff(2);
+--------+---------+
| result | role    |
+--------+---------+
| 1,3,4  | Manager |
+--------+---------+



call find_related_staff(1);
+--------+------------+
| result | role       |
+--------+------------+
| 3,4    | Supervisor |
+--------+------------+


call find_related_staff(3);
+--------+----------+
| result | role     |
+--------+----------+
|      3 | Employee |
+--------+----------+

call find_related_staff(4);
+--------+----------+
| result | role     |
+--------+----------+
|      4 | Employee |
+--------+----------+

Наслаждайтесь!

Ответ 3

Я думаю, что самая мощная схема для иерархических данных в реляционной базе данных - transitive-closure-table.

Учитывая ваши данные образца для таблицы departments:

department_id | parent_id | department_name
--------------|-----------|----------------
            1 |         0 | TEST A
            2 |         1 | TEST B
            3 |         2 | TEST C

Ваша таблица закрытия (пусть просто назовет ее departments_tree) будет выглядеть так:

super_id | sub_id
---------|-------
       1 |      1
       1 |      2
       1 |      3
       2 |      2
       2 |      3
       3 |      3

Прочитайте его как: super_id= overordinate department_id; sub_id= subordinate department_id.

Предполагая, что вошедший в систему пользователь является менеджером отдела с department_id = 2, запрос на получение всех "контролируемых" сотрудников:

SELECT DISTINCT s.*
FROM departments_tree t
JOIN stuff_departments sd ON sd.department_id = t.sub_id
JOIN staff s ON s.id = sd.staff_id
WHERE t.super_id = 2

Вы можете использовать триггеры для заполнения и обновления таблицы закрытия.

Вставить триггер:

DELIMITER //
CREATE TRIGGER `departments_after_insert` AFTER INSERT ON `departments` FOR EACH ROW BEGIN
    INSERT INTO departments_tree (super_id, sub_id)
        SELECT new.department_id, new.department_id
        UNION ALL
        SELECT super_id, new.department_id
        FROM departments_tree
        WHERE sub_id = new.parent_id;
END//
DELIMITER ;

Удалить триггер:

DELIMITER //
CREATE TRIGGER `departments_before_delete` BEFORE DELETE ON `departments` FOR EACH ROW BEGIN
    DELETE FROM departments_tree
    WHERE sub_id = old.department_id;
END//
DELIMITER ;

Триггер обновления:

DELIMITER //
CREATE TRIGGER `departments_before_update` BEFORE UPDATE ON `departments` FOR EACH ROW BEGIN
    DELETE t
    FROM       departments_tree p 
    CROSS JOIN departments_tree c
    INNER JOIN departments_tree t
      ON  t.super_id = p.super_id
      AND t.sub_id = c.sub_id
    WHERE p.sub_id   = old.parent_id
      AND c.super_id = new.department_id;

    INSERT INTO departments_tree (super_id, sub_id)
        SELECT p.super_id, c.sub_id
        FROM       departments_tree p
        CROSS JOIN departments_tree c
        WHERE p.sub_id   = new.parent_id
          AND c.super_id = new.department_id;
END//

Примечание

Вам не потребуется триггер delete, если вы используете клавиши foreighn с ON DELETE CASCADE:

CREATE TABLE `departments_tree` (
    `super_id` INT(10) UNSIGNED NOT NULL,
    `sub_id` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`super_id`, `sub_id`),
    INDEX `sub_id_super_id` (`sub_id`, `super_id`),
    FOREIGN KEY (`super_id`) REFERENCES `departments` (`department_id`) ON DELETE CASCADE,
    FOREIGN KEY (`sub_id`)   REFERENCES `departments` (`department_id`) ON DELETE CASCADE
);

Примечание 2

Во многих реализациях транзитивной таблицы закрытия вы найдете столбец depth или level. Но вам не нужно это для данных требований. И я считаю, что вам это действительно не понадобится, до тех пор, пока вы не пытаетесь форматировать вывод дерева в SQL.

Ответ 4

Я думаю, что ваша главная проблема заключается в том, как перемещаться вниз, чтобы решить ее getRecursiveDepts(). Я еще не закончил код, но вы можете попробовать что-то вроде этого

file db.php

class DB {
    private $servername = "127.0.0.1";
    private $username = "root";
    private $password = "root";
    private $dbname = "test";
    private $port = '3306';

    public function getRecursiveDepts($deptIds) {
        if (!is_array($deptIds)) {
            $deptIds = array($deptIds);
        }
        $sql = "SELECT id FROM Departments WHERE parentId IN (";
        $sql .= implode(', ', $deptIds);
        $sql .= ")";

        $conn = new mysqli($this->servername, $this->username, $this->password, $this->dbname, $this->port);
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
        $result = $conn->query($sql);
        if ($result->num_rows > 0) {
            $newDept = array();
            while($row = $result->fetch_assoc()) {
                array_push($newDept, $row['id']);
            }
            $conn->close();
            $moreDepts = $this->getRecursiveDepts($newDept);
            if (is_null($moreDepts)) {
                $finalIds = array_unique(array_merge($deptIds, $newDept));
            } else {
                $finalIds = array_unique(array_merge($deptIds, $newDept, $moreDepts));
            }
            return $finalIds;
        } else {
            $conn->close();
            return null;
        }
    }

    public function getRoles($empId) {
        $sql = "SELECT role_id, department_id FROM staff_departmen_role WHERE staff_id = '$empId' GROUP BY role_id, department_id";
        $conn = new mysqli($this->servername, $this->username, $this->password, $this->dbname, $this->port);
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
        $result = $conn->query($sql);
        if ($result->num_rows > 0) {
            $emp = array();
            while($row = $result->fetch_assoc()) {
                if (!array_key_exists($row['role_id'], $emp)) {
                    $emp[$row['role_id']] = array();
                }
                array_push($emp[$row['role_id']], $row['department_id']);
            }
        }
        $conn->close();
        return $emp;
    }

    public function getEmpDetails($empId) {
        $sql = "SELECT role_id, department_id FROM staff_departmen_role WHERE staff_id = '$empId' GROUP BY role_id, department_id";
        $conn = new mysqli($this->servername, $this->username, $this->password, $this->dbname, $this->port);
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
        $result = $conn->query($sql);
        if ($result->num_rows > 0) {
            $emp = array();
            while($row = $result->fetch_assoc()) {
                if (!array_key_exists($row['role_id'], $emp)) {
                    $emp[$row['role_id']] = array();
                }
                array_push($emp[$row['role_id']], $row['department_id']);
            }
        }
        $conn->close();
        return $emp;
    }
}

файл index.php

<?php
include_once 'db.php';

$objDB = new DB();

$empId = 2;

$emps = $objDB->getRoles($empId);
foreach ($emps as $roleId => $deptIds) {
    switch ($roleId) {
        case 1:
            $allDeptIds = $objDB->getRecursiveDepts($deptIds);
            break;

        case 2://Supervisor GetEmpIds of current dept role >= 2

            break;

        case 3://Employee GetEmpIds of current dept role >= 2
            $emp = $objDB->getEmpDetails($empId);
            break;

        default:
            # code...
            break;
    }
}
$data = $objDB->getRecursiveDepts($empId);
print_r($data);
?>