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
