private async Task <List <Waypoint> > getAssociatedWaypointsAsync(string routeName)
{
int routeID = Database.ExecuteScalarAsync <int>("SELECT \"RouteID\" FROM \"DatabaseRoute\" WHERE \"Name\" = ?", new object[] { routeName }).Result;
List <RouteBind> waypointsID = Database.QueryAsync <RouteBind>("SELECT \"WaypointID\" FROM \"RouteBinds\" WHERE \"RouteID\" = ?", new object[] { routeID }).Result;
List <Waypoint> retVal = new List <Waypoint>();
foreach (RouteBind r in waypointsID)
{
List <DatabasePOI> temp = Database.QueryAsync <DatabasePOI>("SELECT * FROM DatabasePOI WHERE WaypointID = ?", new object[] { r.WaypointID }).Result;
retVal.Add(DatabasePOI.ToWaypoint(temp[0]));
}
return(retVal);
}
public async Task EditRouteAsync(string oldRouteName, Route newRoute)
{
//Remember the Database ID of the route
int id = Database.QueryAsync <DatabaseRoute>("SELECT * FROM \"DatabaseRoute\" WHERE \"Name\" = ? AND RouteID <> 999 AND RouteID <> 998", new object[] { oldRouteName }).Result[0].RouteID;
//And remove it
await DeleteRouteAsync(oldRouteName);
//Now create & insert the new route
DatabaseRoute forDatabase = DatabaseRoute.ToDatabaseRouteIDless(newRoute, id);
await Database.InsertAsync(forDatabase);
//Then bind the waypoints
foreach (Waypoint newWaypoint in newRoute.WayPoints)
{
int existingID = await Database.ExecuteScalarAsync <int>("SELECT WaypointID FROM DatabasePOI WHERE Latitude = ? AND Longitude = ?", new object[] { newWaypoint.Latitude, newWaypoint.Longitude });
if (existingID == 0)
{
//In the case it's a new waypoint, save and bind to this route
int waypointID = await SaveWaypoint(newWaypoint);
await Database.ExecuteAsync("INSERT INTO RouteBinds VALUES(?, ?)", new object[] { forDatabase.RouteID, waypointID });
}
else
{
//But if it exists, edit waypoint if neccesary, check if the bind persisted and then insert
await EditWaypointAsync(DatabasePOI.ToWaypoint(
Database.QueryAsync <DatabasePOI>("SELECT * FROM DatabasePOI WHERE Latitude = ? AND Longitude = ?", new object[] { newWaypoint.Latitude, newWaypoint.Longitude }).Result[0])
, newWaypoint);
List <RouteBind> binds = await Database.QueryAsync <RouteBind>("SELECT WaypointID FROM RouteBinds WHERE RouteID = ? AND RouteID <> 999 AND RouteID <> 998 AND WaypointID = ?", new object[] { id, existingID });
if (binds.Count == 0)
{
await Database.ExecuteAsync("INSERT INTO RouteBinds VALUES(?, ?)", new object[] { forDatabase.RouteID, existingID });
}
}
}
//In the case this is the active route, replace.
Route currentRoute = await GetCurrentRoute();
if (currentRoute != null && currentRoute.Name == oldRouteName)
{
await SaveCurrentRouteAsync(newRoute);
}
}
