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 report_6():
connection = connect_to_db()
o_courier_assigned = execute_sql_select(connection, ('SELECT courier_assigned FROM orders'))
o_courier_names = execute_sql_select(connection, ('SELECT courier_name FROM couriers'))
counts = Counter(x[0] for x in o_courier_assigned)
no_of_deliveries = list(counts.values())
name_list = []
for name in o_courier_names:
name_list.append(name[0])
plt.bar(name_list, no_of_deliveries, color ='maroon',
width = 0.4)
yint = range(min(no_of_deliveries), math.ceil(max(no_of_deliveries))+1)
plt.yticks(yint)
plt.xlabel("COURIER NAMES")
plt.ylabel("NO OF DELIVERIES ASSIGNED")
plt.title("Number of Deliveries Assigned per Courier")
plt.show()
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 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 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 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 report_5():
connection = connect_to_db()
o_status = execute_sql_select(connection, ('SELECT order_status FROM orders'))
counts = Counter(x[0] for x in o_status)
d = (counts['Order Delivered'])
r = (counts['Order R
c = (counts['Order Canceleceived'])led'])
p = (counts['Order Preparing'])
labels = 'Orders Received', 'Orders Preparing', 'Orders Delivered', 'Orders Cancelled'
sizes = [r, p, d, c]
fig1, ax1 = plt.subplots()
ax1.pie(sizes, labels=labels, autopct='%1.0f%%',
shadow=False, startangle=90)
ax1.axis('equal')
plt.title("Pie Chart showing status of all orders (%)")
plt.show()
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