Hermes CI Fix
fb9eba38a8
- 12表加entity_id列(预算/偏差/规则/成本4表/费用2表/BI2表/驱动预算) - 模型: BudgetPlan/StandardCost/ActualCost/AbcActivity/AbcAllocation/DriverFactorBudget/BiReport/Template/BudgetDeviationAlert/ExpenseRule/Reimbursement/AlertRule - API隔离: budget plans / cost standard+actual / expenses rules+reimb / bi_reports list / alert_rules list 按token企业过滤 - 回填: kpi_id关联按KPI归属, 无关联默认酣客(entity=1); 当前数据全归酣客 - 验证: import+全端点200+pytest 451 passed
320 lines
11 KiB
Python
320 lines
11 KiB
Python
"""BI报表集成 — 任务4
|
||
分析模式 + 预置报表模板 + 报表保存/分享 + 导出
|
||
"""
|
||
from fastapi import APIRouter, Depends, HTTPException, Query
|
||
from fastapi.responses import Response
|
||
from sqlalchemy.orm import Session
|
||
from typing import Optional, List
|
||
from datetime import datetime
|
||
import json
|
||
import logging
|
||
|
||
from app.database import get_db
|
||
from app.deps import get_entity_id
|
||
from app.auth_middleware import require_auth, require_role
|
||
from app.models import KPIDefinition, KPIValue, BiReportTemplate, BiReport, OperationLog, KPICausality
|
||
|
||
logger = logging.getLogger("bi-reports")
|
||
|
||
router = APIRouter(prefix="/api/cma/bi-reports", tags=["BI报表"],
|
||
dependencies=[Depends(require_role("ceo", "finance", "business", "it"))],
|
||
)
|
||
WRITE_ROLES = Depends(require_role("ceo", "finance", "it"))
|
||
|
||
|
||
# ============================================================
|
||
# 预置模板
|
||
# ============================================================
|
||
|
||
PRESET_TEMPLATES = [
|
||
{
|
||
"name": "四层指标总览",
|
||
"report_type": "overview",
|
||
"is_system": 1,
|
||
"config": {
|
||
"description": "展示财务/客户/流程/学习四层维度的关键KPI概览",
|
||
"layout": "grid",
|
||
"dimensions": ["finance", "customer", "process", "learning"],
|
||
"metrics": ["count", "avg_value", "alert_count"],
|
||
"chart_type": "gauge_card",
|
||
}
|
||
},
|
||
{
|
||
"name": "同比趋势分析",
|
||
"report_type": "trend",
|
||
"is_system": 1,
|
||
"config": {
|
||
"description": "各KPI近12个月趋势对比",
|
||
"period": "monthly",
|
||
"window_months": 12,
|
||
"chart_type": "line",
|
||
"show_compare": True,
|
||
}
|
||
},
|
||
{
|
||
"name": "实际vs预算对比",
|
||
"report_type": "comparison",
|
||
"is_system": 1,
|
||
"config": {
|
||
"description": "KPI实际值 vs 目标值的偏差分析",
|
||
"chart_type": "bar",
|
||
"show_deviation": True,
|
||
"group_by": "dimension",
|
||
}
|
||
},
|
||
{
|
||
"name": "TOP N异常KPI",
|
||
"report_type": "topn",
|
||
"is_system": 1,
|
||
"config": {
|
||
"description": "排名前N的异常KPI（红/黄灯）",
|
||
"top_n": 10,
|
||
"sort_by": "deviation",
|
||
"chart_type": "horizontal_bar",
|
||
"show_threshold": True,
|
||
}
|
||
},
|
||
{
|
||
"name": "因果链推演",
|
||
"report_type": "causality",
|
||
"is_system": 1,
|
||
"config": {
|
||
"description": "基于KPI因果链的推演分析",
|
||
"chart_type": "force_graph",
|
||
"max_depth": 3,
|
||
"min_strength": 0.3,
|
||
}
|
||
},
|
||
]
|
||
|
||
|
||
@router.get("/templates")
|
||
def list_report_templates(db: Session = Depends(get_db)):
|
||
"""获取BI报表模板"""
|
||
templates = db.query(BiReportTemplate).order_by(BiReportTemplate.id).all()
|
||
return {"data": [{c.name: getattr(t, c.name) for c in BiReportTemplate.__table__.columns} for t in templates]}
|
||
|
||
|
||
@router.post("/templates/seed")
|
||
def seed_report_templates(db: Session = Depends(get_db), user=WRITE_ROLES):
|
||
"""初始化预置模板（仅首次运行）"""
|
||
created = 0
|
||
for tpl in PRESET_TEMPLATES:
|
||
existing = db.query(BiReportTemplate).filter(
|
||
BiReportTemplate.name == tpl["name"],
|
||
BiReportTemplate.is_system == 1,
|
||
).first()
|
||
if existing:
|
||
continue
|
||
t = BiReportTemplate(**tpl)
|
||
db.add(t)
|
||
created += 1
|
||
db.commit()
|
||
return {"message": f"新增{created}个预置模板", "created": created}
|
||
|
||
|
||
@router.delete("/templates/{template_id}")
|
||
def delete_template(template_id: int, db: Session = Depends(get_db), user=WRITE_ROLES):
|
||
t = db.query(BiReportTemplate).filter(BiReportTemplate.id == template_id).first()
|
||
if t:
|
||
db.delete(t)
|
||
db.commit()
|
||
return {"message": "已删除"}
|
||
|
||
|
||
# ============================================================
|
||
# 用户报表
|
||
# ============================================================
|
||
|
||
@router.get("")
|
||
def list_reports(db: Session = Depends(get_db), entity_id: int = Depends(get_entity_id)):
|
||
"""获取用户保存的报表（账套隔离: 按token企业, 2026-08-23 P2）"""
|
||
reports = db.query(BiReport).filter(BiReport.entity_id == entity_id).order_by(BiReport.updated_at.desc()).all()
|
||
result = []
|
||
for r in reports:
|
||
d = {c.name: getattr(r, c.name) for c in BiReport.__table__.columns}
|
||
d["created_by_name"] = f"用户{r.created_by}" if r.created_by else "系统"
|
||
result.append(d)
|
||
return {"data": result, "total": len(result)}
|
||
|
||
|
||
@router.get("/{report_id}")
|
||
def get_report(report_id: int, db: Session = Depends(get_db)):
|
||
r = db.query(BiReport).filter(BiReport.id == report_id).first()
|
||
if not r:
|
||
raise HTTPException(404, "报表不存在")
|
||
return {c.name: getattr(r, c.name) for c in BiReport.__table__.columns}
|
||
|
||
|
||
@router.post("")
|
||
def create_report(data: dict, db: Session = Depends(get_db), user=WRITE_ROLES):
|
||
"""保存BI报表"""
|
||
r = BiReport(
|
||
template_id=data.get("template_id"),
|
||
name=data.get("name", "未命名报表"),
|
||
config=data.get("config", {}),
|
||
chart_type=data.get("chart_type", "auto"),
|
||
is_shared=data.get("is_shared", 0),
|
||
created_by=1,
|
||
)
|
||
db.add(r)
|
||
db.commit()
|
||
db.refresh(r)
|
||
db.add(OperationLog(action="create", target_type="bi_report", detail=f"创建报表: {r.name}"))
|
||
db.commit()
|
||
return {c.name: getattr(r, c.name) for c in BiReport.__table__.columns}
|
||
|
||
|
||
@router.put("/{report_id}")
|
||
def update_report(report_id: int, data: dict, db: Session = Depends(get_db), user=WRITE_ROLES):
|
||
r = db.query(BiReport).filter(BiReport.id == report_id).first()
|
||
if not r:
|
||
raise HTTPException(404, "报表不存在")
|
||
for field in ("name", "config", "chart_type", "is_shared"):
|
||
if field in data:
|
||
setattr(r, field, data[field])
|
||
db.commit()
|
||
return {c.name: getattr(r, c.name) for c in BiReport.__table__.columns}
|
||
|
||
|
||
@router.delete("/{report_id}")
|
||
def delete_report(report_id: int, db: Session = Depends(get_db), user=WRITE_ROLES):
|
||
r = db.query(BiReport).filter(BiReport.id == report_id).first()
|
||
if r:
|
||
db.delete(r)
|
||
db.commit()
|
||
return {"message": "已删除"}
|
||
|
||
|
||
# ============================================================
|
||
# 分析引擎
|
||
# ============================================================
|
||
|
||
@router.post("/analyze")
|
||
def analyze_data(data: dict, db: Session = Depends(get_db)):
|
||
"""分析引擎：按配置返回报表数据
|
||
Body: {
|
||
config: { dimensions, kpi_ids, period_start, period_end, group_by, metrics, ... },
|
||
chart_type: str
|
||
}
|
||
"""
|
||
config = data.get("config", {})
|
||
chart_type = data.get("chart_type", "auto")
|
||
|
||
kpi_ids = config.get("kpi_ids", [])
|
||
dimensions = config.get("dimensions", [])
|
||
period_start = config.get("period_start")
|
||
period_end = config.get("period_end")
|
||
group_by = config.get("group_by")
|
||
top_n = config.get("top_n", 10)
|
||
|
||
# 构建KPI查询
|
||
kpi_query = db.query(KPIDefinition).filter(KPIDefinition.status == "active")
|
||
if kpi_ids:
|
||
kpi_query = kpi_query.filter(KPIDefinition.id.in_(kpi_ids))
|
||
if dimensions:
|
||
kpi_query = kpi_query.filter(KPIDefinition.dimension.in_(dimensions))
|
||
kpis = kpi_query.order_by(KPIDefinition.kpi_code).all()
|
||
|
||
# 获取每个KPI的最新值
|
||
rows = []
|
||
for kpi in kpis:
|
||
val_query = db.query(KPIValue).filter(
|
||
KPIValue.kpi_id == kpi.id,
|
||
KPIValue.actual_value.isnot(None),
|
||
)
|
||
if period_start:
|
||
val_query = val_query.filter(KPIValue.period >= period_start)
|
||
if period_end:
|
||
val_query = val_query.filter(KPIValue.period <= period_end)
|
||
|
||
latest = val_query.order_by(KPIValue.period.desc()).first()
|
||
|
||
# 获取趋势数据
|
||
trend_values = val_query.order_by(KPIValue.period.asc()).limit(12).all()
|
||
|
||
rows.append({
|
||
"kpi_id": kpi.id,
|
||
"kpi_code": kpi.kpi_code,
|
||
"kpi_name": kpi.kpi_name,
|
||
"dimension": kpi.dimension,
|
||
"category": kpi.category,
|
||
"unit": kpi.unit,
|
||
"target_value": kpi.target_value,
|
||
"threshold_green": kpi.threshold_green,
|
||
"threshold_yellow": kpi.threshold_yellow,
|
||
"threshold_red": kpi.threshold_red,
|
||
"current_value": latest.actual_value if latest else None,
|
||
"current_period": latest.period if latest else None,
|
||
"trend": [{"period": v.period, "value": v.actual_value} for v in trend_values],
|
||
})
|
||
|
||
# 统计汇总
|
||
summary = {
|
||
"total_kpis": len(rows),
|
||
"dimensions": {},
|
||
}
|
||
for r in rows:
|
||
dim = r["dimension"]
|
||
if dim not in summary["dimensions"]:
|
||
summary["dimensions"][dim] = {"count": 0, "values": []}
|
||
summary["dimensions"][dim]["count"] += 1
|
||
if r["current_value"] is not None:
|
||
summary["dimensions"][dim]["values"].append(r["current_value"])
|
||
|
||
for dim, info in summary["dimensions"].items():
|
||
vals = info["values"]
|
||
if vals:
|
||
info["avg"] = round(sum(vals) / len(vals), 2)
|
||
info["min"] = min(vals)
|
||
info["max"] = max(vals)
|
||
del info["values"]
|
||
|
||
return {
|
||
"config": config,
|
||
"chart_type": chart_type,
|
||
"rows": rows,
|
||
"summary": summary,
|
||
}
|
||
|
||
|
||
# ============================================================
|
||
# 导出功能（CSV格式，前端可转为Excel/PDF）
|
||
# ============================================================
|
||
|
||
@router.post("/export")
|
||
def export_report(data: dict, db: Session = Depends(get_db)):
|
||
"""导出报表数据 (CSV)"""
|
||
config = data.get("config", {})
|
||
format_type = data.get("format", "csv")
|
||
|
||
# 复用analyze获取数据
|
||
from app.database import get_session_local
|
||
temp_db = get_session_local()()
|
||
try:
|
||
result = analyze_data(data, temp_db)
|
||
finally:
|
||
temp_db.close()
|
||
|
||
rows = result.get("rows", [])
|
||
if not rows:
|
||
raise HTTPException(400, "没有可导出的数据")
|
||
|
||
# 生成CSV
|
||
import csv, io
|
||
output = io.StringIO()
|
||
writer = csv.writer(output)
|
||
writer.writerow(["KPI编码", "KPI名称", "维度", "类别", "当前值", "期间", "目标值", "单位"])
|
||
for r in rows:
|
||
writer.writerow([
|
||
r["kpi_code"], r["kpi_name"], r["dimension"], r["category"],
|
||
r["current_value"], r["current_period"], r["target_value"], r["unit"],
|
||
])
|
||
|
||
csv_content = output.getvalue()
|
||
return Response(
|
||
content=csv_content,
|
||
media_type="text/csv",
|
||
headers={"Content-Disposition": f"attachment; filename=bi_report_{datetime.now().strftime('%Y%m%d')}.csv"},
|
||
)
|