예제 #1
0
 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;
 }
예제 #3
0
 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;
 }