PayrollManagementSystem/backend/app/services/report_export.py

"""报表 Excel 模板构建（纯函数，可单测）。

版式（FR-032「导出模板围绕财务复核和领导签字设计」）：
公司信息页眉 → 报表标题 → 筛选条件 → 导出时间/人/模板版本 → 表头 → 数据行
→ 合计行 → 财务复核栏 / 领导签字栏 / 日期栏。

精度红线：金额写 ``Decimal``（openpyxl 原生支持，不经 float）+
``number_format='#,##0.00'``；合计在 Python 侧求和后两位收口，**不写
Excel 公式**（避免打开时重算漂移与公式注入面）。文本列以 ``= + - @``
开头时前置单引号，防公式注入。
"""

from dataclasses import dataclass
from datetime import datetime
from decimal import ROUND_HALF_UP, Decimal
from io import BytesIO

from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter

from app.core.report_meta import (
    BUSINESS_STATUS_LABELS,
    CUSTOMER_TYPE_LABELS,
    PRODUCT_CATEGORY_LABELS,
)

_HEADER_FILL = PatternFill("solid", fgColor="DDE3F0")
_THIN = Side(style="thin", color="B0B7C3")
_BORDER = Border(left=_THIN, right=_THIN, top=_THIN, bottom=_THIN)
_TITLE_FONT = Font(size=14, bold=True)
_COMPANY_FONT = Font(size=16, bold=True)
_HEADER_FONT = Font(bold=True)
_AMOUNT_FORMAT = "#,##0.00"
_DATE_FORMAT = "YYYY-MM-DD"
_FORMULA_PREFIXES = ("=", "+", "-", "@", "\t")


@dataclass(frozen=True, slots=True)
class _Column:
    """列定义：key 取行 dict 字段，kind 决定单元格类型与格式。"""

    key: str
    title: str
    kind: str  # text / amount / int / date / customer / category / status
    width: float


@dataclass(frozen=True, slots=True)
class _Block:
    """签字稿中的一个区块：独立表头、数据行与可选合计行。"""

    title: str
    columns: tuple[_Column, ...]
    rows: list[dict]
    total: dict | None = None
    empty_text: str = "（无）"


_COLUMNS: dict[str, tuple[_Column, ...]] = {
    "personal-detail": (
        _Column("business_no", "合同编号", "text", 20),
        _Column("ship_date", "发货日", "date", 12),
        _Column("receipt_date", "回款日", "date", 12),
        _Column("payment_days", "回款天数", "int", 10),
        _Column("product_category", "产品类别", "category", 14),
        _Column("customer_type", "客户类型", "customer", 14),
        _Column("role_name", "岗位", "text", 12),
        _Column("rate_text", "计提比例", "text", 14),
        _Column("weight_r", "支付权重R", "int", 10),
        _Column("performance_amount", "绩效金额", "amount", 14),
        _Column("payable_amount", "应付绩效", "amount", 14),
    ),
    "department-summary": (
        _Column("manager_name", "经理团队", "text", 16),
        _Column("member_name", "成员", "text", 14),
        _Column("role_code", "岗位编码", "text", 12),
        _Column("performance_sum", "绩效金额", "amount", 14),
        _Column("payable_sum", "应付绩效", "amount", 14),
        _Column("adjust_net", "调整净额", "amount", 14),
        _Column("net_total", "净合计", "amount", 14),
    ),
    "business-stats": (
        _Column("business_no", "业务编号", "text", 20),
        _Column("business_date", "业务日期", "date", 12),
        _Column("customer_name", "客户", "text", 22),
        _Column("owner_personnel_name", "归属人", "text", 12),
        _Column("status", "状态", "status", 10),
        _Column("total_sale_amount", "销售额", "amount", 14),
        _Column("total_ship_amount", "发货额", "amount", 14),
        _Column("total_received_amount", "回款额", "amount", 14),
        _Column("performance_amount", "绩效金额", "amount", 14),
        _Column("payable_amount", "应付绩效", "amount", 14),
    ),
    "payment-stats": (
        _Column("label", "回款天数区间", "text", 20),
        _Column("receipt_amount", "回款金额", "amount", 16),
        _Column("receipt_count", "回款笔数", "int", 10),
        _Column("performance_amount", "绩效金额", "amount", 14),
        _Column("payable_amount", "应付绩效", "amount", 14),
        _Column("ledger_count", "台账行数", "int", 10),
    ),
}


