Читайте также:
|
USE db209
GO
CREATE PROCEDURE [dbo].[GetTaskInfo]
@TaskId INT,
@Estimated INT OUTPUT,
@Spent INT OUTPUT,
@Progress INT OUTPUT,
@StartDate DATE OUTPUT,
@EndDate DATE OUTPUT,
@ModifyDate DATE OUTPUT,
@Performers NVARCHAR(MAX) OUTPUT,
@Comments INT OUTPUT,
@Files INT OUTPUT
AS
SELECT @Estimated = ISNULL(t.EstimatedTime, 0),
@Spent = ISNULL(t.SpentTime, 0),
@Progress = t.Progress,
@StartDate = t.StartDate,
@EndDate = [dbo].[GetTaskEndDate](t.Id),
@ModifyDate = t.ModifyDate,
@Performers = ISNULL([dbo].[FormatUserName](u.FirstName, u.MiddleName, u.LastName), '')
FROM [dbo].[Task] t
JOIN [dbo].[User] u
ON t.PerformerId = u.Id
WHERE t.Id = @TaskId
SELECT @Comments = COUNT(*)
FROM [dbo].[Comment]
WHERE TaskId = @TaskId
SELECT @Files = COUNT(*)
FROM [dbo].[TaskFile]
WHERE TaskId = @TaskId
DECLARE TaskCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT Id FROM [dbo].[Task] WHERE ParentId = @TaskId
OPEN TaskCursor
DECLARE @TotalProgress INT = 0
DECLARE @Total INT = 0
DECLARE @SubId INT
FETCH NEXT FROM TaskCursor INTO @SubId
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SubEstimated INT
DECLARE @SubSpent INT
DECLARE @SubProgress INT
DECLARE @SubStartDate DATE
DECLARE @SubEndDate DATE
DECLARE @SubModifyDate DATE
DECLARE @SubPerformers NVARCHAR(MAX)
DECLARE @SubComments INT
DECLARE @SubFiles INT
EXEC GetTaskInfo
@SubId,
@SubEstimated OUTPUT,
@SubSpent OUTPUT,
@SubProgress OUTPUT,
@SubStartDate OUTPUT,
@SubEndDate OUTPUT,
@SubModifyDate OUTPUT,
@SubPerformers OUTPUT,
@SubComments OUTPUT,
@SubFiles OUTPUT
SET @Estimated = @Estimated + @SubEstimated
SET @Spent = @Spent + @SubSpent
SET @TotalProgress = @TotalProgress + @SubProgress
SET @Comments = @Comments + @SubComments
SET @Files = @Files + @SubFiles
IF (@SubStartDate < @StartDate)
SET @StartDate = @SubStartDate
IF (@SubEndDate > @EndDate)
SET @EndDate = @SubEndDate
IF (@SubModifyDate > @ModifyDate)
SET @ModifyDate = @SubModifyDate
SET @Performers = @Performers + ', ' + @SubPerformers
SET @Total = @Total + 1
FETCH NEXT FROM TaskCursor INTO @SubId
END
IF (@Total > 0)
SET @Progress = (@Progress + @TotalProgress / @Total) / 2
CLOSE TaskCursor
GO
CREATE PROCEDURE [dbo].[GetTask]
@TaskId INT
AS
DECLARE @Estimated INT
DECLARE @Spent INT
DECLARE @Progress INT
DECLARE @StartDate DATE
DECLARE @EndDate DATE
DECLARE @ModifyDate DATE
DECLARE @Performers NVARCHAR(MAX)
DECLARE @Comments INT
DECLARE @Files INT
EXEC [dbo].[GetTaskInfo]
@TaskId,
@Estimated OUTPUT,
@Spent OUTPUT,
@Progress OUTPUT,
@StartDate OUTPUT,
@EndDate OUTPUT,
@ModifyDate OUTPUT,
@Performers OUTPUT,
@Comments OUTPUT,
@Files OUTPUT
SELECT t.Title [Task],
ISNULL(pt.Title, '-') [Parent task],
p.Title [Project],
ISNULL(cm.Title, '-') [Component],
ISNULL(m.Title, '-') [Milestone],
ct.Title [Category],
s.Title [State],
ISNULL([dbo].[FormatUserName](uc.FirstName, uc.MiddleName, uc.LastName), '-')
[Creator],
ISNULL([dbo].[FormatUserName](up.FirstName, up.MiddleName, up.LastName), '-')
[Performer],
@Estimated [Estimated hours],
@Spent [Spent hours],
@Progress [Progress],
@StartDate [Start date],
@EndDate [End date],
@ModifyDate [Modify date],
@Performers [Performers],
@Comments [Comments],
@Files [Files]
FROM [dbo].[Task] t
JOIN [dbo].[Project] p
ON t.ProjectId = p.Id
LEFT OUTER JOIN [dbo].[Milestone] m
ON t.MilestoneId = m.Id
JOIN [dbo].[Category] ct
ON t.CategoryId = ct.Id
LEFT OUTER JOIN [dbo].[Component] cm
ON t.ComponentId = cm.Id
JOIN [dbo].[State] s
ON t.StateId = s.Id
LEFT OUTER JOIN [dbo].[User] uc
ON t.CreatorId = uc.Id
LEFT OUTER JOIN [dbo].[User] up
ON t.PerformerId = up.Id
LEFT OUTER JOIN [dbo].[Task] pt
ON t.ParentId = pt.Id
WHERE t.Id = @TaskId
GO
CREATE PROCEDURE [dbo].[GetProjectInfo]
@ProjectId INT,
@StartDate DATE OUTPUT,
@EndDate DATE OUTPUT,
@ModifyDate DATE OUTPUT,
@Managers NVARCHAR(MAX) OUTPUT,
@Users NVARCHAR(MAX) OUTPUT
AS
SELECT @StartDate = p.StartDate,
@EndDate = [dbo].[GetProjectEndDate](p.Id),
@ModifyDate = p.ModifyDate,
@Managers = [dbo].[FormatUserName](u.FirstName, u.MiddleName, u.LastName)
FROM [dbo].[Project] p JOIN [dbo].[User] u ON p.ManagerId = u.Id
WHERE p.Id = @ProjectId
SET @Users = NULL
SELECT @Users = ISNULL(@Users + ', ', '') + [dbo].[FormatUserName](u.FirstName, u.MiddleName, u.LastName)
FROM [dbo].[ProjectUser] pu
JOIN [dbo].[User] u
ON pu.UserId = u.Id
WHERE pu.ProjectId = @ProjectId
SET @Users = ISNULL(@Users, '')
DECLARE ProjectCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT Id FROM [dbo].[Project] WHERE ParentId = @ProjectId
OPEN ProjectCursor
DECLARE @SubId INT
FETCH NEXT FROM ProjectCursor INTO @SubId
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SubStartDate DATE
DECLARE @SubEndDate DATE
DECLARE @SubModifyDate DATE
DECLARE @SubManagers NVARCHAR(MAX)
DECLARE @SubUsers NVARCHAR(MAX)
EXEC [dbo].[GetProjectInfo]
@SubId,
@SubStartDate OUTPUT,
@SubEndDate OUTPUT,
@SubModifyDate OUTPUT,
@SubManagers OUTPUT,
@SubUsers OUTPUT
IF (@SubStartDate < @StartDate)
SET @StartDate = @SubStartDate
IF (@SubEndDate > @EndDate)
SET @EndDate = @SubEndDate
IF (@SubModifyDate > @ModifyDate)
SET @ModifyDate = @SubModifyDate
SET @Managers = @Managers + ', ' + @SubManagers
SET @Users = @Users + ', ' + @SubUsers
FETCH NEXT FROM ProjectCursor INTO @SubId
END
CLOSE ProjectCursor
GO
CREATE PROCEDURE [dbo].[GetProject]
@ProjectId INT
AS
DECLARE @StartDate DATE
DECLARE @EndDate DATE
DECLARE @ModifyDate DATE
DECLARE @Managers NVARCHAR(MAX)
DECLARE @Users NVARCHAR(MAX)
EXEC [dbo].[GetProjectInfo]
@ProjectId,
@StartDate OUTPUT,
@EndDate OUTPUT,
@ModifyDate OUTPUT,
@Managers OUTPUT,
@Users OUTPUT
SELECT p.Title [Project],
ISNULL(pp.Title, '-') [Parent project],
ISNULL([dbo].[FormatUserName](uc.FirstName, uc.MiddleName, uc.LastName), '-')
[Creator],
ISNULL([dbo].[FormatUserName](um.FirstName, um.MiddleName, um.LastName), '-')
[Manager],
@StartDate [Start date],
@EndDate [End date],
@ModifyDate [Modify date],
@Managers [Managers],
@Users [Users]
FROM [dbo].[Project] p
LEFT OUTER JOIN [dbo].[Project] pp
ON p.ParentId = pp.Id
LEFT OUTER JOIN [dbo].[User] uc
ON p.CreatorId = uc.Id
LEFT OUTER JOIN [dbo].[User] um
ON p.ManagerId = um.Id
WHERE p.Id = @ProjectId
GO
CREATE PROCEDURE [dbo].[CheckUser]
@UserId INT
AS
DECLARE @CurrentUserId INT = [dbo].[GetCurrentUserId]()
IF (@CurrentUserId <> @UserId) AND (@CurrentUserId IS NOT NULL OR @UserId IS NOT NULL)
RAISERROR(N'Access denied', 10, 1)
GO
CREATE PROCEDURE [dbo].[GetCategoryTransitions]
@CategoryId INT = NULL
AS
SELECT c.Title [Category],
s1.Title [Initial state],
s2.Title [Final state]
FROM [dbo].[StateTransition] st
JOIN [dbo].[State] s1 ON st.InitialStateId = s1.Id
JOIN [dbo].[State] s2 ON st.FinalStateId = s2.Id
JOIN [dbo].[Category] c ON st.CategoryId = c.Id
WHERE st.CategoryId = @CategoryId OR @CategoryId IS NULL
ORDER BY [Category], [Initial state], [Final state]
GO
CREATE PROCEDURE [dbo].[GetProjectUsers]
@ProjectId INT = NULL
AS
SELECT p.Title [Project],
[dbo].[FormatUserName](u.FirstName, u.MiddleName, u.LastName) [User]
FROM [dbo].[Project] p
JOIN [dbo].[ProjectUser] pu ON pu.ProjectId = p.Id
JOIN [dbo].[User] u ON pu.UserId = u.Id
WHERE pu.ProjectId = @ProjectId OR @ProjectId IS NULL
ORDER BY [Project], [User]
GO
CREATE PROCEDURE [dbo].[GetTaskAuditors]
@TaskId INT = NULL
AS
SELECT t.Title [Task],
[dbo].[FormatUserName](u.FirstName, u.MiddleName, u.LastName) [Auditor]
FROM [dbo].[Task] t JOIN [dbo].[TaskAuditor] ta ON ta.TaskId = t.Id JOIN [dbo].[User] u ON ta.UserId = u.Id
WHERE ta.TaskId = @TaskId OR @TaskId IS NULL
ORDER BY [Task], [Auditor]
GO
CREATE PROCEDURE [dbo].[CreateProject]
@Title NVARCHAR(256),
@ParentId INT,
@Content NVARCHAR(MAX),
@StartDate DATE = NULL,
@EndDate DATE = NULL,
@ManagerId INT = NULL
AS
DECLARE @CreatorId INT = [dbo].[GetCurrentUserId]()
INSERT INTO [dbo].[Project] (Title, ParentId, StartDate, EndDate, CreatorId, ManagerId, Content)
VALUES (@Title, @ParentId, @StartDate, @EndDate, @CreatorId, @ManagerId, @Content)
GO
CREATE PROCEDURE [dbo].[UpdateProject]
@ProjectId INT,
@Content NVARCHAR(MAX),
@StartDate DATE,
@EndDate DATE,
@ManagerId INT
AS
DECLARE @CheckUserId INT
SELECT @CheckUserId = CreatorId FROM [dbo].[Project] WHERE Id = @ProjectId
EXEC [dbo].[CheckUser] @CheckUserId
UPDATE [dbo].[Project]
SET Content = ISNULL(@Content, Content),
StartDate = ISNULL(@StartDate, StartDate),
EndDate = @EndDate,
ManagerId = @ManagerId
WHERE Id = @ProjectId
GO
CREATE PROCEDURE [dbo].[DeleteProject]
@ProjectId INT
AS
DECLARE @CheckUserId INT
SELECT @CheckUserId = CreatorId FROM [dbo].[Project] WHERE Id = @ProjectId
EXEC [dbo].[CheckUser] @CheckUserId
DELETE FROM [dbo].[Project] WHERE Id = @ProjectId
GO
CREATE PROCEDURE [dbo].[CreateComponent]
@ProjectId INT,
@ParentId INT,
@Title NVARCHAR(256)
AS
DECLARE @CheckUserId INT
SELECT @CheckUserId = CreatorId FROM [dbo].[Project] WHERE Id = @ProjectId
EXEC [dbo].[CheckUser] @CheckUserId
INSERT INTO [dbo].[CreateComponent] (ProjectId, ParentId, Title) VALUES (@ProjectId, @ParentId, @Title)
GO
CREATE PROCEDURE [dbo].[DeleteComponent]
@ComponentId INT
AS
DECLARE @CheckUserId INT
SELECT @CheckUserId = p.CreatorId FROM [dbo].[Project] p JOIN [dbo].[Component] c ON c.ProjectId = p.Id WHERE c.Id = @ComponentId
EXEC [dbo].[CheckUser] @CheckUserId
DELETE FROM [dbo].[Component] WHERE Id = @ComponentId
GO
CREATE PROCEDURE [dbo].[CreateMilestone]
@ProjectId INT,
@Title NVARCHAR(256),
@Due DATE
AS
DECLARE @CheckUserId INT
SELECT @CheckUserId = CreatorId FROM [dbo].[Project] WHERE Id = @ProjectId
EXEC [dbo].[CheckUser] @CheckUserId
INSERT INTO [dbo].[Milestone] (ProjectId, Title, Due) VALUES (@ProjectId, @Title, @Due)
GO
CREATE PROCEDURE [dbo].[DeleteMilestone]
@MilestoneId INT
AS
DECLARE @CheckUserId INT
SELECT @CheckUserId = p.CreatorId FROM [dbo].[Project] p JOIN [dbo].[Milestone] m ON m.ProjectId = p.Id WHERE m.Id = @MilestoneId
EXEC [dbo].[CheckUser] @CheckUserId
DELETE FROM [dbo].[Milestone] WHERE Id = @MilestoneId
GO
CREATE PROCEDURE [dbo].[CreateTask]
@Title NVARCHAR(256),
@ParentId INT,
@ProjectId INT,
@CategoryId INT,
@Content NVARCHAR(MAX),
@ComponentId INT = NULL,
@StartDate DATE = NULL,
@EndDate DATE = NULL,
@PerformerId INT = NULL,
@MilestoneId INT = NULL,
@EstimatedTime DECIMAL(6, 1) = NULL
AS
DECLARE @CreatorId INT = [dbo].[GetCurrentUserId]()
DECLARE @CheckUserId INT = -1
SELECT @CheckUserId = UserId FROM [dbo].[ProjectUser] WHERE ProjectId = @ProjectId AND UserId = @CreatorId
EXEC [dbo].[CheckUser] @CheckUserId
DECLARE @StateId INT
SELECT @StateId = InitialStateId FROM [dbo].[Category] WHERE Id = @CategoryId
INSERT INTO [dbo].[Task] (Title, ParentId, ProjectId, CategoryId, StateId, Content, ComponentId, StartDate, EndDate, CreatorId, PerformerId, MilestoneId, EstimatedTime)
VALUES (@Title, @ParentId, @ProjectId, @CategoryId, @StateId, @Content, @ComponentId, @StartDate, @EndDate, @CreatorId, @PerformerId, @MilestoneId, @EstimatedTime)
GO
CREATE PROCEDURE [dbo].[UpdateTask]
@TaskId INT,
@StateId INT,
@StartDate DATE,
@EndDate DATE,
@PerformerId INT,
@MilestoneId INT,
@EstimatedTime DECIMAL(6, 1),
@SpentTime DECIMAL(6, 1),
@Progress TINYINT,
@Content NVARCHAR(MAX)
AS
DECLARE @CheckUserId INT
SELECT @CheckUserId = CreatorId FROM [dbo].[Task] WHERE Id = @TaskId
EXEC [dbo].[CheckUser] @CheckUserId
DECLARE @StateAllowed INT
SELECT @StateAllowed = COUNT(*) FROM [dbo].[StateTransition] st JOIN [dbo].[Task] t ON t.CategoryId = st.CategoryId AND st.InitialStateId = t.StateId WHERE st.FinalStateId = @StateId
IF NOT (@StateAllowed > 0)
RAISERROR(N'State is not allowed', 10, 1)
UPDATE [dbo].[Task]
SET StateId = ISNULL(@StateId, StateId),
StartDate = ISNULL(@StartDate, StartDate),
EndDate = @EndDate,
PerformerId = @PerformerId,
MilestoneId = @MilestoneId,
EstimatedTime = ISNULL(@EstimatedTime, EstimatedTime),
SpentTime = ISNULL(SpentTime + @SpentTime, SpentTime),
Progress = ISNULL(@Progress, Progress),
Content = ISNULL(@Content, Content)
WHERE Id = @TaskId
GO
CREATE PROCEDURE [dbo].[DeleteTask]
@TaskId INT
AS
DECLARE @CheckUserId INT
SELECT @CheckUserId = CreatorId FROM [dbo].[Task] WHERE Id = @TaskId
EXEC [dbo].[CheckUser] @CheckUserId
DELETE FROM [dbo].[Task] WHERE Id = @TaskId
GO
CREATE PROCEDURE [dbo].[CreateTaskCheckItem]
@TaskId INT,
@Title NVARCHAR(256)
AS
DECLARE @CheckUserId INT
SELECT @CheckUserId = CreatorId FROM [dbo].[Task] WHERE Id = @TaskId
EXEC [dbo].[CheckUser] @CheckUserId
INSERT INTO [dbo].[TaskCheckItem] (TaskId, Title) VALUES (@TaskId, @Title)
GO
CREATE PROCEDURE [dbo].[DeleteTaskCheckItem]
@TaskCheckItemId INT
AS
DECLARE @CheckUserId INT
SELECT @CheckUserId = t.CreatorId FROM [dbo].[Task] t JOIN [dbo].[TaskCheckItem] tc ON tc.TaskId = t.Id WHERE tc.Id = @TaskCheckItemId
EXEC [dbo].[CheckUser] @CheckUserId
DELETE FROM [dbo].[TaskCheckItem] WHERE Id = @TaskCheckItemId
GO
CREATE PROCEDURE [dbo].[CreateComment]
@TaskId INT,
@Content NVARCHAR(MAX)
AS
DECLARE @CheckUserId INT
SELECT @CheckUserId = CreatorId FROM [dbo].[Task] WHERE Id = @TaskId
EXEC [dbo].[CheckUser] @CheckUserId
INSERT INTO [dbo].[Comment] (UserId, TaskId, Content) VALUES (@TaskId, [dbo].[GetCurrentUserId](), @Content)
GO
CREATE PROCEDURE [dbo].[DeleteComment]
@CommentId INT
AS
DECLARE @CheckUserId INT
SELECT @CheckUserId = UserId FROM [dbo].[Comment] WHERE Id = @CommentId
EXEC [dbo].[CheckUser] @CheckUserId
DELETE FROM [dbo].[Comment] WHERE Id = @CommentId
GO
Дата добавления: 2015-10-31; просмотров: 87 | Нарушение авторских прав
| <== предыдущая страница | | | следующая страница ==> |
| ПРИЛОЖЕНИЕ 4 ФАЙЛ Triggers.sql | | | ПРИЛОЖЕНИЕ 6 ФАЙЛ Reports.sql |