USE [EnVisage]
GO

if exists (select 1 from sys.views where name like 'VW_Holidays2TeamsAndExpenditures')
begin
	DROP VIEW [dbo].[VW_Holidays2TeamsAndExpenditures]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[VW_Holidays2TeamsAndExpenditures] AS
-- Holidays for specified Teams and ECs
(SELECT H.HolidayGroupId, T.Id as TeamId, EC.Id as ExpenditureCategoryId
FROM Holiday H
	LEFT JOIN Holiday2Team H2T ON (H2T.HolidayId = H.Id)
	LEFT JOIN Holiday2ExpenditureCategory H2E ON (H2E.HolidayId = H.Id)
	LEFT JOIN Team T on T.Id = H2T.TeamId or (H2T.Id IS NULL AND H2E.Id IS NOT NULL)
	LEFT JOIN ExpenditureCategory EC on EC.Id = H2E.ExpenditureCategoryId or (H2E.Id IS NULL AND H2T.Id IS NOT NULL)
WHERE (H.CompanyImpact = 0) AND (H.IsInclude = 1) AND (H.WorkingDays = 0)
UNION

-- Holidays for all company resources
SELECT H.HolidayGroupId, T.Id as TeamId, EC.Id as ExpenditureCategoryId
FROM Holiday H
	LEFT JOIN Team T ON (1 = 1)
	LEFT JOIN ExpenditureCategory EC ON (1 = 1)
WHERE (H.CompanyImpact = 1) AND (H.WorkingDays = 0)
UNION

-- Holidays for all resources, except specified resources, teams and ECs
SELECT H.HolidayGroupId, T.Id as TeamId, EC.Id as ExpenditureCategoryId
FROM Holiday H
	LEFT JOIN Team T ON (1 = 1)
	LEFT JOIN ExpenditureCategory EC ON (1 = 1)
WHERE (H.CompanyImpact = 0) AND (H.IsInclude = 0) AND (H.WorkingDays = 0)
)

EXCEPT

(SELECT H.HolidayGroupId, H2T.TeamId, EC.Id as ExpenditureCategoryId
FROM Holiday H
	INNER JOIN Holiday2Team H2T ON (H2T.HolidayId = H.Id)
	LEFT JOIN ExpenditureCategory EC ON (1 = 1)
WHERE (H.CompanyImpact = 0) AND (H.IsInclude = 0) AND (H.WorkingDays = 0)
UNION
SELECT H.HolidayGroupId, T.Id as TeamId, H2E.ExpenditureCategoryId
FROM Holiday H
	INNER JOIN Holiday2ExpenditureCategory H2E ON (H2E.HolidayId = H.Id)
	LEFT JOIN Team T ON (1 = 1)
WHERE (H.CompanyImpact = 0) AND (H.IsInclude = 0) AND (H.WorkingDays = 0)
)
GO

if exists (select 1 from sys.views where name like 'VW_PlanningCapacityWorkingDays')
begin
	DROP VIEW [dbo].[VW_PlanningCapacityWorkingDays]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW VW_PlanningCapacityWorkingDays AS
select B.HolidayGroupId, B.WeekEndingDate, A.ExpenditureCategoryId, A.TeamId, WW.WorkDaysAmount, 
	MIN(CAST((B.Sunday & WW.Sunday) as decimal)) AS Sunday, 
	MIN(CAST((B.Monday & WW.Monday) as decimal)) AS Monday,
	MIN(CAST((B.Tuesday & WW.Tuesday) as decimal)) AS Tuesday, 
	MIN(CAST((B.Wednesday & WW.Wednesday) as decimal)) AS Wednesday, 
	MIN(CAST((B.Thursday & WW.Thursday) as decimal)) AS Thursday, 
	MIN(CAST((B.Friday & WW.Friday) as decimal)) AS Friday,
	MIN(CAST((B.Saturday & WW.Saturday) as decimal)) AS Saturday
from VW_Holidays2TeamsAndExpenditures A
	INNER JOIN HolidayAllocation B ON (B.HolidayGroupId = A.HolidayGroupId)
	INNER JOIN VW_WorkWeek WW ON WW.Id = (select top 1 Id from WorkWeek where IsSystem=1)
GROUP BY B.HolidayGroupId, B.WeekEndingDate, A.ExpenditureCategoryId, A.TeamId, WW.WorkDaysAmount
GO


if exists (select 1 from sys.views where name like 'VW_PlanningCapacityAdjusted')
begin
	DROP VIEW [dbo].[VW_PlanningCapacityAdjusted]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[VW_PlanningCapacityAdjusted]
AS
SELECT distinct sc.Id as ScenarioId, WD.ExpenditureCategoryId, WD.TeamId, WD.WeekEndingDate,
	(CASE (WD.WorkDaysAmount)
	WHEN 0 THEN 0
	ELSE (ISNULL(WD.Sunday + WD.Monday + WD.Tuesday + WD.Wednesday + WD.Thursday + WD.Friday + WD.Saturday, 0) / WD.WorkDaysAmount)
	END) AS AdjustmentKoeff
FROM Scenario sc
	inner join ScenarioDetail sd on sd.ParentID = sc.Id
	inner join Team t on t.PlannedCapacityScenarioId = sc.Id
	left join VW_PlanningCapacityWorkingDays WD on WD.TeamId = t.Id and WD.WeekEndingDate = sd.WeekEndingDate
where WD.WeekEndingDate IS NOT NULL

GO