def build_workbook(
    *,
    code: str,
    title: str,
    filters: str,
    rows: list[dict],
    summary: dict | None,
    buckets: dict | None,
    template_version: str,
    operator_name: str,
    company_name: str,
    department: dict | None = None,
) -> bytes:
    """构建并返回 xlsx 字节流（业务数据只读，不触碰数据库）。

    部门汇总是签字对账文件，走单 sheet 分块版式；其余报表仍是单表 +
    合计行。`department` 为 `department_summary` 的完整出参。
    """
    workbook = Workbook()
    sheet = workbook.active
    sheet.title = _sheet_name(code)
    if code == "department-summary" and department is not None:
        _write_department_document(
            sheet,
            department,
            company_name=company_name,
            title=title,
            filters=filters,
            template_version=template_version,
            operator_name=operator_name,
        )
        buffer = BytesIO()
        if department.get("salary_rows"):
            salary = workbook.create_sheet("工资分项快照")
            salary.append(["人员", "基本工资", "正常绩效", "试用期缓发", "年终及续付差额", "本月实付绩效", "净工资"])
            for row in department["salary_rows"]:
                salary.append([row["personnel_name"], *[Decimal(row[key]) if row.get(key) is not None else None for key in
                    ("basic_amount", "normal_payable_sum", "deferred_sum", "probation_payment_sum", "payable_sum", "net_amount")]])
                if salary.cell(salary.max_row, 1).data_type == "f":
                    salary.cell(salary.max_row, 1).data_type = "s"
            salary.freeze_panes = "A2"
            for column in salary.columns:
                salary.column_dimensions[column[0].column_letter].width = 24
        workbook.save(buffer)
        return buffer.getvalue()

    columns = _COLUMNS[code]
    _write_header(sheet, columns, company_name, title, filters, template_version, operator_name)
    if rows:
        for row in rows:
            _write_row(sheet, columns, row)
    else:
        sheet.append(["本筛选条件无数据"])
        _merge_row(sheet, len(columns))
    _write_total(sheet, columns, rows)
    _write_signature(sheet, len(columns))
    _autofit(sheet, columns)

    # 回款统计附维度对比 sheet（客户类型/产品类别），与区间表共用页眉格式
    if code == "payment-stats" and buckets:
        for dim_key, dim_title in (
            ("by_customer_type", "客户类型对比"),
            ("by_product_category", "产品类别对比"),
        ):
            extra = workbook.create_sheet(dim_title)
            _write_dimension_sheet(
                extra,
                dim_title,
                company_name,
                filters,
                buckets.get(dim_key) or [],
                template_version,
                operator_name,
            )

    buffer = BytesIO()
    workbook.save(buffer)
    return buffer.getvalue()


def _sheet_name(code: str) -> str:
    return {
        "personal-detail": "个人绩效明细",
        "department-summary": "部门绩效汇总",
        "business-stats": "业务统计",
        "payment-stats": "回款统计",
    }[code]


def _write_header(
    sheet,
    columns: tuple[_Column, ...],
    company_name: str,
    title: str,
    filters: str,
    template_version: str,
    operator_name: str,
) -> None:
    """页眉四行 + 表头行；返回后 sheet.max_row 即数据行起点。"""
    _write_document_header(
        sheet,
        len(columns),
        company_name,
        title,
        filters,
        template_version,
        operator_name,
    )

    sheet.append([column.title for column in columns])
    header_row = sheet.max_row
    for cell in sheet[header_row]:
        cell.font = _HEADER_FONT
        cell.fill = _HEADER_FILL
        cell.border = _BORDER
        cell.alignment = Alignment(horizontal="center", vertical="center")
    sheet.freeze_panes = sheet.cell(row=header_row + 1, column=1)


