chatSearch table

----------------TABLE chatSearch--------------
print N'Создание/обновление структуры DB TABLE  as_crud_tables-------------------- '
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name= 'as_crud_tables' AND xtype='U') BEGIN
    CREATE TABLE [dbo].[as_crud_tables]
    (
     [id] [INT] IDENTITY(1,1) NOT NULL
     CONSTRAINT [PK_as_crud_tables] PRIMARY KEY CLUSTERED ( [id] ASC )
    ) ON [PRIMARY]
    print 'Создана таблица as_crud_tables '
END
GO
IF COL_LENGTH( 'as_crud_tables', 'title') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [title] nvarchar (256) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'titleTooltip') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [titleTooltip] nvarchar (1024) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'code') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [code] nvarchar (32) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'addEditLink') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [addEditLink] nvarchar (128) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'commentsCode') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [commentsCode] nvarchar (128) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'immediatelyLoad') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [immediatelyLoad] bit NULL
END
IF COL_LENGTH( 'as_crud_tables', 'emptyText') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [emptyText] nvarchar (128) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'filterLinkTitle') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [filterLinkTitle] nvarchar (128) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'showChecksCol') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [showChecksCol] bit NULL
END
IF COL_LENGTH( 'as_crud_tables', 'showNumsCol') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [showNumsCol] bit NULL
END
IF COL_LENGTH( 'as_crud_tables', 'showToolbar') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [showToolbar] bit NULL
END
IF COL_LENGTH( 'as_crud_tables', 'getItemsURLParameters') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [getItemsURLParameters] nvarchar (128) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'toolbarAdditional') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [toolbarAdditional] nvarchar (MAX) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'groupOperationsToolbar') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [groupOperationsToolbar] nvarchar (MAX) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'getFilterMakeupCallback') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [getFilterMakeupCallback] nvarchar (MAX) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'filterCallback') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [filterCallback] nvarchar (2048) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'processRowCallback') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [processRowCallback] nvarchar (2048) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'getItemsCallback') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [getItemsCallback] nvarchar (2048) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'remove') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [remove] bit NULL
END
IF COL_LENGTH( 'as_crud_tables', 'comments') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [comments] bit NULL
END
IF COL_LENGTH( 'as_crud_tables', 'ctrlClickShowComment') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [ctrlClickShowComment] bit NULL
END
IF COL_LENGTH( 'as_crud_tables', 'users') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [users] nvarchar (128) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'roles') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [roles] nvarchar (128) NULL
END
IF COL_LENGTH( 'as_crud_tables', 'fastCreate') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [fastCreate] bit NULL
END
IF COL_LENGTH( 'as_crud_tables', 'pageSize') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [pageSize] INT NULL
END
IF COL_LENGTH( 'as_crud_tables', 'requestGetItems') IS NULL BEGIN
    ALTER TABLE as_crud_tables
    ADD [requestGetItems] nvarchar (512) NULL
END

-- Внешние ключи таблицы as_crud_tables
-- ВАЖНО Параметры ключа delete / update ОБРАБАТЫВАЮТСЯ

GO
print N'Конец функции DBTABLE  as_crud_tables-------------------- '

-- Конец функции DB Table as_crud_tables--------------------

GO

