Поиск и Отображение 2in1

[Александр]

Альтернативный поиск (идея Андрея) и отображение информации на пользовательских отчетах с использованием повторного использования кода MSSQL(2005).
Первая идея в том - чтобы использовать некий универсальный код - который с одной стороны будет искать объекты по всей базе по критериям - а с другой просто выбирать все дочерние объекты от родителя по условиям
Вторая идея в том - чтобы отображать полученную информацию на одном отчете общем и для поиска и для форм
(к сожалению на настоящий момент отчет для формы понимает только один аргумент поэтому для него используется тот же самый поисковый отчет(копия) но без аргументов кроме id объекта.
Выглядит это так

-Захват-80.gif (82.64 КБ) 43863 просмотра

С одной стороны для поиска - мы вызываем действие и запускаем поисковый отчет с аргументами и ищем объекты по всей базе. С другой стороны мы встаем на корневой объект некоторой структуры и показываем 'на том же самом' отчете информацию по дочерним элементам
ps
на картинке немного съехали заголовки в этих отчетах - только щас заметил

далее- посмотрим на аргументы поискового отчета

[Александр]

аргументы поискового отчета
представлены двумя блоками
Быстрый поиск (фильтрация при формировании отчета) - два поля Описание объекта и Строковый атрибут объекта
Медленный поиск (фильтрация после формирования объекта) - столько полей сколько нужно
Выглядит так

-Захват-81.gif (50.39 КБ) 43860 просмотров

имеет смысл посмотреть на два аргумента
1й - местоположение искомого фрагмента в аргументе описание объекта. Аргумент представлен строкой из двух символов для Like соответственно
'%%'-в любом месте строки
' %'-привязка к началу
'% '-привязка к концу
' '-строгое соответствие
и второе
аргумент с id строкового атрибута
этих двух полей оказалось вполне достаточно для быстрого поиска

второй блок фильтрует в найденном и представлен обычными фильтрами

Захват-87.gif (13.96 КБ) 43861 просмотр

теперь посмотрим на пример вызова и сам код

[Александр]

вызов поискового отчета

Захват-85.gif (6.56 КБ) 43859 просмотров

вызов отчета для формы

Захват-86.gif (5.61 КБ) 43860 просмотров

как видно - вызывается одна и та-же функция включающая в себя все параметры для работы в этих двух направлениях + флаг указывающий какое действие нужно произвести - найти объекты или выбрать дочерние
код функции

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

ALTER  function [LSDBO].[Ric_Get_attrClients]
/*25022010*/
/*
Отчет Форма Прочитать атрибуты объектов клиент
*/ 
        (
         @Sel		int=0,              --0 Child 1 Find 
         @ObjID		numeric(18,0)=0,    --Child/Find	ID объекта
         @Mnemo		varchar(4000)='',	--Child/Find	Мнемо
         @Mask		varchar(255)='%%',	--Find			Маска поиска like первый и последний символ
         @Descr		varchar(255)='',	--Find			Строка поиска объекта по описанию
         @IDAttr	numeric(18,0)=0,	--Find			ID строкового атрибута
         @sAttr		varchar(255)=''		--Find			Строка поиска объекта по атрибуту
         )
Returns table
 as
  return
( 
 SELECT	rw.id as isobject_id,
		rw.cd as cDate,
		vv3.value+coalesce(' ('+ vv13.value+')','') as cIst,
		vv.value as cStatus, 
		vv15.value as cForm, 
		rw.description as cName,
		vv0.value as cAbr,
		vv1.value as cManager,
		vv14.value as cIndex, 
		vv4.value as cStrana,
		vv5.value as cOkrug,
		vv6.value as cSity,
		vv7.value as cStreet,
		vv8.value as cKod,
		vv9.value as cFax,
		vv10.value as cTel,
		vv11.value as cEmail,
		abs(vv12.value) as cBy, 
		vv2.value as cWarning
   FROM	lsdbo.Ric_Get_Select(@Sel,@ObjID,@Mnemo,@Mask,@Descr,@IDAttr,@sAttr) rw left join 
		lsdbo.attrib_value av on rw.id=av.object_id and av.attrib_id=100000016000000 left join 
		lsdbo.value_string vv on av.value_id=vv.id and av.attrib_id=100000016000000 left join 
		lsdbo.attrib_value av0 on rw.id=av0.object_id and av0.attrib_id=100005427200000 left join 
		lsdbo.value_string vv0 on av0.value_id=vv0.id and av0.attrib_id=100005427200000 left join 
...
		lsdbo.attrib_value av2 on rw.id=av2.object_id and av2.attrib_id=100004087100000 left join 
		lsdbo.value_numeric vv2 on av2.value_id=vv2.id and av2.attrib_id=100004087100000 left join  
		lsdbo.attrib_value av13 on rw.id=av13.object_id and av13.attrib_id=100004086100000 left join 
		lsdbo.value_string vv13 on av13.value_id=vv13.id and av13.attrib_id=100004086100000 
)

функция принимает все параметры и просто добавляет нужные аргументы к вызываемой функции

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

lsdbo.Ric_Get_Select(@Sel,@ObjID,@Mnemo,@Mask,@Descr,@IDAttr,@sAttr) rw 

которая содержит id объекта, описание, дату создания ... в общем служебную информацию без атрибутов

посмотрим на эту функцию lsdbo.Ric_Get_Select

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

 ALTER function [LSDBO].[Ric_Get_Select]
/*25022010*/
/*Переключатель - дочерние объекты или поиск объектов*/ 
        (
         @Sel		int=0,              --0 Child 1 Find 
         @ObjID		numeric(18,0)=0,    --Child/Find	ID объекта
         @Mnemo		varchar(4000)='',	--Child/Find	Мнемо
         @Mask		varchar(255)='%%',	--Find			Маска поиска like первый и последний символ
         @Descr		varchar(255)='',	--Find			Строка поиска объекта по описанию
         @IDAttr	numeric(18,0)=0,	--Find			ID строкового атрибута
         @sAttr		varchar(255)=''		--Find			Строка поиска объекта по атрибуту
         )

Returns @Ret table                      --вернуть таблицу формата 
        (id				numeric(18,0),	--ID объекта запрос (имя для запуска из отчета) 
		 Description	varchar(255),	--Описание объекта
         Cd				datetime,		--Время создания объекта
         Author_id		int				--ID автора объекта (пользователя БД)
         ) 
as 
Begin

Insert @Ret 
 Select rw.id,
        rw.description,   
        rw.cd,
		rw.author_id										
   From lsdbo.object_reference rw 
  Where (@Sel =0 AND rw.id in (select id from LSDBO.Ric_Get_objChild(@ObjID,@Mnemo)))
        OR
        (@Sel =1 AND rw.id in (select id from LSDBO.Ric_Get_objFind(@Mnemo,@Mask,@Descr,@IDAttr,@sAttr)))
 Return
End

как видно она просто в свою очередь вызывает либо функцию поиска либо функцию отбора дочерних объектов - передавая в них уже только нужные параметры из общей кучи

посмотрим на эти функции

[Александр]

функция поиска объектов в базе

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

ALTER function [LSDBO].[Ric_Get_objFind]
/*24022010*/
/*найти объекты в БД по параметрам*/
        (
         @Mnemo		varchar(4000)='',	--Child/Find	Мнемо
         @Mask		varchar(255)='%%',	--Find			Маска поиска like первый и последний символ
         @Descr		varchar(255)='',	--Find			Строка поиска объекта по описанию
         @IDAttr	numeric(18,0)=0,	--Find			ID строкового атрибута
         @sAttr		varchar(255)=''		--Find			Строка поиска объекта по атрибуту
         )

Returns @Ret table						--вернуть таблицу формата 
        (id				numeric(18,0),	--ID объекта запрос (имя для запуска из отчета) 
		 Description	varchar(255),	--Описание объекта
         Cd				datetime,		--Время создания объекта
         Author_id		int				--ID автора объекта (пользователя БД)
         ) 
as 
Begin
 Declare @fDescr varchar(255),
         @fsAttr varchar(255)

     Set @fDescr=ltrim(left(@Mask,1))+ltrim(@Descr)+ltrim(right(@Mask,1))
     Set @fsAttr='%'+ltrim(@sAttr)+'%'

Insert @Ret 

Select rw.id,
       rw.description,
       rw.cd,
       rw.author_id										
  From lsdbo.object_reference_view rw left join lsdbo.object_type_view tw  on rw.type_id=tw.id left join
       lsdbo.attrib_value av1 on rw.id = av1.object_id and av1.attrib_id = @IDAttr left join
       lsdbo.value_string vv1 on av1.value_id = vv1.id and av1.attrib_id = @IDAttr
 Where (tw.mnemo= @Mnemo)
       and (
             (ltrim(@Descr)='' and ltrim(@sAttr)='')
              or
             ((ltrim(@Descr)<>'' and ltrim(@sAttr)='') and (rw.description like @fDescr))
              or
             ((ltrim(@Descr)='' and ltrim(@sAttr)<>'') and (vv1.value like @fsAttr))
              or
             ((ltrim(@Descr)<>'' and ltrim(@sAttr)<>'') and ((rw.description like @fDescr)and (vv1.value like @fsAttr)))
)
 Return
End

чистый поиск объектов по И
чтобы соблюсти некоторую 'приличность' мы все же оставили проверку прав на описание объекта
функция возвращает исключительно служебную информацию (без атрибутов) которая затем по id объекта обрастает атрибутами как было показано выше

посмотрим на функцию отбора дочерних объектов - которая возвращает абсолютно тот же набор колонок

[Александр]

функция по дочерним объектам

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

ALTER  function [LSDBO].[Ric_Get_objChild]
/*24022010*/
/*
прочитать дочерние объекты родителя @ObjID глубина поиска @Mnemo)
db		- взяли из системных таблиц Лоции
in		- пришло из внешней функции
attr	- прочитали атрибуты объекта
*/
 
        (@ObjID numeric(18,0)=0,      	--ID объекта
         @Mnemo varchar(4000)	        --Мнемо (перечисление всех нужных Mnemo объектов в порядке поиска)
         )

Returns @Ret table                      --вернуть таблицу формата 
--(состав колонок определяется общим синтаксисом многооператорной функции возвращающей таблицу, т.е. все что есть в запросах)  
        (id				numeric(18,0),	--ID объекта запрос (имя для запуска из отчета) 
		 Description	varchar(255),	--Описание объекта
         Cd				datetime,		--Время создания объекта
         Author_id		int,			--ID автора объекта (пользователя БД)	
         Mnemo			varchar(4)	    --Мнемо объекта 
         ) 
as 
Begin

Declare @tbl		Table (listpos int IDENTITY(1, 1) NOT NULL,str varchar(4000))
Declare @pos		int,
        @textpos	int,
        @chunklen	smallint,
        @tmpstr		nvarchar(4000),
        @leftover	nvarchar(4000),
        @tmpval		nvarchar(4000)
 
 Set @textpos = 1
 Set @leftover = ''
 While @textpos <= datalength(@Mnemo) / 2
  Begin
   Set @chunklen = 4000 - datalength(@leftover) / 2
   Set @tmpstr = @leftover + substring(@Mnemo, @textpos, @chunklen)
   Set @textpos = @textpos + @chunklen
   Set @pos = charindex(',', @tmpstr)
   While @pos > 0
    Begin
     Set @tmpval = ltrim(rtrim(left(@tmpstr, charindex(',', @tmpstr) - 1)))
     Insert @tbl (str) VALUES(@tmpval)
     Set @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
     Set @pos = charindex(',', @tmpstr)
    End
   Set @leftover = @tmpstr
  End
Insert @tbl(str) VALUES (ltrim(rtrim(@leftover)))

--MSSQL 2005 Обобщенное табличное выражение для рекурсии (Rec)
WITH Rec (id, description, cd, author_id, Mnemo)--,level)
AS (
Select rw.id,
       rw.description,
       rw.cd,
       rw.author_id,
       tw.mnemo--, 
       --0
  From lsdbo.object_type tw inner join lsdbo.object_reference rw 
       on rw.type_id = tw.id  inner join
       lsdbo.tree_link tl on rw.id = tl.link_id and tl.link_filial_id = 1  
 Where tl.parent_id =@ObjID 
       and tw.Mnemo in (select str from @tbl /*where listpos=1*/)

  UNION ALL 

Select rw.id,
       rw.description,
       rw.cd,
       rw.author_id,
       tw.mnemo--, 
       --Rec.level+1
  From lsdbo.object_type tw inner join lsdbo.object_reference rw 
       on rw.type_id = tw.id inner join
       lsdbo.tree_link tl on rw.id = tl.link_id and tl.link_filial_id = 1 inner join Rec 
       on Rec.id = tl.parent_id 
       and tw.Mnemo in (select str from @tbl /*where listpos=Rec.level+2*/)
)
Insert @Ret 

