· 3 years ago · Sep 08, 2022, 09:30 AM
1----------------TABLE chatSearch--------------
2print N'Создание/обновление структуры DB TABLE as_crud_tables-------------------- '
3IF NOT EXISTS (SELECT * FROM sysobjects WHERE name= 'as_crud_tables' and xtype='U') BEGIN
4 CREATE TABLE [dbo].[as_crud_tables]
5 (
6 [id] [int] IDENTITY(1,1) NOT NULL
7 CONSTRAINT [PK_as_crud_tables] PRIMARY KEY CLUSTERED ( [id] ASC )
8 ) ON [PRIMARY]
9 print 'Создана таблица as_crud_tables '
10END
11GO
12IF COL_LENGTH( 'as_crud_tables', 'title') IS NULL BEGIN
13 ALTER TABLE as_crud_tables
14 ADD [title] nvarchar (256) NULL
15END
16IF COL_LENGTH( 'as_crud_tables', 'titleTooltip') IS NULL BEGIN
17 ALTER TABLE as_crud_tables
18 ADD [titleTooltip] nvarchar (1024) NULL
19END
20IF COL_LENGTH( 'as_crud_tables', 'code') IS NULL BEGIN
21 ALTER TABLE as_crud_tables
22 ADD [code] nvarchar (32) NULL
23END
24IF COL_LENGTH( 'as_crud_tables', 'addEditLink') IS NULL BEGIN
25 ALTER TABLE as_crud_tables
26 ADD [addEditLink] nvarchar (128) NULL
27END
28IF COL_LENGTH( 'as_crud_tables', 'commentsCode') IS NULL BEGIN
29 ALTER TABLE as_crud_tables
30 ADD [commentsCode] nvarchar (128) NULL
31END
32IF COL_LENGTH( 'as_crud_tables', 'immediatelyLoad') IS NULL BEGIN
33 ALTER TABLE as_crud_tables
34 ADD [immediatelyLoad] bit NULL
35END
36IF COL_LENGTH( 'as_crud_tables', 'emptyText') IS NULL BEGIN
37 ALTER TABLE as_crud_tables
38 ADD [emptyText] nvarchar (128) NULL
39END
40IF COL_LENGTH( 'as_crud_tables', 'filterLinkTitle') IS NULL BEGIN
41 ALTER TABLE as_crud_tables
42 ADD [filterLinkTitle] nvarchar (128) NULL
43END
44IF COL_LENGTH( 'as_crud_tables', 'showChecksCol') IS NULL BEGIN
45 ALTER TABLE as_crud_tables
46 ADD [showChecksCol] bit NULL
47END
48IF COL_LENGTH( 'as_crud_tables', 'showNumsCol') IS NULL BEGIN
49 ALTER TABLE as_crud_tables
50 ADD [showNumsCol] bit NULL
51END
52IF COL_LENGTH( 'as_crud_tables', 'showToolbar') IS NULL BEGIN
53 ALTER TABLE as_crud_tables
54 ADD [showToolbar] bit NULL
55END
56IF COL_LENGTH( 'as_crud_tables', 'getItemsURLParameters') IS NULL BEGIN
57 ALTER TABLE as_crud_tables
58 ADD [getItemsURLParameters] nvarchar (128) NULL
59END
60IF COL_LENGTH( 'as_crud_tables', 'toolbarAdditional') IS NULL BEGIN
61 ALTER TABLE as_crud_tables
62 ADD [toolbarAdditional] nvarchar (max) NULL
63END
64IF COL_LENGTH( 'as_crud_tables', 'groupOperationsToolbar') IS NULL BEGIN
65 ALTER TABLE as_crud_tables
66 ADD [groupOperationsToolbar] nvarchar (max) NULL
67END
68IF COL_LENGTH( 'as_crud_tables', 'getFilterMakeupCallback') IS NULL BEGIN
69 ALTER TABLE as_crud_tables
70 ADD [getFilterMakeupCallback] nvarchar (max) NULL
71END
72IF COL_LENGTH( 'as_crud_tables', 'filterCallback') IS NULL BEGIN
73 ALTER TABLE as_crud_tables
74 ADD [filterCallback] nvarchar (2048) NULL
75END
76IF COL_LENGTH( 'as_crud_tables', 'processRowCallback') IS NULL BEGIN
77 ALTER TABLE as_crud_tables
78 ADD [processRowCallback] nvarchar (2048) NULL
79END
80IF COL_LENGTH( 'as_crud_tables', 'getItemsCallback') IS NULL BEGIN
81 ALTER TABLE as_crud_tables
82 ADD [getItemsCallback] nvarchar (2048) NULL
83END
84IF COL_LENGTH( 'as_crud_tables', 'remove') IS NULL BEGIN
85 ALTER TABLE as_crud_tables
86 ADD [remove] bit NULL
87END
88IF COL_LENGTH( 'as_crud_tables', 'comments') IS NULL BEGIN
89 ALTER TABLE as_crud_tables
90 ADD [comments] bit NULL
91END
92IF COL_LENGTH( 'as_crud_tables', 'ctrlClickShowComment') IS NULL BEGIN
93 ALTER TABLE as_crud_tables
94 ADD [ctrlClickShowComment] bit NULL
95END
96IF COL_LENGTH( 'as_crud_tables', 'users') IS NULL BEGIN
97 ALTER TABLE as_crud_tables
98 ADD [users] nvarchar (128) NULL
99END
100IF COL_LENGTH( 'as_crud_tables', 'roles') IS NULL BEGIN
101 ALTER TABLE as_crud_tables
102 ADD [roles] nvarchar (128) NULL
103END
104IF COL_LENGTH( 'as_crud_tables', 'fastCreate') IS NULL BEGIN
105 ALTER TABLE as_crud_tables
106 ADD [fastCreate] bit NULL
107END
108IF COL_LENGTH( 'as_crud_tables', 'pageSize') IS NULL BEGIN
109 ALTER TABLE as_crud_tables
110 ADD [pageSize] int NULL
111END
112IF COL_LENGTH( 'as_crud_tables', 'requestGetItems') IS NULL BEGIN
113 ALTER TABLE as_crud_tables
114 ADD [requestGetItems] nvarchar (512) NULL
115END
116
117-- Внешние ключи таблицы as_crud_tables
118-- ВАЖНО Параметры ключа delete / update ОБРАБАТЫВАЮТСЯ
119
120go
121print N'Конец функции DBTABLE as_crud_tables-------------------- '
122
123-- Конец функции DB Table as_crud_tables--------------------
124
125GO
126
127GO
128declare @tableID int, @tableDatatypeID int, @editableTypeID int, @filterTypeID int, @operationTypeID int; set @tableID = null;
129print N'НАЧАЛО TABLE chatSearch---------------------------------------------------------------------------------------------------- '
130select top 1 @tableID = t.id from as_crud_tables as t where t.code = 'chatSearch' order by t.id;
131begin try
132 if @tableID > 0 begin
133 update as_crud_tables
134 set [title] = 'Поиск по чату'
135 ,[titleTooltip] = ''
136 ,[code] = 'chatSearch'
137 ,[addEditLink] = ''
138 ,[commentsCode] = ''
139 ,[immediatelyLoad] = 1
140 ,[emptyText] = 'Нет элементов'
141 ,[filterLinkTitle] = ''
142 ,[showChecksCol] = 0
143 ,[showNumsCol] = 1
144 ,[showToolbar] = 1
145 ,[getItemsURLParameters] = ''
146 ,[toolbarAdditional] = ''
147 ,[groupOperationsToolbar] = ''
148 ,[getFilterMakeupCallback] = ''
149 ,[filterCallback] = ''
150 ,[processRowCallback] = ''
151 ,[getItemsCallback] = ''
152 ,[remove] = 0
153 ,[comments] = 0
154 ,[ctrlClickShowComment] = 0
155 ,[users] = ''
156 ,[roles] = '*'
157 ,[fastCreate] = 0
158 ,[pageSize] = 100
159 ,[requestGetItems] = ''
160 where id = @tableID;
161 print N'Таблица chatSearch обновлена'
162 end else begin
163 insert into as_crud_tables ([title],[titleTooltip],[code],[addEditLink],[commentsCode],[immediatelyLoad],[emptyText]
164 ,[filterLinkTitle],[showChecksCol],[showNumsCol],[showToolbar],[getItemsURLParameters],[toolbarAdditional]
165 ,[groupOperationsToolbar],[getFilterMakeupCallback],[filterCallback],[processRowCallback],[getItemsCallback],[remove]
166 ,[comments],[ctrlClickShowComment],[users],[roles],[fastCreate],[pageSize], [requestGetItems])
167 VALUES ( 'Поиск по чату' , ''
168 , 'chatSearch'
169 , ''
170 , ''
171 , 1
172 , 'Нет элементов'
173 , ''
174 , 0
175 , 1
176 , 1
177 , ''
178 , ''
179 , ''
180 , ''
181 , ''
182 , ''
183 , ''
184 , 0
185 , 0
186 , 0
187 , ''
188 , '*'
189 , 0
190 , 100
191 , ''
192 );
193 select @tableID = scope_identity();
194 print N'Таблица chatSearch добавлена'
195 end;
196end try
197begin catch
198 print N' ERROR ############################################## При обновлении\добавлении таблицы chatSearch возникла ошибка: ' + error_message()
199end catch
200
201begin try
202 delete from as_crud_cols where as_crud_cols.tableID = @tableID;
203 print N'Колонки таблицы chatSearch удалены'
204end try
205begin catch
206 PRINT N'ERROR ############################################## При удалении колонок таблицы chatSearch возникла ошибка: ' + error_message()
207end catch
208
209begin try
210 set @tableDatatypeID = null; set @editableTypeID = null; set @filterTypeID = null;
211 select @tableDatatypeID = d.id from as_crud_dataTypes as d where d.code = 'string';
212 select @editableTypeID = e.id from as_crud_editableTypes as e where e.code = null;
213 select @filterTypeID = f.id from as_crud_filterTypes as f where f.code = null;
214 insert into as_crud_cols ([tableID],[title],[tooltip],[ord],[datatypeID],[isSort],[isVisible]
215 ,[isPK],[editableTypeID],[editableCallback],[filterTypeID],[code],[filterNotSelected],[format]
216 ,[editableMin],[editableMax],[editableStep],[sqlGetListValues],[width])
217 VALUES ( @tableID, 'Дата'
218 , ''
219 , 0
220 , @tableDatatypeID
221 , 0
222 , 1
223 , 0
224 , @editableTypeID
225 , ''
226 , @filterTypeID
227 , 'date'
228 , ''
229 , ''
230 , null
231 , null
232 , null
233 , ''
234 , null
235 );
236 print N'Колонка date таблицы добавлена'
237end try
238begin catch
239 print N'ERROR ############################################## При добалении колонки date таблицы возникла ошибка: ' + error_message()
240end catch
241
242begin try
243 set @tableDatatypeID = null; set @editableTypeID = null; set @filterTypeID = null;
244 select @tableDatatypeID = d.id from as_crud_dataTypes as d where d.code = 'string';
245 select @editableTypeID = e.id from as_crud_editableTypes as e where e.code = null;
246 select @filterTypeID = f.id from as_crud_filterTypes as f where f.code = null;
247 insert into as_crud_cols ([tableID],[title],[tooltip],[ord],[datatypeID],[isSort],[isVisible]
248 ,[isPK],[editableTypeID],[editableCallback],[filterTypeID],[code],[filterNotSelected],[format]
249 ,[editableMin],[editableMax],[editableStep],[sqlGetListValues],[width])
250 VALUES ( @tableID, 'Отправитель'
251 , ''
252 , 0
253 , @tableDatatypeID
254 , 0
255 , 1
256 , 0
257 , @editableTypeID
258 , ''
259 , @filterTypeID
260 , 'author'
261 , ''
262 , ''
263 , null
264 , null
265 , null
266 , ''
267 , null
268 );
269 print N'Колонка author таблицы добавлена'
270end try
271begin catch
272 print N'ERROR ############################################## При добалении колонки author таблицы возникла ошибка: ' + error_message()
273end catch
274
275begin try
276 set @tableDatatypeID = null; set @editableTypeID = null; set @filterTypeID = null;
277 select @tableDatatypeID = d.id from as_crud_dataTypes as d where d.code = 'string';
278 select @editableTypeID = e.id from as_crud_editableTypes as e where e.code = null;
279 select @filterTypeID = f.id from as_crud_filterTypes as f where f.code = 'string';
280 insert into as_crud_cols ([tableID],[title],[tooltip],[ord],[datatypeID],[isSort],[isVisible]
281 ,[isPK],[editableTypeID],[editableCallback],[filterTypeID],[code],[filterNotSelected],[format]
282 ,[editableMin],[editableMax],[editableStep],[sqlGetListValues],[width])
283 VALUES ( @tableID, 'Сообщение'
284 , ''
285 , 0
286 , @tableDatatypeID
287 , 0
288 , 1
289 , 0
290 , @editableTypeID
291 , ''
292 , @filterTypeID
293 , 'text'
294 , 'Найти...'
295 , ''
296 , null
297 , null
298 , null
299 , ''
300 , null
301 );
302 print N'Колонка text таблицы добавлена'
303end try
304begin catch
305 print N'ERROR ############################################## При добалении колонки text таблицы возникла ошибка: ' + error_message()
306end catch
307
308begin try
309 set @tableDatatypeID = null; set @editableTypeID = null; set @filterTypeID = null;
310 select @tableDatatypeID = d.id from as_crud_dataTypes as d where d.code = 'string';
311 select @editableTypeID = e.id from as_crud_editableTypes as e where e.code = null;
312 select @filterTypeID = f.id from as_crud_filterTypes as f where f.code = null;
313 insert into as_crud_cols ([tableID],[title],[tooltip],[ord],[datatypeID],[isSort],[isVisible]
314 ,[isPK],[editableTypeID],[editableCallback],[filterTypeID],[code],[filterNotSelected],[format]
315 ,[editableMin],[editableMax],[editableStep],[sqlGetListValues],[width])
316 VALUES ( @tableID, 'id'
317 , ''
318 , 0
319 , @tableDatatypeID
320 , 0
321 , 0
322 , 1
323 , @editableTypeID
324 , ''
325 , @filterTypeID
326 , 'id'
327 , ''
328 , ''
329 , null
330 , null
331 , null
332 , ''
333 , null
334 );
335 print N'Колонка id таблицы добавлена'
336end try
337begin catch
338 print N'ERROR ############################################## При добалении колонки id таблицы возникла ошибка: ' + error_message()
339end catch
340
341begin try
342 set @tableDatatypeID = null; set @editableTypeID = null; set @filterTypeID = null;
343 select @tableDatatypeID = d.id from as_crud_dataTypes as d where d.code = 'string';
344 select @editableTypeID = e.id from as_crud_editableTypes as e where e.code = null;
345 select @filterTypeID = f.id from as_crud_filterTypes as f where f.code = null;
346 insert into as_crud_cols ([tableID],[title],[tooltip],[ord],[datatypeID],[isSort],[isVisible]
347 ,[isPK],[editableTypeID],[editableCallback],[filterTypeID],[code],[filterNotSelected],[format]
348 ,[editableMin],[editableMax],[editableStep],[sqlGetListValues],[width])
349 VALUES ( @tableID, 'Чат'
350 , ''
351 , 0
352 , @tableDatatypeID
353 , 0
354 , 1
355 , 0
356 , @editableTypeID
357 , ''
358 , @filterTypeID
359 , 'room'
360 , ''
361 , ''
362 , null
363 , null
364 , null
365 , ''
366 , null
367 );
368 print N'Колонка room таблицы добавлена'
369end try
370begin catch
371 print N'ERROR ############################################## При добалении колонки room таблицы возникла ошибка: ' + error_message()
372end catch
373
374begin try
375 delete from as_crud_tableOperations where tableID = @tableID;
376 print N'Операции таблицы chatSearch удалены'
377end try
378begin catch
379 PRINT N'ERROR ############################################## При удалении операций таблицы chatSearch возникла ошибка: ' + error_message()
380end catch
381
382go
383
384
385print N'НАЧАЛО PROCS crud_chatSearch[_]-------------------- '
386print N'Попытка удаления хранимых процедур'
387declare @name nvarchar(256), @sqlExpec nvarchar(max);
388declare cur CURSOR LOCAL for select o.name from sys.objects as o where o.name like 'crud_chatSearch[_]%' AND type in (N'P', N'PC')
389open cur fetch next from cur into @name
390while @@FETCH_STATUS = 0 BEGIN
391 begin try
392 set @sqlExpec = 'drop procedure [' + @name+']'
393 exec sp_executesql @sqlExpec
394 print N'Удалена хранимая процедура ' + @name
395 end try
396 begin catch
397 print N'ERROR ############################################## Ошибка при удалени хранимой процедуры ' + @name + ': '+ error_message()
398 end catch
399 fetch next from cur into @name
400END
401close cur deallocate cur
402
403begin try
404 exec sp_executesql N'CREATE PROCEDURE [dbo].[crud_chatSearch_getItems]
405 @filters CRUDFilterParameter READONLY,
406 @sort sql_variant,
407 @direction nvarchar(8),
408 @page int,
409 @pageSize int,
410 @username nvarchar(32)
411AS
412BEGIN
413 declare @ids TABLE (id int)
414
415 -- filters...
416 declare @search nvarchar(128) = isnull((select Value from @filters where [Key] = ''text''), '''')
417
418 declare @userID int = (select top 1 id from as_users where username = @username)
419 declare @roomID int = isnull((select top 1 try_cast(value as int) from @filters where [key]=''itemID''), 0)
420 insert into @ids
421 select id
422 from as_msg_messages
423 where
424 (@roomID =0 and roomID in (select roomID from as_msg_roomUsers where userID = @userID)
425 or roomID = @roomID
426 )
427 and @search<>''''
428 and (isnull(@search, '''')='''' or text like ''%''+@search+''%'')
429
430 -- SELECT 1
431 select isnull(m.[id], 0) [id],
432 isnull(try_convert(nvarchar(16), m.[created], 120), '''') [date],
433 dbo.as_timeDelay(datediff(minute, m.created, getdate())) + '' назад'' desc_date,
434 isnull((select username from as_users where id = m.userID), '''') [author],
435 isnull(replace(m.[text], @search, ''<span class="bg-warning">''+ @search+ ''</span>''), '''') [text],
436 iif(@roomID=0, ''<a href="/messages/''+iif(lower(type.code)=''userchat'', (select top 1 username from as_msg_roomUsers ru
437 inner join as_users u on ru.userID = u.id
438 where roomID = room.id and userID <>@userID
439 ), type.code+''__''+cast(room.itemID as nvarchar))+''">''+[dbo].[msg_getRoomItemName](room.id, type.code, room.itemID, @userID)+''</a>'', NULL) room
440
441 from as_msg_messages m
442 inner join as_msg_rooms room on m.roomID = room.id
443 inner join as_msg_types type on type.id = room.typeID
444 where m.id in (select id from @ids)
445 order by id desc
446 OFFSET @PageSize * (@Page - 1) ROWS
447 FETCH NEXT @PageSize ROWS ONLY;
448
449 -- SELECT 2
450 select count(*) from @ids
451
452 -- SELECT 3
453 select 1 Compact, ''14px'' fontSize, 1 HideTitleCount, ''h3'' headerTag,
454 iif(@search='''', ''Что будем искать?'', ''Не найдено сообщений'') EmptyText
455end
456
457--ru 08.09.2022 11:31:36
458--ru 08.09.2022 11:39:33
459--ru 08.09.2022 12:15:03
460'
461 print N'Добавлена хранимая процедура crud_chatSearch_getItems'
462end try
463begin catch
464 print N'ERROR ############################################## Ошибка при добавлении хранимой процедуры crud_chatSearch_getItems: '+ error_message()
465end catch
466
467
468go
469
470print N'КОНЕЦ PROCS crud_chatSearch[_]-------------------- '
471
472
473--КОНЕЦ PROCS crud_chatSearch[_]--------------------
474go
475print N'КОНЕЦ TABLE chatSearch---------------------------------------------------------------------------------------------------- '
476
477
478--КОНЕЦ TABLE chatSearch----------------------------------------------------------------------------------------------------
479