GO
DECLARE @tableID INT, @tableDatatypeID INT, @editableTypeID INT, @filterTypeID INT, @operationTypeID INT; SET @tableID = NULL;
print  N'НАЧАЛО TABLE chatSearch---------------------------------------------------------------------------------------------------- '
SELECT top 1 @tableID = t.id FROM as_crud_tables AS t WHERE t.code = 'chatSearch' ORDER BY t.id;
BEGIN try
    IF @tableID > 0 BEGIN
        UPDATE as_crud_tables
        SET [title] = 'Поиск по чату'
            ,[titleTooltip] = ''
            ,[code] = 'chatSearch'
            ,[addEditLink] = ''
            ,[commentsCode] = ''
            ,[immediatelyLoad] = 1
            ,[emptyText] = 'Нет элементов'
            ,[filterLinkTitle] = ''
            ,[showChecksCol] = 0
            ,[showNumsCol] = 1
            ,[showToolbar] = 1
            ,[getItemsURLParameters] = ''
            ,[toolbarAdditional] = ''
            ,[groupOperationsToolbar] = ''
            ,[getFilterMakeupCallback] = ''
            ,[filterCallback] = ''
            ,[processRowCallback] = ''
            ,[getItemsCallback] = ''
            ,[remove] = 0
            ,[comments] = 0
            ,[ctrlClickShowComment] = 0
            ,[users] = ''
            ,[roles] = '*'
            ,[fastCreate] = 0
            ,[pageSize] = 100
            ,[requestGetItems] = ''
        WHERE id = @tableID;
        print N'Таблица chatSearch обновлена'
    END ELSE BEGIN
        INSERT INTO as_crud_tables ([title],[titleTooltip],[code],[addEditLink],[commentsCode],[immediatelyLoad],[emptyText]
            ,[filterLinkTitle],[showChecksCol],[showNumsCol],[showToolbar],[getItemsURLParameters],[toolbarAdditional]
            ,[groupOperationsToolbar],[getFilterMakeupCallback],[filterCallback],[processRowCallback],[getItemsCallback],[remove]
            ,[comments],[ctrlClickShowComment],[users],[roles],[fastCreate],[pageSize], [requestGetItems])
         VALUES ( 'Поиск по чату'            , ''
            , 'chatSearch'
            , ''
            , ''
            , 1
            , 'Нет элементов'
            , ''
            , 0
            , 1
            , 1
            , ''
            , ''
            , ''
            , ''
            , ''
            , ''
            , ''
            , 0
            , 0
            , 0
            , ''
            , '*'
            , 0
            , 100
            , ''
        );
        SELECT @tableID = scope_identity();
        print N'Таблица chatSearch добавлена'
    END;
END try
BEGIN catch
    print N' ERROR ############################################## При обновлении\добавлении таблицы chatSearch возникла ошибка: ' + error_message()
END catch

BEGIN try
    DELETE FROM as_crud_cols WHERE as_crud_cols.tableID = @tableID;
    print N'Колонки таблицы chatSearch удалены'
END try
BEGIN catch
    PRINT N'ERROR ############################################## При удалении колонок таблицы chatSearch возникла ошибка: ' + error_message()
END catch

BEGIN try
    SET @tableDatatypeID = NULL; SET @editableTypeID = NULL; SET @filterTypeID = NULL;
    SELECT @tableDatatypeID = d.id FROM as_crud_dataTypes AS d WHERE d.code = 'string';
    SELECT @editableTypeID = e.id FROM as_crud_editableTypes AS e WHERE e.code = NULL;
    SELECT @filterTypeID = f.id FROM as_crud_filterTypes AS f WHERE f.code = NULL;
     INSERT INTO as_crud_cols ([tableID],[title],[tooltip],[ord],[datatypeID],[isSort],[isVisible]
        ,[isPK],[editableTypeID],[editableCallback],[filterTypeID],[code],[filterNotSelected],[format]
        ,[editableMin],[editableMax],[editableStep],[sqlGetListValues],[width])
    VALUES ( @tableID, 'Дата'
        , ''
        , 0
        , @tableDatatypeID
        , 0
        , 1
        , 0
        , @editableTypeID
        , ''
        , @filterTypeID
        , 'date'
        , ''
        , ''
        , NULL
        , NULL
        , NULL
        , ''
        , NULL
    );
    print N'Колонка date таблицы добавлена'
END try
BEGIN catch
    print  N'ERROR ############################################## При добалении колонки date таблицы возникла ошибка: ' + error_message()
END catch

BEGIN try
    SET @tableDatatypeID = NULL; SET @editableTypeID = NULL; SET @filterTypeID = NULL;
    SELECT @tableDatatypeID = d.id FROM as_crud_dataTypes AS d WHERE d.code = 'string';
    SELECT @editableTypeID = e.id FROM as_crud_editableTypes AS e WHERE e.code = NULL;
    SELECT @filterTypeID = f.id FROM as_crud_filterTypes AS f WHERE f.code = NULL;
     INSERT INTO as_crud_cols ([tableID],[title],[tooltip],[ord],[datatypeID],[isSort],[isVisible]
        ,[isPK],[editableTypeID],[editableCallback],[filterTypeID],[code],[filterNotSelected],[format]
        ,[editableMin],[editableMax],[editableStep],[sqlGetListValues],[width])
    VALUES ( @tableID, 'Отправитель'
        , ''
        , 0
        , @tableDatatypeID
        , 0
        , 1
        , 0
        , @editableTypeID
        , ''
        , @filterTypeID
        , 'author'
        , ''
        , ''
        , NULL
        , NULL
        , NULL
        , ''
        , NULL
    );
    print N'Колонка author таблицы добавлена'
