Пример #1
0
        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);
        }
Пример #2
0
        // 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);
        }