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); }