public ServiceResult<Category> AddCategory(string name, int? categoryGroupId = null)
{
var result = new ServiceResult<Category>();
// does Category Group exist?
if (categoryGroupId.HasValue)
{
var categoryGroupResult = GetCategoryGroup(categoryGroupId.Value);
if (categoryGroupResult.HasErrors)
{
result.AddErrors(categoryGroupResult);
return result;
}
}
// create Category
var category = new Category()
{
Name = name,
CategoryGroupId = categoryGroupId,
};
_db.Insert<Category>(category);
result.Result = category;
return result;
}
public ServiceResult<Vendor> AddVendor(string name, int? defaultCategoryId = null)
{
var result = new ServiceResult<Vendor>();
// TODO do we need to handle a case where defaultCategoryId = 0
//if (defaultCategoryId.HasValue && defaultCategoryId.Value == 0)
// defaultCategoryId = null;
// does category exist?
if (defaultCategoryId.HasValue)
{
var categoryResult = _categoryService.GetCategory(defaultCategoryId.Value);
if (categoryResult.HasErrors)
{
result.AddErrors(categoryResult);
return result;
}
}
// create Vendor
var vendor = new Vendor()
{
Name = name,
DefaultCategoryId = defaultCategoryId
};
_db.Insert<Vendor>(vendor);
result.Result = vendor;
return result;
}
public ServiceResult<Account> AddAccount(string name, decimal initialBalance = 0.0M, int? defaultCategoryId = null, int? accountGroupId = null)
{
var result = new ServiceResult<Account>();
// does AccountGroup exist?
if (accountGroupId.HasValue)
{
var accountGroupResult = GetAccountGroup(accountGroupId.Value);
if (accountGroupResult.HasErrors)
{
result.AddErrors(accountGroupResult);
return result;
}
}
// does default Category exist?
if (defaultCategoryId.HasValue)
{
var categoryResult = _categoryService.GetCategory(defaultCategoryId.Value);
if (categoryResult.HasErrors)
{
result.AddErrors(categoryResult);
return result;
}
}
// create Account
var account = new Account()
{
Name = name,
InitialBalance = initialBalance,
Balance = initialBalance,
BalanceTimestamp = DateTime.Now,
DefaultCategoryId = defaultCategoryId,
AccountGroupId = accountGroupId
};
_db.Insert<Account>(account);
result.Result = account;
return result;
}
private ServiceResult <IEnumerable <SearchResultViewModel> > GetFormatedResult(ServiceResult <IEnumerable <Drink> > result)
{
var formatedResult = new ServiceResult <IEnumerable <SearchResultViewModel> > {
Data = result.Data.Select(x => new SearchResultViewModel {
Name = x.Name
})
};
formatedResult.AddErrors(result.Errors);
return(formatedResult);
}
public ServiceResult<bool> DeleteBudgetCategory(int categoryId, DateTime month)
{
// TODO handle cascading deletes
var result = new ServiceResult<bool>();
bool deletionResult = false;
// does category exist?
var categoryResult = _categoryService.GetCategory(categoryId);
if (categoryResult.HasErrors)
{
result.AddErrors(categoryResult);
return result;
}
// does budget category exist?
var predicates = new List<IPredicate>();
predicates.Add(Predicates.Field<BudgetCategory>(x => x.Month, Operator.Eq, month));
predicates.Add(Predicates.Field<BudgetCategory>(x => x.CategoryId, Operator.Eq, categoryId));
var predicate = new PredicateGroup { Operator = GroupOperator.And, Predicates = predicates };
var budgetCategory = _db.GetList<BudgetCategory>(predicate);
// are there multiple budget categories with the same month?
if (budgetCategory.Count() > 1)
{
result.AddError(ErrorType.Generic, "Multiple Budget Categories for month {0} exist", month.ToShortDateString());
return result;
}
// is this an existing budget category?
else if (budgetCategory.Count() == 1)
{
var existingBudgetCategory = budgetCategory.First();
deletionResult = _db.Delete<BudgetCategory>(existingBudgetCategory);
}
result.Result = deletionResult;
return result;
}
public ServiceResult<Vendor> UpdateVendorName(int vendorId, string name)
{
var result = new ServiceResult<Vendor>();
Vendor resultVendor;
// does vendor exist?
var vendorResult = GetVendor(vendorId);
if (vendorResult.HasErrors)
{
result.AddErrors(vendorResult);
return result;
}
// does a vendor with this name already exist?
var existingVendorResult = GetVendor(name);
if (existingVendorResult.Result == null)
{
// just rename the original vendor
vendorResult.Result.Name = name;
_db.Update<Vendor>(vendorResult.Result);
resultVendor = vendorResult.Result;
}
else
{
// move this vendor's transactions to the existing vendor
_db.Connection.Execute("UPDATE [Transaction] SET VendorId = @existingVendorId WHERE VendorId = @vendorId", new { existingVendorId = existingVendorResult.Result.Id, vendorId = vendorResult.Result.Id });
// move this vendor's bills and bill transactions to the existing vendor
_db.Connection.Execute("UPDATE Bill SET VendorId = @existingVendorId WHERE VendorId = @vendorId", new { existingVendorId = existingVendorResult.Result.Id, vendorId = vendorResult.Result.Id });
_db.Connection.Execute("UPDATE BillTransaction SET VendorId = @existingVendorId WHERE VendorId = @vendorId", new { existingVendorId = existingVendorResult.Result.Id, vendorId = vendorResult.Result.Id });
_db.Connection.Execute("UPDATE BillTransaction SET OriginalVendorId = @existingVendorId WHERE OriginalVendorId = @vendorId", new { existingVendorId = existingVendorResult.Result.Id, vendorId = vendorResult.Result.Id });
// move this vendor's import description maps to the existing vendor
_db.Connection.Execute("UPDATE ImportDescriptionVendorMap SET VendorId = @existingVendorId WHERE VendorId = @vendorId", new { existingVendorId = existingVendorResult.Result.Id, vendorId = vendorResult.Result.Id });
// delete the original vendor
var deleteVendorResult = DeleteVendor(vendorResult.Result.Id);
if (deleteVendorResult.HasErrors)
{
result.AddErrors(deleteVendorResult);
return result;
}
resultVendor = existingVendorResult.Result;
}
// keep track of the rename in the mappings table
var vendorMapResult = AddVendorMap(resultVendor.Id, name);
if (vendorMapResult.HasErrors)
{
result.AddErrors(vendorMapResult);
return result;
}
result.Result = resultVendor;
return result;
}
public ServiceResult<List<BillTransactionPrediction>> PredictBillTransactionMatch(int billTransactionId)
{
var result = new ServiceResult<List<BillTransactionPrediction>>();
// get the bill transaction
var billTransactionResult = GetBillTransaction(billTransactionId);
if (billTransactionResult.HasErrors)
{
result.AddErrors(billTransactionResult);
return result;
}
// get the vendor name
string billTransactionVendorName = "";
if (billTransactionResult.Result.VendorId.HasValue)
{
var vendorResult = _vendorService.GetVendor(billTransactionResult.Result.VendorId.Value);
if (vendorResult.HasErrors)
{
result.AddErrors(vendorResult);
return result;
}
billTransactionVendorName = vendorResult.Result.Name;
}
// get all transactions
var transactions = _db.Connection.Query(@"
SELECT t.Id, t.Amount, t.Timestamp, v.Name
FROM [Transaction] t
LEFT JOIN Vendor v on v.Id = t.VendorId
");
// calculate confidences
var confidences = transactions.Select(t => new BillTransactionPrediction()
{
BillTransactionId = billTransactionId,
TransactionId = t.Id,
Amount = t.Amount,
Timestamp = t.Timestamp,
VendorName = t.Name,
AmountConfidence = (decimal)Math.Exp(-1 * Math.Pow((double)((t.Amount - billTransactionResult.Result.Amount) / billTransactionResult.Result.Amount), 2.0) / 2.0),
TimestampConfidence = (decimal)Math.Exp(-1 * Math.Pow(((t.Timestamp - billTransactionResult.Result.Timestamp).TotalDays / 60.0), 2.0)),
VendorNameConfidence = (decimal)Math.Exp(-1 * Math.Pow(LevenshteinDistance.Compute(t.Name == null ? "" : t.Name, billTransactionVendorName) / 20.0, 2.0))
});
// order by confidence
result.Result = confidences
.OrderByDescending(x => x.Confidence)
.Take(5)
.ToList();
return result;
}
public ServiceResult<Account> UpdateAccount(int accountId, Filter<string> name = null, Filter<decimal> initialBalance = null, Filter<int?> defaultCategoryId = null, Filter<int?> accountGroupId = null)
{
var result = new ServiceResult<Account>();
// does account exist?
var accountResult = GetAccount(accountId);
if (accountResult.HasErrors)
{
result.AddErrors(accountResult);
return result;
}
var account = accountResult.Result;
if (name != null)
account.Name = name.Object;
if (initialBalance != null)
account.InitialBalance = initialBalance.Object;
if (defaultCategoryId != null)
{
// does category exist?
if (defaultCategoryId.Object.HasValue)
{
var categoryResult = _categoryService.GetCategory(defaultCategoryId.Object.Value);
if (categoryResult.HasErrors)
{
result.AddErrors(categoryResult);
return result;
}
}
account.DefaultCategoryId = defaultCategoryId.Object;
}
if (accountGroupId != null)
{
// does account group exist?
if (accountGroupId.Object.HasValue)
{
var accountGroupResult = GetAccountGroup(accountGroupId.Object.Value);
if (accountGroupResult.HasErrors)
{
result.AddErrors(accountGroupResult);
return result;
}
}
account.AccountGroupId = accountGroupId.Object;
}
_db.Update<Account>(account);
UpdateAccountBalances();
result.Result = account;
return result;
}
public ServiceResult<AccountNameMap> AddAccountNameMap(int accountId, string name)
{
var result = new ServiceResult<AccountNameMap>();
var accountResult = GetAccount(accountId);
if (accountResult.HasErrors)
{
result.AddErrors(accountResult);
return result;
}
var accountNameMap = new AccountNameMap()
{
AccountId = accountId,
Name = name
};
// TODO check for existing mappings?
_db.Insert<AccountNameMap>(accountNameMap);
result.Result = accountNameMap;
return result;
}
public ServiceResult<Vendor> UpdateVendor(int vendorId, string name, int? defaultCategoryId, bool updateUncategorizedTransactions)
{
ServiceResult<Vendor> result = new ServiceResult<Vendor>();
// does this vendor even exist?
var vendor = _vendorRepository.GetById(vendorId);
if (vendor == null)
{
result.AddError(ErrorType.NotFound, "Vendor with Id {0} not found", vendorId);
return result;
}
// see if a vendor with this name already exists
var existingVendor = _vendorRepository.Get(x => x.Name.Equals(name, StringComparison.CurrentCultureIgnoreCase));
if (existingVendor != null && existingVendor != vendor)
{
var renameResult = RenameVendor(vendorId, name);
if (renameResult.HasErrors)
{
result.AddErrors(renameResult.ErrorMessages);
return result;
}
vendor = renameResult.Result;
}
if (defaultCategoryId.HasValue && defaultCategoryId.Value == 0)
defaultCategoryId = null;
// update uncategorized transactions
if (updateUncategorizedTransactions && defaultCategoryId.HasValue)
{
var uncategorizedTransactions = _transactionRepository.GetMany(x => x.VendorId == vendorId && x.Subtransactions.Any(y => y.CategoryId == null)).ToList();
if (uncategorizedTransactions != null)
{
foreach (var trx in uncategorizedTransactions)
{
var subtrx = trx.Subtransactions.Where(x => x.CategoryId == null);
if (subtrx != null)
{
foreach (var sub in subtrx)
{
sub.CategoryId = defaultCategoryId.Value;
}
}
}
}
}
// keep track of the rename in the mappings table
if (!vendor.Name.Equals(name, StringComparison.CurrentCultureIgnoreCase))
{
vendor.ImportDescriptionVendorMaps.Add(new ImportDescriptionVendorMap() { Description = vendor.Name });
}
vendor.Name = name;
vendor.DefaultCategoryId = defaultCategoryId;
_unitOfWork.Commit();
result.Result = vendor;
return result;
}
public ServiceResult<ImportDescriptionVendorMap> AddVendorMap(int vendorId, string description)
{
var result = new ServiceResult<ImportDescriptionVendorMap>();
var vendorResult = GetVendor(vendorId);
if (vendorResult.HasErrors)
{
result.AddErrors(vendorResult);
return result;
}
var vendorMap = new ImportDescriptionVendorMap()
{
VendorId = vendorId,
Description = description
};
_db.Insert<ImportDescriptionVendorMap>(vendorMap);
result.Result = vendorMap;
return result;
}
public ServiceResult<BillTransaction> UpdateBillTransaction(int billTransactionId, decimal? amount, DateTime? date, bool? isPaid)
{
var result = new ServiceResult<BillTransaction>();
// does the bill transaction exist?
var billTransactionResult = GetBillTransaction(billTransactionId);
if (billTransactionResult.HasErrors)
{
result.AddErrors(billTransactionResult);
return result;
}
if (amount.HasValue)
billTransactionResult.Result.Amount = amount.Value;
if (date.HasValue)
billTransactionResult.Result.Timestamp = date.Value;
if (isPaid.HasValue)
billTransactionResult.Result.IsPaid = isPaid.Value;
_db.Update<BillTransaction>(billTransactionResult.Result);
result.Result = billTransactionResult.Result;
return result;
}
public ServiceResult<Transaction> UpdateTransaction(int transactionId, Filter<int> accountId = null, DateTime? timestamp = null, decimal? amount = null, string memo = null, string notes = null, bool isReconciled = false, bool isExcludedFromBudget = false, bool isTransfer = false, Filter<int?> categoryId = null, Filter<int?> vendorId = null, Filter<int?> billTransactionId = null)
{
var result = new ServiceResult<Transaction>();
var transactionResult = GetTransaction(transactionId);
if (transactionResult.HasErrors)
{
result.AddErrors(transactionResult);
return result;
}
// account update
if (accountId != null)
{
// make sure account exists
var accountResult = _accountService.GetAccount(accountId.Object);
if (accountResult.HasErrors)
{
result.AddErrors(accountResult);
return result;
}
transactionResult.Result.AccountId = accountId.Object;
}
// category update
if (categoryId != null)
{
// make sure category exists
if (categoryId.Object.HasValue)
{
var categoryResult = _categoryService.GetCategory(categoryId.Object.Value);
if (categoryResult.HasErrors)
{
result.AddErrors(categoryResult);
return result;
}
}
foreach (var s in transactionResult.Result.Subtransactions)
{
s.CategoryId = categoryId.Object;
}
}
// vendor update
if (vendorId != null)
{
// make sure vendor exists
if (vendorId.Object.HasValue)
{
var vendorResult = _vendorService.GetVendor(vendorId.Object.Value);
if (vendorResult.HasErrors)
{
result.AddErrors(vendorResult);
return result;
}
}
transactionResult.Result.VendorId = vendorId.Object;
}
// bill transaction update
if (billTransactionId != null)
{
// make sure bill transaction exists
if (billTransactionId.Object.HasValue)
{
var billTransactionResult = _billService.GetBillTransaction(billTransactionId.Object.Value);
if (billTransactionResult.HasErrors)
{
result.AddErrors(billTransactionResult);
return result;
}
}
transactionResult.Result.BillTransactionId = billTransactionId.Object;
}
if (timestamp.HasValue)
transactionResult.Result.Timestamp = timestamp.Value;
if (amount.HasValue)
transactionResult.Result.Amount = amount.Value;
foreach (var s in transactionResult.Result.Subtransactions)
{
// TODO how does the amount parameter relate to the subtransaction values?
s.Memo = memo;
s.Notes = notes;
s.IsExcludedFromBudget = isExcludedFromBudget;
s.IsTransfer = isTransfer;
}
transactionResult.Result.IsReconciled = isReconciled;
// update database
_db.Update<Transaction>(transactionResult.Result);
foreach (var s in transactionResult.Result.Subtransactions)
{
_db.Update<Subtransaction>(s);
}
_accountService.UpdateAccountBalances();
result.Result = transactionResult.Result;
return result;
}
public ServiceResult<Transaction> GetTransaction(int transactionId)
{
var result = new ServiceResult<Transaction>();
var transactionsResult = GetTransactions(new Filter<int>(transactionId));
if (transactionsResult.HasErrors)
{
result.AddErrors(transactionsResult);
return result;
}
if (transactionsResult.Result.Count() > 1)
{
result.AddError(ErrorType.Generic, "More than one transaction with Id {0} found", transactionId);
return result;
}
result.Result = transactionsResult.Result.First();
return result;
}
public ServiceResult<BudgetAmountInfo> UpdateBudget(int categoryId, DateTime month, decimal amount)
{
var result = new ServiceResult<BudgetAmountInfo>();
BudgetAmountInfo budgetAmountInfo = new BudgetAmountInfo() { CategoryId = categoryId, Month = month, ExtraAmount = amount };
// does category exist?
var categoryResult = _categoryService.GetCategory(categoryId);
if (categoryResult.HasErrors)
{
result.AddErrors(categoryResult);
return result;
}
// does budget category exist?
var predicates = new List<IPredicate>();
predicates.Add(Predicates.Field<BudgetCategory>(x => x.Month, Operator.Eq, month));
predicates.Add(Predicates.Field<BudgetCategory>(x => x.CategoryId, Operator.Eq, categoryId));
var predicate = new PredicateGroup { Operator = GroupOperator.And, Predicates = predicates };
var budgetCategory = _db.GetList<BudgetCategory>(predicate);
// are there multiple budget categories with the same month?
if (budgetCategory.Count() > 1)
{
result.AddError(ErrorType.Generic, "Multiple Budget Categories for month {0} exist", month.ToShortDateString());
return result;
}
// is this an existing budget category?
else if (budgetCategory.Count() == 1)
{
var existingBudgetCategory = budgetCategory.First();
existingBudgetCategory.Amount = amount;
_db.Update<BudgetCategory>(existingBudgetCategory);
}
// is this a new budget category?
else
{
var newBudgetCategory = new BudgetCategory()
{
CategoryId = categoryId,
Month = month,
Amount = amount
};
_db.Insert<BudgetCategory>(newBudgetCategory);
}
// what is the bills amount for this month?
var nextMonth = month.AddMonths(1);
predicates = new List<IPredicate>();
predicates.Add(Predicates.Field<BillTransaction>(x => x.CategoryId, Operator.Eq, categoryId));
predicates.Add(Predicates.Field<BillTransaction>(x => x.Timestamp, Operator.Ge, month));
predicates.Add(Predicates.Field<BillTransaction>(x => x.Timestamp, Operator.Le, nextMonth));
predicate = new PredicateGroup { Operator = GroupOperator.And, Predicates = predicates };
var billTransactions = _db.GetList<BillTransaction>(predicate);
var billsAmount = billTransactions.Any() ? billTransactions.Sum(x => x.Amount) : 0M;
budgetAmountInfo.BillsAmount = billsAmount;
result.Result = budgetAmountInfo;
return result;
}
public ServiceResult<Bill> AddBill(string name, decimal amount, BillFrequency frequency, DateTime startDate, DateTime endDate, int? billGroupId = null, int? categoryId = null, int? vendorId = null, DateTime? secondaryStartDate = null, DateTime? secondaryEndDate = null)
{
var result = new ServiceResult<Bill>();
// TODO do we need to handle a case where billGroupId = 0
if (billGroupId.HasValue && billGroupId.Value == 0)
billGroupId = null;
if (categoryId.HasValue && categoryId.Value == 0)
categoryId = null;
if (vendorId.HasValue && vendorId.Value == 0)
vendorId = null;
// does the bill group exist?
if (billGroupId.HasValue)
{
var billGroupResult = GetBillGroup(billGroupId.Value);
if (billGroupResult.HasErrors)
{
result.AddErrors(billGroupResult);
}
}
// does the category exist?
if (categoryId.HasValue)
{
var categoryResult = _categoryService.GetCategory(categoryId.Value);
if (categoryResult.HasErrors)
{
result.AddErrors(categoryResult);
}
}
// does the vendor exist?
if (vendorId.HasValue)
{
var vendorResult = _vendorService.GetVendor(vendorId.Value);
if (vendorResult.HasErrors)
{
result.AddErrors(vendorResult);
}
}
// does the startDate come before the endDate?
if (endDate < startDate)
result.AddError(ErrorType.Generic, "Start date {0} must come before End date {1}", startDate.ToString(), endDate.ToString());
// does the secondary startDate come before the secondary endDate?
if (secondaryStartDate.HasValue && secondaryEndDate.HasValue)
{
if (secondaryEndDate.Value < secondaryStartDate.Value)
{
result.AddError(ErrorType.Generic, "Secondary Start date {0} must come before Secondary End date {1}", secondaryStartDate.ToString(), secondaryEndDate.ToString());
}
}
// are both secondary startDate and secondary endDate provided?
if ((secondaryStartDate.HasValue && !secondaryEndDate.HasValue) || (!secondaryStartDate.HasValue && secondaryEndDate.HasValue))
{
result.AddError(ErrorType.Generic, "Both Secondary Start date and Secondary End date should be provided: {0} & {1}", secondaryStartDate.ToString(), secondaryEndDate.ToString());
}
// any errors thus far?
if (result.HasErrors)
return result;
// create bill
var bill = new Bill()
{
Name = name,
Amount = amount,
BillGroupId = billGroupId,
CategoryId = categoryId,
VendorId = vendorId,
StartDate = startDate,
EndDate = endDate,
RecurrenceFrequency = frequency,
StartDate2 = secondaryStartDate,
EndDate2 = secondaryEndDate
};
_db.Insert<Bill>(bill);
// create bill transactions
int count = 0;
var billTransactions = new List<BillTransaction>();
DateTime cur = new DateTime(startDate.Year, startDate.Month, startDate.Day);
while (cur <= endDate)
{
BillTransaction trx = new BillTransaction()
{
BillId = bill.Id,
Amount = amount,
OriginalAmount = amount,
CategoryId = categoryId,
OriginalCategoryId = categoryId,
VendorId = vendorId,
OriginalVendorId = vendorId,
Timestamp = cur,
OriginalTimestamp = cur
};
billTransactions.Add(trx);
count++;
if (frequency == 0)
cur = endDate.AddDays(1);
else if (frequency > 0)
cur = startDate.AddDays(count * (int)frequency);
else
cur = startDate.AddMonths(count * -1 * (int)frequency);
}
if (secondaryStartDate.HasValue)
{
if (secondaryEndDate.HasValue)
endDate = secondaryEndDate.Value;
count = 0;
cur = new DateTime(secondaryStartDate.Value.Year, secondaryStartDate.Value.Month, secondaryStartDate.Value.Day);
while (cur <= endDate)
{
BillTransaction trx = new BillTransaction()
{
BillId = bill.Id,
Amount = amount,
OriginalAmount = amount,
CategoryId = categoryId,
OriginalCategoryId = categoryId,
VendorId = vendorId,
OriginalVendorId = vendorId,
Timestamp = cur,
OriginalTimestamp = cur
};
billTransactions.Add(trx);
count++;
if (frequency == 0)
cur = endDate.AddDays(1);
else if (frequency > 0)
cur = secondaryStartDate.Value.AddDays(count * (int)frequency);
else
cur = secondaryStartDate.Value.AddMonths(count * -1 * (int)frequency);
}
}
_db.Insert<BillTransaction>(billTransactions);
result.Result = bill;
return result;
}
public ServiceResult<Category> UpdateCategoryName(int categoryId, string name)
{
var result = new ServiceResult<Category>();
Category resultCategory;
// does category exist?
var categoryResult = GetCategory(categoryId);
if (categoryResult.HasErrors)
{
result.AddErrors(categoryResult);
return result;
}
// does a category with this name already exist?
var existingCategoryResult = GetCategory(name);
if (existingCategoryResult.Result == null)
{
// just rename the original category
categoryResult.Result.Name = name;
_db.Update<Category>(categoryResult.Result);
resultCategory = categoryResult.Result;
}
else
{
// Bill, BillTransaction, BudgetCategory, Subtransaction, Account, Vendor
_db.Connection.Execute("UPDATE Bill SET CategoryId = @existingCategoryId WHERE CategoryId = @categoryId", new { existingCategoryId = existingCategoryResult.Result.Id, categoryId = categoryResult.Result.Id });
_db.Connection.Execute("UPDATE BillTransaction SET CategoryId = @existingCategoryId WHERE CategoryId = @categoryId", new { existingCategoryId = existingCategoryResult.Result.Id, categoryId = categoryResult.Result.Id });
_db.Connection.Execute("UPDATE BillTransaction SET OriginalCategoryId = @existingCategoryId WHERE OriginalCategoryId = @categoryId", new { existingCategoryId = existingCategoryResult.Result.Id, categoryId = categoryResult.Result.Id });
_db.Connection.Execute("UPDATE BudgetCategory SET CategoryId = @existingCategoryId WHERE CategoryId = @categoryId", new { existingCategoryId = existingCategoryResult.Result.Id, categoryId = categoryResult.Result.Id });
_db.Connection.Execute("UPDATE Subtransaction SET CategoryId = @existingCategoryId WHERE CategoryId = @categoryId", new { existingCategoryId = existingCategoryResult.Result.Id, categoryId = categoryResult.Result.Id });
_db.Connection.Execute("UPDATE [Account] SET DefaultCategoryId = @existingCategoryId WHERE DefaultCategoryId = @categoryId", new { existingCategoryId = existingCategoryResult.Result.Id, categoryId = categoryResult.Result.Id });
_db.Connection.Execute("UPDATE Vendor SET DefaultCategoryId = @existingCategoryId WHERE DefaultCategoryId = @categoryId", new { existingCategoryId = existingCategoryResult.Result.Id, categoryId = categoryResult.Result.Id });
// delete the original category
var deleteCategoryResult = DeleteCategory(categoryResult.Result.Id);
if (deleteCategoryResult.HasErrors)
{
result.AddErrors(deleteCategoryResult);
return result;
}
resultCategory = existingCategoryResult.Result;
}
result.Result = resultCategory;
return result;
}
public ServiceResult<bool> DeleteCategory(int categoryId)
{
var result = new ServiceResult<bool>();
var categoryResult = GetCategory(categoryId);
if (categoryResult.HasErrors)
{
result.AddErrors(categoryResult);
return result;
}
// unassign other entities that use this category
_db.Connection.Execute("UPDATE Account SET DefaultCategoryId = NULL WHERE DefaultCategoryId = @categoryId", new { categoryId });
_db.Connection.Execute("UPDATE Bill SET CategoryId = NULL WHERE CategoryId = @categoryId", new { categoryId });
_db.Connection.Execute("UPDATE BillTransaction SET CategoryId = NULL WHERE CategoryId = @categoryId", new { categoryId });
_db.Connection.Execute("UPDATE BillTransaction SET OriginalCategoryId = NULL WHERE OriginalCategoryId = @categoryId", new { categoryId });
_db.Connection.Execute("UPDATE BudgetCategory SET CategoryId = NULL WHERE CategoryId = @categoryId", new { categoryId });
_db.Connection.Execute("UPDATE Subtransaction SET CategoryId = NULL WHERE CategoryId = @categoryId", new { categoryId });
_db.Connection.Execute("UPDATE Vendor SET DefaultCategoryId = NULL WHERE DefaultCategoryId = @categoryId", new { categoryId });
var deletionResult = _db.Delete<Category>(Predicates.Field<Category>(x => x.Id, Operator.Eq, categoryId));
if (!deletionResult)
{
result.AddError(ErrorType.NotFound, "Category {0} not found", categoryId);
return result;
}
result.Result = deletionResult;
return result;
}
public ServiceResult<Transaction> AddTransaction(int accountId, DateTime timestamp, decimal amount, string memo = null, string notes = null, bool isReconciled = false, bool isExcludedFromBudget = false, bool isTransfer = false, int? categoryId = null, int? vendorId = null, int? billTransactionId = null)
{
var result = new ServiceResult<Transaction>();
// does account exist?
var accountResult = _accountService.GetAccount(accountId);
if (accountResult.HasErrors)
{
result.AddErrors(accountResult);
return result;
}
// add transaction
var transaction = new Transaction()
{
AccountId = accountId,
Timestamp = timestamp,
OriginalTimestamp = timestamp,
Amount = amount,
IsReconciled = isReconciled,
VendorId = vendorId,
BillTransactionId = billTransactionId
};
_db.Insert<Transaction>(transaction);
// add subtransaction
var subTransaction = new Subtransaction()
{
TransactionId = transaction.Id,
Amount = amount,
CategoryId = categoryId,
Memo = memo,
Notes = notes,
IsTransfer = isTransfer,
IsExcludedFromBudget = isExcludedFromBudget,
};
_db.Insert<Subtransaction>(subTransaction);
transaction.Subtransactions = new List<Subtransaction>() { subTransaction };
_accountService.UpdateAccountBalances();
result.Result = transaction;
return result;
}
public ServiceResult<Vendor> UpdateVendorDefaultCategory(int vendorId, int? defaultCategoryId, bool updateUncategorizedTransactions = false)
{
var result = new ServiceResult<Vendor>();
// does vendor exist?
var vendorResult = GetVendor(vendorId);
if (vendorResult.HasErrors)
{
result.AddErrors(vendorResult);
return result;
}
// does category exist?
if (defaultCategoryId.HasValue)
{
var categoryResult = _categoryService.GetCategory(defaultCategoryId.Value);
if (categoryResult.HasErrors)
{
result.AddErrors(categoryResult);
return result;
}
}
vendorResult.Result.DefaultCategoryId = defaultCategoryId;
_db.Update<Vendor>(vendorResult.Result);
// update any uncategorized transactions
if (updateUncategorizedTransactions && defaultCategoryId.HasValue)
{
_db.Connection.Execute("UPDATE s SET s.CategoryId = @categoryId FROM Subtransaction s JOIN [Transaction] t ON t.Id = s.TransactionId AND t.VendorId = @vendorId", new { categoryId = defaultCategoryId.Value, vendorId = vendorId });
}
result.Result = vendorResult.Result;
return result;
}
private ServiceResult<IEnumerable<Transaction>> GetTransactions(Filter<int> transactionId = null, Filter<int?> accountId = null, Filter<int?> categoryId = null, Filter<int?> vendorId = null, DateTime? from = null, DateTime? to = null)
{
var result = new ServiceResult<IEnumerable<Transaction>>();
var parameters = new List<dynamic>();
var builder = new StringBuilder();
builder.Append(@"
FROM [Transaction] t
JOIN Subtransaction st ON st.TransactionId = t.Id
LEFT JOIN Account a on a.Id = t.AccountId
LEFT JOIN Vendor v on v.Id = t.VendorId
LEFT JOIN Category c on c.Id = st.CategoryId
");
var whereClauses = new List<string>();
// transaction clause
if (transactionId != null)
{
// make sure transaction exists
var transactionResult = base.GetEntity<Transaction>(transactionId.Object);
if (transactionResult.HasErrors)
{
result.AddErrors(transactionResult);
return result;
}
whereClauses.Add("t.Id = @transactionId");
parameters.Add(new { transactionId = transactionId.Object });
}
// account clause
if (accountId != null)
{
// make sure account exists
if (accountId.Object.HasValue)
{
var accountResult = _accountService.GetAccount(accountId.Object.Value);
if (accountResult.HasErrors)
{
result.AddErrors(accountResult);
return result;
}
}
whereClauses.Add("t.AccountId = @accountId");
parameters.Add(new { accountId = accountId.Object });
}
// category clause
if (categoryId != null)
{
// make sure category exists
if (categoryId.Object.HasValue)
{
var categoryResult = _categoryService.GetCategory(categoryId.Object.Value);
if (categoryResult.HasErrors)
{
result.AddErrors(categoryResult);
return result;
}
}
whereClauses.Add("st.CategoryId = @categoryId");
parameters.Add(new { categoryId = categoryId.Object });
}
// vendor clause
if (vendorId != null)
{
// make sure vendor exists
if (vendorId.Object.HasValue)
{
var vendorResult = _vendorService.GetVendor(vendorId.Object.Value);
if (vendorResult.HasErrors)
{
result.AddErrors(vendorResult);
return result;
}
}
whereClauses.Add("t.VendorId = @vendorId");
parameters.Add(new { vendorId = vendorId.Object });
}
// start/end clauses
if (from.HasValue)
{
whereClauses.Add("t.Timestamp >= @from");
parameters.Add(new { from = new DateTime(Math.Max(SqlDateTime.MinValue.Value.Ticks, from.Value.Ticks)) });
}
if (to.HasValue)
{
whereClauses.Add("t.Timestamp <= @to");
parameters.Add(new { to = new DateTime(Math.Min(SqlDateTime.MaxValue.Value.Ticks, to.Value.Ticks)) });
}
if (whereClauses.Any())
{
builder.Append("WHERE ");
builder.Append(String.Join(" AND ", whereClauses));
}
// get transactions and subtransactions separately
var sql = builder.ToString();
object mergedParameters = MergeParameters(parameters);
var transactions = _db.Connection.Query<Transaction>("SELECT t.*, a.Name as AccountName, v.Name as VendorName " + sql, mergedParameters);
var subtransactions = _db.Connection.Query<Subtransaction>("SELECT st.*, c.Name as CategoryName " + sql, mergedParameters);
// stitch the subtransactions into the transactions
foreach (var t in transactions)
{
t.Subtransactions = subtransactions.Where(x => x.TransactionId == t.Id);
}
result.Result = transactions;
return result;
}
public ServiceResult<Bill> UpdateBill(int billId, string name, decimal amount, BillFrequency frequency, DateTime startDate, DateTime endDate, int? billGroupId = null, int? categoryId = null, int? vendorId = null, DateTime? secondaryStartDate = null, DateTime? secondaryEndDate = null, bool updateExisting = false)
{
var result = new ServiceResult<Bill>();
var billResult = GetBill(billId);
if (billResult.HasErrors)
{
result.AddErrors(billResult);
return result;
}
// if the new value is 0, that means set it to null
if (billGroupId.HasValue && billGroupId.Value == 0)
billGroupId = null;
if (categoryId.HasValue && categoryId.Value == 0)
categoryId = null;
if (vendorId.HasValue && vendorId.Value == 0)
vendorId = null;
// do the new BillGroup, Category and Vendor exist?
if (billGroupId.HasValue)
{
var billGroupResult = GetBillGroup(billGroupId.Value);
if (billGroupResult.HasErrors)
{
result.AddErrors(billGroupResult);
return result;
}
}
if (categoryId.HasValue)
{
var categoryResult = _categoryService.GetCategory(categoryId.Value);
if (categoryResult.HasErrors)
{
result.AddErrors(categoryResult);
return result;
}
}
if (vendorId.HasValue)
{
var vendorResult = _vendorService.GetVendor(vendorId.Value);
if (vendorResult.HasErrors)
{
result.AddErrors(vendorResult);
return result;
}
}
// TODO handle updateExisting
//if (updateExisting)
//{
// if (bill.StartDate != startDate || bill.EndDate != endDate || bill.RecurrenceFrequency != frequency || bill.StartDate2 != secondaryStartDate || bill.EndDate2 != secondaryEndDate)
// {
// List<BillTransaction> existing = _billTransactionRepository.GetMany(x => x.BillId == billId).ToList();
// List<BillTransaction> expected = new List<BillTransaction>();
// #region Generate expected transactions
// int count = 0;
// DateTime cur = new DateTime(startDate.Year, startDate.Month, startDate.Day);
// while (cur <= endDate)
// {
// BillTransaction trx = new BillTransaction()
// {
// Amount = amount,
// OriginalAmount = amount,
// CategoryId = categoryId,
// OriginalCategoryId = categoryId,
// VendorId = vendorId,
// OriginalVendorId = vendorId,
// Timestamp = cur,
// OriginalTimestamp = cur
// };
// expected.Add(trx);
// count++;
// if (frequency == 0)
// cur = endDate.AddDays(1);
// else if (frequency > 0)
// cur = startDate.AddDays(count * frequency);
// else
// cur = startDate.AddMonths(count * -1 * frequency);
// }
// if (secondaryStartDate.HasValue)
// {
// if (secondaryEndDate.HasValue)
// endDate = secondaryEndDate.Value;
// count = 0;
// cur = new DateTime(secondaryStartDate.Value.Year, secondaryStartDate.Value.Month, secondaryStartDate.Value.Day);
// while (cur <= endDate)
// {
// BillTransaction trx = new BillTransaction()
// {
// Amount = amount,
// OriginalAmount = amount,
// CategoryId = categoryId,
// OriginalCategoryId = categoryId,
// VendorId = vendorId,
// OriginalVendorId = vendorId,
// Timestamp = cur,
// OriginalTimestamp = cur
// };
// expected.Add(trx);
// count++;
// if (frequency == 0)
// cur = endDate.AddDays(1);
// else if (frequency > 0)
// cur = secondaryStartDate.Value.AddDays(count * frequency);
// else
// cur = secondaryStartDate.Value.AddMonths(count * -1 * frequency);
// }
// }
// #endregion
// List<BillTransaction> reused = new List<BillTransaction>();
// while (existing.Any() && expected.Any())
// {
// var existingProjections = existing.Select(e => new
// {
// Item = e,
// Comparisons = expected.Select(x => new
// {
// Item = x,
// Days = Math.Abs((x.Timestamp - e.Timestamp).TotalDays)
// })
// });
// var bestExisting = existingProjections.OrderBy(x => x.Comparisons.Min(y => y.Days)).FirstOrDefault();
// if (bestExisting != null)
// {
// // shift existing record's timestamp to closest match in expected
// var bestMatch = bestExisting.Comparisons.OrderBy(x => x.Days).FirstOrDefault().Item;
// bestExisting.Item.Timestamp = bestMatch.Timestamp;
// bestExisting.Item.OriginalTimestamp = bestMatch.OriginalTimestamp;
// expected.Remove(bestMatch);
// existing.Remove(bestExisting.Item);
// reused.Add(bestExisting.Item);
// }
// }
// // delete unused transactions
// var complete = reused.Union(expected).Select(x => x.Id);
// _billTransactionRepository.Delete(x => x.BillId == billId && !complete.Contains(x.Id));
// //reused.ForEach(x => bill.BillTransactions.Add(x));
// expected.ForEach(x => bill.BillTransactions.Add(x));
// }
// if (bill.Amount != amount || bill.CategoryId != categoryId || bill.VendorId != vendorId)
// {
// var billTransasctions = _billTransactionRepository.GetMany(x => x.BillId == billId);
// if (billTransasctions != null)
// {
// foreach (var trx in billTransasctions)
// {
// if (bill.Amount != amount)
// {
// // only update a transaction amount if it hadn't been edited from it's original value (ie don't change modified amounts)
// if (trx.Amount == trx.OriginalAmount)
// trx.Amount = amount;
// trx.OriginalAmount = amount;
// }
// if (bill.CategoryId != categoryId)
// trx.CategoryId = categoryId;
// if (bill.VendorId != vendorId)
// trx.VendorId = vendorId;
// }
// }
// }
//}
billResult.Result.Name = name;
billResult.Result.Amount = amount;
billResult.Result.RecurrenceFrequency = frequency;
billResult.Result.StartDate = startDate;
billResult.Result.EndDate = endDate;
billResult.Result.BillGroupId = billGroupId;
billResult.Result.CategoryId = categoryId;
billResult.Result.VendorId = vendorId;
billResult.Result.StartDate2 = secondaryStartDate;
billResult.Result.EndDate2 = secondaryEndDate;
_db.Update<Bill>(billResult.Result);
result.Result = billResult.Result;
return result;
}
