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