using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;
using System.Net;
using System.Web;
using System.Web.Mvc;
using System.Web.Script.Serialization;
using EnVisage;
using EnVisage.Code;
using EnVisage.Code.BLL;
using EnVisage.Code.HtmlHelpers;
using EnVisage.Models;
using Microsoft.AspNet.Identity;
using jQuery.DataTables.Mvc;
using EnVisage.App_Start;
using EnVisage.Code.Cache;

namespace EnVisage.Controllers
{
    [Authorize]
    public class CapacityManagementController : BaseController
    {
        private IQueryable<VW_ScenarioAndProxyDetails> scenarioDetails;

        private class Pairs
        {
            public Guid Id { get; set; }
            public decimal Quantity { get; set; }
        }

        /// <summary>
        /// Direct GET to the page - returns main view
        /// </summary>
        public ActionResult Index(string menuId, string additionalFilters)
        {
            return PartialView("_capacityManagement",
                               new CapacityDetailsOptionsModel { MenuId = menuId, AdditionalFilterParams = additionalFilters });
        }

		public ActionResult ExportToPDF()
		{
			PDFExporter exporter = new PDFExporter();
			exporter.BrowserWidth = 2560;
			byte[] pdfBuffer = exporter.ExportPage(Url.Action("Index", "CapacityManagement", null, this.Request.Url.Scheme), 5, true, Request.Cookies);

			// send the PDF file to browser
			FileResult fileResult = new FileContentResult(pdfBuffer, "application/pdf");
			fileResult.FileDownloadName = "Prevu-ActivityCalendar-" + DateTime.Today.Month + "-" + DateTime.Today.Day + ".pdf";

			return fileResult;
		}

        //TO-DO: review ability to remove this method
        /// <summary>
        /// GET: /Clients/
        /// </summary>
        /// <returns>Empty view</returns>
        [HttpGet]
        [AreaSecurityAttribute(area = Areas.Clients, level = AccessLevel.Read)]
        public ActionResult Index(Guid? id)
        {
            // SA. ENV-799
            CapacityDetailsModel model = new CapacityDetailsModel();
            LoadFilteringOptionsToModel(model);

            if ((id != null) && !id.Value.Equals(Guid.Empty))
            {
                var foundEntity = DbContext.BLL_Objects.Where(x => x.Id.Equals(id.Value)).FirstOrDefault();

                if (foundEntity != null)
                {
                    // Get entity type
                    string entityTypeName = foundEntity.EntityName;

                    if (entityTypeName == "Company")
                        model.CompanyId = id.Value;

                    if (entityTypeName == "View")
                        model.ViewId = id.Value;

                    if (entityTypeName == "Team")
                        model.TeamId = id.Value;

                    if (entityTypeName == "Resource")
                        model.ResourceId = id.Value;
                }
            }

            return View(model);
        }

        /// <summary>
        /// Fills model with options for filters to display on the page
        /// </summary>
        /// <param name="model"></param>
        /// <remarks>SA. ENV-799</remarks>
        private void LoadFilteringOptionsToModel(CapacityDetailsModel model)
        {
            string userIdAsText = this.User.Identity.GetID();
            Guid userId = Guid.Parse(userIdAsText);

            List<SelectListItem> options;

            #region Teams
            TeamManager teamMngr = new TeamManager(DbContext);
            IList<Team> availableTeams = teamMngr.GetTeamsByUser(userId);

            options = (availableTeams.OrderBy(x => x.Name).Select(x => new SelectListItem()
            {
                Text = x.Name,
                Value = x.Id.ToString()
            })).ToList();

            model.OptionsForFilters.Teams = options;
            availableTeams = null;
            teamMngr = null;

            #endregion
            #region Views

            ViewManager viewMngr = new ViewManager(DbContext);
            IList<View> availableViews = viewMngr.GetViewsByOwner(userId);

            options = (availableViews.OrderBy(x => x.Name).Select(x => new SelectListItem()
            {
                Text = x.Name,
                Value = x.Id.ToString()
            })).ToList();

            model.OptionsForFilters.Views = options;
            availableViews = null;
            viewMngr = null;

            #endregion
            #region Companies

            CompanyManager cmpnyMngr = new CompanyManager(DbContext);
            IList<Company> availableCompanies = cmpnyMngr.GetCompaniesByUser(userId);

            options = (availableCompanies.Select(x => new SelectListItem()
            {
                Text = x.Name,
                Value = x.Id.ToString(),
                Group = x.Company2 == null ? null : new SelectListGroup { Name = x.Company2.Name }
            })).ToList();

            model.OptionsForFilters.Companies = options;
            availableCompanies = null;
            cmpnyMngr = null;

            #endregion
            #region Resources

            // SA. Resource list is made as stub. 
            // Client web page shows calendar at a time only for the single and given resource. It doesn't
            // allow a user to select resource from a list. 
            options = new List<SelectListItem>();
            model.OptionsForFilters.Resources = options;

            #endregion
        }

        [HttpPost]
        public ActionResult GetCompanies(string mode)
        {
            if (mode == "team")
                return Json(Utils.GetTeamsAvailableForUser(Guid.Parse(User.Identity.GetID())));
            else if (mode == "view")
                return Json(new ViewManager(DbContext).GetViewsByOwner(Guid.Parse(User.Identity.GetID())));
            else return Json(DbContext.Companies.AsNoTracking().Select(c =>
                new SelectListItem()
                {
                    Text = c.Name,
                    Value = c.Id.ToString()
                }).ToList()
            );

        }

        [HttpPost]
        public ActionResult LoadJsonCalendar(CapacityDetailsModel model)
        {
            //if (string.IsNullOrEmpty(Request.QueryString["teamId"]))
            //    model.TeamId = Guid.Parse(Request.QueryString["teamId"]);
            //else if (string.IsNullOrEmpty(Request.QueryString["viewId"]))
            //    model.ViewId = Guid.Parse(Request.QueryString["viewId"]);

            if (model == null || ((model.CompanyId == null || model.CompanyId == Guid.Empty)
                && (model.TeamId == null || model.TeamId == Guid.Empty)
                && (model.ViewId == null || model.ViewId == Guid.Empty))
                && (model.ResourceId == null || model.ResourceId == Guid.Empty))
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);

            if (model.CompanyId != null && model.CompanyId != Guid.Empty)
            {
                var company = DbContext.Companies.AsNoTracking().FirstOrDefault(t => t.Id == model.CompanyId);
                if (company == null)
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }

            if (model.TeamId != null && model.TeamId != Guid.Empty)
            {
                var team = DbContext.Teams.AsNoTracking().FirstOrDefault(t => t.Id == model.TeamId);
                if (team == null)
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }

