Advertisement
hecrus

Canban Getitem

Oct 24th, 2020
2,247
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.67 KB | None | 0 0
  1. CREATE PROCEDURE [dbo].[crud_kanbanOrders_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.     CREATE TABLE dbo.#result (id int, name nvarchar(256), color nvarchar(64) )
  11.  
  12.   declare @langID int
  13.     select @langID = try_cast(Value as int) from @filters where [Key] = 'langID'
  14.  
  15.     declare @filterCustomer int
  16.     select @filterCustomer = Value from @filters where [Key] = 'name'
  17.  
  18.     insert into #result
  19.     SELECT 1, iif(@langID=1, 'small', 'Мелкий'), 'lightGreen'
  20.     union
  21.     SELECT 2, iif(@langID=1, 'medium', 'Средний'), 'lightBlue'
  22.     union
  23.     SELECT 3, iif(@langID=1, 'large', 'Крупный'), 'pink'
  24.    
  25.     select * from #result
  26.     order by  id
  27.     OFFSET @PageSize * (@Page - 1) ROWS
  28.     FETCH NEXT @PageSize ROWS ONLY;
  29.    
  30.     select count(*) from tst_orders
  31.     drop table #result
  32.    
  33.     select 'kanban' viewType, -- 'entityOrder' kanbanItemForm, 'Детали заказа' kanbanItemFormTitle,
  34.            iif(@langID=1, 'Kanban Board (orders)', 'Доска Канбан (заказы)') Title
  35.  
  36.     select id,
  37.            statusID,
  38.            (select name from tst_customers where id = customerID) as name,
  39.            '<div class="small">'+(select name from tst_products where id = productID)+', '+cast(isnull(cnt,0) as nvarchar)+'</div>' [text],
  40.            iif(@langID=1, 'Customer comment:', 'Комментарий заказчика: ') + note tooltip,
  41.            isnull(convert(nvarchar, created, 120),'') bottomText,
  42.            '' customClass
  43.     from tst_orders
  44.     where (isnull(@filterCustomer,0)=0 or customerID = @filterCustomer) and statusID is not null
  45. END
  46.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement