def delete(self): token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") privilege_handler = PrivilegeHandler(token) if not privilege_handler.assign_privileges( ): # I'm using this as a placeholder for a general admin privilege abort(403, error="Unauthorized Access (invalid permissions)") parser = reqparse.RequestParser() parser.add_argument('leagueID', type=int) args = parser.parse_args() target_league_id = args['leagueID'] db = DatabaseConnector() db.cursor.execute( "DELETE FROM gameMembers WHERE teamID in (SELECT teamID FROM teams WHERE leagueID = %d);" % target_league_id) db.cursor.execute("DELETE FROM games WHERE leagueID = %d;" % target_league_id) db.cursor.execute( "DELETE FROM players WHERE teamID in (SELECT teamID FROM teams WHERE leagueID = %d);" % target_league_id) db.cursor.execute("DELETE FROM teams WHERE leagueID = %d;" % target_league_id) db.cursor.execute("DELETE FROM leagues WHERE leagueID = %d;" % target_league_id) db.conn.commit() return 200
def put(self): token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token") privilege_handler = PrivilegeHandler(token) if not privilege_handler.user_privileges(): abort(403, error="Unauthorized Access (invalid permissions)") parser = reqparse.RequestParser() parser.add_argument('userID') parser.add_argument('userType') parser.add_argument('firstName') parser.add_argument('lastName') parser.add_argument('email') args = parser.parse_args() user_id = args['userID'] user_type = args['userType'] first_name = args['firstName'] last_name = args['lastName'] email = args['email'] privilege_id = None if user_type == 'Admin': privilege_id = 1 elif user_type == 'Coordinator': privilege_id = 2 elif user_type == 'Manager': privilege_id = 3 elif user_type == 'Referee': privilege_id = 4 # using update_user stored procedure to update user db_connector = DatabaseConnector() db_connector.cursor.callproc( 'update_user', [user_id, privilege_id, user_type, first_name, last_name, email]) db_connector.conn.commit() # getting user_id to return to the frontend db_connector.cursor.execute('CALL get_user("{}");'.format(email)) db_response = db_connector.cursor.fetchone() user_data = { 'user_id': db_response[0], 'user_type': db_response[2], 'first_name': db_response[3], 'last_name': db_response[4], 'email': db_response[5], 'last_login': db_response[7].strftime('%Y-%m-%d %H:%M:%S') if db_response[7] else None } db_connector.conn.close() return {'user': user_data}, 200
def get(self): db_connector = DatabaseConnector() parser = reqparse.RequestParser() parser.add_argument('leagueID', type=int) args = parser.parse_args() league_id = args['leagueID'] db_connector.cursor.execute('SELECT * FROM games WHERE leagueID = ' + str(league_id)) games = db_connector.cursor.fetchall() schedule_data = [] for game in games: schedule_data.append({ "gameID": game[0], "leagueID": game[1], "homeTeamID": game[2], "awayTeamID": game[3], "refereeID": game[4], "gameTime": str(game[5]), "fieldName": game[6], "status": game[7] }) return {'games': schedule_data}, 200
def __init__(self, token): tk_handler = TokenHandler() db_connector = DatabaseConnector() user_email = tk_handler.decode_token(token) db_connector.cursor.execute('CALL get_user("{}");'.format(user_email)) db_response = db_connector.cursor.fetchone() privilege_id = db_response[1] if privilege_id is None: self.privileges = None else: db_connector.cursor.execute('CALL get_privileges({});'.format(privilege_id)) db_response = db_connector.cursor.fetchone() self.privileges = { 'privileges_type': db_response[1], 'cancel_game': db_response[2], 'update_score': db_response[3], 'create_player': db_response[4], 'create_game': db_response[5], 'create_user': db_response[6], 'create_team': db_response[7], 'create_league': db_response[8], 'assign_player': db_response[9], 'assign_manager': db_response[10], 'assign_coordinator': db_response[11], 'assign_privileges': db_response[12] } db_connector.conn.close()
def get(self): db_connector = DatabaseConnector() parser = reqparse.RequestParser() parser.add_argument('leagueID', type=int) parser.add_argument('playerID', type=int) args = parser.parse_args() league_id = args['leagueID'] player_id = args['playerID'] db_connector.cursor.execute( 'select * from games where leagueID = ' + str(league_id) + ' and (homeTeamID in (select teamID from players where playerID = ' + str(player_id) + ') or awayTeamID in (select teamID from players where playerID = ' + str(player_id) + '));') # TODO filter out games from the past games = db_connector.cursor.fetchall() schedule_data = {} for game in games: schedule_data[game[0]] = { "gameID": game[0], "homeTeamID": game[2], "awayTeamID": game[3], "gameTime": game[5], "field": game[6], "canceled": game[7] } return schedule_data, 200
def get(self): db_connector = DatabaseConnector() db_connector.cursor.callproc('get_all_users') users = db_connector.cursor.fetchall() users_data = [] for user in users: users_data.append({ 'userID': user[0], 'privilegeID': user[1], 'userType': user[2], 'firstName': user[3], 'lastName': user[4], 'email': user[5], 'lastLogin': user[7].strftime('%Y-%m-%d %H:%M:%S') if user[7] else None }) db_connector.conn.close() return {'users': users_data}, 200
def get(self): """ Gets all player records from the database. :return: The list of all teams .. code-block:: javascript { 'players': List (of Player JSON objects) } Player JSON object: .. code-block:: javascript { 'playerID': Integer, 'teamID': Integer, 'firstName': String, 'lastName': String, 'email': String, 'number': Integer, 'loanedGames': Integer, 'goals': Integer, 'cleanSheet': Integer, 'yellowCards': Integer, 'redCards': Integer } Success gives status code 200 """ db_connector = DatabaseConnector() db_connector.cursor.callproc('get_players', []) players = db_connector.cursor.fetchall() db_connector.conn.close() players_data = [] for player in players: players_data.append({ 'playerID': player[0], 'teamID': player[1], 'firstName': player[2], 'lastName': player[3], 'email': player[4], 'number': player[5], 'loanedGames': player[6], 'goals': player[7], 'cleanSheet': player[8], 'yellowCards': player[9], 'redCards': player[10] }) return {'players': players_data}, 200
def post(self): token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") privilege_handler = PrivilegeHandler(token) if not privilege_handler.game_privileges(): abort(403, error="Unauthorized Access (invalid permissions)") db = DatabaseConnector() # 1. generate play order for teams in league # 2. match play order with field/time pairs given by client # 3. generate db data # EXPECTED DATA # {leagueID: 1, # games: [ {gameTime: "2000-12-12 06:00:00", (formatted datetime str) # field: "FieldA", # refereeID: 1} (TODO will likely need to implement some sort of referee lookup to handle this data # ]} data = request.get_json(force=True) print(data) leagueid = data["leagueID"] db.cursor.execute('SELECT * FROM teams WHERE leagueID = ' + str(leagueid)) teams = db.cursor.fetchall() datelist = [] teamlist = [] for t in teams: teamlist.append(t[0]) for g in data['games']: datetime = g["gameTime"] field = g["field"] ref = g['refereeID'] datelist.append((datetime, field, ref)) matchlist = Scheduler.create_matches(teamlist) print(matchlist) scheduleGenerator = newScheduler.findNextValid(matchlist, datelist) schedule = [] for i in range(0, len(datelist)): schedule.append(next(scheduleGenerator)) for s in schedule: homeID = s[0][0] awayID = s[0][1] gameTime = s[1][0] field = s[1][1] query = 'INSERT INTO games (leagueID, homeTeamID, awayTeamID, gameTime, field) VALUES (%d, %d, %d, \'%s\', \'%s\');' % ( leagueid, homeID, awayID, gameTime, field) print(query) db.cursor.execute(query=query) return schedule, 200
def get(self, team_id): """ Gets team roster from the database. :return: The list of all active players on a team .. code-block:: javascript { 'roster': List (of Player JSON objects) } Player JSON object: .. code-block:: javascript { 'playerID': Integer, 'teamID': Integer, 'firstName': String, 'lastName': String, 'email': String, 'number': Integer, 'loanedGames': Integer } Success gives status code 200 """ db_connector = DatabaseConnector() db_connector.cursor.callproc('get_team_roster', [team_id]) roster = db_connector.cursor.fetchall() db_connector.conn.close() roster_data = [] print(roster) for player in roster: roster_data.append({ 'playerID': player[0], 'teamID': player[1], 'firstName': player[2], 'lastName': player[3], 'email': player[4], 'number': player[5], 'loanedGames': player[6] }) return {'Team' + team_id: roster_data}, 200
def post(self): token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") privilege_handler = PrivilegeHandler(token) if not privilege_handler.schedule_privileges(): abort(403, error="Unauthorized Access (invalid permissions)") parser = reqparse.RequestParser() parser.add_argument('leagueID', type=int, required=True) parser.add_argument('homeTeamID', type=int, required=True) parser.add_argument('awayTeamID', type=int, required=True) parser.add_argument('refereeID', type=int) parser.add_argument('gameTime', type=str) parser.add_argument('fieldName', type=str) parser.add_argument('status', type=str) args = parser.parse_args() league_id = args['leagueID'] home_team_id = args['homeTeamID'] away_team_id = args['awayTeamID'] referee_id = args['refereeID'] game_time = args['gameTime'] field_name = args['fieldName'] status = args['status'] db = DatabaseConnector() db.cursor.callproc('create_game', [ league_id, home_team_id, away_team_id, referee_id, game_time, field_name, status ]) db.conn.commit() db.cursor.close() game_data = { 'leagueName': league_id, 'homeTeamID': home_team_id, 'awayTeamID': away_team_id, 'refereeID': referee_id, 'gameTime': game_time, 'fieldName': field_name, 'status': status, } return {'game': game_data}, 201
def get(self): """ Gets all league records from the database. :return: The list of all leagues .. code-block:: javascript { 'leagues': List (of League JSON objects) } League JSON object: .. code-block:: javascript { 'leagueID': Integer, 'managerID': Integer, 'leagueName': String, 'season': String } Success gives status code 200 """ db_connector = DatabaseConnector() db_connector.cursor.execute('SELECT * FROM leagues') leagues = db_connector.cursor.fetchall() leagues_data = [] for league in leagues: leagues_data.append({ 'leagueID': league[0], 'managerID': league[1], 'leagueName': league[2], 'season': league[3], 'pointScheme': league[4] }) return {'leagues': leagues_data}, 200
def put(self): token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") privilege_handler = PrivilegeHandler(token) if not privilege_handler.player_privileges(): abort(403, error="Unauthorized Access (invalid permissions)") parser = reqparse.RequestParser() parser.add_argument('playerID', type=int) parser.add_argument('teamID', type=int) parser.add_argument('firstName', type=str) parser.add_argument('lastName', type=str) parser.add_argument('email', type=str) parser.add_argument('number', type=int) args = parser.parse_args() player_id = args['playerID'] team_id = args['teamID'] first_name = args['firstName'] last_name = args['lastName'] email = args['email'] if args['email'] else None number = args['number'] db_connector = DatabaseConnector() db_connector.cursor.callproc( 'update_player', [player_id, team_id, first_name, last_name, email, number]) db_connector.conn.commit() db_connector.conn.close() player = { 'playerID': player_id, 'teamID': team_id, 'firstName': first_name, 'lastName': last_name, 'email': email, 'number': number } return {'player': player}, 200
def applySQL(self, filepath, skip): # Open file fd = open(filepath, 'r') sqlFile = fd.read() fd.close() sqlCommands = "" # If there is a delimiter split on it and execute if ('DELIMITER' in sqlFile): # Remove delimiters sqlCommands = sqlFile.replace('DELIMITER //', '') sqlCommands = sqlCommands.replace('DELIMITER ;', '') # split file sqlCommands = sqlCommands.split("//") else: # split file sqlCommands = sqlFile.split(';') # Open DB connection db = DatabaseConnector() # Execute every command from the input file for command in sqlCommands: try: # Skip x number of commands. ie: DROP DATABASE IF EXISTS leagues; if (skip > 0): skip = skip - 1 raise Exception('skip command') # execute a command self.execute(db.cursor, command) except Exception as e: # print error if hasattr(e, 'message'): print(e.message) else: print(e) db.conn.commit() db.cursor.close()
def delete(self): token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token") privilege_handler = PrivilegeHandler(token) if not privilege_handler.user_privileges(): abort(403, error="Unauthorized Access (invalid permissions)") parser = reqparse.RequestParser() parser.add_argument('userID') args = parser.parse_args() user_id = args['userID'] # deleting user object db_connector = DatabaseConnector() db_connector.cursor.execute( 'DELETE FROM users WHERE userID = {}'.format(user_id)) db_connector.conn.commit() db_connector.conn.close() return
def delete(self): """ Deletes a team from the database. :Input: ID of the team to delete. .. code-block:: javascript { 'teamID': Integer } Possible status codes: 403 = No permissions, 200 = Successfully deleted. """ token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") privilege_handler = PrivilegeHandler(token) if not privilege_handler.team_privileges(): abort(403, error="Unauthorized Access (invalid permissions)") parser = reqparse.RequestParser() parser.add_argument('teamID') args = parser.parse_args() team_id = args['teamID'] # deleting team object db_connector = DatabaseConnector() try: db_connector.cursor.execute('DELETE FROM teams WHERE teamID = {}'.format(team_id)) db_connector.conn.commit() except Exception as e: abort(401, error='Invalid delete: {}'.format(e)) finally: db_connector.conn.close() return 200
def delete(self, game_id): token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") privilege_handler = PrivilegeHandler(token) if not privilege_handler.league_privileges(): abort(403, error="Unauthorized Access (invalid permissions)") player_id = request.args.get("player_id") # creating new league in the database db_connector = DatabaseConnector() try: db_connector.cursor.callproc('delete_game_member', [int(game_id), player_id]) except Exception as e: abort(400, error=str(e)) db_connector.conn.commit() db_connector.cursor.close() return 'gameMember (' + str(game_id) + ", " + str( player_id) + ") has been deleted", 200
def put(self): token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") privilege_handler = PrivilegeHandler(token) if not privilege_handler.league_privileges( ): # I'm using this as a placeholder for a general admin privilege abort(403, error="Unauthorized Access (invalid permissions)") parser = reqparse.RequestParser() parser.add_argument('leagueID', type=int, required=True) parser.add_argument('managerID', type=int) parser.add_argument('leagueName', type=str) parser.add_argument('season', type=str) parser.add_argument('pointScheme', type=str) args = parser.parse_args() query = "UPDATE leagues SET coordinatorID = %d, leagueName = '%s', season = '%s', pointScheme = '%s' WHERE leagueID = %d" \ % (args['managerID'], args['leagueName'], args['season'], args['pointScheme'], args['leagueID']) db = DatabaseConnector() db.cursor.execute(query) db.conn.commit() return 200
def delete(self): token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") privilege_handler = PrivilegeHandler(token) if not privilege_handler.schedule_privileges(): abort(403, error="Unauthorized Access (invalid permissions)") parser = reqparse.RequestParser() parser.add_argument('leagueID', type=int, required=True) args = parser.parse_args() query = "SELECT gameID from gameMembers WHERE gameID in (SELECT gameID from games where leagueID = %s)" % args[ 'leagueID'] db = DatabaseConnector() db.cursor.execute(query) res = db.cursor.fetchall() if res: return 400, "Cannot delete schedule were games have been recorded" else: query = "DELETE FROM games WHERE leagueID = %s" % args['leagueID'] db.cursor.execute(query) db.conn.commit() return 200
def delete(self): token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") privilege_handler = PrivilegeHandler(token) if not privilege_handler.player_privileges(): abort(403, error="Unauthorized Access (invalid permissions)") parser = reqparse.RequestParser() parser.add_argument('playerID') args = parser.parse_args() player_id = args['playerID'] db_connector = DatabaseConnector() try: db_connector.cursor.execute( 'DELETE FROM players WHERE playerID = {}'.format(player_id)) db_connector.conn.commit() db_connector.conn.close() except Exception as e: db_connector.conn.close() abort(400, error='Invalid delete {}'.format(e)) return
def get(self): """ Gets user data from the database. :Input: .. code-block:: javascript Header: 'Authorization': String (JSON Web Token) :return: A JSON object containing the user data. .. code-block:: javascript { 'userID': Integer, 'userType': String, 'firstName': String, 'lastName': String, 'email': String, 'lastLogin': String } Success gives status code 200 """ token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") tk_handler = TokenHandler() user_email = tk_handler.decode_token(token) db_connector = DatabaseConnector() # getting user_id to return to the frontend db_connector.cursor.execute('CALL get_user("{}");'.format(user_email)) db_response = db_connector.cursor.fetchone() user_data = { 'userID': db_response[0], 'userType': db_response[2], 'firstName': db_response[3], 'lastName': db_response[4], 'email': db_response[5], 'lastLogin': db_response[7].strftime('%Y-%m-%d %H:%M:%S') if db_response[7] else None } # returning data for all users if the user has user modification privileges privilege_handler = PrivilegeHandler(token) payload = {'user': user_data} if privilege_handler.user_privileges(): db_connector.cursor.execute('SELECT * FROM users') users = db_connector.cursor.fetchall() users_data = [] for user in users: users_data.append({ 'userID': user[0], 'privilegeID': user[1], 'userType': user[2], 'firstName': user[3], 'lastName': user[4], 'email': user[5], 'lastLogin': user[7].strftime('%Y-%m-%d %H:%M:%S') if user[7] else None }) payload['users'] = users_data db_connector.conn.close() return payload, 200
def post(self): """ Adds a new user to the database with default permissions. :Input: JSON object representing the new user account .. code-block:: javascript { 'email': String, 'password': String, 'firstName': String, 'lastName': String } :return: The user object that was created .. code-block:: javascript { 'teamName': String, 'leagueID': Integer, 'colour': String (Hex Colour Code) } Success gives status code 201 """ parser = reqparse.RequestParser() parser.add_argument('email', type=str) parser.add_argument('password', type=str) parser.add_argument('firstName', type=str) parser.add_argument('lastName', type=str) args = parser.parse_args() first_name = args['firstName'] last_name = args['lastName'] email = args['email'] hash_pw = pbkdf2_sha512.encrypt(args['password'], rounds=30000, salt_size=32) db_connector = DatabaseConnector() if db_connector.cursor.execute( 'CALL get_user("{}");'.format(email)) != 0: abort(409, error='email entered is already registered', email=email) # using create_user sp to store new user db_connector.cursor.callproc('create_user', [first_name, last_name, email, hash_pw]) db_connector.conn.commit() # getting user_id to return to the frontend db_connector.cursor.execute('CALL get_user("{}");'.format(email)) db_response = db_connector.cursor.fetchone() user_data = { 'userID': db_response[0], 'userType': db_response[2], 'firstName': db_response[3], 'lastName': db_response[4], 'email': db_response[5], 'lastLogin': db_response[7] } db_connector.conn.close() return {"user": user_data}, 201
def get(self, game_id): """ Gets team roster from the database. :return: The list of all active players on a team .. code-block:: javascript { 'game-roster': { 'home': The home teams game roster 'away': The away teams game roster } } Roster JSON object: .. code-block:: javascript List (of player JSON objects) Player JSON object: .. code-block:: javascript { 'playerID': Integer, 'teamID': Integer, 'firstName': String, 'lastName': String, 'email': String, 'number': Integer, 'loanedGames': Integer } Success gives status code 200 """ db_connector = DatabaseConnector() db_connector.cursor.callproc('get_game_roster', [game_id]) roster = db_connector.cursor.fetchall() db_connector.conn.close() home_roster = [] away_roster = [] for player in roster: playerObj = { 'playerID': player[0], 'firstName': player[1], 'lastName': player[2], 'email': player[3], 'loanedGames': player[4], 'teamID': player[6], 'number': player[8], 'goals': player[9], 'cleanSheet': player[10], 'yellowCards': player[11], 'redCards': player[12] } if playerObj['teamID'] == player[13]: home_roster.append(playerObj) else: away_roster.append(playerObj) return {"game-roster": {"home": home_roster, "away": away_roster}}
def post(self, game_id): """ Adds a roster to the database. :Input: JSON object representing the game-roster .. code-block:: javascript roster: List<Player> { "playerID": int, "teamID": int, "number": int, "goals": int, "cleanSheet": int, "yellowCards": int, "redCards": int } Success gives status code 201 """ token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") privilege_handler = PrivilegeHandler(token) if not privilege_handler.assign_player(): abort(403, error="Unauthorized Access (invalid permissions)") roster = request.get_json()['roster'] # creating new league in the database db_connector = DatabaseConnector() db_connector.cursor.callproc('get_game_team_lineup', [int(game_id)]) team_lineup = db_connector.cursor.fetchall()[0] conflicts = [] count = 0 for player in roster: player_id = player['playerID'] team_id = player['teamID'] number = player['number'] if team_id != team_lineup[0] and team_id != team_lineup[1]: conflicts.append("Team " + str(team_id) + " is not playing in game " + str(game_id)) else: try: db_connector.cursor.callproc( 'create_game_member', [int(game_id), player_id, team_id, number, 0, 0, 0, 0]) except Exception as e: conflicts.append(str(e)) count += 1 db_connector.conn.commit() db_connector.cursor.close() return {'conflicts': conflicts}, 201
def post(self): """ Adds a new league to the database. .. todo:: Privileges associated with token .. code-block:: python token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") :Input: JSON object representing the league .. code-block:: javascript { 'leagueName': String, 'season': String } :return: The league object that was created .. code-block:: javascript { 'leagueName': String, 'season': String } Success gives status code 201 """ token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") privilege_handler = PrivilegeHandler(token) if not privilege_handler.league_privileges(): abort(403, error="Unauthorized Access (invalid permissions)") parser = reqparse.RequestParser() parser.add_argument('leagueName', type=str) parser.add_argument('season', type=str) parser.add_argument('pointScheme', type=str) args = parser.parse_args() league_name = args['leagueName'] season = args['season'] point_scheme = args['pointScheme'] # creating new league in the database db_connector = DatabaseConnector() db_connector.cursor.callproc('create_league', [league_name, season, point_scheme]) db_connector.conn.commit() db_connector.cursor.close() league_data = { 'leagueName': league_name, 'season': season, 'pointScheme': point_scheme } return {'league': league_data}, 201
def post(self): """ Adds a new team to the database. :Input: JSON object representing the team .. code-block:: javascript { 'teamName': String, 'leagueID': Integer, 'colour': String (Hex Colour Code) } :return: The team object that was created .. code-block:: javascript { 'teamName': String, 'leagueID': Integer, 'colour': String (Hex Colour Code) } Possible status codes: 403 = No permissions, 201 = Successfully added. """ token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") privilege_handler = PrivilegeHandler(token) if not privilege_handler.team_privileges(): abort(403, error="Unauthorized Access (invalid permissions)") parser = reqparse.RequestParser() parser.add_argument('teamName', type=str) parser.add_argument('leagueID', type=int) parser.add_argument('colour', type=str) args = parser.parse_args() team_name = args['teamName'] league_id = args['leagueID'] colour = args['colour'] # creating new league in the database db_connector = DatabaseConnector() db_connector.cursor.callproc('create_team', [team_name, league_id, colour]) db_connector.conn.commit() db_connector.cursor.close() team_data = { 'teamName': team_name, 'leagueID': league_id, 'colour': colour } return {'team': team_data}, 201
def get(self): """ Gets all team records from the database. :return: The list of all teams .. code-block:: javascript { 'teams': List (of Team JSON objects) } Team JSON object: .. code-block:: javascript { 'teamID': Integer, 'leagueID': Integer, 'managerID': Integer, 'teamName': String, 'colour': String (Hex Colour Code), 'leaguePoints': Integer, 'wins': Integer, 'losses': Integer, 'draws': Integer, 'gamesPlayed': Integer, 'goalsFor': Integer, 'goalsAgainst': Integer, 'goalsDifference': Integer, 'cleanSheets': Integer, 'yellowCards': Integer, 'redCards': Integer } Possible status codes: 200 = Success. """ db_connector = DatabaseConnector() db_connector.cursor.callproc('get_all_teams') teams = db_connector.cursor.fetchall() teams_data = [] for team in teams: teams_data.append({ 'teamID': team[0], 'leagueID': team[1], 'managerID': team[2], 'teamName': team[3], 'colour': team[4], 'leaguePoints': team[5], 'wins': team[6], 'losses': team[7], 'draws': team[8], 'goalsFor': team[9], 'goalsAgainst': 0, 'cleanSheets': team[10], 'yellowCards': team[11], 'redCards': team[12] }) # getting team statistics query = 'SELECT g.teamID, g.gameID, CAST(SUM(g.goals) AS INT) FROM gameMembers g GROUP BY g.gameID, g.teamID;' db_connector.cursor.execute(query) goals_data = db_connector.cursor.fetchall() teams = [] games = [] goals = [] for row in goals_data: teams.append(row[0]) goals.append(row[2]) games.append(row[1]) goals_against = {} for team_id in set(teams): goals_against[team_id] = 0 games_played = [] for i in range(len(games)): if teams[i] == team_id: games_played.append(games[i]) for i in range(len(teams)): if games[i] in games_played and teams[i] != team_id: goals_against[team_id] += goals[i] for team in teams_data: team['goalsAgainst'] = 0 if team['teamID'] not in goals_against.keys() else goals_against[team['teamID']] team['goalDifference'] = team['goalsFor'] - team['goalsAgainst'] team['gamesPlayed'] = team['wins'] + team['losses'] + team['draws'] # calculating league points for team in teams_data: query = 'SELECT pointScheme FROM leagues WHERE leagueID = {}'.format(team['leagueID']) db_connector.cursor.execute(query) point_scheme = db_connector.cursor.fetchone()[0] if point_scheme == 'Capital Scoring': team['leaguePoints'] = 1 * team['wins'] + 0 * team['draws'] - 1 * team['losses'] elif point_scheme == 'Standard': team['leaguePoints'] = 3 * team['wins'] + 1 * team['draws'] + 0 * team['losses'] db_connector.conn.close() return {'teams': teams_data}, 200
def post(self): """ Adds a new player to the database. :Input: JSON object representing the player .. code-block:: javascript { 'teamID': Integer, 'firstName': String, 'lastName': String, 'email': String (optional), 'number': Integer (optional) } :return: The player object that was created .. code-block:: javascript { 'teamID': Integer, 'firstName': String, 'lastName': String, 'email': String, 'number': Integer } Success gives status code 201 """ token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") privilege_handler = PrivilegeHandler(token) if not privilege_handler.player_privileges(): abort(403, error="Unauthorized Access (invalid permissions)") parser = reqparse.RequestParser() parser.add_argument('teamID', type=int) parser.add_argument('firstName', type=str) parser.add_argument('lastName', type=str) parser.add_argument('email', type=str) parser.add_argument('number', type=int) args = parser.parse_args() team_id = args['teamID'] first_name = args['firstName'] last_name = args['lastName'] email = args['email'] if args['email'] else None number = args['number'] db_connector = DatabaseConnector() db_connector.cursor.callproc( 'create_player', [team_id, first_name, last_name, email, number]) db_connector.conn.commit() db_connector.conn.close() player = { 'teamID': team_id, 'firstName': first_name, 'lastName': last_name, 'email': email, 'number': number } return {'player': player}, 201
def post(self): """ Attempts to log in with the given account. :Input: JSON object with the user email and password. .. code-block:: javascript { 'email': String, 'password': String } :return: The JSON Web Token and the user's data. .. code-block:: javascript { 'token': String (JSON Web Token), 'user_data': { 'userID': Integer, 'userType': String, 'firstName': String, 'lastName': String, 'email': String, 'lastLogin': String } } Success gives status code 201 """ parser = reqparse.RequestParser() parser.add_argument('email', type=str) parser.add_argument('password', type=str) args = parser.parse_args() email = args['email'] password = args['password'] db_connector = DatabaseConnector() if db_connector.cursor.execute( 'CALL get_user("{}");'.format(email)) == 0: abort(404, error='email entered has not been registered') db_response = db_connector.cursor.fetchone() user_data = { 'userID': db_response[0], 'userType': db_response[2], 'firstName': db_response[3], 'lastName': db_response[4], 'email': db_response[5], 'lastLogin': db_response[7].strftime('%Y-%m-%d %H:%M:%S') if db_response[7] else None } if not pbkdf2_sha512.verify(password, db_response[6]): abort(403, error='the password entered is incorrect') # update last login in database update_stmt = 'UPDATE users SET lastLogin = "******" WHERE email = "{}"' db_connector.cursor.execute( update_stmt.format(time.strftime('%Y-%m-%d %H:%M:%S'), email)) db_connector.conn.commit() db_connector.conn.close() # creating validation token token_handler = TokenHandler() token = token_handler.create_token(email) return {'token': token.decode('UTF-8'), 'user': user_data}, 201
def put(self): """ Updates a team object in the database. :Input: Team JSON object with new data. .. code-block:: javascript { 'teamID': Integer, 'leagueID': Integer, 'managerID': Integer, 'teamName': String, 'colour': String (Hex Colour Code), 'leaguePoints': Integer, 'wins': Integer, 'losses': Integer, 'draws': Integer } :return: The updated team JSON object .. code-block:: javascript { 'teamID': Integer, 'leagueID': Integer, 'managerID': Integer, 'teamName': String, 'colour': String (Hex Colour Code), 'leaguePoints': Integer, 'wins': Integer, 'losses': Integer, 'draws': Integer } Possible status codes: 403 = No permissions, 200 = Successfully updated. """ token = request.headers.get('Authorization') if not token: abort(403, error="Unauthorized Access (no token)") privilege_handler = PrivilegeHandler(token) if not privilege_handler.team_privileges(): abort(403, error="Unauthorized Access (invalid permissions)") parser = reqparse.RequestParser() parser.add_argument('teamID', type=int) parser.add_argument('leagueID', type=int) parser.add_argument('managerID', type=int) parser.add_argument('teamName', type=str) parser.add_argument('colour', type=str) parser.add_argument('leaguePoints', type=str) parser.add_argument('wins', type=str) parser.add_argument('losses', type=str) parser.add_argument('draws', type=str) args = parser.parse_args() team_id = args['teamID'] league_id = args['leagueID'] manager_id = args['managerID'] team_name = args['teamName'] colour = args['colour'] league_points = args['leaguePoints'] wins = args['wins'] losses = args['losses'] draws = args['draws'] # using update_team stored procedure to update team db_connector = DatabaseConnector() db_connector.cursor.callproc('update_team', [team_id, league_id, manager_id, team_name, colour, league_points, wins, losses, draws]) db_connector.conn.commit() # getting team_id to return to the frontend db_connector.cursor.callproc('get_team', [team_id]) db_response = db_connector.cursor.fetchone() team_data = { 'teamID': db_response[0], 'leagueID': db_response[1], 'managerID': db_response[2], 'teamName': db_response[3], 'colour': db_response[4], 'leaguePoints': db_response[5], 'wins': db_response[6], 'losses': db_response[7], 'draws': db_response[8] } db_connector.conn.close() return team_data, 200