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