public void UpdateDataGrid()
{
ComboBoxItem selected_date = (ComboBoxItem)(comboDate.SelectedItem);
if (comboBank != null && selected_date.Content != null)
{
DataTable dt = new DataTable();
using (SQLiteConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["BudgetDataConnectionString"].ConnectionString))
{
using (SQLiteDataAdapter adapter = new SQLiteDataAdapter())
{
string subquery_date = "";
switch (selected_date.Content.ToString())
{
case "Last 3 Months":
subquery_date = " WHERE date between DATE('now', 'start of month', '-3 month') AND DATE('now') OR category IS NULL OR totalCount > 1 "; break;
case "Last 6 Months":
subquery_date = " WHERE date between DATE('now', 'start of month', '-6 month') AND DATE('now') OR category IS NULL OR totalCount > 1 "; break;
case "Last Year":
subquery_date = " WHERE date between DATE('now', 'start of month', '-12 month') AND DATE('now') OR category IS NULL OR totalCount > 1 "; break;
case "Last 2 Years":
subquery_date = " WHERE date between DATE('now', 'start of month', '-24 month') AND DATE('now') OR category IS NULL OR totalCount > 1 "; break;
default:
break;
}
string qry = "select *, COALESCE(category_presort, 1) as category_sort from Statements ";
qry += " LEFT JOIN (SELECT Id as id2, COUNT(1) totalCount FROM Statements GROUP BY Id) a on id2 = id ";
qry += " LEFT JOIN (SELECT Id as id3, 0 as category_presort FROM Statements WHERE category IS NULL) b on id3 = id ";
qry += subquery_date;
qry += " order by totalCount desc, category_sort asc, date desc";
using (SQLiteCommand cmd = new SQLiteCommand(qry, conn))
{
adapter.SelectCommand = cmd;
adapter.Fill(dt);
DataGridDefinitions.CommitEdit();
DataGridDefinitions.CommitEdit();
DataGridDefinitions.DataContext = dt;
}
}
}
}
}
private void GridCellEditEnding(object sender, DataGridCellEditEndingEventArgs e)
{
if (!isManualEditCommit)
{
isManualEditCommit = true;
DataGridRow row = e.Row;
DataRowView row_items = (DataRowView)row.Item;
int row_index = row.GetIndex();
BudgetCategory new_category = new BudgetCategory();
if (e.Column.Header.ToString() == "Keyword" && old_keyword.Item1 == row_index && !string.IsNullOrWhiteSpace(row_items.Row.ItemArray[5].ToString()))
{
TextBox t = e.EditingElement as TextBox;
new_category.Category = row_items.Row.ItemArray[5].ToString();
new_category.Keyword = t.Text.ToString();
new_category.UpdateDefinition(old_keyword.Item2, row_items.Row.ItemArray[5] != DBNull.Value && !string.IsNullOrWhiteSpace(t.Text.ToString()));
UpdateDataGrid();
}
else if (e.Column.Header.ToString() == "Category" && !string.IsNullOrWhiteSpace(row_items.Row.ItemArray[6].ToString()))
{
string keyword = row_items.Row.ItemArray[6].ToString();
ComboBox cb = e.EditingElement as ComboBox;
string t = Convert.ToString(cb.SelectedItem);
new_category.Category = t;
new_category.Keyword = keyword;
new_category.UpdateDefinition(keyword, row_items.Row.ItemArray[6] != DBNull.Value && !string.IsNullOrWhiteSpace(t));
UpdateDataGrid();
}
else if (e.Column.Header.ToString() == "Category Override" &&
!string.IsNullOrWhiteSpace(row_items.Row.ItemArray[5].ToString()) && !string.IsNullOrWhiteSpace(row_items.Row.ItemArray[6].ToString()))
{
string keyword = row_items.Row.ItemArray[6].ToString();
ComboBox cb = e.EditingElement as ComboBox;
string t = Convert.ToString(cb.SelectedItem);
int id = Convert.ToInt32(row_items.Row.ItemArray[0]);
new_category.CustomCategory = t;
new_category.CategoryOverride(id, !string.IsNullOrWhiteSpace(t));
UpdateDataGrid();
}
else if (e.Column.Header.ToString() == "Holder" &&
!string.IsNullOrWhiteSpace(row_items.Row.ItemArray[5].ToString()) && !string.IsNullOrWhiteSpace(row_items.Row.ItemArray[6].ToString()))
{
ComboBox cb = e.EditingElement as ComboBox;
string t = Convert.ToString(cb.SelectedItem);
BankTransaction new_transaction = new BankTransaction();
new_transaction.Holder = t;
new_transaction.ChangeHolder(Convert.ToInt32(row_items.Row.ItemArray[0]));
UpdateDataGrid();
}
else if (e.Column.Header.ToString() == "Category Override")
{
UpdateDataGrid();
}
else
{
DataGridDefinitions.CommitEdit();
DataGridDefinitions.CommitEdit();
}
isManualEditCommit = false;
}
}