def create_game(payload):
query = """INSERT INTO Game (name, developer, publisher, price, description, image)
VALUES (%s, %s, %s, %s, %s, %s);"""
DbService.execute(query, 'c', payload['name'], payload['developer'],
payload['publisher'], payload['price'],
payload['description'], payload['image'])
return retrieve_game(payload['name'])
def create_user(payload):
query = """INSERT INTO User (first_name, last_name, email, address, gamertag, profile_picture,
password, role)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s);"""
DbService.execute(query, 'c', payload['first_name'], payload['last_name'],
payload['email'], payload['address'],
payload['gamertag'], payload['profile_picture'],
payload['password'], payload['role'])
return retrieve_user(payload['gamertag'])
def get_games(name):
if len(name):
search = '%' + name + '%'
query = """SELECT * FROM Game WHERE name LIKE %s;"""
result = DbService.execute(query, 'r', search)
else:
query = """SELECT * FROM Game;"""
result = DbService.execute(query, 'r')
response = []
for row in result:
response.append(Game(row))
return response
def retrieve_transactions_by_user(id):
transactions_query = """SELECT *
FROM Transaction
WHERE user_id = %s;"""
rows = DbService.execute(transactions_query, 'r', id)
return __get_personalized_transactions(rows)
def retrieve_transactions_by_game_id(game_id):
query = """SELECT *
FROM Transaction
WHERE game_id = %s;"""
result = DbService.execute(query, 'r', game_id)
return __process_multiple_transactions_result(result)
def __get_personalized_transactions(order_rows):
orders = []
print(order_rows)
for row in order_rows:
user_query = """SELECT gamertag FROM User WHERE id = %s"""
game_query = """SELECT name FROM Game WHERE id = %s"""
user = DbService.execute(user_query, 'r', row[1])
game = DbService.execute(game_query, 'r', row[2])
order = Transaction(row).to_dictionary()
order['gamertag'] = user[0][0]
order['game_name'] = game[0][0]
orders.append(order)
print(orders)
return orders
def get_users():
query = """SELECT * FROM User;"""
result = DbService.execute(query, 'r')
response = []
for row in result:
response.append(User(row))
return response
def get_user_role(id):
query = 'SELECT role FROM User WHERE id = %s;'
result = DbService.execute(query, 'r', id)[0]
if not len(result):
return None
return {'role': result[0]}
def retrieve_user(gamertag):
query = """SELECT *
FROM User
WHERE gamertag = %s;"""
result = DbService.execute(query, 'r', gamertag)
if len(result):
result = result[0]
return User(result)
else:
return None
def __retrieve_user_by_id(id):
query = """SELECT *
FROM User
WHERE id = %s;"""
result = DbService.execute(query, 'r', id)
if len(result):
return User(result[0])
else:
return None
def __retrieve_game_by_id(id):
query = """SELECT *
FROM Game
WHERE id = %s;"""
result = DbService.execute(query, 'r', id)
if len(result):
return Game(result[0])
else:
return None
def retrieve_game(name):
query = """SELECT *
FROM Game
WHERE name = %s;"""
result = DbService.execute(query, 'r', name)
if len(result):
result = result[0]
return Game(result)
else:
return None
def retrieve_transaction_by_order_number(order_number):
query = """SELECT *
FROM Transaction
WHERE order_number = %s;"""
result = DbService.execute(query, 'r', order_number)
if len(result):
return Transaction(result[0])
else:
return None
def update_transaction(payload):
query = """UPDATE Transaction
SET status = %s
WHERE order_number = %s;"""
rows_affected = DbService.execute(query, 'u', payload['status'],
payload['order_number'])
if not rows_affected:
return None
else:
return retrieve_transaction_by_order_number(payload['order_number'])
def delete_user(id):
query = """DELETE FROM User WHERE id = %s;"""
response = __retrieve_user_by_id(id)
if not response:
return response
result = DbService.execute(query, 'd', id)
if result:
return response
else:
return None
def login_user(payload):
query = "SELECT id, role, gamertag FROM User WHERE email = %s AND password = %s;"
result = DbService.execute(query, 'r', payload['email'],
payload['password'])
if len(result):
return {
'id': result[0][0],
'role': result[0][1],
'gamertag': result[0][2]
}
else:
return None
def update_user(payload):
query = """UPDATE User
SET first_name = %s,
last_name = %s,
email = %s,
gamertag = %s,
address = %s,
profile_picture = %s
WHERE id = %s;"""
rows_affected = DbService.execute(query, 'u', payload['first_name'],
payload['last_name'], payload['email'],
payload['gamertag'], payload['address'],
payload['profile_picture'],
payload['id'])
if not rows_affected:
return None
else:
return __retrieve_user_by_id(payload['id'])
def update_game(payload):
query = """UPDATE Game
SET name = %s,
developer = %s,
publisher = %s,
price = %s,
description = %s,
image = %s
WHERE id = %s;"""
rows_affected = DbService.execute(query, 'u', payload['name'],
payload['developer'],
payload['publisher'], payload['price'],
payload['description'], payload['image'],
payload['id'])
if not rows_affected:
return None
else:
return __retrieve_game_by_id(payload['id'])
def create_order(payload):
query = """INSERT INTO Transaction (user_id, game_id, total) VALUES (%s, %s, %s);"""
return DbService.execute(query, 'c', payload['user_id'],
payload['game_id'], payload['total'])
def check_email_gamertag_duplication(id, email, gamertag):
query = """SELECT email, gamertag FROM User WHERE (email = %s OR gamertag = %s) AND id != %s"""
result = DbService.execute(query, 'r', email, gamertag, id)
return len(result)
def get_transactions():
query = """SELECT * FROM Transaction;"""
rows = DbService.execute(query, 'r')
return __get_personalized_transactions(rows)