def add_item_to_db(connection, what, val, table): if table == 'products' or table == 'couriers': add_item_sub_menu(what, val, table) elif table == 'orders': add_order(connection, what, val, table) app_header(f"{what} Screen") menu(what, val, table)
def add_item_sub_menu(what, val, table): added_item = input( f'Please enter the name of the {what} you wish to add...\n \nPlease press 0 if you wish to return to the previous menu...' ) if added_item == "0": app_header(f"{what} Screen") menu(what, val, table) else: add_item_write_to_db(what, val, table, added_item) app_header(f"{what} Screen") menu(what, val, table)
def replace_item_menu(connection, what, val, table): print_from_db_menu(table) item_to_replace = input( f'Please enter the ID for the {what} you want to replace...\n \nPlease press 0 if you wish to return to the previous menu...' ) if item_to_replace == "0": app_header(f"{what} Screen") menu(what, val, table) else: replace_item_write_to_db(connection, what, val, table, item_to_replace) app_header(f"{what} Screen") menu(what, val, table)
def assign_courier_to_order(connection): existing_courier_ids = [courier_id[0] for courier_id in execute_sql_select(connection, ('SELECT couriers_id from couriers'))] chosen_courier_id = [] app_header("Courier List") print_from_db_menu('couriers') while True: assigned_courier = input("Please enter a courier you want to assign to the order...") if int(assigned_courier) not in existing_courier_ids: print("Invalid Courier ID, please try again...") continue elif assigned_courier in existing_courier_ids: break chosen_courier_id.append(assigned_courier) return chosen_courier_id
def start_app(): while True: contents = input(""" 1: Product Menu... 2: Courier Screen... 3: Order Menu.... 4: Reporting Screen... 0: Exit App... Please enter your choice: """) if contents == "1": app_header("Product Main Menu") menu("Product", "price", "products") elif contents == "2": app_header("Courier Main Menu") menu("Courier", "number", "couriers") elif contents == "3": app_header("Order Main Menu") menu("Order", "Status", "orders") elif contents == "4": reporting_menu() elif contents == "0": exit_app() else: app_header("Main Screen") print("You made an incorrect selection, please try again... \n")
def report_2(): # Report to show the customer name, address, for orders not yet delivered connection = connect_to_db() app_header("Orders yet to be delivered...") results = execute_sql_select(connection, (f'SELECT customer_name, customer_address, order_status from orders where order_status = "Order Received" or order_status = "Order Preparing"')) name = [] address = [] order_status = [] for row in results: name.append(row[0]) for row in results: address.append(row[1]) for row in results: order_status.append(row[2]) report_table(name, address, order_status)
def report_4(): # Report to show the customer name, address, for orders cancelled connection = connect_to_db() app_header("Cancelled Orders") results = execute_sql_select(connection, (f'SELECT customer_name, customer_address, order_status from orders where order_status = "Order Cancelled"')) name = [] address = [] order_status = [] for row in results: name.append(row[0]) for row in results: address.append(row[1]) for row in results: order_status.append(row[2]) report_table(name, address, order_status)
def assign_products_to_order(connection): existing_product_ids = [products_id[0] for products_id in execute_sql_select(connection, ('SELECT products_id from products'))] chosen_product_id = [] app_header("Product List") print_from_db_menu('products') print() print("Press 0 when you have assigned all products to this order.") print() while True: assigned_product = input("Please enter a product you want to assign to the order...") if int(assigned_product) == 0: break elif int(assigned_product) not in existing_product_ids: print("Invalid product ID, please try again...") continue chosen_product_id.append(assigned_product) return chosen_product_id
def reports_return_option(): print() while True: rtn_input = input( "Would you like to return to the reports screen? Y/N") if rtn_input.upper() == "Y" or rtn_input.upper() == "YES": app_header("Reporting Screen") reporting_menu() elif rtn_input.upper() == "N" or rtn_input.upper() == "NO": exit_app() else: print( "You have not choosen a suitable option, please try again...") continue
def return_option(connection, what, val, table): print() while True: rtn_input = input("Would you like to return to the " + what + " screen? Y/N") if rtn_input.upper() == "Y" or rtn_input.upper() == "YES": app_header(what + " Screen") menu(what, val, table) elif rtn_input.upper() == "N" or rtn_input.upper() == "NO": exit_app() else: print( "You have not choosen a suitable option, please try again...") continue
def replace_order_options_in_db(connection, what, val, table): while True: app_header("Order List") print_from_db_menu('orders') id_of_order_to_change = input('Please enter the order ID for the order that you want to update...\n \nPlease press 0 if you wish to return to the previous menu...') if id_of_order_to_change == "0": break else: updated_name = input("Enter updated name...").title() updated_address = input("Enter updated address...").title() updated_number = input("Enter updated number...") app_header("Courier Table") print_from_db_menu('couriers') updated_courier = input("Enter updated courier...") updated_status = choose_order_status() products_to_add = assign_products_to_order(connection) if updated_name == "": pass else: execute_sql_crud(connection, (f'UPDATE orders SET customer_name = "{updated_name}" WHERE orders_id = {id_of_order_to_change}')) if updated_address == "": pass else: execute_sql_crud(connection, (f'UPDATE orders SET customer_address = "{updated_address}" WHERE orders_id = {id_of_order_to_change}')) if updated_number == "": pass else: execute_sql_crud(connection, (f'UPDATE orders SET phone_number = "{updated_number}" WHERE orders_id = {id_of_order_to_change}')) if updated_courier == "": pass else: execute_sql_crud(connection, (f'UPDATE orders SET courier_assigned = "{updated_courier}" WHERE orders_id = {id_of_order_to_change}')) if updated_status == "": pass else: execute_sql_crud(connection, (f'UPDATE orders SET order_status = "{updated_status}" WHERE orders_id = {id_of_order_to_change}')) if products_to_add == "": pass else: execute_sql_crud(connection, (f'DELETE FROM order_product WHERE order_id = {id_of_order_to_change}')) for product in products_to_add: execute_sql_crud(connection,(f" INSERT INTO order_product (order_id, product_id) VALUES ('{id_of_order_to_change}', '{product}')")) break
def add_order(connection, what, val, table): while True: connection = connect_to_db() app_header("Create New Order") cust_name = input(f"Please enter the customer's name...\n \nPlease press 0 if you wish to return to the previous menu...") if cust_name == "0": break else: cust_address = input("Please enter the customer's address...").title() cust_num = validate_number('customer') courier = assign_courier_to_order(connection) cour=courier[0] status = 'Order Received' products_to_add = assign_products_to_order(connection) execute_sql_crud(connection, (f' INSERT INTO orders (customer_name, customer_address, phone_number, order_status, courier_assigned) VALUES ("{cust_name}", "{cust_address}", "{cust_num}", "{status}", "{cour}")')) order_id = execute_sql_select_('SELECT MAX(orders_id) from orders')[0][0] for product in products_to_add: execute_sql_crud(connection,(f" INSERT INTO order_product (order_id, product_id) VALUES ('{order_id}', '{product}')")) break
def report_1(table): # Report to show the customer name, address and list of products in an order. connection = connect_to_db() existing_order_ids = [orders_id[0] for orders_id in execute_sql_select(connection, ('SELECT orders_id from orders'))] app_header("Reporting Screen") print_from_db_menu(table) while True: id = input("Please choose an order from the list above...") if int(id) in existing_order_ids: results = execute_sql_select(connection, (f'SELECT o.customer_name, o.customer_address, p.product_name from order_product op join orders o on op.order_id = o.orders_id join products p on op.product_id = p.products_id where o.orders_id = {id}')) name = results[0][0] address = results[0][1] products = [] for row in results: products.append(row[2]) app_header(f"{name}'s Order") print(f''' Customer Name *|---------------------------|* {name} *|---------------------------|* Customer Address *|---------------------------|* {address} *|---------------------------|* Ordered Items *|---------------------------|*''') for x in products: print(" ",x) print(" *|---------------------------|*") print() print() break else: print("Please enter a valid ID from the above list...")
def choose_order_status(): app_header("Choose Order Status") status_choice = input( ''' Please update the status of the order... 1. Order Received 2. Order Preparing 3. Order Delivered 4. Order Cancelled Please choose an option from 1 - 4.... ''' ) if status_choice == "1": return("Order Received") elif status_choice == "2": return ("Order Preparing") elif status_choice == "3": return ("Order Delivered") elif status_choice == "4": return ("Order Cancelled")
def delete_row(connection, what, val, table): existing_ids = [id[0] for id in execute_sql_select(connection, (f'SELECT {table}_id from {table}'))] app_header(f"{what} Screen") print_from_db_menu(table) while True: try: item_to_delete = input(f"Please choose which {what} you wish to delete...") if int(item_to_delete) not in existing_ids: print(f"You did not choose a valid {what} ID, please try again...") continue else: if what == "Product": execute_sql_crud(connection, (f'DELETE FROM products where products_id = {item_to_delete}')) elif what == "Courier": execute_sql_crud(connection, (f'DELETE FROM couriers where couriers_id = {item_to_delete}')) elif what == "Order": execute_sql_crud(connection, (f'DELETE FROM order_product WHERE order_id = {item_to_delete}')) execute_sql_crud(connection, (f'DELETE FROM orders WHERE orders_id = {item_to_delete}')) connection.close() break except ValueError: print("There has been an error, please try again...") break
def update_item_sub_menu(what, val, table): app_header(f'Update {what} {val}') print_from_db_menu(table) update_item = input( f'Please enter the ID for the {what} you want to update the {val} for...\n \nPlease press 0 if you wish to return to the previous menu...' ) if update_item == "0": app_header(f"{what} Screen") menu(what, val, table) else: update_item_write_to_db(what, val, table, update_item) app_header(f"{what} Screen") menu(what, val, table)
def print_from_db_menu(table): list = read_from_database(table) if table == 'products': app_header("Product Menu") header = ["Product ID", "Product Name", "Price"] elif table == 'couriers': app_header("Courier List") header = ["Courier ID", "Courier Name", "Phone Number"] elif table == 'orders': app_header("Order List") header = [ "Order ID", "Customer Name", "Customer Address", "Customer Number", "Order Status", "Assigned Courier", "Item's Ordered" ] rows = [x.values() for x in list] print(tabulate.tabulate(rows, header, tablefmt='fancy_grid'))
def menu(what, val, table): while True: connection = connect_to_db() start_option = input(f"""Please select from the following options: 0) Return to Main Menu 1) Print Out {what} List 2) Create {what} 3) Update {what} {val} 4) Replace {what} 5) Delete {what} 6) Exit App """) if start_option == "0": app_header("Main Screen") start_app() elif start_option == "1": print_from_db_menu(table) return_option(connection, what, val, table) elif start_option == "2": add_item_to_db(connection, what, val, table) return_option(connection, what, val, table) elif start_option == "3": update_item_sub_menu(what, val, table) elif start_option == "4": replace_item_in_db(connection, what, val, table) app_header(what) menu(what, val, table) elif start_option == "5": delete_row(connection, what, val, table) return_option(connection, what, val, table) elif start_option == "6": exit_app() else: app_header(f"{what} Screen") print("You made an incorrect selection, please try again... \n") connection.close
def reporting_menu(): while True: app_header("Reports Screen") reporting_option = input(""" Please select a report: 0) Return to the main menu... 1) Details for a specific order... 2) Orders not yet delivered... 3) Delivered orders... 4) Cancelled orders... 5) Graphical representation of Order Status... 6) Deliveries per courier... """) if reporting_option == "0": app_header("Main Screen") start_app() elif reporting_option == "1": report_1('orders') reports_return_option() elif reporting_option == "2": report_2() reports_return_option() elif reporting_option == "3": report_3() reports_return_option() elif reporting_option == "4": report_4() reports_return_option() elif reporting_option == "5": report_5() reports_return_option() elif reporting_option == "6": report_6() reports_return_option() else: app_header("Reports Screen") print( "You made an incorrect selection, please try again... \n \n Please press 0 to return to the main menu...\n" )
else: print( "You have not choosen a suitable option, please try again...") continue # Reports Return Option def reports_return_option(): print() while True: rtn_input = input( "Would you like to return to the reports screen? Y/N") if rtn_input.upper() == "Y" or rtn_input.upper() == "YES": app_header("Reporting Screen") reporting_menu() elif rtn_input.upper() == "N" or rtn_input.upper() == "NO": exit_app() else: print( "You have not choosen a suitable option, please try again...") continue app_header("Main Screen") start_app()