shanxia2.0/backend/app/api/bx/payment.py

import csv
import io
from datetime import datetime
from typing import Any, List, Optional
from urllib.parse import quote

from fastapi import APIRouter, Depends, HTTPException, Request, status, Query
from pydantic import Field
from fastapi.responses import StreamingResponse
from sqlalchemy import or_
from sqlalchemy.orm import Session

from app.api.bx.export_common import (
    EXPORT_MAX_ROWS,
    ExportColumn,
    ExportValidationError,
    BxApplyListExportBody,
    dump_export_row,
    issue_status_label,
    respond_xlsx,
)
from app.core.bx_auth import BxUserContext, get_bx_current_user
from app.core.database import get_db
from app.core.tenant_context import get_tenant_no
from app.models.bx_models import BxAdvanceApply, BxReimburseApply, BxReimburseOffset
from app.schemas.bx.advance import (
    AdvanceFinanceIssueStatsOut,
    AdvanceIssueOut,
    AdvanceIssueRequest,
    AdvanceOffsetRecordOut,
)
from app.schemas.bx.common import ApiResponse, PagedData
from app.schemas.bx.reimburse import (
    BatchOffsetRequest,
    OffsetRequest,
    PayRequest,
    PaymentFinanceStatsOut,
    ReimburseApplyOut,
    ReimburseOffsetRecordOut,
)
from app.services.bx.advance import AdvanceService
from app.services.bx.advance_list_filters import build_advance_column_filters
from app.services.bx.attachment import AttachmentService
from app.services.bx.list_sort import order_advance, order_reimburse, parse_sort_params
from app.services.bx.payment_list_filters import (
    payment_filters_from_params as _payment_filters_from_params,
)
from app.services.bx.reimburse import ReimburseService

router = APIRouter(prefix="/payments", tags=["支付处理"])


def _tenant(db: Session, user: BxUserContext) -> str:
    return get_tenant_no(db, user.to_tenant_user_dict())


def _wrap_reimburse_rows(outs: List[ReimburseApplyOut]) -> List[dict]:
    return [{"type": "reimburse", "data": o.model_dump(by_alias=True)} for o in outs]


def _merge_query(request: Request, **known) -> dict:
    out = {k: v for k, v in known.items() if v is not None and v != ""}
    for k, v in request.query_params.multi_items():
        if k in ("page", "pageSize"):
            continue
        if k not in out and v not in (None, ""):
            out[k] = v
    return out


def _param_get(params: dict, *keys: str) -> Any:
    for key in keys:
        if key in params and params[key] not in (None, ""):
            return params[key]
    return None


def _advance_filters_from_params(params: dict, *, tenant_no: str = "") -> list:
    ids = _param_get(params, "ids")
    if isinstance(ids, str):
        ids = [s.strip() for s in ids.split(",") if s.strip()]
    return build_advance_column_filters(
        tenant_no=tenant_no,
        keyword=str(_param_get(params, "keyword") or ""),
        status=_param_get(params, "status"),
        status_in=_param_get(params, "statusIn", "status_in"),
        apply_code=_param_get(params, "applyCode", "apply_code"),
        apply_code_in=_param_get(params, "applyCodeIn", "apply_code_in"),
        reason=_param_get(params, "reason"),
        reason_in=_param_get(params, "reasonIn", "reason_in"),
        applicant_id=_param_get(params, "applicantId", "applicant_id"),
        applicant_name=_param_get(params, "applicantName", "applicant_name"),
        applicant_name_in=_param_get(params, "applicantNameIn", "applicant_name_in"),
        current_node=_param_get(params, "currentNode", "current_node"),
        current_node_in=_param_get(params, "currentNodeIn", "current_node_in"),
        issue_by=_param_get(params, "issueBy", "issue_by"),
        issue_user_id=_param_get(params, "issueUserId", "issue_user_id"),
        issue_user_name=_param_get(params, "issueUserName", "issue_user_name"),
        issue_user_name_in=_param_get(params, "issueUserNameIn", "issue_user_name_in"),
        amount_op=_param_get(params, "amountOp", "amount_op"),
        amount=_param_get(params, "amount"),
        amount_min=_param_get(params, "amountMin", "amount_min"),
        amount_max=_param_get(params, "amountMax", "amount_max"),
        actual_amount_op=_param_get(params, "actualAmountOp", "actual_amount_op"),
        actual_amount=_param_get(params, "actualAmount", "actual_amount"),
        actual_amount_min=_param_get(params, "actualAmountMin", "actual_amount_min"),
        actual_amount_max=_param_get(params, "actualAmountMax", "actual_amount_max"),
        offset_amount_op=_param_get(params, "offsetAmountOp", "offset_amount_op"),
        offset_amount=_param_get(params, "offsetAmount", "offset_amount"),
        offset_amount_min=_param_get(params, "offsetAmountMin", "offset_amount_min"),
        offset_amount_max=_param_get(params, "offsetAmountMax", "offset_amount_max"),
        apply_date=_param_get(params, "applyDate", "apply_date"),
        apply_date_from=_param_get(params, "applyDateFrom", "apply_date_from"),
        apply_date_to=_param_get(params, "applyDateTo", "apply_date_to"),
        issue_time_from=_param_get(params, "issueTimeFrom", "issue_time_from"),
        issue_time_to=_param_get(params, "issueTimeTo", "issue_time_to"),
        ids=ids if isinstance(ids, list) else None,
    )


