USE [EnVisage]
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)
  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 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 and
		not pr.Id in (
			select sub.Id from
				(select r.Id,
				isnull(((select sum(isnull(UOM.UOMValue, 0)) -- * count(fc2.Id)
				from FiscalCalendar fc2
					left join VW_TeamResource pr on pr.TeamStartDate <= fc2.StartDate
						and (pr.TeamEndDate is null	or pr.TeamEndDate >= fc2.EndDate) and pr.Id = r.Id-- in( '43E77FE8-8986-4B4B-A076-49E9ECA35946','B4CB1413-9F4B-4ACB-BF94-DD3696F36444') --= r.Id
					left join ExpenditureCategory ec on ec.Id=pr.ExpenditureCategoryId 
					left join PeopleResource pr1 on pr1.Id = pr.Id 
					left join UOM on UOM.Id=ec.UOMId
				where fc2.EndDate >= min(fc.EndDate) and fc2.EndDate <= max(fc.EndDate))
				-
				ISNULL((select SUM (isnull(pa.Quantity, 0))
				From FiscalCalendar fc2
					left join VW_TeamResource pr on pr.TeamStartDate <= fc2.StartDate
						and (pr.TeamEndDate is null	or pr.TeamEndDate >= fc2.EndDate) and pr.Id = r.Id
					left join PeopleResource pr1 on pr1.Id = pr.Id 
					left join PeopleResourceAllocation pa on pa.PeopleResourceId=pr.Id and pa.TeamId = pr.TeamId and 
					pa.WeekEndingDate <= fc2.StartDate	and pa.WeekEndingDate >= fc2.EndDate and pr.Id = r.Id
					left join Scenario s on s.Id = pa.ScenarioId and s.Status = 1					
				where fc2.EndDate >= min(fc.EndDate) and fc2.EndDate <= max(fc.EndDate)), 0)
				-
				case when @DisregardNPTime = 0 then
				ISNULL((select SUM (isnull(pt.HoursOff, 0))
				From FiscalCalendar fc2
					left join VW_TeamResource pr on pr.TeamStartDate <= fc2.StartDate
						and (pr.TeamEndDate is null	or pr.TeamEndDate >= fc2.EndDate) and pr.Id = r.Id 
					left join NonProjectTimeAllocation pt on pt.PeopleResourceId=pr.Id 
					and pt.WeekEndingDate >= min(fc.EndDate) and pt.WeekEndingDate <= max(fc.EndDate)
				where fc2.EndDate <= fc2.StartDate and fc2.EndDate >= fc2.EndDate
				), 0) else 0 end
				), 0) as AvailableHours

				from FiscalCalendar fc, PeopleResource r
				where  
				fc.EndDate >= @FCStartDate
				and fc.EndDate <= @FCEndDate
				group by datepart(MONTH,fc.EndDate), datepart(YEAR,fc.EndDate), r.Id
			)sub where sub.AvailableHours < @AvailableHours	
		)
	) 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)
  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 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 and
		not pr.Id in (
			select sub.Id from
				(select 
				r.Id,
				fc.EndDate,
				isnull(((select UOM.UOMValue 
				from UOM, ExpenditureCategory ec, PeopleResource pr1
				join VW_TeamResource pr on fc.StartDate >=pr.TeamStartDate and (pr.TeamEndDate is null or fc.EndDate <= pr.TeamEndDate) and r.Id = pr.Id
				where UOM.Id=ec.UOMId and ec.Id=pr.ExpenditureCategoryId and pr1.Id = pr.Id)
				-
				ISNULL((select SUM (pa.Quantity)
				From PeopleResourceAllocation pa
				join VW_TeamResource pr on fc.StartDate >=pr.TeamStartDate and (pr.TeamEndDate is null or fc.EndDate <= pr.TeamEndDate) and r.Id = pr.Id
					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)
				-
				case when @DisregardNPTime = 0 then
				ISNULL((select SUM (pt.HoursOff)
				From NonProjectTimeAllocation pt
				join VW_TeamResource pr on fc.StartDate >=pr.TeamStartDate and (pr.TeamEndDate is null or fc.EndDate <= pr.TeamEndDate) and r.Id = pr.Id
				Where pt.PeopleResourceId=pr.Id and pt.WeekEndingDate =fc.EndDate), 0) else 0 end --as TrainingHours,
				), 0) as AvailableHours

				from FiscalCalendar fc, PeopleResource r	
				where  fc.EndDate >= @FCStartDate and fc.EndDate <= @FCEndDate and fc.Type = 0	
				group by fc.EndDate, fc.StartDate, r.Id--, pr.ExpenditureCategoryId, pr.TeamId
			)sub  where sub.AvailableHours < @AvailableHours	
		)
	) as vw 
	group by vw.Id, vw.expId, vw.teamId, vw.FirstName, vw.LastName, vw.Name, vw.TeamName--, vw.EndDate
 end
 else
	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 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 @AvailableHours <= (sum(vw.AvailableHours)-sum(vw.AllocatedHours)-sum(vw.NonProjectTimeHours ))) or 
	 (@DisregardNPTime = 1 and @AvailableHours <= (sum(vw.AvailableHours) - sum(vw.AllocatedHours))))
end
RETURN  
END 

GO