kill/item costs

1 files changed, 88 insertions, 0 deletions

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()