public ActionResult Category(int year, int categoryID)
{
var db = new ApplicationDbContext();
var query =
from exp in db.Expenses
join m in db.Months on exp.Created.Month equals m.MonthNumber into me
from grp in me.DefaultIfEmpty()
where exp.Created.Year == year && exp.Category_CategoryID == categoryID
orderby exp.Category.Description, exp.Created.Year, exp.Created.Month
group exp by new { exp.Category, exp.Created.Year, exp.Created.Month } into c
select new
{
Category = c.Key,
MonthNumber = c.Key.Month,
TotalDue = c.Sum(exp => exp.Amount),
Count = c.Count(t => t.Created.Month != null)
};
CategoryTotal ct = new CategoryTotal();
foreach (Month m in db.Months)
{
var t = (from tot in query
where tot.MonthNumber == m.MonthNumber
select tot).ToList();
MonthTotal mt = new MonthTotal();
mt.Month = m;
if (t.ElementAtOrDefault(0) == null)
{
mt.Amount = 0;
mt.Style = "gray";
}
else
{
mt.Amount = t.First().TotalDue;
}
}
/*
var query =
from exp in db.Expenses
join m in db.Months on exp.Created.Month equals m.MonthNumber into me
from grp in me.DefaultIfEmpty()
where exp.Created.Year == year && exp.Category_CategoryID == categoryID
orderby exp.Category.Description, exp.Created.Year, exp.Created.Month
group exp by new { exp.Category, exp.Created.Year, exp.Created.Month } into c
select new
{
Category = c.Key,
MonthNumber = c.Key.Month,
TotalDue = c.Sum(exp => exp.Amount),
Count = c.Count(t => t.Created.Month != null)
};
CategoryTotal ct = new CategoryTotal();
foreach (var e in query)
{
ct.MonthTotals = new List<MonthTotal>();
ct.Category = e.Category.Category;
MonthTotal mt = new MonthTotal();
mt.Amount = e.TotalDue;
mt.Month = (Month)(from mth in db.Months
where mth.MonthNumber == e.MonthNumber
select mth).ToList().First();
ct.MonthTotals.Add(mt);
}
CategoryTotal ctfilled = new CategoryTotal();
foreach(Month m in db.Months)
{
var mt = (from mtt in ct.MonthTotals
where mtt.Month.MonthNumber == m.MonthNumber
select mtt).ToList();
if(mt.ElementAtOrDefault(0) == null)
{
MonthTotal mtnew = new MonthTotal();
mtnew.Month = m;
mtnew.Amount = 0;
ctfilled.MonthTotals.Add(mtnew);
}
else
{
ctfilled.MonthTotals.Add(mt.First());
}
}
*/
return View(ct);
}
// GET: Report
public ActionResult Monthly()
{
var db = new ApplicationDbContext();
var query =
from exp in db.Expenses
join m in db.Months on exp.Created.Month equals m.MonthNumber into me
from grp in me.DefaultIfEmpty()
where exp.Created.Year == DateTime.Now.Year
orderby exp.Category.Description, exp.Created.Year, exp.Created.Month
group exp by new { exp.Category, exp.Created.Year, exp.Created.Month } into c
select new
{
Category = c.Key,
MonthNumber = c.Key.Month,
TotalDue = c.Sum(exp => exp.Amount),
Count = c.Count(t=>t.Created.Month != null)
};
#region oldcode
/*
var expenses = from exp in db.Expenses
join m in db.Months on exp.Created.Month equals m.MonthNumber into me
from grp in me.DefaultIfEmpty()
select new
{
MonthNumber = grp.MonthNumber,
Expense = exp
};
*/
/*
var expenses =
from m in db.Months
join exp in db.Expenses on m.MonthNumber equals exp.Created.Month into me
from grp in me.DefaultIfEmpty()
select new
{
Expense = grp,
MonthNumber = m.MonthNumber
};
var query =
from exp in expenses
orderby exp.Expense.Category.Description, exp.Expense.Created.Year, exp.Expense.Created.Month
group exp by new { exp.Expense.Category, exp.Expense.Created.Year, exp.Expense.Created.Month } into c
select new
{
Category = c.Key,
MonthNumber = c.Key.Month,
TotalDue = c.Sum(exp => exp.Expense.Amount)
};
*/
#endregion
List<CategoryTotal> totals = new List<CategoryTotal>();
foreach(ExpenseCategory ec in db.Categories)
{
CategoryTotal ct = new CategoryTotal();
ct.Category = ec;
ct.MonthTotals = new List<MonthTotal>();
foreach(Month m in db.Months)
{
var t = (from tot in query
where tot.Category.Category.CategoryID == ec.CategoryID && tot.MonthNumber == m.MonthNumber
select tot).ToList();
MonthTotal mt = new MonthTotal();
mt.Month = m;
if (t.ElementAtOrDefault(0) == null)
{
mt.Amount = 0;
mt.Style = "gray";
}
else
{
mt.Amount = t.First().TotalDue;
}
ct.MonthTotals.Add(mt);
}
totals.Add(ct);
}
#region older code
/*
foreach(var e in query)
{
if(prevCategoryID != e.Category.Category.CategoryID)
{
CategoryTotal ct = new CategoryTotal();
ct.MonthTotals = new List<MonthTotal>();
ct.Category = e.Category.Category;
MonthTotal mt = new MonthTotal();
mt.Amount = e.TotalDue;
mt.Month = (Month)(from m in db.Months
where m.MonthNumber == e.MonthNumber
select m).ToList().First();
ct.MonthTotals.Add(mt);
totals.Add(ct);
}
else
{
MonthTotal mt = new MonthTotal();
mt.Amount = e.TotalDue;
mt.Month = (Month)(from m in db.Months
where m.MonthNumber == e.MonthNumber
select m).ToList().First();
totals.Last().MonthTotals.Add(mt);
//totals.Add(ct);
}
prevCategoryID = e.Category.Category.CategoryID;
}
List<CategoryTotal> sortedtotals = new List<CategoryTotal>();
foreach(CategoryTotal ct in totals)
{
CategoryTotal ctnew = new CategoryTotal();
ctnew.MonthTotals = new List<MonthTotal>();
ctnew.Category = ct.Category;
foreach(Month m in db.Months)
{
var mt = (from mtt in ct.MonthTotals
where mtt.Month.MonthNumber == m.MonthNumber
select mtt).ToList();
if(mt.ElementAtOrDefault(0) == null)
{
MonthTotal mtempty = new MonthTotal();
mtempty.Month = m;
mtempty.Amount = 0;
ctnew.MonthTotals.Add(mtempty);
}
else
{
ctnew.MonthTotals.Add(mt.First());
}
}
sortedtotals.Add(ctnew);
}
foreach(ExpenseCategory ec in db.Categories)
{
var c = (from cat in sortedtotals
where cat.Category.CategoryID == ec.CategoryID
select cat).ToList();
if(c.ElementAtOrDefault(0) == null)
{
CategoryTotal ct = new CategoryTotal();
ct.Category = ec;
ct.MonthTotals = new List<MonthTotal>();
foreach (Month m in db.Months)
{
MonthTotal mtempty = new MonthTotal();
mtempty.Month = m;
mtempty.Amount = 0;
ct.MonthTotals.Add(mtempty);
}
sortedtotals.Add(ct);
}
}
*/
#endregion
ViewBag.Months = (List<Month>)(from m in db.Months
select m).ToList<Month>();
ViewBag.Year = 2015;
return View(totals);
}