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))