Рекурсивный обход сотрудников компании (mssql2005)

Обсуждение технических вопросов работы с системами управления базами данных (СУБД), работе с языком SQL и скриптовыми языками.
Ответить
Аватара пользователя
Александр
Активный участник
Сообщения: 1652
Зарегистрирован: 24 авг 2006, 08:06
Используемое ПО: Lotsia PDM PLUS
Откуда: 55.745578,37.665825

Рекурсивный обход сотрудников компании (mssql2005)

Сообщение Александр »

... по материалам супер популярной темы http://www.lplm.ru/phpBB2/viewtopic.php?f=2&t=249
Может кому и пригодится
мы начали разборки с атрибутивными формами на пользовательских отчетах и вот к чему это привело :wink:
есть структура компании
Захват-29.gif
нужно при активации либо корневого объекта либо какого либо из отделов получить список сотрудников с атрибутивной информацией
если для отделов все просто - можно использовать дочерний отчет - то для корня не совсем поскольку все сотрудники расположены на 2 уровня глубже... а так хотелось посмотреть на них именно из корня :wink: (причем компания и отделы - объекты разных типов, это просто к слову)
поэтому делаем пользовательский отчет - указываем его как атрибутивная форма для объектов Компания и Отдел - (для отделов тоже - потому что стандартный дочерний отчет уже не вписывается в концепцию)
внутри все выглядит так
отчет с аргументом - выделенный объект
Захват-30.gif
запрос как видим никакой - все вынесено в SQL чтобы избавиться от _view
переходим туда
первая функция - рекурсивный обход всех дочерних объектов (вернуть все id и сопутствующие колонки)

Код: Выделить всё

ALTER  function [LSDBO].[Ric_Get_Personnel]
/*Прочитать всех действующих сотрудников филиала (имя филиала находится в атрибуте объекта @ObjID)*/ 
        (@ObjID numeric(18,0)=0      	--ID объекта
         )

Returns @Ret table                      --вернуть таблицу формата 
--(состав колонок определяется общим синтаксисом многооператорной функции возвращающей таблицу, т.е. все что есть в запросах)  

        (isobject_id numeric(18,0),     --ID объекта сотрудник (имя для запуска из отчета) 
         fFIO varchar(255),             --Описание объекта сотрудник
         fID int,                       --ID сотрудника (логина) БД
         dMnemo varchar(4),             --Mnemo объекта (может быть сотрудник 'Подч' или отдел 'Деп')
         dParent numeric(18,0),         --ID родителя сотрудника объекта  
         uID int,                       --ID сотрудника (логина) БД 
         uCN int                        --юзер подключен -1 или нет 0
         ) 
as 
Begin

--MSSQL 2005 Обобщенное табличное выражение для рекурсии (Personnel)
WITH Personnel (isobject_id, fFIO, fID, dMnemo, dParent) AS 
(
--закрепленные данные (дочерние элементы от заданного)
 Select tChild.isobject_id, tChild.fFIO, tChild.fID, tChild.dMnemo, tChild.dParent
   From (Select rw.id as isobject_id,
                rw.description as fFIO,
                vv.value as fID,   
                tw.mnemo as dMnemo, 
                tl.parent_id as dParent
           From lsdbo.object_reference rw left join lsdbo.object_type tw  on rw.type_id = tw.id  left join
                lsdbo.tree_link tl on rw.id = tl.link_id and  link_filial_id = 1 left join
                lsdbo.attrib_value av on rw.id = av.object_id and av.attrib_id = 100004060200000 left join 
                lsdbo.value_numeric vv on av.value_id = vv.id and av.attrib_id = 100004060200000 
         Where  tw.mnemo in ('Деп','Подч')) tChild
  where tChild.dParent =@ObjID
 
 Union ALL
--рекурсивные данные (Дочерние объекты от закрепленных) тот же запрос но без внешних соединений
 Select tChild.isobject_id, tChild.fFIO, tChild.fID, tChild.dMnemo, tChild.dParent
   From (Select rw.id as isobject_id,
                rw.description as fFIO,
                (Select vv.value 
                   From lsdbo.attrib_value_view av,
                        lsdbo.value_numeric vv 
                  Where av.value_id = vv.id 
                        AND av.object_id = rw.id
                        AND av.treelink_id = 0 
                        AND av.attrib_id = 100004060200000) as fID,
                tw.mnemo as dMnemo, 
                tl.parent_id as dParent
           From lsdbo.object_reference rw,
                lsdbo.object_type tw,
                lsdbo.tree_link tl
          Where rw.type_id = tw.id  
                and rw.id = tl.link_id
                and tl.link_filial_id = 1 
                and tw.Mnemo in ('Деп','Подч') ) tChild
 inner join Personnel ON Personnel.isobject_id = tChild.dParent  -- условие выхода (глубина рекурсии)- конец закрепленных данных
)

