--Action_People
SELECT SYS_CONNECT_BY_PATH(Name, '/') AS Path
FROM Action_People WHERE APG_ID IS NOT NULL
START WITH APG_ID IS NULL
CONNECT BY PRIOR AP_ID = APG_ID

SELECT level, AP_ID, APG_ID, Name
FROM Action_People WHERE APG_ID IS NOT NULL
START WITH APG_ID IS NULL
CONNECT BY PRIOR AP_ID = APG_ID
ORDER BY 4

--Hobby
SELECT SYS_CONNECT_BY_PATH(Hobby_Name, '/') AS Path
FROM Hobby
WHERE Hobby_Type_ID IS NOT NULL
START WITH Hobby_Type_ID IS NULL
CONNECT BY PRIOR Hobby_ID = Hobby_Type_ID

SELECT level, Hobby_ID, Hobby_Type_ID, Hobby_Name
FROM Hobby
--where Hobby_Type_ID is not null
START WITH Hobby_Type_ID IS NULL
CONNECT BY PRIOR Hobby_ID = Hobby_Type_ID
ORDER BY 4

--Employer
SELECT SYS_CONNECT_BY_PATH(Concat(Concat(FIRST_NAME, ' '), LAST_NAME), '/') AS Путь
FROM Employer
WHERE Parent_id IS NOT NULL
START WITH Parent_id IS NULL
CONNECT BY PRIOR Employer_id = Parent_id

SELECT SYS_CONNECT_BY_PATH(Concat(Concat(TOP.Name, ' '), Emp.LAST_NAME), '/') AS Путь
FROM Employer Emp, Type_of_Person TOP
WHERE Parent_id IS NOT NULL AND TOP.TOP_id = Emp.TOP_id
START WITH Parent_id IS NULL
CONNECT BY PRIOR Employer_id = Parent_id

SELECT level, Employer_id, Parent_id, Concat(Concat(FIRST_NAME, ' '), LAST_NAME)
FROM Employer
WHERE Parent_id IS NOT NULL
START WITH Parent_id IS NULL
CONNECT BY PRIOR Employer_id = Parent_id
ORDER BY 4

--Черновые идеи с хешем
SELECT ora_hash(to_clob('Некая строка для проверки')) hash_ FROM dual
UNION ALL
SELECT ora_hash(to_clob('Некая строка для проверки')) hash_ FROM dual

--Расширяем сознание в возможности управления иерархией
SELECT employer_id, parent_id, Concat(Concat(FIRST_NAME, ' '), LAST_NAME) AS Имя_Фамилия, level,
CONNECT_BY_ISLEAF AS IsLeaf,
PRIOR Concat(Concat(FIRST_NAME, ' '), LAST_NAME) AS Parent,
CONNECT_BY_ROOT Concat(Concat(FIRST_NAME, ' '), LAST_NAME) AS Root
FROM employer
START WITH parent_id IS NULL
CONNECT BY PRIOR Employer_id = Parent_id
ORDER SIBLINGS BY Concat(Concat(FIRST_NAME, ' '), LAST_NAME);

--А также возможность шифровать данные
SELECT EMP.EMPLOYER_ID, COALESCE(EMP.IMAGE, '(нету)') AS IMAGE, Concat(Concat(Concat(Concat(EMP.Last_NAME, ' '), EMP.First_NAME), ' '), EMP.Middle_NAME) AS FIO, AP.NAME, EMP.BIRTH_DATE, substr(CAST(Emp.TELEPHONE AS VARCHAR(22)), 1, 4)||'******'||substr(CAST(Emp.TELEPHONE AS VARCHAR(22)), -1, 3) AS Telephone, TOP.Name, RANK.NAME, EMP.CONT_NUMBER, substr(CAST(Emp.Password AS VARCHAR(10)), 1, 0)||'********' AS Password, Emp.PRIM, Emp.PARENT_ID FROM employer emp, action_people AP, Type_of_person TOP, RANK WHERE Emp.AP_ID = AP.AP_ID AND TOP.TOP_ID = EMP.TOP_ID AND EMP.RANK_ID = RANK.RANK_ID

--Вспоминаем путь
SELECT SYS_CONNECT_BY_PATH(Concat(Concat(substr(TOP.Name, 1, 1), ' '), Emp.LAST_NAME), '/') AS Путь
FROM Employer Emp, Type_of_Person TOP
WHERE Parent_id IS NOT NULL AND TOP.TOP_id = Emp.TOP_id
START WITH Parent_id IS NULL
CONNECT BY PRIOR Employer_id = Parent_id

SELECT employer_id, parent_id, Concat(Concat(FIRST_NAME, ' '), LAST_NAME) AS Имя_Фамилия, level,
CONNECT_BY_ISLEAF AS IsLeaf,
PRIOR Concat(Concat(FIRST_NAME, ' '), LAST_NAME) AS Parent,
CONNECT_BY_ROOT Concat(Concat(FIRST_NAME, ' '), LAST_NAME) AS Root
FROM employer
START WITH parent_id IS NULL
CONNECT BY PRIOR Employer_id = Parent_id
ORDER SIBLINGS BY Concat(Concat(FIRST_NAME, ' '), LAST_NAME);

--ВОТ ТЕПЕРЬ ПОШЛИ ИДЕИ НАСЧЁТ ДОСТУПА!!!

