58 lines
1.7 KiB
Transact-SQL
58 lines
1.7 KiB
Transact-SQL
USE [envisage]
|
|
Go
|
|
|
|
if exists (select * from sys.views where name='VW_ProjectAccessPermissions')
|
|
begin
|
|
drop view VW_ProjectAccessPermissions
|
|
end
|
|
GO
|
|
|
|
if exists (select * from sys.views where name='VW_ProjectAccessRead')
|
|
begin
|
|
drop view VW_ProjectAccessRead
|
|
end
|
|
GO
|
|
|
|
if exists (select * from sys.views where name='VW_ProjectAccessWrite')
|
|
begin
|
|
drop view VW_ProjectAccessWrite
|
|
end
|
|
GO
|
|
|
|
if exists (select * from sys.views where name='VW_ProjectAccessByUser')
|
|
begin
|
|
drop view VW_ProjectAccessByUser
|
|
end
|
|
GO
|
|
|
|
if exists (select * from sys.views where name='VW_ProjectAccess')
|
|
begin
|
|
drop view VW_ProjectAccess
|
|
end
|
|
GO
|
|
|
|
CREATE VIEW VW_ProjectAccess AS
|
|
SELECT K.UserId, L.ProjectId, L.[Read], L.Write FROM AspNetUserRoles K
|
|
INNER JOIN ProjectAccess L ON (L.PrincipalId = K.RoleId)
|
|
WHERE NOT EXISTS(
|
|
SELECT B.* FROM ProjectAccess B
|
|
WHERE (B.PrincipalId = K.UserId) AND (B.ProjectId = L.ProjectId)
|
|
)
|
|
UNION
|
|
SELECT M.PrincipalId, M.ProjectId, M.[Read], M.Write FROM ProjectAccess M
|
|
INNER JOIN AspNetUsers N ON (N.Id = M.PrincipalId)
|
|
GO
|
|
|
|
CREATE VIEW VW_ProjectAccessByUser AS
|
|
select A.*, B.UserId,
|
|
ISNULL(C.Name, '') AS StatusName, ISNULL(D.Name, '') AS CompanyName, ISNULL(E.Name, '') AS ClientName,
|
|
ISNULL(F.Name, '') AS TypeName, G.Id AS ActiveScenarioId, ISNULL(G.Name, '') AS ActiveScenarioName,
|
|
B.[Read], B.Write from Project A
|
|
INNER JOIN VW_ProjectAccess B ON (B.ProjectId = A.Id)
|
|
LEFT JOIN [Status] C ON (C.Id = A.StatusId)
|
|
LEFT JOIN Company D ON (D.Id = A.CompanyId)
|
|
LEFT JOIN Client E ON (E.Id = A.ClientId)
|
|
LEFT JOIN [Type] F ON (F.Id = A.TypeId)
|
|
LEFT JOIN Scenario G ON (G.ParentId = A.Id) AND (G.[Type] = 2) AND (G.[Status] = 1) -- Poftfolio scenario & active
|
|
WHERE (B.[Read] = 1) OR (B.Write = 1)
|
|
GO |