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