public void AddItem(SynergyItem si)
{
bool found = false;
foreach (SynergyItem item in _items)
{
if(si.Item.Equals(item.Item,
StringComparison.CurrentCultureIgnoreCase))
{
item.True(si);
found = true;
}
}
if (!found &&
!string.IsNullOrWhiteSpace(si.Item) &&
!si.Fragment.Contains("item={}")) //HACK: trying to fix a bug causing "item={item={}}"
{
_items.Add(si);
}
}
private void MenuItemImportSyncedFiles_Click(object sender, RoutedEventArgs e)
{
try
{
Stopwatch sw = Stopwatch.StartNew();
_sh.ImportSynergyFiles();
foreach (var sl in _sh.Lists)
{
//TODO: needs testing
//make sure categorized items make it
//back into their source lists
var catItems =
new Dictionary<SynergyItem, string>();
var toBeRemoved =
new List<SynergyItem>();
foreach(var si in sl.Items)
{
if (GauntletUtils.IsCategorizedItem(si.Item))
{
string cat =
GauntletUtils.ParseCategory(si.Item);
string trimmedItem =
GauntletUtils.TrimCategory(si.Item);
SynergyItem newItem =
new SynergyItem();
newItem.Item = trimmedItem;
newItem.True(si);
catItems.Add(newItem, cat);
toBeRemoved.Add(si);
}
}
foreach(SynergyItem si in catItems.Keys)
{
EnsureList(catItems[si]).AddItem(si);
}
foreach(SynergyItem si in toBeRemoved)
{
sl.RemoveItem(si);
}
EnsureList(sl.Name).True(sl);
}
sw.Stop();
string time = sw.Elapsed.ToString("mm\\:ss\\.ff");
statusBar.StatusBarText = "Synergy Files Imported: " + time;
}
catch (Exception ex)
{
Display.Exception(ex);
}
}
public override IEnumerable<SynergyList> GetLists(bool active)
{
Dictionary<string, SynergyList> lists =
new Dictionary<string, SynergyList>();
try
{
using (var conn =
new SQLiteConnection(@"Data Source=" +
Configuration.GetSqliteDbPath(GetDbName())))
{
conn.Open();
using (var cmd = new SQLiteCommand(conn))
{
using (var transaction = conn.BeginTransaction())
{
string cmdString =
"SELECT sl." + NwdContract.COLUMN_SYNERGY_LIST_NAME + ", " +
"si." + NwdContract.COLUMN_SYNERGY_ITEM_VALUE + ", " +
"std." + NwdContract.COLUMN_SYNERGY_TO_DO_COMPLETED_AT + ", " +
"std." + NwdContract.COLUMN_SYNERGY_TO_DO_ARCHIVED_AT + " " +
"FROM " + NwdContract.TABLE_SYNERGY_LIST + " sl " +
"JOIN " + NwdContract.TABLE_SYNERGY_LIST_ITEM + " sli " +
"ON sl." + NwdContract.COLUMN_SYNERGY_LIST_ID +
" = sli." + NwdContract.COLUMN_SYNERGY_LIST_ID + " " +
"JOIN " + NwdContract.TABLE_SYNERGY_ITEM + " si " +
"ON sli." + NwdContract.COLUMN_SYNERGY_ITEM_ID +
" = si." + NwdContract.COLUMN_SYNERGY_ITEM_ID + " " +
"JOIN " + NwdContract.TABLE_SYNERGY_TO_DO + " std " +
"ON sli." + NwdContract.COLUMN_SYNERGY_LIST_ITEM_ID + " " +
" = std." + NwdContract.COLUMN_SYNERGY_LIST_ITEM_ID + " " +
"WHERE (std." + NwdContract.COLUMN_SYNERGY_TO_DO_COMPLETED_AT + " IS NULL OR " +
"std." + NwdContract.COLUMN_SYNERGY_TO_DO_COMPLETED_AT + " = '') " +
"AND (std." + NwdContract.COLUMN_SYNERGY_TO_DO_ARCHIVED_AT + " IS NULL OR " +
"std." + NwdContract.COLUMN_SYNERGY_TO_DO_ARCHIVED_AT + " = '') ";
if (active)
{
cmdString += "AND sl." + NwdContract.COLUMN_SYNERGY_LIST_ACTIVATED_AT +
" > sl." + NwdContract.COLUMN_SYNERGY_LIST_SHELVED_AT + " ";
}
cmd.CommandText = cmdString;
//cmd.Parameters.AddWithValue("@active", active);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
string listName = rdr.GetString(0);
string itemValue = rdr.GetString(1);
string completedAt = "";
string archivedAt = "";
if (!rdr.IsDBNull(2))
{
completedAt = rdr.GetString(2);
}
if (!rdr.IsDBNull(3))
{
archivedAt = rdr.GetString(3);
}
if (!lists.ContainsKey(listName))
{
lists[listName] = new SynergyList()
{
Name = listName
};
}
SynergyItem si = new SynergyItem();
si.TurnOffFragmentUpdating();
si.Item = itemValue;
si.CompletedAt = completedAt;
si.ArchivedAt = archivedAt;
si.TurnOnFragmentUpdatingAndUpdate();
lists[listName].AddItem(si);
}
}
transaction.Commit();
}
}
conn.Close();
}
}
catch (Exception ex)
{
//lets just throw it for now, but put something here eventually
throw ex;
}
return lists.Values
.OrderBy(x => x.Name)
.ToList<SynergyList>();
}
public override void UpsertFragment(int listId, int itemId, SynergyItem si, SQLiteCommand cmd)
{
//need to do a separate update attemp for
//each value to avoid overwriting existing
//values or ignoring updates to rows
//with some values already populated
//but others still null
//store updatedAt, we will update this along with
//fragment value as those are not conditional
//updates (always overwritten on every storage op)
string updatedAt =
NwdUtils.GetTimeStamp_yyyyMMddHHmmss();
int synergyListItemId = EnsureSynergyListItemId(listId, itemId, cmd);
//attempt update for CompletedAt
cmd.Parameters.Clear();
cmd.CommandText =
"UPDATE OR IGNORE " + NwdContract.TABLE_SYNERGY_TO_DO + " " +
"SET " + NwdContract.COLUMN_SYNERGY_TO_DO_COMPLETED_AT + " = @completedAt " +
"WHERE " + NwdContract.COLUMN_SYNERGY_LIST_ITEM_ID + " = @synergyListItemId " +
"AND " + NwdContract.COLUMN_SYNERGY_TO_DO_COMPLETED_AT + " IS NOT @completedAt " +
"AND (" + NwdContract.COLUMN_SYNERGY_TO_DO_COMPLETED_AT + " IS NULL " +
"OR " + NwdContract.COLUMN_SYNERGY_TO_DO_COMPLETED_AT + " = '') ";
cmd.Parameters.AddWithValue("@completedAt", si.CompletedAt);
cmd.Parameters.AddWithValue("@synergyListItemId", synergyListItemId);
cmd.ExecuteNonQuery();
//attempt update for ArchivedAt
cmd.Parameters.Clear();
cmd.CommandText =
"UPDATE OR IGNORE " + NwdContract.TABLE_SYNERGY_TO_DO + " " +
"SET " + NwdContract.COLUMN_SYNERGY_TO_DO_ARCHIVED_AT + " = @archivedAt " +
"WHERE " + NwdContract.COLUMN_SYNERGY_LIST_ITEM_ID + " = @synergyListItemId " +
"AND " + NwdContract.COLUMN_SYNERGY_TO_DO_ARCHIVED_AT + " IS NOT @archivedAt " +
"AND (" + NwdContract.COLUMN_SYNERGY_TO_DO_ARCHIVED_AT + " IS NULL " +
"OR " + NwdContract.COLUMN_SYNERGY_TO_DO_ARCHIVED_AT + " = '') ";
cmd.Parameters.AddWithValue("@archivedAt", si.ArchivedAt);
cmd.Parameters.AddWithValue("@synergyListItemId", synergyListItemId);
cmd.ExecuteNonQuery();
//SHOULDN'T NEED THIS WITH V5... leaving it here until tested
////attempt update for Fragment
//cmd.Parameters.Clear();
//cmd.CommandText =adsf
// "UPDATE OR IGNORE " + NwdContract.TABLE_FRAGMENT + " " +
// "SET " + NwdContract.COLUMN_SYNERGY_LIST_ITEM_UPDATED_AT + " = @updatedAt " +
// "WHERE " + NwdContract.COLUMN_LIST_ID + " = @listId " +
// "AND " + NwdContract.COLUMN_ITEM_ID + " = @itemId ";
//cmd.Parameters.AddWithValue("@fragVal", si.Fragment);
//cmd.Parameters.AddWithValue("@updatedAt", updatedAt);
//cmd.Parameters.AddWithValue("@listId", listId);
//cmd.Parameters.AddWithValue("@itemId", itemId);
//cmd.ExecuteNonQuery();
//if all the above fail and are ignored, this will work
//attempt insert for Fragment
cmd.Parameters.Clear();
cmd.CommandText =
"INSERT OR IGNORE INTO " + NwdContract.TABLE_SYNERGY_TO_DO +
" (" + NwdContract.COLUMN_SYNERGY_LIST_ITEM_ID + ", " +
"" + NwdContract.COLUMN_SYNERGY_TO_DO_COMPLETED_AT + ", " +
"" + NwdContract.COLUMN_SYNERGY_TO_DO_ARCHIVED_AT + ", " +
"" + NwdContract.COLUMN_SYNERGY_TO_DO_UPDATED_AT + ") " +
"VALUES (@listItemId, " +
"@completedAt, " +
"@archivedAt, " +
"@updatedAt) ";
cmd.Parameters.AddWithValue("@listItemId", synergyListItemId);
cmd.Parameters.AddWithValue("@completedAt", si.CompletedAt);
cmd.Parameters.AddWithValue("@archivedAt", si.ArchivedAt);
cmd.Parameters.AddWithValue("@updatedAt", updatedAt);
cmd.ExecuteNonQuery();
}
public abstract void UpsertFragment(int listId, int itemId, SynergyItem si, SQLiteCommand cmd);
public override void UpsertFragment(int listId, int itemId, SynergyItem si, SQLiteCommand cmd)
{
//need to do a separate update attemp for
//each value to avoid overwriting existing
//values or ignoring updates to rows
//with some values already populated
//but others still null
//store updatedAt, we will update this along with
//fragment value as those are not conditional
//updates (always overwritten on every storage op)
string updatedAt =
NwdUtils.GetTimeStamp_yyyyMMddHHmmss();
//attempt update for CompletedAt
cmd.Parameters.Clear();
cmd.CommandText =
//"UPDATE OR IGNORE Fragment " +
//"SET CompletedAt = @completedAt " +
//"WHERE ListId = @listId " +
//"AND ItemId = @itemId " +
//"AND CompletedAt IS NOT @completedAt " +
//"AND (CompletedAt IS NULL " +
// "OR CompletedAt = '') ";
"UPDATE OR IGNORE " + NwdContract.TABLE_FRAGMENT + " " +
"SET " + NwdContract.COLUMN_COMPLETED_AT + " = @completedAt " +
"WHERE " + NwdContract.COLUMN_LIST_ID + " = @listId " +
"AND " + NwdContract.COLUMN_ITEM_ID + " = @itemId " +
"AND " + NwdContract.COLUMN_COMPLETED_AT + " IS NOT @completedAt " +
"AND (" + NwdContract.COLUMN_COMPLETED_AT + " IS NULL " +
"OR " + NwdContract.COLUMN_COMPLETED_AT + " = '') ";
cmd.Parameters.AddWithValue("@completedAt", si.CompletedAt);
cmd.Parameters.AddWithValue("@listId", listId);
cmd.Parameters.AddWithValue("@itemId", itemId);
cmd.ExecuteNonQuery();
//attempt update for ArchivedAt
cmd.Parameters.Clear();
cmd.CommandText =
//"UPDATE OR IGNORE Fragment " +
//"SET ArchivedAt = @archivedAt " +
//"WHERE ListId = @listId " +
//"AND ItemId = @itemId " +
//"AND ArchivedAt IS NOT @archivedAt " +
//"AND (ArchivedAt IS NULL " +
// "OR ArchivedAt = '') ";
"UPDATE OR IGNORE " + NwdContract.TABLE_FRAGMENT + " " +
"SET " + NwdContract.COLUMN_ARCHIVED_AT + " = @archivedAt " +
"WHERE " + NwdContract.COLUMN_LIST_ID + " = @listId " +
"AND " + NwdContract.COLUMN_ITEM_ID + " = @itemId " +
"AND " + NwdContract.COLUMN_ARCHIVED_AT + " IS NOT @archivedAt " +
"AND (" + NwdContract.COLUMN_ARCHIVED_AT + " IS NULL " +
"OR " + NwdContract.COLUMN_ARCHIVED_AT + " = '') ";
cmd.Parameters.AddWithValue("@archivedAt", si.ArchivedAt);
cmd.Parameters.AddWithValue("@listId", listId);
cmd.Parameters.AddWithValue("@itemId", itemId);
cmd.ExecuteNonQuery();
//attempt update for Fragment
cmd.Parameters.Clear();
cmd.CommandText =
//"UPDATE OR IGNORE Fragment " +
//"SET FragmentValue = @fragVal, " +
// "UpdatedAt = @updatedAt " +
//"WHERE ListId = @listId " +
//"AND ItemId = @itemId ";
"UPDATE OR IGNORE " + NwdContract.TABLE_FRAGMENT + " " +
"SET " + NwdContract.COLUMN_FRAGMENT_VALUE + " = @fragVal, " +
NwdContract.COLUMN_UPDATED_AT + " = @updatedAt " +
"WHERE " + NwdContract.COLUMN_LIST_ID + " = @listId " +
"AND " + NwdContract.COLUMN_ITEM_ID + " = @itemId ";
cmd.Parameters.AddWithValue("@fragVal", si.Fragment);
cmd.Parameters.AddWithValue("@updatedAt", updatedAt);
cmd.Parameters.AddWithValue("@listId", listId);
cmd.Parameters.AddWithValue("@itemId", itemId);
cmd.ExecuteNonQuery();
//if all the above fail and are ignored, this will work
//attempt insert for Fragment
cmd.Parameters.Clear();
cmd.CommandText =
//"INSERT OR IGNORE INTO Fragment (ListId, " +
// "ItemId, " +
// "FragmentValue, " +
// "CompletedAt, " +
// "ArchivedAt, " +
// "UpdatedAt) " +
//"VALUES (@listId, " +
// "@itemId, " +
// "@fragVal, " +
// "@completedAt, " +
// "@archivedAt, " +
// "@updatedAt) ";
"INSERT OR IGNORE INTO " + NwdContract.TABLE_FRAGMENT +
" (" + NwdContract.COLUMN_LIST_ID + ", " +
"" + NwdContract.COLUMN_ITEM_ID + ", " +
"" + NwdContract.COLUMN_FRAGMENT_VALUE + ", " +
"" + NwdContract.COLUMN_COMPLETED_AT + ", " +
"" + NwdContract.COLUMN_ARCHIVED_AT + ", " +
"" + NwdContract.COLUMN_UPDATED_AT + ") " +
"VALUES (@listId, " +
"@itemId, " +
"@fragVal, " +
"@completedAt, " +
"@archivedAt, " +
"@updatedAt) ";
cmd.Parameters.AddWithValue("@listId", listId);
cmd.Parameters.AddWithValue("@itemId", itemId);
cmd.Parameters.AddWithValue("@fragVal", si.Fragment);
cmd.Parameters.AddWithValue("@completedAt", si.CompletedAt);
cmd.Parameters.AddWithValue("@archivedAt", si.ArchivedAt);
cmd.Parameters.AddWithValue("@updatedAt", updatedAt);
cmd.ExecuteNonQuery();
}
public void UpsertFragment(int listId, int itemId, SynergyItem si, SQLiteCommand cmd)
{
db.UpsertFragment(listId, itemId, si, cmd);
}
private void ImportFiles(IEnumerable<string> filePaths, bool archiveIfNot)
{
foreach (string filePath in filePaths)
{
string listName = Path.GetFileNameWithoutExtension(filePath);
//process list
foreach (string item in File.ReadLines(filePath))
{
if (!string.IsNullOrWhiteSpace(item))
{
SynergyItem si;
if (NwdParser.IsAtomic(item))
{
si = new SynergyItem()
{
Fragment = item
};
}
else
{
si = new SynergyItem()
{
Item = item
};
}
if(archiveIfNot &&
string.IsNullOrWhiteSpace(si.ArchivedAt))
{
si.ArchiveNow();
}
Lists.EnsureList(listName).AddItem(si);
}
}
FilesToConsume.Add(filePath);
DbSavePending = true;
}
}
public void RemoveItem(SynergyItem si)
{
_items.Remove(si);
}
/// <summary>
/// ensures the two items are brought into agreement
/// regarding common boolean properties
/// </summary>
/// <param name="si"></param>
public void True(SynergyItem si)
{
if(!GauntletUtils.TrimCategory(Item).Equals(
GauntletUtils.TrimCategory(si.Item),
StringComparison.CurrentCultureIgnoreCase))
{
throw new ArgumentException(
"SyncItem.True() failed for items: [" +
Item + "] and [" + si.Item + "]");
}
if (string.IsNullOrWhiteSpace(ArchivedAt))
{
ArchivedAt = si.ArchivedAt;
}
if (string.IsNullOrWhiteSpace(CompletedAt))
{
CompletedAt = si.CompletedAt;
}
}
