jianwel/fa-agent
1
0
Fork 0
Code Issues Pull Requests 8 Actions Packages Projects Releases Wiki Activity

Compare commits

merge into: jianwel/fa-agent:feat/ai-model-config
Branches Tags
jianwel/fa-agent:main jianwel/fa-agent:docs/user-guide-and-gaps jianwel/fa-agent:feat/web-custom-columns jianwel/fa-agent:fix/formula-export-reliability jianwel/fa-agent:fix/evidence-display-ui jianwel/fa-agent:feat/ai-model-config jianwel/fa-agent:fix/remaining-issues-2026-09-26 jianwel/fa-agent:fix/dialogs-f03-f04 jianwel/fa-agent:fix/security-f01-f02-f08
...
pull from: jianwel/fa-agent:docs/user-guide-and-gaps
Branches Tags
jianwel/fa-agent:docs/user-guide-and-gaps jianwel/fa-agent:feat/web-custom-columns jianwel/fa-agent:fix/formula-export-reliability jianwel/fa-agent:fix/evidence-display-ui jianwel/fa-agent:feat/ai-model-config jianwel/fa-agent:main jianwel/fa-agent:fix/remaining-issues-2026-09-26 jianwel/fa-agent:fix/dialogs-f03-f04 jianwel/fa-agent:fix/security-f01-f02-f08
4 Commits
feat/ai-model-config ... docs/user-guide-and-gaps
Author SHA1 Message Date
Jayway Liu cd07ba1887 docs: 使用文档与未实现功能/测试指南
Verify / verify (push) Failing after 1s
Details
AI Review / review (pull_request) Successful in 14s
Details
Verify / verify (pull_request) Failing after 1s
Details 
- user-guide.html：面向使用者的完整操作说明（含 AI 模型配置）
- gaps-and-testing.html：未实现功能清单、F01-F08 状态、发布门禁与
  分层测试方法、验收矩阵、当前基线
- README 加入口链接
 2026-10-11 09:45:22 +08:00
Jayway Liu adce23bbd9 feat(analysis): 网页汇总表自定义列
Verify / verify (push) Failing after 1s
Details
AI Review / review (pull_request) Successful in 14s
Details
Verify / verify (pull_request) Failing after 0s
Details 
- analyze 支持 custom_columns：白名单表达式树由服务端 Decimal 求值，
  自定义列进入汇总、分组行与明细（缺失保持不可用，不转零）
- 新增「选择网页自定义列」弹窗：从定义库（含共享）多选最多 10 列，
  应用后进入主分析表格与热力图；公式工作簿可一键导出网页已选列
- 请求去重纳入自定义列；Playwright 覆盖选择后显示与数值断言
 2026-10-11 09:45:22 +08:00
Jayway Liu 509b406254 fix(formula): 导出文件安全、清理回收、撤权校验与定义库可靠性（F01/F02/F05-F08）
Verify / verify (push) Failing after 1s
Details
Verify / verify (pull_request) Failing after 1s
Details
AI Review / review (pull_request) Successful in 16s
Details 
- F01: _safe_unlink_export 不跟随符号链接、逐层校验父目录，导出失败路径与
  清理命令统一使用，只清理受控目录内本次创建的文件
- F02: 清理失败记录告警日志；清理命令回收 failed 任务残留文件
- F05: 定义保存事务内锁数据集行，配额原子检查；revision 冲突返回 409；
  保留最近 20 版历史；支持个人/团队共享；审计与变更同事务
- F06: 工作簿参数页毛利率改为只读快照；可编辑单元格解锁并加数值校验
- F07: 对账按指标分容差（人数 0、比率 0.0001、金额 0.01）；自定义列使用
  服务端 Decimal 表达式求值参考
- F08: 导出任务执行/下载阶段重校验账号有效性与角色
- 对话草稿会话恢复；载入定义后追问携带原定义（F04）；下载改浏览器直传
- 迁移 0006-0008；PostgreSQL 咨询锁语义修正（import_worker）
 2026-10-11 09:43:27 +08:00
Jayway Liu 494490f6e6 fix(display): 证据金额去尾零与界面显示修复
Verify / verify (push) Failing after 1s
Details
Verify / verify (pull_request) Failing after 0s
Details
AI Review / review (pull_request) Successful in 12s
Details 
- 证据接口与 CSV/XLSX 导出去掉 NUMERIC(44,24) 存储尺度尾零（无损精度）
- KPI 大金额折行截断：字号收紧 + nowrap + 省略号兜底
- 贡献分解/走势图表轴标签重叠与 Y 轴裁剪：hideOverlap + 万/亿缩写
- 表格隐藏全不可用指标列，空态改为条件渲染（修复空表覆盖层残留）
- 数据质量与字段字典/导入确认的中文标签映射（不再暴露内部键名）
- 证据弹窗重复点击当前期间停在加载中（F03）；证据表格最小宽度
- Playwright 覆盖 F03 重复点击与期间切换场景
 2026-10-11 09:41:31 +08:00