Insert @Ret 
--запуск рекурсии и наполнение возвращаемой таблицы + состояние пользователя подключен или нет
 Select *
   From (Select * from Personnel where dMnemo='Подч') as tPersonnel left join --убрать отделы
        (Select tEmpl.user_id uID,
                Case When tLogin.loginame is null Then 0 Else 1 End uCN
           From lsdbo.empldb tEmpl left join (Select distinct loginame From master..sysprocesses) as tLogin
             on tEmpl.loginname=tLogin.loginame) as tUser
     on tPersonnel.fID=tUser.uID

 Return
End
работает под MSSQL2005 - запрос не оптимизирован - просто не хватает знаний как это сделать, но не важно работает вроде нормально - сотрудников не очень много. Он вытаскивает все дочерние отделы и всех дочерних сотрудников
вторая функция - добавить к результатам первой нужные атрибуты (собственно эта функция и вызывается из отчета, вызывая в свою очередь первую)

Код: Выделить всё

ALTER function [LSDBO].[Ric_Get_ePersonnel]
/*Добавить атрибутов к действующим сотрудникам Филиала (корневой объект сотрудников Филиала компании @ObjID)*/ 
        (@ObjID numeric(18,0)=0      	--ID объекта
         )
Returns table
 as
 Return
(
 Select *
   From
        (Select isobject_id, fFIO, fID, uCn from LSDBO.Ric_Get_Personnel(@ObjID)) as tPersonnel left join 
        (Select vv3.value as fDep,      --Отдел
                vv4.value as fDol,      --Должность
                vv6.value as fBdDate,   --День рождения
                vv7.value as fStatus,   --Статус
                rw.cd as uCD,           --Дата регистрации в БД
                rw.id as objID          --ID объекта сотрудник
           From lsdbo.object_type tw left join lsdbo.object_reference rw on rw.type_id = tw.id left join
                lsdbo.attrib_value av3 on rw.id = av3.object_id and av3.attrib_id = 3000000000087 left join 
                lsdbo.value_string vv3 on av3.value_id = vv3.id and av3.attrib_id = 3000000000087 left join 
                lsdbo.attrib_value av4 on rw.id = av4.object_id and av4.attrib_id = 3000000000086 left join 
                lsdbo.value_string vv4 on av4.value_id = vv4.id and av4.attrib_id = 3000000000086 left join 
                lsdbo.attrib_value av6 on rw.id = av6.object_id and av6.attrib_id = 100000006300005 left join 
                lsdbo.value_datetime vv6 on av6.value_id = vv6.id and av6.attrib_id = 100000006300005 left join 
                lsdbo.attrib_value av7 on rw.id = av7.object_id and av7.attrib_id = 100000012000005 left join 
                lsdbo.value_string vv7 on av7.value_id = vv7.id and av7.attrib_id = 100000012000005 ) as tAttrib
     on tPersonnel.isobject_id=tAttrib.objID
)
Вот собственно и все. При активации либо корневого объекта либо объекта первого уровня - данная форма рисует отчет. Код разбит на две функции поскольку может использоваться либо весь - либо только первая часть (допустим в действии для выпадающего списка - получить ФИО всех сотрудников их id как объектов так и пользователей и статус подключения к БД)

Спасибо Юрию и Андрею!
Если можно как-то оптимизировать первую функцию (рекурсия) - будет еще круче, не знаю только как.... (немного тормозит выбор значения атрибута содержащего номер ID юзера)
ps
Можно конечно было не заморачиваться с рекурсией и просто вытащить все типы Сотрудник - но дело в том что часть из них в архиве, часть из других филиалов а часть еще где-то, и весь этот анализ (разборки по дополнительным 2- 4 атрибутам) работает немного медленней по сравнением с отбором всех дочерних объектов вниз

Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный

Аватара пользователя
Александр
Активный участник
Сообщения: 1652
Зарегистрирован: 24 авг 2006, 08:06
Используемое ПО: Lotsia PDM PLUS
Откуда: 55.745578,37.665825

Re: Рекурсивный обход сотрудников компании (mssql2005)

Сообщение Александр »

а картинки то - слетели!! :wink: :wink:
вот первая
Захват-29.gif
Захват-29.gif (18.5 КБ) 10973 просмотра
вот вторая
Захват-30.gif
Захват-30.gif (23.44 КБ) 10973 просмотра
раз уж такое дело
вот третья
Захват-31.gif
Захват-31.gif (7.72 КБ) 10971 просмотр
вот четвертая ...упс а четвертую не разрешают добавить - вроде и содержание приличное - но говорят не больше 3х за раз, так что обойдемся без четвертой - там сопоставление формы объекту :wink: :wink: :wink: :wink:

Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный

Ответить