unis_hrms/app/api/helpers.py

168 lines
5.9 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.

"""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))))