public void AddProductItem(ProductInfo info)
{
// Step 0: Validation
if (info == null)
{
throw new ArgumentNullException(nameof(info), $"No {nameof(ProductInfo)} was supplied for adding a new product to the catalog.");
}
if (info.Price <= 0)
{
throw new ArgumentOutOfRangeException(nameof(info.Price), $"The supplied price of {info.Price} must be greater than zero.");
}
// Step 1: Process the request by adding a new Product to the database
using (var context = new WestWindContext())
{
var newItem = new Product
{
ProductName = info.Name?.Trim(), // Null Conditional Operator ?.
QuantityPerUnit = info.QtyPerUnit?.Trim(),
UnitPrice = info.Price,
CategoryID = info.CategoryId,
SupplierID = info.SupplierId
};
context.Products.Add(newItem);
context.SaveChanges(); // This will cause all the validation attributes to be checked
}
}
public List <Customer> ListCustomers()
{
using (var context = new WestWindContext())
{
return(context.Customers.ToList());
}
}
public List <Employee> ListEmployees()
{
using (var context = new WestWindContext())
{
return(context.Employees.ToList());
}
}
static List <OrderShipment> ListOrdersByDate()
{
using (var context = new WestWindContext())
{
var orders = from order in context.Orders
orderby order.OrderDate
select new OrderShipment
{
OrderID = order.OrderID,
OrderedOn = order.OrderDate,
RequiredBy = order.RequiredDate,
FreightCharge = order.Freight,
Details = from item in order.OrderDetails
group item by item.Product.Supplier into dropShippers
select new SupplierShipment
{
SupplierName = dropShippers.Key.CompanyName,
Items = from i in dropShippers
select new ShipmentItem
{
ProductID = i.ProductID,
Quantity = i.Quantity,
Price = i.UnitPrice,
Discount = i.Discount,
QuantityPerUnit = i.Product.QuantityPerUnit
}
}
};
return(orders.ToList());
}
}
public List <Category> ListCategories()
{
using (var context = new WestWindContext())
{
return(context.Categories.ToList());
}
}
public void AddSupplier(Supplier item)
{
using (var context = new WestWindContext()){
context.Suppliers.Add(item);
context.SaveChanges();
}
}
public List <Address> ListAddresses()
{
using (var context = new WestWindContext())
{
return(context.Addresses.ToList());
}
}
public List <OutstandingOrder> LoadOrders(int supplierID)
{
using (var context = new WestWindContext())
{
/*
* Validation:
* Make sure the supplier ID exists, otherwise throw exception
*/
var supplier = context.Suppliers.Find(supplierID);
if (supplier == null)
{
throw new Exception("Unknown supplier");
}
/*
* [Advanced:] Make sure the logged-in user works for the identified supplier.
*/
//Processing
var result =
from ord in context.Orders
where !ord.Shipped && // Still items to be shipped
ord.OrderDate.HasValue // The order has been placed and is ready to ship
select new OutstandingOrder
{
OrderID = ord.OrderID,
ShipToName = ord.ShipName,
OrderDate = ord.OrderDate.Value,
RequiredBy = ord.RequiredDate.Value,
OutstandingItems = from detail in ord.OrderDetails
select new OrderProductInformation
{
ProductID = detail.ProductID,
ProductName = detail.Product.ProductName,
Qty = detail.Quantity,
QtyPerUnit = detail.Product.QuantityPerUnit,
// TODO:
// Outstanding = (from ship in detail.Order.Shipments
// from item in ship.ManifestItems
// where item.ProductID == detail.ProductID
// select item.ShipQuantity)
},
// ord.ShipAddressID,
// Note to self: If there is a ShipTo address, use that, otherwise use the customer address
FullShippingAddress = ord.ShipAddressID.HasValue
? context.Addresses.Where(x => x.AddressID == ord.ShipAddressID)
.Select(a => a.Street + Environment.NewLine +
a.City + Environment.NewLine +
a.Region + " " +
a.Country + ", " +
a.PostalCode).FirstOrDefault()
: ord.Customer.Address.Street + Environment.NewLine +
ord.Customer.Address.City + Environment.NewLine +
ord.Customer.Address.Region + " " +
ord.Customer.Address.Country + ", " +
ord.Customer.Address.PostalCode,
Comments = ord.Comments
};
return(result.ToList());
}
}
public List <Supplier> ListSuppliers()
{
using (var context = new WestWindContext())
{
return(context.Suppliers.ToList());
}
}
public List <Supplier> ListSuppliers()
{
using (var context = new WestWindContext())
{
return(context.Suppliers.Include(nameof(Supplier.Address)).ToList());
}
}
//public Product GetProduct(int productId)
//{
// using (var context = new WestWindContext)
// {
// return
// }
//}
public Product LookupProduct(int productId)
{
using (var context = new WestWindContext())
{
return(context.Products.Find(productId));
}
}
public List <SupplierShipment> OrderShipments(int orderId)
{
using (var context = new WestWindContext())
{
// Answer to query
var result = from shipment in context.Shipments
where shipment.OrderID == 11021
select new SupplierShipment
{
Sender = shipment.ManifestItems.First().Product.Supplier.CompanyName,
ShippedOn = shipment.ShippedDate,
ShipVia = new ShipVia
{
Company = shipment.Shipper.CompanyName,
Phone = shipment.Shipper.Phone
},
Manifest = (from item in shipment.ManifestItems
select new ShippedItem
{
Product = item.Product.ProductName,
Quantity = item.ShipQuantity
}).ToList(),
FreightCost = shipment.FreightCharge
};
return(result.ToList());
}
}
public List <Region> ListRegions()
{
using (var context = new WestWindContext())
{
return(context.Regions.ToList());
}
}
static List <int> ListShipperIds()
{
using (var context = new WestWindContext())
{
return(context.Shippers.Select(x => x.ShipperID).ToList());
}
}
static int ShipmentCount()
{
using (var context = new WestWindContext())
{
return(context.Shipments.Count());
}
}
static void ShipToCustomer(SupplierShipment shipment, int orderId, DateTime shipDate, int shipvia)
{
using (var context = new WestWindContext())
{
var ship = new Shipment
{
OrderID = orderId,
ShippedDate = shipDate,
ShipVia = shipvia,
FreightCharge = shipment.Freight,
TrackingCode = Guid.NewGuid().ToString()
};
foreach (var item in shipment.Items)
{
ship.ManifestItems.Add(new ManifestItem
{
ProductID = item.ProductID,
ShipQuantity = item.Quantity
});
}
context.Shipments.Add(ship);
context.Orders.Find(orderId).Shipped = true;
context.SaveChanges();
System.Console.WriteLine($"{counter++} - {ship.TrackingCode}");
}
}
public List <CustomerOrder> GetOrdersByCustomer(string customerId, string filter)
{
if (!"open".Equals(filter.ToLower()) && !"shipped".Equals(filter.ToLower()))
{
throw new ArgumentException("Filter values are required", nameof(filter));
}
using (var context = new WestWindContext())
{
var shipped = "shipped".Equals(filter.ToLower());
var results = from data in context.Orders
orderby data.OrderDate descending
where data.CustomerID == customerId &&
data.Shipped == shipped
select new CustomerOrder
{
OrderId = data.OrderID,
Employee = data.Employee.FirstName
+ " "
+ data.Employee.LastName,
OrderDate = data.OrderDate,
RequiredDate = data.RequiredDate,
Freight = data.Freight,
Shipments = from sent in data.Shipments
orderby sent.ShippedDate
select new ShipmentSummary
{
ShippedOn = sent.ShippedDate,
Carrier = sent.Shipper.CompanyName
},
OrderTotal = data.OrderDetails.Sum(x => x.Quantity * x.UnitPrice)
};
return(results.ToList());
}
}
/// <summary>
/// AddOrder will create a new customer order, processing it as a single transaction.
/// </summary>
/// <param name="order">The particulars of the order</param>
private void AddPendingOrder(EditCustomerOrder order)
{
using (var context = new WestWindContext())
{
var orderInProcess = context.Orders.Add(new Order());
// Make the orderInProcess match the customer order as given...
// A) The general order information
orderInProcess.CustomerID = order.CustomerId;
orderInProcess.SalesRepID = order.EmployeeId;
orderInProcess.RequiredDate = order.RequiredDate;
orderInProcess.Freight = order.FreightCharge;
// B) Add order details
foreach (var item in order.OrderItems)
{
// Add as a new item
var newItem = new OrderDetail
{
ProductID = item.ProductId,
Quantity = item.OrderQuantity,
UnitPrice = item.UnitPrice,
Discount = item.DiscountPercent
};
orderInProcess.OrderDetails.Add(newItem);
}
// C) Save the changes (one save, one transaction)
context.SaveChanges();
}
}
public List <Product> ListProducts()
{
using (var context = new WestWindContext())
{
return(context.Products.ToList());
}
}
public List <Order> ListOrders()
{
using (var context = new WestWindContext())
{
return(context.Orders.ToList());
}
}
public List <OutstandingOrder> LoadOrders(int supplierId)
{
//throw new NotImplementedException();
// TODO: Implement this method with the following
/*
* Validation:
* Make sure the supplier ID exixts, otherwise throw an exception
* [Advanced] Make sure the logged-in user works for the identified supplier.
* Query for outstanding orders, getting data from the following tables:
* TODO: List table names
*/
using (var context = new WestWindContext()) // Using my DAL object
{
// validation
var supplier = context.Suppliers.Find(supplierId);
if (supplier == null)
{
throw new Exception("Invalid Supplier - unable to load orders");
}
// Processing
var result =
from sale in context.Orders
where !sale.Shipped &&
sale.OrderDate.HasValue
select new OutstandingOrder
{
OrderId = sale.OrderID,
ShipToName = sale.ShipName,
OrderDate = sale.OrderDate.Value,
RequiredBy = sale.RequiredDate.Value,
OutstandingItems =
from item in sale.OrderDetails
where item.Product.SupplierID == supplierId
select new OrderItem
{
ProductID = item.ProductID,
ProductName = item.Product.ProductName,
Qty = item.Quantity,
QtyPerUnit = item.Product.QuantityPerUnit,
// TODO: Figure out the Outstanding quantity
// Outstanding = (from ship in item.Order.Shipments
// from shipItem in ship.ManifestItems
// where shipItem.ProductID == item.ProductID
// select shipItem.ShipQuantity).Sum()
},
FullShippingAddress = //TODO: how to use sale.ShipAddressID,
sale.Customer.Address.Street + Environment.NewLine +
sale.Customer.Address.City + ", " +
sale.Customer.Address.Region + Environment.NewLine +
sale.Customer.Address.Country + " " +
sale.Customer.Address.PostalCode,
Comments = sale.Comments
};
return(result.ToList());
}
}
private void DisplayAddresses()
{
using (var context = new WestWindContext())
{
int count = context.Addresses.Count();
Console.WriteLine($"There are {count} Addresses");
}
}
private void DisplayManifests()
{
using (var context = new WestWindContext())
{
int count = context.ManifestItems.Count();
Console.WriteLine($"There are {count} manifest items");
}
}
public Supplier GetSupplier(int id)
{
using (var context = new WestWindContext())
{
//The .Find() method will look up the object based on its pk
return(context.Suppliers.Find(id));
}
}
// Use this class to help with CRUD (Create Read Update Delete) maintenance of our database
public List <Supplier> ListSuppliers()
{
// Make use of our "virtual database" class to get the data
using (var context = new WestWindContext())
{
return(context.Suppliers.ToList());
}
}
public void AddAddress(Address Item)
{
using (var context = new WestWindContext())
{
context.Addresses.Add(Item);
context.SaveChanges();
}
}
private void DisplayOrders()
{
using (var context = new WestWindContext())
{
int count = context.Orders.Count();
Console.WriteLine($"There are {count} orders");
}
}
private void DisplayPaymentTypes()
{
using (var context = new WestWindContext())
{
int count = context.PaymentTypes.Count();
Console.WriteLine($"There are {count} payment types");
}
}
private void DisplayRegions()
{
using (var context = new WestWindContext())
{
int count = context.Regions.Count();
Console.WriteLine($"There are {count} regions");
}
}
private void DisplayTerritories()
{
using (var context = new WestWindContext())
{
int count = context.Territories.Count();
Console.WriteLine($"There are {count} territories");
}
}
