def remove_courier(couriers):
conn = connection()
del_id = int(
input('Enter the id of the courier you would like to delete: '))
delete_course = "DELETE FROM courier WHERE id=%s;"
a = (del_id)
execute_query(conn, delete_course, a)
return couriers
def create_new(couriers):
conn = connection()
new_courier = input("What is the name of the new courier?: ")
new_courier_phone = input("Enter the phone for this courier: ")
update = "INSERT INTO courier (name, phone) VALUES (%s,%s)"
a = (new_courier, new_courier_phone)
execute_query(conn, update, a)
return couriers
def del_product(products):
conn = connection()
del_id = int(
input('Enter the id of the product you would like to delete: '))
delete_course = "DELETE FROM product WHERE id=%s;"
a = (del_id)
execute_query(conn, delete_course, a)
return products
def add_product(products):
conn = connection()
new_product = input("What is the name of the new product?: ")
new_product_price = input("Enter the price of this product: ")
update = "INSERT INTO product (name, price) VALUES (%s,%s)"
a = (new_product, new_product_price)
execute_query(conn, update, a)
return products
def load_franchise_table(
data
): # see transform.convert_locations_to_keys for the locations_dict
conn = connection()
for i in (data):
SQL = ("INSERT INTO franchise (id, cafe_location)" "VALUES (%s,%s)")
val = (data[i], i)
update(conn, SQL, val, False)
conn.commit()
conn.close()
def update_courier(couriers):
update_index = int(
input('Enter the index of the couriers you want to change: '))
update_phone = float(input("enter phone: "))
new_courier_name = input('Enter a new name for this courier: ')
selected_couriers = couriers[update_index]
conn = connection()
sql = "UPDATE courier SET name = %s, phone = %s WHERE id=%s"
values = (new_courier_name, update_phone, selected_couriers["id"])
push_to_db(conn, sql, values)
return couriers
def load_payment_table():
#this is a very simple reference table which will contain CASH, CARD or OTHER
#This should only ever happen ONCE
conn = connection()
options = ["CASH", "CARD", "OTHER"]
for num, i in enumerate(options):
SQL = ("INSERT INTO payment (id, type)" "VALUES (%s,%s)")
val = (num + 1, i)
update(conn, SQL, val, False)
conn.commit()
conn.close()
def update_product(products):
update_index = int(
input('Enter the index of the product you want to change: '))
update_price = input('Enter price: ')
new_product_name = input('Enter a new name for this product: ')
selected_products = products[update_index]
conn = connection()
sql = "UPDATE product SET name = %s, price = %s WHERE id=%s"
values = (new_product_name, update_price, selected_products["id"])
push_to_db(conn, sql, values)
return products
def Load_Transaction_Data(data):
conn = connection()
for transaction in data:
try:
update(
conn,
"INSERT INTO transaction(date_time, franchise, payment, id) VALUES (%s, %s, %s, %s)",
transaction.values(), False)
except:
print("Invalid input, skipping insertion")
conn.commit()
conn.close()
def Load_Basket_Data(data):
conn = connection()
for basket in data:
try:
update(
conn,
"INSERT INTO basket(transaction, product, id) VALUES (%s, %s, %s)",
basket.values(), False)
except:
print("Invalid input, skipping insertion")
conn.commit()
conn.close()
def Load_Product_Data(data):
conn = connection()
for product in data:
try:
update(
conn,
"INSERT INTO product(size, name, price, id) VALUES (%s, %s, %s, %s)",
product.values(), False)
except:
print("Invalid input, skipping insertion")
conn.commit()
conn.close()
def execute(event, context):
bucket = event["Records"][0]["s3"]["bucket"]["name"]
key = event["Records"][0]["s3"]["object"]["key"]
s3_resource = boto3.resource("s3")
s3_object = s3_resource.Object(bucket, key)
raw = s3_object.get()["Body"].read().decode("utf-8").splitlines()
df = extract(raw)
data = transform(df)
conn = connection()
load(data, conn)
conn.close()
def load(data):
conn = connection()
for index, row in enumerate(data): # enumerate is an option
copy = dict(row)
copy = load_franchise_data(
conn, copy) # load franchise data (replace location with ID)
copy = load_payment_data(
conn, copy) # load payment data ( replace payment_method with ID)
copy = load_transaction_data(
conn, copy
) # load transaction data (replace location with ID? ask for suggestions)
copy = load_product_data(
conn,
copy) # load products data ( replace basket with list of IDs)
copy = load_basket_data(conn, copy) #load basket IDs
conn.commit()
conn.close()
def change_list_of_products_to_ids(transform_object):
#transform object must be the Transform class
#pulls the entire product database, and replaces any products with their ids only
#the input will be an object of the Transform
# [[{"size" : "large", "price" : 1.9, "name": "coffee"}], [,,,]]
#the output will look like [["uuid123"],["uuid124","uuid125",uuid126]]
conn = connection()
orders = format_orders(transform_object.dataframe)
#my_transaction_and_whole_basket(orders, products):
sql = ("SELECT * FROM product")# and price = %s")
unique_products = query(conn, sql)
#print(unique_products)
#3 nested for loops is very very bad - look at reducing this
for index,i in enumerate(orders):
for index1,j in enumerate(i):
for k in unique_products:
if (k[1] == j["product"] and k[3] == float(j["price"])):
orders[index][index1] = k[0]
break
return orders
from src.db.core import connection, query, push_to_db, execute_query, pull_table
import pymysql.cursors
import itertools
sub_menu = """
Please Choose an option:
[0] : Exit
[1] : View All
[2] : Create New Order
[3] : Update Order Status
[4] : Remove Order
"""
status = ["PREPARING", "READY", "OUT-FOR-DELIVERY", "DELIVERED"]
conn = connection()
select_all_orders = "SELECT * FROM transaction"
def view_all(state):
os.system("clear")
idx = 0
for order in state:
print(
f"[{idx}] - {order['id']} {order['courier_id']} {order['customer_name']} {order['customer_address']} {order['customer_phone']} {order['order_status']}"
)
idx += 1
def create_order(state):
name = input("What is the name of the customer: ")