public List <SpreadRow> GetSpreadForLocation()
{
var fleetHistory = FleetHistoryQueryable.GetAvailabilityHistory(DataContext, Parameters);
var holdingCosts = from lhc in DataContext.LifecycleHoldingCosts
where lhc.Month == 1 &&
lhc.Year == 2014
select lhc;
var revenue = from rev in DataContext.RevenueByCommercialCarSegments
where rev.Month == 1 &&
rev.Year == 2014
group rev by new { rev.CarGroupId, rev.LocationId }
into groupedData
select new
{
groupedData.Key.CarGroupId,
groupedData.Key.LocationId,
Revenue = groupedData.Sum(d => d.GrossRevenue)
};
var groupedHistory = from fh in fleetHistory
group fh by new { fh.CarGroupId, fh.LocationId }
into groupedData
select new { groupedData.Key.CarGroupId, groupedData.Key.LocationId, TotalFleet = groupedData.Sum(d => d.AvgTotal) };
var spreadData = from gd in groupedHistory
join rev in revenue on
new { gd.CarGroupId, gd.LocationId }
equals new { rev.CarGroupId, rev.LocationId }
join lc in holdingCosts on
gd.CarGroupId equals lc.CarGroupId
join cg in DataContext.CAR_GROUPs on gd.CarGroupId equals cg.car_group_id
join l in DataContext.LOCATIONs on gd.LocationId equals l.dim_Location_id
select new SpreadRow
{
Pool = l.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1,
LocationGroup = l.CMS_LOCATION_GROUP.cms_location_group1,
Location = l.location1,
CarSegment = cg.CAR_CLASS.CAR_SEGMENT.car_segment1,
CarClass = cg.CAR_CLASS.car_class1,
CarGroup = cg.car_group1,
TotalFleet = gd.TotalFleet,
Revenue = rev.Revenue,
Cost = lc.Cost
};
var returned = spreadData.ToList().OrderByDescending(d => d.Spread).ToList();
return(returned);
}
public IQueryable <NessesaryFleet> CalculateMinNessesaryFleet()
{
var fleetHistory = FleetHistoryQueryable.GetAvailabilityHistory(DataContext, Parameters);
var groupedOnWeek = from fh in fleetHistory
join week in DataContext.IsoWeekOfYears on fh.Timestamp equals week.Day
group fh by new
{
fh.LocationId,
fh.CarGroupId,
week.WeekOfYear
}
into groupedData
select new
{
groupedData.Key.CarGroupId,
groupedData.Key.LocationId,
OnRent = groupedData.Max(d => d.PeakOnRent),
//DayOfWeek = groupedData.Key.DayOfWeek
};
var averagedForMonth = from gow in groupedOnWeek
group gow by new
{
gow.LocationId,
gow.CarGroupId,
}
into groupedData
select new NessesaryFleet
{
LocationId = groupedData.Key.LocationId,
CarGroupId = groupedData.Key.CarGroupId,
AverageOnRent = groupedData.Average(d => d.OnRent)
};
return(averagedForMonth);
}
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 IQueryable <DemandGapVisualization> GetDemandGapStepOneViz()
{
var fleetHistory = FleetHistoryQueryable.GetAvailabilityHistory(DataContext, Parameters);
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 }
into groupedData
select new
{
LocationId = groupedData.Key.LocId,
CarGroupId = groupedData.Key.CarGrpId,
WeekNumber = groupedData.Key.WeekOfYear,
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 }
into groupedData
select new
{
LocationId = groupedData.Key.LocId,
CarGroupId = groupedData.Key.CarGrpId,
WeekNumber = groupedData.Key.WeekOfYear,
Deletions = groupedData.Sum(d => d.Value)
};
var demandGapData = from fh in fleetHistory
join minFs in DataContext.MinNessesaryFleets on
new { fh.CarGroupId, fh.LocationId } equals new { minFs.CarGroupId, minFs.LocationId }
join maxFs in DataContext.MaxFleetSizes on
new { fh.CarGroupId, fh.LocationId } equals new { maxFs.CarGroupId, maxFs.LocationId }
join adds in additions on new { fh.CarGroupId, fh.LocationId, maxFs.WeekNumber }
equals new { adds.CarGroupId, adds.LocationId, adds.WeekNumber }
into joinedAdds
from joinedAdditions in joinedAdds.DefaultIfEmpty()
join dels in deletions on new { fh.CarGroupId, fh.LocationId, maxFs.WeekNumber }
equals new { dels.CarGroupId, dels.LocationId, dels.WeekNumber }
into joinedDels
from joinedDeletions in joinedDels.DefaultIfEmpty()
group new { fh, minFs, maxFs, joinedAdditions, joinedDeletions } by
new { fh.CarGroupId, fh.LocationId, maxFs.WeekNumber, maxFs.PeakDay.Year }
into groupedData
join cg in DataContext.CAR_GROUPs on groupedData.Key.CarGroupId equals cg.car_group_id
join loc in DataContext.LOCATIONs on groupedData.Key.LocationId equals loc.dim_Location_id
orderby groupedData.Key.Year, groupedData.Key.WeekNumber, loc.location1, cg.car_group1
select new DemandGapVisualization
{
CarGroupId = groupedData.Key.CarGroupId,
CarGroupName = cg.car_group1,
LocationId = groupedData.Key.LocationId,
LocationName = loc.location1,
WeekNumber = groupedData.Key.WeekNumber,
OperationalFleet = groupedData.Sum(d => d.fh.PeakOperationalFleet),
MinFleetRequired = groupedData.Sum(d => d.minFs.MinFleet),
MaxFleet = groupedData.Sum(d => d.maxFs.MaxFleet),
Additions = groupedData.Sum(d => d.joinedAdditions == null ? 0 : d.joinedAdditions.Additions),
Deletions = groupedData.Sum(d => d.joinedDeletions == null ? 0 : d.joinedDeletions.Deletions),
ExpectedFleet = groupedData.Sum(d => d.fh.PeakOperationalFleet)
- groupedData.Sum(d => d.joinedDeletions == null ? 0 : d.joinedDeletions.Deletions)
+ groupedData.Sum(d => d.joinedAdditions == null ? 0 : d.joinedAdditions.Additions)
};
var returned = demandGapData;
//var xx = returned.Where(d => d.Additions != 0.0).Take(10).ToList();
return(returned);
}
public IQueryable <DemandGapOneRow> GetDemandGapStepOne()
{
var fleetHistory = FleetHistoryQueryable.GetAvailabilityHistory(DataContext, Parameters);
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)
};
var demandGapData = from fh in fleetHistory
join minFs in DataContext.MinNessesaryFleets on
new { fh.CarGroupId, fh.LocationId } equals new { minFs.CarGroupId, minFs.LocationId }
join maxFs in DataContext.MaxFleetSizes on
new { fh.CarGroupId, fh.LocationId } equals new { maxFs.CarGroupId, maxFs.LocationId }
join adds in additions on new { fh.CarGroupId, fh.LocationId, maxFs.WeekNumber }
equals new { adds.CarGroupId, adds.LocationId, adds.WeekNumber }
into joinedAdds
from joinedAdditions in joinedAdds.DefaultIfEmpty()
join dels in deletions on new { fh.CarGroupId, fh.LocationId, maxFs.WeekNumber }
equals new { dels.CarGroupId, dels.LocationId, dels.WeekNumber }
into joinedDels
from joinedDeletions in joinedDels.DefaultIfEmpty()
group new { fh, minFs, maxFs, joinedAdditions, joinedDeletions } by
new { fh.CarGroupId, fh.LocationId, maxFs.WeekNumber, maxFs.PeakDay.Year }
into groupedData
join cg in DataContext.CAR_GROUPs on groupedData.Key.CarGroupId equals cg.car_group_id
join loc in DataContext.LOCATIONs on groupedData.Key.LocationId equals loc.dim_Location_id
join weekToMonth in DataContext.IsoWeekToMonths on
new { groupedData.Key.WeekNumber, groupedData.Key.Year }
equals new { WeekNumber = weekToMonth.IsoWeekNumber, Year = (int)weekToMonth.Year }
orderby groupedData.Key.Year, groupedData.Key.WeekNumber, loc.location1, cg.car_group1
select new DemandGapOneRow
{
Year = groupedData.Key.Year,
WeekNumber = groupedData.Key.WeekNumber,
MonthNumber = weekToMonth.Month,
CarGroupName = cg.car_group1,
CarGroupId = groupedData.Key.CarGroupId,
LocationId = groupedData.Key.LocationId,
CarSegmentId = cg.CAR_CLASS.car_segment_id,
LocationName = loc.location1,
OperationalFleet = groupedData.Sum(d => d.fh.PeakOperationalFleet),
AdditionDeletionSum = groupedData.Sum(d => d.joinedAdditions == null ? 0 : d.joinedAdditions.Additions) - groupedData.Sum(d => d.joinedDeletions == null ? 0 : d.joinedDeletions.Deletions),
MinFleet = ((int)groupedData.Sum(d => d.minFs.MinFleet)),
ReasonForGap = string.Empty
};
var returned = demandGapData;
//var xx = returned.Take(10).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);
}
