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);
}
