package cpsc4620; import java.io.IOException; import java.math.BigDecimal; import java.sql.Date; import java.sql.*; import java.util.*; import java.util.regex.Pattern; /** * A utility class to help add and retrieve information from a MySQL database. */ public final class DBNinja { // Order type string constants public final static String pickup = "pickup"; public final static String delivery = "delivery"; public final static String dine_in = "dinein"; // Pizza size string constants public final static String size_s = "Small"; public final static String size_m = "Medium"; public final static String size_l = "Large"; public final static String size_xl = "XLarge"; // Pizza crust type string constants public final static String crust_thin = "Thin"; public final static String crust_orig = "Original"; public final static String crust_pan = "Pan"; public final static String crust_gf = "Gluten-Free"; // Custom Comparator for comparing Order objects by OrderTimestamp from most recent to oldest public static Comparator OrderTimestampComparator = new Comparator<>() { @Override public int compare(Order o1, Order o2) { return Date.valueOf(o2.getDate()).compareTo(Date.valueOf(o1.getDate())); } }; // Custom Comparator for comparing Customer objects by CustomerID public static Comparator CustomerIDComparator = new Comparator<>() { @Override public int compare(Customer c1, Customer c2) { return c1.getCustID() - c2.getCustID(); } }; // Database connection handler private static Connection conn; /** * Function which attempts a MySQL database connection via JDBC and handles exceptions. * * @return Success of database connection */ private static boolean connect_to_db() { try { conn = DBConnector.make_connection(); return true; } catch (SQLException e) { return false; } catch (IOException e) { return false; } } /** * Helper function which assembles a printable format string based on provided lengths, objects being printed, and * justifications. * * @param lengths List of lengths for formatting * @param toPrint List of objects to be printed * @param justifications List of booleans where true = left justification and false = right justification * @return Assembled, printable format string * @precondition lengths.size() = toPrint.size() = justifications.size() AND NOT lengths.isEmpty() * AND NOT toPrint.isEmpty() AND NOT justifications.isEmpty() */ public static String getPrintableFormatString(ArrayList lengths, ArrayList toPrint, ArrayList justifications) { String formatString = ""; for (Object object : toPrint) { int index = toPrint.indexOf(object); // Add basic format specifier structure formatString += "%" + (justifications.get(index) ? "-" : "") + lengths.get(index).toString(); // Add format specifier type character based on object type if (object instanceof Number && !(object instanceof Float || object instanceof Double)) { formatString += "d "; } else if (object instanceof Float || object instanceof Double) { formatString += "."; formatString += (Double.toString((Double) object).length() - Double.toString((Double) object).indexOf('.') - 1); formatString += "f "; } else if (object instanceof Character) { formatString += "c "; } else { formatString += "s "; } } return String.format(formatString.trim(), toPrint.toArray()); } /** * Helper function which assembles an SQL PreparedStatement, executes an SQL update/insertion, and * returns the number of rows affected. * * @param query SQL query to execute * @param params List of parameters to add to PreparedStatement * @return Number of rows affected by update * @throws SQLException * @precondition Query.isEmpty() = false and database connection is established */ public static int executeSQLUpdate(String query, ArrayList params) throws SQLException { // Make sure database connection is open if (conn == null || conn.isClosed()) { connect_to_db(); } // Create PreparedStatement based on supplied query PreparedStatement ps = conn.prepareStatement(query); // Loop through PreparedStatement parameters for (int i = 0; i < params.size(); i++) { // Set parameter based on object type ps.setObject(i + 1, params.get(i).toString()); } // Execute query and return number of rows affected return ps.executeUpdate(); } /** * Helper function which assembles an SQL PreparedStatement, executes an SQL retrieval, and * returns a list of returned rows with associated column names. * * @param query SQL query to execute * @param params List of parameters to add to PreparedStatement * @return List of result sets * @throws SQLException */ public static ArrayList> executeSQLRetrieve(String query, ArrayList params) throws SQLException { // Make sure database connection is open if (conn == null || conn.isClosed()) { connect_to_db(); } // Create PreparedStatement based on supplied query PreparedStatement ps = conn.prepareStatement(query); // Loop through PreparedStatement parameters for (int i = 0; i < params.size(); i++) { // Set parameter based on object type ps.setObject(i + 1, params.get(i).toString()); } // Initialize Array of Maps where each key is SQL column name and each value is SQL column value ArrayList> results = new ArrayList<>(); // Execute query ResultSet rs = ps.executeQuery(); // Loop through returned results while (rs.next()) { // Create new Map for result Map result = new HashMap<>(); ResultSetMetaData rsmd = rs.getMetaData(); // Get column names and values for Map for (int i = 1; i <= rsmd.getColumnCount(); i++) { // Handle null values if (rs.getObject(rsmd.getColumnName(i)) == null) { continue; } // Handle SQL dates if (Pattern.compile("\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}.\\d") .matcher(rs.getObject(rsmd.getColumnName(i)).toString()).matches()) { result.put(rsmd.getColumnName(i), rs.getDate(rsmd.getColumnName(i))); } else { result.put(rsmd.getColumnName(i), rs.getObject(rsmd.getColumnName(i))); } } // Add result to list results.add(result); } // Close ResultSet (best practice) rs.close(); return results; } /** * Function which determines an Order's type (dine-in, pickup, or delivery) and * adds entries to the appropriate database tables. * * @param o Initialized Order object * @throws SQLException * @throws IOException */ public static void addOrder(Order o) throws SQLException, IOException { String orderType; // Get order type and normalize if (o instanceof DineinOrder) { // dine-in order orderType = dine_in; } else if (o instanceof PickupOrder) { // pickup order orderType = pickup; } else { // delivery order orderType = delivery; } // SQL query to insert into the order table String query = "INSERT INTO `order`(OrderType, OrderTimestamp, OrderIsComplete, OrderTotalCost, OrderTotalPrice) VALUES (?, ?, ?, ?, ?);"; // Assemble query parameters ArrayList params = new ArrayList<>(Arrays.asList(orderType, o.getDate(), o.getIsComplete(), o.getBusPrice(), o.getCustPrice())); // Execute query and ensure results if (executeSQLUpdate(query, params) != 1) { throw new SQLException(); } // Get the OrderID of the order just added to the database o.setOrderID(getLastOrderID()); // Handle database entries for each order type if (orderType.equals(dine_in)) { // dine-in order // SQL query to insert into the dine_in_order table query = "INSERT INTO dine_in_order(DineInOrderOrderID, DineInOrderTableNumber) VALUES (?, ?);"; // Assemble query parameters params = new ArrayList<>(Arrays.asList(o.getOrderID(), ((DineinOrder) o).getTableNum())); // Execute query and ensure results if (executeSQLUpdate(query, params) != 1) { throw new SQLException(); } } else if (orderType.equals(pickup)) { // pickup order // SQL query to insert into the pickup_order table query = "INSERT INTO pickup_order(PickupOrderOrderID, PickupOrderCustomerID) VALUES (?, ?);"; // Assemble query parameters params = new ArrayList<>(Arrays.asList(o.getOrderID(), o.getCustID())); // Execute query and ensure results if (executeSQLUpdate(query, params) != 1) { throw new SQLException(); } } else { // delivery order // SQL query to insert into the delivery_order table query = "INSERT INTO delivery_order(DeliveryOrderOrderID, DeliveryOrderCustomerID) VALUES (?, ?);"; // Assemble query parameters params = new ArrayList<>(Arrays.asList(o.getOrderID(), o.getCustID())); // Execute query and ensure results if (executeSQLUpdate(query, params) != 1) { throw new SQLException(); } } // Loop through all the pizzas associated with an order for (Pizza pizza : o.getPizzaList()) { // Update the PizzaOrderID based on the OrderID of the order just added to the database pizza.setOrderID(o.getOrderID()); // Associate pizza with order in database addPizza(pizza); } // Loop through all the discounts associated with an order for (Discount discount : o.getDiscountList()) { // Associate discount with order in database useOrderDiscount(o, discount); } conn.close(); } /** * Function that adds a pizza to the database. * * @param p Initialized Pizza object * @throws SQLException * @throws IOException */ public static void addPizza(Pizza p) throws SQLException, IOException { // SQL query to insert into the pizza table String query = "INSERT INTO pizza(PizzaOrderID, PizzaSize, PizzaCrustType, PizzaCostToCompany, PizzaCostToCustomer, PizzaReadyState) VALUES (?, ?, ?, ?, ?, ?);"; // Assemble query parameters ArrayList params = new ArrayList<>(Arrays.asList(p.getOrderID(), p.getSize(), p.getCrustType(), p.getBusPrice(), p.getCustPrice(), p.getPizzaState())); // Execute query and ensure results if (executeSQLUpdate(query, params) != 1) { throw new SQLException(); } // Update PizzaID p.setPizzaID(getLastPizzaID()); // Loop through toppings associated with Pizza object for (int i = 0; i < p.getToppings().size(); i++) { // Associate pizza with topping in database useTopping(p, p.getToppings().get(i), p.getIsDoubleArray()[p.getToppings().get(i).getTopID() - 1]); } // Loop through discounts associated with Pizza object for (Discount discount : p.getDiscounts()) { // Associate pizza with discount in database usePizzaDiscount(p, discount); } conn.close(); } /** * Function which gets the PizzaID of the last pizza in the database. * * @return PizzaID */ public static int getLastPizzaID() throws SQLException { // SQL query to retrieve from pizza table String query = "SELECT MAX(PizzaID) FROM pizza;"; // Execute query and retrieve results ArrayList> results = executeSQLRetrieve(query, new ArrayList<>()); conn.close(); // Get PizzaID return (int) results.get(0).get("MAX(PizzaID)"); } /** * Function which updates topping inventory and associates a topping with a pizza in the database. * * @param p Initialized Pizza object * @param t Initialized Topping object * @param isDoubled Whether the topping is doubled * @throws SQLException * @throws IOException */ public static void useTopping(Pizza p, Topping t, boolean isDoubled) throws SQLException, IOException { // SQL query to update the topping table String query = "UPDATE topping SET ToppingCurrentInv = ToppingCurrentInv - ? WHERE ToppingID = ?;"; // Assemble query parameters ArrayList params = new ArrayList<>(Arrays.asList(t.getVarAMT(p.getSize(), isDoubled), t.getTopID())); // Execute query and ensure results if (executeSQLUpdate(query, params) != 1) { throw new SQLException(); } // SQL query to insert into the customization table query = "INSERT INTO customization VALUES (?, ?, ?);"; // Assemble query parameters params = new ArrayList<>(Arrays.asList(p.getPizzaID(), t.getTopID(), isDoubled ? 1 : 0)); // Execute query and ensure results if (executeSQLUpdate(query, params) != 1) { throw new SQLException(); } conn.close(); } /** * Function which associates a discount with a pizza in the database. * * @param p Initialized Pizza object * @param d Initialized Discount object * @throws SQLException * @throws IOException */ public static void usePizzaDiscount(Pizza p, Discount d) throws SQLException, IOException { // SQL query to insert to pizza_discount table String query = "INSERT INTO pizza_discount(PizzaDiscountPizzaID, PizzaDiscountDiscountID) VALUES (?, ?);"; // Assemble query parameters ArrayList params = new ArrayList<>(Arrays.asList(p.getPizzaID(), d.getDiscountID())); // Execute query and ensure results if (executeSQLUpdate(query, params) != 1) { throw new SQLException(); } conn.close(); } /** * Function which associates a discount with an order in the database. * * @param o Initialized Order object * @param d Initialized Discount object * @throws SQLException * @throws IOException */ public static void useOrderDiscount(Order o, Discount d) throws SQLException, IOException { // SQL query to insert to order_discount table String query = "INSERT INTO order_discount(OrderDiscountOrderID, OrderDiscountDiscountID) VALUES (?, ?);"; // Assemble query parameters ArrayList params = new ArrayList<>(Arrays.asList(o.getOrderID(), d.getDiscountID())); // Execute query and ensure results if (executeSQLUpdate(query, params) != 1) { throw new SQLException(); } conn.close(); } /** * Function which adds a customer to the database. * * @param c Initialized Customer object * @throws SQLException * @throws IOException */ public static void addCustomer(Customer c) throws SQLException, IOException { // SQL query to insert into the customer table String query = "INSERT INTO customer(CustomerFirstName, CustomerLastName, CustomerPhone"; // Check whether the customer's address is populated and complete query accordingly if (c.getAddress() != null) { // Address is populated query += ", CustomerStreet, CustomerCity, CustomerState, CustomerZipCode) VALUES (?, ?, ?, ?, ?, ?, ?);"; } else { // Address is not populated query += ") VALUES (?, ?, ?);"; } // Assemble query parameters ArrayList params; if (c.getAddress() == null) { params = new ArrayList<>(Arrays.asList(c.getFName(), c.getLName(), c.getPhone())); } else { String[] address = c.getAddress().split("\n"); params = new ArrayList<>(Arrays.asList(c.getFName(), c.getLName(), c.getPhone(), address[0], address[1], address[2], address[3])); } // Execute query and ensure results if (executeSQLUpdate(query, params) != 1) { throw new SQLException(); } conn.close(); } /** * Function which updates a customer's address in the database according to the supplied CustomerID. * * @param CustID CustomerID of customer to update * @param street Customer street address * @param city Customer city * @param state Customer state * @param zip Customer zip code */ public static void updateCustomerAddress(int CustID, String street, String city, String state, String zip) throws SQLException { // SQL query to update customer table String query = "UPDATE customer SET CustomerStreet = ?, CustomerCity = ?, CustomerState = ?, CustomerZipCode = ? WHERE CustomerID = ?;"; // Assemble query parameters ArrayList params = new ArrayList<>(Arrays.asList(street, city, state, zip, CustID)); // Execute query and ensure results if (executeSQLUpdate(query, params) != 1) { throw new SQLException(); } conn.close(); } /** * Function that finds an order in the database and marks the order as complete. * * @param o Initialized Order object * @throws SQLException * @throws IOException */ public static void completeOrder(Order o) throws SQLException, IOException { // SQL query to update the order table String query = "UPDATE `order` SET OrderIsComplete = TRUE WHERE OrderId = ?;"; // Assemble query parameters ArrayList params = new ArrayList<>(List.of(o.getOrderID())); // Execute query and ensure results if (executeSQLUpdate(query, params) != 1) { throw new SQLException(); } // Loop through all the pizzas in an order for (Pizza pizza : o.getPizzaList()) { // SQL query to update the pizza table query = "UPDATE pizza SET PizzaReadyState = 'ready' WHERE PizzaOrderID = ?;"; // Assemble query parameters params = new ArrayList<>(List.of(o.getOrderID())); // Execute query and ensure results if (executeSQLUpdate(query, params) != 1) { throw new SQLException(); } } conn.close(); } /** * Function that retrieves a list of orders from the database. * * @param openOnly Whether to return only a list of open (not completed) orders * @return ArrayList of Order objects * @throws SQLException * @throws IOException */ public static ArrayList getOrders(boolean openOnly) throws SQLException, IOException { // SQL queries that join order table with appropriate order type table String diOrderQuery = "SELECT * FROM `order` JOIN dine_in_order ON `order`.OrderID = dine_in_order.DineInOrderOrderID"; String pOrderQuery = "SELECT * FROM `order` JOIN pickup_order ON `order`.OrderID = pickup_order.PickupOrderOrderID"; String dOrderQuery = "SELECT * FROM `order` JOIN delivery_order ON `order`.OrderID = delivery_order.DeliveryOrderOrderID JOIN customer ON delivery_order.DeliveryOrderCustomerID = customer.CustomerID"; // Also joins customer table // If openOnly then modify each SQL query to include WHERE clause if (openOnly) { diOrderQuery += " WHERE `order`.OrderIsComplete = FALSE;"; pOrderQuery += " WHERE `order`.OrderIsComplete = FALSE;"; dOrderQuery += " WHERE `order`.OrderIsComplete = FALSE;"; // Else, terminate SQL queries } else { diOrderQuery += ";"; pOrderQuery += ";"; dOrderQuery += ";"; } // Create an ArrayList to store orders ArrayList orders = new ArrayList<>(); // Execute query and retrieve results ArrayList> results = executeSQLRetrieve(diOrderQuery, new ArrayList<>()); for (Map result : results) { // Create a new DineinOrder object DineinOrder order = new DineinOrder((Integer) result.get("OrderID"), 0, result.get("OrderTimestamp").toString(), ((BigDecimal) result.get("OrderTotalPrice")).doubleValue(), ((BigDecimal) result.get("OrderTotalCost")).doubleValue(), ((Boolean) result.get("OrderIsComplete")) ? 1 : 0, (Integer) result.get("DineInOrderTableNumber")); // Add to orders list orders.add(order); } // Execute query and retrieve results results = executeSQLRetrieve(pOrderQuery, new ArrayList<>()); for (Map result : results) { // Create a new PickupOrder object PickupOrder order = new PickupOrder((Integer) result.get("OrderID"), (Integer) result.get("PickupOrderCustomerID"), result.get("OrderTimestamp").toString(), ((BigDecimal) result.get("OrderTotalPrice")).doubleValue(), ((BigDecimal) result.get("OrderTotalCost")).doubleValue(), ((Boolean) result.get("PickupOrderIsPickedUp")) ? 1 : 0, ((Boolean) result.get("OrderIsComplete")) ? 1 : 0); // Add to orders list orders.add(order); } // Execute query and retrieve results results = executeSQLRetrieve(dOrderQuery, new ArrayList<>()); for (Map result : results) { // Create a new DeliveryOrder object DeliveryOrder order = new DeliveryOrder((Integer) result.get("OrderID"), (Integer) result.get("DeliveryOrderCustomerID"), result.get("OrderTimestamp").toString(), ((BigDecimal) result.get("OrderTotalPrice")).doubleValue(), ((BigDecimal) result.get("OrderTotalCost")).doubleValue(), ((Boolean) result.get("OrderIsComplete")) ? 1 : 0, (result.get("CustomerStreet").toString() + "\n" + result.get("CustomerCity").toString() + "\n" + result.get("CustomerState").toString() + "\n" + result.get("CustomerZipCode").toString())); // Add to orders list orders.add(order); } // Get pizzas associated with each order for (Order order : orders) { // SQL query to retrieve from pizza table String pizzaQuery = "SELECT * FROM pizza WHERE PizzaOrderID = ?;"; // Assemble query parameters ArrayList params = new ArrayList<>(List.of(order.getOrderID())); // Execute query and retrieve results ArrayList> pResults = executeSQLRetrieve(pizzaQuery, params); for (Map pResult : pResults) { // Create a new Pizza object Pizza pizza = new Pizza((Integer) pResult.get("PizzaID"), pResult.get("PizzaSize").toString(), pResult.get("PizzaCrustType").toString(), order.getOrderID(), pResult.get("PizzaReadyState").toString(), order.getDate(), ((BigDecimal) pResult.get("PizzaCostToCustomer")).doubleValue(), ((BigDecimal) pResult.get("PizzaCostToCompany")).doubleValue()); // SQL query to retrieve from customization table String customizationQuery = "SELECT * FROM customization JOIN topping ON customization.CustomizationToppingID = topping.ToppingID WHERE CustomizationPizzaID = ?;"; // Assemble query parameters params = new ArrayList<>(List.of(pizza.getPizzaID())); // Execute query and retrieve results ArrayList> tResults = executeSQLRetrieve(customizationQuery, params); for (Map tResult : tResults) { // Create a new Topping object Topping topping = new Topping((Integer) tResult.get("ToppingID"), tResult.get("ToppingName").toString(), ((BigDecimal) tResult.get("ToppingAmtUsedPersonal")).doubleValue(), ((BigDecimal) tResult.get("ToppingAmtUsedMedium")).doubleValue(), ((BigDecimal) tResult.get("ToppingAmtUsedLarge")).doubleValue(), ((BigDecimal) tResult.get("ToppingAmtUsedXLarge")).doubleValue(), ((BigDecimal) tResult.get("ToppingPrice")).doubleValue(), ((BigDecimal) tResult.get("ToppingCost")).doubleValue(), ((BigDecimal) tResult.get("ToppingMinimumInv")).doubleValue(), ((BigDecimal) tResult.get("ToppingCurrentInv")).doubleValue()); // Save current price and cost double currentPrice = pizza.getCustPrice(); double currentCost = pizza.getBusPrice(); // Add topping to pizza pizza.addToppings(topping, ((Boolean) tResult.get("CustomizationToppingIsDoubled"))); // Offset customer price and business cost pizza.setCustPrice(currentPrice); pizza.setBusPrice(currentCost); } // SQL query to retrieve from pizza_discount table String discountQuery = "SELECT * FROM pizza_discount JOIN discount ON pizza_discount.PizzaDiscountDiscountID = discount.DiscountID WHERE PizzaDiscountPizzaID = ?;"; // Assemble query parameters params = new ArrayList<>(List.of(pizza.getPizzaID())); // Execute query and retrieve results ArrayList> dResults = executeSQLRetrieve(discountQuery, params); for (Map dResult : dResults) { // Create a new Discount object Discount discount = new Discount((Integer) dResult.get("DiscountID"), dResult.get("DiscountName").toString(), ((BigDecimal) dResult.get("DiscountDollarAmt")).doubleValue(), false); // Save current pizza price double currentPrice = pizza.getCustPrice(); // Add discount to pizza pizza.addDiscounts(discount); // Offset customer price pizza.setCustPrice(currentPrice); } // Add pizza to order order.addPizza(pizza); } // SQL query to retrieve from order_discount table String discountQuery = "SELECT * FROM order_discount JOIN discount ON order_discount.OrderDiscountDiscountID = discount.DiscountID WHERE order_discount.OrderDiscountOrderID = ?;"; // Assemble query parameters params = new ArrayList<>(List.of(order.getOrderID())); // Execute query and retrieve results results = executeSQLRetrieve(discountQuery, params); for (Map result : results) { // Create a new Discount object Discount discount = new Discount((Integer) result.get("DiscountID"), result.get("DiscountName").toString(), ((BigDecimal) result.get("DiscountPercent")).doubleValue(), true); // Save current customer price double currentPrice = order.getCustPrice(); // Add discount to order order.addDiscount(discount); // Offset customer price order.setCustPrice(currentPrice); } } // Sort orders by OrderTimestamp Collections.sort(orders, OrderTimestampComparator); conn.close(); return orders; } /** * Helper function which prints the list of orders. * * @param orders List of Order objects */ public static void printOrders(ArrayList orders) { for (Order order : orders) { System.out.println(order.toSimplePrint()); } } /** * Function that retrieves the OrderID of the most recent order in the database. * * @return OrderID of most recent order */ public static int getLastOrderID() throws SQLException { // SQL query to retrieve from order table String query = "SELECT * FROM `order` WHERE OrderID = (SELECT MAX(OrderID) FROM `order`);"; // Execute query and retrieve results ArrayList> results = executeSQLRetrieve(query, new ArrayList<>()); conn.close(); // Extract and return OrderID return (int) results.get(0).get("OrderID"); } /** * Function that retrieves the most recent order in the database. * * @return Most recent order as Order object * @throws SQLException * @throws IOException */ public static Order getLastOrder() throws SQLException, IOException { return getOrderByID(getLastOrderID()); } /** * Helper function which determines if an order exists based on its OrderID. * * @param orders List of orders to check * @param orderID OrderID being searched for * @return Whether order with specified OrderID exists */ public static boolean orderExists(ArrayList orders, int orderID) { // Loop through orders for (Order order : orders) { if (order.getOrderID() == orderID) { return true; } } return false; } /** * Helper function which returns an Order object matching the supplied OrderID. * * @param orderID OrderID being searched for * @return Found Order object or null * @throws SQLException * @throws IOException */ public static Order getOrderByID(int orderID) throws SQLException, IOException { // Get list of orders ArrayList orders = getOrders(false); // Loop through orders for (Order order : orders) { if (order.getOrderID() == orderID) { return order; } } return null; } /** * Function that retrieves a list of orders from the database with a timestamp * matching the specified date. * * @param date Date string in format 'YYYY-MM-DD HH:mm:ss' * @return ArrayList of orders with matching dates as Order objects * @throws SQLException * @throws IOException */ public static ArrayList getOrdersByDate(String date) throws SQLException, IOException { // Use existing method to get a list of all orders ArrayList orders = getOrders(false); // Create a new ArrayList to store orders matching the target date ArrayList ordersByDate = new ArrayList<>(); // Loop through the orders for (Order order : orders) { // If the date matches, add it to ordersByDate if (order.getDate().equals(date)) { ordersByDate.add(order); } } return ordersByDate; } /** * Function which prints discounts. * * @param discounts List of discounts from database */ public static void printDiscounts(ArrayList discounts) { for (Discount discount : discounts) { System.out.println(discount.toString()); } } /** * Function that retrieves a list of discounts from the database. * * @return ArrayList of discounts represented as Discount objects * @throws SQLException * @throws IOException */ public static ArrayList getDiscountList() throws SQLException, IOException { // Create an ArrayList to store Discount objects ArrayList discounts = new ArrayList<>(); // SQL query to retrieve from the discount table String query = "SELECT * FROM discount;"; // Execute query and retrieve results ArrayList> results = executeSQLRetrieve(query, new ArrayList<>()); for (Map result : results) { // Create a new Discount object Discount discount = new Discount((Integer) result.get("DiscountID"), result.get("DiscountName").toString(), result.get("DiscountType").toString().equals("order") ? ((BigDecimal) result.get("DiscountPercent")).doubleValue() : ((BigDecimal) result.get("DiscountDollarAmt")).doubleValue(), result.get("DiscountType").toString().equals("order")); // Add discount to discounts array discounts.add(discount); } conn.close(); return discounts; } /** * Function that retrieves a discount from the database matching the specified name. * * @param name Name of discount being searched for * @return Discount matching the specified name or null if no match * @throws SQLException * @throws IOException */ public static Discount findDiscountByName(String name) throws SQLException, IOException { // Use existing method to get list of discounts ArrayList discounts = getDiscountList(); // Loop through discounts for (Discount discount : discounts) { // If a match is found for discount name, return the discount if (discount.getDiscountName().equals(name)) { return discount; } } // If no match has been found return null; } /** * Function that retrieves a list of customers from the database. * * @return ArrayList of customers represented as Customer objects * @throws SQLException * @throws IOException */ public static ArrayList getCustomerList() throws SQLException, IOException { // Create an ArrayList to store Customer objects ArrayList customers = new ArrayList<>(); // SQL query to retrieve from the customer table String query = "SELECT * FROM customer ORDER BY CustomerLastName, CustomerFirstName, CustomerPhone;"; // Execute query and retrieve results ArrayList> results = executeSQLRetrieve(query, new ArrayList<>()); for (Map result : results) { // Create a new Customer object Customer customer = new Customer((Integer) result.get("CustomerID"), result.get("CustomerFirstName").toString(), result.get("CustomerLastName").toString(), result.get("CustomerPhone").toString()); // Check to see if returned row has address attributes populated if (result.containsKey("CustomerStreet") && result.containsKey("CustomerCity") && result.containsKey("CustomerState") && result.containsKey("CustomerZipCode")) { // Update address attributes customer.setAddress(result.get("CustomerStreet").toString(), result.get("CustomerCity").toString(), result.get("CustomerState").toString(), result.get("CustomerZipCode").toString()); } // Add customer to list customers.add(customer); } conn.close(); return customers; } /** * Function that retrieves the CustomerID of the last customer record in the database. * * @return CustomerID of last customer */ public static int getLatestCustomerID() throws SQLException, IOException { // Use existing method to get all customers ArrayList customers = getCustomerList(); // Sort customers list Collections.sort(customers, CustomerIDComparator); return customers.get(customers.size() - 1).getCustID(); } /** * Function that retrieves the customer with the supplied phone number. * * @param phoneNumber Phone number being searched for in format '##########' * @return Customer with matching phone number as a Customer object or null if no match * @throws SQLException * @throws IOException */ public static Customer findCustomerByPhone(String phoneNumber) throws SQLException, IOException { // Use existing method to get all customers ArrayList customers = getCustomerList(); // Loop through customers for (Customer customer : customers) { // If the customer's phone matches the target, return it if (customer.getPhone().equals(phoneNumber)) { return customer; } } // If a matching customer has not been found return null; } /** * Function which retrieves a list of toppings from the database. * * @return ArrayList of toppings represented as Topping objects * @throws SQLException * @throws IOException */ public static ArrayList getToppingList() throws SQLException, IOException { // Create an ArrayList to store Topping objects ArrayList toppings = new ArrayList<>(); // SQL query to retrieve from the topping table String query = "SELECT * FROM topping ORDER BY ToppingName ASC;"; // Execute query and retrieve results ArrayList> results = executeSQLRetrieve(query, new ArrayList<>()); for (Map result : results) { // Create a new Topping object Topping topping = new Topping((Integer) result.get("ToppingID"), result.get("ToppingName").toString(), ((BigDecimal) result.get("ToppingAmtUsedPersonal")).doubleValue(), ((BigDecimal) result.get("ToppingAmtUsedMedium")).doubleValue(), ((BigDecimal) result.get("ToppingAmtUsedLarge")).doubleValue(), ((BigDecimal) result.get("ToppingAmtUsedXLarge")).doubleValue(), ((BigDecimal) result.get("ToppingPrice")).doubleValue(), ((BigDecimal) result.get("ToppingCost")).doubleValue(), ((BigDecimal) result.get("ToppingMinimumInv")).doubleValue(), ((BigDecimal) result.get("ToppingCurrentInv")).doubleValue()); // Add topping to list toppings.add(topping); } conn.close(); return toppings; } /** * Helper function which retrieves a topping by its ID. * * @param toppings List of Topping objects * @param toppingID ID of topping being searched for * @return Found Topping object or null */ public static Topping getToppingByID(ArrayList toppings, int toppingID) { // Loop through toppings for (Topping topping : toppings) { if (topping.getTopID() == toppingID) { return topping; } } return null; } /** * Function which retrieves a topping by its name. * * @param name Name of the topping being searched for * @return Topping matching the supplied name as a Topping object or null if no match * @throws SQLException * @throws IOException */ public static Topping findToppingByName(String name) throws SQLException, IOException { // Use existing method to get list of toppings ArrayList toppings = getToppingList(); // Loop through toppings for (Topping topping : toppings) { // If topping name matches, return the topping if (topping.getTopName().equals(name)) { return topping; } } // If no match has been found return null; } /** * Function which updates the quantity of a topping in the database by the amount specified. * * @param t Initialized Topping object being updated * @param quantity Quantity to update by * @throws SQLException * @throws IOException */ public static void addToInventory(Topping t, double quantity) throws SQLException, IOException { // SQL query to update topping table String query = "UPDATE topping SET ToppingCurrentInv = ToppingCurrentInv + ? WHERE ToppingID = ?;"; // Assemble query parameters ArrayList params = new ArrayList<>(Arrays.asList(quantity, t.getTopID())); // Execute query and ensure results if (executeSQLUpdate(query, params) != 1) { throw new SQLException(); } } /** * Function which retrieves the base customer price for a pizza with the specified size and crust type. * * @param size Size of the pizza * @param crust Crust type of the pizza * @return Dollar amount * @throws SQLException * @throws IOException */ public static double getBaseCustPrice(String size, String crust) throws SQLException, IOException { // SQL query to retrieve from the base_price table String query = "SELECT * FROM base_price WHERE BasePricePizzaSize = ? AND BasePricePizzaCrustType = ?;"; // Assemble query parameters ArrayList params = new ArrayList<>(Arrays.asList(size, crust)); // Execute query and retrieve results ArrayList> results = executeSQLRetrieve(query, params); conn.close(); return ((BigDecimal) results.get(0).get("BasePricePriceToCustomer")).doubleValue(); } /** * Function which retrieves the base business price for a pizza with the specified size and crust type. * * @param size Size of the pizza * @param crust Crust type of the pizza * @return Dollar amount * @throws SQLException * @throws IOException */ public static double getBaseBusPrice(String size, String crust) throws SQLException, IOException { // SQL query to retrieve from the base_price table String query = "SELECT * FROM base_price WHERE BasePricePizzaSize = ? AND BasePricePizzaCrustType = ?;"; // Assemble query parameters ArrayList params = new ArrayList<>(Arrays.asList(size, crust)); // Execute query and retrieve results ArrayList> results = executeSQLRetrieve(query, params); conn.close(); return ((BigDecimal) results.get(0).get("BasePriceCostToCompany")).doubleValue(); } /** * Function which prints the current inventory levels. * * @throws SQLException * @throws IOException */ public static void printInventory() throws SQLException, IOException { // Get list of toppings as Topping objects ArrayList toppings = getToppingList(); // Call helper function to print printInventoryLevels(toppings); } /** * Helper function which prints a list of toppings. Used to handle printing anonymous to * order of Topping objects. * * @param toppings List of Topping objects */ public static void printInventoryLevels(ArrayList toppings) { // Print heading row System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(2, 17, 7)), new ArrayList<>(Arrays.asList("ID", "Name", "CurINVT")), new ArrayList<>(Arrays.asList(true, true, true)))); // Loop through the toppings for (Topping topping : toppings) { // Print topping ID, name, and current inventory System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(2, 17, 7)), new ArrayList<>(Arrays.asList(topping.getTopID(), topping.getTopName(), topping.getCurINVT())), new ArrayList<>(Arrays.asList(true, true, true)))); } } /** * Function which retrieves and prints the ToppingPopularity view from the database. * * @throws SQLException * @throws IOException */ public static void printToppingPopReport() throws SQLException, IOException { // SQL query to retrieve ToppingPopularity report String query = "SELECT * FROM ToppingPopularity;"; // Print heading row System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(17, 12)), new ArrayList<>(Arrays.asList("Topping", "ToppingCount")), new ArrayList<>(Arrays.asList(true, true)))); // Execute query and retrieve results ArrayList> results = executeSQLRetrieve(query, new ArrayList<>()); for (Map result : results) { // Print row System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(17, 12)), new ArrayList<>(Arrays.asList(result.get("Topping"), result.get("ToppingCount"))), new ArrayList<>(Arrays.asList(true, true)))); } conn.close(); } /** * Function which retrieves and prints the ProfitByPizza view from the database. * * @throws SQLException * @throws IOException */ public static void printProfitByPizzaReport() throws SQLException, IOException { // SQL query to retrieve ProfitByPizza report String query = "SELECT * FROM ProfitByPizza;"; // Print heading row System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(10, 11, 10, 11)), new ArrayList<>(Arrays.asList("Pizza Size", "Pizza Crust", "Profit", "Order Month")), new ArrayList<>(Arrays.asList(true, true, true, true)))); // Execute query and retrieve results ArrayList> results = executeSQLRetrieve(query, new ArrayList<>()); for (Map result : results) { // Print row System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(10, 11, 10, 11)), new ArrayList<>(Arrays.asList(result.get("Size"), result.get("Crust"), ((BigDecimal) result.get("Profit")).doubleValue(), result.get("Order Month"))), new ArrayList<>(Arrays.asList(true, true, true, true)))); } conn.close(); } /** * Function which retrieves and prints the ProfitByOrderType view from the database. * * @throws SQLException * @throws IOException */ public static void printProfitByOrderType() throws SQLException, IOException { // SQL query to retrieve ProfitByOrderType report String query = "SELECT * FROM ProfitByOrderType;"; // Print heading row System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(9, 11, 15, 14, 10)), new ArrayList<>(Arrays.asList("OrderType", "Order Month", "TotalOrderPrice", "TotalOrderCost", "Profit")), new ArrayList<>(Arrays.asList(true, true, true, true, true)))); // Execute query and retrieve results ArrayList> results = executeSQLRetrieve(query, new ArrayList<>()); for (Map result : results) { // Print row System.out.println(getPrintableFormatString(new ArrayList<>(Arrays.asList(9, 11, 15, 14, 10)), new ArrayList<>(Arrays.asList(result.get("customerType"), result.get("Order Month"), ((BigDecimal) result.get("TotalOrderPrice")).doubleValue(), ((BigDecimal) result.get("TotalOrderCost")).doubleValue(), ((BigDecimal) result.get("Profit")).doubleValue())), new ArrayList<>(Arrays.asList(true, true, true, true, true)))); } conn.close(); } /** * Function which retrieves the name of a customer matching the specified CustomerID. * * @param CustID CustomerID of customer * @return Name of customer * @throws SQLException * @throws IOException */ public static String getCustomerName(int CustID) throws SQLException, IOException { connect_to_db(); // If the supplied customerID is 0, which is used for dine-in orders, return "N/A" to indicate // no customer details if (CustID == 0) { return "N/A"; } // Use existing method to get list of customers ArrayList customers = getCustomerList(); // Variable to store customer name String customerName = ""; // Loop through customers for (Customer customer : customers) { // If the current customer's CustomerID matches the specified CustomerID if (customer.getCustID() == CustID) { // Assemble the customer's name customerName = customer.getFName() + " " + customer.getLName(); } } return customerName; } private static int getYear(String date) { // Assumes date format 'YYYY-MM-DD HH:mm:ss' return Integer.parseInt(date.substring(0, 4)); } private static int getMonth(String date) { // Assumes date format 'YYYY-MM-DD HH:mm:ss' return Integer.parseInt(date.substring(5, 7)); } private static int getDay(String date) { // Assumes date format 'YYYY-MM-DD HH:mm:ss' return Integer.parseInt(date.substring(8, 10)); } public static boolean checkDate(int year, int month, int day, String dateOfOrder) { if (getYear(dateOfOrder) > year) { return true; } else if (getYear(dateOfOrder) < year) { return false; } else { if (getMonth(dateOfOrder) > month) { return true; } else if (getMonth(dateOfOrder) < month) { return false; } else { return getDay(dateOfOrder) >= day; } } } }