/// <summary> /// mini method for generating new key value /// </summary> public int OrderSize() { using (var db = new Pzero_DbContextClass()) { var ords = db.Orders .FromSqlRaw("SELECT * FROM Orders") .ToList(); return(ords.Count); } }
/// <summary> /// Can be used to read out all customer info /// </summary> public void ReadCustomers() { using (var db = new Pzero_DbContextClass()) { var custs = db.Customers .FromSqlRaw("SELECT * FROM Customers") .ToList(); foreach (var cust in custs) { Console.WriteLine($"first: {cust.FName} last: {cust.LName} Id: {cust.CustomerID}"); } } }
/// <summary> /// used to update inventory after a successful order is made /// </summary> public void UpdateInventory(int ID, int Q) { using (var db = new Pzero_DbContextClass()) { try { var locs = db.Locations .FromSqlInterpolated($"UPDATE Locations SET Quantity = Quantity - {Q} WHERE InventoryID = {ID}") .ToList(); } catch { } } }
/// <summary> /// used to display store/inventory quickly /// </summary> public void ShowInventory() { using (var db = new Pzero_DbContextClass()) { var locs = db.Locations .FromSqlRaw("SELECT * FROM Locations") .ToList(); foreach (var l in locs) { Console.WriteLine($"InventoryID {l.InventoryID} | StoreID {l.StoreID} | ItemID {l.ItemID} | Quantity Available {l.Quantity}"); } } }
/// <summary> /// Used to quickly print out all products, called in main /// </summary> public void ShowAllProducts() { using (var db = new Pzero_DbContextClass()) { var prods = db.Products .FromSqlRaw("SELECT * FROM Products") .ToList(); foreach (var p in prods) { Console.WriteLine($"ProductID ({p.ProductID}) | Item Name ({p.PName}) | Unit Cost ({p.PCost})"); } } }
public void SearchCustomer(int version) // version >> 0 -> Id : 1 -> Name { using (var db = new Pzero_DbContextClass()) { if (version == 0) { Console.Write("Enter their ID: "); int input = Convert.ToInt32(Console.ReadLine()); var cust = db.Customers .FromSqlInterpolated($"SELECT * FROM Customers WHERE CustomerID = {input}") .ToList(); if (cust.Count() >= 1) { foreach (var C in cust) // since it's using ID should only be one row large { Console.WriteLine($"FName ({C.FName}) | LName ({C.LName}) | Default Store ({C.DefaultSto})"); } } else { Console.WriteLine("No customers were found with that ID"); } } else if (version == 1) { Console.Write("Enter their first name (Case Sensitive) >> "); string first = Console.ReadLine(); Console.Write("Enter their last name (Case Sensitive) >> "); string last = Console.ReadLine(); var cust = db.Customers .FromSqlInterpolated($"SELECT * FROM Customers WHERE FName = {first} AND LName = {last}") .ToList(); if (cust.Count() >= 1) { foreach (var C in cust) // since it's using ID should only be one row large { Console.WriteLine($"CustomerID ({C.CustomerID}) | Default Store ({C.DefaultSto})"); } } else { Console.WriteLine("No customers were found under that that name"); } } else { Console.WriteLine("huh this is an error"); } } }
/// <summary> /// used to extract the name of ordered items /// </summary> public string GetPName(int i) //only called on valid product so should never return error { using (var db = new Pzero_DbContextClass()) { var prods = db.Products .FromSqlInterpolated($"SELECT * FROM Products WHERE ProductID = {i}") .ToList(); foreach (var p in prods) { return(p.PName); } return("error"); } }
/// <summary> /// Used to quickly determin if a customer exists, used for making orders /// </summary> public bool IsValidCustomer(int i) { using (var db = new Pzero_DbContextClass()) { var custs = db.Customers .FromSqlInterpolated($"SELECT * FROM Customers WHERE CustomerID = {i}") .ToList(); if (custs.Count >= 1) { return(true); } else { return(false); } } }
/// <summary> /// Used to confirm store location is valid for order placement /// </summary> public bool IsValidLocation(int i) //will stop order if the store doesn't exist { using (var db = new Pzero_DbContextClass()) { var locs = db.Locations .FromSqlInterpolated($"SELECT * FROM Locations WHERE StoreID = {i}") .ToList(); if (locs.Count > 0) { return(true); } else { return(false); } } }
/// <summary> /// Determines if provided product ID is valid for order placement /// </summary> public bool IsValidProduct(int i) { using (var db = new Pzero_DbContextClass()) { var prods = db.Products .FromSqlInterpolated($"SELECT * FROM Products WHERE ProductID = {i}") .ToList(); if (prods.Count >= 1) { return(true); } else { Console.WriteLine("Invalid product ID, try again"); return(false); } } }
/// <summary> /// Returns how many of an item is available, so order can't exceede current volume /// </summary> public int MaxAvailable(int ItemID, int StoreID) //returns how many of an Item there is to reject oversized orders { using (var db = new Pzero_DbContextClass()) { var locs = db.Locations .FromSqlInterpolated($"SELECT * FROM Locations WHERE ItemID = {ItemID} AND Quantity >= 1 AND StoreID = {StoreID}") .ToList(); if (locs.Count > 0) { foreach (var l in locs) //since using SUM there should only be one { return(l.Quantity); } } else { return(-1); } } return(-1); }
/// <summary> /// used to show details of a specific order /// </summary> public void ShowSpecificOrder() { using (var db = new Pzero_DbContextClass()) { Console.Write("Enter OrderID >> "); int TargetID = Convert.ToInt32(Console.ReadLine()); var ords = db.Orders .FromSqlInterpolated($"SELECT * FROM Orders WHERE OrderID = {TargetID}") .ToList(); if (ords.Count() >= 1) { foreach (var O in ords) { Console.WriteLine($"StoreID ({O.StoreID}) | CustomerID ({O.CustomerID}) | SellTime ({O.SellTime}) | Sold Items ({O.SoldItems})"); } } else { Console.WriteLine("Sorry couldn't find that order"); } } }
/// <summary> /// Used to write entry to Customer table /// </summary> public void WriteCustomer() { using (var db = new Pzero_DbContextClass()) { Console.Write("Enter First Name: "); string first = Console.ReadLine(); Console.Write("Last Name: "); string last = Console.ReadLine(); prefSto: Console.Write("Prefered store ID: "); string prefSto = Console.ReadLine(); int prefStoInt = IsInt(prefSto); Location l = new Location(); if (l.IsValidLocation(prefStoInt) == false) { Console.WriteLine("Invalid store ID try again"); goto prefSto; } //auto increment isn't behaving so int custSize; var s = db.Customers .FromSqlRaw("SELECT * FROM Customers") .ToList(); custSize = (s.Count() + 1); // its forcing me to supply an ID -_- try // It's throwing an exception for NO REASON, so Im just going to ignore it { var custs = db.Customers //.FromSqlInterpolated($"INSERT INTO Customers ( CustomerID, FName, LName, DefaultSto) VALUES ( {custSize}, {first}, {last}, 1)") .FromSqlInterpolated($"INSERT INTO Customers VALUES ( {custSize}, {first}, {last}, {prefStoInt})") .ToList(); } catch { } Console.WriteLine($"New entry added ID: {custSize}"); } }
/// <summary> /// Most complex method: used to create and place an order /// </summary> public void MakeOder() { //verify customer input and validity ---------------------------- int cust; while (true) { Console.Write("Enter your customer ID >> "); string InP = Console.ReadLine(); if (IsInt(InP) != -1) { cust = IsInt(InP); } else { continue; } Customer c = new Customer(); if (c.IsValidCustomer(cust)) { break; } else { Console.WriteLine("invalid customer ID try again"); continue; } } //verify storeID input and validity ------------------------------ int store; while (true) { Console.Write("Enter store ID to order from>> "); string InP = Console.ReadLine(); if (IsInt(InP) != -1) { store = IsInt(InP); } else { continue; } Location l = new Location(); if (l.IsValidLocation(store)) { break; } else { Console.WriteLine("invalid store ID try again"); continue; } } List <int[]> orders = new List <int[]>(); //loop go get order ids and quantities while (true) { int PID; int quantity; Console.Write("Enter an item ID you want to order>> "); string InP = Console.ReadLine(); int temp = IsInt(InP); if (temp != -1) { Product p = new Product(); if (p.IsValidProduct(temp)) { PID = IsInt(InP); } else { continue; } } else { continue; } bool repeat = false; // make sure it's not already in orders - otherwise updating may go past 0 foreach (var ord in orders) { if (PID == ord[0]) { repeat = true; } } if (repeat) { Console.Write("You already ordered that, want to add a different item? (y/n) >> "); int still = YesNo(Console.ReadLine()); if (still == 1) { continue; } else { break; } } HowMany: Console.Write("How many do you want >> "); InP = Console.ReadLine(); temp = IsInt(InP); if (temp != -1) { Location l = new Location(); int max = l.MaxAvailable(PID, store); if (temp <= max && temp > 0) { quantity = temp; } else { if (temp <= 0) { Console.WriteLine($"you need to add more than 0 of the item"); goto HowMany; } else { Console.WriteLine($"Sorry thats more than the store has, choose {max} or less"); goto HowMany; } } } else { goto HowMany; } orders.Add(new int[] { PID, quantity }); Console.Write("Want to add another item? (y/n) >> "); int ans = YesNo(Console.ReadLine()); if (ans == 1) { continue; } else { break; } } foreach (var ord in orders) { Console.WriteLine($"Ordering {ord[1]} of item {ord[0]}"); } // use variables cust, store and orders to place the order // insert orders foreach (var ord in orders) { int OID = (OrderSize() + 1); Product p = new Product(); string prod = p.GetPName(ord[0]); using (var db = new Pzero_DbContextClass()) { try { var PlaceOrds = db.Orders .FromSqlInterpolated($"INSERT INTO ORDERS VALUES({OID},{store},{cust},DATE('now'),{prod})") .ToList(); } catch { } } Location l = new Location(); // --- update inventory l.UpdateInventory(ord[0], ord[1]); } Console.WriteLine("All done"); }