hecrus

Demo1_get

Oct 17th, 2020
646
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE PROCEDURE [dbo].[crud_tst-customers_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.     -- основная процедура настройки таблицы
  11.    
  12.     -- результирующая таблица (описываем здесь поля)
  13.     -- спецполя для нее: icon_age, prev_age, prevcolor_age, color_age, backcolor_age, desc_name, color, barPercent_age, barClass_age, badge_age  
  14.     declare @result TABLE(
  15.         id nvarchar(max),
  16.         name nvarchar(max),
  17.         fio nvarchar(max),
  18.         actions nvarchar(max),
  19.         orders int,
  20.         vip bit,
  21.         age int,
  22.         color nvarchar(32)
  23.     )
  24.  
  25.     declare @langID int
  26.     select @langID = try_cast(Value as int) from @filters where [Key] = 'langID'
  27.  
  28.  
  29.     -- извлекаем доп параметры из URL
  30.     declare @filterName nvarchar(128)
  31.     select @filterName = Value from @filters where [Key] = 'name'
  32.     declare @filterWithOrders nvarchar(128)
  33.     select @filterWithOrders = Value from @filters where [Key] = 'withOrders'
  34.     if(@filterWithOrders is null) set @filterWithOrders = ''
  35.    
  36.     declare @filterVip nvarchar(128)
  37.     select @filterVip = Value from @filters where [Key] = 'vip'
  38.     if(@filterVip is null) set @filterVip = ''
  39.    
  40.     insert into @result select
  41.         id id,
  42.         isnull(name, '') name,
  43.         isnull(fio, '') fio,        
  44.         '<a href="#" class="as-form-modal btn btn-warning" data-code="tst-newOrder" data-itemID="'+cast(id as nvarchar)+'" data-big="0" data-title="'+iif(@langID=1, 'New order for ', 'Новый заказ для ')+''+isnull(name, '----')+'" data-btnText="'+iif(@langID=1, 'Create order', 'Создать заказ')+'"><span class="d-none d-xl-inline"> '+iif(@langID=1, 'Order', 'Заказ')+'</span></a>' actions,
  45.         (select count(*) from tst_orders where customerID = cust.id ) orders,
  46.         isnull(vip, 0) vip,
  47.         isnull(age,0) age,
  48.         iif(id%2 = 0, 'lightGreen','') color
  49.     from tst_customers cust
  50.     where
  51.         (isnull(@filterName, '')='' or name like '%'+@filterName+'%'  or fio like '%'+@filterName+'%')
  52.         and
  53.         (@filterWithOrders not in ('1', 'true') or @filterWithOrders in('1', 'true') and exists(select id from tst_orders where customerID = cust.id))
  54.         and
  55.         (@filterVip not in ('1', 'true') or @filterVip in('1', 'true') and vip=1)
  56.     -- 1 SELECT - сами данные    
  57.     select *,
  58.         '<div class="as-table" data-code="tst-customerOrders" data-itemID="'+cast(id as nvarchar)+'" data-animated="bounceInLeft"></div>' sub_name,
  59.         iif(@langID=1, 'There can be a form with detailed customer data (and any other component, for example Files, Chat)', 'Здесь может быть  форма с детальными данными по заказчику (и любой другой компонент, например Файлы, Чат и т.д.)') modal_fio,
  60.        'fa-info'  modalIcon_fio,
  61.         iif(@langID=1, 'Order count ', 'Количество заказов ')+'<span class="badge badge-info">'+cast(orders as nvarchar)+'</span>' desc_name,
  62.         (age * 2) barPercent_age,
  63.         'danger' barClass_age,
  64.         'white' color_age,
  65.         '20' prev_age,
  66.         'gray' prevcolor_age
  67.     from @result
  68.     order by  
  69.         case when @sort = 'name' and @direction = 'down' then name end desc,
  70.         case when @sort = 'name' and @direction = 'up' then name end asc,
  71.         case when @sort = 'fio' and @direction = 'down' then fio end desc,
  72.         case when @sort = 'fio' and @direction = 'up' then fio end asc
  73.     OFFSET @PageSize * (@Page - 1) ROWS
  74.     FETCH NEXT @PageSize ROWS ONLY;
  75.    
  76.     -- 2 SELECT - кол-во в таблице
  77.     select count(*) from @result   
  78.  
  79.     -- 3 SELECT Дополнительные настройки таблицы
  80.     Select
  81.         iif(@langID=1, 'Customers', 'Заказчики')  Title,
  82.         iif(@langID=1, 'This is a demo of the capabilities of the Tables component. The data structure and business logic can be changed as desired.',
  83.             'Это демо возможностей компоне0та Таблицы. Структуру данных и бизнес-логику можно менять как угодно. ') titleTooltip,
  84.         '' ToolbarAdditional,
  85.         '' GroupOperationsToolbar,
  86.         '' FastCreateLinkText, '' FastCreateDialogHeader, '' FastCreateDialogPlaceholder,
  87.          1 HideTitleCount,
  88.          0 DisableCellTitle,
  89.          '14px' FontSize,
  90.          '' FilterMakeup,
  91.          1 InstantFilter,
  92.          1 EnableExcelExport,
  93.          1 EnablePrint
  94.            --'dark' Theme
  95.         --'gantt' ViewType,
  96.         -- GanttScale, GanttNavigate, GanttItemForm, GanttItemFormTitle
  97.         -- KanbanItemForm, KanbanItemFormTitle
  98.  
  99.  
  100.     -- 4 SELECT Данные для подвала страницы или данные для Ганта/Канбана (если установлен ViewType в 3 SELECT)
  101.  
  102. END
  103.  
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.

×