--Рекурсия
Select * 
  from Rec 
 where Rec.Mnemo=(Select str 
                 from @tbl 
                where listpos=(Select max(listpos) from @tbl ))

 Return
End

интересен параметр @Mnemo
рекурсия идет по Мнемо объектов делая фильтрацию по последнему из указанных Мнемо в параметре @Mnemo
сам параметр принимает строку (длина до 4000 символов) в которой через запятую перечислены Мнемо которые мы ищем
строка в свою очередь в начале функции преобразуется в таблицу для использования ее в секции in

что здесь интересно - через запятую мы можем указать уникальную цепочку Мнемо дочерних объектов по которым отправится рекурсия - т.е. отбор будет произведен не по всем дочерним объектам а только по объявленным - и в конце выйдут только те объекты Мнемо которых стоит последним как в строке так и соответственно в массиве который из этой строки получен

функция возвращает тот же набор колонок что и поисковая

[Александр]

в итоге мы имеем следующий алгоритм
1. в запрос забрасываются все условия
для поиска

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

select * from LSDBO.Ric_Get_attrClients(1,0,:f_type,:f_mask,:f_name,:f_idattr,:f_attr) order by cName 

для дочерних объектов

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

select * from LSDBO.Ric_Get_attrClients(0,:id,'Clts,Clt','','',0,'') order by cName

