def get_league(self, league_id):
# get leagues metadata
self.db.query('''select Leagues.*
from Leagues
where Leagues.league_id = {}'''.format(league_id))
return_dict = util.get_dict_from_query(
self.db.store_result().fetch_row(maxrows=0, how=1))
return_dict['start_time'] = return_dict['start_time'].strftime(
"%m/%d/%Y")
return_dict['end_time'] = return_dict['end_time'].strftime("%m/%d/%Y")
# get data for pools in league
self.db.query(
'''select Pools.pool_id, Pools.day, cast(Pools.pool_time as char) as time, Teams_In_Pools.num_teams
from Pools, (select Teams.pool_id, count(*) as num_teams from Teams group by Teams.pool_id) as Teams_In_Pools
where Pools.league_id = {}
and Pools.pool_id = Teams_In_Pools.pool_id'''.format(
league_id, league_id))
pool_list = util.get_dict_from_query(self.db.store_result().fetch_row(
maxrows=0, how=1))
if type(pool_list) is dict and pool_list:
pool_list = [pool_list]
elif type(pool_list) is dict and not pool_list:
pool_list = []
return_dict['pools'] = pool_list
return return_dict
def get_pool(self, pool_id):
self.db.query(
'''select Pools.league_id, Pools.pool_id, Pools.day as poolDay, cast(Pools.pool_time as char) as poolTime,
Leagues.level as leagueLevel, Leagues.sport as leagueSport, Leagues.location as leagueLocation, Leagues.league_id as leagueId,
Teams.team_id, Teams.name as team_name, Teams.wins, Teams.losses, Teams.ties
from Pools, Teams, Leagues
where Pools.league_id = Leagues.league_id
and Teams.pool_id = Pools.pool_id
and Pools.pool_id = {}'''.format(pool_id))
r = self.db.store_result()
return_list = util.get_dict_from_query(r.fetch_row(maxrows=0, how=1))
if type(return_list) is dict and return_list: # only one team in pool
return_list = [return_list]
elif type(
return_list
) is dict and not return_list: # league is empty, so just return metadata
self.db.query(
'''select Pools.league_id, Pools.pool_id, Pools.day as poolDay, cast(Pools.pool_time as char) as poolTime,
Leagues.level as leagueLevel, Leagues.sport as leagueSport, Leagues.location as leagueLocation, Leagues.league_id as leagueId
from Pools, Leagues
where Pools.league_id = Leagues.league_id
and Pools.pool_id = {}'''.format(pool_id))
r = self.db.store_result()
return_dict = util.get_dict_from_query(
r.fetch_row(maxrows=0, how=1))
return return_dict
first_entry = return_list[0]
return_dict = {
'leagueLevel': first_entry['leagueLevel'],
'leagueSport': first_entry['leagueSport'],
'leagueLocation': first_entry['leagueLocation'],
'leagueId': first_entry['leagueId'],
'poolDay': first_entry['poolDay'],
'poolTime': first_entry['poolTime']
}
# for each team, apppend id, name, wins, losses, ties
teams = [{
'team_id': team['team_id'],
'team_name': team['team_name'],
'wins': team['wins'],
'losses': team['losses'],
'ties': team['ties']
} for team in return_list]
return_dict['teams'] = teams
return return_dict
def set_pool(self, data):
pool_id = None # keeping this variable in case we need it later
data = util.clean_query_input(data, "Pools")
if pool_id is None:
query = '''insert into Pools(
league_id,
day,
pool_time,
max_size)
values (
{}, \"{}\", \"{}\", {})'''.format(data['league_id'],
data['day'],
data['pool_time'],
data['max_size'])
self.db.query(query)
else:
self.db.query('''update Pools set
league_id = {},
day = {},
pool_time = {},
max_size = {}
where pool_id = {}'''.format(data['league_id'],
data['pool_time'],
data['max_size'], pool_id))
self.db.query('select last_insert_id()')
r = self.db.store_result()
return util.get_dict_from_query(r.fetch_row(how=1))['last_insert_id()']
def set_user(self, data):
user_id = None # keeping this variable in case we need it later
pass_hash = hashlib.sha256()
pass_hash.update(data['pass_hash'].encode(encoding='ascii'))
pass_hash = pass_hash.digest().hex()
data['pass_hash'] = pass_hash
data = util.clean_query_input(data, "Users")
if user_id is None:
self.db.query('''insert into Users(
pass_hash,
netid,
email,
first_name,
last_name,
is_undergrad,
is_admin,
gender,
residence_hall,
wins,
losses,
ties) values (
{},{},{},{},{},{},{},{},{},{},{},{})'''.format(
data['pass_hash'], data['netid'], data['email'],
data['first_name'], data['last_name'], data['is_undergrad'],
data['is_admin'], data['gender'], data['residence_hall'],
data['wins'], data['losses'], data['ties']))
#else:
# self.db.query('''update Users set
# pass_hash = {},
# netid = {},
# email = {},
# first_name = {},
# last_name = {},
# is_undergrad = {},
# is_admin = {},
# gender = {},
# residence_hall = {},
# wins = {},
# losses = {},
# ties = {}
# where user_id = {}'''.format(
# data['pass_hash'],
# data['netid'],
# data['email'],
# data['first_name'],
# data['last_name'],
# data['is_undergrad'],
# data['is_admin'],
# data['gender'],
# data['residence_hall'],
# data['wins'],
# data['losses'],
# data['ties'],
# user_id))
self.db.query('select last_insert_id()')
r = self.db.store_result()
return util.get_dict_from_query(r.fetch_row(how=1))['last_insert_id()']
def get_sport(self, sport_id):
self.db.query('''select * from Sports
where sport_id = {}'''.format(sport_id))
r = self.db.store_result()
return_dict = util.get_dict_from_query(r.fetch_row(how=1))
self.db.query('''select league_id from Leagues
where sport_id = {}'''.format(sport_id))
leagues_in_sport = util.get_dict_from_query(
self.db.store_result().fetch_row(maxrows=0, how=1))
if type(leagues_in_sport) is dict: # only returned one item
return_dict.update({"leagues": [leagues_in_sport["league_id"]]})
else: # returned multiple items
return_dict.update({
"leagues":
[sql_return["league_id"] for sql_return in leagues_in_sport]
})
return return_dict
def get_league_users(self, league_id):
self.db.query(
'''SELECT user_id FROM Users_Teams WHERE team_id IN (SELECT team_id FROM Teams WHERE pool_id IN (SELECT pool_id FROM Pools WHERE league_id = {}))'''
.format(league_id))
users_in_league = util.get_dict_from_query(
self.db.store_result().fetch_row(how=1))
if (len(users_in_league) == 0):
return {}
returner = {
'users': [sql_return['user_id'] for sql_return in users_in_league]
}
return returner
def get_users(self):
self.db.query(
'select user_id, first_name, last_name from Users order by last_name'
)
r = self.db.store_result()
d = util.get_dict_from_query(r.fetch_row(maxrows=0, how=1))
for m in range(len(d)):
if os.path.isfile("../data/userPictures/{}.jpg".format(
d[m]["user_id"])):
d[m]["profilePicExists"] = 1
else:
d[m]["profilePicExists"] = 0
return d
def set_sport(self, data):
sport_id = None # keeping this variable in case we need it later
data = util.clean_query_input(data, "Sports")
if sport_id is None:
self.db.query('''insert into Sports(
name) values (
{})'''.format(data['name']))
else:
self.db.query('''update Sports set
name = {}
where sport_id = {}'''.format(data['name'], sport_id))
self.db.query('select last_insert_id()')
r = self.db.store_result()
return util.get_dict_from_query(r.fetch_row(how=1))['last_insert_id()']
def validate_user(self, email, password):
pass_hash = hashlib.sha256()
pass_hash.update(password.encode(encoding='ascii'))
pass_hash = pass_hash.digest().hex()
self.db.query('''select user_id, pass_hash, is_admin
from Users where email = \'{}\' '''.format(email))
r = self.db.store_result()
result = util.get_dict_from_query(r.fetch_row(maxrows=0, how=1))
if not result:
return {"status": "failure", "reason": "email not in system"}
if result['pass_hash'] == pass_hash:
return {
"status": "success",
"user_id": result['user_id'],
"is_admin": result['is_admin']
}
else:
return {"status": "failure", "reason": "unknown"}
def set_team(self, data):
team_id = None # keeping this variable in case we need it later
data = util.clean_query_input(data, "Teams")
if team_id is None:
self.db.query('''insert into Teams(
league_id,
pool_id,
name,
wins,
losses,
ties,
max_members) values (
{},{},{},{},{},{},{})'''.format(
data['league_id'],
data['pool_id'],
data['name'],
data['wins'],
data['losses'],
data['ties'],
data['max_members']))
else:
self.db.query('''update Teams set
league_id = {},
pool_id = {},
name = {},
wins = {},
losses = {},
ties = {},
max_members = {}
where team_id = {}'''.format(
data['league_id'],
data['pool_id'],
data['name'],
data['wins'],
data['losses'],
data['ties'],
data['max_members'],
team_id))
self.db.query('select last_insert_id()')
r = self.db.store_result()
return util.get_dict_from_query(r.fetch_row(how=1))['last_insert_id()']
def set_league(self, data):
league_id = None # keeping this variable in case we need it later
data = util.clean_query_input(data, "Leagues")
# TODO: update sport id to grab from page rather than hard-coding in 1
if league_id is None:
self.db.query('''insert into Leagues(
sport_id,
name,
start_time,
end_time,
team_size)
values (
{},\"{}\",\"{}\",\"{}\",{})'''.format(
#data['sport_id'],
1,
data['leagueName'],
data['startDate'],
data['endDate'],
#data['team_size']))
10))
else:
self.db.query('''update Leagues set
sport_id = {},
name = {},
start_time = {},
end_time = {},
team_size = {}
where league_id = {}'''.format(data['sport_id'], data['name'],
data['start_time'],
data['end_time'],
data['team_size'], league_id))
self.db.query('select last_insert_id()')
r = self.db.store_result()
return util.get_dict_from_query(r.fetch_row(how=1))['last_insert_id()']
def get_user(self, user_id):
self.db.query('''select * from Users
where user_id = {}'''.format(user_id))
r = self.db.store_result()
return util.get_dict_from_query(r.fetch_row(how=1))
def get_user_notifications(self, user_id):
# get player Notifications
notification_query = '''select distinct Teams.name as team_name, Teams.team_id as team_id,
Leagues.league_id as league_id, Leagues.level as league_level, Leagues.sport as league_sport, concat(Leagues.level, " ", Leagues.sport) as league_name,
Users.first_name, Users.last_name
from Team_Requests, Teams, Leagues, Pools, Users
where Teams.team_id = Team_Requests.team_id
and Users.user_id = Teams.capt_id
and Pools.pool_id = Teams.pool_id
and Leagues.league_id = Pools.league_id
and Team_Requests.new_member_invited = 1
and Team_Requests.new_member_accepted = 0
and Team_Requests.new_member_id = {}'''.format(user_id)
self.db.query(notification_query)
playerNotifications = util.get_dict_from_query(
self.db.store_result().fetch_row(maxrows=0, how=1))
# get pending notifications
self.db.query(
'''select distinct Teams.name as team_name, Teams.team_id as team_id,
Users.first_name, Users.last_name
from Team_Requests, Teams, Users
where Teams.team_id = Team_Requests.team_id
and Users.user_id = Teams.capt_id
and Team_Requests.new_member_invited = 0
and Team_Requests.new_member_accepted = 0
and Team_Requests.new_member_id = {};
'''.format(user_id))
pendingNotifications = util.get_dict_from_query(
self.db.store_result().fetch_row(maxrows=0, how=1))
# get captain notifications
self.db.query(
'''select distinct Teams.name as team_name, Teams.team_id,
Users.first_name, Users.last_name, Users.user_id
from Team_Requests, Teams, Leagues, Users
where Teams.team_id = Team_Requests.team_id
and Users.user_id = Team_Requests.new_member_id
and Team_Requests.new_member_invited = 0
and Team_Requests.new_member_accepted = 0
and Teams.capt_id = {};
'''.format(user_id))
captainNotifications = util.get_dict_from_query(
self.db.store_result().fetch_row(maxrows=0, how=1))
# get game notifications
self.db.query(
'''select Games.game_id, DATE_FORMAT(Games.date, '%Y-%m-%d') as date, Games.team1_id, Games.team2_id,
cast(Pools.pool_time as char) as time,
Leagues.location as location, concat(Leagues.level, " ", Leagues.sport) as league, Leagues.league_id,
Teams1.name as team1Name, Teams1.wins as team1Wins, Teams1.losses as team1Losses, Teams1.ties as team1Ties,
Teams2.name as team2Name, Teams2.wins as team2Wins, Teams2.losses as team2Losses, Teams2.ties as team2Ties
from Games, Teams as Teams1, Teams as Teams2, Pools, Leagues
where (
Games.team1_id =
(select Users_Teams.team_id from Users_Teams where Users_Teams.user_id = {})
or Games.team2_id =
(select Users_Teams.team_id from Users_Teams where Users_Teams.user_id = {})
)
and Teams1.team_id = Games.team1_id
and Teams2.team_id = Games.team2_id
and Teams1.pool_id = Pools.pool_id
and Pools.league_id = Leagues.league_id
and Games.date <= DATE_ADD(curdate(), interval 1 month)
order by Games.date
;
'''.format(user_id, user_id))
gameNotifications = util.get_dict_from_query(
self.db.store_result().fetch_row(maxrows=0, how=1))
# get user's first name to welcome them
self.db.query(
'''select first_name from Users where user_id = {}'''.format(
user_id))
firstName = util.get_dict_from_query(self.db.store_result().fetch_row(
maxrows=0, how=1))
if type(playerNotifications) is dict and playerNotifications:
playerNotifications = [playerNotifications]
elif type(playerNotifications) is dict and not playerNotifications:
playerNotifications = []
if type(pendingNotifications) is dict and pendingNotifications:
pendingNotifications = [pendingNotifications]
elif type(pendingNotifications) is dict and not pendingNotifications:
pendingNotifications = []
if type(captainNotifications) is dict and captainNotifications:
captainNotifications = [captainNotifications]
elif type(captainNotifications) is dict and not captainNotifications:
captainNotifications = []
if type(gameNotifications) is dict and gameNotifications:
gameNotifications = [gameNotifications]
elif type(gameNotifications) is dict and not gameNotifications:
gameNotifications = []
return {
"playerNotifications":
playerNotifications,
"pendingNotifications":
pendingNotifications,
"captainNotifications":
captainNotifications,
"gameNotifications":
gameNotifications,
"profilePicExists":
os.path.exists("../data/userPictures/{}.jpg".format(user_id)),
"first_name":
firstName["first_name"]
}
def get_leagues(self):
self.db.query(
'select l.league_id, l.level, l.sport from Leagues l order by l.sport ASC'
)
return util.get_dict_from_query(self.db.store_result().fetch_row(
maxrows=0, how=1)) # return all rows as a dictionary
def get_teams(self):
self.db.query('''select t.team_id, t.name as team_name, cast(p.pool_time as char) as pool_time, t.wins, t.losses, t.ties, l.sport
from Teams t natural join Pools p natural join Leagues l
order by team_name''')
r = self.db.store_result()
return util.get_dict_from_query(r.fetch_row(maxrows=0, how=1))
def get_games(self, date):
self.db.query('''select date, t1.name as t1_name, t2.name as t2_name, location
from Games, Teams t1, Teams t2
where date(date) = \"{}\" and t1.team_id = Games.team1_id and t2.team_id = Games.team2_id'''.format(date))
return util.get_dict_from_query(self.db.store_result().fetch_row(maxrows=0, how=1)) # return all rows as a dictionary
def get_pools(self):
self.db.query('select * from Pools')
r = self.db.store_result()
return util.get_dict_from_query(r.fetch_row(maxrows=0, how=1))
def get_teams_league(self, league_id):
self.db.query('''SELECT team_id, name FROM Teams WHERE league_id = {}'''.format(league_id))
r = self.db.store_result()
return util.get_dict_from_query(r.fetch_row(maxrows=0, how=1))
def get_users_by_team(self, team_id):
self.db.query('''select user_id from Users_Teams
where team_id = {}'''.format(team_id))
r = self.db.store_result()
return util.get_dict_from_query(r.fetch_row(maxrows=0, how=1))
def get_team(self, team_id):
# get team metadata
self.db.query('''select Teams.name as teamName, Teams.wins as teamWins, Teams.losses as teamLosses, Teams.ties as teamTies, Teams.capt_id,
cast(Pools.pool_time as char) as poolTime, Pools.day as poolDay, Pools.pool_id as poolId,
Leagues.location as poolLocation, concat(Leagues.level, " ", Leagues.sport) as leagueName, Leagues.league_id as leagueId
from Teams, Pools, Leagues
where Teams.pool_id = Pools.pool_id
and Pools.league_id = Leagues.league_id
and Teams.team_id = {}'''.format(team_id))
r = self.db.store_result()
return_dict = util.get_dict_from_query(r.fetch_row(how=1))
# TODO: calculate team's rank in pool
return_dict["teamRankInPool"] = 1 # hard-coded for now
# get users on team
self.db.query('''select Users.user_id, Users.first_name, Users.last_name, Users.email, Users.gender
from Users, Users_Teams
where Users.user_id = Users_Teams.user_id
and Users_Teams.team_id = {}'''.format(team_id))
users_on_team = util.get_dict_from_query(self.db.store_result().fetch_row(maxrows=0, how=1))
if type(users_on_team) is dict: # only returned one item
if len(users_on_team) == 0:
return_dict.update({'roster': []})
else:
return_dict.update({"roster": [users_on_team]})
else: # returned multiple items
return_dict.update({"roster": users_on_team})
return_dict["malePlayers"] = sum([1 for player in return_dict["roster"] if player["gender"] == "Male"])
return_dict["femalePlayers"] = sum([1 for player in return_dict["roster"] if player["gender"] == "Female"])
for player in return_dict["roster"]:
if player["user_id"] == return_dict["capt_id"]:
player["is_capt"] = True
else:
player["is_capt"] = False
# get number of teams in pool
self.db.query('''select count(*) as teamsInPool
from Teams
where Teams.pool_id = (select pool_id from Teams where team_id={})'''.format(team_id))
r = self.db.store_result()
return_dict["teamsInPool"] = util.get_dict_from_query(r.fetch_row(how=1))["teamsInPool"]
# get team's games
self.db.query('''select Games.game_id as game_id, cast(Games.date as char) as date, Games.team1_score as team1Score, Games.team2_score as team2Score,
Team1.name as team1Name, Team1.team_id as team1Id,
Team2.name as team2Name, Team2.team_id as team2Id
from Games, Teams as Team1, Teams as Team2
where Games.team1_id = Team1.team_id
and Games.team2_id = Team2.team_id
and (Team1.team_id = {} or Team2.team_id = {})
order by date'''.format(team_id, team_id))
games = util.get_dict_from_query(self.db.store_result().fetch_row(maxrows=0, how=1))
if type(games) is dict and games:
games = [games]
elif type(games) is dict and not games:
games = []
for game in games:
if game["team1Id"] == int(team_id):
game["opponentId"] = game["team2Id"]
game["opponentName"] = game["team2Name"]
game["yourScore"] = game["team1Score"]
game["opponentScore"] = game["team2Score"]
elif game["team2Id"] == int(team_id):
game["opponentId"] = game["team1Id"]
game["opponentName"] = game["team2Name"]
game["yourScore"] = game["team2Score"]
game["opponentScore"] = game["team1Score"]
schedule = [{"date": game["date"], "opponentId": game["opponentId"], "opponentName": game["opponentName"], "yourScore": game["yourScore"], "opponentScore": game["opponentScore"]} for game in games]
return_dict["schedule"] = schedule
return return_dict
def get_user_email(self, email):
self.db.query("select * from Users where email = '{}'".format(email))
r = self.db.store_result()
return util.get_dict_from_query(r.fetch_row(how=1))