def _body_to_params(body: BxApplyListExportBody) -> dict:
    data = body.model_dump(by_alias=True, exclude_none=True)
    extra = getattr(body, "model_extra", None) or {}
    for key, value in extra.items():
        if key not in data and value not in (None, ""):
            data[key] = value
    return data


def _strip_tab_status_params(params: dict) -> None:
    """Tab 已固定 status 语义时，去掉冲突的 status 列参。"""
    params.pop("status", None)
    params.pop("statusIn", None)
    params.pop("status_in", None)


def _advance_issue_tab_filters(issue_status: Optional[str]) -> list:
    filters = []
    if issue_status == "pending":
        filters.append(BxAdvanceApply.status == "approved")
        filters.append(or_(BxAdvanceApply.issue_status.is_(None), BxAdvanceApply.issue_status != "issued"))
    elif issue_status == "issued":
        filters.append(BxAdvanceApply.status == "issued")
    else:
        filters.append(or_(BxAdvanceApply.status == "approved", BxAdvanceApply.status == "issued"))
    return filters


@router.get("/pending", response_model=ApiResponse[PagedData[Any]])
def list_pending_payments(
    request: Request,
    page: int = 1,
    pageSize: int = Query(10, ge=1, le=500),
    keyword: str = "",
    applicantId: Optional[str] = None,
    applyDate: Optional[str] = None,
    applyDateFrom: Optional[str] = None,
    applyDateTo: Optional[str] = None,
    db: Session = Depends(get_db),
    user: BxUserContext = Depends(get_bx_current_user),
):
    tenant_no = _tenant(db, user)
    filters = [BxReimburseApply.status == "approved"]
    params = _merge_query(
        request,
        keyword=keyword or None,
        applicantId=applicantId,
        applyDate=applyDate,
        applyDateFrom=applyDateFrom,
        applyDateTo=applyDateTo,
    )
    # Tab 语义固定 approved；不把 status 列参传入 builder
    _strip_tab_status_params(params)
    filters.extend(_payment_filters_from_params(params, db=db, tenant_no=tenant_no))
    sf, so = parse_sort_params(params)
    rows, total = ReimburseService.get_paged(
        db,
        tenant_no,
        page=page,
        page_size=pageSize,
        filters=filters,
        user=user,
        order_by=order_reimburse(sf, so),
    )
    outs = ReimburseService.out_rows_with_attachment_counts(db, tenant_no, list(rows))
    return ApiResponse(
        data=PagedData(rows=_wrap_reimburse_rows(outs), total=total, page=page, page_size=pageSize)
    )


@router.get("/stats", response_model=ApiResponse[PaymentFinanceStatsOut])
def payment_finance_stats(
    db: Session = Depends(get_db),
    user: BxUserContext = Depends(get_bx_current_user),
):
    tenant_no = _tenant(db, user)
    data = ReimburseService.finance_payment_stats(db, tenant_no, user=user)
    return ApiResponse(data=PaymentFinanceStatsOut.model_validate(data))


