///
// use SQL.Update to update Task
///
// use SQL.MoveToGarbage to remove Task
///
// use SQL.RestoreFromGarbage to restore Task
///
// use SQL.RemoveFromGarbage to remove Task completely
///
// <summary>
// Query a list of tasks which predicate(task) returns true :)
// </summary>
// <param name="predicate">
// The boolean function to filter Tasks
// </param>
// <returns></returns>
//public async t.Task<List<Task>>
// GetTaskListAsync(Predicate<Task> predicate, List<Tag> tagList)
//{
// List<Task> tasks = new List<Task>();
// await _sQLite.OpenAsync();
// SQLiteCommand sQLiteCommand = _sQLite.CreateCommand();
// sQLiteCommand.CommandText = "SELECT * FROM Tasks;";
// DbDataReader reader = await sQLiteCommand.ExecuteReaderAsync();
// if (predicate == null) predicate = t => true;
// var emptyArray = new Tag[0];
// while (await reader.ReadAsync())
// {
// string title = reader[0].ToString();
// string ID = reader[1].ToString();
// Repeat Repeat = RepeatStorageConverter.Parse(reader[2].ToString());
// DateTime activatedTime = reader.GetDateTime(3);
// DateTime expiryTime = reader.GetDateTime(4);
// ObservableCollection<TimeInfo> time = TimeInfosStorageConverter.Parse(reader[5].ToString());
// Tag[] tags = emptyArray;
// string s_tags = reader[6].ToString();
// if (s_tags != "") tags = Array.ConvertAll(s_tags.Split(','), x => tagList.First(y => y.ID == x));
// string webAddress = reader[7].ToString();
// Task task = new Task(title, ID, Repeat, activatedTime, expiryTime, time, new ObservableCollection<Tag>(tags), webAddress);
// if (predicate(task)) tasks.Add(task);
// }
// _sQLite.Close();
// return tasks;
//}
public async t.Task <List <Task> > GetTaskListAsync(string conditions, CollectionForIdentificationObject <Tag> tagList)
{
List <Task> tasks = new List <Task>();
await _sQLite.OpenAsync();
SQLiteCommand sQLiteCommand = _sQLite.CreateCommand();
sQLiteCommand.CommandText = "SELECT *, EXISTS (SELECT ID FROM Notes WHERE Tasks.ID = Notes.ID) AS HasNote FROM Tasks WHERE " + conditions + ";";
var reader = await sQLiteCommand.ExecuteReaderAsync();
var emptyArray = new Tag[0];
while (await reader.ReadAsync())
{
string title = reader[0].ToString();
string ID = reader[1].ToString();
Repeat Repeat = RepeatStorageConverter.Parse(reader[2].ToString());
DateTime activatedTime = reader.GetDateTime(3);
DateTime expiryTime = reader.GetDateTime(4);
ObservableCollection <TimeInfo> time = TimeInfosStorageConverter.Parse(reader[5].ToString());
Tag[] tags = emptyArray;
string s_tags = reader[6].ToString();
if (s_tags != "")
{
tags = Array.ConvertAll(s_tags.Split(','), x => tagList[tagList.IndexOfID(x)]);
}
string webAddress = reader[7].ToString();
Task task = new Task(title, ID, Repeat, activatedTime, expiryTime, time, new ObservableCollection <Tag>(tags), webAddress);
task.HasNote = reader.GetBoolean(8);
tasks.Add(task);
}
_sQLite.Close();
return(tasks);
}
public async t.Task InsertAsync(Task task)
{
string tags;
if (task.Tags.Count == 0)
{
tags = "";
}
else
{
tags = task.Tags[0].ID;
for (var i = 1; i < task.Tags.Count; i++)
{
tags += "," + task.Tags[i].ID;
}
}
string cmd = String.Format(@"INSERT INTO tasks(Title, ID, Repeat, ActivatedTime, ExpiryTime, Time, Tags, WebAddress) VALUES
('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}');",
task.Title, task.ID, RepeatStorageConverter.ToString(task.Repeat), task.ActivatedTime.ToString("yyyy-MM-dd HH:mm"),
task.ExpiryTime.ToString("yyyy-MM-dd HH:mm"), TimeInfosStorageConverter.ToString(task.Time), tags, task.WebAddress);
await ExecuteQueryAsync(cmd);
}