-- SA. Views for scenario performance indication
USE [envisage]
Go

if exists (select * from sys.views where name='VW_ScenarioPerformance')
begin
  drop view VW_ScenarioPerformance
end
GO

if exists (select * from sys.views where name='VW_ScenarioForecastTotalCost')
begin
  drop view VW_ScenarioForecastTotalCost
end
GO

if exists (select * from sys.views where name='VW_ScenarioActualsTotalCost')
begin
  drop view VW_ScenarioActualsTotalCost
end
GO

if exists (select * from sys.views where name='VW_ScenarioPseudoActuals')
begin
  drop view VW_ScenarioPseudoActuals
end
GO

if exists (select * from sys.views where name='VW_ScenarioActualsReplacePoints')
begin
  drop view VW_ScenarioActualsReplacePoints
end
GO

if exists (select * from sys.views where name='VW_ScenarioRelations')
begin
  drop view VW_ScenarioRelations
end
GO

if exists (select * from sys.views where name='VW_ScenarioActualsPeriod')
begin
  drop view VW_ScenarioActualsPeriod
end
GO

-- Period of actuals existance for every actuals scenario
CREATE VIEW VW_ScenarioActualsPeriod AS
SELECT A.ParentId AS ScenarioId, MIN(A.WeekEndingDate) AS StartDate, MAX(A.WeekEndingDate) AS EndDate
FROM ScenarioDetail A
INNER JOIN Scenario B ON (A.ParentId = B.Id)
WHERE B.[Type] = 9
GROUP BY A.ParentID
GO

-- The corresponding actuals scenario Id for every forecast scenario
CREATE VIEW VW_ScenarioRelations AS
SELECT A.Id AS ForecastScenarioId, A.[Type] AS ForecastScenarioType, B.Id AS ActualsScenarioId
FROM Scenario A
INNER JOIN Scenario B ON (A.ParentId = B.ParentId)
WHERE A.[Type] <> 9
AND B.[Type] = 9
GO

-- Data points for every forecast scenario, that are missing in its corresponding actuals scenario
-- Forecast data from these points of scenario must be added to actuals to fill actuals gaps
CREATE VIEW VW_ScenarioActualsReplacePoints AS
SELECT A.ParentID AS ForecastScenarioId, A.ExpenditureCategoryId, A.WeekEndingDate FROM ScenarioDetail A
INNER JOIN VW_ScenarioRelations C ON (A.ParentID = C.ForecastScenarioId)
INNER JOIN VW_ScenarioActualsPeriod D ON (C.ActualsScenarioId = D.ScenarioId) AND (A.WeekEndingDate <= D.EndDate)
WHERE C.ForecastScenarioType <> 9
EXCEPT 
SELECT M.ForecastScenarioId, K.ExpenditureCategoryId, K.WeekEndingDate FROM ScenarioDetail K
INNER JOIN Scenario L ON (K.ParentID = L.Id)
INNER JOIN VW_ScenarioRelations M ON (K.ParentID = M.ActualsScenarioId)
WHERE L.[Type] = 9
GO

-- Actuals with the forecast values in the actuals gaps for every project
CREATE VIEW VW_ScenarioPseudoActuals AS
SELECT A.Id, C.ActualsScenarioId, A.ExpenditureCategoryId, A.WeekEndingDate, A.Quantity,
	A.Cost, 1 AS IsForecast  
FROM ScenarioDetail A
INNER JOIN VW_ScenarioActualsReplacePoints B ON (A.ParentID = B.ForecastScenarioId)
	AND (A.ExpenditureCategoryId = B.ExpenditureCategoryId)
	AND (A.WeekEndingDate = B.WeekEndingDate)
INNER JOIN VW_ScenarioRelations C ON (A.ParentID = C.ForecastScenarioId)
UNION
SELECT M.Id, M.ParentID, M.ExpenditureCategoryId, M.WeekEndingDate, M.Quantity,
	M.Cost, 0 AS IsForecast FROM ScenarioDetail M
INNER JOIN Scenario N ON (M.ParentID = N.Id)
WHERE N.[Type] = 9
GO

-- Total scenario actuals cost
CREATE VIEW VW_ScenarioActualsTotalCost AS
SELECT A.ActualsScenarioId, SUM(A.Cost) AS TotalCost 
FROM VW_ScenarioPseudoActuals A
GROUP BY A.ActualsScenarioId
GO

CREATE VIEW VW_ScenarioForecastTotalCost AS
SELECT A.ParentID AS ForecastScenarioId, SUM(A.Cost) AS TotalCost 
FROM ScenarioDetail A
INNER JOIN VW_ScenarioRelations B ON (A.ParentID = B.ForecastScenarioId)
INNER JOIN VW_ScenarioActualsPeriod C ON (B.ActualsScenarioId = C.ScenarioId) AND (A.WeekEndingDate <= C.EndDate)
GROUP BY A.ParentID
GO

CREATE VIEW VW_ScenarioPerformance AS
SELECT A.ForecastScenarioId, A.ActualsScenarioId, A.ForecastScenarioType, 
	B.TotalCost AS ForecastTotalCost, C.TotalCost AS ActualsTotalCost,
	(CASE B.TotalCost
		WHEN 0 THEN NULL
		ELSE ABS((B.TotalCost - C.TotalCost) / B.TotalCost)
		END) AS VariationPercent
FROM VW_ScenarioRelations A
LEFT JOIN VW_ScenarioForecastTotalCost B ON (A.ForecastScenarioId = B.ForecastScenarioId)
LEFT JOIN VW_ScenarioActualsTotalCost C ON (A.ActualsScenarioId = C.ActualsScenarioId)
GO