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