public ActionResult ViewOrders()
{
List <Order> orders;
if (TempData["orders"] != null)
{
orders = (List <Order>)TempData["orders"];
}
else
{
using (NorthwindConnection db = new NorthwindConnection())
{
if (Session["EmployeeId"] != null)
{
int id = (int)Session["EmployeeId"];
orders = db.Orders.Select(p => p)
.Include(p => p.Customer)
.Where(p => p.EmployeeID == id).ToList();
}
else
{
string id = (string)Session["CustomerId"];
orders = db.Orders.Select(p => p)
.Include(p => p.Customer)
.Where(p => p.CustomerID == id)
.ToList();
}
}
}
return(View(orders));
}
public ActionResult AddNewProduct(int productId, int orderId, short amount)
{
List <Order_Detail> orderDetails;
using (NorthwindConnection db = new NorthwindConnection())
{
orderDetails = GetOrderDetails(db, orderId);
bool isNew = true;
foreach (var orderDetail in orderDetails)
{
if (orderDetail.ProductID == productId)
{
isNew = false;
}
}
if (isNew)
{
Product prod = db.Products.SingleOrDefault(x => x.ProductID == productId);
//Order order = db.Orders.SingleOrDefault(p => p.OrderID == orderId);
db.UpdateOrderDetails(orderId, productId, prod.UnitPrice, amount, 0);
db.SaveChanges();
}
else
{
return(RedirectToAction("ChangeAmount", new{ orderId = orderId, productId = productId, change = "add", amount = amount }));
}
}
return(RedirectToAction("OrderDetails", new{ id = orderId }));
}
public ActionResult OrderDetails(int id)
{
Order order;
List <Order_Detail> orderDetail;
Customer customer;
List <Product> products;
List <Category> categories;
using (NorthwindConnection db = new NorthwindConnection())
{
order = db.Orders.SingleOrDefault(o => o.OrderID == id);
orderDetail = GetOrderDetails(db, order.OrderID);
customer = db.Customers.SingleOrDefault(c => c.CustomerID == order.CustomerID);
products = db.Products.ToList();
categories = db.Categories.ToList();
}
OrderDetailsModel model = new OrderDetailsModel();
model.Order = order;
model.OrderDetails = orderDetail;
model.Customer = customer;
model.Products = products;
model.categories = categories.Select(x => new SelectListItem()
{
Value = x.CategoryID.ToString(),
Text = x.CategoryName
});
return(PartialView("_OrderDetails", model));
}
public ActionResult DeleteOrder(int id)
{
Order order;
List <Order_Detail> orderDetails;
List <Order> orders;
using (NorthwindConnection db = new NorthwindConnection())
{
order = db.Orders.SingleOrDefault(x => x.OrderID == id);
if (order.ShippedDate > DateTime.Now)
{
orderDetails = db.Order_Details.Where(x => x.OrderID == id).ToList();
List <int> prodIds = new List <int>();
foreach (var orderDetail in orderDetails)
{
prodIds.Add(orderDetail.ProductID);
}
foreach (var prodId in prodIds)
{
db.DeleteOrderDetail(id, prodId);
}
db.DeleteOrder(id);
orders = db.Orders.ToList();
return(RedirectToAction("Index", "Home"));
}
else
{
return(View());
}
}
}
public ActionResult Login(User u)
{
User user;
using (NorthwindConnection db = new NorthwindConnection())
{
user = db.Users.SingleOrDefault(x => x.UserName == u.UserName && x.Password == u.Password);
}
if (user != null)
{
FormsAuthentication.SetAuthCookie(user.UserName, true);
if (user.EmployeeID != null)
{
Session["EmployeeId"] = user.EmployeeID;
}
else if (user.CustomerID != null)
{
Session["CustomerId"] = user.CustomerID;
}
return(RedirectToAction("Index", "Home"));
}
else
{
return(View());
}
}
public ActionResult AccountDetails()
{
Employee employee;
Customer customer;
AccountDetailsModel model = new AccountDetailsModel();
string type;
if (Session["EmployeeId"] != null)
{
int id = (int)Session["EmployeeId"];
using (NorthwindConnection db = new NorthwindConnection())
{
employee = db.Employees.SingleOrDefault(p => p.EmployeeID == id);
}
type = "employee";
model.Employee = employee;
}
else
{
string id = (string)Session["CustomerId"];
using (NorthwindConnection db = new NorthwindConnection())
{
customer = db.Customers.SingleOrDefault(c => c.CustomerID == id);
}
model.Customer = customer;
type = "customer";
}
model.UserType = type;
return(View(model));
}
public ActionResult FindCustomer(CustomerModel c)
{
List <Customer> customers;
using (NorthwindConnection db = new NorthwindConnection())
{
if (c.Customer.CompanyName != null && c.Customer.City != null)
{
customers =
db.Customers.Where(x => x.CompanyName.Contains(c.Customer.CompanyName) && x.City == c.Customer.City).ToList();
}
else if (c.Customer.CompanyName != null)
{
customers = db.Customers.Where(x => x.CompanyName.Contains(c.Customer.CompanyName)).ToList();
}
else
{
customers = db.Customers.Where(x => x.City == c.Customer.City).ToList();
}
}
CustomerModel cm = new CustomerModel();
cm.Customers = customers;
return(View(cm));
}
public ActionResult UpdateProfile(AccountDetailsModel m)
{
AccountDetailsModel model = m;
if (m.UserType == "employee")
{
using (NorthwindConnection db = new NorthwindConnection())
{
Employee e = db.Employees.SingleOrDefault(x => x.EmployeeID == m.Employee.EmployeeID);
db.Employees.SingleOrDefault(x => x.EmployeeID == m.Employee.EmployeeID).FirstName =
m.Employee.FirstName;
db.Employees.SingleOrDefault(x => x.EmployeeID == m.Employee.EmployeeID).LastName =
m.Employee.LastName;
db.SaveChanges();
}
}
else
{
using (NorthwindConnection db = new NorthwindConnection())
{
db.Customers.SingleOrDefault(x => x.CustomerID == m.Customer.CustomerID).ContactName =
m.Customer.ContactName;
db.Customers.SingleOrDefault(x => x.CustomerID == m.Customer.CustomerID).ContactTitle =
m.Customer.ContactTitle;
db.SaveChanges();
}
}
return(View());
}
public ActionResult ChangeAmount(int orderId, int productId, string change, short amount)
{
Order order;
List <Order_Detail> orderDetail;
Customer customer;
List <Product> products;
List <Category> categories;
using (NorthwindConnection db = new NorthwindConnection())
{
categories = db.Categories.ToList();
products = db.Products.ToList();
order = db.Orders.SingleOrDefault(o => o.OrderID == orderId);
orderDetail = GetOrderDetails(db, order.OrderID);
short quantity = orderDetail.SingleOrDefault(x => x.ProductID == productId).Quantity;
if (change == "add")
{
quantity += amount;
}
else
{
if ((quantity - amount) > 0)
{
quantity -= amount;
}
else
{
quantity = 0;
}
}
if (quantity > 0)
{
orderDetail.SingleOrDefault(x => x.ProductID == productId).Quantity = quantity;
}
else
{
Order_Detail rowToRemove = orderDetail.SingleOrDefault(x => x.ProductID == productId);
orderDetail.Remove(rowToRemove);
db.DeleteOrderDetail(order.OrderID, productId);
}
customer = db.Customers.SingleOrDefault(c => c.CustomerID == order.CustomerID);
db.SaveChanges();
}
OrderDetailsModel model = new OrderDetailsModel();
model.Order = order;
model.OrderDetails = orderDetail;
model.Customer = customer;
model.Products = products;
model.categories = categories.Select(x => new SelectListItem()
{
Value = x.CategoryID.ToString(),
Text = x.CategoryName
});
return(PartialView("_OrderDetails", model));
}
private static int FindAnalog(NorthwindConnection db, OrderDetail notShippedOrder)
{
var analogProduct = notShippedOrder.ProductId++;
if (!db.Products.Any(p => p.ProductId == analogProduct))
{
analogProduct = notShippedOrder.ProductId--;
}
return(analogProduct);
}
public ActionResult ViewAllOrders()
{
List <Order> orders;
using (NorthwindConnection db = new NorthwindConnection())
{
orders = db.Orders.Select(p => p).Include(p => p.Customer).ToList();
}
TempData["orders"] = orders;
return(RedirectToAction("ViewOrders"));
}
public override string[] GetRolesForUser(string username)
{
string[] roles = new string[1];
string roleName;
using (NorthwindConnection db = new NorthwindConnection())
{
roleName = db.Users.SingleOrDefault(u => u.UserName == username).Role.RoleName;
}
roles[0] = roleName;
return(roles);
}
public static void ListOfProductsWithCategoryAndSuppliers()
{
using (var db = new NorthwindConnection())
{
foreach (var product in db.Products.LoadWith(p => p.Category).LoadWith(p => p.Supplier))
{
Console.WriteLine(string.Concat(ConstantsHelper.PRODUCT_NAME, ConstantsHelper.DELIMITER) + product.ProductName + " "
+ string.Concat(ConstantsHelper.CATEGORY, ConstantsHelper.DELIMITER) + product.Category.CategoryName + " "
+ string.Concat(ConstantsHelper.SUPPLIER, ConstantsHelper.DELIMITER) + product.Supplier.CompanyName);
}
}
}
public static void MoveProductsToAnotherCategory()
{
using (var db = new NorthwindConnection())
{
var category = db.Categories.First();
var product = db.Products.First(
p => p.CategoryId != category.CategoryId);
product.CategoryId = category.CategoryId;
db.Update(product);
}
}
private List <Order_Detail> GetOrderDetails(NorthwindConnection db, int orderId)
{
List <Order_Detail> orderDetail;
orderDetail =
db.Order_Details.Select(p => p)
.Where(p => p.OrderID == orderId)
.Include(p => p.Product)
.ToList();
return(orderDetail);
}
public static void ReplaceProductWithAnalog()
{
using (var db = new NorthwindConnection())
{
var updatedRows = db.OrderDetails.LoadWith(od => od.Order).LoadWith(od => od.Product)
.Where(od => od.Order.ShippedDate == null).Update(
od => new OrderDetail
{
ProductId = db.Products.First(p => p.CategoryId == od.Product.CategoryId && p.ProductId > od.ProductId) != null
? db.Products.First(p => p.CategoryId == od.Product.CategoryId && p.ProductId > od.ProductId).ProductId
: db.Products.First(p => p.CategoryId == od.Product.CategoryId).ProductId
});
Console.WriteLine($"{updatedRows} rows updated");
}
}
public static void ListOfEmployeesWithRegion()
{
using (var db = new NorthwindConnection())
{
foreach (var employee in db.Employees.Join(db.EmployeeTerritories, e => e.EmployeeId, et => et.EmployeeId,
(e, et) => new { Name = e.LastName + " " + e.FirstName, TerritoryID = et.TerritoryId })
.Join(db.Territories, e => e.TerritoryID, t => t.TerritoryId,
(e, t) => new { Name = e.Name, RegionID = t.RegionId })
.Join(db.Regions, e => e.RegionID, r => r.RegionId,
(e, r) => new { Name = e.Name, Region = r.RegionDescription }))
{
Console.WriteLine(string.Concat(ConstantsHelper.EMPLOYEE, ConstantsHelper.DELIMITER) + employee.Name + " "
+ string.Concat(ConstantsHelper.REGION, ConstantsHelper.DELIMITER) + employee.Region);
}
}
}
public static void ListOfEmployeesAndShippers()
{
using (var db = new NorthwindConnection())
{
foreach (var orders in db.Orders.Select(o => new {
EmployeeName = o.Employee.LastName + ", " + o.Employee.LastName,
ShipperName = o.Shipper.CompanyName
}).Distinct().GroupBy(o => o.EmployeeName))
{
foreach (var order in orders)
{
Console.WriteLine(string.Concat(ConstantsHelper.EMPLOYEE, ConstantsHelper.DELIMITER) + orders.Key + " "
+ string.Concat(ConstantsHelper.SHIPPER_NAME, ConstantsHelper.DELIMITER) + order.ShipperName);
}
}
}
}
public ActionResult AddNewOrder(NewOrderModel model)
{
Customer customer;
int id = (int)Session["EmployeeId"];
using (NorthwindConnection db = new NorthwindConnection())
{
customer = db.Customers.SingleOrDefault(x => x.CustomerID == model.Order.CustomerID);
db.AddOrderTemporary(customer.CustomerID, id, model.Order.OrderDate, model.Order.RequiredDate,
model.Order.ShippedDate, model.Order.ShipVia, customer.CompanyName);
db.SaveChanges();
}
return(RedirectToAction("Index", "Home"));
}
public static void CountOfEmployeesByRegion()
{
using (var db = new NorthwindConnection())
{
var employees = db.Employees.Join(db.EmployeeTerritories, e => e.EmployeeId, et => et.EmployeeId,
(e, et) => new { Name = e.LastName + " " + e.FirstName, TerritoryID = et.TerritoryId })
.Join(db.Territories, e => e.TerritoryID, t => t.TerritoryId,
(e, t) => new { Name = e.Name, RegionID = t.RegionId });
foreach (var region in db.Regions.GroupJoin(employees, r => r.RegionId, e => e.RegionID,
(r, er) => new { Region = r.RegionDescription, CountOfEmployees = er.Count() }))
{
Console.WriteLine(string.Concat(ConstantsHelper.REGION, ConstantsHelper.DELIMITER) + region.Region + " " +
string.Concat(ConstantsHelper.COUNT_OF_EMPLOYEES, ConstantsHelper.DELIMITER) + region.CountOfEmployees);
}
}
}
public static void AddNewEmployeeWithTerritories()
{
using (var db = new NorthwindConnection())
{
var id = Convert.ToInt32(db.InsertWithIdentity(
new Employee {
FirstName = "Alex", LastName = "Moralez"
}));
var territories = db.Territories.Select(x => x.TerritoryId).Skip(20).Take(2).ToArray();
db.Insert(new EmployeeTerritory
{
EmployeeId = id,
TerritoryId = territories[0]
});
db.Insert(new EmployeeTerritory
{
EmployeeId = id,
TerritoryId = territories[1]
});
}
}
public ActionResult AddNewOrder()
{
int employeeId = (int)Session["EmployeeId"];
List <Customer> customers;
List <Customer> customersForEmployee = new List <Customer>();
List <Shipper> shippers;
using (NorthwindConnection db = new NorthwindConnection())
{
shippers = db.Shippers.ToList();
customers = db.Customers.ToList();
var orders = db.Orders.Where(x => x.EmployeeID == employeeId).ToList().GroupBy(x => x.CustomerID);
foreach (IGrouping <string, Order> grouping in orders)
{
Customer customer = db.Customers.SingleOrDefault(x => x.CustomerID == grouping.Key);
customersForEmployee.Add(customer);
}
}
NewOrderModel model = new NewOrderModel();
model.CustomerNames = customers.Select(x => new SelectListItem()
{
Value = x.CustomerID,
Text = x.CompanyName
});
model.CustomerNamesForEmployee = customersForEmployee.Select(x => new SelectListItem()
{
Value = x.CustomerID,
Text = x.CompanyName
});
model.Shippers = shippers.Select(x => new SelectListItem()
{
Value = x.ShipperID.ToString(),
Text = x.CompanyName
});
return(View(model));
}
public ActionResult FindProducts(OrderDetailsModel model, int id)
{
List <Product> products;
using (NorthwindConnection db = new NorthwindConnection())
{
products = db.Products.Where(x => x.CategoryID == model.Product.CategoryID && x.UnitPrice >= model.MinPrice).ToList();
if (model.MaxPrice > 0)
{
products = products.Where(x => x.UnitPrice <= model.MaxPrice).ToList();
}
if (model.Product.ProductName != null && model.Product.ProductName != "")
{
products = products.Where(x => x.ProductName.Contains(model.Product.ProductName)).ToList();
}
}
ProductsListModel plModel = new ProductsListModel
{
Products = products,
OrderId = id
};
return(PartialView("_ProductList", plModel));
}
public static void AddProducts()
{
using (var db = new NorthwindConnection())
{
var products = new List <Product>
{
new Product
{
ProductName = "SomeProduct",
Category = new Category {
CategoryName = "SomeCategory"
},
Supplier = new Supplier {
CompanyName = "SomeSupplier"
}
},
new Product
{
ProductName = "AnotherProduct",
Category = new Category {
CategoryName = "AnotherCategory"
},
Supplier = new Supplier {
CompanyName = "AnotherSupplier"
}
}
};
foreach (var p in products)
{
if (db.Categories.Any(c => c.CategoryName == p.Category.CategoryName))
{
p.CategoryId = db.Categories.
First(c => c.CategoryName == p.Category.CategoryName).CategoryId;
}
else
{
p.CategoryId = Convert.ToInt32(
db.InsertWithIdentity(
new Category {
CategoryName = p.Category.CategoryName
}));
}
if (db.Suppliers.Any(s => s.CompanyName == p.Supplier.CompanyName))
{
p.SupplierId = db.Suppliers.
First(s => s.CompanyName == p.Supplier.CompanyName).SupplierId;
}
else
{
p.SupplierId = Convert.ToInt32(
db.InsertWithIdentity(
new Supplier {
CompanyName = p.Supplier.CompanyName
}));
}
db.Insert(p);
}
}
}