def get_items_by_category(category, page):
cursor, cnx = cursor_connect()
insert_stmt = (
"SELECT inventory_id, name, price, weight, description, stock, category FROM inventory WHERE category LIKE %s LIMIT 20 OFFSET %s"
)
if page:
numPage = int(page)
else:
numPage = 1
if(numPage == 1):
offset = 0
if(numPage > 1 ):
offset = (numPage-1)*20+1
data = (category, offset)
cursor.execute(insert_stmt, data)
items = cursor.fetchall()
json_items = []
for item in items:
it = {"inventory_id":item[0],
"name": item[1],
"price": item[2],
"weight": item[3],
"description": item[4],
"stock": item[5],
"category":item[6],
}
json_items.append(it)
cursor.close()
cnx.close()
return json_items
def get_total_pages(category):
cursor, cnx = cursor_connect()
cursor.execute("""SELECT COUNT(*) FROM inventory WHERE category LIKE %s GROUP BY category""", (category,))
count = cursor.fetchall()[0][0]
cursor.close()
cnx.close()
totalPages = math.ceil(count/20);
return totalPages
def existing_order(order_number):
cursor, cnx = cursor_connect()
cursor.execute("""SELECT * FROM orders WHERE id = %s""", (order_number))
if cursor.fetchall():
cursor.close()
cnx.close()
return True
else:
cursor.close()
cnx.close()
return False
def admin_update_quantity(item_id, quantity):
cursor, cnx = cursor_connect()
try:
cursor.execute("""UPDATE inventory SET stock = %s WHERE inventory_id = %s""", (quantity, item_id))
cnx.commit()
except mysql.connector.DataError as err:
cursor.close()
cnx.close()
return False
cursor.close()
cnx.close()
return True
def forgot_password(email):
cursor, cnx = cursor_connect()
cursor.execute("""SELECT password FROM accounts WHERE email like %s""",
(email, ))
pword = cursor.fetchone()
if not pword:
cursor.close()
cnx.close()
print("no password returned, account does not exist\n")
else:
cursor.close()
cnx.close()
return str(pword[0])
def get_id_by_email(email):
cursor, cnx = cursor_connect()
cursor.execute("SELECT account_id FROM accounts WHERE email=%s", (email, ))
id = cursor.fetchone()
if not id:
cursor.close()
cnx.close()
return False
else:
cursor.close()
cnx.close()
return id[0]
def get_item_by_id(inv_id):
cursor, cnx = cursor_connect()
cursor.execute("""SELECT inventory_id, name, price, weight, description, stock FROM inventory WHERE inventory_id=%s""", (inv_id,))
item = cursor.fetchall()
if item:
it = {"id": item[0][0], "name": item[0][1], "price": item[0][2], "weight": item[0][3], "description": item[0][4], "stock": item[0][5]}
else:
cursor.close()
cnx.close()
return None
cursor.close()
cnx.close()
return it
def delete_item(name):
cursor, cnx = cursor_connect()
cursor.execute("""SELECT inventory_id FROM inventory WHERE item_name LIKE %s""", (name,))
item_id = cursor.fetchone()[0]
if item_id:
cursor.execute("""DELETE FROM inventory WHERE inventory_id = %s""", (item_id,))
cnx.commit()
return True
else:
print("failed to remove")
return False
cur.close()
cnx.close()
def authenticate_user(email, password):
cursor, cnx = cursor_connect()
cursor.execute("SELECT * FROM accounts WHERE email = %s AND password = %s",
(email, password))
user_info = cursor.fetchall()[0]
if not user_info:
cursor.close()
cnx.close()
return None
else:
info = {"id": user_info[0], "type": user_info[4]}
cursor.close()
cnx.close()
return info
def get_all_items():
cursor, cnx = cursor_connect()
cursor.execute("""SELECT inventory_id, name, price, stock, weight FROM inventory""")
inventory = cursor.fetchall()
inv = []
for item in inventory:
i = {"inventory_id" : item[0],
"name" : item[1],
"stock" : item[2]
}
inv.append(i)
cursor.close()
cnx.close()
return inv
def add_item(name, category, description, price, stock, weight, warehouse_id):
cursor, cnx = cursor_connect()
cursor.execute("""SELECT * FROM inventory WHERE (name LIKE %s)
AND (category LIKE %s) AND (price = %s)""", (name, category, price))
if not cursor.fetchall():
cursor.execute("""INSERT INTO inventory (name, price, weight, description, category, stock, warehouse_id)
VALUES (%s,%s,%s,%s,%s,%s,%s)""", (name, price, weight, description, category, stock, warehouse_id))
cnx.commit()
# print("item added\n")
cursor.close()
cnx.close()
return True
else:
# print("item already exists in inventory\n")
cursor.close()
cnx.close()
return False
def add_warehouse(name, latitude, longitude):
cursor, cnx = cursor_connect()
cursor.execute("""SELECT * FROM warehouses WHERE (name LIKE %s)
AND (latitude LIKE %s) AND (longitude = %s)""", (name, latitude, longitude))
if not cursor.fetchall():
cursor.execute("""INSERT INTO warehouses (name, latitude, longitude)
VALUES (%s,%s,%s)""", (name, latitude, longitude))
cnx.commit()
# print("warehouse added\n")
cursor.close()
cnx.close()
return True
else:
# print("warehouse already exists in warehouses\n")
cursor.close()
cnx.close()
return False
def get_orders_by_user(id):
cursor, cnx = cursor_connect()
cursor.execute("SELECT order_id, account_id, purchase_time, items, total_price, total_weight FROM orders WHERE account_id=%s",
(id,))
orders = cursor.fetchall()
json_orders = []
for order in orders:
dict = {"order_id": order[0],
"account_id": order[1],
"purchase_time": order[2],
"items": json.loads(order[3]),
"total_price": order[4],
"total_weight": order[5]
}
json_orders.append(dict)
cursor.close()
cnx.close()
return json_orders
def get_tracking_by_order(id):
cursor, cnx = cursor_connect()
cursor.execute(
"""SELECT lat, lng, delivery_method, delivery_status, purchase_time FROM orders WHERE order_id=%s""",
(id,))
tracking = cursor.fetchall()
print(tracking[0])
if tracking:
info = {
"lat":str(tracking[0][0]),
"lng":str(tracking[0][1]),
"delivery_status": tracking[0][2],
"delivery_method": tracking[0][3],
"purchase_time": tracking[0][4]
}
cursor.close()
cnx.close()
return info
def add_account(name, email, password):
cursor, cnx = cursor_connect()
cursor.execute("""SELECT email FROM accounts WHERE email LIKE %s""",
(email, ))
# if email not found in database
if not cursor.fetchall():
cursor.execute(
"""INSERT INTO accounts (name, email, accounts.password, type) VALUES (%s,%s,%s,%s);""",
(name, email, password, "user"))
cnx.commit()
# print("executed")
cursor.close()
cnx.close()
return True
else:
# email account already exists in database
# print("account with that email already exists")
cursor.close()
cnx.close()
return False
def new_order(a_id, items, tot_price, tot_weight, address, delivery_method, delivery_status):
cursor, cnx = cursor_connect()
gmaps_key = googlemaps.Client(key = "AIzaSyALPpI2grWR5poDZf4JMpHDLMcAHAwZ6R0")
geocode = gmaps_key.geocode(address)
try:
lng = geocode[0]["geometry"]["location"]["lng"]
lat = geocode[0]["geometry"]["location"]["lat"]
cursor.execute("""INSERT INTO orders (account_id, items, total_price, total_weight, lat, lng, delivery_method, delivery_status) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)""",
(a_id, items, tot_price, tot_weight, lat, lng, delivery_method, delivery_status))
cnx.commit()
# print("new order added")
except Exception as e:
print("order unable to be added:")
print(e)
cursor.close()
cnx.close()
return False
cursor.close()
cnx.close()
return True
def update_qty(cart):
cursor, cnx = cursor_connect()
for i in cart:
item_id = i['id']
item_qty = i['qty']
item = get_item_by_id(item_id)
current_stock = item['stock']
updated_stock = int(current_stock) - int(item_qty)
insert_stmt = (
"UPDATE inventory SET stock = %s WHERE inventory_id = %s"
)
data = (updated_stock, item_id)
try:
cursor.execute(insert_stmt, data)
cnx.commit()
except mysql.connector.DataError as err:
cursor.close()
cnx.close()
return False
cursor.close()
cnx.close()
return True