2. в зависимости от того что нужно сделать внутренние функции возвращают только служебные колонки найденных/отобранных объектов

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

        (id				numeric(18,0),	--ID объекта запрос (имя для запуска из отчета) 
		 Description	varchar(255),	--Описание объекта
         Cd				datetime,		--Время создания объекта
         Author_id		int,			--ID автора объекта (пользователя БД)	
         Mnemo			varchar(4)	    --Мнемо объекта 
         ) 

как видим ничего лишнего
3. основная вызываемая из отчета функция просто добавляет к полученным id нужные атрибуты

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

...
		vv11.value as cEmail,
		abs(vv12.value) as cBy, 
		vv2.value as cWarning
   FROM	lsdbo.Ric_Get_Select(@Sel,@ObjID,@Mnemo,@Mask,@Descr,@IDAttr,@sAttr) rw left join 
		lsdbo.attrib_value av on rw.id=av.object_id and av.attrib_id=100000016000000 left join 
		lsdbo.value_string vv on av.value_id=vv.id and av.attrib_id=100000016000000 left join 
		lsdbo.attrib_value av0 on rw.id=av0.object_id and av0.attrib_id=100005427200000 left join
...

4. вся информация выводится на 'одном' отчете

вообще все довольно круто и быстро
мы используем такой подход для ряда поисковых и дочерних объектов, а также для выпадающих списков в Party и Workflow и т.д.

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

