Архив форума A-Number CRM

 

Примеры SQL - скриптов

admin
avatar Администратор

Администратор
Постов: 2728
graph
 
Примеры SQL - скриптов - В этой теме будем делиться опытом в создании скриптов.

P.S. Перед тем как создавать собственные скрипты, обязательно ознакомьтесь с теорией.

http://a-number.com/index.php?option=com_content&task=view&id=134


Посмотрите еще тему : SQL метки, в этой же ветке, там есть несколько примеров по созданию скриптов для SQL-меток.

--СОВСЕМ КРАТКОЕ ВВЕДЕНИЕ

Сначала нечто общее по абсолютно всем скриптам:

большую часть нужно заносить запросами в блоб-поля, так что сначала формируем строку а затем вносим ее апдейтом.
Дело это несложное, но требует некоторой усидчивости и аккуратности.

declare @p nvarchar(4000) - объявляем строку с запросом, которую затем занесем базу

если запрос достаточно короткий (как оно обычно и бывает), то хватит 4000 символов в формате юникод

char(39) - это аналог одинарной кавычки

и допустим такой скрипт :

declare @p varchar(50)
set @p=char(39)
select @p

выдаст в качестве ответа один знак - верхнюю одинарную кавычку(')
 
admin
avatar Администратор

Администратор
Постов: 2728
graph
 
В ответ на: Примеры SQL - скриптов - И так начнем:

Скрипт который открывает доступ пользователей к Карточке, в режиме - Только для чтения. (Администратору остается полный доступ к редактированию)

Code:

  -- --администратор (пользователь номер 1всегда редактирует любую карточку --пример скрипта для блокирования карточек  --таблица со скриптом Contacts_SqlScriptстолбец Event(событие)должен содержать ключевое слово EventReadOnly --Значения на выходе: --запрос должен возвращать столбец с именем ReadOnly  --и значениями 1 если заблокироватьне блокировать, -если скрипт для данных условий следует игнорировать --Входные переменные: -- _ID_USER_ автоматически подставит номер текущего пользователя -- _NUMBER_CONTACTS_ автоматически подставит текущий контакт --ПРИМЕР СКРИПТАВНИМАНИЕ ВАЖНО ДЛЯ ДАННОГО ПРИМЕРА ПРЕДВАРИТЕЛЬНО СОЗДАЙТЕ ДОПОЛНИТЕЛЬНЫЙ СПРАВОЧНИК "ПОЛЬЗОВАТЕЛИ" (если его еще нет) --предложенный скрипт делает следующее: -- 1 шагвносит пустую строку для события в таблицу событий -- 2 шагформирует запрос который ищет дополнительный справочник "пользователи" и если находит  --        открывает редактирование карточки если текущий пользователь соответствует выбранному в карточке  --      закрывает карточку (режим "только для чтения"если пользователь другой --        ничего не делает (то есть как назначены права для групп так и будетесли пользователь не установлен       if (select count([id]) from  Contacts_SqlScript where Event='EventReadOnly')=0  begin     insert Contacts_SqlScript (Event)     values('EventReadOnly') end declare @n nvarchar(4000) declare @Field varchar(250) set @Field =(select top 1 Field from Field_Name where AdvancedNameGuide='tUser') set @Field=isnull(@Field,'')   set @n' declare @ReadOnly varchar(250), @CurUser varchar(250) '+char(13)+     ' set @ReadOnly= (select Value from MultiGuide where [id] in (select ['+@Field+'] from Contacts where id_contacts=_NUMBER_CONTACTS_)) 'char(13)+         ' set @ReadOnly=isnull(@ReadOnly,'+char(39)+char(39)+') 'char(13)+         ' set @CurUser=(select Name from tuser where id_user=_ID_USER_) 'char(13)+     ' if @ReadOnly=@CurUser or _ID_USER_=1 select 0 as ReadOnly '+char(13)+     ' if @ReadOnly<>@CurUser and @ReadOnly<>'+char(39)+char(39)+' and _ID_USER_<>1 select 1 as ReadOnly '+char(13)+     ' if @ReadOnly='+char(39)+char(39)+' select -1 as ReadOnly ' update Contacts_SqlScript set SQL_SCRIPT = @n from Contacts_SqlScript where Event='EventReadOnly'

 
admin
avatar Администратор

Администратор
Постов: 2728
graph
 
В ответ на: Примеры SQL - скриптов - Продолжаем:

Скрипт, который помещает значения из подветок дерева, в какой-нибудь справочник. То есть справочник меняется динамически при изменении значений в дереве контактов.


Code:

  --скрипт срабатывает при открытии окна проектов --ищет значения контактов в подветках одной из веток дерева (в данном случае в ветке номер 7) --и заносит найденные контакты в справочник программы (в данном случае поле с наименованием организации),  --далее удаляет те контактыкоторые удалены из этих веток но еще присутствуют в справочнике  -- в качестве констант используются  -- Edit1179 внутреннее наименование поля со справочником (из таблицы MultiGuide) -- Edit32 внутреннее наименование поля с которым ведется сравнение (взято из таблицы Contaсts)   -- номер ветки с подветками (взято из таблицы tree)  declare @p nvarchar(4000) set @p= ' declare @RusName varchar(250)'+char(13)+ ' set @RusName=(select top 1 Field_Name from field_name where field='+char(39)+'Edit1179'+char(39)+')'+char(13)+ ' insert MultiGuide (FieldName, Value, TypeName, id_parent, PageSpravName)'+char(13)+ ' select '+char(39)+'Edit1179'+char(39)+', Edit32, '+char(39)+'varchar'+char(39)+', 0, @RusName'+char(13)+ ' from contacts where id_contacts in'+char(13)+ ' (select id_contacts from linkstreecontacts where id_tree in (select id_tree from tree where  id_parent=7))'+char(13)+ ' and Edit32 not in (select Value from MultiGuide where FieldName='+char(39)+'Edit1179'+char(39)+' and isnull(FlagDelete,0)=0 ) '+char(13)+ 'update MultiGuide '+char(13)+ 'set FlagDelete=1 '+char(13)+ 'from  MultiGuide '+char(13)+ 'where [id]  in ('+char(13)+ 'select [id] from MultiGuide where FieldName='+char(39)+'Edit1179'+char(39)+' and isnull(FlagDelete,0)=0 and Value Not in '+char(13)+ '(select Edit32  from contacts k,linkstreecontacts L  where k.id_contacts=L.id_contacts and  L.id_tree in (select id_tree from tree where id_parent=7))) ' update projects_event set SQL_SCRIPT=@p from projects_event  where Event='OpenProjects'

 
admin
avatar Администратор

Администратор
Постов: 2728
graph
 
В ответ на: Примеры SQL - скриптов - Скрипт для проверки корректности ввода дат в том случае если у Вас есть два поля с типом "Дата" и надо просто их сравнить. (Чтобы дата начала не была больше даты окончания какого-то события и наоборот дата окончания не была меньше даты начала)

Code:

  --скриптовдвано они аналогичны и привязаны к двум столбцам в закладке дата начала и дата окончания --в случае несоответствия условиявыдается предупреждение о некорректно введенной датеи изменения не вносятся. --столбцы у них обязательно должны быть с типом "Дата" указанное в примере событие именно для них) --_ID_PAGE_NUMBER_ вместо этой константы автоматически подставится текущий номер строки -- вот это -> ValueGuideDatePageForCont1_3 наименование событияномер столбца,  --первая часть PageForCont1 наименование закладкиValueGuideDate событие связано с изменение даты declare @Res nvarchar(4000) set @Res= ' declare @DateBegin datetime, @DateEnd datetime, @S1 varchar(250), @S2 varchar(250) '+ ' select @DateBegin=[2], @DateEnd=_DATE1_ from PageForCont1 where [id]=_ID_PAGE_NUMBER_ '+ ' if @DateBegin>@DateEnd '+ ' begin '+ '  set  @S1='+char(39)+'1'+char(39)+' set @S2='+char(39)+'Начало этапа не может быть позже окончания.'+char(39)+ ' end else '+ ' begin '+ '  set  @S1='+char(39)+'0'+char(39)+' set @S2='+char(39)+char(39)+ ' end '+ ' select @S1, @S2 ' delete from Contacts_SqlScript where Event='ValueGuideDatePageForCont1_3' insert Contacts_SqlScript (EventSql_Script) values ('ValueGuideDatePageForCont1_3', @Res) set @Res= ' declare @DateBegin datetime, @DateEnd datetime, @S1 varchar(250), @S2 varchar(250) '+ ' select @DateBegin=_DATE1_,@DateEnd=[3] from PageForCont1 where [id]=_ID_PAGE_NUMBER_ '+ ' if @DateBegin>@DateEnd '+ ' begin '+ '  set  @S1='+char(39)+'1'+char(39)+' set @S2='+char(39)+'Начало этапа не может быть позже окончания.'+char(39)+ ' end else '+ ' begin '+ '  set  @S1='+char(39)+'0'+char(39)+' set @S2='+char(39)+char(39)+ ' end '+ ' select @S1, @S2 ' delete from Contacts_SqlScript where Event='ValueGuideDatePageForCont1_2' insert Contacts_SqlScript (EventSql_Script) values ('ValueGuideDatePageForCont1_2', @Res)

 
admin
avatar Администратор

Администратор
Постов: 2728
graph
 
В ответ на: Примеры SQL - скриптов - Скрипт для динамического изменения изображения в отчете "HTML-карточка" в зависимости от выбранного контакта.

Code:

  --простой скриптсрабатывает в отчете "HTML-карточка" --в зависимости от контакта меняет верхнюю картинку в HTML-карточке (берет из закладкигде они хранятся)   -- особенность данного события автоматически подставляет в конец скрипка номер контакта,  --поэтому запрос должен оканчиваться конструкцией вида where id_contacts=,  --после "равно" программа сама подставит номер контакта declare @SQL_SCRIPT nvarchar(4000) set @SQL_SCRIPT='select [8] as BigImage from PageForCont3 where id_contacts=' delete from Contacts_SqlScript where Event='ReloadHeader' insert Contacts_SqlScript (SQL_SCRIPTEvent)         values (@SQL_SCRIPT,'ReloadHeader')

 
admin
avatar Администратор

Администратор
Постов: 2728
graph
 
В ответ на: Примеры SQL - скриптов - Пишем запрос для SQL-label

Code:

  --простой но важный пример написания запроса для SQL-label --на карточку бросается SQL-label в который вносим запрос --в конце программа сама добавит номер контакта и знак равно (это уже после сохранения),  --вместо :@id_contacts так же будет вставлен номер контакта -- в данном случае указывается есть или нет абонемент у читателя в зависимости от номера контакта --особенностьпрограмма добавит номер контакта и знак равно в любом случае,  --так что окончание запроса в виде "where id_contacts" строго обязательно declare @KOL int set @KOL=(select count(*) as KOL from PageForCont5 where          [2]<getdate() and datepart(year, [2])>1950 and ([4]>=getdate() or datepart(year, [4])<1950)          and id_contacts=:@id_contacts) if @KOL>0 select 'Абонемент: ДА' else select 'Абонемент: НЕТ' from contacts where id_contacts

 
admin
avatar Администратор

Администратор
Постов: 2728
graph
 
В ответ на: Примеры SQL - скриптов - Скрипт, который не даст закрыть Карточку при несоблюдении определенных условий, в данном примере если не изменена дата звонка.

Code:

  --важный пример который может пригодиться многим --не дает закрыть карточку при несоблюдении определенных условий,  --в данном случае при отказе выдает предупреждение "Внесите новую дату звонка"  --_NUMBER_CONTACTS_ константа (номер текущего контакта), подставляется автоматически declare @n nvarchar(4000) set @' declare @Date datetime, @id int, @id_role int '+char(13)+ ' if isnull((select max(id_role) from tUserRole where id_user=_ID_USER_ and id_role=1),0)>0 set  @id_role=1 else set @id_role=0 '+char(13)+ ' set @Date=(select DateEdit3 from contacts where id_contacts=_NUMBER_CONTACTS_) '+char(13)+ ' if @Date>=getdate() select '+char(39)+'0'+char(39)+', '+char(39)+char(39)+','+char(39)+'1'+char(39)+char(13)+ ' else begin'+char(13)+ ' set @id=isnull((select max([id]) from historycontacts where Date>@Date and  id_contacts=_NUMBER_CONTACTS_),0) '+char(13)+ ' if @id=0 and @id_role=0 select '+char(39)+'1'+char(39)+','+char(39)+'Внесите новую дату звонка.'+char(39)+','+char(39)+'0'+char(39)+char(13)+ ' else '+ ' select '+char(39)+'0'+char(39)+', '+char(39)+char(39)+','+char(39)+'1'+char(39)+char(13)+ ' end' print @n delete from Contacts_SqlScript where Event='FormCloseQuery'  insert Contacts_SqlScript (EventSQL_SCRIPT) values ('FormCloseQuery', @n)

 
admin
avatar Администратор

Администратор
Постов: 2728
graph
 
В ответ на: Примеры SQL - скриптов - Хорошему человеку нужен был вот такой отчет, может кому-то будет интересен !!

Отчет который выводит список контактов за период по условиям :
-если в течении выбранного периода дата введенной истории старше предыдущей даты истории на определенное количество дней

-если этот контакт новый и история введена впервые

Создаем новый отчет, переключаемся на - Использовать язык запросов (SQL)

В верхнее окно, помещаем код
Code:

  declare @id_user int set @Delta=10 set @id_user = (select [id_userfrom tUser where [Name]=@Name) if exists (select from tempdb..sysobjects where id object_id(N'[tempdb]..[#PromHcRes]')) drop table #PromHcRes create table #PromHcRes ([id] integer primary key identity(1,1),id_contacts1 int,id_contacts2 int,                          ID1 int,ID2 int,DT1 datetime,DT2 datetime,COUNTER1 intCOUNTER2 int) if exists (select from tempdb..sysobjects where id object_id(N'[tempdb]..[#PromHC]')) drop table  #PromHC create table #PromHC ([id] integer primary key identity(1,1), [Date] datetime, id_contacts int) insert #PromHC ([Date], id_contacts) select [Date], id_contacts from historycontacts where [date]>=@DateBegin and [date]<=@DateEnd and id_user=@id_user   order by id_contacts asc, [datedesc insert #PromHcRes (id_contacts1,id_contacts2,ID1,ID2,DT1,DT2,COUNTER1,COUNTER2) SELECT t1.id_contactst2.id_contacts,    t1.[id] as ID1t2.id as ID2,  t1.[Date], t2.[date] as DT2,   CASE WHEN t1.IdPrev IS NULL THEN  -ELSE DATEDIFF (ddt2.[Date],t1.[Date])  END AS  COUNTER1,    CASE WHEN t1.IdPrev IS NULL THEN  -ELSE DATEDIFF (dd,t2.[Date],t1.[Date])END AS  COUNTER2 FROM  SELECT    *,         (SELECT TOP 1 [idFROM #PromHC t2 WHERE t2.[id] = t1.[id]+1 ORDER BY t2.[date] asc) as IDPrev     FROM #PromHC T1  ) as T1    LEFT OUTER JOIN #PromHC t2   ON t1.IDPrev t2.[ID] and t1.id_contacts=t2.id_contacts ORDER by t1.[datedesc if exists (select from tempdb..sysobjects where id object_id(N'[tempdb]..[#PromHcITOG]')) drop table #PromHcITOG create table #PromHcITOG ([id] integer primary key identity(1,1),                            id_contacts1 intDT1 datetime,DT2 datetimeCounter1 int) insert #PromHcITOG (id_contacts1, DT1,DT2, Counter1) select id_contacts1DT1,DT2Counter1 from #PromHcRes where Counter1>=@Delta and id_contacts2 is not Null    insert #PromHcITOG (id_contacts1, DT1,DT2, Counter1) select id_contacts1DT1,DT2Counter1 from #PromHcRes where Counter1>=@Delta and id_contacts2 is not Null    insert #PromHcITOG (id_contacts1, DT1,DT2, Counter1)  select id_contacts1DT1,DT2Counter1 from  #PromHcRes  where id_contacts1 in  (select id_contacts1 from #PromHcRes  group by id_contacts1 having count(id_contacts1)=1) select id_contacts1 as '№'DT1 as 'Дата 1'DT2 as 'Дата 2'isnull(cast(Counter1 as varchar(250)),'Новая') as 'Дельта'   from #PromHcITOG



В нижнем вносим параметры.

Параметры

@Name
@DateBegin
@DateEnd
@Delta

Наименование параметров

Пользователь
Дата начала
Дата окончания
Период

Тип параметров

строка
дата
дата
целое
 
admin
avatar Администратор

Администратор
Постов: 2728
graph
 
В ответ на: Примеры SQL - скриптов - Картинки к отчету.




 
© 2005 - 2015 A-Number Software. Все права защищены                          Написать письмо