async def top_donations(self, ctx):
"""Displays top ten donation totals for all of the RCS"""
async with ctx.typing():
data = await self.get_member_list("donations")
title = "RCS Top Ten for Donations"
ctx.icon = "https://cdn.discordapp.com/emojis/301032036779425812.png"
p = formats.TablePaginator(ctx,
data=data,
title=title,
page_count=1)
await p.paginate()
async def top_defenses(self, ctx):
"""Displays top ten defense win totals for all of the RCS"""
async with ctx.typing():
data = await self.get_member_list("defense_wins")
title = "RCS Top Ten for Defense Wins"
ctx.icon = "https://cdn.discordapp.com/emojis/635642869373468704.png"
p = formats.TablePaginator(ctx,
data=data,
title=title,
page_count=1)
await p.paginate()
async def top_levels(self, ctx):
"""Displays top ten Exp Levels for all of the RCS"""
async with ctx.typing():
data = await self.get_member_list("exp_level")
title = "RCS Top Ten for Exp Level"
ctx.icon = "https://cdn.discordapp.com/emojis/748585659085881444.png"
p = formats.TablePaginator(ctx,
data=data,
title=title,
page_count=1)
await p.paginate()
async def top_attacks(self, ctx):
"""Displays top ten attack win totals for all of the RCS"""
async with ctx.typing():
data = await self.get_member_list("attack_wins")
title = "RCS Top Ten for Attack Wins"
ctx.icon = "https://cdn.discordapp.com/emojis/635642869750824980.png"
p = formats.TablePaginator(ctx,
data=data,
title=title,
page_count=1)
await p.paginate()
async def push_gain(self, ctx):
"""Returns top 25 players based on number of trophies gained."""
with Sql() as cursor:
cursor.execute(
"SELECT trophyGain, player FROM rcspush_vwGains ORDER BY trophyGain DESC"
)
fetch = cursor.fetchall()
page_count = math.ceil(len(fetch) / 25)
title = "RCS Push Top Trophy Gains"
ctx.icon = "https://cdn.discordapp.com/emojis/635642869738111016.png"
p = formats.TablePaginator(ctx,
data=fetch,
title=title,
page_count=page_count)
await p.paginate()
async def push_all(self, ctx):
"""Returns list of all clans ranked by score (only top 30 trophies contribute to the score)."""
with Sql() as cursor:
cursor.execute(
"SELECT SUM(clanPoints) AS totals, clanName FROM vRCSPush30 "
"GROUP BY clanName "
"ORDER BY totals DESC")
fetch = cursor.fetchall()
page_count = math.ceil(len(fetch) / 20)
title = "RCS Push Rankings"
ctx.icon = "https://cdn.discordapp.com/emojis/635642869738111016.png"
p = formats.TablePaginator(ctx,
data=fetch,
title=title,
page_count=page_count,
rows_per_table=20)
await p.paginate()
async def games_clan(self, ctx, *, clan: ClanConverter = None):
"""Returns the individual player points for the specified clan
Examples:
`++games clan Team Boom`
`++games clan #CVCJR89`
`++games clan Pi`
"""
async with ctx.typing():
conn = self.bot.pool
sql = ("SELECT player_points "
"FROM rcs_events "
"WHERE event_type_id = 1 and start_time < NOW() "
"ORDER BY start_time DESC LIMIT 1")
player_points = await conn.fetchval(sql)
sql = ("SELECT player_name, points "
"FROM rcs_clan_games_players "
"WHERE clan_tag = $1"
"ORDER BY points DESC")
fetch = await conn.fetch(sql, clan.tag[1:])
clan_total = 0
clan_size = len(fetch)
data = []
for member in fetch:
if member['player_name'].startswith("#"):
# Player has left clan and we can't retrieve the player name
player = await self.bot.coc.get_player(
member['player_name'])
player_name = player.name
else:
player_name = member['player_name']
if member['points'] >= player_points:
clan_total += player_points
data.append([member['points'], "* " + player_name])
else:
clan_total += member['points']
data.append([member['points'], player_name])
clan_average = clan_total / clan_size
page_count = math.ceil(len(data) / 25)
title = f"{clan.name} Points {clan_total} ({clan_average:.2f} avg)"
ctx.icon = "https://cdn.discordapp.com/emojis/639623355770732545.png"
p = formats.TablePaginator(ctx,
data=data,
title=title,
page_count=page_count)
await p.paginate()
async def games_average(self, ctx):
"""Returns the average player points for all RCS clans"""
conn = self.bot.pool
sql = ("SELECT clan_avg, clan_name "
"FROM rcs_clan_games_average "
"ORDER BY clan_avg DESC")
fetch = await conn.fetch(sql)
data = []
for clan in fetch:
data.append([clan['clan_avg'], clan['clan_name']])
page_count = math.ceil(len(data) / 25)
title = "RCS Clan Games Averages"
ctx.icon = "https://cdn.discordapp.com/emojis/639623355770732545.png"
p = formats.TablePaginator(ctx,
data=data,
title=title,
page_count=page_count)
await p.paginate()
async def defenses(self, ctx, *, clan: ClanConverter = None):
"""Defense wins for the whole clan
**Example:**
++def Reddit Example
"""
if not clan:
return await ctx.send(
"You have not provided a valid clan name or clan tag.")
async with ctx.typing():
sql = "SELECT defense_wins, player_name FROM rcs_members WHERE clan_tag = $1 ORDER BY defense_wins DESC"
fetch = await self.bot.pool.fetch(sql, clan.tag[1:])
page_count = math.ceil(len(fetch) / 25)
title = f"Defense Wins for {clan.name}"
ctx.icon = "https://cdn.discordapp.com/emojis/635642869373468704.png"
p = formats.TablePaginator(ctx,
data=fetch,
title=title,
page_count=page_count)
await p.paginate()
async def warstars(self, ctx, *, clan: ClanConverter = None):
"""List of clan members by war stars earned
**Example:**
++stars Reddit Example
"""
if not clan:
return await ctx.send(
"You have not provided a valid clan name or clan tag.")
async with ctx.typing():
sql = "SELECT war_stars, player_name FROM rcs_members WHERE clan_tag = $1 ORDER BY war_stars DESC"
fetch = await self.bot.pool.fetch(sql, clan.tag[1:])
page_count = math.ceil(len(fetch) / 25)
title = f"War Stars for {clan.name}"
ctx.icon = "https://cdn.discordapp.com/emojis/635642870350741514.png"
p = formats.TablePaginator(ctx,
data=fetch,
title=title,
page_count=page_count)
await p.paginate()
async def builderhalls(self, ctx, *, clan: ClanConverter = None):
"""List of clan members by builder hall level
**Example:**
++bh Reddit Example
"""
if not clan:
return await ctx.send(
"You have not provided a valid clan name or clan tag.")
async with ctx.typing():
sql = "SELECT bh_level, player_name FROM rcs_members WHERE clan_tag = $1 ORDER BY bh_level DESC"
fetch = await self.bot.pool.fetch(sql, clan.tag[1:])
page_count = math.ceil(len(fetch) / 25)
title = f"Builder Halls for {clan.name}"
ctx.icon = "https://cdn.discordapp.com/emojis/513119024188489738.png"
p = formats.TablePaginator(ctx,
data=fetch,
title=title,
page_count=page_count)
await p.paginate()
async def donations(self, ctx, *, clan: ClanConverter = None):
"""Donations for the whole clan
**Example:**
++don Reddit Example
"""
if not clan:
return await ctx.send(
"You have not provided a valid clan name or clan tag.")
sql = (
"SELECT donations, donations_received, player_name FROM rcs_members WHERE clan_tag = $1 "
"ORDER BY donations DESC")
fetch = await self.bot.pool.fetch(sql, clan.tag[1:])
page_count = math.ceil(len(fetch) / 25)
title = f"Donations for {clan.name}"
ctx.icon = "https://cdn.discordapp.com/emojis/301032036779425812.png"
p = formats.TablePaginator(ctx,
data=fetch,
title=title,
page_count=page_count)
await p.paginate()
async def levels(self, ctx, *, clan: ClanConverter = None):
"""Exp Level for the whole clan
**Example:**
++xp Reddit Oak
++level Oak
++lvl #CVCJR89"""
if not clan:
return await ctx.send(
"You have not provided a valid clan name or clan tag.")
async with ctx.typing():
sql = "SELECT exp_level, player_name FROM rcs_members WHERE clan_tag = $1 ORDER BY exp_level DESC"
fetch = await self.bot.pool.fetch(sql, clan.tag[1:])
page_count = math.ceil(len(fetch) / 25)
title = f"Exp Levels for {clan.name}"
ctx.icon = "http://cdn.discordapp.com/emojis/748585659085881444.png"
p = formats.TablePaginator(ctx,
data=fetch,
title=title,
page_count=page_count)
await p.paginate()
async def push_th(self, ctx, th_level: int):
"""Returns list of top 100 players at the TH specified (there must be a space between th and the number)."""
if (th_level > 13) or (th_level < 6):
return await ctx.send(
"You have not provided a valid town hall level.")
with Sql() as cursor:
cursor.execute(
f"SELECT TOP 100 currentTrophies, CAST(clanPoints AS DECIMAL(5,2)) as Pts, "
f"playerName + ' (' + COALESCE(altName, clanName) + ')' as Name "
f"FROM vRCSPush "
f"WHERE currentThLevel = {th_level} "
f"ORDER BY clanPoints DESC")
fetch = cursor.fetchall()
page_count = math.ceil(len(fetch) / 20)
title = f"RCS Push Top Points for TH{th_level}"
ctx.icon = "https://cdn.discordapp.com/emojis/635642869738111016.png"
p = formats.TablePaginator(ctx,
data=fetch,
title=title,
page_count=page_count,
rows_per_table=20)
await p.paginate()
async def push_clan(self, ctx, clan: ClanConverter = None):
"""Returns a list of players from the specified clan with their push points"""
if not clan:
return await ctx.send(
"Please provide a valid clan name/tag when using this command."
)
print(clan)
with Sql() as cursor:
cursor.execute(
f"SELECT CAST(clanPoints as decimal(5,2)), "
f"playerName + ' (TH' + CAST(currentThLevel as varchar(2)) + ')' "
f"FROM vRCSPush "
f"WHERE clanName = %s "
f"ORDER BY clanPoints DESC", clan.name)
fetch = cursor.fetchall()
page_count = math.ceil(len(fetch) / 25)
title = f"RCS Push - {clan.name} Points"
ctx.icon = "https://cdn.discordapp.com/emojis/635642869738111016.png"
p = formats.TablePaginator(ctx,
data=fetch,
title=title,
page_count=page_count)
await p.paginate()
async def donations(self, ctx, *, clan: ClanConverter = None):
"""Donations for the whole clan
**Example:**
++don Reddit Example
"""
if not clan:
return await ctx.send(
"You have not provided a valid clan name or clan tag.")
with Sql() as cursor:
cursor.execute(
f"SELECT donations, donationsReceived, playerName FROM rcs_members "
f"WHERE clanTag = %s AND timestamp = (SELECT TOP 1 timestamp from rcs_members "
f"WHERE clanTag = %s ORDER BY timestamp DESC) ORDER BY donations DESC",
(clan.tag[1:], clan.tag[1:]))
fetch = cursor.fetchall()
page_count = math.ceil(len(fetch) / 25)
title = f"Donations for {clan.name}"
ctx.icon = "https://cdn.discordapp.com/emojis/301032036779425812.png"
p = formats.TablePaginator(ctx,
data=fetch,
title=title,
page_count=page_count)
await p.paginate()
async def push_diff(self, ctx):
"""Returns list of clans ranked by score, showing the differential to the top clan."""
with Sql() as cursor:
cursor.execute(
"SELECT SUM(clanPoints) AS totals, clanName FROM vRCSPush30 "
"GROUP BY clanName "
"ORDER BY totals DESC")
fetch = cursor.fetchall()
top_score = fetch[0][0]
data = []
for row in fetch:
if row[0] == top_score:
data.append([f"{top_score:.0f}", row[1]])
else:
data.append([f"-{top_score - row[0]:.0f}", row[1]])
page_count = math.ceil(len(fetch) / 20)
title = "RCS Push Ranking Differentials"
ctx.icon = "https://cdn.discordapp.com/emojis/635642869738111016.png"
p = formats.TablePaginator(ctx,
data=data,
title=title,
page_count=page_count,
rows_per_table=20)
await p.paginate()
