public ActionResult ShowLargestOrder()
{
using (MainContext db = new MainContext())
{
BIDashboardVM data = new BIDashboardVM();
return(View(data));
}
}
// GET: BIDashboard
public ActionResult Index()
{
//initialize db connection to maincontext
using (MainContext db = new MainContext())
{
//Linq query to join customer and order table
var usersWithCount = (from c in db.Customers
join o in db.Orders on c.CustomerId equals o.CustomerId
into result
from sub in result.DefaultIfEmpty()
select new ViewModels.CustomerOrder
{
OrderId = sub != null ? sub.OrderId : (int?)null,
CustomerId = c.CustomerId,
}
) // Here we have the left join result.
.GroupBy(g => g.CustomerId, (k, orders) => new
{
CustomerId = k,
//do count on number of orders per customer
OrderCount = orders.Count(t => t.OrderId != null),
//order the query result from highest number of orders to lowest.
}).OrderByDescending(t => t.OrderCount).ToList();
//select the top 3 customers from query results
var top3Customers = usersWithCount.Take(3);
//initialize new BIDashboard ViewModel
BIDashboardVM data = new BIDashboardVM();
//populate the view models with customer data from top 3 performers
foreach (var item in top3Customers)
{
int cid = item.CustomerId;
var customer = db.Customers.Find(cid);
data.customerList.Add(customer);
db.SaveChanges();
}
var allOrders = (from o in db.Orders
select new OrderValue
{
OrderId = o.OrderId,
Amount = o.TotalAmount,
customerId = o.CustomerId
}
).OrderByDescending(t => t.Amount).ToList();
var top3OrderValues = allOrders.Take(3);
//Get top 3 values from order table save to orderLists
foreach (var item in top3OrderValues)
{
decimal value = item.Amount;
int cid = item.customerId;
int? oid = item.OrderId;
var order = db.Orders.Find(oid);
data.ordersLists.Add(order);
db.SaveChanges();
}
foreach (var item in allOrders)
{
int?oid = item.OrderId;
var order = db.Orders.Find(oid);
data.allOrderList.Add(order);
db.SaveChanges();
}
//Load BIDashboardVM with all orders data.
//OrderbyDecending(o => o.OrderId).ToList();
//pass ViewModel data to the view
return(View(data));
}
}
