/// <summary> /// This method is to get trips for the roots searched by date /// </summary> /// <param name="rootId"></param> /// <param name="date"></param> /// <returns></returns> public JsonResult GetTripsForRoot(string rootId, DateTime date) { try { string day = date.ToString("dddd"); List <Calendar> cal = dbContext.Calendar.ToList(); int[] service_ids = cal.Where(c => (int)(c.GetType().GetRuntimeProperty(day.ToLower()).GetValue(c)) == 1).Select(c => c.service_id).ToArray(); if (service_ids.Count() == 0) { return(Json(new { TripList = "Error" }, JsonRequestBehavior.AllowGet)); } List <Trips> trips = (from t in dbContext.Trips from c in dbContext.Calendar where t.route_id.Equals(rootId) && t.service_id.Equals(c.service_id) && service_ids.Contains(c.service_id) select t).ToList(); //dbContext.Trips.Where(t => t.route_id == rootId).ToList(); List <TripsFlattenedModel> tripdata = new List <TripsFlattenedModel>(); foreach (Trips tr in trips) { List <Stop_times> stoptimes = dbContext.StopTimes.Where(t => t.trip_id == tr.trip_id).OrderBy(t => t.stop_sequence).ToList(); Stop_times sourceStopTimesObj = stoptimes.First(); Stop_times destinationStopTimesObj = stoptimes.Last(); tripdata.Add(new TripsFlattenedModel { RouteId = tr.route_id, TripId = tr.trip_id, ShapeId = tr.shape_id, SourceSequence = sourceStopTimesObj.stop_sequence, DestinationSequence = destinationStopTimesObj.stop_sequence, SourceArrivalTime = sourceStopTimesObj.arrival_time, SourceDepartureTime = sourceStopTimesObj.departure_time, DestinationArrivalTime = destinationStopTimesObj.arrival_time, DestinationDepartureTime = destinationStopTimesObj.departure_time }); } return(Json(new { TripList = tripdata.OrderBy(t => Convert.ToDateTime(t.SourceDepartureTime)) }, JsonRequestBehavior.AllowGet)); } catch (Exception ex) { return(Json(new { TripList = "Error" }, JsonRequestBehavior.AllowGet)); } }
/// <summary> /// This method is to get the list of stops between one stop to another. searched by date /// </summary> /// <param name="sourceStop"></param> /// <param name="destinationStop"></param> /// <param name="date"></param> /// <returns></returns> public JsonResult GetListOfTripsFromOneStopToAnotherByDate(string sourceStop, string destinationStop, DateTime date) { try { string day = date.ToString("dddd"); List <Calendar> cal = dbContext.Calendar.ToList(); int[] service_ids = cal.Where(c => (int)(c.GetType().GetRuntimeProperty(day.ToLower()).GetValue(c)) == 1).Select(c => c.service_id).ToArray(); if (service_ids.Count() == 0) { return(Json(new { TripList = "Error" }, JsonRequestBehavior.AllowGet)); } List <Stops> sources = (from s in dbContext.Stops.Where(t => t.stop_name.Equals(sourceStop)) select s).ToList(); List <Stops> destinations = (from s in dbContext.Stops.Where(t => t.stop_name.Equals(destinationStop)) select s).ToList(); List <TripsFlattenedModel> TripsList = new List <TripsFlattenedModel>(); foreach (Stops source in sources) { foreach (Stops destination in destinations) { int sourceId = source.stop_id; int destinationId = destination.stop_id; List <Stop_times> sourceStopTimes = (from st in dbContext.StopTimes.Where(t => t.stop_id == sourceId) select st).ToList(); List <Stop_times> destinationStopTimes = (from st in dbContext.StopTimes.Where(t => t.stop_id == destinationId) select st).ToList(); List <string> commonTripIds = (from st1 in sourceStopTimes from st2 in destinationStopTimes where st1.trip_id.Equals(st2.trip_id) select st1.trip_id).Distinct().ToList(); foreach (string tripId in commonTripIds) { int sourceSequenceNo = (from st in sourceStopTimes.Where(t => t.trip_id.Equals(tripId)) select st).FirstOrDefault().stop_sequence; int destinationSequenceNo = (from st in destinationStopTimes.Where(t => t.trip_id.Equals(tripId)) select st).FirstOrDefault().stop_sequence; //if (sourceSequenceNo >= destinationSequenceNo) //{ // continue; //} //Trips trip = dbContext.Trips.Where(t => t.trip_id.Equals(tripId)).FirstOrDefault(); Trips trip = (from t in dbContext.Trips from c in dbContext.Calendar where t.trip_id.Equals(tripId) && t.service_id.Equals(c.service_id) && service_ids.Contains(c.service_id) select t).FirstOrDefault(); if (trip != null) { Stop_times sourceStopTimesObj = sourceStopTimes.Where(t => t.stop_sequence == sourceSequenceNo && t.trip_id == tripId).FirstOrDefault(); Stop_times destinationStopTimesObj = destinationStopTimes.Where(t => t.stop_sequence == destinationSequenceNo && t.trip_id == tripId).FirstOrDefault(); TripsList.Add(new TripsFlattenedModel { RouteId = trip.route_id, TripId = tripId, ShapeId = trip.shape_id, SourceSequence = sourceSequenceNo, DestinationSequence = destinationSequenceNo, SourceArrivalTime = sourceStopTimesObj.arrival_time, SourceDepartureTime = sourceStopTimesObj.departure_time, DestinationArrivalTime = destinationStopTimesObj.arrival_time, DestinationDepartureTime = destinationStopTimesObj.departure_time }); } } } } return(Json(new { TripList = TripsList.OrderBy(t => Convert.ToDateTime(t.SourceDepartureTime)), shapes = "success" }, JsonRequestBehavior.AllowGet)); } catch (Exception ex) { return(Json(new { TripList = "Error" }, JsonRequestBehavior.AllowGet)); } }
/// <summary> /// This method is to get trips according to stops searched by date /// </summary> /// <param name="stopId"></param> /// <param name="date"></param> /// <returns></returns> public JsonResult GetTripsByStop(int stopId, DateTime date) { try { string day = date.ToString("dddd"); List <Calendar> cal = dbContext.Calendar.ToList(); int[] service_ids = cal.Where(c => (int)(c.GetType().GetRuntimeProperty(day.ToLower()).GetValue(c)) == 1).Select(c => c.service_id).ToArray(); if (service_ids.Count() == 0) { return(Json(new { TripList = "Error" }, JsonRequestBehavior.AllowGet)); } List <TripsFlattenedModel> tripdata = new List <TripsFlattenedModel>(); List <Stop_routes> routedata = (from c in dbContext.StopRoutes.Where(t => t.stop_id == stopId) select c).ToList(); routedata.ForEach(tr => { if (tr != null) { var source = (from s in dbContext.Stops.Where(t => t.stop_name.Equals(tr.source)) select s).FirstOrDefault(); var destination = (from s in dbContext.Stops.Where(t => t.stop_name.Equals(tr.destination)) select s).FirstOrDefault(); int sourceId = source != null ? source.stop_id : 0; int destinationId = destination != null ? destination.stop_id : 0; if (sourceId != 0 && destinationId != 0) { List <Stop_times> sourceStopTimes = dbContext.StopTimes.Where(t => t.stop_id == sourceId).ToList(); List <Stop_times> destinationStopTimes = dbContext.StopTimes.Where(t => t.stop_id == destinationId).ToList(); List <Trips> tripdatatemp = (from t in dbContext.Trips from c in dbContext.Calendar where t.route_id.Equals(tr.route_id) && t.service_id.Equals(c.service_id) && service_ids.Contains(c.service_id) select t).ToList(); //(from c in dbContext.Trips.Where(t => t.route_id == tr.route_id) select c).ToList(); if (sourceStopTimes != null && destinationStopTimes != null && tripdatatemp != null) { tripdatatemp.ForEach(trip => { Stop_times sourceStopTimesObj = sourceStopTimes.FirstOrDefault(t => t.trip_id == trip.trip_id); Stop_times destinationStopTimesObj = destinationStopTimes.FirstOrDefault(t => t.trip_id == trip.trip_id); if (sourceStopTimesObj != null && destinationStopTimesObj != null) { tripdata.Add(new TripsFlattenedModel { RouteId = trip.route_id, TripId = trip.trip_id, ShapeId = trip.shape_id, SourceSequence = sourceStopTimesObj.stop_sequence, DestinationSequence = destinationStopTimesObj.stop_sequence, SourceArrivalTime = sourceStopTimesObj.arrival_time, SourceDepartureTime = sourceStopTimesObj.departure_time, DestinationArrivalTime = destinationStopTimesObj.arrival_time, DestinationDepartureTime = destinationStopTimesObj.departure_time }); } }); } } } }); return(Json(new { TripList = tripdata.OrderBy(t => Convert.ToDateTime(t.SourceDepartureTime)) }, JsonRequestBehavior.AllowGet)); } catch (Exception ex) { return(Json(new { TripList = "Error" }, JsonRequestBehavior.AllowGet)); } }