public ActionResult Custom(ReportingSummaryViewModel summary) { var sales = _context.SalesTransactions .Where(s => s.Date >= summary.SearchCriteria.StartDate && s.Date <= summary.SearchCriteria.EndDate) .GroupBy(s => s.ItemId) .Select(s => new ReportingItemSummary { ItemId = s.Key, Quantity = s.Sum(i => i.Quantity), TotalPrice = s.Sum(i => i.TotalPrice) }).ToList(); var viewModel = new ReportingSummaryViewModel { ItemSummaries = sales, Items = _context.Items.ToList(), // -1 means that the reporting is a custom date range, not a single month Month = -1, SearchCriteria = new CustomReporting() { StartDate = summary.SearchCriteria.StartDate, EndDate = summary.SearchCriteria.EndDate, Item = summary.SearchCriteria.Item } }; return(View("DisplayMonthlyReport", viewModel)); }
public ActionResult DisplayMonthlyReport(int month) { //var sales = _context.SalesTransactions // .SqlQuery("SELECT * FROM SalesTransactions WHERE MONTH(Date)=" + @month) // .ToList(); // var sales = _context.SalesTransactions.ToList(); var sales = _context.SalesTransactions .Where(s => s.Date.Month == month) .GroupBy(s => s.ItemId) // Here you can select the properties you'd like the query to return // and input the results into a model/viewModel constructor. // Essentially saying "Select this info and store it in a new object" // for each item returned by the 'Where' clause above (in our case where month equals inputted month) .Select(s => new ReportingItemSummary { ItemId = s.Key, Quantity = s.Sum(i => i.Quantity), TotalPrice = s.Sum(i => i.TotalPrice) }).ToList(); var viewModel = new ReportingSummaryViewModel { ItemSummaries = sales, // The items list is passed as part of the viewModel as we need them to access // the naming, price, etc of the items within the view Items = _context.Items.ToList(), // Pass the selected month back to the view in order to use it for // the " Month = month, SearchCriteria = new CustomReporting() { StartDate = DateTime.Now, EndDate = DateTime.Now, Item = new Item() } }; return(View(viewModel)); }