def close_round_in_database(open_round: Round):
db = Database(Config)
db.run_query(f"""
UPDATE BrewRound SET is_open=FALSE WHERE round_id={open_round.round_id}
""")
open_round.is_open = False
return open_round
def create_new_open_round_in_database(new_round: Round):
db = Database(Config)
new_round.round_id = db.run_query(f"""
INSERT INTO BrewRound(is_open,brewer, open_date)
VALUES ({new_round.is_open},'{new_round.brewer.person_id}',
'{new_round.open_date.strftime('%Y-%m-%d %H:%M:%S')}')
""")
return new_round
def get_rounds_from_database(is_open_filter=None):
rounds = []
db = Database(Config)
query = """
SELECT r.is_open, r.brewer as brewer_id, r.open_date, r.round_id,
p.name as brewer_name, p2.name as person_name, d.name as drink_name,
d.drink_id, p2.person_id, o.order_id
FROM BrewOrder as o
RIGHT JOIN BrewRound as r
ON o.round_id = r.round_id
INNER JOIN Person as p
ON p.person_id = r.brewer
LEFT JOIN Drink as d
ON d.drink_id = o.drink_id
LEFT JOIN Person as p2
ON p2.person_id = o.person_id
"""
if is_open_filter != None and isinstance(is_open_filter, bool):
query += f" WHERE r.is_open={int(is_open_filter)}"
query += " ORDER BY r.round_id"
db_rounds = db.run_query(query)
rounds = []
if len(db_rounds) != 0:
curret_round = db_rounds[0]
round_orders = []
for idx, order in enumerate(db_rounds):
if curret_round['round_id'] != order['round_id'] or idx == len(
db_rounds):
rounds.append(
Round(
round_orders, curret_round['open_date'],
Person(curret_round['brewer_name'], None,
curret_round['brewer_id']),
curret_round['is_open'], curret_round['round_id']))
curret_round = order
round_orders = []
if order['person_name']:
round_orders.append(
Order(
Person(order['person_name'],
person_id=order['person_id']),
Drink(order['drink_name'], order['drink_id']),
order['order_id']))
rounds.append(
Round(
round_orders, curret_round['open_date'],
Person(curret_round['brewer_name'], None,
curret_round['brewer_id']), curret_round['is_open'],
curret_round['round_id']))
return rounds
def add_order_to_round_in_database(open_round: Round, new_order: Order):
db = Database(Config)
new_order.order_id = db.run_query(f"""
INSERT INTO BrewOrder(drink_id, person_id, round_id)
VALUES ({new_order.drink.drink_id},{
new_order.person.person_id},{open_round.round_id})
""")
return new_order
def save_new_user_in_database(new_user: Person):
db = Database(Config)
query = f"""INSERT INTO Person(name) VALUES ('{new_user.name}')"""
if new_user.favourite_drink != None:
query = f"""INSERT INTO Person(name,favourite_drink_id)
VALUES ('{new_user.name}', {new_user.favourite_drink.drink_id})"""
new_user.person_id = db.run_query(query)
return new_user
def get_drinks_from_database():
drinks = []
db = Database(Config)
db_drinks = db.run_query("""SELECT * FROM Drink""")
for drink in db_drinks:
drinks.append(Drink(drink['name'], drink['drink_id']))
return drinks
def update_user_in_database(person: Person):
db = Database(Config)
if person.favourite_drink == None:
query = f"""UPDATE Person
SET name='{person.name}', favourite_drink_id=NULL"""
else:
query = f"""UPDATE Person
SET name='{person.name}', favourite_drink_id={person.favourite_drink.drink_id}"""
query += f""" WHERE person_id={person.person_id}"""
db.run_query(query)
def get_drink_by_id_from_database(drink_id):
db = Database(Config)
db_drinks = db.run_query(
f"""SELECT * FROM Drink AS d WHERE d.drink_id = {int(drink_id)} """)
if len(db_drinks) != 0:
drink = db_drinks[0]
return Drink(drink['name'], drink['drink_id'])
else:
return None
def search_drinks_by_name_from_database(drink_name):
drinks = []
db = Database(Config)
db_drinks = db.run_query(
f"""SELECT * FROM Drink AS d WHERE upper(d.name) = '{drink_name.strip().upper()}' """
)
for drink in db_drinks:
drinks.append(Drink(drink['name'], drink['drink_id']))
return drinks
def get_people_by_favourite_drink_from_database(drink_id):
db = Database(Config)
db_people = db.run_query(
f"""SELECT p.person_id, p.name as person_name, p.favourite_drink_id, d.name as drink_name
FROM Person AS p INNER JOIN Drink as d ON p.favourite_drink_id = d.drink_id WHERE p.favourite_drink_id = {drink_id} """
)
people = []
for person in db_people:
people.append(
Person(person['person_name'],
Drink(person['drink_name'], person['favourite_drink_id']),
person['person_id']))
return people
def get_person_by_id_from_database(person_id):
db = Database(Config)
db_users = db.run_query(
f"""SELECT p.person_id, p.name as person_name, p.favourite_drink_id, d.name as drink_name
FROM Person AS p INNER JOIN Drink as d ON p.favourite_drink_id = d.drink_id WHERE p.person_id = {person_id} """
)
if len(db_users) != 0:
user = db_users[0]
return Person(user['person_name'],
Drink(user['drink_name'], user['favourite_drink_id']),
user['person_id'])
else:
return None
def get_people_from_database():
users = []
db = Database(Config)
db_users = db.run_query(
"""SELECT p.person_id, p.name as person_name, p.favourite_drink_id, d.name as drink_name
FROM Person AS p LEFT JOIN Drink as d ON p.favourite_drink_id = d.drink_id"""
)
for user in db_users:
users.append(
Person(user['person_name'],
Drink(user['drink_name'], user['favourite_drink_id']),
user['person_id']))
return users
def search_person_by_name(person_name):
db = Database(Config)
people = []
db_users = db.run_query(
f"""SELECT p.person_id, p.name as person_name, p.favourite_drink_id, d.name as drink_name
FROM Person AS p INNER JOIN Drink as d ON p.favourite_drink_id = d.drink_id WHERE upper(p.name) = '{person_name.strip().upper()}' """
)
if len(db_users) != 0:
for user in db_users:
people.append(
Person(user['person_name'],
Drink(user['drink_name'], user['favourite_drink_id']),
user['person_id']))
return people
def get_round_by_id(round_id):
db = Database(Config)
query = f"""
SELECT r.is_open, r.brewer as brewer_id, r.open_date, r.round_id,
p.name as brewer_name, p2.name as person_name, d.name as drink_name,
d.drink_id, p2.person_id, o.order_id
FROM BrewOrder as o
RIGHT JOIN BrewRound as r
ON o.round_id = r.round_id
INNER JOIN Person as p
ON p.person_id = r.brewer
LEFT JOIN Drink as d
ON d.drink_id = o.drink_id
LEFT JOIN Person as p2
ON p2.person_id = o.person_id
WHERE r.round_id = {round_id}
"""
db_orders = db.run_query(query)
if len(db_orders) == 0:
return None
round_orders = []
for order in db_orders:
if order['person_name']:
round_orders.append(
Order(
Person(order['person_name'], person_id=order['person_id']),
Drink(order['drink_name'], order['drink_id']),
order['order_id']))
rount_to_return = Round(
round_orders, db_orders[0]['open_date'],
Person(db_orders[0]['brewer_name'], None, db_orders[0]['brewer_id']),
db_orders[0]['is_open'], db_orders[0]['round_id'])
return rount_to_return
def delete_user_in_database(person: Person):
db = Database(Config)
db.run_query(f"""DELETE FROM Person WHERE person_id={person.person_id}""")
def update_drink_in_database(drink: Drink):
db = Database(Config)
db.run_query(f"""UPDATE Drink
SET name='{drink.name}', drink_id={drink.drink_id}
WHERE drink_id={drink.drink_id}
""")
def delete_drink_in_database(drink: Drink):
db = Database(Config)
db.run_query(f"""DELETE FROM Drink WHERE drink_id={drink.drink_id}""")
def save_new_drink_in_database(new_drink: Drink):
db = Database(Config)
new_drink.drink_id = db.run_query(f"""INSERT INTO Drink(name)
VALUES ('{new_drink.name}')""")
return new_drink