public void AddSupplier(Supplier item)
{
using (var context = new WestWindContext()){
context.Suppliers.Add(item);
context.SaveChanges();
}
}
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}");
}
}
/// <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 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 void AddAddress(Address Item)
{
using (var context = new WestWindContext())
{
context.Addresses.Add(Item);
context.SaveChanges();
}
}
/// <summary>
/// UpdateExistingOrder will make changes to an existing customer order, processing it as a single transaction.
/// </summary>
/// <param name="order">The particulars of the order</param>
private void UpdatePendingOrder(EditCustomerOrder order)
{
using (var context = new WestWindContext())
{
var orderInProcess = context.Orders.Find(order.OrderId);
if (orderInProcess == null)
{
throw new Exception("The order could not be found");
}
// 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/Update/Delete order details
// Loop through the items as known in the database (to update/remove)
foreach (var detail in orderInProcess.OrderDetails.ToList()) // .ToList() to bring into RAM
{
var changes = order.OrderItems.SingleOrDefault(x => x.ProductId == detail.ProductID);
if (changes == null)
{
//toRemove.Add(detail);
context.Entry(detail).State = EntityState.Deleted; // flag for deletion
}
else
{
detail.Discount = changes.DiscountPercent;
detail.Quantity = changes.OrderQuantity;
detail.UnitPrice = changes.UnitPrice;
context.Entry(detail).State = EntityState.Modified;
}
}
// Loop through the new items to add to the database
foreach (var item in order.OrderItems)
{
bool notPresent = !orderInProcess.OrderDetails.Any(x => x.ProductID == item.ProductId);
if (notPresent)
{
// 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.Entry(orderInProcess).State = EntityState.Modified;
context.SaveChanges();
}
}
public int AddProduct(Product item) // we could also just return void
{
using (var context = new WestWindContext())
{
Product addedItem = context.Products.Add(item);
context.SaveChanges(); // do the work to save the changes to the database
return(addedItem.ProductID); // because the PK is an Identity column and generated by the database
}
}
public void DeleteAddress(Address item)
{
using (var context = new WestWindContext())
{
var existing = context.Addresses.Find(item.AddressID);
context.Addresses.Remove(existing);
context.SaveChanges();
}
}
public void UpdateSupplier(Supplier item)
{
using (var context = new WestWindContext())
{
var existing = context.Entry(item);
existing.State = System.Data.Entity.EntityState.Modified;
context.SaveChanges();
}
}
public void DeleteSupplier(int supplierId)
{
using (var context = new WestWindContext())
{
Supplier found = context.Suppliers.Find(supplierId);
context.Suppliers.Remove(found);
context.SaveChanges();
}
}
public void DeleteSupplier(Supplier item)
{
using (var context = new WestWindContext())
{
var existing = context.Suppliers.Find(item.SupplierID);
context.Suppliers.Remove(existing);
context.SaveChanges();
}
}
public int AddSupplier(Supplier item)
{
using (var context = new WestWindContext())
{
var added = context.Suppliers.Add(item);
context.SaveChanges();
//After saving changes, local context object syncs with newly add supplierID that was generated from table's identity constraints
return(added.SupplierID);
}
}
public void DeleteProduct(int id)
{
using (var context = new WestWindContext())
{
// The .Find method will look up the specific Product based on the Primary Key value
var existing = context.Products.Find(id);
context.Products.Remove(existing);
context.SaveChanges();
}
}
public int AddSupplier(Supplier item)
{
using (var context = new WestWindContext())
{
context.Suppliers.Add(item);
context.SaveChanges();
//After saving changes, my local context object "syncs" up the newlly added supplier's ID that was generated from the table's
// IDENTITY Constraint
return(item.SupplierID);
}
}
static void MakePayments()
{
using (var context = new WestWindContext())
{
if (!context.Payments.Any())
{
context.Database.ExecuteSqlCommand("ALTER TABLE Payments NOCHECK CONSTRAINT [CK_PAY_PDate_Not_Old]");
var subtotals = context.Database.SqlQuery <OrderSubtotal>(OrderSubtotal.Query);
var methods = context.PaymentTypes.Select(x => x.PaymentTypeID).ToList();
foreach (var order in subtotals)
{
var ord = context.Orders.Find(order.OrderID);
if (ord.Shipped)
{
// Make payment(s)
var amountDue = order.Subtotal + (ord.Freight.HasValue ? ord.Freight.Value : 0m);
var date = ord.PaymentDueDate.Value.AddDays(Rnd.Next(4) * -1);
var method = methods[Rnd.Next(methods.Count)];
var pay = new Payment
{
Amount = amountDue,
OrderID = order.OrderID,
PaymentDate = date,
PaymentTypeID = method,
TransactionID = Guid.NewGuid()
};
if (date.AddDays(14) < DateTime.Today)
{
pay.ClearedDate = date.AddDays(Rnd.Next(3, 14));
}
context.Payments.Add(pay);
Console.WriteLine($"Paying order {pay.OrderID} - {pay.Amount:C}");
}
}
context.SaveChanges();
context.Database.ExecuteSqlCommand("ALTER TABLE Payments CHECK CONSTRAINT [CK_PAY_PDate_Not_Old]");
context.SaveChanges();
}
}
}
public void UpdateProduct(Product item)
{
using (var context = new WestWindContext())
{
// The following approach will update the entire Product object in the database
var existing = context.Entry(item);
// Tell the context that this object's data is modified
existing.State = System.Data.Entity.EntityState.Modified;
// Save the changes
context.SaveChanges();
}
}
public void UpdateSupplier(Supplier item)
{
using (var context = new WestWindContext())
{
//The following approach will update entire suppplier object in database
DbEntityEntry <Supplier> existing = context.Entry(item);
//treat whole entity (all propoerties) as being modified
existing.State = System.Data.Entity.EntityState.Modified;
//Save
context.SaveChanges();
}
}
public void UpdateProductItem(ProductInfo info) // ProductInfo is our POCO class
{
// Step 0: Validation
if (info == null)
{
throw new ArgumentNullException(nameof(info), $"No {nameof(ProductInfo)} was supplied for updating an existing product in 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 modifying an existing Product in the database
using (var context = new WestWindContext())
{
// Note to Self: When doing an update for an existing item,
// do a .Find() or a .Attach() to load up the
// item from the database.
var given = context.Products.Find(info.ProductId);
// Assuming I will get a null if the product does not exist
if (given == null)
{
throw new ArgumentException($"The given product id of {info.ProductId} does not exist in the database.", nameof(info.ProductId));
}
// Update the found product with the given information
given.ProductName = info.Name;
given.UnitPrice = info.Price;
given.QuantityPerUnit = info.QtyPerUnit;
given.CategoryID = info.CategoryId;
given.SupplierID = info.SupplierId;
// Grab a DbEntityEntry<Product>, with which I can say what's been changed.
var existing = context.Entry(given); // .Entry() will look for the Product obj with a matching ID
// Specify which Product properties I've modified, 'cause I don't want to lose
// the .Discontinued or the .UnitsOnOrder values.
existing.Property(nameof(given.ProductName)).IsModified = true;
existing.Property(nameof(given.UnitPrice)).IsModified = true;
existing.Property(nameof(given.QuantityPerUnit)).IsModified = true;
existing.Property(nameof(given.CategoryID)).IsModified = true;
existing.Property(nameof(given.SupplierID)).IsModified = true;
// Update the database
context.SaveChanges();
}
}
public void DeleteProductItem(ProductInfo info)
{
if (info == null)
{
throw new ArgumentNullException(nameof(info), "No Product Info was supplied for deletion.");
}
using (var context = new WestWindContext())
{
var existing = context.Products.Find(info.ProductId);
if (existing == null)
{
throw new ArgumentException("The product was not found", nameof(info.ProductId));
}
context.Products.Remove(existing);
context.SaveChanges();
}
}
public void DiscontinueProductItem(ProductInfo info)
{
if (info == null)
{
throw new ArgumentNullException(nameof(info), "No Product Info was supplied for discontinuing.");
}
using (var context = new WestWindContext())
{
var existing = context.Products.Find(info.ProductId);
if (existing == null)
{
throw new ArgumentException("The product was not found", nameof(info.ProductId));
}
existing.Discontinued = true;
var entry = context.Entry(existing);
entry.Property(x => x.Discontinued).IsModified = true;
context.SaveChanges();
}
}
static void SetPaymentDueDate()
{
using (var context = new WestWindContext())
{
if (!context.Payments.Any())
{
foreach (var order in context.Orders)
{
if (order.OrderDate.HasValue && order.RequiredDate.HasValue)
{
var due = order.OrderDate.Value.AddDays(28);
while (due < order.RequiredDate)
{
due = due.AddDays(7);
}
order.PaymentDueDate = due;
}
}
context.SaveChanges();
}
}
}
public static void CorrectForWeekdays()
{
using (var context = new WestWindContext())
{
int offset = 0;
var info = context.Orders.Where(x => x.OrderDate.HasValue).Select(x => x.OrderDate.Value).ToList();
if (info.Any(x => (x.DayOfWeek == DayOfWeek.Saturday || x.DayOfWeek == DayOfWeek.Sunday)))
{
offset = info.Count(x => x.DayOfWeek == DayOfWeek.Saturday) == 0 ? 1
: info.Count(x => x.DayOfWeek == DayOfWeek.Friday) == 0 ? 2
: info.Count(x => x.DayOfWeek == DayOfWeek.Thursday) == 0 ? 3
: info.Count(x => x.DayOfWeek == DayOfWeek.Wednesday) == 0 ? 4
: info.Count(x => x.DayOfWeek == DayOfWeek.Tuesday) == 0 ? 5
: 6;
var orders = context.Orders;
foreach (var order in orders)
{
order.OrderDate = order.OrderDate.Value.AddDays(offset);
}
context.SaveChanges();
Console.WriteLine("Fixed order dates");
}
}
}
public void PlaceOrder(EditCustomerOrder order)
{
// Always ensure you have been given data to work with
if (order == null)
{
throw new ArgumentNullException("order", "Cannot place order; order information was not supplied.");
}
// Business validation rules
if (!order.RequiredDate.HasValue)
{
throw new Exception($"A required date for the order is required when placing orders.");
}
if (order.OrderItems.Count() == 0)
{
throw new Exception("An order must have at least one item before it can be placed.");
}
// Begin processing the order
order.OrderDate = DateTime.Today;
using (var context = new WestWindContext())
{
// Prep for processing...
var customer = context.Customers.Find(order.CustomerId);
if (customer == null)
{
throw new Exception("Customer does not exist");
}
var orderInProcess = context.Orders.Find(order.OrderId);
if (orderInProcess == null)
{
orderInProcess = context.Orders.Add(new Order());
}
else
{
if (orderInProcess.OrderDate.HasValue)
{
throw new Exception("Aborting changes: The order has previously been placed.");
}
context.Entry(orderInProcess).State = EntityState.Modified;
}
// Make the orderInProcess match the customer order as given...
// A) The general order information
orderInProcess.CustomerID = order.CustomerId;
orderInProcess.SalesRepID = order.EmployeeId;
orderInProcess.OrderDate = order.OrderDate;
orderInProcess.RequiredDate = order.RequiredDate;
orderInProcess.Freight = order.FreightCharge;
// B) Default the ship-to info to the customer's info
orderInProcess.ShipName = customer.CompanyName;
// TODO: Redesign for new Address information
//orderInProcess.ShipAddress = customer.Address;
//orderInProcess.ShipCity = customer.City;
//orderInProcess.ShipRegion = customer.Region;
//orderInProcess.ShipPostalCode = customer.PostalCode;
// C) Add/Remove/Update order details
//var toRemove = new List<OrderDetail>();
foreach (var detail in orderInProcess.OrderDetails.ToList()) // .ToList() to bring into RAM
{
var changes = order.OrderItems.SingleOrDefault(x => x.ProductId == detail.ProductID);
if (changes == null)
{
//toRemove.Add(detail);
context.Entry(detail).State = EntityState.Deleted; // flag for deletion
}
else
{
detail.Discount = changes.DiscountPercent;
detail.Quantity = changes.OrderQuantity;
detail.UnitPrice = changes.UnitPrice;
context.Entry(detail).State = EntityState.Modified;
}
}
foreach (var item in order.OrderItems)
{
if (!orderInProcess.OrderDetails.Any(x => x.ProductID == item.ProductId))
{
// 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);
}
}
// D) Save the changes (one save, one transaction)
context.SaveChanges();
}
}
public void ShipOrder(int orderId, ShippingDirections shipping, List <ProductShipment> products)
{
using (var context = new WestWindContext())
{
//TODO: Validation
var existingOrder = context.Orders.Find(orderId);
// a) OrderId must be valid
if (existingOrder == null)
{
throw new Exception("Order does not exist");
}
if (existingOrder.Shipped)
{
throw new Exception("This order has already been completed");
}
if (!existingOrder.OrderDate.HasValue)
{
throw new Exception("This order is not ready to be shipped (no order date has beens specified)");
}
// b) products cannot be an empty list
if (products == null || !products.Any())
{
throw new Exception("No products identified for shipping");
}
// c) products identified must be on the order
foreach (var item in products)
{
if (item == null)
{
throw new Exception("Blank item listed in products to be shipped");
}
if (!existingOrder.OrderDetails.Any(x => x.ProductID == item.ProductId))
{
throw new Exception($"The product {item.ProductId} does not exist on the order");
}
// TODO: d) quantity must be greated than zero and less than or equal to the quantity outstanding
}
// e) shipper must exist
if (shipping == null)
{
throw new Exception("No shipping details provided");
}
var shipper = context.Shippers.Find(shipping.ShipperId);
if (shipper == null)
{
throw new Exception("Invalid shipper ID");
}
// f) freight charge must be either null or no charge or > 0
//todo Q) Should i just convert $0 charge to a null
if (shipping.FreightCharge.HasValue && shipping.FreightCharge <= 0)
{
throw new Exception("Freight charge must be either a positive value or no charge");
}
// Processing
// 1) Create new Shipment
var ship = new Shipment
{
OrderID = orderId,
ShipVia = shipping.ShipperId,
TrackingCode = shipping.TrackingCode,
FreightCharge = shipping.FreightCharge.HasValue ? shipping.FreightCharge.Value : 0,
ShippedDate = DateTime.Now
};
// 2) Add all manifest items
foreach (var item in products)
{
ship.ManifestItems.Add(new ManifestItem
{
ProductID = item.ProductId,
ShipQuantity = (short)item.ShipQuantity
});
}
// TODO: 3) Check if order is complete; if so, update Order.Shipped
// 4) Add the shipment to the database context
context.Shipments.Add(ship);
//5) Save the changes
context.SaveChanges();
/*Processing (tables/data that must be updated/inserted/deleted/whatever)
* Create new Shipment
*
* Check if order is complete; if so, update Order.Shipped
*/
}
}
public void ShipOrder(int orderId, ShippingDirections shipping, List <ShippedItem> items)
{
using (var context = new WestWindContext())
{
// TODO: Validation steps
// a) OrderId must be valid
var existingOrder = context.Orders.Find(orderId);
if (existingOrder == null)
{
throw new Exception("Order does not exist");
}
if (existingOrder.Shipped)
{
throw new Exception("This order has already been completed");
}
if (!existingOrder.OrderDate.HasValue)
{
throw new Exception("This order is not ready to be shipped (no order date has been specified)");
}
// b) ShippingDirections is required (cannot be null)
if (shipping == null)
{
throw new Exception("No shipping details provided");
}
// c) Shipper must exist
var shipper = context.Shippers.Find(shipping.ShipperId);
if (shipper == null)
{
throw new Exception("Invalid shipper Id");
}
// d) Freight charge must be either null (no charge) or > $0.00
// TODO: Q) Should I just convert a $0 charge to a null??
if (shipping.FreightCharge.HasValue && shipping.FreightCharge <= 0)
{
throw new Exception("Freight charge must be either a positive value or no charge");
}
// e) List<ShippedItem> cannot be empty/null
if (items == null || !items.Any())
{
throw new Exception("No products identified for shipping");
}
// f) The products must be on the order
foreach (var item in items)
{
if (item == null)
{
throw new Exception("Blank item listed in the products to be shipped");
}
if (!existingOrder.OrderDetails.Any(x => x.ProductID.ToString() == item.Product))
{
throw new Exception($"The product {item.Product} does not exist on the order");
}
// f-2) AND items that this supplier provides
// TODO: g) Quantities must be greater than zero and less than or equal to the quantity outstanding
}
//Process order shipment
// 1) Create new Shipment
var ship = new Shipment
{
OrderID = orderId,
ShipVia = shipping.ShipperId,
TrackingCode = shipping.TrackingCode,
FreightCharge = shipping.FreightCharge.HasValue
? shipping.FreightCharge.Value
: 0,
ShippedDate = DateTime.Now
};
// 2) Create manifest items for shipment
foreach (var item in items)
{
//Notice that I'm adding the manifest item to the Shipment object
//rather than directly to the db context.
//That's because by adding to the Shipment object,
//the correct values for foreign key fields will be assigned to the new data.
ship.ManifestItems.Add(new ManifestItem
{
ProductID = int.Parse(item.Product),
ShipQuantity = item.Quantity
});
}
// TODO: 3) Check if order is complete; if so, update Order.Shipped
// 4) Add the shipment to the context
context.Shipments.Add(ship);
// 5) Save the changes (as a single transaction)
context.SaveChanges();
}
}
public void ShipOrder(int orderID, ShippingDirections shipping, List <ProductShipment> products)
{
using (var context = new WestWindContext())
{
//Validation
var existingOrder = context.Orders.Find(orderID);
//Order ID must be valid
if (existingOrder == null)
{
throw new Exception("Order does not exist");
}
if (existingOrder.Shipped)
{
throw new Exception("This order has already been completed");
}
if (!existingOrder.OrderDate.HasValue)
{
throw new Exception("this order is not ready to be shipped (no order data has been specified)");
}
//Products cannot be an empty list
if (products == null || !products.Any())
{
throw new Exception("No products identified for shipping");
}
//Products identified must be on the order
foreach (var item in products)
{
if (item == null)
{
throw new Exception("Blank item listed in products to be shipped");
}
if (!existingOrder.OrderDetails.Any(x => x.ProductID == item.ProductID))
{
throw new Exception($"the product {item.ProductID} does not exist on the order");
}
}
//Shipper must exist
if (shipping == null)
{
throw new Exception("No shipping details provided");
}
var shipper = context.Shippers.Find(shipping.ShipperID);
if (shipper == null)
{
throw new Exception("Invalid shipper ID");
}
//Freight Charge must either be null (no charge) or > 0.00
if (shipping.FreightCharge.HasValue && shipping.FreightCharge <= 0)
{
throw new Exception("Freight charge must be either a positive value or no charge");
}
//Processing the shipment
//1. Create new Shipment
var ship = new Shipment
{
OrderID = orderID,
ShipVia = shipping.ShipperID,
TrackingCode = shipping.TrackingCode,
FreightCharge = shipping.FreightCharge.HasValue ? shipping.FreightCharge.Value : 0,
ShippedDate = DateTime.Now
};
//2. Add all manifest items
foreach (var item in products)
{
ship.ManifestItems.Add(new ManifestItem
{
ProductID = item.ProductID,
ShipQuantity = (short)item.ShipQuantity //TODO: Change the data type on ProductShipment.ShipQuantity to a short
});
}
//3. Check if order is complete; if so, update Order.Shipped
//4. Add the shipment to the database context
context.Shipments.Add(ship);
//5. Save the changes as a single transaction
context.SaveChanges();
}
}