USE [EnVisage]
GO
/****** Object:  StoredProcedure [dbo].[sp_BuildRaceMatrix]    Script Date: 06/29/2016 12:23:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS(SELECT * FROM sys.procedures WHERE Name = N'sp_BuildRaceMatrix')
DROP PROCEDURE sp_BuildRaceMatrix
GO

-- =============================================
-- Author:           <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
create PROCEDURE [dbo].[sp_BuildRaceMatrix]

@TeamID uniqueidentifier,
@RaceStartDate datetime,
@RaceEndDate  datetime,
@threshold int

as
begin
 
 declare @Projects table(
Idx int NOT NULL identity(1,1),
Id uniqueidentifier,
StartDate datetime,
DurationInWeeks int,
[Priority]  int,
Probability int,
deadline datetime,
RaceOrder int,
RaceStartDate datetime,
Score2 decimal(18,4),
EndDate datetime, 
isPinned bit ,
Name nvarchar(200),
DeadLineMultiple decimal(4,2)
)
declare @MaxDeadline datetime

insert into @Projects
select p.Id, s.StartDate,s.Duration,p.[Priority] ,p.Probability*100,isnull(p.Deadline,@RaceEndDate),0,s.StartDate,(p.Probability/p.[Priority])*100,s.EndDate, CAST(CASE WHEN s.StartDate < getdate() THEN 1 ELSE 0 END AS bit),
p.Name, dbo.fn_GetProjectScoreWeight( @RaceStartDate,@RaceEndDate,isnull(p.Deadline,@RaceEndDate),s.Duration)
from Project p
join Scenario s on s.ParentId=p.Id and s.Type=2 and s.Status=1
join Team2Project t2p on t2p.ProjectId=p.Id
where t2p.TeamId=@TeamID

 

update @Projects set isPinned =1, DeadLineMultiple=1.0 where DeadLineMultiple = 0.0
update @Projects set Score2=Score2*DeadLineMultiple+(DurationInWeeks/100)

select @MaxDeadline=Max(deadline) from @Projects
declare @MaxDuration int=0
select @MaxDuration=Max(DurationInWeeks) from @Projects

select * from @Projects

--team wide totals by EC not seperated by project
declare @RequiredCapacityByEC table(
RequiredCapacity decimal(18,6),
ExpenditureCategoryId uniqueidentifier, 
WeekendingDate datetime
)

insert into @RequiredCapacityByEC
select sum(sd.Quantity ) as RequiredCapacity,ExpenditureCategoryId as ExpendtiureCategory ,sd.WeekEndingDate from ScenarioDetail sd
join Scenario s on s.Id=sd.ParentID  and Type=2
join @Projects p on p.Id = s.ParentId
group by sd.ExpenditureCategoryId ,sd.WeekEndingDate 
order by sd.WeekEndingDate 



--project level actuals by Resource
declare  @RequiredCapacityByRS table (
RequiredCapacity decimal(18,6),
PeopleResourceID uniqueidentifier,
ExpenditureCategoryId uniqueidentifier,
WeekEndingDate DateTime,
ProjectID uniqueidentifier,
WeekOrdinal int
)

insert into @RequiredCapacityByRS
select distinct u.UOMValue as RequiredCapacity,pra.PeopleResourceID, pra.ExpenditureCategoryId as ExpendtiureCategory,pra.WeekEndingDate , p.Id as ProjectId, sd.WeekOrdinal from PeopleResourceAllocation pra
join Scenario s on s.Id=pra.ScenarioID   
join @Projects p on p.Id = s.ParentId
join ScenarioDetail sd on sd.ParentId=s.Id and sd.WeekEndingDate =pra.WeekEndingDate and sd.ExpenditureCategoryId=pra.ExpenditureCategoryId
join ExpenditureCategory ec on ec.Id= sd.ExpenditureCategoryId
join UOM u on u.Id=ec.UOMId

group by pra.ExpenditureCategoryId, pra.WeekEndingDate,p.Id,PeopleResourceID,WeekOrdinal,u.UOMValue
order by p.Id,pra.WeekEndingDate



update r set r.RequiredCapacity=ec.RequiredCapacity from @RequiredCapacityByRS r
join @RequiredCapacityByEC ec on ec.ExpenditureCategoryId = r.ExpenditureCategoryId and ec.WeekendingDate= r.WeekEndingDate
where ec.RequiredCapacity < r.RequiredCapacity


 declare @ProjectId uniqueidentifier
 declare @I int=0
 declare @IMax int
 select @IMax=max(Idx) from @Projects

 while(@I <=@IMax)
 begin

 
select @ProjectID =Id from @Projects where Idx=@I

IF OBJECT_ID('tempdb..#RequiredCapacityByRS') IS NOT NULL
	drop table #RequiredCapacityByRS
 

create table #RequiredCapacityByRS (
RequiredCapacity decimal(18,6),
PeopleResourceID uniqueidentifier,
ProjectID uniqueidentifier,
WeekOrdinal int
)

insert into #RequiredCapacityByRS
select sum(RequiredCapacity) as RequiredCapacity,PeopleResourceID, ProjectID, WeekOrdinal from @RequiredCapacityByRS where ProjectID=@ProjectId group by ProjectID,PeopleResourceID, WeekOrdinal order by PeopleResourceID,WeekOrdinal 



DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct  ',' + QUOTENAME(WeekOrdinal) 
                    from #RequiredCapacityByRS
                    group by WeekOrdinal,PeopleResourceID,ProjectID
                     
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ProjectID,PeopleResourceID,' + @cols + N' from 
             (
                select ProjectID,PeopleResourceID,RequiredCapacity, WeekOrdinal 
                from #RequiredCapacityByRS
            ) x
            pivot 
            (
               max( RequiredCapacity)
                for WeekOrdinal in (' + @cols + N')
            ) p '

exec sp_executesql @query;
  set @I=@I+1
end

select @RaceEndDate=DateAdd(day,@MaxDuration*7,@RaceEndDate);

declare @AvailableCapacity  table (
AvailableCapacity decimal(18,6),
WeekOrdinal int,
 PeopleResourceId uniqueidentifier,
ExpenditureCategoryId uniqueidentifier,
WeekEndingDate DateTime,
MaxHours int,
isReal bit
)
insert into @AvailableCapacity
select MAX (isnull(cap.QuantityAdjusted, 0)) - SUM(isnull(npa.HoursOff, 0)) as AvailableCapacity,fc.PeriodInt, pr.Id, pr.ExpenditureCategoryId, fc.EndDate, cap.QuantityDirect as MaxHours,1
from FiscalCalendar fc
       left join VW_TeamResource pr on pr.TeamId = @TeamID and fc.StartDate >= pr.TeamStartDate and (pr.TeamEndDate is null or fc.EndDate <= pr.TeamEndDate)
       left join VW_ActualCapacityAdjusted      cap on cap.PeopleResourceId = pr.Id and fc.EndDate = cap.WeekEndingDate and cap.TeamId = @TeamID
       left join VW_NonProjectTimeAllocation npa on pr.Id=npa.PeopleResourceID and npa.TeamId = @TeamID and npa.WeekEndingDate = fc.EndDate
where fc.EndDate>= @RaceStartDate and fc.EndDate <= @RaceEndDate and fc.EndDate <= @MaxDeadline and fc.Type=0
group by pr.Id, pr.ExpenditureCategoryId, fc.EndDate, cap.QuantityDirect, fc.PeriodInt

 declare @AvailableCapacityRR  table (
AvailableCapacity decimal(18,6),
WeekOrdinal int,
 PeopleResourceId uniqueidentifier,
ExpenditureCategoryId uniqueidentifier,
WeekEndingDate DateTime,
MaxHours int,
isReal bit
)
insert into @AvailableCapacityRR
select SUM(AvailableCapacity) AvailableCapacity,WeekOrdinal ,NewID(), ExpenditureCategoryId,WeekEndingDate,0 ,0 from @AvailableCapacity   GROUP BY ExpenditureCategoryId,WeekEndingDate,WeekOrdinal order by WeekEndingDate
 


insert into @AvailableCapacity
select * from @AvailableCapacityRR 


 

IF OBJECT_ID('tempdb..#AvailableCapacityRR') IS NOT NULL
	drop table #AvailableCapacityRR
--project level actuals by Resource
 
create table #AvailableCapacityRR  (
WeekOrdinal int  ,
AvailableCapacity decimal(18,6),
 PeopleResourceId uniqueidentifier,
 isReal bit,
 WeekEndingDate datetime
)
 
insert into #AvailableCapacityRR
select (datediff(day,@RaceStartDate,WeekEndingDate)/7 )+1, sum(AvailableCapacity) as AvailableCapacity,PeopleResourceID,isReal,WeekEndingDate from @AvailableCapacity where WeekEndingDate <= @RaceEndDate group by PeopleResourceID,WeekEndingDate,WeekOrdinal,isReal order by PeopleResourceID,WeekEndingDate 
declare @coltbl table(
 Weekordinal int
 )
 insert into @coltbl
 select distinct WeekOrdinal from #AvailableCapacityRR
 
select @cols = STUFF((SELECT distinct  ',' + QUOTENAME(WeekOrdinal) 
                    from @coltbl
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
		select @cols as ColumnNames
set @query = N'SELECT PeopleResourceID, isReal,' + @cols + N' from 
             (
                select distinct PeopleResourceID ,isReal,AvailableCapacity, WeekOrdinal 
                from  #AvailableCapacityRR 
            ) x
            pivot 
            (
               sum(AvailableCapacity)
                for WeekOrdinal in (' + @cols + N')
            ) p '

exec sp_executesql @query;
end