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

Timestamp:

12/15/17 17:51:28 (7 years ago)

Author:

jzenisek

Message:

#2839

• worked on Job operations add&update
• worked on ProjectPermission handling
• worked on Project-Resource assignment

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

@@ -45 +45 @@
     }
 
+    public bool CheckProjectGrantedForResources(Guid projectId, IEnumerable<Guid> resourceIds) {
+      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
+        string queryString = string.Format(CheckProjectGrantedForResourcesQueryString, projectId, paramResourceIds);
+        return DataContext.ExecuteQuery<int>(queryString).Count() == 0;
+      }
+      return false;
+    }
+
+    public IEnumerable<Resource> GetAllGrantedResourcesByProjectId(Guid projectId) {
+      return DataContext.ExecuteQuery<Resource>(GetAllGrantedResourcesByProjectIdQueryString, projectId);
+    }
+
+    public IEnumerable<Guid> GetAllGrantedResourceIdsByProjectId(Guid projectId) {
+      return DataContext.ExecuteQuery<Guid>(GetAllGrantedResourceIdsByProjectIdQueryString, projectId);
+    }
+
     #region Compiled queries
     private static readonly Func<DataContext, Guid, IEnumerable<AssignedProjectResource>> GetByProjectIdGetByIdQuery =
@@ -58 +75 @@
          WHERE ProjectId = '{0}'
            AND ResourceId IN ({1});";
+    private const string CheckProjectGrantedForResourcesQueryString = @"
+    WITH 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
+    )
+    SELECT r.ResourceId
+    FROM [Resource] r
+    WHERE r.ResourceId IN ({1})
+    EXCEPT
+    (
+      SELECT rtree.ResourceId
+      FROM rtree, [AssignedProjectResource] apr
+      WHERE rtree.ParentResourceId = apr.ResourceId
+      AND apr.ProjectId = {0}
+      UNION
+      SELECT apr.ResourceId
+      FROM [AssignedProjectResource] apr
+      WHERE apr.ProjectId = {0}
+    )
+    ";
+    private const string GetAllGrantedResourcesByProjectIdQueryString = @"
+      WITH 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
+      )
+      SELECT res.*
+      FROM rtree, [AssignedProjectResource] apr, [Resource] res
+      WHERE rtree.ParentResourceId = apr.ResourceId
+      AND rtree.ResourceId = res.ResourceId
+      AND apr.ProjectId = {0}
+      UNION
+      SELECT res.*
+      FROM [AssignedProjectResource] apr, [Resource] res
+      WHERE apr.ResourceId = res.ResourceId
+      AND apr.ProjectId = {0}
+    ";
+    private const string GetAllGrantedResourceIdsByProjectIdQueryString = @"
+    WITH 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
+    )
+    SELECT rtree.ResourceId
+    FROM rtree, [AssignedProjectResource] apr
+    WHERE rtree.ParentResourceId = apr.ResourceId
+    AND apr.ProjectId = {0}
+    UNION
+    SELECT apr.ResourceId
+    FROM [AssignedProjectResource] apr
+    WHERE apr.ProjectId = {0}
+    ";
     #endregion
   }