public List <ActualDataEntity> GetRentalCheckIns()
{
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(Parameters, DataContext, true, true);
if (Parameters.ContainsValueAndIsntEmpty(DictionaryParameter.ExcludeAirportForLongtermRentals) &&
Parameters[DictionaryParameter.ExcludeAirportForLongtermRentals] == true.ToString())
{
vehicles = vehicles.Except(vehicles.Where(d => d.LastLocationCode.Substring(5, 1) != "5" &&
SqlMethods.DateDiffDay(d.LastChangeDateTime, d.ExpectedDateTime) > 27));
}
var startDate = DateTime.Parse(Parameters[DictionaryParameter.StartDate]);
var endDate = DateTime.Parse(Parameters[DictionaryParameter.EndDate]);
var totalCheckInVehicles = vehicles.Where(d => d.LastMovementTypeId == 10 &&
d.ExpectedDateTime.HasValue &&
d.ExpectedDateTime >= startDate &&
d.ExpectedDateTime <= endDate);
var groupedVehData = from ci in totalCheckInVehicles
join loc in DataContext.LOCATIONs on ci.ExpectedLocationCode equals loc.location1
group ci by new { ci.ExpectedDateTime.Value.Date, TotalHours = ci.ExpectedDateTime.Value.Hour + loc.turnaround_hours ?? 0 }
into groupedRes
orderby groupedRes.Key.Date, groupedRes.Key.TotalHours
select new ActualDataEntity
{
Day = groupedRes.Key.Date,
Hour = groupedRes.Key.TotalHours,
Number = groupedRes.Count()
};
var returned = groupedVehData.ToList();
return(returned);
}
public IQueryable <FleetStatusRow> ExtractVehicleColumns(IQueryable <IGrouping <string, Vehicle> > groupedQueryable)
{
var divisorType = BaseVehicleDataAccess.GetPercentageDivisorTypeFromParameters(Parameters);
var dt = DateTime.Now.Date;
var fsrData = from gq in groupedQueryable
select new FleetStatusRow(divisorType, false)
{
Key = gq.Key,
TotalFleet = gq.Count(),
Cu = gq.Sum(d => d.LastOperationalStatusId == 2 ? 1 : 0),
Ha = gq.Sum(d => d.LastOperationalStatusId == 4 ? 1 : 0),
Hl = gq.Sum(d => d.LastOperationalStatusId == 5 ? 1 : 0),
Ll = gq.Sum(d => d.LastOperationalStatusId == 6 ? 1 : 0),
Nc = gq.Sum(d => d.LastOperationalStatusId == 8 ? 1 : 0),
Pl = gq.Sum(d => d.LastOperationalStatusId == 9 ? 1 : 0),
Tc = gq.Sum(d => d.LastOperationalStatusId == 16 ? 1 : 0),
Sv = gq.Sum(d => d.LastOperationalStatusId == 14 ? 1 : 0),
Ws = gq.Sum(d => d.LastOperationalStatusId == 19 ? 1 : 0),
Bd = gq.Sum(d => d.LastOperationalStatusId == 1 ? 1 : 0),
Mm = gq.Sum(d => d.LastOperationalStatusId == 7 ? 1 : 0),
Tw = gq.Sum(d => d.LastOperationalStatusId == 18 ? 1 : 0),
Tb = gq.Sum(d => d.LastOperationalStatusId == 15 ? 1 : 0),
Fs = gq.Sum(d => d.LastOperationalStatusId == 3 ? 1 : 0),
Rl = gq.Sum(d => d.LastOperationalStatusId == 10 ? 1 : 0),
Rp = gq.Sum(d => d.LastOperationalStatusId == 11 ? 1 : 0),
Tn = gq.Sum(d => d.LastOperationalStatusId == 17 ? 1 : 0),
Idle = gq.Sum(d => (d.LastOperationalStatusId == 12 && d.LastMovementTypeId != 10) ? 1 : 0),
Su = gq.Sum(d => d.LastOperationalStatusId == 13 ? 1 : 0),
Overdue = gq.Sum(d => (d.LastOperationalStatusId == 12 && d.LastMovementTypeId == 10 &&
d.ExpectedDateTime < dt) ? 1 : 0)
};
var returned = fsrData;
return(returned);
}
protected ComparisonRow BuildUnmappedVehicleHistoryRow(bool siteComparison)
{
var vehicleHistories = BaseVehicleDataAccess.GetVehicleHistoryQueryable(Parameters, DataContext, true, true);
if (vehicleHistories == null)
{
return(new ComparisonRow());
}
var startDate = Parameters.GetDateFromDictionary(DictionaryParameter.StartDate);
vehicleHistories = from hd in vehicleHistories
where hd.TimeStamp == startDate
select hd;
var historyGroups = from v in vehicleHistories
select new { v.VehicleHistoryId, cg = v.Vehicle.CarGroup, country = v.Vehicle.OwningCountry, lastLocation = v.LocationCode };
IQueryable <int> matchedSet;
if (siteComparison)
{
matchedSet = from hg in historyGroups
join loc in DataContext.LOCATIONs.Select(d => d.location1).Distinct()
on hg.lastLocation equals loc
select hg.VehicleHistoryId;
}
else
{
var carGroups = (from cg in DataContext.CAR_GROUPs
select new { cg = cg.car_group1, cg.CAR_CLASS.CAR_SEGMENT.country }).Distinct();
matchedSet = from hg in historyGroups
join cg in carGroups on new { hg.cg, hg.country }
equals new { cg.cg, cg.country }
select hg.VehicleHistoryId;
}
var unmappedCars = from vh in vehicleHistories.Where(d => !matchedSet.Any(m => m == d.VehicleHistoryId))
select vh;
if (!unmappedCars.Any())
{
return(null);
}
var totalRow = new ComparisonRow
{
Key = NotFoundKeyName,
FleetCount = unmappedCars.Count(),
NonRevCount = unmappedCars.Sum(d => d.IsNonRev ? 1 : 0),
ReasonsEntered = unmappedCars.Sum(d => d.RemarkId == null ? 0 : 1),
};
return(totalRow);
}
public List <FleetStatusRow> GetHistoricalTrend()
{
var availabilityKeyGrouping = BaseVehicleDataAccess.GetAvailabilityGroupingFromParameters(Parameters);
var groupedQueryable = GetAvailabilityHistoryGroupedByDate();
var fleetDayKeyGrouping = ExtractFleetHistoryColumns(availabilityKeyGrouping, groupedQueryable);
var returned = fleetDayKeyGrouping.OrderBy(d => d.Day).ToList();
return(returned);
}
public int GetCurrentAvailable()
{
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(Parameters, DataContext, true, true);
var totalAvailable =
vehicles.Count(d => d.LastOperationalStatusId == 12 && d.LastMovementTypeId != 10);
return(totalAvailable);
}
private IEnumerable <LocationSummaryRow> GetNonRevSummaryData(
Dictionary <DictionaryParameter, string> parameters)
{
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(parameters, DataContext, false, true);
var carClassGroupedData = GroupByCarClass(vehicles);
var lsrData = BuildLocationSummaryNonRevRows(carClassGroupedData);
var returned = lsrData.ToList();
return(returned);
}
public int GetOpenTripsDue()
{
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(Parameters, DataContext, true, true);
// 12 == Rental Agreement Open
var nowHour = DateTime.Now.Date.AddHours(DateTime.Now.Hour);
var collections = vehicles.Where(d => (d.LastMovementTypeId == 12) &&
d.ExpectedDateTime < nowHour
);
var returned = collections.Count();
return(returned);
}
public int GetCollections()
{
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(Parameters, DataContext, true, true);
// 13 == Transfer Open 4 == Transfer Lorry Open
var nowHour = DateTime.Now.Date.AddHours(DateTime.Now.Hour);
var collections = vehicles.Where(d => (d.LastMovementTypeId == 13 ||
d.LastMovementTypeId == 4) &&
d.ExpectedDateTime < nowHour
);
var returned = collections.Count();
return(returned);
}
private IEnumerable <LocationSummaryRow> GetAvailabilitySummaryData(Dictionary <DictionaryParameter, string> parameters)
{
var fleetStatusDataAccess = new ComparisonDataAccess(parameters, DataContext);
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(parameters, DataContext, true, true);
var carClassGroupedData = GroupByCarClass(vehicles);
var extractedData = fleetStatusDataAccess.ExtractVehicleColumns(carClassGroupedData);
var availabilityRows = BuildLocationSummaryAvailabilityRows(extractedData);
return(availabilityRows);
}
private List <FleetStatusRow> GetHistoricalComparison(bool siteComparison)
{
var comparisonType = siteComparison ? ComparisonLevelLookup.GetSiteComparisonTypeFromParameters(Parameters)
: ComparisonLevelLookup.GetFleetComparisonTypeFromParameters(Parameters);
var groupedQueryable = GetTwoKeyGroupedAvailabilityHistory(comparisonType);
var availabilityKeyGrouping = BaseVehicleDataAccess.GetAvailabilityGroupingFromParameters(Parameters);
var fleetDayKeyGrouping = ExtractFleetHistoryColumns(availabilityKeyGrouping, groupedQueryable);
var fleetKeyGrouping = GroupByKey(fleetDayKeyGrouping);
var returned = fleetKeyGrouping.ToList();
return(returned);
}
private IEnumerable <LocationSummaryForeignRow> GetForeignVehicleSummaryData(Dictionary <DictionaryParameter, string> parameters)
{
var fleetStatusDataAccess = new ComparisonDataAccess(parameters, DataContext);
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(parameters, DataContext, true, true);
vehicles = from v in vehicles
where v.LastLocationCode.Substring(0, 2) != v.OwningCountry
select v;
var carClassGroupedData = GroupByCarSegmentName(vehicles);
var extractedData = fleetStatusDataAccess.ExtractVehicleColumns(carClassGroupedData);
var availabilityRows = BuildLocationSummaryForeignRows(extractedData);
return(availabilityRows);
}
private IQueryable <VehicleHistory> GetHistories(bool includeRevVehicles)
{
var vehicleHistories = BaseVehicleDataAccess.GetVehicleHistoryQueryable(Parameters, DataContext, includeRevVehicles, true);
if (vehicleHistories == null)
{
return(null);
}
var startDate = Parameters.GetDateFromDictionary(DictionaryParameter.StartDate);
var endDate = Parameters.GetDateFromDictionary(DictionaryParameter.EndDate);
vehicleHistories = from hd in vehicleHistories
where hd.TimeStamp >= startDate &&
hd.TimeStamp <= endDate
select hd;
return(vehicleHistories);
}
public List <FleetStatusRow> GetCurrentTrend()
{
var divisorType = BaseVehicleDataAccess.GetPercentageDivisorTypeFromParameters(Parameters);
var todayAvailability = GetAvailabilityToday();
var groupedData = from v in todayAvailability
group v by v.Timestamp
into gq
select new FleetStatusRow(divisorType, false)
{
Day = gq.Key,
TotalFleet = gq.Sum(d => (int)d.SumTotal),
Bd = gq.Sum(d => (int)d.SumBd),
Cu = gq.Sum(d => (int)d.SumCu),
Fs = gq.Sum(d => (int)d.SumFs),
Ha = gq.Sum(d => (int)d.SumHa),
Hl = gq.Sum(d => (int)d.SumHl),
Ll = gq.Sum(d => (int)d.SumLl),
Mm = gq.Sum(d => (int)d.SumMm),
Nc = gq.Sum(d => (int)d.SumNc),
Pl = gq.Sum(d => (int)d.SumPl),
Rl = gq.Sum(d => (int)d.SumRl),
Rp = gq.Sum(d => (int)d.SumRp),
Idle = gq.Sum(d => (int)d.SumIdle),
Su = gq.Sum(d => (int)d.SumSu),
Sv = gq.Sum(d => (int)d.SumSv),
Tb = gq.Sum(d => (int)d.SumTb),
Tc = gq.Sum(d => (int)d.SumTc),
Tn = gq.Sum(d => (int)d.SumTn),
Tw = gq.Sum(d => (int)d.SumTw),
Ws = gq.Sum(d => (int)d.SumWs),
Overdue = gq.Sum(d => (int)d.SumOverdue),
};
var returned = groupedData.OrderBy(d => d.Day).ToList();
return(returned);
}
private FleetStatusRow GetFleetCurrentStatusEntry()
{
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(Parameters, DataContext, true, true);
var dt = DateTime.Now.Date;
if (!vehicles.Any())
{
return(new FleetStatusRow(PercentageDivisorType.Values, false));
}
var returned = new FleetStatusRow(PercentageDivisorType.Values, false)
{
TotalFleet = vehicles.Count(),
Cu = vehicles.Sum(d => d.LastOperationalStatusId == 2 ? 1 : 0),
Ha = vehicles.Sum(d => d.LastOperationalStatusId == 4 ? 1 : 0),
Hl = vehicles.Sum(d => d.LastOperationalStatusId == 5 ? 1 : 0),
Ll = vehicles.Sum(d => d.LastOperationalStatusId == 6 ? 1 : 0),
Nc = vehicles.Sum(d => d.LastOperationalStatusId == 8 ? 1 : 0),
Pl = vehicles.Sum(d => d.LastOperationalStatusId == 9 ? 1 : 0),
Tc = vehicles.Sum(d => d.LastOperationalStatusId == 16 ? 1 : 0),
Sv = vehicles.Sum(d => d.LastOperationalStatusId == 14 ? 1 : 0),
Ws = vehicles.Sum(d => d.LastOperationalStatusId == 19 ? 1 : 0),
Bd = vehicles.Sum(d => d.LastOperationalStatusId == 1 ? 1 : 0),
Mm = vehicles.Sum(d => d.LastOperationalStatusId == 7 ? 1 : 0),
Tw = vehicles.Sum(d => d.LastOperationalStatusId == 18 ? 1 : 0),
Tb = vehicles.Sum(d => d.LastOperationalStatusId == 15 ? 1 : 0),
Fs = vehicles.Sum(d => d.LastOperationalStatusId == 3 ? 1 : 0),
Rl = vehicles.Sum(d => d.LastOperationalStatusId == 10 ? 1 : 0),
Rp = vehicles.Sum(d => d.LastOperationalStatusId == 11 ? 1 : 0),
Tn = vehicles.Sum(d => d.LastOperationalStatusId == 17 ? 1 : 0),
Idle = vehicles.Sum(d => (d.LastOperationalStatusId == 12 && d.LastMovementTypeId != 10) ? 1 : 0),
Su = vehicles.Sum(d => d.LastOperationalStatusId == 13 ? 1 : 0),
Overdue = vehicles.Sum(d => (d.LastOperationalStatusId == 12 && d.LastMovementTypeId == 10 &&
d.ExpectedDateTime < dt) ? 1 : 0)
};
return(returned);
}
public List <OverviewGridItemHolder> GetForeignVehicleOverviewGrid()
{
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(Parameters, DataContext, true, true);
vehicles = VehicleFieldRestrictions.RestrictByPredicament(vehicles, Parameters);
if (Parameters.ContainsValueAndIsntEmpty(DictionaryParameter.MinDaysNonRev))
{
var daysSinceLastRevenue = int.Parse(Parameters[DictionaryParameter.MinDaysNonRev]);
vehicles = vehicles.Where(d => d.DaysSinceLastRevenueMovement >= daysSinceLastRevenue);
}
var activeOwningCountries = GetActiveOwningCountries();
var returned = new List <OverviewGridItemHolder>();
var dict = GetCountryDescriptionDictionary();
var groupingLevel = GetGroupingLevel();
foreach (var oc in activeOwningCountries)
{
var owningCountry = oc;
IQueryable <LocationCount> filteredOwner = null;
switch (groupingLevel)
{
case DictionaryParameter.LocationCountry:
filteredOwner = from v in vehicles
where v.OwningCountry == owningCountry
group v by v.LastLocationCode.Substring(0, 2)
into g
select new LocationCount
{
LocationGroupingId = g.Key, Count = g.Count()
};
break;
case DictionaryParameter.Pool:
filteredOwner = from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
where v.OwningCountry == owningCountry
group l by l.CMS_LOCATION_GROUP.CMS_POOL.cms_pool_id
into g
select new LocationCount {
LocationGroupingId = g.Key.ToString(), Count = g.Count()
};
break;
case DictionaryParameter.Region:
filteredOwner = from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
where v.OwningCountry == owningCountry
group l by l.OPS_AREA.ops_region_id
into g
select new LocationCount {
LocationGroupingId = g.Key.ToString(), Count = g.Count()
};
break;
case DictionaryParameter.LocationGroup:
filteredOwner = from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
where v.OwningCountry == owningCountry
group l by l.cms_location_group_id
into g
select new LocationCount {
LocationGroupingId = g.Key.ToString(), Count = g.Count()
};
break;
case DictionaryParameter.Area:
filteredOwner = from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
where v.OwningCountry == owningCountry
group l by l.ops_area_id
into g
select new LocationCount {
LocationGroupingId = g.Key.ToString(), Count = g.Count()
};
break;
case DictionaryParameter.Location:
filteredOwner = from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
where v.OwningCountry == owningCountry
group l by l.dim_Location_id
into g
select new LocationCount {
LocationGroupingId = g.Key.ToString(), Count = g.Count()
};
break;
}
var gvItem = new OverviewGridItemHolder
{
CountryId = oc,
CountryName = dict[oc],
ForeignVehiclesHolder = new List <LocationIdHolder>()
};
if (filteredOwner != null)
{
var filteredLocalList = filteredOwner.ToList();
foreach (var fo in filteredLocalList)
{
gvItem.ForeignVehiclesHolder.Add(new LocationIdHolder
{
LocationId = fo.LocationGroupingId,
OwningCountry = owningCountry,
VehicleCount = fo.Count
});
}
}
returned.Add(gvItem);
}
foreach (var gv in returned)
{
gv.ForeignVehiclesHolder.Add(new LocationIdHolder {
LocationId = TotalString
, LocationName = TotalString
, VehicleCount = gv.ForeignVehiclesHolder.Sum(d => d.VehicleCount)
, OwningCountry = null
});
}
return(returned);
}
public Dictionary <string, string> GetDistinctOwningLocationIds()
{
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(Parameters, DataContext, true, true);
vehicles = from v in vehicles
//where v.LastLocationCode.Substring(0, 2) != v.OwningCountry
select v;
IQueryable <LocationIdHolder> dataHolder = null;
if (Parameters.ContainsValueAndIsntEmpty(DictionaryParameter.LocationGroup) ||
Parameters.ContainsValueAndIsntEmpty(DictionaryParameter.Area))
{
dataHolder = (from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
select new LocationIdHolder {
LocationId = l.dim_Location_id.ToString(), LocationName = l.location1
});
}
else
{
if (Parameters.ContainsValueAndIsntEmpty(DictionaryParameter.CmsSelected))
{
if (Parameters.ContainsValueAndIsntEmpty(DictionaryParameter.Pool))
{
var multiSelectionMade = Parameters[DictionaryParameter.Pool].Contains(VehicleFieldRestrictions.Separator);
dataHolder = (from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
select new LocationIdHolder
{
LocationId = l.cms_location_group_id.ToString(),
LocationName = (multiSelectionMade ? (l.country + JoinLocationParts
+ l.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1 + JoinLocationParts)
: string.Empty)
+ l.CMS_LOCATION_GROUP.cms_location_group1
});
}
else if (Parameters.ContainsValueAndIsntEmpty(DictionaryParameter.LocationCountry))
{
var multiSelectionMade = Parameters[DictionaryParameter.LocationCountry].Contains(VehicleFieldRestrictions.Separator);
dataHolder = (from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
select new LocationIdHolder
{
LocationId = l.CMS_LOCATION_GROUP.cms_pool_id.ToString(),
LocationName = (multiSelectionMade ? (l.country + JoinLocationParts)
: string.Empty)
+ l.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1
});
}
}
else
{
if (Parameters.ContainsValueAndIsntEmpty(DictionaryParameter.Region))
{
var multiSelectionMade = Parameters[DictionaryParameter.Region].Contains(VehicleFieldRestrictions.Separator);
dataHolder = (from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
select new LocationIdHolder
{
LocationId = l.ops_area_id.ToString(),
LocationName = (multiSelectionMade ? (l.country + JoinLocationParts
+ l.OPS_AREA.OPS_REGION.ops_region1 + JoinLocationParts)
: string.Empty)
+ l.OPS_AREA.ops_area1
});
}
else if (Parameters.ContainsValueAndIsntEmpty(DictionaryParameter.LocationCountry))
{
var multiSelectionMade = Parameters[DictionaryParameter.LocationCountry].Contains(VehicleFieldRestrictions.Separator);
dataHolder = (from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
select new LocationIdHolder
{
LocationId = l.OPS_AREA.ops_region_id.ToString(),
LocationName = (multiSelectionMade ? (l.country + JoinLocationParts)
: string.Empty)
+ l.OPS_AREA.OPS_REGION.ops_region1
});
}
}
}
if (dataHolder == null)
{
dataHolder = (from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
select new LocationIdHolder
{
LocationId = l.country,
LocationName = l.COUNTRy1.country_description
});
}
var returned = dataHolder.Distinct().OrderBy(d => d.LocationName).ToDictionary(d => d.LocationId, d => d.LocationName);
return(returned);
}
protected List <ComparisonRow> GetCurrentComparisonData(DictionaryParameter comparisonType)
{
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(Parameters, DataContext, true, true);
if (vehicles == null)
{
return(new List <ComparisonRow>());
}
IQueryable <IGrouping <string, VehicleRemarkIdHolder> > groupedRemData = null;
int minDays = 0;
if (Parameters.ContainsKey(DictionaryParameter.MinDaysNonRev) &&
Parameters[DictionaryParameter.MinDaysNonRev] != string.Empty)
{
minDays = int.Parse(Parameters[DictionaryParameter.MinDaysNonRev]);
}
var allRemarks = from r in DataContext.VehicleNonRevPeriodEntryRemarks
select r;
IEnumerable <int> selectedOperStats = null;
if (Parameters.ContainsValueAndIsntEmpty(DictionaryParameter.OperationalStatuses))
{
selectedOperStats = Parameters[DictionaryParameter.OperationalStatuses].Split(',').Select(int.Parse);
allRemarks = allRemarks.Where(d => selectedOperStats.Contains(d.VehicleNonRevPeriodEntry.VehicleNonRevPeriod.Vehicle.LastOperationalStatusId));
}
IEnumerable <int> selectedMovementTypes = null;
if (Parameters.ContainsValueAndIsntEmpty(DictionaryParameter.MovementTypes))
{
selectedMovementTypes = Parameters[DictionaryParameter.MovementTypes].Split(',').Select(int.Parse);
allRemarks = allRemarks.Where(d => selectedMovementTypes.Contains(d.VehicleNonRevPeriodEntry.VehicleNonRevPeriod.Vehicle.LastMovementTypeId));
}
var remData = from rem in allRemarks
//join l in DataContext.LOCATIONs on rem.VehicleNonRevPeriodEntry.VehicleNonRevPeriod.Vehicle.LastLocationCode equals l.location1
where rem.VehicleNonRevPeriodEntry.VehicleNonRevPeriod.Active &&
(rem.VehicleNonRevPeriodEntry.VehicleNonRevPeriod.Vehicle.DaysSinceLastRevenueMovement >= minDays) &&
rem.ExpectedResolutionDate.Date >= DateTime.Now.Date
group rem by rem.VehicleNonRevPeriodEntry.VehicleNonRevPeriod.VehicleId
into g
join rem2 in DataContext.VehicleNonRevPeriodEntryRemarks
on g.Max(d => d.VehicleNonRevPeriodEntryRemarkId) equals rem2.VehicleNonRevPeriodEntryRemarkId
select new VehicleRemarkIdHolder
{
VehicleId = g.Key,
ReasonId = rem2.RemarkId
};
switch (comparisonType)
{
case DictionaryParameter.LocationCountry:
groupedRemData = from rd in remData
join v in vehicles on rd.VehicleId equals v.VehicleId
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
group rd by l.COUNTRy1.country_description
into gd
select gd;
break;
case DictionaryParameter.Pool:
groupedRemData = from rd in remData
join v in vehicles on rd.VehicleId equals v.VehicleId
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
group rd by l.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1
into gd
select gd;
break;
case DictionaryParameter.LocationGroup:
groupedRemData = from rd in remData
join v in vehicles on rd.VehicleId equals v.VehicleId
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
group rd by l.CMS_LOCATION_GROUP.cms_location_group1
into gd
select gd;
break;
case DictionaryParameter.Area:
groupedRemData = from rd in remData
join v in vehicles on rd.VehicleId equals v.VehicleId
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
group rd by l.OPS_AREA.ops_area1
into gd
select gd;
break;
case DictionaryParameter.Region:
groupedRemData = from rd in remData
join v in vehicles on rd.VehicleId equals v.VehicleId
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
group rd by l.OPS_AREA.OPS_REGION.ops_region1
into gd
select gd;
break;
case DictionaryParameter.Location:
groupedRemData = from rd in remData
join v in vehicles on rd.VehicleId equals v.VehicleId
group rd by v.LastLocationCode
into gd
select gd;
break;
case DictionaryParameter.OwningCountry:
groupedRemData = from rd in remData
join v in vehicles on rd.VehicleId equals v.VehicleId
join cg in DataContext.CAR_GROUPs on
new { carG = v.CarGroup, country = v.OwningCountry }
equals new { carG = cg.car_group1, country = cg.CAR_CLASS.CAR_SEGMENT.country }
group rd by cg.CAR_CLASS.CAR_SEGMENT.COUNTRy1.country_description
into gd
select gd;
break;
case DictionaryParameter.CarSegment:
groupedRemData = from rd in remData
join v in vehicles on rd.VehicleId equals v.VehicleId
join cg in DataContext.CAR_GROUPs on
new { carG = v.CarGroup, country = v.OwningCountry }
equals new { carG = cg.car_group1, country = cg.CAR_CLASS.CAR_SEGMENT.country }
group rd by cg.CAR_CLASS.CAR_SEGMENT.car_segment1
into gd
select gd;
break;
case DictionaryParameter.CarClass:
groupedRemData = from rd in remData
join v in vehicles on rd.VehicleId equals v.VehicleId
join cg in DataContext.CAR_GROUPs on
new { carG = v.CarGroup, country = v.OwningCountry }
equals new { carG = cg.car_group1, country = cg.CAR_CLASS.CAR_SEGMENT.country }
group rd by cg.CAR_CLASS.car_class1
into gd
select gd;
break;
case DictionaryParameter.CarGroup:
groupedRemData = from rd in remData
join v in vehicles on rd.VehicleId equals v.VehicleId
group rd by v.CarGroup
into gd
select gd;
break;
case DictionaryParameter.OperationalStatusGrouping:
groupedRemData = from rd in remData
join v in vehicles on rd.VehicleId equals v.VehicleId
group rd by v.Operational_Status.OperationalStatusCode
into gd
select gd;
break;
case DictionaryParameter.KciGrouping:
groupedRemData = from rd in remData
join v in vehicles on rd.VehicleId equals v.VehicleId
group rd by v.Operational_Status.KCICode
into gd
select gd;
break;
default:
return(null);
}
var groupedData = GetGroupedVehicleQueryable(comparisonType);
var compData = from gd in groupedData
select new ComparisonRow
{
Key = gd.Key,
FleetCount = gd.Count(),
NonRevCount = gd.Sum(d => d.IsNonRev &&
d.DaysSinceLastRevenueMovement >= minDays &&
(selectedMovementTypes == null || selectedMovementTypes.Contains(d.LastMovementTypeId)) &&
(selectedOperStats == null || selectedOperStats.Contains(d.LastOperationalStatusId))
? 1 : 0),
};
var reasonRows = from rd in groupedRemData
select new ComparisonRow
{
Key = rd.Key,
ReasonsEntered = rd.Sum(d => d.ReasonId > 0 ? 1 : 0)
};
var returned = compData.ToList();
foreach (var cr in returned)
{
var keyToMatch = cr.Key;
var reasonRow = reasonRows.FirstOrDefault(d => d.Key == keyToMatch);
if (reasonRow == null)
{
continue;
}
cr.ReasonsEntered = reasonRow.ReasonsEntered;
}
return(returned);
}
public IQueryable <IGrouping <string, VehicleHistory> > GetGroupedVehicleHistoryQuery(DictionaryParameter comparisonType)
{
var vehicleHistories = BaseVehicleDataAccess.GetVehicleHistoryQueryable(Parameters, DataContext, true, true);
if (vehicleHistories == null)
{
return(null);
}
if (Parameters.ContainsKey(DictionaryParameter.OperationalStatuses))
{
if (Parameters[DictionaryParameter.OperationalStatuses] == string.Empty)
{
return(null);
}
var selectedOperStats = Parameters[DictionaryParameter.OperationalStatuses].Split(',').Select(int.Parse);
vehicleHistories = vehicleHistories.Where(d => selectedOperStats.Contains(d.OperationalStatusId));
}
if (Parameters.ContainsKey(DictionaryParameter.MovementTypes))
{
if (Parameters[DictionaryParameter.MovementTypes] == string.Empty)
{
return(null);
}
var selectedMovementTypes = Parameters[DictionaryParameter.MovementTypes].Split(',').Select(int.Parse);
vehicleHistories = vehicleHistories.Where(d => selectedMovementTypes.Contains(d.MovementTypeId));
}
var startDate = Parameters.GetDateFromDictionary(DictionaryParameter.StartDate);
vehicleHistories = from hd in vehicleHistories
where hd.TimeStamp == startDate
select hd;
IQueryable <IGrouping <string, VehicleHistory> > groupedData;
switch (comparisonType)
{
case DictionaryParameter.LocationCountry:
groupedData = from v in vehicleHistories
join l in DataContext.LOCATIONs on v.LocationCode equals l.location1
group v by l.COUNTRy1.country_description
into gd
select gd;
break;
case DictionaryParameter.Pool:
groupedData = from v in vehicleHistories
join l in DataContext.LOCATIONs on v.LocationCode equals l.location1
group v by l.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1
into gd
select gd;
break;
case DictionaryParameter.LocationGroup:
groupedData = from v in vehicleHistories
join l in DataContext.LOCATIONs on v.LocationCode equals l.location1
group v by l.CMS_LOCATION_GROUP.cms_location_group1
into gd
select gd;
break;
case DictionaryParameter.Area:
groupedData = from v in vehicleHistories
join l in DataContext.LOCATIONs on v.LocationCode equals l.location1
group v by l.OPS_AREA.ops_area1
into gd
select gd;
break;
case DictionaryParameter.Region:
groupedData = from v in vehicleHistories
join l in DataContext.LOCATIONs on v.LocationCode equals l.location1
group v by l.OPS_AREA.OPS_REGION.ops_region1
into gd
select gd;
break;
case DictionaryParameter.Location:
groupedData = from v in vehicleHistories
join l in DataContext.LOCATIONs on v.LocationCode equals l.location1
group v by l.location1
into gd
select gd;
break;
case DictionaryParameter.OwningCountry:
groupedData = from v in vehicleHistories
join cg in DataContext.CAR_GROUPs on
new { carG = v.Vehicle.CarGroup, country = v.Vehicle.OwningCountry }
equals new { carG = cg.car_group1, country = cg.CAR_CLASS.CAR_SEGMENT.country }
group v by cg.CAR_CLASS.CAR_SEGMENT.COUNTRy1.country_description
into gd
select gd;
break;
case DictionaryParameter.CarSegment:
groupedData = from v in vehicleHistories
join cg in DataContext.CAR_GROUPs on
new { carG = v.Vehicle.CarGroup, country = v.Vehicle.OwningCountry }
equals new { carG = cg.car_group1, country = cg.CAR_CLASS.CAR_SEGMENT.country }
group v by cg.CAR_CLASS.CAR_SEGMENT.car_segment1
into gd
select gd;
break;
case DictionaryParameter.CarClass:
groupedData = from v in vehicleHistories
join cg in DataContext.CAR_GROUPs on
new { carG = v.Vehicle.CarGroup, country = v.Vehicle.OwningCountry }
equals new { carG = cg.car_group1, country = cg.CAR_CLASS.CAR_SEGMENT.country }
group v by cg.CAR_CLASS.car_class1
into gd
select gd;
break;
case DictionaryParameter.CarGroup:
groupedData = from v in vehicleHistories
group v by v.Vehicle.CarGroup
into gd
select gd;
break;
case DictionaryParameter.OperationalStatusGrouping:
groupedData = from v in vehicleHistories
group v by v.Operational_Status.OperationalStatusCode
into gd
select gd;
break;
case DictionaryParameter.KciGrouping:
groupedData = from v in vehicleHistories
group v by v.Operational_Status.KCICode
into gd
select gd;
break;
default:
return(null);
}
return(groupedData);
}
protected ComparisonRow BuildUnmappedVehicleRow(bool siteComparison)
{
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(Parameters, DataContext, true, true);
if (vehicles == null)
{
return(new ComparisonRow());
}
IQueryable <int> matchedSet;
if (siteComparison)
{
var locationCountry = Parameters[DictionaryParameter.LocationCountry];
if (!string.IsNullOrEmpty(locationCountry))
{
return(null);
}
matchedSet = from v in vehicles
join loc in DataContext.LOCATIONs.Select(d => d.location1).Distinct()
on v.LastLocationCode equals loc
select v.VehicleId;
}
else
{
var owningCountry = Parameters[DictionaryParameter.OwningCountry];
if (!string.IsNullOrEmpty(owningCountry))
{
return(null);
}
var carGroups = (from cg in DataContext.CAR_GROUPs
select new { cg = cg.car_group1, cg.CAR_CLASS.CAR_SEGMENT.country }).Distinct();
matchedSet = from v in vehicles
join cg in carGroups on new { cg = v.CarGroup, country = v.OwningCountry }
equals new { cg.cg, cg.country }
select v.VehicleId;
}
var unmappedCars = from vh in vehicles.Where(d => !matchedSet.Any(m => m == d.VehicleId))
select vh;
if (!unmappedCars.Any())
{
return(null);
}
var remData = from rem in DataContext.VehicleNonRevPeriodEntryRemarks
join l in DataContext.LOCATIONs on
rem.VehicleNonRevPeriodEntry.VehicleNonRevPeriod.Vehicle.LastLocationCode equals l.location1
where unmappedCars.Any(d => d.VehicleId == rem.VehicleNonRevPeriodEntry.VehicleNonRevPeriod.Vehicle.VehicleId)
where rem.VehicleNonRevPeriodEntry.VehicleNonRevPeriod.Active
group rem by rem.VehicleNonRevPeriodEntry.VehicleNonRevPeriod.VehicleId
into g
join rem2 in DataContext.VehicleNonRevPeriodEntryRemarks
on g.Max(d => d.VehicleNonRevPeriodEntryRemarkId) equals rem2.VehicleNonRevPeriodEntryRemarkId
select g.Key;
var totalRow = new ComparisonRow
{
Key = NotFoundKeyName,
FleetCount = unmappedCars.Count(),
NonRevCount = unmappedCars.Sum(d => d.IsNonRev ? 1 : 0),
ReasonsEntered = remData.Count(),
};
return(totalRow);
}
private List <AgeingRow> GetCurrentAgeRowData(DictionaryParameter comparisonType)
{
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(Parameters, DataContext, true, true);
if (vehicles == null)
{
return(new List <AgeingRow>());
}
IQueryable <IGrouping <string, Vehicle> > groupedData;
switch (comparisonType)
{
case DictionaryParameter.OperationalStatusGrouping:
groupedData = from v in vehicles
group v by v.Operational_Status.OperationalStatusCode
into gd
select gd;
break;
case DictionaryParameter.KciGrouping:
groupedData = from v in vehicles
group v by v.Operational_Status.KCICode
into gd
select gd;
break;
default:
return(null);
}
IEnumerable <int> selectedOperStats = null;
if (Parameters.ContainsKey(DictionaryParameter.OperationalStatuses))
{
if (Parameters[DictionaryParameter.OperationalStatuses] == string.Empty)
{
return(null);
}
selectedOperStats = Parameters[DictionaryParameter.OperationalStatuses].Split(',').Select(int.Parse);
}
IEnumerable <int> selectedMovementTypes = null;
if (Parameters.ContainsKey(DictionaryParameter.MovementTypes))
{
if (Parameters[DictionaryParameter.MovementTypes] == string.Empty)
{
return(null);
}
selectedMovementTypes = Parameters[DictionaryParameter.MovementTypes].Split(',').Select(int.Parse);
}
var compData = from gd in groupedData
select new AgeingRow
{
Key = gd.Key,
FleetCount = gd.Count(),
NonRevCount = gd.Sum(d => d.IsNonRev &&
(selectedOperStats == null || selectedOperStats.Contains(d.LastOperationalStatusId)) &&
(selectedMovementTypes == null || selectedMovementTypes.Contains(d.LastMovementTypeId))
? 1 : 0),
Ages = gd.Where(d =>
d.IsNonRev &&
(selectedOperStats == null || selectedOperStats.Contains(d.LastOperationalStatusId)) &&
(selectedMovementTypes == null || selectedMovementTypes.Contains(d.LastMovementTypeId))
).Select(d => d.DaysSinceLastRevenueMovement).ToList()
};
var returned = compData.ToList();
return(returned);
}
public IQueryable <IGrouping <string, Vehicle> > GetSingleKeyGroupedVehicle(DictionaryParameter comparisonType)
{
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(Parameters, DataContext, true, true);
IQueryable <IGrouping <string, Vehicle> > groupedData;
switch (comparisonType)
{
case DictionaryParameter.LocationCountry:
groupedData = from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
group v by l.COUNTRy1.country_description
into gd
select gd;
break;
case DictionaryParameter.Pool:
groupedData = from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
group v by l.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1
into gd
select gd;
break;
case DictionaryParameter.LocationGroup:
groupedData = from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
group v by l.CMS_LOCATION_GROUP.cms_location_group1
into gd
select gd;
break;
case DictionaryParameter.Area:
groupedData = from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
group v by l.OPS_AREA.ops_area1
into gd
select gd;
break;
case DictionaryParameter.Region:
groupedData = from v in vehicles
join l in DataContext.LOCATIONs on v.LastLocationCode equals l.location1
group v by l.OPS_AREA.OPS_REGION.ops_region1
into gd
select gd;
break;
case DictionaryParameter.Location:
groupedData = from v in vehicles
group v by v.LastLocationCode
into gd
select gd;
break;
case DictionaryParameter.OwningCountry:
groupedData = from v in vehicles
join cg in DataContext.CAR_GROUPs on new { v.CarGroup, v.OwningCountry } equals
new { CarGroup = cg.car_group1, OwningCountry = cg.CAR_CLASS.CAR_SEGMENT.country }
group v by cg.CAR_CLASS.CAR_SEGMENT.COUNTRy1.country_description
into gd
select gd;
break;
case DictionaryParameter.CarSegment:
groupedData = from v in vehicles
join cg in DataContext.CAR_GROUPs on new { v.CarGroup, v.OwningCountry } equals
new { CarGroup = cg.car_group1, OwningCountry = cg.CAR_CLASS.CAR_SEGMENT.country }
group v by cg.CAR_CLASS.CAR_SEGMENT.car_segment1
into gd
select gd;
break;
case DictionaryParameter.CarClass:
groupedData = from v in vehicles
join cg in DataContext.CAR_GROUPs on new { v.CarGroup, v.OwningCountry } equals
new { CarGroup = cg.car_group1, OwningCountry = cg.CAR_CLASS.CAR_SEGMENT.country }
group v by cg.CAR_CLASS.car_class1
into gd
select gd;
break;
case DictionaryParameter.CarGroup:
groupedData = from v in vehicles
group v by v.CarGroup
into gd
select gd;
break;
default:
return(null);
}
return(groupedData);
}
public List <HistoricalTrendRow> GetHistoricAgeRowData()
{
var vehicleHistories = BaseVehicleDataAccess.GetVehicleHistoryQueryable(Parameters, DataContext, true, true);
if (vehicleHistories == null)
{
return(new List <HistoricalTrendRow>());
}
var startDate = Parameters.GetDateFromDictionary(DictionaryParameter.StartDate);
var endDate = Parameters.GetDateFromDictionary(DictionaryParameter.EndDate);
var daysInCountry = 0;
if (Parameters.ContainsValueAndIsntEmpty(DictionaryParameter.MinDaysInCountry))
{
daysInCountry = int.Parse(Parameters[DictionaryParameter.MinDaysInCountry]);
}
vehicleHistories = from vh in vehicleHistories
where vh.TimeStamp >= startDate &&
vh.TimeStamp <= endDate &&
vh.IsFleet &&
vh.DaysInCountry >= daysInCountry
select vh;
vehicleHistories = VehicleFieldRestrictions.RestrictVehicleHistoryByIdleInForeignCountry(vehicleHistories);
var comparisonType = ComparisonLevelLookup.GetSiteComparisonTypeFromParameters(Parameters);
IQueryable <HistoricalTrendRow> historicalTrendData = null;
switch (comparisonType)
{
case DictionaryParameter.LocationCountry:
historicalTrendData = from v in vehicleHistories
group v by new { v.TimeStamp.Date, v.LOCATION.COUNTRy1.country_description }
into gd
select new HistoricalTrendRow
{
ColumnCode = gd.Key.country_description,
CodeCount = gd.Count(),
Date = gd.Key.Date
};
break;
case DictionaryParameter.Pool:
historicalTrendData = from v in vehicleHistories
group v by new { v.TimeStamp.Date, v.LOCATION.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1 }
into gd
select new HistoricalTrendRow
{
ColumnCode = gd.Key.cms_pool1,
CodeCount = gd.Count(),
Date = gd.Key.Date
};
break;
case DictionaryParameter.LocationGroup:
historicalTrendData = from v in vehicleHistories
group v by new { v.TimeStamp.Date, v.LOCATION.CMS_LOCATION_GROUP.cms_location_group1 }
into gd
select new HistoricalTrendRow
{
ColumnCode = gd.Key.cms_location_group1,
CodeCount = gd.Count(),
Date = gd.Key.Date
};
break;
case DictionaryParameter.Area:
historicalTrendData = from v in vehicleHistories
group v by new { v.TimeStamp.Date, v.LOCATION.OPS_AREA.ops_area1 }
into gd
select new HistoricalTrendRow
{
ColumnCode = gd.Key.ops_area1,
CodeCount = gd.Count(),
Date = gd.Key.Date
};
break;
case DictionaryParameter.Region:
historicalTrendData = from v in vehicleHistories
group v by new { v.TimeStamp.Date, v.LOCATION.OPS_AREA.OPS_REGION.ops_region1 }
into gd
select new HistoricalTrendRow
{
ColumnCode = gd.Key.ops_region1,
CodeCount = gd.Count(),
Date = gd.Key.Date
};
break;
case DictionaryParameter.Location:
historicalTrendData = from v in vehicleHistories
group v by new { v.TimeStamp.Date, v.LOCATION.location1 }
into gd
select new HistoricalTrendRow
{
ColumnCode = gd.Key.location1,
CodeCount = gd.Count(),
Date = gd.Key.Date
};
break;
}
if (historicalTrendData == null)
{
return(new List <HistoricalTrendRow>());
}
var returned = historicalTrendData.ToList();
return(returned);
}
/// <summary>
/// Special version of the Extract Columns method in the base class. Groups by StringDateGrouping rather than just date
/// </summary>
private IQueryable <FleetStatusRow> ExtractFleetHistoryColumns(AvailabilityGrouping columnRequested, IQueryable <IGrouping <StringDateGrouping, FleetHistory> > groupedQueryable)
{
var divisorType = BaseVehicleDataAccess.GetPercentageDivisorTypeFromParameters(Parameters);
IQueryable <FleetStatusRow> fleetDayKeyGrouping;
switch (columnRequested)
{
case AvailabilityGrouping.Min:
fleetDayKeyGrouping = from gq in groupedQueryable
select new FleetStatusRow(divisorType, true)
{
Key = gq.Key.Key,
Day = gq.Key.Date,
TotalFleet = gq.Sum(d => d.MinTotal),
Bd = gq.Sum(d => d.MinBd),
Cu = gq.Sum(d => d.MinCu),
Fs = gq.Sum(d => d.MinFs),
Ha = gq.Sum(d => d.MinHa),
Hl = gq.Sum(d => d.MinHl),
Ll = gq.Sum(d => d.MinLl),
Mm = gq.Sum(d => d.MinMm),
Nc = gq.Sum(d => d.MinNc),
Pl = gq.Sum(d => d.MinPl),
Rl = gq.Sum(d => d.MinRl),
Rp = gq.Sum(d => d.MinRp),
Idle = gq.Sum(d => d.MinIdle),
Su = gq.Sum(d => d.MinSu),
Sv = gq.Sum(d => d.MinSv),
Tb = gq.Sum(d => d.MinTb),
Tc = gq.Sum(d => d.MinTc),
Tn = gq.Sum(d => d.MinTn),
Tw = gq.Sum(d => d.MinTw),
Ws = gq.Sum(d => d.MinWs),
Overdue = gq.Sum(d => d.MinOverdue),
OperationalFleetDatabaseValue = gq.Sum(d => d.MinOperationalFleet),
AvailableFleetDatabaseValue = gq.Sum(d => d.MinAvailableFleet),
OnRentDatabaseValue = gq.Sum(d => d.MinOnRent)
};
break;
case AvailabilityGrouping.Max:
fleetDayKeyGrouping = from gq in groupedQueryable
select new FleetStatusRow(divisorType, true)
{
Key = gq.Key.Key,
Day = gq.Key.Date,
TotalFleet = gq.Sum(d => d.MaxTotal),
Bd = gq.Sum(d => d.MaxBd),
Cu = gq.Sum(d => d.MaxCu),
Fs = gq.Sum(d => d.MaxFs),
Ha = gq.Sum(d => d.MaxHa),
Hl = gq.Sum(d => d.MaxHl),
Ll = gq.Sum(d => d.MaxLl),
Mm = gq.Sum(d => d.MaxMm),
Nc = gq.Sum(d => d.MaxNc),
Pl = gq.Sum(d => d.MaxPl),
Rl = gq.Sum(d => d.MaxRl),
Rp = gq.Sum(d => d.MaxRp),
Idle = gq.Sum(d => d.MaxIdle),
Su = gq.Sum(d => d.MaxSu),
Sv = gq.Sum(d => d.MaxSv),
Tb = gq.Sum(d => d.MaxTb),
Tc = gq.Sum(d => d.MaxTc),
Tn = gq.Sum(d => d.MaxTn),
Tw = gq.Sum(d => d.MaxTw),
Ws = gq.Sum(d => d.MaxWs),
Overdue = gq.Sum(d => d.MaxOverdue),
OperationalFleetDatabaseValue = gq.Sum(d => d.MaxOperationalFleet),
AvailableFleetDatabaseValue = gq.Sum(d => d.MaxAvailableFleet),
OnRentDatabaseValue = gq.Sum(d => d.MaxOnRent)
};
break;
case AvailabilityGrouping.Average:
fleetDayKeyGrouping = from gq in groupedQueryable
select new FleetStatusRow(divisorType, true)
{
Key = gq.Key.Key,
Day = gq.Key.Date,
TotalFleet = gq.Sum(d => d.AvgTotal),
Bd = gq.Sum(d => d.AvgBd),
Cu = gq.Sum(d => d.AvgCu),
Fs = gq.Sum(d => d.AvgFs),
Ha = gq.Sum(d => d.AvgHa),
Hl = gq.Sum(d => d.AvgHl),
Ll = gq.Sum(d => d.AvgLl),
Mm = gq.Sum(d => d.AvgMm),
Nc = gq.Sum(d => d.AvgNc),
Pl = gq.Sum(d => d.AvgPl),
Rl = gq.Sum(d => d.AvgRl),
Rp = gq.Sum(d => d.AvgRp),
Idle = gq.Sum(d => d.AvgIdle),
Su = gq.Sum(d => d.AvgSu),
Sv = gq.Sum(d => d.AvgSv),
Tb = gq.Sum(d => d.AvgTb),
Tc = gq.Sum(d => d.AvgTc),
Tn = gq.Sum(d => d.AvgTn),
Tw = gq.Sum(d => d.AvgTw),
Ws = gq.Sum(d => d.AvgWs),
Overdue = gq.Sum(d => d.AvgOverdue),
OperationalFleetDatabaseValue = gq.Sum(d => d.AvgOperationalFleet),
AvailableFleetDatabaseValue = gq.Sum(d => d.AvgAvailableFleet),
OnRentDatabaseValue = gq.Sum(d => d.AvgOnRent)
};
break;
case AvailabilityGrouping.Peak:
fleetDayKeyGrouping = from gq in groupedQueryable
select new FleetStatusRow(divisorType, true)
{
Key = gq.Key.Key,
Day = gq.Key.Date,
TotalFleet = gq.Sum(d => d.PeakTotal),
Bd = gq.Sum(d => d.PeakBd),
Cu = gq.Sum(d => d.PeakCu),
Fs = gq.Sum(d => d.PeakFs),
Ha = gq.Sum(d => d.PeakHa),
Hl = gq.Sum(d => d.PeakHl),
Ll = gq.Sum(d => d.PeakLl),
Mm = gq.Sum(d => d.PeakMm),
Nc = gq.Sum(d => d.PeakNc),
Pl = gq.Sum(d => d.PeakPl),
Rl = gq.Sum(d => d.PeakRl),
Rp = gq.Sum(d => d.PeakRp),
Idle = gq.Sum(d => d.PeakIdle),
Su = gq.Sum(d => d.PeakSu),
Sv = gq.Sum(d => d.PeakSv),
Tb = gq.Sum(d => d.PeakTb),
Tc = gq.Sum(d => d.PeakTc),
Tn = gq.Sum(d => d.PeakTn),
Tw = gq.Sum(d => d.PeakTw),
Ws = gq.Sum(d => d.PeakWs),
Overdue = gq.Sum(d => d.PeakOverdue),
OperationalFleetDatabaseValue = gq.Sum(d => d.PeakOperationalFleet),
AvailableFleetDatabaseValue = gq.Sum(d => d.PeakAvailableFleet),
OnRentDatabaseValue = gq.Sum(d => d.PeakOnRent)
};
break;
case AvailabilityGrouping.Trough:
fleetDayKeyGrouping = from gq in groupedQueryable
select new FleetStatusRow(divisorType, true)
{
Key = gq.Key.Key,
Day = gq.Key.Date,
TotalFleet = gq.Sum(d => d.TroughTotal),
Bd = gq.Sum(d => d.TroughBd),
Cu = gq.Sum(d => d.TroughCu),
Fs = gq.Sum(d => d.TroughFs),
Ha = gq.Sum(d => d.TroughHa),
Hl = gq.Sum(d => d.TroughHl),
Ll = gq.Sum(d => d.TroughLl),
Mm = gq.Sum(d => d.TroughMm),
Nc = gq.Sum(d => d.TroughNc),
Pl = gq.Sum(d => d.TroughPl),
Rl = gq.Sum(d => d.TroughRl),
Rp = gq.Sum(d => d.TroughRp),
Idle = gq.Sum(d => d.TroughIdle),
Su = gq.Sum(d => d.TroughSu),
Sv = gq.Sum(d => d.TroughSv),
Tb = gq.Sum(d => d.TroughTb),
Tc = gq.Sum(d => d.TroughTc),
Tn = gq.Sum(d => d.TroughTn),
Tw = gq.Sum(d => d.TroughTw),
Ws = gq.Sum(d => d.TroughWs),
Overdue = gq.Sum(d => d.TroughOverdue),
OperationalFleetDatabaseValue = gq.Sum(d => d.TroughOperationalFleet),
AvailableFleetDatabaseValue = gq.Sum(d => d.TroughAvailableFleet),
OnRentDatabaseValue = gq.Sum(d => d.TroughOnRent)
};
break;
default:
throw new ArgumentOutOfRangeException();
}
return(fleetDayKeyGrouping);
}
public List <OverviewGridRow> GetVehicles()
{
bool includeRev = true;
if (Parameters.ContainsKey(DictionaryParameter.NonRevOnly) &&
!string.IsNullOrEmpty(Parameters[DictionaryParameter.NonRevOnly]))
{
includeRev = Parameters[DictionaryParameter.NonRevOnly] == false.ToString();
}
var vehicleData = BaseVehicleDataAccess.GetVehicleQueryable(Parameters, DataContext, includeRev, true);
if (vehicleData == null)
{
return(new List <OverviewGridRow>());
}
var today = DateTime.Now.Date;
vehicleData = BaseVehicleDataAccess.RestrictByAdditionalParameters(Parameters, DataContext, vehicleData);
var overViewDataGrid = from v in vehicleData
orderby v.DaysSinceLastRevenueMovement descending, v.LastLocationCode, v.DepreciationStatus, v.CarGroup
select new OverviewGridRow
{
VehicleId = v.VehicleId,
LastLocationCode = v.LastLocationCode,
CarGroup = v.CarGroup,
LicensePlate = v.LicensePlate,
Serial = v.Vin,
HoldFlag1 = v.HoldFlag1,
InCountryDays = v.DaysInCountry.HasValue ? v.DaysInCountry.Value : 0,
DepreciationStatus = v.DepreciationStatus,
LastChangeDateTime = v.LastChangeDateTime,
LastDriverName = v.LastDriverName,
DocumentNumber = v.LastDocumentNumber,
CarGroupCharged = v.CarGroupCharged,
LastMilage = v.LastMilage ?? 0,
ModelDescription = v.ModelDescription,
ModelCode = v.TasModelCode,
BdDays = v.DaysInBd ?? 0,
MmDays = v.DaysInMm ?? 0,
InstallationDateTime = v.InstallationDate,
InstallationMsoDateTime = v.InstallationMsoDate,
BlockDateTime = v.BlockDate,
MovementTypeCode = v.Movement_Type.MovementTypeCode,
NextRent = null,
OwningArea = v.OwningArea,
OwningCountry = v.OwningCountry,
NonRevDays = v.DaysSinceLastRevenueMovement ?? 0,
NextRentColor = v.IsNonRev ? "~/App.Images/exclamation.png" : string.Empty,
LastUpdate = v.VehicleNonRevPeriods.First(d => d.Active).VehicleNonRevPeriodEntries.Max(d => d.LastChangeDateTime),
LastPeriodEntryId = v.IsNonRev ? v.VehicleNonRevPeriods.First(d => d.Active).VehicleNonRevPeriodEntries.Max(d => d.VehicleNonRevPeriodEntryId)
: (int?)null,
OperationalStatusCode = v.Operational_Status.OperationalStatusCode,
Remark = string.Empty,
RemarkCode = string.Empty,
UnitNumber = v.UnitNumber.ToString(),
ExpectedDateTime = v.ExpectedDateTime,
ExpectedLocationCode = v.ExpectedLocationCode ?? string.Empty,
Comment = v.Comment,
CommentShort = v.Comment.Substring(0, v.Comment.Length < 20 ? v.Comment.Length : 20)
};
var remData = from rem in DataContext.VehicleNonRevPeriodEntryRemarks
where rem.VehicleNonRevPeriodEntry.VehicleNonRevPeriod.Active
group rem by rem.VehicleNonRevPeriodEntry.VehicleNonRevPeriod.VehicleId
into g
join rem2 in DataContext.VehicleNonRevPeriodEntryRemarks
on g.Max(d => d.VehicleNonRevPeriodEntryRemarkId) equals rem2.VehicleNonRevPeriodEntryRemarkId
select new
{
VehicleId = g.Key,
RemarkText = rem2.Remark.Substring(0, 20),
RemarkTextFull = rem2.Remark,
EstimatedResolution = rem2.ExpectedResolutionDate,
Reason = rem2.NonRev_Remarks_List.RemarkText
};
var localVehicleData = overViewDataGrid.ToList();
var remarksData = remData.ToList();
foreach (var v in localVehicleData)
{
int vehicleId = v.VehicleId;
var remarkEntity = remarksData.FirstOrDefault(d => d.VehicleId == vehicleId);
if (remarkEntity == null)
{
v.LastRemark = string.Empty;
v.LastReason = string.Empty;
continue;
}
v.LastRemark = remarkEntity.RemarkText ?? string.Empty;
v.LastRemarkFull = remarkEntity.RemarkTextFull ?? string.Empty;
v.LastReason = remarkEntity.Reason ?? string.Empty;
v.LastRemarkFull = remarkEntity.RemarkTextFull ?? string.Empty;
v.EstimatedResultion = remarkEntity.EstimatedResolution;
if (remarkEntity.EstimatedResolution.Date == today)
{
v.NextRentColor = "~/App.Images/warning.png";
}
if (remarkEntity.EstimatedResolution.Date > today)
{
v.NextRentColor = "~/App.Images/accept.png";
}
}
return(localVehicleData);
}
protected FleetStatusRow GroupToSingle(IQueryable <FleetStatusRow> fleetDayKeyGrouping)
{
var divisorType = BaseVehicleDataAccess.GetPercentageDivisorTypeFromParameters(Parameters);
var availabilityDayGrouping =
BaseVehicleDataAccess.GetAvailabilityDayGroupingFromParameters(Parameters);
var ld = fleetDayKeyGrouping.ToList();
if (ld.Count == 0)
{
return(new FleetStatusRow(divisorType, true));
}
var fsr = new FleetStatusRow(divisorType, true)
{
TotalFleet = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.TotalFleet)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.TotalFleet)
: ld.Min(d => d.TotalFleet),
Bd = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Bd)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Bd)
: ld.Min(d => d.Bd),
Cu = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Cu)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Cu)
: ld.Min(d => d.Cu),
Fs = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Fs)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Fs)
: ld.Min(d => d.Fs),
Ha = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Ha)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Ha)
: ld.Min(d => d.Ha),
Hl = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Hl)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Hl)
: ld.Min(d => d.Hl),
Ll = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Ll)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Ll)
: ld.Min(d => d.Ll),
Mm = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Mm)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Mm)
: ld.Min(d => d.Mm),
Nc = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Nc)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Nc)
: ld.Min(d => d.Nc),
Pl = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Pl)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Pl)
: ld.Min(d => d.Pl),
Rl = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Rl)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Rl)
: ld.Min(d => d.Rl),
Rp = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Rp)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Rp)
: ld.Min(d => d.Rp),
Idle = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Idle)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Idle)
: ld.Min(d => d.Idle),
Su = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Su)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Su)
: ld.Min(d => d.Su),
Sv = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Sv)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Sv)
: ld.Min(d => d.Sv),
Tb = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Tb)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Tb)
: ld.Min(d => d.Tb),
Tc = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Tc)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Tc)
: ld.Min(d => d.Tc),
Tn = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Tn)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Tn)
: ld.Min(d => d.Tn),
Tw = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Tw)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Tw)
: ld.Min(d => d.Tw),
Ws = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Ws)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Ws)
: ld.Min(d => d.Ws),
Overdue = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.Overdue)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.Overdue)
: ld.Min(d => d.Overdue),
OperationalFleetDatabaseValue = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.OperationalFleetDatabaseValue)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.OperationalFleetDatabaseValue)
: ld.Min(d => d.OperationalFleetDatabaseValue),
AvailableFleetDatabaseValue = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.AvailableFleetDatabaseValue)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.AvailableFleetDatabaseValue)
: ld.Min(d => d.AvailableFleetDatabaseValue),
OnRentDatabaseValue = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)ld.Average(d => d.OnRentDatabaseValue)
: availabilityDayGrouping == AvailabilityGrouping.Max ? ld.Max(d => d.OnRentDatabaseValue)
: ld.Min(d => d.OnRentDatabaseValue),
};
var returned = fsr;
return(returned);
}
public string GetFleetMatchExcelExport()
{
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(Parameters, DataContext, true, true);
vehicles = BaseVehicleDataAccess.RestrictByAdditionalParameters(Parameters, DataContext, vehicles);
IQueryable <Reservation> reservations =
DataContext.Reservations1.Select(d => d).Where(d => d.PickupDate > DateTime.Now &&
d.PickupDate <= DateTime.Now.Date.AddDays(DaysForReservationMatchFuture));
vehicles = VehicleFieldRestrictions.RestrictByMatchPredicament(vehicles);
var reservationVehicleCountHolder = from v in vehicles
join r in reservations on new
{
Country = v.OwningCountry,
LocationGroupId = v.LOCATION.cms_location_group_id
}
equals new
{
Country = r.ReturnLocation.country,
LocationGroupId = r.PickupLocation.cms_location_group_id
}
orderby v.VehicleId
select new
{
v.OwningCountry
, v.VehicleId
, CurrentLocation = v.LOCATION.location1
, v.CarGroup
, v.LicensePlate
, v.UnitNumber
, v.ModelDescription
, v.DaysSinceLastRevenueMovement
, DaysUntilCheckout = SqlMethods.DateDiffDay(r.PickupDate.Date, r.ReturnDate.Date)
, CheckoutDate = r.PickupDate.Date
, CarGroupReserved = v.CarGroup
, r.ExternalId
, r.CustomerName
, r.PickupLocation.location1
, Length = SqlMethods.DateDiffDay(DateTime.Now, r.PickupDate.Date)
};
var sb = new StringBuilder();
sb.AppendLine(string.Format("Vehicle:, {0},{1},{2},{3},{4},{5},{6}, Reservation: ,{7},{8},{9},{10},{11},{12},{13}"
, "Owning Country", "Current Location", "Group", "License", "Unit", "Description"
, "Days Non Rev", "Days Until Checkout", "Check Out Date", "Check Out Location", "Group Reserved", "Reservation ID", "Customer"
, "Length"));
//const string emptyVehicleData = " , , , , , ,";
//int previousVehicleId = 0;
foreach (var r in reservationVehicleCountHolder)
{
var vehicleData = string.Format("{0},{1},{2},{3},{4},{5},{6}", r.OwningCountry, r.CurrentLocation,
r.CarGroup, r.LicensePlate
, r.UnitNumber, r.ModelDescription, r.DaysSinceLastRevenueMovement);
var reservationData = string.Format("{0},{1},{2},{3},{4},{5},{6}", r.DaysUntilCheckout,
r.CheckoutDate.ToShortDateString(), r.location1, r.CarGroupReserved, r.ExternalId,
r.CustomerName, r.Length);
sb.AppendLine(string.Format(" ,{0}, ,{1}", vehicleData, reservationData));
//previousVehicleId = r.VehicleId;
}
return(sb.ToString());
}
public List <VehicleMatchGridRow> GetVehicleMatches(int?reservationId = null)
{
var reservations = DataContext.Reservations1.Select(d => d);
reservations = ReservationRestriction.RestrictVehicleQueryable(DataContext, reservations);
IQueryable <Vehicle> vehicles;
if (reservationId == null)
{
reservations = reservations.Where(d =>
d.PickupLocation.country != d.ReturnLocation.country &&
d.PickupDate.Date < DateTime.Now.Date.AddDays(DaysForReservationMatchFuture));
vehicles = BaseVehicleDataAccess.GetVehicleQueryable(Parameters, DataContext, true, true);
vehicles = BaseVehicleDataAccess.RestrictByAdditionalParameters(Parameters, DataContext, vehicles);
}
else
{
vehicles = DataContext.Vehicles.Select(d => d).Where(d => d.IsFleet);
vehicles = VehicleRestriction.RestrictVehicleQueryable(DataContext, vehicles);
reservations = reservations.Where(d => d.ReservationId == reservationId);
}
vehicles = VehicleFieldRestrictions.RestrictByMatchPredicament(vehicles);
var vehicleResCountHolder = from v in vehicles
join r in reservations on new
{
Country = v.OwningCountry,
LocationGroupId = v.LOCATION.cms_location_group_id
}
equals new
{
Country = r.ReturnLocation.country,
LocationGroupId = r.PickupLocation.cms_location_group_id
}
where r.PickupDate > DateTime.Now
group v by v.VehicleId
into groupedReservations
select new { VehicleId = groupedReservations.Key, MatchCount = groupedReservations.Count() };
IQueryable <VehicleMatchGridRow> matchRowEntities;
if (reservationId == null)
{
matchRowEntities = from v in vehicles
join rch in vehicleResCountHolder on v.VehicleId equals rch.VehicleId into rvch
from rch in rvch.DefaultIfEmpty()
select new VehicleMatchGridRow
{
VehicleId = v.VehicleId,
CarGroup = v.CarGroup,
LastLocation = v.LastLocationCode,
LicensePlate = v.LicensePlate,
ModelDescription = v.ModelDescription,
NonRevDays =
v.DaysSinceLastRevenueMovement.HasValue ? v.DaysSinceLastRevenueMovement.Value : 0,
OperationalStatusCode = v.Operational_Status.OperationalStatusCode,
OwningCountry = v.OwningCountry,
UnitNumber = v.UnitNumber.HasValue ? v.UnitNumber.Value : 0,
ReservationsMatched = rch == null ? 0 : rch.MatchCount
};
}
else
{
matchRowEntities = from v in vehicles
join rch in vehicleResCountHolder on v.VehicleId equals rch.VehicleId
select new VehicleMatchGridRow
{
VehicleId = v.VehicleId,
CarGroup = v.CarGroup,
LastLocation = v.LastLocationCode,
LicensePlate = v.LicensePlate,
ModelDescription = v.ModelDescription,
NonRevDays =
v.DaysSinceLastRevenueMovement.HasValue ? v.DaysSinceLastRevenueMovement.Value : 0,
OperationalStatusCode = v.Operational_Status.OperationalStatusCode,
OwningCountry = v.OwningCountry,
UnitNumber = v.UnitNumber.HasValue ? v.UnitNumber.Value : 0,
ReservationsMatched = rch.MatchCount
};
}
var returned = ApplyDefaultOrderToVehicles(matchRowEntities).ToList();
return(returned);
}
protected IEnumerable <FleetStatusRow> GroupByKey(IQueryable <FleetStatusRow> fleetDayKeyGrouping)
{
var divisorType = BaseVehicleDataAccess.GetPercentageDivisorTypeFromParameters(Parameters);
var availabilityDayGrouping =
BaseVehicleDataAccess.GetAvailabilityDayGroupingFromParameters(Parameters);
var fleetKeyGrouping = from fd in fleetDayKeyGrouping
group fd by fd.Key
into g
select new FleetStatusRow(divisorType, false)
{
Key = g.Key,
TotalFleet = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.TotalFleet)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.TotalFleet)
: g.Min(d => d.TotalFleet),
Bd = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Bd)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Bd)
: g.Min(d => d.Bd),
Cu = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Cu)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Cu)
: g.Min(d => d.Cu),
Fs = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Fs)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Fs)
: g.Min(d => d.Fs),
Ha = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Ha)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Ha)
: g.Min(d => d.Ha),
Hl = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Hl)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Hl)
: g.Min(d => d.Hl),
Ll = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Ll)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Ll)
: g.Min(d => d.Ll),
Mm = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Mm)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Mm)
: g.Min(d => d.Mm),
Nc = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Nc)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Nc)
: g.Min(d => d.Nc),
Pl = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Pl)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Pl)
: g.Min(d => d.Pl),
Rl = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Rl)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Rl)
: g.Min(d => d.Rl),
Rp = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Rp)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Rp)
: g.Min(d => d.Rp),
Idle = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Idle)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Idle)
: g.Min(d => d.Idle),
Su = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Su)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Su)
: g.Min(d => d.Su),
Sv = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Sv)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Sv)
: g.Min(d => d.Sv),
Tb = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Tb)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Tb)
: g.Min(d => d.Tb),
Tc = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Tc)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Tc)
: g.Min(d => d.Tc),
Tn = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Tn)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Tn)
: g.Min(d => d.Tn),
Tw = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Tw)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Tw)
: g.Min(d => d.Tw),
Ws = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Ws)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Ws)
: g.Min(d => d.Ws),
Overdue = availabilityDayGrouping == AvailabilityGrouping.Average ? (int)g.Average(d => d.Overdue)
: availabilityDayGrouping == AvailabilityGrouping.Max ? g.Max(d => d.Overdue)
: g.Min(d => d.Overdue),
};
var returned = fleetKeyGrouping;
return(returned);
}
private List <AgeingRow> GetTodayAgeRowData()
{
var vehicles = BaseVehicleDataAccess.GetVehicleQueryable(Parameters, DataContext, true, true);
vehicles = VehicleFieldRestrictions.RestrictByIdleInForeignCountry(vehicles);
var comparisonType = ComparisonLevelLookup.GetSiteComparisonTypeFromParameters(Parameters);
IQueryable <IGrouping <string, Vehicle> > groupedData = null;
switch (comparisonType)
{
case DictionaryParameter.LocationCountry:
groupedData = from v in vehicles
group v by v.LOCATION.COUNTRy1.country_description
into gd
select gd;
break;
case DictionaryParameter.Pool:
groupedData = from v in vehicles
group v by v.LOCATION.CMS_LOCATION_GROUP.CMS_POOL.cms_pool1
into gd
select gd;
break;
case DictionaryParameter.LocationGroup:
groupedData = from v in vehicles
group v by v.LOCATION.CMS_LOCATION_GROUP.cms_location_group1
into gd
select gd;
break;
case DictionaryParameter.Area:
groupedData = from v in vehicles
group v by v.LOCATION.OPS_AREA.ops_area1
into gd
select gd;
break;
case DictionaryParameter.Region:
groupedData = from v in vehicles
group v by v.LOCATION.OPS_AREA.OPS_REGION.ops_region1
into gd
select gd;
break;
case DictionaryParameter.Location:
groupedData = from v in vehicles
group v by v.LOCATION.location1
into gd
select gd;
break;
}
if (groupedData == null)
{
return(null);
}
var compData = from gd in groupedData
select new AgeingRow
{
Key = gd.Key,
FleetCount = gd.Count(),
Ages = gd.Select(d => d.DaysInCountry).ToList()
};
var returned = compData.ToList();
return(returned);
}
