def add_item_write_to_db(what, val, table, added_item):
connection = database.connect_to_db()
while True:
if what == "Product":
try:
added_val = float(input(f'Please enter the {val} of the {added_item} you wish to add...'))
database.execute_sql_crud(connection, f'INSERT INTO products (product_name, product_price) VALUES ("{added_item}", {added_val})')
except ValueError:
print("You have not entered a price, please try again...")
continue
elif what == "Courier":
added_number = validate_number('courier')
database.execute_sql_crud(connection, f'INSERT INTO couriers (courier_name, courier_number) VALUES ("{added_item}", "{added_number}")')
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 replace_item_write_to_db(connection, what, val, table, item_to_replace):
connection = connect_to_db()
while True:
if what == "Product":
new_product = input("Please enter the product you wish to add to the menu...").title()
new_product_price = float(input(f"Please enter the price for the {new_product}... £"))
execute_sql_crud(connection, (f'UPDATE products SET product_name = "{new_product}" WHERE products_id = {item_to_replace}'))
execute_sql_crud(connection, (f'UPDATE products SET product_price = {new_product_price} WHERE products_id = {item_to_replace}'))
break
elif what == "Courier":
new_courier = input("Please enter the name of the courier you wish to add to the roster...")
new_courier_number = validate_number('courier')
execute_sql_crud(connection, (f'UPDATE {table} SET courier_name = "{new_courier}" WHERE couriers_id = {item_to_replace}'))
execute_sql_crud(connection, (f'UPDATE {table} SET courier_number = "{new_courier_number}" WHERE couriers_id = {item_to_replace}'))
connection.close()
break
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_write_to_db(what, val, table, update_item):
connection = connect_to_db()
while True:
if what == "Product":
try:
new_price = float(input(f'Please enter the new price for the product {update_item}... £'))
except ValueError:
print("You have not entered a price, please try again...")
continue
else:
execute_sql_crud(connection, (f'UPDATE products SET product_price = {new_price} WHERE products_id = {update_item}'))
elif what == "Courier":
added_number = validate_number('courier')
execute_sql_crud(connection, (f'UPDATE {table} SET courier_number = "{added_number}" WHERE couriers_id = {update_item}'))
elif what == "Order":
updated_status = choose_order_status()
execute_sql_crud(connection, (f'UPDATE {table} SET order_status = "{updated_status}" WHERE orders_id ={update_item}'))
connection.close()
break
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