def combined_stats(team_id=0):
team = Team.query.filter_by(id=team_id).first()
if not team:
return redirect(url_for('all'))
if g.user.is_on_team(team_id):
page = { 'top' : 'my_teams', 'sub' : 'player_stats' }
else:
page = { 'top' : 'team', 'sub' : 'player_stats' }
game_id = int(request.values.get('game', 1))
games = db.session.query(Game).all()
game = [ ga for ga in games if ga.id == game_id ]
if not game:
flash("That game doesn't exit.", "info")
return redirect(url_for('stats', team_id=team_id))
else:
game = game[0]
competitions = db.session.query(Competition.id, Competition.name).\
filter_by(game_id=game_id).all()
competition_ids = [ c.id for c in competitions ]
gametype_id = int(request.values.get('gametype', 0))
map_id = int(request.values.get('map', 0))
competition_id = int(request.values.get('competition', 0))
competitions = db.session.query(Competition.id,
Competition.abbr,
Competition.name).\
filter_by(game_id=game_id).order_by(Competition.abbr).all()
gametypes = db.session.query(GameType.id, GameType.name).\
order_by(GameType.name).\
all()
maps = db.session.query(Map.id, Map.name).order_by(Map.name).all()
competition = next((v for v in competitions if v.id == competition_id), None)
gametype = next((v for v in gametypes if v.id == gametype_id), None)
mapp = next((v for v in maps if v.id == map_id), None)
if not gametype and not mapp and not competition:
return redirect(url_for('stats', team_id=team_id, game=game_id))
combine_form = PlayerStatsCombineForm()
combine_form.game.choices = [ (ga.id, ga.name) for ga in games ]
combine_form.competition.choices = [ (0, "Competition"), (0, "Any") ]
combine_form.gametype.choices = [ (0, "Game Type"), (0, "Any") ]
combine_form.map.choices = [ (0, "Map"), (0, "Any") ]
combine_form.competition.choices.extend([ (c.id, c.abbr) for c in\
competitions ])
combine_form.gametype.choices.extend([ (gt.id, gt.name) for gt in\
gametypes ])
combine_form.map.choices.extend([ (m.id, m.name) for m in\
maps ])
combine_form.game.data = game_id
combine_form.competition.data = competition_id
combine_form.gametype.data = gametype_id
combine_form.map.data = map_id
pcount_subquery = \
db.session.query(CompletedMatchPlayer.user_id,
CompletedMatchRound.gametype_id,
CompletedMatchRound.map_id,
CompletedMatch.competition_id).\
join((CompletedMatch,
CompletedMatchPlayer.cmatch_id == CompletedMatch.id)).\
join((CompletedMatchRound,\
db.and_(\
CompletedMatchRound.cmatch_id == CompletedMatch.id,\
CompletedMatchRound.round_id ==\
CompletedMatchPlayer.round_id,\
))).\
filter(CompletedMatch.team_id == team_id)
if gametype_id:
pcount_subquery = pcount_subquery.filter_by(gametype_id=gametype_id)
if map_id:
pcount_subquery = pcount_subquery.filter_by(map_id=map_id)
if competition_id:
pcount_subquery = pcount_subquery.\
filter(CompletedMatch.competition_id == competition_id)
else:
pcount_subquery = pcount_subquery.\
filter(CompletedMatch.competition_id.in_(competition_ids))
pcount_subquery = pcount_subquery.\
group_by(CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.user_id).\
having(db.func.sum(CompletedMatchPlayer.kills) >=\
db.func.sum(CompletedMatchPlayer.deaths)).\
with_entities(CompletedMatchPlayer.user_id).\
subquery()
positive_counts = db.engine.execute( \
db.select(columns=['user_id', db.func.count()],\
from_obj=pcount_subquery).\
group_by('user_id') )
pcounts = dict()
for p in positive_counts:
pcounts[ p[0] ] = p[1]
record_subquery = \
db.session.query(CompletedMatchPlayer.user_id,
CompletedMatch.competition_id,
CompletedMatchPlayer.cmatch_id).\
join((CompletedMatchRound,\
db.and_(\
CompletedMatchRound.cmatch_id ==\
CompletedMatchPlayer.cmatch_id,\
CompletedMatchRound.round_id ==\
CompletedMatchPlayer.round_id,\
)))
if gametype_id:
record_subquery = record_subquery.filter_by(gametype_id=gametype_id)
if map_id:
record_subquery = record_subquery.filter_by(map_id=map_id)
if competition_id:
record_subquery = record_subquery.\
filter(CompletedMatch.competition_id == competition_id)
record_subquery = record_subquery.\
filter(CompletedMatch.id == CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.user_id).\
with_entities(CompletedMatchPlayer.user_id,
CompletedMatchPlayer.cmatch_id).\
subquery()
records = db.session.query(\
record_subquery.c.user_id,
db.func.sum(\
db.cast(CompletedMatch.wins >\
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.losses >\
CompletedMatch.wins, db.INT))\
).\
select_from(CompletedMatch).\
join((record_subquery,\
CompletedMatch.id ==\
record_subquery.c.cmatch_id\
)).\
filter(CompletedMatch.team_id == team_id).\
filter(CompletedMatch.competition_id.\
in_(competition_ids)).\
group_by(record_subquery.c.user_id)
stats_pm = dict()
for s in records:
stats_pm[ s[0] ] = (s[1], s[2])
stats_res = db.session.query(CompletedMatchPlayer.user_id,
db.func.sum(CompletedMatchPlayer.kills),
db.func.sum(CompletedMatchPlayer.deaths),
db.func.sum(CompletedMatchPlayer.off_objs),
db.func.sum(CompletedMatchPlayer.def_objs),
db.func.sum(CompletedMatchPlayer.score),
db.func.count(db.func.distinct(CompletedMatch.id))).\
join((CompletedMatch,
CompletedMatchPlayer.cmatch_id == CompletedMatch.id)).\
join((CompletedMatchRound,\
db.and_(\
CompletedMatchRound.cmatch_id == CompletedMatch.id,\
CompletedMatchRound.round_id ==\
CompletedMatchPlayer.round_id,\
))).\
filter(CompletedMatch.team_id == team_id)
if gametype_id:
stats_res = stats_res.filter_by(gametype_id=gametype_id)
if map_id:
stats_res = stats_res.filter_by(map_id=map_id)
if competition_id:
stats_res = stats_res.\
filter(CompletedMatch.competition_id == competition_id)
else:
stats_res = stats_res.\
filter(CompletedMatch.competition_id.in_(competition_ids))
stats_res = stats_res.\
group_by(CompletedMatchPlayer.user_id).\
all()
stats = []
for s in stats_res:
w = 0
l = 0
pos_kdr = 0
if s[0] in stats_pm:
w, l = stats_pm[ s[0] ]
if s[0] in pcounts:
pos_kdr = pcounts[ s[0] ]
if (w+l) == 0:
ts = 0
else:
ts = float(s[3] + s[4]) / float(w+l)
stats_item = { 'user_id' : s[0],
'kills' : s[1],
'deaths' : s[2],
'offobjs' : s[3],
'defobjs' : s[4],
'score' : s[5],
'team_score' : ts,
'wins' : w,
'losses' : l,
'pos_kdr' : pos_kdr
}
stats.append(stats_item)
# team record
cmatches = db.session.query(\
CompletedMatchRound.cmatch_id,
CompletedMatch.wins,
CompletedMatch.losses,\
CompletedMatch.final_result_method).\
join(CompletedMatch).\
filter_by(team_id=team_id).\
filter(CompletedMatch.final_result_method !=\
CompletedMatch.FinalResultByForfeit)
if gametype_id:
cmatches = cmatches.\
filter(CompletedMatchRound.gametype_id == gametype_id)
if map_id:
cmatches = cmatches.\
filter(CompletedMatchRound.map_id == map_id)
if competition_id:
cmatches = cmatches.\
filter(CompletedMatch.competition_id == competition_id)
else:
cmatches = cmatches.\
filter(CompletedMatch.competition_id.in_(competition_ids))
cmatches = cmatches.all()
total_wins = 0
total_losses = 0
total_draws = 0
seen_matches = set()
for c in cmatches:
cmatch_id = c[0]
if cmatch_id in seen_matches:
continue
seen_matches.add(cmatch_id)
if c.wins > c.losses:
total_wins += 1
elif c.wins < c.losses:
total_losses += 1
else:
total_draws += 1
phash = dict()
for p in team.players:
phash[p.user_id] = p.user.name
return rt('teams/stats.html',
page=page,
game=game,
combined=True,
total_stats=stats,
stats=[],
phash=phash,
gametype=gametype,
map=mapp,
combine_form=combine_form,
competition=competition,
total_wins=total_wins,
total_losses=total_losses,
total_draws=total_draws,
team=team)
def stats(team_id=0):
team = Team.query.filter_by(id=team_id).first()
if not team:
return redirect(url_for('all'))
if g.user.is_on_team(team_id):
page = { 'top' : 'my_teams', 'sub' : 'player_stats' }
else:
page = { 'top' : 'team', 'sub' : 'player_stats' }
# only look at one game at a time. but the tables only have competition
# id, so get the competition ids from the game id.
game_id = int(request.values.get('game', 1))
games = db.session.query(Game).all()
game = [ ga for ga in games if ga.id == game_id ]
if not game:
flash("That game doesn't exit.", "info")
return redirect(url_for('stats', team_id=team_id))
else:
game = game[0]
competitions = db.session.query(Competition.id,
Competition.abbr,
Competition.name).\
filter_by(game_id=game_id).order_by(Competition.abbr).all()
gametypes = db.session.query(GameType.id, GameType.name).\
filter_by(game_id=game_id).\
order_by(GameType.name).\
all()
maps = db.session.query(Map.id, Map.name).\
filter_by(game_id=game_id).\
order_by(Map.name).all()
competition_ids = [ c.id for c in competitions ]
combine_form = PlayerStatsCombineForm()
combine_form.game.choices = [ (ga.id, ga.name) for ga in games ]
combine_form.competition.choices = [ (0, "Competition"), (0, "Any") ]
combine_form.gametype.choices = [ (0, "Game Type"), (0, "Any") ]
combine_form.map.choices = [ (0, "Map"), (0, "Any") ]
combine_form.competition.choices.extend([ (c.id, c.abbr) for c in\
competitions ])
combine_form.gametype.choices.extend([ (gt.id, gt.name) for gt in\
gametypes ])
combine_form.map.choices.extend([ (m.id, m.name) for m in\
maps ])
combine_form.game.data = game_id
grouper_id = grouper_id_to_int(request.values.get('grouper'))
if grouper_id == StatsGrouperGametype:
grouper = CompletedMatchRound.gametype_id
ghash = dict()
for gt in gametypes:
ghash[gt.id] = gt.name
elif grouper_id == StatsGrouperMap:
grouper = CompletedMatchRound.map_id
ghash = dict()
for m in maps:
ghash[m.id] = m.name
elif grouper_id == StatsGrouperCompetition:
grouper = CompletedMatch.competition_id
ghash = dict()
for c in competitions:
ghash[c.id] = c.name
else:
return redirect(url_for('stats', team_id=team_id))
##########################################################
# fetch totals
pcount_subquery = \
db.session.query(CompletedMatchPlayer.user_id).\
join((CompletedMatch,
CompletedMatchPlayer.cmatch_id == CompletedMatch.id)).\
filter(CompletedMatch.team_id == team_id).\
filter(CompletedMatch.competition_id.in_(competition_ids)).\
group_by(CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.user_id).\
having(db.func.sum(CompletedMatchPlayer.kills) >=\
db.func.sum(CompletedMatchPlayer.deaths)).\
with_entities(CompletedMatchPlayer.user_id).\
subquery()
positive_counts = db.engine.execute( \
db.select(columns=['user_id', db.func.count()],\
from_obj=pcount_subquery).\
group_by('user_id') )
pcounts = dict()
for p in positive_counts:
pcounts[ p[0] ] = p[1]
record_subquery = \
db.session.query(CompletedMatchPlayer.user_id,
CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.user_id).\
with_entities(CompletedMatchPlayer.user_id,\
CompletedMatchPlayer.cmatch_id).\
subquery()
records = db.session.query(\
record_subquery.c.user_id,
db.func.sum(\
db.cast(CompletedMatch.wins >\
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.losses >\
CompletedMatch.wins, db.INT))\
).\
select_from(CompletedMatch).\
join((record_subquery,\
CompletedMatch.id ==\
record_subquery.c.cmatch_id\
)).\
filter(CompletedMatch.team_id == team_id).\
filter(CompletedMatch.competition_id.in_(competition_ids)).\
group_by(record_subquery.c.user_id)
stats_pm = dict()
for s in records:
stats_pm[ s[0] ] = (s[1], s[2])
stats_res = db.session.query(CompletedMatchPlayer.user_id,
db.func.sum(CompletedMatchPlayer.kills),
db.func.sum(CompletedMatchPlayer.deaths),
db.func.sum(CompletedMatchPlayer.off_objs),
db.func.sum(CompletedMatchPlayer.def_objs),
db.func.sum(CompletedMatchPlayer.score),
db.func.count(db.func.distinct(CompletedMatch.id))).\
join((CompletedMatch,
CompletedMatchPlayer.cmatch_id == CompletedMatch.id)).\
filter(CompletedMatch.team_id == team_id).\
filter(CompletedMatch.competition_id.in_(competition_ids)).\
group_by(CompletedMatchPlayer.user_id).\
all()
total_stats = []
for s in stats_res:
w = 0
l = 0
pos_kdr = 0
if s[0] in stats_pm:
w, l = stats_pm[ s[0] ]
if s[0] in pcounts:
pos_kdr = pcounts[ s[0] ]
if (w+l) == 0:
ts = 0
else:
ts = float(s[3] + s[4]) / float(w+l)
stats_item = { 'user_id' : s[0],
'kills' : s[1],
'deaths' : s[2],
'offobjs' : s[3],
'defobjs' : s[4],
'score' : s[5],
'team_score' : ts,
'wins' : w,
'losses' : l,
'pos_kdr' : pos_kdr
}
total_stats.append(stats_item)
##########################################################
pcount_subquery = \
db.session.query(CompletedMatchPlayer.user_id,
grouper).\
join((CompletedMatch,
CompletedMatchPlayer.cmatch_id == CompletedMatch.id)).\
join((CompletedMatchRound,\
db.and_(\
CompletedMatchRound.cmatch_id == CompletedMatch.id,\
CompletedMatchRound.round_id ==\
CompletedMatchPlayer.round_id,\
))).\
filter(CompletedMatch.team_id == team_id).\
filter(CompletedMatch.competition_id.in_(competition_ids)).\
group_by(grouper).\
group_by(CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.user_id).\
having(db.func.sum(CompletedMatchPlayer.kills) >=\
db.func.sum(CompletedMatchPlayer.deaths)).\
with_entities(CompletedMatchPlayer.user_id,\
grouper).\
subquery()
positive_counts = db.engine.execute( \
db.select(columns=['user_id', grouper.key, db.func.count()],\
from_obj=pcount_subquery).\
group_by(grouper.key, 'user_id') )
pcounts = dict()
for p in positive_counts:
pcounts[ (p[0], p[1]) ] = p[2]
record_subquery = \
db.session.query(CompletedMatchPlayer.user_id,
grouper,
CompletedMatchPlayer.cmatch_id).\
join((CompletedMatchRound,\
db.and_(\
CompletedMatchRound.cmatch_id ==\
CompletedMatchPlayer.cmatch_id,\
CompletedMatchRound.round_id ==\
CompletedMatchPlayer.round_id,\
)))
if grouper.key == 'competition_id':
record_subquery = record_subquery.\
filter(CompletedMatch.id == CompletedMatchPlayer.cmatch_id)
record_subquery = record_subquery.\
group_by(grouper).\
group_by(CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.user_id).\
with_entities(CompletedMatchPlayer.user_id,
grouper,
CompletedMatchPlayer.cmatch_id).\
subquery()
records = db.session.query(\
record_subquery.c.user_id,
record_subquery.c.get(grouper.key),
db.func.sum(\
db.cast(CompletedMatch.wins >\
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.losses >\
CompletedMatch.wins, db.INT))\
).\
select_from(CompletedMatch).\
join((record_subquery,\
CompletedMatch.id ==\
record_subquery.c.cmatch_id\
)).\
filter(CompletedMatch.team_id == team_id).\
filter(CompletedMatch.competition_id.\
in_(competition_ids)).\
group_by(record_subquery.c.get(grouper.key)).\
group_by(record_subquery.c.user_id)
stats_pm = dict()
for s in records:
stats_pm[ (s[0], s[1]) ] = (s[2], s[3])
stats_res = db.session.query(CompletedMatchPlayer.user_id,
grouper,
db.func.sum(CompletedMatchPlayer.kills),
db.func.sum(CompletedMatchPlayer.deaths),
db.func.sum(CompletedMatchPlayer.off_objs),
db.func.sum(CompletedMatchPlayer.def_objs),
db.func.sum(CompletedMatchPlayer.score),
db.func.count(db.func.distinct(CompletedMatch.id))).\
join((CompletedMatch,
CompletedMatchPlayer.cmatch_id == CompletedMatch.id)).\
join((CompletedMatchRound,\
db.and_(\
CompletedMatchRound.cmatch_id == CompletedMatch.id,\
CompletedMatchRound.round_id ==\
CompletedMatchPlayer.round_id,\
))).\
filter(CompletedMatch.team_id == team_id).\
filter(CompletedMatch.competition_id.in_(competition_ids)).\
group_by(grouper).\
group_by(CompletedMatchPlayer.user_id).\
all()
stats = []
for s in stats_res:
w = 0
l = 0
pos_kdr = 0
if (s[0], s[1]) in stats_pm:
w, l = stats_pm[ (s[0], s[1]) ]
if (s[0], s[1]) in pcounts:
pos_kdr = pcounts[ (s[0], s[1]) ]
if (w+l) == 0:
ts = 0
else:
ts = float(s[4] + s[5]) / float(w+l)
stats_item = { 'user_id' : s[0],
'grouper' : s[1],
'kills' : s[2],
'deaths' : s[3],
'offobjs' : s[4],
'defobjs' : s[5],
'score' : s[6],
'team_score' : ts,
'wins' : w,
'losses' : l,
'pos_kdr' : pos_kdr
}
stats.append(stats_item)
if grouper.key == 'competition_id':
cmatches = db.session.query(grouper,
CompletedMatch.id,
CompletedMatch.wins,
CompletedMatch.losses).\
filter_by(team_id=team_id).\
filter(CompletedMatch.competition_id.in_(competition_ids)).\
filter(CompletedMatch.final_result_method !=\
CompletedMatch.FinalResultByForfeit).\
all()
else:
cmatches = db.session.query(grouper,
CompletedMatchRound.cmatch_id,
CompletedMatch.wins,
CompletedMatch.losses).\
join(CompletedMatch).\
filter_by(team_id=team_id).\
filter(CompletedMatch.competition_id.in_(competition_ids)).\
filter(CompletedMatch.final_result_method !=\
CompletedMatch.FinalResultByForfeit).\
all()
total_wins = 0
total_losses = 0
total_draws = 0
seen_matches = set()
seen_match_grouper = set()
wins = collections.defaultdict(int)
losses = collections.defaultdict(int)
draws = collections.defaultdict(int)
for c in cmatches:
val = c[0]
cmatch_id = c[1]
if (cmatch_id, val) in seen_match_grouper:
continue
seen_match_grouper.add((cmatch_id, val))
wins[val] += 0
if c.wins > c.losses:
wins[val] += 1
elif c.wins < c.losses:
losses[val] += 1
else:
draws[val] += 1
if cmatch_id not in seen_matches:
seen_matches.add(cmatch_id)
if c.wins > c.losses:
total_wins += 1
elif c.wins < c.losses:
total_losses += 1
else:
total_draws += 1
phash = dict()
for p in team.players:
phash[p.user_id] = p.user.name
return rt('teams/stats.html',
page=page,
game=game,
combined=False,
stats=stats,
pcounts=pcounts,
ghash=ghash,
phash=phash,
combine_form=combine_form,
grouper=grouper.key[:-3],
wins=wins, losses=losses, draws=draws,
total_stats=total_stats,
total_wins=total_wins, total_losses=total_losses,
total_draws=total_draws,
team=team)
def historical_records(self):
opponent_rec = db.session.query(\
db.func.sum(\
db.cast(CompletedMatch.wins >\
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins < \
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins == \
CompletedMatch.losses, db.INT))\
).\
filter(CompletedMatch.team_id == self.team_id).\
filter(CompletedMatch.opponent_id == self.opponent_id).\
first()
competition_rec = db.session.query(\
db.func.sum(\
db.cast(CompletedMatch.wins >\
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins < \
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins == \
CompletedMatch.losses, db.INT))\
).\
filter(CompletedMatch.team_id == self.team_id).\
filter(CompletedMatch.competition_id == self.competition_id).\
first()
server_rec = db.session.query(\
db.func.sum(\
db.cast(CompletedMatch.wins >\
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins < \
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins == \
CompletedMatch.losses, db.INT))\
).\
filter(CompletedMatch.team_id == self.team_id).\
filter(CompletedMatch.server_id == self.server_id).\
first()
opp_comp_rec = db.session.query(\
db.func.sum(\
db.cast(CompletedMatch.wins >\
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins < \
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins == \
CompletedMatch.losses, db.INT))\
).\
filter(CompletedMatch.team_id == self.team_id).\
filter(CompletedMatch.opponent_id == self.opponent_id).\
filter(CompletedMatch.competition_id == self.competition_id).\
first()
def zero(rec):
if rec == (None, None, None):
return (0, 0, 0)
return rec
opponent_rec = zero(opponent_rec)
competition_rec = zero(competition_rec)
server_rec = zero(server_rec)
opp_comp_rec = zero(opp_comp_rec)
return (opponent_rec, competition_rec, server_rec, opp_comp_rec)
def combined_stats(team_id=0):
team = Team.query.filter_by(id=team_id).first()
if not team:
return redirect(url_for('all'))
if g.user.is_on_team(team_id):
page = {'top': 'my_teams', 'sub': 'player_stats'}
else:
page = {'top': 'team', 'sub': 'player_stats'}
game_id = int(request.values.get('game', 1))
games = db.session.query(Game).all()
game = [ga for ga in games if ga.id == game_id]
if not game:
flash("That game doesn't exit.", "info")
return redirect(url_for('stats', team_id=team_id))
else:
game = game[0]
competitions = db.session.query(Competition.id, Competition.name).\
filter_by(game_id=game_id).all()
competition_ids = [c.id for c in competitions]
gametype_id = int(request.values.get('gametype', 0))
map_id = int(request.values.get('map', 0))
competition_id = int(request.values.get('competition', 0))
competitions = db.session.query(Competition.id,
Competition.abbr,
Competition.name).\
filter_by(game_id=game_id).order_by(Competition.abbr).all()
gametypes = db.session.query(GameType.id, GameType.name).\
order_by(GameType.name).\
all()
maps = db.session.query(Map.id, Map.name).order_by(Map.name).all()
competition = next((v for v in competitions if v.id == competition_id),
None)
gametype = next((v for v in gametypes if v.id == gametype_id), None)
mapp = next((v for v in maps if v.id == map_id), None)
if not gametype and not mapp and not competition:
return redirect(url_for('stats', team_id=team_id, game=game_id))
combine_form = PlayerStatsCombineForm()
combine_form.game.choices = [(ga.id, ga.name) for ga in games]
combine_form.competition.choices = [(0, "Competition"), (0, "Any")]
combine_form.gametype.choices = [(0, "Game Type"), (0, "Any")]
combine_form.map.choices = [(0, "Map"), (0, "Any")]
combine_form.competition.choices.extend([ (c.id, c.abbr) for c in\
competitions ])
combine_form.gametype.choices.extend([ (gt.id, gt.name) for gt in\
gametypes ])
combine_form.map.choices.extend([ (m.id, m.name) for m in\
maps ])
combine_form.game.data = game_id
combine_form.competition.data = competition_id
combine_form.gametype.data = gametype_id
combine_form.map.data = map_id
pcount_subquery = \
db.session.query(CompletedMatchPlayer.user_id,
CompletedMatchRound.gametype_id,
CompletedMatchRound.map_id,
CompletedMatch.competition_id).\
join((CompletedMatch,
CompletedMatchPlayer.cmatch_id == CompletedMatch.id)).\
join((CompletedMatchRound,\
db.and_(\
CompletedMatchRound.cmatch_id == CompletedMatch.id,\
CompletedMatchRound.round_id ==\
CompletedMatchPlayer.round_id,\
))).\
filter(CompletedMatch.team_id == team_id)
if gametype_id:
pcount_subquery = pcount_subquery.filter_by(gametype_id=gametype_id)
if map_id:
pcount_subquery = pcount_subquery.filter_by(map_id=map_id)
if competition_id:
pcount_subquery = pcount_subquery.\
filter(CompletedMatch.competition_id == competition_id)
else:
pcount_subquery = pcount_subquery.\
filter(CompletedMatch.competition_id.in_(competition_ids))
pcount_subquery = pcount_subquery.\
group_by(CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.user_id).\
having(db.func.sum(CompletedMatchPlayer.kills) >=\
db.func.sum(CompletedMatchPlayer.deaths)).\
with_entities(CompletedMatchPlayer.user_id).\
subquery()
positive_counts = db.engine.execute( \
db.select(columns=['user_id', db.func.count()],\
from_obj=pcount_subquery).\
group_by('user_id') )
pcounts = dict()
for p in positive_counts:
pcounts[p[0]] = p[1]
record_subquery = \
db.session.query(CompletedMatchPlayer.user_id,
CompletedMatch.competition_id,
CompletedMatchPlayer.cmatch_id).\
join((CompletedMatchRound,\
db.and_(\
CompletedMatchRound.cmatch_id ==\
CompletedMatchPlayer.cmatch_id,\
CompletedMatchRound.round_id ==\
CompletedMatchPlayer.round_id,\
)))
if gametype_id:
record_subquery = record_subquery.filter_by(gametype_id=gametype_id)
if map_id:
record_subquery = record_subquery.filter_by(map_id=map_id)
if competition_id:
record_subquery = record_subquery.\
filter(CompletedMatch.competition_id == competition_id)
record_subquery = record_subquery.\
filter(CompletedMatch.id == CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.user_id).\
with_entities(CompletedMatchPlayer.user_id,
CompletedMatchPlayer.cmatch_id).\
subquery()
records = db.session.query(\
record_subquery.c.user_id,
db.func.sum(\
db.cast(CompletedMatch.wins >\
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.losses >\
CompletedMatch.wins, db.INT))\
).\
select_from(CompletedMatch).\
join((record_subquery,\
CompletedMatch.id ==\
record_subquery.c.cmatch_id\
)).\
filter(CompletedMatch.team_id == team_id).\
filter(CompletedMatch.competition_id.\
in_(competition_ids)).\
group_by(record_subquery.c.user_id)
stats_pm = dict()
for s in records:
stats_pm[s[0]] = (s[1], s[2])
stats_res = db.session.query(CompletedMatchPlayer.user_id,
db.func.sum(CompletedMatchPlayer.kills),
db.func.sum(CompletedMatchPlayer.deaths),
db.func.sum(CompletedMatchPlayer.off_objs),
db.func.sum(CompletedMatchPlayer.def_objs),
db.func.sum(CompletedMatchPlayer.score),
db.func.count(db.func.distinct(CompletedMatch.id))).\
join((CompletedMatch,
CompletedMatchPlayer.cmatch_id == CompletedMatch.id)).\
join((CompletedMatchRound,\
db.and_(\
CompletedMatchRound.cmatch_id == CompletedMatch.id,\
CompletedMatchRound.round_id ==\
CompletedMatchPlayer.round_id,\
))).\
filter(CompletedMatch.team_id == team_id)
if gametype_id:
stats_res = stats_res.filter_by(gametype_id=gametype_id)
if map_id:
stats_res = stats_res.filter_by(map_id=map_id)
if competition_id:
stats_res = stats_res.\
filter(CompletedMatch.competition_id == competition_id)
else:
stats_res = stats_res.\
filter(CompletedMatch.competition_id.in_(competition_ids))
stats_res = stats_res.\
group_by(CompletedMatchPlayer.user_id).\
all()
stats = []
for s in stats_res:
w = 0
l = 0
pos_kdr = 0
if s[0] in stats_pm:
w, l = stats_pm[s[0]]
if s[0] in pcounts:
pos_kdr = pcounts[s[0]]
if (w + l) == 0:
ts = 0
else:
ts = float(s[3] + s[4]) / float(w + l)
stats_item = {
'user_id': s[0],
'kills': s[1],
'deaths': s[2],
'offobjs': s[3],
'defobjs': s[4],
'score': s[5],
'team_score': ts,
'wins': w,
'losses': l,
'pos_kdr': pos_kdr
}
stats.append(stats_item)
# team record
cmatches = db.session.query(\
CompletedMatchRound.cmatch_id,
CompletedMatch.wins,
CompletedMatch.losses,\
CompletedMatch.final_result_method).\
join(CompletedMatch).\
filter_by(team_id=team_id).\
filter(CompletedMatch.final_result_method !=\
CompletedMatch.FinalResultByForfeit)
if gametype_id:
cmatches = cmatches.\
filter(CompletedMatchRound.gametype_id == gametype_id)
if map_id:
cmatches = cmatches.\
filter(CompletedMatchRound.map_id == map_id)
if competition_id:
cmatches = cmatches.\
filter(CompletedMatch.competition_id == competition_id)
else:
cmatches = cmatches.\
filter(CompletedMatch.competition_id.in_(competition_ids))
cmatches = cmatches.all()
total_wins = 0
total_losses = 0
total_draws = 0
seen_matches = set()
for c in cmatches:
cmatch_id = c[0]
if cmatch_id in seen_matches:
continue
seen_matches.add(cmatch_id)
if c.wins > c.losses:
total_wins += 1
elif c.wins < c.losses:
total_losses += 1
else:
total_draws += 1
phash = dict()
for p in team.players:
phash[p.user_id] = p.user.name
return rt('teams/stats.html',
page=page,
game=game,
combined=True,
total_stats=stats,
stats=[],
phash=phash,
gametype=gametype,
map=mapp,
combine_form=combine_form,
competition=competition,
total_wins=total_wins,
total_losses=total_losses,
total_draws=total_draws,
team=team)
def stats(team_id=0):
team = Team.query.filter_by(id=team_id).first()
if not team:
return redirect(url_for('all'))
if g.user.is_on_team(team_id):
page = {'top': 'my_teams', 'sub': 'player_stats'}
else:
page = {'top': 'team', 'sub': 'player_stats'}
# only look at one game at a time. but the tables only have competition
# id, so get the competition ids from the game id.
game_id = int(request.values.get('game', 1))
games = db.session.query(Game).all()
game = [ga for ga in games if ga.id == game_id]
if not game:
flash("That game doesn't exit.", "info")
return redirect(url_for('stats', team_id=team_id))
else:
game = game[0]
competitions = db.session.query(Competition.id,
Competition.abbr,
Competition.name).\
filter_by(game_id=game_id).order_by(Competition.abbr).all()
gametypes = db.session.query(GameType.id, GameType.name).\
filter_by(game_id=game_id).\
order_by(GameType.name).\
all()
maps = db.session.query(Map.id, Map.name).\
filter_by(game_id=game_id).\
order_by(Map.name).all()
competition_ids = [c.id for c in competitions]
combine_form = PlayerStatsCombineForm()
combine_form.game.choices = [(ga.id, ga.name) for ga in games]
combine_form.competition.choices = [(0, "Competition"), (0, "Any")]
combine_form.gametype.choices = [(0, "Game Type"), (0, "Any")]
combine_form.map.choices = [(0, "Map"), (0, "Any")]
combine_form.competition.choices.extend([ (c.id, c.abbr) for c in\
competitions ])
combine_form.gametype.choices.extend([ (gt.id, gt.name) for gt in\
gametypes ])
combine_form.map.choices.extend([ (m.id, m.name) for m in\
maps ])
combine_form.game.data = game_id
grouper_id = grouper_id_to_int(request.values.get('grouper'))
if grouper_id == StatsGrouperGametype:
grouper = CompletedMatchRound.gametype_id
ghash = dict()
for gt in gametypes:
ghash[gt.id] = gt.name
elif grouper_id == StatsGrouperMap:
grouper = CompletedMatchRound.map_id
ghash = dict()
for m in maps:
ghash[m.id] = m.name
elif grouper_id == StatsGrouperCompetition:
grouper = CompletedMatch.competition_id
ghash = dict()
for c in competitions:
ghash[c.id] = c.name
else:
return redirect(url_for('stats', team_id=team_id))
##########################################################
# fetch totals
pcount_subquery = \
db.session.query(CompletedMatchPlayer.user_id).\
join((CompletedMatch,
CompletedMatchPlayer.cmatch_id == CompletedMatch.id)).\
filter(CompletedMatch.team_id == team_id).\
filter(CompletedMatch.competition_id.in_(competition_ids)).\
group_by(CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.user_id).\
having(db.func.sum(CompletedMatchPlayer.kills) >=\
db.func.sum(CompletedMatchPlayer.deaths)).\
with_entities(CompletedMatchPlayer.user_id).\
subquery()
positive_counts = db.engine.execute( \
db.select(columns=['user_id', db.func.count()],\
from_obj=pcount_subquery).\
group_by('user_id') )
pcounts = dict()
for p in positive_counts:
pcounts[p[0]] = p[1]
record_subquery = \
db.session.query(CompletedMatchPlayer.user_id,
CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.user_id).\
with_entities(CompletedMatchPlayer.user_id,\
CompletedMatchPlayer.cmatch_id).\
subquery()
records = db.session.query(\
record_subquery.c.user_id,
db.func.sum(\
db.cast(CompletedMatch.wins >\
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.losses >\
CompletedMatch.wins, db.INT))\
).\
select_from(CompletedMatch).\
join((record_subquery,\
CompletedMatch.id ==\
record_subquery.c.cmatch_id\
)).\
filter(CompletedMatch.team_id == team_id).\
filter(CompletedMatch.competition_id.in_(competition_ids)).\
group_by(record_subquery.c.user_id)
stats_pm = dict()
for s in records:
stats_pm[s[0]] = (s[1], s[2])
stats_res = db.session.query(CompletedMatchPlayer.user_id,
db.func.sum(CompletedMatchPlayer.kills),
db.func.sum(CompletedMatchPlayer.deaths),
db.func.sum(CompletedMatchPlayer.off_objs),
db.func.sum(CompletedMatchPlayer.def_objs),
db.func.sum(CompletedMatchPlayer.score),
db.func.count(db.func.distinct(CompletedMatch.id))).\
join((CompletedMatch,
CompletedMatchPlayer.cmatch_id == CompletedMatch.id)).\
filter(CompletedMatch.team_id == team_id).\
filter(CompletedMatch.competition_id.in_(competition_ids)).\
group_by(CompletedMatchPlayer.user_id).\
all()
total_stats = []
for s in stats_res:
w = 0
l = 0
pos_kdr = 0
if s[0] in stats_pm:
w, l = stats_pm[s[0]]
if s[0] in pcounts:
pos_kdr = pcounts[s[0]]
if (w + l) == 0:
ts = 0
else:
ts = float(s[3] + s[4]) / float(w + l)
stats_item = {
'user_id': s[0],
'kills': s[1],
'deaths': s[2],
'offobjs': s[3],
'defobjs': s[4],
'score': s[5],
'team_score': ts,
'wins': w,
'losses': l,
'pos_kdr': pos_kdr
}
total_stats.append(stats_item)
##########################################################
pcount_subquery = \
db.session.query(CompletedMatchPlayer.user_id,
grouper).\
join((CompletedMatch,
CompletedMatchPlayer.cmatch_id == CompletedMatch.id)).\
join((CompletedMatchRound,\
db.and_(\
CompletedMatchRound.cmatch_id == CompletedMatch.id,\
CompletedMatchRound.round_id ==\
CompletedMatchPlayer.round_id,\
))).\
filter(CompletedMatch.team_id == team_id).\
filter(CompletedMatch.competition_id.in_(competition_ids)).\
group_by(grouper).\
group_by(CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.user_id).\
having(db.func.sum(CompletedMatchPlayer.kills) >=\
db.func.sum(CompletedMatchPlayer.deaths)).\
with_entities(CompletedMatchPlayer.user_id,\
grouper).\
subquery()
positive_counts = db.engine.execute( \
db.select(columns=['user_id', grouper.key, db.func.count()],\
from_obj=pcount_subquery).\
group_by(grouper.key, 'user_id') )
pcounts = dict()
for p in positive_counts:
pcounts[(p[0], p[1])] = p[2]
record_subquery = \
db.session.query(CompletedMatchPlayer.user_id,
grouper,
CompletedMatchPlayer.cmatch_id).\
join((CompletedMatchRound,\
db.and_(\
CompletedMatchRound.cmatch_id ==\
CompletedMatchPlayer.cmatch_id,\
CompletedMatchRound.round_id ==\
CompletedMatchPlayer.round_id,\
)))
if grouper.key == 'competition_id':
record_subquery = record_subquery.\
filter(CompletedMatch.id == CompletedMatchPlayer.cmatch_id)
record_subquery = record_subquery.\
group_by(grouper).\
group_by(CompletedMatchPlayer.cmatch_id).\
group_by(CompletedMatchPlayer.user_id).\
with_entities(CompletedMatchPlayer.user_id,
grouper,
CompletedMatchPlayer.cmatch_id).\
subquery()
records = db.session.query(\
record_subquery.c.user_id,
record_subquery.c.get(grouper.key),
db.func.sum(\
db.cast(CompletedMatch.wins >\
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.losses >\
CompletedMatch.wins, db.INT))\
).\
select_from(CompletedMatch).\
join((record_subquery,\
CompletedMatch.id ==\
record_subquery.c.cmatch_id\
)).\
filter(CompletedMatch.team_id == team_id).\
filter(CompletedMatch.competition_id.\
in_(competition_ids)).\
group_by(record_subquery.c.get(grouper.key)).\
group_by(record_subquery.c.user_id)
stats_pm = dict()
for s in records:
stats_pm[(s[0], s[1])] = (s[2], s[3])
stats_res = db.session.query(CompletedMatchPlayer.user_id,
grouper,
db.func.sum(CompletedMatchPlayer.kills),
db.func.sum(CompletedMatchPlayer.deaths),
db.func.sum(CompletedMatchPlayer.off_objs),
db.func.sum(CompletedMatchPlayer.def_objs),
db.func.sum(CompletedMatchPlayer.score),
db.func.count(db.func.distinct(CompletedMatch.id))).\
join((CompletedMatch,
CompletedMatchPlayer.cmatch_id == CompletedMatch.id)).\
join((CompletedMatchRound,\
db.and_(\
CompletedMatchRound.cmatch_id == CompletedMatch.id,\
CompletedMatchRound.round_id ==\
CompletedMatchPlayer.round_id,\
))).\
filter(CompletedMatch.team_id == team_id).\
filter(CompletedMatch.competition_id.in_(competition_ids)).\
group_by(grouper).\
group_by(CompletedMatchPlayer.user_id).\
all()
stats = []
for s in stats_res:
w = 0
l = 0
pos_kdr = 0
if (s[0], s[1]) in stats_pm:
w, l = stats_pm[(s[0], s[1])]
if (s[0], s[1]) in pcounts:
pos_kdr = pcounts[(s[0], s[1])]
if (w + l) == 0:
ts = 0
else:
ts = float(s[4] + s[5]) / float(w + l)
stats_item = {
'user_id': s[0],
'grouper': s[1],
'kills': s[2],
'deaths': s[3],
'offobjs': s[4],
'defobjs': s[5],
'score': s[6],
'team_score': ts,
'wins': w,
'losses': l,
'pos_kdr': pos_kdr
}
stats.append(stats_item)
if grouper.key == 'competition_id':
cmatches = db.session.query(grouper,
CompletedMatch.id,
CompletedMatch.wins,
CompletedMatch.losses).\
filter_by(team_id=team_id).\
filter(CompletedMatch.competition_id.in_(competition_ids)).\
filter(CompletedMatch.final_result_method !=\
CompletedMatch.FinalResultByForfeit).\
all()
else:
cmatches = db.session.query(grouper,
CompletedMatchRound.cmatch_id,
CompletedMatch.wins,
CompletedMatch.losses).\
join(CompletedMatch).\
filter_by(team_id=team_id).\
filter(CompletedMatch.competition_id.in_(competition_ids)).\
filter(CompletedMatch.final_result_method !=\
CompletedMatch.FinalResultByForfeit).\
all()
total_wins = 0
total_losses = 0
total_draws = 0
seen_matches = set()
seen_match_grouper = set()
wins = collections.defaultdict(int)
losses = collections.defaultdict(int)
draws = collections.defaultdict(int)
for c in cmatches:
val = c[0]
cmatch_id = c[1]
if (cmatch_id, val) in seen_match_grouper:
continue
seen_match_grouper.add((cmatch_id, val))
wins[val] += 0
if c.wins > c.losses:
wins[val] += 1
elif c.wins < c.losses:
losses[val] += 1
else:
draws[val] += 1
if cmatch_id not in seen_matches:
seen_matches.add(cmatch_id)
if c.wins > c.losses:
total_wins += 1
elif c.wins < c.losses:
total_losses += 1
else:
total_draws += 1
phash = dict()
for p in team.players:
phash[p.user_id] = p.user.name
return rt('teams/stats.html',
page=page,
game=game,
combined=False,
stats=stats,
pcounts=pcounts,
ghash=ghash,
phash=phash,
combine_form=combine_form,
grouper=grouper.key[:-3],
wins=wins,
losses=losses,
draws=draws,
total_stats=total_stats,
total_wins=total_wins,
total_losses=total_losses,
total_draws=total_draws,
team=team)
def historical_records(self):
opponent_rec = db.session.query(\
db.func.sum(\
db.cast(CompletedMatch.wins >\
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins < \
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins == \
CompletedMatch.losses, db.INT))\
).\
filter(CompletedMatch.team_id == self.team_id).\
filter(CompletedMatch.opponent_id == self.opponent_id).\
first()
competition_rec = db.session.query(\
db.func.sum(\
db.cast(CompletedMatch.wins >\
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins < \
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins == \
CompletedMatch.losses, db.INT))\
).\
filter(CompletedMatch.team_id == self.team_id).\
filter(CompletedMatch.competition_id == self.competition_id).\
first()
server_rec = db.session.query(\
db.func.sum(\
db.cast(CompletedMatch.wins >\
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins < \
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins == \
CompletedMatch.losses, db.INT))\
).\
filter(CompletedMatch.team_id == self.team_id).\
filter(CompletedMatch.server_id == self.server_id).\
first()
opp_comp_rec = db.session.query(\
db.func.sum(\
db.cast(CompletedMatch.wins >\
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins < \
CompletedMatch.losses, db.INT)), \
db.func.sum(\
db.cast(CompletedMatch.wins == \
CompletedMatch.losses, db.INT))\
).\
filter(CompletedMatch.team_id == self.team_id).\
filter(CompletedMatch.opponent_id == self.opponent_id).\
filter(CompletedMatch.competition_id == self.competition_id).\
first()
def zero(rec):
if rec == (None, None, None):
return (0, 0, 0)
return rec
opponent_rec = zero(opponent_rec)
competition_rec = zero(competition_rec)
server_rec = zero(server_rec)
opp_comp_rec = zero(opp_comp_rec)
return ( opponent_rec, competition_rec, server_rec, opp_comp_rec )
