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