Free cookie consent management tool by TermsFeed Policy Generator

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

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

#2839

  • worked on Job operations add&update
  • worked on ProjectPermission handling
  • worked on Project-Resource assignment
File size: 4.9 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 bool CheckJobGrantedForResource(Guid jobId, Guid resourceId) {
21      return DataContext.ExecuteQuery<int>(CheckJobGrantedForResourceQueryString, jobId, resourceId).First() > 0;
22    }
23
24    public bool CheckJobGrantedForResources(Guid jobId, IEnumerable<Guid> resourceIds) {
25      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
26      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
27        string queryString = string.Format(CheckJobGrantedForResourcesQueryString, jobId, paramResourceIds);
28        return DataContext.ExecuteQuery<int>(queryString).Count() == 0;
29      }
30      return false;
31    }
32
33    public bool CheckTaskGrantedForResource(Guid taskId, Guid resourceId) {
34      return DataContext.ExecuteQuery<int>(CheckTaskGrantedForResourceQueryString, taskId, resourceId).First() > 0;
35    }
36
37    public IEnumerable<Resource> GetAllGrantedResourcesByJobId(Guid jobId) {
38      return DataContext.ExecuteQuery<Resource>(GetAllGrantedResourcesByJobIdQueryString, jobId);
39    }
40
41    public IEnumerable<Guid> GetAllGrantedResourceIdsByJobId(Guid jobId) {
42      return DataContext.ExecuteQuery<Guid>(GetAllGrantedResourceIdsByJobIdQueryString, jobId);
43    }
44
45    #region String queries
46    private const string CheckJobGrantedForResourceQueryString = @"
47      WITH rbranch AS (
48        SELECT ResourceId, ParentResourceId
49        FROM [Resource]
50        WHERE ResourceId = {1}
51        UNION ALL
52        SELECT r.ResourceId, r.ParentResourceId
53        FROM [Resource] r
54        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
55      )
56      SELECT COUNT(ajr.JobId)
57      FROM rbranch, AssignedJobResource ajr
58      WHERE rbranch.ResourceId = ajr.ResourceId
59      AND ajr.JobId = {0}
60    ";
61    private const string CheckTaskGrantedForResourceQueryString = @"
62      WITH rbranch AS (
63        SELECT ResourceId, ParentResourceId
64        FROM [Resource]
65        WHERE ResourceId = {1}
66        UNION ALL
67        SELECT r.ResourceId, r.ParentResourceId
68        FROM [Resource] r
69        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
70      )
71      SELECT COUNT(ajr.JobId)
72      FROM rbranch, AssignedJobResource ajr, Task t
73      WHERE rbranch.ResourceId = ajr.ResourceId
74      AND ajr.JobId = t.JobId
75      AND t.JobId = {0}
76    ";
77    private const string CheckJobGrantedForResourcesQueryString = @"
78      WITH rtree AS
79      (
80        SELECT ResourceId, ParentResourceId
81        FROM [Resource]
82        UNION ALL
83        SELECT rt.ResourceId, r.ParentResourceId
84        FROM [Resource] r
85        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId
86      )
87      SELECT r.ResourceId
88      FROM [Resource] r
89      WHERE r.ResourceId IN ({1})
90      EXCEPT
91      (
92        SELECT rtree.ResourceId
93        FROM rtree, [AssignedJobResource] ajr
94        WHERE rtree.ParentResourceId = ajr.ResourceId
95        AND ajr.JobId = {0}
96        UNION
97        SELECT ajr.ResourceId
98        FROM [AssignedJobResource] ajr
99        WHERE ajr.JobId = {0}
100      )
101    ";
102    private const string GetAllGrantedResourcesByJobIdQueryString = @"
103      WITH rtree AS
104      (
105        SELECT ResourceId, ParentResourceId
106        FROM [Resource]
107        UNION ALL
108        SELECT rt.ResourceId, r.ParentResourceId
109        FROM [Resource] r
110        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId
111      )
112      SELECT res.*
113      FROM rtree, [AssignedJobResource] ajr, [Resource] res
114      WHERE rtree.ParentResourceId = ajr.ResourceId
115      AND rtree.ResourceId = res.ResourceId
116      AND ajr.JobId = {0}
117      UNION
118      SELECT res.*
119      FROM [AssignedJobResource] ajr, [Resource] res
120      WHERE ajr.ResourceId = res.ResourceId
121      AND ajr.JobId = {0}
122    ";
123    private const string GetAllGrantedResourceIdsByJobIdQueryString = @"
124    WITH rtree AS
125    (
126      SELECT ResourceId, ParentResourceId
127      FROM [Resource]
128      UNION ALL
129      SELECT rt.ResourceId, r.ParentResourceId
130      FROM [Resource] r
131      JOIN rtree rt ON rt.ParentResourceId = r.ResourceId
132    )
133    SELECT rtree.ResourceId
134    FROM rtree, [AssignedJobResource] ajr
135    WHERE rtree.ParentResourceId = ajr.ResourceId
136    AND ajr.JobId = {0}
137    UNION
138    SELECT ajr.ResourceId
139    FROM [AssignedJobResource] ajr
140    WHERE ajr.JobId = {0}
141    ";
142    #endregion
143  }
144}
Note: See TracBrowser for help on using the repository browser.