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

Last change on this file since 15737 was 15737, checked in by jzenisek, 19 months ago

#2839

  • updated sql scripts (necessary foreign key option alterations & introduction of statistic tables)
  • updated HiveService according to changed client side (deletion-routine, permission checking,...)
File size: 9.0 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 DeleteByProjectIds(IEnumerable<Guid> projectIds) {
33      string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x)));
34      if (!string.IsNullOrWhiteSpace(paramProjectIds)) {
35        string queryString = string.Format(DeleteByProjectIdsQueryString, paramProjectIds);
36        DataContext.ExecuteCommand(queryString);
37      }
38    }
39
40    public void DeleteByProjectIdsAndUserIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> userIds) {
41      string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x)));
42      string paramUserIds = string.Join(",", userIds.ToList().Select(x => string.Format("'{0}'", x)));
43      if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramUserIds)) {
44        string queryString = string.Format(DeleteByProjectIdsAndUserIdsQueryString, paramProjectIds, paramUserIds);
45        DataContext.ExecuteCommand(queryString);
46      }
47    }
48
49    public void DeleteByProjectIdAndResourceIds(Guid projectId, IEnumerable<Guid> resourceIds) {
50      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
51      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
52        string queryString = string.Format(DeleteByProjectIdAndResourceIdsQueryString, projectId, paramResourceIds);
53        DataContext.ExecuteCommand(queryString);
54      }
55    }
56
57    public void DeleteByProjectIdsAndResourceIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> resourceIds) {
58      string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x)));
59      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
60      if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramResourceIds)) {
61        string queryString = string.Format(DeleteByProjectIdsAndResourceIdsQueryString, paramProjectIds, paramResourceIds);
62        DataContext.ExecuteCommand(queryString);
63      }
64    }
65
66    public bool CheckJobGrantedForResource(Guid jobId, Guid resourceId) {
67      return DataContext.ExecuteQuery<int>(CheckJobGrantedForResourceQueryString, jobId, resourceId).First() > 0;
68    }
69
70    public bool CheckJobGrantedForResources(Guid jobId, IEnumerable<Guid> resourceIds) {
71      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
72      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
73        string queryString = string.Format(CheckJobGrantedForResourcesQueryString, jobId, paramResourceIds);
74        return DataContext.ExecuteQuery<int>(queryString).Count() == 0;
75      }
76      return false;
77    }
78
79    public bool CheckTaskGrantedForResource(Guid taskId, Guid resourceId) {
80      return DataContext.ExecuteQuery<int>(CheckTaskGrantedForResourceQueryString, taskId, resourceId).First() > 0;
81    }
82
83    public IEnumerable<Resource> GetAllGrantedResourcesByJobId(Guid jobId) {
84      return DataContext.ExecuteQuery<Resource>(GetAllGrantedResourcesByJobIdQueryString, jobId);
85    }
86
87    public IEnumerable<Guid> GetAllGrantedResourceIdsByJobId(Guid jobId) {
88      return DataContext.ExecuteQuery<Guid>(GetAllGrantedResourceIdsByJobIdQueryString, jobId);
89    }
90
91    #region String queries
92    private const string DeleteByProjectIdQueryString = @"
93      DELETE FROM [AssignedJobResource]
94      WHERE JobId IN
95        (
96          SELECT j.JobId
97          FROM [Job] j
98          WHERE j.ProjectId = {0}
99        )
100    ";
101    private const string DeleteByProjectIdAndUserIdsQueryString = @"
102      DELETE FROM [AssignedJobResource]
103      WHERE JobId IN
104        (
105          SELECT j.JobId
106          FROM [Job] j
107          WHERE j.ProjectId = {0}
108          AND j.OwnerUserId IN ({1})
109        )
110    ";
111    private const string DeleteByProjectIdsQueryString = @"
112      DELETE FROM [AssignedJobResource]
113      WHERE JobId IN
114        (
115          SELECT j.JobId
116          FROM [Job] j
117          WHERE j.ProjectId IN ({0})
118        )
119    ";
120    private const string DeleteByProjectIdsAndUserIdsQueryString = @"
121      DELETE FROM [AssignedJobResource]
122      WHERE JobId IN
123        (
124          SELECT j.JobId
125          FROM [Job] j
126          WHERE j.ProjectId IN ({0})
127          AND j.OwnerUserId IN ({1})
128        )
129    ";
130    private const string DeleteByProjectIdAndResourceIdsQueryString = @"
131      DELETE FROM [AssignedJobResource]
132      WHERE JobId IN
133        (
134          SELECT j.JobId
135          FROM [Job] j
136          WHERE j.ProjectId = '{0}'
137        )
138      AND ResourceId IN ({1})
139    ";
140    private const string DeleteByProjectIdsAndResourceIdsQueryString = @"
141      DELETE FROM [AssignedJobResource]
142      WHERE JobId IN
143        (
144          SELECT j.JobId
145          FROM [Job] j
146          WHERE j.ProjectId IN ({0})
147        )
148      AND ResourceId IN ({1})
149    ";
150    private const string CheckJobGrantedForResourceQueryString = @"
151      WITH rbranch AS (
152        SELECT ResourceId, ParentResourceId
153        FROM [Resource]
154        WHERE ResourceId = {1}
155        UNION ALL
156        SELECT r.ResourceId, r.ParentResourceId
157        FROM [Resource] r
158        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
159      )
160      SELECT COUNT(ajr.JobId)
161      FROM rbranch, [AssignedJobResource] ajr
162      WHERE rbranch.ResourceId = ajr.ResourceId
163      AND ajr.JobId = {0}
164    ";
165    private const string CheckTaskGrantedForResourceQueryString = @"
166      WITH rbranch AS (
167        SELECT ResourceId, ParentResourceId
168        FROM [Resource]
169        WHERE ResourceId = {1}
170        UNION ALL
171        SELECT r.ResourceId, r.ParentResourceId
172        FROM [Resource] r
173        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
174      )
175      SELECT COUNT(ajr.JobId)
176      FROM rbranch, [AssignedJobResource] ajr, [Task] t
177      WHERE rbranch.ResourceId = ajr.ResourceId
178      AND ajr.JobId = t.JobId
179      AND t.JobId = {0}
180    ";
181    private const string CheckJobGrantedForResourcesQueryString = @"
182      WITH rtree AS
183      (
184        SELECT ResourceId, ParentResourceId
185        FROM [Resource]
186        UNION ALL
187        SELECT rt.ResourceId, r.ParentResourceId
188        FROM [Resource] r
189        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId
190      )
191      SELECT r.ResourceId
192      FROM [Resource] r
193      WHERE r.ResourceId IN ({1})
194      EXCEPT
195      (
196        SELECT rtree.ResourceId
197        FROM rtree, [AssignedJobResource] ajr
198        WHERE rtree.ParentResourceId = ajr.ResourceId
199        AND ajr.JobId = {0}
200        UNION
201        SELECT ajr.ResourceId
202        FROM [AssignedJobResource] ajr
203        WHERE ajr.JobId = {0}
204      )
205    ";
206    private const string GetAllGrantedResourcesByJobIdQueryString = @"
207      WITH rtree AS
208      (
209        SELECT ResourceId, ParentResourceId
210        FROM [Resource]
211        UNION ALL
212        SELECT rt.ResourceId, r.ParentResourceId
213        FROM [Resource] r
214        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId
215      )
216      SELECT res.*
217      FROM rtree, [AssignedJobResource] ajr, [Resource] res
218      WHERE rtree.ParentResourceId = ajr.ResourceId
219      AND rtree.ResourceId = res.ResourceId
220      AND ajr.JobId = {0}
221      UNION
222      SELECT res.*
223      FROM [AssignedJobResource] ajr, [Resource] res
224      WHERE ajr.ResourceId = res.ResourceId
225      AND ajr.JobId = {0}
226    ";
227    private const string GetAllGrantedResourceIdsByJobIdQueryString = @"
228    WITH rtree AS
229    (
230      SELECT ResourceId, ParentResourceId
231      FROM [Resource]
232      UNION ALL
233      SELECT rt.ResourceId, r.ParentResourceId
234      FROM [Resource] r
235      JOIN rtree rt ON rt.ParentResourceId = r.ResourceId
236    )
237    SELECT rtree.ResourceId
238    FROM rtree, [AssignedJobResource] ajr
239    WHERE rtree.ParentResourceId = ajr.ResourceId
240    AND ajr.JobId = {0}
241    UNION
242    SELECT ajr.ResourceId
243    FROM [AssignedJobResource] ajr
244    WHERE ajr.JobId = {0}
245    ";
246    #endregion
247  }
248}
Note: See TracBrowser for help on using the repository browser.