import datetime\r
+import json\r
import logging\r
import re\r
import sqlalchemy as sa\r
+import sqlalchemy.sql.functions as func\r
import time\r
from pyramid.response import Response\r
from pyramid.url import current_route_url\r
try:\r
player_q = DBSession.query(Player).\\r
filter(Player.player_id > 2).\\r
+ filter(Player.active_ind == True).\\r
filter(sa.not_(Player.nick.like('Anonymous Player%'))).\\r
order_by(Player.player_id.desc())\r
\r
- players = Page(player_q, current_page, url=page_url)\r
+ players = Page(player_q, current_page, items_per_page=10, url=page_url)\r
\r
last_linked_page = current_page + 4\r
if last_linked_page > players.last_page:\r
}\r
\r
\r
+def get_games_played(player_id):\r
+ """\r
+ Provides a breakdown by gametype of the games played by player_id.\r
+\r
+ Returns a tuple containing (total_games, games_breakdown), where\r
+ total_games is the absolute number of games played by player_id\r
+ and games_breakdown is an array containing (game_type_cd, # games)\r
+ """\r
+ games_played = DBSession.query(Game.game_type_cd, func.count()).\\r
+ filter(Game.game_id == PlayerGameStat.game_id).\\r
+ filter(PlayerGameStat.player_id == player_id).\\r
+ group_by(Game.game_type_cd).\\r
+ order_by(func.count().desc()).all()\r
+\r
+ total = 0\r
+ for (game_type_cd, games) in games_played:\r
+ total += games\r
+\r
+ return (total, games_played)\r
+\r
+\r
+# TODO: should probably factor the above function into this one such that\r
+# total_stats['ctf_games'] is the count of CTF games and so on...\r
+def get_total_stats(player_id):\r
+ """\r
+ Provides aggregated stats by player_id.\r
+\r
+ Returns a dict with the keys 'kills', 'deaths', 'alivetime'.\r
+\r
+ kills = how many kills a player has over all games\r
+ deaths = how many deaths a player has over all games\r
+ alivetime = how long a player has played over all games\r
+\r
+ If any of the above are None, they are set to 0.\r
+ """\r
+ total_stats = {}\r
+ (total_stats['kills'], total_stats['deaths'], total_stats['alivetime']) = DBSession.\\r
+ query("total_kills", "total_deaths", "total_alivetime").\\r
+ from_statement(\r
+ "select sum(kills) total_kills, "\r
+ "sum(deaths) total_deaths, "\r
+ "sum(alivetime) total_alivetime "\r
+ "from player_game_stats "\r
+ "where player_id=:player_id"\r
+ ).params(player_id=player_id).one()\r
+\r
+ (total_stats['wins'],) = DBSession.\\r
+ query("total_wins").\\r
+ from_statement(\r
+ "select count(*) total_wins "\r
+ "from games g, player_game_stats pgs "\r
+ "where g.game_id = pgs.game_id "\r
+ "and player_id=:player_id "\r
+ "and (g.winner = pgs.team or pgs.rank = 1)"\r
+ ).params(player_id=player_id).one()\r
+\r
+ for (key,value) in total_stats.items():\r
+ if value == None:\r
+ total_stats[key] = 0\r
+\r
+ return total_stats\r
+\r
+\r
+def get_accuracy_stats(player_id, weapon_cd, games):\r
+ """\r
+ Provides accuracy for weapon_cd by player_id for the past N games.\r
+ """\r
+ # Reaching back 90 days should give us an accurate enough average\r
+ # We then multiply this out for the number of data points (games) to\r
+ # create parameters for a flot graph\r
+ try:\r
+ raw_avg = DBSession.query(func.sum(PlayerWeaponStat.hit),\r
+ func.sum(PlayerWeaponStat.fired)).\\r
+ filter(PlayerWeaponStat.player_id == player_id).\\r
+ filter(PlayerWeaponStat.weapon_cd == weapon_cd).\\r
+ one()\r
+\r
+ raw_avg = round(float(raw_avg[0])/raw_avg[1]*100, 2)\r
+\r
+ avg = []\r
+ for i in range(games):\r
+ avg.append((i, raw_avg))\r
+\r
+ # Determine the raw accuracy (hit, fired) numbers for $games games\r
+ # This is then enumerated to create parameters for a flot graph\r
+ raw_accs = DBSession.query(PlayerWeaponStat.hit, PlayerWeaponStat.fired).\\r
+ filter(PlayerWeaponStat.player_id == player_id).\\r
+ filter(PlayerWeaponStat.weapon_cd == weapon_cd).\\r
+ order_by(PlayerWeaponStat.create_dt.desc()).\\r
+ limit(games).\\r
+ all()\r
+\r
+ accs = []\r
+ for i in range(len(raw_accs)):\r
+ accs.append((i, round(float(raw_accs[i][0])/raw_accs[i][1]*100, 2)))\r
+ except:\r
+ accs = 0\r
+ avg = 0\r
+\r
+ return (avg, accs)\r
+\r
+\r
def player_info(request):\r
"""\r
Provides detailed information on a specific player\r
player_id = int(request.matchdict['id'])\r
if player_id <= 2:\r
player_id = -1;\r
- \r
+\r
try:\r
- player = DBSession.query(Player).filter_by(player_id=player_id).one()\r
-\r
- weapon_stats = DBSession.query("descr", "weapon_cd", "actual_total", \r
- "max_total", "hit_total", "fired_total", "frags_total").\\r
- from_statement(\r
- "select cw.descr, cw.weapon_cd, sum(actual) actual_total, "\r
- "sum(max) max_total, sum(hit) hit_total, "\r
- "sum(fired) fired_total, sum(frags) frags_total "\r
- "from player_weapon_stats ws, cd_weapon cw "\r
- "where ws.weapon_cd = cw.weapon_cd "\r
- "and player_id = :player_id "\r
- "group by descr, cw.weapon_cd "\r
- "order by descr"\r
- ).params(player_id=player_id).all()\r
+ player = DBSession.query(Player).filter_by(player_id=player_id).\\r
+ filter(Player.active_ind == True).one()\r
+\r
+ # games played, alivetime, wins, kills, deaths\r
+ total_stats = get_total_stats(player.player_id)\r
+\r
+ # games breakdown - N games played (X ctf, Y dm) etc\r
+ (total_games, games_breakdown) = get_games_played(player.player_id)\r
+\r
+\r
+ # friendly display of elo information and preliminary status\r
+ elos = DBSession.query(PlayerElo).filter_by(player_id=player_id).\\r
+ filter(PlayerElo.game_type_cd.in_(['ctf','duel','dm'])).\\r
+ order_by(PlayerElo.elo.desc()).all()\r
+\r
+ elos_display = []\r
+ for elo in elos:\r
+ if elo.games > 32:\r
+ str = "{0} ({1})"\r
+ else:\r
+ str = "{0}* ({1})"\r
+\r
+ elos_display.append(str.format(round(elo.elo, 3),\r
+ elo.game_type_cd))\r
+\r
+ # data for the accuracy graph, which is converted into a JSON array for\r
+ # usage by flot\r
+ (avg, accs) = get_accuracy_stats(player_id, 'nex', 20)\r
+\r
+ avg = json.dumps(avg)\r
+ accs = json.dumps(accs)\r
\r
+\r
+ # recent games table, all data\r
recent_games = DBSession.query(PlayerGameStat, Game, Server, Map).\\r
filter(PlayerGameStat.player_id == player_id).\\r
filter(PlayerGameStat.game_id == Game.game_id).\\r
filter(Game.map_id == Map.map_id).\\r
order_by(Game.game_id.desc())[0:10]\r
\r
- game_stats = {}\r
- (game_stats['avg_rank'], game_stats['total_kills'], \r
- game_stats['total_deaths'], game_stats['total_suicides'], \r
- game_stats['total_score'], game_stats['total_time'], \r
- game_stats['total_held'], game_stats['total_captures'], \r
- game_stats['total_pickups'],game_stats['total_drops'], \r
- game_stats['total_returns'], game_stats['total_collects'], \r
- game_stats['total_destroys'], game_stats['total_dhk'], \r
- game_stats['total_pushes'], game_stats['total_pushed'], \r
- game_stats['total_carrier_frags'], \r
- game_stats['total_alivetime'],\r
- game_stats['total_games_played']) = DBSession.\\r
- query("avg_rank", "total_kills", "total_deaths", \r
- "total_suicides", "total_score", "total_time", "total_held",\r
- "total_captures", "total_pickups", "total_drops", \r
- "total_returns", "total_collects", "total_destroys", \r
- "total_dhk", "total_pushes", "total_pushed", \r
- "total_carrier_frags", "total_alivetime", \r
- "total_games_played").\\r
- from_statement(\r
- "select round(avg(rank)) avg_rank, sum(kills) total_kills, "\r
- "sum(deaths) total_deaths, sum(suicides) total_suicides, "\r
- "sum(score) total_score, sum(time) total_time, "\r
- "sum(held) total_held, sum(captures) total_captures, "\r
- "sum(pickups) total_pickups, sum(drops) total_drops, "\r
- "sum(returns) total_returns, sum(collects) total_collects, "\r
- "sum(destroys) total_destroys, sum(destroys_holding_key) total_dhk, "\r
- "sum(pushes) total_pushes, sum(pushed) total_pushed, "\r
- "sum(carrier_frags) total_carrier_frags, "\r
- "sum(alivetime) total_alivetime, count(*) total_games_played "\r
- "from player_game_stats "\r
- "where player_id=:player_id"\r
- ).params(player_id=player_id).one()\r
-\r
- for (key,value) in game_stats.items():\r
- if value == None:\r
- game_stats[key] = '-'\r
-\r
except Exception as e:\r
player = None\r
- weapon_stats = None\r
- game_stats = None\r
+ elos_display = None\r
+ total_stats = None\r
recent_games = None\r
+ total_games = None\r
+ games_breakdown = None\r
+ avg = None\r
+ accs = None\r
+ raise e\r
\r
- return {'player':player, \r
+ return {'player':player,\r
+ 'elos_display':elos_display,\r
'recent_games':recent_games,\r
- 'weapon_stats':weapon_stats,\r
- 'game_stats':game_stats}\r
+ 'total_stats':total_stats,\r
+ 'total_games':total_games,\r
+ 'games_breakdown':games_breakdown,\r
+ 'avg':avg,\r
+ 'accs':accs,\r
+ }\r
\r
\r
def player_game_index(request):\r
current_page = 1\r
\r
try:\r
- player = DBSession.query(Player).filter_by(player_id=player_id).one()\r
-\r
- games_q = DBSession.query(PlayerGameStat, Game, Server, Map).\\r
- filter(PlayerGameStat.player_id == player_id).\\r
- filter(PlayerGameStat.game_id == Game.game_id).\\r
- filter(Game.server_id == Server.server_id).\\r
- filter(Game.map_id == Map.map_id).\\r
- order_by(Game.game_id.desc())\r
+ games_q = DBSession.query(Game, Server, Map).\\r
+ filter(PlayerGameStat.game_id == Game.game_id).\\r
+ filter(PlayerGameStat.player_id == player_id).\\r
+ filter(Game.server_id == Server.server_id).\\r
+ filter(Game.map_id == Map.map_id).\\r
+ order_by(Game.game_id.desc())\r
\r
games = Page(games_q, current_page, url=page_url)\r
\r
- \r
+ pgstats = {}\r
+ for (game, server, map) in games:\r
+ pgstats[game.game_id] = DBSession.query(PlayerGameStat).\\r
+ filter(PlayerGameStat.game_id == game.game_id).\\r
+ order_by(PlayerGameStat.rank).\\r
+ order_by(PlayerGameStat.score).all()\r
+\r
except Exception as e:\r
player = None\r
games = None\r
\r
- return {'player':player,\r
- 'games':games}\r
+ return {'player_id':player_id,\r
+ 'games':games,\r
+ 'pgstats':pgstats}\r
projects / xonotic / xonstat.git / blobdiff