public ActionResult ReviewReportB(SortReport SelectedSort, String ProfitMin, String ProfitMax, String PriceMin, String PriceMax) { //initialize booksreport viewmodel List<OrderReportVM> allBooksReports = new List<OrderReportVM>(); List<OrderDetail> OrdersReport = new List<OrderDetail>(); var query = from r in _db.OrderDetails select r; if (!string.IsNullOrEmpty(ProfitMin)) { decimal decProfitMin; try { decProfitMin = Convert.ToDecimal(ProfitMin); } catch { //adding error message for viewbag @ViewBag.Message = ProfitMin + "is not a valid number. Please try again."; } decProfitMin = Convert.ToDecimal(ProfitMin); //query = query.Where(r => r.Price >= decProfitMin); query = query.Where(r => (r.Book.AvgSalesPrice - r.Book.AvgBookCost) >= decProfitMin); } if (!string.IsNullOrEmpty(ProfitMax)) { decimal decPMax; try { decPMax = Convert.ToDecimal(ProfitMax); } catch { //adding error message for viewbag @ViewBag.Message = ProfitMin + "is not a valid number. Please try again."; } //query = query.Where(r => r.Price >= decPMax); decPMax = Convert.ToDecimal(ProfitMax); query = query.Where(r => (r.Book.AvgSalesPrice - r.Book.AvgBookCost) <= decPMax); } if (!string.IsNullOrEmpty(PriceMin)) { decimal decProfitMin; try { decProfitMin = Convert.ToDecimal(PriceMin); } catch { //adding error message for viewbag @ViewBag.Message = ProfitMin + "is not a valid number. Please try again."; } //query = query.Where(r => r.Price >= decProfitMin); decProfitMin = Convert.ToDecimal(PriceMin); query = query.Where(r => r.Price >= decProfitMin); } if (!string.IsNullOrEmpty(PriceMax)) { decimal decPMax; try { decPMax = Convert.ToDecimal(PriceMax); } catch { //adding error message for viewbag @ViewBag.Message = ProfitMin + "is not a valid number. Please try again."; } //query = query.Where(r => r.Price >= decPMax); decPMax = Convert.ToDecimal(PriceMax); query = query.Where(r => r.Price <= decPMax); } //query = query.Include(o => o.Book).ThenInclude(o => o.Procurements).Include(o => o.Order).ThenInclude(o => o.Customer); OrdersReport = query.ToList(); List<Order> allOrders = new List<Order>(); var orderquery = _db.Orders.Include(o => o.Customer).Include(o => o.OrderDetails).ThenInclude(o => o.Book).ThenInclude(o => o.Procurements); allOrders = orderquery.ToList(); foreach (Order order in allOrders) { OrderReportVM orvm = new OrderReportVM(); List<string> ListBookTandQ = new List<string>(); decimal OrderRev = 0; decimal OrderCost = 0; decimal OrderProcQuantity = 0; int OrderQuantity = 0; string BookTitle; int BookQ; string TandQ; foreach (OrderDetail od in OrdersReport) { int intTotalProc = od.Book.InitialInventory; decimal decTotalCost = od.Book.InitialCost * od.Book.InitialInventory; foreach (Procurement pro in od.Book.Procurements) { intTotalProc += pro.Quantity; decTotalCost += pro.Price * pro.Quantity; } if (od.Order.OrderID == order.OrderID) { OrderRev += od.ExtendedPrice; BookTitle = od.Book.Title; BookQ = od.Quantity; TandQ = BookTitle + " (" + BookQ.ToString() + ") "; ListBookTandQ.Add(TandQ); orvm.CustomerName = od.Order.Customer.FirstName + ' ' + od.Order.Customer.LastName; OrderCost += decTotalCost; OrderProcQuantity += intTotalProc; orvm.OrderDate = od.Order.OrderDate; OrderQuantity += od.Quantity; } } orvm.Payment = order.Payment; orvm.OrderNumber = order.OrderNumber; if (OrderProcQuantity == 0) { OrderProcQuantity = 1; } orvm.OrderCostAvg = (OrderCost / OrderProcQuantity); decimal OrderCostAvgTimesOrderQuantity = (OrderCost / OrderProcQuantity)*OrderQuantity; orvm.OrderCost = OrderCostAvgTimesOrderQuantity; orvm.ProfitMargin = (OrderRev - OrderCostAvgTimesOrderQuantity); orvm.BookTandQ = ListBookTandQ; orvm.OrderTotal = OrderRev; orvm.ProfitMarginMinusAvg = OrderRev - (OrderCost / OrderProcQuantity); if (orvm.OrderTotal != 0) { allBooksReports.Add(orvm); } } ViewBag.SelectedRecords = allBooksReports.Count(); switch (SelectedSort) { case SortReport.MostRecent: return View("ReviewReportB", allBooksReports.OrderByDescending(r => r.OrderNumber)); case SortReport.PriceAsc: return View("ReviewReportB", allBooksReports.OrderBy(r => r.OrderTotal)); case SortReport.PriceDesc: return View("ReviewReportB", allBooksReports.OrderByDescending(r => r.OrderTotal)); case SortReport.ProfitMarginAsc: return View("ReviewReportB", allBooksReports.OrderBy(r => r.ProfitMargin)); case SortReport.ProfitMarginDesc: return View("ReviewReportB", allBooksReports.OrderByDescending(r => r.ProfitMargin)); } return View("ReviewReportB", allBooksReports); }
public async Task<ActionResult> ReviewReportC(SortReport SelectedSort) { List<CustomerReportVM> customerReportVMs = new List<CustomerReportVM>(); List<OrderDetail> CustomersReports = new List<OrderDetail>(); var query = _db.OrderDetails.Include(o => o.Book).ThenInclude(o => o.Procurements).Include(o => o.Order).ThenInclude(o => o.Customer); CustomersReports = query.ToList(); List<AppUser> customers = new List<AppUser>(); List<AppUser> members = new List<AppUser>(); List<AppUser> nonMembers = new List<AppUser>(); foreach (AppUser user in _userManager.Users.Include(User => User.ReviewsApproved).Include(User => User.ReviewsRejected)) { var cuslist = await _userManager.IsInRoleAsync(user, "Customer") ? members : nonMembers; cuslist.Add(user); } RoleEditModel re = new RoleEditModel(); re.Members = members; foreach (AppUser user in members) { CustomerReportVM crvm = new CustomerReportVM(); List<string> ListBookTandQ = new List<string>(); List<string> ListOrderNumbers = new List<string>(); decimal CustomerProfit = 0; decimal CustomerCost = 0; string BookTitle; int BookQ; string TandQ; string strOrderNum; foreach (OrderDetail od in CustomersReports) { if (od.Order.Customer.Id == user.Id) { CustomerProfit += od.ExtendedPrice; CustomerCost += (Decimal)od.Book.AvgBookCost; strOrderNum = "{" + od.Order.OrderNumber.ToString() + "} "; BookTitle = od.Book.Title; BookQ = od.Quantity; TandQ = BookTitle + " (" + BookQ.ToString() + ") "; ListBookTandQ.Add(TandQ); ListOrderNumbers.Add(strOrderNum); crvm.CustomerName = od.Order.Customer.FirstName + ' ' + od.Order.Customer.LastName; } } if (CustomerCost > 0 && CustomerProfit > 0) { crvm.OrderNumbers = ListOrderNumbers; crvm.ProfitMargin = (CustomerProfit - CustomerCost); crvm.BookTandQ = ListBookTandQ; crvm.CustomerTotal = CustomerProfit; crvm.CustomerBooksCost = CustomerCost; customerReportVMs.Add(crvm); } } ViewBag.SelectedRecords = customerReportVMs.Count(); switch (SelectedSort) { case SortReport.PriceAsc: return View("ReviewReportC", customerReportVMs.OrderBy(r => r.CustomerTotal)); case SortReport.PriceDesc: return View("ReviewReportC", customerReportVMs.OrderByDescending(r => r.CustomerTotal)); case SortReport.ProfitMarginAsc: return View("ReviewReportC", customerReportVMs.OrderBy(r => r.ProfitMargin)); case SortReport.ProfitMarginDesc: return View("ReviewReportC", customerReportVMs.OrderByDescending(r => r.ProfitMargin)); } return View("ReviewReportC", customerReportVMs); }