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

Last change on this file since 15577 was 15577, checked in by jzenisek, 3 years ago

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

File size: 8.6 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> GetUsageGrantedProjectsForUser(IEnumerable<Guid> userAndGroupIds) {
36      string paramUserAndGroupIds = string.Join(",", userAndGroupIds.ToList().Select(x => string.Format("'{0}'", x)));
37      if (!string.IsNullOrWhiteSpace(paramUserAndGroupIds)) {
38        string queryString = string.Format(GetUsageGrantedProjectsForUserQueryString, paramUserAndGroupIds);
39        return DataContext.ExecuteQuery<Project>(queryString);
40      }
41      return Enumerable.Empty<Project>();
42    }
43
44    public IEnumerable<Project> GetAdministrationGrantedProjectsForUser(Guid userId) {
45      return DataContext.ExecuteQuery<Project>(GetAdministrationGrantedProjectsForUserQueryString, userId);
46    }
47
48    public IEnumerable<Project> GetChildProjectsById(Guid id) {
49      return DataContext.ExecuteQuery<Project>(GetChildProjectsByIdQuery, id);
50    }
51
52    public IEnumerable<Guid> GetChildProjectIdsById(Guid id) {
53      return DataContext.ExecuteQuery<Guid>(GetChildProjectIdsByIdQuery, id);
54    }
55
56    public IEnumerable<Project> GetParentProjectsById(Guid id) {
57      return DataContext.ExecuteQuery<Project>(GetParentProjectsByIdQuery, id);
58    }
59
60    public IEnumerable<Guid> GetParentProjectIdsById(Guid id) {
61      return DataContext.ExecuteQuery<Guid>(GetParentProjectIdsByIdQuery, id);
62    }
63
64    public IEnumerable<Project> GetCurrentAndParentProjectsById(Guid id) {
65      return DataContext.ExecuteQuery<Project>(GetCurrentAndParentProjectsByIdQuery, id);
66    }
67
68    public IEnumerable<Guid> GetCurrentAndParentProjectIdsById(Guid id) {
69      return DataContext.ExecuteQuery<Guid>(GetCurrentAndParentProjectIdsByIdQuery, id);
70    }
71
72    #region Compiled queries
73    private static readonly Func<DataContext, Guid, Project> GetByIdQuery =
74      CompiledQuery.Compile((DataContext db, Guid projectId) =>
75        (from project in db.GetTable<Project>()
76         where project.ProjectId == projectId
77         select project).SingleOrDefault());
78    #endregion
79
80    #region String queries
81    private const string GetUsageGrantedProjectsForUserQueryString = @"
82      SELECT DISTINCT p.*
83      FROM [Project] p, [ProjectPermission] pp
84      WHERE p.ProjectId = pp.ProjectId
85      AND pp.GrantedUserId IN ({0})
86    ";
87
88    private const string GetAdministrationGrantedProjectsForUserQueryString = @"
89      WITH ptree AS
90      (
91        SELECT ProjectId, ParentProjectId
92        FROM [Project]
93        UNION ALL
94        SELECT pt.ProjectId, p.ParentProjectId
95        FROM [Project] p
96        JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId
97      )
98      SELECT DISTINCT parent.*
99      FROM [Project] parent
100      WHERE parent.OwnerUserId = {0}
101      UNION
102      SELECT DISTINCT child.*
103      FROM ptree, [Project] parent, [Project] child
104      WHERE ptree.ParentProjectId = parent.ProjectId
105      AND ptree.ProjectId = child.ProjectId
106      AND parent.OwnerUserId = {0}
107    ";
108
109    private const string GetChildProjectsByIdQuery = @"
110      WITH ptree AS
111      (
112        SELECT ProjectId, ParentProjectId
113        FROM [Project]
114        UNION ALL
115        SELECT pt.ProjectId, p.ParentProjectId
116        FROM [Project] p
117        JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId
118      )
119      SELECT DISTINCT pro.*
120      FROM ptree, [Project] pro
121      WHERE ptree.ParentProjectId = {0}
122      AND ptree.ProjectId = pro.ProjectId
123    ";
124    private const string GetChildProjectIdsByIdQuery = @"
125      WITH ptree AS
126      (
127        SELECT ProjectId, ParentProjectId
128        FROM [Project]
129        UNION ALL
130        SELECT pt.ProjectId, r.ParentProjectId
131        FROM [Project] r
132        JOIN ptree pt ON pt.ParentProjectId = r.ProjectId AND r.ParentProjectId <> r.ProjectId AND pt.ParentProjectId <> pt.ProjectId
133      )
134      SELECT DISTINCT ptree.ProjectId
135      FROM ptree
136      WHERE ptree.ParentProjectId = {0}
137    ";
138    private const string GetParentProjectsByIdQuery = @"
139      WITH pbranch AS
140      (
141        SELECT ProjectId, ParentProjectId
142        FROM [Project]
143        UNION ALL
144        SELECT pb.ProjectId, p.ParentProjectId
145        FROM [Project] p
146        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
147      )
148      SELECT DISTINCT pro.*
149      FROM pbranch, [Project] pro
150      WHERE pbranch.ProjectId = {0}
151      AND pbranch.ParentProjectId = pro.ProjectId
152    ";
153    private const string GetParentProjectIdsByIdQuery = @"
154      WITH pbranch AS
155      (
156        SELECT ProjectId, ParentProjectId
157        FROM [Project]
158        UNION ALL
159        SELECT pb.ProjectId, p.ParentProjectId
160        FROM [Project] p
161        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
162      )
163      SELECT DISTINCT pbranch.ParentProjectId
164      FROM pbranch
165      WHERE pbranch.ProjectId = {0}
166    ";
167    private const string GetCurrentAndParentProjectsByIdQuery = @"
168      WITH pbranch AS
169      (
170        SELECT ProjectId, ParentProjectId
171        FROM Project
172        WHERE ProjectId = {0}
173        UNION ALL
174        SELECT p.ProjectId, p.ParentProjectId
175        FROM Project p
176        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
177      )
178      SELECT DISTINCT pro.*
179      FROM pbranch, Project pro
180      WHERE pbranch.ProjectId = pro.ProjectId
181    ";
182    private const string GetCurrentAndParentProjectIdsByIdQuery = @"
183      WITH pbranch AS
184      (
185        SELECT ProjectId, ParentProjectId
186        FROM Project
187        WHERE ProjectId = {0}
188        UNION ALL
189        SELECT p.ProjectId, p.ParentProjectId
190        FROM Project p
191        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
192      )
193      SELECT DISTINCT pbranch.ProjectId
194      FROM pbranch
195    ";
196    private const string GetNearestOwnedParentProjectByIdQuery = @"
197      WITH pbranch AS
198      (
199        SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance
200        FROM [Project]
201        WHERE ProjectId = {0}
202        UNION ALL
203        SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1
204        FROM [Project] p
205        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
206      )
207      SELECT TOP(1) pro.*
208      FROM pbranch, [Project] pro
209      WHERE pbranch.ParentProjectId = pro.ProjectId
210      AND pro.OwnerUserId = {1}
211      ORDER BY pbranch.Distance
212    ";
213    private const string GetFarestOwnedParentProjectIdByIdQuery = @"
214      WITH pbranch AS
215      (
216        SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance
217        FROM [Project]
218        WHERE ProjectId = {0}
219        UNION ALL
220        SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1
221        FROM [Project] p
222        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
223      )
224      SELECT TOP(1) pro.*
225      FROM pbranch, [Project] pro
226      WHERE pbranch.ParentProjectId = pro.ProjectId
227      AND pro.OwnerUserId = {1}
228      ORDER BY pbranch.Distance DESC
229    ";
230    #endregion
231  }
232}
Note: See TracBrowser for help on using the repository browser.