hecrus

Timeline GetItems

Oct 24th, 2020 (edited)
1,170
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE PROCEDURE [dbo].[crud_tst-timeline_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 nvarchar(64), title nvarchar(max), link nvarchar(256), date nvarchar(64), [text] nvarchar(max), icon nvarchar(64), iconBackgroundClass nvarchar(64), isRight bit, dateOrder datetime)
  11.  
  12.     declare @langID int
  13.     select @langID = try_cast(Value as int) from @filters where [Key] = 'langID'
  14.  
  15.  
  16.     declare @filterTitle nvarchar(64)
  17.     select @filterTitle = Value from @filters where [Key] = 'title'
  18.    
  19.     declare @filterDate datetime
  20.     select @filterDate = try_convert(date, Value, 104) from @filters where [Key] = 'date'
  21.    
  22.     insert into @result
  23.     select 'cust_'+cast(id as nvarchar) as id,
  24.            '<sup class="text-success font-weight-bold mr-1">new</sup>'+name title,
  25.            '' as link,
  26.            convert(nvarchar,created,120) as date,
  27.            fio as text,
  28.            'fas fa-user-tie' as icon,
  29.            case when isnull(vip,0)=0 then 'primary' else 'danger' end as iconBackgroundClass,
  30.            0 isRight,
  31.            created as dateOrder
  32.     from tst_customers
  33.     where (isnull(@filterTitle,'')='' or name like '%'+@filterTitle+'%' or fio like '%'+@filterTitle+'%')
  34.     --and (isnull(@filterDate,'01-01-1900')='01-01-1900' or convert(nvarchar,created,104) = convert(nvarchar,@filterDate,104))
  35.     union
  36.     select 'order_'+cast(id as nvarchar) as id,
  37.            cast(isnull(price,0) as nvarchar)+' '+iif(@langID=1, 'USD', 'руб.')+' <sup class="text-secondary font-weight-bold ml-1">'+(select name from tst_products where id = productID)+', '+cast(isnull(cnt,0) as nvarchar)+' '+iif(@langID=1, 'cnt', 'шт.')+'</sup>' title,
  38.            '' as link,
  39.            convert(nvarchar,created,120) as date,
  40.            (select isnull(name,'--')+', '+isnull(fio,'--')+':' from tst_customers where id = customerID)+'<br>'+isnull(note,'--') as text,
  41.            'fas fa-box' as icon,
  42.            'success' as iconBackgroundClass,
  43.            1 isRight,
  44.            created as dateOrder
  45.     from tst_orders
  46.     where (isnull(@filterTitle,'')='' or (select name from tst_customers where id = customerID) like '%'+@filterTitle+'%' or (select fio from tst_customers where id = customerID) like '%'+@filterTitle+'%')
  47.     --and (isnull(@filterDate,'01-01-1900')='01-01-1900' or convert(nvarchar,created,104) = convert(nvarchar,@filterDate,104))
  48.    
  49.     -- 1 SELECT - сами данные    
  50.     select * from @result
  51.     order by dateOrder
  52.     OFFSET @PageSize * (@Page - 1) ROWS
  53.     FETCH NEXT @PageSize ROWS ONLY;
  54.    
  55.     -- 2 SELECT - кол-во в таблице
  56.     select count(*) from @result   
  57.  
  58.     -- 3 SELECT Дополнительные настройки таблицы
  59.     Select 'timeline' ViewType, 1 InstantFilter, iif(@langID=1, 'Timeline of customer activity', 'Временная линия активности заказчиков') Title
  60.     /*Select  '' Title,
  61.         '' ToolbarAdditional,
  62.         '' GroupOperationsToolbar,
  63.         '' FastCreateLinkText, '' FastCreateDialogHeader, '' FastCreateDialogPlaceholder,
  64.          0 HideTitleCount,
  65.          0 DisableCellTitle,
  66.          '10px' FontSize,
  67.          '{filterCode}' FilterMakeup,
  68.          1 InstantFilter,
  69.            */
  70.         --'gantt' ViewType,
  71.         -- GanttScale, GanttNavigate, GanttItemForm, GanttItemFormTitle
  72.         -- KanbanItemForm, KanbanItemFormTitle
  73.  
  74.  
  75.     -- 4 SELECT Данные для подвала страницы или данные для Ганта/Канбана (если установлен ViewType в 3 SELECT)
  76.  
  77. END
  78.  
RAW Paste Data