1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
#!/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)
au79_cost = None
def update_kill(kill_id):
with db.conn.cursor() as c:
c.execute('''
SELECT ship_type_id, cost FROM characters
LEFT JOIN item_costs ON ship_type_id = item_costs.type_id
WHERE kill_id = ? AND victim
''', (kill_id,))
r = c.fetchone()
if r[1] is not None:
cost = r[1]
else:
cost = 0
c.nextset()
if r[0] == 33328: # Capsule - Genolution 'Auroral' 197-variant
cost -= au79_cost
# singleton is 0 normally and for BPOs and 2 for BPCs
# we want to divide by 1 for BPOs and by 1000 for BPCs
c.execute('''
SELECT SUM(cost * (dropped + destroyed) / (singleton * 499.5 + 1))
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 += int(r[0])
if cost < 0:
cost += au79_cost
print('goddamnit CCP', kill_id)
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)
c.execute('SELECT cost FROM item_costs WHERE type_id = 33329') # Genolution 'Auroral' AU-79
global au79_cost
au79_cost = c.fetchone()[0]
c.nextset()
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()
|
