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, ))
