From 585ad90d3cc59ad7d4fd306170b710699e48efda Mon Sep 17 00:00:00 2001 From: raylu Date: Sun, 20 Oct 2013 00:42:11 -0700 Subject: show more data --- db/__init__.py | 9 +++++++ server.py | 53 ++++++++++++++++++++++++++++++++++++++++-- web/templates/corporation.html | 27 +++++++++++++++++++++ web/templates/kill.html | 37 +++++++++++------------------ 4 files changed, 101 insertions(+), 25 deletions(-) create mode 100644 web/templates/corporation.html diff --git a/db/__init__.py b/db/__init__.py index 92837a2..66566d5 100644 --- a/db/__init__.py +++ b/db/__init__.py @@ -13,3 +13,12 @@ def execute(cursor, sql, *values): def query(cursor, sql, *values): execute(cursor, sql, *values) return cursor.fetchall() + +def get(cursor, sql, *values): + execute(cursor, sql, *values) + result = cursor.fetchone() + if result is None: + raise Exception('no rows returned for query: {} with values {}'.format(sql, values)) + if cursor.fetchone() is not None: + raise Exception('multiple results returned for query'.format(sql, values)) + return result diff --git a/server.py b/server.py index 8c87850..54afcc2 100755 --- a/server.py +++ b/server.py @@ -1,5 +1,6 @@ #!/usr/bin/env python3 +from collections import defaultdict import json import operator import os @@ -35,6 +36,53 @@ class SearchHandler(BaseHandler): ''', '%{}%'.format(q)) self.render('search.html', corps=corps) +class CorporationHandler(BaseHandler): + def get(self, corp_id): + with db.cursor() as c: + kills = db.query(c, ''' + SELECT DISTINCT(kills.kill_id), kill_time FROM kills + JOIN characters on characters.kill_id = kills.kill_id + WHERE corporation_id = ? + ''', corp_id) + kill_ids = list(map(operator.itemgetter('kill_id'), kills)) + char_rows = db.query(c, ''' + SELECT + kill_id, victim, final_blow, + character_id, character_name, corporation_id, corporation_name, alliance_id, alliance_name, faction_id, faction_name + FROM characters + WHERE kill_id IN ({}) + '''.format(','.join(map(str, kill_ids)))) + characters = defaultdict(dict) + for char in char_rows: + kill_id = char['kill_id'] + if char['victim']: + characters[kill_id]['victim'] = char + elif char['final_blow']: + characters[kill_id]['final_blow'] = char + for kill in kills: + chars = characters[kill['kill_id']] + kill['victim'] = chars['victim'] + kill['final_blow'] = chars['final_blow'] + self.render('corporation.html', kills=kills) + +class KillHandler(BaseHandler): + def get(self, kill_id): + with db.cursor() as c: + kill = db.get(c, ''' + SELECT kill_time, solarSystemName, security FROM kills + JOIN eve.mapSolarSystems on solar_system_id = solarSystemID + WHERE kill_id = ? + ''', kill_id) + characters = db.query(c, ''' + SELECT ship_type_id, character_id, character_name, + corporation_id, corporation_name, alliance_id, alliance_name, faction_id, faction_name + typeName + FROM characters + JOIN eve.invTypes on ship_type_id = typeID + WHERE kill_id = ? + ''', kill_id) + self.render('kill.html', kill=kill, characters=characters) + class CSSHandler(tornado.web.RequestHandler): def get(self, css_path): css_path = os.path.join(os.path.dirname(__file__), 'web', 'static', css_path) + '.ccss' @@ -42,13 +90,14 @@ class CSSHandler(tornado.web.RequestHandler): self.set_header('Content-Type', 'text/css') self.write(cleancss.convert(f)) - if __name__ == '__main__': tornado.web.Application( handlers=[ (r'/', MainHandler), (r'/search', SearchHandler), - (r"/(css/.+)\.css", CSSHandler), + (r'/corporation/(.+)', CorporationHandler), + (r'/kill/(.+)', KillHandler), + (r'/(css/.+)\.css', CSSHandler), ], template_path=os.path.join(os.path.dirname(__file__), 'web/templates'), cookie_secret=config.cookie_secret, diff --git a/web/templates/corporation.html b/web/templates/corporation.html new file mode 100644 index 0000000..0afe4c3 --- /dev/null +++ b/web/templates/corporation.html @@ -0,0 +1,27 @@ +{% extends "base.html" %} + +{% block main %} + +{% for kill in kills %} + + {{ kill['kill_time'] }} + {{ kill['victim']['character_name'] }} + {{ kill['victim']['character_name'] }} + {{ kill['victim']['corporation_name'] }} + {{ kill['victim']['corporation_name'] }} + {% if kill['victim']['alliance_id'] != 0 %} + {{ kill['victim']['alliance_name'] }} + {{ kill['victim']['alliance_name'] }} + {% end %} + {{ kill['final_blow']['character_name'] }} + {{ kill['final_blow']['character_name'] }} + {{ kill['final_blow']['corporation_name'] }} + {{ kill['final_blow']['corporation_name'] }} + {% if kill['final_blow']['alliance_id'] != 0 %} + {{ kill['final_blow']['alliance_name'] }} + {{ kill['final_blow']['alliance_name'] }} + {% end %} +
+{% end %} + +{% end %} diff --git a/web/templates/kill.html b/web/templates/kill.html index 6d4f69c..bb286bc 100644 --- a/web/templates/kill.html +++ b/web/templates/kill.html @@ -2,29 +2,20 @@ {% block main %} -
- {% for kill in kills %} - - {{ kill['killTime'] }} - {{ kill['solarSystemID'] }} - - - - - {{ kill['victim']['characterName'] }} - {{ kill['victim']['corporationName'] }} - {{ kill['victim']['allianceName'] }} - {% for attacker in kill['attackers'] %} - {% if attacker['finalBlow'] %} - {% set killer = attacker %} - {% break %} - {% end %} - {% end %} - {{ killer['characterName'] }} - {{ killer['corporationName'] }} - {{ killer['allianceName'] }} - +{{ kill['kill_time'] }}
+{{ kill['solarSystemName'] }} ({{ round(kill['security'], 1) }})
+ +{% for char in characters %} + {{ char ['typeName'] }} + {{ char['character_name'] }} + {{ char['character_name'] }} + {{ char['corporation_name'] }} + {{ char['corporation_name'] }} + {% if char['alliance_id'] != 0 %} + alliance + {{ char['alliance_name'] }} {% end %} -
+
+{% end %} {% end %} -- cgit v1.2.3