EnVisageOnline/Main-RMO/Source/EnVisage/Code/BLL/PeopleResourcesManager.cs

using EnVisage.Models;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Web;

namespace EnVisage.Code.BLL
{
    public class PeopleResourcesManager : ManagerBase<PeopleResource, PeopleResourceModel>
    {
        public override DbSet<PeopleResource> DataTable
        {
            get
            {
                return DbContext.PeopleResources;
            }
        }

        public PeopleResourcesManager(EnVisageEntities dbContext)
            : base(dbContext) { }

        protected override PeopleResource InitInstance()
        {
            return new PeopleResource
            {
                Id = Guid.NewGuid(),
            };
        }

        protected override PeopleResource RetrieveReadOnlyById(Guid key)
        {
            return DataTable.AsNoTracking().FirstOrDefault(t => t.Id == key);
        }

        public IList<PeopleResourceModel> GetResources()
        {
            var resources = new List<PeopleResourceModel>();

            foreach (var x in DataTable)
            {
                resources.Add(new PeopleResourceModel()
                {
                    Id = x.Id,
                    IsActiveEmployee = x.IsActiveEmployee,
                    FirstName = x.FirstName,
                    LastName = x.LastName,
                    ExpenditureCategory = x.ExpenditureCategory,
                    ExpenditureCategoryId = x.ExpenditureCategoryId,
                    Team = x.Team,
                    TeamId = x.TeamId,
                    StartDate = x.StartDate,
                    EndDate = x.EndDate
                });
            }

            return resources;
        }

        public void DeleteResource(Guid resourceId)
        {
            (DbContext as IObjectContextAdapter).ObjectContext.ExecuteStoreCommand(string.Format("exec sp_DeletePeopleResource '{0}'", resourceId));
            DbContext.SaveChanges();
        }

        public List<PeopleResource> FindResources(IEnumerable<Guid> teams, bool includeAllocations)
        {
            if (teams == null || !teams.Any())
                return new List<PeopleResource>();

			var resources = new List<PeopleResource>();
			if (includeAllocations)
	        {

		        var query =
			        (from resource in DbContext.PeopleResources.Where(x => x.TeamId.HasValue && teams.Contains(x.TeamId.Value))
			         select new
				         {
					         resource,
					         allocations = from pra in resource.PeopleResourceAllocations
					                       join scenario in DbContext.Scenarios on pra.ScenarioId equals scenario.Id
					                       where scenario.Status == (int) ScenarioStatus.Active
					                       select pra
				         }).AsEnumerable().ToList();
		        foreach (var item in query)
		        {
			        var res = item.resource;
					res.PeopleResourceAllocations.Clear();
					if (item.allocations != null)
						foreach (var peopleResourceAllocation in item.allocations)
						{
							res.PeopleResourceAllocations.Add(peopleResourceAllocation);
						}
					resources.Add(res);
		        }
	        }
	        else
	        {
		        resources = DbContext.PeopleResources.Where(x => x.TeamId.HasValue && teams.Contains(x.TeamId.Value)).ToList();
	        }
			return resources;
		}

        /// <summary>Returns tree-structure of resources: Teams[TeamId, ExpCats[ExpCatId, PeopleResourcesList]]</summary>
        public Dictionary<Guid, Dictionary<Guid, List<PeopleResource>>> FindResourcesTree(IEnumerable<Guid> teams, bool includeAllocations)
        {
            return FindResources(teams, includeAllocations)
                        .GroupBy(team => team.TeamId.Value)
                        .ToDictionary(team => team.Key, expCats => expCats.GroupBy(expCat => expCat.ExpenditureCategoryId)
                                                                        .ToDictionary(expCat => expCat.Key, resources => resources.OrderBy(r=>r.LastName).ToList()));
        }

        public List<PeopleResourceTraining> GetTrainings(IEnumerable<Guid> resources, DateTime? startDate = null, DateTime? endDate = null)
        {
            return BuildBasicTrainingsQuery(resources, startDate, endDate).ToList();
        }

