async def list_accounts(ctx):
try:
guild_id = ctx.message.guild.id
accounts_str = ''
c.execute('select screen_name, channel_id from database where guild_id = (?)', (guild_id, ))
accounts = c.fetchall()
#Check that the list is not empty
if len(accounts) != 0:
embed_var = discord.Embed(title=f'Account list for {ctx.message.guild.name}', color=0x0)
accounts_str = ''
channel_name_str = ''
channel_id_str = ''
for account in accounts:
accounts_str += f'- {account[0]}\n'
channel_name_str += f'{client.get_channel(account[1])}\n'
channel_id_str += f'{account[1]}\n'
embed_var.add_field(name='Account', value=accounts_str, inline=True)
embed_var.add_field(name='Channel name', value=channel_name_str, inline=True)
embed_var.add_field(name='Channel ID', value=channel_id_str, inline=True)
await ctx.send(embed=embed_var)
else:
await ctx.send(f'Nothing registered for {ctx.message.guild.name}')
except Exception as e:
logger.exception(e)
print('GUILD : ' + ctx.message.guild.name + ' - ERROR : ' + str(e))
error_str = 'Error!\n `Code : {}`'.format(str(e))
await ctx.send(error_str)
def get_timestamp(guild_id, screen_name):
c.execute('select timestamp from database where guild_id = (?) and screen_name = (?)', (guild_id, screen_name))
row = c.fetchone()
if row is not None:
return row[0]
else:
#Just for safety, should never be executed
return 0
def remove_account(guild_id, screen_name):
#Look if the user is in the database for the server
c.execute('select * from database where screen_name = (?) and guild_id = (?)', (screen_name, guild_id))
row = c.fetchone()
if row is not None:
#User exists, remove it
c.execute('delete from database where screen_name = (?) and guild_id = (?)', (screen_name, guild_id))
conn.commit()
def remove_guilds():
guilds = []
guilds.clear()
for guild in client.guilds:
guilds.append(guild.id)
guilds_db = get_guilds()
trimmed_guilds = set(guilds) ^ set(guilds_db)
for guild in trimmed_guilds:
c.execute('delete from database where guild_id = (?)', (guild,))
conn.commit()
def get_accounts_and_channels(guild_id):
account_list = []
channel_list = []
account_list.clear()
channel_list.clear()
c.execute('select screen_name, channel_id from database where guild_id = (?)', (guild_id, ))
y = c.fetchall()
for x in y:
account_list.append(x[0])
channel_list.append(x[1])
return account_list, channel_list
def get_guilds():
guild_list = []
guild_list.clear()
c.execute('select guild_id from database')
y = c.fetchall()
#Convert to set to eliminate duplicates, then back to list
temp_set = set(y)
y = list(temp_set)
for x in y:
for guild in x:
guild_list.append(guild)
return guild_list
async def get_channel(ctx, screen_name):
try:
guild_id = ctx.message.guild.id
c.execute('select channel_id from database where guild_id = (?) and screen_name = (?)', (guild_id, screen_name))
row = c.fetchone()
if row is not None:
#Channel (and user) exist, post name and ID
await ctx.send(f'Channel ID: `{row[0]}`\nChannel name: {client.get_channel(row[0])}')
else:
#Channel (and user) not found
await ctx.send('Channel was not found for this user in the database.')
except Exception as e:
logger.exception(e)
print('GUILD : ' + ctx.message.guild.name + ' - ERROR : ' + str(e))
error_str = 'Error!\n `Code : {}`'.format(str(e))
await ctx.send(error_str)
async def remove_account(ctx, screen_name):
try:
guild_id = ctx.message.guild.id
#Look if the user is in the database for the server
c.execute('select * from database where screen_name = (?) and guild_id = (?)', (screen_name, guild_id))
row = c.fetchone()
if row is not None:
#User exists, remove it
c.execute('delete from database where screen_name = (?) and guild_id = (?)', (screen_name, guild_id))
await ctx.send(f'User {screen_name} has been removed from the database for {ctx.message.guild.name}')
else:
#User is not in the database
await ctx.send(f'User {screen_name} was not found in the database.')
conn.commit()
except Exception as e:
logger.exception(e)
print('GUILD : ' + ctx.message.guild.name + ' - ERROR : ' + str(e))
error_str = 'Error!\n `Code : {}`'.format(str(e))
await ctx.send(error_str)
async def add_account(ctx, screen_name, channel_id = None):
try:
if channel_id == None:
channel_id = ctx.message.channel.id
guild_id = ctx.message.guild.id
channel_id = int(channel_id)
#Check if the channel exists before doing anything else
if client.get_channel(channel_id) is None:
await ctx.send('Channel does not exist.')
return
#Make sure the account has no @
if screen_name.startswith('@'):
await ctx.send('Type the account name without the @.')
return
user = api.get_user(screen_name)
c.execute('select * from database where screen_name = (?) and guild_id = (?)', (screen_name, guild_id))
row = c.fetchone()
if row is None:
#User was not found in the database for the server
c.execute('insert into database values (?,?,0,?)', (guild_id, screen_name, channel_id))
await ctx.send(f'User {user.screen_name} has been added to {ctx.message.guild.name}.\nChannel: `{channel_id}`')
else:
#User was found, check if channel_id is the same
c.execute('select channel_id from database where screen_name = (?) and guild_id = (?)', (screen_name, guild_id))
row = c.fetchone()
if row[0] == channel_id:
#Channel_id is the same, no need to change it
await ctx.send('User and channel are already registered.')
else:
#Channel_id is different, update it
c.execute('update database set channel_id = (?) where guild_id = (?) and screen_name = (?)', (channel_id, guild_id, screen_name))
await ctx.send(f'Channel was updated for user {user.screen_name}')
conn.commit()
except Exception as e:
logger.exception(e)
print('GUILD : ' + ctx.message.guild.name + ' - ERROR : ' + str(e))
error_str = 'Error!\n `Code : {}`'.format(str(e))
await ctx.send(error_str)
def update_timestamp(guild_id, screen_name, timestamp):
c.execute('update database set timestamp = (?) where guild_id = (?) and screen_name = (?)', (timestamp, guild_id, screen_name))
conn.commit()