в общем жизнь удалась - если есть вопросы - я готов поболтать на любые отвлеченные темы

[Александр]

кстати может кому нужно
чтобы рекурсивная функция Ric_Get_objChild поехала в обратную сторону достаточно поменять местами link_id и parent_id типа так

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

ALTER function [LSDBO].[Ric_Get_objParent]
/*24022010*/
/*
прочитать родительские объекты от текущего @ObjID глубина поиска @Mnemo)
db		- взяли из системных таблиц Лоции
in		- пришло из внешней функции
attr	- прочитали атрибуты объекта
*/
 
        (@ObjID numeric(18,0)=0,      	--ID объекта
         @Mnemo varchar(4000)	        --Мнемо (перечисление всех нужных Mnemo объектов в порядке поиска)
         )

Returns @Ret table                      --вернуть таблицу формата 
--(состав колонок определяется общим синтаксисом многооператорной функции возвращающей таблицу, т.е. все что есть в запросах)  
        (id				numeric(18,0),	--ID объекта запрос (имя для запуска из отчета) 
		 Description	varchar(255),	--Описание объекта
         Cd				datetime,		--Время создания объекта
         Author_id		int,			--ID автора объекта (пользователя БД)	
         Mnemo			varchar(4)	    --Мнемо объекта 
         ) 
