From fbb64e5d89a0f3369d6809f374c5791853647284 Mon Sep 17 00:00:00 2001 From: raylu Date: Tue, 22 Oct 2013 03:44:52 -0700 Subject: kill/item costs --- db/queries.py | 15 ++++--- update_costs.py | 88 +++++++++++++++++++++++++++++++++++++++++ web/static/css/corporation.ccss | 5 +++ web/static/css/kill.ccss | 7 +++- web/static/js/common.js | 8 +++- web/static/js/corporation.js | 5 +++ web/static/js/kill.js | 22 +++++++---- web/templates/corporation.html | 1 + web/templates/kill.html | 1 + 9 files changed, 135 insertions(+), 17 deletions(-) create mode 100755 update_costs.py diff --git a/db/queries.py b/db/queries.py index 0493af0..7ad56ff 100644 --- a/db/queries.py +++ b/db/queries.py @@ -13,10 +13,11 @@ def search(q): def corporation(corp_id): with db.cursor() as c: kills = db.query(c, ''' - SELECT DISTINCT(kills.kill_id), kill_time, + SELECT DISTINCT(kills.kill_id), kill_time, cost, solarSystemName as system_name, security, regionName as region FROM kills JOIN characters ON characters.kill_id = kills.kill_id + JOIN kill_costs ON kill_costs.kill_id = kills.kill_id JOIN eve.mapSolarSystems ON solar_system_id = solarSystemID JOIN eve.mapRegions ON mapSolarSystems.regionID = mapRegions.regionID WHERE corporation_id = ? @@ -56,9 +57,10 @@ def corporation(corp_id): def kill(kill_id): with db.cursor() as c: kill = db.get(c, ''' - SELECT kill_time, solarSystemName, security FROM kills + SELECT kill_time, cost, solarSystemName, security FROM kills + JOIN kill_costs ON kill_costs.kill_id = kills.kill_id JOIN eve.mapSolarSystems ON solar_system_id = solarSystemID - WHERE kill_id = ? + WHERE kills.kill_id = ? ''', kill_id) kill['kill_time'] = _format_kill_time(kill['kill_time']) @@ -82,10 +84,11 @@ def kill(kill_id): attackers.append(char) item_rows = db.query(c, ''' - SELECT type_id, flag, dropped, destroyed, singleton, - typeName AS item_name, capacity + SELECT items.type_id, flag, dropped, destroyed, singleton, + cost, typeName AS item_name, capacity FROM items - JOIN eve.invTypes ON type_id = typeID + JOIN item_costs ON item_costs.type_id = items.type_id + JOIN eve.invTypes ON items.type_id = typeID WHERE kill_id = ? ORDER BY flag ASC ''', kill_id) items = defaultdict(list) diff --git a/update_costs.py b/update_costs.py new file mode 100755 index 0000000..b2ab85f --- /dev/null +++ b/update_costs.py @@ -0,0 +1,88 @@ +#!/usr/bin/env python3 + +from decimal import Decimal +from io import StringIO +import operator +import sys +from xml.etree import ElementTree + +import requests + +import db + +def fetch_type_ids(c): + if len(sys.argv) > 1 and sys.argv[1] in ['-q', '--quick']: + type_ids = c.execute(''' + SELECT i.type_id FROM items AS i + JOIN eve.invTypes AS t ON i.type_id = t.typeID + WHERE marketGroupID is NOT NULL + UNION SELECT ship_type_id FROM characters + JOIN eve.invTypes ON ship_type_id = typeID + WHERE victim AND marketGroupID is NOT NULL + ''') + else: + type_ids = c.execute('SELECT typeID FROM eve.invTypes WHERE marketGroupID IS NOT NULL') + return set(map(operator.itemgetter(0), c.fetchall())) + +rs = requests.session() +jita_system = 30000142 +def query(type_id): + params = {'typeid': type_id, 'usesystem': jita_system} + r = rs.get('http://api.eve-central.com/api/marketstat', params=params) + try: + tree = ElementTree.parse(StringIO(r.text)) + except ElementTree.ParseError: + return 0 + value = tree.getroot().find('marketstat').find('type').find('sell').find('percentile').text + return int(Decimal(value) * 100) + +def update_kill(kill_id): + with db.conn.cursor() as c: + c.execute(''' + SELECT cost FROM characters + JOIN item_costs ON ship_type_id = item_costs.type_id + WHERE kill_id = ? AND victim + ''', (kill_id,)) + r = c.fetchone() + if r: + cost = r[0] + c.nextset() + else: + cost = 0 + c.execute(''' + SELECT SUM(cost * (dropped + destroyed)) FROM items AS i + JOIN item_costs AS ic ON i.type_id = ic.type_id WHERE kill_id = ? + ''', (kill_id,)) + r = c.fetchone() + c.nextset() + if r[0]: + cost += r[0] + c.execute('UPDATE kill_costs SET cost = ? WHERE kill_id = ?', (cost, kill_id)) + +def main(): + with db.conn.cursor() as c: + print('getting items') + type_ids = fetch_type_ids(c) + + print('updating items') + parambatch = [] + for type_id in type_ids: + value = query(type_id) + parambatch.append((type_id, value, value)) + c.executemany(''' + INSERT INTO item_costs (type_id, cost) VALUES(?, ?) + ON DUPLICATE KEY UPDATE cost = ? + ''', parambatch) + + print('getting kills') + c.execute('SELECT kill_id from kills') + + print('updating kills') + while True: + r = c.fetchone() + if r is None: + break + update_kill(r[0]) + +if __name__ == '__main__': + main() diff --git a/web/static/css/corporation.ccss b/web/static/css/corporation.ccss index c077808..575a040 100644 --- a/web/static/css/corporation.ccss +++ b/web/static/css/corporation.ccss @@ -17,3 +17,8 @@ table#kills: width: 64px th.killer: width: 250px + th.value: + width: 62px + + td:nth-child(7): + text-align: right diff --git a/web/static/css/kill.ccss b/web/static/css/kill.ccss index 6a97f32..1cfc2ff 100644 --- a/web/static/css/kill.ccss +++ b/web/static/css/kill.ccss @@ -117,9 +117,11 @@ table#items: th:nth-child(1): width: 33px th:nth-child(2): - width: 417px + width: 300px th:nth-child(3): width: 50px + th:nth-child(4): + width: 117px .slot: background-color: #222 @@ -134,6 +136,9 @@ table#items: text-align: right font-weight: bold padding-right: 8px + td:nth-child(4): + text-align: right + padding-right: 8px .dropped: background-color: #041 .destroyed: diff --git a/web/static/js/common.js b/web/static/js/common.js index 547c349..e7a4e75 100644 --- a/web/static/js/common.js +++ b/web/static/js/common.js @@ -1,16 +1,20 @@ Object.append(window.ykill, { - 'api': function (path, cb) { + 'api': function(path, cb) { new Request.JSON({ 'url': ykill.api_host + path, 'onSuccess': cb, }).get(); }, - 'portrait': function (id, text, img_dir, img_suffix) { + 'portrait': function(id, text, img_dir, img_suffix) { var img = new Element('img', { 'src': '//image.eveonline.com/' + img_dir + '/' + id + img_suffix, 'alt': text, }); return img; }, + + 'format_isk': function(isk) { + return isk.toLocaleString('en-US', {'minimumFractionDigits': 2, 'maximumFractionDigits': 2}); + }, }); diff --git a/web/static/js/corporation.js b/web/static/js/corporation.js index 73d1b4b..606ce3e 100644 --- a/web/static/js/corporation.js +++ b/web/static/js/corporation.js @@ -59,6 +59,11 @@ window.addEvent('domready', function() { td.appendText(' / ' + final_blow['faction_name']); tr.grab(td); + td = new Element('td'); + var millions = kill['cost'] / (100 * 1000000); + td.appendText(ykill.format_isk(millions)); + tr.grab(td); + table.grab(tr); }); }); diff --git a/web/static/js/kill.js b/web/static/js/kill.js index a37c30c..baac084 100644 --- a/web/static/js/kill.js +++ b/web/static/js/kill.js @@ -1,7 +1,6 @@ window.addEvent('domready', function() { var kill_id = document.location.pathname.split('/').getLast(); ykill.api('/kill/' + kill_id, function(data) { - var kill = data['kill']; var victim = data['victim']; document.title += ' - ' + victim['character_name'] + ' - ' + victim['ship_name']; @@ -43,12 +42,18 @@ window.addEvent('domready', function() { ), new Element('td', {'html': victim['faction_name']}) )); - table.grab(new Element('tr').adopt( - new Element('td').grab( - ykill.portrait(victim['ship_type_id'], victim['ship_name'], 'type', '_64.png') + table.adopt( + new Element('tr').adopt( + new Element('td').grab( + ykill.portrait(victim['ship_type_id'], victim['ship_name'], 'type', '_64.png') + ), + new Element('td', {'html': victim['ship_name']}) ), - new Element('td', {'html': victim['ship_name']}) - )); + new Element('tr').adopt( + new Element('td', {'html': 'cost'}), + new Element('td', {'html': ykill.format_isk(kill['cost'] / 100)}) + ) + ); var items = data['items']; var div = $('ship'); @@ -98,7 +103,7 @@ window.addEvent('domready', function() { if (!items[slot]) return; table.grab(new Element('tr').grab( - new Element('td', {'html': slot, 'colspan': 3, 'class': 'slot'}) + new Element('td', {'html': slot, 'colspan': 4, 'class': 'slot'}) )); if (slot == 'high') { var highs = {'dropped': {}, 'destroyed': {}}; @@ -129,7 +134,8 @@ window.addEvent('domready', function() { }) ), new Element('td', {'html': item['item_name']}), - new Element('td', {'html': count, 'class': item_class}) + new Element('td', {'html': count, 'class': item_class}), + new Element('td', {'html': ykill.format_isk(item['cost'] / 100)}) )); }); }); diff --git a/web/templates/corporation.html b/web/templates/corporation.html index 8e8e7fa..c8550d7 100644 --- a/web/templates/corporation.html +++ b/web/templates/corporation.html @@ -18,6 +18,7 @@ victim killer(s) + value diff --git a/web/templates/kill.html b/web/templates/kill.html index 99a4039..67a8012 100644 --- a/web/templates/kill.html +++ b/web/templates/kill.html @@ -69,6 +69,7 @@ + -- cgit v1.2.3