hecrus

Card Table GetItems

Oct 24th, 2020
833
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE PROCEDURE [dbo].[crud_tst-cardproducts_getItems]
  2.     @filters CRUDFilterParameter READONLY,
  3.     @sort sql_variant,
  4.     @direction nvarchar(8),
  5.     @page int,
  6.     @pageSize int,
  7.     @username nvarchar(32)
  8. AS
  9. BEGIN
  10.     declare @result TABLE (id int, imgTop nvarchar(128), Title nvarchar(512), headerColor nvarchar(16), [text] nvarchar(max), cardClass nvarchar(128))
  11.     declare @langID int
  12.     select @langID = try_cast(Value as int) from @filters where [Key] = 'langID'
  13.  
  14.    
  15.     declare @filterName nvarchar(128)
  16.     select @filterName = Value from @filters where [Key] = 'name'
  17.    
  18.     declare @filterPrice nvarchar(128)
  19.     select @filterPrice = Value from @filters where [Key] = 'price'
  20.     declare @sumFrom int, @sumTo int
  21.     select @sumFrom = min(try_cast(value as money)) from split(@filterPrice, ',')
  22.     select @sumTo = max(try_cast(value as money)) from split(@filterPrice, ',')
  23.    
  24.     insert into @result
  25.     select  id as id,
  26.             dbo.rs_resourceLink('tst-product',try_cast(id as nvarchar),0) as imgTop,
  27.             '<div class="small font-weight-bold mb-3">' + isnull(name, 'Без названия') + '</div>
  28.            <div class="row" style="font-size: small;">
  29.                 <div class="col-12 col-md-6"><i class="fas fa-clipboard-list mr-1 text-success"></i>' + isnull(art, 'Нет артикула') + '</div>
  30.                 <div class="col-12 col-md-6 font-weight-bold" style="text-align: end;">' + isnull(cast(price as nvarchar), 'Цена не указана') + '<i class="fas fa-ruble-sign ml-1 text-primary"></i></div>
  31.            </div>' as Title,            
  32.             'info' as headerColor,    
  33.             '<div class="small">'+[desc]+'</div>' as [text],
  34.             '' as cardClass            
  35.     from tst_products
  36.     where (isnull(@filterName,'')='' or name like '%'+@filterName+'%' or art like '%'+@filterName+'%' or [desc] like '%'+@filterName+'%')
  37.     and (isnull(@filterPrice,'')='' or price between @sumFrom and @sumTo)
  38.    
  39.     -- 1 SELECT - сами данные    
  40.     select * from @result
  41.     order by Title 
  42.     OFFSET @PageSize * (@Page - 1) ROWS
  43.     FETCH NEXT @PageSize ROWS ONLY;
  44.    
  45.     -- 2 SELECT - кол-во в таблице
  46.     select count(*) from @result
  47.    
  48.     -- 3 SELECT Дополнительные настройки таблицы    
  49.     select 'card' ViewType, 'columns' CardGroupType, 1 InstantFilter, iif(@langID=1, 'Product cards', 'Карточки товаров') title
  50. END
  51.  
RAW Paste Data