737 lines
22 KiB
Python
737 lines
22 KiB
Python
import random
|
||
from wood import logger
|
||
|
||
from data.database import db_connection
|
||
|
||
|
||
# -----------------------------------------------------
|
||
# User / Session
|
||
# -----------------------------------------------------
|
||
|
||
def validate_user_session(db_id, discord_id):
|
||
"""Prüft, ob das Cookie noch zur aktuellen Datenbank passt."""
|
||
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 coockies logged in! {discord_id} doesnt 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 = ["Verwirrter", "Blinder", "Heulender", "Zorniger", "Chaos",
|
||
"Verzweifelter", "Schreiender", "Stolpernder", "Schwitzender"]
|
||
nouns = ["Grot", "Kultist", "Servitor", "Snotling", "Guardmen",
|
||
"Würfellecker", "Regelvergesser", "Meta-Chaser", "Klebschnüffler"]
|
||
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(dictionary=True)
|
||
|
||
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-Listen
|
||
# -----------------------------------------------------
|
||
|
||
def get_all_players():
|
||
"""Alle Spieler – Basisdaten."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(dictionary=True)
|
||
|
||
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(dictionary=True)
|
||
|
||
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(dictionary=True)
|
||
|
||
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):
|
||
"""
|
||
- Wenn season_id angegeben: Daten für diese Season.
|
||
- Wenn season_id = None: Aggregierte Gesamtstatistik.
|
||
"""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(dictionary=True)
|
||
|
||
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
|
||
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, (player_id, season_id, system_id))
|
||
else:
|
||
query = """
|
||
SELECT
|
||
sys.name AS gamesystem_name,
|
||
SUM(stat.mmr) / COUNT(stat.season_id) AS mmr, -- Durchschnitt MMR über 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
|
||
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()
|
||
return row
|
||
|
||
|
||
def get_seasons_by_system(gamesystem_id):
|
||
"""Holt alle Seasons eines Spielsystems, sortiert nach Nummer absteigend."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(dictionary=True)
|
||
|
||
query = """
|
||
SELECT id, name, season_number
|
||
FROM seasons
|
||
WHERE gamesystem_id = %s
|
||
ORDER BY season_number DESC
|
||
"""
|
||
|
||
cursor.execute(query, (gamesystem_id,))
|
||
seasons = cursor.fetchall()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
return seasons
|
||
|
||
|
||
def get_player_rank(player_id, system_id, season_id):
|
||
"""Holt den Rang eines Spielers in einem System für eine spezifische Season."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(dictionary=True)
|
||
|
||
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
|
||
)
|
||
"""
|
||
# Parameter: 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. Die richtige Season für dieses System holen
|
||
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"Erfolg: Spieler {player_id} joined System {gamesystem_id} in Season {season_id}")
|
||
|
||
except Exception as e:
|
||
logger.log(f"FEHLER beim Beitreten: {e}")
|
||
raise
|
||
finally:
|
||
# Hier ist ein kleiner Check, ob die connection überhaupt existiert
|
||
if 'connection' in locals() and connection:
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
|
||
|
||
def get_current_season_id(gamesystem_id):
|
||
connection = db_connection()
|
||
cursor = connection.cursor(dictionary=True)
|
||
|
||
query = """
|
||
SELECT id FROM seasons
|
||
WHERE gamesystem_id = %s
|
||
ORDER BY start_date DESC
|
||
LIMIT 1
|
||
"""
|
||
cursor.execute(query, (gamesystem_id,))
|
||
result = cursor.fetchone()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
if result:
|
||
return result['id']
|
||
else:
|
||
# Hier wissen wir jetzt genau, welches Spielsystem keine Saison hat
|
||
raise RuntimeError(f"Keine Saison für {gamesystem_id} gefunden!")
|
||
|
||
|
||
|
||
# -----------------------------------------------------
|
||
# Matches: Lesen
|
||
# -----------------------------------------------------
|
||
|
||
def get_recent_matches_for_player(player_id):
|
||
connection = db_connection()
|
||
cursor = connection.cursor(dictionary=True)
|
||
|
||
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 = db_connection()
|
||
cursor = connection.cursor()
|
||
|
||
cursor.execute("SELECT id FROM gamesystems WHERE name = %s", (system_name,))
|
||
sys_row = cursor.fetchone()
|
||
|
||
if not sys_row:
|
||
cursor.close()
|
||
connection.close()
|
||
return None
|
||
|
||
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()
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
return new_match_id
|
||
|
||
|
||
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} nicht in der Datenbank gefunden.")
|
||
|
||
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"KRITISCHER FEHLER beim Speichern des Matches: {e}")
|
||
return False
|
||
|
||
finally:
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
|
||
# -----------------------------------------------------
|
||
# Get Data Funktionen
|
||
# -----------------------------------------------------
|
||
|
||
def get_gamesystem_data(system_id):
|
||
connection = db_connection()
|
||
cursor = connection.cursor(dictionary=True)
|
||
|
||
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(dictionary=True)
|
||
|
||
cursor.execute("SELECT * FROM gamesystems")
|
||
rows = cursor.fetchall()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
return rows
|
||
|
||
|
||
|
||
def get_leaderboard(system_name):
|
||
"""Holt alle Spieler eines Systems sortiert nach MMR für die Rangliste."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(dictionary=True)
|
||
|
||
query = 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
|
||
WHERE sys.name = %s 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:
|
||
"""Gibt alle Match-Daten inkl. Gamesystem-Name als Dict zurück."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(dictionary=True)
|
||
|
||
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"Fehler beim Laden des Matches: {e}")
|
||
return None
|
||
|
||
finally:
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
|
||
def get_player_name(player_id):
|
||
"""Gibt den Namen eines Spielers im Format 'Anzeigename (Discordname)' zurück."""
|
||
if player_id is None:
|
||
return "Unbekannter Spieler"
|
||
|
||
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 "Gelöschter Spieler"
|
||
|
||
|
||
def get_system_name_by_id(sys_id):
|
||
if sys_id is None:
|
||
return "Unbekanntes 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 "Gelöschtes System"
|
||
|
||
|
||
def get_days_since_last_system_game(player_id, gamesystem_id):
|
||
"""Gibt zurück, wie viele Tage das letzte Spiel in einem bestimmten System her ist."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor()
|
||
|
||
# MariaDB: DATEDIFF statt 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 Bestätigen, Löschen, Berechnen, ...
|
||
# -----------------------------------------------------
|
||
|
||
def get_unconfirmed_matches(player_id):
|
||
"""Holt alle offenen Matches, die der Spieler noch bestätigen muss."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(dictionary=True)
|
||
|
||
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 p1_name
|
||
FROM matches m
|
||
JOIN gamesystems sys ON m.gamesystem_id = sys.id
|
||
JOIN players p1 ON m.player1_id = p1.id
|
||
WHERE m.player2_id = %s AND m.player2_check = 0
|
||
"""
|
||
cursor.execute(query, (player_id,))
|
||
rows = cursor.fetchall()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
return rows
|
||
|
||
|
||
def delete_match(match_id, player_id):
|
||
"""Löscht ein Match anhand seiner ID komplett aus der Datenbank."""
|
||
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 mit ID{match_id} von Player2 bestätigt.")
|
||
connection.commit()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
|
||
|
||
def set_match_counted(match_id):
|
||
"""Setzt den Haken (1), dass das Match erfolgreich in die MMR eingeflossen ist."""
|
||
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_submitted_matches(player_id):
|
||
"""Holt alle offenen Matches, die der Spieler selbst eingetragen hat."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(dictionary=True)
|
||
|
||
query = """
|
||
SELECT m.id AS match_id, m.score_player1, m.score_player2, m.played_at,
|
||
sys.name AS system_name,
|
||
p2.display_name AS p2_name
|
||
FROM matches m
|
||
JOIN gamesystems sys ON m.gamesystem_id = sys.id
|
||
JOIN players p2 ON m.player2_id = p2.id
|
||
WHERE m.player1_id = %s AND m.player2_check = 0
|
||
"""
|
||
cursor.execute(query, (player_id,))
|
||
rows = cursor.fetchall()
|
||
|
||
cursor.close()
|
||
connection.close()
|
||
return rows
|
||
|
||
|
||
def get_match_history_log(player_id):
|
||
"""Holt ALLE Matches eines Spielers inkl. MMR-Änderungen und Faktoren."""
|
||
connection = db_connection()
|
||
cursor = connection.cursor(dictionary=True)
|
||
|
||
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
|