unis-oa/backend/app/api/export.py

510 lines
23 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

"""列表导出:把「当前筛选条件」下的数据导成 CSV,外加**流程明细导出**那一整页。
两半共用同一套底层:值怎么渲染看 :mod:`app.service.exportfmt`,数据范围与筛选条件走
:mod:`app.api.runtime` 里流程监控那一份查询。所以界面上看不到的单子一定导不出来,
不会出现「列表里没有、导出却有」的越权。
后半段(``/export/detail/*``)挂的是独立权限码 ``monitor:export``,界面是侧栏里
「流程明细导出」那条独立菜单,设计稿见 ``docs/process-detail-export-design.md``。
只做读路径,不碰流转。查询条件与列表接口逐字复用同一套函数
(``_created_query`` / ``_task_filter`` / ``_admin_work_page`` / ``_admin_task_stmt``),
所以界面上看不到的单子一定导不出来,不会出现「列表里没有、导出却有」的越权。
三件值得一提的事:
- 编码带 UTF-8 BOM:Excel 双击打开中文不乱码,这是导出现场最常见的坑。
- 表单字段摊不摊成列,取决于本次导出的数据是不是同一张表单。同一张才摊开,
混在一起摊会多出几十列空值,反而不如固定列好读。
- 字典值一律翻成标签再落地,导出来的是「差旅费」而不是「CL01」。
"""
from __future__ import annotations
import csv
import io
from datetime import datetime
from urllib.parse import quote
from fastapi import APIRouter, Depends, Query, Request, Response
from sqlalchemy import select
from sqlalchemy.orm import Session
from .. import models, schemas
from ..db import get_db
from ..deps import CurrentUser, current_user, require_permission
from ..envelope import bad_request, forbidden, ok
from ..service import completed as completed_service
from ..service import detail_export
from ..service import dicts as dicts_service
from ..service.exportfmt import ( # noqa: F401 - 值渲染搬进了 service/exportfmt.py,老名字照旧可用
WORK_STATUS_LABELS,
cell_text,
relation_names as _job_names,
dt as _dt,
form_export_fields,
relation_ids as _relation_ids,
scalar_text as _scalar_text,
status_label as _status_label,
table_text as _table_text,
)
from ..service import inbox as inbox_service
from .runtime import ( # noqa: PLC2701 - 共用条件与留痕,明细导出走的就是监控那一份查询
_admin_task_stmt, _admin_work_page, _created_query, _done_filters, _inbox_filters,
_log_operation, _task_filter,
)
router = APIRouter(tags=["export"])
#: 一次导出的行数上限。列表接口每页最多 200,导出是给整月报表用的,
#: 但不能没有上限:一张宽表单导出几万行会把浏览器和数据库连接一起拖垮。
EXPORT_LIMIT = 5000
WORK_COLUMNS = ("单号", "标题", "流程", "分类", "状态", "发起人", "发起部门", "发起时间", "办结时间",
"当前环节", "当前处理人")
TASK_COLUMNS = ("单号", "标题", "流程", "分类", "环节", "处理人", "处理人部门", "发起人",
"到达时间", "完成时间", "处理动作", "处理意见", "是否超期")
#: 统一待办多出来的那一列(放在最前面:这份 CSV 里两类行混着摆,先说这是什么行)
INBOX_COLUMNS = ("类型", *TASK_COLUMNS)
#: 档位 → CSV 里的说法,与界面上那一排分段筛选同名(frontend 的 task.seg*)
INBOX_KIND_LABELS = {"approval": "待我办", "cc_action": "抄送需处理", "cc_read": "只需阅"}
#: 已办那一档也是两类行混着摆:环节上的表态 + 办完的抄送 / 转发需处理
#: (口径同 ``GET /task/completed``,见 :mod:`app.service.completed`)
#: 「当前环节」插在「环节」右边——那两格是一对(我办的是哪一步 / 这张单现在走到哪一步),
#: 摊到表的头尾就得自己对行。
DONE_COLUMNS = (
"类型",
"单号", "标题", "流程", "分类", "环节", "当前环节",
"处理人", "处理人部门", "发起人", "到达时间", "完成时间",
"处理动作", "处理意见", "是否超期", "处理次数",
)
DONE_SOURCE_LABELS = {
completed_service.SOURCE_APPROVAL: "审批办理",
completed_service.SOURCE_CC: "抄送处理",
}
def _current_activity_cell(state) -> str:
"""「当前环节」那一格的文案,与界面上那一列同一分寸。
还走着的单报停在哪个环节(并行 / 会签就并排写好几个),挂起的补一句;
落定的单报结局——那一格写"结束"这种环节名不如写"已办结"说得明白。
"""
if state is None:
return ""
if state.status and state.status != "running":
return _status_label(state.status, False)
names = state.activity_names
return f"{names}(已挂起)" if state.paused and names else names
# ---------------------------------------------------------------------------
# 已办两档并成同一种行
# ---------------------------------------------------------------------------
def _done_lines(groups: list, states: dict) -> list[list[str]]:
"""抄送那一行没有出口、没有超期、没有处理人部门,那几格留空,由最前面那一列
「类型」说明为什么是空的(与统一待办那份 CSV 同一分寸)。「完成时间」在抄送行上
读的是 ``handle_time``——那才是本人点「处理」的那一刻。
"""
lines: list[list[str]] = []
for group in groups:
row = group.row
is_cc = group.source == completed_service.SOURCE_CC
lines.append(
[
DONE_SOURCE_LABELS.get(group.source, group.source),
str(row.serial or ""),
str(row.title or ""),
str(row.process_name or ""),
str(getattr(row, "category_name", "") or ""),
str(row.activity_name or ""),
_current_activity_cell(states.get(str(group.work or ""))),
str(getattr(row, "identity_name", "") or ""),
str(getattr(row, "unit_name", "") or ""),
str(getattr(row, "creator_person", "") or ""),
_dt(getattr(row, "start_time", None)),
_dt(row.handle_time or row.view_time if is_cc
else getattr(row, "completed_time", None)),
"" if is_cc else str(getattr(row, "route_name", "") or ""),
str(getattr(row, "opinion", "") or ""),
"否" if is_cc else ("是" if bool(getattr(row, "expired", False)) else "否"),
str(int(group.handles)),
]
)
return lines
# ---------------------------------------------------------------------------
# 查询与列拼装
# ---------------------------------------------------------------------------
def _work_lines(rows: list, db: Session, cache: dicts_service.DictCache, with_form: bool):
"""返回 (表头, 数据行)。只有一张表单时才追加表单字段列。"""
form_ids = {str(row.form or "") for row in rows if getattr(row, "form", "")}
fields = form_export_fields(db, next(iter(form_ids))) if (with_form and len(form_ids) == 1) else []
jobs = _job_names(db, rows, fields)
headers = list(WORK_COLUMNS) + [str(item.get("label") or item.get("key")) for item in fields]
lines: list[list[str]] = []
for row in rows:
data = dict(row.data or {})
line = [
str(row.serial or ""),
str(row.title or ""),
str(row.process_name or ""),
str(row.category_name or ""),
_status_label(row.status, bool(getattr(row, "paused", False))),
str(row.creator_person or ""),
str(row.creator_unit_name or ""),
_dt(getattr(row, "start_time", None)),
_dt(getattr(row, "finish_time", None)),
str(row.current_activity_names or ""),
str(getattr(row, "manual_identity_text", "") or ""),
]
line.extend(
cell_text(field, data.get(str(field.get("key") or "")), cache, jobs, data=data)
for field in fields
)
lines.append(line)
return headers, lines
def _task_lines(rows: list) -> list[list[str]]:
lines: list[list[str]] = []
for row in rows:
lines.append(
[
str(row.serial or ""),
str(row.title or ""),
str(row.process_name or ""),
str(row.category_name or ""),
str(row.activity_name or ""),
str(getattr(row, "identity_name", "") or ""),
str(getattr(row, "unit_name", "") or ""),
str(getattr(row, "creator_person", "") or ""),
_dt(getattr(row, "start_time", None)),
_dt(getattr(row, "completed_time", None)),
str(getattr(row, "route_name", "") or ""),
str(getattr(row, "opinion", "") or ""),
"是" if bool(getattr(row, "expired", False)) else "否",
]
)
return lines
def _inbox_lines(rows: list) -> list[list[str]]:
"""统一待办那一沓 → CSV 行。
待办行与抄送行摊成同一种行:抄送行没有「处理动作 / 是否超期 / 处理人部门」这些
环节上的东西,那几格留空,由最前面那一列「类型」说明为什么是空的。每一行的格数
与 ``INBOX_COLUMNS`` 一一对齐——少一格会把后面整排的列名都往左挪一位,
表头写着「发起人」那一列装的是到达时间(``check_read_starter_snapshot.py`` D4/D5)。
「发起人」与「处理人部门」两格在抄送行上读的是待阅行自己抄下来的发起人快照
(``oa_read.creator_person`` / ``creator_unit_name``)——从前这里写的是 ``sender``,
而环节自动抄送把 ``sender`` 落成触发抄送的那位经手人,导出的 CSV 于是把最后一个
审批人报成发起人,与列表上同一行说的不是同一件事。
"""
lines: list[list[str]] = []
for item in rows:
row = item.row
kind = str(item.kind)
if kind == "approval":
handler, handler_unit = row.identity_name, row.unit_name
starter, done_time, route, expired = row.creator_person, None, row.route_name, "是" if row.expired else "否"
else:
# 「处理人」这一格在抄送行上就是收件人本人;他的部门待阅行没存,留空。
# 办结时间同理——抄送不走流转,没有"办完"那一刻(处理意见另见「处理意见」列)。
handler, handler_unit = row.identity_name, ""
# 空着就是空着(这一行没抄到发起人,见 20261117_read_creator_snapshot.sql),
# 不拿 sender 兜底:兜回来的是审批人的名字,界面上同一行画的是横杠,两份账对不上
starter = str(getattr(row, "creator_person", "") or "")
done_time, route = None, ""
expired = "否"
lines.append(
[
INBOX_KIND_LABELS.get(kind, kind),
str(row.serial or ""),
str(row.title or ""),
str(row.process_name or ""),
str(getattr(row, "category_name", "") or ""),
str(row.activity_name or ""),
str(handler or ""),
str(handler_unit or ""),
str(starter or ""),
_dt(getattr(row, "start_time", None)),
_dt(done_time),
str(route or ""),
str(row.opinion or ""),
expired,
]
)
return lines
def _attachment(body: bytes, filename: str, media_type: str) -> Response:
return Response(
content=body,
media_type=media_type,
headers={
# 中文文件名走 RFC 5987,另留一个 ASCII 兜底给老版 Excel 和 curl -O。
"Content-Disposition": f"attachment; filename=\"export\"; filename*=UTF-8''{quote(filename)}",
"Cache-Control": "no-store",
},
)
def _csv(headers: list[str], rows: list[list[str]], filename: str) -> Response:
buffer = io.StringIO()
writer = csv.writer(buffer)
writer.writerow(headers)
writer.writerows(rows)
return _attachment(("\ufeff" + buffer.getvalue()).encode("utf-8"), filename,
"text/csv; charset=utf-8")
# ---------------------------------------------------------------------------
# 接口
# ---------------------------------------------------------------------------
@router.get("/export/works")
def export_works(
scope: str = Query("created", description="created=我发起的 / admin=流程监控范围"),
keyword: str = Query(""),
process: str = Query(""),
status: str = Query(""),
start_date: str = Query(""),
end_date: str = Query(""),
expired: str = Query("", description="admin 范围用:1=只导手里压着超时候办的单子"),
paused: str = Query("", description="admin 范围用:1=只导被挂起的在途单;0=排除被挂起的"),
with_form: bool = Query(True, description="导出的是同一张表单时,是否把表单字段摊成列"),
db: Session = Depends(get_db),
user: CurrentUser = Depends(current_user),
) -> Response:
"""导出「我发起的」,或管理员监控范围内的流程单。"""
cache = dicts_service.DictCache(db)
if scope == "admin":
if not user.can("monitor:work"):
raise forbidden("无权限导出流程监控数据")
# 与监控列表同一个取数函数:界面上筛出来是哪些单子,导出来就是哪些单子,
# 在途的、办结的、终止的都在里面,不会出现「屏上有、CSV 里没有」。
rows, _total = _admin_work_page(
db,
user,
keyword=keyword,
process=process,
status=status,
start_date=start_date,
end_date=end_date,
expired=expired,
paused=paused,
limit=EXPORT_LIMIT,
)
else:
rows = []
if status in ("", "running"):
rows += _created_query(models.Work, user, keyword, process, status, start_date, end_date, db)
if status in ("", "completed", "terminated", "canceled"):
rows += _created_query(models.WorkCompleted, user, keyword, process, status, start_date, end_date, db)
rows.sort(key=lambda row: row.finish_time or row.start_time or row.create_time, reverse=True)
rows = rows[:EXPORT_LIMIT]
headers, lines = _work_lines(rows, db, cache, with_form)
return _csv(headers, lines, f"流程数据_{datetime.now():%Y%m%d_%H%M}.csv")
@router.get("/export/tasks")
def export_tasks(
scope: str = Query("processing", description="processing=我的待办 / completed=我的已办 / admin=流程监控的全部待办"),
keyword: str = Query(""),
process: str = Query(""),
category: str = Query(""),
expired: str = Query(""),
start_date: str = Query(""),
end_date: str = Query(""),
work_status: str = Query("", description="待办那一档用:所属单据的流程状态,口径同 GET /task/processing"),
kind: str = Query("", description="统一待办的档位,逗号分隔:approval / cc_action / cc_read;留空=全导"),
source: str = Query("", description="抄送那一侧的来源:cc / share / comment"),
viewed: str = Query("", description="0=只导没点开过的(红点还亮着),口径同 GET /task/inbox"),
activity: str = Query("", description="环节名筛选,留空=不限"),
db: Session = Depends(get_db),
user: CurrentUser = Depends(current_user),
) -> Response:
"""导出待办 / 已办。
``processing`` 与 ``completed`` 的查询条件本身就锁死本人身份,不需要额外权限点;
``admin`` 是流程监控页「全部待办」那个页签用的,挂 ``monitor:work`` 并走同一套数据范围,
免得监控页看着一片、导出来却只有自己的几条。
已办那一档跟页面一样按单折叠(见 :mod:`app.service.completed`),别导出一张单
好几行——那份 CSV 十有八九是拿去数"这个月办了几张单"的。
"""
if scope == "admin":
if not user.can("monitor:work"):
raise forbidden("无权限导出流程监控数据")
stmt = _admin_task_stmt(
db,
user,
keyword=keyword,
process=process,
category=category,
expired=expired,
start_date=start_date,
end_date=end_date,
)
rows = list(db.execute(stmt.order_by(models.Task.start_time.desc()).limit(EXPORT_LIMIT)).scalars().all())
return _csv(list(TASK_COLUMNS), _task_lines(rows), f"监控待办_{datetime.now():%Y%m%d_%H%M}.csv")
if scope == "completed":
# 导出跟页面同一个口径:一张单只出一行。界面上合成一条、导出却摊成好几行,
# 拿 CSV 对账的人会以为自己少算了单,多出来的那一列「处理次数」把几次表态
# 的账留在同一行上,不靠他记住页面上看过的那个数。
# 读的两本账也与页面一字不差:环节表态 + 办完的抄送需处理。屏幕上摆得见、
# CSV 里却没有,是这种改版最阴的坑
groups, _total = completed_service.collapse(
db,
_done_filters(user, keyword, process, category, start_date, end_date),
limit=EXPORT_LIMIT,
)
return _csv(
list(DONE_COLUMNS),
_done_lines(groups, completed_service.progress(db, [g.work for g in groups])),
f"我的已办_{datetime.now():%Y%m%d_%H%M}.csv",
)
# 待办那一档导的是**统一待办**:我自己的 + 抄送 / 转发,与列表同一沓同一条件
# (见 service/inbox.py)。界面上那一屏混着摆三类行,导出来只有一类的话,
# 拿着 CSV 对账的人会以为自己少抄送了几个。
filters = _inbox_filters(
keyword=keyword, process=process, category=category, activity=activity,
kinds=kind, source=source, viewed=viewed, expired=expired, work_status=work_status,
start_date=start_date, end_date=end_date,
)
rows, _total = inbox_service.page(
db, identity=user.ref, filters=filters, offset=0, limit=EXPORT_LIMIT
)
return _csv(list(INBOX_COLUMNS), _inbox_lines(rows), f"我的待办_{datetime.now():%Y%m%d_%H%M}.csv")
# ---------------------------------------------------------------------------
# 流程明细导出(独立菜单 / 独立权限码)
# ---------------------------------------------------------------------------
#: 这一页的四条接口挂同一码;与 monitor:work 互不为前置,理由见设计稿 §2。
DETAIL = require_permission("monitor:export")
def _detail_plan(body: schemas.ExportDetailBody, db: Session, user: CurrentUser):
"""报文 → (筛选, 列定义),顺带把界面上不该出现的组合挡掉。"""
filters = detail_export.filters_from_body(body.filters.model_dump())
detail_export.validate(filters, body.columns, body.layout, body.fmt)
specs = detail_export.build_columns(
db, filters.processes, body.columns, body.columns_only, body.activities)
if not specs:
raise bad_request("勾选的列在这些流程里都不存在,请重新选择")
limit = detail_export.max_columns()
if len(specs) > limit:
raise bad_request(f"这次要出 {len(specs)} 列,超过单张表上限 {limit} 列:请少勾几列或收起环节列")
return filters, specs
def _process_names(db: Session, process_ids: list[str]) -> dict[str, str]:
if not process_ids:
return {}
return {
str(row.id): str(row.name or "")
for row in db.execute(
select(models.Process).where(models.Process.id.in_(process_ids))).scalars().all()
}
@router.get("/export/detail/catalog")
def export_detail_catalog(
db: Session = Depends(get_db), user: CurrentUser = Depends(DETAIL)
) -> dict:
"""可选流程清单 + 每条流程的字段目录 + 「相关核算项目」那一格在哪。"""
return ok(detail_export.catalog(db))
@router.post("/export/detail/count")
def export_detail_count(
body: schemas.ExportDetailBody,
db: Session = Depends(get_db),
user: CurrentUser = Depends(DETAIL),
) -> dict:
"""按当前筛选估行数(分流程报),导出前先知道这次有多大。"""
filters, _specs = _detail_plan(body, db, user)
counts = detail_export.counts_by_process(db, user, filters)
names = _process_names(db, filters.processes)
limit = detail_export.max_rows()
total = sum(counts.values())
return ok({
"total": total,
"limit": limit,
"exceeded": total > limit,
"byProcess": [
{"process": process_id, "name": names.get(process_id, process_id), "count": counts.get(process_id, 0)}
for process_id in filters.processes
],
})
@router.post("/export/detail/preview")
def export_detail_preview(
body: schemas.ExportDetailBody,
db: Session = Depends(get_db),
user: CurrentUser = Depends(DETAIL),
) -> dict:
"""按最终那份列取一页回来看看:字段勾完就地刷新,不用先导一个文件。"""
filters, specs = _detail_plan(body, db, user)
size = max(1, min(int(body.page_size or 20), 100))
offset = max(0, (int(body.page or 1) - 1) * size)
rows, total = detail_export.query_page(db, user, filters, limit=size, offset=offset)
sheets = detail_export.build_sheets(
db, user, filters, specs, rows, layout=body.layout, summary=body.summary)
return ok({
"total": total,
"page": body.page,
"pageSize": size,
"sheets": [{"name": sheet.name, "headers": sheet.headers, "lines": sheet.lines} for sheet in sheets],
})
@router.post("/export/detail/download")
def export_detail_download(
body: schemas.ExportDetailBody,
db: Session = Depends(get_db),
user: CurrentUser = Depends(DETAIL),
request: Request = None, # type: ignore[assignment]
) -> Response:
"""出文件:默认一条流程一个 sheet,也可以合并成一张表;CSV 只有合并那一种表头。"""
filters, specs = _detail_plan(body, db, user)
limit = detail_export.max_rows()
rows, total = detail_export.query_page(db, user, filters, limit=limit + 1)
if total > limit:
raise bad_request(
f"这次命中 {total} 行,超过单次上限 {limit} 行:请收窄日期或减少流程条数"
"(上限在「系统设置」里可调)")
sheets = detail_export.build_sheets(
db, user, filters, specs, rows, layout=body.layout, summary=body.summary)
names = _process_names(db, filters.processes)
filename = detail_export.file_name(body.fmt)
_log_operation(
db, user, "export_detail", "、".join(names.get(item, item) for item in filters.processes)[:255],
f"导出 {sum(len(sheet.lines) for sheet in sheets)} 行 / {len(body.columns)} 列 / "
f"{body.layout} / {body.fmt};筛选:状态={','.join(filters.statuses) or '全部'} "
f"创建={filters.create_start or '不限'}~{filters.create_end or '不限'} "
f"归档={filters.archive_start or '不限'}~{filters.archive_end or '不限'} "
f"未操作者={len(filters.unoperator.persons)} 人",
request,
)
db.commit()
if body.fmt == detail_export.FORMAT_CSV:
return _attachment(detail_export.to_csv(sheets), filename, "text/csv; charset=utf-8")
return _attachment(detail_export.to_xlsx(sheets), filename, detail_export.XLSX_TYPE)