Читайте также:
|
USE db209
GO
CREATE PROCEDURE [dbo].[UserReport]
@UserId INT = NULL,
@PositionId INT = NULL,
@ProjectId INT = NULL
AS
SELECT [dbo].[FormatUserName](
u.FirstName,
u.MiddleName,
u.LastName) [User],
ps.Title [Position],
p.Title [Project],
ISNULL(ts.Spent, 0) [Spent hours],
ISNULL(ts.Total, 0) [Total tasks],
ISNULL(ts.Opened, 0) [Opened tasks],
ISNULL(ts.Expired, 0) [Expired tasks],
ISNULL(ct.Created, 0) [Created tasks],
ISNULL(cm.Comments, 0) [Comments],
ISNULL(at.Audited, 0) [Audited tasks]
FROM [dbo].[User] u
LEFT OUTER JOIN (SELECT t.PerformerId [UserId],
t.ProjectId [ProjectId],
SUM(t.SpentTime) [Spent],
COUNT(*) [Total],
COUNT(CASE
WHEN t.StateId = c.FinalStateId THEN 1 ELSE NULL
END) [Opened],
COUNT(CASE
WHEN t.EndDate < GETDATE() AND t.StateId <> c.FinalStateId THEN 1 ELSE NULL
END) [Expired]
FROM [dbo].[Task] t JOIN [dbo].[Category] c ON t.CategoryId = c.Id
GROUP BY t.ProjectId, t.PerformerId) ts
ON u.Id = ts.UserId
LEFT OUTER JOIN [dbo].[Project] p
ON p.Id = ts.ProjectId
LEFT OUTER JOIN [dbo].[Position] ps
ON u.PositionId = ps.Id
LEFT OUTER JOIN (SELECT t.CreatorId [UserId],
t.ProjectId [ProjectId],
COUNT(*) [Created]
FROM [dbo].[Task] t
GROUP BY t.CreatorId, t.ProjectId) ct
ON u.Id = ct.UserId AND p.Id = ct.ProjectId
LEFT OUTER JOIN (SELECT c.UserId [UserId],
t.ProjectId [ProjectId],
COUNT(*) [Comments]
FROM [dbo].[Comment] c JOIN [dbo].[Task] t ON c.TaskId = t.Id
GROUP BY c.UserId, t.ProjectId) cm
ON u.Id = cm.UserId AND p.Id = cm.ProjectId
LEFT OUTER JOIN (SELECT ta.UserId [UserId],
t.ProjectId [ProjectId],
COUNT(*) [Audited]
FROM [dbo].[TaskAuditor] ta JOIN [dbo].[Task] t ON ta.TaskId = t.Id
GROUP BY ta.UserId, t.ProjectId) at
ON u.Id = at.UserId AND p.Id = at.ProjectId
WHERE p.Id IS NOT NULL
AND (u.Id = @UserId OR @UserId IS NULL)
AND (ps.Id = @PositionId OR @PositionId IS NULL)
AND (p.Id = @ProjectId OR @ProjectId IS NULL)
ORDER BY [User], [Project]
GO
CREATE PROCEDURE [dbo].[ProjectReport]
@ProjectId INT = NULL
AS
SELECT p.Title [Project],
ISNULL(ts.Spent, 0) [Spent hours],
ISNULL(ts.Estimated, 0) [Estimate hours],
ISNULL(ts.Progress, 0) [Progress],
ISNULL(ts.Total, 0) [Total tasks],
ISNULL(ts.Opened, 0) [Opened tasks],
ISNULL(ts.Expired, 0) [Expired tasks],
ISNULL(ut.Users, 0) [Users],
ISNULL(ct.Comments, 0) [Comments],
ISNULL(ft.Files, 0) [Files]
FROM [dbo].[Project] p
LEFT OUTER JOIN (SELECT t.ProjectId [ProjectId],
SUM(t.SpentTime) [Spent],
SUM(t.EstimatedTime) [Estimated],
AVG(t.Progress) [Progress],
COUNT(t.Id) [Total],
COUNT(CASE
WHEN t.StateId = c.FinalStateId THEN 1 ELSE NULL
END) [Opened],
COUNT(CASE
WHEN t.EndDate < GETDATE() AND t.StateId <> c.FinalStateId THEN 1 ELSE NULL
END) [Expired]
FROM [dbo].[Task] t JOIN [dbo].[Category] c ON t.CategoryId = c.Id
GROUP BY t.ProjectId) ts
ON p.Id = ts.ProjectId
LEFT OUTER JOIN (SELECT u.ProjectId [ProjectId],
COUNT(u.UserId) [Users]
FROM [dbo].[ProjectUser] u
GROUP BY u.ProjectId) ut
ON p.Id = ut.ProjectId
LEFT OUTER JOIN (SELECT t.ProjectId [ProjectId],
COUNT(c.Id) [Comments]
FROM [dbo].[Comment] c JOIN [dbo].[Task] t ON c.TaskId = t.Id
GROUP BY t.ProjectId) ct
ON p.Id = ct.ProjectId
LEFT OUTER JOIN ((SELECT t.ProjectId [ProjectId],
COUNT(cf.FileId) [Files]
FROM [dbo].[Comment] c JOIN [dbo].[Task] t ON c.TaskId = t.Id
JOIN [dbo].[CommentFile] cf ON cf.CommentId = c.Id
GROUP BY t.ProjectId)
UNION
(SELECT pf.ProjectId [ProjectId],
COUNT(pf.FileId) [Files]
FROM [dbo].[ProjectFile] pf
GROUP BY pf.ProjectId)
UNION
(SELECT t.ProjectId [ProjectId],
COUNT(tf.FileId) [Files]
FROM [dbo].[TaskFile] tf JOIN [dbo].[Task] t ON tf.TaskId = t.Id
GROUP BY t.ProjectId)) ft
ON p.Id = ct.ProjectId
WHERE p.Id = @ProjectId OR @ProjectId IS NULL
ORDER BY [Project]
GO
CREATE PROCEDURE [dbo].[TasksByState]
@StateId INT = NULL,
@CategoryId INT = NULL
AS
SELECT c.[Title] [Category],
s.[Title] [State],
p.[Title] [Project],
t.[Count] [Tasks]
FROM (SELECT CategoryId,
StateId,
ProjectId,
COUNT(Id) [Count]
FROM [dbo].[Task]
WHERE (StateId = @StateId OR @StateId IS NULL)
AND (CategoryId = @CategoryId OR @CategoryId IS NULL)
GROUP BY CategoryId,
StateId,
ProjectId) t
JOIN [dbo].[Category] c
ON t.CategoryId = c.Id
JOIN [dbo].[Project] p
ON t.ProjectId = p.Id
JOIN [dbo].[State] s
ON t.StateId = s.Id
ORDER BY [Category], [State], [Project], [Tasks] DESC
GO
CREATE PROCEDURE [dbo].[TasksByMilestone]
@MilestoneId INT = NULL,
@ProjectId INT = NULL
AS
SELECT p.[Title] [Project],
m.[Title] [Milestone],
m.[Due] [Due],
t.[Count] [Tasks]
FROM (SELECT ProjectId,
MilestoneId,
COUNT(Id) [Count]
FROM [dbo].[Task]
WHERE (MilestoneId = @MilestoneId OR @MilestoneId IS NULL)
AND (ProjectId = @ProjectId OR @ProjectId IS NULL)
GROUP BY ProjectId,
MilestoneId) t
JOIN [dbo].[Project] p
ON t.ProjectId = p.Id
JOIN [dbo].[Milestone] m
ON t.MilestoneId = m.Id
ORDER BY [Project], [Milestone], [Due], [Tasks]
GO
Дата добавления: 2015-10-31; просмотров: 76 | Нарушение авторских прав
| <== предыдущая страница | | | следующая страница ==> |
| ПРИЛОЖЕНИЕ 5 ФАЙЛ Procedures.sql | | | ПРИЛОЖЕНИЕ 9 ФАЙЛ FillData.sql |