168 lines
5.9 KiB
Python
168 lines
5.9 KiB
Python
"""Small shared helpers for resource routers (label joins, lookups, export)."""
|
||
|
||
from __future__ import annotations
|
||
|
||
from typing import Any
|
||
|
||
from sqlalchemy import select
|
||
from sqlalchemy.ext.asyncio import AsyncSession
|
||
|
||
from ..core.deps import department_subtree
|
||
from ..core.errors import AppError
|
||
|
||
|
||
async def dept_filter_ids(session: AsyncSession, raw: str | None,
|
||
subtree: bool = True) -> list[int]:
|
||
"""把 ?department_id=3,7 解析成部门 id 集合(subtree 时展开下级)。"""
|
||
picked = [int(x) for x in (raw or "").split(",") if x.strip().isdigit()]
|
||
if not picked:
|
||
return []
|
||
if not subtree:
|
||
return picked
|
||
out: set[int] = set()
|
||
for one in picked:
|
||
out.update(await department_subtree(session, one))
|
||
return sorted(out)
|
||
|
||
|
||
async def attach_labels(session: AsyncSession, rows: list[Any],
|
||
specs: list[tuple[str, type, str, str]]) -> None:
|
||
"""Fill ``row.<target>`` with the referenced record's label.
|
||
|
||
``specs`` entries are ``(target_attr, ReferencedModel, local_fk_attr, label_attr)``.
|
||
One batched query per spec keeps list endpoints free of N+1 round trips.
|
||
"""
|
||
if not rows:
|
||
return
|
||
for target, ref_model, fk_attr, label_attr in specs:
|
||
ids = {getattr(r, fk_attr, None) for r in rows}
|
||
ids.discard(None)
|
||
if not ids:
|
||
for r in rows:
|
||
try:
|
||
setattr(r, target, None)
|
||
except (AttributeError, TypeError):
|
||
pass
|
||
continue
|
||
rows_map = dict((
|
||
await session.execute(select(ref_model.id, getattr(ref_model, label_attr))
|
||
.where(ref_model.id.in_(ids)))
|
||
).all())
|
||
for r in rows:
|
||
value = rows_map.get(getattr(r, fk_attr, None))
|
||
try:
|
||
setattr(r, target, value)
|
||
except (AttributeError, TypeError):
|
||
pass
|
||
|
||
|
||
async def label_map(session: AsyncSession, model: type, label_attr: str = "name") -> dict[int, str]:
|
||
rows = (await session.execute(select(model.id, getattr(model, label_attr)))).all()
|
||
return {r[0]: r[1] for r in rows}
|
||
|
||
|
||
async def counts_by(session: AsyncSession, model: type, fk_attr: str) -> dict[int, int]:
|
||
from sqlalchemy import func
|
||
|
||
col = getattr(model, fk_attr)
|
||
rows = (await session.execute(select(col, func.count()).where(col.is_not(None)).group_by(col))).all()
|
||
return {r[0]: int(r[1]) for r in rows}
|
||
|
||
|
||
async def employee_dept_names(session: AsyncSession, rows: list[Any], fk: str = "employee_id") -> None:
|
||
"""Resolve ``department_name`` through the employee relation.
|
||
|
||
Payroll / benefits rows carry an ``employee_id`` but no department of their own,
|
||
so the two-hop lookup happens here once per page instead of per row.
|
||
"""
|
||
from ..models.employee import Employee
|
||
from ..models.org import Department
|
||
|
||
emp_ids = {getattr(r, fk) for r in rows if getattr(r, fk, None)}
|
||
if not emp_ids:
|
||
return
|
||
emps = {e.id: e for e in (await session.execute(
|
||
select(Employee).where(Employee.id.in_(emp_ids)))).scalars().all()}
|
||
depts = await label_map(session, Department)
|
||
for r in rows:
|
||
emp = emps.get(getattr(r, fk))
|
||
if emp is not None:
|
||
try:
|
||
r.department_name = depts.get(emp.department_id)
|
||
except (AttributeError, TypeError):
|
||
pass
|
||
|
||
|
||
async def one_scalar(session: AsyncSession, stmt) -> int:
|
||
return int((await session.execute(stmt)).scalar() or 0)
|
||
|
||
|
||
def xlsx_response(headers: list[str], rows: list[list[Any]], filename: str):
|
||
"""Stream a workbook as an attachment (used by roster / payslip exports)."""
|
||
import io
|
||
|
||
from fastapi.responses import StreamingResponse
|
||
from openpyxl import Workbook
|
||
|
||
wb = Workbook()
|
||
ws = wb.active
|
||
ws.title = "Sheet1"
|
||
ws.append(headers)
|
||
for row in rows:
|
||
ws.append([v.isoformat() if hasattr(v, "isoformat") else v for v in row])
|
||
for idx, title in enumerate(headers, start=1):
|
||
ws.column_dimensions[ws.cell(row=1, column=idx).column_letter].width = max(
|
||
12, min(32, len(str(title)) * 2 + 6)
|
||
)
|
||
buffer = io.BytesIO()
|
||
wb.save(buffer)
|
||
buffer.seek(0)
|
||
from urllib.parse import quote
|
||
|
||
return StreamingResponse(
|
||
buffer,
|
||
media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
|
||
headers={"Content-Disposition": f"attachment; filename*=UTF-8''{quote(filename)}"},
|
||
)
|
||
|
||
|
||
def _rows_to_dicts(rows: list[tuple | list]) -> list[dict[str, Any]]:
|
||
if not rows:
|
||
return []
|
||
headers = [str(h).strip() if h is not None else "" for h in rows[0]]
|
||
out = []
|
||
for raw in rows[1:]:
|
||
if raw is None or all(c in (None, "") for c in raw):
|
||
continue
|
||
out.append({headers[i]: raw[i] for i in range(min(len(headers), len(raw))) if headers[i]})
|
||
return out
|
||
|
||
|
||
def parse_xlsx(data: bytes) -> list[dict[str, Any]]:
|
||
"""解析上传的表格:xlsx 取第一个工作表,csv 按 UTF-8 / GB18030 解码。
|
||
|
||
旧接口名为兼容保留;``.xls``(OLE 二进制)不在支持范围内,会得到明确提示。
|
||
"""
|
||
import csv
|
||
import io
|
||
|
||
if data.startswith(b"PK"):
|
||
from openpyxl import load_workbook
|
||
|
||
wb = load_workbook(io.BytesIO(data), data_only=True, read_only=True)
|
||
return _rows_to_dicts(list(wb.active.iter_rows(values_only=True)))
|
||
|
||
if data.startswith(b"\xd0\xcf\x11\xe0"):
|
||
raise AppError("暂不支持旧版 .xls,请另存为 .xlsx 或 .csv 后重新上传", "unsupported_file")
|
||
|
||
text = None
|
||
for encoding in ("utf-8-sig", "gb18030"):
|
||
try:
|
||
text = data.decode(encoding)
|
||
break
|
||
except UnicodeDecodeError:
|
||
continue
|
||
if text is None:
|
||
raise AppError("无法识别文件编码,请使用 UTF-8 编码的 CSV 或 .xlsx", "unsupported_file")
|
||
return _rows_to_dicts(list(csv.reader(io.StringIO(text))))
|