759 lines
22 KiB
Python
759 lines
22 KiB
Python
import random
|
||
from wood import logger
|
||
from pymysql.cursors import DictCursor
|
||
from data.database import db_connection
|
||
|
||
|
||
# -----------------------------------------------------
|
||
# User / Session
|
||
# -----------------------------------------------------
|
||
|
||
def validate_user_session(db_id, discord_id):
|
||
"""Checks if the cookie still matches the current database."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor()
|
||
|
||
cursor.execute("SELECT discord_id FROM players WHERE id = %s", (db_id,))
|
||
result = cursor.fetchone()
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
if result is None:
|
||
logger.log(f"Player not found in database. Discord:{discord_id}")
|
||
return False
|
||
|
||
if str(result[0]) != str(discord_id):
|
||
logger.log(f"Player with false cookies logged in! {discord_id} does not belong to {db_id}")
|
||
return False
|
||
|
||
return True
|
||
|
||
|
||
def change_display_name(player_id, new_name):
|
||
connection = db_connection()
|
||
cursor = connection.cursor()
|
||
|
||
cursor.execute("UPDATE players SET display_name = %s WHERE id = %s", (new_name, player_id))
|
||
|
||
connection.commit()
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
|
||
def generate_silly_name():
|
||
adjectives = ["Confused", "Blind", "Howling", "Angry", "Chaos",
|
||
"Desperate", "Shouting", "Stumbling", "Sweating"]
|
||
nouns = ["Grot", "Cultist", "Servitor", "Snotling", "Guardmen",
|
||
"DiceLover", "RuleBreaker", "MetaChaser", "SlimeSniffer"]
|
||
return f"{random.choice(adjectives)} {random.choice(nouns)}"
|
||
|
||
|
||
def get_or_create_player(discord_id, discord_name, avatar_url):
|
||
connection = db_connection()
|
||
cursor = connection.cursor(cursor=DictCursor)
|
||
|
||
cursor.execute(
|
||
"SELECT id, discord_name, display_name, discord_avatar_url FROM players WHERE discord_id = %s",
|
||
(discord_id,)
|
||
)
|
||
player = cursor.fetchone()
|
||
|
||
if player is None:
|
||
silly_name = generate_silly_name()
|
||
cursor.execute(
|
||
"INSERT INTO players (discord_id, discord_name, display_name, discord_avatar_url) "
|
||
"VALUES (%s, %s, %s, %s)",
|
||
(discord_id, discord_name, silly_name, avatar_url)
|
||
)
|
||
connection.commit()
|
||
logger.log(f"new player added. Discord:{discord_name}")
|
||
|
||
cursor.execute(
|
||
"SELECT id, discord_name, display_name, discord_avatar_url FROM players WHERE discord_id = %s",
|
||
(discord_id,)
|
||
)
|
||
player = cursor.fetchone()
|
||
else:
|
||
cursor.execute(
|
||
"UPDATE players SET discord_name = %s, discord_avatar_url = %s WHERE discord_id = %s",
|
||
(discord_name, avatar_url, discord_id)
|
||
)
|
||
connection.commit()
|
||
|
||
cursor.execute(
|
||
"SELECT id, discord_name, display_name, discord_avatar_url FROM players WHERE discord_id = %s",
|
||
(discord_id,)
|
||
)
|
||
player = cursor.fetchone()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
return player
|
||
|
||
|
||
# -----------------------------------------------------
|
||
# Player Lists
|
||
# -----------------------------------------------------
|
||
|
||
def get_all_players():
|
||
"""All players – basic data."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(cursor=DictCursor)
|
||
|
||
cursor.execute(
|
||
"SELECT id, display_name, discord_name, discord_avatar_url FROM players "
|
||
"ORDER BY display_name ASC"
|
||
)
|
||
rows = cursor.fetchall()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
return rows
|
||
|
||
|
||
def get_all_players_from_system(system_name):
|
||
connection = db_connection()
|
||
cursor = connection.cursor(cursor=DictCursor)
|
||
|
||
query = """
|
||
SELECT DISTINCT
|
||
p.id AS player_id,
|
||
p.display_name,
|
||
p.discord_name
|
||
FROM players p
|
||
JOIN player_game_statistic stat ON p.id = stat.player_id
|
||
JOIN gamesystems sys ON stat.gamesystem_id = sys.id
|
||
WHERE sys.name = %s
|
||
ORDER BY p.display_name ASC
|
||
"""
|
||
cursor.execute(query, (system_name,))
|
||
rows = cursor.fetchall()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
return rows
|
||
|
||
|
||
def get_gamesystem_id_by_name(system_name):
|
||
connection = db_connection()
|
||
cursor = connection.cursor()
|
||
|
||
cursor.execute("SELECT id FROM gamesystems WHERE name = %s", (system_name,))
|
||
row = cursor.fetchone()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
return row[0] if row else None
|
||
|
||
|
||
# -----------------------------------------------------
|
||
# Player Statistics
|
||
# -----------------------------------------------------
|
||
|
||
def get_player_statistics(player_id):
|
||
connection = db_connection()
|
||
cursor = connection.cursor(cursor=DictCursor)
|
||
|
||
query = """
|
||
SELECT
|
||
sys.id AS gamesystem_id,
|
||
sys.name AS gamesystem_name,
|
||
sys.*,
|
||
stat.mmr,
|
||
stat.games_in_system,
|
||
stat.points,
|
||
stat.avv_points,
|
||
stat.last_played,
|
||
stat.win_rate,
|
||
stat.trend,
|
||
stat.tyrann_id,
|
||
stat.pushover_id,
|
||
stat.nemesis_id
|
||
FROM gamesystems sys
|
||
LEFT JOIN player_game_statistic stat
|
||
ON sys.id = stat.gamesystem_id AND stat.player_id = %s
|
||
"""
|
||
cursor.execute(query, (player_id,))
|
||
rows = cursor.fetchall()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
return rows
|
||
|
||
|
||
def get_player_statistic(player_id, system_id, season_id=None):
|
||
"""
|
||
- If season_id is provided: Data for this season.
|
||
- If season_id is None: Aggregated total statistics.
|
||
"""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(cursor=DictCursor)
|
||
|
||
if season_id:
|
||
query = """
|
||
SELECT
|
||
sys.name AS gamesystem_name,
|
||
stat.mmr, stat.games_in_system, stat.points,
|
||
stat.avv_points, stat.last_played, stat.win_rate,
|
||
stat.trend, stat.tyrann_id, stat.pushover_id, stat.nemesis_id,
|
||
(
|
||
SELECT COUNT(*) + 1
|
||
FROM player_game_statistic rank_stat
|
||
WHERE rank_stat.gamesystem_id = %s
|
||
AND rank_stat.season_id = %s
|
||
AND rank_stat.games_in_system > 0
|
||
AND rank_stat.mmr > COALESCE(stat.mmr, 0)
|
||
) AS rank
|
||
FROM gamesystems sys
|
||
LEFT JOIN player_game_statistic stat
|
||
ON sys.id = stat.gamesystem_id
|
||
AND stat.player_id = %s
|
||
AND stat.season_id = %s
|
||
WHERE sys.id = %s
|
||
"""
|
||
cursor.execute(query, (system_id, season_id, player_id, season_id, system_id))
|
||
else:
|
||
query = """
|
||
SELECT
|
||
sys.name AS gamesystem_name,
|
||
SUM(stat.mmr) / COUNT(stat.season_id) AS mmr, -- Average MMR over seasons
|
||
SUM(stat.games_in_system) AS games_in_system,
|
||
SUM(stat.points) AS points,
|
||
SUM(stat.points) / SUM(stat.games_in_system) AS avv_points,
|
||
MAX(stat.last_played) AS last_played,
|
||
NULL AS rank
|
||
FROM gamesystems sys
|
||
LEFT JOIN player_game_statistic stat
|
||
ON sys.id = stat.gamesystem_id
|
||
AND stat.player_id = %s
|
||
WHERE sys.id = %s
|
||
GROUP BY sys.id
|
||
"""
|
||
cursor.execute(query, (player_id, system_id))
|
||
|
||
row = cursor.fetchone()
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
# Sicherstellen dass rank nie None ist wenn season_id gesetzt
|
||
if row and season_id and row.get('rank') is None:
|
||
row['rank'] = 1
|
||
|
||
return row
|
||
|
||
|
||
def get_seasons_by_system(system_id):
|
||
|
||
"""Retrieves all seasons of a game system, sorted by number descending."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(cursor=DictCursor)
|
||
|
||
query = """
|
||
SELECT id, name, season_number
|
||
FROM seasons
|
||
WHERE gamesystem_id = %s
|
||
ORDER BY season_number DESC
|
||
"""
|
||
|
||
cursor.execute(query, (system_id,))
|
||
seasons = cursor.fetchall()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
return seasons
|
||
|
||
|
||
def get_player_rank(player_id, system_id, season_id):
|
||
"""Retrieves the rank of a player in a system for a specific season."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(cursor=DictCursor)
|
||
|
||
query = """
|
||
SELECT COUNT(*) + 1 AS rank
|
||
FROM player_game_statistic stat
|
||
WHERE stat.gamesystem_id = %s
|
||
AND stat.season_id = %s
|
||
AND stat.games_in_system > 0
|
||
AND stat.mmr > (
|
||
SELECT stat2.mmr
|
||
FROM player_game_statistic stat2
|
||
WHERE stat2.player_id = %s
|
||
AND stat2.gamesystem_id = %s
|
||
AND stat2.season_id = %s
|
||
)
|
||
"""
|
||
# Parameters: gamesystem, season, player, gamesystem, season
|
||
cursor.execute(query, (system_id, season_id, player_id, system_id, season_id))
|
||
row = cursor.fetchone()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
return row['rank'] if row and row['rank'] is not None else 1
|
||
|
||
|
||
|
||
def join_league(player_id, gamesystem_id):
|
||
try:
|
||
# 1. Get the correct season for this system
|
||
season_id = get_current_season_id(gamesystem_id)
|
||
|
||
connection = db_connection()
|
||
cursor = connection.cursor()
|
||
|
||
query = """
|
||
INSERT INTO player_game_statistic
|
||
(player_id, gamesystem_id, season_id)
|
||
VALUES (%s, %s, %s)
|
||
"""
|
||
cursor.execute(query, (player_id, gamesystem_id, season_id))
|
||
connection.commit()
|
||
|
||
logger.log(f"Success: Player {player_id} joined System {gamesystem_id} in Season {season_id}")
|
||
|
||
except Exception as e:
|
||
logger.log(f"ERROR joining league: {e}")
|
||
raise
|
||
finally:
|
||
# Here is a small check if the connection actually exists
|
||
if 'connection' in locals() and connection:
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
|
||
|
||
def get_current_season_id(system_id):
|
||
connection = db_connection()
|
||
cursor = connection.cursor(cursor=DictCursor)
|
||
|
||
query = """
|
||
SELECT id FROM seasons
|
||
WHERE gamesystem_id = %s
|
||
ORDER BY start_date DESC
|
||
LIMIT 1
|
||
"""
|
||
cursor.execute(query, (system_id,))
|
||
result = cursor.fetchone()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
if result:
|
||
return result['id']
|
||
else:
|
||
# Here we know exactly which game system has no season
|
||
raise RuntimeError(f"No season found for {gamesystem_id}!")
|
||
|
||
|
||
# -----------------------------------------------------
|
||
# Matches: Reading
|
||
# -----------------------------------------------------
|
||
|
||
def get_recent_matches_for_player(player_id):
|
||
connection = db_connection()
|
||
cursor = connection.cursor(cursor=DictCursor)
|
||
|
||
query = """
|
||
SELECT
|
||
m.id AS match_id,
|
||
sys.name AS gamesystem_name,
|
||
m.player1_id,
|
||
p1.display_name AS p1_display,
|
||
p1.discord_name AS p1_discord,
|
||
m.score_player1,
|
||
m.player2_id,
|
||
p2.display_name AS p2_display,
|
||
p2.discord_name AS p2_discord,
|
||
m.score_player2,
|
||
m.played_at
|
||
FROM matches m
|
||
JOIN gamesystems sys ON m.gamesystem_id = sys.id
|
||
JOIN players p1 ON m.player1_id = p1.id
|
||
JOIN players p2 ON m.player2_id = p2.id
|
||
WHERE m.player1_id = %s OR m.player2_id = %s
|
||
ORDER BY m.played_at DESC
|
||
LIMIT 10
|
||
"""
|
||
cursor.execute(query, (player_id, player_id))
|
||
rows = cursor.fetchall()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
return rows
|
||
|
||
|
||
def add_new_match(system_name, player1_id, player2_id, score_p1, score_p2):
|
||
connection = None
|
||
try:
|
||
connection = db_connection()
|
||
cursor = connection.cursor()
|
||
|
||
cursor.execute("SELECT id FROM gamesystems WHERE name = %s", (system_name,))
|
||
sys_row = cursor.fetchone()
|
||
|
||
if not sys_row:
|
||
raise ValueError(f"Game system '{system_name}' not found.")
|
||
|
||
sys_id = sys_row[0]
|
||
|
||
query = """
|
||
INSERT INTO matches (gamesystem_id, player1_id, player2_id, score_player1, score_player2)
|
||
VALUES (%s, %s, %s, %s, %s)
|
||
"""
|
||
cursor.execute(query, (sys_id, player1_id, player2_id, score_p1, score_p2))
|
||
new_match_id = cursor.lastrowid
|
||
|
||
logger.log(
|
||
f"{get_player_name(player1_id)}:({score_p1}) posted Match. "
|
||
f"System: {system_name}, {get_player_name(player2_id)}:({score_p2})"
|
||
)
|
||
|
||
connection.commit()
|
||
return new_match_id
|
||
|
||
except Exception as e:
|
||
if connection:
|
||
connection.rollback()
|
||
raise # Propagate the exception
|
||
|
||
finally:
|
||
if connection:
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
|
||
|
||
def save_calculated_match(calc_results: dict):
|
||
connection = db_connection()
|
||
cursor = connection.cursor()
|
||
|
||
try:
|
||
match_id = calc_results["match_id"]
|
||
winner_id = calc_results["winner_id"]
|
||
looser_id = calc_results["looser_id"]
|
||
|
||
cursor.execute(
|
||
"SELECT player1_id, player2_id, gamesystem_id FROM matches WHERE id = %s",
|
||
(match_id,)
|
||
)
|
||
row = cursor.fetchone()
|
||
if not row:
|
||
raise ValueError(f"Match ID {match_id} not found in the database.")
|
||
|
||
player1_id, player2_id, gamesystem_id = row
|
||
|
||
p1 = calc_results[player1_id]
|
||
p2 = calc_results[player2_id]
|
||
|
||
match_query = """
|
||
UPDATE matches
|
||
SET
|
||
player1_base_change = %s, player1_khorne = %s, player1_slaanesh = %s,
|
||
player1_tzeentch = %s, player1_mmr_change = %s,
|
||
player2_base_change = %s, player2_khorne = %s, player2_slaanesh = %s,
|
||
player2_tzeentch = %s, player2_mmr_change = %s,
|
||
rust_factor = %s, elo_factor = %s, point_inflation = %s,
|
||
match_is_counted = 1
|
||
WHERE id = %s
|
||
"""
|
||
cursor.execute(match_query, (
|
||
p1["base"], p1["khorne"], p1["slaanesh"], p1["tzeentch"], p1["total"],
|
||
p2["base"], p2["khorne"], p2["slaanesh"], p2["tzeentch"], p2["total"],
|
||
calc_results["rust_factor"], calc_results["elo_factor"], calc_results["point_inflation"],
|
||
match_id
|
||
))
|
||
|
||
cursor.execute(
|
||
"SELECT score_player1, score_player2 FROM matches WHERE id = %s",
|
||
(match_id,)
|
||
)
|
||
score_row = cursor.fetchone()
|
||
score_p1, score_p2 = score_row if score_row else (0, 0)
|
||
|
||
stat_query = """
|
||
UPDATE player_game_statistic
|
||
SET
|
||
mmr = mmr + %s,
|
||
games_in_system = games_in_system + 1,
|
||
points = points + %s,
|
||
avv_points = (points + %s) / (games_in_system + 1),
|
||
last_played = CURRENT_TIMESTAMP
|
||
WHERE player_id = %s AND gamesystem_id = %s
|
||
"""
|
||
|
||
cursor.execute(stat_query, (
|
||
p1["total"], score_p1, score_p1, player1_id, gamesystem_id
|
||
))
|
||
cursor.execute(stat_query, (
|
||
p2["total"], score_p2, score_p2, player2_id, gamesystem_id
|
||
))
|
||
|
||
connection.commit()
|
||
logger.log(f"Match ID:{match_id} calculated.")
|
||
return True
|
||
|
||
except Exception as e:
|
||
connection.rollback()
|
||
logger.log(f"CRITICAL ERROR saving the match: {e}")
|
||
return False
|
||
|
||
finally:
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
|
||
# -----------------------------------------------------
|
||
# Get Data Functions
|
||
# -----------------------------------------------------
|
||
|
||
def get_gamesystem_data(system_id):
|
||
connection = db_connection()
|
||
cursor = connection.cursor(cursor=DictCursor)
|
||
|
||
cursor.execute("SELECT * FROM gamesystems WHERE id = %s", (system_id,))
|
||
row = cursor.fetchone()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
return row
|
||
|
||
|
||
def get_gamesystems_data():
|
||
connection = db_connection()
|
||
cursor = connection.cursor(cursor=DictCursor)
|
||
|
||
cursor.execute("SELECT * FROM gamesystems")
|
||
rows = cursor.fetchall()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
return rows
|
||
|
||
|
||
|
||
def get_leaderboard(system_name):
|
||
"""Retrieves all players of a system sorted by MMR for the leaderboard (current season only)."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(cursor=DictCursor)
|
||
|
||
query = """
|
||
SELECT p.id, p.display_name, p.discord_name, p.discord_id, p.discord_avatar_url,
|
||
stat.mmr, stat.games_in_system, stat.wins, stat.loss, stat.avv_points
|
||
FROM players p
|
||
JOIN player_game_statistic stat ON p.id = stat.player_id
|
||
JOIN gamesystems sys ON stat.gamesystem_id = sys.id
|
||
JOIN seasons s ON stat.season_id = s.id
|
||
WHERE sys.name = %s
|
||
AND s.end_date IS NULL
|
||
AND stat.games_in_system > 0
|
||
ORDER BY stat.mmr DESC
|
||
"""
|
||
cursor.execute(query, (system_name,))
|
||
rows = cursor.fetchall()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
return rows
|
||
|
||
|
||
def get_match_by_id(match_id: int) -> dict | None:
|
||
"""Returns all match data including game system name as a dictionary."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(cursor=DictCursor)
|
||
|
||
try:
|
||
cursor.execute("""
|
||
SELECT
|
||
m.id,
|
||
m.gamesystem_id,
|
||
g.name AS gamesystem_name,
|
||
m.player1_id,
|
||
m.score_player1,
|
||
m.player2_id,
|
||
m.score_player2,
|
||
m.played_at
|
||
FROM matches m
|
||
JOIN gamesystems g ON m.gamesystem_id = g.id
|
||
WHERE m.id = %s
|
||
""", (match_id,))
|
||
|
||
return cursor.fetchone()
|
||
|
||
except Exception as e:
|
||
logger.log(f"Error loading the match: {e}")
|
||
return None
|
||
|
||
finally:
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
|
||
def get_player_name(player_id):
|
||
"""Returns the name of a player in the format 'Display Name (Discord Name)'."""
|
||
if player_id is None:
|
||
return "Unknown Player"
|
||
|
||
connection = db_connection()
|
||
cursor = connection.cursor()
|
||
|
||
cursor.execute("SELECT display_name, discord_name FROM players WHERE id = %s", (player_id,))
|
||
row = cursor.fetchone()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
if row:
|
||
return f"{row[0]} ({row[1]})"
|
||
return "Deleted Player"
|
||
|
||
|
||
def get_system_name_by_id(sys_id):
|
||
if sys_id is None:
|
||
return "Unknown System"
|
||
|
||
connection = db_connection()
|
||
cursor = connection.cursor()
|
||
|
||
cursor.execute("SELECT name FROM gamesystems WHERE id = %s", (sys_id,))
|
||
row = cursor.fetchone()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
return row[0] if row else "Deleted System"
|
||
|
||
|
||
def get_days_since_last_system_game(player_id, gamesystem_id):
|
||
"""Returns how many days the last game in a specific system was."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor()
|
||
|
||
# MariaDB: DATEDIFF instead of julianday()
|
||
query = """
|
||
SELECT DATEDIFF(NOW(), last_played)
|
||
FROM player_game_statistic
|
||
WHERE player_id = %s AND gamesystem_id = %s
|
||
"""
|
||
cursor.execute(query, (player_id, gamesystem_id))
|
||
result = cursor.fetchone()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
if result and result[0] is not None:
|
||
return int(result[0])
|
||
return 0
|
||
|
||
|
||
# -----------------------------------------------------
|
||
# Matches Confirming, Deleting, Calculating, ...
|
||
# -----------------------------------------------------
|
||
|
||
def get_open_matches(player_id):
|
||
"""Retrieves all open matches (player2_check = 0)."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(cursor=DictCursor)
|
||
|
||
query = """
|
||
SELECT m.id AS match_id,
|
||
m.score_player1, m.score_player2,
|
||
m.played_at,
|
||
sys.name AS system_name,
|
||
p1.display_name AS player1_name,
|
||
p2.display_name AS player2_name,
|
||
CASE WHEN m.player1_id = %s THEN 1 ELSE 0 END AS is_submitter
|
||
FROM matches m
|
||
LEFT JOIN gamesystems sys ON m.gamesystem_id = sys.id
|
||
LEFT JOIN players p1 ON m.player1_id = p1.id
|
||
LEFT JOIN players p2 ON m.player2_id = p2.id
|
||
WHERE m.player2_check = 0
|
||
AND (m.player1_id = %s OR m.player2_id = %s)
|
||
"""
|
||
cursor.execute(query, (player_id, player_id, player_id))
|
||
rows = cursor.fetchall()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
submitted = []
|
||
to_confirm = []
|
||
|
||
for row in rows:
|
||
if row.pop('is_submitter'):
|
||
submitted.append(row)
|
||
else:
|
||
to_confirm.append(row)
|
||
|
||
return {"submitted": submitted, "to_confirm": to_confirm}
|
||
|
||
|
||
|
||
|
||
def delete_match(match_id, player_id):
|
||
"""Deletes a match completely from the database by its ID."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor()
|
||
|
||
cursor.execute("DELETE FROM matches WHERE id = %s", (match_id,))
|
||
logger.log(f"Match ID{match_id} deleted from user {get_player_name(player_id)}(ID:{player_id})")
|
||
connection.commit()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
|
||
def confirm_match(match_id):
|
||
connection = db_connection()
|
||
cursor = connection.cursor()
|
||
|
||
cursor.execute("UPDATE matches SET player2_check = 1 WHERE id = %s", (match_id,))
|
||
logger.log(f"Match with ID{match_id} confirmed by Player2.")
|
||
connection.commit()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
|
||
def set_match_counted(match_id):
|
||
"""Sets the flag (1) that the match was successfully incorporated into the MMR."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor()
|
||
|
||
cursor.execute("UPDATE matches SET match_is_counted = 1 WHERE id = %s", (match_id,))
|
||
connection.commit()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
|
||
|
||
def get_match_history_log(player_id):
|
||
"""Retrieves ALL matches of a player including MMR changes and factors."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(cursor=DictCursor)
|
||
|
||
query = """
|
||
SELECT m.played_at, sys.name AS gamesystem_name,
|
||
m.player1_id, p1.display_name AS p1_display, p1.discord_name AS p1_discord,
|
||
m.score_player1, m.player1_mmr_change,
|
||
m.player1_khorne, m.player1_tzeentch, m.player1_slaanesh, m.player1_base_change,
|
||
m.player2_id, p2.display_name AS p2_display, p2.discord_name AS p2_discord,
|
||
m.score_player2, m.player2_mmr_change,
|
||
m.player2_khorne, m.player2_tzeentch, m.player2_slaanesh, m.player2_base_change,
|
||
m.elo_factor, m.rust_factor,
|
||
m.player2_check, m.match_is_counted
|
||
FROM matches m
|
||
JOIN gamesystems sys ON m.gamesystem_id = sys.id
|
||
JOIN players p1 ON m.player1_id = p1.id
|
||
JOIN players p2 ON m.player2_id = p2.id
|
||
WHERE m.player1_id = %s OR m.player2_id = %s
|
||
ORDER BY m.played_at DESC
|
||
"""
|
||
cursor.execute(query, (player_id, player_id))
|
||
rows = cursor.fetchall()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
return rows
|