public static async Task <List <OCStop> > getStopByNameOrID(String value)
{
String path = ApplicationData.Current.LocalFolder.Path + "/OCTranspo.sqlite";
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path);
var count = await conn.ExecuteScalarAsync <int>("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='OCStop'");
if (count > 0)
{
if (value.Length > 0)
{
String Query = "SELECT DISTINCT(stop_id), * from OCStop where stop_name LIKE '%" + value.ToUpper() + "%' OR stop_code LIKE '" + value + "%';";
List <OCStop> stops = await conn.QueryAsync <OCStop>(Query);
return(stops);
}
else
{
return(new List <OCStop>());
}
}
else
{
OCTranspoStopsData.initDB();
return(await getStopByNameOrID(value));
}
}
// Stops
public static async Task <ObservableCollection <OCStop> > getCloseStops(double latitude, double longitude, double zoomLevel)
{
//TODO Math.
String path = ApplicationData.Current.LocalFolder.Path + "/OCTranspo.sqlite";
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path);
var count = await conn.ExecuteScalarAsync <int>("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='OCStop'");
if (count > 0)
{
OCSettings settings = await getSettings();
OCGeoMath latlong = OCGeoMath.getRange(latitude, longitude, settings.nearbyDistance);
String Query = "SELECT DISTINCT(stop_id), * from OCStop where ((stop_lat BETWEEN " + latlong.lowerLat + " AND " + latlong.upperLat + ")" +
" AND (stop_lon BETWEEN " + latlong.lowerLong + " AND " + latlong.upperLong + ")) ORDER BY stop_id;";
List <OCStop> stops = await conn.QueryAsync <OCStop>(Query);
ObservableCollection <OCStop> stopsCollection = new ObservableCollection <OCStop>(stops);
return(stopsCollection);
}
else
{
OCTranspoStopsData.initDB();
return(await getCloseStops(latitude, longitude, zoomLevel));
}
}
public static async Task <List <OCSchedule> > getScheduleForDayAndStop(String day, String stop, int route)
{
String path = ApplicationData.Current.LocalFolder.Path + "/OCTranspo.sqlite";
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path);
var count = await conn.ExecuteScalarAsync <int>("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='OCStop'");
if (count > 0)
{
int date = getTodaysDate();
String Query = "SELECT * from OCSchedule where start_date <= " + date + " AND end_date >= " + date + " AND stop_code = '" + stop + "' AND route_short_name='" + route + "' AND " + day.ToLower() + " = '1' ORDER BY arrival_time";
List <OCSchedule> schedule = await conn.QueryAsync <OCSchedule>(Query);
return(schedule);
}
else
{
OCTranspoStopsData.initDB();
return(await getScheduleForDayAndStop(day, stop, route));
}
}
//Schedules
public static async Task <List <OCStop> > getStopIDForCode(String stop)
{
String path = ApplicationData.Current.LocalFolder.Path + "/OCTranspo.sqlite";
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path);
var count = await conn.ExecuteScalarAsync <int>("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='OCStop'");
if (count > 0)
{
int date = getTodaysDate();
String Query = "SELECT stop_id from OCStop where stop_code = '" + stop + "'";
List <OCStop> codes = await conn.QueryAsync <OCStop>(Query);
return(codes);
}
else
{
OCTranspoStopsData.initDB();
return(await getStopIDForCode(stop));
}
}