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); }