def validate(self):
if str(self.date) == '1000-01-01': return ['date missing']
max_sojourner = (self.date - sojourner_start).days + 1
max_guardian = (self.date - game_start).days + 1
apdiff = exec_mysql("SELECT apdiff FROM agents WHERE `name` = '{0}';".format(self.name))
if apdiff: self.apdiff = apdiff[0][0]
reasons = []
# this seems to be a more common bug, and until there is some action agents can take to fix it, it wont be flagged
#if self.min_level > self.level + 1: # +1 is for special case where agents just dinged and scanner hasn't caught up yet. better to let some slip through than to flag an exited agent's ding
# reasons.append( 'reported level %s < %s' % (self.level, self.min_level) )
if self.guardian > max_guardian:
reasons.append( 'guardian %s > %s' % (self.guardian, max_guardian) )
if self.sojourner > max(0, max_sojourner):
reasons.append( 'sojourner %s > %s' % (self.sojourner, max_sojourner) )
if game_start > self.date:
reasons.append( 'date %s < %s' % (self.date, game_start) )
if self.date > today+datetime.timedelta(days=1):
reasons.append( 'date %s > %s' % (self.date, today+datetime.timedelta(days=1)) )
if (self.mind_controller/2) > self.connector:
reasons.append( 'connector:mind controller %s < %s/2' % (self.connector, self.mind_controller) )
if self.explorer > self.hacker+self.builder+self.engineer+self.connector:
reasons.append( 'explorer:H+B+E+C %s > %s' % (self.explorer, self.hacker+self.builder+self.engineer+self.connector) )
if self.pioneer > self.explorer:
reasons.append( 'pioneer:explorer %s > %s' % (self.pioneer, self.explorer) )
if self.pioneer > self.liberator:
reasons.append( 'pioneer:liberator %s > %s' % (self.pioneer, self.liberator) )
if self.liberator > self.builder:
reasons.append( 'liberator:builder %s > %s' % (self.liberator, self.builder) )
if (self.salvator/2) > self.disruptor:
reasons.append( 'disruptor:salvator %s < %s/2' % (self.disruptor, self.salvator) )
if self.disruptor > self.purifier:
reasons.append( 'disruptor:purifier %s > %s' % (self.disruptor, self.purifier) )
if self.neutralizer > self.purifier:
reasons.append( 'neutralizer:purifier %s > %s' % (self.neutralizer, self.purifier) )
if (self.translator/15) > self.hacker:
reasons.append( 'hacker:translator %s < %s/15' % (self.hacker, self.translator) )
# there was a missionday where they didnt require missions at all. 100 UPV would get you the badge
# http://www.pref.iwate.jp/dbps_data/_material_/_files/000/000/031/399/morioka0621.pdf (in japanese, on page 2)
#if self.missionday > self.specops:
# reasons.append( 'missionday:specops %s > %s' % (self.missionday, self.specops) )
# this catches faction flippers unfortunately
#if self.min_ap > self.ap:
# reasons.append( 'ap:min_ap %s < %s' % (self.ap, self.min_ap) )
#if self.apdiff > self.ap-self.min_ap:
# reasons.append( 'apdiff %s > %s' % (self.apdiff, self.ap-self.min_ap) )
if not reasons:
exec_mysql("UPDATE agents SET apdiff={0} WHERE `name`='{1}';".format(self.ap-self.min_ap, self.name))
return reasons
def table_load(self, **row):
self.date = parse(
row['last_submit'] if row['last_submit']
and not row['last_submit'].startswith('0') else '1000/1/1').date()
self.name = row['name'][:16]
self.faction = row['faction']
self.level = row['level']
self.ap = row['ap']
self.explorer = row['explorer']
self.seer = row['seer']
self.recon = row['recon']
self.collector = row['collector']
self.trekker = row['trekker']
self.builder = row['builder']
self.connector = row['connector']
self.mind_controller = row['mind-controller']
self.illuminator = row['illuminator']
self.binder = row['binder']
self.country_master = row['country-master']
self.recharger = row['recharger']
self.liberator = row['liberator']
self.pioneer = row['pioneer']
self.engineer = row['engineer']
self.purifier = row['purifier']
self.neutralizer = row['neutralizer']
self.disruptor = row['disruptor']
self.salvator = row['salvator']
self.guardian = row['guardian']
self.smuggler = row['smuggler']
self.link_master = row['link-master']
self.controller = row['controller']
self.field_master = row['field-master']
self.specops = row['specops']
self.missionday = row['missionday']
self.hacker = row['hacker']
self.translator = row['translator']
self.sojourner = row['sojourner']
self.recruiter = row['recruiter']
self.magnusbuilder = row['magnusbuilder']
agent_id = exec_mysql(
"SELECT idagents FROM agents WHERE name = '{0}';".format(
self.name))
if agent_id:
self.agent_id = agent_id[0][0]
else:
sql = '''INSERT INTO `agents` SET `name`='{0}', `faction`='{1}';'''.format(
self.name, self.faction)
exec_mysql(sql)
logging.info('new entry created for {} in agents table'.format(
self.name))
self.agent_id = exec_mysql(
"SELECT idagents FROM agents WHERE name = '{0}';".format(
self.name))[0][0]
def update_group_names(group):
db = dict(exec_mysql('SELECT url, `name` FROM groups WHERE url IS NOT NULL;'))
web = dict(groups())
allgood = True
for gid in web:
if web[gid] != db[gid]:
allgood = False
print('{} was named "{}" is now "{}"'.format(gid, db[gid], web[gid]))
if input('Update the database? (y/N) ').lower().startswith('y'):
exec_mysql('UPDATE groups SET `name`="{}" WHERE url="{}" AND `name`="{}"; '.format(web[gid], gid, db[gid]))
if allgood:
print('\nAll group names match\n')
def update_group_names(group):
db = dict(exec_mysql('SELECT url, `name` FROM `groups` WHERE url IS NOT NULL;'))
web = dict(groups())
allgood = True
for gid in web:
if web[gid] != db[gid]:
allgood = False
print(f'{gid} was named "{db[gid]}" is now "{web[gid]}"')
if input('Update the database? (y/N) ').lower().startswith('y'):
exec_mysql(f'UPDATE `groups` SET `name`="{web[gid]}" WHERE url="{gid}" AND `name`="{db[gid]}";')
if allgood:
print('\nAll group names match\n')
def db_load(
self, row
): # ATTENTION: DO NOT simply use 'SELECT * FROM stats;' here. It will end in tears.
row = Row(*row) # your boat...
self.name = row.name
self.date = row.date if row.date else datetime.date(1000, 1, 1)
#self.flag = row.flag
#self.min_ap = row.min_ap
self.ap = row.ap
self.level = row.level
self.explorer = row.explorer
self.seer = row.seer
self.recon = row.recon
self.collector = row.collector
self.trekker = row.trekker
self.builder = row.builder
self.connector = row.connector
self.mind_controller = row.mind_controller
self.illuminator = row.illuminator
self.binder = row.binder
self.country_master = row.country_master
self.recharger = row.recharger
self.liberator = row.liberator
self.pioneer = row.pioneer
self.engineer = row.engineer
self.purifier = row.purifier
self.neutralizer = row.neutralizer
self.disruptor = row.disruptor
self.salvator = row.salvator
self.guardian = row.guardian
self.smuggler = row.smuggler
self.link_master = row.link_master
self.controller = row.controller
self.field_master = row.field_master
self.specops = row.specops
self.missionday = row.missionday
self.hacker = row.hacker
self.translator = row.translator
self.sojourner = row.sojourner
self.recruiter = row.recruiter
self.magnusbuilder = row.magnusbuilder
if str(self.name).startswith('@'):
self.agent_id = exec_mysql(
"SELECT idagents FROM agents WHERE name = '{0}';".format(
self.name[:16]))[0][0]
else: # probably good enough, but if this still blows up then make sure it's a numeric id and not just a name missing its @
self.agent_id = self.name
self.name = exec_mysql(
"SELECT name FROM agents WHERE idagents = '{0}';".format(
self.agent_id))[0][0]
def get_chart_data(cmd='start'):
data = list(exec_mysql('SELECT ping, pong, `name`, `status`, portal_url FROM portals2 WHERE NOT (portal_url IS NOT NULL AND `status` = 0);'))
data.extend(exec_mysql('SELECT ping, pong, `name`, -1, portal_url FROM portals2 WHERE portal_url IS NOT NULL AND `status` = 0;'))
first_run = exec_mysql('SELECT min(ping) FROM portals2;')[0][0]
dataTable = []
status_name = {True: 'Accepted',
False: 'Rejected',
None: 'Pending',
-1: 'Duplicate'}
status_color = {True: '#3366CC',
False: '#DC3912',
None: '#FF9900',
-1: '#990099'}
if cmd == 'start' or cmd == None:
data.sort(key=lambda x: x[0] if x[0] else first_run)
if cmd == 'end':
data.sort(key=lambda x: x[1] if x[1] else datetime.datetime.utcnow())
if cmd == 'days':
data.sort(key=lambda x: (get_timespan(x[0], x[1]), x[0]))
data.reverse()
colors = []
for row in data:
if row[3] not in colors:
colors.append(row[3])
for i, color in enumerate(colors):
colors[i] = status_color[color]
now = datetime.datetime.utcnow()
for ping, pong, name, status, portal_url in data:
fillings = {'id': status_name[status],
'name': '{} ({} days)'.format(name.replace("'", "\\'"), get_timespan(ping, pong)),
'ping': ping.isoformat() if ping else first_run.isoformat(),
'pong': pong.isoformat() if pong else now.isoformat()
}
dataTable.append( fillings )
return {'data': dataTable,
'colors': colors,
'count': Counter(list(zip(*data))[3]),
'start_url': url_for('portals.start'),
'end_url': url_for('portals.end'),
'days_url': url_for('portals.days'),
'json_url': url_for('portals.get_json'),
'summary_url': url_for('portals.get_summary_data'),
'histogram_url': url_for('portals.get_histogram')}
def set_flag(agent, date, flag=1):
#date needs to be yyyy-mm-dd
if not agent.startswith('@'):
agent = '@' + agent
print(agent)
sql1 = "SELECT idagents FROM agents WHERE `name`='{}';".format(agent)
print(sql1)
agentid = exec_mysql(sql1)[0][0]
print(agentid)
sql2 = "UPDATE `agent_stats`.`stats` SET `flag`='{flag}' WHERE `idagents`='{agentid}' and`date`='{date}';".format(
agentid=agentid, date=date, flag=flag)
print(sql2)
exec_mysql(sql2)
print('done')
def get_json():
output = []
for ping, pong, name, status in exec_mysql('SELECT ping, pong, `name`, `status` FROM portals2;'):
output.append({'ping': ping.isoformat() if ping else None,
'pong': pong.isoformat() if pong else None,
'name': name,
'status': status})
return json.dumps(output, indent=4, separators=(',', ': '))
def validate_group(group):
groups = [
name for row in exec_mysql('SELECT name, url FROM groups;')
for name in row
]
groups.append(None)
if group in groups:
return group
logging.info(f'Valid groups are {groups[::2]}')
def table_load(self, **row):
self.date = parse(row['last_submit'] if row['last_submit'] and not row['last_submit'].startswith('0') else '1000/1/1').date()
self.name = row['name']
self.faction = row['faction']
self.level = row['level']
self.ap = row['ap']
self.explorer = row['explorer']
self.seer = row['seer']
self.collector = row['collector']
self.trekker = row['trekker']
self.builder = row['builder']
self.connector = row['connector']
self.mind_controller = row['mind-controller']
self.illuminator = row['illuminator']
self.binder = row['binder']
self.country_master = row['country-master']
self.recharger = row['recharger']
self.liberator = row['liberator']
self.pioneer = row['pioneer']
self.engineer = row['engineer']
self.purifier = row['purifier']
self.neutralizer = row['neutralizer']
self.disruptor = row['disruptor']
self.salvator = row['salvator']
self.guardian = row['guardian']
self.smuggler = row['smuggler']
self.link_master = row['link-master']
self.controller = row['controller']
self.field_master = row['field-master']
self.specops = row['specops']
self.missionday = row['missionday']
self.hacker = row['hacker']
self.translator = row['translator']
self.sojourner = row['sojourner']
self.recruiter = row['recruiter']
agent_id = exec_mysql("SELECT idagents FROM agents WHERE name = '{0}';".format(self.name))
if agent_id:
self.agent_id = agent_id[0][0]
else:
sql = '''INSERT INTO `agents` SET `name`='{0}', `faction`='{1}';'''.format(self.name, self.faction)
exec_mysql(sql)
logging.info('new entry created for {} in agents table'.format(self.name))
self.agent_id = exec_mysql("SELECT idagents FROM agents WHERE name = '{0}';".format(self.name))[0][0]
def db_load(self, row): # ATTENTION: DO NOT simply use 'SELECT * FROM stats;' here. It will end in tears.
row = Row(*row) # your boat...
self.name = row.name
self.date = row.date if row.date else datetime.date(1000, 1, 1)
#self.flag = row.flag
#self.min_ap = row.min_ap
self.ap = row.ap
self.level = row.level
self.explorer = row.explorer
self.seer = row.seer
self.collector = row.collector
self.trekker = row.trekker
self.builder = row.builder
self.connector = row.connector
self.mind_controller = row.mind_controller
self.illuminator = row.illuminator
self.binder = row.binder
self.country_master = row.country_master
self.recharger = row.recharger
self.liberator = row.liberator
self.pioneer = row.pioneer
self.engineer = row.engineer
self.purifier = row.purifier
self.neutralizer = row.neutralizer
self.disruptor = row.disruptor
self.salvator = row.salvator
self.guardian = row.guardian
self.smuggler = row.smuggler
self.link_master = row.link_master
self.controller = row.controller
self.field_master = row.field_master
self.specops = row.specops
self.missionday = row.missionday
self.hacker = row.hacker
self.translator = row.translator
self.sojourner = row.sojourner
self.recruiter = row.recruiter
if str(self.name).startswith('@'):
self.agent_id = exec_mysql("SELECT idagents FROM agents WHERE name = '{0}';".format(self.name))[0][0]
else: # probably good enough, but if this still blows up then make sure its a numeric id and not just a name missing its @
self.agent_id = self.name
self.name = exec_mysql("SELECT name FROM agents WHERE idagents = '{0}';".format(self.agent_id))[0][0]
def get_groups(group=None):
if group in ('smurfs', 'frogs', 'all', None):
group_id, group_name = None, None
elif re.fullmatch(r'([0-9a-f]{14}\.[\d]{8})', group):
group_id = group
group_name = groups()[group]
else:
group_id = exec_mysql('SELECT url FROM groups WHERE `name` = "{}"'.format(group))[0][0]
group_name = group
return group_id, group_name
def get_json():
output = []
for ping, pong, name, status in exec_mysql(
'SELECT ping, pong, `name`, `status` FROM portals2;'):
output.append({
'ping': ping.isoformat() if ping else None,
'pong': pong.isoformat() if pong else None,
'name': name,
'status': status
})
return json.dumps(output, indent=4, separators=(',', ': '))
def get_start_date():
dates = list(exec_mysql('SELECT max(ping), max(pong) FROM portals2;')[0])
#dates=[]
dates.append(datetime.datetime(2012, 11, 15)) # closed beta begin date
#dates.append(datetime.datetime(2015, 5, 12)) # debuging date
#return dates[-1].date()
dates = list(filter(lambda x: bool(x), dates))
if len(dates) > 1:
return max(dates).date() - datetime.timedelta(days=1)
else:
return dates[0].date() - datetime.timedelta(days=1)
def get_groups(group=None):
if group in ('smurfs', 'frogs', 'all', None):
group_id, group_name = None, None
elif re.fullmatch(r'([0-9a-f]{14}\.[\d]{8})', group):
group_id = group
group_name = groups()[group]
else:
group_id = exec_mysql(
'SELECT url FROM groups WHERE `name` = "{}"'.format(group))[0][0]
group_name = group
return group_id, group_name
def get_portal_info(date):
try:
date = datetime.datetime.utcfromtimestamp(int(date)/1000.0) # attempt to make sure it's date-like (rather than SQLi-like)
data = exec_mysql('''SELECT ping, pong, `name`, `status`, image_url, portal_url
FROM portals2
WHERE ping = '%s';''' % date.strftime("%Y-%m-%d %H:%M:%S"))[0]
data = dict(zip(['ping', 'pong', 'name', 'status', 'image_url', 'portal_url'], data))
data['days'] = get_timespan(data['ping'], data['pong'])
except (IndexError, ValueError):
data = {}
return render_template('detail.html', data=data)
def get_summary_data():
data = exec_mysql('''SELECT ping, pong, `name`, `status`, image_url, portal_url, notes
FROM portals2
ORDER BY ping''')
cols = ['ping', 'pong', 'name', 'status', 'image_url', 'portal_url', 'notes']
output = []
for row in data:
r = dict(zip(cols, row))
r['days'] = get_timespan(r['ping'], r['pong'])
r['ping'] = r['ping'].isoformat() if r['ping'] else None
r['pong'] = r['pong'].isoformat() if r['pong'] else None
output.append(r)
return render_template('summary.html', **{'data': output})
def get_groups(group=None):
group_id, group_name = None, None
if group not in ('smurfs', 'frogs', 'all', None):
group_id = group
if not re.fullmatch(r'([0-9a-f]{14}\.[\d]{8})', group_id):
group_id = exec_mysql(f'SELECT url FROM `groups` WHERE `name` = "{group}"')[0][0]
try:
group_name = groups()[group_id]
except KeyError:
logging.error(f'You are not a member of {group} anymore')
group_id = None
return group_id, group_name
def get_summary_data():
data = exec_mysql(
'''SELECT ping, pong, `name`, `status`, image_url, portal_url, notes
FROM portals2
ORDER BY ping''')
cols = [
'ping', 'pong', 'name', 'status', 'image_url', 'portal_url', 'notes'
]
output = []
for row in data:
r = dict(zip(cols, row))
r['days'] = get_timespan(r['ping'], r['pong'])
r['ping'] = r['ping'].isoformat() if r['ping'] else None
r['pong'] = r['pong'].isoformat() if r['pong'] else None
output.append(r)
return render_template('summary.html', **{'data': output})
def get_portal_info(date):
try:
date = datetime.datetime.utcfromtimestamp(
int(date) / 1000.0
) # attempt to make sure it's date-like (rather than SQLi-like)
data = exec_mysql(
'''SELECT ping, pong, `name`, `status`, image_url, portal_url
FROM portals2
WHERE ping = '%s';''' %
date.strftime("%Y-%m-%d %H:%M:%S"))[0]
data = dict(
zip(['ping', 'pong', 'name', 'status', 'image_url', 'portal_url'],
data))
data['days'] = get_timespan(data['ping'], data['pong'])
except (IndexError, ValueError):
data = {}
return render_template('detail.html', data=data)
def collate_agents():
logging.info('collate agents')
general_groups = dict(exec_mysql("SELECT name, idgroups FROM `groups` WHERE name IN ('smurfs', 'frogs', 'all');"))
for agent_id, faction in exec_mysql('SELECT idagents, faction FROM agents;'):
faction = 'frogs' if faction == 'enl' else 'smurfs'
sql = f'''INSERT INTO `membership`
VALUES ('{agent_id}', '{general_groups['all']}')
ON DUPLICATE KEY UPDATE idagents=idagents;'''
exec_mysql(sql)
sql = f'''INSERT INTO `membership`
VALUES ('{agent_id}', '{general_groups[faction]}')
ON DUPLICATE KEY UPDATE idagents=idagents;'''
exec_mysql(sql)
def colate_agents():
logging.info('colate agents')
general_groups = dict(exec_mysql("SELECT name, idgroups FROM groups WHERE name IN ('smurfs', 'frogs', 'all');"))
for agent_id, name, faction in exec_mysql('select idagents, name, faction from agents;'):
faction = 'frogs' if faction == 'enl' else 'smurfs'
sql = '''INSERT INTO `membership`
VALUES ('{}', '{}')
ON DUPLICATE KEY UPDATE idagents=idagents;'''.format(agent_id, general_groups['all'])
exec_mysql(sql)
sql = '''INSERT INTO `membership`
VALUES ('{}', '{}')
ON DUPLICATE KEY UPDATE idagents=idagents;'''.format(agent_id, general_groups[faction])
exec_mysql(sql)
def collate_agents():
logging.info('collate agents')
general_groups = dict(
exec_mysql(
"SELECT name, idgroups FROM groups WHERE name IN ('smurfs', 'frogs', 'all');"
))
for agent_id, name, faction in exec_mysql(
'select idagents, name, faction from agents;'):
faction = 'frogs' if faction == 'enl' else 'smurfs'
sql = '''INSERT INTO `membership`
VALUES ('{}', '{}')
ON DUPLICATE KEY UPDATE idagents=idagents;'''.format(
agent_id, general_groups['all'])
exec_mysql(sql)
sql = '''INSERT INTO `membership`
VALUES ('{}', '{}')
ON DUPLICATE KEY UPDATE idagents=idagents;'''.format(
agent_id, general_groups[faction])
exec_mysql(sql)
def summary(group='all', days=7):
snarf(group)
group_id, group_name = get_groups(group)
if not group_id:
group_id = {'all': 1, 'smurfs': 2, 'frogs': 3}.get(group, None)
headers = ('explorer', 'seer', 'recon', 'trekker', 'builder', 'connector',
'mind_controller', 'illuminator', 'recharger', 'liberator',
'pioneer', 'engineer', 'purifier', 'guardian', 'specops',
'missionday', 'hacker', 'translator', 'sojourner', 'recruiter',
'magnusbuilder')
sql_before = '''SELECT x.name, s.`date`, `level`, ap, explorer, seer, recon, trekker, builder, connector, `mind-controller` mind_controller, illuminator,
recharger, liberator, pioneer, engineer, purifier, guardian, specops, missionday, hacker, translator, sojourner, recruiter, magnusbuilder
FROM (
SELECT a.name name, s.idagents id, MAX(s.date) AS date
FROM agents a, stats s, membership m, groups g
WHERE a.idagents = s.idagents AND
s.idagents = m.idagents AND
m.idgroups = g.idgroups AND
g.`url` = '{}' AND
s.flag != 1 AND
date < ( CURDATE() - INTERVAL {} DAY )
GROUP BY id ) x
JOIN stats s ON x.id = s.idagents AND x.date = s.date
'''.format(group_id, days)
baseline = {}
for row in exec_mysql(sql_before):
agent = row[0]
if row[1]: # if has date. filters out the agents with rows of all 0s
baseline[agent] = {
'date': row[1],
'level': row[2],
'ap': row[3],
'badges': get_badges(dict(zip(headers, row[4:])))
}
sql_now = '''SELECT x.name, s.`date`, `level`, ap, explorer, seer, recon, trekker, builder, connector, `mind-controller` mind_controller, illuminator,
recharger, liberator, pioneer, engineer, purifier, guardian, specops, missionday, hacker, translator, sojourner, recruiter, magnusbuilder
FROM (
SELECT a.name name, s.idagents id, MAX(s.date) AS date
FROM agents a, stats s, membership m, groups g
WHERE a.idagents = s.idagents AND
s.idagents = m.idagents AND
m.idgroups = g.idgroups AND
g.`url` = '{}' AND
s.flag != 1 AND
date >= ( CURDATE() - INTERVAL {} DAY )
GROUP BY id ) x
JOIN stats s ON x.id = s.idagents AND x.date = s.date
'''.format(group_id, days)
output = []
footnote = ''
for row in exec_mysql(sql_now):
agent = row[0]
if agent in baseline:
date_old = baseline[agent]['date']
date_new = row[1]
level_old = baseline[agent]['level']
level_new = row[2]
ap_old = baseline[agent]['ap']
ap_new = row[3]
ap_40m_old = int(ap_old) // 40000000
ap_40m_new = int(ap_new) // 40000000
badges_old = baseline[agent]['badges']
badges_new = get_badges(dict(zip(headers, row[4:])))
changes = OrderedDict()
if badges_old != badges_new:
changes.update(new_badges(badges_old, badges_new))
if ap_40m_old != ap_40m_new:
changes['ap'] = [
'{} MILLION'.format((l + 1) * 40)
for l in range(ap_40m_old, ap_40m_new)
]
if level_old < level_new:
changes['level'] = [
str(l + 1) for l in range(level_old, level_new)
]
if changes:
earnings = englishify(changes)
today = datetime.date.today()
stale = today - datetime.timedelta(days=days * 2)
note = ''
if date_old < stale:
note = '¹' # chcp 65001
footnote = '¹Start date more than 2 %s ago' % (
'weeks' if days == 7 else 'months', )
if today - date_old > datetime.timedelta(
days=365
): # close enough. no one cares about leap years
template = '*{}* earned {} sometime between {old.month}/{old.day}/{old.year}{} and {new.month}/{new.day}/{new.year}'
else:
template = '*{}* earned {} sometime between {old.month}/{old.day}{} and {new.month}/{new.day}'
output.append(
template.format(agent,
earnings,
note,
old=date_old,
new=date_new))
output = sorted(output, key=lambda s: s.lower())
if footnote:
output.append(footnote)
return '\n'.join(output)
def summary(group='all', days=7):
snarf(group)
group_id, group_name = get_groups(group)
if not group_id:
group_id = {'all': 1, 'smurfs': 2, 'frogs':3}.get(group, None)
headers = ('explorer',
'seer',
'trekker',
'builder',
'connector',
'mind_controller',
'illuminator',
'recharger',
'liberator',
'pioneer',
'engineer',
'purifier',
'guardian',
'specops',
'missionday',
'hacker',
'translator',
'sojourner',
'recruiter')
sql_before = '''SELECT x.name, s.`date`, `level`, ap, explorer, seer, trekker, builder, connector, `mind-controller` mind_controller, illuminator,
recharger, liberator, pioneer, engineer, purifier, guardian, specops, missionday, hacker, translator, sojourner, recruiter
FROM (
SELECT a.name name, s.idagents id, MAX(s.date) AS date
FROM agents a, stats s, membership m, groups g
WHERE a.idagents = s.idagents AND
s.idagents = m.idagents AND
m.idgroups = g.idgroups AND
g.`url` = '{}' AND
s.flag != 1 AND
date < ( CURDATE() - INTERVAL {} DAY )
GROUP BY id ) x
JOIN stats s ON x.id = s.idagents AND x.date = s.date
'''.format(group_id, days)
baseline = {}
for row in exec_mysql(sql_before):
agent = row[0]
if row[1]: # if has date. filters out the agents with rows of all 0s
baseline[agent] = {'date': row[1], 'level': row[2], 'ap': row[3],
'badges': get_badges(dict(zip(headers, row[4:])))}
sql_now = '''SELECT x.name, s.`date`, `level`, ap, explorer, seer, trekker, builder, connector, `mind-controller` mind_controller, illuminator,
recharger, liberator, pioneer, engineer, purifier, guardian, specops, missionday, hacker, translator, sojourner, recruiter
FROM (
SELECT a.name name, s.idagents id, MAX(s.date) AS date
FROM agents a, stats s, membership m, groups g
WHERE a.idagents = s.idagents AND
s.idagents = m.idagents AND
m.idgroups = g.idgroups AND
g.`url` = '{}' AND
s.flag != 1 AND
date >= ( CURDATE() - INTERVAL {} DAY )
GROUP BY id ) x
JOIN stats s ON x.id = s.idagents AND x.date = s.date
'''.format(group_id, days)
output = []
footnote = ''
for row in exec_mysql(sql_now):
agent = row[0]
if agent in baseline:
date_old = baseline[agent]['date']
date_new = row[1]
level_old = baseline[agent]['level']
level_new = row[2]
ap_old = baseline[agent]['ap']
ap_new = row[3]
ap_40m_old = int(ap_old)//40000000
ap_40m_new = int(ap_new)//40000000
badges_old = baseline[agent]['badges']
badges_new = get_badges(dict(zip(headers, row[4:])))
changes = OrderedDict()
if badges_old != badges_new:
changes.update(new_badges(badges_old, badges_new))
if ap_40m_old != ap_40m_new:
changes['ap'] = ['{} MILLION'.format((l+1)*40) for l in range(ap_40m_old, ap_40m_new)]
if level_old < level_new:
changes['level'] = [str(l+1) for l in range(level_old, level_new)]
if changes:
earnings = englishify(changes)
stale = datetime.date.today() - datetime.timedelta(days=days*2)
note = ''
if date_old < stale:
note = '¹' # chcp 65001
footnote = '¹Start date more than 2 %s ago' % ('weeks' if days == 7 else 'months',)
if date_new - date_old > datetime.timedelta(days=365): # close enough. no one cares about leap years
template = '*{}* earned {} sometime between {old.month}/{old.day}/{old.year}{} and {new.month}/{new.day}/{new.year}'
else:
template = '*{}* earned {} sometime between {old.month}/{old.day}{} and {new.month}/{new.day}'
output.append(template.format(agent, earnings, note, old=date_old, new=date_new))
output = sorted(output, key=lambda s: s.lower())
if footnote:
output.append(footnote)
return '\n'.join(output)
def save(self):
self.flag, self.min_ap # hack to make sure these are in the cache
sql = '''INSERT INTO `stats`
SET idagents={agent_id},
`date`='{date}',
`level`='{level}',
ap='{ap}',
explorer='{explorer}',
seer='{seer}',
trekker='{trekker}',
builder='{builder}',
connector='{connector}',
`mind-controller`='{mind_controller}',
illuminator='{illuminator}',
recharger='{recharger}',
liberator='{liberator}',
pioneer='{pioneer}',
engineer='{engineer}',
purifier='{purifier}',
guardian='{guardian}',
specops='{specops}',
missionday='{missionday}',
hacker='{hacker}',
translator='{translator}',
sojourner='{sojourner}',
recruiter='{recruiter}',
collector='{collector}',
binder='{binder}',
`country-master`='{country_master}',
neutralizer='{neutralizer}',
disruptor='{disruptor}',
salvator='{salvator}',
smuggler='{smuggler}',
`link-master`='{link_master}',
controller='{controller}',
`field-master`='{field_master}',
flag={flag},
`min-ap`='{min_ap}'
ON DUPLICATE KEY UPDATE `level`='{level}',
ap='{ap}',
explorer='{explorer}',
seer='{seer}',
trekker='{trekker}',
builder='{builder}',
connector='{connector}',
`mind-controller`='{mind_controller}',
illuminator='{illuminator}',
recharger='{recharger}',
liberator='{liberator}',
pioneer='{pioneer}',
engineer='{engineer}',
purifier='{purifier}',
guardian='{guardian}',
specops='{specops}',
missionday='{missionday}',
hacker='{hacker}',
translator='{translator}',
sojourner='{sojourner}',
recruiter='{recruiter}',
collector='{collector}',
binder='{binder}',
`country-master`='{country_master}',
neutralizer='{neutralizer}',
disruptor='{disruptor}',
salvator='{salvator}',
smuggler='{smuggler}',
`link-master`='{link_master}',
controller='{controller}',
`field-master`='{field_master}',
flag={flag},
`min-ap`='{min_ap}';'''.format(**self.__dict__)
self.changed = exec_mysql(sql)
web = dict(groups())
allgood = True
for gid in web:
if web[gid] != db[gid]:
allgood = False
print('{} was named "{}" is now "{}"'.format(gid, db[gid], web[gid]))
if input('Update the database? (y/N) ').lower().startswith('y'):
exec_mysql('UPDATE groups SET `name`="{}" WHERE url="{}" AND `name`="{}"; '.format(web[gid], gid, db[gid]))
if allgood:
print('\nAll group names match\n')
if __name__ == '__main__':
parser = argparse.ArgumentParser(description='Tools for agent-stats admins')
parser.add_argument('action', help='task to perform', choices=['snarf', 'check_for_applicants', 'weekly', 'monthly', 'update_group_names', 'summary', 'test'])
parser.add_argument('-n', '--number', default=10, type=int, help='number of ranks to show')
parser.add_argument('-g', '--group', help='group to focus on', choices=[name for row in exec_mysql('SELECT name FROM groups;') for name in row])
parser.add_argument('-m', '--mail', nargs='*', help='email address to get output')
parser.add_argument('-s', '--subject', help='optional email subject')
args = parser.parse_args()
actions = {'snarf': snarf,
'summary': summary,
'weekly': weekly_roundup,
'monthly': monthly_roundup,
'check_for_applicants': check_for_applicants,
'update_group_names': update_group_names,
'test': test}
result = actions.get(args.action)(args.group)
if result:
('test', test)])
parser = argparse.ArgumentParser(
description='Tools for agent-stats admins')
parser.add_argument('action', help='task to perform', choices=actions)
parser.add_argument('-n',
'--number',
default=10,
type=int,
help='number of ranks to show')
parser.add_argument('-g',
'--group',
help='group to focus on',
choices=[
name
for row in exec_mysql('SELECT name FROM groups;')
for name in row
])
parser.add_argument('-m',
'--mail',
nargs='*',
help='email address to get output')
parser.add_argument('-s', '--subject', help='optional email subject')
args = parser.parse_args()
try:
result = actions.get(args.action)(args.group)
except:
if not args.mail:
raise
def snarf(group=None):
group_id, group_name = get_groups(group)
if not group_id:
results = []
for group_id, group_name in groups().items():
idgroups = exec_mysql(f"SELECT idgroups FROM `groups` WHERE url = '{group_id}';")
if not idgroups:
sql = f'''INSERT INTO `groups`
SET `name`='{group_name}', url='{group_id}';'''
exec_mysql(sql)
results.append(snarf(group_id)) # getting all recursive and shiz
collate_agents()
return '\n'.join(filter(None, results))
else:
logging.info(f'snarfing {group_name}')
added, removed, flagged, flipped = [], [], [], []
idgroups = exec_mysql(f"SELECT idgroups FROM `groups` WHERE url = '{group_id}';")[0][0]
remaining_roster = [item for sublist in exec_mysql(f"SELECT idagents FROM membership WHERE idgroups = {idgroups};") for item in sublist] # get the class attendance sheet
logging.info(f'read table: group {group_name}, span now')
for data in read_table(group_id, 'now'):
stat = Stat()
stat.table_load(**data)
stat.save()
if stat.flag and stat.changed:
flagged.append((stat.date, stat.name, stat.reasons))
try:
remaining_roster.remove(stat.agent_id) # take attendance
except ValueError:
logging.info(f'Agent added: {stat.faction.upper()} {stat.name}') # new kid
added.append(stat.faction.upper() + ' ' + stat.name)
sql = f'''INSERT INTO `membership`
VALUES ('{stat.agent_id}', '{idgroups}')
ON DUPLICATE KEY UPDATE idagents=idagents;'''
exec_mysql(sql)
if stat.faction != exec_mysql(f'SELECT faction FROM agents WHERE `name` = "{stat.name}";')[0][0]:
logging.info(f'Agent flipped: {stat.name} -> {stat.faction.upper()}')
flipped.append(f'{stat.name} -> {stat.faction}')
exec_mysql(f'UPDATE agents SET faction="{stat.faction}" WHERE `name`="{stat.name}";')
if remaining_roster:
remaining_roster = str(tuple(remaining_roster)).replace(',)',')') # absentees
removed = sum(exec_mysql(f"SELECT name FROM agents WHERE idagents in {remaining_roster};"), ())
logging.info(f'Agent(s) removed: {removed}')
exec_mysql(f"DELETE FROM membership WHERE idagents in {remaining_roster} and idgroups = {idgroups};")
output = []
if added or removed or flagged or flipped:
output.append(group_name+':')
if flipped:
output.append(' Flipped:')
output.append(' '+'\n '.join(flipped))
if added:
output.append(' Added:')
output.append(' '+'\n '.join(added))
if removed:
output.append(' Removed:')
output.append(' '+'\n '.join(removed))
if flagged:
output.append(' Flagged:')
for flagged_agent in flagged:
output.append(' {} {}'.format(*flagged_agent))
output.append(' '+'\n '.join(flagged_agent[2]))
return '\n'.join(output)
def get_histogram():
return render_template(
'histogram.html', **{
'data': [(row[0], str(row[0].time())[:2]) for row in exec_mysql(
'select pong from portals2 where pong is not null')]
})
def get_chart_data(cmd='start'):
data = list(
exec_mysql(
'SELECT ping, pong, `name`, `status`, portal_url FROM portals2 WHERE NOT (portal_url IS NOT NULL AND `status` = 0);'
))
data.extend(
exec_mysql(
'SELECT ping, pong, `name`, -1, portal_url FROM portals2 WHERE portal_url IS NOT NULL AND `status` = 0;'
))
first_run = exec_mysql('SELECT min(ping) FROM portals2;')[0][0]
dataTable = []
status_name = {
True: 'Accepted',
False: 'Rejected',
None: 'Pending',
-1: 'Duplicate'
}
status_color = {
True: '#3366CC',
False: '#DC3912',
None: '#FF9900',
-1: '#990099'
}
if cmd == 'start' or cmd == None:
data.sort(key=lambda x: x[0] if x[0] else first_run)
if cmd == 'end':
data.sort(key=lambda x: x[1] if x[1] else datetime.datetime.utcnow())
if cmd == 'days':
data.sort(key=lambda x: (get_timespan(x[0], x[1]), x[0]))
data.reverse()
colors = []
for row in data:
if row[3] not in colors:
colors.append(row[3])
for i, color in enumerate(colors):
colors[i] = status_color[color]
now = datetime.datetime.utcnow()
for ping, pong, name, status, portal_url in data:
fillings = {
'id':
status_name[status],
'name':
'{} ({} days)'.format(name.replace("'", "\\'"),
get_timespan(ping, pong)),
'ping':
ping.isoformat() if ping else first_run.isoformat(),
'pong':
pong.isoformat() if pong else now.isoformat()
}
dataTable.append(fillings)
return {
'data': dataTable,
'colors': colors,
'count': Counter(list(zip(*data))[3]),
'start_url': url_for('portals.start'),
'end_url': url_for('portals.end'),
'days_url': url_for('portals.days'),
'json_url': url_for('portals.get_json'),
'summary_url': url_for('portals.get_summary_data'),
'histogram_url': url_for('portals.get_histogram')
}
def load(self, name):
if not name.startswith('@'):
name = '@' + name
self.db_load(
exec_mysql(
"call FindAgentByName('{name}');".format(name=name))[-1])
def load(self, name):
if not name.startswith('@'):
name = '@' + name
self.db_load(exec_mysql("call FindAgentByName('{name}');".format(name=name))[-1])
def save(self):
self.flag, self.min_ap # hack to make sure these are in the cache
sql = '''INSERT INTO `stats`
SET idagents={agent_id},
`date`='{date}',
`level`='{level}',
ap='{ap}',
explorer='{explorer}',
seer='{seer}',
recon='{recon}',
trekker='{trekker}',
builder='{builder}',
connector='{connector}',
`mind-controller`='{mind_controller}',
illuminator='{illuminator}',
recharger='{recharger}',
liberator='{liberator}',
pioneer='{pioneer}',
engineer='{engineer}',
purifier='{purifier}',
guardian='{guardian}',
specops='{specops}',
missionday='{missionday}',
hacker='{hacker}',
translator='{translator}',
sojourner='{sojourner}',
recruiter='{recruiter}',
magnusbuilder='{magnusbuilder}',
collector='{collector}',
binder='{binder}',
`country-master`='{country_master}',
neutralizer='{neutralizer}',
disruptor='{disruptor}',
salvator='{salvator}',
smuggler='{smuggler}',
`link-master`='{link_master}',
controller='{controller}',
`field-master`='{field_master}',
flag={flag},
`min-ap`='{min_ap}'
ON DUPLICATE KEY UPDATE `level`='{level}',
ap='{ap}',
explorer='{explorer}',
seer='{seer}',
recon='{recon}',
trekker='{trekker}',
builder='{builder}',
connector='{connector}',
`mind-controller`='{mind_controller}',
illuminator='{illuminator}',
recharger='{recharger}',
liberator='{liberator}',
pioneer='{pioneer}',
engineer='{engineer}',
purifier='{purifier}',
guardian='{guardian}',
specops='{specops}',
missionday='{missionday}',
hacker='{hacker}',
translator='{translator}',
sojourner='{sojourner}',
recruiter='{recruiter}',
magnusbuilder='{magnusbuilder}',
collector='{collector}',
binder='{binder}',
`country-master`='{country_master}',
neutralizer='{neutralizer}',
disruptor='{disruptor}',
salvator='{salvator}',
smuggler='{smuggler}',
`link-master`='{link_master}',
controller='{controller}',
`field-master`='{field_master}',
flag={flag},
`min-ap`='{min_ap}';'''.format(
**self.__dict__)
self.changed = exec_mysql(sql)
def scrape(service):
pings = ('Ingress Portal Submitted',
'Portal submission confirmation',)
pongs = ('Ingress Portal Live',
'Ingress Portal Rejected',
'Ingress Portal Duplicate',
'Ingress Portal Game Rejected',
'Portal review complete',)
print('Hello', service.users().getProfile(userId='me').execute()['emailAddress'])
query = ('(from:[email protected] OR from:[email protected] OR [email protected])'
' after:%(date)s subject:"Portal"' % {'date': get_start_date()})
# print query
user_id = 'me'
emails = ListMessagesMatchingQuery(service, user_id, query=query)[::-1]
if len(emails): print('emails found. proccessing...')
else: print('no new emails found')
status_before = exec_mysql('SELECT SUM(status = 1), SUM(status = 0), SUM(status IS NULL) FROM portals2')[0]
for m in emails:
message = service.users().messages().get(userId=user_id, id=m['id'], format='full').execute()
for header in message['payload']['headers']:
if header['name'] == 'Date':
date = header['value']
date = parse(date, ignoretz=True)
if header['name'] == 'Subject':
subject = header['value']
for part in message['payload']['parts']:
if part['mimeType'] == 'text/html':
html = base64.b64decode(part['body']['data'].replace('-', '+').replace('_', '/')).decode('utf8')
##########################
#print date, subject, html
if ':' in subject:
preamble, portal_name = subject.partition(':')[::2]
portal_name = portal_name.lower().strip()
status = ('Live' in preamble or "we've accepted your submission" in html)
image_url = html.partition('src="')[2].partition('" alt="')[0]
portal_url = html.partition('<a href="')[2].partition('">')[0].replace('&', '&')
portal_url = canonicalize_url(portal_url) if 'll' in portal_url else 'null'
#print preamble, portal_name, status, image_url, portal_url
if preamble in pings and date not in set(chain(*exec_mysql('SELECT ping FROM portals2'))):
print(subject)
print('new submitted portal')
exec_mysql("""INSERT INTO portals2 (ping, `name`, image_url) VALUES ('%s', "%s", '%s') ON DUPLICATE KEY UPDATE image_url='%s';""" % (date, portal_name.replace('"', '\\"'), image_url, image_url))
#print """INSERT INTO portals2 (ping, `name`, image_url) VALUES ('%s', "%s", '%s') ON DUPLICATE KEY UPDATE image_url='%s';""" % (date, portal_name.replace('"', '\\"'), image_url, image_url)
if preamble in pongs and date not in set(chain(*exec_mysql('SELECT pong FROM portals2'))):
print(subject)
print('portal response received')
names = list(chain(*exec_mysql('SELECT `name` FROM portals2 WHERE status is null;')))
if names.count(portal_name) == 1:
exec_mysql("""UPDATE portals2 SET pong = '%s', `status` = %s, portal_url = %s WHERE `name` = "%s" AND status is null LIMIT 1;""" % (date, status, portal_url, portal_name.replace('"', '\\"')))
#print """UPDATE portals2 SET pong = '%s', `status` = %s, portal_url = %s WHERE `name` = "%s" AND status is null LIMIT 1;""" % (date, status, portal_url, portal_name.replace('"', '\\"'))
else:
# id = exec_sql("""SELECT Id FROM portals2 WHERE image_url = '%s';""" % image_url)[0]
if image_url:
print('attempting image_url match')
exec_mysql("""UPDATE portals2 SET pong = '%s', `status` = %s, portal_url = %s WHERE image_url = '%s' AND status is null LIMIT 1;""" % (date, status, portal_url, image_url))
#print """UPDATE portals2 SET pong = '%s', `status` = %s, portal_url = %s WHERE image_url = '%s' AND status is null LIMIT 1;""" % (date, status, portal_url, image_url)
else:
print('FAILED! duplicate or modified name; attention required')
exec_mysql("""INSERT INTO portals2 (pong, `name`, `status`, portal_url) VALUES ('%s', "%s", %s, %s) ON DUPLICATE KEY UPDATE Id=Id;""" % (date, portal_name.replace('"', '\\"'), status, portal_url))
#print """INSERT INTO portals2 (pong, `name`, `status`, portal_url) VALUES ('%s', "%s", %s, %s) ON DUPLICATE KEY UPDATE Id=Id;""" % (date, portal_name.replace('"', '\\"'), status, portal_url)
status_after = exec_mysql('SELECT SUM(status = 1), SUM(status = 0), SUM(status IS NULL) FROM portals2')[0]
print(get_status(status_before, status_after))
print('all done')
def snarf(group=None):
group_id, group_name = get_groups(group)
if not group_id:
results = ''
for group_id, group_name in groups().items():
logging.info('snarfing '+group_name)
idgroups = exec_mysql("SELECT idgroups FROM groups WHERE url = '{}';".format(group_id))
if not idgroups:
sql = '''INSERT INTO `groups`
SET `name`='{}', url='{}';'''.format(group_name, group_id)
exec_mysql(sql)
results += snarf(group_id) # getting all recursive and shiz
colate_agents()
return results
else:
added, removed, flagged, flipped = [], [], [], []
idgroups = exec_mysql("SELECT idgroups FROM groups WHERE url = '{}';".format(group_id))[0][0]
remaining_roster = [item for sublist in exec_mysql("SELECT idagents FROM membership WHERE idgroups = {};".format(idgroups)) for item in sublist] # get the class attendance sheet
logging.info('read table: group {}, span now'.format(group_name))
for data in read_table(group_id, 'now'):
stat = Stat()
stat.table_load(**data)
stat.save()
if stat.flag and stat.changed:
flagged.append((stat.date, stat.name, stat.reasons))
try:
remaining_roster.remove(stat.agent_id) # take attendance
except ValueError:
logging.info('Agent added: {} {}'.format(stat.faction.upper(), stat.name)) # new kid
added.append(stat.faction.upper() + ' ' + stat.name)
sql = '''INSERT INTO `membership`
VALUES ('{}', '{}')
ON DUPLICATE KEY UPDATE idagents=idagents;'''.format(stat.agent_id, idgroups)
exec_mysql(sql)
if stat.faction != exec_mysql('SELECT faction FROM agents WHERE `name` = "{}";'.format(stat.name))[0][0]:
logging.info('Agent flipped: {} -> {}'.format(stat.name, stat.faction.upper()))
flipped.append('{} -> {}'.format(stat.name, stat.faction))
exec_mysql('UPDATE agents SET faction="{}" WHERE `name`="{}";'.format(stat.faction, stat.name))
if remaining_roster:
remaining_roster = str(tuple(remaining_roster)).replace(',)',')') # absentees
removed = sum(exec_mysql("SELECT name FROM agents WHERE idagents in {};".format(remaining_roster)), ())
logging.info('Agent(s) removed: %s' % str(removed))
exec_mysql("DELETE FROM membership WHERE idagents in {} and idgroups = {};".format(remaining_roster, idgroups))
output = []
if added or removed or flagged or flipped:
output.append(group_name+':')
if flipped:
output.append(' Flipped:')
output.append(' '+'\n '.join(flipped))
if added:
output.append(' Added:')
output.append(' '+'\n '.join(added))
if removed:
output.append(' Removed:')
output.append(' '+'\n '.join(removed))
if flagged:
output.append(' Flagged:')
for flagged_agent in flagged:
output.append(' {} {}'.format(*flagged_agent))
output.append(' '+'\n '.join(flagged_agent[2]))
return '\n'.join(output) + '\n'
def validate(self):
if str(self.date) == '1000-01-01': return ['date missing']
max_sojourner = (self.date - sojourner_start).days + 1
max_guardian = (self.date - game_start).days + 1
apdiff = exec_mysql(
"SELECT apdiff FROM agents WHERE `name` = '{0}';".format(
self.name))
if apdiff: self.apdiff = apdiff[0][0]
reasons = []
# this seems to be a more common bug, and until there is some action agents can take to fix it, it wont be flagged
#if self.min_level > self.level + 1: # +1 is for special case where agents just dinged and scanner hasn't caught up yet. better to let some slip through than to flag an exited agent's ding
# reasons.append( 'reported level %s < %s' % (self.level, self.min_level) )
if self.guardian > max_guardian:
reasons.append('guardian %s > %s' % (self.guardian, max_guardian))
if self.sojourner > max(0, max_sojourner):
reasons.append('sojourner %s > %s' %
(self.sojourner, max_sojourner))
if game_start > self.date:
reasons.append('date %s < %s' % (self.date, game_start))
if self.date > today + datetime.timedelta(days=1):
reasons.append('date %s > %s' %
(self.date, today + datetime.timedelta(days=1)))
if (self.mind_controller / 2) > self.connector:
reasons.append('connector:mind controller %s < %s/2' %
(self.connector, self.mind_controller))
if self.explorer > self.hacker + self.builder + self.engineer + self.connector:
reasons.append('explorer:H+B+E+C %s > %s' %
(self.explorer, self.hacker + self.builder +
self.engineer + self.connector))
if self.pioneer > self.explorer:
reasons.append('pioneer:explorer %s > %s' %
(self.pioneer, self.explorer))
if self.pioneer > self.liberator:
reasons.append('pioneer:liberator %s > %s' %
(self.pioneer, self.liberator))
if self.liberator > self.builder:
reasons.append('liberator:builder %s > %s' %
(self.liberator, self.builder))
if (self.salvator / 2) > self.disruptor:
reasons.append('disruptor:salvator %s < %s/2' %
(self.disruptor, self.salvator))
if self.disruptor > self.purifier:
reasons.append('disruptor:purifier %s > %s' %
(self.disruptor, self.purifier))
if self.neutralizer > self.purifier:
reasons.append('neutralizer:purifier %s > %s' %
(self.neutralizer, self.purifier))
if (self.translator / 15) > self.hacker:
reasons.append('hacker:translator %s < %s/15' %
(self.hacker, self.translator))
if (self.magnusbuilder / 8) > self.explorer:
reasons.append('explorer:magnusbuilder %s < %s/8' %
(self.explorer, self.magnusbuilder))
if self.magnusbuilder > self.builder:
reasons.append('builder:magnusbuilder %s < %s' %
(self.builder, self.magnusbuilder))
# there was a missionday where they didnt require missions at all. 100 UPV would get you the badge
# http://www.pref.iwate.jp/dbps_data/_material_/_files/000/000/031/399/morioka0621.pdf (in japanese, on page 2)
#if self.missionday > self.specops:
# reasons.append( 'missionday:specops %s > %s' % (self.missionday, self.specops) )
# this catches faction flippers unfortunately
#if self.min_ap > self.ap:
# reasons.append( 'ap:min_ap %s < %s' % (self.ap, self.min_ap) )
#if self.apdiff > self.ap-self.min_ap:
# reasons.append( 'apdiff %s > %s' % (self.apdiff, self.ap-self.min_ap) )
if not reasons:
exec_mysql(
"UPDATE agents SET apdiff={0} WHERE `name`='{1}';".format(
self.ap - self.min_ap, self.name))
return reasons
def table_load(self, **row):
self.date = parse(row['last_submit'] if row['last_submit'] and not row['last_submit'].startswith('0') else '1000/1/1').date()
self.name = row['name'][:16]
self.faction = row.get('faction', 'UNK')
self.level = row.get('level', 0)
self.lifetime_ap = row.get('lifetime_ap', 0)
self.recursions = row.get('recursions', 0)
self.ap = row.get('ap', 0)
self.explorer = row.get('explorer', 0)
self.discoverer = row.get('discoverer', 0)
self.seer = row.get('seer', 0)
self.recon = row.get('recon', 0)
self.scout = row.get('scout', 0)
self.collector = row.get('collector', 0)
self.trekker = row.get('trekker', 0)
self.builder = row.get('builder', 0)
self.connector = row.get('connector', 0)
self.mind_controller = row.get('mind-controller', 0)
self.illuminator = row.get('illuminator', 0)
self.binder = row.get('binder', 0)
self.country_master = row.get('country-master', 0)
self.recharger = row.get('recharger', 0)
self.liberator = row.get('liberator', 0)
self.pioneer = row.get('pioneer', 0)
self.engineer = row.get('engineer', 0)
self.purifier = row.get('purifier', 0)
self.neutralizer = row.get('neutralizer', 0)
self.disruptor = row.get('disruptor', 0)
self.salvator = row.get('salvator', 0)
self.guardian = row.get('guardian', 0)
self.smuggler = row.get('smuggler', 0)
self.link_master = row.get('link-master', 0)
self.controller = row.get('controller', 0)
self.field_master = row.get('field-master', 0)
self.specops = row.get('specops', 0)
self.missionday = row.get('missionday', 0)
self.nl_1331_meetups = row.get('nl-1331-meetups', 0)
self.hacker = row.get('hacker', 0)
self.translator = row.get('translator', 0)
self.sojourner = row.get('sojourner', 0)
self.recruiter = row.get('recruiter', 0)
self.prime_challenge = row.get('prime_challenge', 0)
self.stealth_ops = row.get('stealth_ops', 0)
self.opr_live = row.get('opr_live', 0)
self.ocf = row.get('ocf', 0)
self.intel_ops = row.get('intel_ops', 0)
self.ifs = row.get('ifs', 0)
self.drone_explorer = row.get('drone_explorer', 0)
self.drone_distance = row.get('drone_distance', 0)
self.drone_recalls = row.get('drone_recalls', 0)
self.drone_sender = row.get('drone_sender', 0)
self.maverick = row.get('maverick', 0)
self.scout_controller = row.get('scout_controller', 0)
self.crafter = row.get('crafter', 0)
self.bb_combatant = row.get('bb_combatant', 0)
self.epoch = row.get('epoch', 0)
self.operation_sentinel = row.get('operation_sentinel', 0)
agent_id = exec_mysql("SELECT idagents FROM agents WHERE name = '{0}';".format(self.name))
if agent_id:
self.agent_id = agent_id[0][0]
else:
sql = '''INSERT INTO `agents` SET `name`='{0}', `faction`='{1}';'''.format(self.name, self.faction)
exec_mysql(sql)
logging.info('new entry created for {} in agents table'.format(self.name))
self.agent_id = exec_mysql("SELECT idagents FROM agents WHERE name = '{0}';".format(self.name))[0][0]
def save(self):
self.flag, self.min_ap # hack to make sure these are in the cache
sql = '''INSERT INTO `stats`
SET idagents={agent_id},
`date`='{date}',
`level`='{level}',
ap='{ap}',
lifetime_ap='{lifetime_ap}',
recursions='{recursions}',
explorer='{explorer}',
discoverer='{discoverer}',
seer='{seer}',
recon='{recon}',
scout='{scout}',
trekker='{trekker}',
builder='{builder}',
connector='{connector}',
`mind-controller`='{mind_controller}',
illuminator='{illuminator}',
recharger='{recharger}',
liberator='{liberator}',
pioneer='{pioneer}',
engineer='{engineer}',
purifier='{purifier}',
guardian='{guardian}',
specops='{specops}',
missionday='{missionday}',
`nl-1331-meetups`='{nl_1331_meetups}',
hacker='{hacker}',
translator='{translator}',
sojourner='{sojourner}',
recruiter='{recruiter}',
collector='{collector}',
binder='{binder}',
`country-master`='{country_master}',
neutralizer='{neutralizer}',
disruptor='{disruptor}',
salvator='{salvator}',
smuggler='{smuggler}',
`link-master`='{link_master}',
controller='{controller}',
`field-master`='{field_master}',
prime_challenge='{prime_challenge}',
stealth_ops='{stealth_ops}',
opr_live='{opr_live}',
ocf='{ocf}',
intel_ops='{intel_ops}',
ifs='{ifs}',
drone_explorer='{drone_explorer}',
drone_distance='{drone_distance}',
drone_recalls='{drone_recalls}',
drone_sender='{drone_sender}',
maverick='{maverick}',
scout_controller='{scout_controller}',
crafter='{crafter}',
bb_combatant='{bb_combatant}',
epoch='{epoch}',
operation_sentinel='{operation_sentinel}',
flag={flag},
`min-ap`='{min_ap}'
ON DUPLICATE KEY UPDATE `level`='{level}',
ap='{ap}',
lifetime_ap='{lifetime_ap}',
recursions='{recursions}',
explorer='{explorer}',
discoverer='{discoverer}',
seer='{seer}',
recon='{recon}',
scout='{scout}',
trekker='{trekker}',
builder='{builder}',
connector='{connector}',
`mind-controller`='{mind_controller}',
illuminator='{illuminator}',
recharger='{recharger}',
liberator='{liberator}',
pioneer='{pioneer}',
engineer='{engineer}',
purifier='{purifier}',
guardian='{guardian}',
specops='{specops}',
missionday='{missionday}',
`nl-1331-meetups`='{nl_1331_meetups}',
hacker='{hacker}',
translator='{translator}',
sojourner='{sojourner}',
recruiter='{recruiter}',
collector='{collector}',
binder='{binder}',
`country-master`='{country_master}',
neutralizer='{neutralizer}',
disruptor='{disruptor}',
salvator='{salvator}',
smuggler='{smuggler}',
`link-master`='{link_master}',
controller='{controller}',
`field-master`='{field_master}',
prime_challenge='{prime_challenge}',
stealth_ops='{stealth_ops}',
opr_live='{opr_live}',
ocf='{ocf}',
intel_ops='{intel_ops}',
ifs='{ifs}',
drone_explorer='{drone_explorer}',
drone_distance='{drone_distance}',
drone_recalls='{drone_recalls}',
drone_sender='{drone_sender}',
maverick='{maverick}',
scout_controller='{scout_controller}',
crafter='{crafter}',
bb_combatant='{bb_combatant}',
epoch='{epoch}',
operation_sentinel='{operation_sentinel}',
flag={flag},
`min-ap`='{min_ap}';'''.format(**self.__dict__)
self.changed = exec_mysql(sql)
def db_load(self, row): # ATTENTION: DO NOT simply use 'SELECT * FROM stats;' here. It will end in tears.
row = Row(*row) # your boat...
self.name = row.name
self.date = row.date if row.date else datetime.date(1000, 1, 1)
#self.flag = row.flag
#self.min_ap = row.min_ap
self.ap = row.ap
self.lifetime_ap = row.lifetime_ap
self.recursions = row.recursions
self.level = row.level
self.explorer = row.explorer
self.discoverer = row.discoverer
self.seer = row.seer
self.recon = row.recon
self.scout = row.scout
self.collector = row.collector
self.trekker = row.trekker
self.builder = row.builder
self.connector = row.connector
self.mind_controller = row.mind_controller
self.illuminator = row.illuminator
self.binder = row.binder
self.country_master = row.country_master
self.recharger = row.recharger
self.liberator = row.liberator
self.pioneer = row.pioneer
self.engineer = row.engineer
self.purifier = row.purifier
self.neutralizer = row.neutralizer
self.disruptor = row.disruptor
self.salvator = row.salvator
self.guardian = row.guardian
self.smuggler = row.smuggler
self.link_master = row.link_master
self.controller = row.controller
self.field_master = row.field_master
self.specops = row.specops
self.missionday = row.missionday
self.nl_1331_meetups = row.nl_1331_meetups
self.hacker = row.hacker
self.translator = row.translator
self.sojourner = row.sojourner
self.recruiter = row.recruiter
self.prime_challenge = row.prime_challenge
self.stealth_ops = row.stealth_ops
self.opr_live = row.opr_live
self.ocf = row.ocf
self.intel_ops = row.intel_ops
self.ifs = row.ifs
self.drone_explorer = row.drone_explorer
self.drone_distance = row.drone_distance
self.drone_recalls = row.drone_recalls
self.drone_sender = row.drone_sender
self.maverick = row.maverick
self.scout_controller = row.scout_controller
self.crafter = row.crafter
self.bb_combatant = row.bb_combatant
self.epoch = row.epoch
self.matryoshka_links = row.matryoshka_links
self.operation_sentinel = row.operation_sentinel
# obsolete stats
self.hack_the_world202104 = row.hack_the_world202104
self.cassandra_neutralizer = row.cassandra_neutralizer
self.magnusbuilder = row.magnusbuilder
self.dark_xm_threat = row.dark_xm_threat
self.myriad_hack = row.myriad_hack
self.aurora_glyph = row.aurora_glyph
self.umbra_deploy = row.umbra_deploy
self.didact_field = row.didact_field
if str(self.name).startswith('@'):
self.agent_id = exec_mysql("SELECT idagents FROM agents WHERE name = '{0}';".format(self.name[:16]))[0][0]
else: # probably good enough, but if this still blows up then make sure it's a numeric id and not just a name missing its @
self.agent_id = self.name
self.name = exec_mysql("SELECT name FROM agents WHERE idagents = '{0}';".format(self.agent_id))[0][0]
def summary(group='all', days=7):
snarf(group)
group_id = get_groups(group)[0]
if not group_id:
group_id = {'all': 1, 'smurfs': 2, 'frogs':3}.get(group, None)
headers = ('explorer',
'discoverer',
'seer',
'recon',
'scout',
'trekker',
'builder',
'connector',
'mind_controller',
'illuminator',
'recharger',
'liberator',
'pioneer',
'engineer',
'purifier',
'specops',
'missionday',
'nl_1331_meetups',
'cassandra_neutralizer', # obsolete
'hacker',
'translator',
'sojourner',
'recruiter',
'magnusbuilder', # obsolete
'recursions',
'prime_challenge',
'stealth_ops',
'opr_live',
'ocf',
'intel_ops',
'ifs',
'maverick',
'dark_xm_threat', # obsolete
'myriad_hack', # obsolete
'aurora_glyph', # obsolete
'umbra_deploy', # obsolete
'didact_field', # obsolete
'scout_controller',
'epoch',
)
sql_before = f'''SELECT x.name, s.`date`, `level`, ap, explorer, discoverer, seer, recon, scout, trekker, builder, connector,
`mind-controller` mind_controller, illuminator, recharger, liberator, pioneer, engineer, purifier,
specops, missionday, `nl-1331-meetups` nl_1331_meetups, `cassandra-neutralizer` cassandra_neutralizer,
hacker, translator, sojourner, recruiter, magnusbuilder, recursions, prime_challenge, stealth_ops,
opr_live, ocf, intel_ops, ifs, maverick, dark_xm_threat, myriad_hack, aurora_glyph, umbra_deploy,
didact_field, scout_controller, epoch
FROM (
SELECT a.name name, s.idagents id, MAX(s.date) AS date
FROM agents a, stats s, membership m, `groups` g
WHERE a.idagents = s.idagents AND
s.idagents = m.idagents AND
m.idgroups = g.idgroups AND
g.`url` = '{group_id}' AND
s.flag != 1 AND
date < ( CURDATE() - INTERVAL {days} DAY )
GROUP BY id ) x
JOIN stats s ON x.id = s.idagents AND x.date = s.date'''
baseline = {}
for row in exec_mysql(sql_before):
agent = row[0]
if row[1]: # if has date. filters out the agents with rows of all 0s
baseline[agent] = {'date': row[1], 'level': row[2], 'ap': row[3],
'badges': get_badges(dict(zip(headers, row[4:])))}
sql_now = f'''SELECT x.name, s.`date`, `level`, ap, explorer, discoverer, seer, recon, scout, trekker, builder, connector,
`mind-controller` mind_controller, illuminator, recharger, liberator, pioneer, engineer, purifier,
specops, missionday, `nl-1331-meetups` nl_1331_meetups, `cassandra-neutralizer` cassandra_neutralizer,
hacker, translator, sojourner, recruiter, magnusbuilder, recursions, prime_challenge, stealth_ops,
opr_live, ocf, intel_ops, ifs, maverick, dark_xm_threat, myriad_hack, aurora_glyph, umbra_deploy,
didact_field, scout_controller, epoch
FROM (
SELECT a.name name, s.idagents id, MAX(s.date) AS date
FROM agents a, stats s, membership m, `groups` g
WHERE a.idagents = s.idagents AND
s.idagents = m.idagents AND
m.idgroups = g.idgroups AND
g.`url` = '{group_id}' AND
s.flag != 1 AND
date >= ( CURDATE() - INTERVAL {days} DAY )
GROUP BY id ) x
JOIN stats s ON x.id = s.idagents AND x.date = s.date
ORDER BY x.name'''
output = {'data': []}
footnote = ''
for row in exec_mysql(sql_now):
agent = row[0]
if agent in baseline:
date_old = baseline[agent]['date']
date_new = row[1]
level_old = baseline[agent]['level']
level_new = row[2]
ap_old = baseline[agent]['ap']
ap_new = row[3]
ap_40m_old = int(ap_old)//40000000
ap_40m_new = int(ap_new)//40000000
badges_old = baseline[agent]['badges']
badges_new = get_badges(dict(zip(headers, row[4:])))
changes = OrderedDict()
if badges_old != badges_new:
changes.update(new_badges(badges_old, badges_new))
if ap_40m_old < ap_40m_new:
changes['ap'] = []
for l in range(ap_40m_old, ap_40m_new):
if l < 24:
changes['ap'].append(f'{(l+1)*40} MILLION')
else:
billions = (l+1)/25
if billions.is_integer():
changes['ap'].append(f'{int(billions)} BILLION')
else:
changes['ap'].append(f'{billions} BILLION')
if level_old < level_new:
changes['level'] = [str(l+1) for l in range(level_old, level_new)]
if changes:
earnings = englishify(changes)
today = datetime.date.today()
stale = today - datetime.timedelta(days=days*2)
note = ''
if date_old < stale:
note = '¹' # chcp 65001
footnote = f"¹Start date more than 2 {('weeks' if days == 7 else 'months')} ago"
if today.year == date_old.year:
template = f'earned {earnings} sometime between {date_old.month}/{date_old.day}{note} and {date_new.month}/{date_new.day}'
else:
template = f'earned {earnings} sometime between {date_old.month}/{date_old.day}/{date_old.year}{note} and {date_new.month}/{date_new.day}/{date_new.year}'
output['data'].append({'name': agent, 'earned': template})
if footnote:
output['footnote'] = footnote
return output
def get_histogram():
return render_template('histogram.html', **{'data':[(row[0], str(row[0].time())[:2]) for row in exec_mysql('select pong from portals2 where pong is not null')]})