internal static List <String> GetListOfCategories()
{
var context = new PersonalSpendingAnalysisRepo();
var categoryList = context.Categories.Select(x => x.Name).OrderBy(x => x).ToList();
return(categoryList);
}
private void buttonExportAllToCsv_Click(object sender, EventArgs e)
{
var dlg = new SaveFileDialog();
dlg.Title = "Enter name of json file to save as";
var result = dlg.ShowDialog();
if (result == DialogResult.OK)
{
Thread backgroundThread = new Thread(
new ThreadStart(() =>
{
var context = new PersonalSpendingAnalysisRepo();
var exportable = new Exportable();
exportable.transactions = context.Transaction.ToList();
exportable.categories = context.Categories.ToList();
var settings = new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Ignore,
MissingMemberHandling = MissingMemberHandling.Ignore,
DateFormatHandling = DateFormatHandling.IsoDateFormat
};
string json = JsonConvert.SerializeObject(exportable, settings);
File.WriteAllText(dlg.FileName, json);
MessageBox.Show("Export completed!");
}
));
backgroundThread.Start();
}
}
internal static void CreateOrUpdateBudgets(List <BudgetModel> listOfBudgets)
{
var context = new PersonalSpendingAnalysisRepo();
foreach (var budget in listOfBudgets)
{
var existingBudget = context.Budgets.Include("Category").FirstOrDefault(x => x.Category.Name == budget.CategoryName);
if (existingBudget == null)
{
Category category = context.Categories.First(x => x.Name == budget.CategoryName);
var newBudget = new Budget
{
Category = category,
CategoryId = category.Id,
amount = budget.Amount
};
context.Budgets.Add(newBudget);
context.SaveChanges();
}
else
{
existingBudget.amount = budget.Amount;
context.SaveChanges();
}
}
}
internal static List <BudgetModel> GetBudgets()
{
var context = new PersonalSpendingAnalysisRepo();
var categoryList = context.Budgets.Select(x => new BudgetModel {
CategoryName = x.Category.Name, Amount = x.amount
}).ToList();
return(categoryList);
}
internal static double GetNumberOfDaysOfRecordsInSystem()
{
var context = new PersonalSpendingAnalysisRepo();
var earliestDate = context.Transaction.Select(x => x.transactionDate).Min(x => x);
var latestDate = context.Transaction.Select(x => x.transactionDate).Max(x => x);
var datespan = latestDate.Subtract(earliestDate);
return(datespan.TotalDays);
}
private void buttonResetCategory_Click(object sender, EventArgs e)
{
var context = new PersonalSpendingAnalysisRepo();
var thisTransaction = context.Transaction.Single(x => x.Id == transactionId);
thisTransaction.ManualCategory = false;
thisTransaction.CategoryId = null;
context.SaveChanges();
this.Close();
}
private void buttonAddSearchStringToCategory_Click(object sender, EventArgs e)
{
var context = new PersonalSpendingAnalysisRepo();
var selectedCategory = (ComboboxItem)this.comboBoxCategory.SelectedItem;
var category = context.Categories.Single(x => x.Id == selectedCategory.Value);
category.SearchString += "," + this.textBoxSearchString.Text;
context.SaveChanges();
this.Close();
}
private void Reports_Load(object sender, EventArgs e)
{
progressBar1.Visible = false;
var context = new PersonalSpendingAnalysisRepo();
var startDate = context.Transaction.Min(x => x.transactionDate);
this.endDate.Value = DateTime.Today;
this.startDate.Value = startDate;
}
private void buttonSetCategory_Click(object sender, EventArgs e)
{
var context = new PersonalSpendingAnalysisRepo();
var thisTransaction = context.Transaction.Single(x => x.Id == transactionId);
thisTransaction.ManualCategory = true;
ComboboxItem item = (ComboboxItem)this.comboBox1.SelectedItem;
thisTransaction.CategoryId = item.Value;
context.SaveChanges();
this.Close();
}
private void AddSearchStringToCategory_Load(object sender, EventArgs e)
{
this.comboBoxCategory.DisplayMember = "Text";
this.comboBoxCategory.ValueMember = "Value";
var context = new PersonalSpendingAnalysisRepo();
var categories = context.Categories.OrderBy(x => x.Name);
foreach (var category in categories)
{
this.comboBoxCategory.Items.Add(new ComboboxItem(category.Name, category.Id));
}
}
internal static List <CategoryTotal> GetCategoryTotalsForAllTime()
{
var context = new PersonalSpendingAnalysisRepo();
var transactions = context.Transaction.Include("Category");
var categories = transactions
.GroupBy(x => new { CategoryName = x.Category.Name })
.Select(x => new CategoryTotal
{
CategoryName = x.Key.CategoryName,
Amount = -1 * x.Sum(y => y.amount)
}).OrderByDescending(x => x.Amount)
.ToList();
return(categories);
}
//private void InitializeComponent()
//{
// this.SuspendLayout();
//
// ManuallyAssignCategory
//
// this.ClientSize = new System.Drawing.Size(284, 261);
// this.Name = "ManuallyAssignCategory";
// this.ResumeLayout(false);
//}
private void ManuallyAssignCategory_Load(object sender, EventArgs e)
{
this.comboBox1.DisplayMember = "Text";
this.comboBox1.ValueMember = "Value";
var context = new PersonalSpendingAnalysisRepo();
var categories = context.Categories.OrderBy(x => x.Name);
foreach (var category in categories)
{
this.comboBox1.Items.Add(new ComboboxItem(category.Name, category.Id));
}
var thisTransaction = context.Transaction.Single(x => x.Id == transactionId);
if (thisTransaction.CategoryId != null)
{
this.comboBox1.SelectedValue = (object)thisTransaction.CategoryId;
}
}
private void CategoryManager_Load(object sender, EventArgs e)
{
var context = new PersonalSpendingAnalysisRepo();
var categories = context.Categories.OrderBy(x => x.Name);
foreach (var category in categories)
{
//category
var row = new DataGridViewRow();
var idCell = new DataGridViewTextBoxCell();
idCell.Value = category.Id;
var nameCell = new DataGridViewTextBoxCell();
nameCell.Value = category.Name;
row.Cells.Add(idCell);
row.Cells.Add(nameCell);
this.categoriesGridView.Rows.Add(row);
}
}
internal static List <CategoryTotal> GetCategoryTotals(DateTime startDate, DateTime endDate, bool showDebitsOnly)
{
var context = new PersonalSpendingAnalysisRepo();
var transactions = context.Transaction.Include("Category")
.Where(x => (x.transactionDate > startDate) &&
(x.transactionDate < endDate)
);
var categories = transactions
.GroupBy(x => new { CategoryName = x.Category.Name })
.Select(x => new CategoryTotal {
CategoryName = x.Key.CategoryName,
Amount = -1 * x.Sum(y => y.amount)
}).OrderByDescending(x => x.Amount)
.ToList();
if (showDebitsOnly)
{
categories = categories.Where(x => x.Amount > 0).ToList();
}
return(categories);
}
private void buttonOk_Click(object sender, EventArgs e)
{
var context = new PersonalSpendingAnalysisRepo();
foreach (var category in context.Categories)
{
context.Categories.Remove(category);
}
context.SaveChanges();
foreach (DataGridViewRow row in this.categoriesGridView.Rows)
{
Guid id;
if (row.Cells[0].Value == null)
{
id = Guid.NewGuid();
}
else
{
id = (Guid)row.Cells[0].Value;
}
String name = (String)row.Cells[1].Value;
var existingRowForThisSHA256 = context.Categories.SingleOrDefault(x => x.Id == id);
if (existingRowForThisSHA256 == null)
{
if (!String.IsNullOrEmpty(name))
{
context.Categories.Add(new Repo.Entities.Category
{
Id = id,
Name = name
});
}
}
context.SaveChanges();
this.Close();
}
}
private void refreshCategories()
{
var context = new PersonalSpendingAnalysisRepo();
var categories = context.Categories.OrderBy(x => x.Name);
foreach (var category in categories)
{
//category
var row = new DataGridViewRow();
var idCell = new DataGridViewTextBoxCell();
idCell.Value = category.Id;
var nameCell = new DataGridViewTextBoxCell();
nameCell.Value = category.Name;
var searchStringCell = new DataGridViewTextBoxCell();
searchStringCell.Value = category.SearchString;
row.Cells.Add(idCell);
row.Cells.Add(nameCell);
row.Cells.Add(searchStringCell);
this.categoriesGridView.Rows.Add(row);
}
}
private void buttonSyncToWeb_Click(object sender, EventArgs e)
{
status.Text = "";
var username = this.UsernameTextBox.Text;
var password = this.passwordTextBox.Text;
var userRegistryKey = Microsoft.Win32.Registry.CurrentUser.CreateSubKey("PSAauth");
userRegistryKey.SetValue("User", username);
userRegistryKey.SetValue("Pwd", password);
userRegistryKey.Close();
var deleteExistingTransactions = deleteExistingTransactionsCheckbox.Checked;
//LOGIN
var client = new RestClient("https://www.talkisbetter.com/api/auth");
var request = new RestRequest(Method.POST);
request.AddHeader("content-type", "application/json");
request.AddParameter("application/json", "{\r\n \"username\":\"" + username + "\",\r\n \"password\":\"" + password + "\"\r\n}\r\n", ParameterType.RequestBody);
IRestResponse response = client.Execute(request);
if (response.StatusCode != System.Net.HttpStatusCode.OK)
{
status.AppendText(username + "failed to log in\r\n");
return;
}
dynamic loginResult = JsonConvert.DeserializeObject <dynamic>(response.Content);
var jwt = loginResult.token.Value;
var userId = (string)loginResult.userId.Value;
status.AppendText("userId: " + userId + "\r\n");
var context = new PersonalSpendingAnalysisRepo();
var localCategories = context.Categories.Select(x => new {
Id = x.Id,
Name = x.Name,
SearchString = x.SearchString,
userId = userId
}).ToList();
var localTransactions = context.Transaction.Select(x => new TransactionModel
{
Id = x.Id,
amount = x.amount,
transactionDate = x.transactionDate,
Notes = x.Notes,
CategoryId = x.CategoryId,
SubCategory = x.SubCategory,
AccountId = x.AccountId,
SHA256 = x.SHA256,
userId = userId,
ManualCategory = x.ManualCategory
}).ToList();
Thread backgroundThread = new Thread(
new ThreadStart(() =>
{
//categories first to make sure primary keys are ok
//GET CATEGORIES
client = new RestClient("https://www.talkisbetter.com/api/bankcategorys");
request = new RestRequest(Method.GET);
request.AddHeader("jwt", jwt);
request.AddHeader("userId", userId);
request.AddHeader("content-type", "application/json");
response = client.Execute(request);
if (response.StatusCode != System.Net.HttpStatusCode.OK)
{
status.BeginInvoke(
new Action(() =>
{
status.AppendText("\r\nError encountered reading categories " + response.ErrorMessage + " \r\n");
})
);
}
var remoteCategories = JsonConvert.DeserializeObject <List <dynamic> >(response.Content);
status.BeginInvoke(
new Action(() =>
{
status.AppendText("\r\nsuccessfully downloaded " + remoteCategories.Count + " categories\r\n");
})
);
var successfullyDeleted = 0;
var failedDelete = 0;
if (deleteExistingTransactions)
{
foreach (var remoteCategory in remoteCategories)
{
client = new RestClient("https://www.talkisbetter.com/api/bankcategorys/" + remoteCategory._id);
request = new RestRequest(Method.DELETE);
request.AddHeader("jwt", jwt);
request.AddHeader("userId", userId);
request.AddHeader("content-type", "application/json");
response = client.Execute(request);
if (response.StatusCode == System.Net.HttpStatusCode.OK)
{
successfullyDeleted++;
status.BeginInvoke(
new Action(() =>
{
status.AppendText(".");
})
);
}
else
{
failedDelete++;
status.BeginInvoke(
new Action(() =>
{
status.AppendText("x");
})
);
}
}
status.BeginInvoke(
new Action(() =>
{
status.AppendText("\r\nsuccessfully deleted " + successfullyDeleted + " remote categories failed to delete " + failedDelete + " remote categories\r\n");
})
);
}
//POST EACH NEW CATEGORY
var numberOfCategoriesAdded = 0;
var numberFailedAddCategories = 0;
client = new RestClient("https://www.talkisbetter.com/api/bankcategorys");
foreach (var localCategory in localCategories)
{
var matchingCategory = remoteCategories.SingleOrDefault(x => x.Id == localCategory.Id);
if (deleteExistingTransactions || matchingCategory == null)
{
//no remote category for the localCategory
//category.userId = userId;
JsonSerializer serializer = new JsonSerializer();
serializer.Converters.Add(new JavaScriptDateTimeConverter());
serializer.NullValueHandling = NullValueHandling.Ignore;
string jsonCategory = JsonConvert.SerializeObject(localCategory, Formatting.Indented);
request = new RestRequest(Method.POST);
request.AddHeader("jwt", jwt);
request.AddHeader("userId", userId);
request.AddHeader("content-type", "application/json");
request.AddParameter("application/json", jsonCategory, ParameterType.RequestBody);
response = client.Execute(request);
if (response.StatusCode == System.Net.HttpStatusCode.OK)
{
numberOfCategoriesAdded++;
status.BeginInvoke(
new Action(() =>
{
status.AppendText(".");
})
);
}
else
{
numberFailedAddCategories++;
status.BeginInvoke(
new Action(() =>
{
status.AppendText("x");
})
);
} //end of test Http Response
}
else
{
//existing remote category for the localCategory
//todo merge search strings
}
}
status.BeginInvoke(
new Action(() =>
{
status.AppendText("\r\nsuccessfully uploaded " + numberOfCategoriesAdded + " categories to remote failed to upload " + numberFailedAddCategories + " categories \r\n");
})
);
//SAVE NEW TRANSACTIONS FROM WEB TO DB
var numberAdded = 0;
var numberFailedAdd = 0;
foreach (var remoteCategory in remoteCategories) //localTransactionsToPushUp
{
string stripped = remoteCategory.ToString();
stripped = stripped.Replace("{{", "{").Replace("}}", "}");
var t = JsonConvert.DeserializeObject <Repo.Entities.Category>(stripped);
var matchingCategory = localCategories.SingleOrDefault(x => x.Id == t.Id);
if (matchingCategory == null)
{
var newCategory = new Repo.Entities.Category
{
Id = t.Id,
Name = t.Name,
SearchString = t.SearchString
};
context.Categories.Add(newCategory);
context.SaveChanges();
}
}
status.BeginInvoke(
new Action(() =>
{
status.AppendText("\r\ndb successfully added " + numberAdded + " categories to local db failed to add " + numberFailedAdd + " categories \r\n");
status.AppendText("\r\ncompleted local Categories \r\n");
})
);
//GET TRANSACTIONS
client = new RestClient("https://www.talkisbetter.com/api/banks");
request = new RestRequest(Method.GET);
request.AddHeader("jwt", jwt);
request.AddHeader("userId", userId);
request.AddHeader("content-type", "application/json");
response = client.Execute(request);
var remoteTransactions = JsonConvert.DeserializeObject <dynamic[]>(response.Content);
status.BeginInvoke(
new Action(() =>
{
status.AppendText("\r\nsuccessfully downloaded " + remoteTransactions.Length + " transactions\r\n");
})
);
successfullyDeleted = 0;
failedDelete = 0;
if (deleteExistingTransactions)
{
foreach (var remoteTransaction in remoteTransactions)
{
client = new RestClient("https://www.talkisbetter.com/api/banks/" + remoteTransaction._id);
request = new RestRequest(Method.DELETE);
request.AddHeader("jwt", jwt);
request.AddHeader("userId", userId);
request.AddHeader("content-type", "application/json");
response = client.Execute(request);
if (response.StatusCode == System.Net.HttpStatusCode.OK)
{
successfullyDeleted++;
status.BeginInvoke(
new Action(() =>
{
status.AppendText(".");
})
);
}
else
{
failedDelete++;
status.BeginInvoke(
new Action(() =>
{
status.AppendText("x");
})
);
}
}
status.BeginInvoke(
new Action(() =>
{
status.AppendText("\r\nsuccessfully deleted " + successfullyDeleted + " localTransactions failed to delete " + failedDelete + " localTransactions\r\n");
})
);
}
//POST EACH NEW TRANSACTION
numberAdded = 0;
numberFailedAdd = 0;
client = new RestClient("https://www.talkisbetter.com/api/bank");
foreach (var localTransaction in localTransactions) //localTransactionsToPushUp
{
var matchingTransaction = remoteTransactions.SingleOrDefault(x => x.SHA256 == localTransaction.SHA256);
if (deleteExistingTransactions || matchingTransaction == null)
{
localTransaction.userId = userId;
JsonSerializer serializer = new JsonSerializer();
serializer.Converters.Add(new JavaScriptDateTimeConverter());
serializer.NullValueHandling = NullValueHandling.Ignore;
string jsonTransaction = JsonConvert.SerializeObject(localTransaction, Formatting.Indented);
request = new RestRequest(Method.POST);
request.AddHeader("jwt", jwt);
request.AddHeader("userId", userId);
request.AddHeader("content-type", "application/json");
request.AddParameter("application/json", jsonTransaction, ParameterType.RequestBody);
response = client.Execute(request);
if (response.StatusCode == System.Net.HttpStatusCode.OK)
{
numberAdded++;
status.BeginInvoke(
new Action(() =>
{
status.AppendText(".");
})
);
}
else
{
numberFailedAdd++;
status.BeginInvoke(
new Action(() =>
{
status.AppendText("x");
})
);
} //end of response code
}
else
{
//todo merge the records somehow.
}
}
status.BeginInvoke(
new Action(() =>
{
status.AppendText("\r\nsuccessfully uploaded " + numberAdded + " transactions to remote failed to upload " + numberFailedAdd + " transactions \r\n");
})
);
//SAVE NEW TRANSACTIONS FROM WEB TO DB
numberAdded = 0;
numberFailedAdd = 0;
foreach (var remoteTransaction in remoteTransactions) //localTransactionsToPushUp
{
string stripped = remoteTransaction.ToString();
stripped = stripped.Replace("{{", "{").Replace("}}", "}");
var t = JsonConvert.DeserializeObject <TransactionModel>(stripped);
var matchingTransaction = localTransactions.SingleOrDefault(x => x.SHA256 == t.SHA256);
if (matchingTransaction == null)
{
var newTransaction = new Repo.Entities.Transaction
{
AccountId = t.AccountId,
amount = t.amount,
CategoryId = t.CategoryId,
ManualCategory = t.ManualCategory,
Id = t.Id,
SHA256 = t.SHA256,
transactionDate = t.transactionDate,
Notes = t.Notes,
SubCategory = t.SubCategory
};
context.Transaction.Add(newTransaction);
context.SaveChanges();
}
}
status.BeginInvoke(
new Action(() =>
{
status.AppendText("\r\ndb successfully added " + numberAdded + " transactions to local db failed to add " + numberFailedAdd + " transactions \r\n");
status.AppendText("\r\ncompleted localTransactions \r\n");
})
);
}
));
backgroundThread.Start();
}
private void drawMonthByCategoryChart()
{
var context = new PersonalSpendingAnalysisRepo();
var transactions = context.Transaction.Include("Category")
.Where(x => (x.transactionDate > this.startDate.Value) &&
(x.transactionDate < this.endDate.Value) &&
(this.showDebitsOnly.Checked && x.amount < 0)
);
var categories = transactions
.GroupBy(x => new { Year = x.transactionDate.Year, Month = x.transactionDate.Month, CategoryName = x.Category.Name })
.Select(x => new {
Year = x.Key.Year,
Month = x.Key.Month,
CategoryName = x.Key.CategoryName,
Amount = -1 * x.Sum(y => y.amount)
})
.ToList();
this.chart.Series.Clear();
this.chart.Legends.Clear();
chart.ChartAreas.First().AxisX.CustomLabels.Clear();
List <String> categoryList = categories.Select(x => x.CategoryName).Distinct().ToList();
var monthList = MonthsBetween(this.startDate.Value, this.endDate.Value);
var monthnum = 1;
foreach (var month in monthList)
{
var yearmonth = DateTimeFormatInfo.CurrentInfo.GetAbbreviatedMonthName(month.Item1) + " " + month.Item2;
chart.ChartAreas.First().AxisX.CustomLabels.Add(monthnum - 0.5, monthnum + 0.5, yearmonth);
monthnum++;
}
var colorIndex = 0;
foreach (var category in categoryList)
{
var newColor = ColorTranslator.FromHtml("#" + ColourValues[colorIndex]);
var subSet = categories.Where(x => x.CategoryName == category).OrderBy(x => x.Month);
var newSeries = new Series()
{
Name = category == null ? "uncategorized" : category,
Color = newColor,
IsVisibleInLegend = true,
IsXValueIndexed = true,
ChartType = style,
};
foreach (var month in monthList)
{
var thisMonth = subSet.FirstOrDefault(x => (x.Month == month.Item1) && (x.Year == month.Item2));
var total = thisMonth == null ? 0 : thisMonth.Amount;
newSeries.Points.AddY((double)total);
}
this.chart.Series.Add(newSeries);
this.chart.Legends.Add(new Legend(category));
colorIndex++;
}
this.chart.ChartAreas[0].AxisX.Minimum = 0;
this.chart.ChartAreas[0].AxisX.Maximum = monthnum + 1;
this.chart.ChartAreas[0].AxisY.Minimum = 0;
//this.chart.ChartAreas[0].AxisY.Maximum = (double)categories.Max(x => x.Amount);
}
private void runReport()
{
treeView.Nodes.Clear();
var yearList = new List <float>();
for (var loopYear = this.startDate.Value.Year; loopYear <= this.endDate.Value.Year; loopYear++)
{
yearList.Add(loopYear);
}
var context = new PersonalSpendingAnalysisRepo();
var transactions = context.Transaction.Include("Category")
.Where(x => (x.transactionDate > this.startDate.Value) &&
(x.transactionDate < this.endDate.Value)
);
var categories = transactions
.GroupBy(x => new { CategoryName = x.Category.Name })
.Select(x => new
{
CategoryName = x.Key.CategoryName,
Amount = x.Sum(y => y.amount)
}).OrderByDescending(x => x.Amount)
.ToList();
var count = 0;
foreach (var category in categories)
{
count++;
decimal percent = (decimal)count / (decimal)transactions.Count();
UpdateProgressBar(percent);
var node = new TreeNode(category.CategoryName + " = " + category.Amount);
foreach (var year in yearList)
{
var value = transactions.Where(x => x.Category.Name == category.CategoryName && x.transactionDate.Year == year).Sum(x => (Decimal?)x.amount);
decimal?permonth = (value / 12.0m);
var yearNode = new TreeNode(year + " = " + value + (permonth == null?"":" (per month = " + ((decimal)permonth).ToString("#.##") + " ) "));
var searchStrings = context.Categories.First(x => x.Name == category.CategoryName).SearchString + ",manually assigned";
var subCategories = new List <SortableTreeNode>();
foreach (var searchString in searchStrings.Split(',').ToList())
{
var subCatValue = transactions
.Where(x => x.Category.Name == category.CategoryName && x.transactionDate.Year == year && x.SubCategory == searchString)
.Sum(x => (Decimal?)x.amount);
var subCategoryNode = new TreeNode(searchString + " " + subCatValue);
var transactionsInSubcategory = transactions
.Where(x => x.Category.Name == category.CategoryName && x.transactionDate.Year == year && x.SubCategory == searchString).ToArray();
foreach (var transaction in transactionsInSubcategory)
{
var transactionString = transaction.transactionDate.ToShortDateString() + " " + transaction.Notes + " " + transaction.amount;
var transactionNode = new TreeNode(transactionString);
subCategoryNode.Nodes.Add(transactionNode);
}
subCategories.Add(new SortableTreeNode {
treeNode = subCategoryNode, value = subCatValue
});
}
foreach (var subCategory in subCategories.OrderBy(x => x.value))
{
if (subCategory.treeNode.Nodes.Count > 0)
{
yearNode.Nodes.Add(subCategory.treeNode);
}
}
node.Nodes.Add(yearNode);
}
treeView.Nodes.Add(node);
}
buttonReport.Enabled = true;
buttonExportPdf.Enabled = true;
}
private void drawYearByCategoryChart()
{
var context = new PersonalSpendingAnalysisRepo();
var transactions = context.Transaction.Include("Category")
.Where(x => (x.transactionDate > this.startDate.Value) &&
(x.transactionDate < this.endDate.Value) &&
(this.showDebitsOnly.Checked && x.amount < 0)
);
var categories = transactions
.GroupBy(x => new { Year = x.transactionDate.Year, CategoryName = x.Category.Name })
.Select(x => new {
Year = x.Key.Year,
CategoryName = x.Key.CategoryName,
Amount = -1 * x.Sum(y => y.amount)
})
.ToList();
this.chart.Series.Clear();
this.chart.Legends.Clear();
chart.ChartAreas.First().AxisX.CustomLabels.Clear();
List <String> categoryList = categories.Select(x => x.CategoryName).Distinct().ToList();
var yearList = new List <float>();
for (var loopYear = this.startDate.Value.Year; loopYear <= this.endDate.Value.Year; loopYear++)
{
yearList.Add(loopYear);
}
foreach (var year in yearList)
{
chart.ChartAreas.First().AxisX.CustomLabels.Add(year - 0.5, year + 0.5, year.ToString());
}
var colorIndex = 0;
foreach (var category in categoryList)
{
var newColor = ColorTranslator.FromHtml("#" + ColourValues[colorIndex]);
var subSet = categories.Where(x => x.CategoryName == category).OrderBy(x => x.Year);
var newSeries = new Series()
{
Name = category == null ? "uncategorized" : category,
Color = newColor,
IsVisibleInLegend = true,
IsXValueIndexed = true,
ChartType = style,
};
foreach (var year in yearList)
{
var thisYear = subSet.FirstOrDefault(x => (x.Year == year));
var total = thisYear == null ? 0 : thisYear.Amount;
newSeries.Points.AddY((double)total);
}
this.chart.Series.Add(newSeries);
this.chart.Legends.Add(new Legend(category));
colorIndex++;
}
this.chart.ChartAreas[0].RecalculateAxesScale();
//this.chart.ChartAreas[0].AxisX.Minimum = yearList.First()-1;
//this.chart.ChartAreas[0].AxisX.Maximum = yearList.Last()+1;
//this.chart.ChartAreas[0].AxisY.Minimum = 0;
//this.chart.ChartAreas[0].AxisY.Maximum = (double)categories.Max(x => x.Amount);
}
private void buttonAutoCategorize_Click(object sender, EventArgs e)
{
var context = new PersonalSpendingAnalysisRepo();
var categories = context.Categories;
var datarows = context.Transaction;
var totalCount = datarows.Count();
Thread backgroundThread = new Thread(
new ThreadStart(() =>
{
int currentCount = 0;
var force = true;
foreach (var datarow in datarows)
{
currentCount++;
progressBar1.BeginInvoke(
new Action(() =>
{
progressBar1.Value = (currentCount / totalCount) * 100;
}
));
if (datarow.ManualCategory != true)
{
if (force == true)
{
datarow.CategoryId = null;
}
foreach (var category in categories)
{
if (datarow.CategoryId == null)
{
if (!String.IsNullOrEmpty(category.SearchString))
{
var searchStrings = category.SearchString.ToLower().Split(',');
foreach (var searchString in searchStrings)
{
var trimmedSearchString = searchString.TrimStart().TrimEnd();
if (datarow.Notes.ToLower().Contains(trimmedSearchString))
{
datarow.CategoryId = category.Id;
datarow.SubCategory = trimmedSearchString;
}
}
}
}
}
}
else
{
datarow.SubCategory = "manually assigned";
}
}
context.SaveChanges();
MessageBox.Show("Autocategorization completed!");
progressBar1.BeginInvoke(
new Action(() =>
{
progressBar1.Value = 0;
refresh();
}
));
}
));
backgroundThread.Start();
}
private void buttonOk_Click(object sender, EventArgs e)
{
var context = new PersonalSpendingAnalysisRepo();
var originalCategories = context.Categories.ToList();
var newCategories = new List <Repo.Entities.Category>();
var updatedCategories = new List <Repo.Entities.Category>();
var unchangedCategories = new List <Repo.Entities.Category>();
var deletedCategories = new List <Repo.Entities.Category>();
var categoriesFromDialog = new List <Repo.Entities.Category>();
//store the data from the dialog in categories list
foreach (DataGridViewRow row in this.categoriesGridView.Rows)
{
Guid id;
if (row.Cells[0].Value == null)
{
id = Guid.NewGuid();
}
else
{
id = (Guid)row.Cells[0].Value;
}
String name = (String)row.Cells[1].Value;
String searchString = (String)row.Cells[2].Value;
categoriesFromDialog.Add(new Repo.Entities.Category
{
Id = id,
Name = name,
SearchString = searchString
});
}
foreach (var category in categoriesFromDialog.ToArray())
{
if (originalCategories.Any(x => x.Id == category.Id && x.Name == category.Name && x.SearchString == category.SearchString))
{
unchangedCategories.Add(category);
}
else if (originalCategories.Any(x => x.Id == category.Id && (x.Name != category.Name || x.SearchString != category.SearchString)))
{
//update the category
var existingRowForThisId = context.Categories.SingleOrDefault(x => x.Id == category.Id);
existingRowForThisId.Name = category.Name;
existingRowForThisId.SearchString = category.SearchString;
updatedCategories.Add(category);
}
else
{
//add new category
if (!String.IsNullOrEmpty(category.Name))
{
var newCategory = context.Categories.Add(new Repo.Entities.Category
{
Id = category.Id,
Name = category.Name,
SearchString = category.SearchString
});
newCategories.Add(category);
}
}
}
context.SaveChanges();
//deletions
originalCategories = context.Categories.ToList();
var futureCategories = new List <Category>();
futureCategories.AddRange(newCategories);
futureCategories.AddRange(updatedCategories);
futureCategories.AddRange(unchangedCategories);
deletedCategories = originalCategories;
foreach (var futureCategory in futureCategories)
{
var categoryToDelete = deletedCategories.Single(x => x.Id == futureCategory.Id);
deletedCategories.Remove(categoryToDelete);
}
//deletedCategories should now have a list of deleted items
foreach (var deletedCategory in deletedCategories.ToArray())
{
foreach (var transaction in context.Transaction.Where(x => x.CategoryId == deletedCategory.Id))
{
transaction.Category = null;
}
context.SaveChanges();
context.Categories.Remove(deletedCategory);
context.SaveChanges();
}
}
private void ImportCsv_Click(object sender, EventArgs e)
{
ImportResults results = new ImportResults();
var importCsvDialog = new OpenFileDialog();
importCsvDialog.Filter = "CSV Files | *.csv";
importCsvDialog.Title = "Select Csv File to import";
DialogResult result = importCsvDialog.ShowDialog();
if (result == DialogResult.OK) // Test result.
{
string csvText = System.IO.File.ReadAllText(importCsvDialog.FileName);
csvText = csvText.Replace("\n", "");
var importLines = csvText.Split('\r');
var nonNullLineCount = 0;
String[] headers;
foreach (var importLine in importLines)
{
if (importLine != "")
{
results.NumberOfNewRecordsFound++;
if (nonNullLineCount == 0)
{
headers = purgeCommasInTextFields(importLine).Split(',');
results.NumberOfFieldsFound = headers.Length;
}
else
{
results.NumberOfRecordsImported++;
var columns = purgeCommasInTextFields(importLine).Split(',');
var context = new PersonalSpendingAnalysisRepo();
var id = sha256_hash(importLine);
DateTime tDate;
DateTime.TryParse(columns[0], out tDate);
decimal tAmount;
Decimal.TryParse(columns[3], out tAmount);
var existingRowForThisSHA256 = context.Transaction.SingleOrDefault(x => x.SHA256 == id);
if (existingRowForThisSHA256 == null)
{
results.NumberOfNewRecordsFound++;
context.Transaction.Add(new Repo.Entities.Transaction
{
transactionDate = tDate,
amount = tAmount,
Notes = columns[2].Replace("\"", ""),
SHA256 = id,
});
context.SaveChanges();
}
else
{
results.NumberOfDuplicatesFound++;
}
}
nonNullLineCount++;
}
}
MessageBox.Show("Import finished ( " + results.importId + " ) " + "\r" +
"number of fields per row = " + results.NumberOfFieldsFound + "\r" +
"number of records = " + results.NumberOfRecordsImported + "\r" +
"number of duplicates found = " + results.NumberOfDuplicatesFound + "\r" +
"number of new records = " + results.NumberOfNewRecordsFound
);
}
refresh();
} //end click
private void buttonImportAllFromCsv_Click(object sender, EventArgs e)
{
var context = new PersonalSpendingAnalysisRepo();
var numberOfDuplicateTransactions = 0;
var numberOfNewTransactions = 0;
var numberOfDuplicateCategories = 0;
var numberOfNewCategories = 0;
var dlg = new OpenFileDialog();
dlg.Title = "Enter name of json file to open";
var result = dlg.ShowDialog();
if (result == DialogResult.OK) // Test result.
{
var fileText = File.ReadAllText(dlg.FileName);
var import = JsonConvert.DeserializeObject <Exportable>(fileText);
//do the categories first to avoid foreign key issues
foreach (var category in import.categories)
{
if (context.Categories.SingleOrDefault(x => x.Id == category.Id) != null)
{
//this method aggregates search strings - todo perhaps give user the choice to aggregate or
//replace or keep search strings.
numberOfDuplicateCategories++;
var oldCategory = context.Categories.Single(x => x.Id == category.Id);
var searchStrings = oldCategory.SearchString.Split(',').ToList();
searchStrings.AddRange(category.SearchString.Split(','));
var uniqueSearchStrings = searchStrings.Distinct();
oldCategory.SearchString = string.Join(",", uniqueSearchStrings);
}
else
{
context.Categories.Add(category);
context.SaveChanges();
numberOfNewCategories++;
}
}
foreach (var transaction in import.transactions)
{
if (context.Transaction.SingleOrDefault(x => x.SHA256 == transaction.SHA256) == null)
{
transaction.Category = null;
//import the transaction
context.Transaction.Add(transaction);
context.SaveChanges();
numberOfNewTransactions++;
}
else
{
//transaction is already here
numberOfDuplicateTransactions++;
}
}
refresh();
MessageBox.Show("Import complete "
+ numberOfDuplicateTransactions + " duplicate transactions "
+ numberOfNewTransactions + " new transactions "
+ numberOfDuplicateCategories + " duplicate categories "
+ numberOfNewCategories + " new categories"
);
}
}
private void refresh()
{
this.transactionsGridView.Rows.Clear();
var context = new PersonalSpendingAnalysisRepo();
var categories = context.Categories;
var datarows = new List <Repo.Entities.Transaction>().ToList();
switch (currentOrder)
{
case orderBy.transactionDateDescending:
datarows = context.Transaction.Include("Category").OrderByDescending(x => x.transactionDate).ToList();
break;
case orderBy.transactionDateAscending:
datarows = context.Transaction.Include("Category").OrderBy(x => x.transactionDate).ToList();
break;
case orderBy.amountAscending:
datarows = context.Transaction.Include("Category").OrderBy(x => x.amount).ToList();
break;
case orderBy.amountDescending:
datarows = context.Transaction.Include("Category").OrderByDescending(x => x.amount).ToList();
break;
case orderBy.categoryAscending:
datarows = context.Transaction.Include("Category").OrderBy(x => x.Category == null? "": x.Category.Name).ToList();
break;
case orderBy.categoryDescending:
datarows = context.Transaction.Include("Category").OrderByDescending(x => x.Category == null ? "" : x.Category.Name).ToList();
break;
}
var uncategorized = 0;
var totalTransactions = 0;
decimal value = 0;
foreach (var datarow in datarows)
{
//category
var row = new DataGridViewRow();
var idCell = new DataGridViewTextBoxCell();
idCell.Value = datarow.Id;
var transactionDateCell = new DataGridViewTextBoxCell {
Value = datarow.transactionDate
};
var notesCell = new DataGridViewTextBoxCell {
Value = datarow.Notes
};
var amountCell = new DataGridViewTextBoxCell {
Value = datarow.amount
};
var categoryCell = new DataGridViewTextBoxCell {
Value = datarow.Category == null?null:datarow.Category.Name
};
if (datarow.Category == null)
{
uncategorized++;
value += datarow.amount;
}
totalTransactions++;
row.Cells.Add(idCell);
row.Cells.Add(transactionDateCell);
row.Cells.Add(notesCell);
row.Cells.Add(amountCell);
row.Cells.Add(categoryCell);
this.transactionsGridView.Rows.Add(row);
}
this.label1.Text = ("uncategorized transactions: " + uncategorized + "/" + totalTransactions + " value £" + value);
}
