public List <ForecastContributionRow> GetForecastContribution(int additionPlanA, int additionPlanB) { var maxDate = DataContext.RevenueByCommercialCarSegments.Max(d => d.MonthDate); var minDate = maxDate.AddMonths(-3); var monthlyAverageExpected = GetExpectedFLeet(); var groupedAdditionAData = GetQuerysFromPlan(additionPlanA); var groupedAdditionBData = GetQuerysFromPlan(additionPlanB); var contributionData = ContributionQueryable.GetContribution(DataContext, minDate); var fleetHistory = FleetHistoryQueryable.GetAvailabilityHistory(DataContext, Parameters); var sumFleet = from fh in fleetHistory group fh by new { fh.CarGroupId, fh.LocationId, fh.Timestamp } into gd select new { gd.Key.CarGroupId, gd.Key.LocationId, TotalFleet = gd.Sum(d => d.MaxTotal) }; var totalFleet = from fh in sumFleet group fh by new { fh.CarGroupId, fh.LocationId } into gd select new { gd.Key.CarGroupId, gd.Key.LocationId, TotalFleet = gd.Average(d => d.TotalFleet) }; var forecastData = from wae in monthlyAverageExpected join tf in totalFleet on new { wae.CarGroupId, wae.LocationId } equals new { tf.CarGroupId, tf.LocationId } join cntr in contributionData on new { wae.CarGroupId, wae.LocationId } equals new { cntr.CarGroupId, cntr.LocationId } into jContri from joinedContri in jContri.DefaultIfEmpty() join cg in DataContext.CAR_GROUPs on wae.CarGroupId equals cg.car_group_id join loc in DataContext.LOCATIONs on wae.LocationId equals loc.dim_Location_id orderby wae.Year, wae.Month select new ForecastContributionRow { Year = wae.Year, Month = wae.Month, CarGroup = cg.car_group1, Location = loc.location1, CpU = (((joinedContri == null ? 0 : joinedContri.Revenue) / tf.TotalFleet) - (joinedContri == null ? 0 : joinedContri.HoldingCost)), Expected = (double)wae.Expected, CumulativeAdditionsA = groupedAdditionAData.Where(d => (d.Year < wae.Year || (d.Year == wae.Year && d.Month <= wae.Month)) && d.CarGroupId == wae.CarGroupId && d.LocationId == wae.LocationId ).Sum(d => (int?)d.Additions) ?? 0, CumulativeAdditionsB = groupedAdditionBData.Where(d => (d.Year < wae.Year || (d.Year == wae.Year && d.Month <= wae.Month)) && d.CarGroupId == wae.CarGroupId && d.LocationId == wae.LocationId ).Sum(d => (int?)d.Additions) ?? 0 }; var returned = forecastData.ToList(); return(returned); }
public List <WeeklyMaxMinValues> CalculateMinMax(int minFleetScenarioId, int maxFleetFactorScenarioId, int weeksToCalculate) { var additions = from ad in DataContext.ResAdditions join isoWeek in DataContext.IsoWeekOfYears on ad.RepDate.Date equals isoWeek.Day.Date group new { ad, isoWeek } by new { ad.CarGrpId, ad.LocId, isoWeek.WeekOfYear, isoWeek.Day.Year } into groupedData select new { LocationId = groupedData.Key.LocId, CarGroupId = groupedData.Key.CarGrpId, WeekNumber = groupedData.Key.WeekOfYear, Year = groupedData.Key.Year, Additions = groupedData.Sum(d => d.ad.Value) }; var deletions = from del in DataContext.ResDeletions join isoWeek in DataContext.IsoWeekOfYears on del.RepDate.Date equals isoWeek.Day.Date group del by new { del.CarGrpId, del.LocId, isoWeek.WeekOfYear, isoWeek.Day.Year } into groupedData select new { LocationId = groupedData.Key.LocId, CarGroupId = groupedData.Key.CarGrpId, WeekNumber = groupedData.Key.WeekOfYear, Year = groupedData.Key.Year, Deletions = groupedData.Sum(d => d.Value) }; //How many weeks to calculate into the future var minFleetDataAccess = new MinFleetDataAccess(Parameters, DataContext); var minFleet = minFleetDataAccess.CalculateMinNessesaryFleetCommercialSegment(minFleetScenarioId); var maxFleetDataAccess = new MaxFleetDataAccess(Parameters, DataContext); var maxFleet = maxFleetDataAccess.GetMaxFleetSize(maxFleetFactorScenarioId); //Build Operational Fleet from the last entry in the Availability History //var firstCmsPredictionDate = DataContext.FleetHistories.Max(d => d.Timestamp); //Parameters[DictionaryParameter.StartDate] = firstCmsPredictionDate.ToShortDateString(); var fleetHistory = FleetHistoryQueryable.GetAvailabilityHistory(DataContext, Parameters); //Sum FleetType Away, Leaving Date var sumFleet = from fh in fleetHistory group fh by new { fh.CarGroupId, fh.LocationId, fh.Timestamp } into gd select new { gd.Key.CarGroupId, gd.Key.LocationId, TotalFleet = gd.Sum(d => d.MaxTotal) }; //Sum Date Away var totalFleet = from fh in sumFleet group fh by new { fh.CarGroupId, fh.LocationId } into gd select new { gd.Key.CarGroupId, gd.Key.LocationId, TotalFleet = gd.Average(d => d.TotalFleet) }; var maxDate = DataContext.RevenueByCommercialCarSegments.Max(d => d.MonthDate); var minDate = maxDate.AddMonths(-3); var contributionData = ContributionQueryable.GetContribution(DataContext, minDate); var demandGapData = from fh in totalFleet join minFs in minFleet on new { fh.CarGroupId, fh.LocationId } equals new { minFs.CarGroupId, minFs.LocationId } join maxFs in maxFleet on new { fh.CarGroupId, fh.LocationId } equals new { maxFs.CarGroupId, maxFs.LocationId } join adds in additions on new { fh.CarGroupId, fh.LocationId, maxFs.WeekNumber, maxFs.Year } equals new { adds.CarGroupId, adds.LocationId, adds.WeekNumber, adds.Year } into joinedAdds from joinedAdditions in joinedAdds.DefaultIfEmpty() join dels in deletions on new { fh.CarGroupId, fh.LocationId, maxFs.WeekNumber, maxFs.Year } equals new { dels.CarGroupId, dels.LocationId, dels.WeekNumber, dels.Year } into joinedDels from joinedDeletions in joinedDels.DefaultIfEmpty() join cd in contributionData on new { fh.CarGroupId, fh.LocationId } equals new { cd.CarGroupId, cd.LocationId } into joinedCd from joinedContriData in joinedCd.DefaultIfEmpty() join cg in DataContext.CAR_GROUPs on fh.CarGroupId equals cg.car_group_id join loc in DataContext.LOCATIONs on fh.LocationId equals loc.dim_Location_id join weekToMonth in DataContext.IsoWeekToMonths on new { maxFs.WeekNumber, maxFs.Year } equals new { WeekNumber = weekToMonth.IsoWeekNumber, Year = (int)weekToMonth.Year } // orderby groupedData.Key.Year, groupedData.Key.WeekNumber, loc.location1, cg.car_group1 select new WeeklyMaxMinValues { Year = maxFs.Year, WeekNumber = maxFs.WeekNumber, MonthNumber = weekToMonth.Month, CarGroupName = cg.car_group1, CarGroupId = fh.CarGroupId, LocationId = fh.LocationId, CarSegmentId = cg.CAR_CLASS.car_segment_id, LocationName = loc.location1, TotalFleet = (int)fh.TotalFleet, AdditionDeletionSum = (joinedAdditions == null ? 0 : joinedAdditions.Additions) - (joinedDeletions == null ? 0 : joinedDeletions.Deletions), MinFleet = ((int)minFs.MinNessesaryFleetRequired), MaxFleet = ((int)maxFs.MaxForLocation), ReasonForGap = string.Empty, Contribution = (decimal)((joinedContriData == null || joinedContriData.HoldingCost == 0 || joinedContriData.Revenue == 0 ? 0 : (joinedContriData.Revenue / fh.TotalFleet) - (joinedContriData.HoldingCost))), Revenue = joinedContriData == null ? 0 : joinedContriData.Revenue, HoldingCost = joinedContriData == null ? 0 : joinedContriData.HoldingCost }; var returned = demandGapData.ToList(); return(returned); }