def run(self): alliance_query = alliances.AllianceQuery() all_alliances = alliance_query.getAll() alliances_names = [item["shortname"] for item in all_alliances] users_with_alliance = users.UserQuery().find_name_by_alliances( alliances_names) query = "SELECT id FROM room_imports WHERE status LIKE 'complete' ORDER BY started_at DESC" result = db.find_one(query) self.room_import_id = result[0] self.conn = get_conn() self.start() print(self.id) for alliance in all_alliances: users_with_alliance = self.find_name_by_alliances(alliances_names) members = [ user['name'] for user in users_with_alliance if user['alliance'] == alliance['shortname'] ] filtered_members = [ user for user in members if self.get_player_room_count(user) > 0 ] # Not enough members. if len(filtered_members) < 2: continue # Not enough rooms if self.get_room_count(alliance['shortname']) < 2: continue rcl = self.getAllianceRCL(alliance['shortname']) combined_gcl = sum( self.getUserGCL(user) for user in filtered_members) control = sum( getUserControlPoints(user) for user in filtered_members) alliance_gcl = self.convertGcl(control) combined_power = sum( self.getUserPowerLevel(user) for user in filtered_members) power = sum(getUserPowerPoints(user) for user in filtered_members) alliance_power = self.convertPowerToLevel(power) spawns = self.getAllianceSpawns(alliance['shortname']) print('%s- %s, %s, %s, %s, %s, %s, %s' % (alliance['shortname'], combined_gcl, alliance_gcl, rcl, spawns, len(filtered_members), alliance_power, combined_power)) self.update(alliance['shortname'], alliance_gcl, combined_gcl, rcl, spawns, len(filtered_members), alliance_power, combined_power) self.finish() self.conn.commit()
def getMembershipData(self): query = "SELECT id FROM room_imports ORDER BY id desc LIMIT 1" result = db.find_one(query) if result is None: return [] import_id = result[0] query = """ SELECT t.alliance, string_agg(t.ign, ',') AS members, SUM(CASE WHEN t.room_count > 0 THEN 1 ELSE 0 END) AS active_member_count, SUM(t.room_count) AS room_count FROM ( SELECT COUNT(DISTINCT rooms.name) AS room_count, users.ign, users.alliance FROM users JOIN alliances ON users.alliance = alliances.shortname LEFT JOIN rooms ON rooms.owner = users.id AND rooms.import = %s GROUP BY users.ign, users.alliance ORDER BY users.ign ) t GROUP BY t.alliance ORDER BY t.alliance; """ result = db.find_all(query, (import_id, )) return_value = [] return [{ "shortname": row[0], "members": row[1].split(","), "active_member_count": int(row[2]), "room_count": int(row[3]) } for row in result]
def get_room_count(shortname): query = ''' SELECT COUNT(DISTINCT rooms.name) FROM rooms,users WHERE rooms.owner=users.id AND users.alliance=%s AND rooms.import = (SELECT id FROM room_imports ORDER BY id desc LIMIT 1 ); ''' result = db.find_one(query, (shortname, )) return int(result[0])
def get_latest_import_id(): query = "SELECT id FROM rankings_imports WHERE status LIKE 'complete' ORDER BY started_at DESC" result = db.find_one(query) if result is None: return None return result[0]
def get_all_rankings(import_id=None): if not import_id: query = "SELECT id FROM rankings_imports WHERE status LIKE 'complete' ORDER BY started_at DESC" result = db.find_one(query) import_id = result[0] return get_all_rankings_by_import(import_id)
def find_by_shortname(self, name): query = "SELECT fullname from alliances where shortname=%s" result = db.find_one(query, (name, )) if result is not None: return result[0] return None
def user_name_from_db_id(id): query = "SELECT ign FROM users WHERE id=%s" row = db.find_one(query, (id,)) if (row is not None): return row[0] return None
def user_id_from_db(name): query = "SELECT id FROM users WHERE LOWER(ign)=LOWER(%s)" row = db.find_one(query, (name,)) if (row is not None): return row[0] return None
def alliance_of_user(id): query = '''SELECT fullname, shortname, logo, charter, slack_channel, color from users JOIN alliances ON alliance=shortname where id=%s''' return db.find_one(query, (id,))
def get_invite_by_id(id): query = "SELECT * from alliance_invites where id=%s" result = db.find_one(query, (id, )) if result is not None: return result return None