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(); }