use EnVisage
go

begin tran
--STEP 1: delete all actual capacity scenarios and underlying data and clear Team.ActualCapacityScenarioId
update Team set ActualCapacityScenarioId = null
go
declare @scenId uniqueidentifier
declare oldscenariodel cursor for select Id from Scenario where Type = 14
open oldscenariodel 
fetch next from oldscenariodel into @scenId
while @@FETCH_STATUS = 0
begin
	exec [sp_DeleteScenario] @scenId
	fetch next from oldscenariodel into @scenId
end
close oldscenariodel
deallocate oldscenariodel
go

--STEP 2: Prepare data
select t.Name as TeamName, t.Id as TeamId, fc.EndDate, ec.Id as CategoryId, ec.Name, sum(uom.UOMValue) as NumberHours,
(select top 1 Rate from Rate where Rate.Type = 0 and Rate.ExpenditureCategoryId = ec.Id 
	and Rate.StartDate <= fc.EndDate and Rate.EndDate >= fc.EndDate order by Rate.StartDate desc) as Rate
into #tmp
from PeopleResource pr
inner join Team t on t.Id = pr.TeamId
inner join ExpenditureCategory ec on ec.id = pr.ExpenditureCategoryId
left join UOM on UOM.Id = ec.UOMId
inner join FiscalCalendar fc on fc.Type = 0 and fc.AdjustingPeriod = 0 and fc.NonWorking = 0 and fc.StartDate >= pr.StartDate and fc.EndDate <= pr.EndDate
where pr.IsActiveEmployee = 1
group by t.Name, t.Id, fc.EndDate, ec.Id, ec.Name
order by t.Id, CategoryId, fc.EndDate

--STEP 3: loop through teams and create new scenarios
declare @teamId uniqueidentifier
declare @scenId uniqueidentifier
declare teamscursor cursor for select Id from Team
open teamscursor 
fetch next from teamscursor into @teamId
while @@FETCH_STATUS = 0
begin
	set @scenId = newid()
	insert into Scenario(Id, ParentId, Type, Name, ProjectedRevenue, StartDate, Color, FreezeRevenue, GrowthScenario)
	values (@scenId, null, 14, (select Name from Team where Id = @teamId) + ' Actual Capacity', 0, getdate(), '', 0, 0)
	update Team set ActualCapacityScenarioId = @scenId where Id = @teamId
	print 'team ' + cast(@teamId as nvarchar(50))+ ' - capacity scenario added'	
	
	insert into ScenarioDetail(Id, ParentId, ExpenditureCategoryId, WeekEndingDate, Quantity, WeekOrdinal, Cost) 
	select newid(), @scenId, CategoryId, EndDate, NumberHours, 0, NumberHours * ISNULL (coalesce(Rate, 0), 0)
	from #tmp 
	where TeamId = @teamId

	update Scenario set StartDate = (select min(WeekEndingDate) from ScenarioDetail Where ParentId = Scenario.Id) where Id = @scenId

	fetch next from teamscursor into @teamId
end
close teamscursor
deallocate teamscursor

drop table #tmp
go

commit tran