USE [EnVisage]
GO

/****** Object:  View [dbo].[VW_ProjectAccessExtended]    Script Date: 5/26/2016 9:28:33 AM ******/
if exists (select 1 from sys.views where name like 'VW_ProjectAccessExtended')
BEGIN
	DROP VIEW [dbo].[VW_ProjectAccessExtended]
END
GO

/****** Object:  View [dbo].[VW_ProjectAccessExtended]    Script Date: 5/26/2016 9:28:33 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[VW_ProjectAccessExtended] AS
WITH UnitedProjectPermissions (UserId, ProjectId, [Read], [Write])
	AS (select  PA.UserId, PA.ProjectId, PA.[Read], PA.Write
from VW_ProjectAccess PA
WHERE (PA.[Read] = 1) OR (PA.Write = 1)
UNION
select distinct u.Id as UserId, project.Id as ProjectId, 1 as [Read], 1 as [Write]
from [AspNetUsers] u
	inner join PeopleResource pr on pr.Email = u.Email
	inner join PeopleResourceAllocation pra on pra.PeopleResourceId = pr.Id
	inner join Scenario sc on sc.Id = pra.ScenarioId and sc.Status = 1
	inner join Project part on part.Id = sc.ParentId
	inner join Project project on project.Id = part.Id or project.Id = part.ParentProjectId)
select UserId, ProjectId, MAX([Read]) as [Read], MAX(Write) as Write -- USE Maximum permissions this way even if user set project access directly for project which assigned to this user then he will have full access
from UnitedProjectPermissions
group by UserId, ProjectId
GO