def generate_players_page(connection, season, s3, push_to_s3, create_local_files): query = f'SELECT * FROM player_x_season_detail WHERE season={season} ORDER BY lnf' print(query) rows = get_rows(query) with open("html/templates/trade_row.html") as file: template = file.read() vals = [ "added_player_fnf", "date", "dropped_player_fnf", "nickname", "pos" ] table_rows = "" for row in rows: print(row) content = get_players_page_content(connection, season) if create_local_files: write_to_local_disk(content, "players", season) if push_to_s3: write_to_s3(content, "players", season, s3)
def get_leaders(col, person_type, season, picked_only=True): if person_type == 'owner': name_col = 'nickname' table = 'ownersXseasons_detail' if person_type == 'player': name_col = 'fnf' if picked_only: table = 'player_x_season_leaderboard_rostered_only' else: table = 'player_x_season_leaderboard_all' query = f'SELECT {name_col} AS name, {col} AS val FROM {table} WHERE season={season} ORDER BY {col} DESC, {name_col} ASC LIMIT 5' print(query) rows = get_rows(query) i = 1 for x in range(0, len(rows)): rows[x]['rank'] = i i += 1 print(rows) return rows
def get_most_drafted_by(connection, player_id): owner_string = "" query = f'SELECT drafted, owner_id, nickname FROM ownersXplayers_view WHERE player_id={player_id} ORDER BY drafted DESC, nickname LIMIT 5' print(query) rows = get_rows(query) for row in rows: years = get_owner_years(row["owner_id"], player_id) ############## this_owner_string = row["nickname"] + " (" + str( len(years)) + " times): " for year in years: this_owner_string += str(year) + ", " this_owner_string = this_owner_string.rstrip() this_owner_string = this_owner_string.rstrip(",") owner_string += this_owner_string + "<br>\n" return owner_string
def get_teams(season): query = f'SELECT DISTINCT owner_id FROM ownersXseasons_detail WHERE season = {season} AND owner_id != 63 ORDER BY nickname ASC' rows = get_rows(query) teams = [] for row in rows: print(row) owner_id = row['owner_id'] query = f'SELECT bank, nickname, owner_id, place, points, recent, salary, season, team_name, yesterday FROM ownersXseasons_detail WHERE owner_id = {owner_id} AND season = {season}' team = get_row(query) team['place'] = make_ordinal(team['place']) team['active_players'] = get_roster(owner_id, season, "active") benched_players = get_roster(owner_id, season, "benched") if (len(benched_players)) > 0: team['has_benched_players'] = True team['benched_players'] = benched_players teams.append(team) return teams
def get_trades(season): query = f'SELECT * FROM trades_detail WHERE season = {season} AND owner_id != 63 ORDER BY stamp DESC' trades = get_rows(query) for x in range(0, len(trades)): added_id = trades[x]['added_player_id'] dropped_id = trades[x]['dropped_player_id'] query = f'SELECT fnf FROM players WHERE player_id = {added_id}' r = get_row(query) trades[x]['added_fnf'] = r['fnf'] query = f'SELECT fnf FROM players WHERE player_id = {dropped_id}' r = get_row(query) trades[x]['dropped_fnf'] = r['fnf'] d = trades[x]['stamp'].strftime('%b %-d') trades[x]['date'] = trades[x]['stamp'].strftime('%b %-d') query = f'SELECT pos FROM playersXseasons WHERE player_id = {dropped_id}' r = get_row(query) trades[x]['pos'] = r['pos'] return trades
def get_all_time_rows(connection, player_id): html_rows = "" query = f'SELECT drafted, points, pos, salary, season, team, value FROM playersXseasons WHERE player_id={player_id} ORDER BY season DESC' print(query) rows = get_rows(query) for row in rows: row["drafted_by"] = "" if row["drafted"] > 0: owners = get_owners(player_id, row["season"]) for owner in owners: row["drafted_by"] += owner["nickname"] + ", " row["drafted_by"] = row["drafted_by"].rstrip() row["drafted_by"] = row["drafted_by"].rstrip(",") ########################## html_row = "<tr><td align='center'>{season}</td><td align='center'>{pos}</td><td align='center'>{team}</td><td align='right'>${salary}</td><td align='right'>{points}</td><td align='center'>{value}</td><td align='center'>{drafted}</td><td>{drafted_by}</td></tr>" for val in row: html_row = html_row.replace("{" + val + "}", str(row[val])) html_rows += html_row + "\n" return html_rows
def generate_season_nav_page(connection, s3, env): content = "<p>Seasons</p>" # content["title"] = f'Baseball seasons' with open("html/templates/base.html") as file: base = file.read() query = 'SELECT season FROM seasons ORDER BY season ASC' print(query) rows = get_rows(query) for row in rows: content += f'<p><a href = "/seasons/{row["season"]}">{row["season"]}</a></p>\n' page = base.replace("{main}", content) page = page.replace("{title}", "Baseball seasons") page = page.replace("{page_generated}", str(datetime.datetime.now())) if env["create_local_files"]: write_to_local_disk(page, "season_nav", 0) if env["push_to_s3"]: write_to_s3(page, "season_nav", 0, s3)
def get_owners(season): query = f'SELECT owner_id, nickname, season FROM ownersXseasons_detail WHERE season = {season} ORDER BY nickname ASC' rows = get_rows(query) return rows
def get_players(season): query = f'SELECT * FROM player_x_season_detail WHERE season={season} ORDER BY lnf' rows = get_rows(query) return rows
def get_players_page_content(connection, season): content = {"season": season, "page_generated": datetime.datetime.now()} content["title"] = f'Baseball {season}: Players' query = f'SELECT * FROM player_x_season_detail WHERE season={season} ORDER BY points DESC, lnf ASC' html = "" with open("html/templates/player_in_player_table.html") as file: template = file.read() rows = get_rows(query) for row in rows: if row["value"] == -1: row["value"] = "0" if row["pos"] == "C": row["pos"] = "C2" row["salary"] = "$" + str(row["salary"]) player_id = row["player_id"] fnf = row["fnf"] row["name_with_link"] = f'<a href="/players/{player_id}" target="_blank">{fnf}</a>' this_player = template vals = [ "points", "name_with_link", "pos", "salary", "team", "value", "drafted", "picked" ] for val in vals: this_player = this_player.replace("{" + val + "}", str(row[val])) html += this_player + "\n" content["player_rows"] = html with open("html/templates/base.html") as file: base = file.read() with open("html/templates/players.html") as file: players_page = file.read() players_page = base.replace("{main}", players_page) for chunk in content: players_page = players_page.replace("{" + chunk + "}", str(content[chunk])) return players_page
def get_owners(player_id, season): query = f'SELECT nickname FROM oXr_AT_view WHERE player_id={player_id} AND drafted=1 AND season={season} ORDER BY nickname' print(query) rows = get_rows(query) return rows
def get_owner_detailed_team(connection, season, owner_id): query = f'SELECT * FROM owner_x_roster_detail WHERE owner_id = {owner_id} AND season = {season}' query += " ORDER BY o ASC, salary DESC" print(query) players = get_rows(query) return players
def get_owner_rows(season): query = f'SELECT * FROM ownersXseasons_detail WHERE season = {season} AND owner_id != 63 ORDER BY place, nickname ASC' rows = get_rows(query) for row in rows: row['place'] = make_ordinal(row['place']) return rows
def get_owner_detailed_teams(connection, season, season_is_current): teams = "" query = f'SELECT * FROM ownersXseasons_detail WHERE season = {season} ORDER BY place ASC' print(query) rows = get_rows(query) template = get_owner_template(season_is_current) for row in rows: owner_id = row["owner_id"] this_team = template row["season"] = season if row["team_name"]: row["team_name"] = " - " + row["team_name"] if row["bank"]: row["bank"] = "bank: $" + str(row["bank"]) else: row["bank"] = "" row["link"] = str(season) + "_" + str(owner_id) row["place"] = make_ordinal(row["place"]) int_fields = ["points", "recent", "yesterday"] fields = int_fields + [ "bank", "link", "nickname", "salary", "place", "season", "team_name" ] for field in fields: if field in int_fields and int(row[field]) < 0: row[field] = "N/A" this_team = this_team.replace("{" + field + "}", str(row[field])) roster = get_owner_detailed_team(connection, season, owner_id) roster_html = get_roster_html(roster, season_is_current) this_team = this_team.replace("{roster}", roster_html) teams += this_team return teams
def get_small_stats_table(column, person_type, picked_only, season): html = "" if person_type == "player": with open("html/templates/player_row_tiny.html") as file: template = file.read() query = f'SELECT fnf, player_id, {column} FROM player_x_season_detail WHERE season = {season}' if picked_only: query += ' AND picked > 0' query += f' ORDER BY {column} DESC' else: with open("html/templates/owner_row_tiny.html") as file: template = file.read() query = f'SELECT nickname, owner_id, {column} FROM ownersXseasons_detail WHERE season = {season} ORDER BY {column} DESC, nickname ASC' query += ' LIMIT 5' print(query) rows = get_rows(query) i = 0 for row in rows: i += 1 this_person = template if row[column] < 0: cat_val = "N/A" else: cat_val = row[column] vals = {"rank": i, "cat_val": cat_val} if person_type == "player": vals["fnf"] = row["fnf"] vals["player_id"] = row["player_id"] else: vals["nickname"] = row["nickname"] vals["owner_id"] = row["owner_id"] for val in vals: this_person = this_person.replace("{" + val + "}", str(vals[val])) html += this_person + "\n" return html
def get_owner_years(owner_id, player_id): query = f'SELECT season FROM ownersXrosters WHERE player_id={player_id} AND owner_id={owner_id} AND drafted = 1 ORDER BY season ASC' print(query) years = [] rows = get_rows(query) for row in rows: years.append(row["season"]) return years
def get_roster(owner_id, season, active_or_benched): if active_or_benched == "active": clause = "benched = 0" else: clause = "benched > 0" query = f'SELECT * FROM owner_x_roster_detail WHERE owner_id = {owner_id} AND season = {season} AND {clause} ORDER BY o ASC, salary DESC' players = get_rows(query) for x in range(0, len(players)): if players[x]['yesterday'] == -1: players[x]['yesterday'] = "N/A" if players[x]['recent'] == -1: players[x]['recent'] = "N/A" return players
def generate_player_pages(connection, season, s3, push_to_s3, create_local_files): query = f'SELECT * FROM players_current_view WHERE season={season} ORDER BY lnf' # query = f'SELECT player_id FROM players_current_view WHERE season={season} AND player_id=5016 ORDER BY lnf' # if player_id == 5016: # exit() print(query) rows = get_rows(query) for player in rows: generate_player_page(connection, season, s3, push_to_s3, create_local_files, player["player_id"]) exit()
def get_owner_rows_summary(connection, season): owner_rows_summary = "" with open("html/templates/owner_row_home_page.html") as file: template = file.read() query = f'SELECT * FROM ownersXseasons_detail WHERE season={season} ORDER BY place ASC, nickname ASC' print(query) rows = get_rows(query) # now generate owner rows for row in rows: this_row = template owner_id = row["owner_id"] row["link"] = str(season) + "_" + str(owner_id) if row["head_shot"]: url = row["head_shot"] row["head_shot"] = f'<img src="{url}">' fields = [ "head_shot", "link", "nickname", "place", "points", "team_name" ] for field in fields: this_row = this_row.replace("{" + field + "}", str(row[field])) owner_rows_summary += this_row return owner_rows_summary
def get_owner_dropdown(connection, season): owner_dropdown = "" template = "<a class='dropdown-item' href='#{link}'>{nickname}</a>" query = f'SELECT * FROM ownersXseasons_detail WHERE season = {season} ORDER BY nickname ASC' print(query) rows = get_rows(query) for row in rows: this_row = template link = str(season) + "_" + str(row["owner_id"]) this_row = this_row.replace("{link}", link) this_row = this_row.replace("{nickname}", row["nickname"]) owner_dropdown += this_row + "\n" return owner_dropdown
def get_trades_content(connection, season): content = { "season": season, "page_generated": datetime.datetime.now() } query = f'SELECT * FROM trades_view WHERE season={season} ORDER BY stamp DESC' print(query) rows = get_rows(query) with open("html/templates/trade_row.html") as file: template = file.read() vals = ["added_player_fnf", "date", "dropped_player_fnf", "nickname", "pos"] table_rows = "" for row in rows: trade_row = template added_player_id = row["added_player_id"] dropped_player_id = row["dropped_player_id"] query = f'SELECT * FROM players_current_view WHERE player_id = {added_player_id}' r = get_row(query) row["added_player_fnf"] = r["fnf"] row["pos"] = r["pos"] query = f'SELECT * FROM players WHERE player_id = {dropped_player_id}' r = get_row(query) row["dropped_player_fnf"] = r["fnf"] m = row["stamp"].strftime("%b") d = row["stamp"].strftime("%d") row["date"] = m + " " + d for val in vals: trade_row = trade_row.replace("{" + val + "}", row[val]) table_rows += trade_row with open("html/templates/trade_table.html") as file: template = file.read() ################################################### template = template.replace("{trades}", table_rows) with open("html/templates/base.html") as file: base = file.read() base = base.replace("{title}", f'Baseball {season}: Trades') base = base.replace("{page_generated}", str(content["page_generated"])) base = base.replace("{main}", template) base = base.replace("{season}", str(season)) return base
def get_command_line_args(): print('Number of arguments:' + str(len(sys.argv))) print('Argument List:' + str(sys.argv)) if len(sys.argv) == 1: return False if '--push_to_s3' in sys.argv: globals()['push_to_s3'] = True ######################################## if '--current' in sys.argv: generate_page('current') if '--help' in sys.argv or '-h' in sys.argv: print('available commands: ') print('--current: generate current season home page') print('--history: generate the narrative history page') print( '--players [season]: generate the players home page for a season') print('--season [season]: generate the season home page for a season') exit() if '--history' in sys.argv: generate_page('history') if '--players' in sys.argv: index = sys.argv.index('--players') season = sys.argv[index + 1] generate_page('players', season) if '--season' in sys.argv: index = sys.argv.index('--season') season = sys.argv[index + 1] print('the season is: ' + season) generate_page('season', season) if '--trades' in sys.argv: index = sys.argv.index('--trades') season = sys.argv[index + 1] print('the season is: ' + season) generate_page('trades', season) for x in range(0, len(sys.argv)): arg = sys.argv[x] if arg == "--owner": owner_id = sys.argv[x + 1] print("the owner id is: " + str(owner_id)) create_owner_page(owner_id) if arg == "--seasons": query = "SELECT * FROM seasons" rows = get_rows(query) for row in rows: season = row['season'] if season == env["current_season"]: season_is_current = True else: season_is_current = False print("generating home page for season " + str(season)) generate_season_home_page(connection, season, season_is_current, s3, env) generate_season_nav_page(connection, s3, env) exit()