hecrus

Card Table GetItems

Oct 24th, 2020
1,146
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

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×