219 lines
8.2 KiB
Transact-SQL
219 lines
8.2 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
IF (NOT EXISTS(SELECT * FROM sys.indexes
|
|
WHERE Name = N'IX_NonProjectTime2ResourcePos'))
|
|
BEGIN
|
|
CREATE NONCLUSTERED INDEX [IX_NonProjectTime2ResourcePos] ON [dbo].[NonProjectTime2Resource]
|
|
(
|
|
[NonProjectTimeId] ASC,
|
|
[Position] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
END
|
|
GO
|
|
|
|
IF (NOT EXISTS(SELECT * FROM sys.indexes
|
|
WHERE Name = N'IX_NonProjectTimeResourceAllocation'))
|
|
BEGIN
|
|
CREATE NONCLUSTERED INDEX [IX_NonProjectTimeResourceAllocation] ON [dbo].[NonProjectTimeResourceAllocation]
|
|
(
|
|
[NonProjectTime2ResourceId] ASC,
|
|
[WeekEndingDate] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
END
|
|
GO
|
|
|
|
IF (NOT EXISTS (SELECT * FROM sys.tables where name='NonProjectTime2Team'))
|
|
BEGIN
|
|
CREATE TABLE [dbo].[NonProjectTime2Team](
|
|
[Id] [uniqueidentifier] NOT NULL,
|
|
[NonProjectTimeId] [uniqueidentifier] NOT NULL,
|
|
[TeamId] [uniqueidentifier] NOT NULL,
|
|
[Position] [int] NOT NULL,
|
|
CONSTRAINT [PK_NonProjectTime2Team] PRIMARY KEY CLUSTERED
|
|
(
|
|
[Id] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
|
|
ALTER TABLE [dbo].[NonProjectTime2Team] ADD CONSTRAINT [DF_NonProjectTime2Team_Id] DEFAULT (newid()) FOR [Id]
|
|
|
|
ALTER TABLE [dbo].[NonProjectTime2Team] ADD CONSTRAINT [DF_NonProjectTime2Team_Position] DEFAULT ((0)) FOR [Position]
|
|
|
|
ALTER TABLE [dbo].[NonProjectTime2Team] WITH CHECK ADD CONSTRAINT [FK_NonProjectTime2Team_NonProjectTime] FOREIGN KEY([NonProjectTimeId])
|
|
REFERENCES [dbo].[NonProjectTime] ([Id])
|
|
|
|
ALTER TABLE [dbo].[NonProjectTime2Team] CHECK CONSTRAINT [FK_NonProjectTime2Team_NonProjectTime]
|
|
|
|
ALTER TABLE [dbo].[NonProjectTime2Team] WITH CHECK ADD CONSTRAINT [FK_NonProjectTime2Team_Team] FOREIGN KEY([TeamId])
|
|
REFERENCES [dbo].[Team] ([Id])
|
|
|
|
ALTER TABLE [dbo].[NonProjectTime2Team] CHECK CONSTRAINT [FK_NonProjectTime2Team_Team]
|
|
|
|
CREATE UNIQUE NONCLUSTERED INDEX [UNIQUE_NonProjectTime2Team] ON [dbo].[NonProjectTime2Team]
|
|
(
|
|
[NonProjectTimeId] ASC,
|
|
[TeamId] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
|
|
CREATE NONCLUSTERED INDEX [UNIQUE_NonProjectTime2Team_1] ON [dbo].[NonProjectTime2Team]
|
|
(
|
|
[NonProjectTimeId] ASC,
|
|
[Position] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
END
|
|
GO
|
|
|
|
IF (NOT EXISTS (SELECT * FROM sys.tables where name='NonProjectTimeTeamAllocation'))
|
|
BEGIN
|
|
CREATE TABLE [dbo].[NonProjectTimeTeamAllocation](
|
|
[Id] [uniqueidentifier] NOT NULL,
|
|
[WeekEndingDate] [datetime] NOT NULL,
|
|
[HoursOff] [int] NOT NULL,
|
|
[NonProjectTime2TeamId] [uniqueidentifier] NOT NULL,
|
|
CONSTRAINT [PK_NonProjectTimeTeamAllocation] PRIMARY KEY CLUSTERED
|
|
(
|
|
[Id] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
|
|
ALTER TABLE [dbo].[NonProjectTimeTeamAllocation] ADD CONSTRAINT [DF_NonProjectTimeTeamAllocation_Id] DEFAULT (newid()) FOR [Id]
|
|
|
|
ALTER TABLE [dbo].[NonProjectTimeTeamAllocation] WITH CHECK ADD CONSTRAINT [FK_NonProjectTimeTeamAllocation_NonProjectTime2Team] FOREIGN KEY([NonProjectTime2TeamId])
|
|
REFERENCES [dbo].[NonProjectTime2Team] ([Id])
|
|
|
|
ALTER TABLE [dbo].[NonProjectTimeTeamAllocation] CHECK CONSTRAINT [FK_NonProjectTimeTeamAllocation_NonProjectTime2Team]
|
|
|
|
CREATE NONCLUSTERED INDEX [IX_NonProjectTimeTeamAllocation] ON [dbo].[NonProjectTimeTeamAllocation]
|
|
(
|
|
[NonProjectTime2TeamId] ASC,
|
|
[WeekEndingDate] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
END
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_NonProjectTimeResourceAllocation')
|
|
BEGIN
|
|
DROP VIEW VW_NonProjectTimeResourceAllocation
|
|
END
|
|
GO
|
|
|
|
CREATE VIEW VW_NonProjectTimeResourceAllocation AS
|
|
SELECT A.NonProjectTimeId, A.PeopleResourceId, B.WeekEndingDate, B.HoursOff, P.ExpenditureCategoryId,
|
|
T.NonProjectTimeCategoryId
|
|
FROM NonProjectTime2Resource A
|
|
INNER JOIN NonProjectTimeResourceAllocation B ON (B.NonProjectTime2ResourceId = A.Id)
|
|
INNER JOIN NonProjectTime T ON (T.Id = A.NonProjectTimeId)
|
|
INNER JOIN PeopleResource P ON (P.Id = A.PeopleResourceId)
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_NonProjectTimeTeamAllocation')
|
|
BEGIN
|
|
DROP VIEW VW_NonProjectTimeTeamAllocation
|
|
END
|
|
GO
|
|
|
|
CREATE VIEW VW_NonProjectTimeTeamAllocation AS
|
|
SELECT A.NonProjectTimeId, A.TeamId, R2T.PeopleResourceId, B.WeekEndingDate, B.HoursOff, P.ExpenditureCategoryId,
|
|
T.NonProjectTimeCategoryId
|
|
FROM NonProjectTime2Team A
|
|
INNER JOIN NonProjectTimeTeamAllocation B ON (B.NonProjectTime2TeamId = A.Id)
|
|
INNER JOIN NonProjectTime T ON (T.Id = A.NonProjectTimeId)
|
|
INNER JOIN PeopleResource2Team R2T ON (R2T.TeamId = A.TeamId) AND (R2T.StartDate < B.WeekEndingDate) AND
|
|
((R2T.EndDate IS NULL) OR (R2T.EndDate >= B.WeekEndingDate))
|
|
INNER JOIN PeopleResource P ON (P.Id = R2T.PeopleResourceId)
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_NonProjectTimeAllocation')
|
|
BEGIN
|
|
DROP VIEW VW_NonProjectTimeAllocation
|
|
END
|
|
GO
|
|
|
|
CREATE VIEW VW_NonProjectTimeAllocation AS
|
|
|
|
WITH MixedAllocationData (NonProjectTimeId, PeopleResourceId, WeekEndingDate, HoursOff, ExpenditureCategoryId,
|
|
NonProjectTimeCategoryId) AS
|
|
(
|
|
SELECT NonProjectTimeId, PeopleResourceId, WeekEndingDate, HoursOff, ExpenditureCategoryId,
|
|
NonProjectTimeCategoryId
|
|
FROM VW_NonProjectTimeResourceAllocation
|
|
UNION ALL
|
|
SELECT NonProjectTimeId, PeopleResourceId, WeekEndingDate, HoursOff, ExpenditureCategoryId,
|
|
NonProjectTimeCategoryId
|
|
FROM VW_NonProjectTimeTeamAllocation
|
|
)
|
|
SELECT NonProjectTimeId, PeopleResourceId, WeekEndingDate, ISNULL(SUM(HoursOff), 0) AS HoursOff, ExpenditureCategoryId,
|
|
NonProjectTimeCategoryId
|
|
FROM MixedAllocationData
|
|
GROUP BY NonProjectTimeId, PeopleResourceId, WeekEndingDate, ExpenditureCategoryId, NonProjectTimeCategoryId
|
|
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.procedures WHERE Name = N'sp_DeleteNonProjectTime')
|
|
DROP PROCEDURE sp_DeleteNonProjectTime
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[sp_DeleteNonProjectTime] (@id uniqueidentifier)
|
|
AS
|
|
BEGIN
|
|
begin transaction
|
|
|
|
delete from NonProjectTimeResourceAllocation
|
|
where NonProjectTime2ResourceId IN (
|
|
select Id from NonProjectTime2Resource where NonProjectTimeId = @id
|
|
)
|
|
|
|
delete from NonProjectTimeTeamAllocation
|
|
where NonProjectTime2TeamId IN (
|
|
select Id from NonProjectTime2Team where NonProjectTimeId = @id
|
|
)
|
|
|
|
delete from NonProjectTime2Resource where NonProjectTimeId = @id
|
|
delete from NonProjectTime2Team where NonProjectTimeId = @id
|
|
delete from NonProjectTime where Id = @id
|
|
|
|
commit transaction
|
|
END
|
|
GO
|
|
|
|
ALTER PROCEDURE [dbo].[sp_DeletePeopleResource] (@id uniqueidentifier)
|
|
AS
|
|
BEGIN
|
|
begin transaction
|
|
|
|
delete from Holiday2PeopleResource where ResourceId = @id
|
|
|
|
-- Delete non-project time for resource
|
|
delete from NonProjectTimeResourceAllocation
|
|
where NonProjectTime2ResourceId IN (
|
|
select Id from NonProjectTime2Resource where PeopleResourceId = @id
|
|
)
|
|
delete from NonProjectTime2Resource where PeopleResourceId = @id
|
|
delete from NonProjectTime where (
|
|
not exists(
|
|
select 1 from NonProjectTime2Resource B where B.NonProjectTimeId = NonProjectTime.Id
|
|
))
|
|
AND
|
|
(not exists(
|
|
select 2 from NonProjectTime2Team C where C.NonProjectTimeId = NonProjectTime.Id
|
|
))
|
|
|
|
delete from PeopleResourceVacation where PeopleResourceId = @id
|
|
delete from PeopleResourceAllocation where PeopleResourceId = @id
|
|
delete from Skill2Resource where ResourceId = @id
|
|
delete from PeopleResource2Team where PeopleResourceId = @id
|
|
delete from PeopleResource where Id = @id
|
|
|
|
commit transaction
|
|
END
|
|
GO
|
|
|
|
IF NOT EXISTS(SELECT * FROM sys.columns
|
|
WHERE Name = N'IsTeamMode' AND Object_ID = Object_ID(N'NonProjectTime'))
|
|
BEGIN
|
|
ALTER table [dbo].[NonProjectTime] Add [IsTeamMode] [bit] NOT NULL default 0
|
|
end
|
|
go
|
|
|