            if (model.ViewId != null && model.ViewId != Guid.Empty)
            {
                var team = DbContext.Views.AsNoTracking().FirstOrDefault(t => t.Id == model.ViewId);
                if (team == null)
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            if (model.ResourceId != null && model.ResourceId != Guid.Empty)
            {
                var team = DbContext.PeopleResources.AsNoTracking().FirstOrDefault(t => t.Id == model.ResourceId).Team;
                if (team == null)
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            if (!model.StartDate.HasValue)
            {
                model.StartDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
            }
            if (!model.EndDate.HasValue)
            {
                model.EndDate = model.StartDate.Value.AddMonths(6);
            }
            var jsonResult = Json(GetCalendar(model), JsonRequestBehavior.AllowGet);
            jsonResult.MaxJsonLength = int.MaxValue;
            return jsonResult;
        }

        private List<DateTime> GetPeriodWeekDates(DateTime periodStartDate, DateTime periodEndDate)
        {
            return (from c in DbContext.FiscalCalendars
                    where c.Type == 0 && (c.StartDate <= periodStartDate || c.StartDate <= periodEndDate) && (c.EndDate >= periodStartDate || c.EndDate >= periodEndDate) && c.AdjustingPeriod == false && c.NonWorking == 0
                    orderby c.StartDate
                    select c.EndDate).ToList();

        }

        private CapacityDetailsModel GetCalendar(CapacityDetailsModel model)
        {
            var periodStartDate = model.StartDate.Value;
            var periodEndDate = model.EndDate.Value;
            var actualScenarioId = Guid.Empty;
            var allExpCats = DbContext.ExpenditureCategory.AsNoTracking().ToDictionary(x => x.Id);
            var allUoms = DbContext.UOMs.AsNoTracking().ToDictionary(x => x.Id);

            //var user = new UsersCache().Value.FirstOrDefault(x => x.Id == new Guid(HttpContext.User.Identity.GetID()));
            //if (!model.IsUOMHours.HasValue && user != null)
            //{
            //    model.IsUOMHours = !user.PreferredResourceAllocation;
            //}
            //model.PreferredTotalsDisplaying = user.PreferredTotalsDisplaying;

            var calendarList = new List<CapacityDetailsModel.CalendarRow>();
            var totalsList = new List<CapacityDetailsModel.CalendarRow>();

            List<Project> projects;
            List<Guid> teamIds;
            List<Team> teams;
            List<CapacityDetailsModel.ScenarioCalendarRowResource> allResources;
            //Ids of all projects active scenarios and type ScenarioType.Portfolio
            List<Guid> scenarioIds;

            var groupByTeamMode = model.GroupByTeam && (model.TeamId == null || model.TeamId.Equals(Guid.Empty))
                && (model.ResourceId == null || model.ResourceId.Equals(Guid.Empty));

            var showAvgQuantity = model.PreferredTotalsDisplaying && !model.IsUOMHours;

            BuildHeaders(model, GetPeriodWeekDates(periodStartDate, periodEndDate));
            var weeksCount = model.Headers.Count;

            CollectCommonData(periodStartDate, periodEndDate, weeksCount, model.CompanyId, model.TeamId, model.ViewId, model.ResourceId,
                groupByTeamMode, out projects, out teamIds, out scenarioIds, out teams, out allResources);

            //Dictionary<Guid, List<Team2Scenario>> activeScenariosTeams =
            //    DbContext.Team2Scenario.AsNoTracking().Where(t2s => scenarioIds.Contains(t2s.ScenarioId))
            //    .GroupBy(t2s => t2s.ScenarioId).ToDictionary(key => key.Key, grouping => grouping.ToList());

            // SA. ENV-886. Added condition for teamIds
            Dictionary<Guid, List<Team2Scenario>> activeScenariosTeams =
                DbContext.Team2Scenario.AsNoTracking().Where(t2s =>
                    scenarioIds.Contains(t2s.ScenarioId) && teamIds.Contains(t2s.TeamId))
                .GroupBy(t2s => t2s.ScenarioId).ToDictionary(key => key.Key, grouping => grouping.ToList());

            //var resourceAllocation = DbContext.PeopleResourceAllocations.Where(r => scenarioIds.Contains(r.ScenarioId)).ToList();
            List<PeopleResourceAllocation> resourceAllocation;

            if (!model.ResourceId.HasValue)
                resourceAllocation = DbContext.PeopleResourceAllocations.Where(r => scenarioIds.Contains(r.ScenarioId)).ToList();
            else
                // For individual resource calendar
                resourceAllocation = DbContext.PeopleResourceAllocations.Where(r =>
                    scenarioIds.Contains(r.ScenarioId) && r.PeopleResourceId.Equals(model.ResourceId.Value)).ToList();

            var allResIds = allResources.Select(t => t.Id).Distinct().ToArray();
            var allResourceVacations = DbContext.PeopleResourceVacations.Where(t => allResIds.Contains(t.PeopleResourceId))
                .Select(t => new
            {
                t.PeopleResourceId,
                t.HoursOff,
                t.WeekEndingDate
            }).ToArray();

            var allResourceTrainings = DbContext.PeopleResourceTrainings.Where(t => allResIds.Contains(t.PeopleResourceId))
                .Select(t => new
            {
                t.PeopleResourceId,
                t.HoursOff,
                t.WeekEndingDate
            }).ToArray();

            var totalResourceAllocation = 
				DbContext.PeopleResourceAllocations.AsNoTracking()
                .Where(x => scenarioIds.Contains(x.ScenarioId) && 	 // SA. ENV-962. Added filter by active scenarios
					allResIds.Contains(x.PeopleResourceId) && 
					x.WeekEndingDate >= periodStartDate && x.WeekEndingDate <= periodEndDate).ToList()
                .GroupBy(x => x.PeopleResourceId).ToDictionary(x => x.Key, g => g.GroupBy(t => t.WeekEndingDate).ToDictionary(key => key.Key, group => group.Sum(s => s.Quantity ?? 0)));

            string defaultColor = "";
            var settings = this.DbContext.SystemSettings.FirstOrDefault(
                item =>
                item.Type == (int)SystemSettingType.DefaultProjectColorType);
            if (settings != null)
            {
                defaultColor = settings.Value;
            }
            if (!string.IsNullOrEmpty(defaultColor) && !defaultColor.StartsWith("#"))
                defaultColor = "#" + defaultColor;

            foreach (var resource in allResources)
            {
                var UOMId = allExpCats[resource.ExpedentureCategoryId.Value].UOMId;
                var uomMultiplier = Utils.GetUOMMultiplier(allExpCats, allUoms, resource.ExpedentureCategoryId ?? Guid.Empty, model.IsUOMHours);
                if (allUoms.ContainsKey(UOMId))
                {
                    for (var colIndex = 0; colIndex < model.Headers.Count; colIndex++)
                    {
                        var date = Utils.ConvertFromUnixDate(model.Headers[colIndex].Milliseconds);

                        //Collect vacations
                        var vacationsSum = allResourceVacations.Where(t => t.PeopleResourceId == resource.Id &&
                            t.WeekEndingDate <= date && t.WeekEndingDate >= date.AddDays(-6)).Sum(s => s.HoursOff);

                        //Collect trainings
                        var trainingsSum = allResourceTrainings.Where(t => t.PeopleResourceId == resource.Id &&
                            t.WeekEndingDate <= date && t.WeekEndingDate >= date.AddDays(-6)).Sum(s => s.HoursOff);

                        resource.CapacityQuantityValues[colIndex] = (allUoms[UOMId].UOMValue - vacationsSum - trainingsSum)
                            * uomMultiplier;

                        if (totalResourceAllocation.ContainsKey(resource.Id) && totalResourceAllocation[resource.Id].ContainsKey(date))
                        {
                            resource.AllocatedQuantityValues[colIndex] = totalResourceAllocation[resource.Id][date] * uomMultiplier;
                        }
                        else
                        {
                            resource.AllocatedQuantityValues[colIndex] = 0m;
                        }

                        if (!model.Headers[colIndex].IsMonth)
                            resource.ReadOnlyWeeks[colIndex] = date < resource.StartDate || date > resource.EndDate;
                        //x.RestQuantityValues[colIndex] = x.CapacityQuantityValues[colIndex] - 
                        //    resourceAllocation.Where(ra => ra.PeopleResourceId == x.Id && ra.WeekEndingDate == date).Sum(ra => ra.Quantity.Value);
                    }
                }
            }

            Dictionary<Guid, List<Guid>> epxCategoriesByTeams =
                    (from t2s in DbContext.Team2Scenario
                     join team in DbContext.Teams on t2s.TeamId equals team.Id
                     join pr in DbContext.PeopleResources on team.Id equals pr.TeamId
                     into prl
                     from p in prl.DefaultIfEmpty()
                     where scenarioIds.Contains(t2s.ScenarioId) &&
                         // SA. ENV-886. The following condition added
                        (!model.ResourceId.HasValue || (model.ResourceId.HasValue && p.Id.Equals(model.ResourceId.Value)))
                     select new
                     {
                         TeamId = t2s.TeamId,
                         ExpCat = (p == null ? Guid.Empty : p.ExpenditureCategoryId)
                     })
                    .GroupBy(x => x.TeamId).ToDictionary(x => x.Key, val => val.Select(z => z.ExpCat).ToList());

            #region Convert scenarioDetails to scenarioDetailsInternal
            var scenarioDetailsInternal = scenarioDetails.Select(t => new
            {
                t.Id,
                t.ExpenditureCategoryId,
                t.WeekEndingDate,
                t.GLId,
                t.ExpCategoryWithCcName,    // SA. ENV-839
                t.Quantity,
                t.Cost,
                t.ParentID,
                t.WeekOrdinal,
                t.UseType,
                t.UOMId
            }).ToArray().GroupBy(
                    key => new
                    {
                        key.ExpenditureCategoryId,
                        key.ExpCategoryWithCcName,    // SA. ENV-839
                        key.ParentID,
                        key.GLId,
                        key.UseType,
                        key.UOMId
                    }).ToDictionary(key => key.Key, grouping => grouping.ToList());

            #endregion

            //Add Total (Active Scenarios), Vacation, Training, Loan-outs, Capacity
            totalsList.AddRange(AddTotalsRows(weeksCount));
            CapacityDetailsModel.CalendarRow vacationRow = totalsList.FirstOrDefault(x => x.RowType == CapacityDetailsModel.RowType.Vacation);
            CapacityDetailsModel.CalendarRow trainingRow = totalsList.FirstOrDefault(x => x.RowType == CapacityDetailsModel.RowType.Training);
            CapacityDetailsModel.CalendarRow capacityRow = totalsList.FirstOrDefault(x => x.RowType == CapacityDetailsModel.RowType.Capacity);
            CapacityDetailsModel.CalendarRow grandTotalRow = totalsList.FirstOrDefault(x => x.RowType == CapacityDetailsModel.RowType.Total);

            foreach (var team in teams.OrderBy(a => a.Name))
            {
                var groupByTeam = !Guid.Empty.Equals(team.Id);
                if (groupByTeam && groupByTeamMode)
                {
                    #region Add Team row
                    calendarList.Add(new CapacityDetailsModel.CalendarRow()
                    {
                        Name = team.Name,
                        RowType = CapacityDetailsModel.RowType.Team,
                    });
                    #endregion
                }

                var query = projects.Where(p => !groupByTeam || p.Team2Project.Select(t2p => t2p.TeamId).Contains(team.Id));
                if (model.sortBy == "Type")
                    if (model.sortOrder)
                        query = query.OrderBy(x => x.Type.Name).ThenBy(x => x.Name);
                    else query = query.OrderByDescending(x => x.Type.Name).ThenByDescending(x => x.Name);
                else if (model.sortBy == "Date")
                    if (model.sortOrder)
                        query = query.OrderBy(x => x.Deadline).ThenBy(x => x.Name);
                    else query = query.OrderByDescending(x => x.Deadline).ThenByDescending(x => x.Name);
                else if (model.sortBy == "Priority")
                    if (model.sortOrder)
                        query = query.OrderBy(x => x.Priority).ThenBy(x => x.Name);
                    else query = query.OrderByDescending(x => x.Priority).ThenByDescending(x => x.Name);
                else
                    if (model.sortOrder)
                        query = query.OrderBy(x => x.Name);
                    else query = query.OrderByDescending(x => x.Name);


                var teamProjects = query.ToList();
                foreach (var project in teamProjects)
                {
                    var projectActiveScenarios = project.Scenarios.Where(s => s.Status.HasValue && s.Status.Value == (int)ScenarioStatus.Active
                        && s.Type == (int)ScenarioType.Portfolio && s.StartDate < model.EndDate.Value && s.EndDate > model.StartDate.Value).OrderBy(p => p.Name).ThenBy(p => p.Id);

                    var projectInactiveScenarios = project.Scenarios.Where(s => (!s.Status.HasValue || s.Status.Value != (int)ScenarioStatus.Active)
                        && s.Type == (int)ScenarioType.Portfolio).OrderBy(p => p.Name).ThenBy(p => p.Id).Select(t => new CapacityDetailsModel.CalendarScenarioItem
                            {
                                Id = t.Id,
                                Name = t.Name
                            }).ToArray();

                    if (model.ResourceId.HasValue)
                    {
                        var resource = DbContext.PeopleResources.AsNoTracking().FirstOrDefault(r => r.Id == model.ResourceId.Value);
                        //Get all ExpCats by available resorces
                        var expCats1 = scenarioDetailsInternal.Where(sd => resource.ExpenditureCategoryId == sd.Key.ExpenditureCategoryId.Value).ToDictionary(key => key.Key.ParentID, pairs => pairs);

                        projectActiveScenarios = projectActiveScenarios.Where(s => expCats1.ContainsKey(s.Id)).OrderBy(p => p.Name).ThenBy(p => p.Id);
                        projectInactiveScenarios = projectInactiveScenarios.Where(s => expCats1.ContainsKey(s.Id)).ToArray();
                    }

                    if (!projectActiveScenarios.Any())
                        continue;

                    #region Add Project row

                    calendarList.Add(new CapacityDetailsModel.CalendarRow()
                    {
                        ProjectId = project.Id,
                        ParentProjectId = project.ParentProject != null ? project.ParentProject.Id : project.Id,
                        PartId = project.ParentProject != null ? (Guid?)project.Id : null,
                        Name = project.Name + (project.ParentProject != null && !string.IsNullOrWhiteSpace(project.ParentProject.Name) ? ": " + project.ParentProject.Name : ""),
                        Color = setProjectColor(project, defaultColor),
                        //Color = !string.IsNullOrEmpty(project.Color) ? project.Color.Contains('#') ? project.Color : "#" + project.Color :
                        //    (project.ParentProject != null && !string.IsNullOrWhiteSpace(project.ParentProject.Color) ? project.ParentProject.Color.Contains('#') ? project.ParentProject.Color : "#" + project.ParentProject.Color : null),
                        DetailIds = new Guid?[weeksCount],
                        QuantityValues = new decimal[weeksCount],
                        SpreadVal = new CapacityDetailsModel.Spread[weeksCount],
                        IsParentCollapsed = false,
                        ExpCatId = null,
                        ScenarioId = null,
                        RowType = CapacityDetailsModel.RowType.Project,
                        ReadOnly = new bool[weeksCount],
                        TeamId = team.Id,
                        InactiveScenarios = projectInactiveScenarios
                    });

                    #endregion

                    foreach (var scenario in projectActiveScenarios)
                    {
                        var projectRow = calendarList.FirstOrDefault(x => x.ProjectId == project.Id && x.TeamId == team.Id && x.ExpCatId == null);

                        #region Add Scenario to Project row

                        if (projectRow != null && projectRow.ScenarioId == null)
                        {
                            projectRow.ScenarioId = scenario.Id;
                            projectRow.Name1 = scenario.Name;
                            projectRow.StartDate = (long)scenario.StartDate.Value.Subtract(Constants.UnixEpochDate).TotalMilliseconds;
                            // last scenario detail exists on the next week ending after scenario ends if EndDate does not WeekEnding
                            projectRow.EndDate = (long)scenario.EndDate.Value.AddDays(6).Subtract(Constants.UnixEpochDate).TotalMilliseconds;
                            var ind = 0;
                            model.Headers.ForEach(h => projectRow.ReadOnly[ind++] = !(h.Milliseconds >= projectRow.StartDate &&
                                    h.Milliseconds <= projectRow.EndDate));
                        }

                        #endregion

                        List<Guid?> project2ExpCats = allResources
                            .Where(r => ((groupByTeam && team.Id.Equals(r.TeamId)) || (!groupByTeam && teamIds.Contains(r.TeamId))))
                            .Select(r => r.ExpedentureCategoryId).ToList();

                        if (model.ResourceId.HasValue)
                        {
                            project2ExpCats = DbContext.PeopleResources.AsNoTracking().Where(r => r.Id == model.ResourceId.Value).Select(x => (Guid?)x.ExpenditureCategoryId).ToList();
                        }

                        //Get all ExpCats by available resorces
                        var expCats = scenarioDetailsInternal.Where(sd => sd.Key.ParentID == scenario.Id &&
                            project2ExpCats.Contains(sd.Key.ExpenditureCategoryId.Value));

                        foreach (var expCat in expCats)
                        {
                            #region Add exp cat row for the top part of the calendar

                            calendarList.Add(new CapacityDetailsModel.CalendarRow()
                            {
                                ProjectId = project.Id,
                                ScenarioId = scenario.Id,
                                TeamId = team.Id,
                                ExpCatId = expCat.Key.ExpenditureCategoryId,
                                Name = expCat.Key.ExpCategoryWithCcName,   // SA. ENV-839
                                DetailIds = new Guid?[weeksCount],
                                QuantityValues = new decimal[weeksCount],
                                SpreadVal = new CapacityDetailsModel.Spread[weeksCount],
                                CollapsedClass = "fa-plus-square-2",
                                RestQuantity = new decimal[weeksCount],
                                ReadOnly = new bool[weeksCount],
                                EmptyScenario = false,
                                RowType = CapacityDetailsModel.RowType.ProjectExpenditureCategory,
                                Resources = allResources.Where(x => resourceAllocation.Where(ec => ec.ExpenditureCategoryId == expCat.Key.ExpenditureCategoryId
                                    && ec.ScenarioId == scenario.Id && (!groupByTeam || (groupByTeam && team.Id == x.TeamId)) && (!model.ResourceId.HasValue || x.Id == model.ResourceId.Value))
                                .Select(r => r.PeopleResourceId).Contains(x.Id))
                                .Select(x => new CapacityDetailsModel.ScenarioCalendarRowResource()
                                {
                                    Id = x.Id,
                                    Name = x.Name,
                                    QuantityValues = new decimal[weeksCount],
                                    CapacityQuantityValues = new decimal[weeksCount],
                                    VacationQuantityValues = new decimal[weeksCount],
                                    TrainingQuantityValues = new decimal[weeksCount],
                                    SpreadVal = new CapacityDetailsModel.Spread[weeksCount],
                                    //AllocatedQuantityValues = new decimal[weeksCount],
                                    ReadOnly = !teamIds.Contains(x.TeamId) || (model.ResourceId.HasValue && model.ResourceId != x.Id),
                                    ReadOnlyWeeks = x.ReadOnlyWeeks,
                                    IsActiveEmployee = x.IsActiveEmployee,
                                    TeamId = x.TeamId,
                                    StartDate = x.StartDate,
                                    EndDate = x.EndDate

                                }).ToList(),
                            });

                            #endregion

                            var expCatRow = calendarList.Last();

                            #region Add exp cat row for the bottom part of the calendar

                            var totalsListExpCat = totalsList.FirstOrDefault(t => t.ExpCatId == expCatRow.ExpCatId);
                            if (totalsListExpCat == null)
                            {
                                totalsListExpCat = new CapacityDetailsModel.CalendarRow()
                                {
                                    ExpCatId = expCat.Key.ExpenditureCategoryId,
                                    Name = expCat.Key.ExpCategoryWithCcName, // SA. ENV-839
                                    QuantityValues = new decimal[weeksCount],
                                    SpreadVal = new CapacityDetailsModel.Spread[weeksCount],
                                    CollapsedClass = "fa-plus-square",
                                    IsParentCollapsed = false,
                                    EmptyScenario = false,
                                    QuantityTotalResValue = new decimal[weeksCount],
                                    QuantityExpCatTotalValue = new decimal[weeksCount],
                                    RowType = CapacityDetailsModel.RowType.BottomCategory,
                                    Resources = new List<CapacityDetailsModel.ScenarioCalendarRowResource>(allResources.
                                        Where(x => x.ExpedentureCategoryId == expCat.Key.ExpenditureCategoryId && teamIds.Contains(x.TeamId) && (!model.ResourceId.HasValue || x.Id == model.ResourceId.Value)).
                                        Select(x =>
                                        new CapacityDetailsModel.ScenarioCalendarRowResource()
                                        {
                                            Id = x.Id,
                                            ExpedentureCategoryId = x.ExpedentureCategoryId,
                                            Name = x.Name,
                                            QuantityValues = new decimal[weeksCount],
                                            SpreadVal = new CapacityDetailsModel.Spread[weeksCount],
                                            QuantityTotalResValue = new decimal[weeksCount],
                                            VacationQuantityValues = new decimal[weeksCount],
                                            TrainingQuantityValues = new decimal[weeksCount],
                                            //AllocatedQuantityValues = new decimal[weeksCount],
                                            GrandTotalQuantity = 0M,
                                            IsActiveEmployee = x.IsActiveEmployee,
                                            TeamId = x.TeamId,
                                            StartDate = x.StartDate,
                                            EndDate = x.EndDate
                                        }))
                                };
                                totalsList.Add(totalsListExpCat);
                            }

                            #endregion

                            projectRow.EmptyScenario = false;

                            var uomMultiplier = Utils.GetUOMMultiplier(allExpCats, allUoms, expCat.Key.ExpenditureCategoryId ?? Guid.Empty, model.IsUOMHours);
                            var uomValue = allUoms.ContainsKey(expCat.Key.UOMId) ? allUoms[expCat.Key.UOMId].UOMValue * uomMultiplier : 0;

                            var monthQuantity = 0.0M;
                            var monthResVal = 0.0M;
                            var monthResTotalVal = 0.0M;
                            var resourceTotals = allResources.Select(x => x.Id).ToDictionary(key => key, pairs => 0.0M);
                            var resourceTotalsExpCatTotals = allResources.Where(x => !model.ResourceId.HasValue || x.Id == model.ResourceId.Value).Select(x => new Pairs() { Id = x.Id, Quantity = 0.0M }).ToList();
                            var isMonthReadOnly = true;

                            decimal teamAssignmentMultiplier = 0;
                            var teamsAllocation = new Dictionary<DateTime, decimal>();

                            if (activeScenariosTeams.ContainsKey(scenario.Id) && expCat.Key.ExpenditureCategoryId.HasValue)
                            {
                                teamAssignmentMultiplier = GetProjectNeedMultiplier(expCat.Key.ExpenditureCategoryId.Value,
                                       teamIds, activeScenariosTeams[scenario.Id], epxCategoriesByTeams);

                                foreach (var team2Scenario in activeScenariosTeams[scenario.Id])
                                {
                                    var teamInScenario = new CapacityDetailsModel.ActiveScenariosTeams()
                                    {
                                        Id = team2Scenario.TeamId,
                                        QuantityValues = new decimal[weeksCount],
                                        AllocatedByResources = new decimal[weeksCount]
                                    };

                                    var allocation = DbContext.TeamAllocations.AsNoTracking().Where(ta => ta.ExpenditureCategoryId == expCat.Key.ExpenditureCategoryId.Value &&
                                        team2Scenario.TeamId == ta.TeamId &&
                                        ta.ScenarioId == scenario.Id &&
                                        ta.WeekEndingDate >= model.StartDate.Value && ta.WeekEndingDate <= model.EndDate.Value)
                                        .ToDictionary(x => x.WeekEndingDate, g => g.Quantity * uomMultiplier);

                                    for (int colIndex = 0; colIndex < weeksCount; colIndex++)
                                    {
                                        if (model.Headers[colIndex].IsMonth)
                                            continue;

                                        var weekEnding = Constants.UnixEpochDate.AddMilliseconds(model.Headers[colIndex].Milliseconds);
                                        if (!allocation.ContainsKey(weekEnding))
                                            continue;

                                        teamInScenario.QuantityValues[colIndex] = allocation[weekEnding];
                                    }

                                    expCatRow.Teams.Add(teamInScenario);
                                }

                                if (groupByTeam)
                                {
                                    teamsAllocation = DbContext.TeamAllocations.AsNoTracking().Where(ta => ta.ExpenditureCategoryId == expCat.Key.ExpenditureCategoryId.Value &&
                                        ta.TeamId == team.Id &&
                                        ta.ScenarioId == scenario.Id &&
                                        ta.WeekEndingDate >= model.StartDate.Value && ta.WeekEndingDate <= model.EndDate.Value).
                                        GroupBy(g => g.WeekEndingDate).ToDictionary(x => x.Key, g => g.Sum(s => s.Quantity));

                                    teamAssignmentMultiplier = GetProjectNeedMultiplier(expCat.Key.ExpenditureCategoryId.Value,
                                        new List<Guid>() { team.Id }, activeScenariosTeams[scenario.Id], epxCategoriesByTeams);
                                }
                                else
                                {
                                    teamsAllocation = DbContext.TeamAllocations.AsNoTracking().Where(ta => ta.ExpenditureCategoryId == expCat.Key.ExpenditureCategoryId.Value &&
                                        teamIds.Contains(ta.TeamId) &&
                                        ta.ScenarioId == scenario.Id &&
                                        ta.WeekEndingDate >= model.StartDate.Value && ta.WeekEndingDate <= model.EndDate.Value).
                                        GroupBy(g => g.WeekEndingDate).ToDictionary(x => x.Key, g => g.Sum(s => s.Quantity));

                                    teamAssignmentMultiplier = GetProjectNeedMultiplier(expCat.Key.ExpenditureCategoryId.Value,
                                        teamIds, activeScenariosTeams[scenario.Id], epxCategoriesByTeams);
                                }
                            }

                            // iterate through the weeks/months collection (1 month item following 4/5 week items)
                            for (int colIndex = 0; colIndex < weeksCount; colIndex++)
                            {
                                var isScenarioDate = (model.Headers[colIndex].Milliseconds >= projectRow.StartDate &&
                                    model.Headers[colIndex].Milliseconds <= projectRow.EndDate);

                                expCatRow.ReadOnly[colIndex] = !isScenarioDate;
                                projectRow.ReadOnly[colIndex] = !isScenarioDate;
                                isMonthReadOnly &= !isScenarioDate;

                                // if item is a new week
                                if (!model.Headers[colIndex].IsMonth)
                                {
                                    #region Calc week cell
                                    var date = Utils.ConvertFromUnixDate(model.Headers[colIndex].Milliseconds);
                                    if (isScenarioDate)
                                    {
                                        var val = expCat.Value.FirstOrDefault(x => x.WeekEndingDate.Value.Subtract(Constants.UnixEpochDate).TotalMilliseconds == model.Headers[colIndex].Milliseconds);
                                        if (val != null)
                                        {
                                            expCatRow.DetailIds[colIndex] = val.Id; // column.Id;
                                            if (teamsAllocation != null && teamsAllocation.ContainsKey(date))
                                                expCatRow.QuantityValues[colIndex] = teamsAllocation[date] * uomMultiplier;
                                            else
                                                expCatRow.QuantityValues[colIndex] = (val.Quantity ?? 0) * uomMultiplier * teamAssignmentMultiplier; // (column.Quantity ?? 0) * uomMultiplier;
                                        }
                                    }
                                    else
                                    {
                                        expCatRow.QuantityValues[colIndex] = 0;
                                        expCatRow.DetailIds[colIndex] = null;
                                    }

                                    //Get allocation for the date
                                    var currAllocation = resourceAllocation.Where(r => r.WeekEndingDate == date
                                        && r.ExpenditureCategoryId == expCatRow.ExpCatId && r.ScenarioId == expCatRow.ScenarioId).ToList();

                                    AddECForWeek(expCatRow, projectRow, grandTotalRow, currAllocation, totalsListExpCat,
                                        colIndex, uomMultiplier, uomValue, date, model.ResourceId.HasValue);


                                    expCatRow.Resources.ForEach(x =>
                                    {
                                        //if (allUoms.ContainsKey(expCat.Key.UOMId))
                                        //{
                                        //    //Collect vacations
                                        //    var vacationsSum = allResourceVacations.Where(t => t.PeopleResourceId == x.Id &&
                                        //        t.WeekEndingDate <= date && t.WeekEndingDate >= date.AddDays(-6)).Sum(s => s.HoursOff);

                                        //    vacationRow.QuantityValues[colIndex] += vacationsSum * uomMultiplier;
                                        //    monthVacation += vacationsSum * uomMultiplier;
                                        //    grandTotalRow.QuantityValues[colIndex] += vacationsSum * uomMultiplier;

                                        //    //Collect trainings
                                        //    var trainingsSum = allResourceTrainings.Where(t => t.PeopleResourceId == x.Id &&
                                        //        t.WeekEndingDate <= date && t.WeekEndingDate >= date.AddDays(-6)).Sum(s => s.HoursOff);

                                        //    trainingRow.QuantityValues[colIndex] += trainingsSum * uomMultiplier;
                                        //    monthTraining += trainingsSum * uomMultiplier;
                                        //    grandTotalRow.QuantityValues[colIndex] += trainingsSum * uomMultiplier;

                                        //    x.CapacityQuantityValues[colIndex] = (allUoms[expCat.Key.UOMId].UOMValue - vacationsSum - trainingsSum)
                                        //        * uomMultiplier;

                                        //    //x.RestQuantityValues[colIndex] = x.CapacityQuantityValues[colIndex] - 
                                        //    //    resourceAllocation.Where(ra => ra.PeopleResourceId == x.Id && ra.WeekEndingDate == date).Sum(ra => ra.Quantity.Value);
                                        //}
                                        if (resourceTotals.ContainsKey(x.Id))
                                            resourceTotals[x.Id] += x.QuantityValues[colIndex];

                                        resourceTotalsExpCatTotals.FirstOrDefault(r => r.Id == x.Id)
                                            .Quantity += x.QuantityValues[colIndex];

                                        var teamResourceAllocation = expCatRow.Teams.FirstOrDefault(t => t.Id == x.TeamId);
                                        if (teamResourceAllocation != null)
                                            teamResourceAllocation.AllocatedByResources[colIndex] += x.QuantityValues[colIndex];
                                    });

                                    expCatRow.Resources.ForEach(r => r.SpreadVal[colIndex] = compareValues(r.QuantityValues[colIndex], expCatRow.QuantityValues[colIndex]) == 0 ? CapacityDetailsModel.Spread.Equal :
                                   compareValues(r.QuantityValues[colIndex], expCatRow.QuantityValues[colIndex]) > 0 ? CapacityDetailsModel.Spread.Over :
                                   compareValues(r.QuantityValues[colIndex], expCatRow.QuantityValues[colIndex]) < 0 ? CapacityDetailsModel.Spread.Less : CapacityDetailsModel.Spread.Notspecified);

                                    //Collect monthely EC quantity
                                    monthQuantity += expCatRow.QuantityValues[colIndex];
                                    //Collect monthely EC resource capacity
                                    monthResVal += uomValue;
                                    //Collect monthely EC resource capacity for all EC resources
                                    monthResTotalVal += totalsListExpCat.QuantityTotalResValue[colIndex];

                                    #endregion
                                }
                                else//if (model.Headers[colIndex].IsMonth) if item is a month then let's summarize data of it's weeks
                                {
                                    #region Calc month total cell
                                    expCatRow.DetailIds[colIndex] = Guid.Empty;
                                    if (showAvgQuantity)
                                    {
                                        expCatRow.QuantityValues[colIndex] = Math.Round(monthQuantity / model.Headers[colIndex].Weeks.Count(), 3);
                                        expCatRow.Resources.ForEach(x =>
                                        {
                                            x.QuantityValues[colIndex] = Math.Round((resourceTotals.ContainsKey(x.Id)
                                                                             ? resourceTotals[x.Id]
                                                                             : 0) / model.Headers[colIndex].Weeks.Count(), 3);
                                        });
                                        var date = Constants.UnixEpochDate.AddSeconds(model.Headers[colIndex].Milliseconds / 1000);
                                        totalsListExpCat.Resources.ForEach(x =>
                                        {
                                            x.QuantityTotalResValue[colIndex] = (x.StartDate <= date && (x.EndDate >= date || x.EndDate == DateTime.MinValue) && x.IsActiveEmployee ? Math.Round(monthResVal / model.Headers[colIndex].Weeks.Count(), 3) : 0);
                                            x.QuantityValues[colIndex] += Math.Round(resourceTotalsExpCatTotals.FirstOrDefault(r => r.Id == x.Id).Quantity / model.Headers[colIndex].Weeks.Count(), 3);
                                        });


                                    }
                                    else
                                    {
                                        expCatRow.QuantityValues[colIndex] = monthQuantity;
                                        expCatRow.Resources.ForEach(x =>
                                        {
                                            x.QuantityValues[colIndex] = resourceTotals.ContainsKey(x.Id)
                                                                             ? resourceTotals[x.Id]
                                                                             : 0;
                                        });
                                        var date = Constants.UnixEpochDate.AddSeconds(model.Headers[colIndex].Milliseconds / 1000);
                                        totalsListExpCat.Resources.ForEach(x =>
                                        {
                                            x.QuantityTotalResValue[colIndex] = (x.StartDate <= date && (x.EndDate >= date || x.EndDate == DateTime.MinValue) && x.IsActiveEmployee ? monthResVal : 0);
                                            x.QuantityValues[colIndex] += resourceTotalsExpCatTotals.FirstOrDefault(r => r.Id == x.Id).Quantity;
                                        });
                                    }

                                    totalsListExpCat.QuantityExpCatTotalValue[colIndex] += expCatRow.QuantityValues[colIndex];
                                    totalsListExpCat.QuantityValues[colIndex] = totalsListExpCat.Resources.Select(x => x.QuantityValues[colIndex]).Sum();

                                    expCatRow.ReadOnly[colIndex] = isMonthReadOnly;
                                    projectRow.ReadOnly[colIndex] = isMonthReadOnly;

                                    isMonthReadOnly = true;

                                    //vacationRow.QuantityValues[colIndex] += monthVacation;
                                    //trainingRow.QuantityValues[colIndex] += monthTraining;

                                    //Project/Scenario row update
                                    projectRow.QuantityValues[colIndex] += expCatRow.QuantityValues[colIndex];
									if (model.ResourceId.HasValue) // show resource total in individual calendar
									{
										grandTotalRow.QuantityValues[colIndex] += expCatRow.Resources.Sum(t => t.QuantityValues[colIndex]);
									}
									else // show expenditure total in individual calendar
									{
										grandTotalRow.QuantityValues[colIndex] += expCatRow.QuantityValues[colIndex];
									}
									

                                    //Set validation class
                                    SetValidationClass(projectRow, expCatRow, colIndex);

                                    if (showAvgQuantity)
                                    {
                                        totalsListExpCat.QuantityTotalResValue[colIndex] = Math.Round(monthResTotalVal / model.Headers[colIndex].Weeks.Count, 3); // totalsListExpCat.Resources.Count* UoMVal;
                                    }
                                    else
                                    {
                                        totalsListExpCat.QuantityTotalResValue[colIndex] = monthResTotalVal; // totalsListExpCat.Resources.Count* UoMVal;
                                    }
                                    totalsListExpCat.QuantityResValue = uomValue;

                                    //Set validation class to total exp cat row
                                    SetValidationClassTotals(totalsListExpCat, colIndex);

                                    monthQuantity = 0.0M;
                                    monthResVal = 0.0M;
                                    monthResTotalVal = 0.0M;
                                    var keys = resourceTotals.Keys.ToArray();
                                    foreach (var key in keys)
                                    {
                                        resourceTotals[key] = 0.0M;
                                    }
                                    resourceTotalsExpCatTotals.ForEach(x => x.Quantity = 0.0M);

                                    #endregion
                                }
                            }
                        }
                    }
                }
            }

            allResources.ForEach(z =>
            // (allResources.Count() == 1 && (teams == null || teams.Count() == 0 || projects == null || projects.Count() == 0))
            {
                var monthVacation = 0.0M;
                var monthTraining = 0.0M; ;
                var resourceTotals = allResources.Select(x => x.Id).ToDictionary(key => key, pairs => 0.0M);
                

                var uomMultiplier = Utils.GetUOMMultiplier(allExpCats, allUoms, z.ExpedentureCategoryId.Value, model.IsUOMHours);
                var uomValue = allUoms.ContainsKey(allExpCats[z.ExpedentureCategoryId.Value].UOMId) ? allUoms[allExpCats[z.ExpedentureCategoryId.Value].UOMId].UOMValue : 0;

                // iterate through the weeks/months collection (1 month item following 4/5 week items)
                for (int colIndex = 0; colIndex < weeksCount; colIndex++)
                {
                    // if item is a new week
                    if (!model.Headers[colIndex].IsMonth)
                    {
                        #region Calc week cell
						var date = Constants.UnixEpochDate.AddMilliseconds(model.Headers[colIndex].Milliseconds);

                        //Get allocation for the date

                        if (allUoms.ContainsKey(allExpCats[z.ExpedentureCategoryId.Value].UOMId))
                        {
                            //Collect vacations
                            var vacationsSum = allResourceVacations.Where(t => t.PeopleResourceId == z.Id &&
                                t.WeekEndingDate <= date && t.WeekEndingDate >= date.AddDays(-6)).Sum(s => s.HoursOff);

                            vacationRow.QuantityValues[colIndex] += vacationsSum * uomMultiplier;
                            monthVacation += vacationsSum * uomMultiplier;
                            grandTotalRow.QuantityValues[colIndex] += vacationsSum * uomMultiplier;

                            //Collect trainings
                            var trainingsSum = allResourceTrainings.Where(t => t.PeopleResourceId == z.Id &&
                                t.WeekEndingDate <= date && t.WeekEndingDate >= date.AddDays(-6)).Sum(s => s.HoursOff);

                            trainingRow.QuantityValues[colIndex] += trainingsSum * uomMultiplier;
                            monthTraining += trainingsSum * uomMultiplier;
                            grandTotalRow.QuantityValues[colIndex] += trainingsSum * uomMultiplier;

                            z.CapacityQuantityValues[colIndex] = (uomValue - vacationsSum - trainingsSum)
                                * uomMultiplier;

                            //x.RestQuantityValues[colIndex] = x.CapacityQuantityValues[colIndex] - 
                            //    resourceAllocation.Where(ra => ra.PeopleResourceId == x.Id && ra.WeekEndingDate == date).Sum(ra => ra.Quantity.Value);
                        }



                        #endregion
                    }
                    else//if (model.Headers[colIndex].IsMonth) if item is a month then let's summarize data of it's weeks
                    {
                        #region Calc month total cell

                        vacationRow.QuantityValues[colIndex] += monthVacation;
                        trainingRow.QuantityValues[colIndex] += monthTraining;

                        monthVacation = 0.0M;
                        monthTraining = 0.0M;
                        var keys = resourceTotals.Keys.ToArray();
                        foreach (var key in keys)
                        {
                            resourceTotals[key] = 0.0M;
                        }

                        #endregion
                    }
                }
            });


            #region Recalc grandTotalRow
            for (var colIndex = 0; colIndex < model.Headers.Count; colIndex++)
            {
                if (model.Headers[colIndex].IsMonth)
                {
                    if (showAvgQuantity)
                    {
                        if (model.Headers[colIndex].Weeks.Count() > 0)// && assignedResCount > 0)
                        {
                            vacationRow.QuantityValues[colIndex] = Math.Round(vacationRow.QuantityValues[colIndex] / model.Headers[colIndex].Weeks.Count(), 3); // / assignedResCount, 3);
                            trainingRow.QuantityValues[colIndex] = Math.Round(trainingRow.QuantityValues[colIndex] / model.Headers[colIndex].Weeks.Count(), 3); // / assignedResCount, 3);
                        }
                    }
                    grandTotalRow.QuantityValues[colIndex] += vacationRow.QuantityValues[colIndex] + trainingRow.QuantityValues[colIndex];
                }
            }
            #endregion

            model.Calendar = calendarList;

            #region Add Total (Active Scenarios), Vacation, Training, Loan-outs, Capacity when no totals exist
            if (totalsList.Count == 0)
            {
                //BuildHeaders(model, headerDates.ToList());
                //weeksCount = model.Headers.Count;

                //Add Total (Active Scenarios), Vacation, Training, Loan-outs, Capacity
                totalsList.AddRange(AddTotalsRows(weeksCount));

            }
            #endregion

            totalsList.ForEach(ec =>
                ec.EmptyScenario = (ec.Resources == null || !ec.Resources.Any()));

            #region Add expected EC

            // SA. ENV-839. Rewrited to use view for Expcats
            var totalExpCats = DbContext.PeopleResources.Where(r => teamIds.Contains(r.TeamId.Value) && (!model.ResourceId.HasValue || r.Id == model.ResourceId.Value)).Select(r => r.ExpenditureCategory).Distinct().ToList();
            var totalExpCatIds = totalExpCats.Select(x => x.Id).ToList();
            var totalsListCatIds = totalsList.Select(x => x.ExpCatId).ToList();

            var remainingExpCats = DbContext.VW_ExpenditureCategory.Where(x => totalExpCatIds.Contains(x.Id) && !totalsListCatIds.Contains(x.Id)).ToDictionary(y => y.Id);

            var rcats = remainingExpCats.Select(ec => new CapacityDetailsModel.CalendarRow()
            {
                ExpCatId = ec.Key,
                Name = ec.Value.ExpCategoryWithCcName, // SA. ENV-756. ExpName -> ExpCatName. ENV-839
                QuantityValues = new decimal[model.Headers.Count],
                SpreadVal = new CapacityDetailsModel.Spread[model.Headers.Count],
                CollapsedClass = "fa-plus-square",
                IsParentCollapsed = false,
                EmptyScenario = false,
                QuantityTotalResValue = new decimal[model.Headers.Count],
                QuantityExpCatTotalValue = new decimal[model.Headers.Count],
                RowType = CapacityDetailsModel.RowType.BottomCategory,
                Resources = new List<CapacityDetailsModel.ScenarioCalendarRowResource>(allResources.
                    Where(x => x.ExpedentureCategoryId == ec.Key && teamIds.Contains(x.TeamId)).Select(x =>
                    new CapacityDetailsModel.ScenarioCalendarRowResource()
                    {
                        Id = x.Id,
                        ExpedentureCategoryId = x.ExpedentureCategoryId,
                        Name = x.Name,
                        QuantityValues = new decimal[model.Headers.Count],
                        SpreadVal = new CapacityDetailsModel.Spread[model.Headers.Count],
                        QuantityTotalResValue = new decimal[model.Headers.Count],
                        AllocatedQuantityValues = new decimal[weeksCount],
                        GrandTotalQuantity = 0M,
                        IsActiveEmployee = x.IsActiveEmployee,
                        TeamId = x.TeamId,
                        StartDate = x.StartDate,
                        EndDate = x.EndDate
                    }))
            }).ToList();

            totalsList.AddRange(rcats);

            #endregion

            #region set capacity row values as sum of each expenditure category capacities
            Guid?[] teamScenarioId = null;

            if (!model.ResourceId.HasValue) // SA. ENV-886
                if (model.IsCapacityModeActuals)
                {
                    teamScenarioId = DbContext.Teams.Where(t => teamIds.Contains(t.Id)).Select(x => x.ActualCapacityScenarioId).ToArray();
                }
                else
                {
                    teamScenarioId = DbContext.Teams.Where(t => teamIds.Contains(t.Id)).Select(x => x.PlannedCapacityScenarioId).ToArray();
                }

            var teamsExpCats = totalExpCats.Where(ec => totalsList.Select(ec1 => ec1.ExpCatId).Contains(ec.Id)).ToDictionary(x => x.Id);
            foreach (var ec in totalsList.Where(t => CapacityDetailsModel.RowType.BottomCategory.Equals(t.RowType)))
            {
                var uomMultiplier = Utils.GetUOMMultiplier(teamsExpCats, allUoms, ec.ExpCatId.Value, model.IsUOMHours);
                var UoMVal = teamsExpCats[ec.ExpCatId.Value].UOM.UOMValue * uomMultiplier;
                ec.QuantityResValue = UoMVal;
                var monthQuantity = 0.0M;
                var monthQuantityRes = 0.0M;

                List<ScenarioDetail> sds = null;

                if (!model.ResourceId.HasValue) // SA. ENV-886
                    sds = DbContext.ScenarioDetail.Where(x => teamScenarioId.Contains(x.ParentID) && x.ExpenditureCategoryId == ec.ExpCatId.Value).ToList();

                for (var colIndex = 0; colIndex < model.Headers.Count; colIndex++)
                {
                    var date = Constants.UnixEpochDate.AddSeconds(model.Headers[colIndex].Milliseconds / 1000);

                    if (model.Headers[colIndex].IsMonth)
                    {
                        if (showAvgQuantity)
                        {
                            ec.QuantityTotalResValue[colIndex] = Math.Round(monthQuantity / model.Headers[colIndex].Weeks.Count(), 3);
                            ec.Resources.ForEach(r => r.QuantityTotalResValue[colIndex] = (r.StartDate <= date && (r.EndDate >= date || r.EndDate == DateTime.MinValue) && r.IsActiveEmployee ? Math.Round(monthQuantityRes / model.Headers[colIndex].Weeks.Count(), 3) : 0));
                        }
                        else
                        {
                            ec.QuantityTotalResValue[colIndex] = monthQuantity;
                            ec.Resources.ForEach(r => r.QuantityTotalResValue[colIndex] = (r.StartDate <= date && (r.EndDate >= date || r.EndDate == DateTime.MinValue) && r.IsActiveEmployee ? monthQuantityRes : 0));
                        }

                        monthQuantity = 0.0M;
                        monthQuantityRes = 0.0M;
                    }
                    else
                    {

                        if (!model.ResourceId.HasValue)
                        {
                            //monthQuantityRes += UoMVal * ec.Resources.Where(r => r.StartDate <= date && (r.EndDate >= date || r.EndDate == DateTime.MinValue) && r.IsActiveEmployee).Count();
                            var enddate = new DateTime(1970, 01, 01).AddMilliseconds(model.Headers[colIndex].Milliseconds);
                            var currsds = sds.Where(x => x.WeekEndingDate == enddate).ToList();
                            ec.QuantityTotalResValue[colIndex] = (currsds.Count > 0 ? (decimal)currsds.Select(x => x.Quantity).Sum() : 0) * uomMultiplier;
                        }
                        else
                            // SA. ENV-886. For resource individual page
                            ec.QuantityTotalResValue[colIndex] = UoMVal;

                        monthQuantity += ec.QuantityTotalResValue[colIndex];
	                    monthQuantityRes += UoMVal;
                        ec.Resources.ForEach(r => r.QuantityTotalResValue[colIndex] = ((r.StartDate <= date && (r.EndDate >= date || r.EndDate == DateTime.MinValue) && r.IsActiveEmployee) ? UoMVal : 0));
                    }

                    //ec.SpreadVal[colIndex] = ec.QuantityValues[colIndex] == ec.QuantityTotalResValue[colIndex] ? CapacityDetailsModel.Spread.Equal :
                    //               ec.QuantityValues[colIndex] > ec.QuantityTotalResValue[colIndex] ? CapacityDetailsModel.Spread.Over :
                    //               ec.QuantityValues[colIndex] < r.QuantityTotalResValue[colIndex] ? CapacityDetailsModel.Spread.Less : CapacityDetailsModel.Spread.Notspecified);

                    ec.Resources.ForEach(r => r.SpreadVal[colIndex] = compareValues(r.QuantityValues[colIndex], r.QuantityTotalResValue[colIndex]) == 0 ? CapacityDetailsModel.Spread.Equal :
                                   compareValues(r.QuantityValues[colIndex], r.QuantityTotalResValue[colIndex]) > 0 ? CapacityDetailsModel.Spread.Over :
                                   compareValues(r.QuantityValues[colIndex], r.QuantityTotalResValue[colIndex]) < 0 ? CapacityDetailsModel.Spread.Less : CapacityDetailsModel.Spread.Notspecified);
                }
            }

            var bottomExpCatRows = totalsList.Where(t => CapacityDetailsModel.RowType.BottomCategory.Equals(t.RowType));
            foreach (var bottomExpCatRow in bottomExpCatRows)
            {
                if (bottomExpCatRow.QuantityTotalResValue != null)
                    for (var weekIndex = 0; weekIndex < bottomExpCatRow.QuantityTotalResValue.Length; weekIndex++)
                    {
                        //if (model.Headers[weekIndex].IsMonth)
                        //{

                        //}
                        //else
                        {
                            capacityRow.QuantityValues[weekIndex] += bottomExpCatRow.QuantityTotalResValue[weekIndex];
                        }
                    }

            }

            #endregion

            model.Calendar.AddRange(totalsList);

            #region Fill Vacation, trainig and loan out weekly allocations for each resource
            decimal monthResVacation = 0.0M;
            decimal monthResTraining = 0.0M;

            foreach (var res in allResources.Where(r => !model.ResourceId.HasValue || r.Id == model.ResourceId.Value))
            {
                var uomMultiplier = Utils.GetUOMMultiplier(allExpCats, allUoms,
                                                           res.ExpedentureCategoryId ?? Guid.Empty,
                                                           model.IsUOMHours);
                for (int colIndex = 0; colIndex < weeksCount; colIndex++)
                {
                    var date = Constants.UnixEpochDate.AddSeconds(model.Headers[colIndex].Milliseconds / 1000);
                    // if item is a new week
                    if (!model.Headers[colIndex].IsMonth)
                    {
                        var vacationQuantityValue = allResourceVacations.Where(t => t.PeopleResourceId == res.Id &&
                                                                           t.WeekEndingDate <= date &&
                                                                           t.WeekEndingDate >= date.AddDays(-6))
                                                               .Sum(s => s.HoursOff) * uomMultiplier;
                        res.VacationQuantityValues[colIndex] = vacationQuantityValue;
                        monthResVacation += vacationQuantityValue;

                        var trainingQuantityValue = allResourceTrainings.Where(t => t.PeopleResourceId == res.Id &&
                                                       t.WeekEndingDate <= date &&
                                                       t.WeekEndingDate >= date.AddDays(-6))
                                           .Sum(s => s.HoursOff) * uomMultiplier;
                        res.TrainingQuantityValues[colIndex] = trainingQuantityValue;
                        monthResTraining += trainingQuantityValue;
                    }
                    else
                    {
                        res.VacationQuantityValues[colIndex] = monthResVacation;
                        monthResVacation = 0.0M;
                        res.TrainingQuantityValues[colIndex] = monthResTraining;
                        monthResTraining = 0.0M;
                    }
                }
            }
            #endregion

            model.AllResources = allResources;

            return model;
        }
        private string setProjectColor(Project project, string DefaultColor)
        {
            
            string color =
            !string.IsNullOrEmpty(project.Color) ? project.Color.Contains('#') ? project.Color : "#" + project.Color :
                            (project.ParentProject != null && !string.IsNullOrWhiteSpace(project.ParentProject.Color) ? project.ParentProject.Color.Contains('#') ? project.ParentProject.Color : "#" + project.ParentProject.Color : null);
            if (string.IsNullOrEmpty(color))
                color = DefaultColor;
           
            return color;
        }
        private void AddECForWeek(CapacityDetailsModel.CalendarRow expCatRow,
            CapacityDetailsModel.CalendarRow projectRow,
            CapacityDetailsModel.CalendarRow grandTotalRow,
            List<PeopleResourceAllocation> currAllocation,
            CapacityDetailsModel.CalendarRow totalsListExpCat,
            int colIndex, decimal uomMultiplier, decimal uomValue, DateTime date, bool isIndividualCalendar)
        {

            //Project/Scenario row update
            projectRow.QuantityValues[colIndex] += expCatRow.QuantityValues[colIndex];

            #region Recalc EC resources
            expCatRow.Resources.ForEach(x =>
            {
                var peopleResourceAllocation = currAllocation.FirstOrDefault(ar => ar.PeopleResourceId == x.Id);
                if (peopleResourceAllocation != null)
                    x.QuantityValues[colIndex] = (peopleResourceAllocation.Quantity ?? 0) * uomMultiplier;
                else
                    x.QuantityValues[colIndex] = 0;
                x.GrandTotalQuantity += x.QuantityValues[colIndex];
            });

            #endregion

			grandTotalRow.QuantityValues[colIndex] += isIndividualCalendar
														  ? expCatRow.Resources.Sum(t => t.QuantityValues[colIndex])
														  : expCatRow.QuantityValues[colIndex];
			
			//Calculate not allocated quantity
            var resQty = expCatRow.Resources.Select(x => x.QuantityValues[colIndex]).Sum(); // *uomMultiplier;
            if (resQty < expCatRow.QuantityValues[colIndex])
                expCatRow.RestQuantity[colIndex] = expCatRow.QuantityValues[colIndex] - resQty;
            else
                expCatRow.RestQuantity[colIndex] = 0;

            //Set validation class
            SetValidationClass(projectRow, expCatRow, colIndex);

            //EC capacity
            //Add resources to the capacity EC
            foreach (var resCloneFrom in expCatRow.Resources)
            {
                var resCloneTo = totalsListExpCat.Resources.FirstOrDefault(r => resCloneFrom.Id == r.Id);
                if (resCloneTo != null)
                {
                    resCloneTo.QuantityValues[colIndex] += resCloneFrom.QuantityValues[colIndex];
                }
            }
            totalsListExpCat.QuantityExpCatTotalValue[colIndex] += expCatRow.QuantityValues[colIndex];
            totalsListExpCat.QuantityResValue = uomValue;
            totalsListExpCat.QuantityValues[colIndex] = totalsListExpCat.Resources.Select(x => x.QuantityValues[colIndex]).Sum();
            //expCatRow.Resources.ForEach(x =>
            //    resourceTotalsExpCatTotals.FirstOrDefault(r => r.Id == x.Id).Quantity += x.QuantityValues[colIndex]);
            totalsListExpCat.QuantityTotalResValue[colIndex] = totalsListExpCat.Resources.Where(r => r.StartDate <= date && (r.EndDate >= date || r.EndDate == DateTime.MinValue) && r.IsActiveEmployee).Count() * uomValue;
            totalsListExpCat.Resources.ForEach(r =>
                r.QuantityTotalResValue[colIndex] = (r.StartDate <= date && (r.EndDate >= date || r.EndDate == DateTime.MinValue) && r.IsActiveEmployee ? uomValue : 0));

            //Set validation class
            SetValidationClassTotals(totalsListExpCat, colIndex);
        }

        private void SetValidationClassTotals(CapacityDetailsModel.CalendarRow totalsListExpCat, int colIndex)
        {
            var compareRes = compareValues(totalsListExpCat.QuantityValues[colIndex], totalsListExpCat.QuantityTotalResValue[colIndex]);
            totalsListExpCat.SpreadVal[colIndex] = compareRes == 0 ? CapacityDetailsModel.Spread.Equal :
                compareRes > 0 ? CapacityDetailsModel.Spread.Over :
                compareRes < 0 ? CapacityDetailsModel.Spread.Less : CapacityDetailsModel.Spread.Notspecified;

            totalsListExpCat.Resources.ForEach(r => r.SpreadVal[colIndex] = r.QuantityValues[colIndex] == r.QuantityTotalResValue[colIndex] ? CapacityDetailsModel.Spread.Equal :
                r.QuantityValues[colIndex] > r.QuantityTotalResValue[colIndex] ? CapacityDetailsModel.Spread.Over :
                r.QuantityValues[colIndex] < r.QuantityTotalResValue[colIndex] ? CapacityDetailsModel.Spread.Less : CapacityDetailsModel.Spread.Notspecified);
        }

        private void SetValidationClass(CapacityDetailsModel.CalendarRow projectRow, CapacityDetailsModel.CalendarRow expCatRow, int colIndex)
        {

            var resTotal = expCatRow.Resources.Select(r => r.QuantityValues[colIndex]).Sum();
            var compareRes = compareValues(resTotal, expCatRow.QuantityValues[colIndex]);
            expCatRow.SpreadVal[colIndex] = compareRes == 0 ? CapacityDetailsModel.Spread.Equal :
                compareRes > 0 ? CapacityDetailsModel.Spread.Over :
                compareRes < 0 ? CapacityDetailsModel.Spread.Less : CapacityDetailsModel.Spread.Notspecified;

            if (projectRow.SpreadVal[colIndex] != CapacityDetailsModel.Spread.Over)
            {
                if (projectRow.SpreadVal[colIndex] != CapacityDetailsModel.Spread.Less)
                {
                    if (expCatRow.SpreadVal[colIndex] != CapacityDetailsModel.Spread.Notspecified)
                    {
                        projectRow.SpreadVal[colIndex] = expCatRow.SpreadVal[colIndex];
                    }
                }
                else
                {
                    if (expCatRow.SpreadVal[colIndex] == CapacityDetailsModel.Spread.Over)
                    {
                        projectRow.SpreadVal[colIndex] = expCatRow.SpreadVal[colIndex];
                    }
                }
            }
        }

        private IEnumerable<CapacityDetailsModel.CalendarRow> AddTotalsRows(int count)
        {
            return new List<CapacityDetailsModel.CalendarRow>(){
            new CapacityDetailsModel.CalendarRow
            {
                Name = "Vacation",
                QuantityValues = new decimal[count],
                IsParentCollapsed = false,
                IsTotals = true,
                RowType = CapacityDetailsModel.RowType.Vacation
            },
            //totalsList.Add(vacationRow);

            new CapacityDetailsModel.CalendarRow()
            {
                Name = "Training",
                QuantityValues = new decimal[count],
                IsParentCollapsed = false,
                IsTotals = true,
                RowType = CapacityDetailsModel.RowType.Training
            },
            //totalsList.Add(trainingRow);

            //totalsList.Add(new CapacityDetailsModel.CalendarRow()
            //{
            //    Name = "Loan-outs",
            //    QuantityValues = new decimal[model.Headers.Count],
            //    IsParentCollapsed = false,
            //    IsTotals = true,
            //    RowType = CapacityDetailsModel.RowType.LoanOut
            //});
            new CapacityDetailsModel.CalendarRow()
            {
                Name = "Total (Active Scenarios)",
                QuantityValues = new decimal[count],
                IsParentCollapsed = false,
                IsTotals = true,
                RowType = CapacityDetailsModel.RowType.Total
            },
            //totalsList.Add(grandTotalRow);
            new CapacityDetailsModel.CalendarRow()
            {
                Name = "Remaining Capacity",
                QuantityValues = new decimal[count],
                IsParentCollapsed = false,
                IsTotals = true,
                RowType = CapacityDetailsModel.RowType.Capacity
            }};
        }

        private void CollectTeams(Guid? CompanyId, Guid? TeamId, Guid? ViewId, Guid? ResourceId, bool GroupByTeam,
            out List<Project> projects, out List<Guid> teamIds, out List<Team> teams)
        {
            Guid userId = SecurityManager.GetUserPrincipal();

            var teamIdsInt = new List<Guid>();
            var teamsInt = new List<Team>();
            projects = new List<Project>();

            if (CompanyId != null && !CompanyId.Equals(Guid.Empty))
            {
                var companyIds = DbContext.Companies.Where(c => c.ParentCompanyId == CompanyId).Select(c => c.Id).ToList();
                companyIds.Add(CompanyId.Value);

                teamsInt = DbContext.Teams.Where(t => companyIds.Contains(t.CompanyId.Value)).ToList();
                teamIdsInt.AddRange(teamsInt.Select(t => t.Id));
				// SA. ENV-1249. Added to the fallowing query Distinct to fix duplication rows bug in Activity Calendar
                projects = (from tpRec in DbContext.Team2Project
                     join sec in DbContext.VW_ProjectAccessByUser on tpRec.ProjectId equals sec.Id
                            where sec.UserId.Equals(userId) && teamIdsInt.Contains(tpRec.TeamId) && !tpRec.Project.HasChildren
                     select tpRec.Project).AsNoTracking().Distinct().ToList();  
            }
            else if (TeamId != null && !TeamId.Equals(Guid.Empty))
            {
                //groupByTeamMode = false;
                projects =
                    (from tpRec in DbContext.Team2Project
                     join sec in DbContext.VW_ProjectAccessByUser on tpRec.ProjectId equals sec.Id
                     where sec.UserId.Equals(userId) && tpRec.TeamId == TeamId && !tpRec.Project.HasChildren
                     select tpRec.Project).AsNoTracking().ToList();

                projects.ForEach(p => p.Team2Project.Where(t2p => TeamId == t2p.TeamId && !teamsInt.Select(t => t.Id).Contains(t2p.TeamId)).ToList().ForEach(t => teamsInt.Add(t.Team)));
                teamIdsInt.AddRange(teamsInt.Select(t => t.Id));
            }
            else if (ViewId != null && !ViewId.Equals(Guid.Empty))
            {
                var viewTeamIds = (from tv in DbContext.Team2View
                                   where tv.ViewId == ViewId
                                   select tv.TeamId).ToList();
                teamIdsInt.AddRange(viewTeamIds);

                projects =
                    (from tpRec in DbContext.Team2Project
                     join sec in DbContext.VW_ProjectAccessByUser on tpRec.ProjectId equals sec.Id
                     where sec.UserId.Equals(userId) && teamIdsInt.Contains(tpRec.TeamId) &&
                         !tpRec.Project.HasChildren
                     select tpRec.Project).AsNoTracking().Distinct().ToList();

                projects.ForEach(p => p.Team2Project.Where(t2p => teamIdsInt.Contains(t2p.TeamId) && !teamsInt.Select(t => t.Id).Contains(t2p.TeamId)).ToList().ForEach(t => teamsInt.Add(t.Team)));
            }
            else if (ResourceId.HasValue && !ResourceId.Equals(Guid.Empty))
            {
                // SA. ENV-886. Begin
                projects =
                    (from PeopleResourceAllocation resAllocation in DbContext.PeopleResourceAllocations
                     join Scenario scen in DbContext.Scenarios on resAllocation.ScenarioId equals scen.Id
                     join Project proj in DbContext.Projects on scen.ParentId equals proj.Id
                     join VW_ProjectAccessByUser sec in DbContext.VW_ProjectAccessByUser on proj.Id equals sec.Id
                     where sec.UserId.Equals(userId) && resAllocation.PeopleResourceId.Equals(ResourceId.Value)
                     select proj).Distinct().ToList();

                var projectIds = projects.Select(x => x.Id);

                teamsInt =
                   (from Team team in DbContext.Teams
                    join PeopleResource res in DbContext.PeopleResources on team.Id equals res.TeamId
                    join Team2Project t2p in DbContext.Team2Project on team.Id equals t2p.TeamId
                    where res.TeamId.HasValue && res.Id.Equals(ResourceId.Value) &&
                         projectIds.Contains(t2p.ProjectId)
                    select team).Distinct().ToList();

                teamIdsInt = teamsInt.Select(x => x.Id).ToList();
                // SA. ENV-886. End
            }

            if (!GroupByTeam || teamsInt.Count() == 0)
            {
                teamsInt = new List<Team>() { new Team() { Id = Guid.Empty } };
            }

            teamIds = teamIdsInt;
            teams = teamsInt;
        }

        private void CollectCommonData(DateTime periodStartDate, DateTime periodEndDate, int weeksCount, Guid? CompanyId, Guid? TeamId, Guid? ViewId, Guid? ResourceId, bool GroupByTeam,
            out List<Project> projects, out List<Guid> teamIds, out List<Guid> scenarioIds, out List<Team> teams, out List<CapacityDetailsModel.ScenarioCalendarRowResource> AllResources)
        {

            #region Collect common data

            CollectTeams(CompanyId, TeamId, ViewId, ResourceId, GroupByTeam, out projects, out teamIds, out teams);

            var teamIdsInt = teamIds;
            var projectsInt = projects;
            var scenarioIdsInt = new List<Guid>();
            var AllResourcesInt = new List<CapacityDetailsModel.ScenarioCalendarRowResource>();
            var projectIds = new List<Guid>();

            projects.ForEach(p =>
                projectIds.Add(p.Id));

            projects.ForEach(p =>
                p.Scenarios.Where(s => s.Status.HasValue && s.Status.Value == (int)ScenarioStatus.Active
                    && s.Type == (int)ScenarioType.Portfolio)
                .ToList().ForEach(s => scenarioIdsInt.Add(s.Id)));

            // SA. ENV-886. Begin
            IQueryable<PeopleResource> resourcesByTeams;

            if (!ResourceId.HasValue)
                resourcesByTeams = DbContext.Teams.AsNoTracking().Where(x => teamIdsInt.Contains(x.Id)).
                    Select(x => x.PeopleResources).SelectMany(x => x).Distinct();
            else
                // For individual resource page
                resourcesByTeams = DbContext.PeopleResources.Where(x => x.Id.Equals(ResourceId.Value)).Select(x => x);
            // SA. ENV-886. End

            foreach (var teamResource in resourcesByTeams.OrderBy(x=>x.LastName))
            {

                AllResourcesInt.Add(
                    new CapacityDetailsModel.ScenarioCalendarRowResource()
                    {
                        Id = teamResource.Id,
                        Name = string.Format("{0} {1}", teamResource.FirstName, teamResource.LastName),
                        ExpedentureCategoryId = teamResource.ExpenditureCategoryId,
                        TeamId = teamResource.TeamId.Value,
                        AssignedToTeam = teamIdsInt.Contains(teamResource.TeamId.Value),
                        ProjectIds = projectsInt.SelectMany(p => p.Team2Project).Where(p => p.TeamId == teamResource.TeamId.Value).Select(p => p.ProjectId).ToArray(),
                        IsActiveEmployee = teamResource.IsActiveEmployee,
                        VacationQuantityValues = new decimal[weeksCount],
                        TrainingQuantityValues = new decimal[weeksCount],
                        AllocatedQuantityValues = new decimal[weeksCount],
                        CapacityQuantityValues = new decimal[weeksCount],
                        ReadOnlyWeeks = new bool[weeksCount],
                        StartDate = teamResource.StartDate,
                        EndDate = teamResource.EndDate
                    });
            }

            scenarioDetails = DbContext.VW_ScenarioAndProxyDetails.AsNoTracking()
                                                .Where(t => t.ParentID.HasValue && scenarioIdsInt.Contains(t.ParentID.Value) &&
                                                    t.WeekEndingDate >= periodStartDate &&
                                                    t.WeekEndingDate <= periodEndDate)
                                                    .OrderBy(t => t.ExpCategoryWithCcName)  // SA. ENV-839
                                                    .ThenBy(t => t.WeekEndingDate);
            #endregion

            teamIds = teamIdsInt;
            AllResources = AllResourcesInt;
            projects = projectsInt;
            scenarioIds = scenarioIdsInt;
        }

        private int compareValues(decimal val1, decimal val2)
        {

            var val1_ = decimal.Round(val1, 3);
            var val2_ = decimal.Round(val2, 3);
            if ((-0.005m > (val1_ - val2_)))
                return -1;
            else
                if ((0.005m < (val1_ - val2_)))

                    return 1;
                else
                    return 0;

        }
        public void BuildHeaders(CapacityDetailsModel model, List<DateTime> gridHeaders)
        {
            model.Headers = new List<CapacityDetailsModel.Header>((int)(gridHeaders.Count * 1.25));
            var prevMonth = string.Empty;
            var prevYear = string.Empty;
            var monthIndex = -1;
            var yearIndex = -1;
            CapacityDetailsModel.Header monthColumn = null;
            CapacityDetailsModel.Header yearColumn = null;
            foreach (var gridHeader in gridHeaders)
            {
                // get week start date as previous week end date + 1 day
                //var weekStartDate = prevWeek.AddDays(1);
                // if there is a gap between weeks (e.g. there is a non-working week that was not represented in scenario details records)
                // then we should subtract 6 days from current week end date
                //if (gridHeader.AddDays(-6) > weekStartDate)
                //    weekStartDate = gridHeader.AddDays(-6);
                // get month name as month of the week start date
                var gridHeaderTitle = gridHeader.ToString("MMMM"); //("MMMM yyyy");
                var gridHeaderYearTitle = gridHeader.ToString("yyyy");

                if (!prevMonth.Equals(gridHeaderTitle))
                {
                    if (monthColumn != null)
                    {
                        model.Headers.Add(monthColumn);
                        //yearColumn.SpanCount++; // model.Headers.Count - 1;
                    }
                    monthColumn = new CapacityDetailsModel.Header()
                    {
                        Show = true,
                        Milliseconds = (long)gridHeader.Subtract(Constants.UnixEpochDate).TotalMilliseconds,
                        MonthIndex = ++monthIndex,
                        IsMonth = true,
                        Title = gridHeaderTitle,
                        Year = gridHeaderYearTitle,
                        Weeks = new List<int>()
                    };
                }
                if (!prevYear.Equals(gridHeaderYearTitle))
                {
                    if (yearColumn != null)
                    {
                        model.YearHeaders.Add(yearColumn);
                    }
                    yearColumn = new CapacityDetailsModel.Header()
                    {
                        Show = true,
                        Milliseconds = (long)gridHeader.Subtract(Constants.UnixEpochDate).TotalMilliseconds,
                        YearIndex = ++yearIndex,
                        IsMonth = false,
                        Title = gridHeaderYearTitle,
                        //Weeks = new List<int>()
                    };
                }

                var weekHeader = new CapacityDetailsModel.Header()
                {
                    IsMonth = false,
                    Milliseconds = (long)gridHeader.Subtract(Constants.UnixEpochDate).TotalMilliseconds,
                    MonthIndex = monthColumn.MonthIndex,
                    Title = gridHeader.ToString("dd") //ToShortDateString()
                };
                model.Headers.Add(weekHeader);
                monthColumn.Weeks.Add(model.Headers.Count - 1);
                monthColumn.Milliseconds = weekHeader.Milliseconds;

                yearColumn.Milliseconds = weekHeader.Milliseconds;

                prevMonth = gridHeaderTitle;
                prevYear = gridHeaderYearTitle;
            }
            if (monthColumn != null)
            {
                model.Headers.Add(monthColumn);
                //yearColumn.SpanCount++;
            }
            if (yearColumn != null)
            {
                model.YearHeaders.Add(yearColumn);
            }
        }

        private decimal GetProjectNeedMultiplier(Guid expenditureCategory, List<Guid> calendarTeamIds, List<Team2Scenario> teams2scenarios, Dictionary<Guid, List<Guid>> expCategoriesByTeams)
        {
            var allTeamsThatHaveExpCategory = teams2scenarios.FindAll(x => expCategoriesByTeams.Any(ec => ec.Key == x.TeamId && ec.Value.Contains(expenditureCategory)));

            decimal replicatedTotal = allTeamsThatHaveExpCategory.Sum(x => x.Allocation);
            if (replicatedTotal <= 0)
                return 1;

            decimal replicatedByCertainTeams = allTeamsThatHaveExpCategory.Where(x => calendarTeamIds.Contains(x.TeamId)).Sum(x => x.Allocation);
            return (replicatedByCertainTeams / replicatedTotal);
        }

        [HttpPost]
        [AreaSecurityAttribute(area = Areas.Scenarios, level = AccessLevel.Write)]
        public ActionResult SaveChanges(SaveCapacityDetailsChangesModel model)
        {
            var context = new EnVisageEntities();
            model.TrimStringProperties();

            var allExpCats = DbContext.ExpenditureCategory.AsNoTracking().ToDictionary(x => x.Id);
            var allUoms = DbContext.UOMs.AsNoTracking().ToDictionary(x => x.Id);
            if (ModelState.IsValid)
            {
                try
                {
                    foreach (var changedExpCat in model.ChangedExpCats)
                    {
                        var uomMultiplier = Utils.GetUOMMultiplier(allExpCats, allUoms, changedExpCat.Id, model.ScenarioFilters.IsUOMHours);
                        if (changedExpCat.Resources != null)
                        {
                            var resourceIds = changedExpCat.Resources.Select(x => x.Id).ToList();
                            var resourceAllocations = context.PeopleResourceAllocations.Where(x => resourceIds.Contains(x.PeopleResourceId)
                                && x.ScenarioId == changedExpCat.ScenarioId
                                && x.ExpenditureCategoryId == changedExpCat.Id).ToList();

                            foreach (var resource in changedExpCat.Resources)
                            {
                                var resourceId = resource.Id;

                                if (resource.IsRemoved)
                                {
                                    var recourcesToDelete = context.PeopleResourceAllocations.Where(x => x.PeopleResourceId == resource.Id && x.ScenarioId == changedExpCat.ScenarioId && x.ExpenditureCategoryId == changedExpCat.Id).ToList();
                                    recourcesToDelete.ForEach(x => context.PeopleResourceAllocations.Remove(x));
                                    recourcesToDelete.ForEach(x => context.Entry(x).State = System.Data.Entity.EntityState.Deleted);
                                }
                                else
                                {
                                    var allocateResourceIdsUpdated = new List<Guid>();

                                    foreach (var changedResource in resource.Values)
                                    {
                                        var date = Constants.UnixEpochDate.AddSeconds(changedResource.Milliseconds / 1000);

                                        var allocatedResource = (from c in resourceAllocations
                                                                 where c.WeekEndingDate == date && c.PeopleResourceId == resourceId
                                                                 select c).FirstOrDefault();
                                        if (changedResource.Quantity <= 0)
                                        {
                                            if (allocatedResource != null)
                                                context.Entry(allocatedResource).State = System.Data.Entity.EntityState.Deleted;
                                            continue;
                                        }
                                        if (allocatedResource == null)
                                        {
                                            allocatedResource = context.PeopleResourceAllocations.Create();
                                            allocatedResource.Id = Guid.NewGuid();
                                            allocatedResource.ExpenditureCategoryId = changedExpCat.Id;
                                            allocatedResource.PeopleResourceId = resourceId;
                                            allocatedResource.ScenarioId = changedExpCat.ScenarioId;
                                            allocatedResource.WeekEndingDate = date;
                                            context.Entry(allocatedResource).State = System.Data.Entity.EntityState.Added;
                                        }
                                        else
                                        {
                                            context.Entry(allocatedResource).State = System.Data.Entity.EntityState.Modified;
                                        }
                                        allocatedResource.Quantity = changedResource.Quantity / uomMultiplier;
                                    }
                                }
                            }
                        }
                    }
                    context.SaveChanges();

                    return new HttpStatusCodeResult(HttpStatusCode.OK);
                }
                catch (BLLException blEx) // handle any system specific error
                {
                    // display error message if required
                    if (blEx.DisplayError)
                        ModelState.AddModelError(string.Empty, blEx.Message);
                    else // if display not requried then display modal form with general error message 
                    {
                        LogException(blEx);
                        SetErrorScript();
                    }
                }
                catch (Exception exception) // handle any unexpected error
                {
                    LogException(exception);
                    SetErrorScript();
                }
            }
            return new HttpStatusCodeResult(HttpStatusCode.InternalServerError);
        }

    }
}