def hitter_trades_post(self):
"""POST request to update the database with the trade information to create the player split."""
vm = TradesViewModel()
vm.from_dict(self.request.POST)
session = DbSessionFactory.create_session()
su__query = (
session.query(Account.id)
.filter(Account.is_super_user == 1)
.filter(Account.id == self.logged_in_user_id)
.first()
)
if su__query is None:
print("You must be an administrator to view this page")
self.redirect("/home")
TradeService.get_hitter_trade(
vm.player_id,
vm.team_id,
vm.hr,
vm.ba,
vm.ab,
vm.hits,
vm.pa,
vm.games,
vm.rbi,
)
session.close()
# redirect
self.redirect("/admin")
def get_account_info(cls, user_id):
session = DbSessionFactory.create_session()
account_info = session.query(Account).filter(
Account.id == user_id).all()
return account_info
def get_tiebreaker():
session = DbSessionFactory.create_session()
season_row = session.query(SeasonInfo).filter(
SeasonInfo.id == "1").first()
season = season_row.current_season
update_date = get_update_date()
response = requests.get(
"https://api.mysportsfeeds.com/v2.0/pull/mlb/" + str(season) +
"-regular/standings.json?team=120",
auth=HTTPBasicAuth(config.msf_api, config.msf_v2pw),
)
team_json = response.json()
team_data = team_json["teams"]
for teams in team_data:
twins_wins = teams["stats"]["standings"]["wins"]
session.query(WeeklyTeamStats).filter(
WeeklyTeamStats.team_id == 120).filter(
update_date == update_date).filter(
season == season).update(
{"tiebreaker_twin_wins": twins_wins})
session.commit()
session.close()
def find_changes(user_id):
session = DbSessionFactory.create_session()
season_row = (session.query(
SeasonInfo.current_season).filter(SeasonInfo.id == 1).first())
season = season_row.current_season
try:
for pick in (session.query(PlayerPicks.changed).filter(
PlayerPicks.user_id == user_id).filter(
PlayerPicks.season == season).filter(
PlayerPicks.changed).all()):
print(pick)
if pick[0] == 1:
pick = True
else:
pick = False
print(pick)
return pick
except TypeError:
pick = 0
return pick
session.close()
def display_player_standings(player_id, season=None):
if season is None:
season = get_seasons()
sqlstr = "SELECT DISTINCT(w.pick_id), coalesce(w.points_earned,0) as points, a.first_name, a.last_name, p.pick_type, p.rank, p.multiplier, t.name, p.changed, "
sqlstr += "c.league, d.division, ap.firstname, ap.lastname "
sqlstr += "FROM (PlayerPicks p, Account a) "
sqlstr += "LEFT JOIN WeeklyPlayerResults w on p.pick_id = w.pick_id "
sqlstr += (
"AND w.update_date = (SELECT MAX(update_date) from WeeklyPlayerResults WHERE season="
+ str(season) + ") ")
sqlstr += "LEFT JOIN DivisionInfo d on p.division_id=d.division_id "
sqlstr += "LEFT JOIN LeagueInfo c ON p.league_id= c.league_id "
sqlstr += "LEFT JOIN TeamInfo t ON p.team_id = t.team_id "
sqlstr += "LEFT JOIN ActiveMLBPlayers ap ON p.player_id = ap.player_id AND p.season = ap.season "
sqlstr += "WHERE "
sqlstr += "p.user_id = a.id "
sqlstr += "AND p.season = " + str(season) + " "
sqlstr += "AND p.user_id = '" + player_id + "'"
# print(sqlstr)
session = DbSessionFactory.create_session()
standings = session.execute(sqlstr)
dict_standings = [dict(row) for row in standings]
session.close()
return dict_standings
def display_weekly_standings(season=None):
# return list that contains player standings for most recent week in results table
if season is None:
season = get_seasons()
sqlstr = "SELECT SUM(w.points_earned) as total_points, a.first_name, a.last_name, a.id from WeeklyPlayerResults w, PlayerPicks p, Account a "
sqlstr += "WHERE w.pick_id = p.pick_id AND p.user_id = a.id "
sqlstr += "AND w.season = " + str(season) + " "
sqlstr += "AND p.season=w.season "
sqlstr += (
"AND w.update_date = (SELECT MAX(update_date) from WeeklyPlayerResults WHERE season = "
+ str(season) + ") ")
sqlstr += "GROUP BY p.user_id "
sqlstr += "ORDER BY total_points DESC"
#print(sqlstr)
session = DbSessionFactory.create_session()
standings = session.execute(sqlstr)
dict_standings = [dict(row) for row in standings]
session.close()
return dict_standings
def display_picks(user_id, season):
session = DbSessionFactory.create_session()
picks_query = (session.query(
PlayerPicks.pick_type,
LeagueInfo.league,
DivisionInfo.division,
TeamInfo.name,
TeamInfo.team_id,
PlayerPicks.rank,
DivisionInfo.division_id,
LeagueInfo.league_id,
ActiveMLBPlayers.firstname,
ActiveMLBPlayers.lastname,
PlayerPicks.multiplier,
PlayerPicks.twins_wins,
PlayerPicks.changed,
PlayerPicks.player_id,
).outerjoin(LeagueInfo).outerjoin(TeamInfo).outerjoin(
DivisionInfo,
and_(PlayerPicks.division_id ==
DivisionInfo.division_id)).outerjoin(
ActiveMLBPlayers,
and_(
PlayerPicks.player_id == ActiveMLBPlayers.player_id,
PlayerPicks.season == ActiveMLBPlayers.season,
),
).filter(PlayerPicks.user_id == user_id,
PlayerPicks.season == season))
session.close()
return picks_query
def get_all_accounts():
session = DbSessionFactory.create_session()
account_list = session.query(Account).all()
session.close()
return account_list
def unique_player_picks(cls, pick_type, league):
session = DbSessionFactory.create_session()
season_row = session.query(SeasonInfo).filter(
SeasonInfo.id == "1").first()
current_season = season_row.current_season
all_star_game_query = session.query(
SeasonInfo.all_star_game_date).first()
all_star_game_date_string = str(all_star_game_query[0])
all_star_game_datetime = pendulum.parse(all_star_game_date_string,
tz="America/New_York")
converted_date = pendulum.instance(all_star_game_datetime)
all_star_game = converted_date.at(19)
now_time = TimeService.get_time()
# Calculate Unique Picks at season start
if now_time < all_star_game:
# Unique picks are 2 players - trying ct = 2 (instead of ct=1
txtstr = "UPDATE PlayerPicks SET multiplier=2 WHERE player_id IN "
txtstr += "(SELECT player_id FROM (select DISTINCT(player_id), COUNT(player_id) AS ct FROM PlayerPicks WHERE "
midstr = " GROUP BY player_id)PlayerPicks WHERE ct<3) "
condstr = (" pick_type=" + str(pick_type) + " AND season=" +
str(current_season))
condstr += " AND league_id=" + str(league)
txtstr += condstr + midstr + "AND " + condstr
# print(txtstr)
session.execute(txtstr)
session.commit()
else:
txtstr = (
"UPDATE PlayerPicks SET multiplier=2 WHERE changed=1 and player_id IN "
)
txtstr += "(SELECT player_id FROM (select DISTINCT(player_id), COUNT(player_id) AS ct FROM PlayerPicks WHERE "
midstr = " GROUP BY player_id)PlayerPicks WHERE ct<3) "
condstr = (" pick_type=" + str(pick_type) + " AND season=" +
str(current_season))
condstr += " AND league_id=" + str(league)
txtstr += condstr + midstr + "AND " + condstr
# print(txtstr)
session.execute(txtstr)
session.commit()
session.close()
def admin_check():
session = DbSessionFactory.create_session()
season_start_query = session.query(SeasonInfo.season_start_date).first()
session.close()
return season_start_query
def get_update_players_date(season):
session = DbSessionFactory.create_session()
qry = session.query(
func.max(WeeklyMLBPlayerStats.update_date).label("max"))
res = qry.one()
latest_date = res.max
session.close()
return latest_date
def get_seasons():
session = DbSessionFactory.create_session()
season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
current_season = season_row.current_season
session.close()
return current_season
def trade_adjustments():
season = get_seasons()
session = DbSessionFactory.create_session()
# ASSUMES players are either pitcher or not
# update all except batting average
sqlstr = "UPDATE WeeklyMLBPlayerStats w "
sqlstr += "INNER JOIN InterleagueTrades i "
sqlstr += "ON i.player_id = w.player_id AND i.season=w.season "
sqlstr += "SET w.home_runs = (w.home_runs - i.home_runs), "
sqlstr += "w.at_bats = (w.at_bats - i.at_bats), "
sqlstr += "w.hits = (w.hits - i.hits), "
sqlstr += "w.plate_appearances = (w.RBI - i.RBI) "
sqlstr += "WHERE w.batting_average IS NOT NULL "
sqlstr += "AND w.season=" + str(season)
session.execute(sqlstr)
session.commit()
# print(sqlstr)
# update batting average
sqlstr = "UPDATE WeeklyMLBPlayerStats w "
sqlstr += "INNER JOIN InterleagueTrades i "
sqlstr += "ON i.player_id = w.player_id AND i.season=w.season "
sqlstr += "SET w.batting_average = (w.hits / w.at_bats) "
sqlstr += "WHERE w.batting_average IS NOT NULL "
sqlstr += "AND w.season=" + str(season)
session.execute(sqlstr)
session.commit()
# print(sqlstr)
# pitchers
sqlstr = "UPDATE WeeklyMLBPlayerStats w "
sqlstr += "INNER JOIN InterleagueTrades i "
sqlstr += "ON i.player_id = w.player_id AND i.season=w.season "
sqlstr += "SET w.pitcher_wins = (w.pitcher_wins-i.pitcher_wins), "
sqlstr += "w.earned_runs = (w.earned_runs-i.earned_runs), "
sqlstr += "w.innings_pitched=(w.innings_pitched-i.innings_pitched) "
sqlstr += "WHERE w.ERA IS NOT NULL "
sqlstr += "AND w.season=" + str(season)
session.execute(sqlstr)
session.commit()
# print(sqlstr)
# pitchers update ERA
sqlstr = "UPDATE WeeklyMLBPlayerStats w "
sqlstr += "INNER JOIN InterleagueTrades i "
sqlstr += "ON i.player_id = w.player_id AND i.season=w.season "
sqlstr += "SET "
sqlstr += "w.ERA = ROUND(((w.earned_runs/w.innings_pitched)*9),2) "
sqlstr += "WHERE w.ERA IS NOT NULL "
sqlstr += "AND w.season=" + str(season)
# print(sqlstr)
session.execute(sqlstr)
session.commit()
session.close()
def seasons_played(cls, user_id):
session = DbSessionFactory.create_session()
seasons_played = (session.query(PlayerPicks.season).distinct(
PlayerPicks.season).filter(Account.id == user_id))
session.close()
return seasons_played
def get_seasons():
"""Get the current active season from the database"""
session = DbSessionFactory.create_session()
season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
current_season = season_row.current_season
session.close()
return current_season
def league_list():
session = DbSessionFactory.create_session()
leagues = session.query(LeagueInfo).all()
session.close()
return leagues
def split_player_stats():
session = DbSessionFactory.create_session()
season_row = session.query(SeasonInfo).filter(
SeasonInfo.id == "1").first()
season = season_row.current_season
session.close()
def division_list():
session = DbSessionFactory.create_session()
divisions = session.query(DivisionInfo).all()
session.close()
return divisions
def all_star_game_date():
"""Get the time of the season opener's game"""
session = DbSessionFactory.create_session()
all_star_game_date = session.query(SeasonInfo.all_star_game_date).first()
session.close()
return all_star_game_date
def reset_paid():
session = DbSessionFactory.create_session()
for player in session.query(Account):
session.query(Account.paid).update({"paid": 0})
session.commit()
session.close()
def update_paid(cls, user_id: str):
session = DbSessionFactory.create_session()
for player in session.query(Account.id).filter(Account.id == user_id):
session.query(Account.id).filter(Account.id == user_id).update(
{"paid": 1})
session.commit()
session.close()
def find_reset_code(cls, code):
if not code or not code.strip():
return None
session = DbSessionFactory.create_session()
reset = session.query(PasswordReset).filter(
PasswordReset.id == code).first()
return reset
def team_list():
session = DbSessionFactory.create_session()
teams = (session.query(TeamInfo).filter(TeamInfo.team_id).order_by(
TeamInfo.name).all())
session.close()
return teams
def update_mlbplayers():
session = DbSessionFactory.create_session()
season_row = session.query(SeasonInfo).filter(
SeasonInfo.id == "1").first()
season = season_row.current_season
response = requests.get(
"https://api.mysportsfeeds.com/v2.0/pull/mlb/"
"players.json?season=" + str(season) +
"&rosterstatus=assigned-to-roster",
auth=HTTPBasicAuth(config.msf_api, config.msf_v2pw),
)
player_info = response.json()
player_list = player_info["players"]
player_tuple = (session.query(ActiveMLBPlayers.player_id).filter(
ActiveMLBPlayers.season == season).all())
current_players = [sql_players for sql_players, in player_tuple]
print(current_players)
for players in player_list:
try:
firstname = players["player"]["firstName"]
lastname = players["player"]["lastName"]
player_id = players["player"]["id"]
team_id = players["player"]["currentTeam"]["id"]
position = players["player"]["primaryPosition"]
except KeyError:
continue
if int(player_id) not in current_players:
updated_players = ActiveMLBPlayers(
firstname=firstname,
lastname=lastname,
player_id=player_id,
team_id=team_id,
position=position,
season=season,
)
session.add(updated_players)
session.commit()
else:
pass
session.close()
def all_seasons_played():
"""This method is used to get a list of all seasons played and display on the Standings index page
for players to click through to see the season standings / points scored by player"""
session = DbSessionFactory.create_session()
seasons_played = (session.query(PlayerPicks.season).distinct(
PlayerPicks.season).order_by(PlayerPicks.season.desc()))
session.close()
return seasons_played
def get_account_date(cls, user_id):
session = DbSessionFactory.create_session()
account_created = session.query(Account.created).first()
account_string = str(account_created[0])
account_date_split = account_string.split()
account_date = account_date_split[0]
session.close()
return account_date
def last_game_date():
session = DbSessionFactory.create_session()
last_game_date = session.query(SeasonInfo.season_end_date).first()
last_game_info = str(last_game_date[0])
last_game = pendulum.parse(last_game_info, tz=timezone)
final_date = last_game.add(days=3)
session.close()
return final_date
def find_account_by_id(cls, user_id):
if not user_id:
return None
user_id = user_id.strip()
session = DbSessionFactory.create_session()
account = session.query(Account).filter(Account.id == user_id).first()
return account
def get_hitter_stats():
"""Get stats for hitters (home runs, batting average and ERA) from MySportsFeeds and insert into the database"""
session = DbSessionFactory.create_session()
season_row = session.query(SeasonInfo).filter(
SeasonInfo.id == "1").first()
season = season_row.current_season
response = requests.get(
"https://api.mysportsfeeds.com/v2.0/pull/mlb/" + str(season) +
"-regular/player_stats_totals.json?position=C,1B,2B,SS,3B,OF,RF,CF,LF,DH",
auth=HTTPBasicAuth(config.msf_api, config.msf_v2pw),
)
player_json = response.json()
player_data = player_json["playerStatsTotals"]
for players in player_data:
try:
player_id = players["player"]["id"]
home_runs = players["stats"]["batting"]["homeruns"]
RBI = players["stats"]["batting"]["runsBattedIn"]
batting_average = players["stats"]["batting"]["battingAvg"]
at_bats = players["stats"]["batting"]["atBats"]
hits = players["stats"]["batting"]["hits"]
plate_appearances = players["stats"]["batting"][
"plateAppearances"]
player_games_played = players["stats"]["gamesPlayed"]
except KeyError:
continue
update_date = get_update_date()
weekly_player_stats = WeeklyMLBPlayerStats(
player_id=player_id,
season=season,
home_runs=home_runs,
RBI=RBI,
batting_average=batting_average,
at_bats=at_bats,
hits=hits,
plate_appearances=plate_appearances,
player_games_played=player_games_played,
update_date=update_date,
)
session.add(weekly_player_stats)
session.commit()
session.close()
def find_account_by_email(cls, email):
if not email or not email.strip():
return None
email = email.lower().strip()
session = DbSessionFactory.create_session()
account = session.query(Account).filter(Account.email == email).first()
return account