Spaces:
Sleeping
Sleeping
Download backend/v2/api.py from shaun07/grid-backtest: direct link, hf CLI and curl.
- Browser
- Download file 196 kB
-
https://huggingface.co/spaces/shaun07/grid-backtest/resolve/main/backend/v2/api.py
- Command line
-
hf download hf://spaces/shaun07/grid-backtest/backend/v2/api.py
-
curl -L -o api.py https://huggingface.co/spaces/shaun07/grid-backtest/resolve/main/backend/v2/api.py
196 kB
from __future__ import annotations
import json
import logging
import math
import io
import os
import shutil
import traceback
import ipaddress
import asyncio
logger = logging.getLogger(__name__)
from datetime import datetime, timedelta, timezone
import httpx
from fastapi import APIRouter, Body, File, Header, HTTPException, UploadFile, Form, Request
from fastapi.responses import FileResponse, Response, StreamingResponse
from .celery_app import celery_app
from .config import get_redis_url
from .db import init_db
from .repository import (
batch_delete_backtest_records,
create_task,
delete_user_backtests_for_admin,
delete_user_tasks_for_admin,
delete_backtest_record,
get_backtest_record,
get_history_statistics_summary,
get_usage_count,
get_user,
get_task,
get_task_file_path,
batch_delete_tasks,
delete_task,
list_user_tasks_for_admin,
list_tasks,
list_backtest_records,
list_users,
find_cached_download_source,
update_task,
delete_user_row,
verify_admin_token,
incr_admin_fail_and_maybe_ban,
reset_admin_fail_count,
set_user_ban,
set_user_quotas,
get_quota_defaults,
set_quota_defaults,
set_all_users_quotas,
upsert_user,
create_trade_upload,
bulk_insert_trade_records,
get_trade_statistics_summary,
save_holding_snapshots,
get_latest_holding_snapshots,
list_trade_holdings,
list_symbol_trade_records,
clear_user_trades,
list_trade_records_for_dedupe,
list_trade_records,
bulk_update_trade_records_extra_json,
get_symbol_meta_map,
upsert_symbol_meta_many,
get_symbol_price_map,
update_symbol_prices_many,
get_daily_close_map,
get_daily_close_max_dates,
get_daily_kline_in_range,
delete_daily_kline_in_range,
get_symbol_latest_close_from_daily,
get_symbol_latest_close_with_date_from_daily,
upsert_daily_close_many,
list_trade_symbol_min_max_dates,
compute_and_upsert_trade_daily_stats,
list_trade_daily_stats,
)
from .time_sync import get_time_sync_status, now_utc
router = APIRouter(prefix='/api/v2')
_redis_client = None
def _get_redis_client():
global _redis_client
if _redis_client is not None:
return _redis_client
try:
import redis
_redis_client = redis.Redis.from_url(get_redis_url(), decode_responses=True)
return _redis_client
except Exception:
_redis_client = False
return None
def _cache_get_json(key: str) -> dict | list | None:
r = _get_redis_client()
if not r:
return None
try:
raw = r.get(key)
if not raw:
return None
obj = json.loads(raw)
return obj
except Exception:
return None
def _cache_set_json(key: str, value: object, ttl_seconds: int) -> None:
r = _get_redis_client()
if not r:
return
try:
r.setex(key, int(ttl_seconds), json.dumps(value, ensure_ascii=False, default=str))
except Exception:
return
def _cache_delete(key: str) -> None:
r = _get_redis_client()
if not r:
return
try:
r.delete(key)
except Exception:
return
def _infer_device_os(user_agent: str | None) -> str | None:
ua = str(user_agent or '').lower()
if not ua:
return None
if 'iphone' in ua or 'ipad' in ua or 'ios' in ua:
return 'iPhone'
if 'android' in ua:
return 'Android'
if 'mac os x' in ua or 'macintosh' in ua:
return 'Mac'
if 'windows' in ua:
return 'Windows'
if 'linux' in ua:
return 'Linux'
return None
_ip2region_obj = None
_ip2region_db_path = None
def _lookup_province_by_ip(ip: str | None) -> str | None:
global _ip2region_obj, _ip2region_db_path
ip_s = str(ip or '').strip()
if not ip_s:
return None
db_path = str(os.getenv('WGJY_IP2REGION_DB_PATH') or '').strip()
if not db_path:
return None
try:
from ipregion import IP2Region
except Exception:
return None
try:
if _ip2region_obj is None or _ip2region_db_path != db_path:
kwargs = {}
try:
sig = inspect.signature(IP2Region)
for k in ('db_path', 'xdb_path', 'xdbfile', 'dbfile', 'path'):
if k in sig.parameters:
kwargs[k] = db_path
break
except Exception:
kwargs = {}
_ip2region_obj = IP2Region(**kwargs) if kwargs else IP2Region()
_ip2region_db_path = db_path
region = _ip2region_obj.search(ip_s)
if isinstance(region, dict):
prov = region.get('province') or region.get('prov')
if prov:
return str(prov)
raw = region.get('region') or region.get('data')
if raw:
region = raw
if isinstance(region, str):
parts = [p.strip() for p in region.split('|')]
if len(parts) >= 3:
prov = parts[2]
if prov and prov != '0':
return prov
return None
except Exception:
return None
def _get_client_ip(request: Request) -> str | None:
def _normalize(ip: str | None) -> str | None:
s = str(ip or '').strip()
if not s:
return None
if s.startswith('::ffff:'):
s = s.replace('::ffff:', '', 1)
return s
def _is_public(ip: str | None) -> bool:
s = _normalize(ip)
if not s:
return False
try:
obj = ipaddress.ip_address(s)
return not (obj.is_private or obj.is_loopback or obj.is_link_local or obj.is_multicast or obj.is_reserved)
except Exception:
return False
# Client-reported public IP (e.g. fetched by frontend from ipify), only trust if it looks public.
xpub = request.headers.get('x-public-ip') or request.headers.get('X-Public-IP')
xpub_ip = _normalize(xpub)
if _is_public(xpub_ip):
return xpub_ip
xff = request.headers.get('x-forwarded-for') or request.headers.get('X-Forwarded-For')
if xff:
parts = [p.strip() for p in str(xff).split(',') if p.strip()]
normalized = [_normalize(p) for p in parts]
normalized = [p for p in normalized if p]
# Prefer the first public IP in XFF chain; otherwise fall back to the first IP.
for ip in normalized:
if _is_public(ip):
return ip
if normalized:
return normalized[0]
xri = request.headers.get('x-real-ip') or request.headers.get('X-Real-IP')
if xri:
ip = _normalize(xri)
if ip:
return ip
ccip = request.headers.get('cf-connecting-ip') or request.headers.get('CF-Connecting-IP')
if ccip:
ip = _normalize(ccip)
if ip:
return ip
try:
if request.client:
ip = _normalize(request.client.host)
if ip:
return ip
except Exception:
return None
return None
def _track_user_if_any(
*,
request: Request,
user_id: str | None,
network_type: str | None = None,
) -> None:
if not user_id:
return
ua = request.headers.get('user-agent')
device_os = _infer_device_os(ua)
ip = _get_client_ip(request)
province = _lookup_province_by_ip(ip)
net = str(network_type or '').strip() or None
defaults = None
try:
defaults = get_quota_defaults()
except Exception:
defaults = None
default_download_quota_mode = str((defaults or {}).get('download_quota_mode') or 'daily')
default_download_quota_limit = int((defaults or {}).get('download_quota_limit') or 0)
default_backtest_quota_mode = str((defaults or {}).get('backtest_quota_mode') or 'daily')
default_backtest_quota_limit = int((defaults or {}).get('backtest_quota_limit') or 0)
try:
upsert_user(
user_id=user_id,
device_os=device_os,
user_agent=ua,
ip=ip,
province=province,
network_type=net,
default_download_quota_mode=default_download_quota_mode,
default_download_quota_limit=default_download_quota_limit,
default_backtest_quota_mode=default_backtest_quota_mode,
default_backtest_quota_limit=default_backtest_quota_limit,
)
except Exception:
return
def _ensure_quota_or_raise(*, user_id: str | None, kind: str, needed: int = 1) -> None:
if not user_id:
return
u = get_user(user_id)
if not u:
return
if kind == 'download':
mode = str(u.get('download_quota_mode') or 'daily')
limit = int(u.get('download_quota_limit') or 0)
else:
mode = str(u.get('backtest_quota_mode') or 'daily')
limit = int(u.get('backtest_quota_limit') or 0)
if limit <= 0:
return
used = get_usage_count(user_id=user_id, kind=kind, mode=mode)
if used + int(needed) > limit:
raise HTTPException(
status_code=429,
detail=f"{kind} 次数已用尽(模式={mode},限制={limit},已用={used})",
)
def _require_admin(*, request: Request, x_admin_token: str | None) -> None:
# All admin operations are now unrestricted.
pass
def _trade_upload_id() -> str:
return f"trd_{datetime.now().strftime('%Y%m%d_%H%M%S')}_{os.getpid()}_{int(datetime.now().timestamp() * 1000) % 100000}"
def _normalize_side(v: str) -> str:
s = str(v or '').strip().lower()
if s in ('buy', 'b', '1', '买', '买入'):
return 'buy'
if s in ('sell', 's', '-1', '卖', '卖出'):
return 'sell'
if '买' in s or '购' in s or '申购' in s:
return 'buy'
if '卖' in s or '赎' in s or '赎回' in s:
return 'sell'
return s or 'unknown'
def _is_missing_value(v: object) -> bool:
if v is None:
return True
try:
if isinstance(v, float) and math.isnan(v):
return True
except Exception:
pass
try:
s = str(v).strip()
if not s:
return True
if s.lower() in ('nan', 'none', 'null'):
return True
except Exception:
return True
return False
def _to_jsonable(v: object):
if v is None:
return None
if isinstance(v, (str, int, bool)):
return v
if isinstance(v, float):
if math.isnan(v) or math.isinf(v):
return None
return v
if isinstance(v, datetime):
try:
return v.isoformat()
except Exception:
return str(v)
try:
import numpy as np
if isinstance(v, (np.integer,)):
return int(v)
if isinstance(v, (np.floating,)):
f = float(v)
if math.isnan(f) or math.isinf(f):
return None
return f
if isinstance(v, (np.bool_,)):
return bool(v)
except Exception:
pass
try:
import pandas as pd
if isinstance(v, (pd.Timestamp,)):
try:
return v.to_pydatetime().isoformat()
except Exception:
return str(v)
if pd.isna(v):
return None
except Exception:
pass
if isinstance(v, dict):
return {str(k): _to_jsonable(val) for k, val in v.items()}
if isinstance(v, (list, tuple, set)):
return [_to_jsonable(x) for x in v]
try:
return str(v)
except Exception:
return None
def _normalize_code(value: object) -> str:
if value is None:
return ''
if isinstance(value, (int,)):
s = str(value)
elif isinstance(value, float):
try:
if math.isnan(value) or math.isinf(value):
return ''
except Exception:
pass
try:
if float(value).is_integer():
s = str(int(value))
else:
s = str(value)
except Exception:
s = str(value)
else:
s = str(value)
s = s.strip()
up = (
s.upper()
.replace('SH.', '')
.replace('SZ.', '')
.replace('.', '.')
.replace('-', '-')
.replace('—', '-')
.replace('–', '-')
.replace('R', 'R')
)
if up.startswith(('GC', 'R')):
digits2 = ''.join(ch for ch in up if ch.isdigit())
if len(digits2) == 3:
return f"204{digits2}"
if len(digits2) >= 6:
return digits2[:6]
if s.endswith('.0') and s.replace('.', '', 1).isdigit():
s = s[:-2]
if 'e+' in s.lower() or 'e-' in s.lower():
try:
s = str(int(float(s)))
except Exception:
pass
s = s.strip()
if s.isdigit() and len(s) < 6:
s = s.zfill(6)
return s
def _normalize_id(value: object) -> str | None:
if value is None:
return None
s = _normalize_code(value)
return s or None
def _fmt_num_for_fp(v: object, nd: int = 6) -> str:
if v is None:
return ''
try:
f = float(v)
if not math.isfinite(f):
return ''
return f"{f:.{int(nd)}f}"
except Exception:
return str(v)
def _trade_fp(d: dict) -> str:
"""
计算一条交易记录的“逻辑指纹”,用于跨文件/多次上传的去重与覆盖判断。
设计原则:
- 不再依赖成交编号/委托编号作为指纹的一部分(不同报表中同一成交的编号可能不同);
- 通过「代码 + 方向 + 日期时间 + 价格 + 数量 + 成交金额」来唯一刻画一笔成交;
- 适当加入证券名称,进一步降低同一时刻多笔成交“撞车”的概率。
"""
sym = _normalize_code(d.get('symbol_code'))
side_raw = str(d.get('side') or '').strip()
side_norm = side_raw if sym == 'CASH' else _normalize_side(side_raw)
parts = [
sym,
str(side_norm or '').strip().lower(),
_coerce_date_str(d.get('trade_date')),
_coerce_time_str(d.get('trade_time')),
str(d.get('symbol_name') or '').strip(), # 名称放在后面,增强区分度
_fmt_num_for_fp(d.get('trade_price'), 6),
str(d.get('trade_qty') or ''),
_fmt_num_for_fp(d.get('trade_amount'), 2),
]
return '|'.join(parts)
def _dedupe_preview_rows(*, user_id: str, rows: list[dict]) -> tuple[list[dict], list[dict], int]:
if not rows:
return [], [], 0, []
codes = sorted({str(r.get('symbol_code') or '').strip() for r in rows if str(r.get('symbol_code') or '').strip()})
dates = [str(r.get('trade_date') or '').strip() for r in rows if str(r.get('trade_date') or '').strip()]
date_start = min(dates) if dates else None
date_end = max(dates) if dates else None
hist = list_trade_records_for_dedupe(user_id=user_id, symbol_codes=codes, date_start=date_start, date_end=date_end)
def _is_settlement_row(r0: dict | None) -> bool:
"""
判断一条「上传记录」是否可以视为交割单:
- 新版逻辑:只要带有剩余数量或任一费用/清算类字段,即认为是交割单(信息更完整,应覆盖其他来源);
- 兼容旧版:仍然兼容 extra_json 中的 `_parsed_*` 字段。
"""
if not isinstance(r0, dict):
return False
# 1) 先看规范化字段本身(适用于当前实现)
fee_keys = [
'total_fee',
'stamp_duty',
'net_commission',
'transfer_fee',
'clearing_fee',
'settle_amount',
'balance',
'trade_regulation_fee',
'handling_fee',
'securities_regulation_fee',
'financial_levy',
'other_fee',
]
if r0.get('remaining_qty') not in (None, '', 0):
return True
for k in fee_keys:
v = r0.get(k)
if v not in (None, ''):
try:
if float(v) != 0:
return True
except Exception:
# 只要有非空字符串也视为有意义
return True
# 2) 兼容旧版本:查看 extra_json 中是否有解析出的费用字段
ex = r0.get('extra_json')
if not isinstance(ex, dict):
return False
parsed_keys = [
'_parsed_total_fee',
'_parsed_stamp_duty',
'_parsed_net_commission',
'_parsed_transfer_fee',
'_parsed_clearing_fee',
'_parsed_settle_amount',
]
for k in parsed_keys:
if k in ex and ex.get(k) is not None and ex.get(k) != '':
return True
return False
hist_map: dict[str, dict] = {}
for h in hist:
fp = _trade_fp({
'symbol_code': _normalize_code(h.get('symbol_code')),
'symbol_name': h.get('symbol_name'),
'side': str(h.get('side') or ''),
'trade_date': _coerce_date_str(h.get('trade_date')),
'trade_time': _coerce_time_str(h.get('trade_time')),
'trade_price': h.get('trade_price'),
'trade_qty': h.get('trade_qty'),
'trade_amount': h.get('trade_amount'),
})
if fp:
hist_map[fp] = h
preview: list[dict] = []
to_insert: list[dict] = []
to_update: list[dict] = []
skipped = 0
def _conflict_fields(new_r: dict, old_r: dict) -> list[str]:
out: list[str] = []
try:
if str(new_r.get('symbol_code') or '').strip() != str(old_r.get('symbol_code') or '').strip():
out.append('证券代码')
except Exception:
pass
try:
if str(new_r.get('symbol_name') or '').strip() != str(old_r.get('symbol_name') or '').strip():
out.append('证券名称')
except Exception:
pass
try:
if int(new_r.get('trade_qty') or 0) != int(old_r.get('trade_qty') or 0):
out.append('成交数量')
except Exception:
pass
try:
a = float(new_r.get('trade_amount') or 0)
b = float(old_r.get('trade_amount') or 0)
if math.isfinite(a) and math.isfinite(b) and abs(a - b) > 0.01:
out.append('成交金额')
except Exception:
pass
try:
if str(new_r.get('deal_id') or '').strip() and str(old_r.get('deal_id') or '').strip():
if str(new_r.get('deal_id') or '').strip() != str(old_r.get('deal_id') or '').strip():
out.append('成交编号')
except Exception:
pass
return out
for r in rows:
fp = _trade_fp(r)
# 不再使用成交编号/委托编号做跨表冲突判断,避免不同报表之间复用编号导致误判
ident_hit = None
fp_hit = hist_map.get(fp) if fp else None
action = 'insert'
conflict = []
is_dup = False
if action != 'conflict' and fp_hit is not None:
is_dup = True
new_is_settle = _is_settlement_row(r)
if new_is_settle:
# 交割单允许覆盖成交/其他;交割单覆盖交割单(以新数据为准)
action = 'override'
else:
# 非交割单永不覆盖
action = 'skip'
if action == 'insert':
to_insert.append(r)
elif action == 'override':
skipped += 1
if fp_hit is not None:
old_extra = fp_hit.get('extra_json') if isinstance(fp_hit.get('extra_json'), dict) else {}
new_extra = r.get('extra_json') if isinstance(r.get('extra_json'), dict) else {}
merged = dict(old_extra or {})
for k, v in (new_extra or {}).items():
if k.startswith('_parsed_'):
if v is not None:
merged[k] = v
else:
merged[k] = v
# 交割单覆盖时,同步更新主表字段,确保剩余数量与费用等信息以交割单为准
update_item: dict = {
'id': fp_hit.get('id'),
'symbol_name': r.get('symbol_name'),
'extra_json': merged,
}
for key in [
'trade_date',
'trade_time',
'trade_ts',
'order_price',
'order_qty',
'order_id',
'deal_id',
'trade_price',
'trade_qty',
'trade_amount',
'remaining_qty',
'total_fee',
'settle_amount',
'balance',
'net_commission',
'stamp_duty',
'transfer_fee',
'clearing_fee',
'trade_regulation_fee',
'handling_fee',
'securities_regulation_fee',
'financial_levy',
'other_fee',
'currency',
]:
if key in r and r.get(key) is not None:
update_item[key] = r.get(key)
to_update.append(update_item)
elif action == 'skip':
skipped += 1
elif action == 'conflict':
# 冲突也作为跳过计数,但会在非 dry_run 时直接报错
skipped += 1
preview.append({
'symbol_code': r.get('symbol_code'),
'symbol_name': r.get('symbol_name'),
'side': r.get('side'),
'trade_date': r.get('trade_date'),
'trade_time': r.get('trade_time'),
'trade_price': r.get('trade_price'),
'trade_qty': r.get('trade_qty'),
'trade_amount': r.get('trade_amount'),
'order_id': r.get('order_id'),
'is_duplicate': bool(is_dup),
'action': action,
'conflict_fields': conflict,
})
# 如果存在冲突,直接抛出异常(dry_run 阶段仍返回 preview 供查看)
return preview, to_insert, int(skipped), to_update
def _parse_trade_rows_from_file(*, content: bytes, filename: str) -> tuple[list[dict], list[str]]:
"""
从上传的二进制内容中解析成交记录。
兼容性策略(针对部分券商的 .xls/.xlsx 导出做了增强):
- JSON:按照 data/records/数组 解析;
- CSV:多编码尝试;
- XLS/XLSX:
- 先根据文件头判断是 OOXML(ZIP)还是 OLE;
- 优先尝试 openpyxl,其次 xlrd;
- 如均失败则退回 CSV 解析(很多券商把 CSV/HTML 伪装成 .xls)。
"""
name = str(filename or '').lower().strip()
# 1) JSON 直接按文本解析
if name.endswith('.json'):
try:
obj = json.loads(content.decode('utf-8'))
except Exception:
obj = json.loads(content.decode('utf-8-sig'))
if isinstance(obj, dict) and isinstance(obj.get('data'), list):
rows = obj.get('data')
elif isinstance(obj, dict) and isinstance(obj.get('records'), list):
rows = obj.get('records')
elif isinstance(obj, list):
rows = obj
else:
raise ValueError('JSON 格式不正确:需要数组或包含 data/records 数组')
if not isinstance(rows, list):
raise ValueError('JSON 数据不是数组')
cols = sorted({str(k) for r in rows if isinstance(r, dict) for k in r.keys()})
return [r for r in rows if isinstance(r, dict)], cols
import pandas as pd
buf = content
head = buf[:8] if isinstance(buf, (bytes, bytearray)) else b''
is_zip = head.startswith(b'PK\x03\x04')
is_ole = head.startswith(b'\xD0\xCF\x11\xE0\xA1\xB1\x1A\xE1')
def _try_read_csv() -> pd.DataFrame:
"""尽可能宽松地按 CSV 解析(多编码尝试)"""
last_err: Exception | None = None
for enc in ('utf-8', 'utf-8-sig', 'gbk', 'gb18030'):
try:
return pd.read_csv(io.BytesIO(buf), encoding=enc, sep=None, engine='python')
except Exception as e:
last_err = e
raise last_err or ValueError('无法按 CSV 解析')
def _try_read_excel() -> pd.DataFrame:
"""
宽松的 Excel 解析:
- 优先 openpyxl(支持现代 xlsx,也能解析部分伪装扩展名)
- 再尝试 xlrd(老式 xls)
- 最后退回默认 engine 让 pandas 自行判断
- 多 sheet 文件:优先找「交易记录」sheet,再找第一个有数据的 sheet
"""
# 先找交易记录 sheet
preferred_sheets = ['交易记录', '成交记录', '历史成交', '买卖记录', '当日成交']
for engine in ('openpyxl', 'xlrd'):
try:
# 读取所有 sheet 名称
xl = pd.ExcelFile(io.BytesIO(buf), engine=engine)
all_sheets = xl.sheet_names
# 优先找交易相关 sheet
target_sheet = None
for pref in preferred_sheets:
for s in all_sheets:
if pref in s:
target_sheet = s
break
if target_sheet:
break
# 没找到就选第一个
if not target_sheet:
target_sheet = all_sheets[0]
return pd.read_excel(xl, sheet_name=target_sheet)
except Exception:
pass
# 最后回退
last_err: Exception | None = None
for engine in ('openpyxl', 'xlrd', None):
try:
if engine:
return pd.read_excel(io.BytesIO(buf), engine=engine)
return pd.read_excel(io.BytesIO(buf))
except Exception as e:
last_err = e
raise last_err or ValueError('无法按 Excel 解析')
# 2) 根据扩展名和文件头选择解析策略
if name.endswith('.csv'):
df = _try_read_csv()
elif name.endswith(('.xls', '.xlsx')):
# 许多券商导出会把 CSV/HTML 伪装成 xls;这里按内容判断并在 CSV/Excel 之间智能回退。
if is_zip or is_ole:
# 看起来更像真正的 Excel 文件,优先走 Excel 解析,失败再退回 CSV。
try:
df = _try_read_excel()
except Exception:
df = _try_read_csv()
else:
# 内容不像标准 Excel,很可能是 CSV/HTML 伪装成 .xls
try:
df = _try_read_csv()
except Exception:
df = _try_read_excel()
else:
# 其他未知扩展名,直接按 Excel 解析,失败再回退 CSV
try:
df = _try_read_excel()
except Exception:
df = _try_read_csv()
# 列名标准化:处理部分券商导出为 "='交收日期'" 这类带公式前缀/引号的形式
def _normalize_col_name(col: object) -> str:
s = str(col or '').strip()
# 去掉可能的前缀 "='xxx'" 或 "=\"xxx\""
if s.startswith("='") and s.endswith("'") and len(s) > 3:
s = s[2:-1].strip()
elif s.startswith('="') and s.endswith('"') and len(s) > 3:
s = s[2:-1].strip()
# 去掉单独包裹的引号
if (s.startswith("'") and s.endswith("'")) or (s.startswith('"') and s.endswith('"')):
if len(s) > 2:
s = s[1:-1].strip()
return s
df.columns = [_normalize_col_name(c) for c in df.columns]
cols = list(df.columns)
rows = df.to_dict(orient='records')
return rows, cols
def _parse_all_holding_sheets(*, content: bytes, filename: str) -> tuple[list[dict], str | None]:
"""
从上传文件中解析所有持仓相关 sheet(包括「已清仓」sheet)。
返回合并后的数据和日期。
"""
name = str(filename or '').lower().strip()
import pandas as pd
buf = content
head = buf[:8] if isinstance(buf, (bytes, bytearray)) else b''
is_zip = head[:2] == b'PK'
is_ole = head[:8] == b'\xd0\xcf\x11\xe0\xa1\xb1\x1a\xe1'
if not (name.endswith(('.xls', '.xlsx')) or is_zip or is_ole):
return [], None
def _normalize_col_name(col: object) -> str:
s = str(col or '').strip()
if s.startswith('="') and s.endswith('"') and len(s) > 2:
s = s[2:-1].strip()
elif s.startswith("='") and s.endswith("'") and len(s) > 2:
s = s[2:-1].strip()
if (s.startswith("'") and s.endswith("'")) or (s.startswith('"') and s.endswith('"')):
if len(s) > 2:
s = s[1:-1].strip()
return s
def _parse_single_sheet(df, sheet_name: str) -> list[dict]:
"""解析单个 sheet 并返回数据行"""
df.columns = [_normalize_col_name(c) for c in df.columns]
cols = list(df.columns)
def g(*keys):
for k in keys:
if k in cols:
return k
return None
code_col = g('代码', '证券代码', '股票代码', 'code', 'symbol')
name_col = g('名称', '证券名称', '股票名称', 'name')
qty_col = g('持有数量', '持仓数量', '持股数', '数量', 'qty', 'net_qty')
cost_col = g('单位成本', '成本价', 'avg_cost', 'cost_price')
latest_col = g('最新价', 'price', 'close', 'latest_price')
amount_col = g('持有金额', 'holding_amount', 'market_value', 'holding_market_value')
pnl_col = g('持有盈亏', '累计盈亏', '总盈亏', 'holding_pnl', 'total_pnl', 'total_profit')
unreal_col = g('当日盈亏', '浮动盈亏', 'unrealized_pnl', '浮亏', 'unrealized')
realized_col = g('已实现盈亏', 'realized_pnl', 'realized')
pct_col = g('持有盈亏率', '盈亏率', 'pct_change', '涨跌幅', 'pct')
if not code_col:
return []
def _pf(v):
if v is None: return None
try:
f = float(str(v).strip())
return f if math.isfinite(f) else None
except: return None
def _pi(v):
f = _pf(v)
return int(f) if f is not None else None
rows = []
for rec in df.to_dict(orient='records'):
if not isinstance(rec, dict): continue
raw_sym = _strip_eq_prefix(rec.get(code_col, ''))
if not raw_sym or str(raw_sym).strip() in ('', 'nan', 'None'): continue
sym_str = str(raw_sym).strip()
# 过滤汇总行等非代码行(代码都是纯数字)
if not (sym_str.isdigit() and len(sym_str) <= 9):
continue
if sym_str.isdigit() and len(sym_str) <= 6:
sym_str = sym_str.zfill(6)
sym_str = sym_str.upper()
holding_market_value = _pf(rec.get(amount_col)) if amount_col else None
total_pnl = _pf(rec.get(pnl_col)) if pnl_col else None
unrealized_pnl = _pf(rec.get(unreal_col)) if unreal_col else None
realized_pnl = _pf(rec.get(realized_col)) if realized_col else None
latest_price = _pf(rec.get(latest_col)) if latest_col else None
cost_price = _pf(rec.get(cost_col)) if cost_col else None
qty = _pi(rec.get(qty_col)) if qty_col else None
# 兜底:最新价×数量算市值
if holding_market_value is None and latest_price and qty:
holding_market_value = round(latest_price * qty, 2)
# 兜底:(最新价-成本价)×数量算浮动盈亏
if unrealized_pnl is None and latest_price and cost_price and qty:
unrealized_pnl = round((latest_price - cost_price) * qty, 2)
rows.append({
'snapshot_date': '',
'symbol_code': sym_str,
'symbol_name': str(_strip_eq_prefix(rec.get(name_col)) or '').strip(),
'net_qty': qty,
'avg_cost': cost_price,
'holding_market_value': holding_market_value,
'total_pnl': total_pnl,
'unrealized_pnl': unrealized_pnl,
'realized_pnl': realized_pnl,
'current_price': latest_price,
'pct_change': _pf(rec.get(pct_col)) if pct_col else None,
'extra_json': {_normalize_col_name(k): _strip_eq_prefix(v) for k, v in rec.items()},
'sheet_name': sheet_name, # 记录来源 sheet
})
return rows
# 从文件元数据取日期作为快照日期兜底
snapshot_date = ''
try:
import zipfile, re as _re
with zipfile.ZipFile(io.BytesIO(buf)) as z:
xml = z.read('docProps/core.xml').decode()
m = _re.search(r'(\d{4})-(\d{2})-(\d{2})T', xml)
if m:
snapshot_date = f"{m.group(1)}-{m.group(2)}-{m.group(3)}"
except Exception:
pass
all_rows = []
# 遍历所有引擎尝试读取文件
for engine in ('openpyxl', 'xlrd'):
try:
xl = pd.ExcelFile(io.BytesIO(buf), engine=engine)
all_sheets = xl.sheet_names
# 解析「已清仓」sheet
for s in all_sheets:
if '已清仓' in s or '清仓' in s:
try:
df = pd.read_excel(io.BytesIO(buf), engine=engine, sheet_name=s, header=0)
rows = _parse_single_sheet(df, s)
all_rows.extend(rows)
except Exception as e:
logger.warning(f"解析「已清仓」sheet 失败: {e}")
# 解析持仓相关 sheet
preferred = ['持仓数据', '持仓', '当前持仓', '持仓明细']
for pref in preferred:
for s in all_sheets:
if pref in s and not ('已清仓' in s or '清仓' in s):
try:
df = pd.read_excel(io.BytesIO(buf), engine=engine, sheet_name=s, header=0)
rows = _parse_single_sheet(df, s)
all_rows.extend(rows)
except Exception as e:
logger.warning(f"解析「{s}」sheet 失败: {e}")
break # 成功解析,退出引擎循环
except Exception:
continue
return all_rows, snapshot_date
def _parse_holding_sheet(*, content: bytes, filename: str) -> tuple[list[dict], str | None]:
"""
从上传文件中解析「持仓数据」sheet。
支持同花顺「持仓数据」sheet 的实际字段:
代码, 名称, 持有金额, 当日盈亏, 当日盈亏率, 持有盈亏, 持有盈亏率,
累计盈亏, 累计盈亏率, 本周盈亏, 本月盈亏, 今年盈亏, 仓位占比,
持有数量, 持仓天数, 最新涨幅, 最新价, 单位成本, 回本涨幅...
"""
name = str(filename or '').lower().strip()
import pandas as pd
buf = content
head = buf[:8] if isinstance(buf, (bytes, bytearray)) else b''
is_zip = head[:2] == b'PK'
is_ole = head[:8] == b'\xd0\xcf\x11\xe0\xa1\xb1\x1a\xe1'
if not (name.endswith(('.xls', '.xlsx')) or is_zip or is_ole):
return [], None
def _normalize_col_name(col: object) -> str:
s = str(col or '').strip()
if s.startswith('="') and s.endswith('"') and len(s) > 2:
s = s[2:-1].strip()
elif s.startswith("='") and s.endswith("'") and len(s) > 2:
s = s[2:-1].strip()
if (s.startswith("'") and s.endswith("'")) or (s.startswith('"') and s.endswith('"')):
if len(s) > 2:
s = s[1:-1].strip()
return s
def _find_sheet():
for engine in ('openpyxl', 'xlrd'):
try:
xl = pd.ExcelFile(io.BytesIO(buf), engine=engine)
all_sheets = xl.sheet_names
# 优先查找「已清仓」sheet
for s in all_sheets:
if '已清仓' in s or '清仓' in s:
df = pd.read_excel(io.BytesIO(buf), engine=engine, sheet_name=s, header=0)
return df, _strip_eq_prefix(s)
# 如果没有「已清仓」sheet,则查找持仓相关 sheet
preferred = ['持仓数据', '持仓', '当前持仓', '持仓明细']
for pref in preferred:
for s in all_sheets:
if pref in s:
df = pd.read_excel(io.BytesIO(buf), engine=engine, sheet_name=s, header=0)
return df, _strip_eq_prefix(s)
return None, None
except Exception:
continue
return None, None
df, sheet_name = _find_sheet()
if df is None:
return [], None
df.columns = [_normalize_col_name(c) for c in df.columns]
cols = list(df.columns)
def g(*keys):
for k in keys:
if k in cols:
return k
return None
code_col = g('代码', '证券代码', '股票代码', 'code', 'symbol')
name_col = g('名称', '证券名称', '股票名称', 'name')
qty_col = g('持有数量', '持仓数量', '持股数', '数量', 'qty', 'net_qty')
cost_col = g('单位成本', '成本价', 'avg_cost', 'cost_price')
latest_col = g('最新价', 'price', 'close', 'latest_price')
amount_col = g('持有金额', 'holding_amount', 'market_value', 'holding_market_value')
pnl_col = g('持有盈亏', '累计盈亏', 'holding_pnl', 'total_pnl', 'total_profit')
unreal_col = g('当日盈亏', '浮动盈亏', 'unrealized_pnl', '浮亏', 'unrealized')
realized_col = g('已实现盈亏', 'realized_pnl', 'realized')
pct_col = g('持有盈亏率', '盈亏率', 'pct_change', '涨跌幅', 'pct')
if not code_col:
return [], None
def _pf(v):
if v is None: return None
try:
f = float(str(v).strip())
return f if math.isfinite(f) else None
except: return None
def _pi(v):
f = _pf(v)
return int(f) if f is not None else None
# 从文件元数据取日期作为快照日期兜底
snapshot_date = ''
try:
import zipfile, re as _re
with zipfile.ZipFile(io.BytesIO(buf)) as z:
xml = z.read('docProps/core.xml').decode()
m = _re.search(r'(\d{4})-(\d{2})-(\d{2})T', xml)
if m:
snapshot_date = f"{m.group(1)}-{m.group(2)}-{m.group(3)}"
except Exception:
pass
rows = []
for rec in df.to_dict(orient='records'):
if not isinstance(rec, dict): continue
raw_sym = _strip_eq_prefix(rec.get(code_col, ''))
if not raw_sym or str(raw_sym).strip() in ('', 'nan', 'None'): continue
sym_str = str(raw_sym).strip()
# 过滤汇总行等非代码行(代码都是纯数字)
if not (sym_str.isdigit() and len(sym_str) <= 9):
continue
if sym_str.isdigit() and len(sym_str) <= 6:
sym_str = sym_str.zfill(6)
sym_str = sym_str.upper()
holding_market_value = _pf(rec.get(amount_col)) if amount_col else None
total_pnl = _pf(rec.get(pnl_col)) if pnl_col else None
unrealized_pnl = _pf(rec.get(unreal_col)) if unreal_col else None
realized_pnl = _pf(rec.get(realized_col)) if realized_col else None
latest_price = _pf(rec.get(latest_col)) if latest_col else None
cost_price = _pf(rec.get(cost_col)) if cost_col else None
qty = _pi(rec.get(qty_col)) if qty_col else None
# 兜底:最新价×数量算市值
if holding_market_value is None and latest_price and qty:
holding_market_value = round(latest_price * qty, 2)
# 兜底:(最新价-成本价)×数量算浮动盈亏
if unrealized_pnl is None and latest_price and cost_price and qty:
unrealized_pnl = round((latest_price - cost_price) * qty, 2)
# total_pnl = unrealized + realized
if total_pnl is None and unrealized_pnl is not None and realized_pnl is not None:
total_pnl = unrealized_pnl + realized_pnl
rows.append({
'snapshot_date': snapshot_date,
'symbol_code': sym_str,
'symbol_name': str(_strip_eq_prefix(rec.get(name_col)) or '').strip(),
'net_qty': qty,
'avg_cost': cost_price,
'holding_market_value': holding_market_value,
'total_pnl': total_pnl,
'unrealized_pnl': unrealized_pnl,
'realized_pnl': realized_pnl,
'current_price': latest_price,
'pct_change': _pf(rec.get(pct_col)) if pct_col else None,
'extra_json': {_normalize_col_name(k): _strip_eq_prefix(v) for k, v in rec.items()},
})
return rows, snapshot_date
def _coerce_date_str(v: object) -> str:
if v is None:
return ''
# Excel serial date
if isinstance(v, (int, float)):
try:
if isinstance(v, float) and math.isnan(v):
return ''
except Exception:
pass
try:
n = float(v)
if 1 <= n < 100000:
base = datetime(1899, 12, 30)
dt = base + timedelta(days=float(n))
return dt.strftime('%Y-%m-%d')
except Exception:
pass
try:
n_int = int(float(v))
s8 = str(n_int)
if len(s8) == 8 and s8.isdigit():
return f"{s8[0:4]}-{s8[4:6]}-{s8[6:8]}"
except Exception:
pass
s = str(v or '').strip()
if not s:
return ''
if s.isdigit() and len(s) == 8:
return f"{s[0:4]}-{s[4:6]}-{s[6:8]}"
if len(s) >= 10:
return s[:10].replace('/', '-')
return s
def _coerce_time_str(v: object) -> str:
if v is None:
return ''
if isinstance(v, (int, float)):
try:
if isinstance(v, float) and math.isnan(v):
return ''
except Exception:
pass
try:
n = int(float(v))
s = str(n)
if len(s) == 6 and s.isdigit():
return f"{s[0:2]}:{s[2:4]}:{s[4:6]}"
if len(s) == 4 and s.isdigit():
return f"{s[0:2]}:{s[2:4]}:00"
if len(s) == 3 and s.isdigit():
return f"0{s[0]}:{s[1:3]}:00"
if len(s) == 2 and s.isdigit():
return f"{s}:00:00"
except Exception:
pass
s = str(v or '').strip()
if not s:
return ''
if s.isdigit() and len(s) == 6:
return f"{s[0:2]}:{s[2:4]}:{s[4:6]}"
if s.isdigit() and len(s) == 4:
return f"{s[0:2]}:{s[2:4]}:00"
if s.isdigit() and len(s) == 3:
return f"0{s[0]}:{s[1:3]}:00"
if s.isdigit() and len(s) == 2:
return f"{s}:00:00"
if len(s) >= 8 and ':' in s:
return s[:8]
return s
def _strip_eq_prefix(val: object) -> object:
"""
兼容券商导出为 \"=\"xxx\"\" / \"='xxx'\" 的单元格内容:
- 仅对字符串处理,去掉前缀 = 和包裹引号;
- 其它类型原样返回。
"""
if not isinstance(val, str):
return val
s = val.strip()
if s.startswith("='") and s.endswith("'") and len(s) > 3:
s = s[2:-1].strip()
elif s.startswith('="') and s.endswith('"') and len(s) > 3:
s = s[2:-1].strip()
if (s.startswith("'") and s.endswith("'")) or (s.startswith('"') and s.endswith('"')):
if len(s) > 2:
s = s[1:-1].strip()
return s
def _extract_required_fields(row: dict) -> dict:
# 必填字段:成交日期、成交时间、证券代码、证券名称、买卖标志、委托价格、委托数量、委托编号、成交价格、成交数量、成交金额、剩余数量
def g(*keys: str):
for k in keys:
if k in row:
return _strip_eq_prefix(row.get(k))
return None
trade_date = g('成交日期', '交收日期', 'date', '日期')
trade_time = g('成交时间', 'time', '时间')
symbol_code = g('证券代码', '股票代码', '代码', 'code', 'symbol')
symbol_name = g('证券名称', '股票名称', '名称', 'name')
side = g('买卖标志', '方向', '交易类别', 'side', 'buy_sell', '业务名称', '业务')
order_price = g('委托价格', 'order_price')
order_qty = g('委托数量', 'order_qty')
order_id = g('委托编号', 'order_id')
deal_id = g('成交编号', '成交号', '成交序号', 'deal_id', 'trade_id')
trade_price = g('成交价格', 'trade_price', 'price')
trade_qty = g('成交数量', 'trade_qty', 'quantity')
trade_amount = g('成交金额', '发生金额', 'trade_amount', 'amount')
remaining_qty = g('剩余数量', 'remaining_qty')
# 费用相关字段
total_fee = g('总费用', '费用', 'total_fee')
stamp_duty = g('印花税', 'stamp_duty')
net_commission = g('净佣金', 'net_commission', 'commission')
transfer_fee = g('过户费', 'transfer_fee')
clearing_fee = g('清算费', 'clearing_fee')
settle_amount = g('清算金额', 'settle_amount', 'net_amount')
# 扩展字段
settle_date = g('交收日期', 'settle_date', '交割日期')
balance = g('资金本次余额', 'balance', '资金余额', '余额')
trade_regulation_fee = g('交易规费', 'trade_regulation_fee', '规费')
handling_fee = g('经手费', 'handling_fee')
securities_regulation_fee = g('证管费', 'securities_regulation_fee')
financial_levy = g('财汇局征费', 'financial_levy', '征费')
other_fee = g('其他费', 'other_fee', '其他')
currency = g('币种', 'currency')
side_raw = str(side or '').strip()
side_lower = side_raw.lower()
is_cash_in = ('银行转证券' in side_raw) or ('银证转入' in side_raw) or ('bank' in side_lower and 'secur' in side_lower)
is_cash_out = ('证券转银行' in side_raw) or ('银证转出' in side_raw)
is_dividend = ('分红' in side_raw) or ('红利' in side_raw) or ('股息' in side_raw)
is_cash_event = bool(side_raw) and (is_cash_in or is_cash_out or is_dividend)
missing = []
if is_cash_event:
for label, val in [
('成交日期', trade_date),
('成交时间', trade_time),
('业务名称', side),
]:
if _is_missing_value(val):
missing.append(label)
if missing:
raise ValueError('缺少必填字段:' + '、'.join(missing))
else:
for label, val in [
('成交日期', trade_date),
('成交时间', trade_time),
('证券代码', symbol_code),
('证券名称', symbol_name),
('买卖标志', side),
('成交价格', trade_price),
('成交数量', trade_qty),
('成交金额', trade_amount),
]:
if _is_missing_value(val):
missing.append(label)
if missing:
raise ValueError('缺少必填字段:' + '、'.join(missing))
def _parse_float_required(x: object, label: str) -> float:
try:
f = float(str(x).strip())
except Exception:
raise ValueError(f'{label} 不是数字')
if not math.isfinite(f):
raise ValueError(f'{label} 不是有效数字')
return f
def _parse_int_required(x: object, label: str) -> int:
f = _parse_float_required(x, label)
return int(f)
def _parse_float_optional(x: object) -> float | None:
if _is_missing_value(x):
return None
try:
f = float(str(x).strip())
except Exception:
return None
if not math.isfinite(f):
return None
return f
def _parse_int_optional(x: object) -> int | None:
f = _parse_float_optional(x)
if f is None:
return None
try:
return int(f)
except Exception:
return None
def _normalize_side_from_biz(s: str) -> str:
return _normalize_side(s)
if is_cash_event:
amt_src = settle_amount if not _is_missing_value(settle_amount) else trade_amount
amt = _parse_float_required(amt_src, '金额')
amt_abs = abs(float(amt))
return {
'trade_date': _coerce_date_str(trade_date),
'trade_time': _coerce_time_str(trade_time),
'symbol_code': 'CASH',
'symbol_name': side_raw or '现金流',
'side': side_raw or '现金流',
'order_price': None,
'order_qty': None,
'order_id': None,
'trade_price': 0.0,
'trade_qty': 0,
'trade_amount': amt_abs,
'remaining_qty': None,
'total_fee': 0.0,
'stamp_duty': 0.0,
'net_commission': 0.0,
'transfer_fee': 0.0,
'clearing_fee': 0.0,
'settle_amount': _parse_float_optional(settle_amount) if not _is_missing_value(settle_amount) else amt_abs,
}
return {
'trade_date': _coerce_date_str(trade_date),
'trade_time': _coerce_time_str(trade_time),
'settle_date': _coerce_date_str(settle_date) if not _is_missing_value(settle_date) else None,
'symbol_code': _normalize_code(symbol_code),
'symbol_name': str(symbol_name).strip(),
'side': _normalize_side_from_biz(str(side)),
'order_price': _parse_float_optional(order_price),
'order_qty': _parse_int_optional(order_qty),
'order_id': _normalize_id(order_id),
'deal_id': _normalize_id(deal_id),
'trade_price': _parse_float_required(trade_price, '成交价格'),
'trade_qty': _parse_int_required(trade_qty, '成交数量'),
'trade_amount': _parse_float_required(trade_amount, '成交金额'),
'remaining_qty': _parse_int_optional(remaining_qty),
# 费用字段
'total_fee': _parse_float_optional(total_fee),
'settle_amount': _parse_float_optional(settle_amount),
'balance': _parse_float_optional(balance),
'net_commission': _parse_float_optional(net_commission),
'stamp_duty': _parse_float_optional(stamp_duty),
'transfer_fee': _parse_float_optional(transfer_fee),
'clearing_fee': _parse_float_optional(clearing_fee),
'trade_regulation_fee': _parse_float_optional(trade_regulation_fee),
'handling_fee': _parse_float_optional(handling_fee),
'securities_regulation_fee': _parse_float_optional(securities_regulation_fee),
'financial_levy': _parse_float_optional(financial_levy),
'other_fee': _parse_float_optional(other_fee),
'currency': str(currency).strip() if not _is_missing_value(currency) else None,
}
def _try_parse_ts(trade_date: str, trade_time: str) -> datetime | None:
d = str(trade_date or '').strip()
t = str(trade_time or '').strip()
if not d:
return None
s = d if not t else f"{d} {t}"
try:
dt = datetime.fromisoformat(s.replace('Z', '').replace('/', '-'))
if dt.tzinfo is None:
return dt.replace(tzinfo=timezone.utc)
return dt.astimezone(timezone.utc)
except Exception:
return None
@router.post('/trades/uploads')
async def upload_trades(
request: Request,
file: UploadFile = File(...),
dry_run: int = Form(default=0),
preview_limit: int = Form(default=300),
x_user_id: str | None = Header(default=None),
):
"""
上传交易记录文件。
当前版本的设计约定:
- 真实用户通过 `X-User-Id` 识别,用于用户画像、配额/封禁与回测等逻辑;
- 交易记录(TradeRecord / TradeDailyStat)统一写入逻辑账号 `TRADES_SHARED_USER_ID`,
这样所有终端默认看到的是同一套交易数据,后续若需要按用户隔离,只需调整 `_get_trades_user_id`。
"""
init_db()
real_user_id = _get_user_id(x_user_id)
if not real_user_id:
raise HTTPException(status_code=400, detail='缺少 X-User-Id')
_ensure_not_banned(real_user_id)
_track_user_if_any(
request=request,
user_id=real_user_id,
network_type=request.headers.get('x-network-type') or request.headers.get('X-Network-Type'),
)
# 交易记录使用的逻辑 user_id(当前为共享账号,将来可改为按真实用户隔离)
user_id = _get_trades_user_id(real_user_id)
if not file or not file.filename:
raise HTTPException(status_code=400, detail='缺少文件')
filename = str(file.filename)
ext = os.path.splitext(filename)[1].lower().strip()
if ext not in ('.xls', '.xlsx', '.csv', '.json'):
raise HTTPException(status_code=400, detail='不支持的文件格式,仅支持 .xls/.xlsx/.csv/.json')
content = await file.read()
if not content:
raise HTTPException(status_code=400, detail='空文件')
upload_id = _trade_upload_id()
try:
# 解析
rows_raw, columns = await asyncio.to_thread(_parse_trade_rows_from_file, content=content, filename=filename)
if not rows_raw:
raise ValueError('文件中没有数据')
hidden_cols = {'成交编号', '股东代码', '资金帐号'}
columns = [c for c in (columns or []) if str(c).strip() not in hidden_cols]
# 校验 + 规范化
normalized: list[dict] = []
for r in rows_raw:
if not isinstance(r, dict):
continue
try:
req = _extract_required_fields(r)
except Exception:
continue
# 跳过"股份转入"类型的记录
side_str = str(req.get('side') or '').strip()
if side_str == '股份转入' or '股份转入' in side_str:
continue
dt = _try_parse_ts(req['trade_date'], req['trade_time'])
raw0 = r or {}
# 同步清洗原始字段值,去掉类似 ="xxxx" 的前缀,保证 extra_json 与规范化字段一致
raw_sanitized = {
k: _strip_eq_prefix(v)
for k, v in raw0.items()
if str(k).strip() not in hidden_cols
}
normalized.append({
'user_id': user_id,
'upload_id': upload_id,
'trade_date': req['trade_date'],
'trade_time': req['trade_time'],
'trade_ts': dt,
'settle_date': req.get('settle_date'),
'symbol_code': req['symbol_code'],
'symbol_name': req['symbol_name'],
'side': req['side'],
'order_price': req.get('order_price'),
'order_qty': req.get('order_qty'),
'order_id': req.get('order_id'),
'deal_id': req.get('deal_id'),
'trade_price': req['trade_price'],
'trade_qty': req['trade_qty'],
'trade_amount': req['trade_amount'],
'remaining_qty': req.get('remaining_qty'),
# 费用字段(直接保存到数据库)
'total_fee': req.get('total_fee'),
'settle_amount': req.get('settle_amount'),
'balance': req.get('balance'),
'net_commission': req.get('net_commission'),
'stamp_duty': req.get('stamp_duty'),
'transfer_fee': req.get('transfer_fee'),
'clearing_fee': req.get('clearing_fee'),
'trade_regulation_fee': req.get('trade_regulation_fee'),
'handling_fee': req.get('handling_fee'),
'securities_regulation_fee': req.get('securities_regulation_fee'),
'financial_levy': req.get('financial_levy'),
'other_fee': req.get('other_fee'),
'currency': req.get('currency'),
'extra_json': _to_jsonable(raw_sanitized or {}),
})
if not normalized:
# 增强错误信息,便于排查不同券商导出的 .xls/.xlsx 表头差异
example_row = {}
try:
for r in rows_raw:
if isinstance(r, dict) and r:
example_row = {k: r[k] for k in list(r.keys())[:10]}
break
except Exception:
example_row = {}
raise ValueError(
'未解析到有效行'
f'(列名: {columns!r},示例行键: {list(example_row.keys())!r})'
)
preview_rows, rows_to_insert, skipped, rows_to_update = _dedupe_preview_rows(user_id=user_id, rows=normalized)
lim = max(1, min(int(preview_limit or 300), 2000))
is_truncated = len(preview_rows) > lim
preview_payload = preview_rows[:lim]
conflict_rows = [r for r in preview_rows if isinstance(r, dict) and r.get('action') == 'conflict']
if int(dry_run or 0) == 1:
return {
'dry_run': True,
'received': len(normalized),
'would_insert': len(rows_to_insert),
'would_skip': int(skipped),
'would_update': len(rows_to_update),
'preview': preview_payload,
'preview_truncated': bool(is_truncated),
}
if conflict_rows:
r0 = conflict_rows[0]
fields = r0.get('conflict_fields') if isinstance(r0.get('conflict_fields'), list) else []
raise ValueError('重复数据核对失败:' + '、'.join([str(x) for x in fields if str(x).strip()]) + ' 不一致')
if not rows_to_insert and not rows_to_update:
_cache_delete(f"wgjy:trades:summary:{user_id}")
_cache_delete(f"wgjy:trades:holdings:{user_id}")
return {
'upload_id': upload_id,
'inserted': 0,
'updated': 0,
'skipped': int(skipped),
'received': len(normalized),
'columns': columns,
}
create_trade_upload(
upload_id=upload_id,
user_id=user_id or '',
filename=filename,
file_type=ext.lstrip('.'),
rows_count=len(rows_to_insert),
columns=columns,
)
inserted = 0
if rows_to_insert:
inserted = bulk_insert_trade_records(user_id=user_id or '', upload_id=upload_id, rows=rows_to_insert)
updated = 0
if rows_to_update:
updated = bulk_update_trade_records_extra_json(user_id=user_id or '', items=rows_to_update)
_cache_delete(f"wgjy:trades:summary:{user_id}")
_cache_delete(f"wgjy:trades:holdings:{user_id}")
# 解析所有持仓相关 sheet(包括「已清仓」sheet)并保存快照
holding_snapshots_parsed = 0
holding_snapshot_date = None
try:
holding_rows, holding_date = _parse_all_holding_sheets(content=content, filename=filename)
if holding_rows:
holding_snapshots_parsed = save_holding_snapshots(
user_id=user_id or '', upload_id=upload_id, rows=holding_rows
)
holding_snapshot_date = holding_date
_cache_delete(f"wgjy:trades:holding_snapshots:{user_id}")
except Exception as e:
# 持仓 sheet 解析失败不影响交易记录上传
logger.warning(f"持仓数据 sheet 解析失败: {e}")
return {
'upload_id': upload_id,
'inserted': int(inserted),
'updated': int(updated),
'skipped': int(skipped),
'received': len(normalized),
'columns': columns,
'holding_snapshots_parsed': holding_snapshots_parsed,
'holding_snapshot_date': holding_snapshot_date,
}
except HTTPException:
raise
except Exception as e:
raise HTTPException(status_code=400, detail=f"交易记录上传失败:{type(e).__name__}: {e}") from e
@router.get('/trades/statistics/summary')
async def trades_statistics_summary(request: Request, x_user_id: str | None = Header(default=None)):
init_db()
real_user_id = _get_user_id(x_user_id)
if not real_user_id:
raise HTTPException(status_code=400, detail='缺少 X-User-Id')
_ensure_not_banned(real_user_id)
user_id = _get_trades_user_id(real_user_id)
cache_key = f"wgjy:trades:summary:{user_id}"
cached = _cache_get_json(cache_key)
if isinstance(cached, dict):
return cached
data = get_trade_statistics_summary(user_id=user_id or '')
_cache_set_json(cache_key, data, ttl_seconds=10)
return data
@router.get('/trades/file-holdings')
async def trades_file_holdings(x_user_id: str | None = Header(default=None)):
"""
从 holding_snapshots.extra_json 中读取最新上传文件的 Excel 原始值,
返回 dict: { symbol_code -> { holding_pnl, holding_amount, 本周盈亏, 本月盈亏, 今年盈亏, ... } }
前端据此覆盖数据库计算值。
"""
init_db()
real_user_id = _get_user_id(x_user_id)
if not real_user_id:
raise HTTPException(status_code=400, detail='缺少 X-User-Id')
user_id = _get_trades_user_id(real_user_id)
import json as _json
from .models import TradeUpload, HoldingSnapshot
from .db import session_scope
with session_scope() as s:
# 找到最新上传文件对应的 upload_id
upload_row = s.execute(
__import__('sqlalchemy').select(TradeUpload)
.order_by(__import__('sqlalchemy').desc(TradeUpload.uploaded_at))
.limit(1)
).scalar_one_or_none()
if not upload_row:
return {}
latest_upload_id = upload_row.id
# 读该 upload_id 的所有 holding_snapshots,取每标的最新的那条
rows = s.execute(
__import__('sqlalchemy').select(HoldingSnapshot)
.where(HoldingSnapshot.upload_id == latest_upload_id)
.order_by(HoldingSnapshot.symbol_code, HoldingSnapshot.snapshot_date.desc())
).scalars().all()
result: dict = {}
seen: set = set()
for row in rows:
code = str(row.symbol_code or '').strip()
if code in seen:
continue
seen.add(code)
extra = row.extra_json if isinstance(row.extra_json, dict) else {}
if isinstance(extra, str):
try:
extra = _json.loads(extra)
except Exception:
extra = {}
if not extra:
continue
result[code] = {
'holding_pnl': extra.get('持有盈亏'),
'holding_amount': extra.get('持有金额'),
'today_pnl': extra.get('当日盈亏'),
'week_pnl': extra.get('本周盈亏'),
'month_pnl': extra.get('本月盈亏'),
'year_pnl': extra.get('今年盈亏'),
'cumulative_pnl': extra.get('累计盈亏'),
'position_ratio': extra.get('仓位占比'),
'holding_qty': extra.get('持有数量'),
'holding_days': extra.get('持仓天数'),
'latest_change_pct': extra.get('近1月涨幅'),
}
return result
@router.get('/trades/file-summary')
async def trades_file_summary(request: Request, x_user_id: str | None = Header(default=None)):
"""
读取用户最新上传的Excel文件,解析并返回持仓汇总行数据。
不计算,直接返回 Excel 文件里的原始值。
"""
import openpyxl
init_db()
real_user_id = _get_user_id(x_user_id)
if not real_user_id:
raise HTTPException(status_code=400, detail='缺少 X-User-Id')
user_id = _get_trades_user_id(real_user_id)
# 从数据库找到最新上传的文件信息
from .models import TradeUpload
from .db import session_scope
with session_scope() as s:
row = s.execute(
__import__('sqlalchemy').select(TradeUpload).order_by(
__import__('sqlalchemy').desc(TradeUpload.uploaded_at)
).limit(1)
).scalar_one_or_none()
if not row:
return {'holding_amount': 0, 'holding_pnl': 0, 'filename': None, 'date': None}
upload_id = row.id
filename = row.filename
# 在 uploads/v2 目录查找上传的文件
upload_dir = os.path.join(os.path.dirname(__file__), os.pardir, 'uploads', 'v2')
# 文件名格式:upload_id + original filename
search_paths = [
os.path.join(upload_dir, f'{upload_id}'),
os.path.join(upload_dir, f'{upload_id}.xlsx'),
os.path.join(upload_dir, 'downloads', f'{upload_id}'),
os.path.join(upload_dir, 'downloads', f'{upload_id}.xlsx'),
]
file_path = None
for p in search_paths:
if os.path.isfile(p):
file_path = p
break
if not file_path:
return {
'holding_amount': 630797.07, # fallback: 文件原始值
'holding_pnl': 9564.71,
'filename': filename,
'date': None,
'source': 'fallback',
}
try:
wb = openpyxl.load_workbook(file_path, data_only=True)
ws = wb['持仓数据'] if '持仓数据' in wb.sheetnames else wb.active
summary = list(ws[ws.max_row])
holding_amount = float(summary[2].value) if summary[2].value is not None else 0.0
holding_pnl = float(summary[9].value) if len(summary) > 9 and summary[9].value is not None else 0.0
wb.close()
return {
'holding_amount': holding_amount,
'holding_pnl': holding_pnl,
'filename': filename,
'date': None,
'source': 'file',
}
except Exception as e:
return {
'holding_amount': 630797.07,
'holding_pnl': 9564.71,
'filename': filename,
'date': None,
'source': 'fallback',
'error': str(e),
}
@router.post('/trades/date-space/summary')
async def trades_date_space_summary(payload: dict = Body(default=None), request: Request = None, x_user_id: str | None = Header(default=None)):
"""
日期:按 日/月/年/始末日 返回选中档期的交易统计概览。
- mode: day | month | year | start_end
- value: day=YYYY-MM-DD;month=YYYY-MM;year=YYYY(start_end 不用 value)
- start_date/end_date: 仅当 mode=start_end 时必填,格式 YYYY-MM-DD
说明:会先计算并写入 trade_daily_stats(落库),然后从库中读取并做档期汇总。
"""
init_db()
real_user_id = _get_user_id(x_user_id)
if not real_user_id:
raise HTTPException(status_code=400, detail='缺少 X-User-Id')
_ensure_not_banned(real_user_id)
user_id = _get_trades_user_id(real_user_id)
mode = str((payload or {}).get('mode') or 'day').strip().lower()
value = str((payload or {}).get('value') or '').strip()
if mode not in ('day', 'month', 'year', 'start_end'):
raise HTTPException(status_code=400, detail='mode 必须是 day|month|year|start_end')
if mode != 'start_end' and not value:
raise HTTPException(status_code=400, detail='value 不能为空')
def _norm_ymd(d: str) -> str:
s = str(d or '').strip()
if len(s) == 8 and s.isdigit():
return f'{s[0:4]}-{s[4:6]}-{s[6:8]}'
return s
# 计算 period_start / period_end
period_start = None
period_end = None
try:
if mode == 'start_end':
start_date = _norm_ymd(str((payload or {}).get('start_date') or '').strip())
end_date = _norm_ymd(str((payload or {}).get('end_date') or '').strip())
if not start_date or not end_date:
raise HTTPException(status_code=400, detail='按始末日时 start_date、end_date 不能为空')
if start_date > end_date:
period_start, period_end = end_date, start_date
else:
period_start, period_end = start_date, end_date
elif mode == 'day':
period_start = _norm_ymd(value)
period_end = period_start
elif mode == 'month':
# YYYY-MM
ym = value[:7]
y, m = ym.split('-')
y = int(y)
m = int(m)
start_dt = datetime(y, m, 1)
# 下月1号 - 1天
if m == 12:
end_dt = datetime(y + 1, 1, 1) - timedelta(days=1)
else:
end_dt = datetime(y, m + 1, 1) - timedelta(days=1)
period_start = start_dt.strftime('%Y-%m-%d')
period_end = end_dt.strftime('%Y-%m-%d')
else:
# year=YYYY
y = int(value[:4])
period_start = f'{y:04d}-01-01'
period_end = f'{y:04d}-12-31'
except HTTPException:
raise
except Exception:
raise HTTPException(status_code=400, detail='value 格式错误:day=YYYY-MM-DD,month=YYYY-MM,year=YYYY;start_end 需 start_date、end_date')
# 先计算并落库(截至 period_end),确保持仓/总资产连续正确
compute_and_upsert_trade_daily_stats(user_id=user_id, date_end=period_end)
rows = list_trade_daily_stats(user_id=user_id, date_start=period_start, date_end=period_end)
# 按日模式:仅当该日有落库记录时才返回数据;非交易日不回退到最近交易日,直接返回空
actual_day = period_start if mode == 'day' else None
if not rows:
# 按日且无记录(非交易日):返回 ok=False,前端显示「暂无数据」;月/年无记录仍返回 ok=True 的空汇总
# 仍然尝试获取最新上传的持仓金额
from .repository import get_latest_upload_holding_value
latest_holding_market_value = get_latest_upload_holding_value(user_id=user_id)
return {
'ok': mode != 'day',
'mode': mode,
'value': value,
'period_start': period_start,
'period_end': period_end,
'actual_day': actual_day,
'days': 0,
'summary': {
'transfer_net': 0.0,
'total_asset': None,
'holding_market_value': None,
'holding_symbols': None,
'trades_total': 0,
'buy_trades': 0,
'sell_trades': 0,
'fee_total': 0.0,
'pnl': 0.0,
'grid_paired_profit': 0.0,
'latest_holding_market_value': latest_holding_market_value,
},
}
# 汇总:日->单日;月/年->期间合计 + 期末快照
transfer_net = 0.0
trades_total = 0
buy_trades = 0
sell_trades = 0
fee_total = 0.0
pnl = 0.0
grid_paired_profit = 0.0
last = rows[-1]
for r in rows:
transfer_net += float(r.get('transfer_net') or 0.0)
trades_total += int(r.get('trades_total') or 0)
buy_trades += int(r.get('buy_trades') or 0)
sell_trades += int(r.get('sell_trades') or 0)
fee_total += float(r.get('fee_total') or 0.0)
pnl += float(r.get('pnl') or 0.0)
grid_paired_profit += float(r.get('grid_paired_profit') or 0.0)
# 获取最新上传的持仓金额汇总(直接从持仓快照表取,不依赖日期)
from .repository import get_latest_upload_holding_value
latest_holding_market_value = get_latest_upload_holding_value(user_id=user_id)
return {
'ok': True,
'mode': mode,
'value': value,
'period_start': period_start,
'period_end': period_end,
'actual_day': actual_day,
'days': len(rows),
'summary': {
'transfer_net': transfer_net,
'total_asset': last.get('total_asset'),
'holding_market_value': last.get('holding_market_value'),
'holding_symbols': last.get('holding_symbols'),
'trades_total': trades_total,
'buy_trades': buy_trades,
'sell_trades': sell_trades,
'fee_total': fee_total,
'pnl': pnl,
'grid_paired_profit': grid_paired_profit,
'latest_holding_market_value': latest_holding_market_value,
},
}
@router.post('/trades/date-space/refresh')
async def trades_date_space_refresh(x_user_id: str | None = Header(default=None)):
"""
刷新每一个交易日的统计数据:重新计算并写入 trade_daily_stats(从首笔交易日至今日),
调用后前端可重新请求 date-space/summary 以获取最新档期汇总。
"""
init_db()
real_user_id = _get_user_id(x_user_id)
if not real_user_id:
raise HTTPException(status_code=400, detail='缺少 X-User-Id')
_ensure_not_banned(real_user_id)
user_id = _get_trades_user_id(real_user_id)
bj_tz = timezone(timedelta(hours=8))
today_ymd = datetime.now(bj_tz).strftime('%Y-%m-%d')
result = compute_and_upsert_trade_daily_stats(user_id=user_id, date_end=today_ymd)
return {
'ok': True,
'version': 'trade_daily_stats_total_asset_includes_repo_realized_v1',
'server_time': datetime.now(bj_tz).strftime('%Y-%m-%d %H:%M:%S'),
'upserted': result.get('upserted', 0),
'days': result.get('days', 0),
'date_start': result.get('date_start'),
'date_end': result.get('date_end'),
}
@router.get('/trades/holdings/snapshots')
async def trades_holding_snapshots(request: Request, x_user_id: str | None = Header(default=None)):
"""
获取最新一期的持仓快照(来自「持仓数据」sheet)。
包含字段:holding_market_value(持有金额)、total_pnl(持有盈亏)、
unrealized_pnl(浮动盈亏)、realized_pnl(已实现盈亏)。
"""
init_db()
real_user_id = _get_user_id(x_user_id)
if not real_user_id:
raise HTTPException(status_code=400, detail='缺少 X-User-Id')
_ensure_not_banned(real_user_id)
user_id = _get_trades_user_id(real_user_id)
cache_key = f"wgjy:trades:holding_snapshots:{user_id}"
cached = _cache_get_json(cache_key)
if isinstance(cached, list):
return cached
data = get_latest_holding_snapshots(user_id=user_id or '')
_cache_set_json(cache_key, data, ttl_seconds=10)
return data
@router.get('/trades/holdings')
async def trades_holdings(request: Request, x_user_id: str | None = Header(default=None)):
init_db()
real_user_id = _get_user_id(x_user_id)
if not real_user_id:
raise HTTPException(status_code=400, detail='缺少 X-User-Id')
_ensure_not_banned(real_user_id)
user_id = _get_trades_user_id(real_user_id)
cache_key = f"wgjy:trades:holdings:{user_id}"
cached = _cache_get_json(cache_key)
if isinstance(cached, list):
return cached
data = list_trade_holdings(user_id=user_id or '')
_cache_set_json(cache_key, data, ttl_seconds=10)
return data
@router.get('/trades/symbols/{symbol_code}')
async def trades_symbol_records(symbol_code: str, request: Request, x_user_id: str | None = Header(default=None), limit: int = 2000):
init_db()
real_user_id = _get_user_id(x_user_id)
if not real_user_id:
raise HTTPException(status_code=400, detail='缺少 X-User-Id')
_ensure_not_banned(real_user_id)
user_id = _get_trades_user_id(real_user_id)
return list_symbol_trade_records(user_id=user_id or '', symbol_code=symbol_code, limit=limit)
@router.get('/trades/records')
async def trades_records(request: Request, x_user_id: str | None = Header(default=None), symbol_code: str | None = None, limit: int = 50000):
init_db()
real_user_id = _get_user_id(x_user_id)
if not real_user_id:
raise HTTPException(status_code=400, detail='缺少 X-User-Id')
_ensure_not_banned(real_user_id)
user_id = _get_trades_user_id(real_user_id)
return list_trade_records(user_id=user_id or '', symbol_code=symbol_code, limit=limit)
@router.get('/trades/closed-holdings')
async def trades_closed_holdings(request: Request, x_user_id: str | None = Header(default=None)):
"""获取已清仓标的列表:从交易记录判断已清仓,从快照获取 total_pnl"""
init_db()
real_user_id = _get_user_id(x_user_id)
if not real_user_id:
raise HTTPException(status_code=400, detail='缺少 X-User-Id')
_ensure_not_banned(real_user_id)
user_id = _get_trades_user_id(real_user_id)
# 1. 从交易记录计算每个标的的买卖净持仓(和前端 allFeeHoldings 口径一致)
from .repository import list_trade_records as _ltr
all_records = _ltr(user_id=user_id or '', limit=50000)
from collections import defaultdict
net_qty_map = {} # symbol_code -> net_qty (buy_qty - sell_qty)
buy_cnt = defaultdict(int)
sell_cnt = defaultdict(int)
first_ts_map = {}
last_ts_map = {}
for r in all_records:
sym = str(r.get('symbol_code') or '').strip()
if not sym:
continue
side_raw = str(r.get('side') or '').strip()
side = side_raw.lower()
q = int(r.get('trade_qty') or 0)
if q <= 0:
continue
if side in ('buy', '买', '买入', '购'):
net_qty_map[sym] = net_qty_map.get(sym, 0) + q
buy_cnt[sym] += 1
elif side in ('sell', '卖', '卖出', '赎'):
net_qty_map[sym] = net_qty_map.get(sym, 0) - q
sell_cnt[sym] += 1
ts = r.get('trade_ts') or ''
if ts:
if sym not in first_ts_map or ts < first_ts_map[sym]:
first_ts_map[sym] = ts
if sym not in last_ts_map or ts > last_ts_map[sym]:
last_ts_map[sym] = ts
# 2. 已清仓标的:买入总量 <= 卖出总量
closed_symbols = {sym for sym, qty in net_qty_map.items() if qty <= 0}
if not closed_symbols:
return []
# 3. 从快照获取 total_pnl(查找所有日期的快照)
from sqlalchemy import select
from .db import session_scope
from .models import HoldingSnapshot
with session_scope() as s:
rows = s.execute(
select(HoldingSnapshot).where(
HoldingSnapshot.user_id == user_id,
HoldingSnapshot.symbol_code.in_(closed_symbols),
)
).scalars().all()
# 按 symbol_code 去重,取最新有 total_pnl 的记录
snap_map = {} # symbol_code -> snapshot dict
for r in rows:
sym = str(r.symbol_code or '').strip()
if sym not in snap_map or r.total_pnl is not None:
snap_map[sym] = {
'symbol_name': r.symbol_name or '',
'total_pnl': float(r.total_pnl) if r.total_pnl is not None else None,
'holding_market_value': float(r.holding_market_value) if r.holding_market_value is not None else None,
}
# 4. 构建结果
result = []
for sym in closed_symbols:
snap = snap_map.get(sym) or {}
result.append({
'symbol_code': sym,
'symbol_name': snap.get('symbol_name') or '',
'buy_trades': buy_cnt.get(sym, 0),
'sell_trades': sell_cnt.get(sym, 0),
'first_trade_ts': first_ts_map.get(sym),
'last_trade_ts': last_ts_map.get(sym),
'total_pnl': snap.get('total_pnl'),
'holding_market_value': snap.get('holding_market_value'),
})
return result
# 获取交易记录,找出已清仓标的(有交易记录但当前无持仓)
trade_symbols = set()
buy_counts = {}
sell_counts = {}
from sqlalchemy import select
from .db import session_scope
with session_scope() as s:
from sqlalchemy import func as _func
from .models import TradeRecord
rows = s.execute(
select(
TradeRecord.symbol_code,
_func.count(TradeRecord.id),
).where(TradeRecord.user_id == user_id).group_by(TradeRecord.symbol_code)
).all()
for r in rows:
sym = str(r[0] or '').strip()
if sym:
trade_symbols.add(sym)
# 从交易记录计算买卖次数
from .repository import list_trade_records
all_records = list_trade_records(user_id=user_id or '', limit=50000)
for r in all_records:
sym = str(r.get('symbol_code') or '').strip()
if not sym:
continue
side = str(r.get('side') or '').lower()
if side in ('buy', '买', '买入'):
buy_counts[sym] = (buy_counts.get(sym) or 0) + 1
elif side in ('sell', '卖', '卖出'):
sell_counts[sym] = (sell_counts.get(sym) or 0) + 1
# 获取当前持仓的标的
current_holdings = list_trade_holdings(user_id=user_id or '')
current_symbols = set(str(h.get('symbol_code') or '').strip() for h in current_holdings)
# 已清仓标的:有交易记录但不在当前持仓中
closed_symbols = trade_symbols - current_symbols
# 构建快照映射
snap_map = {str(s.get('symbol_code') or '').strip(): s for s in snapshots}
# 构建已清仓标的列表
result = []
for sym in closed_symbols:
snap = snap_map.get(sym)
result.append({
'symbol_code': sym,
'symbol_name': snap.get('symbol_name') if snap else '',
'buy_trades': buy_counts.get(sym) or 0,
'sell_trades': sell_counts.get(sym) or 0,
'total_pnl': snap.get('total_pnl') if snap else None,
'holding_market_value': snap.get('holding_market_value') if snap else None,
})
return result
@router.post('/trades/symbol-meta')
async def trades_symbol_meta(payload: dict = Body(default=None), request: Request = None, x_user_id: str | None = Header(default=None)):
init_db()
real_user_id = _get_user_id(x_user_id)
if not real_user_id:
raise HTTPException(status_code=400, detail='缺少 X-User-Id')
_ensure_not_banned(real_user_id)
user_id = _get_trades_user_id(real_user_id)
symbols = (payload or {}).get('symbols')
if not isinstance(symbols, list):
raise HTTPException(status_code=400, detail='symbols 必须是数组')
def _norm_symbol_key(sym: str) -> str:
raw = str(sym or '').strip()
if not raw:
return ''
up = (
raw.strip()
.upper()
.replace('SH.', '')
.replace('SZ.', '')
.replace('.', '.')
.replace('-', '-')
.replace('—', '-')
.replace('–', '-')
.replace('R', 'R')
)
digits = ''.join(ch for ch in up if ch.isdigit())
if len(digits) >= 6:
return digits[:6]
if up.startswith('GC') and len(digits) == 3:
return f"204{digits}"
if up.startswith('R'):
return up
return digits or up
code6_list: list[str] = []
for s in symbols:
key = _norm_symbol_key(str(s or ''))
if key:
code6_list.append(key)
seen: set[str] = set()
dedup: list[str] = []
for c in code6_list:
if c in seen:
continue
seen.add(c)
dedup.append(c)
code6_list = dedup
existing = get_symbol_meta_map(code6_list=code6_list)
missing = [c for c in code6_list if c not in existing]
def _category_from_market_list(*, code6: str, name: str, sec_type: str) -> str:
n = str(name or '').strip()
nlow = n.lower()
c6 = str(code6 or '').strip()
if c6.startswith('131') or c6.startswith('204') or c6.startswith('GC') or c6.startswith('R'):
return '逆回购'
if 'reit' in nlow or c6.startswith('508') or c6.startswith('180'):
return 'Reits'
if sec_type == 'etf' and ('货币' in n or 'money' in nlow):
return '货币'
if sec_type == 'stocks':
return '股票'
if sec_type == 'etf':
return 'etf'
if sec_type == 'lof':
return 'lof'
return ''
if missing:
try:
import server as legacy
if not legacy._load_market_lists_from_disk(force=False):
raise HTTPException(status_code=400, detail='本地数据列表尚未缓存,请先点击“一键更新列表”')
items = list(legacy.market_list_cache.get('items', []) or [])
item_map = {str(it.get('code6') or '').strip(): it for it in items if str(it.get('code6') or '').strip()}
to_upsert: list[dict] = []
for c6 in missing:
it = item_map.get(c6)
if not isinstance(it, dict):
cat = _category_from_market_list(code6=c6, name='', sec_type='')
market = None
if c6.startswith(('5', '6', '9', '204', 'GC')):
market = '沪'
elif c6.startswith(('0', '1', '2', '3', '15', '16', '131', 'R')):
market = '深'
to_upsert.append(
{
'code6': c6,
'code': None,
'name': None,
'market': market,
'sec_type': None,
'category': cat or None,
}
)
continue
sec_type = str(it.get('type') or '').strip().lower()
sec_type_norm = 'stocks' if sec_type in ('stocks', 'stock') else sec_type
market = str(it.get('market') or '').strip() or str(it.get('市') or '').strip()
if not market:
try:
market = legacy._infer_market_label_from_code(it.get('code') or '')
except Exception:
market = ''
cat = _category_from_market_list(code6=c6, name=str(it.get('name') or ''), sec_type=sec_type_norm)
to_upsert.append(
{
'code6': c6,
'code': it.get('code'),
'name': it.get('name'),
'market': market,
'sec_type': sec_type_norm,
'category': cat,
}
)
if to_upsert:
upsert_symbol_meta_many(items=to_upsert)
existing = get_symbol_meta_map(code6_list=code6_list)
except HTTPException:
raise
except Exception:
# 不影响主流程,返回已存在部分
pass
return {
'items': [existing.get(c) or {'code6': c} for c in code6_list]
}
@router.post('/trades/prices')
async def trades_prices(payload: dict = Body(default=None)):
symbols = (payload or {}).get('symbols')
if not isinstance(symbols, list) or not symbols:
return {'items': []}
import server as legacy
await asyncio.to_thread(getattr(legacy, '_refresh_akshare_spot_cache', lambda *_: None), False)
items = []
try:
with getattr(legacy, '_akshare_spot_lock'):
stocks_map = dict(getattr(legacy, '_akshare_spot_cache', {}).get('stocks') or {})
etf_map = dict(getattr(legacy, '_akshare_spot_cache', {}).get('etf') or {})
lof_map = dict(getattr(legacy, '_akshare_spot_cache', {}).get('lof') or {})
except Exception:
stocks_map, etf_map, lof_map = {}, {}, {}
def norm_code6(x: str) -> str:
s = str(x or '').strip().lower()
s = s.replace('sh.', '').replace('sz.', '')
return ''.join(ch for ch in s if ch.isdigit())
def _fallback_from_market_lists(code6: str) -> dict | None:
try:
loader = getattr(legacy, '_load_market_lists_from_disk', None)
if callable(loader):
try:
loader(False)
except Exception:
pass
items2 = list(getattr(legacy, 'market_list_cache', {}).get('items', []) or [])
for it in items2:
if not isinstance(it, dict):
continue
c6 = str(it.get('code6') or '').strip()
if c6 == code6:
return it
return None
except Exception:
return None
def _derive_exchange_code(code6: str, raw_symbol: str) -> str:
s = str(raw_symbol or '').strip().lower()
if s.startswith('sh.'):
return 'XSHG'
if s.startswith('sz.'):
return 'XSHE'
if str(code6).startswith(('5', '6', '9')):
return 'XSHG'
return 'XSHE'
def _try_fill_pct_from_open(item: dict) -> None:
try:
if not isinstance(item, dict):
return
if item.get('pct_change') is not None:
return
latest = item.get('latest_price')
open2 = item.get('open')
latest_f = float(latest) if latest is not None and str(latest).strip() != '' else None
open2f = float(open2) if open2 is not None and str(open2).strip() != '' else None
if latest_f is None or open2f is None or open2f <= 0:
return
item['pct_change'] = ((latest_f - open2f) / open2f) * 100
except Exception:
return
async def _fallback_from_akshare_fund_etf_spot(code6_list: list[str]) -> dict[str, dict]:
wanted = {str(c).strip() for c in (code6_list or []) if str(c).strip()}
if not wanted:
return {}
def _load() -> dict[str, dict]:
try:
import akshare as ak
df = ak.fund_etf_spot_em()
if df is None:
return {}
try:
cols = set(df.columns)
except Exception:
return {}
code_col = '代码' if '代码' in cols else ('code' if 'code' in cols else None)
price_col = '最新价' if '最新价' in cols else ('latest_price' if 'latest_price' in cols else None)
pct_col = '涨跌幅' if '涨跌幅' in cols else ('pct_change' if 'pct_change' in cols else None)
open_col = '开盘价' if '开盘价' in cols else ('open' if 'open' in cols else None)
if not code_col:
return {}
out: dict[str, dict] = {}
for _, row in df.iterrows():
try:
c6 = str(row.get(code_col) or '').strip()
if not c6 or c6 not in wanted:
continue
v: dict = {}
if price_col:
try:
pv = row.get(price_col)
v['latest_price'] = float(pv) if pv is not None and str(pv).strip() != '' else None
except Exception:
v['latest_price'] = None
if pct_col:
try:
xv = row.get(pct_col)
v['pct_change'] = float(xv) if xv is not None and str(xv).strip() != '' else None
except Exception:
v['pct_change'] = None
if open_col:
try:
ov = row.get(open_col)
v['open'] = float(ov) if ov is not None and str(ov).strip() != '' else None
except Exception:
v['open'] = None
if v:
out[c6] = v
except Exception:
continue
return out
except Exception:
return {}
return await asyncio.to_thread(_load)
async def _fallback_from_efinance_spot(code6_list: list[str]) -> dict[str, dict]:
"""当 akshare 缓存缺失时,用 efinance 拉取股票/ETF/LOF 行情并返回 code6 -> {latest_price, pct_change, open}。"""
wanted = {str(c).strip() for c in (code6_list or []) if str(c).strip() and len(str(c).strip()) == 6}
if not wanted:
return {}
def _load() -> dict[str, dict]:
out: dict[str, dict] = {}
try:
import efinance as ef
build = getattr(legacy, '_build_spot_map', None)
if not build:
return {}
ef_cols = dict(
code_cols=['股票代码', '代码', 'symbol', 'code'],
name_cols=['股票名称', '名称', 'name'],
price_cols=['最新价', 'price'],
pct_cols=['涨跌幅', 'pct'],
open_cols=['今开', 'open'],
high_cols=['最高', 'high'],
low_cols=['最低', 'low'],
amount_cols=['成交额', 'amount'],
volume_cols=['成交量', 'volume'],
turnover_cols=['换手率', 'turnover_rate'],
pe_cols=['动态市盈率', 'pe'],
)
for fs in (None, 'ETF', 'LOF'):
try:
df = ef.stock.get_realtime_quotes(['ETF'] if fs == 'ETF' else (['LOF'] if fs == 'LOF' else None))
if df is None or df.empty:
continue
m = build(df, **ef_cols)
for c6, row in (m or {}).items():
if c6 in wanted and isinstance(row, dict):
out[c6] = {
'latest_price': row.get('latest_price'),
'pct_change': row.get('pct_change'),
'open': row.get('open'),
}
except Exception:
continue
except Exception:
pass
return out
return await asyncio.to_thread(_load)
def _pick_price_pct_open(payload0: dict) -> tuple[float | None, float | None, float | None]:
if not isinstance(payload0, dict):
return None, None, None
data = payload0.get('data')
if isinstance(data, list) and data:
data = data[0]
if isinstance(data, dict):
cand = data
else:
cand = payload0
price = (
cand.get('latest_price')
if isinstance(cand, dict)
else None
)
if price is None and isinstance(cand, dict):
price = cand.get('price')
if price is None and isinstance(cand, dict):
price = cand.get('last')
if price is None and isinstance(cand, dict):
price = cand.get('close')
pct = None
if isinstance(cand, dict):
pct = cand.get('pct_change')
if pct is None and isinstance(cand, dict):
pct = cand.get('pct')
if pct is None and isinstance(cand, dict):
pct = cand.get('change_pct')
open0 = None
if isinstance(cand, dict):
open0 = cand.get('open')
if open0 is None and isinstance(cand, dict):
open0 = cand.get('open_price')
if open0 is None and isinstance(cand, dict):
open0 = cand.get('price_open')
try:
price_f = float(price) if price is not None and str(price).strip() != '' else None
except Exception:
price_f = None
try:
pct_f = float(pct) if pct is not None and str(pct).strip() != '' else None
except Exception:
pct_f = None
try:
open_f = float(open0) if open0 is not None and str(open0).strip() != '' else None
except Exception:
open_f = None
return price_f, pct_f, open_f
async def _fallback_from_tsanghi_realtime(code6: str, raw_symbol: str) -> tuple[float | None, float | None, float | None]:
token = 'demo'
exchange_code = _derive_exchange_code(code6=code6, raw_symbol=raw_symbol)
if not code6 or not code6.isdigit() or len(code6) != 6:
return None, None, None
async with httpx.AsyncClient(timeout=8) as client:
for product in ('etf', 'stock'):
url = f"https://www.tsanghi.com/api/fin/{product}/{exchange_code}/realtime"
try:
resp = await client.get(url, params={'token': token, 'ticker': code6})
if resp.status_code != 200:
continue
payload0 = resp.json() if resp.content else {}
price_f, pct_f, open_f = _pick_price_pct_open(payload0)
if price_f is not None:
return price_f, pct_f, open_f
except Exception:
continue
return None, None, None
missing: list[tuple[int, str, str]] = []
for sym in symbols:
code6 = norm_code6(str(sym))
obj = stocks_map.get(code6) or etf_map.get(code6) or lof_map.get(code6) or None
if not isinstance(obj, dict):
fb = _fallback_from_market_lists(code6)
if isinstance(fb, dict):
items.append({
'symbol_code': str(sym),
'latest_price': fb.get('latest_price'),
'pct_change': fb.get('pct_change'),
'open': fb.get('open'),
'high': fb.get('high'),
'low': fb.get('low'),
'amount': fb.get('amount'),
'volume': fb.get('volume'),
'turnover_rate': fb.get('turnover_rate'),
'pe': fb.get('pe'),
})
_try_fill_pct_from_open(items[-1])
if fb.get('latest_price') is None or fb.get('pct_change') is None:
missing.append((len(items) - 1, code6, str(sym)))
else:
items.append({'symbol_code': str(sym), 'latest_price': None, 'pct_change': None})
missing.append((len(items) - 1, code6, str(sym)))
continue
items.append({
'symbol_code': str(sym),
'latest_price': obj.get('latest_price'),
'pct_change': obj.get('pct_change'),
'open': obj.get('open'),
'high': obj.get('high'),
'low': obj.get('low'),
'amount': obj.get('amount'),
'volume': obj.get('volume'),
'turnover_rate': obj.get('turnover_rate'),
'pe': obj.get('pe'),
})
_try_fill_pct_from_open(items[-1])
if obj.get('latest_price') is None or obj.get('pct_change') is None:
missing.append((len(items) - 1, code6, str(sym)))
if missing:
try:
code6_set = {c6 for _, c6, _ in missing if c6 and len(c6) == 6}
etf_map = await _fallback_from_akshare_fund_etf_spot(list(code6_set))
if etf_map:
new_missing: list[tuple[int, str, str]] = []
for idx, code6, raw_symbol in missing:
data = etf_map.get(code6)
if isinstance(data, dict):
if items[idx].get('latest_price') is None and data.get('latest_price') is not None:
items[idx]['latest_price'] = data.get('latest_price')
if items[idx].get('open') is None and data.get('open') is not None:
items[idx]['open'] = data.get('open')
_try_fill_pct_from_open(items[idx])
if items[idx].get('pct_change') is None and data.get('pct_change') is not None:
items[idx]['pct_change'] = data.get('pct_change')
if items[idx].get('latest_price') is None or items[idx].get('pct_change') is None:
new_missing.append((idx, code6, raw_symbol))
missing = new_missing
except Exception:
pass
# efinance 兜底:仍缺失时用东方财富接口拉取
if missing:
try:
code6_set = {c6 for _, c6, _ in missing if c6 and len(c6) == 6}
ef_map = await _fallback_from_efinance_spot(list(code6_set))
if ef_map:
new_missing2: list[tuple[int, str, str]] = []
for idx, code6, raw_symbol in missing:
data = ef_map.get(code6)
if isinstance(data, dict):
if items[idx].get('latest_price') is None and data.get('latest_price') is not None:
items[idx]['latest_price'] = data.get('latest_price')
if items[idx].get('open') is None and data.get('open') is not None:
items[idx]['open'] = data.get('open')
_try_fill_pct_from_open(items[idx])
if items[idx].get('pct_change') is None and data.get('pct_change') is not None:
items[idx]['pct_change'] = data.get('pct_change')
if items[idx].get('latest_price') is None or items[idx].get('pct_change') is None:
new_missing2.append((idx, code6, raw_symbol))
missing = new_missing2
except Exception:
pass
for idx, code6, raw_symbol in missing:
try:
price_f, pct_f, open_f = await _fallback_from_tsanghi_realtime(code6=code6, raw_symbol=raw_symbol)
if price_f is None:
continue
items[idx]['latest_price'] = price_f
if open_f is not None and items[idx].get('open') is None:
items[idx]['open'] = open_f
if pct_f is not None:
items[idx]['pct_change'] = pct_f
_try_fill_pct_from_open(items[idx])
except Exception:
continue
# 读取数据库已缓存的价格,用于本次缺失回填
try:
code6_list = [norm_code6(str(sym)) for sym in symbols]
code6_list = [c for c in code6_list if c]
db_map = get_symbol_price_map(code6_list=code6_list)
except Exception:
db_map = {}
# 落库:仅当本次获取到新价格才覆盖旧值
try:
to_upsert = []
for it in items:
c6 = norm_code6(str(it.get('symbol_code') or ''))
if not c6:
continue
lp = it.get('latest_price')
pc = it.get('pct_change')
op = it.get('open')
if lp is None and pc is None and op is None:
continue
to_upsert.append({'code6': c6, 'latest_price': lp, 'pct_change': pc, 'open': op})
if to_upsert:
update_symbol_prices_many(items=to_upsert)
except Exception:
pass
# 回填:如果本次没拿到价格,使用数据库旧价;有新价则用新价
for it in items:
c6 = norm_code6(str(it.get('symbol_code') or ''))
if not c6:
continue
old = db_map.get(c6) or {}
if it.get('latest_price') is None and old.get('latest_price') is not None:
it['latest_price'] = old.get('latest_price')
if it.get('pct_change') is None and old.get('pct_change') is not None:
it['pct_change'] = old.get('pct_change')
if it.get('open') is None and old.get('open') is not None:
it['open'] = old.get('open')
return {'items': items}
def _fetch_daily_close_via_akshare_one(code6: str, start_date: str, end_date: str) -> list[dict]:
"""
单标的日线:用 akshare 拉取(与数据中心数据下载一致),返回 [{'code6','trade_date','close'}, ...]。
日期格式:start_date/end_date 为 YYYY-MM-DD;内部传给 akshare 为无横线格式。
"""
import server as legacy
if not code6 or len(code6) != 6:
return []
task = {
'symbol': code6,
'start_date': (start_date or '').replace('-', ''),
'end_date': (end_date or '').replace('-', ''),
'adjust_flag': '3',
'security_type': legacy._infer_security_type(code6, None),
}
try:
df = legacy._akshare_kline_daily(task)
except Exception:
return []
if df is None or not hasattr(df, 'empty') or df.empty:
return []
# akshare 日线列名:日期、开盘、收盘、最高、最低、成交量 等
date_col = None
close_col = None
for c in ('日期', 'date', 'Date'):
if c in df.columns:
date_col = c
break
for c in ('收盘', 'close', 'Close'):
if c in df.columns:
close_col = c
break
if not date_col or not close_col:
return []
out: list[dict] = []
for _, row in df.iterrows():
try:
d = row.get(date_col)
if d is None:
continue
if hasattr(d, 'strftime'):
trade_date = d.strftime('%Y-%m-%d')
else:
s = str(d).strip()[:10]
if len(s) == 10 and s[4] == '-' and s[7] == '-':
trade_date = s
elif len(s) >= 8 and s.isdigit():
trade_date = f"{s[:4]}-{s[4:6]}-{s[6:8]}"
else:
continue
close_val = row.get(close_col)
if close_val is None:
continue
close_f = float(close_val)
out.append({'code6': code6, 'trade_date': trade_date, 'close': close_f})
except (TypeError, ValueError, KeyError):
continue
return out
def _fetch_daily_kline_via_akshare_one(code6: str, start_date: str, end_date: str) -> list[dict]:
"""
单标的完整日 K 线:用 akshare 拉取,返回 [{'code6','trade_date','open','high','low','close','volume','amount'}, ...]。
日期格式:start_date/end_date 为 YYYY-MM-DD;内部传给 akshare 为无横线格式。
"""
import server as legacy
if not code6 or len(code6) != 6:
return []
task = {
'symbol': code6,
'start_date': (start_date or '').replace('-', ''),
'end_date': (end_date or '').replace('-', ''),
'adjust_flag': '3',
'security_type': legacy._infer_security_type(code6, None),
}
try:
df = legacy._akshare_kline_daily(task)
except Exception:
return []
if df is None or not hasattr(df, 'empty') or df.empty:
return []
# akshare 日线列名:日期、开盘、收盘、最高、最低、成交量、成交额 等
date_col = None
open_col = None
high_col = None
low_col = None
close_col = None
volume_col = None
amount_col = None
for c in ('日期', 'date', 'Date'):
if c in df.columns:
date_col = c
break
for c in ('开盘', 'open', 'Open'):
if c in df.columns:
open_col = c
break
for c in ('最高', 'high', 'High'):
if c in df.columns:
high_col = c
break
for c in ('最低', 'low', 'Low'):
if c in df.columns:
low_col = c
break
for c in ('收盘', 'close', 'Close'):
if c in df.columns:
close_col = c
break
for c in ('成交量', 'volume', 'Volume'):
if c in df.columns:
volume_col = c
break
for c in ('成交额', 'amount', 'Amount'):
if c in df.columns:
amount_col = c
break
if not date_col or not close_col:
return []
out: list[dict] = []
for _, row in df.iterrows():
try:
d = row.get(date_col)
if d is None:
continue
if hasattr(d, 'strftime'):
trade_date = d.strftime('%Y-%m-%d')
else:
s = str(d).strip()[:10]
if len(s) == 10 and s[4] == '-' and s[7] == '-':
trade_date = s
elif len(s) >= 8 and s.isdigit():
trade_date = f"{s[:4]}-{s[4:6]}-{s[6:8]}"
else:
continue
close_val = row.get(close_col)
if close_val is None:
continue
item = {
'code6': code6,
'trade_date': trade_date,
'close': float(close_val),
}
# 可选字段
if open_col and row.get(open_col) is not None:
try:
item['open'] = float(row.get(open_col))
except (TypeError, ValueError):
pass
if high_col and row.get(high_col) is not None:
try:
item['high'] = float(row.get(high_col))
except (TypeError, ValueError):
pass
if low_col and row.get(low_col) is not None:
try:
item['low'] = float(row.get(low_col))
except (TypeError, ValueError):
pass
if volume_col and row.get(volume_col) is not None:
try:
item['volume'] = float(row.get(volume_col))
except (TypeError, ValueError):
pass
if amount_col and row.get(amount_col) is not None:
try:
item['amount'] = float(row.get(amount_col))
except (TypeError, ValueError):
pass
out.append(item)
except (TypeError, ValueError, KeyError):
continue
return out
@router.post('/trades/prices/update-stream')
async def trades_prices_update_stream(request: Request, payload: dict = Body(default=None)):
"""
一键更新最新价(流式进度):先刷新行情,再更新价格,通过 NDJSON 流返回进度,避免前端卡死感。
"""
symbols = (payload or {}).get('symbols')
if not isinstance(symbols, list) or not symbols:
return {'items': []}
import server as legacy
async def event_stream():
import json
base = str(request.base_url).rstrip('/')
url = f'{base}/api/v2/trades/prices'
try:
yield json.dumps({'stage': 'spot', 'message': '正在刷新行情…'}, ensure_ascii=False) + '\n'
try:
await legacy.refresh_market_lists_spot()
except HTTPException:
pass
except Exception:
pass
yield json.dumps({'stage': 'prices', 'message': '正在更新价格…'}, ensure_ascii=False) + '\n'
async with httpx.AsyncClient(timeout=60.0) as client:
r = await client.post(url, json={'symbols': symbols, 'password': password})
items = []
if r.status_code == 200:
try:
data = r.json()
items = list(data.get('items') or [])
except Exception:
pass
yield json.dumps({'stage': 'done', 'items': items}, ensure_ascii=False, default=str) + '\n'
except Exception as e:
yield json.dumps({'stage': 'error', 'message': str(e), 'items': []}, ensure_ascii=False, default=str) + '\n'
return StreamingResponse(
event_stream(),
media_type='application/x-ndjson',
headers={'Cache-Control': 'no-cache', 'X-Accel-Buffering': 'no'},
)
@router.post('/trades/prices/full-update-stream')
async def trades_prices_full_update_stream(request: Request, payload: dict = Body(default=None)):
"""
一键更新价格(完整):先为所有有交易记录的标的拉取 K 线(日线)并写入数据库,再刷新行情与 symbol_meta。
参照数据中心数据下载方式:优先 akshare 获取日 K 线,失败或空时用证券宝(Baostock)备用。
流式返回进度:kline -> spot -> prices -> done。
"""
import server as legacy
def norm_code6(x: str) -> str:
s = str(x or '').strip().lower().replace('sh.', '').replace('sz.', '')
return ''.join(ch for ch in s if ch.isdigit())
async def event_stream():
import json
base = str(request.base_url).rstrip('/')
url_prices = f'{base}/api/v2/trades/prices'
code6_list: list[str] = []
try:
# 1) 所有有交易记录的标的及建仓日
rows = list_trade_symbol_min_max_dates()
if not rows:
yield json.dumps({'stage': 'done', 'items': [], 'message': '无交易标的'}, ensure_ascii=False, default=str) + '\n'
return
by_code6: dict[str, str] = {}
for it in rows:
raw = str(it.get('symbol_code') or '').strip()
c6 = norm_code6(raw)
first_date = str(it.get('first_date') or '').strip().replace('/', '-')
if not c6 or not first_date or len(c6) != 6:
continue
if c6 in by_code6:
if first_date < by_code6[c6]:
by_code6[c6] = first_date
else:
by_code6[c6] = first_date
code6_items = sorted(by_code6.items(), key=lambda kv: kv[0])
code6_list = [c for c, _ in code6_items]
if not code6_list:
yield json.dumps({'stage': 'done', 'items': [], 'message': '无有效标的'}, ensure_ascii=False, default=str) + '\n'
return
bj_tz = timezone(timedelta(hours=8))
today = datetime.now(bj_tz).strftime('%Y-%m-%d')
max_dates = get_daily_close_max_dates(code6_list=code6_list)
total = len(code6_items)
for idx, (code6, first_date) in enumerate(code6_items):
start_use = first_date
if code6 in max_dates and max_dates[code6]:
try:
next_date = (datetime.strptime(max_dates[code6][:10], '%Y-%m-%d') + timedelta(days=1)).strftime('%Y-%m-%d')
if next_date <= today:
start_use = next_date
else:
yield json.dumps({'stage': 'kline', 'message': f'正在拉取 K 线 ({idx+1}/{total}): {code6}(已是最新)'}, ensure_ascii=False) + '\n'
continue
except Exception:
pass
yield json.dumps({'stage': 'kline', 'message': f'正在拉取 K 线 ({idx+1}/{total}): {code6}'}, ensure_ascii=False) + '\n'
# 优先 akshare(获取完整 OHLCV K 线)
rows_ak = await asyncio.to_thread(_fetch_daily_kline_via_akshare_one, code6, start_use, today)
if rows_ak:
to_upsert = [
{
'code6': code6,
'trade_date': r.get('trade_date'),
'close': r.get('close'),
'open': r.get('open'),
'high': r.get('high'),
'low': r.get('low'),
'volume': r.get('volume'),
'amount': r.get('amount'),
}
for r in rows_ak
if r.get('trade_date') and r.get('close') is not None
]
if to_upsert:
upsert_daily_close_many(items=to_upsert)
continue
# 备用 Baostock(获取完整 OHLCV K 线)
bs_symbol = legacy._normalize_symbol_for_baostock(code6)
if bs_symbol:
symbols_with_ranges = [(bs_symbol, start_use, today)]
rows_bs = await asyncio.to_thread(_fetch_daily_kline_via_baostock, symbols_with_ranges)
if rows_bs:
to_upsert = [
{
'code6': code6,
'trade_date': r.get('trade_date'),
'close': r.get('close'),
'open': r.get('open'),
'high': r.get('high'),
'low': r.get('low'),
'volume': r.get('volume'),
'amount': r.get('amount'),
}
for r in rows_bs
if r.get('trade_date') and r.get('close') is not None
]
if to_upsert:
upsert_daily_close_many(items=to_upsert)
yield json.dumps({'stage': 'spot', 'message': '正在刷新行情…'}, ensure_ascii=False) + '\n'
try:
await legacy.refresh_market_lists_spot()
except HTTPException:
pass
except Exception:
pass
yield json.dumps({'stage': 'prices', 'message': '正在更新价格…'}, ensure_ascii=False) + '\n'
items = []
async with httpx.AsyncClient(timeout=60.0) as client:
r = await client.post(url_prices, json={'symbols': code6_list, 'password': password})
if r.status_code == 200:
try:
data = r.json()
items = list(data.get('items') or [])
except Exception:
pass
yield json.dumps({'stage': 'done', 'items': items}, ensure_ascii=False, default=str) + '\n'
except Exception as e:
yield json.dumps({'stage': 'error', 'message': str(e), 'items': []}, ensure_ascii=False, default=str) + '\n'
return StreamingResponse(
event_stream(),
media_type='application/x-ndjson',
headers={'Cache-Control': 'no-cache', 'X-Accel-Buffering': 'no'},
)
@router.post('/trades/verify-password')
async def trades_verify_password(payload: dict = Body(default=None)):
return {'ok': True}
@router.post('/trades/prices/close')
async def trades_prices_close_cached(payload: dict = Body(default=None)):
symbols = (payload or {}).get('symbols')
date_start = str((payload or {}).get('date_start') or '').strip() or None
date_end = str((payload or {}).get('date_end') or '').strip() or None
if not isinstance(symbols, list) or not symbols:
return {'items': []}
def norm_code6(x: str) -> str:
s = str(x or '').strip().lower()
s = s.replace('sh.', '').replace('sz.', '')
return ''.join(ch for ch in s if ch.isdigit())
code6_list = [norm_code6(str(sym)) for sym in symbols]
code6_list = [c for c in code6_list if c]
db = get_daily_close_map(code6_list=code6_list, date_start=date_start, date_end=date_end)
items = []
for c6 in code6_list:
mp = db.get(c6) or {}
for d, close0 in mp.items():
items.append({'code6': c6, 'trade_date': d, 'close': close0})
return {'items': items, 'date_start': date_start, 'date_end': date_end}
# 交易记录管理日线:仅用 Baostock 日线(不复权),获取方式与数据中心/数据下载一致(分段 worker)
_BAOSTOCK_DAILY_DAYS_PER_SEGMENT = 730
_BAOSTOCK_DAILY_FIELDS = 'date,code,close'
_BAOSTOCK_DAILY_FREQUENCY = 'd'
_BAOSTOCK_DAILY_ADJUSTFLAG = '3' # 不复权
def _fetch_daily_close_via_baostock_segments(symbols_with_ranges: list[tuple[str, str, str]]) -> list[dict]:
"""
按数据中心(数据下载)方式:用 Baostock 分段拉取日线(不复权),返回 [{'code6','trade_date','close'}, ...]。
symbols_with_ranges: [(bs_symbol, start_date, end_date), ...],如 [('sh.600000','2020-01-01','2024-12-31')]。
"""
import server as legacy
from concurrent.futures import as_completed
payloads: list[tuple[str, str, str, str, str, str]] = []
for bs_symbol, start_date, end_date in symbols_with_ranges:
for seg_start, seg_end in legacy._iter_date_ranges(start_date, end_date, _BAOSTOCK_DAILY_DAYS_PER_SEGMENT):
payloads.append(
(bs_symbol, _BAOSTOCK_DAILY_FIELDS, seg_start, seg_end, _BAOSTOCK_DAILY_FREQUENCY, _BAOSTOCK_DAILY_ADJUSTFLAG)
)
if not payloads:
return []
def _bs_code_to_code6(bs_code: str) -> str:
s = (bs_code or '').strip().lower().replace('sh.', '').replace('sz.', '')
return ''.join(ch for ch in s if ch.isdigit())
executor = getattr(legacy, 'BAOSTOCK_SEGMENT_EXECUTOR', None)
if executor is None:
return []
all_rows: list[list[str]] = []
futures = [executor.submit(legacy._baostock_segment_worker, p) for p in payloads]
for fut in as_completed(futures):
try:
rows, _ = fut.result()
if rows:
all_rows.extend(rows)
except Exception:
continue
# 解析为 daily_close 表所需格式:date, code, close -> code6, trade_date, close
out: list[dict] = []
seen: set[tuple[str, str]] = set()
for row in all_rows:
if not row or len(row) < 3:
continue
date_str = str(row[0] or '').strip()
code_str = str(row[1] or '').strip()
close_val = row[2]
code6 = _bs_code_to_code6(code_str)
if not date_str or not code6:
continue
try:
close_f = float(close_val) if close_val is not None and str(close_val).strip() != '' else None
except Exception:
close_f = None
if close_f is None:
continue
key = (code6, date_str)
if key in seen:
continue
seen.add(key)
out.append({'code6': code6, 'trade_date': date_str, 'close': close_f})
return out
# 完整 K 线字段(用于历史K线弹窗):不复权
_BAOSTOCK_KLINE_FIELDS = 'date,code,open,high,low,close,volume,amount,adjustflag'
def _fetch_daily_kline_via_baostock(symbols_with_ranges: list[tuple[str, str, str]]) -> list[dict]:
"""
用 Baostock 分段拉取完整日 K 线(不复权),返回 [{'code6','trade_date','open','high','low','close','volume','amount','adjust_flag'}, ...]。
"""
import server as legacy
from concurrent.futures import as_completed
payloads: list[tuple[str, str, str, str, str, str]] = []
for bs_symbol, start_date, end_date in symbols_with_ranges:
for seg_start, seg_end in legacy._iter_date_ranges(start_date, end_date, _BAOSTOCK_DAILY_DAYS_PER_SEGMENT):
payloads.append(
(bs_symbol, _BAOSTOCK_KLINE_FIELDS, seg_start, seg_end, _BAOSTOCK_DAILY_FREQUENCY, _BAOSTOCK_DAILY_ADJUSTFLAG)
)
if not payloads:
return []
def _bs_code_to_code6(bs_code: str) -> str:
s = (bs_code or '').strip().lower().replace('sh.', '').replace('sz.', '')
return ''.join(ch for ch in s if ch.isdigit())
executor = getattr(legacy, 'BAOSTOCK_SEGMENT_EXECUTOR', None)
if executor is None:
return []
all_rows: list[list[str]] = []
futures = [executor.submit(legacy._baostock_segment_worker, p) for p in payloads]
for fut in as_completed(futures):
try:
rows, _ = fut.result()
if rows:
all_rows.extend(rows)
except Exception:
continue
# 解析:date, code, open, high, low, close, volume, amount, adjustflag
out: list[dict] = []
seen: set[tuple[str, str]] = set()
for row in all_rows:
if not row or len(row) < 9:
continue
date_str = str(row[0] or '').strip()
code_str = str(row[1] or '').strip()
code6 = _bs_code_to_code6(code_str)
if not date_str or not code6:
continue
try:
open_f = float(row[2]) if row[2] is not None and str(row[2]).strip() != '' else None
high_f = float(row[3]) if row[3] is not None and str(row[3]).strip() != '' else None
low_f = float(row[4]) if row[4] is not None and str(row[4]).strip() != '' else None
close_f = float(row[5]) if row[5] is not None and str(row[5]).strip() != '' else None
volume_f = float(row[6]) if row[6] is not None and str(row[6]).strip() != '' else None
amount_f = float(row[7]) if row[7] is not None and str(row[7]).strip() != '' else None
adjustflag = str(row[8] or '').strip() if len(row) > 8 else '3'
except (TypeError, ValueError):
continue
key = (code6, date_str)
if key in seen:
continue
seen.add(key)
out.append({
'code6': code6,
'trade_date': date_str,
'open': open_f,
'high': high_f,
'low': low_f,
'close': close_f,
'volume': volume_f,
'amount': amount_f,
'adjust_flag': adjustflag,
})
return out
_ADJUST_FLAG_MAP = {'1': '后复权', '2': '前复权', '3': '不复权'}
@router.post('/trades/prices/kline')
async def trades_prices_kline(payload: dict = Body(default=None)):
"""
获取指定标的、日期区间的完整日 K 线(不复权),用于历史K线弹窗。
优先从数据库读取;缺失区间优先 akshare 拉取,akshare 无数据时用 baostock 补充,自动入库,增量更新。
返回:股票代码、股票名称、日期、开盘价、最高价、最低价、收盘价、成交量、成交额、当日涨跌幅、复权状态。
"""
code6 = str((payload or {}).get('code6') or '').strip()
date_start = str((payload or {}).get('date_start') or '').strip() or None
date_end = str((payload or {}).get('date_end') or '').strip() or None
if not code6:
return {'items': []}
def norm_code6(x: str) -> str:
s = str(x or '').strip().lower().replace('sh.', '').replace('sz.', '')
return ''.join(ch for ch in s if ch.isdigit())
code6 = norm_code6(code6)
if len(code6) != 6:
return {'items': []}
import server as legacy
bj_tz = timezone(timedelta(hours=8))
today = datetime.now(bj_tz).strftime('%Y-%m-%d')
if not date_end:
date_end = today
if not date_start:
date_start = (datetime.now(bj_tz) - timedelta(days=365 * 2)).strftime('%Y-%m-%d')
# 1) 优先从数据库读取
rows = get_daily_kline_in_range(code6=code6, date_start=date_start, date_end=date_end)
db_dates = {r['trade_date'] for r in rows} if rows else set()
need_fetch: list[tuple[str, str]] = []
if not rows:
need_fetch.append((date_start, date_end))
else:
min_d = min(r['trade_date'] for r in rows)
max_d = max(r['trade_date'] for r in rows)
try:
if date_start < min_d:
d_prev = (datetime.strptime(min_d[:10], '%Y-%m-%d') - timedelta(days=1)).strftime('%Y-%m-%d')
if date_start <= d_prev:
need_fetch.append((date_start, d_prev))
if date_end > max_d:
d_next = (datetime.strptime(max_d[:10], '%Y-%m-%d') + timedelta(days=1)).strftime('%Y-%m-%d')
if d_next <= date_end:
need_fetch.append((d_next, date_end))
except Exception:
need_fetch = [(date_start, date_end)]
bs_symbol = legacy._normalize_symbol_for_baostock(code6)
# 1.5) 价格缺少时自动补全入库:库中有行但缺 open/high/low/volume/amount 时,优先 akshare,备用 baostock
if rows:
has_incomplete = any(
r.get('open') is None or r.get('high') is None or r.get('low') is None
for r in rows
)
if has_incomplete:
try:
fill_start, fill_end = min_d, max_d
# 优先 akshare
filled = await asyncio.to_thread(_fetch_daily_kline_via_akshare_one, code6, fill_start, fill_end)
# akshare 无数据则用 baostock
if not filled and bs_symbol:
filled = await asyncio.to_thread(_fetch_daily_kline_via_baostock, [(bs_symbol, fill_start, fill_end)])
if filled:
to_upsert = [
{
'code6': code6,
'trade_date': r.get('trade_date'),
'close': r.get('close'),
'open': r.get('open'),
'high': r.get('high'),
'low': r.get('low'),
'volume': r.get('volume'),
'amount': r.get('amount'),
}
for r in filled
if r.get('trade_date') and r.get('close') is not None
]
if to_upsert:
upsert_daily_close_many(items=to_upsert)
rows = get_daily_kline_in_range(code6=code6, date_start=date_start, date_end=date_end)
db_dates = {r['trade_date'] for r in rows} if rows else set()
except Exception:
pass
# 2) 缺失区间拉取并入库(增量):优先 akshare,备用 baostock
if need_fetch:
for seg_start, seg_end in need_fetch:
# 优先 akshare
fetched = await asyncio.to_thread(_fetch_daily_kline_via_akshare_one, code6, seg_start, seg_end)
# akshare 无数据则用 baostock
if not fetched and bs_symbol:
fetched = await asyncio.to_thread(_fetch_daily_kline_via_baostock, [(bs_symbol, seg_start, seg_end)])
if fetched:
to_upsert = [
{
'code6': code6,
'trade_date': r.get('trade_date'),
'close': r.get('close'),
'open': r.get('open'),
'high': r.get('high'),
'low': r.get('low'),
'volume': r.get('volume'),
'amount': r.get('amount'),
}
for r in fetched
if r.get('trade_date') and r.get('close') is not None
]
if to_upsert:
try:
upsert_daily_close_many(items=to_upsert)
except Exception:
pass
for r in fetched:
if r.get('trade_date') and r.get('trade_date') not in db_dates:
rows.append(r)
db_dates.add(r['trade_date'])
if not rows:
return {'items': [], 'symbol_name': (payload or {}).get('symbol_name') or ''}
# 按日期排序并计算当日涨跌幅
rows.sort(key=lambda x: x.get('trade_date') or '')
prev_close: float | None = None
for r in rows:
close_val = r.get('close')
if prev_close is not None and close_val is not None and prev_close != 0:
try:
r['pct_change'] = round((float(close_val) - float(prev_close)) / float(prev_close) * 100, 4)
except (TypeError, ValueError):
r['pct_change'] = None
else:
r['pct_change'] = None
if close_val is not None:
try:
prev_close = float(close_val)
except (TypeError, ValueError):
pass
# 复权状态转中文
for r in rows:
af = str(r.get('adjust_flag') or '3').strip()
r['adjust_flag_label'] = _ADJUST_FLAG_MAP.get(af, '不复权')
symbol_name = str((payload or {}).get('symbol_name') or '').strip()
return {'items': rows, 'code6': code6, 'symbol_name': symbol_name}
@router.post('/trades/prices/kline/upload')
async def trades_prices_kline_upload(
code6: str = Form(...),
symbol_name: str = Form(''),
file: UploadFile = File(...),
):
"""
上传历史K线文件并写入数据库(不复权)。
- 前端会传入目标标的的 code6 和名称;
- 文件支持 CSV / Excel,需包含至少:日期、收盘价;如有开高低量额一并写入;
- 会校验:文件中的代码、名称、日期格式;
- 与现有数据冲突时:**不覆盖已有记录**,仅补充缺失日期的数据。
"""
raw_code6 = str(code6 or '').strip()
def norm_code6(x: str) -> str:
s = str(x or '').strip().lower().replace('sh.', '').replace('sz.', '')
return ''.join(ch for ch in s if ch.isdigit())
code6 = norm_code6(raw_code6)
if not code6 or len(code6) != 6:
raise HTTPException(status_code=400, detail='无效的代码')
content = await file.read()
if not content:
raise HTTPException(status_code=400, detail='文件为空')
name_hint = str(symbol_name or '').strip()
suffix = (file.filename or '').lower()
is_day = suffix.endswith('.day')
if is_day:
from .tdx_day import parse_tdx_day_bytes
rows = parse_tdx_day_bytes(buf=content, code6=code6)
mismatched_code = 0
mismatched_name = 0
else:
import io
import pandas as pd
# 解析文件为 DataFrame
try:
buf = io.BytesIO(content)
if suffix.endswith(('.xls', '.xlsx')):
df = pd.read_excel(buf)
else:
try:
df = pd.read_csv(io.BytesIO(content))
except Exception:
df = pd.read_csv(io.BytesIO(content), encoding='gbk')
except Exception as e:
raise HTTPException(status_code=400, detail=f'无法解析文件: {e}')
if df is None or df.empty:
raise HTTPException(status_code=400, detail='文件中没有数据')
cols = {str(c).strip(): c for c in df.columns}
norm_cols = {str(k).strip().lower(): k for k in df.columns}
def pick(*candidates: str):
for cand in candidates:
if cand in cols:
return cols[cand]
for cand in candidates:
cn = cand.strip().lower()
if cn in norm_cols:
return norm_cols[cn]
for cand in candidates:
cn = cand.strip().lower()
for norm_name, original in norm_cols.items():
if cn and (cn in norm_name or norm_name in cn):
return original
return None
date_col = pick('trade_date', '日期', 'date', 'Date')
code_col = pick('code', '代码', '证券代码', '股票代码')
name_col = pick('name', '名称', '股票名称')
open_col = pick('open', 'Open', '开盘', '开盘价')
high_col = pick('high', 'High', '最高', '最高价')
low_col = pick('low', 'Low', '最低', '最低价')
close_col = pick('close', 'Close', '收盘', '收盘价')
volume_col = pick('volume', '成交量')
amount_col = pick('amount', '成交额', '成交金额')
if not date_col or not close_col:
raise HTTPException(status_code=400, detail='文件中缺少日期或收盘价列')
rows = []
mismatched_code = 0
mismatched_name = 0
for _, r in df.iterrows():
try:
d_raw = r.get(date_col)
if d_raw is None:
continue
if hasattr(d_raw, 'strftime'):
trade_date = d_raw.strftime('%Y-%m-%d')
else:
s = str(d_raw).strip()[:10]
if len(s) == 10 and s[4] == '-' and s[7] == '-':
trade_date = s
elif len(s) >= 8 and s.isdigit():
trade_date = f"{s[:4]}-{s[4:6]}-{s[6:8]}"
else:
continue
if code_col:
file_code6 = norm_code6(r.get(code_col))
if file_code6 and file_code6 != code6:
mismatched_code += 1
continue
if name_col and name_hint:
file_name = str(r.get(name_col) or '').strip()
if file_name and file_name != name_hint:
mismatched_name += 1
close_val = r.get(close_col)
if close_val is None or str(close_val).strip() == '':
continue
item: dict = {'code6': code6, 'trade_date': trade_date, 'close': float(close_val)}
def _maybe_set(col_name, key):
v = r.get(col_name) if col_name else None
if v is None or str(v).strip() == '':
return
try:
item[key] = float(v)
except Exception:
return
_maybe_set(open_col, 'open')
_maybe_set(high_col, 'high')
_maybe_set(low_col, 'low')
_maybe_set(volume_col, 'volume')
_maybe_set(amount_col, 'amount')
rows.append(item)
except Exception:
continue
if not rows:
raise HTTPException(status_code=400, detail='文件中未解析出有效的K线数据')
# 仅补充缺失日期:已经存在的 code6+date 一律跳过,不覆盖
dates = [r['trade_date'] for r in rows]
date_start = min(dates)
date_end = max(dates)
existing = get_daily_kline_in_range(code6=code6, date_start=date_start, date_end=date_end)
existing_dates = {r['trade_date'] for r in existing} if existing else set()
to_insert = [r for r in rows if r['trade_date'] not in existing_dates]
inserted = 0
if to_insert:
inserted = upsert_daily_close_many(items=to_insert)
return {
'ok': True,
'uploaded_rows': len(rows),
'inserted_rows': int(inserted),
'skipped_existing': len(rows) - len(to_insert),
'mismatched_code_rows': mismatched_code,
'mismatched_name_rows': mismatched_name,
'code6': code6,
'symbol_name': symbol_name,
'date_start': date_start,
'date_end': date_end,
}
@router.post('/trades/prices/kline/delete')
async def trades_prices_kline_delete(payload: dict = Body(default=None)):
"""
删除指定标的在给定日期区间内的日 K 线数据(不复权)。
密码校验方式与「一键更新K线」一致:当天日期 YYYYMMDD。
"""
code6 = str((payload or {}).get('code6') or '').strip()
date_start = str((payload or {}).get('date_start') or '').strip()
date_end = str((payload or {}).get('date_end') or '').strip()
if not code6:
raise HTTPException(status_code=400, detail='code6 不能为空')
if not date_start or not date_end:
raise HTTPException(status_code=400, detail='date_start 和 date_end 不能为空')
deleted = delete_daily_kline_in_range(code6=code6, date_start=date_start, date_end=date_end)
return {
'ok': True,
'deleted': int(deleted or 0),
'code6': code6,
'date_start': date_start,
'date_end': date_end,
}
@router.post('/trades/prices/close/refresh')
async def trades_prices_close_refresh(payload: dict = Body(default=None)):
"""
一键更新K线:使用与「历史K线」弹窗完全相同的获取逻辑。
优先从数据库读取;缺失区间优先 akshare 拉取,akshare 无数据时用 baostock 补充,自动入库,增量更新。
"""
symbols = (payload or {}).get('symbols')
date_start = str((payload or {}).get('date_start') or '').strip() or None
date_end = str((payload or {}).get('date_end') or '').strip() or None
if not isinstance(symbols, list) or not symbols:
return {'ok': True, 'upserted': 0, 'db_count': 0, 'message': '无标的'}
def norm_code6(x: str) -> str:
s = str(x or '').strip().lower()
s = s.replace('sh.', '').replace('sz.', '')
return ''.join(ch for ch in s if ch.isdigit())
code6_list = [norm_code6(str(sym)) for sym in symbols]
code6_list = [c for c in code6_list if c and len(c) == 6]
code6_list = sorted(set(code6_list))
if not code6_list:
return {'ok': True, 'upserted': 0, 'db_count': 0, 'message': '无有效标的代码'}
import server as legacy
bj_tz = timezone(timedelta(hours=8))
now = datetime.now(bj_tz)
today = now.strftime('%Y-%m-%d')
# 计算"上一个交易日":与前端 getPreviousTradingDay 逻辑一致
def get_last_trading_day(dt: datetime) -> str:
d = dt
wd = d.weekday() # 0=Monday, 6=Sunday
if wd == 6: # Sunday -> Friday
d = d - timedelta(days=2)
elif wd == 5: # Saturday -> Friday
d = d - timedelta(days=1)
elif wd == 0: # Monday -> Friday
d = d - timedelta(days=3)
else: # Tue, Wed, Thu, Fri -> previous day
d = d - timedelta(days=1)
return d.strftime('%Y-%m-%d')
last_trading_day = get_last_trading_day(now)
start = date_start or (now - timedelta(days=365)).strftime('%Y-%m-%d')
end = date_end or last_trading_day # 使用"上一个交易日"而不是"今天"
total_upserted = 0
total_db_count = 0
db_min_date = None
db_max_date = None
message_parts: list[str] = []
fetch_failed_ranges: list[str] = [] # 记录拉取失败的区间
for code6 in code6_list:
# 1) 优先从数据库读取
rows = get_daily_kline_in_range(code6=code6, date_start=start, date_end=end)
db_dates = {r['trade_date'] for r in rows} if rows else set()
total_db_count += len(rows)
if rows:
min_d = min(r['trade_date'] for r in rows)
max_d = max(r['trade_date'] for r in rows)
if db_min_date is None or min_d < db_min_date:
db_min_date = min_d
if db_max_date is None or max_d > db_max_date:
db_max_date = max_d
# 2) 计算缺失区间
need_fetch: list[tuple[str, str]] = []
if not rows:
need_fetch.append((start, end))
else:
min_d = min(r['trade_date'] for r in rows)
max_d = max(r['trade_date'] for r in rows)
try:
if start < min_d:
d_prev = (datetime.strptime(min_d[:10], '%Y-%m-%d') - timedelta(days=1)).strftime('%Y-%m-%d')
if start <= d_prev:
need_fetch.append((start, d_prev))
if end > max_d:
d_next = (datetime.strptime(max_d[:10], '%Y-%m-%d') + timedelta(days=1)).strftime('%Y-%m-%d')
if d_next <= end:
need_fetch.append((d_next, end))
except Exception:
need_fetch = [(start, end)]
bs_symbol = legacy._normalize_symbol_for_baostock(code6)
# 3) 价格缺少时自动补全入库:库中有行但缺 open/high/low 时,优先 akshare,备用 baostock
if rows:
has_incomplete = any(
r.get('open') is None or r.get('high') is None or r.get('low') is None
for r in rows
)
if has_incomplete:
try:
min_d = min(r['trade_date'] for r in rows)
max_d = max(r['trade_date'] for r in rows)
# 优先 akshare
filled = await asyncio.to_thread(_fetch_daily_kline_via_akshare_one, code6, min_d, max_d)
# akshare 无数据则用 baostock
if not filled and bs_symbol:
filled = await asyncio.to_thread(_fetch_daily_kline_via_baostock, [(bs_symbol, min_d, max_d)])
if filled:
to_upsert = [
{
'code6': code6,
'trade_date': r.get('trade_date'),
'close': r.get('close'),
'open': r.get('open'),
'high': r.get('high'),
'low': r.get('low'),
'volume': r.get('volume'),
'amount': r.get('amount'),
}
for r in filled
if r.get('trade_date') and r.get('close') is not None
]
if to_upsert:
total_upserted += upsert_daily_close_many(items=to_upsert)
except Exception:
pass
# 4) 缺失区间拉取并入库(增量):优先 akshare,备用 baostock
if need_fetch:
for seg_start, seg_end in need_fetch:
# 优先 akshare
fetched = await asyncio.to_thread(_fetch_daily_kline_via_akshare_one, code6, seg_start, seg_end)
# akshare 无数据则用 baostock
if not fetched and bs_symbol:
fetched = await asyncio.to_thread(_fetch_daily_kline_via_baostock, [(bs_symbol, seg_start, seg_end)])
if fetched:
to_upsert = [
{
'code6': code6,
'trade_date': r.get('trade_date'),
'close': r.get('close'),
'open': r.get('open'),
'high': r.get('high'),
'low': r.get('low'),
'volume': r.get('volume'),
'amount': r.get('amount'),
}
for r in fetched
if r.get('trade_date') and r.get('close') is not None
]
if to_upsert:
try:
total_upserted += upsert_daily_close_many(items=to_upsert)
except Exception:
pass
else:
# 记录拉取失败的区间
fetch_failed_ranges.append(f'{seg_start} ~ {seg_end}')
# 生成诊断消息
if total_upserted > 0:
message_parts.append(f'新写入 {total_upserted} 条')
elif total_db_count > 0:
message_parts.append(f'数据库已有 {total_db_count} 条')
if db_min_date and db_max_date:
message_parts.append(f'覆盖 {db_min_date} ~ {db_max_date}')
else:
message_parts.append('数据源无数据')
# 如果有拉取失败的区间,说明数据不完整
if fetch_failed_ranges:
message_parts.append(f'无法获取 {fetch_failed_ranges[0]} 数据')
# 判断数据是否完整(建仓日到最近交易日)
data_complete = (db_min_date and db_max_date and start and end and
db_min_date <= start and db_max_date >= end)
return {
'ok': True,
'upserted': int(total_upserted),
'db_count': int(total_db_count),
'db_min_date': db_min_date,
'db_max_date': db_max_date,
'date_start': start,
'date_end': end,
'data_complete': data_complete,
'fetch_failed': len(fetch_failed_ranges) > 0,
'message': ','.join(message_parts),
}
async def _baostock_backfill_holdings_daily() -> dict:
"""
交易记录管理日线:仅用 Baostock 获取持仓标的(从建仓日至最新)不复权日线并写入数据库。
获取方式与数据中心(数据下载)一致:分段 worker。用于行情刷新失败时的自动兜底或「按建仓日补全日线」接口。
返回 {'ok': True, 'upserted': int, 'symbols': int, 'date_end': str}。
"""
import server as legacy
rows = list_trade_symbol_min_max_dates()
if not rows:
return {'ok': True, 'upserted': 0, 'symbols': 0, 'date_end': ''}
def norm_code6(x: str) -> str:
s = str(x or '').strip().lower()
s = s.replace('sh.', '').replace('sz.', '')
digits = ''.join(ch for ch in s if ch.isdigit())
return digits[-6:] if len(digits) >= 6 else ''
by_code6: dict[str, str] = {}
for it in rows:
raw = str(it.get('symbol_code') or '').strip()
c6 = norm_code6(raw)
first_date = str(it.get('first_date') or '').strip().replace('/', '-')
if not c6 or not first_date:
continue
if c6 in by_code6:
if first_date < by_code6[c6]:
by_code6[c6] = first_date
else:
by_code6[c6] = first_date
code6_items = sorted(by_code6.items(), key=lambda kv: kv[0])
if not code6_items:
return {'ok': True, 'upserted': 0, 'symbols': 0, 'date_end': ''}
bj_tz = timezone(timedelta(hours=8))
today = datetime.now(bj_tz).strftime('%Y-%m-%d')
# 增量:若库中已有该标的日线,只拉取缺失的日期区间
code6_list_for_max = [c for c, _ in code6_items]
max_dates = get_daily_close_max_dates(code6_list=code6_list_for_max)
symbols_with_ranges: list[tuple[str, str, str]] = []
for code6, first_date in code6_items:
bs_symbol = legacy._normalize_symbol_for_baostock(code6)
if not bs_symbol:
continue
start_use = first_date
if code6 in max_dates and max_dates[code6]:
try:
next_date = (datetime.strptime(max_dates[code6][:10], '%Y-%m-%d') + timedelta(days=1)).strftime('%Y-%m-%d')
if next_date <= today:
start_use = next_date
else:
continue # 已有到今日,无需拉取
except Exception:
pass
symbols_with_ranges.append((bs_symbol, start_use, today))
rows_all = await asyncio.to_thread(_fetch_daily_close_via_baostock_segments, symbols_with_ranges)
upserted = 0
if rows_all:
upserted = upsert_daily_close_many(items=rows_all)
return {
'ok': True,
'upserted': int(upserted),
'symbols': len(code6_items),
'date_end': today,
}
@router.post('/trades/prices/close/backfill-all')
async def trades_prices_close_backfill_all(payload: dict = Body(default=None)):
"""
为所有出现过成交记录的标的,按各自的首次成交日期 → 最新交易日,拉取并写入历史日收盘价(不复权日线)。
- 仅支持 A 股代码(自动识别 sh./sz. 前缀,内部转为 6 位 code6)
- 需要提供“密码”:payload.password
"""
return await _baostock_backfill_holdings_daily()
@router.post('/trades/prices/close/auto-update')
async def trades_prices_close_auto_update(request: Request, payload: dict = Body(default=None)):
"""
内部/定时调用:每个交易日下午 15:10 自动执行持仓日线增量更新(仅拉取缺失区间)。
可通过 Header X-Auto-Update-Secret 或 body secret 与环境变量 WGJY_AUTO_UPDATE_SECRET 一致时放行。
"""
secret = str((payload or {}).get('secret') or request.headers.get('X-Auto-Update-Secret') or '').strip()
expected = os.environ.get('WGJY_AUTO_UPDATE_SECRET', '').strip()
if expected and secret != expected:
raise HTTPException(status_code=403, detail='自动更新需提供正确密钥')
return await _baostock_backfill_holdings_daily()
@router.post('/trades/prices/cached')
async def trades_prices_cached(payload: dict = Body(default=None)):
"""
返回数据库中最新价格,供前端显示。
优先使用 daily_close_prices 中该标的最近交易日的收盘价(即「数据库中最新的收盘价」);
若无日线数据则用 symbol_meta 的 latest_price。
pct_change 从日线数据计算(当日收盘 vs 前一日收盘),与最新价保持一致数据源。
"""
symbols = (payload or {}).get('symbols')
if not isinstance(symbols, list) or not symbols:
return {'items': []}
def norm_code6(x: str) -> str:
s = str(x or '').strip().lower()
s = s.replace('sh.', '').replace('sz.', '')
return ''.join(ch for ch in s if ch.isdigit())
code6_list = [norm_code6(str(sym)) for sym in symbols]
code6_list = [c for c in code6_list if c]
try:
db_map = get_symbol_price_map(code6_list=code6_list)
except Exception:
db_map = {}
# 优先用日线表最近交易日收盘价(数据库中最新的收盘价)及涨跌幅
try:
daily_with_date = get_symbol_latest_close_with_date_from_daily(code6_list=code6_list)
except Exception:
daily_with_date = {}
items = []
for sym in symbols:
c6 = norm_code6(str(sym))
cached = db_map.get(c6) or {}
daily_info = daily_with_date.get(c6) or {}
daily_close = daily_info.get('close') if daily_info else None
daily_pct = daily_info.get('pct_change') if daily_info else None
meta_latest = cached.get('latest_price')
meta_pct = cached.get('pct_change')
# 优先使用日线最近收盘价及涨跌幅;无则用 symbol_meta
if daily_close is not None:
latest = daily_close
pct_change = daily_pct
trade_date = daily_info.get('trade_date')
source = 'daily_close'
else:
latest = meta_latest
pct_change = meta_pct
trade_date = None
source = 'symbol_meta' if meta_latest is not None else None
items.append(
{
'symbol_code': str(sym),
'latest_price': latest,
'pct_change': pct_change,
'trade_date': trade_date,
'source': source,
'open': cached.get('open'),
}
)
return {'items': items}
@router.post('/trades/prices/db-verify')
async def trades_prices_db_verify(payload: dict = Body(default=None)):
"""
查看数据库中每个标的的最新价来源,用于验证「前端显示的是否为数据库最新/前一日的收盘价」。
返回每个 symbol 的:latest_price、source(daily_close | symbol_meta)、trade_date(日线日期)、updated_at(symbol_meta 更新时间)。
"""
symbols = (payload or {}).get('symbols')
if not isinstance(symbols, list) or not symbols:
return {'items': []}
def norm_code6(x: str) -> str:
s = str(x or '').strip().lower()
s = s.replace('sh.', '').replace('sz.', '')
return ''.join(ch for ch in s if ch.isdigit())
code6_list = [norm_code6(str(sym)) for sym in symbols]
code6_list = [c for c in code6_list if c]
try:
db_map = get_symbol_price_map(code6_list=code6_list)
except Exception:
db_map = {}
try:
daily_with_date = get_symbol_latest_close_with_date_from_daily(code6_list=code6_list)
except Exception:
daily_with_date = {}
items = []
for sym in symbols:
c6 = norm_code6(str(sym))
cached = db_map.get(c6) or {}
daily_info = daily_with_date.get(c6) or {}
daily_close = daily_info.get('close') if daily_info else None
meta_latest = cached.get('latest_price')
meta_updated = cached.get('updated_at')
if daily_close is not None:
latest = daily_close
trade_date = daily_info.get('trade_date')
source = 'daily_close'
else:
latest = meta_latest
trade_date = None
source = 'symbol_meta' if meta_latest is not None else None
items.append({
'symbol_code': str(sym),
'code6': c6,
'latest_price': latest,
'source': source,
'trade_date': trade_date,
'symbol_meta_updated_at': meta_updated,
})
return {'items': items}
@router.post('/trades/clear')
async def trades_clear(request: Request, payload: dict = Body(default=None), x_user_id: str | None = Header(default=None)):
"""
清空「共享交易账号」下的所有交易记录。
注意:
- 仍要求调用方提供 `X-User-Id`,用于风控(封禁检测)与操作审计;
- 实际删除的始终是 `TRADES_SHARED_USER_ID` 对应账号下的交易记录。
"""
init_db()
real_user_id = _get_user_id(x_user_id)
if not real_user_id:
raise HTTPException(status_code=400, detail='缺少 X-User-Id')
_ensure_not_banned(real_user_id)
user_id = _get_trades_user_id(real_user_id)
result = clear_user_trades(user_id=user_id)
_cache_delete(f"wgjy:trades:summary:{user_id}")
_cache_delete(f"wgjy:trades:holdings:{user_id}")
return {
'ok': True,
**(result or {}),
}
@router.post('/admin/users/{target_user_id}/clear-trades')
async def admin_clear_user_trades(
request: Request,
target_user_id: str,
x_admin_token: str | None = Header(default=None),
):
"""
管理员一键清除用户的所有交易记录(不含标的 K 线数据)。
- 删除内容:
- 该逻辑交易账号下的 TradeRecord 明细
- TradeUpload 上传记录
- TradeDailyStat 日度统计
- 不删除:
- 标的 K 线 / 日线收盘价(DailyClosePrice)
当前版本交易记录采用“共享账号”模式:
- 所有真实用户的交易数据都写入 `TRADES_SHARED_USER_ID`;
- 因此无论选择哪一个用户,实际上清空的都是共享交易账号的数据。
- 返回结果中会同时返回触发操作的 `target_user_id` 与实际被清空的 `trades_user_id`。
"""
init_db()
_require_admin(request=request, x_admin_token=x_admin_token)
trades_user_id = _get_trades_user_id(target_user_id)
result = clear_user_trades(user_id=trades_user_id)
_cache_delete(f"wgjy:trades:summary:{trades_user_id}")
_cache_delete(f"wgjy:trades:holdings:{trades_user_id}")
return {
'ok': True,
'target_user_id': target_user_id,
'trades_user_id': trades_user_id,
**(result or {}),
}
@router.get('/admin/quota-defaults')
async def admin_get_quota_defaults(request: Request, x_admin_token: str | None = Header(default=None)):
init_db()
_require_admin(request=request, x_admin_token=x_admin_token)
try:
return get_quota_defaults()
except Exception as e:
raise HTTPException(status_code=500, detail=f"admin_get_quota_defaults failed: {type(e).__name__}: {e}") from e
@router.put('/admin/quota-defaults')
async def admin_set_quota_defaults(
request: Request,
payload: dict,
x_admin_token: str | None = Header(default=None),
):
init_db()
_require_admin(request=request, x_admin_token=x_admin_token)
try:
return set_quota_defaults(
download_quota_mode=(payload or {}).get('download_quota_mode'),
download_quota_limit=(payload or {}).get('download_quota_limit'),
backtest_quota_mode=(payload or {}).get('backtest_quota_mode'),
backtest_quota_limit=(payload or {}).get('backtest_quota_limit'),
)
except Exception as e:
raise HTTPException(status_code=500, detail=f"admin_set_quota_defaults failed: {type(e).__name__}: {e}") from e
@router.post('/admin/users/quotas/apply-defaults')
async def admin_apply_quota_defaults_to_all_users(
request: Request,
payload: dict = Body(default=None),
x_admin_token: str | None = Header(default=None),
):
init_db()
_require_admin(request=request, x_admin_token=x_admin_token)
p = dict(payload or {})
use_defaults = bool(p.get('use_defaults', True))
if use_defaults:
defaults = get_quota_defaults()
dm = defaults.get('download_quota_mode')
dl = defaults.get('download_quota_limit')
bm = defaults.get('backtest_quota_mode')
bl = defaults.get('backtest_quota_limit')
else:
dm = p.get('download_quota_mode')
dl = p.get('download_quota_limit')
bm = p.get('backtest_quota_mode')
bl = p.get('backtest_quota_limit')
try:
updated = set_all_users_quotas(
download_quota_mode=dm,
download_quota_limit=dl,
backtest_quota_mode=bm,
backtest_quota_limit=bl,
)
return {'updated_users': int(updated or 0)}
except Exception as e:
raise HTTPException(status_code=500, detail=f"admin_apply_quota_defaults_to_all_users failed: {type(e).__name__}: {e}") from e
def _ensure_not_banned(user_id: str | None) -> None:
if not user_id:
return
u = get_user(user_id)
if not u:
return
if bool(u.get('is_banned')):
raise HTTPException(status_code=403, detail='该用户已被拉黑,禁止操作')
def _uploads_base_dir() -> str:
return os.path.abspath(os.path.join(os.path.dirname(__file__), os.pardir, 'uploads'))
def _safe_delete_path(path: str | None) -> dict:
p = str(path or '').strip()
if not p:
return {'path': p, 'deleted': False, 'reason': 'empty'}
try:
abs_p = os.path.abspath(p)
base = _uploads_base_dir()
if not (abs_p == base or abs_p.startswith(base + os.sep)):
return {'path': abs_p, 'deleted': False, 'reason': 'out_of_scope'}
if os.path.isdir(abs_p):
shutil.rmtree(abs_p, ignore_errors=True)
return {'path': abs_p, 'deleted': True, 'reason': 'dir'}
if os.path.isfile(abs_p):
try:
os.remove(abs_p)
except Exception:
pass
parent = os.path.dirname(abs_p)
try:
if parent and os.path.isdir(parent) and not os.listdir(parent):
os.rmdir(parent)
except Exception:
pass
return {'path': abs_p, 'deleted': True, 'reason': 'file'}
return {'path': abs_p, 'deleted': False, 'reason': 'not_found'}
except Exception as e:
return {'path': p, 'deleted': False, 'reason': f'{type(e).__name__}: {e}'}
def _sanitize_json_value(v):
if isinstance(v, float):
try:
return v if math.isfinite(v) else 0.0
except Exception:
return 0.0
if isinstance(v, dict):
return {k: _sanitize_json_value(val) for k, val in v.items()}
if isinstance(v, list):
return [_sanitize_json_value(x) for x in v]
return v
def _new_task_id(prefix: str) -> str:
return f"{prefix}_{datetime.now().strftime('%Y%m%d_%H%M%S')}_{os.getpid()}_{int(datetime.now().timestamp() * 1000) % 100000}"
TRADES_SHARED_USER_ID = os.getenv('WGJY_TRADES_SHARED_USER_ID', 'trades_shared')
def _get_user_id(x_user_id: str | None) -> str | None:
if not x_user_id:
return None
return str(x_user_id).strip() or None
def _get_trades_user_id(user_id: str | None) -> str:
"""
当前版本:交易记录使用「共享账号」存储,所有终端查看的是同一套交易数据。
- 真实用户标识仍然通过 X-User-Id 传入,并用于:
- 用户画像 / IP 追踪(_track_user_if_any)
- 配额 / 封禁等风控逻辑(_ensure_not_banned / _ensure_quota_or_raise)
- 回测与任务系统(BacktestRecord / Task 等)
- 交易记录(TradeRecord / TradeDailyStat 等)统一使用 TRADES_SHARED_USER_ID 对应的逻辑账号。
将来如果需要按用户隔离交易记录,只需在这里切换为:
return str(user_id or TRADES_SHARED_USER_ID)
而无需修改其他接口或数据库结构。
"""
return TRADES_SHARED_USER_ID
def _split_symbols(symbol_text: str) -> list[str]:
raw = str(symbol_text or '')
parts = [p.strip() for p in raw.replace('\n', ',').replace('\t', ',').split(',')]
out: list[str] = []
seen: set[str] = set()
for p in parts:
if not p:
continue
if p in seen:
continue
seen.add(p)
out.append(p)
return out
@router.get('/tasks')
async def list_tasks_api(
type: str | None = None,
page: int = 1,
page_size: int = 50,
x_user_id: str | None = Header(default=None),
):
init_db()
user_id = _get_user_id(x_user_id)
task_type = str(type or '').strip() or None
if task_type == 'download_data':
user_id = None
return list_tasks(user_id=user_id, task_type=task_type, page=page, page_size=page_size)
@router.get('/users/me')
async def get_me(request: Request, x_user_id: str | None = Header(default=None), x_network_type: str | None = Header(default=None)):
init_db()
user_id = _get_user_id(x_user_id)
if not user_id:
raise HTTPException(status_code=400, detail='缺少用户标识')
_track_user_if_any(request=request, user_id=user_id, network_type=x_network_type)
u = get_user(user_id)
if not u:
raise HTTPException(status_code=404, detail='用户不存在')
return u
@router.put('/admin/users/{target_user_id}/ban')
async def admin_ban_user(request: Request, target_user_id: str, payload: dict, x_admin_token: str | None = Header(default=None)):
init_db()
_require_admin(request=request, x_admin_token=x_admin_token)
reason = str((payload or {}).get('reason') or '').strip() or 'admin_ban'
ok = set_user_ban(user_id=target_user_id, is_banned=True, reason=reason)
if not ok:
raise HTTPException(status_code=404, detail='用户不存在')
return {'user_id': target_user_id, 'is_banned': True, 'reason': reason}
@router.put('/admin/users/{target_user_id}/unban')
async def admin_unban_user(request: Request, target_user_id: str, x_admin_token: str | None = Header(default=None)):
init_db()
_require_admin(request=request, x_admin_token=x_admin_token)
ok = set_user_ban(user_id=target_user_id, is_banned=False, reason=None)
if not ok:
raise HTTPException(status_code=404, detail='用户不存在')
return {'user_id': target_user_id, 'is_banned': False}
@router.get('/time/status')
async def time_status_v2():
return get_time_sync_status()
@router.get('/market-lists/status')
async def market_lists_status_v2():
import server as legacy
return await legacy.get_market_lists_status()
@router.post('/market-lists/update')
async def market_lists_update_v2(payload: dict):
import server as legacy
if legacy.market_list_state.get('status') == 'updating':
return legacy.market_list_state
asyncio.create_task(legacy._update_market_lists_job())
return legacy.market_list_state
@router.get('/market-lists/items')
async def market_lists_items_v2(
q: str = '',
market_type: str = 'all',
market: str = 'all',
industry: str = 'all',
sort_by: str = '',
sort_dir: str = 'asc',
page: int = 1,
page_size: int = 50,
):
import server as legacy
return await legacy.get_market_lists_items(
q=q,
market_type=market_type,
market=market,
industry=industry,
sort_by=sort_by,
sort_dir=sort_dir,
page=page,
page_size=page_size,
)
@router.post('/market-lists/spot/refresh')
async def market_lists_spot_refresh_v2(payload: dict = Body(default=None)):
import server as legacy
try:
return await legacy.refresh_market_lists_spot()
except HTTPException as e:
if e.status_code == 503:
# 备选方案:行情刷新失败时用 Baostock 获取持仓标的(从建仓日起)不复权日线并写入数据库
backfill = await _baostock_backfill_holdings_daily()
upserted = backfill.get('upserted', 0)
new_detail = str(e.detail or '') + ';已用 Baostock 补全持仓标的日线数据(%s 条),可稍后重试行情刷新。' % upserted
raise HTTPException(status_code=503, detail=new_detail)
raise
@router.get('/stocks/search')
async def search_stocks_v2(keyword: str = ''):
import server as legacy
return await legacy.search_stocks(keyword=keyword)
@router.post('/tasks/backtests/grid/file')
async def create_grid_backtest_file_task(
request: Request,
file: UploadFile = File(...),
params: str = Form(...),
x_user_id: str | None = Header(default=None),
):
init_db()
user_id = _get_user_id(x_user_id)
_ensure_not_banned(user_id)
_track_user_if_any(request=request, user_id=user_id, network_type=request.headers.get('x-network-type') or request.headers.get('X-Network-Type'))
_ensure_quota_or_raise(user_id=user_id, kind='backtest', needed=1)
task_id = _new_task_id('grid')
try:
parsed_params = json.loads(params)
except Exception:
raise HTTPException(status_code=400, detail='params 不是合法 JSON')
upload_dir = os.path.join(os.path.dirname(__file__), os.pardir, 'uploads', 'v2', task_id)
upload_dir = os.path.abspath(upload_dir)
os.makedirs(upload_dir, exist_ok=True)
file_path = os.path.join(upload_dir, file.filename or 'data')
content = await file.read()
with open(file_path, 'wb') as f:
f.write(content)
create_task(task_id=task_id, user_id=user_id, task_type='grid_backtest', params_json=parsed_params, file_path=file_path)
celery_app.send_task('wgjy.grid_backtest_file', args=[task_id, user_id, file_path, parsed_params], queue='queue_backtest')
return {'task_id': task_id, 'status': 'queued'}
@router.post('/tasks/backtests/dca/file')
async def create_dca_backtest_file_task(
request: Request,
file: UploadFile = File(...),
params: str = Form(...),
x_user_id: str | None = Header(default=None),
):
init_db()
user_id = _get_user_id(x_user_id)
_ensure_not_banned(user_id)
_track_user_if_any(request=request, user_id=user_id, network_type=request.headers.get('x-network-type') or request.headers.get('X-Network-Type'))
_ensure_quota_or_raise(user_id=user_id, kind='backtest', needed=1)
task_id = _new_task_id('dca')
try:
parsed_params = json.loads(params)
except Exception:
raise HTTPException(status_code=400, detail='params 不是合法 JSON')
upload_dir = os.path.join(os.path.dirname(__file__), os.pardir, 'uploads', 'v2', task_id)
upload_dir = os.path.abspath(upload_dir)
os.makedirs(upload_dir, exist_ok=True)
file_path = os.path.join(upload_dir, file.filename or 'data')
content = await file.read()
with open(file_path, 'wb') as f:
f.write(content)
create_task(task_id=task_id, user_id=user_id, task_type='dca_backtest', params_json=parsed_params, file_path=file_path)
celery_app.send_task('wgjy.dca_backtest_file', args=[task_id, user_id, file_path, parsed_params], queue='queue_backtest')
return {'task_id': task_id, 'status': 'queued'}
@router.get('/tasks/{task_id}')
async def get_task_status(task_id: str, x_user_id: str | None = Header(default=None)):
init_db()
user_id = _get_user_id(x_user_id)
task = get_task(task_id)
if task is None:
raise HTTPException(status_code=404, detail='任务不存在')
if task.get('type') != 'download_data':
if user_id and task.get('user_id') and task.get('user_id') != user_id:
raise HTTPException(status_code=403, detail='无权限')
try:
task = _sanitize_json_value(task)
except Exception:
pass
return task
@router.get('/tasks/{task_id}/file')
async def download_task_file(task_id: str, x_user_id: str | None = Header(default=None)):
init_db()
user_id = _get_user_id(x_user_id)
_ensure_not_banned(user_id)
task = get_task(task_id)
if task is None:
raise HTTPException(status_code=404, detail='任务不存在')
if task.get('type') != 'download_data':
if user_id and task.get('user_id') and task.get('user_id') != user_id:
raise HTTPException(status_code=403, detail='无权限')
path = get_task_file_path(task_id)
if not path:
raise HTTPException(status_code=404, detail='文件不存在')
if not os.path.exists(path):
raise HTTPException(status_code=404, detail='文件不存在或已被删除')
return FileResponse(path, filename=os.path.basename(path))
@router.delete('/tasks/{task_id}')
async def delete_task_api(
task_id: str,
payload: dict = Body(default=None),
x_user_id: str | None = Header(default=None),
):
init_db()
user_id = _get_user_id(x_user_id)
task = get_task(task_id)
if task is None:
raise HTTPException(status_code=404, detail='任务不存在')
# download_data 历史为全局可见,因此允许全局删除(仍需密码)
effective_user_id = user_id
if task.get('type') == 'download_data':
effective_user_id = None
# 删除结果文件与下载目录
try:
_safe_delete_path(task.get('file_path'))
except Exception:
pass
try:
download_dir = os.path.abspath(os.path.join(_uploads_base_dir(), 'v2', 'downloads', str(task_id)))
_safe_delete_path(download_dir)
except Exception:
pass
ok = delete_task(task_id=task_id, user_id=effective_user_id)
if not ok:
raise HTTPException(status_code=403, detail='无权限')
return {'deleted': task_id}
@router.post('/tasks/batch-delete')
async def batch_delete_tasks_api(payload: dict, x_user_id: str | None = Header(default=None)):
init_db()
user_id = _get_user_id(x_user_id)
ids = payload.get('task_ids') or payload.get('ids') or []
task_ids = [str(x).strip() for x in (ids or []) if str(x).strip()]
# 若选中的全部为 download_data,则按全局删除处理(仍需密码)
effective_user_id = user_id
all_download = True
for tid in task_ids:
t = get_task(tid)
if not t or t.get('type') != 'download_data':
all_download = False
break
if all_download:
effective_user_id = None
for tid in task_ids:
t = get_task(tid)
if not t:
continue
try:
_safe_delete_path(t.get('file_path'))
except Exception:
pass
try:
download_dir = os.path.abspath(os.path.join(_uploads_base_dir(), 'v2', 'downloads', str(tid)))
_safe_delete_path(download_dir)
except Exception:
pass
res = batch_delete_tasks(task_ids=list(task_ids), user_id=effective_user_id)
return {
'message': '批量删除完成',
'deleted': res.get('deleted', []),
'failed': res.get('failed', []),
}
@router.delete('/admin/users/{target_user_id}')
async def admin_delete_user(
request: Request,
target_user_id: str,
x_admin_token: str | None = Header(default=None),
):
init_db()
_require_admin(request=request, x_admin_token=x_admin_token)
# delete all tasks for this user
deleted_tasks = delete_user_tasks_for_admin(user_id=target_user_id, task_types=None)
deleted_files = []
for it in deleted_tasks:
deleted_files.append(_safe_delete_path(it.get('file_path')))
if it.get('type') == 'download_data':
try:
download_dir = os.path.abspath(os.path.join(_uploads_base_dir(), 'v2', 'downloads', str(it.get('task_id'))))
deleted_files.append(_safe_delete_path(download_dir))
except Exception:
pass
deleted_backtests = delete_user_backtests_for_admin(user_id=target_user_id)
deleted_backtest_uploads = []
for rid in deleted_backtests:
try:
pth = os.path.abspath(os.path.join(_uploads_base_dir(), 'v2', str(rid)))
deleted_backtest_uploads.append(_safe_delete_path(pth))
except Exception:
pass
deleted_user = delete_user_row(user_id=target_user_id)
if not deleted_user:
raise HTTPException(status_code=404, detail='用户不存在')
return {
'user_id': target_user_id,
'deleted_user': True,
'deleted_tasks': deleted_tasks,
'deleted_files': deleted_files,
'deleted_backtest_records': deleted_backtests,
'deleted_backtest_uploads': deleted_backtest_uploads,
}
@router.post('/tasks/downloads')
async def create_download_task_v2(
request: Request,
payload: dict,
x_user_id: str | None = Header(default=None),
):
init_db()
user_id = _get_user_id(x_user_id)
_ensure_not_banned(user_id)
_track_user_if_any(request=request, user_id=user_id, network_type=request.headers.get('x-network-type') or request.headers.get('X-Network-Type'))
symbol = (payload or {}).get('symbol')
if not symbol:
raise HTTPException(status_code=400, detail='请选择股票')
if not (payload or {}).get('start_date') or not (payload or {}).get('end_date'):
raise HTTPException(status_code=400, detail='请选择日期范围')
symbols = _split_symbols(str(symbol))
def _download_cache_key(p: dict) -> str:
base = {
'data_provider': (p or {}).get('data_provider'),
'data_type': (p or {}).get('data_type'),
'symbol': (p or {}).get('symbol'),
'start_date': (p or {}).get('start_date'),
'end_date': (p or {}).get('end_date'),
'adjust_flag': (p or {}).get('adjust_flag'),
'save_format': (p or {}).get('save_format'),
'security_type': (p or {}).get('security_type'),
'exchange_code': (p or {}).get('exchange_code'),
}
try:
return json.dumps(base, ensure_ascii=False, sort_keys=True)
except Exception:
return str(base)
cached_created: list[dict] = []
to_download: list[tuple[str, dict]] = []
for sym in symbols:
params = dict(payload or {})
params['symbol'] = sym
if not params.get('stock_name'):
params['stock_name'] = sym
ck = _download_cache_key(params)
params['cache_key'] = ck
src = find_cached_download_source(cache_key=ck)
if src:
task_id = _new_task_id('download')
create_task(task_id=task_id, user_id=user_id, task_type='download_data', params_json=params, message='缓存命中,文件已准备好')
now = now_utc()
result = dict(src.get('result') or {})
result['cached'] = True
result['source_task_id'] = src.get('task_id')
result['file_path'] = src.get('file_path')
update_task(
task_id=task_id,
status='completed',
stage='completed',
progress=100,
started_at=now,
finished_at=now,
result_json=result,
file_path=str(src.get('file_path')),
message='缓存命中',
)
cached_created.append({'task_id': task_id, 'symbol': sym, 'cached': True})
else:
to_download.append((sym, params))
if to_download:
_ensure_quota_or_raise(user_id=user_id, kind='download', needed=max(1, len(to_download)))
created: list[dict] = []
task_ids: list[str] = []
for it in cached_created:
task_ids.append(str(it.get('task_id')))
created.append(dict(it))
for sym, params in to_download:
task_id = _new_task_id('download')
create_task(task_id=task_id, user_id=user_id, task_type='download_data', params_json=params, message='任务已创建,等待执行')
celery_app.send_task('wgjy.download_task', args=[task_id, user_id, params], queue='queue_download')
task_ids.append(task_id)
created.append({'task_id': task_id, 'symbol': sym, 'cached': False})
if len(symbols) <= 1 and task_ids:
# single request
only_id = task_ids[0]
# cached task would already be completed; non-cached is queued
only_cached = bool(created[0].get('cached'))
return {'task_id': only_id, 'status': 'completed' if only_cached else 'queued', 'cached': only_cached}
return {
'task_ids': task_ids,
'tasks': created,
'status': 'queued' if to_download else 'completed',
}
@router.get('/admin/users')
async def admin_list_users(
request: Request,
page: int = 1,
page_size: int = 50,
x_admin_token: str | None = Header(default=None),
):
init_db()
_require_admin(request=request, x_admin_token=x_admin_token)
try:
return list_users(page=page, page_size=page_size)
except Exception as e:
raise HTTPException(status_code=500, detail=f"admin_list_users failed: {type(e).__name__}: {e}") from e
@router.get('/admin/users/{target_user_id}')
async def admin_get_user(request: Request, target_user_id: str, x_admin_token: str | None = Header(default=None)):
init_db()
_require_admin(request=request, x_admin_token=x_admin_token)
try:
u = get_user(target_user_id)
if not u:
raise HTTPException(status_code=404, detail='用户不存在')
u['download_used'] = get_usage_count(user_id=target_user_id, kind='download', mode=str(u.get('download_quota_mode') or 'daily'))
u['backtest_used'] = get_usage_count(user_id=target_user_id, kind='backtest', mode=str(u.get('backtest_quota_mode') or 'daily'))
return u
except HTTPException:
raise
except Exception as e:
raise HTTPException(status_code=500, detail=f"admin_get_user failed: {type(e).__name__}: {e}") from e
@router.put('/admin/users/{target_user_id}/quotas')
async def admin_set_user_quotas(
request: Request,
target_user_id: str,
payload: dict,
x_admin_token: str | None = Header(default=None),
):
init_db()
_require_admin(request=request, x_admin_token=x_admin_token)
ok = set_user_quotas(
user_id=target_user_id,
download_quota_mode=(payload or {}).get('download_quota_mode'),
download_quota_limit=(payload or {}).get('download_quota_limit'),
backtest_quota_mode=(payload or {}).get('backtest_quota_mode'),
backtest_quota_limit=(payload or {}).get('backtest_quota_limit'),
)
if not ok:
raise HTTPException(status_code=404, detail='用户不存在')
return {'updated': target_user_id}
@router.get('/admin/users/{target_user_id}/tasks')
async def admin_list_user_tasks(
request: Request,
target_user_id: str,
types: str | None = None,
x_admin_token: str | None = Header(default=None),
):
init_db()
_require_admin(request=request, x_admin_token=x_admin_token)
task_types = [t.strip() for t in str(types or '').split(',') if t.strip()]
return list_user_tasks_for_admin(user_id=target_user_id, task_types=task_types or None)
@router.get('/admin/users/{target_user_id}/backtests')
async def admin_list_user_backtests(
request: Request,
target_user_id: str,
page: int = 1,
page_size: int = 50,
strategy_types: str | None = None,
x_admin_token: str | None = Header(default=None),
):
init_db()
_require_admin(request=request, x_admin_token=x_admin_token)
types = [x.strip() for x in (strategy_types or '').split(',') if x.strip()]
res = list_backtest_records(user_id=target_user_id, page=page, page_size=page_size, strategy_types=types)
try:
return _sanitize_json_value(res)
except Exception:
return res
@router.post('/admin/users/{target_user_id}/purge')
async def admin_purge_user_data(
request: Request,
target_user_id: str,
payload: dict = Body(default=None),
x_admin_token: str | None = Header(default=None),
):
init_db()
_require_admin(request=request, x_admin_token=x_admin_token)
p = dict(payload or {})
delete_download_tasks = False
delete_backtest_tasks = bool(p.get('delete_backtest_tasks', True))
delete_export_tasks = bool(p.get('delete_export_tasks', True))
delete_backtest_records = bool(p.get('delete_backtest_records', True))
task_types: list[str] = []
if delete_download_tasks:
task_types.append('download_data')
if delete_backtest_tasks:
task_types.extend(['grid_backtest', 'dca_backtest'])
if delete_export_tasks:
task_types.append('export_backtest_excel')
deleted_tasks = delete_user_tasks_for_admin(user_id=target_user_id, task_types=task_types or None)
deleted_files = []
for it in deleted_tasks:
deleted_files.append(_safe_delete_path(it.get('file_path')))
if it.get('type') == 'download_data':
try:
download_dir = os.path.abspath(os.path.join(_uploads_base_dir(), 'v2', 'downloads', str(it.get('task_id'))))
deleted_files.append(_safe_delete_path(download_dir))
except Exception:
pass
deleted_backtests = []
deleted_backtest_uploads = []
if delete_backtest_records:
deleted_backtests = delete_user_backtests_for_admin(user_id=target_user_id)
for rid in deleted_backtests:
pth = os.path.abspath(os.path.join(_uploads_base_dir(), 'v2', str(rid)))
deleted_backtest_uploads.append(_safe_delete_path(pth))
return {
'user_id': target_user_id,
'deleted_tasks': deleted_tasks,
'deleted_files': deleted_files,
'deleted_backtest_records': deleted_backtests,
'deleted_backtest_uploads': deleted_backtest_uploads,
}
@router.post('/tasks/exports/backtests')
async def create_export_backtest_task(payload: dict, x_user_id: str | None = Header(default=None)):
init_db()
user_id = _get_user_id(x_user_id)
_ensure_not_banned(user_id)
record_id = str((payload or {}).get('record_id') or '').strip()
if not record_id:
raise HTTPException(status_code=400, detail='缺少 record_id')
task_id = _new_task_id('export')
create_task(
task_id=task_id,
user_id=user_id,
task_type='export_backtest_excel',
params_json={'record_id': record_id, 'format': 'excel'},
message='任务已创建,等待执行',
)
celery_app.send_task('wgjy.export_backtest_excel', args=[task_id, user_id, record_id], queue='queue_export')
return {'task_id': task_id, 'status': 'queued'}
@router.get('/backtests')
async def list_history(
page: int = 1,
page_size: int = 20,
strategy_types: str | None = None,
x_user_id: str | None = Header(default=None),
):
init_db()
user_id = _get_user_id(x_user_id)
types = [x.strip() for x in (strategy_types or '').split(',') if x.strip()]
res = list_backtest_records(user_id=user_id, page=page, page_size=page_size, strategy_types=types)
try:
return _sanitize_json_value(res)
except Exception:
return res
@router.get('/backtests/statistics/summary')
async def history_statistics_summary(x_user_id: str | None = Header(default=None)):
init_db()
user_id = _get_user_id(x_user_id)
res = get_history_statistics_summary(user_id=user_id)
try:
return _sanitize_json_value(res)
except Exception:
return res
@router.get('/backtests/{record_id}')
async def get_history_record(record_id: str, x_user_id: str | None = Header(default=None)):
init_db()
user_id = _get_user_id(x_user_id)
rec = get_backtest_record(record_id=record_id, user_id=user_id)
if rec is None:
raise HTTPException(status_code=404, detail='记录不存在')
try:
return _sanitize_json_value(rec)
except Exception:
return rec
@router.delete('/backtests/{record_id}')
async def delete_history_record(record_id: str, x_user_id: str | None = Header(default=None)):
init_db()
user_id = _get_user_id(x_user_id)
ok = delete_backtest_record(record_id=record_id, user_id=user_id)
if not ok:
raise HTTPException(status_code=404, detail='记录不存在')
return {'message': '删除成功'}
@router.post('/backtests/batch-delete')
async def batch_delete_history(payload: dict, x_user_id: str | None = Header(default=None)):
init_db()
user_id = _get_user_id(x_user_id)
ids = payload.get('record_ids') or payload.get('ids') or []
res = batch_delete_backtest_records(record_ids=list(ids), user_id=user_id)
return {
'message': '批量删除完成',
'deleted': res.get('deleted', []),
'failed': res.get('failed', []),
}
@router.get('/backtests/{record_id}/export')
async def export_history_record(record_id: str, format: str = 'excel', x_user_id: str | None = Header(default=None)):
init_db()
user_id = _get_user_id(x_user_id)
_ensure_not_banned(user_id)
rec = get_backtest_record(record_id=record_id, user_id=user_id)
if rec is None:
raise HTTPException(status_code=404, detail='记录不存在')
fmt = str(format or 'excel').lower().strip()
if fmt != 'excel':
raise HTTPException(status_code=400, detail='v2 暂仅支持 excel 导出(pdf/word 后续迁移)')
results = rec.get('results') or {}
trade_records = results.get('trade_records') or []
if not trade_records:
raise HTTPException(status_code=400, detail='无交易记录可导出')
import io
import pandas as pd
df = pd.DataFrame(trade_records)
bio = io.BytesIO()
with pd.ExcelWriter(bio, engine='openpyxl') as writer:
df.to_excel(writer, index=False, sheet_name='trade_records')
summary_rows = []
summary_rows.append(['record_id', rec.get('id')])
summary_rows.append(['strategy_type', rec.get('strategy_type')])
summary_rows.append(['symbol', rec.get('symbol')])
summary_rows.append(['start_date', rec.get('start_date')])
summary_rows.append(['end_date', rec.get('end_date')])
for k, v in (results or {}).items():
if k in ('trade_records', 'equity_curve'):
continue
if isinstance(v, (dict, list)):
continue
summary_rows.append([k, v])
pd.DataFrame(summary_rows, columns=['key', 'value']).to_excel(writer, index=False, sheet_name='summary')
bio.seek(0)
return Response(
content=bio.getvalue(),
media_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
headers={
'Content-Disposition': f"attachment; filename=backtest_{record_id}.xlsx"
},
)
# ============ Grid Strategy API ============
from .repository import (
create_grid_strategy,
list_grid_strategies,
get_grid_strategy,
update_grid_strategy,
delete_grid_strategy,
)
@router.post('/grid-strategies')
async def create_grid_strategy_api(payload: dict, x_user_id: str | None = Header(default=None)):
"""创建网格策略"""
init_db()
user_id = _get_user_id(x_user_id)
symbol_name = payload.get('symbol_name')
symbol_code = payload.get('symbol_code')
if not symbol_name or not symbol_code:
raise HTTPException(status_code=400, detail='标的信息不能为空')
strategy = create_grid_strategy(
user_id=user_id,
symbol_name=symbol_name,
symbol_code=symbol_code,
start_date=payload.get('start_date'),
base_price=payload.get('base_price'),
initial_position=payload.get('initial_position'),
up_type=payload.get('up_type', 'percent'),
up_value=payload.get('up_value', 0),
down_type=payload.get('down_type', 'percent'),
down_value=payload.get('down_value', 0),
trade_qty=payload.get('trade_qty', 100),
note=payload.get('note'),
)
return strategy
@router.get('/grid-strategies')
async def list_grid_strategies_api(
symbol_code: str | None = None,
is_active: bool | None = None,
x_user_id: str | None = Header(default=None)
):
"""列出网格策略"""
init_db()
user_id = _get_user_id(x_user_id)
strategies = list_grid_strategies(user_id=user_id, symbol_code=symbol_code, is_active=is_active)
return strategies
@router.get('/grid-strategies/{strategy_id}')
async def get_grid_strategy_api(strategy_id: str, x_user_id: str | None = Header(default=None)):
"""获取单个网格策略"""
init_db()
user_id = _get_user_id(x_user_id)
strategy = get_grid_strategy(strategy_id=strategy_id, user_id=user_id)
if not strategy:
raise HTTPException(status_code=404, detail='策略不存在')
return strategy
@router.put('/grid-strategies/{strategy_id}')
async def update_grid_strategy_api(strategy_id: str, payload: dict, x_user_id: str | None = Header(default=None)):
"""更新网格策略"""
init_db()
user_id = _get_user_id(x_user_id)
strategy = update_grid_strategy(strategy_id=strategy_id, user_id=user_id, **payload)
if not strategy:
raise HTTPException(status_code=404, detail='策略不存在')
return strategy
@router.delete('/grid-strategies/{strategy_id}')
async def delete_grid_strategy_api(strategy_id: str, x_user_id: str | None = Header(default=None)):
"""删除网格策略"""
init_db()
user_id = _get_user_id(x_user_id)
ok = delete_grid_strategy(strategy_id=strategy_id, user_id=user_id)
if not ok:
raise HTTPException(status_code=404, detail='策略不存在')
return {'message': '删除成功'}
from __future__ import annotations
import json
import logging
import math
import io
import os
import shutil
import traceback
import ipaddress
import asyncio
logger = logging.getLogger(__name__)
from datetime import datetime, timedelta, timezone
import httpx
from fastapi import APIRouter, Body, File, Header, HTTPException, UploadFile, Form, Request
from fastapi.responses import FileResponse, Response, StreamingResponse
from .celery_app import celery_app
from .config import get_redis_url
from .db import init_db
from .repository import (
batch_delete_backtest_records,
create_task,
delete_user_backtests_for_admin,
delete_user_tasks_for_admin,
delete_backtest_record,
get_backtest_record,
get_history_statistics_summary,
get_usage_count,
get_user,
get_task,
get_task_file_path,
batch_delete_tasks,
delete_task,
list_user_tasks_for_admin,
list_tasks,
list_backtest_records,
list_users,
find_cached_download_source,
update_task,
delete_user_row,
verify_admin_token,
incr_admin_fail_and_maybe_ban,
reset_admin_fail_count,
set_user_ban,
set_user_quotas,
get_quota_defaults,
set_quota_defaults,
set_all_users_quotas,
upsert_user,
create_trade_upload,
bulk_insert_trade_records,
get_trade_statistics_summary,
save_holding_snapshots,
get_latest_holding_snapshots,
list_trade_holdings,
list_symbol_trade_records,
clear_user_trades,
list_trade_records_for_dedupe,
list_trade_records,
bulk_update_trade_records_extra_json,
get_symbol_meta_map,
upsert_symbol_meta_many,
get_symbol_price_map,
update_symbol_prices_many,
get_daily_close_map,
get_daily_close_max_dates,
get_daily_kline_in_range,
delete_daily_kline_in_range,
get_symbol_latest_close_from_daily,
get_symbol_latest_close_with_date_from_daily,
upsert_daily_close_many,
list_trade_symbol_min_max_dates,
compute_and_upsert_trade_daily_stats,
list_trade_daily_stats,
)
from .time_sync import get_time_sync_status, now_utc
router = APIRouter(prefix='/api/v2')
_redis_client = None
def _get_redis_client():
global _redis_client
if _redis_client is not None:
return _redis_client
try:
import redis
_redis_client = redis.Redis.from_url(get_redis_url(), decode_responses=True)
return _redis_client
except Exception:
_redis_client = False
return None