- Timestamp:
- 01/05/18 15:13:25 (7 years ago)
- Location:
- branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos
- Files:
-
- 5 edited
-
AssignedJobResourceDao.cs (modified) (4 diffs)
-
AssignedProjectResourceDao.cs (modified) (4 diffs)
-
ProjectDao.cs (modified) (2 diffs)
-
ProjectPermissionDao.cs (modified) (4 diffs)
-
ResourceDao.cs (modified) (1 diff)
Legend:
- Unmodified
- Added
- Removed
-
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedJobResourceDao.cs
r15552 r15577 30 30 } 31 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 32 40 public void DeleteByProjectIdsAndUserIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> userIds) { 33 41 string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x))); 34 string paramUserIds = string.Join(",", userIds. Select(x => string.Format("'{0}'", x)));42 string paramUserIds = string.Join(",", userIds.ToList().Select(x => string.Format("'{0}'", x))); 35 43 if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramUserIds)) { 36 44 string queryString = string.Format(DeleteByProjectIdsAndUserIdsQueryString, paramProjectIds, paramUserIds); … … 83 91 #region String queries 84 92 private const string DeleteByProjectIdQueryString = @" 85 DELETE FROM [AssignedJobResource] ajr86 WHERE ajr.JobId IN87 ( 88 SELECT j.JobId 89 FROM [Job] j 90 WHERE j.ProjectId = {0}93 DELETE FROM [AssignedJobResource] 94 WHERE JobId IN 95 ( 96 SELECT j.JobId 97 FROM [Job] j 98 WHERE j.ProjectId = '{0}' 91 99 ) 92 100 "; 93 101 private const string DeleteByProjectIdAndUserIdsQueryString = @" 94 DELETE FROM [AssignedJobResource] ajr95 WHERE ajr.JobId IN96 ( 97 SELECT j.JobId 98 FROM [Job] j 99 WHERE j.ProjectId = {0}102 DELETE FROM [AssignedJobResource] 103 WHERE JobId IN 104 ( 105 SELECT j.JobId 106 FROM [Job] j 107 WHERE j.ProjectId = '{0}' 100 108 AND j.OwnerUserId IN ({1}) 101 109 ) 102 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 "; 103 120 private const string DeleteByProjectIdsAndUserIdsQueryString = @" 104 DELETE FROM [AssignedJobResource] ajr105 WHERE ajr.JobId IN121 DELETE FROM [AssignedJobResource] 122 WHERE JobId IN 106 123 ( 107 124 SELECT j.JobId … … 112 129 "; 113 130 private const string DeleteByProjectIdAndResourceIdsQueryString = @" 114 DELETE FROM [AssignedJobResource] ajr115 WHERE ajr.JobId IN116 ( 117 SELECT j.JobId 118 FROM [Job] j 119 WHERE j.ProjectId = {0}120 ) 121 AND ajr.ResourceId IN ({1})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}) 122 139 "; 123 140 private const string DeleteByProjectIdsAndResourceIdsQueryString = @" 124 DELETE FROM [AssignedJobResource] ajr125 WHERE ajr.JobId IN141 DELETE FROM [AssignedJobResource] 142 WHERE JobId IN 126 143 ( 127 144 SELECT j.JobId … … 129 146 WHERE j.ProjectId IN ({0}) 130 147 ) 131 AND ajr.ResourceId IN ({1})148 AND ResourceId IN ({1}) 132 149 "; 133 150 private const string CheckJobGrantedForResourceQueryString = @" -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedProjectResourceDao.cs
r15546 r15577 38 38 39 39 public void DeleteByProjectIdAndResourceIds(Guid projectId, IEnumerable<Guid> resourceIds) { 40 string paramIds = string.Join(",", resourceIds. Select(x => string.Format("'{0}'", x)));40 string paramIds = string.Join(",", resourceIds.ToList().Select(x => string.Format("'{0}'", x))); 41 41 if (!string.IsNullOrWhiteSpace(paramIds)) { 42 42 string query = string.Format(DeleteByProjectIdAndResourceIdsQueryString, projectId, paramIds); … … 46 46 47 47 public void DeleteByProjectIdsAndResourceIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> resourceIds) { 48 string paramProjectIds = string.Join(",", projectIds. Select(x => string.Format("'{0}'", x)));49 string paramResourceIds = string.Join(",", resourceIds. Select(x => string.Format("'{0}'", x)));50 if (!string.IsNullOrWhiteSpace(param ResourceIds)) {48 string paramProjectIds = string.Join(",", projectIds.ToList().Select(x => string.Format("'{0}'", x))); 49 string paramResourceIds = string.Join(",", resourceIds.ToList().Select(x => string.Format("'{0}'", x))); 50 if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramResourceIds)) { 51 51 string query = string.Format(DeleteByProjectIdsAndResourceIdsQueryString, paramProjectIds, paramResourceIds); 52 52 DataContext.ExecuteCommand(query); … … 54 54 } 55 55 56 public void DeleteByProjectIds(IEnumerable<Guid> projectIds) { 57 string paramProjectIds = string.Join(",", projectIds.ToList().Select(x => string.Format("'{0}'", x))); 58 if (!string.IsNullOrWhiteSpace(paramProjectIds)) { 59 string query = string.Format(DeleteByProjectIdsQueryString, paramProjectIds); 60 DataContext.ExecuteCommand(query); 61 } 62 } 63 56 64 public bool CheckProjectGrantedForResources(Guid projectId, IEnumerable<Guid> resourceIds) { 57 string paramResourceIds = string.Join(",", resourceIds. Select(x => string.Format("'{0}'", x)));65 string paramResourceIds = string.Join(",", resourceIds.ToList().Select(x => string.Format("'{0}'", x))); 58 66 if (!string.IsNullOrWhiteSpace(paramResourceIds)) { 59 67 string queryString = string.Format(CheckProjectGrantedForResourcesQueryString, projectId, paramResourceIds); … … 85 93 86 94 #region String queries 87 private const string DeleteByProjectIdAndResourceIdsQueryString = 88 @"DELETE FROM [AssignedProjectResource] 89 WHERE ProjectId = '{0}' 90 AND ResourceId IN ({1});"; 91 private const string DeleteByProjectIdsAndResourceIdsQueryString = 92 @"DELETE FROM [AssignedProjectResource] 93 WHERE ProjectId IN ({0}) 94 AND ResourceId IN ({1});"; 95 private const string DeleteByProjectIdAndResourceIdsQueryString = @" 96 DELETE FROM [AssignedProjectResource] 97 WHERE ProjectId = '{0}' 98 AND ResourceId IN ({1}); 99 "; 100 private const string DeleteByProjectIdsAndResourceIdsQueryString = @" 101 DELETE FROM [AssignedProjectResource] 102 WHERE ProjectId IN ({0}) 103 AND ResourceId IN ({1}); 104 "; 105 private const string DeleteByProjectIdsQueryString = @" 106 DELETE FROM [AssignedProjectResource] 107 WHERE ProjectId IN ({0}) 108 "; 95 109 private const string CheckProjectGrantedForResourcesQueryString = @" 96 110 WITH rtree AS -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectDao.cs
r15552 r15577 33 33 } 34 34 35 public IEnumerable<Project> GetGrantedProjectsForUser(IEnumerable<Guid> userAndGroupIds) { 36 return GetGrantedProjectsForUserQuery(DataContext, userAndGroupIds); 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); 37 46 } 38 47 … … 67 76 where project.ProjectId == projectId 68 77 select project).SingleOrDefault()); 69 private static readonly Func<DataContext, IEnumerable<Guid>, IEnumerable<Project>> GetGrantedProjectsForUserQuery =70 CompiledQuery.Compile((DataContext db, IEnumerable<Guid> userAndGroupIds) =>71 (from project in db.GetTable<Project>()72 join projectPermission in db.GetTable<ProjectPermission>()73 on project.ProjectId equals projectPermission.ProjectId74 where userAndGroupIds.Contains(projectPermission.GrantedUserId)75 select project).Distinct());76 78 #endregion 77 79 78 #region String queries 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 79 109 private const string GetChildProjectsByIdQuery = @" 80 110 WITH ptree AS -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectPermissionDao.cs
r15552 r15577 38 38 39 39 public bool CheckUserGrantedForProject(Guid projectId, IEnumerable<Guid> userAndGroupIds) { 40 string paramUserAndGroupIds = string.Join(",", userAndGroupIds. Select(x => string.Format("'{0}'", x)));40 string paramUserAndGroupIds = string.Join(",", userAndGroupIds.ToList().Select(x => string.Format("'{0}'", x))); 41 41 if(!string.IsNullOrWhiteSpace(paramUserAndGroupIds)) { 42 42 string queryString = string.Format(CheckUserGrantedForProjectQueryString, projectId, paramUserAndGroupIds); … … 47 47 48 48 public void DeleteByProjectIdAndGrantedUserIds(Guid projectId, IEnumerable<Guid> grantedUserIds) { 49 string paramIds = string.Join(",", grantedUserIds. Select(x => string.Format("'{0}'", x)));49 string paramIds = string.Join(",", grantedUserIds.ToList().Select(x => string.Format("'{0}'", x))); 50 50 if (!string.IsNullOrWhiteSpace(paramIds)) { 51 51 string query = string.Format(DeleteByProjectIdAndGrantedUserIdsQueryString, projectId, paramIds); … … 55 55 56 56 public void DeleteByProjectIdsAndGrantedUserIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> grantedUserIds) { 57 string paramProjectIds = string.Join(",", projectIds. Select(x => string.Format("'{0}'", x)));58 string paramUserIds = string.Join(",", grantedUserIds. Select(x => string.Format("'{0}'", x)));57 string paramProjectIds = string.Join(",", projectIds.ToList().Select(x => string.Format("'{0}'", x))); 58 string paramUserIds = string.Join(",", grantedUserIds.ToList().Select(x => string.Format("'{0}'", x))); 59 59 if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramUserIds)) { 60 60 string query = string.Format(DeleteByProjectIdsAndGrantedUserIdsQueryString, paramProjectIds, paramUserIds); … … 79 79 private const string DeleteByProjectIdsAndGrantedUserIdsQueryString = @" 80 80 DELETE FROM [ProjectPermission] 81 WHERE ProjectId IN '({0})'81 WHERE ProjectId IN ({0}) 82 82 AND GrantedUserId IN ({1}); 83 83 "; -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ResourceDao.cs
r15540 r15577 38 38 39 39 public bool CheckExistence(IEnumerable<Guid> ids) { 40 string paramResourceIds = string.Join(",", ids. Select(x => string.Format("'{0}'", x)));40 string paramResourceIds = string.Join(",", ids.ToList().Select(x => string.Format("'{0}'", x))); 41 41 if (!string.IsNullOrWhiteSpace(paramResourceIds)) { 42 42 string queryString = string.Format(CountExistenceQuery, paramResourceIds); 43 return DataContext.ExecuteQuery<int>(queryString). Count() == ids.Count();43 return DataContext.ExecuteQuery<int>(queryString).SingleOrDefault() == ids.Count(); 44 44 } 45 45 return false;
Note: See TracChangeset
for help on using the changeset viewer.
