Demo1_get

CREATE PROCEDURE [dbo].[crud_tst-customers_getItems]
    @filters CRUDFilterParameter READONLY,
    @sort sql_variant,
    @direction nvarchar(8),
    @page int,
    @pageSize int,
    @username nvarchar(32)
AS
BEGIN
    -- основная процедура настройки таблицы

    -- результирующая таблица (описываем здесь поля)
    -- спецполя для нее: icon_age, prev_age, prevcolor_age, color_age, backcolor_age, desc_name, color, barPercent_age, barClass_age, badge_age
    declare @result TABLE(
        id nvarchar(max),
        name nvarchar(max),
        fio nvarchar(max),
        actions nvarchar(max),
        orders int,
        vip bit,
        age int,
        color nvarchar(32)
    )

    declare @langID int
    select @langID = try_cast(Value as int) from @filters where [Key] = 'langID'


    -- извлекаем доп параметры из URL
    declare @filterName nvarchar(128)
    select @filterName = Value from @filters where [Key] = 'name'
    declare @filterWithOrders nvarchar(128)
    select @filterWithOrders = Value from @filters where [Key] = 'withOrders'
    if(@filterWithOrders is null) set @filterWithOrders = ''

    declare @filterVip nvarchar(128)
    select @filterVip = Value from @filters where [Key] = 'vip'
    if(@filterVip is null) set @filterVip = ''

    insert into @result select
        id id,
        isnull(name, '') name,
        isnull(fio, '') fio,
        '<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,
        (select count(*) from tst_orders where customerID = cust.id ) orders,
        isnull(vip, 0) vip,
        isnull(age,0) age,
        iif(id%2 = 0, 'lightGreen','') color
    from tst_customers cust
    where
        (isnull(@filterName, '')='' or name like '%'+@filterName+'%'  or fio like '%'+@filterName+'%')
        and
        (@filterWithOrders not in ('1', 'true') or @filterWithOrders in('1', 'true') and exists(select id from tst_orders where customerID = cust.id))
        and
        (@filterVip not in ('1', 'true') or @filterVip in('1', 'true') and vip=1)
    -- 1 SELECT - сами данные
    select *,
        '<div class="as-table" data-code="tst-customerOrders" data-itemID="'+cast(id as nvarchar)+'" data-animated="bounceInLeft"></div>' sub_name,
        iif(@langID=1, 'There can be a form with detailed customer data (and any other component, for example Files, Chat)', 'Здесь может быть  форма с детальными данными по заказчику (и любой другой компонент, например Файлы, Чат и т.д.)') modal_fio,
       'fa-info'  modalIcon_fio,
        iif(@langID=1, 'Order count ', 'Количество заказов ')+'<span class="badge badge-info">'+cast(orders as nvarchar)+'</span>' desc_name,
        (age * 2) barPercent_age,
        'danger' barClass_age,
        'white' color_age,
        '20' prev_age,
        'gray' prevcolor_age
    from @result
    order by
        case when @sort = 'name' and @direction = 'down' then name end desc,
        case when @sort = 'name' and @direction = 'up' then name end asc,
        case when @sort = 'fio' and @direction = 'down' then fio end desc,
        case when @sort = 'fio' and @direction = 'up' then fio end asc
    OFFSET @PageSize * (@Page - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY;

    -- 2 SELECT - кол-во в таблице
    select count(*) from @result

    -- 3 SELECT Дополнительные настройки таблицы
    Select
        iif(@langID=1, 'Customers', 'Заказчики')  Title,
        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.',
            'Это демо возможностей компоне0та Таблицы. Структуру данных и бизнес-логику можно менять как угодно. ') titleTooltip,
        '' ToolbarAdditional,
        '' GroupOperationsToolbar,
        '' FastCreateLinkText, '' FastCreateDialogHeader, '' FastCreateDialogPlaceholder,
         1 HideTitleCount,
         0 DisableCellTitle,
         '14px' FontSize,
         '' FilterMakeup,
         1 InstantFilter,
         1 EnableExcelExport,
         1 EnablePrint
           --'dark' Theme
        --'gantt' ViewType,
        -- GanttScale, GanttNavigate, GanttItemForm, GanttItemFormTitle
        -- KanbanItemForm, KanbanItemFormTitle


    -- 4 SELECT Данные для подвала страницы или данные для Ганта/Канбана (если установлен ViewType в 3 SELECT)

END