public ActionResult EmployeeAverageSales(int EmployeeID, DateTime startDate, DateTime endDate)
{
var northwind = new NorthwindEntities();
var result = northwind.MonthlySalesByEmployee(EmployeeID).Where(d => d.Date >= startDate && d.Date <= endDate);
return Json(result, JsonRequestBehavior.AllowGet);
}
public ActionResult Orders_Destroy([DataSourceRequest]DataSourceRequest request, OrderViewModel order)
{
if (ModelState.IsValid)
{
using (var northwind = new NorthwindEntities())
{
List<Order_Detail> details = northwind.Order_Details.Where(od => od.OrderID == order.OrderID).ToList();
foreach (var orderDetail in details)
{
northwind.Order_Details.Remove(orderDetail);
}
var entity = new Order
{
CustomerID = order.CustomerID,
OrderID = order.OrderID,
EmployeeID = order.EmployeeID,
OrderDate = order.OrderDate,
ShipCountry = order.ShipCountry,
ShipVia = order.ShipVia,
ShippedDate = order.ShippedDate,
ShipName = order.ShipName,
ShipAddress = order.ShipAddress,
ShipCity = order.ShipCity,
ShipPostalCode = order.ShipPostalCode
};
northwind.Orders.Attach(entity);
northwind.Orders.Remove(entity);
northwind.SaveChanges();
}
}
return Json(new[] { order }.ToDataSourceResult(request, ModelState));
}
public ActionResult Orders_Create([DataSourceRequest]DataSourceRequest request, OrderViewModel order)
{
if (ModelState.IsValid)
{
using (var northwind = new NorthwindEntities())
{
var entity = new Order
{
CustomerID = order.CustomerID,
EmployeeID = order.EmployeeID,
OrderDate = order.OrderDate,
ShipCountry = order.ShipCountry,
ShipVia = order.ShipVia,
ShippedDate = order.ShippedDate,
ShipName = order.ShipName,
ShipAddress = order.ShipAddress,
ShipCity = order.ShipCity,
ShipPostalCode = order.ShipPostalCode
};
northwind.Orders.Add(entity);
northwind.SaveChanges();
order.OrderID = entity.OrderID;
}
}
return Json(new[] { order }.ToDataSourceResult(request, ModelState));
}
public ActionResult OrderDetails_Update([DataSourceRequest]DataSourceRequest request, OrderDetailViewModel order)
{
if (ModelState.IsValid)
{
using (var northwind = new NorthwindEntities())
{
var entity = northwind.Order_Details.FirstOrDefault(detail => detail.OrderID == order.OrderID && detail.ProductID == order.ProductID);
if (entity == null)
{
string errorMessage = string.Format("Cannot update record with ProductID:{0} and OrderID:{1} as it's not available in current order.", order.OrderID, order.ProductID);
ModelState.AddModelError("", errorMessage);
}
else
{
entity.UnitPrice = order.UnitPrice;
entity.Quantity = (short)order.Quantity;
entity.Discount = order.Discount;
northwind.SaveChanges();
}
}
}
return Json(new[] { order }.ToDataSourceResult(request, ModelState));
}
public ActionResult Orders_Update([DataSourceRequest]DataSourceRequest request, OrderViewModel order)
{
if (ModelState.IsValid)
{
using (var northwind = new NorthwindEntities())
{
var entity = northwind.Orders.FirstOrDefault(o => o.OrderID == order.OrderID);
if (entity == null)
{
string errorMessage = string.Format("Cannot update record with OrderID:{0} as it's not available.", order.OrderID);
ModelState.AddModelError("", errorMessage);
}
else
{
entity.CustomerID = order.CustomerID;
entity.EmployeeID = order.EmployeeID;
entity.OrderDate = order.OrderDate;
entity.ShipCountry = order.ShipCountry;
entity.ShipVia = order.ShipVia;
entity.ShippedDate = order.ShippedDate;
entity.ShipName = order.ShipName;
entity.ShipAddress = order.ShipAddress;
entity.ShipCity = order.ShipCity;
entity.ShipPostalCode = order.ShipPostalCode;
northwind.Orders.Attach(entity);
northwind.Entry(entity).State = EntityState.Modified;
northwind.SaveChanges();
}
}
}
return Json(new[] { order }.ToDataSourceResult(request, ModelState));
}
public ActionResult TopSellingProducts(string Country, DateTime FromDate, DateTime ToDate)
{
var northwind = new NorthwindEntities();
var result = northwind.CountryTopProducts(Country, FromDate.ToString("yyyyMMdd"), ToDate.ToString("yyyyMMdd"));
return Json(result, JsonRequestBehavior.AllowGet);
}
public ActionResult OrderDetails_Create([DataSourceRequest]DataSourceRequest request, OrderDetailViewModel order, int ParentID)
{
if (ModelState.IsValid)
{
using (var northwind = new NorthwindEntities())
{
var existingEntity = northwind.Order_Details.FirstOrDefault(detail => detail.OrderID == ParentID && detail.ProductID == order.ProductID);
if (existingEntity != null)
{
string errorMessage = string.Format("Record with ProductID:{0} and OrderID:{1} already exists in current order.", order.OrderID, order.ProductID);
ModelState.AddModelError("", errorMessage);
}
else
{
var entity = new Order_Detail
{
OrderID = ParentID,
ProductID = order.ProductID,
UnitPrice = order.UnitPrice,
Quantity = (short)order.Quantity,
Discount = order.Discount
};
northwind.Order_Details.Add(entity);
northwind.SaveChanges();
order.OrderID = entity.OrderID;
}
}
}
return Json(new[] { order }.ToDataSourceResult(request, ModelState));
}
public static IEnumerable<EmployeeViewModel> GetEmployees()
{
var employees = new NorthwindEntities().Employees.Select(e => new EmployeeViewModel
{
EmployeeID = e.EmployeeID,
EmployeeName = e.FirstName + " " + e.LastName
}).OrderBy(e => e.EmployeeName);
return employees;
}
public ActionResult CountryOrders(string Country, DateTime FromDate, DateTime ToDate)
{
var northwind = new NorthwindEntities();
IQueryable<Order> data = northwind.Orders.Where(o => o.OrderDate >= FromDate && o.OrderDate <= ToDate && o.ShipCountry == Country);
var result = from o in data
group o by o.OrderDate into g
select new { Date = g.Key, Value = g.Count() };
return Json(result, JsonRequestBehavior.AllowGet);
}
public ActionResult CountryCompanies(string Country)
{
var northwind = new NorthwindEntities();
var companies = northwind.Customers.Select(customer => new CustomerViewModel
{
CompanyName = customer.CompanyName,
Country = customer.Country
}).Where(x => x.Country == Country);
return Json(companies, JsonRequestBehavior.AllowGet);
}
public List<CountryOrderViewModel> GetCountryOrders(string Country, DateTime FromDate, DateTime ToDate)
{
var northwind = new NorthwindEntities();
IQueryable<Order> data = northwind.Orders.Where(o => o.OrderDate >= FromDate && o.OrderDate <= ToDate && o.ShipCountry == Country);
var result = from o in data
group o by o.OrderDate into g
select new CountryOrderViewModel{ Date = g.Key, Value = g.Count() };
return result.ToList();
}
public IEnumerable<ProductViewModel> GetProducts()
{
var products = new NorthwindEntities().Products.Select(e => new ProductViewModel
{
ProductID = e.ProductID,
ProductName = e.ProductName
}).OrderBy(e => e.ProductName);
return products;
}
public ActionResult TopSellingProducts(string Country, DateTime FromDate, DateTime ToDate)
{
var northwind = new NorthwindEntities();
var topSellers = (from top in
(from allSales in
(from o in northwind.Orders
join od in northwind.Order_Details on o.OrderID equals od.OrderID
where o.OrderDate >= FromDate && o.OrderDate <= ToDate && o.ShipCountry == Country
select new
{
ProductID = od.ProductID,
Quantity = od.Quantity,
Date = o.OrderDate,
}).AsEnumerable()
group allSales by new { allSales.ProductID, Date = new DateTime(allSales.Date.Value.Year, allSales.Date.Value.Month, 1) } into g
select new
{
ProductID = g.Key.ProductID,
Quantity = g.Sum(x => x.Quantity),
Date = g.Key.Date
})
group top by top.ProductID into g
orderby g.Sum(x => x.Quantity) descending
select new
{
ProductID = g.FirstOrDefault().ProductID
}).Take(5);
var all = (from allSales in
(from o in northwind.Orders
join od in northwind.Order_Details on o.OrderID equals od.OrderID
where o.OrderDate >= FromDate && o.OrderDate <= ToDate && o.ShipCountry == Country
select new
{
ProductID = od.ProductID,
Quantity = od.Quantity,
Date = o.OrderDate,
}).AsEnumerable()
group allSales by new { allSales.ProductID, Date = new DateTime(allSales.Date.Value.Year, allSales.Date.Value.Month, 1) } into g
where topSellers.Contains(new { ProductID = g.Key.ProductID })
select new
{
ProductID = g.Key.ProductID,
Quantity = g.Sum(x => x.Quantity),
Date = g.Key.Date
});
var result = (from s in all
join p in northwind.Products on s.ProductID equals p.ProductID
select new
{
ProductName = p.ProductName,
Date = s.Date,
Quantity = s.Quantity
});
return Json(result, JsonRequestBehavior.AllowGet);
}
public List<CustomerViewModel> GetCountryCompanies(string Country)
{
var northwind = new NorthwindEntities();
var companies = northwind.Customers.Select(customer => new CustomerViewModel
{
CompanyName = customer.CompanyName,
Country = customer.Country
}).Where(x => x.Country == Country);
return companies.ToList();
}
public ActionResult ProductsSalesByMonth(int ProductID)
{
var northwind = new NorthwindEntities();
var result = from o in northwind.Orders
join od in northwind.Order_Details on o.OrderID equals od.OrderID
where od.ProductID == ProductID
select new {
Date = o.OrderDate,
Quantity = od.Quantity
};
return Json(result, JsonRequestBehavior.AllowGet);
}
private static IEnumerable<OrderDetailViewModel> GetOrderDetails()
{
var northwind = new NorthwindEntities();
var order_details = northwind.Order_Details.Select(od => new OrderDetailViewModel
{
OrderID = od.OrderID,
ProductID = od.ProductID,
UnitPrice = od.UnitPrice,
Quantity = od.Quantity,
Discount = od.Discount
});
return order_details;
}
public int GetCountryOrdersTotal(string Country, DateTime FromDate, DateTime ToDate)
{
var northwind = new NorthwindEntities();
IQueryable<Order> data = northwind.Orders.Where(o => o.OrderDate >= FromDate && o.OrderDate <= ToDate && o.ShipCountry == Country);
var result = from o in data
group o by o.OrderDate into g
select new { Date = g.Key, Value = g.Count() };
int total = 0;
if (result.Count() > 0)
{
total = result.Sum(x => x.Value);
}
return total;
}
public ActionResult EmployeesList([DataSourceRequest]DataSourceRequest request)
{
var employees = new NorthwindEntities().Employees.Select(e => new EmployeeViewModel
{
EmployeeID = e.EmployeeID,
FirstName = e.FirstName,
LastName = e.LastName,
EmployeeName = e.FirstName + " " + e.LastName,
Notes = e.Notes,
Title = e.Title,
HomePhone = e.HomePhone
}).OrderBy(e => e.FirstName);
return Json(employees.ToDataSourceResult(request, ModelState), JsonRequestBehavior.AllowGet);
}
public List<ProductDetailsViewModel> GetProductDetails()
{
var northwind = new NorthwindEntities();
List<Product> products = northwind.Products.ToList();
List<ProductDetailsViewModel> result = new List<ProductDetailsViewModel>();
foreach (Product product in products)
{
ProductDetailsViewModel productDetails = new ProductDetailsViewModel
{
value = product.ProductID,
text = product.ProductName
};
result.Add(productDetails);
}
return result;
}
public List<EmployeeDetailViewModel> GetEmployeeDetails()
{
var northwind = new NorthwindEntities();
List<Employee> employees = northwind.Employees.ToList();
List<EmployeeDetailViewModel> result = new List<EmployeeDetailViewModel>();
foreach (Employee emp in employees)
{
EmployeeDetailViewModel employeeDetails = new EmployeeDetailViewModel
{
value = emp.EmployeeID,
text = emp.FirstName + " " + emp.LastName
};
result.Add(employeeDetails);
}
return result;
}
public ActionResult EmployeeSales([DataSourceRequest]DataSourceRequest request)
{
var northwind = new NorthwindEntities();
var data = northwind.Orders.Join(northwind.Customers, c => c.CustomerID, o => o.CustomerID, (o, c) => new { Order = o, Customer = c }).ToList();
var sales = data.Select(o => new SaleViewModel
{
SaleID = o.Order.OrderID,
EmployeeID = o.Order.EmployeeID,
Title = o.Customer.CompanyName,
Start = DateTime.SpecifyKind((DateTime)o.Order.OrderDate, DateTimeKind.Utc),
End = DateTime.SpecifyKind((DateTime)o.Order.OrderDate, DateTimeKind.Utc).AddHours(1),
IsAllDay = false
});
return Json(sales.ToDataSourceResult(request, ModelState), JsonRequestBehavior.AllowGet);
}
public ActionResult EmployeeQuarterSales(int EmployeeID, DateTime endDate)
{
DateTime startDate = endDate.AddMonths(-3);
var northwind = new NorthwindEntities();
var sales = northwind.Orders.Where(w => w.EmployeeID == EmployeeID)
.Join(northwind.Order_Details, orders => orders.OrderID, orderDetails => orderDetails.OrderID, (orders, orderDetails) => new { Order = orders, OrderDetails = orderDetails })
.Where(d => d.Order.OrderDate >= startDate && d.Order.OrderDate <= endDate).ToList()
.Select(o => new QuarterToDateSalesViewModel
{
Current = (o.OrderDetails.Quantity * o.OrderDetails.UnitPrice) - (o.OrderDetails.Quantity * o.OrderDetails.UnitPrice * (decimal)o.OrderDetails.Discount)
});
//TODO: Generate the target based on team's average sales
var result = new List<QuarterToDateSalesViewModel>() {
new QuarterToDateSalesViewModel {Current = sales.Sum(s=>s.Current), Target = 15000, OrderDate = endDate}
};
return Json(result, JsonRequestBehavior.AllowGet);
}
private static IQueryable<ProductViewModel> GetProducts()
{
var northwind = new NorthwindEntities();
var products = northwind.Products.Select(product => new ProductViewModel
{
ProductID = product.ProductID,
ProductName = product.ProductName,
Category = new CategoryViewModel() {
CategoryID = product.Category.CategoryID,
CategoryName = product.Category.CategoryName
},
UnitsInStock = (short)product.UnitsInStock,
UnitsOnOrder = (short)product.UnitsOnOrder,
ReorderLevel = (short)product.ReorderLevel,
QuantityPerUnit = product.QuantityPerUnit
});
return products;
}
public ActionResult MarketShareByCountry(string Country, DateTime FromDate, DateTime ToDate)
{
var northwind = new NorthwindEntities();
var allSales = (from o in northwind.Orders
join od in northwind.Order_Details on o.OrderID equals od.OrderID
where o.OrderDate >= FromDate && o.OrderDate <= ToDate
select new
{
Country = o.ShipCountry,
Sales = od.Quantity * od.UnitPrice
}).AsEnumerable();
return Json(new [] {
new { Country = "All", Sales = (decimal?)allSales.Sum(x => x.Sales) ?? 0},
new { Country = Country, Sales = (decimal?)allSales.Where(w=>w.Country == Country).Sum(s => s.Sales) ?? 0}
}, JsonRequestBehavior.AllowGet);
}
// GET api/productsandorders/GetOrders
public IQueryable<OrderViewModel> GetOrders()
{
var northwind = new NorthwindEntities();
var orders = northwind.Orders.Select(order => new OrderViewModel
{
CustomerID = order.CustomerID,
OrderID = order.OrderID,
EmployeeID = order.EmployeeID,
OrderDate = order.OrderDate,
ShipCountry = order.ShipCountry,
ShipVia = order.ShipVia,
ShippedDate = order.ShippedDate,
ShipName = order.ShipName,
ShipAddress = order.ShipAddress,
ShipCity = order.ShipCity,
ShipPostalCode = order.ShipPostalCode
});
return orders;
}
public ActionResult EmployeeAndTeamSales(int EmployeeID, DateTime startDate, DateTime endDate)
{
var northwind = new NorthwindEntities();
var q1 = (from o in northwind.Orders
join od in northwind.Order_Details on o.OrderID equals od.OrderID
where o.OrderDate >= startDate && o.OrderDate <= endDate
select new
{
OrderID = o.OrderID,
EmployeeID = o.EmployeeID,
Date = o.OrderDate,
Sales = od.Quantity * od.UnitPrice
}).AsEnumerable();
var q2 = (from allSales in q1
group allSales by allSales.OrderID into g
select new
{
OrderID = g.Key,
EmployeeID = g.FirstOrDefault().EmployeeID,
Sales = g.Sum(x => x.Sales),
Date = new DateTime(g.FirstOrDefault().Date.Value.Year, g.FirstOrDefault().Date.Value.Month, 1),
});
var q3 = (from groupedSales in q2
group groupedSales by new { groupedSales.EmployeeID, groupedSales.Date } into gs
select new
{
EmployeeID = gs.FirstOrDefault().EmployeeID,
Date = gs.Key.Date,
Sales = gs.Sum(x => x.Sales)
});
var result = (from totalSales in q3
group totalSales by totalSales.Date into gs
select new
{
Date = gs.Key,
TotalSales = gs.Sum(x => x.Sales),
EmployeeSales = (int?)gs.Where(z => z.EmployeeID == EmployeeID).Sum(z => z.Sales) ?? 0
}
);
return Json(result, JsonRequestBehavior.AllowGet);
}
public ActionResult Customers_Destroy([DataSourceRequest]DataSourceRequest request, CustomerViewModel customer)
{
if (ModelState.IsValid)
{
using (var northwind = new NorthwindEntities())
{
var entity = new Customer
{
CustomerID = customer.CustomerID,
CompanyName = customer.CompanyName,
Country = customer.Country,
City = customer.City,
ContactName = customer.ContactName,
Phone = customer.Phone,
};
northwind.Customers.Attach(entity);
northwind.Customers.Remove(entity);
northwind.SaveChanges();
}
}
return Json(new[] { customer }.ToDataSourceResult(request, ModelState));
}
public ActionResult Customers_Create([DataSourceRequest]DataSourceRequest request, CustomerViewModel customer)
{
if (ModelState.IsValid)
{
using (var northwind = new NorthwindEntities())
{
var entity = new Customer
{
CustomerID = Guid.NewGuid().ToString().Substring(0,5),
CompanyName = customer.CompanyName,
Country = customer.Country,
City = customer.City,
ContactName = customer.ContactName,
Phone = customer.Phone,
};
northwind.Customers.Add(entity);
northwind.SaveChanges();
customer.CustomerID = entity.CustomerID;
}
}
return Json(new[] { customer }.ToDataSourceResult(request, ModelState));
}
public ActionResult Products_Read(string text)
{
var northwind = new NorthwindEntities();
var products = northwind.Products.Select(product => new ProductViewModel
{
CategoryID = product.CategoryID,
ProductID = product.ProductID,
ProductName = product.ProductName,
UnitPrice = product.UnitPrice ?? 0,
UnitsInStock = product.UnitsInStock ?? 0,
UnitsOnOrder = product.UnitsOnOrder ?? 0,
Discontinued = product.Discontinued
});
if (!string.IsNullOrEmpty(text))
{
products = products.Where(p => p.ProductName.Contains(text));
}
return Json(products, JsonRequestBehavior.AllowGet);
}
public List<CountryTopProducts_Result> GetTopSellingProducts(string Country, DateTime FromDate, DateTime ToDate)
{
var northwind = new NorthwindEntities();
var result = northwind.CountryTopProducts(Country, FromDate.ToString("yyyyMMdd"), ToDate.ToString("yyyyMMdd"));
return result.ToList();
}