@router.get("/processed", response_model=ApiResponse[PagedData[Any]])
def list_processed_payments(
    request: Request,
    page: int = 1,
    pageSize: int = Query(10, ge=1, le=500),
    keyword: str = "",
    applicantId: Optional[str] = None,
    applyDate: Optional[str] = None,
    applyDateFrom: Optional[str] = None,
    applyDateTo: Optional[str] = None,
    db: Session = Depends(get_db),
    user: BxUserContext = Depends(get_bx_current_user),
):
    tenant_no = _tenant(db, user)
    filters = [BxReimburseApply.status.in_(["processed", "paid", "offset"])]
    params = _merge_query(
        request,
        keyword=keyword or None,
        applicantId=applicantId,
        applyDate=applyDate,
        applyDateFrom=applyDateFrom,
        applyDateTo=applyDateTo,
    )
    filters.extend(_payment_filters_from_params(params, db=db, tenant_no=tenant_no))
    sf, so = parse_sort_params(params)
    rows, total = ReimburseService.get_paged(
        db,
        tenant_no,
        page=page,
        page_size=pageSize,
        filters=filters,
        user=user,
        order_by=order_reimburse(sf, so),
    )
    outs = ReimburseService.out_rows_with_attachment_counts(db, tenant_no, list(rows))
    return ApiResponse(
        data=PagedData(rows=_wrap_reimburse_rows(outs), total=total, page=page, page_size=pageSize)
    )


@router.post("/{payment_id}/pay", response_model=ApiResponse[ReimburseApplyOut])
def pay(
    payment_id: str,
    body: Optional[PayRequest] = None,
    db: Session = Depends(get_db),
    user: BxUserContext = Depends(get_bx_current_user),
):
    tenant_no = _tenant(db, user)
    pay_remark = body.pay_remark if body else ""
    actual_amount = body.actual_amount if body else None
    reimburse = ReimburseService.pay(
        db, payment_id, user, pay_remark=pay_remark or "", actual_amount=actual_amount
    )
    if not reimburse:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="支付记录不存在")
    db.commit()
    db.refresh(reimburse)
    return ApiResponse(data=ReimburseService.out_one_with_attachment_counts(db, tenant_no, reimburse))


@router.post("/{payment_id}/offset", response_model=ApiResponse[ReimburseApplyOut])
def offset(
    payment_id: str,
    body: OffsetRequest,
    db: Session = Depends(get_db),
    user: BxUserContext = Depends(get_bx_current_user),
):
    tenant_no = _tenant(db, user)
    reimburse = ReimburseService.offset(
        db,
        payment_id,
        body.advance_id,
        body.offset_amount,
        user,
        remark=body.remark or "",
    )
    if not reimburse:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="支付记录不存在")
    db.commit()
    db.refresh(reimburse)
    return ApiResponse(data=ReimburseService.out_one_with_attachment_counts(db, tenant_no, reimburse))


@router.post("/{payment_id}/batch-offset", response_model=ApiResponse[ReimburseApplyOut])
def batch_offset(
    payment_id: str,
    body: BatchOffsetRequest,
    db: Session = Depends(get_db),
    user: BxUserContext = Depends(get_bx_current_user),
):
    tenant_no = _tenant(db, user)
    reimburse = ReimburseService.batch_offset(
        db,
        payment_id,
        [item.model_dump() for item in body.items],
        body.pay_amount,
        user,
        remark=body.remark or "",
    )
    if not reimburse:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="支付记录不存在")
    db.commit()
    db.refresh(reimburse)
    return ApiResponse(data=ReimburseService.out_one_with_attachment_counts(db, tenant_no, reimburse))


