// Delete All "Data", but leave Settings and make sure there is one, current Period
public void DeleteAll()
{
lock (locker) {
database.DeleteAll<Task> ();
database.DeleteAll<Period> ();
// but there MUST always be a current period, so create it in the otherwise empty DB
var newPeriod = new Period ();
database.Insert (newPeriod);
}
}
public ScorecardDatabase()
{
database = DependencyService.Get<ISQLite> ().GetConnection ();
// create the tables
database.CreateTable<Task>();
database.CreateTable<Period> ();
database.CreateTable<Settings> ();
if (database.Table<Period> ().Count() == 0) {
// only insert the data if it doesn't already exist
var newPeriod = new Period ();
database.Insert (newPeriod);
}
}
public IEnumerable<Task> GetAllTasksWithinPeriod()
{
lock (locker) {
var currentPeriod = database.Query<Period> ("select * from [Period] where [EndDt] = ?", DateTime.MaxValue);
if (currentPeriod == null) {
throw new Exception ("In GetTasksWithinPeriod: SELECT currentPeriod returned null List");
}
if (currentPeriod.Any ()) {
// There should really only be one such row at this point, but a List needs a First...
var periodStartDate = currentPeriod.First ().StartDT;
// All completed tasks within the *current period*
return database.Query<Task> ("SELECT * FROM [Task] WHERE [DT] > ?", periodStartDate);
} else {
// the app does not function without a Current Period, so
// if we reach this point - something's wrong with the app OR the device's clock went backwards
// So create a current period!
var newPeriod = new Period ();
database.Insert (newPeriod);
return new List<Task> ();
//throw new Exception ("In GetTasksWithinPeriod: No Current Period");
}
}
}
public int SavePeriod()
{
int sqlReturn = 0;
lock (locker) {
// For Debug purposes, show how many rows in DB, both tables, before and after
var countPeriod = database.Table<Period> ().Count();
var countTasks = database.Table<Task> ().Count();
Debug.WriteLine ("Before starting new period, COUNT[Period]=" + countPeriod + " COUNT[Task]=" + countTasks);
var setNow = DateTime.Now;
sqlReturn = database.Execute (
"update [Period] set [EndDt] = ? where [EndDt] = ? ", setNow, DateTime.MaxValue);
if (sqlReturn > 1) {
throw new Exception("Failed to cap [Period] Should be only 1 row with [EndDt]=MaxValue. RC=" + sqlReturn);
}
var newPeriod = new Period ();
sqlReturn = database.Insert (newPeriod);
if (sqlReturn != 1) {
throw new Exception("Failed to properly INSERT new row in [Period]. RC=" + sqlReturn);
}
countPeriod = database.Table<Period> ().Count();
countTasks = database.Table<Task> ().Count();
Debug.WriteLine ("After adding new period, COUNT[Period]=" + countPeriod + " COUNT[Task]=" + countTasks);
return sqlReturn;
}
}