254 lines
9.4 KiB
Python
254 lines
9.4 KiB
Python
import os
|
||
import sqlite3
|
||
import csv
|
||
from flask import Flask, render_template_string, request, redirect, url_for
|
||
import pandas as pd # Still needed for initial conversion if not done
|
||
from collections import defaultdict
|
||
|
||
app = Flask(__name__)
|
||
|
||
# Database file
|
||
DB_FILE = 'all_tops.db'
|
||
|
||
# Function to convert CSV to SQLite if not exists, with indexes for speed
|
||
def csv_to_sqlite(csv_file, db_file):
|
||
if os.path.exists(db_file):
|
||
print(f"Database {db_file} already exists. Skipping conversion.")
|
||
return
|
||
print(f"Converting {csv_file} to {db_file}...")
|
||
conn = sqlite3.connect(db_file)
|
||
cur = conn.cursor()
|
||
cur.execute('''
|
||
CREATE TABLE tops (
|
||
stat TEXT,
|
||
rank INTEGER,
|
||
username TEXT,
|
||
uuid TEXT,
|
||
raw_value INTEGER,
|
||
formatted_value REAL,
|
||
unit TEXT
|
||
)
|
||
''')
|
||
with open(csv_file, 'r', encoding='utf-8') as f:
|
||
reader = csv.reader(f)
|
||
next(reader) # Skip header
|
||
cur.executemany('INSERT INTO tops VALUES (?, ?, ?, ?, ?, ?, ?)', reader)
|
||
# Add indexes for faster queries
|
||
cur.execute('CREATE INDEX idx_username ON tops (username)')
|
||
cur.execute('CREATE INDEX idx_uuid ON tops (uuid)')
|
||
cur.execute('CREATE INDEX idx_stat ON tops (stat)')
|
||
cur.execute('CREATE INDEX idx_rank ON tops (rank)')
|
||
cur.execute('CREATE INDEX idx_raw_value ON tops (raw_value)')
|
||
conn.commit()
|
||
conn.close()
|
||
print("Conversion complete with indexes.")
|
||
|
||
# Run conversion
|
||
csv_to_sqlite('all_tops.csv', DB_FILE)
|
||
|
||
# Connect to DB
|
||
def get_db_connection():
|
||
conn = sqlite3.connect(DB_FILE)
|
||
conn.row_factory = sqlite3.Row
|
||
return conn
|
||
|
||
# Get unique players with pagination
|
||
def get_unique_players(page=1, per_page=20):
|
||
conn = get_db_connection()
|
||
cur = conn.cursor()
|
||
offset = (page - 1) * per_page
|
||
cur.execute('SELECT COUNT(DISTINCT username) FROM tops')
|
||
total = cur.fetchone()[0]
|
||
cur.execute('SELECT DISTINCT username FROM tops ORDER BY username LIMIT ? OFFSET ?', (per_page, offset))
|
||
players = [row['username'] for row in cur.fetchall()]
|
||
conn.close()
|
||
return players, total
|
||
|
||
# Get unique stats
|
||
def get_unique_stats():
|
||
conn = get_db_connection()
|
||
cur = conn.cursor()
|
||
cur.execute('SELECT DISTINCT stat FROM tops ORDER BY stat')
|
||
stats = [row['stat'] for row in cur.fetchall()]
|
||
conn.close()
|
||
return stats
|
||
|
||
# HTML template for main page (no full table to save memory)
|
||
main_template = """
|
||
<!doctype html>
|
||
<html lang="ru">
|
||
<head>
|
||
<title>Данные о Топах</title>
|
||
<style>
|
||
body { font-family: Arial, sans-serif; background-color: #f4f4f9; color: #333; margin: 0; padding: 20px; }
|
||
h1, h2, h3 { color: #4a4a8c; }
|
||
ul { list-style-type: none; padding: 0; }
|
||
li { list-style-type: none; background: #fff; margin: 5px 0; padding: 10px; border-radius: 5px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
|
||
a { color: #6a5acd; text-decoration: none; }
|
||
a:hover { text-decoration: underline; }
|
||
.pagination { list-style-type: none; margin: 10px 0; display: flex; justify-content: center; }
|
||
.pagination li { margin: 0 5px; }
|
||
form { margin-bottom: 20px; text-align: center; }
|
||
input[type="text"] { padding: 8px; width: 300px; }
|
||
button { padding: 8px 12px; background: #6a5acd; color: white; border: none; border-radius: 5px; cursor: pointer; }
|
||
</style>
|
||
</head>
|
||
<body>
|
||
<h1>Данные о Топах из all_tops.db</h1>
|
||
<p>Всего уникальных игроков: {{ num_players }}</p>
|
||
<p>Всего уникальных статистик: {{ num_stats }}</p>
|
||
<form action="/search" method="get">
|
||
<input type="text" name="query" placeholder="Введите ник или UUID игрока">
|
||
<button type="submit">Поиск</button>
|
||
</form>
|
||
<h2>Игроки (Страница {{ page }} из {{ num_pages }})</h2>
|
||
<ul>
|
||
{% for player in players %}
|
||
<li><a href="/player/{{ player }}">{{ player }}</a></li>
|
||
{% endfor %}
|
||
</ul>
|
||
<ul class="pagination">
|
||
{% if page > 1 %}
|
||
<li><a href="?page={{ page - 1 }}">Предыдущая</a></li>
|
||
{% endif %}
|
||
{% if page < num_pages %}
|
||
<li><a href="?page={{ page + 1 }}">Следующая</a></li>
|
||
{% endif %}
|
||
</ul>
|
||
</body>
|
||
</html>
|
||
"""
|
||
|
||
# HTML template for player page
|
||
player_template = """
|
||
<!doctype html>
|
||
<html lang="ru">
|
||
<head>
|
||
<title>Игрок: {{ player }}</title>
|
||
<style>
|
||
body { font-family: Arial, sans-serif; background-color: #f4f4f9; color: #333; margin: 0; padding: 20px; }
|
||
h1, h2, h3 { color: #4a4a8c; }
|
||
table { border-collapse: collapse; width: 100%; margin-bottom: 20px; }
|
||
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
|
||
th { background-color: #6a5acd; color: white; }
|
||
tr:nth-child(even) { background-color: #f2f2f2; }
|
||
img { border-radius: 50%; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
|
||
.overview { display: flex; flex-wrap: wrap; justify-content: space-around; background: #fff; padding: 15px; border-radius: 5px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); margin-bottom: 20px; width: 100%; }
|
||
.category-block { flex: 1 1 200px; margin: 10px; padding: 10px; background: #e6e6fa; border-radius: 5px; }
|
||
.category-block ul { list-style-type: disc; padding-left: 20px; }
|
||
</style>
|
||
</head>
|
||
<body>
|
||
<h1>Игрок: {{ player }}</h1>
|
||
<img src="https://mc-heads.net/head/{{ uuid }}/64" alt="Голова Игрока">
|
||
<a href="/">Назад к Главной</a>
|
||
<h2>Обзор Лучших Результатов</h2>
|
||
<div class="overview">
|
||
{% for cat, top_entries in best_groups %}
|
||
<div class="category-block">
|
||
<h3>Лучшее в {{ cat }}</h3>
|
||
<ul>
|
||
{% for entry in top_entries %}
|
||
<li>{{ entry['stat'] }}: Ранг {{ entry['rank'] }} ({{ entry['formatted_value'] }} {{ entry['unit'] }})</li>
|
||
{% endfor %}
|
||
</ul>
|
||
</div>
|
||
{% endfor %}
|
||
</div>
|
||
<h2>Все Ранги в Топах</h2>
|
||
{% for cat, group_entries in sorted_groups %}
|
||
<h3>{{ cat }}</h3>
|
||
<table>
|
||
<tr>
|
||
<th>Статистика</th>
|
||
<th>Ранг</th>
|
||
<th>Сырое Значение</th>
|
||
<th>Форматированное Значение</th>
|
||
<th>Единица</th>
|
||
</tr>
|
||
{% for entry in group_entries %}
|
||
<tr>
|
||
<td>{{ entry['stat'] }}</td>
|
||
<td>{{ entry['rank'] }}</td>
|
||
<td>{{ entry['raw_value'] }}</td>
|
||
<td>{{ entry['formatted_value'] }}</td>
|
||
<td>{{ entry['unit'] }}</td>
|
||
</tr>
|
||
{% endfor %}
|
||
</table>
|
||
{% endfor %}
|
||
</body>
|
||
</html>
|
||
"""
|
||
|
||
@app.route('/')
|
||
def index():
|
||
page = request.args.get('page', 1, type=int)
|
||
per_page = 20
|
||
players, total = get_unique_players(page, per_page)
|
||
num_pages = (total + per_page - 1) // per_page
|
||
return render_template_string(main_template, num_players=total, num_stats=len(get_unique_stats()), players=players, page=page, num_pages=num_pages)
|
||
|
||
@app.route('/search')
|
||
def search():
|
||
query = request.args.get('query', '').strip()
|
||
if not query:
|
||
return redirect(url_for('index'))
|
||
|
||
conn = get_db_connection()
|
||
cur = conn.cursor()
|
||
|
||
# Check if query is username
|
||
cur.execute('SELECT DISTINCT username FROM tops WHERE username = ?', (query,))
|
||
result = cur.fetchone()
|
||
if result:
|
||
conn.close()
|
||
return redirect(url_for('player_page', player=query))
|
||
|
||
# Check if query is uuid
|
||
cur.execute('SELECT DISTINCT username FROM tops WHERE uuid = ?', (query,))
|
||
result = cur.fetchone()
|
||
if result:
|
||
conn.close()
|
||
return redirect(url_for('player_page', player=result['username']))
|
||
|
||
conn.close()
|
||
return f"Игрок с ником или UUID '{query}' не найден."
|
||
|
||
@app.route('/player/<player>')
|
||
def player_page(player):
|
||
conn = get_db_connection()
|
||
cur = conn.cursor()
|
||
cur.execute('SELECT * FROM tops WHERE username = ? AND raw_value > 0 ORDER BY rank ASC', (player,))
|
||
entries = cur.fetchall()
|
||
conn.close()
|
||
|
||
if not entries:
|
||
return f"Нет данных для игрока {player}"
|
||
|
||
uuid = entries[0]['uuid']
|
||
|
||
grouped = defaultdict(list)
|
||
for entry in entries:
|
||
stat = entry['stat']
|
||
cat = stat.split(':')[0] if ':' in stat else stat
|
||
grouped[cat].append(entry)
|
||
|
||
# Sort groups by the min rank in the group (best performance first)
|
||
sorted_groups = sorted(grouped.items(), key=lambda x: min(e['rank'] for e in x[1]))
|
||
|
||
# For each group, sort entries by rank ASC (best on top)
|
||
for cat, group_entries in sorted_groups:
|
||
group_entries.sort(key=lambda e: e['rank'])
|
||
|
||
# For best overview: for each cat, take top 5 best (lowest rank)
|
||
best_groups = []
|
||
for cat, group_entries in sorted_groups:
|
||
top5 = sorted(group_entries, key=lambda e: e['rank'])[:5]
|
||
best_groups.append((cat, top5))
|
||
|
||
return render_template_string(player_template, player=player, sorted_groups=sorted_groups, best_groups=best_groups, uuid=uuid)
|
||
|
||
if __name__ == '__main__':
|
||
print("Starting Flask server...")
|
||
app.run(debug=True) |