@router.get("/advance-issues", response_model=ApiResponse[PagedData[AdvanceIssueOut]])
def list_advance_issues(
    request: Request,
    page: int = 1,
    pageSize: int = Query(10, ge=1, le=500),
    issue_status: Optional[str] = None,
    keyword: str = "",
    applicantId: Optional[str] = None,
    applyDate: Optional[str] = None,
    applyDateFrom: Optional[str] = None,
    applyDateTo: Optional[str] = None,
    db: Session = Depends(get_db),
    user: BxUserContext = Depends(get_bx_current_user),
):
    tenant_no = _tenant(db, user)
    filters = _advance_issue_tab_filters(issue_status)
    params = _merge_query(
        request,
        keyword=keyword or None,
        applicantId=applicantId,
        applyDate=applyDate,
        applyDateFrom=applyDateFrom,
        applyDateTo=applyDateTo,
    )
    # Tab 语义固定 status；不把 status 列参传入 builder
    _strip_tab_status_params(params)
    filters.extend(_advance_filters_from_params(params, tenant_no=tenant_no))
    sf, so = parse_sort_params(params)
    rows, total = AdvanceService.get_paged(
        db,
        tenant_no,
        page=page,
        page_size=pageSize,
        filters=filters,
        user=user,
        order_by=order_advance(sf, so),
    )
    out_rows = [AdvanceIssueOut.model_validate(r) for r in rows]
    if out_rows:
        offset_map = AdvanceService.offset_amount_map(db, tenant_no, [r.id for r in rows])
        for out_row in out_rows:
            out_row.offset_amount = offset_map.get(out_row.id, 0.0)
    return ApiResponse(data=PagedData(rows=out_rows, total=total, page=page, page_size=pageSize))


@router.get("/advance-issues/stats", response_model=ApiResponse[AdvanceFinanceIssueStatsOut])
def advance_issue_stats(
    db: Session = Depends(get_db),
    user: BxUserContext = Depends(get_bx_current_user),
):
    tenant_no = _tenant(db, user)
    data = AdvanceService.finance_issue_stats(db, tenant_no, user=user)
    return ApiResponse(data=AdvanceFinanceIssueStatsOut.model_validate(data))



PAYMENT_EXPORT_COLUMNS = [
    ExportColumn(title="申请单号", field="applyCode"),
    ExportColumn(title="申请时间", field="applyTime"),
    ExportColumn(title="申请人", field="applicantName"),
    ExportColumn(title="报销主题", field="title"),
    ExportColumn(title="金额（元）", field="amount"),
    ExportColumn(title="冲抵金额（元）", field="offsetAmount"),
    ExportColumn(title="实付金额（元）", field="payAmount"),
    ExportColumn(title="处理状态", field="status"),
    ExportColumn(title="支付时间", field="payTime"),
]

ADVANCE_ISSUE_EXPORT_COLUMNS = [
    ExportColumn(title="申请单号", field="applyCode"),
    ExportColumn(title="申请时间", field="applyTime"),
    ExportColumn(title="申请人", field="applicantName"),
    ExportColumn(title="申请原因", field="reason"),
    ExportColumn(title="申请金额（元）", field="amount"),
    ExportColumn(title="实际发放金额（元）", field="actualAmount"),
    ExportColumn(title="冲抵总金额（元）", field="offsetAmount"),
    ExportColumn(title="审批通过时间", field="approveTime"),
    ExportColumn(title="发放时间", field="issueTime"),
    ExportColumn(title="发放人", field="issueUserName"),
    ExportColumn(title="发放状态", field="issueStatus"),
]


class PaymentExportBody(BxApplyListExportBody):
    issue_status: Optional[str] = Field(default=None, alias="issueStatus")


@router.post("/pending/export")
def export_pending_payments(
    body: BxApplyListExportBody,
    db: Session = Depends(get_db),
    user: BxUserContext = Depends(get_bx_current_user),
):
    try:
        tenant_no = _tenant(db, user)
        filters = [BxReimburseApply.status == "approved"]
        params = _body_to_params(body)
        _strip_tab_status_params(params)
        filters.extend(_payment_filters_from_params(params, db=db, tenant_no=tenant_no))
        sf, so = parse_sort_params(params)
        rows, _ = ReimburseService.get_paged(
            db,
            tenant_no,
            page=1,
            page_size=EXPORT_MAX_ROWS,
            filters=filters,
            user=user,
            order_by=order_reimburse(sf, so),
        )
        outs = ReimburseService.out_rows_with_attachment_counts(db, tenant_no, list(rows))
        export_rows = [dump_export_row(o) for o in outs]
        return respond_xlsx(
            db, tenant_no=tenant_no, sheet_title="待付款", columns=PAYMENT_EXPORT_COLUMNS,
            rows=export_rows, filename_stem="报销待付款", requested_columns=body.columns,
        )
    except ExportValidationError as e:
        raise HTTPException(status_code=400, detail=str(e)) from e


