#region License Information /* HeuristicLab * Copyright (C) 2002-2017 Heuristic and Evolutionary Algorithms Laboratory (HEAL) * * This file is part of HeuristicLab. * * HeuristicLab is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * HeuristicLab is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with HeuristicLab. If not, see . */ #endregion using System; using System.Collections.Generic; using System.Data.Linq; using System.Linq; namespace HeuristicLab.Services.Hive.DataAccess.Daos { public class AssignedProjectResourceDao : GenericDao { public AssignedProjectResourceDao(DataContext dataContext) : base(dataContext) { } public override AssignedProjectResource GetById(Guid id) { throw new NotImplementedException(); } public IQueryable GetByProjectId(Guid projectId) { return Table.Where(x => x.ProjectId == projectId); } public void DeleteByProjectAndGrantedUserId(Guid projectId, IEnumerable resourceIds) { string paramIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x))); if (!string.IsNullOrWhiteSpace(paramIds)) { string query = string.Format(DeleteByGrantedUserQuery, projectId, paramIds); DataContext.ExecuteCommand(query); } } public bool CheckProjectGrantedForResources(Guid projectId, IEnumerable resourceIds) { string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x))); if (!string.IsNullOrWhiteSpace(paramResourceIds)) { string queryString = string.Format(CheckProjectGrantedForResourcesQueryString, projectId, paramResourceIds); return DataContext.ExecuteQuery(queryString).Count() == 0; } return false; } public IEnumerable GetAllGrantedResourcesByProjectId(Guid projectId) { return DataContext.ExecuteQuery(GetAllGrantedResourcesByProjectIdQueryString, projectId); } public IEnumerable GetAllGrantedResourceIdsByProjectId(Guid projectId) { return DataContext.ExecuteQuery(GetAllGrantedResourceIdsByProjectIdQueryString, projectId); } public IEnumerable GetAllGrantedResourceIdsOfOwnedParentProjects(Guid projectId, Guid userId) { return DataContext.ExecuteQuery(GetAllGrantedResourceIdsOfOwnedParentProjectsQueryString, projectId, userId); } #region Compiled queries private static readonly Func> GetByProjectIdGetByIdQuery = CompiledQuery.Compile((DataContext db, Guid projectId) => from projectPermission in db.GetTable() where projectPermission.ProjectId == projectId select projectPermission); #endregion #region String queries private const string DeleteByGrantedUserQuery = @"DELETE FROM [AssignedProjectResource] WHERE ProjectId = '{0}' AND ResourceId IN ({1});"; private const string CheckProjectGrantedForResourcesQueryString = @" WITH rtree AS ( SELECT ResourceId, ParentResourceId FROM [Resource] UNION ALL SELECT rt.ResourceId, r.ParentResourceId FROM [Resource] r JOIN rtree rt ON rt.ParentResourceId = r.ResourceId ) SELECT r.ResourceId FROM [Resource] r WHERE r.ResourceId IN ({1}) EXCEPT ( SELECT rtree.ResourceId FROM rtree, [AssignedProjectResource] apr WHERE rtree.ParentResourceId = apr.ResourceId AND apr.ProjectId = {0} UNION SELECT apr.ResourceId FROM [AssignedProjectResource] apr WHERE apr.ProjectId = {0} ) "; private const string GetAllGrantedResourcesByProjectIdQueryString = @" WITH rtree AS ( SELECT ResourceId, ParentResourceId FROM [Resource] UNION ALL SELECT rt.ResourceId, r.ParentResourceId FROM [Resource] r JOIN rtree rt ON rt.ParentResourceId = r.ResourceId ) SELECT res.* FROM rtree, [AssignedProjectResource] apr, [Resource] res WHERE rtree.ParentResourceId = apr.ResourceId AND rtree.ResourceId = res.ResourceId AND apr.ProjectId = {0} UNION SELECT res.* FROM [AssignedProjectResource] apr, [Resource] res WHERE apr.ResourceId = res.ResourceId AND apr.ProjectId = {0} "; private const string GetAllGrantedResourceIdsByProjectIdQueryString = @" WITH rtree AS ( SELECT ResourceId, ParentResourceId FROM [Resource] UNION ALL SELECT rt.ResourceId, r.ParentResourceId FROM [Resource] r JOIN rtree rt ON rt.ParentResourceId = r.ResourceId ) SELECT rtree.ResourceId FROM rtree, [AssignedProjectResource] apr WHERE rtree.ParentResourceId = apr.ResourceId AND apr.ProjectId = {0} UNION SELECT apr.ResourceId FROM [AssignedProjectResource] apr WHERE apr.ProjectId = {0} "; private const string GetAllGrantedResourceIdsOfOwnedParentProjectsQueryString = @" WITH pbranch AS ( SELECT ProjectId, ParentProjectId FROM [Project] UNION ALL SELECT pb.ProjectId, p.ParentProjectId FROM [Project] p JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId ), rtree AS ( SELECT ResourceId, ParentResourceId FROM [Resource] UNION ALL SELECT rt.ResourceId, r.ParentResourceId FROM [Resource] r JOIN rtree rt ON rt.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rt.ParentResourceId <> rt.ResourceId ) SELECT DISTINCT rtree.ResourceId FROM pbranch, rtree, [Project] pro, [AssignedProjectResource] apr WHERE pbranch.ProjectId = {0} AND pbranch.ParentProjectId = pro.ProjectId AND pro.OwnerUserId = {1} AND pbranch.ParentProjectId = apr.ProjectId AND apr.ResourceId = rtree.ParentResourceId "; #endregion } }