def _write_document_header(
    sheet,
    column_count: int,
    company_name: str,
    title: str,
    filters: str,
    template_version: str,
    operator_name: str,
) -> None:
    """公司名/标题/筛选条件/导出信息四行（分块文档与单表共用）。"""
    sheet.append([company_name])
    _merge_row(sheet, column_count)
    sheet.cell(row=1, column=1).font = _COMPANY_FONT
    sheet.cell(row=1, column=1).alignment = Alignment(horizontal="left", vertical="center")

    sheet.append([title])
    _merge_row(sheet, column_count)
    sheet.cell(row=2, column=1).font = _TITLE_FONT
    sheet.cell(row=2, column=1).alignment = Alignment(horizontal="center", vertical="center")

    sheet.append([f"筛选条件：{filters}"])
    _merge_row(sheet, column_count)
    sheet.append(
        [
            f"导出时间：{datetime.now():%Y-%m-%d %H:%M:%S} ｜ 导出人：{operator_name} ｜ "
            f"模板版本：{template_version}"
        ]
    )
    _merge_row(sheet, column_count)
    sheet.append([])


def _merge_row(sheet, column_count: int) -> None:
    """合并当前行（公司名/标题/筛选行跨全表宽）。"""
    row = sheet.max_row
    if column_count > 1:
        sheet.merge_cells(start_row=row, start_column=1, end_row=row, end_column=column_count)


def _write_row(sheet, columns: tuple[_Column, ...], row: dict) -> None:
    """数据行：按列类型写单元格（金额 Decimal、日期 date、文本防注入）。"""
    values = []
    for column in columns:
        raw = row.get(column.key)
        values.append(_cell_value(column, raw))
    sheet.append(values)
    for index, column in enumerate(columns, start=1):
        cell = sheet.cell(row=sheet.max_row, column=index)
        cell.border = _BORDER
        if column.kind == "amount":
            cell.number_format = _AMOUNT_FORMAT
        elif column.kind == "date":
            cell.number_format = _DATE_FORMAT


def _cell_value(column: _Column, raw):
    """单值转换：标签列转中文，文本列防公式注入。"""
    if raw is None:
        return "待核对" if column.key == "total_sale_amount" else None
    if column.kind == "amount":
        return raw if isinstance(raw, Decimal) else Decimal(str(raw))
    if column.kind == "date":
        return raw
    if column.kind == "int":
        return int(raw)
    if column.kind == "customer":
        return CUSTOMER_TYPE_LABELS.get(str(raw), str(raw))
    if column.kind == "category":
        return PRODUCT_CATEGORY_LABELS.get(str(raw), str(raw))
    if column.kind == "status":
        return BUSINESS_STATUS_LABELS.get(int(raw), str(raw))
    return _safe_text(raw)


def _safe_text(value) -> str:
    """文本防公式注入：以 = + - @ 或制表符开头时前置单引号。"""
    text = str(value)
    return "'" + text if text.startswith(_FORMULA_PREFIXES) else text


def _write_total(sheet, columns: tuple[_Column, ...], rows: list[dict]) -> None:
    """合计行：金额列按行求和后两位收口（不写 Excel 公式）。

    「合计」标签只写在第一个非金额列；其余非金额列留空——否则每遇到
    一个文本列都会重复写一次标签，合计行会满屏「合计」。
    """
    if not rows:
        return
    totals = []
    label_written = False
    for column in columns:
        if column.kind != "amount":
            totals.append("合计" if not label_written else None)
            label_written = True
            continue
        if column.key == "total_sale_amount" and any(row.get(column.key) is None for row in rows):
            totals.append("待核对")
            continue
        total = sum(
            (
                row.get(column.key)
                for row in rows
                if isinstance(row.get(column.key), (Decimal, int))
            ),
            Decimal("0"),
        )
        totals.append(total.quantize(Decimal("0.01"), rounding=ROUND_HALF_UP))
    sheet.append(totals)
    for index, column in enumerate(columns, start=1):
        cell = sheet.cell(row=sheet.max_row, column=index)
        cell.font = _HEADER_FONT
        cell.border = _BORDER
        if column.kind == "amount":
            cell.number_format = _AMOUNT_FORMAT


