def reload(self):
"""
Reloads the AQL thresholds configuration from DB
:return:
"""
log.debug("Reloading AQL thresholds")
self._thresholds = {}
for x in glob.db.fetchAll(
"SELECT `name`, value_string FROM system_settings WHERE `name` LIKE 'aql\_threshold\_%%'"
):
parts = x["name"].split("aql_threshold_")
if len(parts) < 1:
continue
m = gameModes.getGameModeFromDB(parts[1])
if m is None:
continue
try:
self._thresholds[m] = float(x["value_string"])
except ValueError:
continue
log.debug([(gameModes.getGameModeForDB(x), self[x]) for x in self])
if not all(x in self._thresholds
for x in range(gameModes.STD, gameModes.MANIA)):
raise RuntimeError(
"Invalid AQL thresholds. Please check your system_settings table."
)
def incrementPlaytimeRX(userID, gameMode=0, length=0):
modeForDB = gameModes.getGameModeForDB(gameMode)
result = glob.db.fetch("SELECT playtime_{gm} as playtime FROM rx_stats WHERE id = %s".format(gm=modeForDB), [userID])
if result is not None:
glob.db.execute("UPDATE rx_stats SET playtime_{gm} = %s WHERE id = %s".format(gm=modeForDB), [(int(result['playtime'])+int(length)), userID])
else:
print("Something went wrong...")
def user_stats(fro, chan, message):
args = [m.lower() for m in message]
nickname = None
mode = 0
if len(args) < 1:
nickname = fro
else:
nickname = args[0].lower()
if len(args) > 1 and args[1].isdigit():
mode = int(args[1])
if mode > 3:
return "mode is incorrect"
user_id = userUtils.getID(nickname)
if not user_id:
return "User not found!"
mode_str = gameModes.getGameModeForDB(mode)
user = userUtils.getUserStats(user_id, mode)
acc = "{0:.2f}%".format(user['accuracy'])
return (
f"User: {nickname}\n"
f"ID: {user_id}\n"
"---------------------\n"
f"Stats for {mode_str} #{user['gameRank']}\n"
f"Ranked score: {humanize(user['rankedScore'])}\n"
f"Accuracy: {acc}\n"
f"Play count: {humanize(user['playcount'])}\n"
f"Total score: {humanize(user['totalScore'])}\n"
f"PP count: {humanize(user['pp'])}"
)
def getUserStats(userID, gameMode, *, relax=False):
"""
Get all user stats relative to `gameMode`
:param userID:
:param gameMode: game mode number
:param relax: if True, return relax stats, otherwise return classic stats
:return: dictionary with result
"""
modeForDB = gameModes.getGameModeForDB(gameMode)
# Get stats
stats = glob.db.fetch(
"""SELECT
ranked_score_{gm} AS rankedScore,
avg_accuracy_{gm} AS accuracy,
playcount_{gm} AS playcount,
total_score_{gm} AS totalScore,
pp_{gm} AS pp
FROM {table} WHERE id = %s LIMIT 1""".format(
table="users_stats_relax" if relax else "users_stats",
gm=modeForDB), (userID, ))
# Get game rank
stats["gameRank"] = getGameRank(userID, gameMode, relax=relax)
# Return stats + game rank
return stats
def getUserStats(userID, gameMode):
"""
Get all user stats relative to `gameMode`
:param userID:
:param gameMode: game mode number
:return: dictionary with result
"""
modeForDB = gameModes.getGameModeForDB(gameMode)
# Get stats
stats = glob.db.fetch(
"""SELECT
ranked_score_{gm} AS rankedScore,
avg_accuracy_{gm} AS accuracy,
playcount_{gm} AS playcount,
total_score_{gm} AS totalScore,
pp_{gm} AS pp
FROM users_stats WHERE id = %s LIMIT 1""".format(gm=modeForDB), [userID])
# Get game rank
stats["gameRank"] = getGameRank(userID, gameMode)
# Return stats + game rank
return stats
def updateRankGlobally(gameMode):
gm = gameModes.getGameModeForDB(gameMode)
users = glob.db.fetchAll("SELECT user_id FROM osu_user_stats{}".format(gm))
if users is not None:
for uid in users:
updateRank(uid["user_id"], gameMode)
else:
log.warning("Fetched None users")
def getPlaycount(userID, gameMode):
"""
Get `userID`'s playcount relative to `gameMode`
:param userID: user id
:param gameMode: game mode number
:return: playcount
"""
modeForDB = gameModes.getGameModeForDB(gameMode)
return glob.db.fetch("SELECT playcount_"+modeForDB+" FROM users_stats WHERE id = %s LIMIT 1", [userID])["playcount_"+modeForDB]
def updateTotalHits(userID=0, gameMode=gameModes.STD, newHits=0, score=None):
if score is None and userID == 0:
raise ValueError("Either score or userID must be provided")
if score is not None:
newHits = score.c50 + score.c100 + score.c300
gameMode = score.gameMode
userID = score.playerUserID
glob.db.execute(
"UPDATE users_stats SET total_hits_{gm} = total_hits_{gm} + %s WHERE id = %s LIMIT 1"
.format(gm=gameModes.getGameModeForDB(gameMode)), (newHits, userID))
def getAccuracy(userID, gameMode):
"""
Get `userID`'s average accuracy relative to `gameMode`
:param userID: user id
:param gameMode: game mode number
:return: accuracy
"""
modeForDB = gameModes.getGameModeForDB(gameMode)
return glob.db.fetch("SELECT avg_accuracy_"+modeForDB+" FROM users_stats WHERE id = %s LIMIT 1", [userID])["avg_accuracy_"+modeForDB]
def getTotalScore(userID, gameMode):
"""
Get `userID`'s total score relative to `gameMode`
:param userID: user id
:param gameMode: game mode number
:return: total score
"""
modeForDB = gameModes.getGameModeForDB(gameMode)
return glob.db.fetch("SELECT total_score_"+modeForDB+" FROM users_stats WHERE id = %s LIMIT 1", [userID])["total_score_"+modeForDB]
def getGameRankRx(userID, gameMode):
"""
Get `userID`'s **in-game rank** (eg: #1337) relative to gameMode
:param userID: user id
:param gameMode: game mode number
:return: game rank
"""
position = glob.redis.zrevrank("ripple:leaderboard_relax:{}".format(gameModes.getGameModeForDB(gameMode)), userID)
if position is None:
return 0
else:
return int(position) + 1
def getAccuracy(userID, gameMode, *, relax=False):
"""
Get `userID`'s average accuracy relative to `gameMode`
:param userID: user id
:param gameMode: game mode number
:param relax:
:return: accuracy
"""
return glob.db.fetch(
"SELECT avg_accuracy_{m} AS x FROM {table} WHERE id = %s LIMIT 1".
format(table="users_stats_relax" if relax else "users_stats",
m=gameModes.getGameModeForDB(gameMode)), (userID, ))["x"]
def updatePP(userID, gameMode, *, relax=False):
"""
Update userID's pp with new value
:param userID: user id
:param gameMode: game mode number
:param relax: if True, calculate relax pp, otherwise calculate classic pp
"""
pp = calculatePP(userID, gameMode, relax=relax)
gm = gameModes.getGameModeForDB(gameMode)
glob.db.execute(
"UPDATE osu_user_stats{} SET rank_score=%s WHERE user_id = %s LIMIT 1".
format(gm), (pp, userID))
updateRank(userID, gameMode, pp)
def getPlaycount(userID, gameMode, *, relax=False):
"""
Get `userID`'s playcount relative to `gameMode`
:param userID: user id
:param gameMode: game mode number
:param relax:
:return: playcount
"""
return glob.db.fetch(
"SELECT playcount_{m} FROM {table} WHERE id = %s LIMIT 1".format(
m=gameModes.getGameModeForDB(gameMode),
table="users_stats_relax" if relax else "users_stats"),
(userID, ))["x"]
def getTotalScore(userID, gameMode, *, relax=False):
"""
Get `userID`'s total score relative to `gameMode`
:param userID: user id
:param gameMode: game mode number
:param relax:
:return: total score
"""
return glob.db.fetch(
"SELECT total_score_{m} AS x FROM {table} WHERE id = %s LIMIT 1".
format(m=gameModes.getGameModeForDB(gameMode),
table="users_stats_relax" if relax else "users_stats"),
(userID, ))["x"]
def getTotalScore(userID, gameMode, *, relax=False):
"""
Get `userID`'s total score relative to `gameMode`
:param userID: user id
:param gameMode: game mode number
:param relax:
:return: total score
"""
res = glob.db.fetch(
"SELECT total_score FROM osu_user_stats{m} WHERE user_id = %s LIMIT 1".
format(m=gameModes.getGameModeForDB(gameMode)), (userID, ))
if res is None:
return 0
return res["total_score"]
def getPlaycount(userID, gameMode, *, relax=False):
"""
Get `userID`'s playcount relative to `gameMode`
:param userID: user id
:param gameMode: game mode number
:param relax:
:return: playcount
"""
res = glob.db.fetch(
"SELECT playcount FROM osu_user_stats{m} WHERE user_id = %s LIMIT 1".
format(m=gameModes.getGameModeForDB(gameMode), ), (userID, ))
if res is None:
return 0
return res["playcount"]
def updateTotalHits(userID=0,
gameMode=gameModes.STD,
newHits=0,
score=None,
*,
relax=False):
if score is None and userID == 0:
raise ValueError("Either score or userID must be provided")
if score is not None:
newHits = score.c50 + score.c100 + score.c300
gameMode = score.gameMode
userID = score.playerUserID
gm = gameModes.getGameModeForDB(gameMode)
glob.db.execute(
f"UPDATE osu_user_stats{gm} SET count300 = count300 + %s, count100 = count100 + %s, count50 = count50 + %s, countMiss = countMiss + %s WHERE user_id = %s LIMIT 1",
(score.c300, score.c100, score.c50, score.cMiss, userID))
def updateUserPlayTime(userID, gameMode, playTime):
'''
Some python guide for ripple-codders on python3.6+
You can forget about '{}'.format(value) via f'{value}'!
:param userID int: userID of user which you want update
:param gameMode int: gameMode which you want update
:param playTime int: how many seconds you want add
:return bool: Boolean True
'''
modeForDB = gameModes.getGameModeForDB(gameMode)
glob.db.execute(
f"UPDATE users_stats SET playtime_{modeForDB} = playtime_{modeForDB} + %s WHERE id = %s",
[playTime, userID])
return True
def getGameRank(userID, gameMode):
"""
Get `userID`'s **in-game rank** (eg: #1337) relative to gameMode
:param userID: user id
:param gameMode: game mode number
:return: game rank
"""
modeForDB = gameModes.getGameModeForDB(gameMode)
result = glob.db.fetch(
"SELECT position FROM leaderboard_" + modeForDB +
" WHERE user = %s LIMIT 1", [userID])
if result is None:
return 0
else:
return result["position"]
def getGameRank(userID, gameMode, *, relax=False):
"""
Get `userID`'s **in-game rank** (eg: #1337) relative to gameMode
:param userID: user id
:param gameMode: game mode number
:return: game rank
"""
# don't use this
gm = gameModes.getGameModeForDB(gameMode)
res = glob.db.fetch(
"SELECT `rank_score_index` FROM osu_user_stats{m} WHERE user_id = %s".
format(m=gm), (userID, ))
if res is None:
return 0
return res["rank_score_index"]
def getAccuracy(userID, gameMode, *, relax=False):
"""
Get `userID`'s average accuracy relative to `gameMode`
:param userID: user id
:param gameMode: game mode number
:param relax:
:return: accuracy
"""
m = gameModes.getGameModeForDB(gameMode)
res = glob.db.fetch(
f"SELECT accuracy FROM osu_user_stats{m} WHERE user_id = %s LIMIT 1",
(userID, ))
if res is None:
return 0
return res["accuracy"]
def updateRank(userID, gameMode, pp=0):
gm = gameModes.getGameModeForDB(gameMode)
if pp == 0:
ppRes = glob.db.fetch(
"SELECT rank_score FROM osu_user_stats{} WHERE user_id = %s".
format(gm), (userID, ))
if ppRes is None:
return
pp = ppRes["rank_score"]
res = glob.db.fetch(
"SELECT COUNT(*) AS `rank` FROM osu_user_stats{} WHERE rank_score >= %s"
.format(gm), (pp, ))
if res is not None:
# Update rank
glob.db.execute(
"UPDATE osu_user_stats{} SET rank_score_index = %s WHERE user_id = %s LIMIT 1"
.format(gm), (res["rank"], userID))
def calculatePP(userID, gameMode, *, relax=False):
"""
Calculate userID's total PP for gameMode
:param userID: user id
:param gameMode: game mode number
:param relax:
:return: total PP
"""
gm = gameModes.getGameModeForDB(gameMode)
# TODO: Check if the beatmap is pp-able
return sum(
round(round(row["pp"]) * 0.95**i) for i, row in enumerate(
glob.db.fetchAll(
f"SELECT pp FROM osu_scores{gm}_high "
"WHERE user_id = %s AND "
"pp IS NOT NULL "
"ORDER BY pp DESC LIMIT 500", (userID))))
def calculateAccuracy(userID, gameMode, *, relax=False):
"""
Calculate accuracy value for userID relative to gameMode
:param userID: user id
:param gameMode: game mode number
:param relax: if True, calculate relax accuracy, otherwise calculate classic accuracy
:return: new accuracy
"""
gm = gameModes.getGameModeForDB(gameMode)
# Get best accuracy scores
stats = glob.db.fetch(
f"""SELECT
accuracy_total,
accuracy_count
FROM osu_user_stats{gm} WHERE user_id = %s LIMIT 1""", (userID, ))
if stats is None:
return 0
return stats["accuracy_total"] / 10000 / stats["accuracy_count"]
def reload(self):
"""
Reloads the AQL thresholds configuration from DB
:return:
"""
log.debug("Reloading AQL thresholds")
self._thresholds = {}
self._thresholds[''] = float(1000)
self._thresholds['_taiko'] = float(1000)
self._thresholds['_fruits'] = float(1000)
self._thresholds['_mania'] = float(1000)
self._thresholds['std'] = float(1000)
self._thresholds['taiko'] = float(1000)
self._thresholds['ctb'] = float(1000)
self._thresholds['mania'] = float(1000)
log.debug([(gameModes.getGameModeForDB(x), self[x]) for x in self])
if not all(x in self._thresholds for x in range(gameModes.STD, gameModes.MANIA)):
raise RuntimeError("Invalid AQL thresholds. Please check your system_settings table.")
def updateRankCounterRX(rank, gameMode, userID):
"""
This updates the users rank counter.
So I don't have to struggle doing the work of counting in golang :nausated_face:
"""
modeForDB = gameModes.getGameModeForDB(gameMode)
if rank == "F":
return False
elif rank == "D":
return False
elif rank == "C":
return False
elif rank == "B":
return False
else:
glob.db.execute(
"""
UPDATE rx_rank SET {rank}_{gameMode}={rank}_{gameMode}+1 WHERE userid = %s LIMIT 1"""
.format(rank=rank, gameMode=modeForDB), [userID])
def getRelaxStats(userID, gameMode):
"""
Get all relax stats relative to `gameMode`
:param userID:
:param gameMode: game mode number
:return: dictionary with result
"""
modeForDB = gameModes.getGameModeForDB(gameMode)
stats = glob.db.fetch("""SELECT
ranked_score_{gm} AS rankedScore,
avg_accuracy_{gm} AS accuracy,
playcount_{gm} AS playcount,
total_score_{gm} AS totalScore,
pp_{gm} AS pp
FROM rx_stats WHERE id = %s LIMIT 1""".format(gm=modeForDB), [userID])
stats["gameRank"] = rxgetGameRank(userID, gameMode) # idk what zrevrank is, might fix sometime
# Return stats + game rank
return stats
def getUserStats(userID, gameMode, *, relax=False):
"""
Get all user stats relative to `gameMode`
:param userID:
:param gameMode: game mode number
:param relax: if True, return relax stats, otherwise return classic stats
:return: dictionary with result
"""
modeForDB = gameModes.getGameModeForDB(gameMode)
# Get stats
stats = glob.db.fetch(
f"""SELECT
ranked_score AS rankedScore,
accuracy_total,
accuracy_count,
playcount,
total_score AS totalScore,
rank_score AS pp,
max_combo
FROM osu_user_stats{modeForDB} WHERE user_id = %s LIMIT 1""", (userID, ))
if stats is None:
log.info("Creating new stats data for {}".format(userID))
res = glob.db.fetch(
"SELECT `country_acronym` FROM phpbb_users WHERE user_id = %s LIMIT 1",
(userID, ))
if res is None:
log.warning("Failed to get country for {}".format(userID))
country = 'XX'
else:
country = res["country_acronym"]
glob.db.execute(
f"INSERT IGNORE INTO osu_user_stats (`user_id`, `accuracy_total`, `accuracy_count`, `accuracy`, `playcount`, `ranked_score`, `total_score`, `x_rank_count`, `s_rank_count`, `a_rank_count`, `rank`, `level`, `country_acronym`, `rank_score`, `rank_score_index`, `accuracy_new`) VALUES (%s, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, %s, 0, 0, 0)",
(
userID,
country,
))
glob.db.execute(
f"INSERT IGNORE INTO osu_user_stats_taiko (`user_id`, `accuracy_total`, `accuracy_count`, `accuracy`, `playcount`, `ranked_score`, `total_score`, `x_rank_count`, `s_rank_count`, `a_rank_count`, `rank`, `level`, `country_acronym`, `rank_score`, `rank_score_index`, `accuracy_new`) VALUES (%s, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, %s, 0, 0, 0)",
(
userID,
country,
))
glob.db.execute(
f"INSERT IGNORE INTO osu_user_stats_fruits (`user_id`, `accuracy_total`, `accuracy_count`, `accuracy`, `playcount`, `ranked_score`, `total_score`, `x_rank_count`, `s_rank_count`, `a_rank_count`, `rank`, `level`, `country_acronym`, `rank_score`, `rank_score_index`, `accuracy_new`) VALUES (%s, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, %s, 0, 0, 0)",
(
userID,
country,
))
glob.db.execute(
f"INSERT IGNORE INTO osu_user_stats_mania (`user_id`, `accuracy_total`, `accuracy_count`, `accuracy`, `playcount`, `ranked_score`, `total_score`, `x_rank_count`, `s_rank_count`, `a_rank_count`, `rank`, `level`, `country_acronym`, `rank_score`, `rank_score_index`, `accuracy_new`) VALUES (%s, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, %s, 0, 0, 0)",
(
userID,
country,
))
glob.db.execute(
f"INSERT IGNORE INTO osu_user_stats_mania_4k (`user_id`, `playcount`, `x_rank_count`, `s_rank_count`, `a_rank_count`, `country_acronym`, `rank_score`, `rank_score_index`, `accuracy_new`) VALUES (%s, 0, 0, 0, 0, %s, 0, 0, 0)",
(
userID,
country,
))
glob.db.execute(
f"INSERT IGNORE INTO osu_user_stats_mania_7k (`user_id`, `playcount`, `x_rank_count`, `s_rank_count`, `a_rank_count`, `country_acronym`, `rank_score`, `rank_score_index`, `accuracy_new`) VALUES (%s, 0, 0, 0, 0, %s, 0, 0, 0)",
(
userID,
country,
))
return getUserStats(userID, gameMode, relax=relax)
stats["accuracy"] = float(stats["accuracy_total"]) / 10000.0 / max(
1, stats["accuracy_count"])
# Get game rank
stats["gameRank"] = getGameRank(userID, gameMode, relax=relax)
# Return stats + game rank
return stats
def tillerinoLast(fro, chan, message):
try:
# Run the command in PM only
if chan.startswith("#"):
return False
data = glob.db.fetch(
"""SELECT beatmaps.song_name as sn, scores.*,
beatmaps.beatmap_id as bid, beatmaps.difficulty_std, beatmaps.difficulty_taiko, beatmaps.difficulty_ctb, beatmaps.difficulty_mania, beatmaps.max_combo as fc
FROM scores
LEFT JOIN beatmaps ON beatmaps.beatmap_md5=scores.beatmap_md5
LEFT JOIN users ON users.id = scores.userid
WHERE users.username = %s
ORDER BY scores.time DESC
LIMIT 1""", [fro])
if data is None:
return False
diffString = "difficulty_{}".format(
gameModes.getGameModeForDB(data["play_mode"]))
rank = generalUtils.getRank(data["play_mode"], data["mods"],
data["accuracy"], data["300_count"],
data["100_count"], data["50_count"],
data["misses_count"])
ifPlayer = "{0} | ".format(fro) if chan != "FokaBot" else ""
ifFc = " (FC)" if data["max_combo"] == data[
"fc"] else " {0}x/{1}x".format(data["max_combo"], data["fc"])
beatmapLink = "[http://osu.ppy.sh/b/{1} {0}]".format(
data["sn"], data["bid"])
hasPP = data["play_mode"] == gameModes.STD or data[
"play_mode"] == gameModes.MANIA
msg = ifPlayer
msg += beatmapLink
if data["play_mode"] != gameModes.STD:
msg += " <{0}>".format(
gameModes.getGameModeForPrinting(data["play_mode"]))
if data["mods"]:
msg += ' +' + generalUtils.readableMods(data["mods"])
if not hasPP:
msg += " | {0:,}".format(data["score"])
msg += ifFc
msg += " | {0:.2f}%, {1}".format(data["accuracy"], rank.upper())
msg += " {{ {0} / {1} / {2} / {3} }}".format(
data["300_count"], data["100_count"], data["50_count"],
data["misses_count"])
msg += " | {0:.2f} stars".format(data[diffString])
return msg
msg += " ({0:.2f}%, {1})".format(data["accuracy"], rank.upper())
msg += ifFc
msg += " | {0:.2f}pp".format(data["pp"])
stars = data[diffString]
if data["mods"]:
token = glob.tokens.getTokenFromUsername(fro)
if token is None:
return False
userID = token.userID
token.tillerino[0] = data["bid"]
token.tillerino[1] = data["mods"]
token.tillerino[2] = data["accuracy"]
oppaiData = getPPMessage(userID, just_data=True)
if "stars" in oppaiData:
stars = oppaiData["stars"]
msg += " | {0:.2f} stars".format(stars)
return msg
except Exception as a:
log.error(a)
return False
