28 lines
886 B
MySQL
28 lines
886 B
MySQL
|
DELIMITER $$
|
||
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` FUNCTION `vn`.`worker_isInDepartment`(vDepartmentCode VARCHAR(255))
|
||
|
|
RETURNS int(11)
|
||
|
|
DETERMINISTIC
|
||
|
|
BEGIN
|
||
|
|
/**
|
||
|
|
* Devuelve booleano si el trabajador conectado pertenece
|
||
|
|
* al departamento vDepartmentCode o a sus departamentos subordinados
|
||
|
|
*
|
||
|
|
* @param vDepartmentCode code del departamento que se desea comprobar.
|
||
|
|
* @return Devuelve verdadero si es jefe del empleado por escala jerárquica.
|
||
|
|
*/
|
||
|
|
DECLARE vIsInDepartment BOOLEAN;
|
||
|
|
WITH RECURSIVE department AS (
|
||
|
|
SELECT d.id FROM vn.department d WHERE code = vDepartmentCode
|
||
|
|
UNION
|
||
|
|
SELECT d.id
|
||
|
|
FROM department ds
|
||
|
|
JOIN vn.department d ON ds.id = d.parentFk
|
||
|
|
)
|
||
|
|
SELECT COUNT(*) INTO vIsInDepartment FROM department ds
|
||
|
|
JOIN vn.workerDepartment wd ON wd.departmentFk = ds.id
|
||
|
|
WHERE wd.workerFk = account.myUser_getId();
|
||
|
|
|
||
|
|
RETURN vIsInDepartment;
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|