def _write_signature(sheet, column_count: int) -> None:
    """财务复核栏 / 领导签字栏 / 日期栏（FR-032 模板要素）。"""
    sheet.append([])
    sheet.append(["财务复核：________________", "", "日期：______年__月__日"])
    sheet.append(["领导签字：________________", "", "日期：______年__月__日"])
    for row_index in (sheet.max_row - 1, sheet.max_row):
        sheet.cell(row=row_index, column=1).alignment = Alignment(horizontal="left")


def _autofit(sheet, columns: tuple[_Column, ...]) -> None:
    """列宽按定义表设置（CJK 按 2 字符宽，不用默认 8.43）。"""
    for index, column in enumerate(columns, start=1):
        sheet.column_dimensions[get_column_letter(index)].width = column.width


def _write_dimension_sheet(
    sheet,
    title: str,
    company_name: str,
    filters: str,
    rows: list[dict],
    template_version: str,
    operator_name: str,
) -> None:
    """维度对比 sheet（客户类型/产品类别），页眉筛选条件与主表一致。"""
    columns = (
        _Column("label", "维度", "text", 16),
        _Column("receipt_amount", "回款金额", "amount", 16),
        _Column("performance_amount", "绩效金额", "amount", 14),
        _Column("payable_amount", "应付绩效", "amount", 14),
        _Column("ledger_count", "台账行数", "int", 10),
    )
    _write_header(sheet, columns, company_name, title, filters, template_version, operator_name)
    for row in rows:
        _write_row(sheet, columns, row)
    _write_total(sheet, columns, rows)
    _write_signature(sheet, len(columns))
    _autofit(sheet, columns)


# ==== 部门汇总签字稿（FR-030 / FR-032）====

_MEMBER_KIND_LABELS = {
    "regular": "普通绩效",
    "manager_personal": "经理个人户",
    "adjustment": "仅调整单",
}

# 各块列数不同，统一按列号取最大宽度（签字稿打印时不挤在一起）
_DEPARTMENT_WIDTHS = (30, 16, 22, 13, 13, 15, 18, 14, 14, 13)


