Changeset 15540 for branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess

Timestamp:

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

Author:

jzenisek

Message:

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

Location:

branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3

Files:

• Daos/AssignedProjectResourceDao.cs (modified) (2 diffs)
• Daos/ProjectDao.cs (modified) (1 diff)
• Daos/ResourceDao.cs (modified) (2 diffs)
• HeuristicLab.Services.Hive.DataAccess-3.3.csproj (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
   }

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

@@ -153 +153 @@
       FROM pbranch
     ";
+    private const string GetNearestOwnedParentProjectByIdQuery = @"
+      WITH pbranch AS
+      (
+        SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance
+        FROM [Project]
+        WHERE ProjectId = {0}
+        UNION ALL
+        SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1
+        FROM [Project] p
+        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
+      )
+      SELECT TOP(1) pro.*
+      FROM pbranch, [Project] pro
+      WHERE pbranch.ParentProjectId = pro.ProjectId
+      AND pro.OwnerUserId = {1}
+      ORDER BY pbranch.Distance
+    ";
+    private const string GetFarestOwnedParentProjectIdByIdQuery = @"
+      WITH pbranch AS
+      (
+        SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance
+        FROM [Project]
+        WHERE ProjectId = {0}
+        UNION ALL
+        SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1
+        FROM [Project] p
+        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
+      )
+      SELECT TOP(1) pro.*
+      FROM pbranch, [Project] pro
+      WHERE pbranch.ParentProjectId = pro.ProjectId
+      AND pro.OwnerUserId = {1}
+      ORDER BY pbranch.Distance DESC
+    ";
     #endregion
   }

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

@@ -35 +35 @@
     public Resource GetByName(string name) {
       return GetByNameQuery(DataContext, name);
+    }
+
+    public bool CheckExistence(IEnumerable<Guid> ids) {
+      string paramResourceIds = string.Join(",", ids.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
+        string queryString = string.Format(CountExistenceQuery, paramResourceIds);
+        return DataContext.ExecuteQuery<int>(queryString).Count() == ids.Count();
+      }
+      return false;
     }
 
@@ -80 +89 @@
 
     #region String queries
+    private const string CountExistenceQuery = @"
+      SELECT COUNT(DISTINCT r.ResourceId)
+      FROM [Resource] r
+      WHERE r.ResourceId IN ({0})
+    ";
     private const string GetChildResourcesByIdQuery = @"
       WITH rtree AS

branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/HeuristicLab.Services.Hive.DataAccess-3.3.csproj

@@ -104 +104 @@
   <ItemGroup>
     <None Include="Plugin.cs.frame" />
+    <Compile Include="Daos\AssignedJobResourceDao.cs" />
     <Compile Include="Daos\AssignedProjectResourceDao.cs" />
     <Compile Include="Daos\AssignedTaskResourceDao.cs" />
@@ -123 +124 @@
     <Compile Include="Daos\RequiredPluginDao.cs" />
     <Compile Include="Daos\ResourceDao.cs" />
-    <Compile Include="Daos\ResourcePermissionDao.cs" />
     <Compile Include="Daos\SlaveDao.cs" />
     <Compile Include="Daos\SlaveGroupDao.cs" />