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()