public static Transaction GetTransaction(Guid id)
{
var db = new BudgetEntities();
var query = (from transaction in db.Transactions
where transaction.Guid == id
select transaction).Single();
return query;
}
public static Boolean Get(Category cat)
{
var db = new BudgetEntities();
var query = from category in db.Categories
where category.Guid == cat.Guid
select category;
return (query.Count() > 0);
}
public static Boolean Get(Account acct)
{
var db = new BudgetEntities();
var query = from account in db.Accounts
where account.Guid == acct.Guid
select account;
return (query.Count() > 0);
}
public static IQueryable<Account> GetAccounts(Boolean? active = true)
{
var db = new BudgetEntities();
var query = from account in db.Accounts
select account;
if (active != null)
query = query.Where(p => p.Active == active);
query = query.OrderBy(p => p.Name);
return query;
}
public static IQueryable<AccountBalance> GetAccountBalances(Boolean? active = true)
{
var db = new BudgetEntities();
var query = from account in db.Accounts
select new AccountBalance { Account = account, Balance = account.Transactions.Sum(p => p.Amount.Value) };
if (active != null)
query = query.Where(p => p.Account.Active == active);
query = query.OrderBy(p => p.Account.Name);
return query;
}
public static IQueryable<Payee> GetPayees(Boolean? active = true)
{
var db = new BudgetEntities();
var query = from payee in db.Payees
select payee;
if (active != null)
query = query.Where(p => p.Active == active);
query = query.OrderBy(p => p.Name);
return query;
}
public static void AddPayee(String name, Boolean active = true)
{
var db = new BudgetEntities();
var payee = new Payee();
payee.Name = name;
payee.Active = active;
payee.Guid = Guid.NewGuid();
db.Payees.Add(payee);
db.SaveChanges();
}
public static IQueryable<Category> GetCategories(int year, Boolean? active = true)
{
var db = new BudgetEntities();
var query = from category in db.Categories
where category.Year == year
select category;
if (active != null)
query = query.Where(p => p.Active == active);
query = query.OrderBy(p => p.Name);
return query;
}
public static void DeleteTransaction(IEnumerable<Transaction> transactions)
{
var db = new BudgetEntities();
foreach (var curTransaction in transactions)
{
var query = (from transaction in db.Transactions
where transaction.Guid == curTransaction.Guid
select transaction).Single();
db.Transactions.Remove(query);
}
db.SaveChanges();
}
/// <summary>
///
/// </summary>
/// <param name="year">The year to pull records from</param>
/// <param name="startMonth">Integer value corresponding to the starting month</param>
/// <param name="endMonth">Integer value corresponding to the starting month</param>
/// <param name="active">Optional value specifying whether or not to only return active categories (null implies both active and inactive)</param>
/// <returns></returns>
public static IEnumerable<CategoryStatus> GetBudgetStatus(int year, int startMonth, int endMonth, Boolean? active = true)
{
DateTime startDate = new DateTime(year, startMonth, 1);
DateTime endDate = new DateTime(year, endMonth, DateTime.DaysInMonth(year, endMonth));
var db = new BudgetEntities();
var query = from category in db.Categories
where category.Year == year
select new CategoryStatus
{
RelatedCategory = category,
AdjustedBudgetedAmount = (category.Budgeted / 12) * (endMonth - startMonth + 1),
NetGainLoss = category.TransactionCategories.Where(p => p.Transaction1.Date >= startDate && p.Transaction1.Date <= endDate).Sum(p => p.Amount).HasValue ? category.TransactionCategories.Where(p => p.Transaction1.Date >= startDate && p.Transaction1.Date <= endDate).Sum(p => p.Amount).Value : 0
};
if (active != null)
query = query.Where(p => p.RelatedCategory.Active == active);
query = query.OrderBy(p => p.RelatedCategory.Name);
return query;
}
public static IEnumerable<Transaction> GetTransactions(Category selectedCategory = null, int year = default(int))
{
var db = new BudgetEntities();
var query = from transaction in db.Transactions
select transaction;
// if a year is specified just get transactions for that year
if (year != default(int))
query = query.Where(p => p.Date.Year == year);
// if a category is specified, only get transactions for that category
if (selectedCategory != null)
query = query.Where(p => p.TransactionCategories.Any(q => q.Category == selectedCategory.Guid));
// order by
query = query.OrderBy(p => p.Date).ThenBy(q => q.Payee1.Name);
return query;
}
public Boolean Save(out List<string> errors, out List<string> warnings, Boolean ignoreWarnings = false)
{
Boolean result = false;
Validate(out errors, out warnings);
if (errors.Count == 0 && (ignoreWarnings || warnings.Count == 0))
{
try
{
var db = new BudgetEntities();
if (Mode == TransactionEntryWindow.ModeValues.Add)
{
// add the transaction
var transaction = new Transaction();
transaction.Guid = Guid.NewGuid();
transaction = PopulateObject(transaction, db);
db.Transactions.Add(transaction);
db.SaveChanges();
result = true;
}
else if (Mode == TransactionEntryWindow.ModeValues.Edit)
{
// update the transaction
var transaction = (from t in db.Transactions
where t.Guid == TransationID
select t).Single();
transaction = PopulateObject(transaction, db);
db.SaveChanges();
result = true;
}
else
{
throw new InvalidOperationException("Invalid mode specified.");
}
}
catch (Exception ex)
{
errors.Add(ex.Message);
}
}
return result;
}
private Transaction PopulateObject(Transaction transaction, BudgetEntities db)
{
transaction.Date = Date.Value;
transaction.Amount = Double.Parse(Amount, NumberStyles.Currency);
transaction.Payee = Payee.Guid;
transaction.Account = Account.Guid;
transaction.Notes = Notes;
transaction.CheckNumber = CheckNumber;
// clear transaction categories
foreach (var transactionCategory in transaction.TransactionCategories.ToList())
{
db.TransactionCategories.Remove(transactionCategory);
}
// add the transaction categories
foreach (DictionaryEntry item in Budget)
{
var transactionCategory = new TransactionCategory();
transactionCategory.Guid = Guid.NewGuid();
transactionCategory.Amount = Double.Parse(item.Value.ToString(), NumberStyles.Currency);
transactionCategory.Category = ((Category)item.Key).Guid;
transaction.TransactionCategories.Add(transactionCategory);
}
return transaction;
}
