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

Timestamp:

12/14/17 15:08:38 (6 years ago)

Author:

jzenisek

Message:

#2839

• updated dbml (removed ResourcePermission and ResourceIds in Job-Table)
• updated Resource and Project Daos

File:

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

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

@@ -33 +33 @@
     }
 
-    public IEnumerable<Project> GetProjectsByChildId(Guid id) {
-      return DataContext.ExecuteQuery<Project>(GetProjectsByChildIdQuery, id);
+    public IEnumerable<Project> GetChildProjectsById(Guid id) {
+      return DataContext.ExecuteQuery<Project>(GetChildProjectsByIdQuery, id);
     }
 
-    public IEnumerable<Guid> GetProjectIdsByChildId(Guid id) {
-      return DataContext.ExecuteQuery<Guid>(GetProjectIdsByChildIdQuery, id);
+    public IEnumerable<Guid> GetChildProjectIdsById(Guid id) {
+      return DataContext.ExecuteQuery<Guid>(GetChildProjectIdsByIdQuery, id);
+    }
+
+    public IEnumerable<Project> GetParentProjectsById(Guid id) {
+      return DataContext.ExecuteQuery<Project>(GetParentProjectsByIdQuery, id);
+    }
+
+    public IEnumerable<Guid> GetParentProjectIdsById(Guid id) {
+      return DataContext.ExecuteQuery<Guid>(GetParentProjectIdsByIdQuery, id);
+    }
+
+    public IEnumerable<Project> GetCurrentAndParentProjectsById(Guid id) {
+      return DataContext.ExecuteQuery<Project>(GetCurrentAndParentProjectsByIdQuery, id);
+    }
+
+    public IEnumerable<Guid> GetCurrentAndParentProjectIdsById(Guid id) {
+      return DataContext.ExecuteQuery<Guid>(GetCurrentAndParentProjectIdsByIdQuery, id);
     }
 
@@ -50 +66 @@
 
     #region String queries
-    private const string GetProjectsByChildIdQuery = @"
-      WITH ptree AS
+    private const string GetChildProjectsByIdQuery = @"
+      WITH ptree AS
       (
         SELECT ProjectId, ParentProjectId
-        FROM [Project]
+        FROM [Project]
         UNION ALL
         SELECT pt.ProjectId, p.ParentProjectId
@@ -62 +78 @@
       SELECT DISTINCT pro.*
       FROM ptree, [Project] pro
-      WHERE ptree.ProjectId = {0}
-      AND ptree.ParentProjectId = pro.ProjectId
+      WHERE ptree.ParentProjectId = {0}
+      AND ptree.ProjectId = pro.ProjectId
     ";
-    private const string GetProjectIdsByChildIdQuery = @"
+    private const string GetChildProjectIdsByIdQuery = @"
       WITH ptree AS
+      (
+        SELECT ProjectId, ParentProjectId
+        FROM [Project]
+        UNION ALL
+        SELECT pt.ProjectId, r.ParentProjectId
+        FROM [Project] r
+        JOIN ptree pt ON pt.ParentProjectId = r.ProjectId AND r.ParentProjectId <> r.ProjectId AND pt.ParentProjectId <> pt.ProjectId
+      )
+      SELECT DISTINCT ptree.ProjectId
+      FROM ptree
+      WHERE ptree.ParentProjectId = {0}
+    ";
+    private const string GetParentProjectsByIdQuery = @"
+      WITH pbranch AS
       (
         SELECT ProjectId, ParentProjectId
         FROM [Project]
         UNION ALL
-        SELECT pt.ProjectId, p.ParentProjectId
+        SELECT pb.ProjectId, p.ParentProjectId
         FROM [Project] p
-        JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId
+        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
       )
-      SELECT DISTINCT ptree.ParentProjectId
-      FROM ptree
-      WHERE ptree.ProjectId = {0}
+      SELECT DISTINCT pro.*
+      FROM pbranch, [Project] pro
+      WHERE pbranch.ProjectId = {0}
+      AND pbranch.ParentProjectId = pro.ProjectId
+    ";
+    private const string GetParentProjectIdsByIdQuery = @"
+      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
+      )
+      SELECT DISTINCT pbranch.ParentProjectId
+      FROM pbranch
+      WHERE pbranch.ProjectId = {0}
+    ";
+    private const string GetCurrentAndParentProjectsByIdQuery = @"
+      WITH pbranch AS
+      (
+        SELECT ProjectId, ParentProjectId
+        FROM Project
+        WHERE ProjectId = {0}
+        UNION ALL
+        SELECT p.ProjectId, p.ParentProjectId
+        FROM Project p
+        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
+      )
+      SELECT DISTINCT pro.*
+      FROM pbranch, Project pro
+      WHERE pbranch.ProjectId = pro.ProjectId
+    ";
+    private const string GetCurrentAndParentProjectIdsByIdQuery = @"
+      WITH pbranch AS
+      (
+        SELECT ProjectId, ParentProjectId
+        FROM Project
+        WHERE ProjectId = {0}
+        UNION ALL
+        SELECT p.ProjectId, p.ParentProjectId
+        FROM Project p
+        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
+      )
+      SELECT DISTINCT pbranch.ProjectId
+      FROM pbranch
     ";
     #endregion