class UserQueries:
def __init__(self):
self.conn = DbConn().create_connection()
self.cur = self.conn.cursor()
def insert_user(self, first_name, last_name, user_name, email, password,
created_at, admin):
"a method to insert user into users table"
sql = """INSERT INTO users ( first_name, last_name, user_name, email, password, created_at, admin )
VALUES ('{f_name}', '{l_name}', '{u_name}', '{email}', '{password}',
'{created_at}', '{admin}' )"""
sql_command = sql.format(f_name=first_name,
l_name=last_name,
u_name=user_name,
email=email,
password=password,
created_at=created_at,
admin=admin)
self.cur.execute(sql_command)
self.conn.commit()
# self.conn.close()
def get_all_users(self, users_list=[]):
"a method to get al users"
users_list.clear()
sql = """SELECT * FROM users ;"""
self.cur.execute(sql)
orders = self.cur.fetchall()
user = {}
for row in orders:
user = {
"user_id": row[0],
"first_name": row[1],
"last_name": row[2],
"user_name": row[3],
"email": row[4],
"password": row[5],
"created_a": row[6],
"admin": row[7]
}
users_list.append(user)
self.conn.commit()
# self.conn.close()
return users_list
def get_user(self, user_name):
" a methos to get user"
sql = """SELECT * FROM users WHERE user_name ='{u_name}' ;"""
sql_command = sql.format(u_name=user_name)
self.cur.execute(sql_command)
orders = self.cur.fetchall()
user = {}
for row in orders:
user = {
"user_id": row[0],
"first_name": row[1],
"last_name": row[2],
"user_name": row[3],
"email": row[4],
"password": row[5],
"created_a": row[6],
"admin": row[7]
}
self.conn.commit()
# self.conn.close()
return user
def authorise_user(self, u_name, admin):
" a method to promote user"
sql = """UPDATE users SET admin = '{admin}' WHERE user_name = '{u_name}';"""
sql_command = sql.format(admin=admin, u_name=u_name)
self.cur.execute(sql_command)
self.conn.commit()
# self.conn.close()
def get_user_Order_history(self, user_id):
"a metod to get user order history"
order_list = []
sql = """SELECT * FROM orders WHERE user_id = '{user_id}' ;"""
sql_command = sql.format(user_id=user_id)
self.cur.execute(sql_command)
orders = self.cur.fetchall()
order = {}
for row in orders:
order = {
"order_id": row[0],
"order_client": row[1],
"order_food_id": row[2],
"order_uuid": row[3],
"order_created_at": row[4],
"order_status": row[5],
"order_quantity": row[6]
}
order_list.append(order)
self.conn.commit()
# self.conn.close()
return order_list
def check_admin(self, user_id):
" a method to chek whether user is admin"
sql = """SELECT admin FROM users WHERE user_id = '{user_id}' ;"""
sql_command = sql.format(user_id=user_id)
self.cur.execute(sql_command)
values = self.cur.fetchall()
admin = None
for row in values:
admin = row[0]
self.conn.commit()
# self.conn.close()
return admin
def close_conn(self):
" a method to close the datase connection"
DbConn().close_DB()
class DbQueries():
def __init__(self):
self.conn = DbConn().create_connection()
self.cur = self.conn.cursor()
def insert_orders(self, user_id, food_id, order_uuid, created_at, status,
quantity):
"a method to insert orders into the orders table"
sql = """INSERT INTO orders ( user_id ,food_id, order_uuid,created_at, status, quantity )
VALUES ('{user_id}', '{food_id}', '{order_uuid}', '{created_at}',
'{status}', '{quantity}' )"""
sql_command = sql.format(user_id=user_id,
food_id=food_id,
order_uuid=order_uuid,
created_at=created_at,
status=status,
quantity=quantity)
self.cur.execute(sql_command)
self.conn.commit()
# self.conn.close()
def get_orders(self, order_list=[]):
"a methos to fetch orders froorders table"
order_list.clear()
sql = """SELECT * FROM orders ;"""
self.cur.execute(sql)
orders = self.cur.fetchall()
order = {}
for row in orders:
order = {
"order_id": row[0],
"order_client": row[1],
"order_food_id": row[2],
"order_uuid": row[3],
"order_created_at": row[4],
"order_status": row[5],
"order_quantity": row[6]
}
order_list.append(order)
self.conn.commit()
# self.conn.close()
return order_list
def fetch_order(self, uuid):
"a methos to fetch specific order"
sql = """SELECT * FROM orders WHERE order_uuid = '{uuid}' ;"""
sql_command = sql.format(uuid=uuid)
self.cur.execute(sql_command)
orders = self.cur.fetchall()
order = {}
for row in orders:
order = {
"order_id": row[0],
"order_client": row[1],
"order_food_id": row[2],
"order_uuid": row[3],
"order_created_at": row[4],
"order_status": row[5],
"order_quantity": row[6]
}
self.conn.commit()
# self.conn.close()
return order
def update_order_status(self, status, uuid):
"a methos to update order status"
sql = """UPDATE orders SET status = '{status}' WHERE order_uuid = '{uuid}';"""
sql_command = sql.format(status=status, uuid=uuid)
self.cur.execute(sql_command)
self.conn.commit()
# self.conn.close()
def insert_food(self, food_name, food_price):
"a methos to insert food object into menu tale"
sql = """INSERT INTO menu ( food_name, food_price ) VALUES ('{food}', '{price}');"""
sql_command = sql.format(food=food_name, price=food_price)
self.cur.execute(sql_command)
self.conn.commit()
# self.conn.close()
def get_food(self, food_name):
"a methos to get food "
sql = """SELECT * FROM menu WHERE food_name = '{name}' ;"""
sql_command = sql.format(name=food_name)
self.cur.execute(sql_command)
foods = self.cur.fetchall()
food = {}
for row in foods:
food = {
"food_id": row[0],
"food_name": row[1],
"food_price": row[2]
}
self.conn.commit()
# self.conn.close()
return food
def get_all_foods(self, food_list=[]):
"a method to get all foods"
food_list.clear()
sql = """SELECT * FROM menu ;"""
self.cur.execute(sql)
foods = self.cur.fetchall()
food = {}
for row in foods:
food = {
"food_id": row[0],
"food_name": row[1],
"food_price": row[2]
}
food_list.append(food)
self.conn.commit()
# self.conn.close()
return food_list
def close_conn(self):
DbConn().close_DB()