as 
Begin

Declare @tbl		Table (listpos int IDENTITY(1, 1) NOT NULL,str varchar(4000))
Declare @pos		int,
        @textpos	int,
        @chunklen	smallint,
        @tmpstr		nvarchar(4000),
        @leftover	nvarchar(4000),
        @tmpval		nvarchar(4000)
 
 Set @textpos = 1
 Set @leftover = ''
 While @textpos <= datalength(@Mnemo) / 2
  Begin
   Set @chunklen = 4000 - datalength(@leftover) / 2
   Set @tmpstr = @leftover + substring(@Mnemo, @textpos, @chunklen)
   Set @textpos = @textpos + @chunklen
   Set @pos = charindex(',', @tmpstr)
   While @pos > 0
    Begin
     Set @tmpval = ltrim(rtrim(left(@tmpstr, charindex(',', @tmpstr) - 1)))
     Insert @tbl (str) VALUES(@tmpval)
     Set @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
     Set @pos = charindex(',', @tmpstr)
    End
   Set @leftover = @tmpstr
  End
Insert @tbl(str) VALUES (ltrim(rtrim(@leftover)))

--MSSQL 2005 Обобщенное табличное выражение для рекурсии (Rec)
WITH Rec (id, description, cd, author_id, Mnemo)--,level)
AS (
Select rw.id,
       rw.description,
       rw.cd,
       rw.author_id,
       tw.mnemo--, 
       --0
  From lsdbo.object_type tw inner join lsdbo.object_reference rw 
       on rw.type_id = tw.id  inner join
       lsdbo.tree_link tl on rw.id = tl.parent_id and tl.link_filial_id = 1  
 Where tl.link_id =@ObjID 
       and tw.Mnemo in (select str from @tbl /*where listpos=1*/)

  UNION ALL 

Select rw.id,
       rw.description,
       rw.cd,
       rw.author_id,
       tw.mnemo--, 
       --Rec.level+1
  From lsdbo.object_type tw inner join lsdbo.object_reference rw 
       on rw.type_id = tw.id inner join
       lsdbo.tree_link tl on rw.id = tl.parent_id and tl.link_filial_id = 1 inner join Rec 
       on Rec.id = tl.link_id 
       and tw.Mnemo in (select str from @tbl /*where listpos=Rec.level+2*/)
)
Insert @Ret 

--Рекурсия
Select * 
  from Rec 
 where Rec.Mnemo=(Select str 
                 from @tbl 
                where listpos=(Select max(listpos) from @tbl ))

 Return
End