--Данные подчинённых и его - Контент-Менеджер
SELECT EMP.EMPLOYER_ID, COALESCE(EMP.IMAGE, '(нету)') AS IMAGE, Concat(Concat(Concat(Concat(EMP.Last_NAME, ' '), EMP.First_NAME), ' '), EMP.Middle_NAME) AS FIO, AP.NAME, EMP.BIRTH_DATE, substr(CAST(Emp.TELEPHONE AS VARCHAR(22)), 1, 4)||'******'||substr(CAST(Emp.TELEPHONE AS VARCHAR(22)), -1, 3) AS Telephone, TOP.Name, RANK.NAME, EMP.CONT_NUMBER, substr(CAST(Emp.Password AS VARCHAR(10)), 1, 0)||'********' AS Password, Emp.PRIM, Emp.PARENT_ID FROM employer emp, action_people AP, Type_of_person TOP, RANK WHERE Emp.AP_ID = AP.AP_ID AND TOP.TOP_ID = EMP.TOP_ID AND EMP.RANK_ID = RANK.RANK_ID
AND Level >= (SELECT level FROM employer EMP WHERE Employer_ID = 3 START WITH parent_id = (SELECT parent_id FROM Employer EMP WHERE Employer_ID = 3) CONNECT BY PRIOR Employer_id = Parent_id)
AND CONNECT_BY_ROOT Employer_id = (SELECT CONNECT_BY_ROOT Employer_id AS Root FROM employer EMP WHERE Employer_ID = 3 START WITH parent_id = (SELECT parent_id FROM Employer EMP WHERE Employer_ID = 3) CONNECT BY PRIOR Employer_id = Parent_id)
START WITH parent_id IN (SELECT parent_id FROM Employer EMP WHERE Employer_ID = 3)
CONNECT BY PRIOR Employer_id = Parent_id

--Данные администратора (не важно, каков уровень )
SELECT EMP.EMPLOYER_ID, COALESCE(EMP.IMAGE, '(нету)') AS IMAGE, Concat(Concat(Concat(Concat(EMP.Last_NAME, ' '), EMP.First_NAME), ' '), EMP.Middle_NAME) AS FIO, AP.NAME, EMP.BIRTH_DATE, substr(CAST(Emp.TELEPHONE AS VARCHAR(22)), 1, 4)||'******'||substr(CAST(Emp.TELEPHONE AS VARCHAR(22)), -1, 3) AS Telephone, TOP.Name, RANK.NAME, EMP.CONT_NUMBER, substr(CAST(Emp.Password AS VARCHAR(10)), 1, 0)||'********' AS Password, Emp.PRIM, Emp.PARENT_ID FROM employer emp, action_people AP, Type_of_person TOP, RANK WHERE Emp.AP_ID = AP.AP_ID AND TOP.TOP_ID = EMP.TOP_ID AND EMP.RANK_ID = RANK.RANK_ID
AND CONNECT_BY_ROOT Employer_id = (SELECT CONNECT_BY_ROOT Employer_id AS Root FROM employer EMP WHERE Employer_ID = 1 START WITH parent_id IS NULL CONNECT BY PRIOR Employer_id = Parent_id)
START WITH parent_id IS NULL
CONNECT BY PRIOR Employer_id = Parent_id

--Для директора (ему доступны все данные)
SELECT EMP.EMPLOYER_ID, COALESCE(EMP.IMAGE, '(нету)') AS IMAGE, Concat(Concat(Concat(Concat(EMP.Last_NAME, ' '), EMP.First_NAME), ' '), EMP.Middle_NAME) AS FIO, AP.NAME, EMP.BIRTH_DATE, substr(CAST(Emp.TELEPHONE AS VARCHAR(22)), 1, 4)||'******'||substr(CAST(Emp.TELEPHONE AS VARCHAR(22)), -1, 3) AS Telephone, TOP.Name, RANK.NAME, EMP.CONT_NUMBER, substr(CAST(Emp.Password AS VARCHAR(10)), 1, 0)||'********' AS Password, Emp.PRIM, Emp.PARENT_ID FROM employer emp, action_people AP, Type_of_person TOP, RANK WHERE Emp.AP_ID = AP.AP_ID AND TOP.TOP_ID = EMP.TOP_ID AND EMP.RANK_ID = RANK.RANK_ID

--ИДЕЯ С ДРУГОЙ ТАБЛИЦЫ, которая нас интересует (для КМ)

--Statistic (От Кавина и его подчинённых данные об их статьях)
SELECT DISTINCT WS.WORKSTATION_ID, WS.TITLE, WS.AUTHOR, WS.S_EMP_ID, WS.PUBLIC_DATE, WS.VIEW_COUNT, WS.PRIM
FROM WORKSTATION WS, Site_Employer S_EMP, Employer EMP
WHERE WS.S_EMP_id = S_EMP.S_EMP_id AND S_EMP.Employer_id = Emp.Employer_id
AND Level >= (SELECT level FROM employer EMP WHERE Employer_ID = 3 START WITH parent_id = (SELECT parent_id FROM Employer EMP WHERE Employer_ID = 3) CONNECT BY PRIOR Employer_id = Parent_id)
AND CONNECT_BY_ROOT EMP.Employer_id = (SELECT CONNECT_BY_ROOT EMP.Employer_id AS Root FROM employer EMP WHERE EMP.Employer_ID = 3 START WITH EMP.parent_id = (SELECT EMP.parent_id FROM Employer EMP WHERE EMP.Employer_ID = 3) CONNECT BY PRIOR EMP.Employer_id = EMP.Parent_id)
START WITH EMP.parent_id IN (SELECT EMP.parent_id FROM Employer EMP WHERE EMP.Employer_ID = 3)
CONNECT BY PRIOR EMP.Employer_id = EMP.Parent_id