25 changed files with 1650 additions and 215 deletions
Expand all files Collapse all files
README.md
+1 -1
View File
@@ -1,6 +1,6 @@
# TraceIQ 财务分析工作台
响应式 Web 前端 + Django API + PostgreSQL。基于分享会话、医院财务 Excel 原始附件及 AG Grid 开发计划实现，当前交付是可运行、经过本地回归的核心业务版本，不等于全部需求完成或已获生产上线认证。缺口与上线门禁见 [验收状态](docs/acceptance.md)。
响应式 Web 前端 + Django API + PostgreSQL。基于分享会话、医院财务 Excel 原始附件及 AG Grid 开发计划实现，当前交付是可运行、经过本地回归的核心业务版本，不等于全部需求完成或已获生产上线认证。缺口与上线门禁见 [验收状态](docs/acceptance.md) 与 [未实现功能与测试指南](docs/gaps-and-testing.html)，使用者操作说明见 [使用文档](docs/user-guide.html)。
## 已实现
docs/gaps-and-testing.html
+322
View File
@@ -0,0 +1,322 @@
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>TraceIQ 未实现功能与测试指南</title>
<style>
:root {
--ink: #1a2332;
--muted: #5b6675;
--accent: #1f5fa8;
--accent-soft: #eaf2fb;
--line: #dfe5ec;
--bg: #f6f8fa;
--card: #ffffff;
--p0: #b3261e;
--p0-bg: #fdeceb;
--p1: #9a5b00;
--p1-bg: #fdf3e3;
--p2: #1f5fa8;
--p2-bg: #eaf2fb;
--ok: #1d6b4f;
--ok-bg: #e7f4ee;
}
* { box-sizing: border-box; }
body {
margin: 0;
font-family: -apple-system, "PingFang SC", "Microsoft YaHei", "Segoe UI", sans-serif;
color: var(--ink);
background: var(--bg);
line-height: 1.7;
}
header {
background: linear-gradient(135deg, #16324f, #1f5fa8);
color: #fff;
padding: 48px 24px 40px;
}
header .inner, main { max-width: 960px; margin: 0 auto; }
header h1 { margin: 0 0 8px; font-size: 28px; }
header p { margin: 0; opacity: .85; }
.layout { max-width: 1160px; margin: 0 auto; display: flex; gap: 32px; padding: 32px 24px 64px; }
nav.toc {
flex: 0 0 220px;
position: sticky; top: 24px; align-self: flex-start;
background: var(--card); border: 1px solid var(--line); border-radius: 10px;
padding: 16px 18px; font-size: 14px;
}
nav.toc strong { display: block; margin-bottom: 8px; }
nav.toc a { display: block; color: var(--accent); text-decoration: none; padding: 3px 0; }
nav.toc a:hover { text-decoration: underline; }
main { flex: 1; min-width: 0; }
section {
background: var(--card); border: 1px solid var(--line); border-radius: 10px;
padding: 24px 28px; margin-bottom: 24px;
}
h2 { font-size: 20px; margin: 0 0 12px; padding-bottom: 8px; border-bottom: 2px solid var(--accent-soft); }
h3 { font-size: 16px; margin: 20px 0 8px; }
p { margin: 8px 0; }
ul, ol { margin: 8px 0; padding-left: 22px; }
li { margin: 4px 0; }
code {
background: #eef1f5; border-radius: 4px; padding: 1px 6px;
font-family: "SF Mono", Menlo, Consolas, monospace; font-size: .92em;
}
pre {
background: #16202e; color: #e6edf3; border-radius: 8px;
padding: 14px 16px; overflow-x: auto; font-size: 13px; line-height: 1.6;
}
pre code { background: none; padding: 0; color: inherit; }
table { border-collapse: collapse; width: 100%; margin: 12px 0; font-size: 14px; }
th, td { border: 1px solid var(--line); padding: 8px 12px; text-align: left; vertical-align: top; }
th { background: var(--accent-soft); }
tr:nth-child(even) td { background: #fafcfe; }
.note {
background: var(--accent-soft); border-left: 4px solid var(--accent);
border-radius: 0 8px 8px 0; padding: 10px 16px; margin: 12px 0; font-size: 14px;
}
.warn {
background: var(--p1-bg); border-left: 4px solid var(--p1);
border-radius: 0 8px 8px 0; padding: 10px 16px; margin: 12px 0; font-size: 14px;
}
.tag {
display: inline-block; border-radius: 4px; padding: 0 8px;
font-size: 12px; font-weight: 600; line-height: 20px; white-space: nowrap;
}
.tag.p0 { background: var(--p0-bg); color: var(--p0); }
.tag.p1 { background: var(--p1-bg); color: var(--p1); }
.tag.p2 { background: var(--p2-bg); color: var(--p2); }
.tag.ok { background: var(--ok-bg); color: var(--ok); }
.tag.todo { background: #f1f3f5; color: var(--muted); }
footer { text-align: center; color: var(--muted); font-size: 13px; padding: 24px; }
@media (max-width: 860px) {
.layout { flex-direction: column; }
nav.toc { position: static; flex: none; }
}
</style>
</head>
<body>
<header>
<div class="inner">
<h1>TraceIQ 未实现功能与测试指南</h1>
<p>距离生产商用还差什么 · 每一项怎么测 · 2026-10-08 更新</p>
</div>
</header>
<div class="layout">
<nav class="toc">
<strong>目录</strong>
<a href="#summary">1. 结论摘要</a>
<a href="#codes">2. 代码问题与修复</a>
<a href="#features">3. 未实现功能</a>
<a href="#gates">4. 发布门禁（外部）</a>
<a href="#env">5. 测试环境准备</a>
<a href="#how">6. 分层测试方法</a>
<a href="#matrix">7. 验收矩阵</a>
<a href="#baseline">8. 当前基线</a>
</nav>
<main>
<section id="summary">
<h2>1. 结论摘要</h2>
<p>当前版本<strong>不能认定为生产商用完成</strong>。核心分析链路（导入 → 总览 → 异常 → 下钻 → 证据 → 导出）已经实现并通过自动化回归；仍有<strong>一批 P1/P2 产品功能缺口</strong>，以及<strong>必须由外部环境和业务方关闭的发布门禁</strong>（Excel 实机、财务签字、真实 AI 合规、目标基础设施）。</p>
<div class="note"><strong>2026-10-08 工程整改：</strong>F01–F08 已完成代码修复并补充专项回归；本轮又完成网页自定义列、定义草稿恢复、版本历史、个人/团队共享、公式工作簿输入校验与保护、大文件下载直传。Excel 实机仍是独立门禁。</div>
<div class="note"><strong>2026-10-11 复核：</strong>对当前工作区重新全量验证——ruff/格式/迁移检查通过，SQLite 后端 73 passed（4 skipped），前端构建通过，Playwright 4/4 通过（含原附件验收用例，原附件数据集仍在本机库中）。F01–F08 修复确认有效，无需重复修复；剩余开放项见第 3、4 节。</div>
<p>状态口径：本文区分"源码静态确认"（读代码即可判断、尚无专项复现测试）与"实测通过"（有自动化结果）。清单整合自 <code>docs/acceptance.md</code>、<code>docs/remaining-issues-2026-09-26.html</code> 与 2026-09-27 的修复记录；细节以那两个文档为准，本文是单一入口。</p>
<table>
<tr><th>类别</th><th>数量</th><th>最高优先级</th></tr>
<tr><td>已关闭代码问题（F01–F08）</td><td>8</td><td><span class="tag ok">专项回归通过</span></td></tr>
<tr><td>未实现功能（产品边界）</td><td>约 7 项</td><td><span class="tag p1">P1</span></td></tr>
<tr><td>发布门禁（外部责任）</td><td>5 组</td><td><span class="tag p0">P0 ×3</span></td></tr>
</table>
<div class="note">2026-09-27 已完成、不再属于"未实现"的内容：AI 模型页面配置（云端/本地多模型选择）、证据金额 24 位小数修复、KPI 折行、图表轴标签、表格列遮挡、内部键名暴露等 UI 问题修复。详见第 8 节基线。</div>
</section>
<section id="codes">
<h2>2. 代码问题与修复记录（F01–F08）</h2>
<p>以下是历史问题及其当前修复后的验收方式；详细位置与原始建议见 <a href="remaining-issues-2026-09-26.html">remaining-issues-2026-09-26.html</a>。</p>
<table>
<tr><th>编号</th><th>级别</th><th>问题</th><th>修复后怎么测</th></tr>
<tr>
<td>F01</td><td><span class="tag p0">P0</span></td>
<td>公式导出失败清理可能沿符号链接父目录删除非任务文件（<code>finance/exporting.py</code>）</td>
<td>父目录链接到受保护目录并预置同名文件：执行必须失败、受保护文件字节不变、任务标记 failed；覆盖根目录链接/目标链接/写盘失败/正常重试</td>
</tr>
<tr>
<td>F02</td><td><span class="tag ok">已修复</span></td>
<td>清理失败被静默吞掉，失败任务的孤儿 .xlsx 缺少回收路径</td>
<td>注入 unlink 权限错误：任务仍 failed、有可观察告警；恢复权限后回收成功且不影响其他任务文件</td>
</tr>
<tr>
<td>F03</td><td><span class="tag ok">已修复</span></td>
<td>证据弹窗重复点击当前期间会让列表停在"加载中"（<code>EvidenceDialog.selectSide</code>）</td>
<td>重复点击当前期间、快速本期/基期切换、第二页切期间、空列表、请求失败——均不显示过期数据或永久加载（Playwright E2E）</td>
</tr>
<tr>
<td>F04</td><td><span class="tag ok">已修复</span></td>
<td>载入已保存定义后追问，没有把原定义带给模型，指代修改不可靠</td>
<td>保存定义 A → 重开载入 A → 发送依赖 A 的修改：检查模型请求实际包含 A；未发送补充时禁止生成</td>
</tr>
<tr>
<td>F05</td><td><span class="tag ok">已修复</span></td>
<td>定义库 50 条上限非原子约束；同名更新最后写入覆盖、无 revision 冲突检测；审计不在同一事务</td>
<td>PostgreSQL 下并发保存不得产生第 51 条；两个标签页更新不静默丢失；模拟审计写入失败后定义变更回滚</td>
</tr>
<tr>
<td>F06</td><td><span class="tag ok">已修复</span></td>
<td>工作簿参数页把毛利率标为"可编辑"但实际公式引用每行 MarginRate，修改不联动</td>
<td>每个标为可编辑的输入都对应真实依赖；收入/成本/原价/直接毛利/lookup 毛利率分别改值重算验证</td>
</tr>
<tr>
<td>F07</td><td><span class="tag ok">已修复</span></td>
<td>对账统一用 0.01 阈值，人数/金额/比率混用；自定义列无独立服务器参考值</td>
<td>阈值边界、大数、抵消、极小值、除零、缺失、多层表达式独立黄金样例通过；容差需先经财务确认</td>
</tr>
<tr>
<td>F08</td><td><span class="tag ok">已修复</span></td>
<td>导出任务执行/下载阶段不重新校验角色；撤权后的下载政策未定义；缺超时/崩溃恢复测试</td>
<td>排队后撤权、成功后撤权按签署政策处理；半成品不可下载；worker 崩溃重启不重复发布或卡死在 processing</td>
</tr>
</table>
<p>F01–F08 当前均有代码修复和自动化回归；后续验收重点转为 PostgreSQL 并发、Excel 实机和目标部署环境。</p>
</section>
<section id="features">
<h2>3. 未实现功能（产品边界）</h2>
<table>
<tr><th>级别</th><th>功能</th><th>当前缺口</th></tr>
<tr><td><span class="tag ok">已实现</span></td><td>新列进入网页汇总表</td><td>从定义库选择的列由服务端安全计算并显示在主分析表、汇总和分组行；E2E 覆盖选择后显示</td></tr>
<tr><td><span class="tag ok">已实现</span></td><td>多列编辑与定义生命周期</td><td>网页最多选择 10 列；对话草稿会话恢复，定义支持最近 20 个版本、个人更新与全范围账号共享</td></tr>
<tr><td><span class="tag p1">P1</span></td><td>适用范围与表达式能力</td><td>scope 只是说明文本；不支持任意条件、跨明细新聚合、Excel 函数、维度列、缺失替代策略</td></tr>
<tr><td><span class="tag ok">部分完成</span></td><td>Excel 内编辑防误操作</td><td>结构、证据、人数和公式工作表已保护；计算明细仅收入/成本/毛利输入/毛利率/原价金额解锁并带数值校验。增删明细行仍需重新导出</td></tr>
<tr><td><span class="tag p1">P1</span></td><td>原附件 332,830 行全量导出</td><td>产品上限 100,000 行/200 组，原附件一次导不全；需可审计分卷或正式提高上限后再验收</td></tr>
<tr><td><span class="tag p1">P1</span></td><td>结果缓存与大文件下载</td><td>公式下载已改为浏览器直传，避免前端 Blob 全量驻留；openpyxl/LibreOffice 计算缓存与 /tmp 容量仍需目标环境方案</td></tr>
<tr><td><span class="tag p1">P1</span></td><td>高基数与可访问性</td><td>聚合分组全量返回，无 SSRM/结果窗口；无键盘/屏幕阅读器/平板专项</td></tr>
<tr><td><span class="tag p2">P2</span></td><td>扩展业务能力</td><td>多收入字段口径、消费比率、生效期毛利率、退款策略、结构占比、历史阈值、告警渠道、团队调查分派</td></tr>
<tr><td><span class="tag p2">P2</span></td><td>跨平台与高级网格</td><td>无原生客户端；AG Grid SSRM/Pivot/Tree Data/Master-Detail 未接入（需 Enterprise 许可）</td></tr>
</table>
<div class="note"><strong>本轮 P1 完成：</strong>网页自定义列、多列选择、定义草稿恢复/版本历史/共享、Excel 受保护输入与校验、公式下载直传。<strong>仍开放：</strong>任意条件与跨明细聚合、33 万行分卷/容量、结果缓存、高基数窗口、无障碍专项，以及第 4 节外部发布门禁。</div>
<div class="warn">另外：趋势/贡献图为 ECharts 折线+横条，不是完整瀑布/Pareto/散点套件；异常规则只有变化率+影响额+样本数，无结构占比、历史分位数与告警投递；无团队工单分派。这些属于需求范围决策，开发前先冻结业务定义。</div>
</section>
<section id="gates">
<h2>4. 发布门禁（必须由外部关闭）</h2>
<table>
<tr><th>级别</th><th>门禁</th><th>责任方</th><th>关闭证据</th></tr>
<tr>
<td><span class="tag p0">P0</span></td><td>Microsoft Excel 实机验收</td>
<td>财务、QA、目标终端负责人</td>
<td>可编辑 Windows/Mac Excel 打开受控样例 → 强制重算 → 保存；改值后依赖链正确；覆盖人数、升单率、空集、缺期</td>
</tr>
<tr>
<td><span class="tag p0">P0</span></td><td>财务业务口径签字</td>
<td>财务负责人</td>
<td>签署收入/实收/原价、退款、成本归属、毛利、ROI、自营、升单与容差；原附件成本全缺，ROI 保持不可用</td>
</tr>
<tr>
<td><span class="tag p0">P0</span></td><td>真实 AI 联调与数据合规</td>
<td>合规、数据负责人、AI 服务负责人</td>
<td>数据处理/出境审批、模型版本与留存策略；真实验证超时/拒答/限流/非法输出。批准前保持 AI 关闭</td>
</tr>
<tr>
<td><span class="tag p1">P1</span></td><td>目标基础设施验收</td>
<td>运维、安全</td>
<td>镜像构建、迁移、非 root/卷权限、TLS、代理、数据库认证、队列、备份恢复、日志脱敏、安全扫描、许可证</td>
</tr>
<tr>
<td><span class="tag p1">P1</span></td><td>当前版本 PostgreSQL 与 SLA</td>
<td>工程 + 运维</td>
<td>在独立 PostgreSQL 跑当前全量测试、并发定义保存、worker 恢复、导出压力；记录 P95、资源预算、RPO/RTO</td>
</tr>
</table>
<div class="note">AI 联调现在可以在页面完成配置：管理 → AI 模型，选择 OpenAI 或本地 OpenAI 兼容服务（Ollama / LM Studio / vLLM），"获取可用模型"可多选本地模型。配置入口已实现（见使用文档第 9 节），但"真实模型联调 + 合规审批"这一门禁仍然开放。SQLite 仅用于开发回归，金额验收必须用 PostgreSQL。</div>
</section>
<section id="env">
<h2>5. 测试环境准备</h2>
<h3>5.1 快速启动（SQLite 轻量开发库）</h3>
<pre><code>scripts/dev_up.sh # 迁移 + 管理员 + 前端构建 + API + worker，自动健康检查
scripts/dev_up.sh --stop # 停止</code></pre>
<p>登录凭据在 <code>var/local-access.txt</code>（用户名 <code>admin</code>，随机密码，权限 0600）。</p>
<h3>5.2 PostgreSQL（财务验收必需）</h3>
<pre><code>/opt/homebrew/opt/postgresql@17/bin/pg_ctl -D var/postgres -l var/postgres.log \
-o '-h 127.0.0.1 -p 55432' start
export DATABASE_URL='postgresql://jianwel@127.0.0.1:55432/traceiq'</code></pre>
<div class="warn">SQLite 不保证高精度金额聚合，禁止用于财务验收和生产。对账脚本 <code>scripts/reconcile_reference.py</code> 明确要求 PostgreSQL。</div>
<h3>5.3 Playwright 浏览器</h3>
<pre><code>npx playwright install chromium # 首次执行</code></pre>
</section>
<section id="how">
<h2>6. 分层测试方法</h2>
<h3>6.1 静态检查与后端单测（每次改动都跑）</h3>
<pre><code>uv run ruff check server finance tests scripts
uv run ruff format --check server finance tests scripts
uv run python manage.py makemigrations --check --dry-run
uv run pytest --cov=finance --cov-report=term-missing --cov-fail-under=85</code></pre>
<h3>6.2 前端构建与浏览器 E2E</h3>
<pre><code>npm run build
E2E_PASSWORD='&lt;测试口令&gt;' npm run test:e2e</code></pre>
<p>E2E 需要正在运行的本地 API + worker；会真实导入测试 CSV 数据集，<strong>不可指向生产环境</strong>。覆盖：导入确认、总览 KPI、异常下钻、调查保存、证据分页、设置乐观锁、Copilot、公式工作簿追问与下载、手机布局无横向溢出。</p>
<h3>6.3 原附件验收用例（本机显式启用）</h3>
<pre><code>REFERENCE_DATASET_NAME='分析数据_原稿(1).xlsx' E2E_PASSWORD='&lt;口令&gt;' npm run test:e2e</code></pre>
<p>断言：KPI 精确值（收入 152,419,115.35、毛利 17,868,580.85、人数 38,361）、环比缺 2026-07 禁比、同比 -32.4%、移动端无溢出、无页面脚本错误。附件文件不在仓库中，需本机提供。</p>
<h3>6.4 真实数据对账（PostgreSQL）</h3>
<pre><code>PYTHONPATH=. uv run python scripts/reconcile_reference.py</code></pre>
<p>断言 332,830 行、收入/毛利/人数与历史独立对账一致（误差 0）。原附件 SHA-256：<code>98ef41e898c2…</code>。</p>
<h3>6.5 公式工作簿重算</h3>
<pre><code>uv run pytest tests/test_formula_recalculation.py \
tests/test_formula_recalculation_gross.py # LibreOffice 重算，7 项</code></pre>
<p>LibreOffice 通过 ≠ Excel 通过；Microsoft Excel 实机重算是独立门禁（第 4 节）。</p>
<h3>6.6 安全与部署检查</h3>
<pre><code>uv run pip-audit
npm audit --omit=dev
uv run python manage.py check --deploy # 需完整生产环境变量</code></pre>
<div class="note">依赖审计为 0 漏洞 ≠ 渗透测试通过；<code>check --deploy</code> 无告警 ≠ 基础设施验收通过。</div>
</section>
<section id="matrix">
<h2>7. 验收矩阵：每项怎么算"完成"</h2>
<table>
<tr><th>目标</th><th>通过标准</th><th>手段</th></tr>
<tr><td>F01–F08 修复</td><td>各有"修复前失败、修复后通过"的专项断言；覆盖率 ≥ 85%</td><td>pytest + Playwright（见第 2 节每行）</td></tr>
<tr><td>AI 配置功能</td><td>保存/脱敏回显/权限/模型列表/助手走配置，接口级测试通过</td><td><code>tests/test_assistant_api.py</code>（已实现）</td></tr>
<tr><td>金额精度</td><td>证据与导出无存储尺度尾零；对账 0 误差</td><td><code>test_evidence_amounts_trim_storage_scale</code> + reconcile_reference.py</td></tr>
<tr><td>Excel 引擎门禁</td><td>目标 Excel 改值重算、保存、重开一致</td><td>人工按受控样例执行并记录版本与结果（第 4 节）</td></tr>
<tr><td>财务口径门禁</td><td>财务负责人签字确认口径与容差</td><td>签字文件；不以技术测试替代</td></tr>
<tr><td>AI 合规门禁</td><td>审批完成 + 真实模型故障演练通过</td><td>页面配置真实模型后做超时/拒答/限流演练</td></tr>
<tr><td>基础设施门禁</td><td>镜像构建、TLS、备份恢复演练、RPO/RTO 记录</td><td>docker compose + 恢复演练</td></tr>
<tr><td>性能/SLA</td><td>目标并发下导入/查询 P95 达标；高基数有方案</td><td>压力测试；高基数维度需 SSRM 或服务端窗口</td></tr>
</table>
</section>
<section id="baseline">
<h2>8. 当前基线（最近一次实测）</h2>
<table>
<tr><th>项目</th><th>结果</th><th>日期 / 环境</th></tr>
<tr><td>后端单测（本轮复核）</td><td><span class="tag ok">73 passed</span>，4 skipped</td><td>2026-10-11，SQLite 测试库</td></tr>
<tr><td>Playwright E2E（本轮复核）</td><td><span class="tag ok">4 passed</span>（桌面综合、手机布局、公式导出、原附件验收）</td><td>2026-10-11，本地 API + worker</td></tr>
<tr><td>后端单测</td><td><span class="tag ok">77 passed</span>，finance 覆盖率 88.45%（门槛 85%）</td><td>2026-10-08，SQLite；PostgreSQL 77 passed</td></tr>
<tr><td>Playwright E2E</td><td>3 passed，1 skipped（原附件用例未启用）</td><td>2026-10-08，本地 API + worker</td></tr>
<tr><td>本轮修复</td><td>F01–F08、网页自定义列、多列选择、草稿恢复、版本历史与共享、Excel 输入校验/公式保护、公式下载直传；PostgreSQL 运行与 Excel 实机仍需门禁</td><td>2026-10-08</td></tr>
<tr><td>历史基线（勿直接引用）</td><td>PostgreSQL 67 项、61 项、44/54 项等旧记录，以及 332,830 行旧峰值 RSS 3.31 GB</td><td>历史，见 acceptance.md</td></tr>
</table>
<h3>相关文档</h3>
<ul>
<li><a href="acceptance.md">acceptance.md</a> — 验收状态与上线门禁（最详细）</li>
<li><a href="remaining-issues-2026-09-26.html">remaining-issues-2026-09-26.html</a> — F01–F08 代码问题细节</li>
<li><a href="user-guide.html">user-guide.html</a> — 使用者操作说明（含 AI 模型配置）</li>
<li><a href="improvement-roadmap.html">improvement-roadmap.html</a> / <a href="improvement-plan.html">improvement-plan.html</a> — 改进计划（部分状态已过期，以本文为准）</li>
</ul>
<div class="warn">多个历史文档之间存在状态冲突（如"没有定义库"等旧表述）。后续应统一维护单一验收入口，历史文档标题注明"历史"。本文不是上线授权。</div>
</section>
</main>
</div>
<footer>TraceIQ 未实现功能与测试指南 · 2026-10-11 复核更新 · 状态口径：实测 &gt; 静态确认 &gt; 历史记录</footer>
</body>
</html>
docs/user-guide.html
+316
View File
@@ -0,0 +1,316 @@
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>TraceIQ 财务分析工作台 · 使用文档</title>
<style>
:root {
--ink: #1a2332;
--muted: #5b6675;
--accent: #1f5fa8;
--accent-soft: #eaf2fb;
--line: #dfe5ec;
--bg: #f6f8fa;
--card: #ffffff;
--warn: #9a5b00;
--warn-bg: #fdf3e3;
}
* { box-sizing: border-box; }
body {
margin: 0;
font-family: -apple-system, "PingFang SC", "Microsoft YaHei", "Segoe UI", sans-serif;
color: var(--ink);
background: var(--bg);
line-height: 1.7;
}
header {
background: linear-gradient(135deg, #16324f, #1f5fa8);
color: #fff;
padding: 48px 24px 40px;
}
header .inner, main { max-width: 960px; margin: 0 auto; }
header h1 { margin: 0 0 8px; font-size: 28px; }
header p { margin: 0; opacity: .85; }
.layout { max-width: 1160px; margin: 0 auto; display: flex; gap: 32px; padding: 32px 24px 64px; }
nav.toc {
flex: 0 0 220px;
position: sticky; top: 24px; align-self: flex-start;
background: var(--card); border: 1px solid var(--line); border-radius: 10px;
padding: 16px 18px; font-size: 14px;
}
nav.toc strong { display: block; margin-bottom: 8px; }
nav.toc a { display: block; color: var(--accent); text-decoration: none; padding: 3px 0; }
nav.toc a:hover { text-decoration: underline; }
main { flex: 1; min-width: 0; }
section {
background: var(--card); border: 1px solid var(--line); border-radius: 10px;
padding: 24px 28px; margin-bottom: 24px;
}
h2 { font-size: 20px; margin: 0 0 12px; padding-bottom: 8px; border-bottom: 2px solid var(--accent-soft); }
h3 { font-size: 16px; margin: 20px 0 8px; }
p { margin: 8px 0; }
ul, ol { margin: 8px 0; padding-left: 22px; }
li { margin: 4px 0; }
code {
background: #eef1f5; border-radius: 4px; padding: 1px 6px;
font-family: "SF Mono", Menlo, Consolas, monospace; font-size: .92em;
}
table { border-collapse: collapse; width: 100%; margin: 12px 0; font-size: 14px; }
th, td { border: 1px solid var(--line); padding: 8px 12px; text-align: left; }
th { background: var(--accent-soft); }
tr:nth-child(even) td { background: #fafcfe; }
.note {
background: var(--accent-soft); border-left: 4px solid var(--accent);
border-radius: 0 8px 8px 0; padding: 10px 16px; margin: 12px 0; font-size: 14px;
}
.warn {
background: var(--warn-bg); border-left: 4px solid var(--warn);
border-radius: 0 8px 8px 0; padding: 10px 16px; margin: 12px 0; font-size: 14px;
}
.kbd {
display: inline-block; border: 1px solid var(--line); border-bottom-width: 2px;
border-radius: 4px; background: #fff; padding: 0 6px; font-size: 13px;
font-family: "SF Mono", Menlo, Consolas, monospace;
}
footer { text-align: center; color: var(--muted); font-size: 13px; padding: 24px; }
@media (max-width: 860px) {
.layout { flex-direction: column; }
nav.toc { position: static; flex: none; }
}
</style>
</head>
<body>
<header>
<div class="inner">
<h1>TraceIQ 财务分析工作台 · 使用文档</h1>
<p>面向使用者：导入财务表格 → 总览与对比 → 异常追踪与下钻 → 证据核查与导出</p>
</div>
</header>
<div class="layout">
<nav class="toc">
<strong>目录</strong>
<a href="#start">1. 快速开始</a>
<a href="#layout">2. 界面导览</a>
<a href="#import">3. 导入数据</a>
<a href="#overview">4. 经营总览</a>
<a href="#anomalies">5. 异常追踪与下钻</a>
<a href="#contribution">6. 贡献分解</a>
<a href="#evidence">7. 证据与导出</a>
<a href="#settings">8. 分析设置</a>
<a href="#copilot">9. 助手与 AI 模型配置</a>
<a href="#admin">10. 账号与审计</a>
<a href="#notes">11. 口径与注意事项</a>
</nav>
<main>
<section id="start">
<h2>1. 快速开始</h2>
<p>TraceIQ 是证据优先的财务分析工作台：所有指标都能下钻到原始表格行，操作全程留痕。本地开发环境启动方式：</p>
<ol>
<li>在仓库根目录执行 <code>scripts/dev_up.sh</code>（自动完成迁移、管理员初始化、前端构建并启动服务）。</li>
<li>浏览器访问 <code>http://127.0.0.1:8000</code>。</li>
<li>使用管理员账号登录（本地凭据见 <code>var/local-access.txt</code>，文件权限 0600）。</li>
</ol>
<table>
<tr><th>环境</th><th>用户名</th><th>密码</th></tr>
<tr><td>本地开发（本机）</td><td><code>admin</code></td><td>见 <code>var/local-access.txt</code>（每次初始化随机生成）</td></tr>
<tr><td>自动化测试</td><td><code>admin</code></td><td>测试专用库随机生成，可用环境变量 <code>E2E_PASSWORD</code> 指定</td></tr>
</table>
<div class="note">停止服务：在运行 <code>dev_up.sh</code> 的终端按 <span class="kbd">Ctrl-C</span>，或另开终端执行 <code>scripts/dev_up.sh --stop</code>。</div>
</section>
<section id="layout">
<h2>2. 界面导览</h2>
<p>登录后左侧为导航栏，分两组：</p>
<table>
<tr><th>分组</th><th>入口</th><th>说明</th></tr>
<tr><td rowspan="3">工作台</td><td>经营总览</td><td>核心指标、走势、指标矩阵与同比/环比对比</td></tr>
<tr><td>异常追踪</td><td>超出阈值的分组列表，可进入调查；有新异常时入口显示数量角标</td></tr>
<tr><td>数据管理</td><td>数据集列表、导入新表格、数据来源与字段字典、公式工作簿导出</td></tr>
<tr><td rowspan="4">管理</td><td>分析设置</td><td>默认比较方式、默认指标、异常阈值等个人偏好</td></tr>
<tr><td>账号与权限</td><td>仅全权限管理员可见：查看账号、角色、状态、科室范围</td></tr>
<tr><td>操作审计</td><td>仅全权限管理员可见：时间、用户、操作、资源、详情</td></tr>
<tr><td>AI 模型</td><td>仅全权限管理员可见：配置助手使用的云端或本地模型服务</td></tr>
</table>
<p>顶部右侧为<strong>财务分析助手</strong>入口和<strong>退出登录</strong>。页面为响应式布局，手机浏览器可直接使用。</p>
</section>
<section id="import">
<h2>3. 导入数据</h2>
<h3>3.1 支持的文件</h3>
<ul>
<li>XLSX（Excel）与 UTF-8 编码 CSV。</li>
<li>支持双行表头：第二行表头作为字段语义说明一并保留。</li>
<li>单文件不超过 64 MiB（反向代理层限制 66 MB）。</li>
</ul>
<h3>3.2 导入流程</h3>
<ol>
<li>在「数据管理」页点击 <strong>导入新表格</strong>，选择文件上传。</li>
<li>系统在弹窗中展示识别出的字段，请逐项<strong>确认导入字段</strong>映射：期间、科室等业务维度、实际收入、成本、毛利贡献、客户编码、应收金额等。</li>
<li>选择 ROI 公式口径与自营产品范围后，点击 <strong>确认并导入</strong>。</li>
<li>导入由后台 worker 异步处理，完成后数据自动出现在总览中；列表中可查看状态、数据质量与导入版本。</li>
</ol>
<div class="note">
<strong>去重：</strong>同一组织内完全相同的文件（SHA-256）不会重复导入。<br>
<strong>原始解释：</strong>原始表头说明随数据保留，可在「数据来源与字段字典」中查看，保证指标口径可追溯。
</div>
<div class="warn">原附件中没有的维度（如医生、成本、连续月度数据）不会产生对应排名或环比结论；系统不会虚构数据。</div>
</section>
<section id="overview">
<h2>4. 经营总览</h2>
<p>总览页展示所选数据集团范围的核心经营结果：</p>
<ul>
<li><strong>KPI 卡片：</strong>实际收入、成本、毛利、ROI、客单价、毛利率、去重人数、自营收入、升单率等可用指标（取决于数据中有哪些字段）。</li>
<li><strong>收入与毛利走势：</strong>按月份的趋势图；缺月份的区间趋势自动断开，不会用直线伪造连续性。</li>
<li><strong>经营指标矩阵：</strong>按维度（如科室、产品分类）展开的指标对照表。</li>
</ul>
<h3>比较方式</h3>
<ul>
<li><strong>同比</strong> / <strong>环比</strong>：一键切换，可自定义开始/结束期间。</li>
<li>比较必须满足等长月份范围；<strong>缺基期时禁止比较</strong>，对应卡片显示不可用而不是给出误导数字。</li>
<li>缺成本的数据不显示 ROI 等依赖成本的指标。</li>
</ul>
</section>
<section id="anomalies">
<h2>5. 异常追踪与下钻</h2>
<h3>5.1 异常列表</h3>
<p>「异常追踪」列出超出分析设置中阈值与最小影响额的分组，按影响额排序。点击分组进入调查视图：</p>
<ul>
<li><strong>调查状态：</strong>待调查 / 核查中 / 已处理，保存后持久化。</li>
<li><strong>调查备注：</strong>自由文本，随状态一并保存。</li>
</ul>
<h3>5.2 下钻与面包屑</h3>
<ul>
<li>在调查视图可沿自定义层级<strong>继续下钻</strong>（如 科室 → 学科 → 医生等级）。</li>
<li>顶部<strong>分析路径</strong>面包屑记录当前下钻位置，点击任意一级可跳回。</li>
<li>列表支持<strong>搜索分组</strong>，方便在大体量维度中定位。</li>
</ul>
</section>
<section id="contribution">
<h2>6. 贡献分解</h2>
<p>调查视图提供两张贡献图，回答"变化是谁带来的"：</p>
<ul>
<li><strong>分组变化贡献图：</strong>当前层级各分组对本期与基期差额（变化额/变化贡献）的分解。</li>
<li><strong>调查范围子级贡献图：</strong>在所选分组内部，按下一级维度继续分解。</li>
</ul>
<div class="note">贡献分解只基于权限范围内的数据；科室范围受限的账号看不到范围外的分组。</div>
</section>
<section id="evidence">
<h2>7. 证据与导出</h2>
<h3>7.1 证据明细</h3>
<ul>
<li>任意数字都可通过<strong>查看证据</strong>打开原始行明细弹窗，逐条对应到源文件行号。</li>
<li>证据按<strong>本期 / 基期</strong>切换，每页最多 200 行，上一页/下一页翻页加载。</li>
</ul>
<h3>7.2 导出</h3>
<ul>
<li><strong>导出当前范围：</strong>将当前筛选与下钻范围内的证据导出为 CSV 或 XLSX；Excel 中精确金额以文本单元格导出，避免 15 位有效数字精度丢失。</li>
<li><strong>生成公式工作簿：</strong>在「数据管理」中生成带公式的 Excel 工作簿，含列计算描述，便于财务同事复核口径。</li>
</ul>
<div class="note">客户端不会一次加载全部明细（大文件可达 33 万行），只按需拉取聚合结果与分页证据。</div>
</section>
<section id="settings">
<h2>8. 分析设置</h2>
<p>「分析设置」是个人偏好，随账号保存，字段包括：</p>
<table>
<tr><th>设置项</th><th>作用</th></tr>
<tr><td>默认比较</td><td>同比 / 环比</td></tr>
<tr><td>默认指标</td><td>总览与矩阵默认展示的指标</td></tr>
<tr><td>异常阈值</td><td>判定异常的变化幅度阈值</td></tr>
<tr><td>最小影响额</td><td>低于该金额的变化不列为异常，避免噪声</td></tr>
<tr><td>最小样本数</td><td>样本量过小的分组不参与异常判定</td></tr>
</table>
<p>保存使用乐观锁：若他人在你打开设置期间已保存，系统会提示冲突而不是静默覆盖。页面上一般提供保存 / 取消 / 预览能力。</p>
</section>
<section id="copilot">
<h2>9. 财务分析助手与 AI 模型配置</h2>
<h3>9.1 使用助手</h3>
<ul>
<li>默认启用<strong>本地规则助手</strong>：直接回答指标口径、功能用法等问题，数据不出本机。</li>
<li>勾选「使用已配置的 AI 模型理解问题」并同意后，问题与维度/当前筛选会发送到你配置的模型服务，<strong>原始数据行不出服务器</strong>。</li>
<li>无论本地还是外部模型，都只能返回受控意图（如跳转页面、设置筛选），<strong>不能直接改金额、不能提交 SQL、不能改账号</strong>；查询数据仍受后端权限约束。</li>
<li>模型输出异常或被拒答时，相关执行自动关闭。</li>
</ul>
<h3>9.2 配置 AI 模型（全权限管理员）</h3>
<p>在左侧「管理 → AI 模型」中配置，支持两类服务商：</p>
<table>
<tr><th>服务商</th><th>说明</th><th>Base URL 示例</th><th>API Key</th></tr>
<tr>
<td>OpenAI</td>
<td>OpenAI Responses 接口</td>
<td><code>https://api.openai.com/v1</code>（可留空使用默认）</td>
<td>必填</td>
</tr>
<tr>
<td>本地 OpenAI 兼容服务</td>
<td>Ollama、LM Studio、vLLM 等任何兼容 OpenAI 协议的服务</td>
<td>Ollama：<code>http://127.0.0.1:11434/v1</code><br>LM Studio：<code>http://127.0.0.1:1234/v1</code></td>
<td>通常可留空</td>
</tr>
</table>
<ol>
<li>选择服务商，填写 Base URL（本地服务必填）。</li>
<li>填写 API Key（本地服务通常可留空）。</li>
<li>点击<strong>获取可用模型</strong>：系统从服务的 <code>/v1/models</code> 接口拉取模型列表。本地服务装了多个模型（如 Ollama 里的 qwen2.5:7b、llama3.1:8b）时，可在下拉列表中挑选，不用手敲名字。</li>
<li>选中模型后勾选「启用 AI 模型理解问题」，点击<strong>保存配置</strong>。</li>
<li>回到页面顶部打开「财务分析助手」，勾选「使用已配置的 AI 模型理解问题」并同意后即可提问。</li>
</ol>
<div class="note">
<strong>配置规则：</strong>每个组织一份配置，保存在数据库中，优先于部署环境变量（未配置时回退到
<code>AI_ENABLED</code> / <code>OPENAI_API_KEY</code> / <code>OPENAI_MODEL</code> / <code>OPENAI_BASE_URL</code>）。
仅全权限管理员（未限科室范围的管理员角色）可查看和修改；限域管理员、分析师、查看者无此入口。
</div>
<div class="note">
<strong>密钥安全：</strong>API Key 只保存在后端，页面只显示「已配置」，读取接口不回传密钥原文；
再次保存时留空表示保持原密钥不变。每次配置修改都会记入操作审计（只记服务商、模型与是否启用，不记密钥）。
</div>
<h3>9.3 常见问题</h3>
<ul>
<li><strong>「获取可用模型」失败？</strong>确认本地服务已启动且 Base URL 正确（Ollama 默认 <code>http://127.0.0.1:11434/v1</code>），并先点击「保存配置」再获取。</li>
<li><strong>助手提示「尚未配置外部 AI 服务」？</strong>检查「AI 模型」中是否已启用、模型名是否已填写；OpenAI 还需 API Key。</li>
<li><strong>想停用 AI？</strong>取消勾选「启用 AI 模型理解问题」并保存，助手回到纯本地规则模式。</li>
</ul>
</section>
<section id="admin">
<h2>10. 账号与审计（管理员）</h2>
<h3>10.1 账号与权限</h3>
<ul>
<li>全权限管理员（未限科室范围）可在「账号与权限」查看所有账号的<strong>角色</strong>（查看者 / 分析师 / 管理员）、<strong>启用状态</strong>与<strong>科室范围</strong>。</li>
<li>限域管理员只能管理自己科室范围内的成员，不能管理账号或查看全局审计。</li>
<li>角色能力概览：查看者可看不可改；分析师可做分析调查；管理员可导入数据并管理账号。</li>
</ul>
<h3>10.2 操作审计</h3>
<p>「操作审计」记录登录、导入、导出、设置修改、调查保存等操作的时间、用户、动作与详情，网页端只读。需要不可抵赖审计时应外送到独立追加写日志平台（数据库管理员理论上可改库内记录）。</p>
</section>
<section id="notes">
<h2>11. 口径与注意事项</h2>
<ul>
<li><strong>毛利口径：</strong>毛利是按产品分类毛利率计算的贡献值，<strong>不等同于已审计净利润</strong>。</li>
<li><strong>ROI 口径：</strong>提供两种明确公式（如 （收入 − 成本）/ 成本），导入时选择并可在字段字典中回看。</li>
<li><strong>精度：</strong>后端金额使用高精度定点数存储与计算，JSON 中金额为字符串；浏览器 Number 仅用于绘图显示。</li>
<li><strong>缺数据原则：</strong>缺基期禁止比较、缺成本显示不可用、缺月份趋势断开——系统宁可显示"不可用"也不给误导数字。</li>
<li><strong>安全：</strong>登录有频率限制；原始文件私有存储、按组织隔离；审计留痕。</li>
<li><strong>本地 SQLite 模式</strong>仅限页面测试与开发回归，高精度金额聚合与财务验收请使用 PostgreSQL 环境（见 README）。</li>
</ul>
<div class="warn">本文档面向使用者描述当前交付能力；全部需求完成情况与上线门禁以 <code>docs/acceptance.md</code> 为准。</div>
</section>
</main>
</div>
<footer>TraceIQ 使用文档 · 2026-09-26 · 与代码当前版本同步维护</footer>
</body>
</html>
finance/analysis.py
+44 -10
View File
@@ -167,9 +167,9 @@ def metrics(agg, formula, products=None):
}
def comparison(current, baseline, comparable):
def comparison(current, baseline, comparable, keys=None):
result = {}
for key in METRICS:
for key in keys or METRICS:
c, b = current.get(key), baseline.get(key)
delta = c - b if comparable and c is not None and b is not None else None
change = Decimal(delta) / abs(Decimal(b)) if delta is not None and b != 0 else None
@@ -177,8 +177,31 @@ def comparison(current, baseline, comparable):
return result
def custom_columns(ds, body):
from .formula_columns import ensure_available_metrics, validate_column
columns = body.get("custom_columns", [])
if columns is None:
return []
if not isinstance(columns, list) or len(columns) > 10:
raise ValueError("自定义列最多 10 个")
result = [validate_column(column) for column in columns]
for column in result:
ensure_available_metrics(ds, column)
return result
def custom_values(values, columns):
from .formula_columns import evaluate
return {f"custom_{index}": evaluate(column["expression"], values) for index, column in enumerate(columns)}
def analyze(ds, member, body, preferences):
ctx = context(ds, body)
columns = custom_columns(ds, body)
ctx = {**context(ds, body), "custom_columns": columns}
custom_keys = [f"custom_{index}" for index in range(len(columns))]
value_keys = [*METRICS, *custom_keys]
qs = scoped_facts(ds, member, ctx["filters"])
formula = ds.mapping["roi_formula"]
products = ds.mapping.get("self_operated_products", [])
@@ -190,8 +213,14 @@ def analyze(ds, member, body, preferences):
)
comparable = not ctx["missing"]
if set(ctx["current"]) - set(ds.periods):
total_c = {**total_c, **{key: None for key in METRICS}}
summary = comparison(total_c, total_b, comparable)
total_c = {**total_c, **{key: None for key in value_keys}}
current_missing = bool(set(ctx["current"]) - set(ds.periods))
total_c = {
**total_c,
**({key: None for key in custom_keys} if current_missing else custom_values(total_c, columns)),
}
total_b = {**total_b, **custom_values(total_b, columns)}
summary = comparison(total_c, total_b, comparable, value_keys)
group = "dimensions__" + ctx["group"]
buckets = {}
for side in ("current", "baseline"):
@@ -205,7 +234,7 @@ def analyze(ds, member, body, preferences):
k: Decimal(0)
if k in ("revenue", "cost", "gross", "customers", "self_revenue") and total.get(k) is not None
else None
for k in METRICS
for k in value_keys
},
}
@@ -217,8 +246,10 @@ def analyze(ds, member, body, preferences):
for label, sides in buckets.items():
c, b = sides.get("current", empty_side(total_c)), sides.get("baseline", empty_side(total_b))
if set(ctx["current"]) - set(ds.periods):
c = {**c, **{key: None for key in METRICS}}
values = comparison(c, b, comparable)
c = {**c, **{key: None for key in value_keys}}
c = {**c, **({key: None for key in custom_keys} if current_missing else custom_values(c, columns))}
b = {**b, **custom_values(b, columns)}
values = comparison(c, b, comparable, value_keys)
v = values[metric]
change, delta = v["change"], v["delta"]
impact = (
@@ -249,7 +280,10 @@ def analyze(ds, member, body, preferences):
)
rows.sort(key=lambda r: (not r["anomaly"], -r["impact"], r["label"]))
observed = {
r["period"]: metrics(r, formula, products)
r["period"]: {
**(values := metrics(r, formula, products)),
**custom_values(values, columns),
}
for r in qs.values("period").annotate(**aggregations(products))
}
trend = []
@@ -257,7 +291,7 @@ def analyze(ds, member, body, preferences):
period = month_text(index)
values = observed.get(period)
if values is None:
values = empty_side(total_c) if period in ds.periods else {key: None for key in METRICS}
values = empty_side(total_c) if period in ds.periods else {key: None for key in value_keys}
trend.append({"period": period, **values})
return {
"dataset_id": str(ds.pk),
finance/exporting.py
+91 -13
View File
@@ -1,5 +1,6 @@
import hashlib
import hmac
import logging
import os
import secrets
import warnings
@@ -11,22 +12,62 @@ from django.conf import settings
from django.utils import timezone
from openpyxl import Workbook
from openpyxl.cell import WriteOnlyCell
from openpyxl.styles import Font, PatternFill
from openpyxl.styles import Font, PatternFill, Protection
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.worksheet.table import Table, TableColumn, TableStyleInfo
from .analysis import METRICS, aggregations, context, dimensions, metrics, scoped_facts
from .formula_columns import (
RESERVED_DATA_HEADERS,
ensure_available_metrics,
evaluate,
metrics_used,
to_excel,
validate_column,
)
from .models import FormulaExport, Membership
# Keep the private test seam while sharing the single evaluator implementation.
_evaluate_expression = evaluate
FORMULA_VERSION = "3"
MAX_ROWS = 100_000
MAX_GROUPS = 200
RECONCILE_TOLERANCES = {
"customers": Decimal("0"),
"margin": Decimal("0.0001"),
"discount_rate": Decimal("0.0001"),
"upgrade_rate": Decimal("0.0001"),
}
DEFAULT_RECONCILE_TOLERANCE = Decimal("0.01")
logger = logging.getLogger(__name__)
def _safe_unlink_export(path, root):
"""Delete only a file below the owned export root without following links."""
path, root = Path(path), Path(root).absolute()
if not path.is_absolute() or root.is_symlink() or path.is_symlink():
logger.warning("formula export cleanup refused unsafe path: %s", path)
return False
parent = path.parent
try:
if not parent.is_relative_to(root):
logger.warning("formula export cleanup refused out-of-root path: %s", path)
return False
except ValueError:
return False
current = parent
while current != root:
if current.is_symlink() or current.parent == current:
logger.warning("formula export cleanup refused linked parent: %s", path)
return False
current = current.parent
try:
path.unlink(missing_ok=True)
return True
except OSError:
logger.warning("formula export cleanup failed for %s", path, exc_info=True)
return False
def _excel_number(value):
@@ -269,6 +310,19 @@ def build_formula_workbook(ds, member, export_context, output):
),
)
data.append(headers)
editable_indices = {len(dims) + 2, len(dims) + 3, len(dims) + 4, len(dims) + 5, len(dims) + 7}
if count:
numeric_input = DataValidation(
type="decimal", operator="between", formula1="-1e15", formula2="1e15", allow_blank=True
)
numeric_input.showErrorMessage = True
numeric_input.errorStyle = "stop"
numeric_input.errorTitle = "请输入数值"
numeric_input.error = "计算输入只接受范围内的数值或空白；请勿粘贴公式或文本。"
for index in editable_indices:
column_name = _column_name(index + 1)
numeric_input.add(f"{column_name}2:{column_name}{count + 1}")
data.data_validations.append(numeric_input)
token_secret = secrets.token_bytes(32)
customer_roster = {}
gross_index = next((col["index"] for col in mapping["columns"] if col["role"] == "gross"), None)
@@ -329,6 +383,12 @@ def build_formula_workbook(ds, member, export_context, output):
f'{_column_name(len(dims) + 6)}{row_number},""))'
),
)
for index in editable_indices:
cell = row[index]
if not hasattr(cell, "protection"):
cell = WriteOnlyCell(data, value=cell)
cell.protection = Protection(locked=False)
row[index] = cell
data.append(row)
customer_headers = [
"Group",
@@ -408,7 +468,13 @@ def build_formula_workbook(ds, member, export_context, output):
],
["ROI 口径", mapping["roi_formula"], "按导入映射保存的公式分支", "否"],
["自营产品集合", _text_cell(parameters, ", ".join(products)), "升单率和自营收入口径", "否"],
["产品毛利率", str(rates), "直接毛利缺失时的可编辑计算输入", "是"],
["产品毛利率快照", str(rates), "导入时保存的只读口径；修改需重新确认导入映射", "否"],
[
"对账容差",
"人数 0；金额 0.01；比率 0.0001；自定义 0.01",
"服务器 Decimal 参考与 Excel 结果的绝对差；正式财务验收前需确认",
"否",
],
[
"Excel 数值精度",
"IEEE-754 双精度",
@@ -443,6 +509,10 @@ def build_formula_workbook(ds, member, export_context, output):
["变化率", "差额/ABS(基期)；基期为0或期间缺失时不可用。"],
["精度", "原始 Decimal 以文本留存；Excel 双精度存在细微误差可能。"],
["隐私", "客户标识替换为本文件随机 token，文件仍按敏感财务信息保护。"],
[
"编辑保护",
"工作表结构、人数去重、原始证据和公式列已保护；计算明细仅收入、成本、毛利输入、毛利率和原价金额可编辑。行结构变化请重新导出。",
],
]:
instructions.append(row)
for definition in custom_columns:
@@ -559,6 +629,7 @@ def build_formula_workbook(ds, member, export_context, output):
for key, name in METRICS.items():
formula_row = metric_rows[(label, key)]
value = reference[key]
tolerance = RECONCILE_TOLERANCES.get(key, DEFAULT_RECONCILE_TOLERANCE)
reconcile.append(
[
name,
@@ -567,24 +638,30 @@ def build_formula_workbook(ds, member, export_context, output):
"" if value is None else str(value),
f"='分析汇总'!{col}{formula_row}",
f'=IFERROR(E{reconcile_row}-D{reconcile_row},"")',
f'=IF(D{reconcile_row}="","无参考",IF(ISNUMBER(F{reconcile_row}),IF(ABS(F{reconcile_row})<0.01,"通过","待复核"),"待复核"))',
f'=IF(D{reconcile_row}="","无参考",IF(ISNUMBER(F{reconcile_row}),IF(ABS(F{reconcile_row})<={tolerance},"通过","待复核"),"待复核"))',
]
)
reconcile_row += 1
for index, definition in enumerate(custom_columns):
formula_row = metric_rows[(label, f"custom_{index}")]
custom_reference = evaluate(definition["expression"], reference)
reconcile.append(
[
_text_cell(reconcile, definition["name"]),
_text_cell(reconcile, label),
side_name,
"自定义公式无服务器参考",
"" if custom_reference is None else str(custom_reference),
f"='分析汇总'!{col}{formula_row}",
"",
"需业务验收",
f'=IFERROR(E{reconcile_row}-D{reconcile_row},"")',
f'=IF(D{reconcile_row}="","无参考",IF(ISNUMBER(F{reconcile_row}),IF(ABS(F{reconcile_row})<={DEFAULT_RECONCILE_TOLERANCE},"通过","待复核"),"待复核"))',
]
)
reconcile_row += 1
for sheet in wb.worksheets:
sheet.protection.sheet = True
sheet.protection.selectLockedCells = False
sheet.protection.selectUnlockedCells = True
wb.security.lockStructure = True
wb.calculation.fullCalcOnLoad = True
wb.calculation.forceFullCalc = True
wb.calculation.calcMode = "auto"
@@ -596,8 +673,8 @@ def process_formula_export(export_id):
job = FormulaExport.objects.select_related("dataset", "user").get(pk=export_id)
temporary = None
target = None
try:
root = Path(settings.MEDIA_ROOT) / "formula-exports"
try:
if root.is_symlink():
raise ValueError("公式导出目录不能是符号链接")
root.mkdir(parents=True, exist_ok=True, mode=0o700)
@@ -609,6 +686,8 @@ def process_formula_export(export_id):
target.parent.chmod(0o700)
temporary = target.with_name(f"{job.pk}.{secrets.token_hex(8)}.tmp")
member = Membership.objects.get(user=job.user, organization=job.dataset.organization)
if not job.user.is_active or member.role not in ("admin", "analyst"):
raise PermissionError("账号已撤权，请重新提交导出")
if set(member.departments) != set(job.departments):
raise PermissionError("科室授权已变更，请重新提交导出")
descriptor = os.open(temporary, os.O_WRONLY | os.O_CREAT | os.O_EXCL, 0o600)
@@ -621,13 +700,12 @@ def process_formula_export(export_id):
job.save(update_fields=["path", "status", "error", "finished_at", "expires_at"])
return stats
except Exception as exc:
remaining = ""
for path in (temporary, target):
try:
if path is not None and not path.is_symlink():
path.unlink(missing_ok=True)
except OSError:
pass
job.status, job.error, job.path = "failed", str(exc)[:500], ""
if path is not None and not _safe_unlink_export(path, root):
if path.exists() or path.is_symlink():
remaining = str(path)
job.status, job.error, job.path = "failed", str(exc)[:500], remaining
job.finished_at, job.expires_at = timezone.now(), None
job.save(update_fields=["status", "error", "path", "finished_at", "expires_at"])
raise
finance/formula_columns.py
+25
View File
@@ -1,4 +1,5 @@
import math
from decimal import Decimal, localcontext
from .analysis import METRICS
@@ -107,3 +108,27 @@ def to_excel(node, references):
if "number" in node:
return str(node["number"])
return f"({to_excel(node['left'], references)}{OPERATORS[node['operator']]}{to_excel(node['right'], references)})"
def evaluate(node, references):
"""Evaluate a validated expression with Decimal values and #N/A semantics."""
with localcontext() as decimal_context:
decimal_context.prec = 64
return _evaluate(node, references)
def _evaluate(node, references):
if "metric" in node:
return references.get(node["metric"])
if "number" in node:
return Decimal(str(node["number"]))
left, right = _evaluate(node["left"], references), _evaluate(node["right"], references)
if left is None or right is None:
return None
if node["operator"] == "add":
return left + right
if node["operator"] == "subtract":
return left - right
if node["operator"] == "multiply":
return left * right
return None if right == 0 else left / right
finance/formula_views.py
+72 -10
View File
@@ -3,12 +3,14 @@ from pathlib import Path
import httpx
from django.conf import settings
from django.db import transaction
from django.db.models import Q
from django.http import FileResponse, JsonResponse
from django.utils import timezone
from .analysis import METRICS, context, dimensions
from .formula_columns import available_metrics, ensure_available_metrics, validate_column
from .models import FormulaDefinition, FormulaExport
from .models import Dataset, FormulaDefinition, FormulaExport
from .views import APIError, audit, body, dataset, endpoint, limit
@@ -190,13 +192,23 @@ def formula_definitions(request, dataset_id):
if request.member.departments:
raise APIError("科室授权账号不可管理跨范围公式定义", 403)
if request.method == "GET":
items = FormulaDefinition.objects.filter(dataset=ds, user=request.user).order_by("normalized_name")
items = (
FormulaDefinition.objects.filter(dataset=ds)
.filter(Q(user=request.user) | Q(shared=True))
.select_related("user")
.order_by("normalized_name")
)
return JsonResponse(
{
"items": [
{
"id": str(item.pk),
"definition": item.definition,
"revision": item.revision,
"shared": item.shared,
"owned": item.user_id == request.user.pk,
"owner": item.user.username,
"history": item.history,
"updated_at": item.updated_at.isoformat(),
}
for item in items
@@ -204,28 +216,74 @@ def formula_definitions(request, dataset_id):
}
)
definition = validate_column(body(request).get("definition"))
data = body(request)
definition = validate_column(data.get("definition"))
shared = data.get("shared", False)
if type(shared) is not bool:
raise APIError("共享选项无效")
try:
ensure_available_metrics(ds, definition)
except ValueError as exc:
raise APIError(str(exc)) from exc
normalized_name = definition["name"].strip().casefold()
existing = FormulaDefinition.objects.filter(
dataset=ds, user=request.user, normalized_name=normalized_name
).first()
if not existing and FormulaDefinition.objects.filter(dataset=ds, user=request.user).count() >= 50:
requested_revision = data.get("revision")
with transaction.atomic():
Dataset.objects.select_for_update().get(pk=ds.pk)
existing = (
FormulaDefinition.objects.select_for_update()
.filter(dataset=ds, user=request.user, normalized_name=normalized_name)
.first()
)
if existing:
if requested_revision is not None and (
type(requested_revision) is not int or requested_revision != existing.revision
):
raise APIError("公式定义已在其他窗口更新，请重新载入", 409)
# ponytail: retain 20 revisions; use a separate history table if long-term retention is required.
existing.history = [
*existing.history,
{
"revision": existing.revision,
"definition": existing.definition,
"updated_at": existing.updated_at.isoformat(),
},
][-20:]
existing.definition = definition
existing.shared = shared if "shared" in data else existing.shared
existing.revision += 1
existing.save(update_fields=["definition", "revision", "shared", "history", "updated_at"])
item, created = existing, False
else:
if FormulaDefinition.objects.filter(dataset=ds, user=request.user).count() >= 50:
raise APIError("每个数据集最多保存 50 个公式定义")
item, created = FormulaDefinition.objects.update_or_create(
item = FormulaDefinition.objects.create(
dataset=ds,
user=request.user,
normalized_name=normalized_name,
defaults={"definition": definition},
definition=definition,
shared=shared,
)
created = True
audit(
request,
"formula_definition.save",
item.pk,
{
"dataset": str(ds.pk),
"name": definition["name"],
"shared": item.shared,
"revision": item.revision,
},
)
audit(request, "formula_definition.save", item.pk, {"dataset": str(ds.pk), "name": definition["name"]})
return JsonResponse(
{
"id": str(item.pk),
"definition": item.definition,
"revision": item.revision,
"shared": item.shared,
"owned": True,
"owner": request.user.username,
"history": item.history,
"updated_at": item.updated_at.isoformat(),
},
status=201 if created else 200,
@@ -241,6 +299,8 @@ def formula_definition_delete(request, dataset_id, definition_id):
if not item:
raise APIError("公式定义不存在", 404)
name = item.definition.get("name", "")
with transaction.atomic():
item = FormulaDefinition.objects.select_for_update().get(pk=item.pk)
audit(request, "formula_definition.delete", item.pk, {"dataset": str(ds.pk), "name": name})
item.delete()
return JsonResponse({"deleted": True})
@@ -279,6 +339,8 @@ def formula_export_create(request, dataset_id):
def _owned_job(request, dataset_id, export_id):
if not request.user.is_active or request.member.role not in ("admin", "analyst"):
raise APIError("没有此导出任务的访问权限", 403)
job = FormulaExport.objects.filter(
pk=export_id,
dataset_id=dataset_id,
finance/management/commands/cleanup_formula_exports.py
+16 -6
View File
@@ -5,8 +5,10 @@ from pathlib import Path
from django.conf import settings
from django.core.management.base import BaseCommand
from django.db import transaction
from django.db.models import Q
from django.utils import timezone
from finance.exporting import _safe_unlink_export
from finance.models import FormulaExport
@@ -29,22 +31,30 @@ class Command(BaseCommand):
export_root = Path(settings.MEDIA_ROOT) / "formula-exports"
if export_root.is_symlink():
raise ValueError("公式导出目录不能是符号链接")
root = export_root.resolve()
root = export_root.absolute()
now = timezone.now()
jobs = files = 0
for job in FormulaExport.objects.filter(status="succeeded", expires_at__lte=now).iterator():
jobs_qs = FormulaExport.objects.filter(
Q(status="succeeded", expires_at__lte=now) | Q(status="failed", path__gt="")
).iterator()
for job in jobs_qs:
path = Path(job.path) if job.path else None
resolved = path.resolve() if path and path.is_absolute() and not path.is_symlink() else None
in_root = bool(resolved and resolved.is_relative_to(root))
try:
with transaction.atomic():
if in_root and resolved.is_file():
resolved.unlink()
deleted = bool(path and _safe_unlink_export(path, root))
if job.status == "succeeded":
if deleted or not path or not path.is_absolute() or not path.is_relative_to(root):
if deleted:
files += 1
job.status, job.path = "expired", ""
job.save(update_fields=["status", "path"])
jobs += 1
elif deleted:
files += 1
job.path = ""
job.save(update_fields=["path"])
jobs += 1
except OSError as exc:
self.stderr.write(f"Could not remove expired export {job.pk}: {exc}")
finance/management/commands/import_worker.py
+1 -1
View File
@@ -24,7 +24,7 @@ class Command(BaseCommand):
self.stdout.write("Another worker owns the database lock")
return
try:
self.run_jobs(options, postgres)
self.run_jobs(options, True)
finally:
if postgres:
with connection.cursor() as cursor:
finance/migrations/0006_formuladefinition_revision.py
+15
View File
@@ -0,0 +1,15 @@
from django.db import migrations, models
class Migration(migrations.Migration):
dependencies = [
("finance", "0005_aiconfig"),
]
operations = [
migrations.AddField(
model_name="formuladefinition",
name="revision",
field=models.PositiveIntegerField(default=1),
),
]
finance/migrations/0007_alter_dataset_file.py
+15
View File
@@ -0,0 +1,15 @@
from django.db import migrations, models
class Migration(migrations.Migration):
dependencies = [
("finance", "0006_formuladefinition_revision"),
]
operations = [
migrations.AlterField(
model_name="dataset",
name="file",
field=models.FileField(max_length=500, upload_to="sources/%Y/%m"),
),
]
finance/migrations/0008_formuladefinition_history_formuladefinition_shared.py
+22
View File
@@ -0,0 +1,22 @@
# Generated by Django 5.2.17 on 2026-10-07 23:50
from django.db import migrations, models
class Migration(migrations.Migration):
dependencies = [
("finance", "0007_alter_dataset_file"),
]
operations = [
migrations.AddField(
model_name="formuladefinition",
name="history",
field=models.JSONField(default=list),
),
migrations.AddField(
model_name="formuladefinition",
name="shared",
field=models.BooleanField(default=False),
),
]
finance/models.py
+4 -1
View File
@@ -22,7 +22,7 @@ class Dataset(models.Model):
organization = models.ForeignKey(Organization, on_delete=models.PROTECT)
created_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.PROTECT)
name = models.CharField(max_length=200)
file = models.FileField(upload_to="sources/%Y/%m")
file = models.FileField(upload_to="sources/%Y/%m", max_length=500)
sha256 = models.CharField(max_length=64)
status = models.CharField(max_length=16, default="mapping")
profile = models.JSONField(default=dict)
@@ -133,6 +133,9 @@ class FormulaDefinition(models.Model):
user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
normalized_name = models.CharField(max_length=320)
definition = models.JSONField()
revision = models.PositiveIntegerField(default=1)
shared = models.BooleanField(default=False)
history = models.JSONField(default=list)
created_at = models.DateTimeField(auto_now_add=True)
updated_at = models.DateTimeField(auto_now=True)
finance/views.py
+16 -4
View File
@@ -22,7 +22,7 @@ from django.utils import timezone
from openpyxl import Workbook
from openpyxl.cell import WriteOnlyCell
from .analysis import METRICS, analyze, context, default_preferences, dimensions, scoped_facts
from .analysis import METRICS, analyze, context, custom_columns, default_preferences, dimensions, scoped_facts
from .importing import file_digest, profile_file, suggest_mapping, validate_mapping
from .models import AIConfig, Audit, Dataset, Investigation, Membership, Preference, Throttle
@@ -297,6 +297,14 @@ def query(request, dataset_id):
return JsonResponse(analyze(ds, request.member, body(request), pref))
def amount_text(value):
"""金额展示文本：去掉定点数存储尺度产生的多余尾零（如 10.000000000000000000000000 → 10）。"""
if value is None:
return None
text = format(value.normalize(), "f")
return "0" if text == "-0" else text
@endpoint(("POST",))
def evidence(request, dataset_id):
ds = dataset(request, dataset_id, True)
@@ -314,6 +322,9 @@ def evidence(request, dataset_id):
"source_row", "period", "dimensions", "revenue", "cost", "gross", "raw"
)
)
for row in rows:
for field in ("revenue", "cost", "gross"):
row[field] = amount_text(row[field])
# Raw patient/customer identifiers require an explicit administrator role.
if request.member.role != "admin":
for row in rows:
@@ -412,9 +423,9 @@ def export(request, dataset_id):
row.source_row,
row.period,
*[row.dimensions.get(d["key"], "") for d in dims],
row.revenue,
row.cost,
row.gross,
amount_text(row.revenue),
amount_text(row.cost),
amount_text(row.gross),
]
)
if workbook:
@@ -510,6 +521,7 @@ def investigations(request, dataset_id):
raise APIError("查看者不可修改调查状态", 403)
data = body(request)
ctx = context(ds, data.get("context", {}))
ctx["custom_columns"] = custom_columns(ds, data.get("context", {}))
status, note = data.get("status"), data.get("note", "")
if status not in ("open", "reviewing", "resolved") or not isinstance(note, str) or len(note) > 2000:
raise APIError("调查状态或备注无效")
frontend/src/App.tsx
+97 -40
View File
@@ -35,6 +35,7 @@ import { Chart } from "./Chart";
import {
AdminDialog,
AIConfigDialog,
FormulaColumnsDialog,
FormulaExportDialog,
Copilot,
EvidenceDialog,
@@ -45,9 +46,12 @@ import {
import {
format,
labels,
roleLabels,
type Analysis,
type Context,
type Dataset,
type FormulaColumn,
type SavedFormulaDefinition,
type Metric,
type Preferences,
type Row,
@@ -88,6 +92,7 @@ export function App() {
| "ai"
| null
| "formula"
| "columns"
>(null),
[evidenceCtx, setEvidenceCtx] = useState<Context>(),
[chat, setChat] = useState(false),
@@ -97,6 +102,7 @@ export function App() {
[note, setNote] = useState(""),
[status, setStatus] = useState("open"),
[saved, setSaved] = useState(false);
const [formulaDefinitions, setFormulaDefinitions] = useState<SavedFormulaDefinition[]>([]);
const upload = useRef<HTMLInputElement>(null),
requestVersion = useRef(0);
const report = useCallback(
@@ -225,6 +231,8 @@ export function App() {
c.comparison === current.comparison &&
c.metric === current.metric &&
c.group === current.group &&
JSON.stringify(c.custom_columns || []) ===
JSON.stringify(current.custom_columns || []) &&
Object.keys(c.filters).length ===
Object.keys(current.filters).length &&
Object.entries(c.filters).every(
@@ -308,6 +316,26 @@ export function App() {
setPath([]);
setModal(null);
}
// 全部分组都为空的指标列（如数据集没有成本/ROI）不展示，避免占位挤压可用列。
const metricKeys = useMemo(() => {
const candidates = [
...new Set<Metric>([
"revenue",
"gross",
"cost",
"roi",
"customers",
ctx?.metric || "revenue",
]),
];
const available = candidates.filter(
(m) => m === "revenue" || (result?.rows || []).some((r) => r.values[m]?.current != null),
);
const custom = (ctx?.custom_columns || []).map((_, index) => `custom_${index}`);
return [...(available.length ? available : ["revenue"]), ...custom];
}, [result, ctx]);
const metricLabel = (metric: string) =>
labels[metric as Metric] || ctx?.custom_columns?.[Number(metric.slice(7))]?.name || metric;
const columns = useMemo<ColDef<Row>[]>(
() => [
{
@@ -327,23 +355,14 @@ export function App() {
</button>
),
},
...[
...new Set<Metric>([
"revenue",
"gross",
"cost",
"roi",
"customers",
ctx?.metric || "revenue",
]),
].map((metric) => ({
...metricKeys.map((metric) => ({
colId: metric,
headerName: labels[metric],
headerName: metricLabel(metric),
minWidth: 130,
valueGetter: (p: { data?: Row }) =>
p.data?.values[metric].current == null
p.data?.values[metric]?.current == null
? null
: Number(p.data.values[metric].current),
: Number(p.data.values[metric]?.current),
valueFormatter: (p: { value: unknown }) =>
format(p.value as number, metric),
cellClass: metric === ctx?.metric ? "metric-column" : "",
@@ -396,24 +415,34 @@ export function App() {
),
},
],
[ds, ctx, path],
[ds, ctx, path, metricKeys],
);
const rows = (result?.rows || []).filter(
(r) => (screen !== "anomalies" || r.anomaly) && r.label.includes(search),
);
const metric = ctx?.metric || "revenue";
const compactAxis = (value: number) => {
const abs = Math.abs(value);
if (abs >= 1e8) return `${value / 1e8}亿`;
if (abs >= 1e4) return `${value / 1e4}万`;
return String(value);
};
const trend = useMemo(
() => ({
color: ["#197568", "#5794c9"],
tooltip: { trigger: "axis" },
legend: { bottom: 0 },
grid: { top: 25, left: 65, right: 25, bottom: 65 },
grid: { top: 25, left: 85, right: 25, bottom: 65 },
xAxis: {
type: "category",
data: result?.trend.map((r) => r.period),
axisLabel: { color: "#73827b" },
axisLabel: { color: "#73827b", hideOverlap: true },
},
yAxis: {
type: "value",
splitLine: { lineStyle: { color: "#ecf0ee" } },
axisLabel: { hideOverlap: true },
},
yAxis: { type: "value", splitLine: { lineStyle: { color: "#ecf0ee" } } },
series: [
{
name: "实际收入",
@@ -443,7 +472,11 @@ export function App() {
() => ({
tooltip: { trigger: "axis" },
grid: { top: 15, left: 95, right: 30, bottom: 30 },
xAxis: { type: "value", splitLine: { lineStyle: { color: "#ecf0ee" } } },
xAxis: {
type: "value",
splitLine: { lineStyle: { color: "#ecf0ee" } },
axisLabel: { hideOverlap: true, formatter: compactAxis },
},
yAxis: {
type: "category",
inverse: true,
@@ -878,6 +911,8 @@ export function App() {
missing_gross: "毛利缺失",
missing_customer: "客户标识缺失",
blank_rows: "空白行",
g_l_mismatch: "应收金额两列不一致",
i_n_mismatch: "实收与实际收入不一致",
} as Record<string, string>
)[k] || k}
<strong>{v.toLocaleString()}</strong>
@@ -1030,15 +1065,10 @@ export function App() {
)?.name
}
</th>
{(
[
"revenue",
"gross",
"cost",
"roi",
] as Metric[]
).map((m) => (
<th key={m}>{labels[m]}</th>
{metricKeys
.filter((m) => m !== "customers" && m in labels)
.map((m) => (
<th key={m}>{metricLabel(m)}</th>
))}
<th>状态</th>
</tr>
@@ -1047,15 +1077,10 @@ export function App() {
{result.rows.slice(0, 12).map((r) => (
<tr key={r.label}>
<th>{r.label}</th>
{(
[
"revenue",
"gross",
"cost",
"roi",
] as Metric[]
).map((m) => {
const v = r.values[m].change;
{metricKeys
.filter((m) => m !== "customers" && m in labels)
.map((m) => {
const v = r.values[m]?.change;
return (
<td key={m}>
<button
@@ -1069,7 +1094,7 @@ export function App() {
: "heat-positive"
}
onClick={() => {
setCtx({ ...ctx, metric: m });
setCtx({ ...ctx, metric: m as Metric });
setSelected(r);
setScreen("anomalies");
}}
@@ -1100,7 +1125,7 @@ export function App() {
<div className="investigation-band">
<section>
<div className="section-heading">
<h2>{labels[metric]}变化分解</h2>
<h2>{metricLabel(metric)}变化分解</h2>
<span>
{ctx.start} 至 {ctx.end}
</span>
@@ -1209,6 +1234,22 @@ export function App() {
>
<Calculator />
</button>
{!user.departments.length && (
<button
aria-label="选择网页自定义列"
title="选择网页自定义列"
onClick={() =>
api<{ items: SavedFormulaDefinition[] }>(`/datasets/${ds.id}/formula-definitions`)
.then((data) => {
setFormulaDefinitions(data.items);
setModal("columns");
})
.catch(report)
}
>
自定义列
</button>
)}
<button
className="icon"
aria-label="导出当前范围"
@@ -1227,6 +1268,7 @@ export function App() {
</div>
</div>
<div className="grid-layout">
{rows.length ? (
<div className="grid">
<AgGridReact<Row>
theme={gridTheme}
@@ -1243,7 +1285,6 @@ export function App() {
getRowId={(p) => p.data.label}
onRowClicked={(e) => setSelected(e.data)}
localeText={{
noRowsToShow: "没有符合条件的分组",
page: "页",
of: "/",
to: "至",
@@ -1269,6 +1310,11 @@ export function App() {
: {})}
/>
</div>
) : (
<p role="status" className="grid-empty">
没有符合条件的分组
</p>
)}
{selected && (
<aside className="explanation">
<div className="section-heading">
@@ -1423,6 +1469,17 @@ export function App() {
{modal === "formula" && ds && ctx && (
<FormulaExportDialog ds={ds} ctx={ctx} onClose={() => setModal(null)} />
)}
{modal === "columns" && ds && ctx && (
<FormulaColumnsDialog
definitions={formulaDefinitions}
selected={ctx.custom_columns || []}
onClose={() => setModal(null)}
onApply={(columns: FormulaColumn[]) => {
setCtx({ ...ctx, custom_columns: columns });
setModal(null);
}}
/>
)}
{modal === "settings" && ds && (
<SettingsDialog
ds={ds}
@@ -1472,7 +1529,7 @@ export function App() {
<tr key={c.index}>
<td>{c.name}</td>
<td>{c.description}</td>
<td>{c.role}</td>
<td>{roleLabels[c.role] || c.role}</td>
</tr>
))}
</tbody>
frontend/src/dialogs.tsx
+154 -35
View File
@@ -1,11 +1,22 @@
import { useEffect, useRef, useState } from "react";
import { ChevronLeft, ChevronRight, Download, Save, Trash2, X } from "lucide-react";
import {
ChevronLeft,
ChevronRight,
Download,
Save,
Trash2,
X,
} from "lucide-react";
import { api } from "./api";
import {
labels,
roleLabels,
type AIConfig,
type Context,
type Dataset,
type FormulaColumn,
type FormulaExpression,
type SavedFormulaDefinition,
type Metric,
type Preferences,
type Session,
@@ -13,21 +24,6 @@ import {
type User = NonNullable<Session["user"]>;
type FormulaExpression =
| { metric: Metric }
| { number: number }
| {
operator: "add" | "subtract" | "multiply" | "divide";
left: FormulaExpression;
right: FormulaExpression;
};
type SavedFormulaDefinition = {
id: string;
definition: Record<string, unknown>;
updated_at: string;
};
function describeExpression(expression: FormulaExpression): string {
if ("metric" in expression) return labels[expression.metric];
if ("number" in expression) return String(expression.number);
@@ -165,7 +161,9 @@ export function ImportDialog({
"dimension",
"ignore",
].map((r) => (
<option key={r}>{r}</option>
<option key={r} value={r}>
{roleLabels[r] || r}
</option>
))}
</select>
</td>
@@ -367,6 +365,7 @@ export function EvidenceDialog({
};
}, [ds.id, ctx, side, page]);
function selectSide(value: "current" | "baseline") {
if (value === side && page === 0) return;
setResult(undefined);
setPage(0);
setSide(value);
@@ -406,7 +405,7 @@ export function EvidenceDialog({
</p>
)}
<div className="table-scroll">
<table>
<table className="evidence-table">
<thead>
<tr>
<th>来源行</th>
@@ -823,7 +822,7 @@ export function FormulaExportDialog({
{ role: "user" | "assistant"; content: string }[]
>([]),
[draft, setDraft] = useState(""),
[column, setColumn] = useState<Record<string, unknown> | null>(null),
[column, setColumn] = useState<FormulaColumn | null>(null),
[questions, setQuestions] = useState<string[]>([]),
[task, setTask] = useState<{
id: string;
@@ -832,9 +831,30 @@ export function FormulaExportDialog({
}>(),
[definitions, setDefinitions] = useState<SavedFormulaDefinition[]>([]),
[selectedDefinition, setSelectedDefinition] = useState(""),
[shared, setShared] = useState(false),
[loadedDefinition, setLoadedDefinition] = useState<FormulaColumn | null>(null),
[consent, setConsent] = useState(false),
[busy, setBusy] = useState(false),
[error, setError] = useState("");
const draftKey = `traceiq.formula-draft.${ds.id}`;
const [draftRestored, setDraftRestored] = useState(false);
useEffect(() => {
try {
const stored = sessionStorage.getItem(draftKey);
if (stored) {
const saved = JSON.parse(stored);
setDraft(saved.draft || "");
setMessages(saved.messages || []);
setColumn(saved.column || null);
}
} catch {
sessionStorage.removeItem(draftKey);
}
setDraftRestored(true);
}, [draftKey]);
useEffect(() => {
if (draftRestored) sessionStorage.setItem(draftKey, JSON.stringify({ draft, messages, column }));
}, [draftKey, draftRestored, draft, messages, column]);
useEffect(() => {
api<{ items: SavedFormulaDefinition[] }>(
`/datasets/${ds.id}/formula-definitions`,
@@ -861,7 +881,10 @@ export function FormulaExportDialog({
async function send() {
const text = draft.trim();
if (!text || !consent || userTurns >= 6) return;
const next = [...messages, { role: "user" as const, content: text }];
const content = loadedDefinition
? `已载入的原始定义：${JSON.stringify(loadedDefinition)}\n本次补充：${text}`
: text;
const next = [...messages, { role: "user" as const, content }];
setBusy(true);
setError("");
setQuestions([]);
@@ -870,7 +893,7 @@ export function FormulaExportDialog({
const result = await api<{
complete: boolean;
questions: string[];
column: Record<string, unknown> | null;
column: FormulaColumn | null;
}>(`/datasets/${ds.id}/formula-design`, "POST", {
context: ctx,
messages: next,
@@ -887,6 +910,7 @@ export function FormulaExportDialog({
]);
setQuestions(result.questions);
setColumn(result.column);
setLoadedDefinition(null);
setDraft("");
} catch (e) {
setError((e as Error).message);
@@ -895,7 +919,7 @@ export function FormulaExportDialog({
}
}
async function create() {
if (!column) return;
if (!column && !ctx.custom_columns?.length) return;
setBusy(true);
setError("");
try {
@@ -903,7 +927,12 @@ export function FormulaExportDialog({
await api<{ id: string; status: string }>(
`/datasets/${ds.id}/formula-exports`,
"POST",
{ ...ctx, custom_columns: [column] },
{
...ctx,
custom_columns: column
? [...(ctx.custom_columns || []).filter((item) => item.name !== column.name), column]
: ctx.custom_columns,
},
),
);
} catch (e) {
@@ -920,11 +949,24 @@ export function FormulaExportDialog({
const saved = await api<SavedFormulaDefinition>(
`/datasets/${ds.id}/formula-definitions`,
"POST",
{ definition: column },
{
definition: column,
shared,
revision: definitions.find(
(item) =>
(item.id === selectedDefinition && item.owned) ||
(item.owned &&
String(item.definition.name).trim().toLowerCase() ===
String(column.name).trim().toLowerCase()),
)?.revision,
},
);
setDefinitions((items) =>
[...items.filter((item) => item.id !== saved.id), saved].sort((a, b) =>
String(a.definition.name).localeCompare(String(b.definition.name), "zh-CN"),
String(a.definition.name).localeCompare(
String(b.definition.name),
"zh-CN",
),
),
);
setSelectedDefinition(saved.id);
@@ -937,8 +979,14 @@ export function FormulaExportDialog({
function loadDefinition(id: string) {
setSelectedDefinition(id);
const saved = definitions.find((item) => item.id === id);
if (!saved) return;
if (!saved) {
setLoadedDefinition(null);
setColumn(null);
return;
}
setColumn(saved.definition);
setShared(saved.owned && saved.shared);
setLoadedDefinition(saved.definition);
setMessages([]);
setQuestions([]);
setDraft("");
@@ -956,6 +1004,7 @@ export function FormulaExportDialog({
items.filter((item) => item.id !== selectedDefinition),
);
setSelectedDefinition("");
setLoadedDefinition(null);
setColumn(null);
} catch (e) {
setError((e as Error).message);
@@ -965,15 +1014,10 @@ export function FormulaExportDialog({
}
async function getFile() {
try {
const blob = await api<Blob>(
`/datasets/${ds.id}/formula-exports/${task!.id}/download`,
),
url = URL.createObjectURL(blob),
link = document.createElement("a");
link.href = url;
const link = document.createElement("a");
link.href = `/api/datasets/${ds.id}/formula-exports/${task!.id}/download`;
link.download = "traceiq-formula-analysis.xlsx";
link.click();
window.setTimeout(() => URL.revokeObjectURL(url), 1000);
} catch (e) {
setError((e as Error).message);
}
@@ -982,6 +1026,11 @@ export function FormulaExportDialog({
<Modal title="生成公式工作簿" onClose={onClose} wide>
<div className="dialog-body">
<p>描述新列含义、计算方式、适用范围，以及缺失值或零分母处理方式。</p>
{!column && !task && !!ctx.custom_columns?.length && (
<button disabled={busy} onClick={create}>
按网页已选 {ctx.custom_columns.length} 列生成工作簿
</button>
)}
{definitions.length > 0 && !task && (
<div className="form-row">
<label>
@@ -994,7 +1043,7 @@ export function FormulaExportDialog({
<option value="">选择定义</option>
{definitions.map((item) => (
<option key={item.id} value={item.id}>
{String(item.definition.name)}
{item.definition.name}{item.owned ? "" : `（${item.owner}共享）`}
</option>
))}
</select>
@@ -1003,13 +1052,27 @@ export function FormulaExportDialog({
className="icon"
aria-label="删除所选定义"
title="删除所选定义"
disabled={busy || !selectedDefinition}
disabled={busy || !definitions.find((item) => item.id === selectedDefinition)?.owned}
onClick={deleteDefinition}
>
<Trash2 />
</button>
</div>
)}
{definitions.find((item) => item.id === selectedDefinition)?.history.length ? (
<details>
<summary>版本历史（最近 20 次，可载入后另存）</summary>
{definitions.find((item) => item.id === selectedDefinition)!.history.map((version) => (
<button key={version.revision} disabled={busy} onClick={() => {
setColumn(version.definition);
setLoadedDefinition(version.definition);
setMessages([]);
}}>
载入版本 {version.revision} · {version.definition.meaning}
</button>
))}
</details>
) : null}
<label className="check">
<input
type="checkbox"
@@ -1052,6 +1115,10 @@ export function FormulaExportDialog({
</p>
<p>适用范围：{String(column.scope)}</p>
<p>缺失/零分母：{String(column.missing)}</p>
<label className="check">
<input type="checkbox" checked={shared} onChange={(event) => setShared(event.target.checked)} />
保存时共享给当前数据集的其他全范围分析账号
</label>
<button
disabled={busy || Boolean(draft.trim())}
onClick={saveDefinition}
@@ -1098,3 +1165,55 @@ export function FormulaExportDialog({
</Modal>
);
}
export function FormulaColumnsDialog({
definitions,
selected,
onApply,
onClose,
}: {
definitions: SavedFormulaDefinition[];
selected: FormulaColumn[];
onApply: (columns: FormulaColumn[]) => void;
onClose: () => void;
}) {
const selectedNames = new Set(selected.map((column) => column.name));
const [checked, setChecked] = useState(
() => new Set(definitions.filter((item) => selectedNames.has(item.definition.name)).map((item) => item.id)),
);
function toggle(id: string) {
setChecked((current) => {
const next = new Set(current);
if (next.has(id)) next.delete(id);
else if (next.size < 10) next.add(id);
return next;
});
}
return (
<Modal title="选择网页自定义列" onClose={onClose}>
<div className="dialog-body">
<p>已保存定义会由服务端统一计算并显示在分析表格中，最多选择 10 列。</p>
{!definitions.length && <p className="muted">暂无已保存定义，请先在公式工作簿中保存。</p>}
{definitions.map((item) => (
<label className="check" key={item.id}>
<input
type="checkbox"
checked={checked.has(item.id)}
onChange={() => toggle(item.id)}
/>
{item.definition.name}：{item.definition.meaning}{item.owned ? "" : `（${item.owner}共享）`}
</label>
))}
<footer>
<button onClick={onClose}>取消</button>
<button
className="primary"
onClick={() => onApply(definitions.filter((item) => checked.has(item.id)).map((item) => item.definition))}
>
应用
</button>
</footer>
</div>
</Modal>
);
}
frontend/src/style.css
+22 -7
View File
@@ -484,7 +484,7 @@ a {
display: flex;
flex-direction: column;
align-items: stretch;
padding: 20px 20px 16px;
padding: 18px 16px 14px;
gap: 16px;
background: white;
min-width: 0;
@@ -510,12 +510,13 @@ a {
}
.kpi strong {
text-align: left;
font-size: 24px;
font-size: 21px;
line-height: 1.25;
font-weight: 620;
font-variant-numeric: tabular-nums;
white-space: normal;
overflow-wrap: anywhere;
white-space: nowrap;
overflow: hidden;
text-overflow: ellipsis;
}
.kpi-bottom {
display: flex;
@@ -1271,9 +1272,6 @@ details {
.kpi {
padding: 17px 13px;
}
.kpi strong {
font-size: 21px;
}
.chart-band {
gap: 15px;
}
@@ -1562,3 +1560,20 @@ details {
scroll-behavior: auto !important;
}
}
.grid-empty {
margin: 0;
padding: 40px 0;
text-align: center;
color: var(--muted, #5b6675);
border: 1px dashed var(--line);
border-radius: 6px;
width: 100%;
}
.evidence-table {
min-width: 960px;
}
.evidence-table th {
white-space: nowrap;
}
frontend/src/types.ts
+41 -1
View File
@@ -32,6 +32,20 @@ export interface Column {
key: string;
confidence: number;
}
export const roleLabels: Record<string, string> = {
period: "期间",
year: "年份",
month: "月份",
dimension: "维度",
customer: "客户标识",
quantity: "数量",
revenue: "实际收入",
cost: "成本",
gross: "毛利贡献",
list_amount: "应收金额",
discount: "折扣金额",
ignore: "未使用",
};
export interface Mapping {
sheet: string;
header: number;
@@ -84,6 +98,32 @@ export interface Context {
filters: Record<string, string>;
group: string;
metric: Metric;
custom_columns?: FormulaColumn[];
}
export type FormulaExpression =
| { metric: Metric }
| { number: number }
| {
operator: "add" | "subtract" | "multiply" | "divide";
left: FormulaExpression;
right: FormulaExpression;
};
export interface FormulaColumn {
name: string;
meaning: string;
scope: string;
missing: string;
expression: FormulaExpression;
}
export interface SavedFormulaDefinition {
id: string;
definition: FormulaColumn;
revision: number;
updated_at: string;
shared: boolean;
owned: boolean;
owner: string;
history: { revision: number; definition: FormulaColumn; updated_at: string }[];
}
export interface Value {
current: Numeric;
@@ -93,7 +133,7 @@ export interface Value {
}
export interface Row {
label: string;
values: Record<Metric, Value>;
values: Record<string, Value>;
rows: number;
baseline_rows: number;
anomaly: boolean;
tests/e2e/formula-export.spec.ts
+24 -3
View File
@@ -119,15 +119,36 @@ test("新增列：补充信息触发追问并重新确认后生成公式工作
);
await dialog.getByLabel("关闭", { exact: true }).click();
await page.getByRole("button", { name: "选择网页自定义列" }).click();
const columns = page.getByRole("dialog", { name: "选择网页自定义列" });
await columns.getByRole("checkbox", { name: /收入成本差/ }).check();
const customQuery = page.waitForResponse(
(response) =>
response.url().includes("/query") && response.request().method() === "POST",
);
await columns.getByRole("button", { name: "应用", exact: true }).click();
const customResult = await (await customQuery).json();
expect(customResult.summary.custom_0.current).toBe("1000");
expect(customResult.rows[0].values.custom_0.current).toBe("1000");
await page.getByRole("button", { name: "生成公式工作簿" }).click();
const reopened = page.getByRole("dialog", { name: "生成公式工作簿" });
await reopened.getByLabel("已保存定义").selectOption({ label: "收入成本差" });
await expect(reopened).toContainText("计算表达式：(实际收入 − 成本)");
await reopened.getByLabel("列计算描述").fill("把刚才公式乘以 100");
await expect(
reopened.getByRole("button", { name: "先发送补充内容" }),
).toBeDisabled();
await reopened
.getByRole("button", { name: "确认并生成公式工作簿" })
.click();
.getByLabel("同意将描述、指标定义和当前筛选范围发送至已配置的外部 AI 服务")
.check();
await reopened.getByRole("button", { name: "发送描述" }).click();
expect(sentMessages[0].content).toContain('"name":"收入成本差"');
expect(sentMessages[0].content).toContain('"operator":"subtract"');
expect(sentMessages[0].content).toContain("把刚才公式乘以 100");
await reopened.getByRole("button", { name: "确认并生成公式工作簿" }).click();
await expect(reopened.getByRole("status")).toContainText("生成完成", {
timeout: 30000,
});
expect(designCalls).toBe(4);
expect(designCalls).toBe(5);
});
tests/e2e/workspace.spec.ts
+41 -10
View File
@@ -30,7 +30,9 @@ test("桌面：真实导入、异常、下钻、证据、设置、Copilot、导
});
await uploaded;
const dialog = page.getByRole("dialog");
await expect(dialog.getByRole("heading", { name: "确认导入字段" })).toBeVisible();
await expect(
dialog.getByRole("heading", { name: "确认导入字段" }),
).toBeVisible();
await dialog.getByRole("button", { name: "确认并导入" }).click();
await expect(
page.getByRole("heading", { name: "集团经营总览", exact: true }),
@@ -85,7 +87,14 @@ test("桌面：真实导入、异常、下钻、证据、设置、Copilot、导
await expect(evidenceDialog).toContainText("1 条");
await expect(evidenceDialog).toContainText("2026-08");
await expect(evidenceDialog).toContainText("CSV");
await evidenceDialog.getByRole("button", { name: "基期", exact: true }).click();
await evidenceDialog
.getByRole("button", { name: "本期", exact: true })
.click();
await expect(evidenceDialog).toContainText("2026-08");
await expect(evidenceDialog).not.toContainText("加载中");
await evidenceDialog
.getByRole("button", { name: "基期", exact: true })
.click();
await expect(evidenceDialog).toContainText("2025-08");
await evidenceDialog.getByLabel("关闭", { exact: true }).click();
const evidenceOffsets: number[] = [];
@@ -111,10 +120,17 @@ test("桌面：真实导入、异常、下钻、证据、设置、Copilot、导
});
await page.getByLabel("查看医生A证据", { exact: true }).click();
await expect(evidenceDialog).toContainText("第 1-50 条，共 51 条");
await evidenceDialog.getByRole("button", { name: "下一页", exact: true }).click();
await evidenceDialog
.getByRole("button", { name: "下一页", exact: true })
.click();
await expect(evidenceDialog).toContainText("第 51-51 条，共 51 条");
await expect(evidenceDialog).toContainText("2026-09");
expect(evidenceOffsets).toEqual([0, 50]);
await evidenceDialog
.getByRole("button", { name: "基期", exact: true })
.click();
await expect(evidenceDialog).toContainText("第 1-50 条，共 51 条");
await expect(evidenceDialog).not.toContainText("2026-09");
await evidenceDialog.getByLabel("关闭", { exact: true }).click();
await page
.getByRole("navigation", { name: "分析路径" })
@@ -164,8 +180,13 @@ test("桌面：真实导入、异常、下钻、证据、设置、Copilot、导
await page.route("**/formula-design", async (route) => {
designCalls++;
await route.fulfill({
json: designCalls === 1
? { complete: false, questions: ["缺失值和零分母如何处理？"], column: null }
json:
designCalls === 1
? {
complete: false,
questions: ["缺失值和零分母如何处理？"],
column: null,
}
: {
complete: true,
questions: [],
@@ -186,16 +207,26 @@ test("桌面：真实导入、异常、下钻、证据、设置、Copilot、导
await page.getByRole("button", { name: "生成公式工作簿" }).click();
const formulaDialog = page.getByRole("dialog", { name: "生成公式工作簿" });
await formulaDialog.getByRole("checkbox").check();
await formulaDialog.getByLabel("列计算描述").fill("新增收入成本差，缺失或零分母显示不可用");
await formulaDialog
.getByLabel("列计算描述")
.fill("新增收入成本差，缺失或零分母显示不可用");
await formulaDialog.getByRole("button", { name: "发送描述" }).click();
await expect(formulaDialog).toContainText("缺失值和零分母如何处理？");
await formulaDialog.getByLabel("列计算描述").fill("缺失项和零分母都按不可用处理");
await formulaDialog
.getByLabel("列计算描述")
.fill("缺失项和零分母都按不可用处理");
await formulaDialog.getByRole("button", { name: "发送描述" }).click();
await formulaDialog.getByRole("button", { name: "确认并生成公式工作簿" }).click();
await expect(formulaDialog.getByRole("status")).toContainText("生成完成", { timeout: 30000 });
await formulaDialog
.getByRole("button", { name: "确认并生成公式工作簿" })
.click();
await expect(formulaDialog.getByRole("status")).toContainText("生成完成", {
timeout: 30000,
});
const formulaDownload = page.waitForEvent("download");
await formulaDialog.getByRole("button", { name: "下载公式工作簿" }).click();
expect((await formulaDownload).suggestedFilename()).toBe("traceiq-formula-analysis.xlsx");
expect((await formulaDownload).suggestedFilename()).toBe(
"traceiq-formula-analysis.xlsx",
);
await formulaDialog.getByLabel("关闭").click();
});
test("手机：登录、总览、设置、Copilot 布局无横向溢出", async ({ page }) => {
tests/test_assistant_api.py
+42 -3
View File
@@ -331,19 +331,34 @@ def test_formula_definition_library_is_private_validated_and_audited(workspace):
"right": {"metric": "cost"},
},
}
saved = client.post(url, data={"definition": definition}, content_type="application/json")
saved = client.post(url, data={"definition": definition, "shared": True}, content_type="application/json")
assert saved.status_code == 201
assert saved.json()["shared"] is True
definition_id = saved.json()["id"]
assert client.get(url).json()["items"][0]["definition"] == definition
updated = client.post(
url,
data={"definition": {**definition, "name": " 收入成本差 ", "meaning": "更新说明"}},
data={
"definition": {**definition, "name": " 收入成本差 ", "meaning": "更新说明"},
"shared": True,
"revision": saved.json()["revision"],
},
content_type="application/json",
)
assert updated.status_code == 200
assert FormulaDefinition.objects.filter(dataset=ds, user=ds.created_by).count() == 1
assert updated.json()["definition"]["meaning"] == "更新说明"
assert updated.json()["revision"] == 2
assert len(updated.json()["history"]) == 1
stale = client.post(
url,
data={"definition": {**definition, "meaning": "旧标签页覆盖"}, "revision": 1},
content_type="application/json",
)
assert stale.status_code == 409
assert FormulaDefinition.objects.get(pk=definition_id).definition["meaning"] == "更新说明"
other = Client()
other_user = ds.created_by.__class__.objects.create_user(
@@ -351,7 +366,8 @@ def test_formula_definition_library_is_private_validated_and_audited(workspace):
)
Membership.objects.create(user=other_user, organization=ds.organization, role="analyst")
assert other.login(username=other_user.username, password="Formula-Library-TestPassword991!")
assert other.get(url).json()["items"] == []
shared = other.get(url).json()["items"]
assert len(shared) == 1 and shared[0]["owned"] is False and shared[0]["shared"] is True
assert other.delete(f"{url}/{definition_id}").status_code == 404
deleted = client.delete(f"{url}/{definition_id}")
@@ -385,3 +401,26 @@ def test_formula_definition_library_rejects_unavailable_metric(workspace):
)
assert response.status_code == 400
assert "成本" in response.json()["error"]
def test_formula_definition_audit_failure_rolls_back_definition(workspace, monkeypatch):
ds, _, client, *_ = workspace
definition = {
"name": "审计回滚",
"meaning": "实际收入",
"scope": "所选分组与期间",
"missing": "结果不可用（#N/A）；零分母不可用",
"expression": {"metric": "revenue"},
}
def fail(*_args, **_kwargs):
raise RuntimeError("audit unavailable")
monkeypatch.setattr("finance.formula_views.audit", fail)
with pytest.raises(RuntimeError, match="audit unavailable"):
client.post(
f"/api/datasets/{ds.pk}/formula-definitions",
data={"definition": definition},
content_type="application/json",
)
assert not FormulaDefinition.objects.filter(dataset=ds, user=ds.created_by).exists()
tests/test_finance.py
+61 -2
View File
@@ -85,6 +85,36 @@ def test_metrics_and_analysis_preserve_roi_refunds_and_distinct_customers(worksp
assert unavailable["self_revenue"] == 0
def test_analysis_computes_saved_custom_columns_in_summary_and_rows(workspace):
ds, member, *_ = workspace
column = {
"name": "收入成本差",
"meaning": "收入减成本",
"scope": "按分组计算",
"missing": "结果不可用（#N/A）；零分母不可用",
"expression": {
"operator": "subtract",
"left": {"metric": "revenue"},
"right": {"metric": "cost"},
},
}
result = analyze(
ds,
member,
{
"start": "2026-08",
"end": "2026-08",
"comparison": "yoy",
"group": "department",
"custom_columns": [column],
},
default_preferences(ds),
)
assert result["summary"]["custom_0"]["current"] == Decimal("45")
assert result["summary"]["custom_0"]["baseline"] == Decimal("90")
assert result["rows"][0]["values"]["custom_0"]["current"] == Decimal("60")
def test_dataset_and_evidence_are_organization_and_department_scoped(workspace):
ds, _, _, _, other_client = workspace
url = f"/api/datasets/{ds.pk}"
@@ -133,6 +163,35 @@ def test_evidence_pagination_returns_stable_pages_and_rejects_invalid_bounds(wor
assert response.status_code == 400
def test_evidence_amounts_trim_storage_scale(workspace):
from decimal import Decimal
from finance.views import amount_text
assert amount_text(Decimal("10.000000000000000000000000")) == "10"
assert amount_text(Decimal("10.500000000000000000000000")) == "10.5"
assert amount_text(Decimal("0E-24")) == "0"
assert amount_text(None) is None
ds, _, client, *_ = workspace
response = client.post(
f"/api/datasets/{ds.pk}/evidence",
data={
"start": "2026-08",
"end": "2026-08",
"comparison": "yoy",
"group": "department",
"side": "current",
"limit": 1,
},
content_type="application/json",
)
row = response.json()["rows"][0]
assert row["revenue"] == "80"
assert row["cost"] == "30"
assert row["gross"] == "50"
def test_settings_revision_and_invalid_metric_are_rejected(workspace):
ds, _, client, *_ = workspace
url = f"/api/datasets/{ds.pk}/settings"
@@ -169,8 +228,8 @@ def test_csv_export_neutralizes_formula_injection_and_preserves_exact_values(wor
assert response.streaming
content = b"".join(response.streaming_content).decode("utf-8-sig")
assert "'=1+1" in content
assert ",-10.000000000000000000000000," in content
assert "'-10.000000000000000000000000" not in content
assert ",'-10,5,'-15" in content
assert "00000000000000" not in content
assert "traceiq-evidence.csv" in response["Content-Disposition"]
xlsx = client.post(
tests/test_formula_columns.py
+24 -2
View File
@@ -97,8 +97,30 @@ def test_formula_workbook_exposes_custom_metric_as_recalculable_column(formula_d
custom = workbook["自定义指标"]
assert custom["B1"].value == "收入成本差（本期）"
assert custom["B2"].value == f"='分析汇总'!C{metric_row}"
assert "含义：实际收入减成本" in workbook["使用说明"]["B12"].value
assert "范围：按所选分组及本期、基期计算" in workbook["使用说明"]["B12"].value
instruction_text = " ".join(str(cell.value) for row in workbook["使用说明"].iter_rows() for cell in row)
assert "含义：实际收入减成本" in instruction_text
assert "范围：按所选分组及本期、基期计算" in instruction_text
def test_formula_workbook_protects_structure_and_unlocks_calculation_inputs(formula_data):
ds, member = formula_data
output = BytesIO()
build_formula_workbook(
ds,
member,
{"start": "2026-08", "end": "2026-08", "comparison": "yoy", "group": "department"},
output,
)
workbook = load_workbook(BytesIO(output.getvalue()), data_only=False)
data = workbook["计算明细"]
assert data.protection.sheet
assert data["D2"].protection.locked is False
assert data["F2"].protection.locked is False
assert data["G2"].protection.locked is False
assert data["I2"].protection.locked is False
assert data["C2"].protection.locked is True
assert data["M2"].protection.locked is True
assert workbook["原始证据"].protection.sheet
def test_custom_expression_rejects_formula_text_and_unknown_metrics():
tests/test_formula_export_reliability.py
+78
View File
@@ -198,6 +198,67 @@ def test_formula_export_worker_records_path_preparation_failure(workspace, setti
assert job.status == "failed"
def test_formula_export_failure_does_not_follow_a_symlinked_user_directory(workspace, settings):
from finance.exporting import process_formula_export
ds, _, client, *_ = workspace
response = client.post(
f"/api/datasets/{ds.pk}/formula-exports",
data={"start": "2026-08", "end": "2026-08", "comparison": "yoy", "group": "department"},
content_type="application/json",
)
job = FormulaExport.objects.get(pk=response.json()["id"])
root = Path(settings.MEDIA_ROOT) / "formula-exports"
protected = Path(settings.MEDIA_ROOT) / "protected"
protected.mkdir()
target = protected / f"{job.pk}.xlsx"
target.write_bytes(b"keep")
root.mkdir()
(root / str(job.user_id)).symlink_to(protected, target_is_directory=True)
with pytest.raises(ValueError, match="符号链接"):
process_formula_export(job.pk)
job.refresh_from_db()
assert job.status == "failed"
assert target.read_bytes() == b"keep"
def test_formula_export_failed_cleanup_is_observable_and_reclaimed(workspace, monkeypatch):
from django.core.management import call_command
from finance.exporting import process_formula_export
ds, _, client, *_ = workspace
response = client.post(
f"/api/datasets/{ds.pk}/formula-exports",
data={"start": "2026-08", "end": "2026-08", "comparison": "yoy", "group": "department"},
content_type="application/json",
)
job = FormulaExport.objects.get(pk=response.json()["id"])
target = Path(job.dataset.file.storage.location) / "formula-exports" / str(job.user_id) / f"{job.pk}.xlsx"
target.parent.mkdir(parents=True)
target.write_bytes(b"orphan")
monkeypatch.setattr(
"finance.exporting.build_formula_workbook",
lambda *_a, **_k: (_ for _ in ()).throw(ValueError("retry failed")),
)
real_unlink = Path.unlink
monkeypatch.setattr(Path, "unlink", lambda *_a, **_k: (_ for _ in ()).throw(OSError("read-only")))
with pytest.raises(ValueError, match="retry failed"):
process_formula_export(job.pk)
job.refresh_from_db()
assert job.status == "failed"
assert job.path == str(target)
monkeypatch.setattr(Path, "unlink", real_unlink)
call_command("cleanup_formula_exports")
job.refresh_from_db()
assert job.path == ""
assert target.exists() is False
def test_formula_workbook_rejects_unclassified_label_collision(workspace):
from decimal import Decimal
@@ -374,3 +435,20 @@ def test_formula_workbook_rejects_dimension_keys_that_collide_with_reserved_head
{"start": "2026-08", "end": "2026-08", "comparison": "yoy", "group": "revenue"},
BytesIO(),
)
def test_custom_reconcile_reference_preserves_large_decimal_and_zero_division():
from finance.exporting import _evaluate_expression
expression = {
"operator": "divide",
"left": {"metric": "revenue"},
"right": {"number": 0},
}
assert (
_evaluate_expression(expression, {"revenue": Decimal("1.2345678901234567890123456789012345")}) is None
)
assert _evaluate_expression(
{"operator": "add", "left": {"metric": "revenue"}, "right": {"number": 1}},
{"revenue": Decimal("1234567890123456789012345678901234")},
) == Decimal("1234567890123456789012345678901235")
tests/test_operations.py
+41 -1
View File
@@ -46,6 +46,23 @@ def test_worker_processes_queued_import_and_formula_export(workspace):
assert job.status == "processing"
def test_worker_reclaims_processing_formula_export_after_restart(workspace):
ds, _, _, *_ = workspace
job = FormulaExport.objects.create(
dataset=ds,
user=ds.created_by,
context={"custom_columns": []},
status="processing",
)
from unittest.mock import patch
with patch(
"finance.management.commands.import_worker.process_formula_export", return_value={"rows": 5}
) as process:
call_command("import_worker", once=True)
process.assert_called_once_with(job.pk)
def test_formula_export_status_download_and_access_controls(workspace, settings, tmp_path):
ds, _, client, _, other_client = workspace
url = f"/api/datasets/{ds.pk}"
@@ -58,7 +75,7 @@ def test_formula_export_status_download_and_access_controls(workspace, settings,
job.status = "succeeded"
job.expires_at = timezone.now() + timedelta(hours=1)
target = tmp_path / "formula-exports" / str(ds.created_by_id) / f"{job.pk}.xlsx"
target.parent.mkdir(parents=True)
target.parent.mkdir(parents=True, exist_ok=True)
target.write_bytes(b"xlsx")
job.path = str(target)
job.save()
@@ -85,6 +102,29 @@ def test_formula_export_status_download_and_access_controls(workspace, settings,
assert client.get(status_url).status_code == 403
def test_formula_export_rechecks_role_when_worker_runs_and_file_is_downloaded(workspace, settings, tmp_path):
from finance.exporting import process_formula_export
ds, member, client, _, _ = workspace
job = FormulaExport.objects.create(dataset=ds, user=ds.created_by, context={}, status="processing")
member.role = "viewer"
member.save(update_fields=["role"])
with pytest.raises(PermissionError, match="撤权"):
process_formula_export(job.pk)
job.refresh_from_db()
assert job.status == "failed"
job.status = "succeeded"
job.expires_at = timezone.now() + timedelta(hours=1)
target = tmp_path / "formula-exports" / str(ds.created_by_id) / f"{job.pk}.xlsx"
target.parent.mkdir(parents=True, exist_ok=True)
target.write_bytes(b"xlsx")
job.path = str(target)
job.save(update_fields=["status", "expires_at", "path"])
assert client.get(f"/api/datasets/{ds.pk}/formula-exports/{job.pk}").status_code == 403
assert client.get(f"/api/datasets/{ds.pk}/formula-exports/{job.pk}/download").status_code == 403
def test_formula_export_create_validates_definition_and_records_job(workspace):
ds, _, client, *_ = workspace
url = f"/api/datasets/{ds.pk}/formula-exports"