using EnVisage.Code.BLL;
using EnVisage.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace EnVisage.Code.Charts
{
	public class DashboardCharthManager
	{
		private class Data
		{
			public decimal? cost { get; set; }
			public decimal? quantity { get; set; }
			public DateTime? weekend { get; set; }
			public string projectStatus { get; set; }
			public int? type { get; set; }
            public string Name { get; set; }
            public Guid? ExpCatId { get; set; }
            public Guid ProjectId { get; set; }
            public Guid ProjectType { get; set; }
		    public decimal? ScenarioBUCost { get; set; }
		    public int ScenarioDuration { get; set; }
		}

        public class PieData
        {
            public Guid TypeId { get; set; }
            public string Label { get; set; }
            public decimal Cost { get; set; }
            public int Time { get; set; }
        }

		EnVisageEntities db = new EnVisageEntities();

        /// <summary>
        /// 
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="principals"></param>
        /// <param name="type"></param>
        /// <param name="status"></param>
        /// <param name="classification"></param>
        /// <param name="mode"></param>
        /// <param name="teamId"></param>
        /// <param name="viewId"></param>
        /// <param name="isUOMHours">Indicates whether UOM (unit of measure) is hours or resources.</param>
        /// <returns></returns>
		public Dictionary<string, List<long[]>> GetData(DateTime StartDate, DateTime EndDate, Guid[] principals, Guid userId, string type, string status, string classification, ForecastDashboardMode mode, Guid teamId, Guid viewId, bool? isUOMHours, bool isLaborMode, string filterGroup)
		{
			var result = new Dictionary<string, List<long[]>>();
			IQueryable<Project> projects = (from c in db.Projects join o in db.ProjectAccesses on c.Id equals o.ProjectId where principals.Contains(o.PrincipalId) select c);
            List<Guid> teams = new List<Guid>(); 
			if (ForecastDashboardMode.TeamForecast == mode)
			{
				projects = projects.Where(p => p.Team2Project.Any(t2p => t2p.TeamId == teamId));
                teams.Add(teamId); 
			}
			if (ForecastDashboardMode.ViewForecast == mode)
			{
                projects = projects.Where(p => p.Team2Project.Any(t2p => t2p.Team.Team2View.Any(tv => tv.ViewId == viewId)));
                teams.AddRange(db.Teams.Where(t=>t.Team2View.Any(tv=>tv.ViewId == viewId)).Select(t=>t.Id).ToList());
			}

			var days = EndDate.Subtract(StartDate).TotalDays;

			int typeInt = 0;
			if(!string.IsNullOrWhiteSpace(type) && type != "null")
				typeInt = (int)Enum.Parse(typeof(ScenarioType), type);

			Dictionary<Guid, ExpenditureCategory> expCats = new Dictionary<Guid, ExpenditureCategory>();
			if (isLaborMode)
				expCats = (from e in db.ExpenditureCategory where e.Type == (int)ExpenditureCategoryModel.CategoryTypes.Labor select e).ToDictionary(e => e.Id);
			else
				expCats = (from e in db.ExpenditureCategory where e.Type != (int)ExpenditureCategoryModel.CategoryTypes.Labor select e).ToDictionary(e => e.Id);

			List<Guid> expCatGuids = expCats.Keys.ToList();

			var uoms = db.UOMs.AsNoTracking().ToDictionary(u => u.Id);

			IQueryable<Data> data = null;

		    long[] fiscalCalendarChartPoints = null;
            if (days <= 400)
			{
				data = GetWeeksData(StartDate, EndDate.AddDays(7), projects, status, classification, typeInt, mode != ForecastDashboardMode.MainDashboard, isLaborMode, filterGroup);
                fiscalCalendarChartPoints = (from c in db.FiscalCalendars
                            where c.Type == (int)FiscalCalendarModel.FiscalYearType.Week &&
								c.EndDate >= StartDate && c.EndDate <= EndDate
                            orderby c.StartDate
                            select c.EndDate).ToArray().Select(t => (long)t.Subtract(new DateTime(1970, 1, 1)).TotalMilliseconds).ToArray();
            }
			else
			{
				data = GetMonthData(StartDate, EndDate, projects, status, classification, typeInt, mode != ForecastDashboardMode.MainDashboard, isLaborMode, filterGroup);
                fiscalCalendarChartPoints = (from c in db.FiscalCalendars
                                  where c.Type == (int)FiscalCalendarModel.FiscalYearType.Month &&
									  c.EndDate >= StartDate && c.EndDate <= EndDate
                                  orderby c.StartDate
                            select c.EndDate).ToArray().Select(t => (long)t.Subtract(new DateTime(1970, 1, 1)).TotalMilliseconds).ToArray();
            }
            var projectIds = projects.Select(t => t.Id).ToArray();
            var allVacations = (from vacWeek in db.PeopleResourceVacations
                                join vac in db.Vacations on vacWeek.VacationId equals vac.Id
                                join p in db.PeopleResources on vac.PeopleResourceId equals p.Id
                                join t in db.Team2Project on p.TeamId equals t.TeamId
                                where (teams.Count == 0 || (p.TeamId.HasValue && teams.Contains(p.TeamId.Value))) && projectIds.Contains(t.ProjectId) && expCatGuids.Contains(p.ExpenditureCategoryId)
                                select new {vacWeek.Id, vacWeek.WeekEndingDate, vacWeek.HoursOff, 
									Rate = db.Rates.Where(r => r.ExpenditureCategoryId == p.ExpenditureCategoryId && r.StartDate <= vacWeek.WeekEndingDate && r.EndDate >= vacWeek.WeekEndingDate).FirstOrDefault()}).Distinct().ToArray();

            var allTrainings = (from traningWeek in db.PeopleResourceTrainings
                                join training in db.Trainings on traningWeek.TrainingId equals training.Id
                                join p in db.PeopleResources on traningWeek.PeopleResourceId equals p.Id
                                join t in db.Team2Project on p.TeamId equals t.TeamId
                                where (teams.Count == 0 || (p.TeamId.HasValue && teams.Contains(p.TeamId.Value))) && projectIds.Contains(t.ProjectId) && expCatGuids.Contains(p.ExpenditureCategoryId)
                                select new
                                {
                                    traningWeek.Id,
                                    traningWeek.WeekEndingDate,
                                    traningWeek.HoursOff,
                                    Rate = db.Rates.Where(r => r.ExpenditureCategoryId == p.ExpenditureCategoryId && r.StartDate <= traningWeek.WeekEndingDate && r.EndDate >= traningWeek.WeekEndingDate).FirstOrDefault()
                                }).Distinct().ToArray();
			long wkending = 0;
            //decimal tmpTraining = 0.0M;
            //decimal tmpTrainingQ = 0.0M;
            Dictionary<string, decimal> tmpProjectStatus = new Dictionary<string, decimal>();
			Dictionary<string, decimal> tmpCapacity = new Dictionary<string, decimal>();

			//init result dictionary
			if (mode == ForecastDashboardMode.MainDashboard)
			{
				
                result.Add("plannedCapacity", new List<long[]>());
                result.Add("plannedCapacityQ", new List<long[]>());
			}
            result.Add("training", new List<long[]>());
            result.Add("trainingQ", new List<long[]>());
            result.Add("vacation", new List<long[]>());
            result.Add("vacationQ", new List<long[]>());
            result.Add("totalPlannedCapacity", new List<long[]>());
            result.Add("totalPlannedCapacityQ", new List<long[]>());
			result.Add("resourceCapacity", new List<long[]>());
			result.Add("resourceCapacityQ", new List<long[]>());
			//capacity and project status data remains uninitialized as we do not know what capacity scenarios and project statuses we have

			foreach (var sd in data)
			{
				if ((long)sd.weekend.Value.Subtract(new DateTime(1970, 1, 1)).TotalMilliseconds != wkending)
				{
					//new week, push result data and reinit temps
					if (0 != wkending)
					{
						if (mode == ForecastDashboardMode.MainDashboard)
						{
                            //result["training"].Add(new long[] { wkending, (long)(Math.Round(tmpTraining)) });
                            //result["trainingQ"].Add(new long[] { wkending, (long)(Math.Round(tmpTrainingQ)) });
							foreach (string cn in tmpCapacity.Keys)
							{
								if (!result.ContainsKey(cn))
								{
									result.Add(cn, new List<long[]>());
								}
								result[cn].Add(new long[] { wkending, (long)(Math.Round(tmpCapacity[cn])) });
							}
						}
                        //TRAININGS
                        var ed = new DateTime(1970, 1, 1).AddMilliseconds(wkending);
                        var startd = ed.AddDays(-6);
                        decimal tmpTrainingQ = allTrainings.Where(t => t.WeekEndingDate <= ed && t.WeekEndingDate >= startd).Sum(t => t.HoursOff);
                        result["trainingQ"].Add(new long[] { wkending, (long)(tmpTrainingQ * Utils.GetUOMMultiplier(expCats, uoms, sd.ExpCatId ?? Guid.Empty, isUOMHours)) });

                        decimal tmpTraining = allTrainings.Where(t => t.WeekEndingDate <= ed && t.WeekEndingDate >= startd).Sum(t => t.HoursOff * (t.Rate != null ? t.Rate.Rate1 : 0));
                        result["training"].Add(new long[] { wkending, (long)tmpTraining });

						//VACATIONS
						ed = new DateTime(1970, 1, 1).AddMilliseconds(wkending);
						startd = ed.AddDays(-6);
						decimal tmpVacationQ = allVacations.Where(t => t.WeekEndingDate <= ed && t.WeekEndingDate >= startd).Sum(t => t.HoursOff);
						result["vacationQ"].Add(new long[] { wkending, (long)(tmpVacationQ * Utils.GetUOMMultiplier(expCats, uoms, sd.ExpCatId ?? Guid.Empty, isUOMHours)) });

						decimal tmpVacation = allVacations.Where(t => t.WeekEndingDate <= ed && t.WeekEndingDate >= startd).Sum(t => t.HoursOff * (t.Rate != null ? t.Rate.Rate1 : 0));
						result["vacation"].Add(new long[] { wkending, (long)tmpVacation });

                        foreach (string sn in tmpProjectStatus.Keys)
						{
							if (!result.ContainsKey(sn))
							{
								result.Add(sn, new List<long[]>());
							}
							result[sn].Add(new long[] { wkending, (long)(Math.Round(tmpProjectStatus[sn])) });
						}
					}
					wkending = (long)sd.weekend.Value.Subtract(new DateTime(1970, 1, 1)).TotalMilliseconds;
                    //tmpTraining = 0.0M;
                    //tmpTrainingQ = 0.0M;
					tmpProjectStatus = new Dictionary<string, decimal>();
					tmpCapacity = new Dictionary<string, decimal>();
				}
				
				//fill data into tmps
				if (!sd.type.HasValue)
					continue;

                //else if ((int)ScenarioType.Training == sd.type.Value)
                //{
                //    tmpTraining += sd.cost ?? 0;
                //    tmpTrainingQ += (sd.quantity ?? 0) * Utils.GetUOMMultiplier(expCats, uoms, sd.ExpCatId ?? Guid.Empty, isUOMHours);
                //}
				else if ((int)ScenarioType.Portfolio == sd.type.Value || (int)ScenarioType.Scheduling == sd.type.Value)
				{
					//the dataset is already filtered by correct scenario type so we're just summarizing the numbers here
					if (!tmpProjectStatus.ContainsKey(sd.projectStatus))
					{
						tmpProjectStatus.Add(sd.projectStatus, 0.0M);
						tmpProjectStatus.Add(sd.projectStatus + "Q", 0.0M);
					}

					tmpProjectStatus[sd.projectStatus] += sd.cost ?? 0;
                    tmpProjectStatus[sd.projectStatus + "Q"] += (sd.quantity ?? 0) * Utils.GetUOMMultiplier(expCats, uoms, sd.ExpCatId ?? Guid.Empty, isUOMHours);
				}
				else if ((int)ScenarioType.Capacity == sd.type.Value)
				{
					if (!tmpCapacity.ContainsKey(sd.Name))
					{
						tmpCapacity.Add(sd.Name, 0.0M);
						tmpCapacity.Add(sd.Name + "Q", 0.0M);
					}

					tmpCapacity[sd.Name] += sd.cost ?? 0;
                    tmpCapacity[sd.Name + "Q"] += (sd.quantity ?? 0) * Utils.GetUOMMultiplier(expCats, uoms, sd.ExpCatId ?? Guid.Empty, isUOMHours);
				}
				else
				{
					throw new InvalidOperationException("Incorrect scenario type found");
				}
			}

			List<PeopleResourceModel.CapacityValues> teamCapacity;
			Guid[] userTeams = new TeamManager(db).GetTeamsByUser(userId).Select(t => t.Id).ToArray();
			if (ForecastDashboardMode.TeamForecast == mode)
			{
                teamCapacity = new TotalCapacity(new Guid[] {teamId}, expCats, uoms, isUOMHours).GetDataByDateRange((new DateTime(1970, 1, 1).AddMilliseconds(fiscalCalendarChartPoints.Min())), (new DateTime(1970, 1, 1).AddMilliseconds(fiscalCalendarChartPoints.Max())));
			}
			else if (ForecastDashboardMode.ViewForecast == mode)
			{
                teamCapacity = new ViewCapacity(viewId, expCats, uoms, isUOMHours).GetDataByDateRange((new DateTime(1970, 1, 1).AddMilliseconds(fiscalCalendarChartPoints.Min())), (new DateTime(1970, 1, 1).AddMilliseconds(fiscalCalendarChartPoints.Max())));
			}
			else
			{
				teamCapacity = new TotalCapacity(userTeams, expCats, uoms, isUOMHours).GetDataByDateRange((new DateTime(1970, 1, 1).AddMilliseconds(fiscalCalendarChartPoints.Min())), (new DateTime(1970, 1, 1).AddMilliseconds(fiscalCalendarChartPoints.Max())));
			}

			result["resourceCapacity"].AddRange(teamCapacity.Select(x => new long[]
				{
					(long)x.WeekEnding.Subtract(new DateTime(1970, 1, 1)).TotalMilliseconds,
					(long)(Math.Round(x.Cost))
				}));
			result["resourceCapacityQ"].AddRange(teamCapacity.Select(x => new long[]
				{
					(long)x.WeekEnding.Subtract(new DateTime(1970, 1, 1)).TotalMilliseconds,
					(long)(Math.Round(x.Quantity))
				}));

			
            Dictionary<DateTime, PeopleResourceModel.CapacityValues> totalPlannedCapacity = null;
            if (mode == ForecastDashboardMode.MainDashboard)
				totalPlannedCapacity = GetWeeklyTeamPlannedCapacity(StartDate, EndDate, userTeams, expCats, uoms, isUOMHours);
            else if (mode == ForecastDashboardMode.TeamForecast)
				totalPlannedCapacity = GetWeeklyTeamPlannedCapacity(StartDate, EndDate, new Guid[] { teamId }, expCats, uoms, isUOMHours);
            else if (mode == ForecastDashboardMode.ViewForecast)
                totalPlannedCapacity = GetWeeklyViewPlannedCapacity(StartDate, EndDate, viewId, expCats, uoms, isUOMHours);
            result["totalPlannedCapacity"].AddRange(totalPlannedCapacity.Select(x => new long[]
            {
                (long)x.Key.Subtract(new DateTime(1970, 1, 1)).TotalMilliseconds,
                (long)(Math.Round(x.Value.Cost))
            }));
            result["totalPlannedCapacityQ"].AddRange(totalPlannedCapacity.Select(x => new long[]
            {
                (long)x.Key.Subtract(new DateTime(1970, 1, 1)).TotalMilliseconds,
                (long)(Math.Round(x.Value.Quantity))
            }));

			//Fill missing weekends for all items in dictionary
			foreach (string key in result.Keys)
			{
				List<long[]> lst = FillMissingWeeends(result[key], fiscalCalendarChartPoints);
				result[key].Clear();
				result[key].AddRange(lst);
			}

			return result;
		} 

		/// <summary>
		/// Заполняем нулями данные за недели, которых нет в списке list.
		/// </summary>
		/// <param name="list">Список с данными по неделям.</param>
		/// <param name="weekEnds">Список недель в заданном диапазоне дат.</param>
		private List<long[]> FillMissingWeeends(List<long[]> list, IEnumerable<long> weekEnds)
		{
			if (list.Count < weekEnds.Count())
			{
				var result = weekEnds.Except(list.Select(x => x[0]));
				list.AddRange(result.Select(x => new long[] { x, 0 }));
				list.RemoveAll(x => !weekEnds.Contains(x[0]));
			}
			return list.OrderBy(x => x[0]).ToList();
		}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="projects"></param>
        /// <param name="status"></param>
        /// <param name="classification"></param>
        /// <param name="scenarioType"></param>
        /// <param name="noOldCapacities"></param>
        /// <returns></returns>
		private IQueryable<Data> GetWeeksData(DateTime StartDate, DateTime EndDate, IQueryable<Project> projects, string status, string classification, int scenarioType, bool noOldCapacities, bool isLaborMode, string filterGroup)
		{
			if (!string.IsNullOrWhiteSpace(status) && status != "All")
				projects = projects.Where(x => x.StatusId.ToString() == status);
			if (!string.IsNullOrWhiteSpace(classification) && classification != "All")
				projects = projects.Where(x => x.Type.Id.ToString() == classification);

			Guid scenarioGroup = Guid.Empty;
			Guid.TryParse(filterGroup, out scenarioGroup);
	
			Guid[] projectIds = (from p in projects select p.Id).ToArray();
            Guid[] expCatIds = null;
            if (isLaborMode) expCatIds = (from e in db.ExpenditureCategory where e.Type == (int)ExpenditureCategoryModel.CategoryTypes.Labor select e.Id).ToArray();
            else expCatIds = (from e in db.ExpenditureCategory where e.Type != (int)ExpenditureCategoryModel.CategoryTypes.Labor select e.Id).ToArray();
			List<int> scTypes = new List<int>();
			if(!noOldCapacities)
				scTypes.AddRange(new int[] { (int)ScenarioType.Capacity, (int)ScenarioType.Vacation, (int)ScenarioType.Training });
			if (scenarioType != 0)
				scTypes.Add(scenarioType);
			else
				scTypes.Add((int)ScenarioType.Portfolio);

			IQueryable<Data> data = null;
			if (scenarioGroup != Guid.Empty)
			{
				data = (from sd in db.ScenarioDetail.AsNoTracking()
						join s in db.Scenarios on sd.ParentID equals s.Id
                        join p in db.Projects on s.ParentId equals p.Id
						where sd.WeekEndingDate >= StartDate && sd.WeekEndingDate <= EndDate
						&& projectIds.Contains(s.ParentId.Value) && s.Status == (int?)ScenarioStatus.Active
						&& scTypes.Contains(s.Type) && expCatIds.Contains(sd.ExpenditureCategoryId.Value) && s.Scenario2Group.Any(g => g.GroupId == scenarioGroup)
						orderby sd.WeekEndingDate
						select new Data
						{
							cost = sd.Cost,
							quantity = sd.Quantity,
							weekend = sd.WeekEndingDate,
							projectStatus = projects.FirstOrDefault(x => x.Id == s.ParentId).Status.Name,
							type = s.Type,
							Name = s.Name,
                            ScenarioBUCost = (s.UseLMMargin ?? 0) == 1 ? s.BUDirectCosts_LM : s.BUDirectCosts,
                            ScenarioDuration = s.Duration ?? 0,
							ExpCatId = sd.ExpenditureCategoryId,
                            ProjectId = s.ParentId.Value,
                            ProjectType = p.TypeId
						});
			}
			else
			{
				data = (from sd in db.ScenarioDetail.AsNoTracking()
							join s in db.Scenarios on sd.ParentID equals s.Id
							join p in db.Projects on s.ParentId equals p.Id
                            where sd.WeekEndingDate >= StartDate && sd.WeekEndingDate <= EndDate
							&& projectIds.Contains(s.ParentId.Value) && s.Status == (int?)ScenarioStatus.Active
							&& scTypes.Contains(s.Type) && expCatIds.Contains(sd.ExpenditureCategoryId.Value)
							orderby sd.WeekEndingDate
							select new Data
							{
								cost = sd.Cost,
								quantity = sd.Quantity,
								weekend = sd.WeekEndingDate,
								projectStatus = projects.FirstOrDefault(x => x.Id == s.ParentId).Status.Name,
								type = s.Type,
								Name = s.Name,
                                ScenarioBUCost = (s.UseLMMargin ?? 0) == 1 ? s.BUDirectCosts_LM : s.BUDirectCosts,
                                ScenarioDuration = s.Duration ?? 0,
                                ExpCatId = sd.ExpenditureCategoryId,
                                ProjectType = p.TypeId
							});
			}
			return data;
		}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="projects"></param>
        /// <param name="status"></param>
        /// <param name="classification"></param>
        /// <param name="scenarioType"></param>
        /// <param name="noOldCapacities"></param>
        /// <param name="uomMultiplier">A value of resource capacity multiplier. E.g. if user wants to see data in hours, then <paramref name="uomMultiplier"/> 
        /// equals to 1. If user wants to see data in resources then <paramref name="uomMultiplier"/> equals to 1/40 = 0.025.</param>
        /// <returns></returns>
		private IQueryable<Data> GetMonthData(DateTime StartDate, DateTime EndDate, IQueryable<Project> projects, string status, string classification, int scenarioType, bool noOldCapacities, bool isLaborMode, string filterGroup)
		{
		    var startDate = StartDate.AddMonths(-1);
			var monthDates = (from c in db.FiscalCalendars where c.Type == 1 && c.StartDate >= startDate && c.EndDate <= EndDate orderby c.StartDate select c.EndDate);

			if (!string.IsNullOrWhiteSpace(status) && status != "All")
				projects = projects.Where(x => x.StatusId.ToString() == status);
			if (!string.IsNullOrWhiteSpace(classification) && classification != "All")
				projects = projects.Where(x => x.Type.Id.ToString() == classification);

			Guid scenarioGroup = Guid.Empty;
			Guid.TryParse(filterGroup, out scenarioGroup);

			Guid[] projectIds = (from p in projects select p.Id).ToArray();
            Guid[] expCatIds = null;
            if (isLaborMode) expCatIds = (from e in db.ExpenditureCategory where e.Type == (int)ExpenditureCategoryModel.CategoryTypes.Labor select e.Id).ToArray();
            else expCatIds = (from e in db.ExpenditureCategory where e.Type != (int)ExpenditureCategoryModel.CategoryTypes.Labor select e.Id).ToArray();
			List<int> scTypes = new List<int>();
			if (!noOldCapacities)
				scTypes.AddRange(new int[] { (int)ScenarioType.Capacity, (int)ScenarioType.Vacation, (int)ScenarioType.Training });

			if (scenarioType != 0)
				scTypes.Add(scenarioType);
			else
				scTypes.Add((int)ScenarioType.Portfolio);

			IQueryable<Data> data = null;
			if (scenarioGroup != Guid.Empty)
			{
				data = (from sd in db.ScenarioDetail.AsNoTracking()
						join s in db.Scenarios on sd.ParentID equals s.Id
                        join p in db.Projects on s.ParentId equals p.Id
                        where monthDates.Contains(sd.WeekEndingDate.Value)
						&& projectIds.Contains(s.ParentId.Value) && s.Status == (int?)ScenarioStatus.Active
						&& scTypes.Contains(s.Type) && expCatIds.Contains(sd.ExpenditureCategoryId.Value) && s.Scenario2Group.Any(g => g.GroupId == scenarioGroup)
						orderby sd.WeekEndingDate
						select new Data
						{
							cost = sd.Cost,
							quantity = sd.Quantity,
							weekend = sd.WeekEndingDate,
							projectStatus = projects.FirstOrDefault(x => x.Id == s.ParentId).Status.Name,
							type = s.Type,
							Name = s.Name,
                            ScenarioBUCost = (s.UseLMMargin ?? 0) == 1 ? s.BUDirectCosts_LM : s.BUDirectCosts,
                            ScenarioDuration = s.Duration ?? 0,
                            ExpCatId = sd.ExpenditureCategoryId,
                            ProjectId = s.ParentId.Value,
                            ProjectType = p.TypeId
						});
			}
			else
			{
				data = (from sd in db.ScenarioDetail.AsNoTracking()
						join s in db.Scenarios on sd.ParentID equals s.Id
                        join p in db.Projects on s.ParentId equals p.Id
                        where monthDates.Contains(sd.WeekEndingDate.Value)
						&& projectIds.Contains(s.ParentId.Value) && s.Status == (int?)ScenarioStatus.Active
						&& scTypes.Contains(s.Type) && expCatIds.Contains(sd.ExpenditureCategoryId.Value)
						orderby sd.WeekEndingDate
						select new Data
						{
							cost = sd.Cost,
							quantity = sd.Quantity,
							weekend = sd.WeekEndingDate,
							projectStatus = projects.FirstOrDefault(x => x.Id == s.ParentId).Status.Name,
							type = s.Type,
							Name = s.Name,
                            ScenarioBUCost = (s.UseLMMargin ?? 0) == 1 ? s.BUDirectCosts_LM : s.BUDirectCosts,
                            ScenarioDuration = s.Duration ?? 0,
                            ExpCatId = sd.ExpenditureCategoryId,
                            ProjectId = s.ParentId.Value,
                            ProjectType = p.TypeId
						});
			}
			return data;
		}

        public List<PieData> GetPieData(DateTime startDate, DateTime endDate, Guid[] principals, Guid userId, ForecastDashboardMode mode, Guid teamId, Guid viewId, bool isLaborMode)
        {
            IQueryable<Project> projects = (from c in db.Projects
                                            join o in db.ProjectAccesses on c.Id equals o.ProjectId
                                            where principals.Contains(o.PrincipalId) && !c.ParentProjectId.HasValue
                                            select c);
            List<Guid> teams = new List<Guid>();
            if (ForecastDashboardMode.TeamForecast == mode)
            {
                projects = projects.Where(p => p.Team2Project.Any(t2p => t2p.TeamId == teamId));
                teams.Add(teamId);
            }
            if (ForecastDashboardMode.ViewForecast == mode)
            {
                projects = projects.Where(p => p.Team2Project.Any(t2p => t2p.Team.Team2View.Any(tv => tv.ViewId == viewId)));
                teams.AddRange(db.Teams.Where(t => t.Team2View.Any(tv => tv.ViewId == viewId)).Select(t => t.Id).ToList());
            }

            return GetPieChartData(startDate, endDate.AddDays(7), projects, isLaborMode);
        }

	    /// <summary>
	    /// 
	    /// </summary>
	    /// <param name="startDate">Start of reporting period.</param>
	    /// <param name="endDate">End of reporting period.</param>
	    /// <param name="projects">A queryable collection of projects to filter by.</param>
	    /// <param name="isLaborMode">Indicates whether to load data for labor or for materials expenditure categories.</param>
	    /// <returns>A list of data for each classification.</returns>
	    private List<PieData> GetPieChartData(DateTime startDate, DateTime endDate, IQueryable<Project> projects, bool isLaborMode)
        {
            var projectIds = (from p in projects select p.Id).ToArray();
            var expCatIds = isLaborMode
                            ? (from e in db.ExpenditureCategory
                               where e.Type == (int) ExpenditureCategoryModel.CategoryTypes.Labor
                               select e.Id).ToArray()
                            : (from e in db.ExpenditureCategory
                               where e.Type != (int) ExpenditureCategoryModel.CategoryTypes.Labor
                               select e.Id).ToArray();
            var scTypes = new List<int> {(int) ScenarioType.Portfolio, (int) ScenarioType.Scheduling};
            var types = db.Types.AsNoTracking().ToDictionary(key => key.Id, elem => elem.Name);
            var data = (from sd in db.ScenarioDetail
                        join s in db.Scenarios on sd.ParentID equals s.Id
                        join p in db.Projects on s.ParentId equals p.Id
                        //where !(s.StartDate <= StartDate || s.EndDate >= EndDate)
                        where sd.WeekEndingDate >= startDate && sd.WeekEndingDate <= endDate
                        && projectIds.Contains(s.ParentId.Value) && s.Status == (int?)ScenarioStatus.Active
                        && scTypes.Contains(s.Type) && expCatIds.Contains(sd.ExpenditureCategoryId.Value)
                        select new
                        {
                            s.Id,
                            s.UseLMMargin,
                            s.BUDirectCosts_LM,
                            s.BUDirectCosts,
                            s.Duration,
                            p.TypeId,
                        }).Distinct().ToArray().GroupBy(group=> group.TypeId).Select(grouping => new PieData
                            {
                                TypeId = grouping.Key,
                                Cost = grouping.Sum(x=> (x.UseLMMargin ?? 0) == 1 ? x.BUDirectCosts_LM??0 : x.BUDirectCosts??0),
                                Time = grouping.Sum(x=>x.Duration??0),
                                Label = types.ContainsKey(grouping.Key) ? types[grouping.Key] : string.Empty
                            }).ToList();
            return data;
        }

		private Dictionary<DateTime, PeopleResourceModel.CapacityValues> GetWeeklyViewPlannedCapacity(DateTime StartDate, DateTime EndDate, Guid ViewId, Dictionary<Guid, ExpenditureCategory> expCats, Dictionary<Guid, UOM> uoms, bool? isUOMMode)
        {
            var context = new EnVisageEntities();
            var plannedScenarios = (from s in context.Teams
                                    join tv in context.Team2View on s.Id equals tv.TeamId
                                    where tv.ViewId == ViewId && s.PlannedCapacityScenarioId != null
                                    select s.PlannedCapacityScenarioId.Value).ToArray();
            return GetWeeklyPlannedCapacity(StartDate, EndDate, plannedScenarios, expCats, uoms, isUOMMode);
        }

		private Dictionary<DateTime, PeopleResourceModel.CapacityValues> GetWeeklyTeamPlannedCapacity(DateTime StartDate, DateTime EndDate, Guid[] TeamIds, Dictionary<Guid, ExpenditureCategory> expCats, Dictionary<Guid, UOM> uoms, bool? isUOMMode)
        {
            var context = new EnVisageEntities();
			var plannedScenarios = (from s in context.Teams where TeamIds.Contains(s.Id) && s.PlannedCapacityScenarioId != null select s.PlannedCapacityScenarioId.Value).ToArray();
            return GetWeeklyPlannedCapacity(StartDate, EndDate, plannedScenarios, expCats, uoms, isUOMMode);
        }

		private Dictionary<DateTime, PeopleResourceModel.CapacityValues> GetWeeklyPlannedCapacity(DateTime StartDate, DateTime EndDate, Guid[] SccenarioIds, Dictionary<Guid, ExpenditureCategory> expCats, Dictionary<Guid, UOM> uoms, bool? isUOMMode)
        {
            var dict = new Dictionary<DateTime, PeopleResourceModel.CapacityValues>();
            var context = new EnVisageEntities();

            // get weekending dates between resource Start and End dates
            var weekendings = context.FiscalCalendars
                .Where(x => x.Type == (int)EnVisage.Models.FiscalCalendarModel.FiscalYearType.Week &&
                    x.StartDate >= StartDate && x.EndDate <= EndDate).Select(x => x.EndDate);
            var plannedScenarios = (from s in context.Scenarios where s.Type == (int)ScenarioType.TeamPlannedCapacity select s.Id).ToArray();

			Guid[] expCatIds = expCats.Keys.ToArray();

            var sds = (from sd in context.ScenarioDetail where SccenarioIds.Contains(sd.ParentID.Value) && weekendings.Contains(sd.WeekEndingDate.Value) && expCatIds.Contains(sd.ExpenditureCategoryId.Value) select sd).ToList();

            // fill dictionary with weekly capacity for each week
            foreach (var week in weekendings)
            {
                if (!dict.ContainsKey(week))
                {
                    var quantity = 0M;
                    var cost = 0M;
                    var sd = (from s in sds where s.WeekEndingDate == week select s).ToList();
                    foreach (var s in sd)
                    {
                        quantity += s.Quantity.Value * Utils.GetUOMMultiplier(expCats, uoms, s.ExpenditureCategoryId ?? Guid.Empty, isUOMMode);
                        cost += s.Cost.Value;
                    }
                    dict.Add(week, new PeopleResourceModel.CapacityValues()
                    {
                        Quantity = quantity,
                        Cost = cost,
                        WeekEnding = week
                    });
                }
            }
            return dict;
        }
	}
}