Free cookie consent management tool by TermsFeed Policy Generator

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

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

#2839 added AssignedJobResource to dbml and Daos

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