Поиск и Отображение 2in1
- Александр
- Активный участник
- Сообщения: 1652
- Зарегистрирован: 24 авг 2006, 08:06
- Используемое ПО: Lotsia PDM PLUS
- Откуда: 55.745578,37.665825
Поиск и Отображение 2in1
Альтернативный поиск (идея Андрея) и отображение информации на пользовательских отчетах с использованием повторного использования кода MSSQL(2005).
Первая идея в том - чтобы использовать некий универсальный код - который с одной стороны будет искать объекты по всей базе по критериям - а с другой просто выбирать все дочерние объекты от родителя по условиям
Вторая идея в том - чтобы отображать полученную информацию на одном отчете общем и для поиска и для форм
(к сожалению на настоящий момент отчет для формы понимает только один аргумент поэтому для него используется тот же самый поисковый отчет(копия) но без аргументов кроме id объекта.
Выглядит это так С одной стороны для поиска - мы вызываем действие и запускаем поисковый отчет с аргументами и ищем объекты по всей базе. С другой стороны мы встаем на корневой объект некоторой структуры и показываем 'на том же самом' отчете информацию по дочерним элементам
ps
на картинке немного съехали заголовки в этих отчетах - только щас заметил
далее- посмотрим на аргументы поискового отчета
Первая идея в том - чтобы использовать некий универсальный код - который с одной стороны будет искать объекты по всей базе по критериям - а с другой просто выбирать все дочерние объекты от родителя по условиям
Вторая идея в том - чтобы отображать полученную информацию на одном отчете общем и для поиска и для форм
(к сожалению на настоящий момент отчет для формы понимает только один аргумент поэтому для него используется тот же самый поисковый отчет(копия) но без аргументов кроме id объекта.
Выглядит это так С одной стороны для поиска - мы вызываем действие и запускаем поисковый отчет с аргументами и ищем объекты по всей базе. С другой стороны мы встаем на корневой объект некоторой структуры и показываем 'на том же самом' отчете информацию по дочерним элементам
ps
на картинке немного съехали заголовки в этих отчетах - только щас заметил
далее- посмотрим на аргументы поискового отчета
Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный
- Александр
- Активный участник
- Сообщения: 1652
- Зарегистрирован: 24 авг 2006, 08:06
- Используемое ПО: Lotsia PDM PLUS
- Откуда: 55.745578,37.665825
Re: Поиск и Отображение 2in1
аргументы поискового отчета
представлены двумя блоками
Быстрый поиск (фильтрация при формировании отчета) - два поля Описание объекта и Строковый атрибут объекта
Медленный поиск (фильтрация после формирования объекта) - столько полей сколько нужно
Выглядит так имеет смысл посмотреть на два аргумента
1й - местоположение искомого фрагмента в аргументе описание объекта. Аргумент представлен строкой из двух символов для Like соответственно
'%%'-в любом месте строки
' %'-привязка к началу
'% '-привязка к концу
' '-строгое соответствие
и второе
аргумент с id строкового атрибута
этих двух полей оказалось вполне достаточно для быстрого поиска
второй блок фильтрует в найденном и представлен обычными фильтрами теперь посмотрим на пример вызова и сам код
представлены двумя блоками
Быстрый поиск (фильтрация при формировании отчета) - два поля Описание объекта и Строковый атрибут объекта
Медленный поиск (фильтрация после формирования объекта) - столько полей сколько нужно
Выглядит так имеет смысл посмотреть на два аргумента
1й - местоположение искомого фрагмента в аргументе описание объекта. Аргумент представлен строкой из двух символов для Like соответственно
'%%'-в любом месте строки
' %'-привязка к началу
'% '-привязка к концу
' '-строгое соответствие
и второе
аргумент с id строкового атрибута
этих двух полей оказалось вполне достаточно для быстрого поиска
второй блок фильтрует в найденном и представлен обычными фильтрами теперь посмотрим на пример вызова и сам код
Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный
- Александр
- Активный участник
- Сообщения: 1652
- Зарегистрирован: 24 авг 2006, 08:06
- Используемое ПО: Lotsia PDM PLUS
- Откуда: 55.745578,37.665825
Re: Поиск и Отображение 2in1
вызов поискового отчета
код функции
функция принимает все параметры и просто добавляет нужные аргументы к вызываемой функции
которая содержит id объекта, описание, дату создания ... в общем служебную информацию без атрибутов
посмотрим на эту функцию lsdbo.Ric_Get_Selectкак видно она просто в свою очередь вызывает либо функцию поиска либо функцию отбора дочерних объектов - передавая в них уже только нужные параметры из общей кучи
посмотрим на эти функции
вызов отчета для формы как видно - вызывается одна и та-же функция включающая в себя все параметры для работы в этих двух направлениях + флаг указывающий какое действие нужно произвести - найти объекты или выбрать дочерниекод функции
Код: Выделить всё
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
посмотрим на эту функцию 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
посмотрим на эти функции
Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный
- Александр
- Активный участник
- Сообщения: 1652
- Зарегистрирован: 24 авг 2006, 08:06
- Используемое ПО: Lotsia PDM PLUS
- Откуда: 55.745578,37.665825
Re: Поиск и Отображение 2in1
функция поиска объектов в базе
чистый поиск объектов по И
чтобы соблюсти некоторую 'приличность' мы все же оставили проверку прав на описание объекта
функция возвращает исключительно служебную информацию (без атрибутов) которая затем по id объекта обрастает атрибутами как было показано выше
посмотрим на функцию отбора дочерних объектов - которая возвращает абсолютно тот же набор колонок
Код: Выделить всё
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 объекта обрастает атрибутами как было показано выше
посмотрим на функцию отбора дочерних объектов - которая возвращает абсолютно тот же набор колонок
Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный
- Александр
- Активный участник
- Сообщения: 1652
- Зарегистрирован: 24 авг 2006, 08:06
- Используемое ПО: Lotsia PDM PLUS
- Откуда: 55.745578,37.665825
Re: Поиск и Отображение 2in1
функция по дочерним объектам
интересен параметр @Mnemo
рекурсия идет по Мнемо объектов делая фильтрацию по последнему из указанных Мнемо в параметре @Mnemo
сам параметр принимает строку (длина до 4000 символов) в которой через запятую перечислены Мнемо которые мы ищем
строка в свою очередь в начале функции преобразуется в таблицу для использования ее в секции in
что здесь интересно - через запятую мы можем указать уникальную цепочку Мнемо дочерних объектов по которым отправится рекурсия - т.е. отбор будет произведен не по всем дочерним объектам а только по объявленным - и в конце выйдут только те объекты Мнемо которых стоит последним как в строке так и соответственно в массиве который из этой строки получен
функция возвращает тот же набор колонок что и поисковая
Код: Выделить всё
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
сам параметр принимает строку (длина до 4000 символов) в которой через запятую перечислены Мнемо которые мы ищем
строка в свою очередь в начале функции преобразуется в таблицу для использования ее в секции in
что здесь интересно - через запятую мы можем указать уникальную цепочку Мнемо дочерних объектов по которым отправится рекурсия - т.е. отбор будет произведен не по всем дочерним объектам а только по объявленным - и в конце выйдут только те объекты Мнемо которых стоит последним как в строке так и соответственно в массиве который из этой строки получен
функция возвращает тот же набор колонок что и поисковая
Последний раз редактировалось Александр 05 мар 2010, 13:34, всего редактировалось 2 раза.
Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный
- Александр
- Активный участник
- Сообщения: 1652
- Зарегистрирован: 24 авг 2006, 08:06
- Используемое ПО: Lotsia PDM PLUS
- Откуда: 55.745578,37.665825
Re: Поиск и Отображение 2in1
в итоге мы имеем следующий алгоритм
1. в запрос забрасываются все условия
для поискадля дочерних объектов
2. в зависимости от того что нужно сделать внутренние функции возвращают только служебные колонки найденных/отобранных объектов
как видим ничего лишнего
3. основная вызываемая из отчета функция просто добавляет к полученным id нужные атрибуты
4. вся информация выводится на 'одном' отчете
вообще все довольно круто и быстро
мы используем такой подход для ряда поисковых и дочерних объектов, а также для выпадающих списков в Party и Workflow и т.д.
повторное использование кода получилось - очень неплохим - практически те или иные функции (нижнего уровня) вызываются постоянно и в ряде случаев к ним добавляются атрибуты а в большинстве случаев достаточно и чисто служебных колонок
вот только с отчетами... если бы отчет для форм понимал больше одного аргумента - мы использовали бы один и тот же отчет и там и там - а не обрезанную копию как сейчас
в общем жизнь удалась - если есть вопросы - я готов поболтать на любые отвлеченные темы
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
Код: Выделить всё
(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
...
вообще все довольно круто и быстро
мы используем такой подход для ряда поисковых и дочерних объектов, а также для выпадающих списков в Party и Workflow и т.д.
повторное использование кода получилось - очень неплохим - практически те или иные функции (нижнего уровня) вызываются постоянно и в ряде случаев к ним добавляются атрибуты а в большинстве случаев достаточно и чисто служебных колонок
вот только с отчетами... если бы отчет для форм понимал больше одного аргумента - мы использовали бы один и тот же отчет и там и там - а не обрезанную копию как сейчас
в общем жизнь удалась - если есть вопросы - я готов поболтать на любые отвлеченные темы
Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный
- Александр
- Активный участник
- Сообщения: 1652
- Зарегистрирован: 24 авг 2006, 08:06
- Используемое ПО: Lotsia PDM PLUS
- Откуда: 55.745578,37.665825
Re: Поиск и Отображение 2in1
кстати может кому нужно
чтобы рекурсивная функция Ric_Get_objChild поехала в обратную сторону достаточно поменять местами link_id и parent_id типа так
чтобы рекурсивная функция 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
Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный