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

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HeuristicLab.Services.Hive.DataAccess.Daos {
  public class AssignedJobResourceDao : GenericDao<Guid, AssignedJobResource> {
    public AssignedJobResourceDao(DataContext dataContext) : base(dataContext) { }

    public override AssignedJobResource GetById(Guid id) {
      throw new NotImplementedException();
    }

    public IQueryable<AssignedJobResource> GetByJobId(Guid jobId) {
      return Table.Where(x => x.JobId == jobId);
    }

    public bool CheckJobGrantedForResource(Guid jobId, Guid resourceId) {
      return DataContext.ExecuteQuery<int>(CheckJobGrantedForResourceQueryString, jobId, resourceId).First() > 0;
    }

    public bool CheckJobGrantedForResources(Guid jobId, IEnumerable<Guid> resourceIds) {
      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
        string queryString = string.Format(CheckJobGrantedForResourcesQueryString, jobId, paramResourceIds);
        return DataContext.ExecuteQuery<int>(queryString).Count() == 0;
      }
      return false;
    }

    public bool CheckTaskGrantedForResource(Guid taskId, Guid resourceId) {
      return DataContext.ExecuteQuery<int>(CheckTaskGrantedForResourceQueryString, taskId, resourceId).First() > 0;
    }

    public IEnumerable<Resource> GetAllGrantedResourcesByJobId(Guid jobId) {
      return DataContext.ExecuteQuery<Resource>(GetAllGrantedResourcesByJobIdQueryString, jobId);
    }

    public IEnumerable<Guid> GetAllGrantedResourceIdsByJobId(Guid jobId) {
      return DataContext.ExecuteQuery<Guid>(GetAllGrantedResourceIdsByJobIdQueryString, jobId);
    }

    #region String queries
    private const string CheckJobGrantedForResourceQueryString = @"
      WITH rbranch AS (
        SELECT ResourceId, ParentResourceId
        FROM [Resource]
        WHERE ResourceId = {1}
        UNION ALL
        SELECT r.ResourceId, r.ParentResourceId
        FROM [Resource] r
        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
      )
      SELECT COUNT(ajr.JobId)
      FROM rbranch, AssignedJobResource ajr
      WHERE rbranch.ResourceId = ajr.ResourceId
      AND ajr.JobId = {0}
    ";
    private const string CheckTaskGrantedForResourceQueryString = @"
      WITH rbranch AS (
        SELECT ResourceId, ParentResourceId
        FROM [Resource]
        WHERE ResourceId = {1}
        UNION ALL
        SELECT r.ResourceId, r.ParentResourceId
        FROM [Resource] r
        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
      )
      SELECT COUNT(ajr.JobId)
      FROM rbranch, AssignedJobResource ajr, Task t
      WHERE rbranch.ResourceId = ajr.ResourceId
      AND ajr.JobId = t.JobId
      AND t.JobId = {0}
    ";
    private const string CheckJobGrantedForResourcesQueryString = @"
      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, [AssignedJobResource] ajr
        WHERE rtree.ParentResourceId = ajr.ResourceId
        AND ajr.JobId = {0}
        UNION
        SELECT ajr.ResourceId
        FROM [AssignedJobResource] ajr
        WHERE ajr.JobId = {0}
      )
    ";
    private const string GetAllGrantedResourcesByJobIdQueryString = @"
      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, [AssignedJobResource] ajr, [Resource] res
      WHERE rtree.ParentResourceId = ajr.ResourceId
      AND rtree.ResourceId = res.ResourceId
      AND ajr.JobId = {0}
      UNION
      SELECT res.*
      FROM [AssignedJobResource] ajr, [Resource] res
      WHERE ajr.ResourceId = res.ResourceId
      AND ajr.JobId = {0}
    ";
    private const string GetAllGrantedResourceIdsByJobIdQueryString = @"
    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, [AssignedJobResource] ajr
    WHERE rtree.ParentResourceId = ajr.ResourceId
    AND ajr.JobId = {0}
    UNION
    SELECT ajr.ResourceId
    FROM [AssignedJobResource] ajr
    WHERE ajr.JobId = {0}
    ";
    #endregion
  }
}