def _write_department_document(
    sheet,
    data: dict,
    *,
    company_name: str,
    title: str,
    filters: str,
    template_version: str,
    operator_name: str,
) -> None:
    """部门汇总签字稿：团队分成 → 全公司 5% → 对账 → 成员 → 未分配 → 签字栏。

    全公司 5% 每位有月结聚合行的经理各计一次，合计在第二块展示；
    团队块的调整净额仅供成员汇总核对。应发合计由后端 `reconciliation`
    按聚合行口径算好（含分列舍入差），本函数只渲染。
    """
    params = data.get("params") or {}
    team_text = params.get("team_text", "")
    company_text = params.get("company_text", "")
    teams = data.get("teams") or []
    unassigned = data.get("unassigned") or []
    company = data.get("company") or {}
    reconciliation = data.get("reconciliation") or {}
    company_share_count = int(company.get("company_share_count") or 0)

    _write_document_header(
        sheet,
        len(_DEPARTMENT_WIDTHS),
        company_name,
        title,
        filters,
        template_version,
        operator_name,
    )

    _write_block(
        sheet,
        _Block(
            "一、团队分成汇总",
            (
                _Column("seq", "序号", "int", 6),
                _Column("manager_name", "经理团队", "text", 16),
                _Column("manager_status_text", "状态", "text", 8),
                _Column("member_count", "团队人数", "int", 9),
                _Column("team_base", "团队基数", "amount", 13),
                _Column("team_share_amount", f"团队分成（{team_text}%）", "amount", 15),
                _Column("adjust_net", "调整净额（仅核对）", "amount", 16),
                _Column("team_subtotal", "团队应发小计（含聚合分成）", "amount", 18),
                _Column("frozen_amount", "月结冻结额", "amount", 14),
            ),
            [
                {
                    "seq": index,
                    "manager_name": team["manager_name"],
                    "manager_status_text": "在职" if team["manager_status"] == 1 else "离职",
                    "member_count": team["member_count"],
                    "team_base": team["team_base"],
                    "team_share_amount": team["team_share_amount"],
                    "adjust_net": team["adjust_net"],
                    "team_subtotal": team["team_subtotal"],
                    "frozen_amount": team["frozen_amount"],
                }
                for index, team in enumerate(teams, start=1)
            ],
            {
                "manager_name": "合计",
                "member_count": sum(team["member_count"] for team in teams),
                "team_base": _sum_amount(team["team_base"] for team in teams),
                "team_share_amount": _sum_amount(team["team_share_amount"] for team in teams),
                "team_subtotal": _sum_amount(team["team_subtotal"] for team in teams),
                # 未生成月结时全部为 None，合计也留空（不能显示 0.00 误导）
                "frozen_amount": (
                    _sum_amount(
                        team["frozen_amount"] for team in teams if team["frozen_amount"] is not None
                    )
                    if any(team["frozen_amount"] is not None for team in teams)
                    else None
                ),
            },
            "（无团队）",
        ),
    )

    _write_block(
        sheet,
        _Block(
            f"二、全公司分成（每位有聚合行的经理各计一次，共 {company_share_count} 次）",
            (
                _Column("company_base", "全公司基数", "amount", 16),
                _Column("company_share_pct", "分成比例", "text", 12),
                _Column("company_share_amount", "单经理分成", "amount", 13),
                _Column("company_share_count", "计入次数", "int", 10),
                _Column("company_share_total", "分成合计", "amount", 14),
            ),
            [
                {
                    "company_base": company.get("company_base"),
                    "company_share_pct": f"{company_text}%",
                    "company_share_amount": company.get("company_share_amount"),
                    "company_share_count": company_share_count,
                    "company_share_total": company.get("company_share_total"),
                }
            ],
        ),
    )

    _write_block(
        sheet,
        _Block(
            "三、对账汇总",
            (
                _Column("item", "项目", "text", 30),
                _Column("amount", "金额", "amount", 16),
                _Column("note", "说明", "text", 40),
            ),
            [
                {
                    "item": "团队成员应付合计",
                    "amount": reconciliation.get("team_member_payable_total"),
                },
                {
                    "item": "未分配人员应付合计",
                    "amount": reconciliation.get("unassigned_payable_total"),
                },
                {"item": "团队分成合计（分列）", "amount": reconciliation.get("team_share_total")},
                {
                    "item": f"全公司分成合计（{company_share_count} 次，分列）",
                    "amount": reconciliation.get("company_share_total"),
                    "note": "每位有聚合行的经理各计一次",
                },
                {
                    "item": "聚合行舍入差",
                    "amount": reconciliation.get("aggregate_rounding_diff"),
                    "note": "分列舍入与聚合行一次舍入的差",
                },
                {
                    "item": "调整净额合计（明细层）",
                    "amount": reconciliation.get("adjust_net_total"),
                },
                {
                    "item": "应发合计",
                    "amount": reconciliation.get("payable_total"),
                    "note": "上列各项之和",
                },
                {"item": "团队基数合计", "amount": reconciliation.get("team_base_total")},
                {
                    "item": "未分配绩效合计",
                    "amount": reconciliation.get("unassigned_performance_total"),
                },
                {
                    "item": "其他未展示基数",
                    "amount": reconciliation.get("other_team_base"),
                    "note": "筛选时含其他团队与全公司未分配",
                },
                {"item": "全公司基数", "amount": reconciliation.get("company_base")},
            ],
        ),
    )

    member_columns = (
        _Column("manager_name", "经理团队", "text", 15),
        _Column("personnel_name", "成员", "text", 12),
        _Column("role_code", "岗位", "text", 10),
        _Column("member_kind_text", "来源", "text", 13),
        _Column("performance_sum", "绩效金额", "amount", 13),
        _Column("payable_sum", "应付绩效", "amount", 13),
        _Column("adjust_extra", "调整补发", "amount", 12),
        _Column("adjust_deduct", "调整扣减", "amount", 12),
        _Column("adjust_net", "调整净额", "amount", 12),
        _Column("net_total", "净合计", "amount", 13),
    )
    member_rows = [
        {
            "manager_name": team["manager_name"],
            "personnel_name": member["personnel_name"],
            "role_code": member["role_code"],
            "member_kind_text": _MEMBER_KIND_LABELS.get(
                member["member_kind"], member["member_kind"]
            ),
            "performance_sum": member["performance_sum"],
            "payable_sum": member["payable_sum"],
            "adjust_extra": member["adjust_extra"],
            "adjust_deduct": member["adjust_deduct"],
            "adjust_net": member["adjust_net"],
            "net_total": member["net_total"],
        }
        for team in teams
        for member in team["members"]
    ]
    _write_block(
        sheet,
        _Block("四、团队成员明细", member_columns, member_rows, empty_text="（无成员）"),
        sum_amounts=True,
    )

    unassigned_columns = (
        _Column("personnel_name", "人员", "text", 14),
        _Column("role_code", "岗位", "text", 10),
        _Column("unassigned_reason", "未纳入原因", "text", 24),
        _Column("performance_sum", "绩效金额", "amount", 13),
        _Column("payable_sum", "应付绩效", "amount", 13),
        _Column("adjust_extra", "调整补发", "amount", 12),
        _Column("adjust_deduct", "调整扣减", "amount", 12),
        _Column("adjust_net", "调整净额", "amount", 12),
        _Column("net_total", "净合计", "amount", 13),
    )
    unassigned_rows = [
        {
            "personnel_name": row["personnel_name"],
            "role_code": row["role_code"],
            "unassigned_reason": row.get("unassigned_reason") or "",
            "performance_sum": row["performance_sum"],
            "payable_sum": row["payable_sum"],
            "adjust_extra": row["adjust_extra"],
            "adjust_deduct": row["adjust_deduct"],
            "adjust_net": row["adjust_net"],
            "net_total": row["net_total"],
        }
        for row in unassigned
    ]
    _write_block(
        sheet,
        _Block("五、未分配人员明细", unassigned_columns, unassigned_rows),
        sum_amounts=True,
    )

    _write_signature(sheet, len(_DEPARTMENT_WIDTHS))
    for index, width in enumerate(_DEPARTMENT_WIDTHS, start=1):
        sheet.column_dimensions[get_column_letter(index)].width = width


