private static string BaseSql(ProjectInfoSortOptions sort = ProjectInfoSortOptions.Priority)
{
return($@"WITH [source] AS (
SELECT
[p].*,
[ptr].[Name] AS [PriorityTier],
[ptr].[Rank] AS [TierRank],
[ptr].[MaxProjects] AS [MaxProjects],
[app].[Name] AS [ApplicationName],
(SELECT
SUM(COALESCE([wid].[EstimateHours], [sz].[EstimateHours]))
FROM [dbo].[WorkItem] [wi] LEFT JOIN [dbo].[WorkItemSize] [sz] ON [wi].[SizeId]=[sz].[Id]
LEFT JOIN [dbo].[WorkItemDevelopment] [wid] ON [wi].[Id]=[wid].[WorkItemId]
WHERE [wi].[ProjectId]=[p].[Id] AND [wi].[CloseReasonId] IS NULL) AS [EstimateHours],
(SELECT COUNT(1) FROM [dbo].[WorkItem] WHERE [ProjectId]=[p].[Id]) AS [TotalWorkItems],
(SELECT COUNT(1) FROM [dbo].[WorkItem] WHERE [ProjectId]=[p].[Id] AND [CloseReasonId] IS NULL) AS [OpenWorkItems],
(SELECT COUNT(1) FROM [dbo].[WorkItem] WHERE [ProjectId]=[p].[Id] AND [CloseReasonId] IS NOT NULL) AS [ClosedWorkItems],
(SELECT COUNT(1)
FROM
[dbo].[WorkItem] [wi]
LEFT JOIN [dbo].[WorkItemDevelopment] [wid] ON [wi].[Id]=[wid].[WorkItemId]
LEFT JOIN [dbo].[WorkItemSize] [sz] ON [wi].[SizeId]=[sz].[Id]
WHERE
[ProjectId]=[p].[Id] AND [CloseReasonId] IS NULL AND
COALESCE([wid].[EstimateHours], [sz].[EstimateHours]) IS NULL) AS [UnestimatedWorkItems],
(SELECT COUNT(1) FROM [dbo].[WorkItem] WHERE [ProjectId]=[p].[Id] AND [CloseReasonId] IS NULL AND [MilestoneId] IS NOT NULL AND [ActivityId] IS NULL) AS [StoppedWorkItems],
(SELECT COUNT(1) FROM [dbo].[WorkItem] WHERE [ProjectId]=[p].[Id] AND [CloseReasonId] IS NULL AND [MilestoneId] IS NULL) AS [UnscheduledWorkItems],
(SELECT COUNT(1) FROM [dbo].[WorkItem] WHERE [ProjectId]=[p].[Id] AND [CloseReasonId] IS NULL AND [HasImpediment]=1) AS [ImpededWorkItems],
CASE
WHEN EXISTS(SELECT 1 FROM [dbo].[WorkItem] WHERE [ProjectId]=[p].[Id]) THEN 0
WHEN [p].[HtmlBody] IS NOT NULL THEN 0
ELSE 1
END AS [AllowDelete]
FROM
[dbo].[Project] [p]
INNER JOIN [dbo].[Application] [app] ON [p].[ApplicationId]=[app].[Id]
LEFT JOIN [dbo].[FnPriorityTierRanges](@orgId) [ptr] ON
[p].[Priority] >= [ptr].[MinPriority] AND
[p].[Priority] <= [ptr].[MaxPriority]
WHERE
[app].[OrganizationId]=@orgId
{{andWhere}}
) SELECT
[source].*,
CASE
WHEN [TotalWorkItems] > 0 THEN CONVERT(float, [ClosedWorkItems]) / CONVERT(float, [TotalWorkItems])
ELSE 0
END AS [PercentComplete]
FROM
[source]
ORDER BY {SortOptions[sort]}" );
}
public ProjectInfo(ProjectInfoSortOptions sort = ProjectInfoSortOptions.Name) : base(BaseSql(sort))
{
}
public ProjectInfo(ProjectInfoSortOptions sort = ProjectInfoSortOptions.Priority) : base(BaseSql(sort))
{
}
