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

Timestamp:

01/05/18 15:13:25 (6 years ago)

Author:

jzenisek

Message:

#2839 worked on service side mgmt of project-resource assignments and project-user permissions

File:

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

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

@@ -33 +33 @@
     }
 
-    public IEnumerable<Project> GetGrantedProjectsForUser(IEnumerable<Guid> userAndGroupIds) {
-      return GetGrantedProjectsForUserQuery(DataContext, userAndGroupIds);
+    public IEnumerable<Project> GetUsageGrantedProjectsForUser(IEnumerable<Guid> userAndGroupIds) {
+      string paramUserAndGroupIds = string.Join(",", userAndGroupIds.ToList().Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramUserAndGroupIds)) {
+        string queryString = string.Format(GetUsageGrantedProjectsForUserQueryString, paramUserAndGroupIds);
+        return DataContext.ExecuteQuery<Project>(queryString);
+      }
+      return Enumerable.Empty<Project>();
+    }
+
+    public IEnumerable<Project> GetAdministrationGrantedProjectsForUser(Guid userId) {
+      return DataContext.ExecuteQuery<Project>(GetAdministrationGrantedProjectsForUserQueryString, userId);
     }
 
@@ -67 +76 @@
          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 GetUsageGrantedProjectsForUserQueryString = @"
+      SELECT DISTINCT p.*
+      FROM [Project] p, [ProjectPermission] pp
+      WHERE p.ProjectId = pp.ProjectId
+      AND pp.GrantedUserId IN ({0})
+    ";
+
+    private const string GetAdministrationGrantedProjectsForUserQueryString = @"
+      WITH ptree AS
+      (
+        SELECT ProjectId, ParentProjectId
+        FROM [Project]
+        UNION ALL
+        SELECT pt.ProjectId, p.ParentProjectId
+        FROM [Project] p
+        JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId
+      )
+      SELECT DISTINCT parent.*
+      FROM [Project] parent
+      WHERE parent.OwnerUserId = {0}
+      UNION
+      SELECT DISTINCT child.* 
+      FROM ptree, [Project] parent, [Project] child
+      WHERE ptree.ParentProjectId = parent.ProjectId
+      AND ptree.ProjectId = child.ProjectId
+      AND parent.OwnerUserId = {0}
+    ";
+
     private const string GetChildProjectsByIdQuery = @"
       WITH ptree AS