341 lines
9.9 KiB
SQL
341 lines
9.9 KiB
SQL
|
|
USE partsltd_prod;
|
|
|
|
DROP PROCEDURE IF EXISTS partsltd_prod.p_ph_user_eval;
|
|
DROP PROCEDURE IF EXISTS partsltd_prod.p_ph_calc_user;
|
|
|
|
DELIMITER //
|
|
CREATE PROCEDURE partsltd_prod.p_ph_calc_user (
|
|
IN a_guid BINARY(36)
|
|
, IN a_ids_user TEXT
|
|
, IN a_get_inactive_user BIT
|
|
, IN a_ids_permission VARCHAR(4000)
|
|
, IN a_debug BIT
|
|
)
|
|
BEGIN
|
|
DECLARE v_has_filter_permission BIT;
|
|
DECLARE v_has_filter_user BIT;
|
|
DECLARE v_id_permission INT;
|
|
DECLARE v_time_start TIMESTAMP(6);
|
|
DECLARE v_ids_row_delete VARCHAR(500);
|
|
DECLARE v_code_type_error_bad_data VARCHAR(200);
|
|
DECLARE v_id_type_error_bad_data INT;
|
|
DECLARE v_code_error_permission VARCHAR(200);
|
|
DECLARE v_id_permission_required INT;
|
|
DECLARE v_priority_access_level_required INT;
|
|
DECLARE v_priority_access_level_view INT;
|
|
|
|
SET v_time_start := CURRENT_TIMESTAMP(6);
|
|
SET v_code_type_error_bad_data := (SELECT code FROM partsltd_prod.CORE_Msg_Error_Type WHERE code = 'BAD_DATA');
|
|
SET v_id_type_error_bad_data := (SELECT id_type FROM partsltd_prod.CORE_Msg_Error_Type WHERE code = v_code_type_error_bad_data);
|
|
SET v_code_error_permission := (SELECT code FROM partsltd_prod.CORE_Msg_Error_Type WHERE id_type = 2);
|
|
SET v_priority_access_level_view := (SELECT priority FROM partsltd_prod.PH_Access_Level WHERE code = 'VIEW' LIMIT 1);
|
|
|
|
CALL partsltd_prod.p_core_validate_guid ( a_guid );
|
|
SET a_ids_user := TRIM(IFNULL(a_ids_user, ''));
|
|
SET a_get_inactive_user := IFNULL(a_get_inactive_user, 0);
|
|
SET a_ids_permission := TRIM(IFNULL(a_ids_permission, ''));
|
|
SET a_debug := IFNULL(a_debug, 0);
|
|
|
|
IF a_debug = 1 THEN
|
|
SELECT
|
|
a_guid
|
|
, a_ids_user
|
|
, a_get_inactive_user
|
|
, a_ids_permission
|
|
, a_debug
|
|
;
|
|
END IF;
|
|
|
|
DROP TABLE IF EXISTS tmp_Calc_User;
|
|
DROP TABLE IF EXISTS tmp_User_Calc_User;
|
|
DROP TABLE IF EXISTS tmp_Split_Calc_User;
|
|
|
|
CREATE TEMPORARY TABLE tmp_Calc_User (
|
|
id_row INT PRIMARY KEY AUTO_INCREMENT NOT NULL
|
|
, id_user INT NULL
|
|
, id_permission_required INT NOT NULL
|
|
, priority_access_level_required INT NOT NULL
|
|
, is_super_user BIT NULL
|
|
, priority_access_level_user INT NULL
|
|
, has_access BIT
|
|
);
|
|
|
|
CREATE TEMPORARY TABLE tmp_User_Calc_User (
|
|
id_user INT NOT NULL
|
|
, is_super_user BIT NOT NULL
|
|
-- , id_access_level INT
|
|
, priority_access_level INT NOT NULL
|
|
);
|
|
|
|
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_Msg_Error (
|
|
display_order INT NOT NULL PRIMARY KEY AUTO_INCREMENT
|
|
, id_type INT NULL
|
|
, code VARCHAR(50) NOT NULL
|
|
, msg VARCHAR(4000) NOT NULL
|
|
);
|
|
|
|
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_Split_Calc_User (
|
|
substring VARCHAR(4000) NOT NULL
|
|
, as_int INT NULL
|
|
);
|
|
DELETE FROM tmp_Split_Calc_User;
|
|
|
|
SET v_has_filter_user = CASE WHEN a_ids_user = '' THEN 0 ELSE 1 END;
|
|
SET a_ids_permission = REPLACE(a_ids_permission, '|', ',');
|
|
SET v_has_filter_permission = CASE WHEN a_ids_permission = '' THEN 0 ELSE 1 END;
|
|
-- SET v_id_access_level_view = (SELECT id_access_level FROM partsltd_prod.PH_Access_Level WHERE code = 'VIEW' LIMIT 1);
|
|
|
|
|
|
-- Permission IDs
|
|
IF NOT v_has_filter_permission THEN
|
|
INSERT INTO tmp_Msg_Error (
|
|
id_type
|
|
, code
|
|
, msg
|
|
)
|
|
SELECT
|
|
v_id_type_error_bad_data
|
|
, v_code_type_error_bad_data
|
|
, 'Permission ID required.'
|
|
;
|
|
ELSE
|
|
CALL partsltd_prod.p_core_split(a_guid, a_ids_permission, ',', a_debug);
|
|
|
|
INSERT INTO tmp_Split_Calc_User (
|
|
substring
|
|
, as_int
|
|
)
|
|
SELECT
|
|
substring
|
|
, CONVERT(substring, DECIMAL(10,0)) AS as_int
|
|
FROM partsltd_prod.CORE_Split_Temp
|
|
WHERE
|
|
GUID = a_guid
|
|
AND NOT ISNULL(substring)
|
|
AND substring != ''
|
|
;
|
|
|
|
CALL partsltd_prod.p_core_clear_split( a_guid );
|
|
|
|
-- Invalid or inactive
|
|
IF EXISTS (
|
|
SELECT PERM.id_permission
|
|
FROM tmp_Split_Calc_User t_S
|
|
LEFT JOIN partsltd_prod.PH_Permission PERM ON t_S.as_int = PERM.id_permission
|
|
WHERE
|
|
ISNULL(t_S.as_int)
|
|
OR ISNULL(PERM.id_permission)
|
|
OR PERM.active = 0
|
|
) THEN
|
|
INSERT INTO tmp_Msg_Error (
|
|
id_type
|
|
, code
|
|
, msg
|
|
)
|
|
SELECT
|
|
v_id_type_error_bad_data
|
|
, v_code_type_error_bad_data
|
|
, CONCAT('Invalid or inactive permission IDs: ', IFNULL(GROUP_CONCAT(t_S.substring SEPARATOR ', '), 'NULL'))
|
|
FROM tmp_Split_Calc_User t_S
|
|
LEFT JOIN partsltd_prod.PH_Permission PERM ON t_S.as_int = PERM.id_permission
|
|
WHERE
|
|
ISNULL(t_S.as_int)
|
|
OR ISNULL(PERM.id_permission)
|
|
OR PERM.active = 0
|
|
;
|
|
ELSE
|
|
SELECT
|
|
PERM.id_permission
|
|
, PERM.id_access_level_required
|
|
INTO
|
|
v_id_permission_required
|
|
, v_priority_access_level_required
|
|
FROM tmp_Split_Calc_User t_S
|
|
LEFT JOIN partsltd_prod.PH_Permission PERM ON t_S.as_int = PERM.id_permission
|
|
INNER JOIN partsltd_prod.PH_Access_Level AL ON PERM.id_access_level_required = AL.id_access_level
|
|
ORDER BY AL.priority ASC
|
|
LIMIT 1
|
|
;
|
|
|
|
IF ISNULL(v_id_permission_required) THEN
|
|
INSERT INTO tmp_Msg_Error (
|
|
id_type
|
|
, code
|
|
, msg
|
|
)
|
|
SELECT
|
|
v_id_type_error_bad_data
|
|
, v_code_type_error_bad_data
|
|
, 'Valid Permission ID required.'
|
|
;
|
|
END IF;
|
|
END IF;
|
|
END IF;
|
|
DELETE FROM tmp_Split_Calc_User;
|
|
|
|
-- Users
|
|
CALL partsltd_prod.p_core_split(a_guid, a_ids_user, ',', a_debug);
|
|
|
|
INSERT INTO tmp_Split_Calc_User (
|
|
substring
|
|
, as_int
|
|
)
|
|
SELECT
|
|
substring
|
|
, CONVERT(substring, DECIMAL(10,0)) AS as_int
|
|
FROM partsltd_prod.CORE_Split_Temp
|
|
WHERE
|
|
GUID = a_guid
|
|
AND NOT ISNULL(substring)
|
|
AND substring != ''
|
|
;
|
|
|
|
CALL partsltd_prod.p_core_clear_split( a_guid );
|
|
|
|
-- Invalid or inactive
|
|
IF EXISTS (SELECT U.id_user FROM tmp_Split_Calc_User t_S LEFT JOIN partsltd_prod.PH_User U ON t_S.as_int = U.id_user WHERE ISNULL(t_S.as_int) OR ISNULL(U.id_user) OR (a_get_inactive_user = 0 AND U.active = 0)) THEN
|
|
INSERT INTO tmp_Msg_Error (
|
|
id_type
|
|
, code
|
|
, msg
|
|
)
|
|
SELECT
|
|
v_id_type_error_bad_data
|
|
, v_code_type_error_bad_data
|
|
, CONCAT('Invalid or inactive user IDs: ', IFNULL(GROUP_CONCAT(t_S.substring SEPARATOR ', '), 'NULL'))
|
|
FROM tmp_Split_Calc_User t_S
|
|
LEFT JOIN partsltd_prod.PH_User U ON t_S.as_int = U.id_user
|
|
WHERE
|
|
ISNULL(t_S.as_int)
|
|
OR ISNULL(U.id_user)
|
|
OR (
|
|
a_get_inactive_user = 0
|
|
AND U.active = 0
|
|
)
|
|
;
|
|
ELSE
|
|
IF NOT EXISTS (SELECT * FROM tmp_Split_Calc_User) THEN
|
|
INSERT INTO tmp_Split_Calc_User (substring, as_int)
|
|
VALUES ( '', NULL );
|
|
END IF;
|
|
|
|
IF a_debug = 1 THEN
|
|
SELECT *
|
|
FROM tmp_Split_Calc_User;
|
|
END IF;
|
|
|
|
INSERT INTO tmp_User_Calc_User (
|
|
id_user
|
|
, is_super_user
|
|
, priority_access_level
|
|
)
|
|
SELECT
|
|
U.id_user
|
|
, IFNULL(U.is_super_user, 0) AS is_super_user
|
|
, IFNULL(MIN(AL_U.priority), v_priority_access_level_view) AS priority_access_level
|
|
FROM tmp_Split_Calc_User t_S
|
|
INNER JOIN partsltd_prod.PH_User U ON t_S.as_int = U.id_user
|
|
LEFT JOIN partsltd_prod.PH_User_Role_Link URL
|
|
ON U.id_user = URL.id_user
|
|
AND URL.active
|
|
LEFT JOIN partsltd_prod.PH_Role_Permission_Link RPL
|
|
ON URL.id_role = RPL.id_role
|
|
AND RPL.active
|
|
LEFT JOIN partsltd_prod.PH_Access_Level AL_U
|
|
ON RPL.id_access_level = AL_U.id_access_level
|
|
AND AL_U.active
|
|
GROUP BY U.id_user
|
|
;
|
|
|
|
INSERT INTO tmp_Calc_User (
|
|
id_user
|
|
, id_permission_required
|
|
, priority_access_level_required
|
|
, priority_access_level_user
|
|
, is_super_user
|
|
)
|
|
SELECT
|
|
t_UCU.id_user
|
|
, v_id_permission_required
|
|
, v_priority_access_level_required
|
|
, t_UCU.priority_access_level AS priority_access_level_user
|
|
, t_UCU.is_super_user AS is_super_user
|
|
FROM tmp_User_Calc_User t_UCU
|
|
;
|
|
END IF;
|
|
DELETE FROM tmp_Split_Calc_User;
|
|
|
|
|
|
-- Calculated fields
|
|
UPDATE tmp_Calc_User t_CU
|
|
SET
|
|
t_CU.has_access = (
|
|
(t_CU.is_super_user = 1)
|
|
OR (t_CU.priority_access_level_user <= t_CU.priority_access_level_required)
|
|
)
|
|
;
|
|
|
|
-- Export data to staging table
|
|
IF NOT EXISTS (SELECT * FROM tmp_Msg_Error) THEN
|
|
START TRANSACTION;
|
|
INSERT INTO partsltd_prod.PH_Calc_User_Temp (
|
|
guid
|
|
, id_user
|
|
, id_permission_required
|
|
, priority_access_level_required
|
|
, priority_access_level_user
|
|
, is_super_user
|
|
, has_access
|
|
)
|
|
SELECT
|
|
a_guid
|
|
, t_CU.id_user
|
|
, t_CU.id_permission_required
|
|
, t_CU.priority_access_level_required
|
|
, t_CU.priority_access_level_user
|
|
, t_CU.is_super_user
|
|
, t_CU.has_access
|
|
FROM tmp_Calc_User t_CU
|
|
;
|
|
COMMIT;
|
|
END IF;
|
|
|
|
IF a_debug = 1 THEN
|
|
SELECT * FROM tmp_Msg_Error;
|
|
SELECT * FROM tmp_Calc_User;
|
|
SELECT * FROM tmp_User_Calc_User;
|
|
SELECT * FROM partsltd_prod.PH_Calc_User_Temp WHERE GUID = a_guid;
|
|
CALL partsltd_prod.p_ph_clear_calc_user ( a_guid, a_debug );
|
|
END IF;
|
|
|
|
-- Clean up
|
|
DROP TABLE IF EXISTS tmp_Calc_User;
|
|
DROP TABLE IF EXISTS tmp_User_Calc_User;
|
|
DELETE FROM tmp_Split_Calc_User;
|
|
|
|
IF a_debug = 1 THEN
|
|
CALL partsltd_prod.p_debug_timing_reporting( v_time_start );
|
|
END IF;
|
|
END //
|
|
DELIMITER ;
|
|
|
|
/*
|
|
|
|
CALL partsltd_prod.p_ph_calc_user (
|
|
'chips ' -- a_guid
|
|
, 1 -- a_ids_user
|
|
, 0 -- a_get_inactive_user
|
|
, '2' -- a_ids_permission
|
|
, '1' -- a_ids_access_level
|
|
, 0 -- a_debug
|
|
);
|
|
CALL partsltd_prod.p_ph_calc_user (
|
|
'chips ' -- a_guid
|
|
, 1 -- a_ids_user
|
|
, 0 -- a_get_inactive_user
|
|
, '2' -- a_ids_permission
|
|
, '1' -- a_ids_access_level
|
|
, 0 -- a_debug
|
|
);
|
|
*/ |