Map GetItems

CREATE PROCEDURE [dbo].[crud_testMap_getItems]
    @filters CRUDFilterParameter READONLY,
    @sort sql_variant,
    @direction nvarchar(8),
    @page int,
    @pageSize int,
    @username nvarchar(32)
AS
BEGIN
    declare   @result TABLE (id int, lat nvarchar(32), lng nvarchar(32), description nvarchar(max),
                             tooltip nvarchar(128), radius int, canDrag bit, fillColor nvarchar(256), strokeWeight int, strokeColor nvarchar(max),
                            label nvarchar(128), labelClass nvarchar(256), icon nvarchar(128)
                            )

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


    declare @filterCity int
    select @filterCity = Value from @filters where [Key] = 'tooltip'

    insert into @result
    select id,
           lat,
           lng,
            (select top 1 name  from as_geo_regions where id = cityID) + ' ' + address description,
              address as tooltip,
           500 radius,
           1 canDrag,
           '#0000ff' fillColor,
           10 strokeWeight,
           '#00aadd' strokeColor,
           'Point '+ cast(id as nvarchar) label,
           'btn btn-info p-0 mb-5 font-weight-bold text-white' labelClass,
           '' icon
    from tst_addresses
    where isnull(@filterCity,0)=0 or cityID in (select id from as_geo_regions where id = @filterCity)
/*
    insert into @result
    select 1 id,
        '43.5868685' lat,
        '39.7299474' lng,
        '<div class="as-form" data-code="captureContact"></div>' description,
        'Подсказка для элемента' tooltip,
        500 radius,
        0 canDrag

    insert into @result
    select 2 id,
        '42.5868685' lat,
        '39.1299474' lng,
        'Вторая точка' description,
        'Подсказка для второй точки' tooltip,
        500 radius,
        0 canDrag

    insert into @result
    select 3 id,
        '55.753960' lat,
        '37.620393' lng,
        'Еще одна точка' description,
        'Подсказка для этой точки' tooltip,
        500 radius,
        0 canDrag
 */

    -- 1 SELECT - сами данные
    select * from @result
    order by  radius

    OFFSET @PageSize * (@Page - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY;

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

    declare @isAdmin int = 0
    select @isAdmin = 1 from sec_getUserRoles(@username) where role = 'admin'


    -- 3 SELECT Дополнительные настройки таблицы
    Select 'map' ViewType,
        1 MapShowUserLocation,
        '/Controls/Resource/GetFile?code=userPhoto&user='+@username+'&thumb=1' MapUserImageUrl,
        1 MapShowAllRegions,
        1 MapShowLocationInRange,
        '{
            "fitBound": 0,
            "zoom":7,
            "mapCenter": {"lat":55.760533, "lng": 37.487028}
        }' MapOptions,
        iif(@langID=1, 'Map with points', 'Вывод точек на карте') title,
        iif(@langID=1, 'An example of a map display with some points found, each point has the ability to place a shape or some kind of description', 'Пример вывода карты с некоторыми найденными точками, у каждой точки есть возможность разместить форму или некое описание') titleTooltip,

        case when isnull(@isAdmin,0)=0 then '' else '<a href="/tst-addAddress" class="btn btn-primary mb-1"><i class="fas fa-plus"></i> '+iif(@langID=1, 'Add point', 'Добавить точку')+'</a>' end as ToolbarAdditional,
        1 InstantFilter
    -- 4 SELECT Данные для подвала страницы или данные для Ганта/Канбана (если установлен ViewType в 3 SELECT)
END

--admin 29.04.2021 12:50:08
--ru 02.06.2021 15:16:16
--ru 15.01.2022 20:03:07
--ru 27.01.2022 10:29:00
--ru 27.01.2022 10:33:50