87 lines
3.4 KiB
Transact-SQL
87 lines
3.4 KiB
Transact-SQL
use EnVisage
|
|
--WARNING!!! will take long to complete
|
|
--select Teams w/o capacity scenarios
|
|
select t.Id
|
|
into #ttmp
|
|
from Team t
|
|
where t.ActualCapacityScenarioId is null
|
|
DECLARE @t XML = (SELECT * FROM #ttmp FOR XML AUTO)
|
|
--iterate through #ttmp and create capacity scenarios
|
|
declare @teamId uniqueidentifier
|
|
declare capcursor cursor for select Id from #ttmp
|
|
DECLARE @scenarioIdTable TABLE (newId UNIQUEIDENTIFIER)
|
|
open capcursor
|
|
fetch next from capcursor into @teamId
|
|
while @@FETCH_STATUS = 0
|
|
begin
|
|
declare @newScenarioId uniqueidentifier
|
|
set @newScenarioId = newid();
|
|
insert into Scenario(Id, ParentId, Type, Name, ProjectedRevenue, StartDate, Color, FreezeRevenue, GrowthScenario)
|
|
values (@newScenarioId, null, 14, (select Name from Team where Id = @teamId) + 'Capacity', 0, getdate(), '', 0, 0)
|
|
update Team set ActualCapacityScenarioId = @newScenarioId where Id = @teamId
|
|
print 'team ' + cast(@teamId as nvarchar(50))+ ' - capacity scenario added'
|
|
--getting people resorces
|
|
declare @prId uniqueidentifier
|
|
declare @expCatId uniqueidentifier
|
|
select pr.Id
|
|
into #prtmp
|
|
from PeopleResource pr
|
|
where pr.TeamId = @teamId and pr.IsActiveEmployee = 1
|
|
DECLARE @pr XML = (SELECT * FROM #prtmp FOR XML AUTO)
|
|
declare prcursor cursor for select Id from #prtmp
|
|
open prcursor
|
|
fetch next from prcursor into @prId
|
|
while @@FETCH_STATUS = 0
|
|
begin
|
|
set @expCatId = (select ExpenditureCategoryId from PeopleResource where Id = @prId)
|
|
declare @uom decimal
|
|
set @uom = (select UOMValue from UOM where Id = (select UOMId from ExpenditureCategory where Id = @expCatId))
|
|
--getting fiscal calendar data
|
|
select fc.EndDate
|
|
into #fctmp
|
|
from FiscalCalendar fc
|
|
where fc.Type = 0 and fc.EndDate >= (select StartDate from PeopleResource pr where pr.Id = @prId) and fc.EndDate <= (select EndDate from PeopleResource pr where pr.Id = @prId)
|
|
DECLARE @fc XML = (SELECT * FROM #fctmp FOR XML AUTO)
|
|
--cycling through FC data
|
|
declare @endDate DateTime
|
|
declare fccursor cursor for select EndDate from #fctmp
|
|
open fccursor
|
|
fetch next from fccursor into @endDate
|
|
while @@FETCH_STATUS = 0
|
|
begin
|
|
declare @rate decimal
|
|
set @rate = (select top(1) Rate from Rate where ExpenditureCategoryId = @expCatId and StartDate <= @endDate and EndDate >= @endDate and Rate.Type = 0)
|
|
|
|
--filling ScenarioDetails
|
|
--checking if ScenarioDetails already there
|
|
if exists (select Id from ScenarioDetail where ParentID = @newScenarioId and ExpenditureCategoryId = @expCatId and WeekEndingDate = @endDate )
|
|
begin
|
|
declare @quant decimal
|
|
declare @cost decimal
|
|
declare @sdId uniqueidentifier
|
|
select @sdId = Id, @quant = Quantity, @cost = Cost from ScenarioDetail where ParentID = @newScenarioId and ExpenditureCategoryId = @expCatId and WeekEndingDate = @endDate
|
|
update ScenarioDetail set Quantity = @quant + @uom , Cost = @cost + @uom * ISNULL(@rate, 0), LastUpdate = getdate()
|
|
where Id = @sdId
|
|
end
|
|
else
|
|
begin
|
|
insert into ScenarioDetail(Id, ParentId, ExpenditureCategoryId, WeekEndingDate, Quantity, LastUpdate, WeekOrdinal, Cost)
|
|
values (newid(), @newScenarioId, @expCatId, @endDate, @uom, getdate(), null, @uom * ISNULL(@rate, 0))
|
|
end
|
|
fetch next from fccursor into @endDate
|
|
end
|
|
close fccursor;
|
|
deallocate fccursor;
|
|
drop table #fctmp
|
|
fetch next from prcursor into @prId
|
|
end
|
|
close prcursor;
|
|
deallocate prcursor;
|
|
drop table #prtmp
|
|
delete from @scenarioIdTable
|
|
fetch next from capcursor into @teamId
|
|
end
|
|
close capcursor;
|
|
deallocate capcursor;
|
|
drop table #ttmp
|