public OpenWorkItems(int rootId) : base(
$@"WITH [tree] AS (
{MyFolderTree.GetRecursiveQuery(rootId)}
) SELECT
[wi].*,
[a].[Name] AS [CurrentActivity],
COALESCE([au].[DisplayName], [au].[UserName]) AS [AssignedTo],
[t].[FullPath] AS [FolderPath]
FROM
[dbo].[WorkItem] [wi]
LEFT JOIN [dbo].[Activity] [a] ON [wi].[ActivityId]=[a].[Id]
LEFT JOIN [dbo].[WorkItemActivity] [wia] ON
[wi].[Id]=[wia].[WorkItemId] AND
[wi].[ActivityId]=[wia].[ActivityId]
LEFT JOIN [dbo].[AspNetUsers] [au] ON [wia].[UserId]=[au].[UserId]
INNER JOIN [tree] [t] ON [wi].[FolderId]=[t].[Id]
WHERE
[wi].[WorkspaceId]=@workspaceId AND
[wi].[CloseReasonId] IS NULL
{{andWhere}}
ORDER BY
[t].[FullPath],
[wi].[Number] {{offset}}")
{
RootId = rootId;
}
public OpenWorkItemLabelCounts(int rootId) : base(
$@"WITH [tree] AS (
{MyFolderTree.GetRecursiveQuery(rootId)}
), [source] AS (
SELECT
[lbl].[Id],
[lbl].[Name],
[lbl].[BackColor],
[lbl].[TextColor],
(
SELECT
COUNT(1)
FROM
[dbo].[WorkItemLabel] [wil]
INNER JOIN [dbo].[WorkItem] [wi] ON [wil].[WorkItemId]=[wi].[Id]
INNER JOIN [tree] [t] ON [wi].[FolderId]=[t].[Id]
WHERE
[wil].[LabelId]=[lbl].[Id] AND
[wi].[WorkspaceId]=@workspaceId AND
[wi].[CloseReasonId] IS NULL
) AS [Count]
FROM
[dbo].[Label] [lbl]
WHERE
[lbl].[WorkspaceId]=@workspaceId
) SELECT
[source].*
FROM
[source]
ORDER BY
[Count] DESC")
{
RootId = rootId;
}
public CountWorkItemsByFolder(int rootId) : base(
$@"WITH [tree] AS (
{MyFolderTree.GetRecursiveQuery(rootId)}
) SELECT
[f].[Id] AS [FolderId],
(SELECT COUNT(1) FROM [dbo].[WorkItem] WHERE [FolderId]=[f].[Id] AND [CloseReasonId] IS NULL) AS [OpenCount],
(SELECT COUNT(1) FROM [dbo].[WorkItem] WHERE [FolderId]=[f].[Id] AND [CloseReasonId] IS NOT NULL) AS [ClosedCount]
FROM
[dbo].[Folder] [f]
INNER JOIN [tree] [t] ON [f].[Id]=[t].[Id]")
{
RootId = rootId;
}