END try
BEGIN catch
    print  N'ERROR ############################################## При добалении колонки author таблицы возникла ошибка: ' + error_message()
END catch

BEGIN try
    SET @tableDatatypeID = NULL; SET @editableTypeID = NULL; SET @filterTypeID = NULL;
    SELECT @tableDatatypeID = d.id FROM as_crud_dataTypes AS d WHERE d.code = 'string';
    SELECT @editableTypeID = e.id FROM as_crud_editableTypes AS e WHERE e.code = NULL;
    SELECT @filterTypeID = f.id FROM as_crud_filterTypes AS f WHERE f.code = 'string';
     INSERT INTO as_crud_cols ([tableID],[title],[tooltip],[ord],[datatypeID],[isSort],[isVisible]
        ,[isPK],[editableTypeID],[editableCallback],[filterTypeID],[code],[filterNotSelected],[format]
        ,[editableMin],[editableMax],[editableStep],[sqlGetListValues],[width])
    VALUES ( @tableID, 'Сообщение'
        , ''
        , 0
        , @tableDatatypeID
        , 0
        , 1
        , 0
        , @editableTypeID
        , ''
        , @filterTypeID
        , 'text'
        , 'Найти...'
        , ''
        , NULL
        , NULL
        , NULL
        , ''
        , NULL
    );
    print N'Колонка text таблицы добавлена'
END try
BEGIN catch
    print  N'ERROR ############################################## При добалении колонки text таблицы возникла ошибка: ' + error_message()
END catch

BEGIN try
    SET @tableDatatypeID = NULL; SET @editableTypeID = NULL; SET @filterTypeID = NULL;
    SELECT @tableDatatypeID = d.id FROM as_crud_dataTypes AS d WHERE d.code = 'string';
    SELECT @editableTypeID = e.id FROM as_crud_editableTypes AS e WHERE e.code = NULL;
    SELECT @filterTypeID = f.id FROM as_crud_filterTypes AS f WHERE f.code = NULL;
     INSERT INTO as_crud_cols ([tableID],[title],[tooltip],[ord],[datatypeID],[isSort],[isVisible]
        ,[isPK],[editableTypeID],[editableCallback],[filterTypeID],[code],[filterNotSelected],[format]
        ,[editableMin],[editableMax],[editableStep],[sqlGetListValues],[width])
    VALUES ( @tableID, 'id'
        , ''
        , 0
        , @tableDatatypeID
        , 0
        , 0
        , 1
        , @editableTypeID
        , ''
        , @filterTypeID
        , 'id'
        , ''
        , ''
        , NULL
        , NULL
        , NULL
        , ''
        , NULL
    );
    print N'Колонка id таблицы добавлена'
END try
BEGIN catch
    print  N'ERROR ############################################## При добалении колонки id таблицы возникла ошибка: ' + error_message()
END catch

BEGIN try
    SET @tableDatatypeID = NULL; SET @editableTypeID = NULL; SET @filterTypeID = NULL;
    SELECT @tableDatatypeID = d.id FROM as_crud_dataTypes AS d WHERE d.code = 'string';
    SELECT @editableTypeID = e.id FROM as_crud_editableTypes AS e WHERE e.code = NULL;
    SELECT @filterTypeID = f.id FROM as_crud_filterTypes AS f WHERE f.code = NULL;
     INSERT INTO as_crud_cols ([tableID],[title],[tooltip],[ord],[datatypeID],[isSort],[isVisible]
        ,[isPK],[editableTypeID],[editableCallback],[filterTypeID],[code],[filterNotSelected],[format]
        ,[editableMin],[editableMax],[editableStep],[sqlGetListValues],[width])
    VALUES ( @tableID, 'Чат'
        , ''
        , 0
        , @tableDatatypeID
        , 0
        , 1
        , 0
        , @editableTypeID
        , ''
        , @filterTypeID
        , 'room'
        , ''
        , ''
        , NULL
        , NULL
        , NULL
        , ''
        , NULL
    );
    print N'Колонка room таблицы добавлена'
END try
BEGIN catch
    print  N'ERROR ############################################## При добалении колонки room таблицы возникла ошибка: ' + error_message()