        /// <summary>Returns total trainings for group of resources on week ending</summary>
        public Dictionary<Guid, Dictionary<DateTime, int>> GetTotalTrainings(IEnumerable<Guid> resources, DateTime? startDate = null, DateTime? endDate = null)
        {
            var trainingsQuery = BuildBasicTrainingsQuery(resources, startDate, endDate);

            return trainingsQuery.Select(x => new { x.PeopleResource.ExpenditureCategoryId, x.WeekEndingDate, x.HoursOff })
                                 .GroupBy(x => new
                                 {
                                     x.ExpenditureCategoryId,
                                     x.WeekEndingDate
                                 })
                                 .Select(x => new
                                 {
                                     ExpenditureCategoryId = x.Key.ExpenditureCategoryId,
                                     WeekEndingDate = x.Key.WeekEndingDate,
                                     HoursOff = x.Sum(r => r.HoursOff)
                                 })
                                 .ToList()
                                 .GroupBy(x => x.ExpenditureCategoryId)
                                 .ToDictionary(x => x.Key, g => g.ToDictionary(v => v.WeekEndingDate, val => val.HoursOff));
        }

        public List<PeopleResourceVacation> GetVacations(IEnumerable<Guid> resources, DateTime? startDate = null, DateTime? endDate = null)
        {
            return BuildBasicVacationsQuery(resources, startDate, endDate).ToList();
        }

        /// <summary>Returns total vacations for group of resources on week ending</summary>
        public Dictionary<Guid, Dictionary<DateTime, int>> GetTotalVacaitons(IEnumerable<Guid> resources, DateTime? startDate = null, DateTime? endDate = null)
        {
            var vacationsQuery = BuildBasicVacationsQuery(resources, startDate, endDate);

            return vacationsQuery.Select(x => new { x.PeopleResource.ExpenditureCategoryId, x.WeekEndingDate, x.HoursOff })
                                 .GroupBy(x => new
                                 {
                                     x.ExpenditureCategoryId,
                                     x.WeekEndingDate
                                 })
                                 .Select(x => new
                                 {
                                     ExpenditureCategoryId = x.Key.ExpenditureCategoryId,
                                     WeekEndingDate = x.Key.WeekEndingDate,
                                     HoursOff = x.Sum(r => r.HoursOff)
                                 })
                                 .ToList()
                                 .GroupBy(x => x.ExpenditureCategoryId)
                                 .ToDictionary(x => x.Key, g => g.ToDictionary(v => v.WeekEndingDate, val => val.HoursOff));
        }

        public decimal ResolveResourceLeaving(Guid resourceId, DateTime weekEnding, List<ResourceLeavingInfoModel> leavingInfo)
        {
            return ResolveResourceLeaving(new List<Guid>() { resourceId }, weekEnding, leavingInfo);
        }

        public decimal ResolveResourceLeaving(List<Guid> resources, DateTime weekEnding, List<ResourceLeavingInfoModel> leavingInfo)
        {
            if (leavingInfo == null)
                return 0;

            return leavingInfo.Where(t => resources.Contains(t.PeopleResourceId) && t.WeekEndingDate >= weekEnding.AddDays(-6) && t.WeekEndingDate <= weekEnding)
                              .Sum(s => s.HoursOff);
        }

        #region Private Methods

        private IQueryable<PeopleResourceTraining> BuildBasicTrainingsQuery(IEnumerable<Guid> resources, DateTime? startDate, DateTime? endDate)
        {
            var trainingsQuery = DbContext.PeopleResourceTrainings.Where(x => resources.Contains(x.PeopleResourceId));
            if (startDate.HasValue)
                trainingsQuery = trainingsQuery.Where(x => x.WeekEndingDate >= startDate.Value);
            if (endDate.HasValue)
                trainingsQuery = trainingsQuery.Where(x => x.WeekEndingDate <= endDate.Value);
            return trainingsQuery;
        }

        private IQueryable<PeopleResourceVacation> BuildBasicVacationsQuery(IEnumerable<Guid> resources, DateTime? startDate, DateTime? endDate)
        {
            var vacationsQuery = DbContext.PeopleResourceVacations.Where(x => resources.Contains(x.PeopleResourceId));
            if (startDate.HasValue)
                vacationsQuery = vacationsQuery.Where(x => x.WeekEndingDate >= startDate.Value);
            if (endDate.HasValue)
                vacationsQuery = vacationsQuery.Where(x => x.WeekEndingDate <= endDate.Value);
            return vacationsQuery;
        }

        #endregion
    }
}