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

Timestamp:

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

Author:

jzenisek

Message:

#2839

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

Location:

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

Files:

• AssignedJobResourceDao.cs (modified) (5 diffs)
• AssignedProjectResourceDao.cs (modified) (2 diffs)
• ProjectPermissionDao.cs (modified) (2 diffs)

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

@@ -22 +22 @@
     }
 
-    public bool CheckJobGrantedForResources(Guid jobId, Guid[] resourceIds) {
-      return DataContext.ExecuteQuery<int>(CheckJobGrantedForResourcesQueryString).Count() > 0;
+    public bool CheckJobGrantedForResources(Guid jobId, IEnumerable<Guid> resourceIds) {
+      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
+        string queryString = string.Format(CheckJobGrantedForResourcesQueryString, jobId, paramResourceIds);
+        return DataContext.ExecuteQuery<int>(queryString).Count() == 0;
+      }
+      return false;
     }
 
@@ -43 +48 @@
         SELECT ResourceId, ParentResourceId
         FROM [Resource]
-        WHERE ResourceId = {0}
+        WHERE ResourceId = {1}
         UNION ALL
         SELECT r.ResourceId, r.ParentResourceId
@@ -52 +57 @@
       FROM rbranch, AssignedJobResource ajr
       WHERE rbranch.ResourceId = ajr.ResourceId
-      AND ajr.JobId = {1}
+      AND ajr.JobId = {0}
     ";
     private const string CheckTaskGrantedForResourceQueryString = @"
@@ -58 +63 @@
         SELECT ResourceId, ParentResourceId
         FROM [Resource]
-        WHERE ResourceId = {0}
+        WHERE ResourceId = {1}
         UNION ALL
         SELECT r.ResourceId, r.ParentResourceId
@@ -68 +73 @@
       WHERE rbranch.ResourceId = ajr.ResourceId
       AND ajr.JobId = t.JobId
-      AND t.JobId = {1}
+      AND t.JobId = {0}
     ";
     private const string CheckJobGrantedForResourcesQueryString = @"

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
   }

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

@@ -37 +37 @@
     }
 
-    public void DeleteByProjectAndGrantedUserId(Guid projectId, IEnumerable<Guid> grantedUserId) {
-      string paramIds = string.Join(",", grantedUserId.Select(x => string.Format("'{0}'", x)));
+    public bool CheckUserGrantedForProject(Guid projectId, IEnumerable<Guid> userAndGroupIds) {
+      string paramUserAndGroupIds = string.Join(",", userAndGroupIds.Select(x => string.Format("'{0}'", x)));
+      if(!string.IsNullOrWhiteSpace(paramUserAndGroupIds)) {
+        string queryString = string.Format(CheckUserGrantedForProjectQueryString, projectId, paramUserAndGroupIds);
+        return DataContext.ExecuteQuery<int>(queryString).First() > 0;
+      }
+      return false;
+    }
+
+    public void DeleteByProjectIdAndGrantedUserIds(Guid projectId, IEnumerable<Guid> grantedUserIds) {
+      string paramIds = string.Join(",", grantedUserIds.Select(x => string.Format("'{0}'", x)));
       if (!string.IsNullOrWhiteSpace(paramIds)) {
-        string query = string.Format(DeleteByGrantedUserQuery, projectId, paramIds);
+        string query = string.Format(DeleteByProjectIdAndGrantedUserIdsQueryString, projectId, paramIds);
+        DataContext.ExecuteCommand(query);
+      }
+    }
+
+    public void DeleteByProjectIdsAndGrantedUserIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> grantedUserIds) {
+      string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x)));
+      string paramUserIds = string.Join(",", grantedUserIds.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramUserIds)) {
+        string query = string.Format(DeleteByProjectIdsAndGrantedUserIdsQueryString, paramProjectIds, paramUserIds);
         DataContext.ExecuteCommand(query);
       }
@@ -54 +72 @@
 
     #region String queries
-    private const string DeleteByGrantedUserQuery =
-      @"DELETE FROM [ProjectPermission]
-         WHERE ProjectId = '{0}'
-           AND GrantedUserId IN ({1});";
+    private const string DeleteByProjectIdAndGrantedUserIdsQueryString = @"
+      DELETE FROM [ProjectPermission]
+      WHERE ProjectId = '{0}'
+      AND GrantedUserId IN ({1});
+    ";
+    private const string DeleteByProjectIdsAndGrantedUserIdsQueryString = @"
+      DELETE FROM [ProjectPermission]
+      WHERE ProjectId IN '({0})'
+      AND GrantedUserId IN ({1});
+    ";
+    private const string CheckUserGrantedForProjectQueryString = @"
+      SELECT COUNT(pp.ProjectId)
+      FROM [ProjectPermission] pp
+      WHERE pp.ProjectId = {0}
+      AND pp.GrantedUserId IN ({1})
+    ";
     #endregion
   }