from flask import Flask, render_template, session, redirect, url_for, request, jsonify, render_template_string import mysql.connector from mysql.connector import Error import requests import os from datetime import datetime import hashlib import hmac from urllib.parse import urlencode import os from dotenv import load_dotenv import csv import io import json import re import operator from functools import wraps load_dotenv() app = Flask(__name__) app.secret_key = os.getenv('SECRET_KEY', 'your-secret-key-here') # --- Настройки режима технических работ --- TECH_WORKS = os.getenv('TECH_WORKS', '0') # '0' - нет тех.работ, '1' - тех.работы ALLOWED_IPS = os.getenv('ALLOWED_IPS', '').split(',') # Список IP-адресов, которым разрешен доступ # Очищаем пустые строки, если ALLOWED_IPS был пустым или имел лишние запятые ALLOWED_IPS = [ip.strip() for ip in ALLOWED_IPS if ip.strip()] # --- Конец настроек режима технических работ --- # Конфигурация Steam API STEAM_API_KEY = os.getenv('STEAM_API_KEY') STEAM_OPENID_URL = 'https://steamcommunity.com/openid/login' # Конфигурация базы данных DB_CONFIG = { 'host': os.getenv('DB_HOST', 'localhost'), 'user': os.getenv('DB_USER', 'root'), 'password': os.getenv('DB_PASSWORD', ''), 'database': os.getenv('DB_NAME', 'hltv_clone'), 'charset': 'utf8mb4', 'collation': 'utf8mb4_unicode_ci' } @app.before_request def check_maintenance(): if TECH_WORKS == '1': user_ip = request.remote_addr # Check if the user's IP is in the allowed list if user_ip not in ALLOWED_IPS: # If maintenance is on and IP is not allowed, redirect to maintenance page return render_template('maintenance.html'), 503 # 503 Service Unavailable def get_player_stats_from_csv_row(row): """ Парсинг CSV. steamid64 заменен на steam_id. """ try: # 0: matchid, 1: mapnumber, 2: steamid64, 3: team, 4: name, 5: kills, 6: deaths, 7: damage, 8: assists # 14: utility_damage # 34: head_shot_kills return { 'match_id': int(row[0]), 'map_number': int(row[1]), 'steam_id': row[2], 'team': row[3], 'username': row[4], 'kills': int(row[5]), 'deaths': int(row[6]), 'damage': int(row[7]), 'assists': int(row[8]), 'utility_damage': int(row[14]), 'head_shot_kills': int(row[34]) } except (IndexError, ValueError) as e: print(f"Ошибка парсинга строки CSV: {e}. Строка: {row[:10]}...") return None class PlayerStatsCalculator: def __init__(self, player_data): self.username = player_data.get('username', 'Unknown Player') self.total_kills = player_data.get('total_kills', 0) self.total_deaths = player_data.get('total_deaths', 0) self.total_assists = player_data.get('total_assists', 0) self.total_damage = player_data.get('total_damage', 0) self.total_headshot_kills = player_data.get('total_headshot_kills', 0) self.total_utility_damage = player_data.get('total_utility_damage', 0) self.wins = player_data.get('wins', 0) self.losses = player_data.get('losses', 0) self.total_matches_played = self.wins + self.losses def calculate_derived_stats(self): """ Вычисляет все производные статистические данные для игрока. """ stats = {} divisor_for_averages = self.total_matches_played if self.total_matches_played > 0 else 1 stats['winrate'] = (self.wins / divisor_for_averages) * 100 if self.total_matches_played > 0 else 0.0 stats['kd_ratio'] = self.total_kills / self.total_deaths if self.total_deaths > 0 else float(self.total_kills) # Handle infinite K/D for 0 deaths if total_rounds_played > 0: stats['avg_adr'] = self.total_damage / 24 else: stats['avg_adr'] = 0.0 stats['avg_kills'] = self.total_kills / divisor_for_averages stats['avg_deaths'] = self.total_deaths / divisor_for_averages stats['avg_assists'] = self.total_assists / divisor_for_averages stats['avg_headshot_kills'] = self.total_headshot_kills / divisor_for_averages stats['avg_utility_damage'] = self.total_utility_damage / divisor_for_averages self.derived_stats = stats return stats def _normalize_stat(self, value, min_val, max_val, invert=False): """ Нормализует значение между min_val и max_val к диапазону [0, 1]. Значения вне диапазона обрезаются. Если invert=True, то низкие значения дают высокий результат (например, для смертей). """ if min_val == max_val: # Avoid division by zero if min and max are the same return 0.5 # Or some other neutral value clipped_value = max(min_val, min(max_val, value)) normalized = (clipped_value - min_val) / (max_val - min_val) return 1 - normalized if invert else normalized def calculate_player_rating(self): """ Вычисляет PlayerRatingIndex на основе нормализованных данных игрока. Рейтинг будет в диапазоне примерно от 0.0 до 2.0. """ if not hasattr(self, 'derived_stats'): self.calculate_derived_stats() # --- Определение бенчмарков (min_val, max_val) для каждой метрики --- # Эти значения ОЧЕНЬ ВАЖНЫ и должны быть настроены на основе реальных данных вашей игры. # Они определяют, что считается "плохим" (min) и "отличным" (max) для каждой метрики. # Примеры значений (могут быть неточными для вашей игры): # Средние показатели за матч BENCHMARKS = { 'avg_kills': {'min': 10, 'max': 30}, # 10 kills per match (bad) to 30 (excellent) 'avg_deaths': {'min': 5, 'max': 20}, # 5 deaths (excellent) to 20 (bad) 'avg_assists': {'min': 2, 'max': 15}, # 2 assists (bad) to 15 (excellent) 'avg_adr': {'min': 50, 'max': 150}, # 50 damage per round (bad) to 150 (excellent) 'avg_headshot_kills': {'min': 1, 'max': 10}, # 1 HS kill (bad) to 10 (excellent) 'avg_utility_damage': {'min': 50, 'max': 300}, # 50 utility damage (bad) to 300 (excellent) 'winrate': {'min': 30, 'max': 70}, # 30% winrate (bad) to 70% (excellent) 'kd_ratio': {'min': 0.5, 'max': 2.0} # 0.5 K/D (bad) to 2.0 K/D (excellent) } # Получаем производные статы kpm = self.derived_stats['avg_kills'] dpm = self.derived_stats['avg_deaths'] apm = self.derived_stats['avg_assists'] adr = self.derived_stats['avg_adr'] hspm = self.derived_stats['avg_headshot_kills'] udpm = self.derived_stats['avg_utility_damage'] win_rate = self.derived_stats['winrate'] kd_ratio = self.derived_stats['kd_ratio'] # Нормализация каждой метрики к диапазону [0, 1] norm_kpm = self._normalize_stat(kpm, BENCHMARKS['avg_kills']['min'], BENCHMARKS['avg_kills']['max']) norm_dpm = self._normalize_stat(dpm, BENCHMARKS['avg_deaths']['min'], BENCHMARKS['avg_deaths']['max'], invert=True) # More deaths is worse norm_apm = self._normalize_stat(apm, BENCHMARKS['avg_assists']['min'], BENCHMARKS['avg_assists']['max']) norm_adr = self._normalize_stat(adr, BENCHMARKS['avg_adr']['min'], BENCHMARKS['avg_adr']['max']) norm_hspm = self._normalize_stat(hspm, BENCHMARKS['avg_headshot_kills']['min'], BENCHMARKS['avg_headshot_kills']['max']) norm_udpm = self._normalize_stat(udpm, BENCHMARKS['avg_utility_damage']['min'], BENCHMARKS['avg_utility_damage']['max']) norm_win_rate = self._normalize_stat(win_rate, BENCHMARKS['winrate']['min'], BENCHMARKS['winrate']['max']) # Для K/D Ratio, если оно бесконечное (0 смертей), можно установить его на max_val или даже выше # Но для нашей нормализации, просто обрежем его по max_val. norm_kd_ratio = self._normalize_stat(kd_ratio, BENCHMARKS['kd_ratio']['min'], BENCHMARKS['kd_ratio']['max']) # --- Весовые коэффициенты для нормализованных метрик --- # Теперь эти веса определяют, насколько важен каждый *нормализованный* стат. # Сумма весов не обязана быть 1, но будет влиять на окончательный диапазон перед масштабированием. W_K = 0.20 # Убийства W_D = 0.15 # Смерти (инвертировано) W_A = 0.10 # Ассисты W_ADR = 0.15 # Урон W_HS = 0.10 # Хедшоты W_UD = 0.05 # Урон от утилити W_WR = 0.15 # Винрейт W_KD = 0.10 # K/D Ratio # Сумма весов для нормализации итогового рейтинга TOTAL_WEIGHT_SUM = W_K + W_D + W_A + W_ADR + W_HS + W_UD + W_WR + W_KD # Расчет "сырого" рейтинга raw_rating = (norm_kpm * W_K) + \ (norm_dpm * W_D) + \ (norm_apm * W_A) + \ (norm_adr * W_ADR) + \ (norm_hspm * W_HS) + \ (norm_udpm * W_UD) + \ (norm_win_rate * W_WR) + \ (norm_kd_ratio * W_KD) # --- Финальное масштабирование рейтинга до диапазона 0.0 - 2.0 --- # Если все нормализованные статы равны 0 (худший игрок по всем показателям), # то raw_rating будет 0. # Если все нормализованные статы равны 1 (лучший игрок по всем показателям), # то raw_rating будет равен TOTAL_WEIGHT_SUM. # Мы хотим, чтобы средний игрок (нормализованный рейтинг около 0.5 для всех статов) # имел рейтинг около 1.0. # Максимальный возможный raw_rating = TOTAL_WEIGHT_SUM (если все norm_stat = 1) # Минимальный возможный raw_rating = 0 (если все norm_stat = 0) # Чтобы масштабировать к [0.0, 2.0]: # new_value = (old_value - old_min) * (new_max - new_min) / (old_max - old_min) + new_min # Здесь old_min = 0, old_max = TOTAL_WEIGHT_SUM, new_min = 0.0, new_max = 2.0 final_rating = (raw_rating / TOTAL_WEIGHT_SUM) * 2.0 return round(final_rating, 2) class DB: def __init__(self, config): self.config = config def get_connection(self): try: conn = mysql.connector.connect(**self.config) return conn except Error as e: print(f"Ошибка подключения к MySQL: {e}") return None def execute_query(self, query, params=None, fetchone=False, commit=True): conn = self.get_connection() if conn is None: return None result = None cursor = None try: # Использование dictionary=True для получения результата в виде словаря cursor = conn.cursor(dictionary=True) cursor.execute(query, params or ()) is_select = query.strip().upper().startswith('SELECT') if is_select: if fetchone: result = cursor.fetchone() else: result = cursor.fetchall() if commit and not is_select: conn.commit() result = cursor.lastrowid elif commit and is_select: # Только для SELECT, если нужно применить SET/временные таблицы (обычно не нужно) pass except Error as e: print(f"Ошибка выполнения запроса: {e}") print(f"Запрос: {query}") print(f"Параметры: {params}") if conn: conn.rollback() return None finally: if cursor: cursor.close() if conn: conn.close() return result db = DB(DB_CONFIG) def login_required(f): @wraps(f) def decorated_function(*args, **kwargs): if 'steam_id' not in session: return redirect(url_for('login')) return f(*args, **kwargs) return decorated_function class SteamAuth: @staticmethod def get_steam_login_url(): params = { 'openid.ns': 'http://specs.openid.net/auth/2.0', 'openid.identity': 'http://specs.openid.net/auth/2.0/identifier_select', 'openid.claimed_id': 'http://specs.openid.net/auth/2.0/identifier_select', 'openid.mode': 'checkid_setup', 'openid.return_to': request.url_root + 'auth/steam/callback', 'openid.realm': request.url_root } return STEAM_OPENID_URL + '?' + urlencode(params) @staticmethod def verify_steam_auth(params): return 'openid.claimed_id' in params and 'steamcommunity.com/openid/id/' in params['openid.claimed_id'] @staticmethod def get_steam_id_from_openid(openid_url): return openid_url.split('/')[-1] @staticmethod def get_steam_user_info(steam_id): url = f'http://api.steampowered.com/ISteamUser/GetPlayerSummaries/v0002/?key={STEAM_API_KEY}&steamids={steam_id}' try: response = requests.get(url) data = response.json() if data['response']['players']: return data['response']['players'][0] except Exception as e: print(f"Ошибка получения данных Steam: {e}") return None def get_player_aggregate_stats(steam_id): """ Получает агрегированную статистику игрока путем суммирования всех его матчей. Эту функцию вы будете использовать на странице профиля игрока. """ query = """ SELECT COUNT(id) AS total_matches, SUM(kills) AS total_kills, SUM(deaths) AS total_deaths, SUM(damage) AS total_damage, SUM(assists) AS total_assists, SUM(head_shot_kills) AS total_headshot_kills, SUM(utility_damage) AS total_utility_damage FROM matches_new WHERE steam_id = %s """ stats = db.execute_query(query, (steam_id,), fetchone=True) # Расчет K/D ratio (устраняем деление на ноль) if stats and stats['total_deaths'] is not None and stats['total_deaths'] > 0: stats['kd_ratio'] = round(stats['total_kills'] / stats['total_deaths'], 2) elif stats and stats['total_kills'] is not None and stats['total_kills'] > 0: stats['kd_ratio'] = stats['total_kills'] # Если смертей нет, но есть убийства else: stats['kd_ratio'] = 0.0 return stats if stats and stats['total_matches'] is not None else None @app.route('/player_stats/') def player_stats(steam_id): stats = get_player_aggregate_stats(steam_id) if stats: return jsonify(stats) return jsonify({"error": "Статистика не найдена."}), 404 @app.route('/') def index(): return render_template('index.html') @app.route('/match/') def match_details(match_id): # ИСПРАВЛЕНИЕ COLLATE: Запрос статистики игроков match_players_query = """ SELECT mps.steam_id, u.username, u.avatar, mps.team, mps.kills, mps.deaths, mps.assists, mps.damage, mps.head_shot_kills, mps.utility_damage FROM match_players_stats mps LEFT JOIN users u ON mps.steam_id = u.steam_id COLLATE utf8mb4_unicode_ci WHERE mps.match_id = %s ORDER BY mps.kills DESC, mps.damage DESC """ match_players = db.execute_query(match_players_query, (match_id,)) if not match_players: return render_template('404.html'), 404 # 1. Формирование данных матча с заглушками match_data = { 'id': match_id, 'tournament_name': "N/A (Tournaments Table Deleted)", 'round_name': "N/A", 'score_a': 0, 'score_b': 0, 'match_date': datetime.now().strftime("%Y-%m-%d %H:%M:%S"), 'status': 'Finished' } # 2. Вычисление названий команд из колонки `team` в `match_players_stats` team_names = sorted(list(set(p['team'] for p in match_players if p['team']))) if len(team_names) >= 2: match_data['team_a_name'] = team_names[0] match_data['team_b_name'] = team_names[1] elif len(team_names) == 1: match_data['team_a_name'] = team_names[0] match_data['team_b_name'] = 'TBD (Only one team in stats)' else: match_data['team_a_name'] = 'TBD' match_data['team_b_name'] = 'TBD' # 3. Запрос названий и логотипов команд for key, team_name in [('a', match_data['team_a_name']), ('b', match_data['team_b_name'])]: # Используем team_name в качестве name/tag, чтобы найти его в таблице teams team_info = db.execute_query("SELECT tag, logo_url FROM teams WHERE name = %s OR tag = %s", (team_name, team_name), fetchone=True) # Удалены team_a_id и team_b_id, так как у нас нет гарантии, что команда существует по этому имени/тегу match_data[f'team_{key}_tag'] = team_info['tag'] if team_info else team_name.replace('team_', '') match_data[f'team_{key}_logo'] = team_info['logo_url'] if team_info else None current_user_player = None if 'steam_id' in session: current_user_player = db.execute_query("SELECT * FROM users WHERE steam_id = %s", (session['steam_id'],), fetchone=True) return render_template( 'match.html', match=match_data, match_players=match_players or [], current_user_player=current_user_player ) BENCHMARKS = { 'avg_kills': {'min': 5, 'max': 25}, 'avg_deaths': {'min': 5, 'max': 20}, 'avg_assists': {'min': 1, 'max': 10}, 'avg_adr': {'min': 50, 'max': 200}, 'avg_headshot_kills': {'min': 0, 'max': 8}, 'avg_utility_damage': {'min': 20, 'max': 250}, 'winrate': {'min': 10, 'max': 100}, 'kd_ratio': {'min': 0.3, 'max': 2.0} } W_K, W_D, W_A, W_ADR, W_HS, W_UD, W_WR, W_KD = 0.25, 0.20, 0.12, 0.07, 0.15, 0.05, 0.15, 0.20 TOTAL_WEIGHT_SUM = W_K + W_D + W_A + W_ADR + W_HS + W_UD + W_WR + W_KD def _normalize_stat(value, min_val, max_val, invert=False): if min_val == max_val: return 0.5 value = float(value or 0) # Убедимся, что None станет 0 clipped_value = max(min_val, min(max_val, value)) normalized = (clipped_value - min_val) / (max_val - min_val) return 1 - normalized if invert else normalized @app.route('/players') def players(): # ИСПРАВЛЕНИЕ: Заменил 'mps.steamid64' на 'mps.steam_id' для соответствия схеме БД. players_query = """ SELECT u.id, u.steam_id, u.username, u.avatar, u.role, u.is_ban, u.ban_reason, u.wins, u.losses, t.name AS team_name, t.id AS team_id, -- Агрегированные Статистики из всех матчей (match_players_stats) COALESCE(SUM(mps.kills), 0) AS total_kills, COALESCE(SUM(mps.deaths), 0) AS total_deaths, COALESCE(SUM(mps.damage), 0) AS total_damage, COALESCE(SUM(mps.assists), 0) AS total_assists, COALESCE(SUM(mps.head_shot_kills), 0) AS total_headshot_kills, COALESCE(SUM(mps.utility_damage), 0) AS total_utility_damage, COUNT(DISTINCT mps.match_id) AS total_matches_played FROM users u LEFT JOIN teams t ON u.team_id = t.id LEFT JOIN match_players_stats mps ON u.steam_id = mps.steam_id COLLATE utf8mb4_unicode_ci GROUP BY u.id, u.steam_id, u.username, u.avatar, u.role, u.is_ban, u.ban_reason, u.wins, u.losses, t.name, t.id ORDER BY (COALESCE(SUM(mps.damage), 0) / NULLIF(COUNT(DISTINCT mps.match_id), 0)) DESC -- Сортируем по ADR (damage/matches) LIMIT 50 """ all_players = db.execute_query(players_query) processed_players = [] if all_players: for player in all_players: # Расчет K/D (Убийства/Смерти) total_deaths = player['total_deaths'] if total_deaths == 0: kd_ratio = player['total_kills'] else: kd_ratio = player['total_kills'] / total_deaths # Расчет ADR (Средний Урон за Раунд) # Принимаем, что total_matches_played - это количество сыгранных матчей. # Для точного ADR нужно знать общее количество раундов. # Временно используем матчи как приближение, или используем просто Damage/Matches, как в ORDER BY total_matches_played = player['total_matches_played'] if total_matches_played == 0: avg_adr = 0 else: # Временно используем Total Damage / Total Matches, хотя это не совсем ADR # (ADR = Total Damage / Total Rounds) avg_adr = player['total_damage'] / total_matches_played # Расчет Winrate (Процент побед) total_games_from_user_table = player['wins'] + player['losses'] if total_games_from_user_table == 0: winrate = 0.0 else: winrate = (player['wins'] / total_games_from_user_table) * 100 # Расчет Player Rating 2.0 (Упрощенный расчет) # Player Rating = (Kills / Rounds + Assists / Rounds + Damage / 1000 / Rounds) * C # Где Rounds ~ Matches * Avg_Rounds_Per_Match # Упростим: Rating = K/D * 0.4 + ADR/100 * 0.4 + Winrate/100 * 0.2 # Поскольку у нас нет точного числа раундов, используем упрощенную формулу, # основанную на агрегированных показателях player_rating = (float(kd_ratio) * 0.4) + (float(avg_adr) / 100 * 0.4) + (float(winrate) / 100 * 0.2) player['kd_ratio'] = kd_ratio player['avg_adr'] = avg_adr player['winrate'] = winrate player['player_rating'] = player_rating processed_players.append(player) # Фактическая сортировка по Player Rating, так как SQL сортировал по ADR processed_players.sort(key=operator.itemgetter('player_rating'), reverse=True) return render_template('players.html', players=processed_players) def get_player_achievements_from_db(user_id, db_instance): """ Получает достижения игрока, читая achievements_json из таблицы users и затем запрашивая полную информацию из таблицы achievements, включая название и ID команды, используя столбец 'team' из таблицы achievements. """ # Сначала получаем JSON-массив ID достижений для пользователя user_achievements_ids_query = """ SELECT achievements_json FROM users WHERE id = %s """ result = db_instance.execute_query(user_achievements_ids_query, (user_id,), fetch_one=True) if not result or not result['achievements_json']: return [] achievement_ids = result['achievements_json'] if not achievement_ids: return [] # Теперь получаем детали этих достижений из таблицы 'achievements' # Используем JOIN с таблицей 'teams' по столбцу 'achievements.team' placeholders = ','.join(['%s'] * len(achievement_ids)) achievements_details_query = f""" SELECT a.id, a.name, a.description, a.rarity, a.created_at, a.tournament_id, t.name as team_name, -- Название команды из таблицы teams t.id as team_id -- ID команды из таблицы teams FROM achievements a LEFT JOIN teams t ON a.team = t.id -- Присоединяем таблицу teams по a.team (которая является ID команды) WHERE a.id IN ({placeholders}) """ player_achievements = db_instance.execute_query(achievements_details_query, tuple(achievement_ids)) return player_achievements if player_achievements else [] @app.route('/player/') def player_profile(steam_id): # 1. Запрос основной информации (использует steam_id) player_query = """ SELECT u.id, u.steam_id, u.username, u.avatar, u.wins, u.losses, u.role, u.is_ban, u.ban_reason, u.age, u.real_name, t.name as team_name, t.id as team_id, t.elo as team_elo, u.description, u.country, u.achievements_json FROM users u LEFT JOIN teams t ON u.team_id = t.id WHERE u.steam_id = %s COLLATE utf8mb4_unicode_ci """ player_base_data = db.execute_query(player_query, (steam_id,), fetchone=True) if not player_base_data: return render_template('404.html'), 404 # steam_id уже определен из маршрута, но для единообразия... steam_id = player_base_data['steam_id'] # 2. АГРЕГАЦИЯ ВСЕЙ СТАТИСТИКИ total_stats_query = """ SELECT SUM(mps.kills) AS total_kills, SUM(mps.deaths) AS total_deaths, SUM(mps.damage) AS total_damage, SUM(mps.assists) AS total_assists, SUM(mps.head_shot_kills) AS total_headshot_kills, SUM(mps.utility_damage) AS total_utility_damage, COUNT(DISTINCT mps.match_id) AS total_matches_played FROM match_players_stats mps WHERE mps.steam_id = %s """ player_agg_stats = db.execute_query(total_stats_query, (steam_id,), fetchone=True) # 3. Объединение и расчет (логика PRI и средних значений...) player = dict(player_base_data) if player_agg_stats is None: player_agg_stats = {} total_matches = int(player_agg_stats.get('total_matches_played') or 0) player['total_matches_played'] = total_matches divisor_for_averages = total_matches if total_matches > 0 else 1 player['total_kills'] = int(player_agg_stats.get('total_kills') or 0) player['total_deaths'] = int(player_agg_stats.get('total_deaths') or 0) player['total_damage'] = int(player_agg_stats.get('total_damage') or 0) player['total_assists'] = int(player_agg_stats.get('total_assists') or 0) player['total_headshot_kills'] = int(player_agg_stats.get('total_headshot_kills') or 0) player['total_utility_damage'] = int(player_agg_stats.get('total_utility_damage') or 0) total_games_wl = (player.get('wins') or 0) + (player.get('losses') or 0) player['winrate'] = (player.get('wins', 0) / total_games_wl) * 100 if total_games_wl > 0 else 0.0 player['kd_ratio'] = player['total_kills'] / player['total_deaths'] if player['total_deaths'] > 0 else float(player['total_kills']) total_rounds_played = total_matches * 24 player['avg_adr'] = player['total_damage'] / total_rounds_played if total_rounds_played > 0 else 0.0 player['avg_kills'] = player['total_kills'] / divisor_for_averages player['avg_deaths'] = player['total_deaths'] / divisor_for_averages player['avg_assists'] = player['total_assists'] / divisor_for_averages player['avg_headshot_kills'] = player['total_headshot_kills'] / divisor_for_averages player['avg_utility_damage'] = player['total_utility_damage'] / divisor_for_averages norm_kpm = _normalize_stat(player['avg_kills'], BENCHMARKS['avg_kills']['min'], BENCHMARKS['avg_kills']['max']) norm_dpm = _normalize_stat(player['avg_deaths'], BENCHMARKS['avg_deaths']['min'], BENCHMARKS['avg_deaths']['max'], invert=True) norm_apm = _normalize_stat(player['avg_assists'], BENCHMARKS['avg_assists']['min'], BENCHMARKS['avg_assists']['max']) norm_adr = _normalize_stat(player['avg_adr'], BENCHMARKS['avg_adr']['min'], BENCHMARKS['avg_adr']['max']) norm_hspm = _normalize_stat(player['avg_headshot_kills'], BENCHMARKS['avg_headshot_kills']['min'], BENCHMARKS['avg_headshot_kills']['max']) norm_udpm = _normalize_stat(player['avg_utility_damage'], BENCHMARKS['avg_utility_damage']['min'], BENCHMARKS['avg_utility_damage']['max']) norm_win_rate = _normalize_stat(player['winrate'], BENCHMARKS['winrate']['min'], BENCHMARKS['winrate']['max']) norm_kd_ratio = _normalize_stat(player['kd_ratio'], BENCHMARKS['kd_ratio']['min'], BENCHMARKS['kd_ratio']['max']) raw_rating_player = (norm_kpm * W_K) + (norm_dpm * W_D) + (norm_apm * W_A) + (norm_adr * W_ADR) + \ (norm_hspm * W_HS) + (norm_udpm * W_UD) + (norm_win_rate * W_WR) + (norm_kd_ratio * W_KD) player['player_rating'] = round((raw_rating_player / TOTAL_WEIGHT_SUM) * 2.0, 2) # 4. РАСЧЕТ РЕЙТИНГА ДЛЯ ВСЕХ (для #1) # ИСПРАВЛЕНИЕ COLLATE: JOIN all_players_query = """ SELECT u.id, u.wins, u.losses, SUM(mps.kills) AS total_kills, SUM(mps.deaths) AS total_deaths, SUM(mps.damage) AS total_damage, SUM(mps.assists) AS total_assists, SUM(mps.head_shot_kills) AS total_headshot_kills, SUM(mps.utility_damage) AS total_utility_damage, COUNT(DISTINCT mps.match_id) AS total_matches_played FROM users u LEFT JOIN match_players_stats mps ON u.steam_id = mps.steam_id COLLATE utf8mb4_unicode_ci GROUP BY u.id """ all_users_data = db.execute_query(all_players_query) or [] ranked_players = [] for user_data in all_users_data: total_matches_user = int(user_data.get('total_matches_played') or 0) divisor_user = total_matches_user if total_matches_user > 0 else 1 total_games_wl_user = (user_data.get('wins') or 0) + (user_data.get('losses') or 0) user_winrate = (user_data.get('wins', 0) / total_games_wl_user) * 100 if total_games_wl_user > 0 else 0.0 user_kills = int(user_data.get('total_kills') or 0) user_deaths = int(user_data.get('total_deaths') or 0) user_kd_ratio = user_kills / user_deaths if user_deaths > 0 else float(user_kills) user_total_rounds = total_matches_user * 24 user_avg_adr = (user_data.get('total_damage') or 0) / user_total_rounds if user_total_rounds > 0 else 0.0 user_avg_kills = user_kills / divisor_user user_avg_deaths = user_deaths / divisor_user user_avg_assists = (user_data.get('total_assists') or 0) / divisor_user user_avg_headshot_kills = (user_data.get('total_headshot_kills') or 0) / divisor_user user_avg_utility_damage = (user_data.get('total_utility_damage') or 0) / divisor_user norm_kpm_user = _normalize_stat(user_avg_kills, BENCHMARKS['avg_kills']['min'], BENCHMARKS['avg_kills']['max']) norm_dpm_user = _normalize_stat(user_avg_deaths, BENCHMARKS['avg_deaths']['min'], BENCHMARKS['avg_deaths']['max'], invert=True) norm_apm_user = _normalize_stat(user_avg_assists, BENCHMARKS['avg_assists']['min'], BENCHMARKS['avg_assists']['max']) norm_adr_user = _normalize_stat(user_avg_adr, BENCHMARKS['avg_adr']['min'], BENCHMARKS['avg_adr']['max']) norm_hspm_user = _normalize_stat(user_avg_headshot_kills, BENCHMARKS['avg_headshot_kills']['min'], BENCHMARKS['avg_headshot_kills']['max']) norm_udpm_user = _normalize_stat(user_avg_utility_damage, BENCHMARKS['avg_utility_damage']['min'], BENCHMARKS['avg_utility_damage']['max']) norm_win_rate_user = _normalize_stat(user_winrate, BENCHMARKS['winrate']['min'], BENCHMARKS['winrate']['max']) norm_kd_ratio_user = _normalize_stat(user_kd_ratio, BENCHMARKS['kd_ratio']['min'], BENCHMARKS['kd_ratio']['max']) user_raw_rating = (norm_kpm_user * W_K) + (norm_dpm_user * W_D) + (norm_apm_user * W_A) + (norm_adr_user * W_ADR) + \ (norm_hspm_user * W_HS) + (norm_udpm_user * W_UD) + (norm_win_rate_user * W_WR) + (norm_kd_ratio_user * W_KD) user_player_rating = round((user_raw_rating / TOTAL_WEIGHT_SUM) * 2.0, 2) ranked_players.append({'id': user_data['id'], 'rating': user_player_rating}) is_number_one = False if ranked_players: ranked_players.sort(key=operator.itemgetter('rating'), reverse=True) if ranked_players[0]['id'] == player['id']: is_number_one = True player['is_number_one'] = is_number_one # 5. Достижения (из JSON) try: player['achievements'] = json.loads(player['achievements_json']) if player['achievements_json'] else [] except (json.JSONDecodeError, TypeError): player['achievements'] = [] # 6. ИСТОРИЯ МАТЧЕЙ player_matches_query = """ SELECT mps.match_id, mps.team, mps.kills, mps.deaths, mps.damage, mps.head_shot_kills, mps.created_at FROM match_players_stats mps WHERE mps.steam_id = %s ORDER BY mps.created_at DESC LIMIT 10 """ player_matches = db.execute_query(player_matches_query, (steam_id,)) # 7. Получаем данные о текущем пользователе current_user_player = None if 'steam_id' in session: current_user_player = db.execute_query("SELECT * FROM users WHERE steam_id = %s", (session['steam_id'],), fetchone=True) return render_template( 'player.html', player=player, player_matches=player_matches or [], current_user_player=current_user_player ) @app.route('/team/') def team_profile(team_id): team_query = "SELECT id, name, logo_url, wins, losses, country, tag, elo, is_retired, retired_reason FROM teams WHERE id = %s" team = db.execute_query(team_query, (team_id,), fetch_one=True) if not team: return render_template('404.html'), 404 team['total_matches_played'] = team['wins'] + team['losses'] if team['total_matches_played'] > 0: team['winrate'] = (team['wins'] / team['total_matches_played']) * 100 else: team['winrate'] = 0.0 stats_query = """ SELECT COUNT(id) as total_players FROM users WHERE team_id = %s """ stats = db.execute_query(stats_query, (team_id,), fetch_one=True) or {'total_players': 0} players_query = """ SELECT u.id, u.username, u.avatar, u.wins, u.losses, u.total_kills, u.total_deaths, u.total_damage, u.total_assists, u.total_headshot_kills, u.total_utility_damage, u.role, u.is_ban, u.ban_reason FROM users u WHERE u.team_id = %s """ players = db.execute_query(players_query, (team_id,)) or [] # Weight coefficients for rating calculation (duplicate from player_profile for consistency) W_K = 1.5 W_KD = 1.0 W_A = 0.5 W_Dmg = 0.01 W_HS = 0.75 W_UD = 0.005 W_WR = 0.1 for player in players: player['total_matches_played'] = (player.get('wins', 0) or 0) + (player.get('losses', 0) or 0) # Divisor for averages (if no matches, divide by 1) divisor_for_averages = player['total_matches_played'] if player['total_matches_played'] > 0 else 1 if player['total_matches_played'] > 0: player['winrate'] = (player['wins'] / player['total_matches_played']) * 100 else: player['winrate'] = 0.0 # Calculate K/D Ratio player['kd_ratio'] = player['total_kills'] / player['total_deaths'] if player['total_deaths'] > 0 else player['total_kills'] # Calculate avg_adr: always use 24 rounds total_rounds_played = player['total_matches_played'] * 24 player['avg_adr'] = player['total_damage'] / total_rounds_played if total_rounds_played > 0 else 0.0 # Calculate match averages player['avg_kills'] = player['total_kills'] / divisor_for_averages player['avg_deaths'] = player['total_deaths'] / divisor_for_averages player['avg_assists'] = player['total_assists'] / divisor_for_averages player['avg_headshot_kills'] = player['total_headshot_kills'] / divisor_for_averages player['avg_utility_damage'] = player['total_utility_damage'] / divisor_for_averages if player['total_utility_damage'] is not None else 0.0 # --- Calculate Player Rating Index (PRI) for each player --- kpm = player['avg_kills'] apm = player['avg_assists'] dmgpm = player['avg_adr'] hspm = player['avg_headshot_kills'] udpm = player['avg_utility_damage'] win_rate = player['winrate'] kd_ratio = player['kd_ratio'] player_rating = (kpm * W_K) + \ (kd_ratio * W_KD) + \ (apm * W_A) + \ (dmgpm * W_Dmg) + \ (hspm * W_HS) + \ (udpm * W_UD) + \ (win_rate * W_WR) player['player_rating'] = round(player_rating, 2) # --- End of Rating Calculation --- players.sort(key=lambda p: p['kd_ratio'], reverse=True) # NEW: Query for tournaments won by this team won_tournaments_query = """ SELECT id, name, short_name, start_date, end_date, status FROM tournaments WHERE winner_team_id = %s ORDER BY end_date DESC """ won_tournaments = db.execute_query(won_tournaments_query, (team_id,)) or [] recent_matches_details = [] # This was an empty list in your original code, keeping it for now. players1_query = """ SELECT u.id,steam_id, u.username, u.avatar, u.wins, u.losses, u.total_kills, u.total_deaths, u.total_damage, u.total_assists, u.total_headshot_kills, u.total_utility_damage, u.role, u.is_ban, u.ban_reason, t.name as team_name, t.id as team_id FROM users u LEFT JOIN teams t ON u.team_id = t.id -- Сортировка будет по рейтингу после расчетов в Python, поэтому здесь LIMIT 50 достаточно LIMIT 50 """ players1 = db.execute_query(players1_query) or [] user_data_from_session = session.get('user') current_user_steam_id = None if user_data_from_session: current_user_steam_id = user_data_from_session.get('steam_id') print(f"DEBUG: Extracted current_user_steam_id: {current_user_steam_id}") # For double-checking current_user_player = None if current_user_steam_id: # Only proceed if we have a steam_id from the session for p in players1: # Ensure consistent types for comparison if str(p.get('steam_id')) == str(current_user_steam_id): current_user_player = p break # Found the player, no need to continue the loop return render_template('team.html', team=team, stats=stats, players=players, won_tournaments=won_tournaments, # Pass won tournaments to the template current_user_player=current_user_player, recent_matches=recent_matches_details) @app.route('/tournaments') def tournaments_list(): db = Database() try: tournaments_query = "SELECT id, name, start_date, end_date, format, status, is_private FROM tournaments ORDER BY start_date DESC" tournaments = db.execute_query(tournaments_query) players_query = """ SELECT u.id,steam_id, u.username, u.avatar, u.wins, u.losses, u.total_kills, u.total_deaths, u.total_damage, u.total_assists, u.total_headshot_kills, u.total_utility_damage, u.role, u.is_ban, u.ban_reason, t.name as team_name, t.id as team_id FROM users u LEFT JOIN teams t ON u.team_id = t.id -- Сортировка будет по рейтингу после расчетов в Python, поэтому здесь LIMIT 50 достаточно LIMIT 50 """ players = db.execute_query(players_query) or [] user_data_from_session = session.get('user') current_user_steam_id = None if user_data_from_session: current_user_steam_id = user_data_from_session.get('steam_id') print(f"DEBUG: Extracted current_user_steam_id: {current_user_steam_id}") # For double-checking current_user_player = None if current_user_steam_id: # Only proceed if we have a steam_id from the session for p in players: # Ensure consistent types for comparison if str(p.get('steam_id')) == str(current_user_steam_id): current_user_player = p break # Found the player, no need to continue the loop return render_template('tournaments.html',current_user_player=current_user_player, tournaments=tournaments) except Error as e: print(f"Error fetching tournaments: {e}") return "Ошибка при загрузке турниров", 500 finally: # db.close() # Ensure connection is closed - REMOVE THIS LINE pass # Or remove the entire finally block if only db.close() was there def get_db_connection(): try: conn = mysql.connector.connect(**DB_CONFIG) if conn.is_connected(): cursor = conn.cursor(dictionary=True) # Return results as dictionaries return conn, cursor else: print('Failed to connect to database.') return None, None except Error as e: print(f'Error connecting to MySQL: {e}') return None, None @app.route('/tournament/') def tournament_details(tournament_id): # 1. Получаем информацию о турнире, включая JSON-поле 'bracket_data' и 'format' tournament_query = """ SELECT id, name, description, start_date, end_date, status, format, bracket_data, winner_team_id, add_team_elo FROM tournaments WHERE id = %s """ tournament = db.execute_query(tournament_query, (tournament_id,), fetch_one=True) players_query = """ SELECT u.id,steam_id, u.username, u.avatar, u.wins, u.losses, u.total_kills, u.total_deaths, u.total_damage, u.total_assists, u.total_headshot_kills, u.total_utility_damage, u.role, u.is_ban, u.ban_reason, t.name as team_name, t.id as team_id FROM users u LEFT JOIN teams t ON u.team_id = t.id -- Сортировка будет по рейтингу после расчетов в Python, поэтому здесь LIMIT 50 достаточно LIMIT 50 """ players = db.execute_query(players_query) or [] user_data_from_session = session.get('user') current_user_steam_id = None if user_data_from_session: current_user_steam_id = user_data_from_session.get('steam_id') print(f'DEBUG: Extracted current_user_steam_id: {current_user_steam_id}') # For double-checking current_user_player = None if ( current_user_steam_id ): # Only proceed if we have a steam_id from the session for p in players: # Ensure consistent types for comparison if str(p.get('steam_id')) == str(current_user_steam_id): current_user_player = p break # Found the player, no need to continue the loop if not tournament: return render_template('404.html'), 404 # 2. Извлекаем и парсим 'bracket_data' из JSON поля турнира # Инициализируем bracket_data с ожидаемыми ключами, чтобы избежать KeyError bracket_data = { 'quarterfinals': [], 'semifinals': [], 'grand_final': [], } if tournament.get('bracket_data'): try: bracket_data_from_db = tournament['bracket_data'] if isinstance(bracket_data_from_db, str): parsed_bracket_data = json.loads(bracket_data_from_db) else: parsed_bracket_data = bracket_data_from_db # Обновляем bracket_data только для тех ключей, которые есть в спарсенных данных # Это позволяет поддерживать гибкость, если не все раунды присутствуют for key in ['quarterfinals', 'semifinals', 'grand_final']: if ( key in parsed_bracket_data and isinstance(parsed_bracket_data[key], list) ): bracket_data[key] = parsed_bracket_data[key] except (json.JSONDecodeError, TypeError) as e: print( f"Ошибка парсинга JSON 'bracket_data' для турнира {tournament_id}: {e}" ) # Оставим пустую сетку, если произошла ошибка парсинга # --- START: New logic for Tournament Winner --- tournament_winner_info = None if tournament.get('winner_team_id'): winner_team_id = tournament['winner_team_id'] winner_team_query = 'SELECT id, name, tag, logo_url, elo FROM teams WHERE id = %s' winner_team_data = db.execute_query( winner_team_query, (winner_team_id,), fetch_one=True ) if winner_team_data: tournament_winner_info = winner_team_data tournament_winner_info['add_team_elo'] = tournament.get('add_team_elo') # --- END: New logic for Tournament Winner --- # 3. Собираем информацию о всех командах, участвующих в играх, используя данные из bracket_data team_ids = set() # Итерируемся по всем ожидаемым раундам и их матчам for round_key in ['quarterfinals', 'semifinals', 'grand_final']: if ( round_key in bracket_data ): # Проверяем, что ключ раунда существует (он должен быть из инициализации) for game in bracket_data[round_key]: # Теперь bracket_data[round_key] точно список if game.get('team_a_id') is not None: team_ids.add(int(game['team_a_id'])) if game.get('team_b_id') is not None: team_ids.add(int(game['team_b_id'])) if game.get('winner_team_id') is not None: team_ids.add(int(game['winner_team_id'])) teams_info = {} if team_ids: placeholders = ','.join(['%s'] * len(team_ids)) # Fetch logo_url as well teams_query = f'SELECT id, name, tag, logo_url FROM teams WHERE id IN ({placeholders})' fetched_teams = db.execute_query(teams_query, tuple(list(team_ids))) if fetched_teams: for team in fetched_teams: teams_info[team['id']] = team # 4. Рендерим шаблон, передавая все необходимые данные return render_template( 'tournament_details.html', tournament=tournament, bracket_data=bracket_data, # Теперь это уже распарсенный JSON из БД current_user_player=current_user_player, teams_info=teams_info, tournament_winner_info=tournament_winner_info, ) # Pass winner info @app.route('/api/tournament//bracket', methods=['POST']) def get_tournament_bracket_html(tournament_id): conn, cursor = None, None try: conn, cursor = get_db_connection() if not conn or not cursor: return jsonify({'error': 'Database connection failed'}), 500 tournament_query = 'SELECT bracket_data FROM tournaments WHERE id = %s;' cursor.execute(tournament_query, (tournament_id,)) tournament_data = cursor.fetchone() if not tournament_data or not tournament_data['bracket_data']: return jsonify( { 'html': '
Сетка еще не сформирована.
' } ) bracket_json = json.loads(tournament_data['bracket_data']) # --- NEW: Fetch all actual match details from the 'matches' table for this tournament --- # We need the 'id' from the 'matches' table to link to the /match/ route # and also status if you want to display it. tournament_matches_query = """ SELECT id, bracket_match_json_id, status, match_date FROM matches WHERE tournament_id = %s """ cursor.execute(tournament_matches_query, (tournament_id,)) tournament_matches_from_db = cursor.fetchall() # Create a lookup dictionary for quick access: bracket_match_json_id -> actual_match_db_row matches_db_lookup = {m['bracket_match_json_id']: m for m in tournament_matches_from_db} # --- END NEW --- all_team_ids = set() for round_name, matches_in_round in bracket_json.items(): for match in matches_in_round: if match.get('team_a_id'): all_team_ids.add(match['team_a_id']) if match.get('team_b_id'): all_team_ids.add(match['team_b_id']) if match.get('winner_team_id'): all_team_ids.add(match['winner_team_id']) teams_info = {} if all_team_ids: team_placeholders = ','.join(['%s'] * len(all_team_ids)) teams_query = f'SELECT id, name, tag, logo_url FROM teams WHERE id IN ({team_placeholders});' cursor.execute(teams_query, tuple(list(all_team_ids))) teams_data = cursor.fetchall() for team in teams_data: teams_info[team['id']] = { 'name': team['name'], 'tag': team['tag'], 'logo_url': team.get('logo_url'), } def get_sort_key(round_name): numbers = re.findall(r'\d+', round_name) if numbers: try: return int(numbers[0]) except ValueError: pass if round_name == 'quarterfinals': return 50 elif round_name == 'semifinals': return 100 elif round_name == 'grand_final': return 200 return float('inf') sorted_round_keys = sorted(bracket_json.keys(), key=get_sort_key) full_bracket_html_parts = [] for round_name in sorted_round_keys: matches_in_round = bracket_json[round_name] round_title = round_name.replace('_', ' ').upper() round_html = f"""
{round_title}
""" for match in matches_in_round: # --- NEW: Augment the bracket_data match with details from the 'matches' table --- db_match_info = matches_db_lookup.get(match.get('match_id')) # Use .get() for safety if db_match_info: match['db_match_id'] = db_match_info['id'] # This is the ID we need for /match/ match['status_from_db'] = db_match_info['status'] match['match_date_from_db'] = db_match_info['match_date'] else: # Fallback if no entry in 'matches' table for this bracket match match['db_match_id'] = None match['status_from_db'] = 'Not Recorded' # Or 'Upcoming', 'Pending', etc. match['match_date_from_db'] = None # --- END NEW --- match_html = render_template( 'bracket_match_triangle.html', match=match, teams_info=teams_info ) round_html += match_html round_html += """
""" full_bracket_html_parts.append(round_html) full_bracket_html = ''.join(full_bracket_html_parts) return jsonify({'html': full_bracket_html}) except Error as e: print(f'Database error: {e}') return jsonify({'error': 'Database error', 'details': str(e)}), 500 except json.JSONDecodeError: return jsonify({'error': 'Invalid bracket_data JSON'}), 400 except Exception as e: print(f'An unexpected error occurred: {e}') return jsonify({'error': 'An unexpected error occurred', 'details': str(e)}), 500 finally: if cursor: cursor.close() if conn: conn.close() @app.route('/login') def login(): if 'user' in session: return redirect(url_for('index')) return redirect(SteamAuth.get_steam_login_url()) @app.route('/auth/steam/callback') def steam_callback(): if SteamAuth.verify_steam_auth(request.args): steam_id = SteamAuth.get_steam_id_from_openid(request.args.get('openid.claimed_id')) user_info = SteamAuth.get_steam_user_info(steam_id) if user_info: user_check_query = "SELECT id, username, avatar, wins, losses, team_id, description, is_ban, ban_reason, country, total_kills, total_deaths, total_damage, total_assists, total_headshot_kills, total_utility_damage FROM users WHERE steam_id = %s" user_results = db.execute_query(user_check_query, (steam_id,)) user_in_db = user_results[0] if user_results else None if user_in_db: session['user'] = { 'id': user_in_db['id'], 'steam_id': steam_id, 'username': user_in_db['username'], 'avatar': user_in_db['avatar'], 'wins': user_in_db['wins'], 'losses': user_in_db['losses'], 'team_id': user_in_db['team_id'], 'description': user_in_db['description'], 'country': user_in_db['country'], 'is_player': True, 'is_ban': user_in_db['is_ban'], 'ban_reason': user_in_db['ban_reason'] } else: insert_user_query = """ INSERT INTO users (steam_id, username, avatar) VALUES (%s, %s, %s) """ db.execute_query(insert_user_query, (steam_id, user_info['personaname'], user_info['avatarfull'])) inserted_user = db.execute_query(user_check_query, (steam_id,), fetch_one=True) session['user'] = { 'id': inserted_user['id'], 'steam_id': steam_id, 'username': user_info['personaname'], 'avatar': user_info['avatarfull'], 'wins': 0, 'losses': 0, 'team_id': None, 'description': None, 'country': None, 'is_player': True, 'is_ban': 0, 'ban_reason': None } return redirect(url_for('index')) return redirect(url_for('login')) @app.route('/logout') def logout(): session.pop('user', None) return redirect(url_for('index')) COUNTRIES = [ 'Private', 'Afghanistan', 'Albania', 'Algeria', 'Andorra', 'Angola', 'Antigua and Barbuda', 'Argentina', 'Armenia', 'Australia', 'Austria', 'Azerbaijan', 'Bahamas', 'Bahrain', 'Bangladesh', 'Barbados', 'Belarus', 'Belgium', 'Belize', 'Benin', 'Bhutan', 'Bolivia', 'Bosnia and Herzegovina', 'Botswana', 'Brazil', 'Brunei', 'Bulgaria', 'Burkina Faso', 'Burundi', 'Cabo Verde', 'Cambodia', 'Cameroon', 'Canada', 'Central African Republic', 'Chad', 'Chile', 'China', 'Colombia', 'Comoros', 'Congo (Brazzaville)', 'Congo (Kinshasa)', 'Costa Rica', 'Croatia', 'Cuba', 'Cyprus', 'Czechia', 'Denmark', 'Djibouti', 'Dominica', 'Dominican Republic', 'Ecuador', 'Egypt', 'El Salvador', 'Equatorial Guinea', 'Eritrea', 'Estonia', 'Eswatini', 'Ethiopia', 'Fiji', 'Finland', 'France', 'Gabon', 'Gambia', 'Georgia', 'Germany', 'Ghana', 'Greece', 'Grenada', 'Guatemala', 'Guinea', 'Guinea-Bissau', 'Guyana', 'Haiti', 'Honduras', 'Hungary', 'Iceland', 'India', 'Indonesia', 'Iran', 'Iraq', 'Ireland', 'Israel', 'Italy', 'Ivory Coast', 'Jamaica', 'Japan', 'Jordan', 'Kazakhstan', 'Kenya', 'Kiribati', 'Kuwait', 'Kyrgyzstan', 'Laos', 'Latvia', 'Lebanon', 'Lesotho', 'Liberia', 'Libya', 'Liechtenstein', 'Lithuania', 'Luxembourg', 'Madagascar', 'Malawi', 'Malaysia', 'Maldives', 'Mali', 'Malta', 'Marshall Islands', 'Mauritania', 'Mauritius', 'Mexico', 'Micronesia', 'Moldova', 'Monaco', 'Mongolia', 'Montenegro', 'Morocco', 'Mozambique', 'Myanmar', 'Namibia', 'Nauru', 'Nepal', 'Netherlands', 'New Zealand', 'Nicaragua', 'Niger', 'Nigeria', 'North Korea', 'North Macedonia', 'Norway', 'Oman', 'Pakistan', 'Palau', 'Palestine', 'Panama', 'Papua New Guinea', 'Paraguay', 'Peru', 'Philippines', 'Poland', 'Portugal', 'Qatar', 'Romania', 'Russia', 'Rwanda', 'Saint Kitts and Nevis', 'Saint Lucia', 'Saint Vincent and the Grenadines', 'Samoa', 'San Marino', 'Sao Tome and Principe', 'Saudi Arabia', 'Senegal', 'Serbia', 'Seychelles', 'Sierra Leone', 'Singapore', 'Slovakia', 'Slovenia', 'Solomon Islands', 'Somalia', 'South Africa', 'South Korea', 'South Sudan', 'Spain', 'Sri Lanka', 'Sudan', 'Suriname', 'Sweden', 'Switzerland', 'Syria', 'Taiwan', 'Tajikistan', 'Tanzania', 'Thailand', 'Timor-Leste', 'Togo', 'Tonga', 'Trinidad and Tobago', 'Tunisia', 'Turkey', 'Turkmenistan', 'Tuvalu', 'Uganda', 'Ukraine', 'United Arab Emirates', 'United Kingdom', 'United States', 'Uruguay', 'Uzbekistan', 'Vanuatu', 'Vatican City', 'Venezuela', 'Vietnam', 'Yemen', 'Zambia', 'Zimbabwe' ] @app.route('/player/edit', methods=['GET', 'POST']) def edit_profile(): if 'user' not in session: return redirect(url_for('login')) user_id = session['user']['id'] if request.method == 'POST': description = request.form.get('description') country = request.form.get('country') real_name = request.form.get('real_name') # Получаем реальное имя age = request.form.get('age') # Получаем дату рождения if country not in COUNTRIES: country = None # Проверяем, если дата рождения пустая строка, сохраняем как None if not age: age = None update_query = """ UPDATE users SET description = %s, country = %s, real_name = %s, age = %s WHERE id = %s """ db.execute_query(update_query, (description, country, real_name, age, user_id)) # Обновляем данные в сессии, чтобы они сразу отобразились без нового запроса к БД session['user']['description'] = description session['user']['country'] = country session['user']['real_name'] = real_name session['user']['age'] = age return redirect(url_for('player_profile', user_id=user_id)) # Для GET-запроса, получаем все необходимые данные игрока # Убедитесь, что 'real_name' и 'age' также извлекаются из БД player_query = """ SELECT id, username, avatar, description, country, wins, losses, team_id, real_name, age FROM users WHERE id = %s """ player = db.execute_query(player_query, (user_id,), fetch_one=True) if not player: session.pop('user', None) return redirect(url_for('login')) return render_template('edit_profile.html', player=player, countries=COUNTRIES) @app.route('/upload_matches', methods=['GET', 'POST']) def upload_matches(): client_ip = request.remote_addr if client_ip not in ALLOWED_IPS: return "Доступ запрещен. Ваш IP не находится в списке разрешенных.", 403 if request.method == 'POST': if 'file' not in request.files: return "Файл не загружен", 400 file = request.files['file'] if file.filename == '': return "Файл не выбран", 400 if file and file.filename.endswith('.csv'): stream = io.StringIO(file.stream.read().decode("UTF8"), newline=None) csv_reader = csv.reader(stream) header = next(csv_reader) # Пропускаем заголовок # Создаем словарь индексов столбцов try: idx = {name: i for i, name in enumerate(header)} # Проверка наличия обязательных полей required_fields = ['matchid', 'steamid64', 'team', 'name', 'kills', 'deaths', 'damage', 'assists', 'head_shot_kills', 'utility_damage'] if not all(field in idx for field in required_fields): return f"Отсутствуют обязательные поля в CSV: {', '.join(required_fields)}", 400 except Exception as e: return f"Ошибка при обработке заголовка CSV: {e}", 400 match_id_set = set() # Используем для проверки уникальности матчей # --- 1. Обработка статистики игроков --- for row in csv_reader: try: match_id = int(row[idx['matchid']]) steam_id = row[idx['steamid64']] if not re.match(r'7656119[0-9]{10}', steam_id): print(f"Пропущен некорректный Steam ID: {steam_id}") continue match_id_set.add(match_id) # Извлечение численных данных и имени игрока stats = { 'username': row[idx['name']], # <--- ИСПОЛЬЗУЕМ 'name' ИЗ CSV 'team': row[idx['team']], 'kills': int(row[idx['kills']]), 'deaths': int(row[idx['deaths']]), 'damage': int(row[idx['damage']]), 'assists': int(row[idx['assists']]), 'head_shot_kills': int(row[idx['head_shot_kills']]), 'utility_damage': int(row[idx['utility_damage']]) } # Проверяем, была ли статистика для этого игрока/матча уже добавлена stat_exists_query = "SELECT 1 FROM match_players_stats WHERE match_id = %s AND steam_id = %s" existing_stat = db.execute_query(stat_exists_query, (match_id, steam_id)) if not existing_stat: insert_stat_query = """ INSERT INTO match_players_stats ( match_id, steam_id, team, kills, deaths, damage, assists, head_shot_kills, utility_damage ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) """ db.execute_query(insert_stat_query, ( match_id, steam_id, stats['team'], stats['kills'], stats['deaths'], stats['damage'], stats['assists'], stats['head_shot_kills'], stats['utility_damage'] )) else: update_stats_query = """ UPDATE match_players_stats SET team = %s, kills = %s, deaths = %s, damage = %s, assists = %s, head_shot_kills = %s, utility_damage = %s WHERE match_id = %s AND steam_id = %s """ db.execute_query(update_stats_query, ( stats['team'], stats['kills'], stats['deaths'], stats['damage'], stats['assists'], stats['head_shot_kills'], stats['utility_damage'], match_id, steam_id )) # --- Обновление или создание пользователя в таблице users --- user_row = db.execute_query("SELECT id FROM users WHERE steam_id = %s", (steam_id,)) if not user_row: # Создаем нового пользователя insert_new_user_query = """ INSERT INTO users (steam_id, username, avatar) VALUES (%s, %s, %s) """ db.execute_query(insert_new_user_query, ( steam_id, stats['username'], 'default_avatar.png' # <--- ИСПОЛЬЗУЕМ stats['username'] )) else: # Обновляем имя, если оно изменилось update_user_name_query = "UPDATE users SET username = %s WHERE steam_id = %s" db.execute_query(update_user_name_query, (stats['username'], steam_id)) except (ValueError, IndexError) as e: print(f"Ошибка при обработке строки CSV: {row}. Ошибка: {e}") continue # --- 2. Упрощенная логика создания записи о матче (matches) --- # Предполагаем, что match_data_map0_7.csv относится только к одному матчу if match_id_set: for match_id in match_id_set: match_exists = db.execute_query("SELECT id FROM matches WHERE id = %s", (match_id,)) if not match_exists: # Получаем две команды для этого матча из match_players_stats teams_data = db.execute_query("SELECT DISTINCT team FROM match_players_stats WHERE match_id = %s LIMIT 2", (match_id,)) if len(teams_data) == 2: team_a_name = teams_data[0]['team'] team_b_name = teams_data[1]['team'] def get_or_create_team_id(team_name): team_data = db.execute_query("SELECT id FROM teams WHERE name = %s", (team_name,)) if team_data: return team_data['id'] else: # Вставляем новую команду (Tag - упрощенно от имени) db.execute_query("INSERT INTO teams (name, tag) VALUES (%s, %s)", (team_name, team_name.split('_')[-1]), commit=True) return db.execute_query("SELECT id FROM teams WHERE name = %s", (team_name,), fetch_mode='one')['id'] team_a_id = get_or_create_team_id(team_a_name) team_b_id = get_or_create_team_id(team_b_name) # В CSV нет информации о счете, используем заглушки или берем из match_id (если это convention) score_a = 16 score_b = 14 winner_team = team_a_name map_name = 'Placeholder Map' insert_match_query = """ INSERT INTO matches (id, team_a_id, team_b_id, score_a, score_b, winner_team, map_name, date) VALUES (%s, %s, %s, %s, %s, %s, %s, NOW()) """ db.execute_query(insert_match_query, (match_id, team_a_id, team_b_id, score_a, score_b, winner_team, map_name)) else: print(f"Матч {match_id} пропущен: найдено {len(teams_data)} команд.") return "Файл CSV успешно загружен и обработан! Статистика игроков и матчи обновлены.", 200 else: return "Неверный формат файла. Пожалуйста, загрузите CSV.", 400 return render_template('upload_matches.html') @app.errorhandler(404) def page_not_found(e): return render_template('404.html'), 404 if __name__ == '__main__': app.run(debug=True)