--- /dev/null
+import datetime\r
+import logging\r
+import re\r
+import time\r
+from pyramid.response import Response\r
+from sqlalchemy import desc\r
+from webhelpers.paginate import Page, PageURL\r
+from xonstat.models import *\r
+from xonstat.util import page_url\r
+\r
+log = logging.getLogger(__name__)\r
+\r
+\r
+def game_index(request):\r
+ """\r
+ Provides a list of current games, with the associated game stats.\r
+ These games are ordered by game_id, with the most current ones first.\r
+ Paginated.\r
+ """\r
+ if 'page' in request.matchdict:\r
+ current_page = request.matchdict['page']\r
+ else:\r
+ current_page = 1\r
+\r
+ games_q = DBSession.query(Game, Server, Map).\\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
+ 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
+ return {'games':games, \r
+ 'pgstats':pgstats}\r
+\r
+\r
+def game_info(request):\r
+ """\r
+ List the game stats (scoreboard) for a particular game. Paginated.\r
+ """\r
+ game_id = request.matchdict['id']\r
+ try:\r
+ notfound = False\r
+\r
+ (start_dt, game_type_cd, server_id, server_name, map_id, map_name) = \\r
+ DBSession.query("start_dt", "game_type_cd", "server_id", \r
+ "server_name", "map_id", "map_name").\\r
+ from_statement("select g.start_dt, g.game_type_cd, "\r
+ "g.server_id, s.name as server_name, g.map_id, "\r
+ "m.name as map_name "\r
+ "from games g, servers s, maps m "\r
+ "where g.game_id = :game_id "\r
+ "and g.server_id = s.server_id "\r
+ "and g.map_id = m.map_id").\\r
+ params(game_id=game_id).one()\r
+\r
+ player_game_stats = DBSession.query(PlayerGameStat).\\r
+ from_statement("select * from player_game_stats "\r
+ "where game_id = :game_id "\r
+ "order by score desc").\\r
+ params(game_id=game_id).all()\r
+ except Exception as inst:\r
+ notfound = True\r
+ start_dt = None\r
+ game_type_cd = None\r
+ server_id = None\r
+ server_name = None\r
+ map_id = None\r
+ map_name = None\r
+ player_game_stats = None\r
+\r
+ return {'notfound':notfound,\r
+ 'start_dt':start_dt,\r
+ 'game_type_cd':game_type_cd,\r
+ 'server_id':server_id,\r
+ 'server_name':server_name,\r
+ 'map_id':map_id,\r
+ 'map_name':map_name,\r
+ 'player_game_stats':player_game_stats}\r
--- /dev/null
+import datetime\r
+import logging\r
+import re\r
+import time\r
+from pyramid.response import Response\r
+from sqlalchemy import desc\r
+from webhelpers.paginate import Page, PageURL\r
+from xonstat.models import *\r
+from xonstat.util import page_url\r
+\r
+log = logging.getLogger(__name__)\r
+\r
+\r
+def player_index(request):\r
+ """\r
+ Provides a list of all the current players. \r
+ """\r
+ players = DBSession.query(Player)\r
+\r
+ log.debug("testing logging; entered PlayerHandler.index()")\r
+ return {'players':players}\r
+\r
+def player_info(request):\r
+ """\r
+ Provides detailed information on a specific player\r
+ """\r
+ player_id = request.matchdict['id']\r
+ try:\r
+ player = DBSession.query(Player).filter_by(player_id=player_id).one()\r
+\r
+ weapon_stats = DBSession.query("descr", "actual_total", \r
+ "max_total", "hit_total", "fired_total", "frags_total").\\r
+ from_statement(\r
+ "select cw.descr, sum(actual) actual_total, "\r
+ "sum(max) max_total, sum(hit) hit_total, "\r
+ "sum(fired) fired_total, sum(frags) frags_total "\r
+ "from xonstat.player_weapon_stats ws, xonstat.cd_weapon cw "\r
+ "where ws.weapon_cd = cw.weapon_cd "\r
+ "and player_id = :player_id "\r
+ "group by descr "\r
+ "order by descr"\r
+ ).params(player_id=player_id).all()\r
+\r
+ log.debug(weapon_stats)\r
+\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.server_id == Server.server_id).\\r
+ filter(Game.map_id == Map.map_id).\\r
+ order_by(Game.game_id.desc())[0:10]\r
+\r
+ except Exception as e:\r
+ player = None\r
+ weapon_stats = None\r
+ recent_games = None\r
+ return {'player':player, \r
+ 'recent_games':recent_games,\r
+ 'weapon_stats':weapon_stats}\r
+\r
+\r
+def player_game_index(request):\r
+ """\r
+ Provides an index of the games in which a particular\r
+ player was involved. This is ordered by game_id, with\r
+ the most recent game_ids first. Paginated.\r
+ """\r
+ player_id = request.matchdict['player_id']\r
+\r
+ if 'page' in request.matchdict:\r
+ current_page = request.matchdict['page']\r
+ else:\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
+\r
+ games = Page(games_q, current_page, url=page_url)\r
+\r
+ \r
+ except Exception as e:\r
+ player = None\r
+ games = None\r
+ raise e\r
+\r
+ return {'player':player,\r
+ 'games':games}\r
+\r
+\r
+def player_weapon_stats(request):\r
+ """\r
+ List the accuracy statistics for the given player_id in a particular\r
+ game.\r
+ """\r
+ game_id = request.matchdict['game_id']\r
+ pgstat_id = request.matchdict['pgstat_id']\r
+ try:\r
+ pwstats = DBSession.query(PlayerWeaponStat, Weapon).\\r
+ filter(PlayerWeaponStat.weapon_cd==Weapon.weapon_cd).\\r
+ filter_by(game_id=game_id).\\r
+ filter_by(player_game_stat_id=pgstat_id).\\r
+ order_by(Weapon.descr).\\r
+ all()\r
+\r
+ pgstat = DBSession.query(PlayerGameStat).\\r
+ filter_by(player_game_stat_id=pgstat_id).one()\r
+\r
+ game = DBSession.query(Game).filter_by(game_id=game_id).one()\r
+\r
+ log.debug(pwstats)\r
+ log.debug(pgstat)\r
+ log.debug(game)\r
+\r
+ except Exception as e:\r
+ pwstats = None\r
+ pgstat = None\r
+ game = None\r
+ raise e\r
+ return {'pwstats':pwstats, 'pgstat':pgstat, 'game':game}\r
--- /dev/null
+import datetime\r
+import logging\r
+import re\r
+import time\r
+from pyramid.response import Response\r
+from sqlalchemy.orm.exc import MultipleResultsFound, NoResultFound\r
+from xonstat.models import *\r
+\r
+log = logging.getLogger(__name__)\r
+\r
+\r
+def get_or_create_server(session=None, name=None):\r
+ """\r
+ Find a server by name or create one if not found. Parameters:\r
+\r
+ session - SQLAlchemy database session factory\r
+ name - server name of the server to be found or created\r
+ """\r
+ try:\r
+ # find one by that name, if it exists\r
+ server = session.query(Server).filter_by(name=name).one()\r
+ log.debug("Found server id {0} with name {1}.".format(\r
+ server.server_id, server.name))\r
+ except NoResultFound, e:\r
+ server = Server(name=name)\r
+ session.add(server)\r
+ session.flush()\r
+ log.debug("Created server id {0} with name {1}".format(\r
+ server.server_id, server.name))\r
+ except MultipleResultsFound, e:\r
+ # multiple found, so use the first one but warn\r
+ log.debug(e)\r
+ servers = session.query(Server).filter_by(name=name).order_by(\r
+ Server.server_id).all()\r
+ server = servers[0]\r
+ log.debug("Created server id {0} with name {1} but found \\r
+ multiple".format(\r
+ server.server_id, server.name))\r
+\r
+ return server\r
+\r
+def get_or_create_map(session=None, name=None):\r
+ """\r
+ Find a map by name or create one if not found. Parameters:\r
+\r
+ session - SQLAlchemy database session factory\r
+ name - map name of the map to be found or created\r
+ """\r
+ try:\r
+ # find one by the name, if it exists\r
+ gmap = session.query(Map).filter_by(name=name).one()\r
+ log.debug("Found map id {0} with name {1}.".format(gmap.map_id, \r
+ gmap.name))\r
+ except NoResultFound, e:\r
+ gmap = Map(name=name)\r
+ session.add(gmap)\r
+ session.flush()\r
+ log.debug("Created map id {0} with name {1}.".format(gmap.map_id,\r
+ gmap.name))\r
+ except MultipleResultsFound, e:\r
+ # multiple found, so use the first one but warn\r
+ log.debug(e)\r
+ gmaps = session.query(Map).filter_by(name=name).order_by(\r
+ Map.map_id).all()\r
+ gmap = gmaps[0]\r
+ log.debug("Found map id {0} with name {1} but found \\r
+ multiple.".format(gmap.map_id, gmap.name))\r
+\r
+ return gmap\r
+\r
+\r
+def create_game(session=None, start_dt=None, game_type_cd=None, \r
+ server_id=None, map_id=None, winner=None):\r
+ """\r
+ Creates a game. Parameters:\r
+\r
+ session - SQLAlchemy database session factory\r
+ start_dt - when the game started (datetime object)\r
+ game_type_cd - the game type of the game being played\r
+ server_id - server identifier of the server hosting the game\r
+ map_id - map on which the game was played\r
+ winner - the team id of the team that won\r
+ """\r
+\r
+ game = Game(start_dt=start_dt, game_type_cd=game_type_cd,\r
+ server_id=server_id, map_id=map_id, winner=winner)\r
+ session.add(game)\r
+ session.flush()\r
+ log.debug("Created game id {0} on server {1}, map {2} at time \\r
+ {3} and on map {4}".format(game.game_id, \r
+ server_id, map_id, start_dt, map_id))\r
+\r
+ return game\r
+\r
+\r
+def get_or_create_player(session=None, hashkey=None, nick=None):\r
+ """\r
+ Finds a player by hashkey or creates a new one (along with a\r
+ corresponding hashkey entry. Parameters:\r
+\r
+ session - SQLAlchemy database session factory\r
+ hashkey - hashkey of the player to be found or created\r
+ nick - nick of the player (in case of a first time create)\r
+ """\r
+ # if we have a bot\r
+ if re.search('^bot#\d+$', hashkey):\r
+ player = session.query(Player).filter_by(player_id=1).one()\r
+ # if we have an untracked player\r
+ elif re.search('^player#\d+$', hashkey):\r
+ player = session.query(Player).filter_by(player_id=2).one()\r
+ # else it is a tracked player\r
+ else:\r
+ # see if the player is already in the database\r
+ # if not, create one and the hashkey along with it\r
+ try:\r
+ hashkey = session.query(Hashkey).filter_by(\r
+ hashkey=hashkey).one()\r
+ player = session.query(Player).filter_by(\r
+ player_id=hashkey.player_id).one()\r
+ log.debug("Found existing player {0} with hashkey {1}.".format(\r
+ player.player_id, hashkey.hashkey))\r
+ except:\r
+ player = Player()\r
+\r
+ if nick:\r
+ player.nick = nick\r
+\r
+ session.add(player)\r
+ session.flush()\r
+ hashkey = Hashkey(player_id=player.player_id, hashkey=hashkey)\r
+ session.add(hashkey)\r
+ log.debug("Created player {0} with hashkey {1}.".format(\r
+ player.player_id, hashkey.hashkey))\r
+\r
+ return player\r
+\r
+def create_player_game_stat(session=None, player=None, \r
+ game=None, player_events=None):\r
+ """\r
+ Creates game statistics for a given player in a given game. Parameters:\r
+\r
+ session - SQLAlchemy session factory\r
+ player - Player record of the player who owns the stats\r
+ game - Game record for the game to which the stats pertain\r
+ player_events - dictionary for the actual stats that need to be transformed\r
+ """\r
+\r
+ # in here setup default values (e.g. if game type is CTF then\r
+ # set kills=0, score=0, captures=0, pickups=0, fckills=0, etc\r
+ # TODO: use game's create date here instead of now()\r
+ pgstat = PlayerGameStat(create_dt=datetime.datetime.now())\r
+\r
+ # set player id from player record\r
+ pgstat.player_id = player.player_id\r
+\r
+ #set game id from game record\r
+ pgstat.game_id = game.game_id\r
+\r
+ # all games have a score\r
+ pgstat.score = 0\r
+\r
+ if game.game_type_cd == 'dm':\r
+ pgstat.kills = 0\r
+ pgstat.deaths = 0\r
+ pgstat.suicides = 0\r
+ elif game.game_type_cd == 'ctf':\r
+ pgstat.kills = 0\r
+ pgstat.captures = 0\r
+ pgstat.pickups = 0\r
+ pgstat.drops = 0\r
+ pgstat.returns = 0\r
+ pgstat.carrier_frags = 0\r
+\r
+ for (key,value) in player_events.items():\r
+ if key == 'n': pgstat.nick = value\r
+ if key == 't': pgstat.team = value\r
+ if key == 'rank': pgstat.rank = value\r
+ if key == 'alivetime': \r
+ pgstat.alivetime = datetime.timedelta(seconds=int(round(float(value))))\r
+ if key == 'scoreboard-drops': pgstat.drops = value\r
+ if key == 'scoreboard-returns': pgstat.returns = value\r
+ if key == 'scoreboard-fckills': pgstat.carrier_frags = value\r
+ if key == 'scoreboard-pickups': pgstat.pickups = value\r
+ if key == 'scoreboard-caps': pgstat.captures = value\r
+ if key == 'scoreboard-score': pgstat.score = value\r
+ if key == 'scoreboard-deaths': pgstat.deaths = value\r
+ if key == 'scoreboard-kills': pgstat.kills = value\r
+ if key == 'scoreboard-suicides': pgstat.suicides = value\r
+\r
+ # check to see if we had a name, and if \r
+ # not use the name from the player id\r
+ if pgstat.nick == None:\r
+ pgstat.nick = player.nick\r
+\r
+ session.add(pgstat)\r
+ session.flush()\r
+\r
+ return pgstat\r
+\r
+\r
+def create_player_weapon_stats(session=None, player=None, \r
+ game=None, pgstat=None, player_events=None):\r
+ """\r
+ Creates accuracy records for each weapon used by a given player in a\r
+ given game. Parameters:\r
+\r
+ session - SQLAlchemy session factory object\r
+ player - Player record who owns the weapon stats\r
+ game - Game record in which the stats were created\r
+ pgstat - Corresponding PlayerGameStat record for these weapon stats\r
+ player_events - dictionary containing the raw weapon values that need to be\r
+ transformed\r
+ """\r
+ pwstats = []\r
+\r
+ for (key,value) in player_events.items():\r
+ matched = re.search("acc-(.*?)-cnt-fired", key)\r
+ if matched:\r
+ weapon_cd = matched.group(1)\r
+ pwstat = PlayerWeaponStat()\r
+ pwstat.player_id = player.player_id\r
+ pwstat.game_id = game.game_id\r
+ pwstat.player_game_stat_id = pgstat.player_game_stat_id\r
+ pwstat.weapon_cd = weapon_cd\r
+\r
+ if 'n' in player_events:\r
+ pwstat.nick = player_events['n']\r
+ else:\r
+ pwstat.nick = player_events['P']\r
+\r
+ if 'acc-' + weapon_cd + '-cnt-fired' in player_events:\r
+ pwstat.fired = int(round(float(\r
+ player_events['acc-' + weapon_cd + '-cnt-fired'])))\r
+ if 'acc-' + weapon_cd + '-fired' in player_events:\r
+ pwstat.max = int(round(float(\r
+ player_events['acc-' + weapon_cd + '-fired'])))\r
+ if 'acc-' + weapon_cd + '-cnt-hit' in player_events:\r
+ pwstat.hit = int(round(float(\r
+ player_events['acc-' + weapon_cd + '-cnt-hit'])))\r
+ if 'acc-' + weapon_cd + '-hit' in player_events:\r
+ pwstat.actual = int(round(float(\r
+ player_events['acc-' + weapon_cd + '-hit'])))\r
+ if 'acc-' + weapon_cd + '-frags' in player_events:\r
+ pwstat.frags = int(round(float(\r
+ player_events['acc-' + weapon_cd + '-frags'])))\r
+\r
+ session.add(pwstat)\r
+ pwstats.append(pwstat)\r
+\r
+ return pwstats\r
+\r
+\r
+def parse_body(request):\r
+ """\r
+ Parses the POST request body for a stats submission\r
+ """\r
+ # storage vars for the request body\r
+ game_meta = {}\r
+ player_events = {}\r
+ current_team = None\r
+ players = []\r
+ \r
+ log.debug(request.body)\r
+\r
+ for line in request.body.split('\n'):\r
+ try:\r
+ (key, value) = line.strip().split(' ', 1)\r
+ \r
+ if key in 'V' 'T' 'G' 'M' 'S' 'C' 'R' 'W':\r
+ game_meta[key] = value\r
+\r
+ if key == 'P':\r
+ # if we were working on a player record already, append\r
+ # it and work on a new one (only set team info)\r
+ if len(player_events) != 0:\r
+ players.append(player_events)\r
+ player_events = {}\r
+ \r
+ player_events[key] = value\r
+\r
+ if key == 'e':\r
+ (subkey, subvalue) = value.split(' ', 1)\r
+ player_events[subkey] = subvalue\r
+ if key == 'n':\r
+ player_events[key] = value\r
+ if key == 't':\r
+ player_events[key] = value\r
+ except:\r
+ # no key/value pair - move on to the next line\r
+ pass\r
+ \r
+ # add the last player we were working on\r
+ if len(player_events) > 0:\r
+ players.append(player_events)\r
+\r
+ return (game_meta, players)\r
+\r
+\r
+def create_player_stats(session=None, player=None, game=None, \r
+ player_events=None):\r
+ """\r
+ Creates player game and weapon stats according to what type of player\r
+ """\r
+ if 'joins' in player_events and 'matches' in player_events\\r
+ and 'scoreboardvalid' in player_events:\r
+ pgstat = create_player_game_stat(session=session, \r
+ player=player, game=game, player_events=player_events)\r
+ if not re.search('^bot#\d+$', player_events['P']):\r
+ create_player_weapon_stats(session=session, \r
+ player=player, game=game, pgstat=pgstat,\r
+ player_events=player_events)\r
+ \r
+\r
+def stats_submit(request):\r
+ """\r
+ Entry handler for POST stats submissions.\r
+ """\r
+ try:\r
+ session = DBSession()\r
+\r
+ (game_meta, players) = parse_body(request) \r
+ \r
+ # verify required metadata is present\r
+ if 'T' not in game_meta or\\r
+ 'G' not in game_meta or\\r
+ 'M' not in game_meta or\\r
+ 'S' not in game_meta:\r
+ log.debug("Required game meta fields (T, G, M, or S) missing. "\\r
+ "Can't continue.")\r
+ raise Exception("Required game meta fields (T, G, M, or S) missing.")\r
+ \r
+ has_real_players = False\r
+ for player_events in players:\r
+ if not player_events['P'].startswith('bot'):\r
+ if 'joins' in player_events and 'matches' in player_events\\r
+ and 'scoreboardvalid' in player_events:\r
+ has_real_players = True\r
+\r
+ if not has_real_players:\r
+ raise Exception("No real players found. Stats ignored.")\r
+\r
+ server = get_or_create_server(session=session, name=game_meta['S'])\r
+ gmap = get_or_create_map(session=session, name=game_meta['M'])\r
+\r
+ if 'W' in game_meta:\r
+ winner = game_meta['W']\r
+ else:\r
+ winner = None\r
+\r
+ game = create_game(session=session, \r
+ start_dt=datetime.datetime(\r
+ *time.gmtime(float(game_meta['T']))[:6]), \r
+ server_id=server.server_id, game_type_cd=game_meta['G'], \r
+ map_id=gmap.map_id, winner=winner)\r
+ \r
+ # find or create a record for each player\r
+ # and add stats for each if they were present at the end\r
+ # of the game\r
+ for player_events in players:\r
+ if 'n' in player_events:\r
+ nick = player_events['n']\r
+ else:\r
+ nick = None\r
+\r
+ player = get_or_create_player(session=session, \r
+ hashkey=player_events['P'], nick=nick)\r
+ log.debug('Creating stats for %s' % player_events['P'])\r
+ create_player_stats(session=session, player=player, game=game, \r
+ player_events=player_events)\r
+ \r
+ session.commit()\r
+ log.debug('Success! Stats recorded.')\r
+ return Response('200 OK')\r
+ except Exception as e:\r
+ session.rollback()\r
+ raise e\r
projects / xonotic / xonstat.git / commitdiff