Changeset 15540 for branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedProjectResourceDao.cs

Timestamp:

12/18/17 17:38:05 (6 years ago)

Author:

jzenisek

Message:

#2839 added checks for the administration of project-resource assignments

File:

• branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedProjectResourceDao.cs (modified) (2 diffs)

branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedProjectResourceDao.cs

@@ -61 +61 @@
       return DataContext.ExecuteQuery<Guid>(GetAllGrantedResourceIdsByProjectIdQueryString, projectId);
     }
+
+    public IEnumerable<Guid> GetAllGrantedResourceIdsOfOwnedParentProjects(Guid projectId, Guid userId) {
+      return DataContext.ExecuteQuery<Guid>(GetAllGrantedResourceIdsOfOwnedParentProjectsQueryString, projectId, userId);
+    }
+
 
     #region Compiled queries
@@ -140 +145 @@
     WHERE apr.ProjectId = {0}
     ";
+    private const string GetAllGrantedResourceIdsOfOwnedParentProjectsQueryString = @"
+      WITH pbranch AS
+      (
+        SELECT ProjectId, ParentProjectId
+        FROM [Project]
+        UNION ALL
+        SELECT pb.ProjectId, p.ParentProjectId
+        FROM [Project] p
+        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
+      ),
+      rtree AS
+      (
+        SELECT ResourceId, ParentResourceId
+        FROM [Resource]
+        UNION ALL
+        SELECT rt.ResourceId, r.ParentResourceId
+        FROM [Resource] r
+        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rt.ParentResourceId <> rt.ResourceId
+      )
+      SELECT DISTINCT rtree.ResourceId
+      FROM pbranch, rtree, [Project] pro, [AssignedProjectResource] apr
+      WHERE pbranch.ProjectId = {0}
+      AND pbranch.ParentProjectId = pro.ProjectId
+      AND pro.OwnerUserId = {1}
+      AND pbranch.ParentProjectId = apr.ProjectId
+      AND apr.ResourceId = rtree.ParentResourceId
+    ";
     #endregion
   }