609 lines
31 KiB
C#
609 lines
31 KiB
C#
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;
|
||
}
|
||
}
|
||
} |