END catch

BEGIN try
    DELETE FROM as_crud_tableOperations WHERE tableID = @tableID;
    print N'Операции таблицы chatSearch удалены'
END try
BEGIN catch
    PRINT N'ERROR ############################################## При удалении операций таблицы chatSearch возникла ошибка: ' + error_message()
END catch

GO


print  N'НАЧАЛО PROCS crud_chatSearch[_]-------------------- '
print N'Попытка удаления хранимых процедур'
DECLARE @name nvarchar(256), @sqlExpec nvarchar(MAX);
DECLARE 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')
OPEN cur fetch NEXT FROM cur INTO @name
while @@FETCH_STATUS = 0 BEGIN
    BEGIN try
        SET @sqlExpec = 'drop procedure [' + @name+']'
        EXEC sp_executesql @sqlExpec
        print N'Удалена хранимая процедура ' + @name
    END try
    BEGIN catch
        print N'ERROR ############################################## Ошибка при удалени хранимой процедуры ' + @name + ': '+ error_message()
    END catch
    fetch NEXT FROM cur INTO @name
END
close cur deallocate cur

BEGIN try
    EXEC sp_executesql N'CREATE PROCEDURE [dbo].[crud_chatSearch_getItems]
    @filters CRUDFilterParameter READONLY,
    @sort sql_variant,
    @direction nvarchar(8),
    @page int,
    @pageSize int,
    @username nvarchar(32)
AS
BEGIN
    declare   @ids TABLE (id int)

    -- filters...
    declare @search nvarchar(128) = isnull((select Value from @filters where [Key] = ''text''), '''')

    declare @userID int = (select top 1 id from as_users where username = @username)
    declare @roomID int = isnull((select top 1 try_cast(value as int) from @filters where [key]=''itemID''), 0)
    insert into @ids
    select id
    from as_msg_messages
    where
        (@roomID =0 and roomID in (select roomID from as_msg_roomUsers where userID = @userID)
            or roomID = @roomID
        )
        and  @search<>''''
        and (isnull(@search, '''')='''' or text like ''%''+@search+''%'')

    -- SELECT 1
    select isnull(m.[id], 0) [id],
        isnull(try_convert(nvarchar(16), m.[created], 120), '''') [date],
        dbo.as_timeDelay(datediff(minute, m.created, getdate())) + '' назад'' desc_date,
        isnull((select username from as_users where id = m.userID), '''') [author],
        isnull(replace(m.[text], @search, ''<span class="bg-warning">''+ @search+ ''</span>''), '''') [text],
        iif(@roomID=0, ''<a href="/messages/''+iif(lower(type.code)=''userchat'', (select top 1 username from as_msg_roomUsers ru
                                                                               inner join as_users u on ru.userID = u.id
                                                                               where roomID = room.id and userID <>@userID
                                                                              ), type.code+''__''+cast(room.itemID as nvarchar))+''">''+[dbo].[msg_getRoomItemName](room.id, type.code, room.itemID, @userID)+''</a>'', NULL) room

    from as_msg_messages m
        inner join as_msg_rooms room on m.roomID = room.id
        inner join as_msg_types type on type.id = room.typeID
    where m.id in (select id from @ids)
    order by id desc
    OFFSET @PageSize * (@Page - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY;

    -- SELECT 2
    select count(*) from @ids

    -- SELECT 3
    select 1 Compact, ''14px'' fontSize, 1 HideTitleCount, ''h3'' headerTag,
        iif(@search='''', ''Что будем искать?'', ''Не найдено сообщений'') EmptyText
end

--ru 08.09.2022 11:31:36
--ru 08.09.2022 11:39:33
--ru 08.09.2022 12:15:03
'
    print N'Добавлена хранимая процедура crud_chatSearch_getItems'
END try
BEGIN catch
    print N'ERROR ############################################## Ошибка при добавлении хранимой процедуры crud_chatSearch_getItems: '+ error_message()
END catch


GO

print  N'КОНЕЦ PROCS crud_chatSearch[_]--------------------  '


--КОНЕЦ PROCS crud_chatSearch[_]--------------------
GO
print  N'КОНЕЦ TABLE chatSearch----------------------------------------------------------------------------------------------------  '


--КОНЕЦ TABLE chatSearch----------------------------------------------------------------------------------------------------