USE [EnVisage]
GO

/****** Object:  UserDefinedFunction [dbo].[resourceAvailability_f]    Script Date: 21.04.2016 22:43:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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 NonProjectTimeResourceAllocation 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 NonProjectTimeResourceAllocation 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 NonProjectTimeResourceAllocation 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 NonProjectTimeResourceAllocation 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 NonProjectTimeResourceAllocation 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