private static async void createFavouritesTable()
{
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='OCDirection'");
if (count == 0)
{
await conn.CreateTableAsync<OCDirection>();
}
}
private static async void createSettingsTable()
{
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='OCSettings'");
if (count == 0)
{
await conn.CreateTableAsync<OCSettings>();
OCSettings settings = OCSettings.newOCSettings(500);
settings.id = 1;
await conn.InsertAsync(settings);
}
}
/// <summary>
/// Initialise the Database connection and creates tables/default values if not existant.
/// </summary>
private async void ConnectionInit()
{
//Connection init
var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
Database = new SQLite.SQLiteAsyncConnection(dbPath);
//Create tables if not exists
await Database.CreateTablesAsync(new Account().GetType(), new DatabaseRoute().GetType(), new DatabasePOI().GetType());
await Database.ExecuteAsync("create table if not exists \"RouteBinds\"(\"RouteID\" integer,\"WaypointID\" integer);", new object[] { });
//Set default Admin Admin password
var result = await Database.ExecuteScalarAsync<String>("Select Gebruikersnaam From Account WHERE Gebruikersnaam = ? AND Password = ?", new object[] { "Admin", "Admin" });
if(result == null)
await Database.InsertAsync(new Account("Admin", "Admin"));
}
public async Task<int> AddLocation(MapLocationModel mapLocation, Guid mapId)
{
SQLiteAsyncConnection connection = new SQLiteAsyncConnection(SQLiteConfiguration.ConnectionString);
int count = await connection.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM MapLocations WHERE MapId = ? AND Latitude = ? AND Longitude = ?", mapId, mapLocation.Latitude, mapLocation.Longitude);
if (count > 0)
return 0;
mapLocation.MapId = mapId;
_locations.Add(mapLocation);
return await connection.InsertAsync(mapLocation);
}
public static async Task<OCSettings> getSettings()
{
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='OCSettings'");
if (count > 0)
{
String Query = "SELECT * from OCSettings LIMIT 1;";
List<OCSettings> settings = await conn.QueryAsync<OCSettings>(Query);
return settings.First<OCSettings>();
}
else
{
createSettingsTable();
return await getSettings();
}
}
public async Task<int> GetNumberOfFavoriteVideosAsync ()
{
try
{
var db = new SQLiteAsyncConnection (_path);
// this counts all records in the database, it can be slow depending on the size of the database
var count = await db.ExecuteScalarAsync<int> ("SELECT Count(*) FROM FavVideoDataModel");
// for a non-parameterless query
// var count = db.ExecuteScalar<int>("SELECT Count(*) FROM Person WHERE FirstName="Amy");
return count;
}
catch (SQLiteException ex)
{
Log.Error (TAG, ex.Message);
return -1;
}
}
public static async Task<ObservableCollection<OCDirection>> getFavourites()
{
//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='OCDirection'");
if (count > 0)
{
String Query = "SELECT Id, * from OCDirection;";
List<OCDirection> stops = await conn.QueryAsync<OCDirection>(Query);
ObservableCollection<OCDirection> directionsCollection = new ObservableCollection<OCDirection>(stops);
return directionsCollection;
}
else
{
createFavouritesTable();
return await getFavourites();
}
}
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);
}
}
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 async Task<IEnumerable<string>> GetAllFavoriteVideosAsync ()
{
try
{
var db = new SQLiteAsyncConnection (_path);
// this counts all records in the database, it can be slow depending on the size of the database
var list = await db.ExecuteScalarAsync<IEnumerable<string>> ("SELECT Id FROM UserVideo WHERE IsFavorite = true");
// for a non-parameterless query
// var count = db.ExecuteScalar<int>("SELECT Count(*) FROM Person WHERE FirstName="Amy");
return list;
}
catch (SQLiteException ex)
{
Log.Error (TAG, ex.Message);
return new List<string>();
}
}
public async Task<IEnumerable<string>> GetAllWatchlistVideosAsync ()
{
try
{
var db = new SQLiteAsyncConnection (_path);
// this counts all records in the database, it can be slow depending on the size of the database
var list = await db.ExecuteScalarAsync<IEnumerable<string>> ("SELECT Id FROM UserVideo WHERE IsInWatchlist = true");
return list;
}
catch (SQLiteException ex)
{
Log.Error (TAG, ex.Message);
return new List<string> ();
}
}
private async Task<int> findNumberRecords(string path)
{
try
{
var db = new SQLiteAsyncConnection(path);
// this counts all records in the database, it can be slow depending on the size of the database
var count = await db.ExecuteScalarAsync<int>("SELECT Count(*) FROM Person");
// for a non-parameterless query
// var count = db.ExecuteScalarAsync<int>("SELECT Count(*) FROM Person WHERE FirstName="Amy");
return count;
}
catch (SQLiteException ex)
{
return -1;
}
}
