source: branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedJobResourceDao.cs @ 15552

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

#2839 worked on permission checks in listing methods

File size: 8.4 KB
Line 
1using System;
2using System.Collections.Generic;
3using System.Data.Linq;
4using System.Linq;
5using System.Text;
6using System.Threading.Tasks;
7
8namespace HeuristicLab.Services.Hive.DataAccess.Daos {
9  public class AssignedJobResourceDao : GenericDao<Guid, AssignedJobResource> {
10    public AssignedJobResourceDao(DataContext dataContext) : base(dataContext) { }
11
12    public override AssignedJobResource GetById(Guid id) {
13      throw new NotImplementedException();
14    }
15
16    public IQueryable<AssignedJobResource> GetByJobId(Guid jobId) {
17      return Table.Where(x => x.JobId == jobId);
18    }
19
20    public void DeleteByProjectId(Guid projectId) {
21      DataContext.ExecuteCommand(DeleteByProjectIdQueryString, projectId);
22    }
23
24    public void DeleteByProjectIdAndUserIds(Guid projectId, IEnumerable<Guid> userIds) {
25      string paramUserIds = string.Join(",", userIds.Select(x => string.Format("'{0}'", x)));
26      if (!string.IsNullOrWhiteSpace(paramUserIds)) {
27        string queryString = string.Format(DeleteByProjectIdAndUserIdsQueryString, projectId, paramUserIds);
28        DataContext.ExecuteCommand(queryString);
29      }
30    }
31
32    public void DeleteByProjectIdsAndUserIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> userIds) {
33      string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x)));
34      string paramUserIds = string.Join(",", userIds.Select(x => string.Format("'{0}'", x)));
35      if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramUserIds)) {
36        string queryString = string.Format(DeleteByProjectIdsAndUserIdsQueryString, paramProjectIds, paramUserIds);
37        DataContext.ExecuteCommand(queryString);
38      }
39    }
40
41    public void DeleteByProjectIdAndResourceIds(Guid projectId, IEnumerable<Guid> resourceIds) {
42      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
43      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
44        string queryString = string.Format(DeleteByProjectIdAndResourceIdsQueryString, projectId, paramResourceIds);
45        DataContext.ExecuteCommand(queryString);
46      }
47    }
48
49    public void DeleteByProjectIdsAndResourceIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> resourceIds) {
50      string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x)));
51      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
52      if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramResourceIds)) {
53        string queryString = string.Format(DeleteByProjectIdsAndResourceIdsQueryString, paramProjectIds, paramResourceIds);
54        DataContext.ExecuteCommand(queryString);
55      }
56    }
57
58    public bool CheckJobGrantedForResource(Guid jobId, Guid resourceId) {
59      return DataContext.ExecuteQuery<int>(CheckJobGrantedForResourceQueryString, jobId, resourceId).First() > 0;
60    }
61
62    public bool CheckJobGrantedForResources(Guid jobId, IEnumerable<Guid> resourceIds) {
63      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
64      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
65        string queryString = string.Format(CheckJobGrantedForResourcesQueryString, jobId, paramResourceIds);
66        return DataContext.ExecuteQuery<int>(queryString).Count() == 0;
67      }
68      return false;
69    }
70
71    public bool CheckTaskGrantedForResource(Guid taskId, Guid resourceId) {
72      return DataContext.ExecuteQuery<int>(CheckTaskGrantedForResourceQueryString, taskId, resourceId).First() > 0;
73    }
74
75    public IEnumerable<Resource> GetAllGrantedResourcesByJobId(Guid jobId) {
76      return DataContext.ExecuteQuery<Resource>(GetAllGrantedResourcesByJobIdQueryString, jobId);
77    }
78
79    public IEnumerable<Guid> GetAllGrantedResourceIdsByJobId(Guid jobId) {
80      return DataContext.ExecuteQuery<Guid>(GetAllGrantedResourceIdsByJobIdQueryString, jobId);
81    }
82
83    #region String queries
84    private const string DeleteByProjectIdQueryString = @"
85      DELETE FROM [AssignedJobResource] ajr
86      WHERE ajr.JobId IN
87        (
88          SELECT j.JobId
89          FROM [Job] j
90          WHERE j.ProjectId = {0}
91        )
92    ";
93    private const string DeleteByProjectIdAndUserIdsQueryString = @"
94      DELETE FROM [AssignedJobResource] ajr
95      WHERE ajr.JobId IN
96        (
97          SELECT j.JobId
98          FROM [Job] j
99          WHERE j.ProjectId = {0}
100          AND j.OwnerUserId IN ({1})
101        )
102    ";
103    private const string DeleteByProjectIdsAndUserIdsQueryString = @"
104      DELETE FROM [AssignedJobResource] ajr
105      WHERE ajr.JobId IN
106        (
107          SELECT j.JobId
108          FROM [Job] j
109          WHERE j.ProjectId IN ({0})
110          AND j.OwnerUserId IN ({1})
111        )
112    ";
113    private const string DeleteByProjectIdAndResourceIdsQueryString = @"
114      DELETE FROM [AssignedJobResource] ajr
115      WHERE ajr.JobId IN
116        (
117          SELECT j.JobId
118          FROM [Job] j
119          WHERE j.ProjectId = {0}
120        )
121      AND ajr.ResourceId IN ({1})
122    ";
123    private const string DeleteByProjectIdsAndResourceIdsQueryString = @"
124      DELETE FROM [AssignedJobResource] ajr
125      WHERE ajr.JobId IN
126        (
127          SELECT j.JobId
128          FROM [Job] j
129          WHERE j.ProjectId IN ({0})
130        )
131      AND ajr.ResourceId IN ({1})
132    ";
133    private const string CheckJobGrantedForResourceQueryString = @"
134      WITH rbranch AS (
135        SELECT ResourceId, ParentResourceId
136        FROM [Resource]
137        WHERE ResourceId = {1}
138        UNION ALL
139        SELECT r.ResourceId, r.ParentResourceId
140        FROM [Resource] r
141        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
142      )
143      SELECT COUNT(ajr.JobId)
144      FROM rbranch, AssignedJobResource ajr
145      WHERE rbranch.ResourceId = ajr.ResourceId
146      AND ajr.JobId = {0}
147    ";
148    private const string CheckTaskGrantedForResourceQueryString = @"
149      WITH rbranch AS (
150        SELECT ResourceId, ParentResourceId
151        FROM [Resource]
152        WHERE ResourceId = {1}
153        UNION ALL
154        SELECT r.ResourceId, r.ParentResourceId
155        FROM [Resource] r
156        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
157      )
158      SELECT COUNT(ajr.JobId)
159      FROM rbranch, AssignedJobResource ajr, Task t
160      WHERE rbranch.ResourceId = ajr.ResourceId
161      AND ajr.JobId = t.JobId
162      AND t.JobId = {0}
163    ";
164    private const string CheckJobGrantedForResourcesQueryString = @"
165      WITH rtree AS
166      (
167        SELECT ResourceId, ParentResourceId
168        FROM [Resource]
169        UNION ALL
170        SELECT rt.ResourceId, r.ParentResourceId
171        FROM [Resource] r
172        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId
173      )
174      SELECT r.ResourceId
175      FROM [Resource] r
176      WHERE r.ResourceId IN ({1})
177      EXCEPT
178      (
179        SELECT rtree.ResourceId
180        FROM rtree, [AssignedJobResource] ajr
181        WHERE rtree.ParentResourceId = ajr.ResourceId
182        AND ajr.JobId = {0}
183        UNION
184        SELECT ajr.ResourceId
185        FROM [AssignedJobResource] ajr
186        WHERE ajr.JobId = {0}
187      )
188    ";
189    private const string GetAllGrantedResourcesByJobIdQueryString = @"
190      WITH rtree AS
191      (
192        SELECT ResourceId, ParentResourceId
193        FROM [Resource]
194        UNION ALL
195        SELECT rt.ResourceId, r.ParentResourceId
196        FROM [Resource] r
197        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId
198      )
199      SELECT res.*
200      FROM rtree, [AssignedJobResource] ajr, [Resource] res
201      WHERE rtree.ParentResourceId = ajr.ResourceId
202      AND rtree.ResourceId = res.ResourceId
203      AND ajr.JobId = {0}
204      UNION
205      SELECT res.*
206      FROM [AssignedJobResource] ajr, [Resource] res
207      WHERE ajr.ResourceId = res.ResourceId
208      AND ajr.JobId = {0}
209    ";
210    private const string GetAllGrantedResourceIdsByJobIdQueryString = @"
211    WITH rtree AS
212    (
213      SELECT ResourceId, ParentResourceId
214      FROM [Resource]
215      UNION ALL
216      SELECT rt.ResourceId, r.ParentResourceId
217      FROM [Resource] r
218      JOIN rtree rt ON rt.ParentResourceId = r.ResourceId
219    )
220    SELECT rtree.ResourceId
221    FROM rtree, [AssignedJobResource] ajr
222    WHERE rtree.ParentResourceId = ajr.ResourceId
223    AND ajr.JobId = {0}
224    UNION
225    SELECT ajr.ResourceId
226    FROM [AssignedJobResource] ajr
227    WHERE ajr.JobId = {0}
228    ";
229    #endregion
230  }
231}
Note: See TracBrowser for help on using the repository browser.