def _write_block(sheet, block: _Block, *, sum_amounts: bool = False) -> None:
    """写一个区块：标题、表头、数据/空提示，以及自定义或自动合计。"""
    sheet.append([block.title])
    title_row = sheet.max_row
    _merge_row(sheet, len(block.columns))
    sheet.cell(row=title_row, column=1).font = _HEADER_FONT

    sheet.append([column.title for column in block.columns])
    header_row = sheet.max_row
    for cell in sheet[header_row]:
        cell.font = _HEADER_FONT
        cell.fill = _HEADER_FILL
        cell.border = _BORDER
        cell.alignment = Alignment(horizontal="center", vertical="center")

    if block.rows:
        for row in block.rows:
            _write_row(sheet, block.columns, row)
    else:
        sheet.append([block.empty_text])
        _merge_row(sheet, len(block.columns))

    if block.total is not None:
        _write_custom_total(sheet, block.columns, block.total)
    elif sum_amounts:
        _write_total(sheet, block.columns, block.rows)
    sheet.append([])


def _write_custom_total(sheet, columns: tuple[_Column, ...], total: dict) -> None:
    """写后端预计算的合计行（不自动求和，避免重复计入共享基数）。"""
    values = []
    for column in columns:
        raw = total.get(column.key)
        values.append(_cell_value(column, raw) if raw is not None else None)
    sheet.append(values)
    for index, column in enumerate(columns, start=1):
        cell = sheet.cell(row=sheet.max_row, column=index)
        cell.font = _HEADER_FONT
        cell.border = _BORDER
        if column.kind == "amount":
            cell.number_format = _AMOUNT_FORMAT


def _sum_amount(values) -> Decimal:
    """金额列求和后两位收口（None 跳过）。"""
    return sum((value for value in values if value is not None), Decimal("0")).quantize(
        Decimal("0.01"), rounding=ROUND_HALF_UP
    )