fc / src /repo.py
Chuiguohuang's picture
feat(ui): OVR 幂次曲线拉开分层(地板58) + 三档门槛重定 + 金银铜配色提亮
49944c9 verified
Raw History Blame Contribute Delete
23 kB
"""数据访问与聚合层。
对外只暴露「返回普通 dict / list」的函数,Shiny 层不直接碰 ORM 对象,
从而避免会话过期、跨线程等坑。聚合一律用 left-join 思路:
matches 为基准,缺失的 Stat 记录按「默认出勤、0 球 0 助攻」处理。
"""
from __future__ import annotations
from datetime import date
from typing import Any, Optional
from sqlalchemy import extract
from .db import get_session
from .models import Match, Stat, User, Vote
from .repo_base import _match_dict, _result, _season_clause, _season_match_ids
from .security import hash_password, verify_password
# ----------------------------------------------------------------------------- 赛季(按比赛年份)
def available_seasons() -> list[int]:
"""所有出现过比赛的年份(赛季),降序。"""
with get_session() as s:
years = s.query(extract("year", Match.match_date)).distinct().all()
return sorted({int(y[0]) for y in years if y[0] is not None}, reverse=True)
# ----------------------------------------------------------------------------- 认证 / 用户
def authenticate(username: str, password: str) -> Optional[dict[str, Any]]:
with get_session() as s:
u = s.query(User).filter_by(username=username).first()
if u and u.active and verify_password(password, u.password_hash):
return {
"id": u.id,
"username": u.username,
"display_name": u.display_name,
"is_admin": u.is_admin,
"is_goalkeeper": u.is_goalkeeper,
"jersey": u.jersey_number,
}
return None
def list_users(active_only: bool = False) -> list[dict[str, Any]]:
with get_session() as s:
q = s.query(User).order_by(User.is_admin.desc(), User.id)
if active_only:
q = q.filter_by(active=True)
return [
{
"id": u.id,
"username": u.username,
"display_name": u.display_name,
"jersey": u.jersey_number,
"is_admin": u.is_admin,
"is_player": u.is_player,
"is_goalkeeper": u.is_goalkeeper,
"active": u.active,
}
for u in q.all()
]
def add_user(
username: str,
display_name: str,
password: str,
jersey: Optional[int] = None,
is_admin: bool = False,
is_player: bool = True,
is_goalkeeper: bool = False,
) -> None:
username = (username or "").strip()
display_name = (display_name or "").strip()
if not username or not display_name or not password:
raise ValueError("用户名、昵称、密码均不能为空")
with get_session() as s:
if s.query(User).filter_by(username=username).first():
raise ValueError(f"用户名「{username}」已存在")
s.add(
User(
username=username,
display_name=display_name,
password_hash=hash_password(password),
jersey_number=jersey,
is_admin=bool(is_admin),
is_player=bool(is_player),
is_goalkeeper=bool(is_goalkeeper),
active=True,
)
)
def update_player_profile(
user_id: int, display_name: str, jersey: Optional[int]
) -> None:
"""管理员修改队员的显示名(昵称)与球衣号;不改登录名与密码。
display_name 为空则忽略改名(避免误清空);jersey 为 None 表示无球衣号。
"""
with get_session() as s:
u = s.get(User, user_id)
if u is None:
return
name = (display_name or "").strip()
if name:
u.display_name = name
u.jersey_number = jersey
def set_goalkeeper(user_id: int, is_goalkeeper: bool) -> None:
"""标记/取消某队员为门将(决定是否显示扑救录入、是否进扑救榜)。"""
with get_session() as s:
u = s.get(User, user_id)
if u:
u.is_goalkeeper = bool(is_goalkeeper)
def reset_password(user_id: int, new_password: str) -> None:
if not new_password:
raise ValueError("新密码不能为空")
with get_session() as s:
u = s.get(User, user_id)
if u:
u.password_hash = hash_password(new_password)
def change_password(user_id: int, old_password: str, new_password: str) -> bool:
"""队员自助改密:先校验旧密码,正确才更新。返回是否成功。"""
if not new_password:
raise ValueError("新密码不能为空")
with get_session() as s:
u = s.get(User, user_id)
if u is None or not verify_password(old_password, u.password_hash):
return False
u.password_hash = hash_password(new_password)
return True
# ----------------------------------------------------------------------------- 比赛
def list_matches(season: Optional[int] = None) -> list[dict[str, Any]]:
with get_session() as s:
q = s.query(Match)
cl = _season_clause(season)
if cl is not None:
q = q.filter(cl)
ms = q.order_by(Match.match_date.desc(), Match.id.desc()).all()
return [_match_dict(m) for m in ms]
def add_match(
match_date: date,
opponent: str,
home_away: str,
our: int,
opp: int,
note: str = "",
) -> None:
opponent = (opponent or "").strip()
if not opponent:
raise ValueError("对手名称不能为空")
with get_session() as s:
s.add(
Match(
match_date=match_date,
opponent=opponent,
home_away=home_away if home_away in ("home", "away") else "home",
our_score=int(our or 0),
opp_score=int(opp or 0),
note=(note or "").strip(),
)
)
def update_match(
match_id: int,
opponent: str,
home_away: str,
our: int,
opp: int,
) -> None:
with get_session() as s:
m = s.get(Match, match_id)
if not m:
return
m.opponent = (opponent or m.opponent).strip()
m.home_away = home_away if home_away in ("home", "away") else m.home_away
m.our_score = int(our or 0)
m.opp_score = int(opp or 0)
def delete_match(match_id: int) -> None:
with get_session() as s:
s.query(Stat).filter_by(match_id=match_id).delete()
s.query(Vote).filter_by(match_id=match_id).delete() # 该场投票一并清理
m = s.get(Match, match_id)
if m:
s.delete(m)
def clear_matches_and_stats() -> int:
"""清空所有比赛与个人数据(保留队员账号)。返回删除的比赛场数。"""
with get_session() as s:
n = s.query(Match).count()
s.query(Stat).delete()
s.query(Vote).delete() # 投票依附比赛,一并清空
s.query(Match).delete()
return n
# ----------------------------------------------------------------------------- 个人数据录入
def upsert_stat(
user_id: int,
match_id: int,
goals: Any,
assists: Any,
attended: Any,
saves: Any = 0,
) -> None:
with get_session() as s:
st = s.query(Stat).filter_by(user_id=user_id, match_id=match_id).one_or_none()
if st is None:
st = Stat(user_id=user_id, match_id=match_id)
s.add(st)
st.goals = max(0, int(goals or 0))
st.assists = max(0, int(assists or 0))
st.saves = max(0, int(saves or 0))
st.attended = bool(attended)
# ----------------------------------------------------------------------------- 聚合
def team_summary(season: Optional[int] = None) -> dict[str, Any]:
with get_session() as s:
q = s.query(Match)
cl = _season_clause(season)
if cl is not None:
q = q.filter(cl)
ms = q.all()
played = len(ms)
w = d = l = gf = ga = 0
for m in ms:
gf += m.our_score
ga += m.opp_score
r = _result(m.our_score, m.opp_score)
if r == "W":
w += 1
elif r == "D":
d += 1
else:
l += 1
return {
"played": played,
"wins": w,
"draws": d,
"losses": l,
"gf": gf,
"ga": ga,
"gd": gf - ga,
"points": w * 3 + d,
"win_rate": round(100 * w / played) if played else 0,
}
def player_aggregate(season: Optional[int] = None) -> list[dict[str, Any]]:
"""每名在役队员的累计:进球、助攻、出场(总场次 - 缺勤场次)。
仅统计 is_player=True 的在役账号;领队/教练不计入榜单。
"""
with get_session() as s:
users = (
s.query(User)
.filter_by(active=True, is_player=True)
.order_by(User.id)
.all()
)
match_ids = _season_match_ids(s, season)
total = len(match_ids)
stats = (
s.query(Stat).filter(Stat.match_id.in_(match_ids)).all() if match_ids else []
)
agg: dict[int, dict[str, int]] = {}
for st in stats:
a = agg.setdefault(st.user_id, {"g": 0, "a": 0, "sv": 0, "absent": 0})
a["g"] += st.goals or 0
a["a"] += st.assists or 0
a["sv"] += st.saves or 0
if not st.attended:
a["absent"] += 1
out: list[dict[str, Any]] = []
for u in users:
a = agg.get(u.id, {"g": 0, "a": 0, "sv": 0, "absent": 0})
out.append(
{
"user_id": u.id,
"name": u.display_name,
"jersey": u.jersey_number,
"is_admin": u.is_admin,
"is_goalkeeper": u.is_goalkeeper,
"goals": a["g"],
"assists": a["a"],
"saves": a["sv"],
"ga": a["g"] + a["a"],
"attended": total - a["absent"],
"total": total,
}
)
return out
def player_matchlog(user_id: int) -> list[dict[str, Any]]:
"""某队员逐场数据;无记录的场次按默认(出勤/0/0)返回。"""
with get_session() as s:
ms = s.query(Match).order_by(Match.match_date.desc(), Match.id.desc()).all()
smap = {st.match_id: st for st in s.query(Stat).filter_by(user_id=user_id).all()}
out: list[dict[str, Any]] = []
for m in ms:
st = smap.get(m.id)
row = _match_dict(m)
row.update(
{
"attended": st.attended if st else True,
"goals": st.goals if st else 0,
"assists": st.assists if st else 0,
"saves": st.saves if st else 0,
"has_record": st is not None,
}
)
out.append(row)
return out
def player_rank(user_id: int) -> Optional[dict[str, Any]]:
agg = player_aggregate()
me = next((x for x in agg if x["user_id"] == user_id), None)
if me is None:
return None
by_goals = sorted(agg, key=lambda x: (-x["goals"], -x["assists"]))
by_assists = sorted(agg, key=lambda x: (-x["assists"], -x["goals"]))
goal_rank = next(i for i, x in enumerate(by_goals, 1) if x["user_id"] == user_id)
assist_rank = next(i for i, x in enumerate(by_assists, 1) if x["user_id"] == user_id)
my_mvps = next(
(r["mvps"] for r in mvp_ranking() if r["user_id"] == user_id), 0
)
my_dmvps = next(
(r["dmvps"] for r in dmvp_ranking() if r["user_id"] == user_id), 0
)
return {
**me,
"goal_rank": goal_rank,
"assist_rank": assist_rank,
"n_players": len(agg),
"mvps": my_mvps,
"dmvps": my_dmvps,
}
def all_fifa_cards() -> list[dict[str, Any]]:
"""全队 FIFA 卡(按 OVR 降序):六维按「队内相对值」折算到 58–99。
维度:SHO 射术(进球) / PAS 传球(助攻) / STA 体能(出勤率) /
HON 荣誉(最佳球员MVP) / DEF 防守(防守MVP,门将加扑救) / IMP 影响(综合)。
换算:58 + (你的值/队内峰值)^0.65 × 41——幂次曲线让「有一点数据就明显上分」,
不再是只有队内第一名才有分;地板 58 避免全员挤在 50 一坨,分层更真实。
没数据=58,队内该项最高者=99,随数据实时成长。
OVR:六维加权(射术/荣誉/传球权重高)。一次聚合产全队卡,避免逐人重复查询。
"""
agg = player_aggregate()
if not agg:
return []
dmvp_all = {r["user_id"]: r["dmvps"] for r in dmvp_ranking()}
mvp_all = {r["user_id"]: r["mvps"] for r in mvp_ranking()}
def scale(mine: float, peak: float) -> int:
"""幂次相对值折算:地板 58,峰值 99,曲线 0.65 让中段更快抬头。"""
if peak <= 0 or mine <= 0:
return 58
return 58 + round(41 * (mine / peak) ** 0.65)
peak_g = max(r["goals"] for r in agg)
peak_a = max(r["assists"] for r in agg)
peak_sv = max(r["saves"] for r in agg)
peak_mvp = max(list(mvp_all.values()) + [0])
peak_dmvp = max(list(dmvp_all.values()) + [0])
peak_ga = max(r["ga"] for r in agg)
peak_att = max((r["attended"] for r in agg), default=0)
weights = {"SHO": .22, "PAS": .18, "STA": .12, "HON": .20, "DEF": .13, "IMP": .15}
cards: list[dict[str, Any]] = []
for r in agg:
uid = r["user_id"]
my_mvp, my_dmvp = mvp_all.get(uid, 0), dmvp_all.get(uid, 0)
# 体能:出勤场次相对队内最勤者(同曲线),全勤或队内最高即 99
# 防守分:防守 MVP 为主;门将再叠加扑救占比(取较高者)
def_score = scale(my_dmvp, peak_dmvp)
if r.get("is_goalkeeper"):
def_score = max(def_score, scale(r["saves"], peak_sv))
# 影响分:参与进球与两类 MVP 的综合占比
imp_raw = (r["ga"] / peak_ga if peak_ga else 0) * 0.6 + (
(my_mvp + my_dmvp) / (peak_mvp + peak_dmvp)
if (peak_mvp + peak_dmvp) else 0
)
stats = {
"SHO": scale(r["goals"], peak_g),
"PAS": scale(r["assists"], peak_a),
"STA": scale(r["attended"], peak_att),
"HON": scale(my_mvp, peak_mvp),
"DEF": def_score,
"IMP": scale(imp_raw, 1.0),
}
cards.append({
"user_id": uid,
"ovr": round(sum(stats[k] * w for k, w in weights.items())),
"pos": "GK" if r.get("is_goalkeeper") else "SDE",
"stats": stats,
"name": r["name"],
"jersey": r["jersey"],
# 背面真实数据(翻面展示 + 视图层生成寄语用)
"goals": r["goals"],
"assists": r["assists"],
"ga": r["ga"],
"saves": r["saves"],
"attended": r["attended"],
"total": r["total"],
"mvps": my_mvp,
"dmvps": my_dmvp,
"is_goalkeeper": r.get("is_goalkeeper", False),
})
return sorted(cards, key=lambda c: (-c["ovr"], c["name"]))
def player_fifa_card(user_id: int) -> Optional[dict[str, Any]]:
"""单人 FIFA 卡(口径与 all_fifa_cards 完全一致)。"""
return next((c for c in all_fifa_cards() if c["user_id"] == user_id), None)
# ----------------------------------------------------------------------------- MVP 投票
def my_votes(voter_id: int) -> dict[int, int]:
"""某人已投的票:{match_id: voted_for_id},用于预填投票界面。"""
with get_session() as s:
rows = s.query(Vote.match_id, Vote.voted_for_id).filter_by(voter_id=voter_id).all()
return {mid: vid for mid, vid in rows}
def set_vote(voter_id: int, match_id: int, voted_for_id: Optional[int]) -> None:
"""投 / 改 / 撤 某场 MVP 票;voted_for_id 为 None 表示撤票。
若该场已被管理员结算锁定(mvp_locked),则忽略任何改票(防止结算后被改)。
"""
with get_session() as s:
m = s.get(Match, match_id)
if m is not None and m.mvp_locked:
return
v = s.query(Vote).filter_by(voter_id=voter_id, match_id=match_id).one_or_none()
if voted_for_id is None:
if v is not None:
s.delete(v)
return
if v is None:
s.add(Vote(voter_id=voter_id, match_id=match_id, voted_for_id=voted_for_id))
else:
v.voted_for_id = voted_for_id
def mvp_ranking(season: Optional[int] = None) -> list[dict[str, Any]]:
"""当季 MVP 榜:按「获得当场 MVP 的次数」降序(含双 MVP,两人各计一次)。
仅统计已结算场次;投票只作结算参考,不直接上榜。
"""
with get_session() as s:
match_ids = _season_match_ids(s, season)
if not match_ids:
return []
rows = (
s.query(Match.mvp_user_id, Match.mvp2_user_id)
.filter(Match.id.in_(match_ids), Match.mvp_user_id.isnot(None))
.all()
)
users = {
u.id: (u.display_name, u.jersey_number) for u in s.query(User).all()
}
tally: dict[int, dict[str, Any]] = {}
for u1, u2 in rows:
for uid in (u1, u2):
if uid is None or uid not in users:
continue
name, jersey = users[uid]
t = tally.setdefault(
uid, {"user_id": uid, "name": name, "jersey": jersey, "mvps": 0}
)
t["mvps"] += 1
return sorted(tally.values(), key=lambda x: (-x["mvps"], x["name"]))
def dmvp_ranking(season: Optional[int] = None) -> list[dict[str, Any]]:
"""当季铁血防守 MVP 榜:按获得次数降序(每场由领队结算指定一人)。"""
with get_session() as s:
match_ids = _season_match_ids(s, season)
if not match_ids:
return []
rows = (
s.query(Match.dmvp_user_id, User.display_name, User.jersey_number)
.join(User, Match.dmvp_user_id == User.id)
.filter(Match.id.in_(match_ids), Match.dmvp_user_id.isnot(None))
.all()
)
tally: dict[int, dict[str, Any]] = {}
for uid, name, jersey in rows:
t = tally.setdefault(
uid, {"user_id": uid, "name": name, "jersey": jersey, "dmvps": 0}
)
t["dmvps"] += 1
return sorted(tally.values(), key=lambda x: (-x["dmvps"], x["name"]))
def settle_mvp(
match_id: int,
mvp_user_id: Optional[int],
mvp2_user_id: Optional[int] = None,
dmvp_user_id: Optional[int] = None,
) -> None:
"""管理员结算某场:最佳球员 MVP(可双)+ 铁血防守 MVP;全传 None=取消结算重开。
数据不变式:任一奖项有人=已结算锁票;最佳球员两栏同一人自动去重、第一栏空则
第二栏顶上;防守 MVP 独立评选,可与最佳球员同场同人。
"""
if mvp_user_id is None and mvp2_user_id is not None:
mvp_user_id, mvp2_user_id = mvp2_user_id, None
if mvp2_user_id == mvp_user_id:
mvp2_user_id = None
with get_session() as s:
m = s.get(Match, match_id)
if m is None:
return
m.mvp_user_id = mvp_user_id
m.mvp2_user_id = mvp2_user_id
m.dmvp_user_id = dmvp_user_id
m.mvp_locked = mvp_user_id is not None or dmvp_user_id is not None
def settled_mvps() -> dict[int, dict[str, Any]]:
"""已结算场次的奖项:{match_id: {"user_ids": [...], "names": [...], "dmvp_name": str|None}}。
names 为最佳球员 MVP(单/双);dmvp_name 为铁血防守 MVP(可空)。
"""
with get_session() as s:
rows = (
s.query(Match.id, Match.mvp_user_id, Match.mvp2_user_id, Match.dmvp_user_id)
.filter((Match.mvp_user_id.isnot(None)) | (Match.dmvp_user_id.isnot(None)))
.all()
)
names = {u.id: u.display_name for u in s.query(User).all()}
out: dict[int, dict[str, Any]] = {}
for mid, u1, u2, ud in rows:
ids = [u for u in (u1, u2) if u is not None]
out[mid] = {
"user_ids": ids,
"names": [names.get(u, "?") for u in ids],
"dmvp_name": names.get(ud) if ud is not None else None,
}
return out
def match_mvp_summary(match_ids: Optional[list[int]] = None) -> dict[int, dict[str, Any]]:
"""每场得票最高者(可并列):{match_id: {"names": [显示名,...], "votes": n}}。
仅含有票的场次;锁定与否都计算(锁定场即「已结算 MVP」,未锁定即「当前领先」)。
"""
with get_session() as s:
q = (
s.query(Vote.match_id, Vote.voted_for_id, User.display_name)
.join(User, Vote.voted_for_id == User.id)
)
if match_ids is not None:
if not match_ids:
return {}
q = q.filter(Vote.match_id.in_(match_ids))
rows = q.all()
tally: dict[int, dict[int, list[Any]]] = {}
for mid, uid, name in rows:
per = tally.setdefault(mid, {})
if uid not in per:
per[uid] = [name, 0]
per[uid][1] += 1
out: dict[int, dict[str, Any]] = {}
for mid, per in tally.items():
top = max(c for _, c in per.values())
names = [name for name, c in per.values() if c == top]
out[mid] = {"names": names, "votes": top}
return out
def match_contributions(season: Optional[int] = None) -> dict[int, dict[str, list[str]]]:
"""每场的进球/助攻明细:{match_id: {"goals": ["Leo×2", "Mark"], "assists": ["HWL"]}}。
仅含该场进球或助攻 > 0 的球员;同一人多球记为「名字×N」,按数量降序。
一次查询批量返回,供主页逐场展示,不影响既有聚合逻辑。
"""
g_tmp: dict[int, list[tuple[str, int]]] = {}
a_tmp: dict[int, list[tuple[str, int]]] = {}
with get_session() as s:
season_ids = set(_season_match_ids(s, season)) if season else None
rows = (
s.query(Stat.match_id, Stat.goals, Stat.assists, User.display_name)
.join(User, Stat.user_id == User.id)
.filter((Stat.goals > 0) | (Stat.assists > 0))
.all()
)
for mid, goals, assists, name in rows:
if season_ids is not None and mid not in season_ids:
continue
if goals and goals > 0:
g_tmp.setdefault(mid, []).append((name, goals))
if assists and assists > 0:
a_tmp.setdefault(mid, []).append((name, assists))
def _fmt(items: list[tuple[str, int]]) -> list[str]:
return [f"{n}×{c}" if c > 1 else n for n, c in sorted(items, key=lambda x: -x[1])]
out: dict[int, dict[str, list[str]]] = {}
for mid in set(g_tmp) | set(a_tmp):
out[mid] = {"goals": _fmt(g_tmp.get(mid, [])), "assists": _fmt(a_tmp.get(mid, []))}
return out