def sql_set_admin_status(auth_user, user_id, new_status, cursor, cnx):
"""
Update the admin status associated with a user.
:param auth_user: id of user authorizing change
:param user_id: id of user whose admin status will be changed
:param new_status: true or false depending on admin status being set
:param cursor: cursor object to execute query
:param cnx: connection object to commit changes
:return: the new table after updating the user table
"""
check_admin_status(auth_user, True,
cursor) # see if the authorizing user is an admin
if check_user_exists(user_id, cursor) == -1:
raise UserNotFoundError()
if not (new_status.lower() == 'true'
or new_status.lower() == 'false'): # check for valid response
raise ResponseError()
cursor.execute('update user '
'set admin = %s '
'where user_id = %s',
(1 if new_status == "true" else 0, user_id))
cnx.commit() # commit changes to user table
cursor.execute('select * from user where user_id = %s',
(user_id, )) # get new user table
return cursor.fetchall()
def sql_add_user(auth_user, user_id, display_name, is_admin, cursor, cnx):
"""
Add a user to the user table.
:param auth_user: id of user authorizing add, None if performing an add_user bypass
:param user_id: numeric id of user to add
:param display_name: display name of user
:param is_admin: admin status of new user
:param cursor: cursor for executing query
:param cnx: connection object for committing change
:return: the new table after insertion or an error flag
"""
if auth_user is not None: # checks to see if auth_user bypass is enabled aka None for auth_user
check_admin_status(auth_user, True, cursor) # see if the authorizing user is an admin
if check_user_exists(user_id, cursor) != -1:
raise ExistingUserError()
if not (is_admin.lower() == 'true' or is_admin.lower() == 'false'): # check for valid response
raise ResponseError()
cursor.execute('insert into user '
'(user_id, display_name, admin) '
'values (%s, %s, %s)', (user_id, display_name, 1 if is_admin == "true" else 0))
cnx.commit() # commit changes to database
cursor.execute('select * from user where user_id = %s', (user_id,)) # get new user table
return cursor.fetchall()
def sql_delete_game(auth_user, name, cursor, cnx):
check_admin_status(auth_user, True,
cursor) # see if the authorizing user is an admin
if len(sql_query_game(name, cursor)) == 0:
raise GameNotFoundError
cursor.execute('delete from game where name = %s',
(name, )) # execute deletion query
cnx.commit() # commit changes to database
cursor.execute('select * from game') # get new user table
return cursor.fetchall()
def sql_add_game(auth_user, game_id, name, cursor, cnx):
check_admin_status(auth_user, True,
cursor) # see if the authorizing user is an admin
if len(sql_query_game(name, cursor)) > 0:
raise ExistingGameError
cursor.execute('insert into game '
'(game_id, name) '
'values (%s, %s)', (game_id, name)) # add new user
cnx.commit() # commit changes to database
cursor.execute('select * from game where name = %s',
(name, )) # get new user table
return cursor.fetchall()
def sql_edit_id(auth_user, name, game_id, cursor, cnx):
check_admin_status(auth_user, True,
cursor) # see if the authorizing user is an admin
if len(sql_query_game(name, cursor)) == 0:
raise GameNotFoundError
cursor.execute('update game '
'set game_id = %s '
'where name = %s',
(game_id, name)) # change the game table with game id
cnx.commit() # commit changes to user table
cursor.execute('select * from game where name = %s',
(name, )) # get new user table
return cursor.fetchall()
def sql_delete_event(auth_user, event_id, cursor, cnx):
"""
Delete an event based on its title.
:param auth_user: user requesting the delete (must be an admin to delete events)
:param event_id: id of the event to be deleted
:param cursor: cursor object for executing command
:param cnx: connection object for verifying change
:return: new event table after deletion
"""
check_admin_status(auth_user, True, cursor) # see if the authorizing user is an admin
cursor.execute('delete from event where event_id = %s', (event_id, )) # execute deletion query
cnx.commit() # commit changes to database
cursor.execute('select * from event where event_id = %s', (event_id,))
return cursor.fetchall()
def sql_edit_name(auth_user, old_name, new_name, cursor, cnx):
check_admin_status(auth_user, True,
cursor) # see if the authorizing user is an admin
if len(sql_query_game(old_name, cursor)) == 0:
raise GameNotFoundError
if len(sql_query_game(new_name, cursor)) > 0:
raise ExistingGameError
cursor.execute(
'update game '
'set name = %s '
'where name = %s',
(new_name, old_name)) # change the game table with new game name
cnx.commit() # commit changes to user table
cursor.execute('select * from game where name = %s',
(new_name, )) # get new user table
return cursor.fetchall()
def sql_delete_user(auth_user, user_id, cursor, cnx):
"""
Delete a row from the user table based on display name.
:param auth_user: id of user who is authorizing the delete
:param user_id: id of the user to be deleted
:param cursor: cursor object for executing query
:param cnx: connection object for committing changes
:return: the new table after deletion or an error flag
"""
check_admin_status(auth_user, True, cursor) # see if the authorizing user is an admin
check_admin_status(user_id, False, cursor) # see if the user to be deleted is an admin
if check_user_exists(user_id, cursor) == -1:
raise UserNotFoundError()
cursor.execute('delete from user where user_id = %s', (user_id,)) # execute deletion query
cnx.commit() # commit changes to database
cursor.execute('select * from user') # get new user table
return cursor.fetchall()
async def perf_update(self, ctx):
"""
Update Performance
:return: number of records updated
"""
user = str(ctx.author)
try:
check_admin_status(user, True, self.cursor)
if len(ctx.message.attachments) > 0:
file_url = ctx.message.attachments[0].url
if file_url[-3:].lower() == 'csv':
headers = {
'User-Agent':
'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) '
'Chrome/41.0.2228.0 Safari/537.3'
}
req = request.Request(url=file_url, headers=headers)
file_pointer = request.urlopen(req)
file = file_pointer.readlines()
if file[0].index(
b'user_id,event_id,kills,deaths,win,length,win_score,lose_score'
) > -1:
records = csv2dicts(file)
inserts, updates = 0, 0
for record in records:
u = sql_perf_update(record, self.cursor, self.cnx)
if u:
updates = updates + 1
else:
inserts = inserts + 1
msg = "%s records were updated, %s new records were inserted" % (
updates, inserts)
await ctx.send(msg)
else:
await ctx.send("File has no header")
else:
await ctx.send("File attached is not a csv")
else:
await ctx.send("No file attached")
except AdminPermissionError:
await ctx.send("You do not have the necessary permissions")
async def set_affiliate(self, ctx, user_id, status):
"""
Update a user's affiliation status
:param user_id: id of the user to set affiliation status
:param status: status of their affiliation (true / false)
:return: void
"""
try:
check_admin_status(ctx.author.id, False, self.cursor)
except AdminPermissionError:
await ctx.send(
"Error: You must be an admin to perform this command!")
status = status.lower()
if not (status == 'true'
or status == 'false'): # check for valid response
await ctx.send(
"Error: Must provide a valid status for setting affiliate status! (eg. true / false)"
)
return
try:
check_user_exists(user_id, self.cursor)
except UserNotFoundError:
await ctx.send(
"Error: Must provide a valid user id. This user doesn't exist!"
)
return
guild = ctx.guild
user = guild.get_member(int(user_id))
affiliate_role = get(guild.roles, name="Affiliate")
if status == 'true':
control_category = self.bot.get_channel(self.control_category_id)
shop_category = self.bot.get_channel(self.shop_category_id)
admin_role = get(guild.roles, name="Creator")
control_overwrites = {
guild.default_role:
discord.PermissionOverwrite(read_messages=False),
user:
discord.PermissionOverwrite(read_messages=True),
admin_role:
discord.PermissionOverwrite(read_messages=True)
}
shop_overwrites = {
guild.default_role:
discord.PermissionOverwrite(read_messages=False,
send_messages=False),
user:
discord.PermissionOverwrite(read_messages=True,
send_messages=False),
admin_role:
discord.PermissionOverwrite(read_messages=True,
send_messages=True)
}
control_channel = await control_category.create_text_channel(
user.name + 's control panel', overwrites=control_overwrites)
await control_channel.send(
"```Welcome to your shop control panel!\n\n"
"Here you can run commands to modify your shop.\n\n"
"Type *help to get started!```")
await control_channel.send(
"```Please note additional help on commands can be found on our documentation page:\n\n"
"https://docs.google.com/document/d/1CW68wzUiIkLB4gMXKQdhTLo8BhZQDTICi4i5kqe1iKA/edit?usp=sharing```"
)
shop_name = user.name + 's shop'
shop_desc = ""
shop_channel = await shop_category.create_text_channel(
shop_name, overwrites=shop_overwrites)
shop_results = [shop_channel.id, user.id, shop_desc, 0, -1]
shop_msg = await shop_channel.send(
create_shop_sign(user, shop_results, self.cursor))
create_user_control_panel(user_id, control_channel.id, self.cursor,
self.cnx)
create_user_shop(user_id, shop_channel.id, shop_name, shop_desc, 0,
shop_msg.id, self.cursor, self.cnx)
await user.add_roles(affiliate_role)
await ctx.send("Successfully updated " + user.name +
"'s affiliate status to true!")
else:
# Try to delete control channel
try:
control_channel_id = get_user_control_panel(
user_id, self.cursor)
await self.bot.get_channel(control_channel_id).delete()
delete_user_control_panel(user_id, self.cursor, self.cnx)
except ControlPanelNotFoundError:
pass
# Try to delete shop channel
try:
shop_channel_id = get_user_shop(user_id, self.cursor)
await self.bot.get_channel(shop_channel_id).delete()
delete_user_shop(user_id, self.cursor, self.cnx)
except ShopNotFoundError:
pass
await user.remove_roles(affiliate_role)
await ctx.send("Successfully updated " + user.name +
"'s affiliate status to false!")
