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(): with Sql() as cursor: member_list = [] cursor.execute( f"SELECT builderHallLevel, playerName FROM rcs_members WHERE clanTag = %s " f"AND timestamp = (SELECT TOP 1 timestamp from rcs_members WHERE clanTag = %s " f"ORDER BY timestamp DESC) ORDER BY builderHallLevel DESC, vsTrophies DESC", (clan.tag[1:], clan.tag[1:])) fetch = cursor.fetchall() 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 clan_checks(self): """Check clans for member count, badge, etc.""" print("Starting clan checks") if date.today().weekday() != 2: return sql = "SELECT MAX(log_date_ AS max_date FROM rcs_task_log WHERE log_type_id = $1" row = self.bot.pool.fetchrow(sql, log_types['loc_check']) if row and row['max_date'] > date.today() - timedelta(days=7): return council_chat = self.guild.get_channel( settings['rcs_channels']['council']) bot_dev = self.guild.get_channel(settings['rcs_channels']['bot_dev']) with Sql(as_dict=True) as cursor: cursor.execute("SELECT clanTag, classification FROM rcs_data") fetch = cursor.fetchall() cursor.execute( "SELECT TOP 1 startTime, endTime FROM rcs_events " "WHERE eventType = 11 AND startTime < GETDATE() ORDER BY startTime DESC" ) cwl_fetch = cursor.fetchone() if cwl_fetch['endTime'] > datetime.utcnow(): cwl = True else: cwl = False for row in fetch: if row['classification'] != "family": clan_desc = "Verified Clan" clan_size = 35 else: clan_desc = "Family Clan" clan_size = 25 clan = await self.bot.coc.get_clan(f"#{row['clanTag']}") if clan.badge.medium not in league_badges: embed = discord.Embed(title=clan.name, description=f"Clan Level: {clan.level}", color=discord.Color.red()) embed.set_thumbnail(url=clan.badge.medium) embed.set_footer(text="Incorrect Badge", icon_url=clan.badge.small) await council_chat.send(embed=embed) await bot_dev.send( f"{clan.name}\n{clan.badge.medium}\nJust in case...") if clan.member_count < clan_size and not cwl: embed = discord.Embed(title=clan.name, description=clan_desc, color=discord.Color.red()) embed.add_field(name="Low Membership:", value=f"{clan.member_count} Members") embed.add_field(name="In-gameLink", value=f"[Click Here]({clan.share_link})") await council_chat.send(embed=embed) if clan.type != "inviteOnly": embed = discord.Embed(title=clan.name, description=f"Type: {clan.type}", color=discord.Color.red()) embed.set_thumbnail(url=clan.badge.medium) embed.set_footer( text="Type not set to Invite Only", icon_url="https://coc.guide/static/imgs/gear_up.png") await council_chat.send(embed=embed)
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(): with Sql() as cursor: cursor.execute( "SELECT defenceWins, playerName FROM rcs_members WHERE clanTag = %s " "AND timestamp = (SELECT TOP 1 timestamp from rcs_members WHERE clanTag = %s " "ORDER BY timestamp DESC) ORDER BY defenceWins DESC", (clan.tag[1:], clan.tag[1:])) fetch = cursor.fetchall() 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(): with Sql() as cursor: cursor.execute( f"SELECT warStars, playerName FROM rcs_members WHERE clanTag = %s " f"AND timestamp = (SELECT TOP 1 timestamp from rcs_members WHERE clanTag = %s " f"ORDER BY timestamp DESC) ORDER BY warStars DESC", (clan.tag[1:], clan.tag[1:])) fetch = cursor.fetchall() 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 kick(self, ctx, player, *, reason: str = ""): """Command to remove players from the clan. This will move member on the Oak Table to Old Members, add a reason if provided, and remove their Member role from Discord.""" if ctx.command == "ban": ban = 1 else: ban = 0 with Sql() as cursor: if player.startswith("#"): sql = "SELECT playerName, tag, slackId FROM coc_oak_players WHERE tag = ?" cursor.execute(sql, player[1:]) else: sql = "SELECT playerName, tag, slackId FROM coc_oak_players WHERE playerName = ?" cursor.execute(sql, player) fetched = cursor.fetchone() if fetched is not None: discord_id = fetched.slackId player_tag = fetched.tag self.bot.coc.remove_player_updates(player_tag) sheet = spreadsheet.worksheet("Current Members") result = sheet.get(curr_member_range) row_num = 3 found = 0 for row in result: if player.lower() == row[0].lower(): found = 1 break else: row_num += 1 if found == 1: # Make call to Google Sheet with info to perform move action url = (f"{settings['google']['oak_table']}?call=kick&rowNum={str(row_num)}&reason={reason}" f"&ban={str(ban)}&source=Arborist") async with ctx.session.get(url) as r: if r.status != 200: await ctx.send("Please check the Oak Table. Removal was not successful.") content = f"{player} (#{player_tag}) has been moved to old members." if discord_id: guild = ctx.bot.get_guild(settings['discord']['oakguild_id']) is_user, user = is_discord_user(guild, int(discord_id)) if is_user and not ban: await user.remove_roles(guild.get_role(settings['oak_roles']['member']), reason=reason) content += " Member role has been removed." if is_user and ban: await user.kick(reason=reason) content += f" {user.mention} kicked from server. If Discord ban is necessary, now is the time!" self.bot.logger.info(f"{ctx.command} by {ctx.author} in {ctx.channel} | " f"{player} {ctx.command}ed for {reason}") await ctx.send(content) else: self.bot.logger.warning(f"{ctx.command} by {ctx.author} in {ctx.channel} | " f"Problem: {player} not found in Oak Table") return await ctx.send("Player name not found in Oak Table. Please try again.") else: self.bot.logger.warning(f"{ctx.command} by {ctx.author} in {ctx.channel} | " f"Problem: {player} not found in SQL Database") return await ctx.send("You have provided an invalid player name. Please try again.")
async def discord_check(self): """Check members and notify clan leaders to confirm they are still in the clan""" if datetime.utcnow().hour != 16: return # THIS IS THE BEGINNING OF THE NAME CHECKS danger_channel = self.guild.get_channel( settings['rcs_channels']['danger_bot']) botdev_channel = self.guild.get_channel( settings['rcs_channels']['bot_dev']) member_role = self.guild.get_role(settings['rcs_roles']['members']) with Sql() as cursor: cursor.execute( "SELECT shortName, discordTag, clanName FROM rcs_vwDiscordClans ORDER BY clanName" ) fetch = cursor.fetchall() daily_clans = [{ "short_name": row[0], "leader_tag": row[1], "clan_name": row[2] } for row in fetch] for clan in daily_clans: report_list = set() short_list = clan['short_name'].split("/") for short_name in short_list: if short_name != "reddit": regex = r"[|(\[]\W*(reddit |.*\/)?{}".format(short_name) else: regex = r"\Wreddit[^\s]|\Wreddit$" for member in self.guild.members: if member_role in member.roles \ and re.search(regex, member.display_name.lower(), re.IGNORECASE): report_list.add( f"{member.display_name.replace('||', '|')} ({member.id})" ) if report_list: await danger_channel.send( f"<@{clan['leader_tag']}> Please check the following list of " f"members to make sure everyone is still in your clan " f"(or feeder).") clan_header = f"Results for {clan['clan_name']}" content = "" for entry in report_list: content += f"\u2800\u2800{entry}\n" await self.send_embed(danger_channel, clan_header, content) # if clan['clan_name'] in ["Ninja Killers", "Faceless Ninjas"]: # requests.post(settings['rcsHooks']['ninjas']) else: await botdev_channel.send(f"No members for {clan['clan_name']}" ) # Add to task log sql = ("INSERT INTO rcs_task_log (log_type_id, log_date, argument) " "VALUES ($1, $2, $3)") try: await self.bot.pool.execute(sql, log_types['discord_check'], date.today(), f"{len(daily_clans)} clans processed") except: self.bot.logger.exception("RCS Task Log insert fail")
def get_active_wars(): with Sql() as cursor: sql = "SELECT '#' + clanTag as tag, war_id FROM rcs_wars WHERE warState <> 'warEnded'" cursor.execute(sql) fetch = cursor.fetchall() wars = {} for row in fetch: wars[row.tag] = row.war_id return wars
def get_member_list(field): with Sql() as cursor: cursor.execute( f"SELECT TOP 10 {field}, playerName + ' (' + altName + ')' as pname FROM rcs_members " f"INNER JOIN rcs_data ON rcs_data.clanTag = rcs_members.clanTag " f"AND timestamp = (SELECT MAX(timestamp) FROM rcs_members WHERE timestamp < " f"(SELECT MAX(timestamp) FROM rcs_members)) ORDER BY {field} DESC" ) fetch = cursor.fetchall() return fetch
def get_clan(tag): """Retrieve the details of a specific clan (provide clan tag without hashtag)""" with Sql() as cursor: sql = ( "SELECT clanName, subReddit, clanLeader, cwlLeague, discordServer, feeder, " "classification, discordTag as leaderTag " "FROM rcs_data " "WHERE clanTag = ?") cursor.execute(sql, tag) clan = cursor.fetchone() return clan
async def push_top(self, ctx): """Returns list of top 10 players for each TH level.""" # TODO change author icon with TH with Sql() as cursor: cursor.execute("SELECT currentTrophies, playerName " "FROM rcspush_vwTopTenByTh " "ORDER BY th DESC, currentTrophies DESC") fetch = cursor.fetchall() ctx.icon = "https://cdn.discordapp.com/emojis/635642869738111016.png" p = formats.TopTenPaginator(ctx, data=fetch) await p.paginate()
def rcs_names_tags(): """Retrieve and cache all RCS clan names and tags""" with Sql() as cursor: sql = "SELECT clanName, clanTag, altName FROM rcs_data ORDER BY clanName" cursor.execute(sql) fetch = cursor.fetchall() clans = {} for clan in fetch: clans[clan.clanName.lower()] = clan.clanTag clans[clan.altName.lower()] = clan.clanTag return clans
async def reddit(self, ctx, *, clan: ClanConverter = None): """Displays a link to specified clan's subreddit""" if not clan: return await ctx.send("You must provide an RCS clan name or tag.") with Sql(as_dict=True) as cursor: cursor.execute( f"SELECT subReddit FROM rcs_data WHERE clanTag = '{clan.tag[1:]}'" ) fetched = cursor.fetchone() if fetched['subReddit'] != "": await ctx.send(fetched['subReddit']) else: await ctx.send("This clan does not have a subreddit.")
async def discord(self, ctx, *, clan: ClanConverter = None): """Displays a link to specified clan's Discord server""" if not clan: return await ctx.send("You must provide an RCS clan name or tag.") async with ctx.typing(): with Sql(as_dict=True) as cursor: cursor.execute( f"SELECT discordServer FROM rcs_data WHERE clanTag = '{clan.tag[1:]}'" ) fetch = cursor.fetchone() if fetch['discordServer']: await ctx.send(fetch['discordServer']) else: await ctx.send("This clan does not have a Discord server.")
def rcs_tags(prefix=False): """Retrieve and cache clan tags for all RCS clans""" if prefix: field = "'#' + clanTag as tag" else: field = "clanTag as tag" with Sql() as cursor: sql = f"SELECT {field} FROM rcs_data ORDER BY clanName" cursor.execute(sql) fetch = cursor.fetchall() clans = [] for clan in fetch: clans.append(clan.tag) return clans
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 new_cwl(self, ctx, start_date, cwl_length: int = 9): """Command to add new CWL dates to SQL database Bot owner only""" # TODO Update postgres as well with Sql(as_dict=True) as cursor: start_day = int(start_date[8:9]) end_day = str(start_day + cwl_length) end_date = start_date[:9] + end_day season = start_date[:7] cursor.execute("SELECT MAX(eventId) as eventId FROM rcs_events WHERE eventType = 11") row = cursor.fetchone() event_id = row['eventId'] + 1 sql = (f"INSERT INTO rcs_events (eventId, eventType, startTime, endTime, season) " f"VALUES (%d, %d, %s, %s, %s)") cursor.execute(sql, (event_id, 11, start_date, end_date, season)) await ctx.send(f"New cwl info added to database.")
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 new_games(self, ctx, start_date, games_length: int = 6, ind_points: int = 4000, clan_points: int = 50000): """Command to add new Clan Games dates to SQL database Bot owner only""" start_day = int(start_date[8:9]) end_day = str(start_day + games_length) end_date = start_date[:9] + end_day with Sql(as_dict=True) as cursor: cursor.execute("SELECT MAX(eventId) as eventId FROM rcs_events WHERE eventType = 5") row = cursor.fetchone() event_id = row['eventId'] + 1 sql = ("INSERT INTO rcs_events (eventId, eventType, startTime, endTime, playerPoints, clanPoints) " "VALUES (%d, %d, %s, %s, %d, %d)") cursor.execute(sql, (event_id, 5, start_date, end_date, ind_points, clan_points)) sql = ("INSERT INTO rcs_events (event_type, start_time, end_time, player_points, clan_points) " "VALUES ($1, $2, $3, $4, $5)") await self.bot.pool.execute(sql, 5, datetime.strptime(start_date, "%Y-%m-%d"), datetime.strptime(end_date, "%Y-%m-%d"), ind_points, clan_points) await ctx.send(f"New games info added to database.")
async def no_clan(self): """Check all discord members to see if they have a clan name in their display name""" if date.today().weekday() != 0 or datetime.utcnow().hour != 0: return member_role = self.guild.get_role(settings['rcs_roles']['members']) mods_channel = self.guild.get_channel(settings['rcs_channels']['mods']) with Sql() as cursor: cursor.execute( "SELECT shortName, clanName FROM rcs_data ORDER BY clanName") fetch = cursor.fetchall() clan_list = [] for row in fetch: if "/" in row[0]: for clan in row[0].split("/"): clan_list.append(clan) else: clan_list.append(row[0]) no_clan_list = [] for member in self.guild.members: if member_role in member.roles: test = 0 for short_name in clan_list: if short_name in member.display_name.lower(): test = 1 break if test == 0: no_clan_list.append( f"{member.mention} did not identify with any clan.") if no_clan_list: log_message = f"{len(no_clan_list)} members found without a clan" content = "**We found some Members without a clan:**\n" content += "\n ".join(no_clan_list) await mods_channel.send(content) else: log_message = "All members have a happy home with a clan in their name." # Add to task log sql = ("INSERT INTO rcs_task_log (log_type_id, log_date, argument) " "VALUES ($1, $2, $3)") try: await self.bot.pool.execute(sql, log_types['no_clan'], date.today(), log_message) except: self.bot.logger.exception("RCS Task Log insert fail")
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 sql(self, ctx, *, query: str): """Run some SQL.""" query = self.cleanup_code(query) is_multistatement = query.count(';') > 1 # if is_multistatement: # # fetch does not support multiple statements # strategy = ctx.db.execute # else: # strategy = ctx.db.fetch try: start = time.perf_counter() with Sql(as_dict=True) as cursor: cursor.execute(query) results = cursor.fetchall() dt = (time.perf_counter() - start) * 1000.0 except Exception: return await ctx.send(f'```py\n{traceback.format_exc()}\n```') rows = len(results) if is_multistatement or rows == 0: return await ctx.send(f'`{dt:.2f}ms: {results}`') headers = list(results[0].keys()) table = TabularData() table.set_columns(headers) table.add_rows(list(r.values()) for r in results) render = table.render() fmt = f'```{query}\n\n{render}\n```\n*Returned {plural(rows):row} in {dt:.2f}ms*' if len(fmt) > 2000: fp = io.BytesIO(fmt.encode('utf-8')) await ctx.send('Too many results...', file=discord.File(fp, 'results.txt')) else: await ctx.send(fmt)
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 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()
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 player(self, ctx, *, player: PlayerConverter = None): """Provide details on the specified player""" if not player: self.bot.logger.warning(f"{ctx.command} by {ctx.author} in {ctx.channel} | " f"Problem: No valid player name or tag was provided.") return await ctx.send(f"{emojis['other']['redx']} You must provide a valid in-game name or tag for this " f"command. Try `/player TubaKid`") # pull non-in-game stats from db with Sql() as cursor: sql = (f"SELECT tag, numWars, avgStars, warStats, joinDate, slackId " f"FROM coc_oak_players " f"WHERE tag = ?") cursor.execute(sql, player.tag[1:]) oak_stats = cursor.fetchone() try: if not oak_stats: self.bot.logger.warning(f"{ctx.command} by {ctx.author} in {ctx.channel} | " f"Problem: {player.name} not found in SQL database") return await ctx.send(f"{emojis['other']['redx']} The player you provided was not found in the " f"database. Please try again.") except: self.bot.logger.error(f"{ctx.command} by {ctx.author} in {ctx.channel} | " f"Unknown error has occurred") return await ctx.send(f"{emojis['other']['redx']} Something has gone horribly wrong. " f"<@251150854571163648> I was trying to look up {player.name} " f"but the world conspired against me.") # retrieve player info from coc.py player_tag = f"#{oak_stats.tag}" player = await self.bot.coc.get_player(player_tag) troop_levels = builder_levels = spell_levels = hero_levels = hero_pets_levels = builder_hero = \ sm_levels = super_troop_levels = "" sm_troops = enums.SIEGE_MACHINE_ORDER super_troops = enums.SUPER_TROOP_ORDER count = 0 for troop in player.home_troops: if troop.name in super_troops: # We're ignoring super troops at this time continue if troop.name not in sm_troops: count += 1 if troop.name == "Minion": count = 1 if troop_levels[-2:] == "\n": troop_levels += "\n" else: troop_levels += "\n\n" if troop.name not in enums.HERO_PETS_ORDER: troop_levels += f"{emojis['troops'][troop.name]}{str(troop.level)} " if count % 6 == 0: troop_levels += "\n" else: sm_levels += f"{emojis['siege'][troop.name]}{str(troop.level)} " count = 0 for spell in player.spells: count += 1 if spell.name == "Poison Spell" and spell_levels[-2:] != "\n": spell_levels += "\n" count = 1 spell_levels += f"{emojis['spells'][spell.name]}{str(spell.level)} " count = 0 # Handle Super Troops for troop in player.home_troops: if troop.name in super_troops: count += 1 if troop.is_active: super_troop_levels += f"{emojis['super_troops_active'][troop.name]}{str(troop.level)} " else: super_troop_levels += f"{emojis['super_troops'][troop.name]}{str(troop.level)} " if count % 6 == 0: super_troop_levels += "\n" count = 0 for troop in player.builder_troops: count += 1 builder_levels += f"{emojis['build_troops'][troop.name]}{str(troop.level)} " if count % 6 == 0: builder_levels += "\n" # Test for number of heroes if len(player.heroes) > 0: for hero in player.heroes: if hero.name != "Battle Machine": hero_levels += f"{emojis['heroes'][hero.name]}{str(hero.level)} " else: builder_hero = f"{emojis['heroes'][hero.name]}{str(hero.level)}" for troop in player.home_troops: if troop.name in enums.HERO_PETS_ORDER: hero_pets_levels += f"{emojis['hero_pets'][troop.name]}{str(troop.level)} " embed = discord.Embed(title=f"{emojis['league'][leagues_to_emoji[player.league.name]]} " f"{player.name} " f"({player.tag})", color=color_pick(226, 226, 26)) embed.add_field(name="Town Hall", value=f"{emojis['th_icon'][player.town_hall]} {str(player.town_hall)}", inline=True) embed.add_field(name="Trophies", value=player.trophies, inline=True) embed.add_field(name="Best Trophies", value=player.best_trophies, inline=True) embed.add_field(name="War Stars", value=player.war_stars, inline=True) embed.add_field(name="Attack Wins", value=player.attack_wins, inline=True) embed.add_field(name="Defense Wins", value=player.defense_wins, inline=True) embed.add_field(name="Wars in Oak", value=oak_stats.numWars, inline=True) embed.add_field(name="Avg. Stars per War", value=str(round(oak_stats.avgStars, 2)), inline=True) embed.add_field(name="This Season", value=oak_stats.warStats, inline=False) embed.add_field(name="Troop Levels", value=troop_levels, inline=False) if super_troop_levels != "": embed.add_field(name="Super Troops", value=super_troop_levels, inline=False) if sm_levels != "": embed.add_field(name="Siege Machines", value=sm_levels, inline=False) embed.add_field(name="Spell Levels", value=spell_levels, inline=False) if hero_levels != "": embed.add_field(name="Heroes", value=hero_levels, inline=False) if hero_pets_levels != "": embed.add_field(name="Hero Pets", value=hero_pets_levels, inline=False) embed.add_field(name="Builder Hall Level", value=f"{emojis['bh_icon'][player.builder_hall]} {str(player.builder_hall)}", inline=False) embed.add_field(name="Versus Trophies", value=str(player.versus_trophies), inline=True) embed.add_field(name="Versus Battle Wins", value=str(player.versus_attack_wins), inline=True) embed.add_field(name="Best Versus Trophies", value=str(player.best_versus_trophies), inline=True) embed.add_field(name="Troop Levels", value=builder_levels, inline=False) if builder_hero != "": embed.add_field(name="Hero", value=builder_hero, inline=False) embed.set_footer(icon_url=player.clan.badge.url, text=f"Member of Reddit Oak since {oak_stats.joinDate.strftime('%e %B, %Y')}") self.bot.logger.debug(f"{ctx.command} by {ctx.author} in {ctx.channel} | " f"Request complete: /player {player.name}") await ctx.send(embed=embed)
async def cwl(self, ctx, *args): """Allows for specifying what CWL league your clan is in. **Example:** ++cwl list - Shows list of RCS clans in their leagues ++cwl Reddit Example Master II - assigns your clan to the specified league """ # Respond with list if args[0] in ["all", "list"]: with Sql(as_dict=True) as cursor: cursor.execute( "SELECT clanName, clanTag, cwlLeague FROM rcs_data " "WHERE cwlLeague IS NOT NULL " "ORDER BY clanName") clans = cursor.fetchall() content = "" for league in cwl_league_order: header = f"**{league}:**\n" temp = "" for clan in clans: if clan['cwlLeague'] == league: temp += f" {clan['clanName']} (#{clan['clanTag']})\n" if temp: content += header + temp return await ctx.send(content) # Handle user arguments with Sql(as_dict=True) as cursor: cursor.execute( "SELECT clanName, clanTag, discordTag FROM rcs_data ORDER BY clanName" ) fetch = cursor.fetchall() clans = [] clans_tag = [] for clan in fetch: clans.append(clan["clanName"].lower()) clans_tag.append( [clan["clanTag"], clan["clanName"], clan["discordTag"]]) leagues = cwl_league_names league_num = "I" if args[-1].lower() in ["3", "iii", "three"]: league_num = "III" if args[-1].lower() in ["2", "ii", "two"]: league_num = "II" if len(args) == 4: clan = f"{args[0]} {args[1]}" league = f"{args[2]} {league_num}" elif len(args) == 3: clan = f"{args[0]}" league = f"{args[1]} {league_num}" elif len(args) == 5: clan = f"{args[0]} {args[1]} {args[2]}" league = f"{args[3]} {league_num}" else: return await ctx.send( "Please provide a clan name and CWL league in that order. " "`++cwl Reddit Example Bronze II`") self.bot.logger.debug( f"{ctx.command} for {ctx.author}\n{args}\n{clan}\n{league}") if clan.lower() in clans and league.lower() in leagues: if args[-2].lower() in ["master", "masters"]: league = f"Master {league_num}" elif args[-2].lower() in [ "champ", "champs", "champion", "champions" ]: league = f"Champion {league_num}" else: league = f"{args[-2].title()} {league_num}" for clan_tuple in clans_tag: if clan.lower() == clan_tuple[1].lower(): clan = clan_tuple[1] clan_tag = clan_tuple[0] leader = clan_tuple[2] break cursor.execute(f"UPDATE rcs_data " f"SET cwlLeague = '{league}' " f"WHERE clanTag = '{clan_tag}'") await ctx.send("Update complete!") if str(ctx.author.id) != str(leader): try: leader_chat = self.bot.get_channel( settings["rcs_channels"]["leader_chat"]) await leader_chat.send( f"<@{leader}> {clan}'s CWL league has been updated to {league} " f"by {ctx.author.mention}.") await ctx.send("Update complete!") except: self.bot.logger.exception( "Failed to send to Leader Chat") else: return await ctx.send( "Please provide a clan name and CWL league in that order. " "`++cwl Reddit Example Bronze ii`")
async def oak_data_push(self): """Update SQL database with latest info from API""" now = datetime.utcnow() with Sql() as cursor: try: clan = await self.bot.coc.get_clan(clans['Reddit Oak']) sql1 = ( "INSERT INTO coc_oak (tag, playerName, XPLevel, trophies, donations, donReceived, league, " "leagueIcon, thLevel, warStars, attackWins, defenseWins, bestTrophies, vsTrophies, bestVsTrophies, " "versusBattleWins, builderHall, timestamp) " "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)") sql2 = ( "UPDATE coc_oak " "SET barbKing = ?, archQueen = ?, grandWarden = ?, royalChamp = ?, battleMachine = ?, " "clanGames = ?, wallWrecker = ?, battleBlimp = ?, stoneSlammer = ?, siegeBarracks = ? " "WHERE tag = ? AND timestamp = ?") self.bot.logger.debug("Starting member loop for SQL") to_google = [] async for m in clan.get_detailed_members(): clan_games = m.get_achievement( "Games Champion").value if m.get_achievement( "Games Champion") else 0 barb_king = m.get_hero( "Barbarian King").level if m.get_hero( "Barbarian King") else 0 arch_queen = m.get_hero( "Archer Queen").level if m.get_hero( "Archer Queen") else 0 grand_warden = m.get_hero( "Grand Warden").level if m.get_hero( "Grand Warden") else 0 royal_champ = m.get_hero( "Royal Champion").level if m.get_hero( "Royal Champion") else 0 battle_mach = m.get_hero( "Battle Machine").level if m.get_hero( "Battle Machine") else 0 wall_wrecker = m.siege_machines[0].level if len( m.siege_machines) > 0 else 0 battle_blimp = m.siege_machines[1].level if len( m.siege_machines) > 1 else 0 stone_slammer = m.siege_machines[2].level if len( m.siege_machines) > 2 else 0 barracks = m.siege_machines[3].level if len( m.siege_machines) > 3 else 0 cursor.execute(sql1, m.tag[1:], m.name, m.exp_level, m.trophies, m.donations, m.received, m.league.name, m.league.icon.url, m.town_hall, m.war_stars, m.attack_wins, m.defense_wins, m.best_trophies, m.versus_trophies, m.best_versus_trophies, m.versus_attack_wins, m.builder_hall, now) cursor.execute(sql2, barb_king, arch_queen, grand_warden, royal_champ, battle_mach, clan_games, wall_wrecker, battle_blimp, stone_slammer, barracks, m.tag[1:], now) # Prep dict for Google to_google.append({ "tag": m.tag, "townHall": m.town_hall, "warStars": m.war_stars, "attackWins": m.attack_wins, "defenseWins": m.defense_wins, "bestTrophies": m.best_trophies, "barbKing": barb_king, "archQueen": arch_queen, "grandWarden": grand_warden, "batMach": battle_mach, "builderHallLevel": m.builder_hall, "versusTrophies": m.versus_trophies, "bestVersusTrophies": m.best_versus_trophies, "versusBattleWins": m.versus_attack_wins, "clanGames": clan_games, "name": m.name, "expLevel": m.exp_level, "trophies": m.trophies, "donations": m.donations, "donationsReceived": m.received, "clanRank": 0, "league": m.league.name, "role": m.role.name }) except: self.bot.logger.exception( "Background failed. You may need to reload. <@251150854571163648>" ) self.bot.logger.debug("Done with SQL - Starting Google") payload = {"type": "players", "data": to_google} url = "https://script.google.com/macros/s/AKfycbzhXbO1CCcRuPzTU0mos7MowcucvclAKokkTiq91463xW1ftQEO/exec" requests.post(url, data=json.dumps(payload)) self.bot.logger.info("Oak data push complete.")
async def leader_notes(self): """Check the leader-notes channel and see if any of those players are in an RCS clan""" if datetime.utcnow().hour != 16: return danger_channel = self.bot.get_channel( settings['rcs_channels']['danger_bot']) notes_channel = self.bot.get_channel( settings['rcs_channels']['leader_notes']) messages = "" async for message in notes_channel.history(limit=None, oldest_first=True): if message.content: messages += message.content + " - " regex = r"[tT]ag:\s[PYLQGRJCUV0289]+|#[PYLQGRJCUV0289]{6,}" ban_list = [] for match in re.finditer(regex, messages): if not match.group().startswith("#"): new_match = match.group().upper().replace("TAG: ", "#") else: new_match = match.group().upper() if new_match not in ban_list: ban_list.append(new_match) self.bot.logger.debug(f"ban_list has {len(ban_list)} items") for tag in ban_list: if len(tag) < 6: self.bot.logger.debug(f"Short tag: {tag}") try: player = await self.bot.coc.get_player(tag) if player.clan and player.clan.tag[1:] in rcs_tags(): with Sql() as cursor: sql = ("SELECT COUNT(timestamp) AS reported " "FROM rcs_notify " "WHERE memberTag = %s AND clanTag = %s") cursor.execute(sql, (player.tag[1:], player.clan.tag[1:])) row = cursor.fetchone() reported = row[0] if reported < 3: clan = get_clan(player.clan.tag[1:]) await danger_channel.send(f"<@{clan['leaderTag']}>" ) embed = discord.Embed( color=discord.Color.dark_red()) embed.add_field( name="Leader Note found:", value= f"{player.name} ({player.tag}) is in {player.clan.name}. Please " f"search for `in:leader-notes {player.tag}` for details." ) embed.set_footer( text= "Reminder: This is not a ban list, simply information that this " "member has caused problems in the past.") await danger_channel.send(embed=embed) sql = ("INSERT INTO rcs_notify " "VALUES (%s, %s, %s)") cursor.execute( sql, (datetime.now().strftime('%m-%d-%Y %H:%M:%S'), player.clan.tag[1:], player.tag[1:])) except coc.NotFound: self.bot.logger.warning(f"Exception on tag: {tag}") except: self.bot.logger.exception("Other failure") # Add to task log sql = ( "INSERT INTO rcs_task_log (log_type_id, log_date, argument) " "VALUES ($1, $2, $3)") try: await self.bot.pool.execute(sql, log_types['danger'], date.today(), f"{len(ban_list)} tags processed") except: self.bot.logger.exception("RCS Task Log insert error")
async def update_database(): # Sync changes from SQL to PSQL with Sql() as cursor: sql = ( "SELECT clanleader, socMedia, notes, feeder, classification, subReddit, leaderReddit, " "discordTag, shortName, altName, discordServer, clanTag, clanName FROM rcs_data" ) cursor.execute(sql) fetch = cursor.fetchall() insert_sql = ( "INSERT INTO rcs_clans (leader_name, social_media, notes, family_clan, classification, " "subreddit, leader_reddit, discord_tag, short_name, alt_name, discord_server, clan_tag, " "clan_name) " "VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)" ) update_sql = ( "UPDATE rcs_clans " "SET leader_name = $1, social_media = $2, notes = $3, family_clan = $4, " "classification = $5, subreddit = $6, leader_reddit = $7, discord_tag = $8, " "short_name = $9, alt_name = $10, discord_server = $11 " "WHERE clan_tag = $12") for row in fetch: sql = "SELECT COUNT(*) AS num_found FROM rcs_clans WHERE clan_tag = $1" found = await conn.fetchval(sql, row[11]) if found: # Clan already exists in postgres, update it await conn.execute(update_sql, row[0], row[1], row[2], row[3], row[4], row[5], row[6], int(row[7]), row[8], row[9], row[10], row[11]) else: # Clan does not exist in postgres, insert it await conn.execute(insert_sql, row[0], row[1], row[2], row[3], row[4], row[5], row[6], int(row[7]), row[8], row[9], row[10], row[11], row[12]) # Let's try and delete clans that are not listed in MS SQL sql = "SELECT clan_tag FROM rcs_clans" fetch = await conn.fetch(sql) clan_list = [row['clan_tag'] for row in fetch] sql = "SELECT clanTag FROM rcs_data" cursor.execute(sql) sql_clans = cursor.fetchall() # After this, clan_list should only contain clans that should be removed for row in sql_clans: try: clan_list.remove(row[0]) except ValueError: # This will happen when if there is a value in MS SQL that is not in postgresql # Theoretically, this will never happen since we've upserted above pass if len(clan_list) > 0: self.bot.logger.info(f"Removing: {clan_list}") for tag in clan_list: member_sql = "DELETE FROM rcs_members WHERE clan_tag = $1" clan_sql = "DELETE FROM rcs_clans WHERE clan_tag = $1" await conn.execute(member_sql, tag) await conn.execute(clan_sql, tag) print("SQL synced to postgresql") # Start the update process sql = ( "SELECT clan_tag, leader_name, clan_level, classification, war_wins, win_streak " "FROM rcs_clans ORDER BY clan_name") fetch = await conn.fetch(sql) leader_changes = "" for row in fetch: clan = await self.bot.coc.get_clan(row['clan_tag']) description = re.sub(r"[^a-zA-Z/.,!\s\d]+", "", clan.description) if not clan.public_war_log: clan.war_ties = 0 clan.war_losses = 0 # compare clan leader and report to council chat if different clan_leader = clan.get_member_by(role=coc.Role.leader) comparator = (not leader(clan.tag[1:], clan_leader.tag[1:]) and row['classification'] != "family" and row['leader_name'] != clan_leader.name) if comparator: leader_changes += ( f"{clan.name}: Leader changed from {row['leader_name']} to {clan_leader.name}" f"({clan_leader.tag})\n") # Update MS SQL sql = ( "UPDATE rcs_data " "SET clanName = ?, clanLevel = ?, members = ?, warFreq = ?, clanType = ?, " "clanDescription = ?, clanLocation = ?, clanBadge = ?, clanPoints = ?, " "clanVersusPoints = ?, requiredTrophies = ?, warWinStreak = ?, warWins = ?, warTies = ?, " "warLosses = ?, isWarLogPublic = ? " "WHERE clanTag = ?") try: with Sql() as cursor: cursor.execute( sql, clan.name, clan.level, clan.member_count, clan.war_frequency, clan.type, description, clan.location.name, clan.badge.url, clan.points, clan.versus_points, clan.required_trophies, clan.war_win_streak, clan.war_wins, clan.war_ties, clan.war_losses, clan.public_war_log, clan.tag[1:]) except: self.bot.logger.exception("MSSQL fail") # Update Postgresql sql = ( "UPDATE rcs_clans " "SET clan_name = $1, clan_level = $2, member_count = $3, war_frequency = $4, clan_type = $5, " "clan_description = $6, clan_location = $7, badge_url = $8, clan_points = $9, " "clan_vs_points = $10, required_trophies = $11, win_streak = $12, war_wins = $13, " "war_ties = $14, war_losses = $15, war_log_public = $16, cwl_league = $17 " "WHERE clan_tag = $18") try: cwl_league = clan.war_league.name.replace("League ", "") await conn.execute( sql, clan.name, clan.level, clan.member_count, clan.war_frequency, clan.type, description, clan.location.name, clan.badge.url, clan.points, clan.versus_points, clan.required_trophies, clan.war_win_streak, clan.war_wins, clan.war_ties, clan.war_losses, clan.public_war_log, cwl_league, clan.tag[1:]) except: self.bot.logger.exception("postgreSQL fail") if leader_changes and 8 < now.hour < 12: embed = discord.Embed(color=discord.Color.dark_red()) embed.add_field(name="Leader Changes", value=leader_changes) embed.add_field( name="Disclaimer", value="These changes may or may not be permanent. " "Please investigate as appropriate.") embed.set_footer( text="++help alts (for help adjusting leader alts)", icon_url= "https://api-assets.clashofclans.com/badges/200/h8Hj8FDhK2b1PdkwF7fEb" "TGY5UkT_lntLEOXbiLTujQ.png") council_chat = self.bot.get_channel( settings['rcs_channels']['council']) await council_chat.send(embed=embed)
async def push_start(self, ctx): msg = await ctx.send("Starting process...") # start push start = time.perf_counter() player_list = [] async for clan in self.bot.coc.get_clans(rcs_tags()): if clan.tag == "#9L2PRL0U": # Change to in list if more than one clan bails continue for member in clan.itermembers: player_list.append(member.tag) team_boom = [ '#20PCPRJ8', '#2QG2C9LG8', '#288UUGPGG', '#YQCVUGJU', '#2G0YV209J', '#9P0PPJV8', '#9PYP8VY90', '#982V9288G', '#2Q8GQLU9R', '#22LPCGV8', '#RU9LYLG9', '#28VYCQGRU', '#P2P9QU8C2', '#GVJP200U', '#20CCV90UQ', '#Y9C2909R', '#2UL9UVCC2', '#89QQ9QRJ0', '#8JQGGU2Q0', '#GPUQYRJC', '#R8JVUGVU', '#V8UQ0G0L', '#G82J00P', '#8VQY0GP2', '#88Y0YL98P', '#80LPL9PRP', '#Y0RPYJPC', '#9L9VCYQQ2', '#9P8PCUY8L', '#YY82YY2Y', '#2LQPJVR0', '#2PYQR02GV', '#URLVC082', '#PJ8V0QUU', '#2LJJY8JGQ', '#CYUVGPPQ', '#PY90C2CY', '#L0GYY8V2', '#8L8PLY2Q2', '#LQY0UUV8V', '#2VVQJ9GG2', '#9Y9GCYRJJ', '#8R2QVYYG', '#2VCG8PPVU', '#2UPPC0GUC', '#8LVCUQGRG', '#2LQVURL9L', '#PCR8QGY9', '#2YCV2JRRJ', '#JLPC2GCU' ] player_list.extend(team_boom) print(len(player_list)) players_many = [] to_insert = [] async for player in self.bot.coc.get_players(player_list): players_many.append( (player.tag[1:], player.clan.tag[1:], player.trophies if player.trophies <= 5000 else 5000, player.trophies if player.trophies <= 5000 else 5000, player.best_trophies, player.town_hall, player.name.replace("'", "''"), player.clan.name)) to_insert.append({ "player_tag": player.tag[1:], "clan_tag": player.clan.tag[1:], "starting_trophies": player.trophies if player.trophies <= 5000 else 5000, "current_trophies": player.trophies if player.trophies <= 5000 else 5000, "best_trophies": player.best_trophies, "starting_th_level": player.town_hall, "player_name": player.name.replace("'", "''"), "clan_name": player.clan.name }) print("Player list assembled.") with Sql() as cursor: sql = (f"INSERT INTO rcspush_2020_1 " f"(playerTag, clanTag, startingTrophies, currentTrophies, " f"bestTrophies, startingThLevel, playerName, clanName) " f"VALUES (%s, %s, %d, %d, %d, %d, %s, %s)") cursor.executemany(sql, players_many) sql = ( "UPDATE rcspush_2020_1 SET clanTag = '9L2PRL0U' " "WHERE playerTag IN ('#20PCPRJ8', '#2QG2C9LG8', '#288UUGPGG', '#YQCVUGJU', '#2G0YV209J', " "'#9P0PPJV8', " "'#9PYP8VY90', '#982V9288G', '#2Q8GQLU9R', '#22LPCGV8', '#RU9LYLG9', '#28VYCQGRU', '#P2P9QU8C2', " "'#GVJP200U', '#20CCV90UQ', '#Y9C2909R', '#2UL9UVCC2', '#89QQ9QRJ0', '#8JQGGU2Q0', '#GPUQYRJC', " "'#R8JVUGVU', '#V8UQ0G0L', '#G82J00P', '#8VQY0GP2', '#88Y0YL98P', '#80LPL9PRP', '#Y0RPYJPC', " "'#9L9VCYQQ2', '#9P8PCUY8L', '#YY82YY2Y', '#2LQPJVR0', '#2PYQR02GV', '#URLVC082', '#PJ8V0QUU', " "'#2LJJY8JGQ', '#CYUVGPPQ', '#PY90C2CY', '#L0GYY8V2', '#8L8PLY2Q2', '#LQY0UUV8V', '#2VVQJ9GG2', " "'#9Y9GCYRJJ', '#8R2QVYYG', '#2VCG8PPVU', '#2UPPC0GUC', '#8LVCUQGRG', '#2LQVURL9L', '#PCR8QGY9', " "'#2YCV2JRRJ', '#JLPC2GCU')") cursor.execute(sql) conn = self.bot.pool sql = ( "INSERT INTO rcspush_2020_1 (player_tag, clan_tag, starting_trophies, current_trophies, best_trophies, " "starting_th_level, player_name, clan_name) " "SELECT x.player_tag, x.clan_tag, x.starting_trophies, x.current_trophies, x.best_trophies, " "x.starting_th_level, x.player_name, x.clan_name " "FROM jsonb_to_recordset($1::jsonb) as x (player_tag TEXT, clan_tag TEXT, starting_trophies INTEGER, " "current_trophies INTEGER, best_trophies INTEGER, starting_th_level INTEGER, player_name TEXT, " "clan_name TEXT)") await conn.execute(sql, to_insert) await msg.delete() await ctx.send(f"{len(player_list)} members added. Elapsed time: " f"{(time.perf_counter() - start) / 60:.2f} minutes")