USE [EnVisage]
GO
/****** Object:  StoredProcedure [dbo].[sp_BuildRaceMatrixTeams]    Script Date: 07/12/2016 9:18:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:           <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_BuildRaceMatrixTeams]

@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,DateAdd(day,s.Duration*7,@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,
ProjectId uniqueidentifier,
WeekOrdinal int
)

insert into @RequiredCapacityByEC
select distinct sd.Quantity as RequiredCapacity,ExpenditureCategoryId as ExpendtiureCategory ,sd.WeekEndingDate, p.Id, sd.WeekOrdinal 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 ,p.Id,sd.WeekOrdinal,Quantity
order by sd.WeekEndingDate 

 declare @ProjectId uniqueidentifier
 declare @I int=1
 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..#RequiredCapacityByEC') IS NOT NULL
	drop table #RequiredCapacityByEC
 

create table #RequiredCapacityByEC (
RequiredCapacity decimal(18,6),
ExpenditureCategoryId uniqueidentifier,
ProjectID uniqueidentifier,
WeekOrdinal int
)

insert into #RequiredCapacityByEC
select RequiredCapacity as RequiredCapacity,ExpenditureCategoryId, ProjectID, WeekOrdinal from @RequiredCapacityByEC where ProjectID=@ProjectId group by ProjectID,ExpenditureCategoryId, WeekOrdinal,RequiredCapacity order by ExpenditureCategoryId,WeekOrdinal 



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

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

set @query = N'SELECT ProjectID,ExpenditureCategoryId,' + @cols + N' from 
             (
                select ProjectID,ExpenditureCategoryId,RequiredCapacity, WeekOrdinal 
                from #RequiredCapacityByEC
            ) x
            pivot 
            (
               max( RequiredCapacity)
                for WeekOrdinal in (' + @cols + N')
            ) p '
select @cols as PRColumnNames
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,
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.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,
ExpenditureCategoryId uniqueidentifier,
WeekEndingDate DateTime,
MaxHours int,
isReal bit
)
insert into @AvailableCapacityRR
select SUM(AvailableCapacity) AvailableCapacity,WeekOrdinal , ExpenditureCategoryId,WeekEndingDate,0 ,1 from @AvailableCapacity   GROUP BY ExpenditureCategoryId,WeekEndingDate,WeekOrdinal order by WeekEndingDate
 


 
 


 

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),
 ExpenditureCategoryId uniqueidentifier,
 isReal bit,
 WeekEndingDate datetime
)
 
insert into #AvailableCapacityRR
select (datediff(day,@RaceStartDate,WeekEndingDate)/7 )+1, AvailableCapacity,ExpenditureCategoryId,isReal,WeekEndingDate from @AvailableCapacityRR where WeekEndingDate <= @RaceEndDate group by ExpenditureCategoryId,WeekEndingDate,WeekOrdinal,isReal,AvailableCapacity order by ExpenditureCategoryId,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 ExpenditureCategoryId, isReal,' + @cols + N' from 
             (
                select distinct ExpenditureCategoryId ,isReal,AvailableCapacity, WeekOrdinal 
                from  #AvailableCapacityRR 
            ) x
            pivot 
            (
               sum(AvailableCapacity)
                for WeekOrdinal in (' + @cols + N')
            ) p '

exec sp_executesql @query;end