block_land_customlist_items_get

CREATE PROCEDURE [dbo].[block_land_customlist_items_get]
    @code nvarchar(128),
    @itemID nvarchar(256),
    @parameters ExtendedDictionaryParameter readonly,  -- langID, username...
    @res nvarchar(max) output
AS
BEGIN
    declare @catalogPageCode nvarchar(max) = 'customlist'
    declare @url nvarchar(max) = (select value2 from @parameters where [key]='itemID')

    declare @pageSize int = 10

    declare @filters table(name nvarchar(max), value nvarchar(max))
    insert into @filters (name, value)
    select dbo.str_splitpart(value, '=', 1) name, dbo.str_splitpart(value, '=', 2) value
    from dbo.split(@url, '--') where len(value)>0

    --declare @fsValues nvarchar(max) = ''
    -- select @fsValues = @fsValues +  Value + ' ' from @filters where len(value)>0
    declare @s1  nvarchar(max)= isnull((select top 1  value from @filters where name = 's1'), '')
    declare @s2 int= isnull((select top 1 try_cast( value as int) from @filters where name = 's2'), 0)
    declare @s3  bit = isnull((select top 1  try_cast(value as bit) from @filters where name = 's3'), 0)
    declare @page  int = isnull((select top 1  try_cast(value as int) from @filters where name = 'page'), 1)

    declare @ids table (id int primary key)
    insert into @ids(id)
    select id
    from tst_categories
    where ( @s1='' or name like '%'+@s1+'%')
        and (@s2=0 or parentID = @s2)
        and (@s3=0 or selected = 1)
    declare @total int = (select count(*) from @ids)
    declare @title nvarchar(max) = 'Каталог элементов (найдено '+cast(@total as nvarchar)+') '
        + iif(@s1<>'', 'Поиск по '+ @s1, '' )
        + iif(@s2>0, 'В категории  '+ isnull((select name from tst_categories where id = @s2), ''), '' )
        + iif(@s3=1, '(только ключевые)', '' )

    declare @text nvarchar(max) = ''
    select @text = @text + '
    <div class="col-12 col-md-3">
        <div class="as-panel">
            <h3>'+isnull(name, '')+'</h3>
            <div>'+isnull([code], '')+'</div>
        </div>
    </div>'
    from tst_categories
    where id in (select id from @ids)
    order by id
    OFFSET @PageSize * (@Page - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY;

    if(@total = 0) set @text = '<div class="col-12"><div class="mx-auto text-center alert alert-secondary w-50" >Ничего не найдено по запросу</div></div>'

    -- определяем пагинацию
    declare @pg nvarchar(max) = ''
    declare @i int = 1
    declare @clearUrl nvarchar(max) = replace(@url, '--page='+ cast(@page as nvarchar), '')
    while (@i <= round(@total / cast(@pageSize as float), 0)) begin
        set @pg = @pg +
            '<li class="page-item '+iif(@page=@i, 'active', '')+'"><a class="page-link"
                href="/'+@catalogPageCode+'/'+@clearUrl+iif(@i =1, '','--page='+cast(@i as nvarchar))+'">'+
                    cast(@i as nvarchar)+'</a>
             </li>'
        set @i = @i + 1
    end
    declare @pagination nvarchar(max) = iif(@pg<>'',  '<nav><ul class="pagination">'+isnull(@pg,'')+'</ul></nav>', '')

    set @res = '
    <div class="mb-5">
        <h1 class="h3">'+isnull(@title, '')+'</h1>
        <div class="row">
            '+isnull(@text, '')+'
        </div>
        '+ isnull(@pagination, '')+ '
    </div>'
END