//Calulate where on a route, a busstop should be placed
public static List<List<string>> CalculateBusStopsForRoute(List<string> stops, List<string> chosenRouteID, string routeNumber, int subRoute)
{
List<List<string>> RouteAndStops = new List<List<string>>();
List<string> chosenRouteLatLng = new List<string>();
List<string> RouteWithStopsID = new List<string>(chosenRouteID);
List<string> StopOnRoute = new List<string>();
int stopCounter = 0;
chosenRouteLatLng = DBConnection.GetLatLngForRoute(chosenRouteID);
decimal currDistToStartOfRoute;
decimal currDistToEndOfRoute;
decimal leastDistToStartOfRoute = -1;
decimal leastDistToEndOfRoute = -1;
int leastStartStopID = 0;
int leastEndStopID = 0;
string leastStartStopName = "";
string leastEndStopName = "";
foreach (string s in stops)
{
decimal leastDist = -1;
decimal currentDist;
BusStops stop = new BusStops();
int pointBeforeStopIndex = 0;
stop = DBConnection.GetIDLatLngForStop(s);
for (int k = 0; k < chosenRouteLatLng.Count - 2; k = k + 2)
{
currentDist = RouteMath.CalculateBusStopToRouteDist(stop.Lat, stop.Lng, (decimal.Parse(chosenRouteLatLng[k])), decimal.Parse(chosenRouteLatLng[k + 1]),
decimal.Parse(chosenRouteLatLng[k + 2]), decimal.Parse(chosenRouteLatLng[k + 3]));
if ((currentDist < leastDist || leastDist == -1) && currentDist <= 15 && currentDist != -1)
{
leastDist = currentDist;
pointBeforeStopIndex = k / 2;
}
}
if (stops.IndexOf(s) == 0 )
{
RouteWithStopsID.Insert(0, stop.ID.ToString());
StopOnRoute.Add(s);
stopCounter++;
continue;
}
else if (stops.IndexOf(s) == stops.Count - 1 )
{
RouteWithStopsID.Add(stop.ID.ToString());
StopOnRoute.Add(s);
stopCounter++;
continue;
}
else if (leastDist != -1)
{
RouteWithStopsID.Insert(pointBeforeStopIndex + stopCounter + 1, stop.ID.ToString());
StopOnRoute.Add(s);
stopCounter++;
}
}
RouteAndStops.Add(RouteWithStopsID);
RouteAndStops.Add(StopOnRoute);
return RouteAndStops;
}
public static BusStops GetIDLatLngForStop(string stopname)
{
using (var connection = new MySqlConnection(DBConnection.getConnectionString()))
{
using (var cmd = connection.CreateCommand())
{
BusStops stop = new BusStops();
cmd.CommandText = string.Format("Select RoutePoint.ID,RoutePoint.Latitude, RoutePoint.Longitude from RoutePoint " +
"inner join BusStop on RoutePoint.ID = BusStop.fk_RoutePoint " +
"where BusStop.StopName='{0}'", stopname);
connection.Open();
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
stop.ID = int.Parse(reader["ID"].ToString());
stop.Lat = decimal.Parse(reader["Latitude"].ToString());
stop.Lng = decimal.Parse(reader["Longitude"].ToString());
}
reader.Close();
connection.Close();
return stop;
}
}
}