Changeset 15552 for branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos

Timestamp:

12/20/17 11:27:33 (7 years ago)

Author:

jzenisek

Message:

#2839 worked on permission checks in listing methods

Location:

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

Files:

• AssignedJobResourceDao.cs (modified) (2 diffs)
• ProjectDao.cs (modified) (2 diffs)
• ProjectPermissionDao.cs (modified) (1 diff)

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

@@ -18 +18 @@
     }
 
+    public void DeleteByProjectId(Guid projectId) {
+      DataContext.ExecuteCommand(DeleteByProjectIdQueryString, projectId);
+    }
+
+    public void DeleteByProjectIdAndUserIds(Guid projectId, IEnumerable<Guid> userIds) {
+      string paramUserIds = string.Join(",", userIds.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramUserIds)) {
+        string queryString = string.Format(DeleteByProjectIdAndUserIdsQueryString, projectId, paramUserIds);
+        DataContext.ExecuteCommand(queryString);
+      }
+    }
+
+    public void DeleteByProjectIdsAndUserIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> userIds) {
+      string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x)));
+      string paramUserIds = string.Join(",", userIds.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramUserIds)) {
+        string queryString = string.Format(DeleteByProjectIdsAndUserIdsQueryString, paramProjectIds, paramUserIds);
+        DataContext.ExecuteCommand(queryString);
+      }
+    }
+
+    public void DeleteByProjectIdAndResourceIds(Guid projectId, IEnumerable<Guid> resourceIds) {
+      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
+        string queryString = string.Format(DeleteByProjectIdAndResourceIdsQueryString, projectId, paramResourceIds);
+        DataContext.ExecuteCommand(queryString);
+      }
+    }
+
+    public void DeleteByProjectIdsAndResourceIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> resourceIds) {
+      string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x)));
+      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramResourceIds)) {
+        string queryString = string.Format(DeleteByProjectIdsAndResourceIdsQueryString, paramProjectIds, paramResourceIds);
+        DataContext.ExecuteCommand(queryString);
+      }
+    }
+
     public bool CheckJobGrantedForResource(Guid jobId, Guid resourceId) {
       return DataContext.ExecuteQuery<int>(CheckJobGrantedForResourceQueryString, jobId, resourceId).First() > 0;
@@ -44 +82 @@
 
     #region String queries
+    private const string DeleteByProjectIdQueryString = @"
+      DELETE FROM [AssignedJobResource] ajr
+      WHERE ajr.JobId IN
+        (
+          SELECT j.JobId
+          FROM [Job] j
+          WHERE j.ProjectId = {0}
+        )
+    ";
+    private const string DeleteByProjectIdAndUserIdsQueryString = @"
+      DELETE FROM [AssignedJobResource] ajr
+      WHERE ajr.JobId IN
+        (
+          SELECT j.JobId
+          FROM [Job] j
+          WHERE j.ProjectId = {0}
+          AND j.OwnerUserId IN ({1})
+        )
+    ";
+    private const string DeleteByProjectIdsAndUserIdsQueryString = @"
+      DELETE FROM [AssignedJobResource] ajr
+      WHERE ajr.JobId IN
+        (
+          SELECT j.JobId
+          FROM [Job] j
+          WHERE j.ProjectId IN ({0})
+          AND j.OwnerUserId IN ({1})
+        )
+    ";
+    private const string DeleteByProjectIdAndResourceIdsQueryString = @"
+      DELETE FROM [AssignedJobResource] ajr
+      WHERE ajr.JobId IN
+        (
+          SELECT j.JobId
+          FROM [Job] j
+          WHERE j.ProjectId = {0}
+        )
+      AND ajr.ResourceId IN ({1})
+    ";
+    private const string DeleteByProjectIdsAndResourceIdsQueryString = @"
+      DELETE FROM [AssignedJobResource] ajr
+      WHERE ajr.JobId IN
+        (
+          SELECT j.JobId
+          FROM [Job] j
+          WHERE j.ProjectId IN ({0})
+        )
+      AND ajr.ResourceId IN ({1})
+    ";
     private const string CheckJobGrantedForResourceQueryString = @"
       WITH rbranch AS (

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

@@ -31 +31 @@
     public override Project GetById(Guid id) {
       return GetByIdQuery(DataContext, id);
+    }
+
+    public IEnumerable<Project> GetGrantedProjectsForUser(IEnumerable<Guid> userAndGroupIds) {
+      return GetGrantedProjectsForUserQuery(DataContext, userAndGroupIds);
     }
 
@@ -63 +67 @@
          where project.ProjectId == projectId
          select project).SingleOrDefault());
+    private static readonly Func<DataContext, IEnumerable<Guid>, IEnumerable<Project>> GetGrantedProjectsForUserQuery =
+      CompiledQuery.Compile((DataContext db, IEnumerable<Guid> userAndGroupIds) =>
+      (from project in db.GetTable<Project>()
+       join projectPermission in db.GetTable<ProjectPermission>()
+       on project.ProjectId equals projectPermission.ProjectId
+       where userAndGroupIds.Contains(projectPermission.GrantedUserId)
+       select project).Distinct());
     #endregion
 
-    #region String queries
+      #region String queries
     private const string GetChildProjectsByIdQuery = @"
       WITH ptree AS

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

@@ -88 +88 @@
       AND pp.GrantedUserId IN ({1})
     ";
+    private const string GetGrantedProjectsForUserQueryString = @"
+      SELECT DISTINCT p.*
+      FROM [ProjectPermission] pp, [Project] p
+      WHERE pp.GrantedUserId IN ({0})
+      AND  pp.ProjectId = p.ProjectId
+    ";
     #endregion
   }