@router.post("/processed/export")
def export_processed_payments(
    body: BxApplyListExportBody,
    db: Session = Depends(get_db),
    user: BxUserContext = Depends(get_bx_current_user),
):
    try:
        tenant_no = _tenant(db, user)
        filters = [BxReimburseApply.status.in_(["processed", "paid", "offset"])]
        params = _body_to_params(body)
        filters.extend(_payment_filters_from_params(params, db=db, tenant_no=tenant_no))
        sf, so = parse_sort_params(params)
        rows, _ = ReimburseService.get_paged(
            db,
            tenant_no,
            page=1,
            page_size=EXPORT_MAX_ROWS,
            filters=filters,
            user=user,
            order_by=order_reimburse(sf, so),
        )
        outs = ReimburseService.out_rows_with_attachment_counts(db, tenant_no, list(rows))
        export_rows = [dump_export_row(o) for o in outs]
        return respond_xlsx(
            db, tenant_no=tenant_no, sheet_title="已付款", columns=PAYMENT_EXPORT_COLUMNS,
            rows=export_rows, filename_stem="报销已付款", requested_columns=body.columns,
        )
    except ExportValidationError as e:
        raise HTTPException(status_code=400, detail=str(e)) from e


@router.post("/advance-issues/export-xlsx")
def export_advance_issues_xlsx(
    body: PaymentExportBody,
    db: Session = Depends(get_db),
    user: BxUserContext = Depends(get_bx_current_user),
):
    try:
        tenant_no = _tenant(db, user)
        params = _body_to_params(body)
        issue_status = body.issue_status or _param_get(params, "issueStatus", "issue_status")
        filters = _advance_issue_tab_filters(issue_status)
        _strip_tab_status_params(params)
        filters.extend(_advance_filters_from_params(params, tenant_no=tenant_no))
        sf, so = parse_sort_params(params)
        rows, _ = AdvanceService.get_paged(
            db,
            tenant_no,
            page=1,
            page_size=EXPORT_MAX_ROWS,
            filters=filters,
            user=user,
            order_by=order_advance(sf, so),
        )
        out_rows = [AdvanceIssueOut.model_validate(r) for r in rows]
        export_rows = []
        for o in out_rows:
            data = dump_export_row(o, status_fields=())
            data["issueStatus"] = issue_status_label(getattr(o, "issue_status", None) or data.get("issueStatus"))
            export_rows.append(data)
        return respond_xlsx(
            db, tenant_no=tenant_no, sheet_title="备用金发放", columns=ADVANCE_ISSUE_EXPORT_COLUMNS,
            rows=export_rows, filename_stem="备用金发放", requested_columns=body.columns,
        )
    except ExportValidationError as e:
        raise HTTPException(status_code=400, detail=str(e)) from e


@router.get("/advance-issues/export")
def export_advance_issues(
    request: Request,
    issue_status: Optional[str] = None,
    keyword: str = "",
    applicantId: Optional[str] = None,
    applyDate: Optional[str] = None,
    applyDateFrom: Optional[str] = None,
    applyDateTo: Optional[str] = None,
    db: Session = Depends(get_db),
    user: BxUserContext = Depends(get_bx_current_user),
):
    tenant_no = _tenant(db, user)
    filters = _advance_issue_tab_filters(issue_status)
    params = _merge_query(
        request,
        keyword=keyword or None,
        applicantId=applicantId,
        applyDate=applyDate,
        applyDateFrom=applyDateFrom,
        applyDateTo=applyDateTo,
    )
    _strip_tab_status_params(params)
    filters.extend(_advance_filters_from_params(params, tenant_no=tenant_no))
    rows, _ = AdvanceService.get_paged(db, tenant_no, page=1, page_size=99999, filters=filters, user=user)
    out_rows = [AdvanceIssueOut.model_validate(r) for r in rows]

    buf = io.StringIO()
    writer = csv.writer(buf)
    writer.writerow(
        [
            "申请单号",
            "申请人",
            "申请原因",
            "申请金额",
            "审批通过时间",
            "发放状态",
            "发放时间",
            "发放操作人",
            "实际发放金额",
            "发放备注",
        ]
    )
    for row in out_rows:
        writer.writerow(
            [
                row.apply_code,
                row.applicant_name,
                row.reason,
                row.amount,
                row.approve_time or "",
                "已发放" if row.issue_status == "issued" else "待发放",
                row.issue_time or "",
                row.issue_user_name or "",
                row.actual_amount or "",
                row.issue_remark or "",
            ]
        )
    buf.seek(0)
    filename = f"备用金发放列表_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv"
    encoded_name = quote(filename)
    return StreamingResponse(
        iter([buf.getvalue()]),
        media_type="text/csv; charset=utf-8",
        headers={"Content-Disposition": f"attachment; filename*=UTF-8''{encoded_name}"},
    )


