public void addCats2Display()
{
CatDisplay.Clear();
string category = "";
using (var conn = new NpgsqlConnection(buildConnString()))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "select distinct category from categories where business_id = '" + ((Milestone1.MainWindow.Business)businessGrid.SelectedValue).bid.ToString() + "';";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
category += reader.GetString(0) + "\n";
}
CatDisplay.Text = category;
}
}
conn.Close();
}
}
public CatDisplayVM CategoryBudget(DateTime startDate, DateTime endDate)
{
int? HhId = Convert.ToInt32(User.Identity.GetHouseholdId());
var hh = db.Households.Find(Convert.ToInt32(User.Identity.GetHouseholdId()));
var deals = hh.BankAccts.SelectMany(a => a.Deals).OrderByDescending(a => a.DealDate).ToList();
var categories = db.Categories.Where(c => c.HouseholdId == HhId).OrderBy(c => c.IsExpense).ThenBy(c => c.Name).ToList();
var catDVM = new CatDisplayVM();
decimal totalActual = 0;
decimal totalBudget = 0;
decimal totalReconciled = 0;
foreach (var cat in categories)
{
if (cat.IsExpense)
break;
var cVM = new CatDisplay();
cVM.Id = cat.Id;
cVM.Name = cat.Name;
cVM.Budget = cat.BudgetAmount;
foreach (var d in deals)
{
if (d.DealDate >= startDate && d.DealDate <= endDate)
{
if (d.CategoryId == cat.Id)
{
cVM.Actual += d.Amount;
if (d.Reconciled)
cVM.Reconciled += d.Amount;
}
}
}
if ((endDate - startDate).TotalDays > 80)
{
cVM.Actual /= 3;
cVM.Reconciled /= 3;
}
cVM.Unreconciled = cVM.Actual - cVM.Reconciled;
cVM.Variance = cVM.Actual - cat.BudgetAmount;
catDVM.IncCats.Add(cVM);
totalActual += cVM.Actual;
totalBudget += cVM.Budget;
totalReconciled += cVM.Reconciled;
}
catDVM.IncTotal.Name = "Total Income";
catDVM.IncTotal.Reconciled = totalReconciled;
catDVM.IncTotal.Unreconciled = totalActual - totalReconciled;
catDVM.IncTotal.Actual = totalActual;
catDVM.IncTotal.Budget = totalBudget;
catDVM.IncTotal.Variance = totalActual - totalBudget;
catDVM.IncTotal.IsTotal = true;
totalActual = totalBudget = totalReconciled = 0;
foreach (var cat in categories)
{
if (!cat.IsExpense)
continue;
var cVM = new CatDisplay();
cVM.Id = cat.Id;
cVM.Name = cat.Name;
cVM.Budget = cat.BudgetAmount;
foreach (var d in deals)
{
if (d.DealDate >= startDate && d.DealDate <= endDate)
{
if (d.CategoryId == cat.Id)
{
cVM.Actual -= d.Amount;
if (d.Reconciled)
cVM.Reconciled -= d.Amount;
}
}
}
if ((endDate - startDate).TotalDays > 80)
{
cVM.Actual /= 3;
cVM.Reconciled /= 3;
}
cVM.Budget *= -1;
cVM.Unreconciled = cVM.Actual - cVM.Reconciled;
cVM.Variance = cVM.Actual - cat.BudgetAmount;
catDVM.ExpCats.Add(cVM);
totalActual += cVM.Actual;
totalBudget += cVM.Budget;
totalReconciled += cVM.Reconciled;
}
catDVM.ExpTotal.Name = "Total Expense";
catDVM.ExpTotal.Reconciled = totalReconciled;
catDVM.ExpTotal.Unreconciled = totalActual - totalReconciled;
catDVM.ExpTotal.Actual = totalActual;
catDVM.ExpTotal.Budget = totalBudget;
catDVM.ExpTotal.Variance = totalActual - totalBudget;
catDVM.ExpTotal.IsTotal = true;
catDVM.NetTotal.Name = "Combined Total";
catDVM.NetTotal.Reconciled = catDVM.IncTotal.Reconciled + catDVM.ExpTotal.Reconciled;
catDVM.NetTotal.Unreconciled = catDVM.IncTotal.Unreconciled + catDVM.ExpTotal.Unreconciled;
catDVM.NetTotal.Actual = catDVM.IncTotal.Actual + catDVM.ExpTotal.Actual;
catDVM.NetTotal.Budget = catDVM.IncTotal.Budget + catDVM.ExpTotal.Budget;
catDVM.NetTotal.Variance = catDVM.IncTotal.Variance + catDVM.ExpTotal.Variance;
catDVM.NetTotal.IsTotal = true;
return catDVM;
}
