375 lines
34 KiB
Transact-SQL
375 lines
34 KiB
Transact-SQL
Use [EnVisage]
|
||
|
||
IF NOT EXISTS(SELECT * FROM sys.columns
|
||
WHERE Name = N'NonProjectTime2ResourceId' AND Object_ID = Object_ID(N'NonProjectTimeResourceAllocation'))
|
||
BEGIN
|
||
ALTER TABLE NonProjectTimeResourceAllocation
|
||
ADD NonProjectTime2ResourceId uniqueidentifier NULL
|
||
END
|
||
GO
|
||
|
||
IF EXISTS(SELECT * FROM sys.columns
|
||
WHERE Name = N'NonProjectTimeId' AND Object_ID = Object_ID(N'NonProjectTimeResourceAllocation'))
|
||
AND
|
||
EXISTS(SELECT * FROM sys.columns
|
||
WHERE Name = N'PeopleResourceId' AND Object_ID = Object_ID(N'NonProjectTimeResourceAllocation'))
|
||
BEGIN
|
||
DECLARE @qryText nvarchar(1000);
|
||
SET @qryText =
|
||
'UPDATE A
|
||
SET A.NonProjectTime2ResourceId = R.Id
|
||
FROM NonProjectTimeResourceAllocation A
|
||
INNER JOIN NonProjectTime2Resource R ON (R.NonProjectTimeId = A.NonProjectTimeId) AND (R.PeopleResourceId = A.PeopleResourceId)
|
||
WHERE
|
||
A.NonProjectTime2ResourceId IS NULL'
|
||
|
||
EXECUTE SP_EXECUTESQL @qryText
|
||
END
|
||
GO
|
||
|
||
ALTER TABLE NonProjectTimeResourceAllocation
|
||
ALTER COLUMN NonProjectTime2ResourceId uniqueidentifier NOT NULL
|
||
|
||
|
||
IF (NOT EXISTS(SELECT * FROM sys.foreign_keys
|
||
WHERE Name = N'FK_NonProjectTimeResourceAllocation_NonProjectTime2Resource'))
|
||
BEGIN
|
||
ALTER TABLE [dbo].[NonProjectTimeResourceAllocation] WITH CHECK ADD CONSTRAINT [FK_NonProjectTimeResourceAllocation_NonProjectTime2Resource] FOREIGN KEY([NonProjectTime2ResourceId])
|
||
REFERENCES [dbo].[NonProjectTime2Resource] ([Id])
|
||
|
||
ALTER TABLE [dbo].[NonProjectTimeResourceAllocation] CHECK CONSTRAINT [FK_NonProjectTimeResourceAllocation_NonProjectTime2Resource]
|
||
END
|
||
GO
|
||
|
||
IF EXISTS(SELECT * FROM sys.foreign_keys
|
||
WHERE Name = N'FK_NonProjectTimeAllocation_NonProjectTime')
|
||
BEGIN
|
||
ALTER TABLE NonProjectTimeResourceAllocation
|
||
DROP CONSTRAINT FK_NonProjectTimeAllocation_NonProjectTime
|
||
END
|
||
GO
|
||
|
||
IF EXISTS(SELECT * FROM sys.foreign_keys
|
||
WHERE Name = N'FK_NonProjectTimeAllocation_PeopleResource')
|
||
BEGIN
|
||
ALTER TABLE NonProjectTimeResourceAllocation
|
||
DROP CONSTRAINT FK_NonProjectTimeAllocation_PeopleResource
|
||
END
|
||
GO
|
||
|
||
IF EXISTS(SELECT * FROM sys.objects
|
||
WHERE Name = N'UNIQUE_NonProjectTimeAllocation')
|
||
BEGIN
|
||
ALTER TABLE NonProjectTimeResourceAllocation
|
||
DROP CONSTRAINT UNIQUE_NonProjectTimeAllocation
|
||
END
|
||
GO
|
||
|
||
IF EXISTS(SELECT * FROM sys.columns
|
||
WHERE Name = N'NonProjectTimeId' AND Object_ID = Object_ID(N'NonProjectTimeResourceAllocation'))
|
||
BEGIN
|
||
ALTER TABLE NonProjectTimeResourceAllocation
|
||
DROP COLUMN NonProjectTimeId
|
||
END
|
||
GO
|
||
|
||
IF EXISTS(SELECT * FROM sys.columns
|
||
WHERE Name = N'PeopleResourceId' AND Object_ID = Object_ID(N'NonProjectTimeResourceAllocation'))
|
||
BEGIN
|
||
ALTER TABLE NonProjectTimeResourceAllocation
|
||
DROP COLUMN PeopleResourceId
|
||
END
|
||
GO
|
||
|
||
IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_NonProjectTimeAllocation')
|
||
BEGIN
|
||
DROP VIEW VW_NonProjectTimeAllocation
|
||
END
|
||
GO
|
||
|
||
CREATE VIEW VW_NonProjectTimeAllocation 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.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 NonProjectTime2Resource where NonProjectTimeId = @id
|
||
delete from NonProjectTime where Id = @id
|
||
|
||
commit transaction
|
||
END
|
||
GO
|
||
|
||
ALTER FUNCTION [dbo].[resourceAvailability_f](@StartDate datetime, @EndDate datetime, @Per char(1), @AvailableHours float, @DisregardNPTime bit)
|
||
|
||
RETURNS @restable TABLE
|
||
(
|
||
FirstName nvarchar(1000) ,
|
||
LastName nvarchar(1000) ,
|
||
ExpenditureCategory nvarchar(800) ,
|
||
AvailableHours float ,
|
||
AllocatedHours float ,
|
||
NonProjectTimeHours float ,
|
||
ResourceAvailability float ,
|
||
TeamName nvarchar(400) ,
|
||
TeamId varchar(36) ,
|
||
ExpenditureCategoryId varchar(36)
|
||
)
|
||
AS
|
||
BEGIN
|
||
declare @FCStartDate datetime
|
||
declare @FCEndDate datetime
|
||
|
||
select
|
||
@FCStartDate = MIN(EndDate) ,
|
||
@FCEndDate = MAX(EndDate)
|
||
from dbo.FiscalCalendar
|
||
where Type = 0
|
||
and (StartDate <= @StartDate or StartDate <= @EndDate)
|
||
and (EndDate >= @StartDate or EndDate >= @EndDate)
|
||
and AdjustingPeriod = 0
|
||
and NonWorking = 0
|
||
|
||
if (@Per = 1) -- month
|
||
begin
|
||
INSERT INTO @restable
|
||
select vw.FirstName, vw.LastName, vw.Name as ExpenditureCategory,
|
||
sum(vw.AvailableHours) as AvailableHours, sum(vw.AllocatedHours) as AllocatedHours,
|
||
sum(vw.NonProjectTimeHours ) as NonProjectTimeHours,
|
||
sum(vw.AvailableHours)-sum(vw.AllocatedHours)-sum(vw.NonProjectTimeHours) as ResourceAvailability,
|
||
vw.TeamName, vw.teamId, vw.expId
|
||
From(
|
||
select
|
||
pr.Id,
|
||
ec2.Id as expId,
|
||
t.Id as teamId,
|
||
pr.FirstName,
|
||
pr.LastName,
|
||
ec2.Name,
|
||
t.Name as TeamName,
|
||
fc.EndDate,
|
||
|
||
((select UOM.UOMValue
|
||
from UOM, ExpenditureCategory ec
|
||
where UOM.Id=ec.UOMId and ec.Id=pr.ExpenditureCategoryId)
|
||
) as AvailableHours,
|
||
|
||
ISNULL((select SUM (pa.Quantity)
|
||
From PeopleResourceAllocation pa
|
||
inner join Scenario s on s.Id = pa.ScenarioId and s.Status = 1
|
||
Where pa.PeopleResourceId=pr.Id and pa.TeamId = pr.TeamId and pa.WeekEndingDate = fc.EndDate), 0) as AllocatedHours,
|
||
|
||
ISNULL((select SUM (pt.HoursOff)
|
||
From VW_NonProjectTimeAllocation pt
|
||
Where pt.PeopleResourceId=pr.Id and pt.WeekEndingDate = fc.EndDate), 0) as NonProjectTimeHours
|
||
|
||
from FiscalCalendar fc
|
||
join VW_TeamResource pr on fc.StartDate >= pr.TeamStartDate and (pr.TeamEndDate is null or fc.EndDate <= pr.TeamEndDate)
|
||
join ExpenditureCategory ec2 on ec2.Id=pr.ExpenditureCategoryId
|
||
join Team t on t.Id = pr.TeamId
|
||
where fc.EndDate >= @FCStartDate and fc.EndDate <= @FCEndDate and fc.Type = 0 and
|
||
not exists (select 1 from (
|
||
select sub.Id, sub.TeamId from
|
||
(select r.Id, r.TeamId, datepart(MONTH,fc.EndDate) as FCMonth, datepart(YEAR,fc.EndDate) as FCYear,
|
||
isnull(((select sum(isnull(UOM.UOMValue, 0))
|
||
from FiscalCalendar fc_sub
|
||
inner join VW_TeamResource pr_sub on pr_sub.TeamStartDate <= fc_sub.StartDate
|
||
and (pr_sub.TeamEndDate is null or pr_sub.TeamEndDate >= fc_sub.EndDate)
|
||
inner join ExpenditureCategory ec on ec.Id=pr_sub.ExpenditureCategoryId
|
||
left join UOM on UOM.Id=ec.UOMId
|
||
where fc_sub.EndDate >= min(fc.EndDate) and fc_sub.EndDate <= max(fc.EndDate) and pr_sub.Id = r.Id and pr_sub.TeamId = r.TeamId)
|
||
-
|
||
ISNULL((select SUM (isnull(pa.Quantity, 0))
|
||
From FiscalCalendar fc_sub
|
||
left join VW_TeamResource pr_sub on pr_sub.TeamStartDate <= fc_sub.StartDate
|
||
and (pr_sub.TeamEndDate is null or pr_sub.TeamEndDate >= fc_sub.EndDate) and pr_sub.Id = r.Id
|
||
left join PeopleResourceAllocation pa on pa.PeopleResourceId=pr_sub.Id and pa.TeamId = pr_sub.TeamId and pr_sub.TeamId = r.TeamId and
|
||
pa.WeekEndingDate <= fc_sub.StartDate and pa.WeekEndingDate >= fc_sub.EndDate
|
||
left join Scenario s on s.Id = pa.ScenarioId and s.Status = 1
|
||
where fc_sub.EndDate >= min(fc.EndDate) and fc_sub.EndDate <= max(fc.EndDate)), 0)
|
||
-
|
||
case when @DisregardNPTime = 0 then
|
||
ISNULL((select SUM (isnull(pt.HoursOff, 0))
|
||
From FiscalCalendar fc_sub
|
||
left join VW_TeamResource pr_sub on pr_sub.TeamStartDate <= fc_sub.StartDate
|
||
and (pr_sub.TeamEndDate is null or pr_sub.TeamEndDate >= fc_sub.EndDate) and pr_sub.Id = r.Id and pr_sub.TeamId = r.TeamId
|
||
left join VW_NonProjectTimeAllocation pt on pt.PeopleResourceId=pr_sub.Id
|
||
and pt.WeekEndingDate >= min(fc.EndDate) and pt.WeekEndingDate <= max(fc.EndDate)
|
||
where fc_sub.EndDate <= fc_sub.StartDate and fc_sub.EndDate >= fc_sub.EndDate
|
||
), 0) else 0 end
|
||
), 0) as AvailableHours
|
||
|
||
from FiscalCalendar fc, VW_TeamResource r
|
||
where fc.EndDate >= @FCStartDate and fc.EndDate <= @FCEndDate
|
||
group by datepart(MONTH,fc.EndDate), datepart(YEAR,fc.EndDate), r.Id, r.TeamId
|
||
)sub where sub.AvailableHours < @AvailableHours
|
||
) query where query.Id = pr.Id and query.TeamId = pr.TeamId)
|
||
) as vw
|
||
group by vw.Id, vw.expId, vw.teamId, vw.FirstName, vw.LastName, vw.Name, vw.TeamName--, vw.EndDate
|
||
end
|
||
else if(@Per= 2) -- week
|
||
begin
|
||
INSERT INTO @restable
|
||
|
||
select
|
||
vw.FirstName, vw.LastName, vw.Name as ExpenditureCategory,
|
||
sum(vw.AvailableHours) as AvailableHours, sum(vw.AllocatedHours) as AllocatedHours,
|
||
sum(vw.NonProjectTimeHours ) as NonProjectTimeHours,
|
||
sum(vw.AvailableHours)-sum(vw.AllocatedHours)-sum(vw.NonProjectTimeHours) as ResourceAvailability,
|
||
vw.TeamName, vw.teamId, vw.expId
|
||
From(
|
||
select
|
||
pr.Id,
|
||
ec2.Id as expId,
|
||
t.Id as teamId,
|
||
pr.FirstName,
|
||
pr.LastName,
|
||
ec2.Name,
|
||
t.Name as TeamName,
|
||
fc.EndDate,
|
||
|
||
((select UOM.UOMValue
|
||
from UOM, ExpenditureCategory ec
|
||
where UOM.Id=ec.UOMId and ec.Id=pr.ExpenditureCategoryId)
|
||
) as AvailableHours,
|
||
|
||
ISNULL((select SUM (pa.Quantity)
|
||
From PeopleResourceAllocation pa
|
||
inner join Scenario s on s.Id = pa.ScenarioId and s.Status = 1
|
||
Where pa.PeopleResourceId=pr.Id and pa.TeamId = pr.TeamId and pa.WeekEndingDate = fc.EndDate), 0) as AllocatedHours,
|
||
|
||
ISNULL((select SUM (pt.HoursOff)
|
||
From VW_NonProjectTimeAllocation pt
|
||
Where pt.PeopleResourceId=pr.Id and pt.WeekEndingDate = fc.EndDate), 0) as NonProjectTimeHours
|
||
|
||
from FiscalCalendar fc
|
||
join VW_TeamResource pr on fc.StartDate >= pr.TeamStartDate and (pr.TeamEndDate is null or fc.EndDate <= pr.TeamEndDate)
|
||
join ExpenditureCategory ec2 on ec2.Id=pr.ExpenditureCategoryId
|
||
join Team t on t.Id = pr.TeamId
|
||
where fc.EndDate >= @FCStartDate and fc.EndDate <= @FCEndDate and fc.Type = 0 and
|
||
not exists (select 1 from (
|
||
select sub.Id, sub.TeamId from
|
||
(select
|
||
r.Id,
|
||
r.TeamId,
|
||
fc_sub.EndDate,
|
||
isnull(((select UOM.UOMValue
|
||
from UOM, ExpenditureCategory ec
|
||
join VW_TeamResource pr_sub on fc_sub.StartDate >=pr_sub.TeamStartDate and (pr_sub.TeamEndDate is null or fc_sub.EndDate <= pr_sub.TeamEndDate)
|
||
where UOM.Id=ec.UOMId and ec.Id=pr_sub.ExpenditureCategoryId and r.Id = pr_sub.Id and r.TeamId = pr_sub.TeamId)
|
||
-
|
||
ISNULL((select SUM (pa.Quantity)
|
||
From PeopleResourceAllocation pa
|
||
inner join VW_TeamResource pr_sub on fc_sub.StartDate >=pr_sub.TeamStartDate and (pr_sub.TeamEndDate is null or fc_sub.EndDate <= pr_sub.TeamEndDate)
|
||
and pa.PeopleResourceId=pr_sub.Id and pa.TeamId = pr_sub.TeamId
|
||
inner join Scenario s on s.Id = pa.ScenarioId and s.Status = 1
|
||
Where r.Id = pr_sub.Id and pa.WeekEndingDate = fc_sub.EndDate), 0)
|
||
-
|
||
case when @DisregardNPTime = 0 then
|
||
ISNULL((select SUM (pt.HoursOff)
|
||
From VW_NonProjectTimeAllocation pt
|
||
join VW_TeamResource pr_sub on fc_sub.StartDate >=pr_sub.TeamStartDate and (pr_sub.TeamEndDate is null or fc_sub.EndDate <= pr_sub.TeamEndDate) and pt.PeopleResourceId=pr_sub.Id
|
||
Where r.Id = pr_sub.Id and pt.WeekEndingDate=fc_sub.EndDate), 0) else 0 end --as TrainingHours,
|
||
), 0) as AvailableHours
|
||
from FiscalCalendar fc_sub, VW_TeamResource r
|
||
where fc_sub.EndDate >= @FCStartDate and fc_sub.EndDate <= @FCEndDate and fc_sub.Type = 0
|
||
group by fc_sub.EndDate, fc_sub.StartDate, r.Id, r.TeamId
|
||
)sub where sub.AvailableHours < @AvailableHours
|
||
) query where query.Id = pr.Id and query.TeamId = pr.TeamId)
|
||
) as vw
|
||
group by vw.Id, vw.expId, vw.teamId, vw.FirstName, vw.LastName, vw.Name, vw.TeamName--, vw.EndDate
|
||
end
|
||
else -- @Per=0 total
|
||
begin
|
||
INSERT INTO @restable
|
||
|
||
select
|
||
vw.FirstName, vw.LastName, vw.Name as ExpenditureCategory,
|
||
sum(vw.AvailableHours) as AvailableHours, sum(vw.AllocatedHours) as AllocatedHours,
|
||
sum(vw.NonProjectTimeHours ) as NonProjectTimeHours,
|
||
sum(vw.AvailableHours)-sum(vw.AllocatedHours)-sum(vw.NonProjectTimeHours ) as ResourceAvailability,
|
||
vw.TeamName, vw.teamId, vw.expId
|
||
|
||
From(
|
||
|
||
select
|
||
pr.Id,
|
||
ec2.Id as expId,
|
||
t.Id as teamId,
|
||
pr.FirstName,
|
||
pr.LastName,
|
||
ec2.Name,
|
||
t.Name as TeamName,
|
||
fc.EndDate,
|
||
|
||
((select UOM.UOMValue
|
||
from UOM, ExpenditureCategory ec
|
||
where UOM.Id=ec.UOMId and ec.Id=pr.ExpenditureCategoryId)
|
||
) as AvailableHours,
|
||
|
||
ISNULL((select SUM (pa.Quantity)
|
||
From PeopleResourceAllocation pa
|
||
inner join Scenario s on s.Id = pa.ScenarioId and s.Status = 1
|
||
Where pa.PeopleResourceId=pr.Id and pa.TeamId = pr.TeamId and pa.WeekEndingDate = fc.EndDate), 0) as AllocatedHours,
|
||
|
||
ISNULL((select SUM (pt.HoursOff)
|
||
From VW_NonProjectTimeAllocation pt
|
||
Where pt.PeopleResourceId=pr.Id and pt.WeekEndingDate = fc.EndDate), 0) as NonProjectTimeHours
|
||
|
||
from FiscalCalendar fc
|
||
join VW_TeamResource pr on fc.StartDate >= pr.TeamStartDate and (pr.TeamEndDate is null or fc.EndDate <= pr.TeamEndDate) --(not(fc.StartDate > pr.EndDate) and not(fc.EndDate < pr.StartDate))
|
||
join ExpenditureCategory ec2 on ec2.Id=pr.ExpenditureCategoryId
|
||
join Team t on t.Id = pr.TeamId
|
||
where fc.EndDate >= @FCStartDate and fc.EndDate <= @FCEndDate and fc.Type = 0
|
||
) as vw
|
||
group by vw.Id, vw.expId, vw.teamId, vw.FirstName, vw.LastName, vw.Name, vw.TeamName, vw.AvailableHours--, vw.EndDate
|
||
having
|
||
((@DisregardNPTime = 0 and ISNULL(@AvailableHours, -1000000) <= (sum(vw.AvailableHours)-sum(vw.AllocatedHours)-sum(vw.NonProjectTimeHours ))) or
|
||
(@DisregardNPTime = 1 and ISNULL(@AvailableHours, -1000000) <= (sum(vw.AvailableHours) - sum(vw.AllocatedHours))))
|
||
end
|
||
RETURN
|
||
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
|
||
)
|
||
|
||
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 |