def getAll(self): query = "SELECT shortname, fullname, slack_channel, color, logo FROM alliances ORDER BY shortname" result = db.find_all(query) return [{ "shortname": i[0], "fullname": i[1], "slack_channel": i[2], "color": i[3], "logo": i[4] } for i in result]
def get_rankings_by_alliance_and_type(ranking_type, alliance, import_id=None): allowed_types = get_rankings_list() if ranking_type not in allowed_types: raise ValueError('Not a valid ranking type') if not import_id: import_id = get_latest_import_id() query = "SELECT " + ranking_type + " FROM rankings WHERE alliance=(%s) AND import=(%s) ORDER BY " + ranking_type + " DESC" return db.find_all(query, (alliance, import_id))
def get_rooms_by_import(import_id, shard=None): if not shard: query = "SELECT name, level, ign FROM rooms JOIN users ON rooms.owner = users.id WHERE import=(%s)" result = db.find_all(query, (import_id, )) return [{ 'roomname': item[0], 'level': item[1], 'owner_name': item[2] } for item in result] s = re.search("\d+$", shard) shard_id = s.group(0) query = "SELECT name, level, ign FROM rooms JOIN users ON rooms.owner = users.id WHERE import=(%s) AND shard=(%s)" result = db.find_all(query, (import_id, shard_id)) return [{ 'roomname': item[0], 'level': item[1], 'owner_name': item[2] } for item in result]
def get_rankings_by_import_and_type(ranking_type, import_id=None): allowed_types = get_rankings_list() if ranking_type not in allowed_types: raise ValueError('Not a valid ranking type') if not import_id: import_id = get_latest_import_id() query = "SELECT alliance FROM rankings WHERE import=(%s) ORDER BY " + ranking_type + " DESC" results = db.find_all(query, (import_id, )) alliance_mapping = {} for index in range(len(results)): alliance_mapping[results[index][0]] = index + 1 return alliance_mapping
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_all_rankings_by_import(import_id): query = "SELECT * FROM rankings WHERE import=(%s) ORDER BY alliance_gcl DESC" return db.find_all(query, (import_id, ))
def get_all_users_for_importing(): query = "SELECT * FROM users ORDER BY gcl IS NOT NULL, RANDOM()" return db.find_all(query)
def find_name_by_alliances(self, alliances): query = "SELECT ign, alliance FROM users where alliance = ANY(%s)" result = db.find_all(query, (alliances,)) return [{"name": row[0], "alliance": row[1]} for row in result]
def get_all_users(): query = "SELECT * FROM users" return db.find_all(query)
def find_player_id_by_alliance(alliance): query = "SELECT screeps_id FROM users where alliance = %s" result = db.find_all(query, (alliance,)) return [row[0] for row in result]
def find_name_by_alliance(alliance): query = "SELECT ign FROM users where alliance = %s" result = db.find_all(query, (alliance,)) return [row[0] for row in result]
def get_invites_by_user(user): query = "SELECT * FROM alliance_invites WHERE user_id=(%s)" return db.find_all(query, (user, ))
def get_invites_by_alliance(alliance): query = "SELECT * FROM alliance_invites WHERE alliance=(%s)" return db.find_all(query, (alliance, ))