1115 lines
46 KiB
Python
1115 lines
46 KiB
Python
"""资金管理API — 资金缺口预测 + 收付款计划 + 预警 + 应收催收闭环 + 网银流水导入 (资金管理智能体)"""
|
||
import io
|
||
import json
|
||
import logging
|
||
import os
|
||
from datetime import datetime, timedelta
|
||
from pathlib import Path
|
||
from fastapi import APIRouter, HTTPException, Depends, Query, Request, UploadFile, File
|
||
from fastapi.responses import FileResponse
|
||
from sqlalchemy.orm import Session
|
||
from sqlalchemy import or_
|
||
import pandas as pd
|
||
from app.database import get_db
|
||
from app.deps import get_entity_id, resolve_entity_for_request
|
||
from app.auth_middleware import require_role
|
||
from app.models import CashPlan
|
||
from app.utils.cash_forecast_engine import (
|
||
forecast_cash_flow_with_plans,
|
||
check_cash_alerts,
|
||
DEFAULT_CASH_WARNING,
|
||
DEFAULT_CASH_CRITICAL,
|
||
)
|
||
|
||
logger = logging.getLogger("cma.cash")
|
||
|
||
router = APIRouter(
|
||
prefix="/api/cma/cash",
|
||
tags=["资金管理"],
|
||
dependencies=[Depends(require_role("ceo", "finance", "business", "it"))],
|
||
)
|
||
|
||
|
||
def _aging_bucket(days_late: int) -> str:
|
||
"""账龄区间（按逾期天数）：未到期 / 0-30天 / 30-60天 / 60-90天 / 90天以上"""
|
||
if days_late <= 0:
|
||
return "未到期"
|
||
if days_late <= 30:
|
||
return "0-30天"
|
||
if days_late <= 60:
|
||
return "30-60天"
|
||
if days_late <= 90:
|
||
return "60-90天"
|
||
return "90天以上"
|
||
|
||
|
||
def _plan_dict(p: CashPlan) -> dict:
|
||
today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
|
||
due = p.plan_date.replace(hour=0, minute=0, second=0, microsecond=0) if p.plan_date else today
|
||
days_late = (today - due).days
|
||
paid = round(p.paid_amount or 0, 2)
|
||
balance = round((p.amount or 0) - paid, 2)
|
||
if p.plan_type == "receive":
|
||
overdue = p.status == "pending" and due < today
|
||
if p.status == "pending":
|
||
aging = _aging_bucket(days_late)
|
||
elif p.status == "completed":
|
||
aging = "已结清"
|
||
else:
|
||
aging = "已取消"
|
||
else:
|
||
overdue = False
|
||
aging = "—"
|
||
return {
|
||
"id": p.id,
|
||
"entity_id": p.entity_id,
|
||
"plan_type": p.plan_type,
|
||
"plan_type_label": "收款" if p.plan_type == "receive" else "付款",
|
||
"amount": round(p.amount or 0, 2),
|
||
"paid_amount": paid,
|
||
"receivable_balance": max(balance, 0),
|
||
"plan_date": p.plan_date.strftime("%Y-%m-%d") if p.plan_date else "",
|
||
"counterparty": p.counterparty or "",
|
||
"description": p.description or "",
|
||
"status": p.status,
|
||
"status_label": {"pending": "待执行", "completed": "已完成", "cancelled": "已取消"}.get(p.status, p.status),
|
||
"owner": p.owner or "",
|
||
"source": p.source or "manual",
|
||
"overdue": overdue,
|
||
"overdue_days": max(days_late, 0) if overdue else 0,
|
||
"aging_bucket": aging,
|
||
"completed_at": p.completed_at.strftime("%Y-%m-%d %H:%M") if p.completed_at else None,
|
||
"created_at": p.created_at.strftime("%Y-%m-%d %H:%M") if p.created_at else None,
|
||
}
|
||
|
||
|
||
# ══════════════════════════════════════════════════════════
|
||
# 1. 资金缺口预测
|
||
# ══════════════════════════════════════════════════════════
|
||
|
||
@router.get("/gap-forecast")
|
||
def api_gap_forecast(
|
||
entity_id: int = Depends(get_entity_id),
|
||
days: int = Query(30, ge=1, le=90, description="预测天数"),
|
||
current_cash: float = Query(None, description="当前现金余额(万元)，不传则自动获取"),
|
||
db: Session = Depends(get_db),
|
||
):
|
||
"""资金缺口预测 — 趋势引擎 + 收付款计划，识别余额<警戒线的缺口日期"""
|
||
try:
|
||
return forecast_cash_flow_with_plans(entity_id, db, days=days, current_cash=current_cash)
|
||
except Exception as e:
|
||
logger.error(f"资金缺口预测失败: {e}", exc_info=True)
|
||
raise HTTPException(400, f"资金缺口预测失败: {str(e)}")
|
||
|
||
|
||
@router.get("/balance")
|
||
def api_get_balance(
|
||
entity_id: int = Depends(get_entity_id),
|
||
db: Session = Depends(get_db),
|
||
):
|
||
"""获取当前现金余额（预测基线）"""
|
||
from app.utils.cash_forecast_engine import get_current_cash_balance
|
||
value = get_current_cash_balance(db, entity_id)
|
||
return {"entity_id": entity_id, "current_cash": value}
|
||
|
||
|
||
@router.post("/balance")
|
||
def api_set_balance(
|
||
data: dict,
|
||
db: Session = Depends(get_db),
|
||
):
|
||
"""设置当前现金余额（万元），作为预测基线"""
|
||
from app.utils.cash_forecast_engine import set_current_cash_balance
|
||
value = float(data.get("current_cash", 0))
|
||
if value < 0:
|
||
raise HTTPException(400, "现金余额不能为负")
|
||
set_current_cash_balance(db, value)
|
||
return {"message": "当前现金余额已更新", "current_cash": value}
|
||
|
||
|
||
# ══════════════════════════════════════════════════════════
|
||
# 2. 收付款计划 CRUD
|
||
# ══════════════════════════════════════════════════════════
|
||
|
||
@router.get("/plans")
|
||
def api_list_plans(
|
||
entity_id: int = Depends(get_entity_id),
|
||
plan_type: str = Query(None, description="receive/pay"),
|
||
status: str = Query(None, description="pending/completed/cancelled"),
|
||
month: str = Query(None, description="YYYY-MM 按计划月份过滤"),
|
||
page: int = Query(1, ge=1),
|
||
page_size: int = Query(100, ge=1, le=500),
|
||
db: Session = Depends(get_db),
|
||
):
|
||
"""收付款计划列表"""
|
||
query = db.query(CashPlan).filter(CashPlan.entity_id == entity_id)
|
||
if plan_type:
|
||
query = query.filter(CashPlan.plan_type == plan_type)
|
||
if status:
|
||
query = query.filter(CashPlan.status == status)
|
||
if month:
|
||
try:
|
||
y, m = int(month[:4]), int(month[5:7])
|
||
start = datetime(y, m, 1)
|
||
end = (start + timedelta(days=32)).replace(day=1)
|
||
query = query.filter(CashPlan.plan_date >= start, CashPlan.plan_date < end)
|
||
except Exception:
|
||
raise HTTPException(400, "month格式应为YYYY-MM")
|
||
total = query.count()
|
||
plans = query.order_by(CashPlan.plan_date.asc(), CashPlan.id.desc()) \
|
||
.offset((page - 1) * page_size).limit(page_size).all()
|
||
return {"total": total, "data": [_plan_dict(p) for p in plans]}
|
||
|
||
|
||
@router.post("/plans")
|
||
def api_create_plan(request: Request, data: dict, db: Session = Depends(get_db)):
|
||
"""新建收付款计划"""
|
||
plan_type = data.get("plan_type")
|
||
if plan_type not in ("receive", "pay"):
|
||
raise HTTPException(400, "plan_type必须为receive(收)或pay(付)")
|
||
amount = float(data.get("amount", 0))
|
||
if amount <= 0:
|
||
raise HTTPException(400, "金额必须大于0")
|
||
date_str = data.get("plan_date")
|
||
if not date_str:
|
||
raise HTTPException(400, "缺少计划日期")
|
||
try:
|
||
plan_date = datetime.strptime(str(date_str)[:10], "%Y-%m-%d")
|
||
except Exception:
|
||
raise HTTPException(400, "plan_date格式应为YYYY-MM-DD")
|
||
|
||
plan = CashPlan(
|
||
entity_id=resolve_entity_for_request(request, int(data.get("entity_id", 1))),
|
||
plan_type=plan_type,
|
||
amount=amount,
|
||
plan_date=plan_date,
|
||
counterparty=(data.get("counterparty") or "").strip(),
|
||
description=(data.get("description") or "").strip(),
|
||
status=data.get("status", "pending"),
|
||
owner=(data.get("owner") or "").strip() or None,
|
||
source=(data.get("source") or "manual").strip(),
|
||
paid_amount=float(data.get("paid_amount") or 0),
|
||
)
|
||
db.add(plan)
|
||
db.commit()
|
||
db.refresh(plan)
|
||
logger.info(f"新建收付款计划 #{plan.id} [{plan_type}] {amount}万 {date_str}")
|
||
return {"message": "收付款计划已创建", "data": _plan_dict(plan)}
|
||
|
||
|
||
@router.put("/plans/{plan_id}")
|
||
def api_update_plan(plan_id: int, data: dict, db: Session = Depends(get_db)):
|
||
"""更新收付款计划"""
|
||
plan = db.query(CashPlan).filter(CashPlan.id == plan_id).first()
|
||
if not plan:
|
||
raise HTTPException(404, "计划不存在")
|
||
if "plan_type" in data:
|
||
if data["plan_type"] not in ("receive", "pay"):
|
||
raise HTTPException(400, "plan_type必须为receive或pay")
|
||
plan.plan_type = data["plan_type"]
|
||
if "amount" in data:
|
||
amount = float(data["amount"])
|
||
if amount <= 0:
|
||
raise HTTPException(400, "金额必须大于0")
|
||
plan.amount = amount
|
||
if "plan_date" in data and data["plan_date"]:
|
||
try:
|
||
plan.plan_date = datetime.strptime(str(data["plan_date"])[:10], "%Y-%m-%d")
|
||
except Exception:
|
||
raise HTTPException(400, "plan_date格式应为YYYY-MM-DD")
|
||
if "counterparty" in data:
|
||
plan.counterparty = (data["counterparty"] or "").strip()
|
||
if "description" in data:
|
||
plan.description = (data["description"] or "").strip()
|
||
if "owner" in data:
|
||
plan.owner = (data["owner"] or "").strip() or None
|
||
if "source" in data:
|
||
plan.source = (data["source"] or "manual").strip()
|
||
if "paid_amount" in data:
|
||
plan.paid_amount = float(data["paid_amount"] or 0)
|
||
if "status" in data:
|
||
plan.status = data["status"]
|
||
if data["status"] == "completed" and not plan.completed_at:
|
||
plan.completed_at = datetime.now()
|
||
elif data["status"] in ("pending", "cancelled"):
|
||
plan.completed_at = None
|
||
db.commit()
|
||
db.refresh(plan)
|
||
# 完成收款时联动：消除到期未收款预警 + 催收行动 + F_AR_DAYS
|
||
if plan.plan_type == "receive" and plan.status == "completed":
|
||
try:
|
||
_resolve_plan_alerts(db, plan.entity_id, plan.id, plan.paid_amount or plan.amount)
|
||
_sync_collection_action_plan(db, plan.entity_id)
|
||
_update_ar_days_kpi(db, plan.entity_id)
|
||
db.commit()
|
||
except Exception as e:
|
||
logger.warning(f"完成收款联动失败: {e}")
|
||
return {"message": "计划已更新", "data": _plan_dict(plan)}
|
||
|
||
|
||
@router.delete("/plans/{plan_id}")
|
||
def api_delete_plan(plan_id: int, db: Session = Depends(get_db)):
|
||
"""删除收付款计划"""
|
||
plan = db.query(CashPlan).filter(CashPlan.id == plan_id).first()
|
||
if not plan:
|
||
raise HTTPException(404, "计划不存在")
|
||
db.delete(plan)
|
||
db.commit()
|
||
return {"message": "计划已删除"}
|
||
|
||
|
||
@router.post("/plans/{plan_id}/complete")
|
||
def api_complete_plan(plan_id: int, db: Session = Depends(get_db)):
|
||
"""标记计划为已完成（收款到账/付款完成）"""
|
||
plan = db.query(CashPlan).filter(CashPlan.id == plan_id).first()
|
||
if not plan:
|
||
raise HTTPException(404, "计划不存在")
|
||
plan.status = "completed"
|
||
plan.completed_at = datetime.now()
|
||
if plan.plan_type == "receive" and not plan.paid_amount:
|
||
plan.paid_amount = plan.amount
|
||
db.commit()
|
||
db.refresh(plan)
|
||
if plan.plan_type == "receive":
|
||
try:
|
||
_resolve_plan_alerts(db, plan.entity_id, plan.id, plan.paid_amount or plan.amount)
|
||
_sync_collection_action_plan(db, plan.entity_id)
|
||
_update_ar_days_kpi(db, plan.entity_id)
|
||
db.commit()
|
||
except Exception as e:
|
||
logger.warning(f"完成收款联动失败: {e}")
|
||
return {"message": "已标记完成", "data": _plan_dict(plan)}
|
||
|
||
|
||
# ══════════════════════════════════════════════════════════
|
||
# 3. 到期提醒 + 页面看板
|
||
# ══════════════════════════════════════════════════════════
|
||
|
||
@router.get("/upcoming")
|
||
def api_upcoming(
|
||
entity_id: int = Depends(get_entity_id),
|
||
days: int = Query(7, ge=1, le=30),
|
||
db: Session = Depends(get_db),
|
||
):
|
||
"""未来N天到期提醒 + 已逾期未收/未付"""
|
||
today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
|
||
upcoming = db.query(CashPlan).filter(
|
||
CashPlan.entity_id == entity_id,
|
||
CashPlan.status == "pending",
|
||
CashPlan.plan_date >= today,
|
||
CashPlan.plan_date <= today + timedelta(days=days),
|
||
).order_by(CashPlan.plan_date.asc()).all()
|
||
overdue = db.query(CashPlan).filter(
|
||
CashPlan.entity_id == entity_id,
|
||
CashPlan.status == "pending",
|
||
CashPlan.plan_date < today,
|
||
).order_by(CashPlan.plan_date.asc()).all()
|
||
return {
|
||
"days": days,
|
||
"upcoming": [_plan_dict(p) for p in upcoming],
|
||
"overdue": [_plan_dict(p) for p in overdue],
|
||
"overdue_receive_amount": round(sum(p.amount for p in overdue if p.plan_type == "receive"), 2),
|
||
"overdue_pay_amount": round(sum(p.amount for p in overdue if p.plan_type == "pay"), 2),
|
||
}
|
||
|
||
|
||
@router.get("/dashboard")
|
||
def api_cash_dashboard(
|
||
entity_id: int = Depends(get_entity_id),
|
||
month: str = Query(None, description="YYYY-MM 默认本月"),
|
||
days: int = Query(30),
|
||
current_cash: float = Query(None, description="当前现金余额(万元)"),
|
||
db: Session = Depends(get_db),
|
||
):
|
||
"""收付款计划页面看板 — 日历汇总 + 预测 + 到期提醒"""
|
||
today = datetime.now()
|
||
if month:
|
||
try:
|
||
y, m = int(month[:4]), int(month[5:7])
|
||
except Exception:
|
||
raise HTTPException(400, "month格式应为YYYY-MM")
|
||
else:
|
||
y, m = today.year, today.month
|
||
start = datetime(y, m, 1)
|
||
end = (start + timedelta(days=32)).replace(day=1)
|
||
|
||
plans = db.query(CashPlan).filter(
|
||
CashPlan.entity_id == entity_id,
|
||
CashPlan.status == "pending",
|
||
CashPlan.plan_date >= start,
|
||
CashPlan.plan_date < end,
|
||
).all()
|
||
|
||
# 日历：按天汇总 应收/应付
|
||
calendar = {}
|
||
for p in plans:
|
||
dkey = p.plan_date.strftime("%Y-%m-%d")
|
||
cell = calendar.setdefault(dkey, {"receive": 0.0, "pay": 0.0, "items": []})
|
||
if p.plan_type == "receive":
|
||
cell["receive"] += p.amount
|
||
else:
|
||
cell["pay"] += p.amount
|
||
cell["items"].append(_plan_dict(p))
|
||
|
||
# 本月合计
|
||
month_receive = round(sum(p.amount for p in plans if p.plan_type == "receive"), 2)
|
||
month_pay = round(sum(p.amount for p in plans if p.plan_type == "pay"), 2)
|
||
|
||
forecast = forecast_cash_flow_with_plans(entity_id, db, days=days, current_cash=current_cash)
|
||
upcoming = api_upcoming(entity_id, 7, db)
|
||
|
||
return {
|
||
"entity_id": entity_id,
|
||
"month": f"{y:04d}-{m:02d}",
|
||
"month_receive": month_receive,
|
||
"month_pay": month_pay,
|
||
"month_net": round(month_receive - month_pay, 2),
|
||
"calendar": calendar,
|
||
"forecast": forecast,
|
||
"upcoming": upcoming,
|
||
}
|
||
|
||
|
||
# ══════════════════════════════════════════════════════════
|
||
# 4. 资金预警 — 缺口前3天预警 + 到期未收款提醒
|
||
# ══════════════════════════════════════════════════════════
|
||
|
||
@router.post("/check-alerts")
|
||
def api_check_cash_alerts(entity_id: int = Depends(get_entity_id), db: Session = Depends(get_db)):
|
||
"""手动触发资金预警检查（写入预警中心kpi_alerts）"""
|
||
try:
|
||
return check_cash_alerts(db, entity_id=entity_id)
|
||
except Exception as e:
|
||
logger.error(f"资金预警检查失败: {e}", exc_info=True)
|
||
raise HTTPException(400, f"资金预警检查失败: {str(e)}")
|
||
|
||
|
||
@router.get("/alerts/status")
|
||
def api_cash_alert_status(entity_id: int = Depends(get_entity_id), db: Session = Depends(get_db)):
|
||
"""资金预警状态概览 — 当前缺口/逾期情况（不写库，只读）"""
|
||
result = forecast_cash_flow_with_plans(entity_id, db, days=30)
|
||
return {
|
||
"entity_id": entity_id,
|
||
"warning_line": result["warning_line"],
|
||
"critical_line": result["critical_line"],
|
||
"gap_dates": result["gap_dates"],
|
||
"pre_alerts": result["pre_alerts"],
|
||
"min_cash": result["min_cash"],
|
||
"min_cash_date": result["min_cash_date"],
|
||
"summary": result["summary"],
|
||
}
|
||
|
||
|
||
# ══════════════════════════════════════════════════════════
|
||
# 5. 应收催收闭环 — 催收视图 / 回款登记 / 博海数据录入
|
||
# 唯一应收载体：cash_plans(plan_type=receive)
|
||
# ══════════════════════════════════════════════════════════
|
||
|
||
# 博海应收款汇总表（2026H1期末余额，按业务员）— 录入源数据
|
||
# 来源: 应收款汇总表(业务员+客户).xlsx / bohai_comprehensive_report.md
|
||
BOHAI_AR_DATA = [
|
||
# (业务员, 期末余额元, 业务说明)
|
||
("董均国", 387676, "IT业务-重点催收"),
|
||
("蒋亚文", 529786, "IT业务-重点催收"),
|
||
("陈艳", 89949, "IT业务"),
|
||
("李亚玲", 92450, "IT业务"),
|
||
("李巧玲", 45006, "IT业务"),
|
||
("贾妮", 27820, "IT业务"),
|
||
("王平安", 31298, "IT业务"),
|
||
("任富海", 26978, "IT业务"),
|
||
("其他", 6559, "IT业务"),
|
||
("王婧", 1065000, "酣客酒类(独立核算)"),
|
||
]
|
||
|
||
|
||
def _resolve_plan_alerts(db: Session, entity_id: int, plan_id: int, amount: float) -> int:
|
||
"""回款登记后自动消除该计划的【到期未收款】预警（kpi_alerts.alert_type=cash_plan）"""
|
||
from app.models import KPIAlert
|
||
alerts = db.query(KPIAlert).filter(
|
||
KPIAlert.alert_type == "cash_plan",
|
||
KPIAlert.status.in_(["pending", "processing"]),
|
||
).all()
|
||
resolved = 0
|
||
for a in alerts:
|
||
try:
|
||
sug = json.loads(a.suggestion or "{}")
|
||
except Exception:
|
||
continue
|
||
if sug.get("plan_id") == plan_id:
|
||
a.status = "resolved"
|
||
a.resolution = f"回款登记+{amount:.2f}万，系统自动消除"
|
||
a.resolved_at = datetime.now()
|
||
resolved += 1
|
||
if resolved:
|
||
db.commit()
|
||
return resolved
|
||
|
||
|
||
def _sync_collection_action_plan(db: Session, entity_id: int):
|
||
"""逾期应收 → 『应收账款催收行动』联动：有逾期→in_progress，全部结清→completed"""
|
||
from app.models import ActionPlan
|
||
today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
|
||
plans = db.query(CashPlan).filter(
|
||
CashPlan.entity_id == entity_id,
|
||
CashPlan.plan_type == "receive",
|
||
).all()
|
||
pending = [p for p in plans if p.status == "pending"]
|
||
overdue = [p for p in pending if p.plan_date < today]
|
||
total_amount = sum(p.amount or 0 for p in plans)
|
||
paid_amount = sum(p.paid_amount or 0 for p in plans)
|
||
progress = round(paid_amount / total_amount * 100) if total_amount > 0 else 0
|
||
if overdue:
|
||
new_status = "in_progress"
|
||
elif not pending:
|
||
new_status = "completed"
|
||
progress = 100
|
||
else:
|
||
new_status = "in_progress"
|
||
|
||
ap = db.query(ActionPlan).filter(
|
||
ActionPlan.title.like("%催收%"),
|
||
ActionPlan.status != "cancelled",
|
||
).order_by(ActionPlan.id.asc()).first()
|
||
if not ap:
|
||
return
|
||
changed = False
|
||
if ap.status != new_status:
|
||
ap.status = new_status
|
||
changed = True
|
||
if ap.progress is None or progress > (ap.progress or 0):
|
||
ap.progress = progress
|
||
changed = True
|
||
if changed:
|
||
db.commit()
|
||
logger.info(f"催收行动#{ap.id} 联动: status={new_status} progress={progress}%")
|
||
|
||
|
||
def _update_ar_days_kpi(db: Session, entity_id: int):
|
||
"""联动F_AR_DAYS（应收周转天数KPI）— 按当前应收余额重算当期值 = 应收余额/月营收×30"""
|
||
from app.models import KPIDefinition, KPIValue
|
||
kpi = db.query(KPIDefinition).filter(
|
||
KPIDefinition.entity_id == entity_id,
|
||
KPIDefinition.kpi_code == "F_AR_DAYS",
|
||
).first()
|
||
if not kpi:
|
||
return
|
||
plans = db.query(CashPlan).filter(
|
||
CashPlan.entity_id == entity_id,
|
||
CashPlan.plan_type == "receive",
|
||
CashPlan.status == "pending",
|
||
).all()
|
||
ar_balance_wan = round(sum((p.amount or 0) - (p.paid_amount or 0) for p in plans), 2)
|
||
period = datetime.now().strftime("%Y-%m")
|
||
monthly_rev = 100.0 # 缺省月营收(万元)
|
||
rev_kpi = db.query(KPIDefinition).filter(
|
||
KPIDefinition.entity_id == entity_id,
|
||
KPIDefinition.kpi_code == "F_REVENUE",
|
||
).first()
|
||
if rev_kpi:
|
||
val = db.query(KPIValue).filter(
|
||
KPIValue.kpi_id == rev_kpi.id,
|
||
KPIValue.period == period,
|
||
KPIValue.actual_value.isnot(None),
|
||
).order_by(KPIValue.id.desc()).first()
|
||
if val and val.actual_value:
|
||
monthly_rev = float(val.actual_value)
|
||
ar_days = round(ar_balance_wan / monthly_rev * 30, 1) if monthly_rev > 0 else None
|
||
if ar_days is None:
|
||
return
|
||
remark = f"应收催收闭环联动: 应收余额{ar_balance_wan:.2f}万/月营收{monthly_rev:.1f}万×30天"
|
||
existing = db.query(KPIValue).filter(
|
||
KPIValue.kpi_id == kpi.id,
|
||
KPIValue.period == period,
|
||
).order_by(KPIValue.id.desc()).first()
|
||
if existing:
|
||
existing.actual_value = ar_days
|
||
existing.source_type = "cash_plan"
|
||
existing.remark = remark
|
||
existing.calculated_at = datetime.now()
|
||
else:
|
||
db.add(KPIValue(
|
||
kpi_id=kpi.id,
|
||
period=period,
|
||
actual_value=ar_days,
|
||
source_type="cash_plan",
|
||
data_status="calculated",
|
||
remark=remark,
|
||
))
|
||
db.commit()
|
||
logger.info(f"F_AR_DAYS联动更新: {period} = {ar_days}天 (应收{ar_balance_wan}万)")
|
||
|
||
|
||
@router.get("/receivables")
|
||
def api_receivables(
|
||
entity_id: int = Depends(get_entity_id),
|
||
owner: str = Query(None, description="按负责人/业务员筛选"),
|
||
status: str = Query(None, description="pending/completed/cancelled/overdue"),
|
||
aging: str = Query(None, description="账龄: 未到期/0-30天/30-60天/60-90天/90天以上/已结清"),
|
||
keyword: str = Query(None, description="客户/说明关键字"),
|
||
db: Session = Depends(get_db),
|
||
):
|
||
"""应收催收视图 — 应收余额 + 账龄(30/60/90) + 逾期状态 + 负责人筛选"""
|
||
query = db.query(CashPlan).filter(
|
||
CashPlan.entity_id == entity_id,
|
||
CashPlan.plan_type == "receive",
|
||
)
|
||
if owner:
|
||
query = query.filter(CashPlan.owner == owner)
|
||
if keyword:
|
||
kw = f"%{keyword}%"
|
||
query = query.filter(or_(CashPlan.counterparty.like(kw), CashPlan.description.like(kw)))
|
||
|
||
plans = query.order_by(CashPlan.plan_date.asc(), CashPlan.id.asc()).all()
|
||
items = []
|
||
total_amount = total_paid = 0.0
|
||
pending_balance = 0.0
|
||
overdue_count = 0
|
||
overdue_amount = 0.0
|
||
aging_amount = {}
|
||
completed_amount = 0.0
|
||
for p in plans:
|
||
d = _plan_dict(p)
|
||
if status:
|
||
if status == "overdue":
|
||
if not d["overdue"]:
|
||
continue
|
||
elif d["status"] != status:
|
||
continue
|
||
if aging and d["aging_bucket"] != aging:
|
||
continue
|
||
items.append(d)
|
||
total_amount += d["amount"]
|
||
total_paid += d["paid_amount"]
|
||
if d["status"] == "pending":
|
||
pending_balance += d["receivable_balance"]
|
||
if d["overdue"]:
|
||
overdue_count += 1
|
||
overdue_amount += d["receivable_balance"]
|
||
if d["status"] == "pending":
|
||
aging_amount[d["aging_bucket"]] = aging_amount.get(d["aging_bucket"], 0) + d["receivable_balance"]
|
||
elif d["status"] == "completed":
|
||
completed_amount += d["amount"]
|
||
|
||
owners = [r[0] for r in db.query(CashPlan.owner).filter(
|
||
CashPlan.entity_id == entity_id,
|
||
CashPlan.plan_type == "receive",
|
||
CashPlan.owner.isnot(None),
|
||
CashPlan.owner != "",
|
||
).distinct().order_by(CashPlan.owner.asc()).all()]
|
||
|
||
return {
|
||
"entity_id": entity_id,
|
||
"total": len(items),
|
||
"owners": owners,
|
||
"summary": {
|
||
"total_amount": round(total_amount, 2),
|
||
"total_paid": round(total_paid, 2),
|
||
"total_balance": round(total_amount - total_paid, 2),
|
||
"pending_balance": round(pending_balance, 2),
|
||
"overdue_count": overdue_count,
|
||
"overdue_amount": round(overdue_amount, 2),
|
||
"completed_amount": round(completed_amount, 2),
|
||
"aging": {k: round(v, 2) for k, v in sorted(aging_amount.items(), key=lambda x: x[0])},
|
||
},
|
||
"data": items,
|
||
}
|
||
|
||
|
||
@router.post("/receivables/{plan_id}/payment")
|
||
def api_register_payment(plan_id: int, data: dict, db: Session = Depends(get_db)):
|
||
"""回款登记 — 更新计划状态 + 自动消除到期未收款预警 + 联动催收行动/F_AR_DAYS"""
|
||
plan = db.query(CashPlan).filter(CashPlan.id == plan_id).first()
|
||
if not plan:
|
||
raise HTTPException(404, "应收计划不存在")
|
||
if plan.plan_type != "receive":
|
||
raise HTTPException(400, "仅应收(receive)计划支持回款登记")
|
||
if plan.status == "cancelled":
|
||
raise HTTPException(400, "已取消的计划不能登记回款")
|
||
amount = float(data.get("amount", 0))
|
||
if amount <= 0:
|
||
raise HTTPException(400, "回款金额必须大于0")
|
||
paid_date_str = str(data.get("paid_date") or "")[:10]
|
||
if not paid_date_str:
|
||
paid_date_str = datetime.now().strftime("%Y-%m-%d")
|
||
try:
|
||
paid_date = datetime.strptime(paid_date_str, "%Y-%m-%d")
|
||
except Exception:
|
||
raise HTTPException(400, "paid_date格式应为YYYY-MM-DD")
|
||
|
||
balance = round((plan.amount or 0) - (plan.paid_amount or 0), 2)
|
||
if amount > balance + 1e-9:
|
||
raise HTTPException(400, f"回款金额{amount}万超过应收余额{balance}万")
|
||
|
||
plan.paid_amount = round((plan.paid_amount or 0) + amount, 2)
|
||
if plan.paid_amount >= (plan.amount or 0) - 1e-9:
|
||
plan.paid_amount = plan.amount
|
||
plan.status = "completed"
|
||
plan.completed_at = paid_date
|
||
else:
|
||
plan.status = "pending"
|
||
plan.completed_at = None
|
||
db.commit()
|
||
|
||
entity_id = plan.entity_id
|
||
resolved = _resolve_plan_alerts(db, entity_id, plan.id, amount)
|
||
_sync_collection_action_plan(db, entity_id)
|
||
_update_ar_days_kpi(db, entity_id)
|
||
db.commit()
|
||
db.refresh(plan)
|
||
logger.info(f"回款登记 #{plan.id} {plan.counterparty or ''} +{amount}万 → {plan.status}, 消除预警{resolved}条")
|
||
return {
|
||
"message": "回款登记成功",
|
||
"resolved_alerts": resolved,
|
||
"data": _plan_dict(plan),
|
||
}
|
||
|
||
|
||
@router.post("/import/bohai-ar")
|
||
def api_import_bohai_ar(data: dict = None, entity_id: int = Depends(get_entity_id), db: Session = Depends(get_db)):
|
||
"""录入博海应收汇总表数据(¥2.3M, 按业务员)到cash_plans — 幂等，重复调用不重复导入"""
|
||
if data is None:
|
||
data = {}
|
||
existing = db.query(CashPlan).filter(
|
||
CashPlan.source == "bohai_ar",
|
||
CashPlan.entity_id == entity_id,
|
||
).count()
|
||
if existing and not data.get("force"):
|
||
return {"message": "博海应收已录入，未重复导入", "existing": existing, "imported": 0}
|
||
if existing:
|
||
db.query(CashPlan).filter(
|
||
CashPlan.source == "bohai_ar",
|
||
CashPlan.entity_id == entity_id,
|
||
).delete()
|
||
db.commit()
|
||
plan_date = datetime(2026, 6, 30) # 2026H1期末余额基准日
|
||
imported = 0
|
||
total_wan = 0.0
|
||
for sp, yuan, note in BOHAI_AR_DATA:
|
||
wan = round(yuan / 10000, 2)
|
||
total_wan += wan
|
||
db.add(CashPlan(
|
||
entity_id=entity_id,
|
||
plan_type="receive",
|
||
amount=wan,
|
||
plan_date=plan_date,
|
||
counterparty=sp,
|
||
description=f"博海应收款汇总表2026H1期末余额({note})",
|
||
status="pending",
|
||
owner=sp,
|
||
source="bohai_ar",
|
||
paid_amount=0,
|
||
))
|
||
imported += 1
|
||
db.commit()
|
||
# 触发预警检查生成到期未收款预警 + 联动催收行动/F_AR_DAYS
|
||
try:
|
||
check_cash_alerts(db, entity_id=entity_id)
|
||
except Exception as e:
|
||
logger.warning(f"导入后预警检查失败: {e}")
|
||
_sync_collection_action_plan(db, entity_id)
|
||
_update_ar_days_kpi(db, entity_id)
|
||
db.commit()
|
||
return {
|
||
"message": "博海应收已录入",
|
||
"imported": imported,
|
||
"total_amount_wan": round(total_wan, 2),
|
||
"entity_id": entity_id,
|
||
}
|
||
|
||
|
||
# ══════════════════════════════════════════════════════════
|
||
# 6. 网银流水标准导入 — 三校验规则 + 现金流余额联动 (P1方案② 2026-08-28)
|
||
# 模板列: 凭证日期/凭证号/科目编码/科目名称/借方金额/贷方金额/摘要
|
||
# ══════════════════════════════════════════════════════════
|
||
|
||
_TEMPLATE_PATH = Path(__file__).resolve().parents[2] / "scripts" / "templates" / "网银流水导入模板.xlsx"
|
||
_TEMPLATE_PATH = str(_TEMPLATE_PATH) # FileResponse/os.path.exists 兼容 str
|
||
|
||
# 模板列别名映射（兼容中英文列名）
|
||
_VOUCHER_COL_ALIASES = {
|
||
"voucher_date": ["凭证日期", "日期", "voucher_date", "date"],
|
||
"voucher_no": ["凭证号", "凭证编号", "凭证字号", "voucher_no"],
|
||
"subject_code": ["科目编码", "科目代码", "subject_code", "code"],
|
||
"subject_name": ["科目名称", "subject_name", "name"],
|
||
"debit_amount": ["借方金额", "借方", "debit_amount", "debit"],
|
||
"credit_amount": ["贷方金额", "贷方", "credit_amount", "credit"],
|
||
"summary": ["摘要", "备注", "summary", "remark"],
|
||
}
|
||
|
||
|
||
def _resolve_voucher_cols(cols: list) -> dict:
|
||
"""列名归一化：返回 {字段: 实际列名}（不区分大小写/空格）"""
|
||
col_map = {}
|
||
for c in cols:
|
||
key = str(c).strip().lower()
|
||
if key and key not in col_map:
|
||
col_map[key] = str(c)
|
||
resolved = {}
|
||
for field, aliases in _VOUCHER_COL_ALIASES.items():
|
||
for alias in aliases:
|
||
if alias.lower() in col_map:
|
||
resolved[field] = col_map[alias.lower()]
|
||
break
|
||
return resolved
|
||
|
||
|
||
def _norm_str(raw) -> str:
|
||
"""单元格→干净字符串：NaN/None→空；整数float→去.0（如1002.0→1002）"""
|
||
if raw is None:
|
||
return ""
|
||
if isinstance(raw, float) and pd.isna(raw):
|
||
return ""
|
||
if isinstance(raw, (int, float)) and not isinstance(raw, bool):
|
||
f = float(raw)
|
||
return str(int(f)) if f.is_integer() else str(f)
|
||
s = str(raw).strip()
|
||
return "" if s.lower() in ("nan", "none") else s
|
||
|
||
|
||
def _is_carry_forward(summary: str, subject_name: str) -> bool:
|
||
"""结转行识别：摘要含'结转' 或 科目名称含'本年利润'/'结转'"""
|
||
return ("结转" in summary) or ("本年利润" in subject_name) or ("结转" in subject_name)
|
||
|
||
|
||
def _sync_cash_balance_from_ledger(db: Session, entity_id: int, batch: str) -> float:
|
||
"""货币资金类科目(1001/1002开头)期末余额 → set_current_cash_balance（万元）"""
|
||
from app.models import VoucherDetail
|
||
from app.utils.cash_forecast_engine import set_current_cash_balance
|
||
rows = db.query(VoucherDetail).filter(VoucherDetail.entity_id == entity_id).all()
|
||
balance_yuan = round(sum(
|
||
(r.debit_amount or 0) - (r.credit_amount or 0)
|
||
for r in rows
|
||
if r.carry_forward == 0
|
||
and r.subject_code
|
||
and (r.subject_code.startswith("1001") or r.subject_code.startswith("1002"))
|
||
), 2)
|
||
cash_wan = round(balance_yuan / 10000, 4)
|
||
set_current_cash_balance(db, cash_wan)
|
||
logger.info(f"网银流水导入[{batch}] 货币资金期末余额{balance_yuan}元 = {cash_wan}万元 → 现金余额联动")
|
||
return cash_wan
|
||
|
||
|
||
def _sync_cash_kpis(db: Session, entity_id: int, batch: str, periods: list) -> list:
|
||
"""现金流KPI联动：
|
||
① EXT_现金类KPI → 货币资金科目期末余额（元，与存量口径一致，source_type=ledger）
|
||
② F_CASH_SAFETY 现金安全垫（万元 = 货币资金余额 - 短期借款EXT_139，entity_id隔离，不存在则创建）
|
||
"""
|
||
from app.models import KPIDefinition, KPIValue, VoucherDetail
|
||
updates = []
|
||
period = periods[-1] if periods else datetime.now().strftime("%Y-%m")
|
||
|
||
rows = db.query(VoucherDetail).filter(VoucherDetail.entity_id == entity_id).all()
|
||
monetary = [
|
||
r for r in rows
|
||
if r.carry_forward == 0 and r.subject_code
|
||
and (r.subject_code.startswith("1001") or r.subject_code.startswith("1002"))
|
||
]
|
||
balance_yuan = round(sum((r.debit_amount or 0) - (r.credit_amount or 0) for r in monetary), 2)
|
||
cash_1001 = round(sum((r.debit_amount or 0) - (r.credit_amount or 0)
|
||
for r in monetary if r.subject_code.startswith("1001")), 2)
|
||
cash_1002 = round(sum((r.debit_amount or 0) - (r.credit_amount or 0)
|
||
for r in monetary if r.subject_code.startswith("1002")), 2)
|
||
cash_wan = round(balance_yuan / 10000, 2)
|
||
|
||
# ── ① EXT_现金类KPI（名称含'现金'/'货币资金'，active；排除F_CASH_SAFETY，由②专用逻辑按万元口径处理）──
|
||
cash_kpis = db.query(KPIDefinition).filter(
|
||
KPIDefinition.entity_id == entity_id,
|
||
or_(KPIDefinition.kpi_name.like("%现金%"), KPIDefinition.kpi_name.like("%货币资金%")),
|
||
KPIDefinition.status == "active",
|
||
KPIDefinition.kpi_code != "F_CASH_SAFETY",
|
||
).all()
|
||
for k in cash_kpis:
|
||
if "库存现金" in k.kpi_name:
|
||
val = cash_1001
|
||
elif "银行" in k.kpi_name:
|
||
val = cash_1002
|
||
else:
|
||
val = balance_yuan
|
||
remark = f"网银流水导入[{batch}]联动: 货币资金科目期末余额{balance_yuan}元(库存现金{cash_1001}/银行存款{cash_1002})"
|
||
existing = db.query(KPIValue).filter(
|
||
KPIValue.kpi_id == k.id,
|
||
KPIValue.period == period,
|
||
KPIValue.source_type == "ledger",
|
||
).order_by(KPIValue.id.desc()).first()
|
||
if existing:
|
||
existing.actual_value = val
|
||
existing.source_batch = batch # type: ignore[assignment] # SQLAlchemy Column类型推断噪音
|
||
existing.remark = remark
|
||
existing.calculated_at = datetime.now()
|
||
else:
|
||
db.add(KPIValue(
|
||
entity_id=entity_id,
|
||
kpi_id=k.id,
|
||
period=period,
|
||
actual_value=val,
|
||
source_type="ledger",
|
||
source_batch=batch,
|
||
data_status="verified",
|
||
remark=remark,
|
||
))
|
||
updates.append({"kpi_code": k.kpi_code, "kpi_name": k.kpi_name, "period": period, "value": val})
|
||
|
||
# ── ② F_CASH_SAFETY 现金安全垫（万元）──
|
||
safety_kpi = db.query(KPIDefinition).filter(
|
||
KPIDefinition.entity_id == entity_id,
|
||
or_(KPIDefinition.kpi_code == "F_CASH_SAFETY", KPIDefinition.kpi_name.like("%安全垫%")),
|
||
).first()
|
||
if not safety_kpi:
|
||
safety_kpi = KPIDefinition(
|
||
entity_id=entity_id,
|
||
kpi_code="F_CASH_SAFETY",
|
||
kpi_name="现金安全垫",
|
||
dimension="finance",
|
||
category="cash_risk",
|
||
formula="货币资金余额-短期借款",
|
||
formula_desc="货币资金科目(1001/1002)期末余额 - 短期借款(EXT_139)，单位万元",
|
||
unit="万元",
|
||
target_value=0, # kpi_definitions.target_value NOT NULL DEFAULT 0.00，ORM显式传None会绕过默认值导致IntegrityError
|
||
data_source_type="ledger",
|
||
data_source="网银流水导入联动",
|
||
data_owner="财务Bot",
|
||
frequency="monthly",
|
||
status="active",
|
||
kpi_level="operational",
|
||
important_flag=1,
|
||
data_level="core",
|
||
epic="Epic2",
|
||
)
|
||
db.add(safety_kpi)
|
||
db.flush()
|
||
logger.info(f"新增KPI F_CASH_SAFETY 现金安全垫 (entity_id={entity_id})")
|
||
|
||
short_debt_wan = None
|
||
debt_kpi = db.query(KPIDefinition).filter(
|
||
KPIDefinition.entity_id == entity_id,
|
||
KPIDefinition.kpi_code == "EXT_139",
|
||
).first()
|
||
if debt_kpi:
|
||
dv = db.query(KPIValue).filter(
|
||
KPIValue.kpi_id == debt_kpi.id,
|
||
KPIValue.actual_value.isnot(None),
|
||
).order_by(KPIValue.period.desc()).first()
|
||
if dv and dv.actual_value is not None:
|
||
short_debt_wan = round(float(dv.actual_value) / 10000, 2) # EXT_139单位元
|
||
|
||
if short_debt_wan is not None:
|
||
safety_value = round(cash_wan - short_debt_wan, 2)
|
||
remark = f"网银流水导入[{batch}]联动: 货币资金{cash_wan}万 - 短期借款{short_debt_wan}万 = 安全垫{safety_value}万"
|
||
else:
|
||
safety_value = cash_wan
|
||
remark = f"网银流水导入[{batch}]联动: 无短期借款(EXT_139)数据，现金安全垫=货币资金余额{cash_wan}万"
|
||
existing = db.query(KPIValue).filter(
|
||
KPIValue.kpi_id == safety_kpi.id,
|
||
KPIValue.period == period,
|
||
KPIValue.source_type == "ledger",
|
||
).order_by(KPIValue.id.desc()).first()
|
||
if existing:
|
||
existing.actual_value = safety_value
|
||
existing.remark = remark
|
||
existing.calculated_at = datetime.now()
|
||
else:
|
||
db.add(KPIValue(
|
||
entity_id=entity_id,
|
||
kpi_id=safety_kpi.id,
|
||
period=period,
|
||
actual_value=safety_value,
|
||
source_type="ledger",
|
||
source_batch=batch,
|
||
data_status="verified",
|
||
remark=remark,
|
||
))
|
||
updates.append({"kpi_code": "F_CASH_SAFETY", "kpi_name": "现金安全垫", "period": period, "value": safety_value})
|
||
return updates
|
||
|
||
|
||
@router.get("/import/template")
|
||
def api_get_voucher_template():
|
||
"""下载网银流水导入模板xlsx"""
|
||
if not os.path.exists(_TEMPLATE_PATH):
|
||
raise HTTPException(404, "模板文件不存在，请联系管理员生成")
|
||
return FileResponse(
|
||
_TEMPLATE_PATH,
|
||
filename="网银流水导入模板.xlsx",
|
||
media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
|
||
)
|
||
|
||
|
||
@router.post("/import/vouchers")
|
||
async def api_import_vouchers(
|
||
file: UploadFile = File(...),
|
||
entity_id: int = Depends(get_entity_id),
|
||
db: Session = Depends(get_db),
|
||
):
|
||
"""网银流水标准导入 — 三校验(借贷平衡/期间合计/结转行) → voucher_details/import_logs → 现金流余额联动"""
|
||
from app.models import VoucherDetail, ImportLog
|
||
|
||
content = await file.read()
|
||
fname = (file.filename or "网银流水.xlsx").strip()
|
||
try:
|
||
df = pd.read_excel(io.BytesIO(content))
|
||
except Exception as e:
|
||
raise HTTPException(400, f"无法读取Excel文件: {e}")
|
||
if df is None or len(df) == 0:
|
||
raise HTTPException(400, "Excel文件为空（无数据行）")
|
||
|
||
colmap = _resolve_voucher_cols(list(df.columns))
|
||
missing = [f for f in ("voucher_date", "voucher_no", "subject_code") if f not in colmap]
|
||
if missing:
|
||
raise HTTPException(400, f"缺少必要列: {', '.join(missing)}（模板列: 凭证日期/凭证号/科目编码/科目名称/借方金额/贷方金额/摘要）")
|
||
|
||
# ── 逐行校验 ──
|
||
errors = []
|
||
valid_rows = []
|
||
for idx, row in df.iterrows():
|
||
excel_row = idx + 2 # 表头占第1行
|
||
raw_date = row.get(colmap["voucher_date"])
|
||
if raw_date is None or (isinstance(raw_date, float) and pd.isna(raw_date)):
|
||
errors.append({"row": excel_row, "field": "voucher_date", "reason": "凭证日期为空"})
|
||
continue
|
||
try:
|
||
voucher_date = pd.to_datetime(raw_date).to_pydatetime()
|
||
except Exception:
|
||
errors.append({"row": excel_row, "field": "voucher_date", "reason": f"日期无法解析: {raw_date}"})
|
||
continue
|
||
|
||
voucher_no = _norm_str(row.get(colmap["voucher_no"]))
|
||
if not voucher_no:
|
||
errors.append({"row": excel_row, "field": "voucher_no", "reason": "凭证号为空"})
|
||
continue
|
||
subject_code = _norm_str(row.get(colmap["subject_code"]))
|
||
if not subject_code:
|
||
errors.append({"row": excel_row, "field": "subject_code", "reason": "科目编码为空"})
|
||
continue
|
||
subject_name = _norm_str(row.get(colmap["subject_name"]) if "subject_name" in colmap else "")
|
||
if not subject_name:
|
||
errors.append({"row": excel_row, "field": "subject_name", "reason": "科目名称为空"})
|
||
continue
|
||
|
||
def _parse_amount(raw) -> float:
|
||
"""金额解析：空→0；数字→float；字符串去逗号→float；失败→None"""
|
||
if raw is None or (isinstance(raw, float) and pd.isna(raw)):
|
||
return 0.0
|
||
if isinstance(raw, (int, float)) and not isinstance(raw, bool):
|
||
return float(raw)
|
||
s = str(raw).strip().replace(",", "")
|
||
try:
|
||
return float(s)
|
||
except Exception:
|
||
return None
|
||
|
||
debit = _parse_amount(row.get(colmap["debit_amount"]) if "debit_amount" in colmap else None)
|
||
credit = _parse_amount(row.get(colmap["credit_amount"]) if "credit_amount" in colmap else None)
|
||
if debit is None:
|
||
errors.append({"row": excel_row, "field": "debit_amount", "reason": f"借方金额不是有效数字: {row.get(colmap['debit_amount'])}"})
|
||
continue
|
||
if credit is None:
|
||
errors.append({"row": excel_row, "field": "credit_amount", "reason": f"贷方金额不是有效数字: {row.get(colmap['credit_amount'])}"})
|
||
continue
|
||
if debit < 0 or credit < 0:
|
||
errors.append({"row": excel_row, "field": "amount", "reason": "金额不能为负"})
|
||
continue
|
||
if debit == 0 and credit == 0:
|
||
errors.append({"row": excel_row, "field": "amount", "reason": "借贷金额不能同时为0"})
|
||
continue
|
||
|
||
summary = _norm_str(row.get(colmap["summary"]) if "summary" in colmap else "")
|
||
carry_forward = 1 if _is_carry_forward(summary, subject_name) else 0
|
||
valid_rows.append({
|
||
"voucher_no": voucher_no,
|
||
"voucher_date": voucher_date,
|
||
"period": voucher_date.strftime("%Y-%m"),
|
||
"subject_code": subject_code,
|
||
"subject_name": subject_name,
|
||
"debit_amount": round(debit, 2),
|
||
"credit_amount": round(credit, 2),
|
||
"summary": summary,
|
||
"carry_forward": carry_forward,
|
||
})
|
||
|
||
total = len(df)
|
||
success = len(valid_rows)
|
||
failed = len(errors)
|
||
|
||
# ── 校验规则① 借贷平衡（Σ借 vs Σ贷，容差0.01）──
|
||
debit_total = round(sum(r["debit_amount"] for r in valid_rows), 2)
|
||
credit_total = round(sum(r["credit_amount"] for r in valid_rows), 2)
|
||
diff = round(debit_total - credit_total, 2)
|
||
balance_ok = abs(diff) <= 0.01
|
||
balance_check = {
|
||
"passed": balance_ok,
|
||
"debit_total": debit_total,
|
||
"credit_total": credit_total,
|
||
"diff": diff,
|
||
"tolerance": 0.01,
|
||
}
|
||
if not balance_ok:
|
||
errors.append({"row": 0, "field": "balance", "reason": f"借贷不平衡: 借方合计{debit_total} ≠ 贷方合计{credit_total}，差额{diff}"})
|
||
|
||
# ── 校验规则② 期间合计（按period汇总，供对账）──
|
||
period_totals = {}
|
||
for r in valid_rows:
|
||
pt = period_totals.setdefault(r["period"], {"debit_total": 0.0, "credit_total": 0.0})
|
||
pt["debit_total"] = round(pt["debit_total"] + r["debit_amount"], 2)
|
||
pt["credit_total"] = round(pt["credit_total"] + r["credit_amount"], 2)
|
||
|
||
# ── 校验规则③ 结转行 ──
|
||
carry_forward_count = sum(1 for r in valid_rows if r["carry_forward"])
|
||
|
||
# ── 入库（部分成功模式：失败行不阻断整体）──
|
||
batch = f"{os.path.splitext(fname)[0]}_{datetime.now().strftime('%Y%m%d%H%M%S')}"
|
||
for r in valid_rows:
|
||
db.add(VoucherDetail(
|
||
entity_id=entity_id,
|
||
voucher_no=r["voucher_no"],
|
||
voucher_date=r["voucher_date"],
|
||
subject_code=r["subject_code"],
|
||
subject_name=r["subject_name"],
|
||
debit_amount=r["debit_amount"],
|
||
credit_amount=r["credit_amount"],
|
||
summary=r["summary"],
|
||
carry_forward=r["carry_forward"],
|
||
period=r["period"],
|
||
batch=batch,
|
||
))
|
||
periods_in = sorted(set(r["period"] for r in valid_rows))
|
||
db.add(ImportLog(
|
||
entity_id=entity_id,
|
||
filename=fname,
|
||
batch=batch,
|
||
total_rows=total,
|
||
success_rows=success,
|
||
failed_rows=failed,
|
||
errors=errors or None,
|
||
period=periods_in[0] if periods_in else None,
|
||
import_type="vouchers",
|
||
created_by="finance-bot",
|
||
))
|
||
db.commit()
|
||
logger.info(f"网银流水导入[{batch}] entity={entity_id}: 总{total}/成功{success}/失败{failed}, 借贷平衡={'通过' if balance_ok else '失败'}")
|
||
|
||
# ── 现金流联动 ──
|
||
cash_balance = None
|
||
kpi_updates = []
|
||
try:
|
||
cash_balance = _sync_cash_balance_from_ledger(db, entity_id, batch)
|
||
kpi_updates = _sync_cash_kpis(db, entity_id, batch, periods_in)
|
||
db.commit()
|
||
except Exception as e:
|
||
db.rollback()
|
||
logger.error(f"网银流水导入[{batch}] 现金流联动失败: {e}", exc_info=True)
|
||
|
||
return {
|
||
"success": True,
|
||
"total": total,
|
||
"success_rows": success,
|
||
"failed_rows": failed,
|
||
"errors": errors,
|
||
"balance_check": balance_check,
|
||
"period_totals": period_totals,
|
||
"carry_forward_count": carry_forward_count,
|
||
"cash_balance": cash_balance,
|
||
"kpi_updates": kpi_updates,
|
||
"batch": batch,
|
||
"entity_id": entity_id,
|
||
}
|