Free cookie consent management tool by TermsFeed Policy Generator

source: branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectDao.cs @ 15540

Last change on this file since 15540 was 15540, checked in by jzenisek, 5 years ago

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

File size: 6.9 KB
Line 
1#region License Information
2/* HeuristicLab
3 * Copyright (C) 2002-2017 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
4 *
5 * This file is part of HeuristicLab.
6 *
7 * HeuristicLab is free software: you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation, either version 3 of the License, or
10 * (at your option) any later version.
11 *
12 * HeuristicLab is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with HeuristicLab. If not, see <http://www.gnu.org/licenses/>.
19 */
20#endregion
21
22using System;
23using System.Collections.Generic;
24using System.Data.Linq;
25using System.Linq;
26
27namespace HeuristicLab.Services.Hive.DataAccess.Daos {
28  public class ProjectDao : GenericDao<Guid, Project> {
29    public ProjectDao(DataContext dataContext) : base(dataContext) { }
30
31    public override Project GetById(Guid id) {
32      return GetByIdQuery(DataContext, id);
33    }
34
35    public IEnumerable<Project> GetChildProjectsById(Guid id) {
36      return DataContext.ExecuteQuery<Project>(GetChildProjectsByIdQuery, id);
37    }
38
39    public IEnumerable<Guid> GetChildProjectIdsById(Guid id) {
40      return DataContext.ExecuteQuery<Guid>(GetChildProjectIdsByIdQuery, id);
41    }
42
43    public IEnumerable<Project> GetParentProjectsById(Guid id) {
44      return DataContext.ExecuteQuery<Project>(GetParentProjectsByIdQuery, id);
45    }
46
47    public IEnumerable<Guid> GetParentProjectIdsById(Guid id) {
48      return DataContext.ExecuteQuery<Guid>(GetParentProjectIdsByIdQuery, id);
49    }
50
51    public IEnumerable<Project> GetCurrentAndParentProjectsById(Guid id) {
52      return DataContext.ExecuteQuery<Project>(GetCurrentAndParentProjectsByIdQuery, id);
53    }
54
55    public IEnumerable<Guid> GetCurrentAndParentProjectIdsById(Guid id) {
56      return DataContext.ExecuteQuery<Guid>(GetCurrentAndParentProjectIdsByIdQuery, id);
57    }
58
59    #region Compiled queries
60    private static readonly Func<DataContext, Guid, Project> GetByIdQuery =
61      CompiledQuery.Compile((DataContext db, Guid projectId) =>
62        (from project in db.GetTable<Project>()
63         where project.ProjectId == projectId
64         select project).SingleOrDefault());
65    #endregion
66
67    #region String queries
68    private const string GetChildProjectsByIdQuery = @"
69      WITH ptree AS
70      (
71        SELECT ProjectId, ParentProjectId
72        FROM [Project]
73        UNION ALL
74        SELECT pt.ProjectId, p.ParentProjectId
75        FROM [Project] p
76        JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId
77      )
78      SELECT DISTINCT pro.*
79      FROM ptree, [Project] pro
80      WHERE ptree.ParentProjectId = {0}
81      AND ptree.ProjectId = pro.ProjectId
82    ";
83    private const string GetChildProjectIdsByIdQuery = @"
84      WITH ptree AS
85      (
86        SELECT ProjectId, ParentProjectId
87        FROM [Project]
88        UNION ALL
89        SELECT pt.ProjectId, r.ParentProjectId
90        FROM [Project] r
91        JOIN ptree pt ON pt.ParentProjectId = r.ProjectId AND r.ParentProjectId <> r.ProjectId AND pt.ParentProjectId <> pt.ProjectId
92      )
93      SELECT DISTINCT ptree.ProjectId
94      FROM ptree
95      WHERE ptree.ParentProjectId = {0}
96    ";
97    private const string GetParentProjectsByIdQuery = @"
98      WITH pbranch AS
99      (
100        SELECT ProjectId, ParentProjectId
101        FROM [Project]
102        UNION ALL
103        SELECT pb.ProjectId, p.ParentProjectId
104        FROM [Project] p
105        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
106      )
107      SELECT DISTINCT pro.*
108      FROM pbranch, [Project] pro
109      WHERE pbranch.ProjectId = {0}
110      AND pbranch.ParentProjectId = pro.ProjectId
111    ";
112    private const string GetParentProjectIdsByIdQuery = @"
113      WITH pbranch AS
114      (
115        SELECT ProjectId, ParentProjectId
116        FROM [Project]
117        UNION ALL
118        SELECT pb.ProjectId, p.ParentProjectId
119        FROM [Project] p
120        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
121      )
122      SELECT DISTINCT pbranch.ParentProjectId
123      FROM pbranch
124      WHERE pbranch.ProjectId = {0}
125    ";
126    private const string GetCurrentAndParentProjectsByIdQuery = @"
127      WITH pbranch AS
128      (
129        SELECT ProjectId, ParentProjectId
130        FROM Project
131        WHERE ProjectId = {0}
132        UNION ALL
133        SELECT p.ProjectId, p.ParentProjectId
134        FROM Project p
135        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
136      )
137      SELECT DISTINCT pro.*
138      FROM pbranch, Project pro
139      WHERE pbranch.ProjectId = pro.ProjectId
140    ";
141    private const string GetCurrentAndParentProjectIdsByIdQuery = @"
142      WITH pbranch AS
143      (
144        SELECT ProjectId, ParentProjectId
145        FROM Project
146        WHERE ProjectId = {0}
147        UNION ALL
148        SELECT p.ProjectId, p.ParentProjectId
149        FROM Project p
150        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
151      )
152      SELECT DISTINCT pbranch.ProjectId
153      FROM pbranch
154    ";
155    private const string GetNearestOwnedParentProjectByIdQuery = @"
156      WITH pbranch AS
157      (
158        SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance
159        FROM [Project]
160        WHERE ProjectId = {0}
161        UNION ALL
162        SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1
163        FROM [Project] p
164        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
165      )
166      SELECT TOP(1) pro.*
167      FROM pbranch, [Project] pro
168      WHERE pbranch.ParentProjectId = pro.ProjectId
169      AND pro.OwnerUserId = {1}
170      ORDER BY pbranch.Distance
171    ";
172    private const string GetFarestOwnedParentProjectIdByIdQuery = @"
173      WITH pbranch AS
174      (
175        SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance
176        FROM [Project]
177        WHERE ProjectId = {0}
178        UNION ALL
179        SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1
180        FROM [Project] p
181        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
182      )
183      SELECT TOP(1) pro.*
184      FROM pbranch, [Project] pro
185      WHERE pbranch.ParentProjectId = pro.ProjectId
186      AND pro.OwnerUserId = {1}
187      ORDER BY pbranch.Distance DESC
188    ";
189    #endregion
190  }
191}
Note: See TracBrowser for help on using the repository browser.