@router.post("/advance/{advance_id}/issue", response_model=ApiResponse[AdvanceIssueOut])
def issue_advance(
    advance_id: str,
    body: AdvanceIssueRequest,
    db: Session = Depends(get_db),
    user: BxUserContext = Depends(get_bx_current_user),
):
    tenant_no = _tenant(db, user)
    issue_atts = AttachmentService.get_by_biz(db, "advance_issue", advance_id, tenant_no, user=user)
    if not issue_atts:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="请至少上传 1 个发放附件")
    advance = AdvanceService.issue(
        db, advance_id, user, body.issue_remark or "", body.actual_amount
    )
    if not advance:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="备用金申请不存在")
    db.commit()
    db.refresh(advance)
    out = AdvanceIssueOut.model_validate(advance)
    offset_map = AdvanceService.offset_amount_map(db, tenant_no, [advance_id])
    out.offset_amount = offset_map.get(advance_id, 0.0)
    return ApiResponse(data=out)


@router.get("/advance/{advance_id}/offset-records", response_model=ApiResponse[List[AdvanceOffsetRecordOut]])
def list_advance_offset_records(
    advance_id: str,
    db: Session = Depends(get_db),
    user: BxUserContext = Depends(get_bx_current_user),
):
    tenant_no = _tenant(db, user)
    AdvanceService.get_by_id(db, advance_id, tenant_no, user=user)
    rows = (
        db.query(BxReimburseOffset, BxReimburseApply)
        .join(
            BxReimburseApply,
            BxReimburseApply.id == BxReimburseOffset.reimburse_id,
        )
        .filter(
            BxReimburseOffset.advance_id == advance_id,
            BxReimburseOffset.tenant_no == tenant_no,
            BxReimburseOffset.del_flag == 0,
            BxReimburseApply.del_flag == 0,
            BxReimburseApply.tenant_no == tenant_no,
        )
        .order_by(BxReimburseOffset.create_time.desc(), BxReimburseOffset.id.desc())
        .all()
    )
    records = [
        AdvanceOffsetRecordOut(
            reimburse_id=reimburse.id,
            reimburse_apply_code=reimburse.apply_code,
            reimburse_title=reimburse.title,
            reimburse_applicant_name=reimburse.applicant_name,
            offset_amount=float(offset.offset_amount or 0),
            remark=offset.remark,
            operator=offset.create_by,
            create_time=offset.create_time,
        )
        for offset, reimburse in rows
    ]
    return ApiResponse(data=records)


@router.get("/{payment_id}/offset-records", response_model=ApiResponse[List[ReimburseOffsetRecordOut]])
def list_reimburse_offset_records(
    payment_id: str,
    db: Session = Depends(get_db),
    user: BxUserContext = Depends(get_bx_current_user),
):
    tenant_no = _tenant(db, user)
    ReimburseService.get_by_id(db, payment_id, tenant_no, user=user)
    rows = (
        db.query(BxReimburseOffset, BxAdvanceApply)
        .join(BxAdvanceApply, BxAdvanceApply.id == BxReimburseOffset.advance_id)
        .filter(
            BxReimburseOffset.reimburse_id == payment_id,
            BxReimburseOffset.tenant_no == tenant_no,
            BxReimburseOffset.del_flag == 0,
            BxAdvanceApply.del_flag == 0,
            BxAdvanceApply.tenant_no == tenant_no,
        )
        .order_by(BxReimburseOffset.create_time.desc(), BxReimburseOffset.id.desc())
        .all()
    )
    records = [
        ReimburseOffsetRecordOut(
            advance_id=advance.id,
            advance_apply_code=advance.apply_code,
            advance_applicant_name=advance.applicant_name,
            advance_reason=advance.reason,
            offset_amount=float(offset.offset_amount or 0),
            remark=offset.remark,
            operator=offset.create_by,
            create_time=offset.create_time,
        )
        for offset, advance in rows
    ]
    return ApiResponse(data=records)