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 static IQueryable <Vehicle> GetVehicleQueryable(Dictionary <DictionaryParameter, string> parameters, MarsDBDataContext dataContext
, bool includeRev, bool includeNonRev)
{
var vehicles = dataContext.Vehicles.Select(d => d);
vehicles = VehicleRestriction.RestrictVehicleQueryable(dataContext, vehicles);
if (!(includeRev && includeNonRev))
{
if (!includeRev)
{
vehicles = vehicles.Where(d => d.IsNonRev);
}
if (!includeNonRev)
{
vehicles = vehicles.Where(d => !d.IsNonRev);
}
}
var defleetedVehicle = parameters.ContainsKey(DictionaryParameter.DefleetedVehicles)
? parameters[DictionaryParameter.DefleetedVehicles] : string.Empty;
var noReason = parameters.ContainsKey(DictionaryParameter.NoReason)
? parameters[DictionaryParameter.NoReason] : string.Empty;
if (defleetedVehicle == string.Empty)
{
vehicles = vehicles.Where(d => d.IsFleet);
}
if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.CarGroup))
{
vehicles = VehicleFieldRestrictions.RestrictByCarGroup(vehicles,
parameters[DictionaryParameter.CarGroup], dataContext);
}
else if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.CarClass))
{
vehicles = VehicleFieldRestrictions.RestrictByCarClass(vehicles,
parameters[DictionaryParameter.CarClass], dataContext);
}
else if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.CarSegment))
{
vehicles = VehicleFieldRestrictions.RestrictByCarSegment(vehicles,
parameters[DictionaryParameter.CarSegment], dataContext);
}
else if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.OwningCountry))
{
vehicles = VehicleFieldRestrictions.RestrictByOwningCountry(vehicles,
parameters[DictionaryParameter.OwningCountry]);
}
var expectedLocationLogic = parameters.ContainsValueAndIsntEmpty(DictionaryParameter.ExpectedLocationLogic);
bool restrictByLocationCountry = true;
if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.Location))
{
vehicles = VehicleFieldRestrictions.RestrictByLocation(vehicles,
parameters[DictionaryParameter.Location], dataContext, expectedLocationLogic);
restrictByLocationCountry = false;
}
else
{
if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.LocationGroup))
{
vehicles = VehicleFieldRestrictions.RestrictByLocationGroup(vehicles,
parameters[DictionaryParameter.LocationGroup], dataContext, expectedLocationLogic);
restrictByLocationCountry = false;
}
else if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.Pool))
{
vehicles = VehicleFieldRestrictions.RestrictByPool(vehicles,
parameters[DictionaryParameter.Pool], dataContext, expectedLocationLogic);
restrictByLocationCountry = false;
}
if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.Area))
{
vehicles = VehicleFieldRestrictions.RestrictByArea(vehicles,
parameters[DictionaryParameter.Area], dataContext, expectedLocationLogic);
restrictByLocationCountry = false;
}
else if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.Region))
{
vehicles = VehicleFieldRestrictions.RestrictByRegion(vehicles,
parameters[DictionaryParameter.Region], dataContext, expectedLocationLogic);
restrictByLocationCountry = false;
}
}
if (restrictByLocationCountry && parameters.ContainsValueAndIsntEmpty(DictionaryParameter.LocationCountry))
{
vehicles = VehicleFieldRestrictions.RestrictByLocationCountry(vehicles,
parameters[DictionaryParameter.LocationCountry], expectedLocationLogic);
}
if (noReason == "0")
{
var reasonsEntered = from per in dataContext.VehicleNonRevPeriodEntryRemarks
where per.VehicleNonRevPeriodEntry.VehicleNonRevPeriod.Active &&
per.ExpectedResolutionDate >= DateTime.Now.Date //
select per.VehicleNonRevPeriodEntry.VehicleNonRevPeriod.Vehicle;
vehicles = vehicles.Except(reasonsEntered.Distinct());
}
if (parameters.ContainsValueAndIsntEmpty(DictionaryParameter.FleetTypes))
{
var selectedFleetTypes = parameters[DictionaryParameter.FleetTypes].Split(',').Select(byte.Parse);
vehicles = vehicles.Where(d => selectedFleetTypes.Contains(d.VehicleFleetTypeId));
}
if (parameters.ContainsKey(DictionaryParameter.OwningArea))
{
if (parameters[DictionaryParameter.OwningArea] != string.Empty)
{
//var selectedOwningAreas = parameters[DictionaryParameter.OwningArea].Split(',').Select(d => d);
//vehicles = vehicles.Where(d => selectedOwningAreas.Contains(d.OwningArea));
vehicles = vehicles.Where(d => d.OwningArea == parameters[DictionaryParameter.OwningArea]);
}
}
return(vehicles);
}
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());
}
}
