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