/// <summary>
/// Checks to see if there are any products with the given
/// productID and returns false if there are none but true if
/// there are
/// </summary>
/// <param name="amount"></param>
/// <param name="id"></param>
/// <returns></returns>
public bool IsValidProductQuantity(int amount, int id)
{
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
try
{
// get product with the matching productID
var check = db.Products
.Where(p => p.ProductID == id)
.FirstOrDefault();
if (check.Inventory < amount)
{
return(false);
}
else
{
return(true);
}
}
catch (Microsoft.Data.Sqlite.SqliteException)
{
Console.WriteLine($"There is no customer table currently.");
return(false);
}
catch (Exception e)
{
Console.WriteLine($"Exception occurred: {e}");
return(false);
}
}
}
public void AddsCustomerToDbTest()
{
//Arrange
var options = new DbContextOptionsBuilder <StoreApp_DbContext>()
.UseInMemoryDatabase(databaseName: "AddsCustomerToDbTest")
.Options;
//Act
using (var db = new StoreApp_DbContext(options))
{
Customer location = new Customer
{
FirstName = "Michael",
LastName = "Hall",
UserName = "******"
};
db.Add(location);
db.SaveChanges();
}
//Assert
using (var context = new StoreApp_DbContext(options))
{
Assert.Equal(1, context.Customers.Count());
var customer1 = context.Customers.Where(c => c.CustomerID == 1).FirstOrDefault();
Assert.Equal(1, customer1.CustomerID);
Assert.Equal("Michael", customer1.FirstName);
Assert.Equal("Hall", customer1.LastName);
Assert.Equal("mbhall", customer1.UserName);
}
}
/// <summary>
/// Queries through the Orders table and sees if the inputted
/// order ID is a real one or not.
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool IsValidOrderID(int id)
{
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
try
{
// checks to see if any orders have the specified ID
var check = db.Orders
.Where(o => o.OrderID == id);
if (check.Count() == 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Microsoft.Data.Sqlite.SqliteException)
{
Console.WriteLine($"There is no customer table currently.");
return(false);
}
catch (Exception e)
{
Console.WriteLine($"Exception occurred: {e}");
return(false);
}
}
}
/// <summary>
/// Queries through the Customers table and sees if the inputted
/// customer ID is a real one or not.
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool IsValidCustomerID(int id)
{
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
try
{
// find customers that have the inputted ID (if any)
var check = db.Customers
.Where(c => c.CustomerID == id);
if (check.Count() == 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Microsoft.Data.Sqlite.SqliteException)
{
Console.WriteLine($"There is no customer table currently.");
return(false);
}
catch (Exception e)
{
Console.WriteLine($"Exception occurred: {e}");
return(false);
}
}
}
/// <summary>
/// Checks to see if the given storeID is a valid one or not.
/// Returns true if it is and false if not.
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool IsValidStoreID(int id)
{
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
try
{
// get any store with the matching storeID
var check = db.Stores
.AsNoTracking()
.Where(s => s.StoreID == id);
if (check.Count() == 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Microsoft.Data.Sqlite.SqliteException)
{
Console.WriteLine($"There is no customer table currently.");
return(false);
}
catch (Exception e)
{
Console.WriteLine($"Exception occurred: {e}");
return(false);
}
}
}
/// <summary>
/// Queries through the Customers table and extracts all the
/// order history and relative information for the customer
/// with the matching customer ID
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ICollection <Order> GetCustomerHistory(int id)
{
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
try
{
// gets all order data for the customer with the inputted ID
return(db.Orders
.AsNoTracking()
.Where(o => o.CustomerID == id)
.Include(customer => customer.Customer)
.Include(order => order.Product)
.ThenInclude(product => product.Store)
.OrderBy(o => o.Timestamp)
.ToList());
}
catch (Microsoft.Data.Sqlite.SqliteException)
{
Console.WriteLine($"There is no customer table currently.");
return(null);
}
catch (Exception e)
{
Console.WriteLine($"Exception occurred: {e}");
return(null);
}
}
}
public void AddsStoreToDbTest()
{
//Arrange
var options = new DbContextOptionsBuilder <StoreApp_DbContext>()
.UseInMemoryDatabase(databaseName: "AddsStoreToDbTest")
.Options;
//Act
using (var db = new StoreApp_DbContext(options))
{
Store location = new Store
{
Location = "Maryland"
};
db.Add(location);
db.SaveChanges();
}
//Assert
using (var context = new StoreApp_DbContext(options))
{
Assert.Equal(1, context.Stores.Count());
var store1 = context.Stores.Where(s => s.StoreID == 1).FirstOrDefault();
Assert.Equal(1, store1.StoreID);
Assert.Equal("Maryland", store1.Location);
}
}
/// <summary>
/// Queries through the orders and retrieves all the order
/// data for the order matching the given orderID. Also returns
/// any orders that had the same timestamp meaning they were a part
/// of the order as an order with multiple products.
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ICollection <Order> GetOrderDetails(int id)
{
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
try
{
// gets order details for the order with the matching orderID
var order = db.Orders
.AsNoTracking()
.Where(o => o.OrderID == id)
.FirstOrDefault();
var time = order.Timestamp;
// returns all orders with the specified timestamp
// for the case that multiple products are in the same order
return(db.Orders
.AsNoTracking()
.Where(o => o.Timestamp == time)
.Include(customer => customer.Customer)
.Include(order => order.Product)
.ThenInclude(product => product.Store)
.ToList());
}
catch (Microsoft.Data.Sqlite.SqliteException)
{
Console.WriteLine($"There is no customer table currently.");
return(null);
}
catch (Exception e)
{
Console.WriteLine($"Exception occurred: {e}");
return(null);
}
}
}
public void AddsProductToDbTest()
{
//Arrange
var options = new DbContextOptionsBuilder <StoreApp_DbContext>()
.UseInMemoryDatabase(databaseName: "AddsProductToDbTest")
.Options;
//Act
using (var db = new StoreApp_DbContext(options))
{
Product bar = new Product
{
StoreID = 7,
ProductName = "bar",
Inventory = 5,
Price = 10
};
db.Add(bar);
db.SaveChanges();
}
//Assert
using (var context = new StoreApp_DbContext(options))
{
Assert.Equal(1, context.Products.Count());
var product1 = context.Products.Where(p => p.StoreID == 7).FirstOrDefault();
Assert.Equal(7, product1.StoreID);
Assert.Equal(1, product1.ProductID);
}
}
public void ValidCustomerIDQueryTest()
{
//Arrange
var options = new DbContextOptionsBuilder <StoreApp_DbContext>()
.UseInMemoryDatabase(databaseName: "CustomerQueriesTests")
.Options;
//Act
using (var db = new StoreApp_DbContext(options))
{
Customer customer = new Customer
{
FirstName = "Michael",
LastName = "Hall",
UserName = "******"
};
db.Add(customer);
db.SaveChanges();
}
//Assert
using (var context = new StoreApp_DbContext(options))
{
Assert.Equal(1, context.Customers.Count());
CustomerQueries check = new CustomerQueries();
Assert.False(check.IsValidCustomerID(1));
Assert.False(check.IsValidCustomerID(2));
Assert.False(check.IsValidCustomerID(-5));
}
}
public void AddsOrderToDbTest()
{
//Arrange
var options = new DbContextOptionsBuilder <StoreApp_DbContext>()
.UseInMemoryDatabase(databaseName: "AddsOrderToDbTest")
.Options;
//Act
using (var db = new StoreApp_DbContext(options))
{
Order location = new Order
{
CustomerID = 5,
ProductID = 10,
Quantity = 3,
Timestamp = DateTime.Today
};
db.Add(location);
db.SaveChanges();
}
//Assert
using (var context = new StoreApp_DbContext(options))
{
Assert.Equal(1, context.Orders.Count());
var order1 = context.Orders.Where(o => o.OrderID == 1).FirstOrDefault();
Assert.Equal(1, order1.OrderID);
Assert.Equal(5, order1.CustomerID);
Assert.Equal(10, order1.ProductID);
Assert.Equal(3, order1.Quantity);
Assert.Equal(DateTime.Today, order1.Timestamp);
}
}
/// <summary>
/// Returns the store info for the store matching the given store ID
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public string GetStoreLocation(int id)
{
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
try
{
// get store info for store with matching store ID
var store = db.Stores
.AsNoTracking()
.Where(s => s.StoreID == id)
.FirstOrDefault();
return(store.Location);
}
catch (Microsoft.Data.Sqlite.SqliteException)
{
Console.WriteLine($"There is no customer table currently.");
return(null);
}
catch (Exception e)
{
Console.WriteLine($"Exception occurred: {e}");
return(null);
}
}
}
/// <summary>
/// Initializes the store and product database info
/// for the case that it has just been cleared or reset
/// </summary>
public void SetUpDatabase()
{
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
try
{
// adds store and product info when needed
db.Add(new Store {
Location = "New York "
});
db.Add(new Store {
Location = "Harrisburg"
});
db.Add(new Store {
Location = "Austin "
});
db.SaveChanges();
db.Add(new Product {
ProductName = "Shampoo ", StoreID = 1, Inventory = 15, Price = 6.50
});
db.Add(new Product {
ProductName = "Conditioner", StoreID = 1, Inventory = 10, Price = 5.00
});
db.Add(new Product {
ProductName = "Soap ", StoreID = 1, Inventory = 20, Price = 4.00
});
db.Add(new Product {
ProductName = "Shampoo ", StoreID = 2, Inventory = 30, Price = 5.00
});
db.Add(new Product {
ProductName = "Conditioner", StoreID = 2, Inventory = 20, Price = 4.00
});
db.Add(new Product {
ProductName = "Soap ", StoreID = 2, Inventory = 10, Price = 3.00
});
db.Add(new Product {
ProductName = "Shampoo ", StoreID = 3, Inventory = 15, Price = 4.00
});
db.Add(new Product {
ProductName = "Conditioner", StoreID = 3, Inventory = 15, Price = 4.00
});
db.Add(new Product {
ProductName = "Soap ", StoreID = 3, Inventory = 30, Price = 2.00
});
db.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine($"Exception occurred: {e}");
}
}
}
/// <summary>
/// Queries through Customers table and returns the whole table
/// </summary>
/// <returns></returns>
public ICollection <Customer> GetCustomers()
{
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
try
{
// get all the data from the Customers table
return(db.Customers
.AsNoTracking()
.ToList());
}
catch (Microsoft.Data.Sqlite.SqliteException)
{
Console.WriteLine($"There is no customer table currently.");
return(null);
}
catch (Exception e)
{
Console.WriteLine($"Exception occurred: {e}");
return(null);
}
}
}
/// <summary>
/// Queries through the Customers table and returns those that
/// contain the first and last name parameters in them.
/// </summary>
/// <param name="first"></param>
/// <param name="last"></param>
/// <returns></returns>
public ICollection <Customer> CustomerSearch(string first, string last)
{
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
/* This block of code can fail without the try/catch block:
*
* return db.Customers
* .AsNoTracking()
* .Where(c => c.FirstName.Contains(first) && c.LastName.Contains(last))
* .OrderBy(c => c.FirstName)
* .ToList();
*/
try
{
// find customers with the specified first and last names
return(db.Customers
.AsNoTracking()
.Where(c => c.FirstName.Contains(first) && c.LastName.Contains(last))
.OrderBy(c => c.FirstName)
.ToList());
}
catch (Microsoft.Data.Sqlite.SqliteException)
{
// exception for if there is not table
Console.WriteLine($"There is no customer table currently.");
return(null);
}
catch (Exception e)
{
// general exceptions
Console.WriteLine($"Exception occurred: {e}");
return(null);
}
}
}
/// <summary>
/// Queries through the Customers table to get the customer that
/// has the matching inputted ID
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Customer GetCustomer(int id)
{
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
try
{
// gets the customer info who has the inputted ID
return(db.Customers
.AsNoTracking()
.Where(c => c.CustomerID == id)
.FirstOrDefault());
}
catch (Microsoft.Data.Sqlite.SqliteException)
{
Console.WriteLine($"There is no customer table currently.");
return(null);
}
catch (Exception e)
{
Console.WriteLine($"Exception occurred: {e}");
return(null);
}
}
}
/// <summary>
/// Takes away the respective amount of product from the respective
/// store's inventory when an order is successfully placed.
/// </summary>
/// <param name="newOrder"></param>
public void UpdateInventory(Order newOrder)
{
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
try
{
// get the inventory for a store's product and update it
// based on the given order that was just placed
var product = db.Products
.Where(p => p.ProductID == newOrder.ProductID)
.FirstOrDefault();
product.Inventory -= newOrder.Quantity;
db.SaveChanges();
}
catch (Microsoft.Data.Sqlite.SqliteException)
{
Console.WriteLine($"There is no customer table currently.");
}
catch (Exception e)
{
Console.WriteLine($"Exception occurred: {e}");
}
}
}
/// <summary>
/// Checks to see if there is any product in the product table
/// with the given productID
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool IsValidProductID(int id)
{
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
try
{
// get any products with the given productID
var check = db.Products
.Where(c => c.ProductID == id);
// get the inventory for that particular product
var inventoryCheck = db.Products
.AsNoTracking()
.Where(c => c.ProductID == id)
.FirstOrDefault();
if (check.Count() == 0 || inventoryCheck.Inventory == 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Microsoft.Data.Sqlite.SqliteException)
{
Console.WriteLine($"There is no customer table currently.");
return(false);
}
catch (Exception e)
{
Console.WriteLine($"Exception occurred: {e}");
return(false);
}
}
}
/// <summary>
/// Returns the product info for the product that has
/// the given productID
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Product GetProductName(int id)
{
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
try
{
// gets single product info for product with matching ID
return(db.Products
.AsNoTracking()
.Where(p => p.ProductID == id)
.FirstOrDefault());
}
catch (Microsoft.Data.Sqlite.SqliteException)
{
Console.WriteLine($"There is no customer table currently.");
return(null);
}
catch (Exception e)
{
Console.WriteLine($"Exception occurred: {e}");
return(null);
}
}
}
/// <summary>
/// Queries through products and returns all the product data
/// (the entire table)
/// </summary>
/// <returns></returns>
public ICollection <Product> GetProducts()
{
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
try
{
// gets all the product data from the table
return(db.Products
.AsNoTracking()
.Include(p => p.Store)
.ToList());
}
catch (Microsoft.Data.Sqlite.SqliteException)
{
Console.WriteLine($"There is no customer table currently.");
return(null);
}
catch (Exception e)
{
Console.WriteLine($"Exception occurred: {e}");
return(null);
}
}
}
/// <summary>
/// input/output for the process of adding a new order with all
/// the validation taking place along the way and finally adding
/// a new order with the given information.
/// </summary>
public void AddNewOrder()
{
// declare new instance(s)
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
OrderCreation createOrder = new OrderCreation();
CustomerQueries checkCustomer = new CustomerQueries();
Order newOrder = new Order();
Console.WriteLine("Please enter the customerID of your Customer placing an order.");
do
{
string input = Console.ReadLine();
if (input == "cancel")
{
return;
}
// check if input is an int
while (!createOrder.IsValidNum(input))
{
Console.WriteLine("Invalid customerID number, please enter another.");
input = Console.ReadLine();
if (input == "cancel")
{
return;
}
}
// check if there is a customer with the inputted ID
int id = createOrder.StringToInt(input);
if (checkCustomer.IsValidCustomerID(id))
{
newOrder.CustomerID = id;
}
else
{
Console.WriteLine("There is no Customer with this ID, please enter another.");
newOrder.CustomerID = 0;
}
} while (newOrder.CustomerID == 0); // repeat if there is no customer with the ID
// display all the available products
ProductQueries checkProducts = new ProductQueries();
var products = checkProducts.GetProducts();
Console.WriteLine("Here are all the available products:");
Console.WriteLine("ID\tStore\t\tName\t\tInventory\tPrice");
foreach (var p in products)
{
Console.WriteLine($"{p.ProductID}\t{p.Store.Location}\t{p.ProductName}" +
$"\t{p.Inventory}\t\t{p.Price}");
}
bool multipleProducts;
int productCount = 0;
do
{
Console.WriteLine("Please enter the ID of the product being ordered");
do
{
string input = Console.ReadLine();
if (input == "cancel")
{
return;
}
// check if input is an int
while (!createOrder.IsValidNum(input))
{
Console.WriteLine("Invalid product ID number, please enter another.");
input = Console.ReadLine();
if (input == "cancel")
{
return;
}
}
int id = createOrder.StringToInt(input);
// check if there is a product with the inputted ID
if (checkProducts.IsValidProductID(id))
{
newOrder.ProductID = id;
}
else
{
Console.WriteLine("There is no product with this ID or there is none left, please enter another.");
newOrder.ProductID = 0;
}
} while (newOrder.ProductID == 0); // repeat if no product with that ID
var product = checkProducts.GetProductName(newOrder.ProductID);
Console.WriteLine($"For buying, specify the number of {product.ProductName}");
do
{
string input = Console.ReadLine();
if (input == "cancel")
{
return;
}
// check if input is an int
while (!createOrder.IsValidNum(input))
{
Console.WriteLine("Invalid amount, please enter another.");
input = Console.ReadLine();
if (input == "cancel")
{
return;
}
}
int amount = createOrder.StringToInt(input);
// check if the inventory is high enough for given amount
if (amount == 0)
{
Console.WriteLine("Please specify an amount");
}
else if (createOrder.IsUnreasonableQuantity(amount))
{
// if the amount requested is unreasonable (>=10)
Console.WriteLine($"{amount} is an unreasonable amount of {product.ProductName}");
newOrder.Quantity = 0;
}
else if (checkProducts.IsValidProductQuantity(amount, newOrder.ProductID))
{
// if there is enough product and it is reasonable
newOrder.Quantity = amount;
}
else
{
Console.WriteLine($"There is not {amount} available at this store, please enter another amount.");
newOrder.Quantity = 0;
}
} while (newOrder.Quantity == 0); // repeat if not enough product or unreasonable
Console.WriteLine("Would you like to include another product in this order (yes or no)?");
string addProduct = Console.ReadLine();
if (addProduct == "cancel")
{
return;
}
// check if they are saying yes or no to extra product
while (addProduct != "yes" && addProduct != "no")
{
Console.WriteLine("Please pick put in one of the two");
addProduct = Console.ReadLine();
if (addProduct == "cancel")
{
return;
}
}
if (addProduct == "yes")
{
multipleProducts = true;
}
else
{
multipleProducts = false;
}
productCount++;
if (productCount == 1)
{
// keep same timestamp for multiple product order
newOrder.Timestamp = createOrder.GetTimeStamp();
}
db.Add <Order>(newOrder);
db.SaveChanges();
StoreQueries updateStore = new StoreQueries();
updateStore.UpdateInventory(newOrder);
newOrder.OrderID++;
} while (multipleProducts); // go back if they wanted another product
Console.WriteLine("Order successfully placed! Hit enter to go back to menu.");
Console.ReadLine();
}
}
/// <summary>
/// input/output for the process of adding a new customer with all
/// the validation taking place along the way and finally adding
/// a new customer with the given information.
/// </summary>
public void AddNewCustomer()
{
// create new instance
using (StoreApp_DbContext db = new StoreApp_DbContext())
{
CustomerCreation createCustomer = new CustomerCreation();
Customer newCustomer = new Customer();
Console.WriteLine("What's the first name of the Customer?");
newCustomer.FirstName = Console.ReadLine();
if (newCustomer.FirstName == "cancel")
{
return;
}
while (!createCustomer.IsValidInputName(newCustomer.FirstName))
{
Console.WriteLine("Invalid first name, please enter another .");
newCustomer.FirstName = Console.ReadLine();
if (newCustomer.FirstName == "cancel")
{
return;
}
}
Console.WriteLine("What's the last name of the customer?");
newCustomer.LastName = Console.ReadLine();
if (newCustomer.LastName == "cancel")
{
return;
}
while (!createCustomer.IsValidInputName(newCustomer.LastName))
{
Console.WriteLine("Invalid last name, please enter another");
newCustomer.LastName = Console.ReadLine();
if (newCustomer.LastName == "cancel")
{
return;
}
}
Console.WriteLine("What would you like the username for the customer to be?");
newCustomer.UserName = Console.ReadLine();
if (newCustomer.UserName == "cancel")
{
return;
}
while (!createCustomer.IsValidUserName(newCustomer.UserName))
{
Console.WriteLine("Invalid username, has to be 8 to 20 characters.");
newCustomer.UserName = Console.ReadLine();
if (newCustomer.UserName == "cancel")
{
return;
}
}
db.Add <Customer>(newCustomer);
db.SaveChanges();
Console.WriteLine("Customer successfully added! Hit enter to go back to menu.");
Console.ReadLine();
}
}
