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 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);
}
public List <ReservationMatchGridRow> GetReservationMatches(int?vehicleId = null)
{
var vehicles = DataContext.Vehicles.Select(d => d).Where(d => d.IsFleet);
vehicles = VehicleRestriction.RestrictVehicleQueryable(DataContext, vehicles);
IQueryable <Reservation> reservations;
if (vehicleId == null)
{
reservations = RestrictReservation();
vehicles = VehicleFieldRestrictions.RestrictByMatchPredicament(vehicles);
if (Parameters.ContainsValueAndIsntEmpty(DictionaryParameter.CheckOutCountry))
{
var checkOutCountry = Parameters[DictionaryParameter.CheckOutCountry];
vehicles = from v in vehicles
where v.LOCATION.country == checkOutCountry
select v;
}
var localVehicles = vehicles.ToList();
var localReservations = reservations.ToList();
var reservationVehicleCountHolder = from v in localVehicles
join r in localReservations 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 r by r.ReservationId
into groupedVehicles
select new { ReservationId = groupedVehicles.Key, MatchCount = groupedVehicles.Count() };
var reservationGridData = from r in localReservations
join vch in reservationVehicleCountHolder on r.ReservationId equals vch.ReservationId into rvch
from vch in rvch.DefaultIfEmpty()
select new ReservationMatchGridRow
{
ReservationId = r.ReservationId,
ExternalId = r.ExternalId,
PickupDate = r.PickupDate,
PickupLocation = r.PickupLocation.location1,
CarGroup = r.CAR_GROUP.car_group1,
CustomerName = r.CustomerName,
//ReservationDuration = SqlMethods.DateDiffDay(r.PickupDate.Date, r.ReturnDate.Date),
//DaysToPickup = SqlMethods.DateDiffDay(DateTime.Now, r.PickupDate.Date),
ReservationDuration = (int)(r.ReturnDate.Date - r.PickupDate.Date).TotalDays,
DaysToPickup = (int)(r.PickupDate.Date - DateTime.Now.Date).TotalDays,
VehiclesMatched = vch == null ? 0 : vch.MatchCount
};
var returned = from r in reservationGridData
orderby r.DaysToPickup, r.PickupLocation, r.CarGroup
select r;
return(returned.ToList());
}
else
{
reservations = DataContext.Reservations1.Select(d => d).Where(d => d.PickupDate > DateTime.Now &&
d.PickupDate < DateTime.Now.Date.AddDays(DaysForReservationMatchFuture)
);
vehicles = vehicles.Where(d => d.VehicleId == vehicleId);
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
}
group r by r.ReservationId
into groupedVehicles
select new { ReservationId = groupedVehicles.Key, MatchCount = groupedVehicles.Count() };
var reservationGridData = from r in reservations
join vch in reservationVehicleCountHolder on r.ReservationId equals vch.ReservationId
select new ReservationMatchGridRow
{
ReservationId = r.ReservationId,
ExternalId = r.ExternalId,
PickupDate = r.PickupDate,
PickupLocation = r.PickupLocation.location1,
CarGroup = r.CAR_GROUP.car_group1,
CustomerName = r.CustomerName,
ReservationDuration = SqlMethods.DateDiffDay(r.PickupDate.Date, r.ReturnDate.Date),
DaysToPickup = SqlMethods.DateDiffDay(DateTime.Now, r.PickupDate.Date),
VehiclesMatched = vch == null ? 0 : vch.MatchCount
};
var returned = ApplyDefaultOrderToReservations(reservationGridData);
return(returned.ToList());
}
}
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);
}
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);
}
