645 lines
17 KiB
Python
645 lines
17 KiB
Python
from db import get_connection
|
||
from repositories.team_repository import get_team_id_by_external_id
|
||
|
||
|
||
def normalize_birth_date_for_input(value) -> str:
|
||
if not value:
|
||
return ""
|
||
|
||
text = str(value).strip()
|
||
|
||
if not text:
|
||
return ""
|
||
|
||
# Уже нормальный формат для input[type=date]
|
||
if len(text) == 10 and text[4] == "-" and text[7] == "-":
|
||
return text
|
||
|
||
# Формат ДД.ММ.ГГГГ -> YYYY-MM-DD
|
||
if len(text) == 10 and text[2] == "." and text[5] == ".":
|
||
dd, mm, yyyy = text.split(".")
|
||
return f"{yyyy}-{mm}-{dd}"
|
||
|
||
return ""
|
||
|
||
|
||
def upsert_player(
|
||
external_id: str,
|
||
team_external_id: str,
|
||
player: str,
|
||
lastname: str = "",
|
||
name: str = "",
|
||
number: str = "",
|
||
pos: str = "",
|
||
amplua: str = "",
|
||
born: str = "",
|
||
games: int = 0,
|
||
goals: int = 0,
|
||
penaltys: int = 0,
|
||
assists: int = 0,
|
||
yellows: int = 0,
|
||
reds: int = 0,
|
||
is_active: bool = True,
|
||
) -> None:
|
||
team_id = get_team_id_by_external_id(str(team_external_id).strip())
|
||
if team_id is None:
|
||
raise ValueError(f"Team not found by external_id: {team_external_id}")
|
||
|
||
query = """
|
||
INSERT INTO players (
|
||
external_id,
|
||
team_id,
|
||
full_name,
|
||
first_name,
|
||
last_name,
|
||
number,
|
||
position,
|
||
is_active,
|
||
pos,
|
||
amplua,
|
||
born,
|
||
games,
|
||
goals,
|
||
penaltys,
|
||
assists,
|
||
yellows,
|
||
reds,
|
||
created_at,
|
||
updated_at
|
||
)
|
||
VALUES (
|
||
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
|
||
NOW(), NOW()
|
||
)
|
||
ON CONFLICT (external_id)
|
||
DO UPDATE SET
|
||
team_id = EXCLUDED.team_id,
|
||
full_name = EXCLUDED.full_name,
|
||
first_name = EXCLUDED.first_name,
|
||
last_name = EXCLUDED.last_name,
|
||
number = EXCLUDED.number,
|
||
position = EXCLUDED.position,
|
||
is_active = EXCLUDED.is_active,
|
||
pos = EXCLUDED.pos,
|
||
amplua = EXCLUDED.amplua,
|
||
born = EXCLUDED.born,
|
||
games = EXCLUDED.games,
|
||
goals = EXCLUDED.goals,
|
||
penaltys = EXCLUDED.penaltys,
|
||
assists = EXCLUDED.assists,
|
||
yellows = EXCLUDED.yellows,
|
||
reds = EXCLUDED.reds,
|
||
updated_at = NOW();
|
||
"""
|
||
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(
|
||
query,
|
||
(
|
||
str(external_id).strip(),
|
||
team_id,
|
||
player.strip(),
|
||
name.strip(),
|
||
lastname.strip(),
|
||
str(number).strip(),
|
||
amplua.strip(),
|
||
is_active,
|
||
pos.strip(),
|
||
amplua.strip(),
|
||
born.strip(),
|
||
games,
|
||
goals,
|
||
penaltys,
|
||
assists,
|
||
yellows,
|
||
reds,
|
||
),
|
||
)
|
||
conn.commit()
|
||
except Exception:
|
||
conn.rollback()
|
||
raise
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def get_match_by_external_id(external_id: str):
|
||
query = """
|
||
SELECT id, external_id, home_team_id, away_team_id
|
||
FROM matches
|
||
WHERE external_id = %s
|
||
LIMIT 1;
|
||
"""
|
||
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(query, (str(external_id).strip(),))
|
||
return cur.fetchone()
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def mark_match_parsed(external_id: str) -> None:
|
||
query = """
|
||
UPDATE matches
|
||
SET parsed = TRUE,
|
||
parse_error = NULL,
|
||
updated_at = NOW()
|
||
WHERE external_id = %s;
|
||
"""
|
||
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(query, (str(external_id).strip(),))
|
||
conn.commit()
|
||
except Exception:
|
||
conn.rollback()
|
||
raise
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def mark_match_parse_error(external_id: str, error_text: str) -> None:
|
||
query = """
|
||
UPDATE matches
|
||
SET parsed = FALSE,
|
||
parse_error = %s,
|
||
updated_at = NOW()
|
||
WHERE external_id = %s;
|
||
"""
|
||
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(query, (str(error_text)[:2000], str(external_id).strip()))
|
||
conn.commit()
|
||
except Exception:
|
||
conn.rollback()
|
||
raise
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def get_player_id_by_name_and_team(full_name: str, team_id: int) -> int | None:
|
||
query = """
|
||
SELECT id
|
||
FROM players
|
||
WHERE team_id = %s
|
||
AND LOWER(full_name) = LOWER(%s)
|
||
LIMIT 1;
|
||
"""
|
||
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(query, (team_id, full_name.strip()))
|
||
row = cur.fetchone()
|
||
return row[0] if row else None
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def get_player_id_by_external_id(external_id: str) -> int | None:
|
||
query = """
|
||
SELECT id,
|
||
position
|
||
FROM players
|
||
WHERE external_id = %s
|
||
LIMIT 1;
|
||
"""
|
||
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(query, (str(external_id).strip(),))
|
||
row = cur.fetchone()
|
||
return row if row else None
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
|
||
def _split_full_name_for_autocreate(full_name: str) -> tuple[str, str]:
|
||
"""Аккуратно делит имя с сайта на first_name / last_name для новых записей."""
|
||
parts = [p.strip() for p in str(full_name or "").replace("\xa0", " ").split() if p.strip()]
|
||
if not parts:
|
||
return "", ""
|
||
if len(parts) == 1:
|
||
return "", parts[0]
|
||
|
||
# На сайте чаще приходит "Имя Фамилия". Полное имя всё равно сохраняем отдельно,
|
||
# поэтому даже при другом порядке данные можно быстро поправить в справочнике.
|
||
first_name = parts[0]
|
||
last_name = " ".join(parts[1:])
|
||
return first_name, last_name
|
||
|
||
|
||
def create_player_from_lineup(
|
||
team_id: int,
|
||
external_id: str = "",
|
||
full_name: str = "",
|
||
number: str = "",
|
||
position: str = "",
|
||
) -> tuple[int | None, str | None]:
|
||
"""Создаёт минимальную карточку игрока из протокола матча и возвращает (id, position).
|
||
|
||
Используется при загрузке составов с сайта, когда игрока ещё нет в справочнике.
|
||
"""
|
||
full_name = str(full_name or "").strip()
|
||
if not full_name:
|
||
return None, None
|
||
|
||
first_name, last_name = _split_full_name_for_autocreate(full_name)
|
||
external_id = str(external_id or "").strip()
|
||
number = str(number or "").strip()
|
||
position = str(position or "").strip()
|
||
|
||
query = """
|
||
INSERT INTO players (
|
||
external_id,
|
||
team_id,
|
||
full_name,
|
||
first_name,
|
||
last_name,
|
||
number,
|
||
position,
|
||
pos,
|
||
amplua,
|
||
is_active,
|
||
photo_enabled,
|
||
created_at,
|
||
updated_at
|
||
)
|
||
VALUES (
|
||
NULLIF(%s, ''), %s, %s, %s, %s, %s, %s, %s, %s, TRUE, FALSE,
|
||
NOW(), NOW()
|
||
)
|
||
ON CONFLICT (external_id)
|
||
DO UPDATE SET
|
||
team_id = EXCLUDED.team_id,
|
||
full_name = COALESCE(NULLIF(EXCLUDED.full_name, ''), players.full_name),
|
||
first_name = COALESCE(NULLIF(EXCLUDED.first_name, ''), players.first_name),
|
||
last_name = COALESCE(NULLIF(EXCLUDED.last_name, ''), players.last_name),
|
||
number = COALESCE(NULLIF(EXCLUDED.number, ''), players.number),
|
||
position = COALESCE(NULLIF(EXCLUDED.position, ''), players.position),
|
||
pos = COALESCE(NULLIF(EXCLUDED.pos, ''), players.pos),
|
||
amplua = COALESCE(NULLIF(EXCLUDED.amplua, ''), players.amplua),
|
||
is_active = TRUE,
|
||
updated_at = NOW()
|
||
RETURNING id, position;
|
||
"""
|
||
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(
|
||
query,
|
||
(
|
||
external_id,
|
||
team_id,
|
||
full_name,
|
||
first_name,
|
||
last_name,
|
||
number,
|
||
position,
|
||
position,
|
||
position,
|
||
),
|
||
)
|
||
row = cur.fetchone()
|
||
conn.commit()
|
||
return row if row else (None, None)
|
||
except Exception:
|
||
conn.rollback()
|
||
raise
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
PLAYER_ADMIN_SORT_COLUMNS = {
|
||
"id": "p.id",
|
||
"full_name": "p.full_name",
|
||
"first_name": "p.first_name",
|
||
"last_name": "p.last_name",
|
||
"external_id": "p.external_id",
|
||
"position": "p.position",
|
||
"team_name": "t.name",
|
||
"photo": "p.photo_enabled",
|
||
}
|
||
|
||
|
||
def ensure_player_photo_enabled_column() -> None:
|
||
"""Добавляет флаг использования фото игрока, если база ещё не обновлена."""
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(
|
||
"""
|
||
ALTER TABLE players
|
||
ADD COLUMN IF NOT EXISTS photo_enabled BOOLEAN NOT NULL DEFAULT FALSE;
|
||
"""
|
||
)
|
||
conn.commit()
|
||
except Exception:
|
||
conn.rollback()
|
||
raise
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def _normalize_player_sort(sort_by: str = "id", sort_dir: str = "desc") -> tuple[str, str, str, str]:
|
||
sort_by = (sort_by or "id").strip()
|
||
if sort_by not in PLAYER_ADMIN_SORT_COLUMNS:
|
||
sort_by = "id"
|
||
|
||
sort_dir = (sort_dir or "desc").strip().lower()
|
||
if sort_dir not in {"asc", "desc"}:
|
||
sort_dir = "desc"
|
||
|
||
return sort_by, sort_dir, PLAYER_ADMIN_SORT_COLUMNS[sort_by], sort_dir.upper()
|
||
|
||
|
||
def search_players_for_admin(q: str = "", sort_by: str = "id", sort_dir: str = "desc") -> list[dict]:
|
||
sort_by, sort_dir, order_column, order_direction = _normalize_player_sort(sort_by, sort_dir)
|
||
limit = 200
|
||
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
params: list = []
|
||
where_sql = ""
|
||
|
||
if q.strip():
|
||
pattern = f"%{q.strip()}%"
|
||
where_sql = """
|
||
WHERE
|
||
p.full_name ILIKE %s
|
||
OR p.first_name ILIKE %s
|
||
OR p.last_name ILIKE %s
|
||
OR COALESCE(p.external_id, '') ILIKE %s
|
||
OR COALESCE(t.name, '') ILIKE %s
|
||
"""
|
||
params.extend([pattern, pattern, pattern, pattern, pattern])
|
||
|
||
params.append(limit)
|
||
cur.execute(
|
||
f"""
|
||
SELECT
|
||
p.id,
|
||
p.full_name,
|
||
p.first_name,
|
||
p.last_name,
|
||
p.external_id,
|
||
p.position,
|
||
COALESCE(t.name, '') AS team_name,
|
||
COALESCE(p.photo_enabled, FALSE) AS photo_enabled
|
||
FROM players p
|
||
LEFT JOIN teams t ON t.id = p.team_id
|
||
{where_sql}
|
||
ORDER BY {order_column} {order_direction} NULLS LAST, p.id ASC
|
||
LIMIT %s
|
||
""",
|
||
tuple(params),
|
||
)
|
||
|
||
rows = cur.fetchall()
|
||
|
||
return [
|
||
{
|
||
"id": row[0],
|
||
"full_name": row[1] or "",
|
||
"first_name": row[2] or "",
|
||
"last_name": row[3] or "",
|
||
"external_id": row[4] or "",
|
||
"position": row[5] or "",
|
||
"team_name": row[6] or "",
|
||
"photo_enabled": bool(row[7]),
|
||
}
|
||
for row in rows
|
||
]
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def get_player_by_id(player_id: int) -> dict | None:
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(
|
||
"""
|
||
SELECT
|
||
p.id,
|
||
p.full_name,
|
||
p.first_name,
|
||
p.last_name,
|
||
p.external_id,
|
||
p.position,
|
||
p.born,
|
||
p.photo,
|
||
p.video,
|
||
COALESCE(t.name, '') AS team_name,
|
||
COALESCE(p.photo_enabled, FALSE) AS photo_enabled
|
||
FROM players p
|
||
LEFT JOIN teams t ON t.id = p.team_id
|
||
WHERE p.id = %s
|
||
LIMIT 1
|
||
""",
|
||
(player_id,),
|
||
)
|
||
row = cur.fetchone()
|
||
|
||
if not row:
|
||
return None
|
||
|
||
return {
|
||
"id": row[0],
|
||
"full_name": row[1] or "",
|
||
"first_name": row[2] or "",
|
||
"last_name": row[3] or "",
|
||
"external_id": row[4] or "",
|
||
"position": row[5] or "",
|
||
"birth_date": normalize_birth_date_for_input(row[6]),
|
||
"photo": row[7] or "",
|
||
"video": row[8] or "",
|
||
"team_name": row[9] or "",
|
||
"photo_enabled": bool(row[10]),
|
||
}
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def update_player_admin(
|
||
player_id: int,
|
||
full_name: str = "",
|
||
first_name: str = "",
|
||
last_name: str = "",
|
||
external_id: str = "",
|
||
position: str = "",
|
||
birth_date: str = "",
|
||
photo: str = "",
|
||
video: str = "",
|
||
photo_enabled: bool = False,
|
||
) -> None:
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(
|
||
"""
|
||
UPDATE players
|
||
SET
|
||
full_name = %s,
|
||
first_name = %s,
|
||
last_name = %s,
|
||
external_id = NULLIF(%s, ''),
|
||
position = %s,
|
||
born = NULLIF(%s, '')::date,
|
||
photo = NULLIF(%s, ''),
|
||
video = NULLIF(%s, ''),
|
||
photo_enabled = %s,
|
||
updated_at = NOW()
|
||
WHERE id = %s
|
||
""",
|
||
(
|
||
full_name.strip(),
|
||
first_name.strip(),
|
||
last_name.strip(),
|
||
external_id.strip(),
|
||
position.strip(),
|
||
birth_date.strip(),
|
||
photo.strip(),
|
||
video.strip(),
|
||
bool(photo_enabled),
|
||
player_id,
|
||
),
|
||
)
|
||
conn.commit()
|
||
except Exception:
|
||
conn.rollback()
|
||
raise
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def update_player_photo_enabled(player_id: int, photo_enabled: bool = False) -> None:
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(
|
||
"""
|
||
UPDATE players
|
||
SET
|
||
photo_enabled = %s,
|
||
updated_at = NOW()
|
||
WHERE id = %s
|
||
""",
|
||
(bool(photo_enabled), player_id),
|
||
)
|
||
conn.commit()
|
||
except Exception:
|
||
conn.rollback()
|
||
raise
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def create_player_admin(
|
||
team_id: int,
|
||
full_name: str,
|
||
first_name: str = "",
|
||
last_name: str = "",
|
||
external_id: str = "",
|
||
number: str = "",
|
||
position: str = "",
|
||
birth_date: str = "",
|
||
photo: str = "",
|
||
video: str = "",
|
||
height_cm: int | None = None,
|
||
weight_kg: int | None = None,
|
||
games: int = 0,
|
||
goals: int = 0,
|
||
penaltys: int = 0,
|
||
assists: int = 0,
|
||
yellows: int = 0,
|
||
reds: int = 0,
|
||
is_active: bool = True,
|
||
photo_enabled: bool = False,
|
||
) -> int:
|
||
"""Создаёт игрока вручную из административного раздела."""
|
||
query = """
|
||
INSERT INTO players (
|
||
external_id,
|
||
team_id,
|
||
full_name,
|
||
first_name,
|
||
last_name,
|
||
number,
|
||
position,
|
||
pos,
|
||
amplua,
|
||
born,
|
||
photo,
|
||
video,
|
||
height_cm,
|
||
weight_kg,
|
||
games,
|
||
goals,
|
||
penaltys,
|
||
assists,
|
||
yellows,
|
||
reds,
|
||
is_active,
|
||
photo_enabled,
|
||
created_at,
|
||
updated_at
|
||
)
|
||
VALUES (
|
||
NULLIF(%s, ''), %s, %s, %s, %s, NULLIF(%s, ''), %s, %s, %s,
|
||
NULLIF(%s, '')::date, NULLIF(%s, ''), NULLIF(%s, ''), %s, %s,
|
||
%s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW()
|
||
)
|
||
RETURNING id;
|
||
"""
|
||
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(
|
||
query,
|
||
(
|
||
external_id.strip(),
|
||
int(team_id),
|
||
full_name.strip(),
|
||
first_name.strip(),
|
||
last_name.strip(),
|
||
number.strip(),
|
||
position.strip(),
|
||
position.strip(),
|
||
position.strip(),
|
||
birth_date.strip(),
|
||
photo.strip(),
|
||
video.strip(),
|
||
height_cm,
|
||
weight_kg,
|
||
int(games),
|
||
int(goals),
|
||
int(penaltys),
|
||
int(assists),
|
||
int(yellows),
|
||
int(reds),
|
||
bool(is_active),
|
||
bool(photo_enabled),
|
||
),
|
||
)
|
||
row = cur.fetchone()
|
||
conn.commit()
|
||
return int(row[0])
|
||
except Exception:
|
||
conn.rollback()
|
||
raise
|
||
finally:
|
||
conn.close()
|