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