Advertisement
hecrus

Chartbar GetItems

Oct 24th, 2020
2,594
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 5.08 KB | None | 0 0
  1. CREATE PROCEDURE [dbo].[crud_tst-chartbar_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 (id nvarchar(128), title nvarchar(256), ord int)
  15.    
  16.     declare @langID int
  17.     select @langID = try_cast(Value as int) from @filters where [Key] = 'langID'
  18.  
  19.     declare @filterWeek nvarchar(256), @d1 datetime, @d2 datetime
  20.     select @filterWeek = Value from @filters where [Key] = 'created'
  21.    
  22.     if isnull(@filterWeek,'')='' begin
  23.         declare @weeks table (i int, d1 datetime, d2 datetime)
  24.         declare @i int = 0
  25.         while @i > -4
  26.         begin
  27.             insert into @weeks
  28.             select abs(@i), dateadd(week,datediff(week,0,dateadd(week,@i,'2020-03-27 00:00:00.000')),0), dateadd(week,datediff(week,0,dateadd(week,@i+1,'2020-03-27 00:00:00.000')),0)
  29.             set @i = @i - 1
  30.         end
  31.  
  32.         select top 1 @filterWeek = convert(nvarchar,d1,104)+','+convert(nvarchar,d2,104)
  33.         from @weeks
  34.         order by i
  35.     end
  36.    
  37.     select @d1 = convert(datetime, dbo.str_splitPart(@filterWeek,',',1), 104)
  38.     select @d2 = convert(datetime, dbo.str_splitPart(@filterWeek,',',2), 104)
  39.    
  40.     SET DATEFIRST 1
  41.  
  42.     insert into @result
  43.     select  '1' id, '<div class="font-weight-normal">'+iif(@langID=1, 'Mon', 'Понедельник')+'</div><div class="small text-muted">'+isnull(convert(nvarchar,@d1,104),'')+'</div>' title, 1 ord
  44.     union
  45.     select  '2' id, '<div class="font-weight-normal">'+iif(@langID=1, 'Tue', 'Вторник')+'</div><div class="small text-muted">'+isnull(convert(nvarchar,dateadd(day,1,@d1),104),'')+'</div>' title, 2 ord
  46.     union
  47.     select  '3' id, '<div class="font-weight-normal">'+iif(@langID=1, 'Web', 'Среда')+'</div><div class="small text-muted">'+isnull(convert(nvarchar,dateadd(day,2,@d1),104),'')+'</div>' title, 3 ord
  48.     union
  49.     select  '4' id, '<div class="font-weight-normal">'+iif(@langID=1, 'Thur', 'Четверг')+'</div><div class="small text-muted">'+isnull(convert(nvarchar,dateadd(day,3,@d1),104),'')+'</div>' title, 4 ord
  50.     union
  51.     select  '5' id, '<div class="font-weight-normal">'+iif(@langID=1, 'Fri', 'Пятница')+'</div><div class="small text-muted">'+isnull(convert(nvarchar,dateadd(day,4,@d1),104),'')+'</div>' title, 5 ord
  52.     union
  53.     select  '6' id, '<div class="font-weight-normal">'+iif(@langID=1, 'Sat', 'Суббота')+'</div><div class="small text-muted">'+isnull(convert(nvarchar,dateadd(day,5,@d1),104),'')+'</div>' title, 6 ord
  54.     union
  55.     select  '7' id, '<div class="font-weight-normal">'+iif(@langID=1, 'Sun', 'Воскресенье')+'</div><div class="small text-muted">'+isnull(convert(nvarchar,dateadd(day,6,@d1),104),'')+'</div>' title, 7 ord
  56.     /*
  57.     select  '1' id, 'ПН '+isnull(left(convert(nvarchar,@d1,104),5),'') title, 1 ord
  58.     union
  59.     select  '2' id, 'ВТ '+isnull(left(convert(nvarchar,dateadd(day,1,@d1),104),5),'') title, 2 ord
  60.     union
  61.     select  '3' id, 'СР '+isnull(left(convert(nvarchar,dateadd(day,2,@d1),104),5),'') title, 3 ord
  62.     union
  63.     select  '4' id, 'ЧТ '+isnull(left(convert(nvarchar,dateadd(day,3,@d1),104),5),'') title, 4 ord
  64.     union
  65.     select  '5' id, 'ПТ '+isnull(left(convert(nvarchar,dateadd(day,4,@d1),104),5),'') title, 5 ord
  66.     union
  67.     select  '6' id, 'СБ '+isnull(left(convert(nvarchar,dateadd(day,5,@d1),104),5),'') title, 6 ord
  68.     union
  69.     select  '7' id, 'ВС '+isnull(left(convert(nvarchar,dateadd(day,6,@d1),104),5),'') title, 7 ord
  70.     */
  71.    
  72.     -- 1 SELECT - сами данные    
  73.     select * from @result
  74.     order by ord
  75.    
  76.     -- 2 SELECT - кол-во в таблице
  77.     select count(*) from @result   
  78.  
  79.     -- 3 SELECT Дополнительные настройки таблицы
  80.     select 'chartbar' ViewType, 1 InstantFilter, 1 HideTitleCount, iif(@langID=1, 'Orders by weeks', 'Заказы по неделям') Title
  81.  
  82.     -- 4 SELECT Данные для подвала страницы или данные для Ганта/Канбана (если установлен ViewType в 3 SELECT)    
  83.     select cast(id as nvarchar) itemID,
  84.            cast(DATEPART(dw,created) as nvarchar) [from],
  85.            cast(DATEPART(dw,created) as nvarchar) [to],
  86.            '<div class="text-dark font-weight-bold">'+cast(isnull(price*cnt,0) as nvarchar)+'</div>' [title],
  87.            '<div class="small">'+(select name from tst_products where id = productID)+', '+cast(isnull(cnt,0) as nvarchar)+'</div>' [desc],
  88.            '' cssClass,
  89.            case when price*cnt <= 25000 then 'lightBlue'
  90.                 when price*cnt > 25000 and price*cnt <= 50000 then 'lightGreen'
  91.                 when price*cnt > 50000 then 'pink' end color
  92.     from tst_orders
  93.     where isnull(@filterWeek,'')='' or created between @d1 and @d2
  94.     order by created
  95. END
  96.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement