Может кому и пригодится
мы начали разборки с атрибутивными формами на пользовательских отчетах и вот к чему это привело
есть структура компаниинужно при активации либо корневого объекта либо какого либо из отделов получить список сотрудников с атрибутивной информацией
если для отделов все просто - можно использовать дочерний отчет - то для корня не совсем поскольку все сотрудники расположены на 2 уровня глубже... а так хотелось посмотреть на них именно из корня
(причем компания и отделы - объекты разных типов, это просто к слову) поэтому делаем пользовательский отчет - указываем его как атрибутивная форма для объектов Компания и Отдел - (для отделов тоже - потому что стандартный дочерний отчет уже не вписывается в концепцию)
внутри все выглядит так
отчет с аргументом - выделенный объект запрос как видим никакой - все вынесено в 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вторая функция - добавить к результатам первой нужные атрибуты (собственно эта функция и вызывается из отчета, вызывая в свою очередь первую)
Код: Выделить всё
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 юзера)
ps
Можно конечно было не заморачиваться с рекурсией и просто вытащить все типы Сотрудник - но дело в том что часть из них в архиве, часть из других филиалов а часть еще где-то, и весь этот анализ (разборки по дополнительным 2- 4 атрибутам) работает немного медленней по сравнением с отбором всех дочерних объектов вниз