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

Timestamp:

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

Author:

jzenisek

Message:

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

File:

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

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
   }