// Assumes we're parsing for a credit card, specifically AMEX
private List <TransactionForDisplay> ParseCsv(StreamReader reader, Guid accountId, DateTime?fromDate)
{
var transactions = new List <TransactionForDisplay>();
// Trash the header line
reader.ReadLine();
while (reader.Peek() >= 0)
{
var line = reader.ReadLine().Split(',');
var trx = new TransactionForDisplay
{
TransactionId = Guid.NewGuid(),
AccountId = accountId,
EnteredDate = DateTime.Now,
Vendor = "",
TransactionDate = DateTime.Parse(line[0]),
Description = line[1],
Amount = -decimal.Parse(line[4])
};
trx.SetDebitAndCredit();
if (!fromDate.HasValue || trx.TransactionDate >= fromDate.Value)
{
transactions.Add(trx);
}
}
return(transactions);
}
public void InsertRelatedTransaction(TransactionForDisplay first, TransactionForDisplay second)
{
first.RelatedTransactionId = null;
Insert(first);
Insert(second);
first.RelatedTransactionId = second.TransactionId;
Update(first);
}
public IActionResult PostTransaction([FromBody] TransactionForDisplay transaction)
{
var transactions = new List <TransactionForDisplay>();
if (transaction.TransactionId == null || transaction.TransactionId == Guid.Empty)
{
transaction.TransactionId = Guid.NewGuid();
}
if (transaction.AccountId == null)
{
transaction.AccountId = _accountRepo.GetAll().Single(a => a.Name.Equals(transaction.AccountName, StringComparison.CurrentCultureIgnoreCase)).AccountId;
}
transaction.SetAmount();
transaction.EnteredDate = DateTime.Now;
if (transaction.CategoryId.IsNullOrEmpty() && !string.IsNullOrWhiteSpace(transaction.CategoryName))
{
var category = _categoryRepo.GetAll().SingleOrDefault(c => (c.Name.Equals(transaction.CategoryName, StringComparison.CurrentCultureIgnoreCase)));
if (category == null)
{
category = new Category
{
CategoryId = Guid.NewGuid(),
Name = transaction.CategoryName,
Type = "Expenses"
};
_categoryRepo.Insert(category);
}
transaction.CategoryId = category.CategoryId;
}
var bankFeeTransactions = TransactionHelpers.GetBankFeeTransactions(transaction, _categoryRepo, _accountRepo);
transactions.Add(transaction);
transactions.AddRange(bankFeeTransactions);
foreach (var trx in transactions)
{
_transactionRepo.Insert(trx);
}
var accountBalances = _accountRepo.GetAccountBalances().Select(a => new { a.AccountId, a.CurrentBalance });
InvoiceForPosting invoice = null;
if (transaction.InvoiceId.HasValue)
{
invoice = _invoiceRepo.Get(transaction.InvoiceId.Value);
}
var vendor = _vendorRepo.GetAll().SingleOrDefault(v => v.Name == transaction.Vendor);
return(CreatedAtAction("PostTransaction", new { id = transaction.TransactionId }, new { transactions, accountBalances, invoice, vendor }));
}
public IActionResult PostAccount([FromBody] AccountForPosting account)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
var newAccount = new Account
{
AccountId = Guid.NewGuid(),
Name = account.Name,
Currency = account.Currency,
Vendor = account.Vendor,
AccountType = account.AccountType,
MortgagePayment = account.MortgagePayment,
MortgageType = account.MortgageType
};
_accountRepo.Insert(newAccount);
var category = TransactionHelpers.GetOrCreateCategory("Starting Balance", _categoryRepo);
var transaction = new TransactionForDisplay
{
TransactionId = Guid.NewGuid(),
AccountId = newAccount.AccountId,
Amount = account.StartingBalance,
TransactionDate = account.StartDate,
Vendor = "",
Description = "",
CategoryId = category.CategoryId,
CategoryName = category.Name,
EnteredDate = account.StartDate,
IsReconciled = true
};
_transactionRepo.Insert(transaction);
var model = new AccountWithTransactions
{
AccountId = newAccount.AccountId,
Name = newAccount.Name,
Transactions = new List <TransactionForDisplay>(new[] { transaction }),
CurrentBalance = account.StartingBalance
};
return(CreatedAtAction("PostAccount", new { id = newAccount.AccountId }, model));
}
public IActionResult PostTransfer([FromBody] TransactionForDisplay transaction)
{
var transactions = new List <TransactionForDisplay>();
if (transaction.TransactionId == null || transaction.TransactionId == Guid.Empty)
{
transaction.TransactionId = Guid.NewGuid();
}
if (!transaction.AccountId.HasValue)
{
transaction.AccountId = _accountRepo.GetAll().Single(
a => a.Name.Equals(transaction.AccountName, StringComparison.CurrentCultureIgnoreCase)).AccountId;
}
transaction.SetAmount();
var relatedTransactionId = Guid.NewGuid();
var relatedTransaction = new TransactionForDisplay {
TransactionDate = transaction.TransactionDate,
TransactionId = relatedTransactionId,
Vendor = transaction.Vendor,
Description = transaction.Description,
AccountId = transaction.RelatedAccountId.Value,
Amount = 0 - transaction.Amount,
EnteredDate = DateTime.Now,
RelatedTransactionId = transaction.TransactionId
};
relatedTransaction.SetDebitAndCredit();
transaction.RelatedTransactionId = relatedTransactionId;
var bankFeeTransactions = TransactionHelpers.GetBankFeeTransactions(transaction, _categoryRepo, _accountRepo);
transactions.Add(transaction);
transactions.Add(relatedTransaction);
transactions.AddRange(bankFeeTransactions);
_transactionRepo.InsertRelatedTransaction(transaction, relatedTransaction);
foreach (var trx in bankFeeTransactions)
{
_transactionRepo.Insert(trx);
}
var accountBalances = _accountRepo.GetAccountBalances().Select(a => new { a.AccountId, a.CurrentBalance });
return(CreatedAtAction("PostTransfer",
new { id = transaction.TransactionId }, new { transactions, accountBalances }));
}
public IActionResult MakeCorrectingEntries()
{
var investments = GetInvestments();
var currencyController = new CurrenciesController(_currencyRepo);
var currency = currencyController.GetExchangeRateFor("CAD").GetAwaiter().GetResult();
var investmentsTotal = investments.Sum(i => i.Price * i.Shares);
var totalInUsd = investmentsTotal / currency;
var investmentAccount = _accountRepo.GetAll().FirstOrDefault(a => a.AccountType == "Investment");
if (investmentAccount == null)
{
return(Ok());
}
var bookBalance = _transactionRepo.GetByAccount(investmentAccount.AccountId).Sum(i => i.Amount);
var difference = Math.Round(totalInUsd - bookBalance, 2);
if (Math.Abs(difference) >= 1)
{
var category = TransactionHelpers.GetOrCreateCategory("Gain/loss on investments", _categoryRepo);
var transaction = new TransactionForDisplay {
TransactionId = Guid.NewGuid(),
AccountId = investmentAccount.AccountId,
Amount = difference,
CategoryId = category.CategoryId,
CategoryName = category.Name,
CategoryDisplay = category.Name,
TransactionDate = DateTime.Now,
EnteredDate = DateTime.Now,
Description = "Gain/loss"
};
transaction.SetDebitAndCredit();
_transactionRepo.Insert(transaction);
var accountBalances = _accountRepo.GetAccountBalances().Select(a => new { a.AccountId, a.CurrentBalance });
var transactions = new[] { transaction };
return(CreatedAtAction("PostTransaction", new { id = transaction.TransactionId }, new { transactions, accountBalances }));
}
else
{
return(Ok());
}
}
public static IEnumerable <TransactionForDisplay> GetBankFeeTransactions(TransactionForDisplay newTransaction,
ICategoryRepository categoryRepo, IAccountRepository accountRepo)
{
var transactions = new List <TransactionForDisplay>();
var description = newTransaction.Description;
var category = GetOrCreateCategory("Bank Fees", categoryRepo);
var account = accountRepo.Get(newTransaction.AccountId.Value);
if (description.Contains("bf:", StringComparison.CurrentCultureIgnoreCase))
{
newTransaction.Description = description.Substring(0, description.IndexOf("bf:", StringComparison.CurrentCultureIgnoreCase));
var parsed = description.Substring(description.IndexOf("bf:", 0, StringComparison.CurrentCultureIgnoreCase));
while (parsed.StartsWith("bf:", StringComparison.CurrentCultureIgnoreCase))
{
var next = parsed.IndexOf("bf:", 1, StringComparison.CurrentCultureIgnoreCase);
if (next == -1)
{
next = parsed.Length;
}
var transactionData = (parsed.Substring(3, next - 3)).Trim().Split(" ");
decimal amount;
if (decimal.TryParse(transactionData[0], out amount))
{
var bankFeeDescription = string.Join(" ", transactionData.Skip(1).ToArray());
var transaction = new TransactionForDisplay {
TransactionId = Guid.NewGuid(),
TransactionDate = newTransaction.TransactionDate,
AccountId = newTransaction.AccountId,
CategoryId = category.CategoryId,
Description = bankFeeDescription,
Vendor = account.Vendor,
Amount = 0 - amount,
Debit = amount,
EnteredDate = newTransaction.EnteredDate
};
transactions.Add(transaction);
}
parsed = parsed.Substring(next);
}
}
return(transactions);
}
public IActionResult UpdateTransaction([FromRoute] Guid id,
[FromBody] TransactionForDisplay transaction)
{
if (id != transaction.TransactionId)
{
return(BadRequest());
}
var originalAmount = _transactionRepo.Get(transaction.TransactionId).Amount;
transaction.SetAmount();
_transactionRepo.Update(transaction);
InvoiceForPosting invoice = null;
if (transaction.InvoiceId.HasValue)
{
invoice = _invoiceRepo.Get(transaction.InvoiceId.Value);
}
return(Ok(new { transaction, originalAmount, accountBalances = _accountRepo.GetAccountBalances(), invoice }));
}
public void Insert(TransactionForDisplay transaction)
{
using (var conn = Connection)
{
conn.Open();
using (var trx = conn.BeginTransaction())
{
try
{
conn.Execute(
@"INSERT INTO transactions (transaction_id, account_id, vendor, description, is_reconciled, transaction_date, category_id,
entered_date, amount, related_transaction_id, invoice_id)
VALUES (@TransactionId, @AccountId, @Vendor, @Description, @IsReconciled, @TransactionDate, @CategoryId,
@EnteredDate, @Amount, @RelatedTransactionId, @InvoiceId)
", transaction, trx);
if (transaction.InvoiceId.HasValue)
{
UpdateInvoice(transaction.InvoiceId.Value, conn, trx);
}
if (transaction.CategoryId.HasValue)
{
UpdateVendor(transaction.Vendor, transaction.CategoryId.Value, conn, trx);
}
trx.Commit();
}
catch
{
trx.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
}
public void Update(TransactionForDisplay transaction)
{
using (var conn = Connection)
{
conn.Open();
using (var trx = conn.BeginTransaction())
{
try
{
conn.Execute(
@"UPDATE transactions
SET account_id = @AccountId, vendor = @Vendor, description = @Description, is_reconciled = @IsReconciled,
transaction_date = @TransactionDate, category_id = @CategoryId, amount = @Amount,
related_transaction_id = @RelatedTransactionId, invoice_id = @InvoiceId
WHERE transaction_id = @TransactionId", transaction);
if (transaction.InvoiceId.HasValue)
{
UpdateInvoice(transaction.InvoiceId.Value, conn, trx);
}
if (transaction.CategoryId.HasValue)
{
UpdateVendor(transaction.Vendor, transaction.CategoryId.Value, conn, trx);
}
trx.Commit();
}
catch
{
trx.Rollback();
}
finally
{
conn.Close();
}
}
}
}
private List <TransactionForDisplay> ParseQif(StreamReader reader, Guid accountId, DateTime?fromDate)
{
var transactions = new List <TransactionForDisplay>();
var trx = new TransactionForDisplay
{
TransactionId = Guid.NewGuid(),
AccountId = accountId,
EnteredDate = DateTime.Now,
Vendor = ""
};
while (reader.Peek() >= 0)
{
var line = reader.ReadLine();
if (line.Length > 1 || line == "^")
{
switch (line[0])
{
case '^':
if (!fromDate.HasValue || trx.TransactionDate >= fromDate.Value)
{
if (trx.Vendor.StartsWith("Transfer :", StringComparison.CurrentCultureIgnoreCase))
{
// For positive transfers, assume they've been handled elsewhere
if (trx.Amount > 0)
{
break;
}
var accountName = trx.Vendor.Replace("Transfer : ", "");
var account = _accountRepo.GetAll().Single(a => a.Name == accountName);
var relatedTransaction = new TransactionForDisplay
{
TransactionId = Guid.NewGuid(),
AccountId = account.AccountId,
EnteredDate = trx.EnteredDate,
Amount = 0 - trx.Amount,
Description = trx.Description,
IsReconciled = trx.IsReconciled,
TransactionDate = trx.TransactionDate,
RelatedTransactionId = trx.TransactionId
};
trx.Vendor = "";
trx.RelatedTransactionId = relatedTransaction.TransactionId;
transactions.Add(relatedTransaction);
}
trx.SetDebitAndCredit();
transactions.Add(trx);
}
trx = new TransactionForDisplay
{
TransactionId = Guid.NewGuid(),
AccountId = accountId,
EnteredDate = DateTime.Now,
Vendor = ""
};
break;
case 'D':
trx.TransactionDate = DateTime.Parse(line.Substring(1));
break;
case 'T':
trx.Amount = decimal.Parse(line.Substring(1));
break;
case 'P':
trx.Vendor = line.Substring(1);
break;
case 'L':
var category = line.Substring(1)
.Replace("Everyday Expenses:", "")
.Replace("Rainy Day Funds:", "")
.Replace("Monthly Bills:", "");
trx.CategoryId = TransactionHelpers.GetOrCreateCategory(category, _categoryRepo).CategoryId;
break;
case 'M':
trx.Description = line.Substring(1);
break;
case 'C':
trx.IsReconciled = line.Substring(1) == "c";
break;
}
}
}
return(transactions);
}
public IActionResult PostMortgagePayment([FromBody] TransactionForDisplay transaction)
{
var principal = transaction.Debit.Value;
var interest = transaction.Credit.Value;
transaction.Credit = null;
var transactions = new List <TransactionForDisplay>();
if (transaction.TransactionId == null || transaction.TransactionId == Guid.Empty)
{
transaction.TransactionId = Guid.NewGuid();
}
if (!transaction.AccountId.HasValue)
{
transaction.AccountId = _accountRepo.GetAll().Single(
a => a.Name.Equals(transaction.AccountName, StringComparison.CurrentCultureIgnoreCase)).AccountId;
}
transaction.SetAmount();
var relatedTransactionId = Guid.NewGuid();
var relatedTransaction = new TransactionForDisplay {
TransactionDate = transaction.TransactionDate,
TransactionId = relatedTransactionId,
Vendor = transaction.Vendor,
Description = string.IsNullOrWhiteSpace(transaction.Description) ? "Mortgage Payment" : transaction.Description,
AccountId = transaction.RelatedAccountId.Value,
Amount = 0 - transaction.Amount,
EnteredDate = DateTime.Now,
RelatedTransactionId = transaction.TransactionId
};
relatedTransaction.SetDebitAndCredit();
transaction.RelatedTransactionId = relatedTransactionId;
var mortgageInterestCategoryId = TransactionHelpers.GetOrCreateCategory("Mortgage Interest", _categoryRepo).CategoryId;
var interestTransaction = new TransactionForDisplay {
TransactionDate = transaction.TransactionDate,
TransactionId = Guid.NewGuid(),
Vendor = transaction.Vendor,
AccountId = transaction.AccountId,
Amount = 0 - interest,
Debit = interest,
EnteredDate = DateTime.Now,
CategoryId = mortgageInterestCategoryId,
CategoryDisplay = "Mortgage Interest"
};
var bankFeeTransactions = TransactionHelpers.GetBankFeeTransactions(transaction, _categoryRepo, _accountRepo);
transactions.Add(transaction);
transactions.Add(relatedTransaction);
transactions.Add(interestTransaction);
transactions.AddRange(bankFeeTransactions);
_transactionRepo.InsertRelatedTransaction(transaction, relatedTransaction);
_transactionRepo.Insert(interestTransaction);
foreach (var trx in bankFeeTransactions)
{
_transactionRepo.Insert(trx);
}
var accountBalances = _accountRepo.GetAccountBalances();
return(CreatedAtAction("PostTransfer",
new { id = transaction.TransactionId }, new { transactions, accountBalances }));
}
