def get_user_times(discord_id: str, df_map: str, physics: str = 'all'):
"""
Retrieves a discord user's times from the local mdd database store.
:param discord_id: discord ID of the user who called the command.
:param df_map: the name of the map for which the user is requesting their times.
:param physics: if provided, the physics for which the user is requesting their times.
:return: a tabulated result of times filtered by the user's inputs.
"""
with db.connect() as conn:
# Read
select_statement = "select player_pos, total_times, time, physics, timestamp " \
"from mdd_records_ranked mdd join discord_ids discord " \
"on mdd.player_id=discord.mdd_id " \
"where discord_id = %s " \
"and map_name=%s " + ("and physics=%s" if physics != "all" else '')
replace_vars = (discord_id, df_map,
physics) if physics != 'all' else (discord_id, df_map)
result_set = conn.execute(select_statement, replace_vars)
rows = list()
for r in result_set: # for each time returned (can be multiple if the user did not specify physics)
rank = f"{r.player_pos}/{r.total_times}" # formats the user's position as <place>/<total times>
time = r.time
physics = r.physics
date = r.timestamp.strftime(
"%m/%d/%Y"
) # omits the hr:min:sec portion of the date for brevity
rows.append([rank, time, physics, date])
result_set.close()
if len(rows) > 0:
return f"```{tabulate(rows, headers=['Rank', 'Time', 'Physics', 'Date'])}```" # tabulates the list of rows
else:
return f"You have no such time(s) on this map"
def get_overall_user_stats(discord_id: str = None, mdd_id: int = None):
"""
Retrieves an user's mdd statistics across all physics.
:param discord_id: The calling user's discord id, used when calling !mystats
:param mdd_id: The desired user's mdd id, used when calling !userstats (not necessarily the calling user's id)
:return: A dictionary of statistical data to be processed into an embed.
"""
# Depending on whether the user is user !mystats or !userstats, the FROM and WHERE clauses will differ.
if discord_id is not None:
# if using !mystats, the calling user must have an entry in the discord_ids table, hence the join.
from_where = "FROM mdd_player_stats m JOIN discord_ids d ON m.player_id=d.mdd_id WHERE discord_id=%s"
replace_vars = (discord_id, )
else:
from_where = "FROM mdd_player_stats WHERE player_id=%s"
replace_vars = (mdd_id, )
with db.connect() as conn:
# Read
select_statement = f"SELECT \
country \
,player_id \
,player_name \
,SUM(total_times) as total_times \
,SUM(total_time_seconds) as total_time_logged \
,SUM(total_world_recs) as total_world_records \
,SUM(total_top3_recs) as total_top_3_times \
,SUM(total_top10_recs) as total_top_10_times \
{from_where} \
GROUP BY country, player_id, player_name"
result_set = conn.execute(select_statement, replace_vars)
if result_set.rowcount == 1:
for row in result_set:
stats_dict = dict(row)
# total seconds to human-readable
total_time = stats_dict['total_time_logged']
days, hours, minutes, secs = process_total_seconds_to_readable(
total_time)
stats_dict[
'total_time_logged'] = f"{days} days, {hours} hours, {minutes} minutes, and {secs} seconds"
# percentage calculations. I.e. what percentage of your times are world records, top3, and top 10.
total_times, top1, top3, top10 = [
stats_dict[datum] for datum in [
'total_times', 'total_world_records',
'total_top_3_times', 'total_top_10_times'
]
]
stats_dict[
'total_world_records'] = f"{top1} ({round(top1/total_times * 100, 2)}%)"
stats_dict[
'total_top_3_times'] = f"{top3} ({round(top3 / total_times * 100, 2)}%)"
stats_dict[
'total_top_10_times'] = f"{top10} ({round(top10 / total_times * 100, 2)}%)"
result_set.close()
return stats_dict
raise Exception(
"No statistics found. Use !myid to check or set your mdd id.")
def set_id(discord_id, mdd_id):
if validate_mdd_id(mdd_id):
with db.connect() as conn:
select_statement = f"INSERT INTO discord_ids (discord_id, mdd_id) " \
f"VALUES (%s, %s) " \
f"ON CONFLICT (discord_id) DO UPDATE " \
f"SET discord_id = excluded.discord_id, mdd_id = excluded.mdd_id;"
replace_vars = (discord_id, mdd_id)
conn.execute(select_statement, replace_vars)
return None
else:
return f"No profile with id {mdd_id} exists in the mdd rankings."
def get_id(discord_user):
discord_id = str(discord_user.id)
with db.connect() as conn:
select_statement = f"SELECT mdd_id FROM discord_ids WHERE discord_id=%s"
replace_vars = (discord_id, )
result_set = conn.execute(select_statement, replace_vars)
if result_set.rowcount == 1:
for row in result_set:
return f"{discord_user.display_name}, your mdd id is set to {row.mdd_id}." \
f" To set your mdd id, use !myid <mdd_id>"
else:
return f"{discord_user.display_name}, you do not have an mdd id tied to your account." \
f" To set your mdd id, use !myid <mdd_id>"
def get_random_map(modes=[]):
"""
Fetches a random map corresponding with mode filters if provided
:param modes: Descriptive filters to narrow down the search, i.e strafe for strafe-only, etc.
:return: Map data dictionary corresponding to the resulting map
"""
with db.connect() as conn:
QUERY_PARAMS = []
if "slick" in modes:
QUERY_PARAMS.append("func_slick_flg = B'1'")
if "weapon" in modes:
QUERY_PARAMS.append("(weap_gl_flg = B'1' OR weap_rl_flg = B'1' OR weap_pg_flg = B'1' OR weap_bf_flg = B'1')")
if "strafe" in modes:
QUERY_PARAMS.append("(weap_gl_flg = B'0' AND weap_rl_flg = B'0' AND weap_pg_flg = B'0' AND weap_bf_flg = B'0')")
if "long" in modes:
# TODO: How to make sure that all physics are long?
QUERY_PARAMS.append("map_nm IN (SELECT DISTINCT map_name FROM mdd_records_ranked WHERE player_pos = 1 AND time_seconds > 150.0 AND physics = 'cpm-run')")
if "deluxe" in modes:
# Blacklist
# Friendly reminder that all % wildcards must be doubled
# to avoid python tomfoolery
blacklist = [
"nice%%",
"igoodmap%%",
"moko%%",
"marvin%%",
"%%gvn%%",
"baulo%%",
"govno%%",
"%%wesp%%",
"%%ass%%",
"%%f*g%%",
"%%shit%%",
"line#%%"
]
blacklist_s = " AND ".join(f"map_nm NOT LIKE '{x}'" for x in blacklist)
# 50 Records (caveat: total_times is per physics, so maybe a lower value of 35-40 is better here for CPM only)
num_records_s = "map_nm IN (SELECT DISTINCT map_name FROM mdd_records_ranked WHERE total_times > 40)"
# Minimum time of 10 seconds
min_time_s = "map_nm NOT IN (SELECT DISTINCT map_name FROM mdd_records_ranked WHERE player_pos = 1 AND time_seconds < 10.0)"
# TODO Maybe use INTERSECT for these
QUERY_PARAMS.append(blacklist_s)
QUERY_PARAMS.append(num_records_s)
QUERY_PARAMS.append(min_time_s)
if len(QUERY_PARAMS) > 0:
QUERY_WHERE = "WHERE " + " AND ".join(QUERY_PARAMS)
select_statement = "SELECT map_nm, author, map_desc, rel_dt, phys_cd_str, func_cd_str, weap_cd_str, " \
"item_cd_str, func_cd_str " \
"FROM ws_maps " \
+ QUERY_WHERE
result_set = conn.execute(select_statement)
result_rows = result_set.fetchall()
offset = randint(0, len(result_rows) - 1)
r = result_rows[offset]
result_set.close()
else:
num_maps = 9251
offset = randint(1, num_maps)
# Read
select_statement = "SELECT map_nm, author, map_desc, rel_dt, phys_cd_str, func_cd_str, weap_cd_str, " \
"item_cd_str, func_cd_str " \
"FROM ws_maps " \
f"LIMIT 1 OFFSET {offset}"
result_set = conn.execute(select_statement)
r = result_set.first()
result_set.close()
map_name = r.map_nm
url = f"http://ws.q3df.org/map/{map_name}/"
map_data = dict()
map_data['name'] = map_name
map_data['levelshot_url'] = f"https://ws.q3df.org/images/levelshots/512x384/{map_name}.jpg?fallback=1"
map_data['url'] = url
fields = dict()
fields['Author'] = r.author
fields['Description'] = r.map_desc
fields['Release Date'] = str(r.rel_dt)
fields['Physics'] = r.phys_cd_str
opt_fields = {"Weapons": r.weap_cd_str, "Items": r.item_cd_str, "Functions": r.func_cd_str}
opt_fields = {key: val for key, val in opt_fields.items() if val != None} # remove None
fields['optional'] = opt_fields
map_data['fields'] = fields
map_data = {key: val for key, val in map_data.items() if val != None} # remove None
return map_data
def validate_mdd_id(mdd_id):
with db.connect() as conn:
select_statement = "SELECT count(1) > 0 FROM mdd_player_stats WHERE player_id = %s"
replace_vars = (mdd_id)
result = conn.execute(select_statement, replace_vars)
return result.first()[0]
def get_physics_user_stats(physics_string: str,
discord_id: str = None,
mdd_id: int = None):
"""
Retrieves an user's mdd statistics for a particular physics
:param physics_string: The physics for which the statistics are being requested
:param discord_id: The calling user's discord id. Used when calling !mystats <physics>
:param mdd_id: The requested user's mdd_id. Used when calling !userstats <physics>
:return: A dictionary of statistical data to be processed into an embed.
"""
# Supported physics dict. The keys are available physics arguments in discord, the values are the corresponding
# physics string representations in the database.
supported_physics = {
'vq3': 'vq3-run',
'vq3.1': 'vq3-ctf1',
'vq3.2': 'vq3-ctf2',
'vq3.3': 'vq3-ctf3',
'vq3.4': 'vq3-ctf4',
'vq3.5': 'vq3-ctf5',
'vq3.6': 'vq3-ctf6',
'vq3.7': 'vq3-ctf7',
'cpm': 'cpm-run',
'cpm.1': 'cpm-ctf1',
'cpm.2': 'cpm-ctf2',
'cpm.3': 'cpm-ctf3',
'cpm.4': 'cpm-ctf4',
'cpm.5': 'cpm-ctf5',
'cpm.6': 'cpm-ctf6',
'cpm.7': 'cpm-ctf7',
}
if physics_string in supported_physics:
physics = supported_physics[physics_string]
else:
raise Exception("Invalid physics.")
with db.connect() as conn:
# FROM and WHERE clauses depend on usage of discord id or mdd id
if discord_id is not None:
from_where = "FROM mdd_player_stats m JOIN discord_ids d ON m.player_id=d.mdd_id " \
"WHERE discord_id=%s AND physics=%s"
replace_vars = (discord_id, physics)
else:
from_where = "FROM mdd_player_stats WHERE player_id=%s AND physics=%s"
replace_vars = (mdd_id, physics)
select_statement = f"SELECT country \
,player_id \
,player_name \
,physics \
,total_times \
,total_time_seconds as total_time_logged \
,avg_pct_rank as average_percent_rank \
,overall_pct_rank as overall_percent_rank \
,total_world_recs as world_records \
,world_recs_pct as top1_percent \
,total_top3_recs as top_3_times \
,top3_recs_pct as top3_percent \
,total_top10_recs as top_10_times \
,top10_recs_pct as top10_percent \
{from_where}"
result_set = conn.execute(select_statement, replace_vars)
if result_set.rowcount == 1:
for row in result_set:
stats_dict = dict(row)
# total seconds to human-readable
total_time = stats_dict['total_time_logged']
days, hours, minutes, secs = process_total_seconds_to_readable(
total_time)
stats_dict[
'total_time_logged'] = f"{days} days, {hours} hours, {minutes} minutes, and {secs} seconds"
total_times, top1, top3, top10 = [
stats_dict[datum] for datum in [
'total_times', 'world_records', 'top_3_times',
'top_10_times'
]
]
total_pc, top1_pc, top3_pc, top10_pc = [
stats_dict.pop(datum) for datum in [
'overall_percent_rank', 'top1_percent', 'top3_percent',
'top10_percent'
]
]
stats_dict['average_percent_rank'] = round(
stats_dict['average_percent_rank'] * 100, 2)
stats_dict[
'total_times'] = f"{total_times} ({round(total_pc * 100, 2)}%)"
stats_dict[
'world_records'] = f"{top1} ({round(top1_pc * 100, 2)}%)"
stats_dict[
'top_3_times'] = f"{top3} ({round(top3_pc * 100, 2)}%)"
stats_dict[
'top_10_times'] = f"{top10} ({round(top10_pc * 100, 2)}%)"
result_set.close()
return stats_dict
result_set.close()
raise Exception(
"No statistics found. Use !myid to check or set your mdd id.")