public static List <S> GetStopByStopID(int stopID) { MajicBusEntities context = new MajicBusEntities(); DateTime now = DateTime.UtcNow.AddHours(-7); DateTime utcMidnight = DateTime.UtcNow.AddHours(-7); utcMidnight = utcMidnight.AddHours(-1 * utcMidnight.Hour); utcMidnight = utcMidnight.AddMinutes(-1 * utcMidnight.Minute); utcMidnight = utcMidnight.AddSeconds(-1 * utcMidnight.Second); TimeSpan ts = now - utcMidnight; DateTime normalizedNow = new DateTime(1900, 1, 1) + ts; List <S> times = (from st in context.StopTimes join t in context.Trips on st.TripID equals t.TripID where st.StopID == stopID && st.dtDeparture >= normalizedNow orderby st.dtDeparture.Value group new { t, st } by t.RouteID into aggregate select new S { RouteID = aggregate.Select(a => a.t.RouteID.Value).FirstOrDefault(), StopID = stopID, Dtimes = aggregate.Select(a => a.st.dtDeparture.Value.ToString("hh:mm")).Take(5).ToList() }).ToList(); return(times); }
public static List <BL.SMSLog> GetSMSLogs() { var context = new MajicBusEntities(); return((from sms in context.SMSLogs select sms).ToList()); }
public static List <RouteShape> GetRouteShapeByRouteID(int routeID) { MajicBusEntities context = new MajicBusEntities(); List <RouteShape> shapePosition = (from rs in context.RouteShapes where rs.RouteID == routeID orderby rs.RouteShapeID, rs.SortID select rs).ToList(); return(shapePosition); }
public static List <BL.SMSLog.MessageCounts> GetMessageCounts() { var context = new MajicBusEntities(); return((from sms in context.SMSLogs group sms by sms.MessageBody into aggregate select new BL.SMSLog.MessageCounts { MessageBody = aggregate.Key, Count = aggregate.Count() }).ToList()); }
//For RouteList for App public static List <Route> GetRoutesJ() { var context = new MajicBusEntities(); //return (from r in context.Routes // join t in context.Trips on r.RouteID equals t.RouteID // select r).Distinct().ToList(); // return(context.Routes.ToList()); }
public static List <SMSLog> GetMessagesByPhone(string phoneNumber) { MajicBusEntities context = new MajicBusEntities(); phoneNumber = Classes.Utility.SanitizePhoneNumber(phoneNumber); List <SMSLog> messages = (from sms in context.SMSLogs where sms.ReceivedFrom == phoneNumber || sms.SentTo == phoneNumber select sms).ToList(); return(messages); //Classes.Utility.S }
public static void UpdateSmsLog(String from, String to, String body) { MajicBusEntities context = new MajicBusEntities(); context.SMSLogs.Add(new SMSLog { MessageBody = body, ReceivedFrom = from, SentTo = to, dtSent = DateTime.UtcNow, dtReceived = DateTime.UtcNow }); context.SaveChanges(); }
//create stops to return lat / long and stop ids public static List <Stop> GetStopsByRouteID(int routeID) { var context = new MajicBusEntities(); List <Stop> stops = (from r in context.Routes let t = context.Trips.FirstOrDefault(tr => tr.RouteID == r.RouteID) join st in context.StopTimes on t.TripID equals st.TripID join s in context.Stops on st.StopID equals s.StopID where r.RouteID == routeID orderby st.SortID select s).ToList(); DateTime now = DateTime.UtcNow.AddHours(-7); DateTime utcMidnight = DateTime.UtcNow.AddHours(-7); utcMidnight = utcMidnight.AddHours(-1 * utcMidnight.Hour); utcMidnight = utcMidnight.AddMinutes(-1 * utcMidnight.Minute); utcMidnight = utcMidnight.AddSeconds(-1 * utcMidnight.Second); TimeSpan ts = now - utcMidnight; DateTime normalizedNow = new DateTime(1900, 1, 1) + ts; List <StopTime> times = (from r in context.Routes join t in context.Trips on r.RouteID equals t.RouteID join st in context.StopTimes on t.TripID equals st.TripID where r.RouteID == routeID && st.dtDeparture >= normalizedNow orderby st.dtDeparture, st.SortID select st).ToList(); foreach (Stop s in stops) { s.Dtimes = times.Where(st => st.StopID == s.StopID).Select(st => st.dtDeparture.Value.ToString("HH:mm")).Distinct().Take(5).ToList(); } //TODO: select the next 5 times or so from the database based on routeID (for each stop!!) //you will need to compare the time for this, so compare the dtDeparture >= DateTime.UtcNow.AddHours(-7) //compile this information into a List<KeyValuePair<int, List<String>>>, the first list's key will be the stopid, //and the nested list will be the times. We only really care about departure times, just ignore arrival for now. //I've added a stops.cs class in the BL folder. you will need to modify that and add the list of stop times to the stop class. return(stops); }
public static List <Coordinate> GetBusPosition(int routeID) { MajicBusEntities context = new MajicBusEntities(); List <usp_estimatedBusLineSegment_Result> res = context.usp_estimatedBusLineSegment(DateTime.UtcNow, "-7:00", routeID).ToList(); List <RouteShape> shapePosition = (from rs in context.RouteShapes where rs.RouteID == routeID select rs).OrderBy(rs => rs.SortID).ToList(); int routeShape = shapePosition.First().RouteShapeID; //shapePosition = shapePosition.Where(s => s.RouteShapeID == routeShape).OrderBy(rs => rs.SortID).ToList(); List <Coordinate> busPositions = new List <Coordinate>(); foreach (usp_estimatedBusLineSegment_Result startStop in res) { RouteShape closestStart = null, closestFinish = null; double minStartDist = Double.MaxValue; double minFinishDist = Double.MaxValue; double distToStart = Double.MaxValue; double distToFinish = Double.MaxValue; foreach (RouteShape pos in shapePosition) { if (closestStart == null && closestFinish == null) { closestStart = pos; closestFinish = pos; } distToStart = Classes.Utility.ComputeDistance((double)pos.Lat, (double)pos.Lon, (double)startStop.s1Lat, (double)startStop.s1Lon); if (distToStart < minStartDist) { minStartDist = distToStart; closestStart = pos; } distToFinish = Classes.Utility.ComputeDistance((double)pos.Lat, (double)pos.Lon, (double)startStop.s2Lat, (double)startStop.s2Lon); if (distToFinish < minFinishDist) { minFinishDist = distToFinish; closestFinish = pos; } } if (closestStart.SortID == closestFinish.SortID) { busPositions.Add(new Coordinate((double)closestStart.Lat, (double)closestStart.Lon)); break; } DateTime normalizedUtcNow = DateTime.UtcNow.AddHours(-7).AddYears(-1 * DateTime.UtcNow.Year + 1900); normalizedUtcNow = normalizedUtcNow.AddMonths(1 - normalizedUtcNow.Month); normalizedUtcNow = normalizedUtcNow.AddDays(1 - normalizedUtcNow.Day); double ticksBetweenStops = startStop.dtS2Departure.Value.Ticks - startStop.dtS1Departure.Value.Ticks; double ticksBetweenNowAndStart = normalizedUtcNow.Ticks - startStop.dtS1Departure.Value.Ticks; double percentageAlong = ticksBetweenNowAndStart / ticksBetweenStops; double totalDistance = 0; //this is the stops we want List <RouteShape> shapesBetweenStops = shapePosition.Where(rs => rs.RouteShapeID == closestStart.RouteShapeID && rs.SortID >= closestStart.SortID && rs.SortID <= closestFinish.SortID).OrderBy(r => r.SortID).ToList(); for (int i = 0; i < shapesBetweenStops.Count - 1; i++) { Coordinate one = new Coordinate(shapesBetweenStops[i].Lat.Value, shapesBetweenStops[i].Lon.Value); Coordinate two = new Coordinate(shapesBetweenStops[i + 1].Lat.Value, shapesBetweenStops[i + 1].Lon.Value); totalDistance += Coordinate.ComputeDistance(one, two); } totalDistance *= percentageAlong; for (int i = 0; i < shapesBetweenStops.Count - 1; i++) { Coordinate one = new Coordinate(shapesBetweenStops[i].Lat.Value, shapesBetweenStops[i].Lon.Value); Coordinate two = new Coordinate(shapesBetweenStops[i + 1].Lat.Value, shapesBetweenStops[i + 1].Lon.Value); double distance = Coordinate.ComputeDistance(one, two); if (totalDistance - distance > 0) { totalDistance -= distance; } else { double percentageBetweenRouteShapes = totalDistance / distance; double lat = (two.Latitude - one.Latitude) * percentageBetweenRouteShapes + one.Latitude; double lon = (two.Longitude - one.Longitude) * percentageBetweenRouteShapes + one.Longitude; busPositions.Add(new Coordinate(lat, lon)); break; } } } return(busPositions); }