Руководство администратора

DECLARE @res NVARCHAR(MAX);
 
SET @res = '
{
   "columns": [
       {"key": "1", "name": "Новая", "template": "1", "style": "info", "icon": "help", "movableTo": true, "movableFrom": true},
       {"key": "2", "name": "Выполняется", "template": "1", "style": "warning", "icon": "check_circle", "movableTo": true, "movableFrom": true},
       {"key": "3", "name": "Завершена", "template": "1", "style": "success", "icon": "check_circle", "movableTo": true, "movableFrom": true, "showArrow": false}
   ],
   "group": {
       "key": "hasDueDate",
       "values": [
           {"key": 1, "label": "Есть срок", "order": 1},
           {"key": 0, "label": "Нет срока", "order": 2}
       ]
   },
  "rows": [
  {
           "key": "withPerf",
           "icon": "security",
           "color": "blue-900",
           "sort_key": 1,
           "name": "Есть исполнитель",
               "showByFilterValue": {
                     "perf": {
                           "onEmpty": true,
                           "values": ["withPerf"]
                     }
               }
      },
      {
           "key": "noPerf",
           "icon": "tags",
           "color": "blue-200",
           "sort_key": 2,
           "name": "Нет исполнителя",
               "showByFilterValue": {
                     "perf": {
                           "onEmpty": true,
                           "values": ["noPerf"]
                     }
               }
      }
   ],  
   "filters": [
       {
              "key": "perf",
              "label": "Исполнители",
              "icon": "settings",
              "type": "multiselect",
              "values": [
                   {
                      "key": "withPerf",
                      "label": "Есть исполнители",
                      "style": "style-primary"
                   },
                  {
                     "key": "noPerf",
                     "label": "Нет исполнителей",
                     "style": "style-primary"
                  }
            ]
      }
   ],
   "refreshOnPreviewClose": true,
   "moveWebRequest": "/app/v1.2/api/publications/action/kanban-step",
   "dataSet": "/app/v1.2/api/publications/action/kanban-data",
   "templates": {
       "1": "<style>.cmkb{display: flex;justify-content: space-between;align-items:flex-end;}.cmkb-user-label{color:rgba(0,0,0,.54);margin-top: 2px;margin-top: 5px;font-weight: bold;}.cmkb-footer{margin-top:5px;justify-content: space-between;align-items: center;}.cmkb-footer i{font-size: 15px;height: 15px; min-height: 15px; min-width: 15px; line-height: 14px; width: 15px; inline-size: 15px; vertical-align: middle; margin-right: 5px;}.cmkb-pic{flex: 0 0 40px;margin-top: 5px;margin-right: 5px;}.cmkb-priority{color: #f44336}</style><div class=''cmkb''> <div class=''cmkb-main''>{{#perfName}}<div class=''cmkb-user-label''>{{perfName}}</div>{{/perfName}}<div class=''cmkb-footer''>{{#isPriorityHigh}}<i title=''Высокий приоритет'' class=''cmkb-priority material-icons''>flash_on</i>{{/isPriorityHigh}}<span title=''Срок'' class=''cmkb-odate''>{{#orderedDate}}<i class=''material-icons''>query_builder</i>{{orderedDate}}{{/orderedDate}}</span> </div>{{#tags}}<div class=''cmkb-tags''><span>{{tags}}</span> </div>{{/tags}}</div>{{#perfName}}<div class=''cmkb-pic''> <img class=''kb-photo'' src=''{{perfPhoto}}''/> </div>{{/perfName}}</div>"
   }
}'
 
select @res

DECLARE @elements NVARCHAR(MAX);
DECLARE @rows NVARCHAR(MAX);
 
SET @elements = (
SELECT TOP 200
    StateId AS [column],
    case when IsNull(th.UserID,0)=0 then 'noPerf' else 'withPerf' end [row],
    TaskText AS label,
    cast(ExtParam2869NativeValue as int) AS percentage,
    CASE
        WHEN td.PriorityId = 3 THEN 'warning'
        WHEN td.PriorityId = 1 THEN 'secondary'
        WHEN td.PriorityId = 0 THEN 'primary'
    END AS style,
     'true' AS canMove,
     '/maintaskform.aspx?taskid=' + CAST(td.TaskId AS VARCHAR(10)) AS urlToOpen,
    FORMAT(OrderedTime, 'dd.MM') AS [info.orderedDate],
    u.DisplayName AS [info.perfName],
     '/GetAvatar.ashx?&Id=' + CAST(u.UserID AS VARCHAR(10)) AS [info.perfPhoto],
    u.UserID AS [info.perfId],
    td.TaskID AS [info.taskId],
    CASE WHEN td.OrderedTime IS NOT NULL THEN 1 ELSE 0 END AS [info.hasDueDate]
FROM TasksInSubcatXXXDenormalized td
    LEFT JOIN TaskHelpers th ON td.TaskID = th.TaskID AND th.IsResponsible = 1
    LEFT JOIN Users u ON th.UserID = u.UserID
WHERE DATEDIFF(day, IsNull(td.EndTime, GetDate()), GetDate()) < 30
FOR JSON PATH, ROOT('elements')
)
 
 
SET @rows = (
SELECT
    cast(sum(case when IsNull(th.UserID,0)=0 then 0 else 1 end) as varchar(max))+'/'+cast(count(*) as varchar(max)) as [withPerf.info],
    cast(sum(case when IsNull(th.UserID,0)=0 then 1 else 0 end) as varchar(max))+'/'+cast(count(*) as varchar(max)) as [noPerf.info]
FROM TasksInSubcatXXXDenormalized td
    LEFT JOIN TaskHelpers th ON td.TaskID = th.TaskID AND th.IsResponsible = 1
WHERE DATEDIFF(day, IsNull(td.EndTime, GetDate()), GetDate()) < 30
FOR JSON PATH
)
 
SELECT  '{"rowsInfo":' + substring(@rows,2,len(@rows)-2) + ',' + substring(@elements,2,len(@elements)-2) + '}'

В данном примере XXX — ID категории, по которой строится Канбан-доска.