public ActionResult MonthlySalesByDate(string _year, string _month) { //assign incoming values to the variables int year =0 , month =0 ; //check if year is null if ( string.IsNullOrWhiteSpace(_year) && _month != null) { year = DateTime.Now.Date.Year; month = Convert.ToInt32(_month.Trim()); } else { year = Convert.ToInt32(_year.Trim()); month = Convert.ToInt32(_month.Trim()); } //calculate ttal number of days in a particular month for a that year int daysInMonth = DateTime.DaysInMonth(year, month); var days = Enumerable.Range(1, daysInMonth); var query = db.Sales.Where(x => x.Date.Year == year && x.Date.Month == month).OrderBy(x => x.Date.Day).Select(g => new { Day = g.Date.Day, Total = g.GrandTotal }); var model = new SalesVM { Date = new DateTime(year, month, 1), Days = days.GroupJoin(query, d => d, q => q.Day, (d, q) => new DayTotalVM { Day = d, Total = q.Sum(x => x.Total) }).ToList() }; return View(model); }
public List<DayTotalVM> MonthlySalesByDate_forCharts(int yr, int mnt) { int year = yr; int month = mnt; int daysInMonth = DateTime.DaysInMonth(year, month); var days = Enumerable.Range(1, daysInMonth); var query = db.Sales.Where(x => x.Date.Year == year && x.Date.Month == month).Select(g => new { Day = g.Date.Day, Total = g.GrandTotal }); SalesVM model = new SalesVM { Date = new DateTime(year, month, 1), Days = days.GroupJoin(query, d => d, q => q.Day, (d, q) => new DayTotalVM { Day = d, Total = q.Sum(x => x.Total) }).ToList() }; return model.Days.ToList(); }
/* //Monthly Sales public ActionResult MonthlySalesByDate() { int year = 2014; int month = 12; var query = db.Sales.Where(x => x.Date.Year == year && x.Date.Month == month).GroupBy(x => x.Date).Select(g => new DayTotalVM { Day = g.Key.Day, Total = g.Sum(x => x.GrandTotal) }); int daysInMonth = DateTime.DaysInMonth(year, month); List<DayTotalVM> days = new List<DayTotalVM>(); for (int i = 1; i < daysInMonth + 1; i++ ) { DayTotalVM item = new DayTotalVM() { Day = i}; DayTotalVM ex = query.Where(x => x.Day == i).FirstOrDefault(); if(ex != null) { item.Total = ex.Total; } days.Add(item); } SalesVM model = new SalesVM() { Date = new DateTime(year, month,1), Days = days }; return View(model); } */ /// <summary> /// Display monthly Sales for each day in a month /// </summary> /// <returns></returns> public ActionResult MonthlySalesByDate() { int year = 2014; int month = 12; int daysInMonth = DateTime.DaysInMonth(year, month); var days = Enumerable.Range(1, daysInMonth); var query = db.Sales.Where(x => x.Date.Year == year && x.Date.Month == month).OrderBy(x=>x.Date).Select(g => new { Day = g.Date.Day, Total = g.GrandTotal }); var model = new SalesVM { Date = new DateTime(year, month, 1), Days = days.GroupJoin(query, d => d, q => q.Day, (d, q) => new DayTotalVM { Day = d, Total = q.Sum(x => x.Total) }).ToList() }; return View(model); }