Пример #1
0
    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
Пример #2
0
    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]
Пример #3
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')
Пример #4
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(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')
Пример #5
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.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]
Пример #6
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')}
Пример #7
0
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')
Пример #8
0
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=(',', ': '))
Пример #9
0
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]}')
Пример #10
0
    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]
Пример #11
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]
Пример #12
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=(',', ': '))
Пример #14
0
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)
Пример #15
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
Пример #16
0
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)
Пример #17
0
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})
Пример #18
0
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)
Пример #21
0
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)
Пример #22
0
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)
Пример #23
0
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)
Пример #24
0
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)
Пример #25
0
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)
Пример #26
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}',
                     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)
Пример #27
0
    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:
Пример #28
0
                           ('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
Пример #29
0
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')
    }
Пример #32
0
 def load(self, name):
     if not name.startswith('@'):
         name = '@' + name
     self.db_load(
         exec_mysql(
             "call FindAgentByName('{name}');".format(name=name))[-1])
Пример #33
0
 def load(self, name):
     if not name.startswith('@'):
         name = '@' + name
     self.db_load(exec_mysql("call FindAgentByName('{name}');".format(name=name))[-1])
Пример #34
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}',
                     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)
Пример #35
0
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('&amp;', '&')
            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')
Пример #36
0
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'
Пример #37
0
    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
Пример #38
0
    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]
Пример #39
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)
Пример #40
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.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]
Пример #41
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
Пример #42
0
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')]})