private void FillTags(long taskID)
{
using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection))
{
var command = new System.Data.SQLite.SQLiteCommand("SELECT tags.name FROM tasks_tags INNER JOIN tags ON tasks_tags.tag_id=tags.ID WHERE tasks_tags.task_id=@id", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", taskID));
var reader = command.ExecuteReader();
var builder = new System.Text.StringBuilder();
while (reader.Read())
{
builder.AppendFormat("[{0}]", reader[0]);
}
TagsTextBox.Text = builder.ToString();
}
}
private void FillDeadline(System.Data.DataRow row)
{
FillEventsComboBox();
if (!row.IsNull("deadline_id"))
{
long deadlineID = (long) row["deadline_id"];
using (var automatic = new AutomaticOpenClose(connection))
{
var command = new System.Data.SQLite.SQLiteCommand("SELECT deadlines.deadline, events.ID FROM deadlines LEFT JOIN events ON deadlines.ID=events.deadline_id WHERE deadlines.ID=@id", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", deadlineID));
var reader = command.ExecuteReader();
if (reader.Read())
{
DeadlineDatePicker.SelectedDate = reader.GetDateTime(0);
if (reader.IsDBNull(1))
{
DeadlineIsSelectedDateRadioButton.IsChecked = true;
}
else
{
DeadlineIsEventRadioButton.IsChecked = true;
EventsComboBox.SelectedValue = reader.GetInt64(1);
}
}
}
}
}
private void Save()
{
// --------------------------------------------------
// Deadline
using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection))
{
var command = new System.Data.SQLite.SQLiteCommand("INSERT INTO deadlines(deadline) VALUES(@deadline); SELECT last_insert_rowid()", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@deadline", DeadlineDatePicker.SelectedDate));
var reader = command.ExecuteReader();
if (reader.Read())
{
row["deadline_id"] = reader.GetInt64(0);
}
else
{
row["deadline_id"] = System.DBNull.Value;
}
}
taskDataAdapter.Update(dataSet, "event");
// --------------------------------------------------
// Tags
using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection))
{
var currentTags = new System.Collections.Generic.List<string>(Util.SplitTags(TagsTextBox.Text));
var whereInTuple = Util.SqlParametersList(currentTags);
var rows = new System.Collections.Generic.List<System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>>();
{
var command = new System.Data.SQLite.SQLiteCommand(@"
SELECT events_tags.ID, events_tags.event_id, events_tags.tag_id, tags.name
FROM events_tags LEFT JOIN tags ON tags.ID = events_tags.tag_id
WHERE events_tags.event_id=@id AND tags.name IN(" + whereInTuple.Item1 + @")
UNION ALL
SELECT NULL, NULL, ID, name
FROM tags
WHERE ID NOT IN(SELECT tag_id FROM events_tags WHERE event_id=@id) AND name IN(" + whereInTuple.Item1 + ")", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id));
foreach (var parameter in whereInTuple.Item2)
command.Parameters.Add(parameter);
var reader = command.ExecuteReader();
while (reader.Read())
{
var tuple = new System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>(null, null, null, "");
System.Nullable<long> tasksTagsID = null;
System.Nullable<long> taskID = null;
System.Nullable<long> tagID = null;
string name = "";
if (!reader.IsDBNull(0))
tasksTagsID = reader.GetInt64(0);
if (!reader.IsDBNull(1))
taskID = reader.GetInt64(1);
if (!reader.IsDBNull(2))
tagID = reader.GetInt64(2);
if (!reader.IsDBNull(3))
name = reader.GetString(3);
rows.Add(System.Tuple.Create(tasksTagsID, taskID, tagID, name));
}
}
// delete all old tasks_tags not need for new tags
{
var oldTasksTagsIDs = new System.Collections.Generic.List<long>();
foreach (var tuple in rows)
{
if (tuple.Item1.HasValue)
oldTasksTagsIDs.Add(tuple.Item1.Value);
}
var whereInTuple2 = Util.SqlParametersList(oldTasksTagsIDs);
var command = new System.Data.SQLite.SQLiteCommand("DELETE FROM events_tags WHERE event_id=@id AND ID NOT IN(" + whereInTuple2.Item1 + ")", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id));
foreach (var parameter in whereInTuple2.Item2)
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
// link existing new tags
foreach (var tuple in rows)
{
if (!tuple.Item1.HasValue && tuple.Item3.HasValue)
{
var tagID = tuple.Item3.Value;
long newTasksTagsID = Util.InsertInto(connection, "events_tags", System.Tuple.Create("event_id", id), System.Tuple.Create("tag_id", tagID));
}
}
// create and link new tags
{
var newTags = new System.Collections.Generic.List<string>();
foreach (var tagName in currentTags)
{
bool found = false;
foreach (var row in rows)
{
if (row.Item4 == tagName)
{
found = true;
break;
}
}
if (!found)
{
long newTagID = Util.InsertInto(connection, "tags", System.Tuple.Create("name", tagName));
long newTasksTagsID = Util.InsertInto(connection, "events_tags", System.Tuple.Create("event_id", id), System.Tuple.Create("tag_id", newTagID));
}
}
}
// Alerts
foreach (System.Data.DataRow row in dataSet.Tables["alerts"].Rows)
{
if (row.RowState == System.Data.DataRowState.Added)
row["event_id"] = id;
}
alertsDataAdapter.Update(dataSet, "alerts");
dataSet.Tables["alerts"].Clear();
alertsDataAdapter.Fill(dataSet, "alerts");
}
// --------------------------------------------------
// Clean state
IsDirty = false;
try
{
this.parent.MassReloadEvents();
}
catch
{
}
}
private bool Save()
{
System.Data.SQLite.SQLiteTransaction transaction = null;
using (AutomaticOpenClose aoc = new AutomaticOpenClose(connection))
{
try
{
transaction = connection.BeginTransaction();
// --------------------------------------------------
// Deadline
if (DeadlineIsSelectedDateRadioButton.IsChecked.Value)
{
var command = new System.Data.SQLite.SQLiteCommand("INSERT INTO deadlines(deadline) VALUES(@deadline); SELECT last_insert_rowid()", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@deadline", DeadlineDatePicker.SelectedDate));
var reader = command.ExecuteReader();
if (reader.Read())
{
row["deadline_id"] = reader.GetInt64(0);
}
}
else if (DeadlineIsEventRadioButton.IsChecked.Value)
{
row["deadline_id"] = (EventsComboBox.SelectedValue != null) ? EventsComboBox.SelectedValue : System.DBNull.Value;
}
else
{
row["deadline_id"] = System.DBNull.Value;
}
taskDataAdapter.Update(dataSet, "task");
// --------------------------------------------------
// Tags
var currentTags = new System.Collections.Generic.List<string>(Util.SplitTags(TagsTextBox.Text));
var whereInTuple = Util.SqlParametersList(currentTags);
var rows = new System.Collections.Generic.List<System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>>();
{
var command = new System.Data.SQLite.SQLiteCommand(@"
SELECT tasks_tags.ID, tasks_tags.task_id, tasks_tags.tag_id, tags.name
FROM tasks_tags LEFT JOIN tags ON tags.ID = tasks_tags.tag_id
WHERE tasks_tags.task_id=@id AND tags.name IN(" + whereInTuple.Item1 + @")
UNION ALL
SELECT NULL, NULL, ID, name
FROM tags
WHERE ID NOT IN(SELECT tag_id FROM tasks_tags WHERE task_id=@id) AND name IN(" + whereInTuple.Item1 + ")", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id));
foreach (var parameter in whereInTuple.Item2)
command.Parameters.Add(parameter);
var reader = command.ExecuteReader();
while (reader.Read())
{
var tuple = new System.Tuple<System.Nullable<long>, System.Nullable<long>, System.Nullable<long>, string>(null, null, null, "");
System.Nullable<long> tasksTagsID = null;
System.Nullable<long> taskID = null;
System.Nullable<long> tagID = null;
string name = "";
if (!reader.IsDBNull(0))
tasksTagsID = reader.GetInt64(0);
if (!reader.IsDBNull(1))
taskID = reader.GetInt64(1);
if (!reader.IsDBNull(2))
tagID = reader.GetInt64(2);
if (!reader.IsDBNull(3))
name = reader.GetString(3);
rows.Add(System.Tuple.Create(tasksTagsID, taskID, tagID, name));
}
}
// delete all old tasks_tags not need for new tags
{
var oldTasksTagsIDs = new System.Collections.Generic.List<long>();
foreach (var tuple in rows)
{
if (tuple.Item1.HasValue)
oldTasksTagsIDs.Add(tuple.Item1.Value);
}
var whereInTuple2 = Util.SqlParametersList(oldTasksTagsIDs);
var command = new System.Data.SQLite.SQLiteCommand("DELETE FROM tasks_tags WHERE task_id=@id AND ID NOT IN(" + whereInTuple2.Item1 + ")", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id));
foreach (var parameter in whereInTuple2.Item2)
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
// link existing new tags
foreach (var tuple in rows)
{
if (!tuple.Item1.HasValue && tuple.Item3.HasValue)
{
var tagID = tuple.Item3.Value;
long newTasksTagsID = Util.InsertInto(connection, "tasks_tags", System.Tuple.Create("task_id", id), System.Tuple.Create("tag_id", tagID));
}
}
// create and link new tags
{
var newTags = new System.Collections.Generic.List<string>();
foreach (var tagName in currentTags)
{
bool found = false;
foreach (var row in rows)
{
if (row.Item4 == tagName)
{
found = true;
break;
}
}
if (!found)
{
long newTagID = Util.InsertInto(connection, "tags", System.Tuple.Create("name", tagName));
long newTasksTagsID = Util.InsertInto(connection, "tasks_tags", System.Tuple.Create("task_id", id), System.Tuple.Create("tag_id", newTagID));
}
}
}
// --------------------------------------------------
// Alerts
foreach (System.Data.DataRow row in dataSet.Tables["alerts"].Rows)
{
if (row.RowState == System.Data.DataRowState.Added)
row["task_id"] = id;
}
alertsDataAdapter.Update(dataSet, "alerts");
dataSet.Tables["alerts"].Clear();
alertsDataAdapter.Fill(dataSet, "alerts");
// --------------------------------------------------
// Sub-Tasks
foreach (System.Data.DataRow row in dataSet.Tables["sub_tasks"].Rows)
{
if (row.RowState == System.Data.DataRowState.Added)
row["child_of"] = id;
}
subTasksDataAdapter.Update(dataSet, "sub_tasks");
dataSet.Tables["sub_tasks"].Clear();
subTasksDataAdapter.Fill(dataSet, "sub_tasks");
// --------------------------------------------------
// Clean state
IsDirty = false;
transaction.Commit();
}
catch (System.Data.SQLite.SQLiteException e)
{
if (transaction != null)
transaction.Rollback();
switch (e.ErrorCode)
{
case System.Data.SQLite.SQLiteErrorCode.Constraint:
Util.ShowFieldMustBeUniqueMessage(this, Util.ExtractColumnName(e.Message));
break;
}
}
}
try
{
this.parent.MassReloadTasks();
}
catch
{
}
return !IsDirty;
}
private void FillDeadline(System.Data.DataRow row)
{
if (!row.IsNull("deadline_id"))
{
long deadlineID = (long)row["deadline_id"];
using (var automatic = new AutomaticOpenClose(connection))
{
var command = new System.Data.SQLite.SQLiteCommand("SELECT deadlines.deadline, events.ID FROM deadlines LEFT JOIN events ON deadlines.ID=events.deadline_id WHERE deadlines.ID=@id", connection);
command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", deadlineID));
var reader = command.ExecuteReader();
if (reader.Read())
{
DeadlineDatePicker.SelectedDate = reader.GetDateTime(0);
}
}
}
}
