kg-agent/scripts/generate_test_data.py

"""生成单表结构的测试数据（与 data/templates 的 10 份模板一一对应）。"""

from __future__ import annotations

import sys
from pathlib import Path

from openpyxl import Workbook
from openpyxl.comments import Comment
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation


sys.stdout.reconfigure(encoding="utf-8")
OUT_DIR = Path(r"E:\CODE\knowledge_agent\data\test_data")
OUT_DIR.mkdir(parents=True, exist_ok=True)

HEADER_FILL = PatternFill("solid", fgColor="2F5597")
HEADER_FONT = Font(name="微软雅黑", size=10, bold=True, color="FFFFFF")
BODY_FONT = Font(name="微软雅黑", size=10)
NOTE_FONT = Font(name="微软雅黑", size=9, color="808080")
THIN = Side(style="thin", color="BFBFBF")
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
TEST_FILL = PatternFill("solid", fgColor="FFF2CC")


def _make_test_file(filename: str, title: str, headers: list[str], widths: list[int],
                    rows: list[list], notes: list[str],
                    validations: dict[int, list[str]] | None = None,
                    comments: dict[int, str] | None = None) -> None:
    wb = Workbook()
    ws = wb.active
    ws.title = title
    for c, (h, w) in enumerate(zip(headers, widths), start=1):
        cell = ws.cell(row=1, column=c, value=h)
        cell.font = HEADER_FONT
        cell.fill = HEADER_FILL
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        cell.border = BORDER
        ws.column_dimensions[get_column_letter(c)].width = w
    ws.row_dimensions[1].height = 30
    for r, row in enumerate(rows, start=2):
        for c, v in enumerate(row, start=1):
            cell = ws.cell(row=r, column=c, value=v)
            cell.font = BODY_FONT
            cell.border = BORDER
            cell.fill = TEST_FILL
            cell.alignment = Alignment(vertical="center", wrap_text=True)
    if validations:
        for col, values in validations.items():
            letter = get_column_letter(col)
            dv = DataValidation(type="list", formula1='"' + ",".join(values) + '"',
                                allow_blank=True, showDropDown=False)
            ws.add_data_validation(dv)
            dv.add(f"{letter}2:{letter}500")
    if comments:
        for col, text in comments.items():
            ws.cell(row=1, column=col).comment = Comment(text, "测试数据生成")
    ws.freeze_panes = "A2"
    note_ws = wb.create_sheet("填写说明")
    note_ws.column_dimensions["A"].width = 115
    note_ws.cell(row=1, column=1, value=f"【{title}】测试数据说明").font = Font(name="微软雅黑", size=12, bold=True)
    lines = ["本文件为测试数据（黄色行），与 data/templates 对应模板同构，用于管线/问答测试。",
             "覆盖场景：续签链、项目从属、人员跨项目流动（董一青）、设备台账、采购维保、检查记录等。"] + notes
    for i, n in enumerate(lines, start=2):
        cell = note_ws.cell(row=i, column=1, value=f"· {n}")
        cell.font = NOTE_FONT
        cell.alignment = Alignment(wrap_text=True, vertical="top")
    wb.save(OUT_DIR / filename)


def main() -> None:
    days = [f"{i}日" for i in range(1, 32)]
    dw = [5] * 31

    _make_test_file(
        "项目信息_测试数据.xlsx", "项目主表",
        ["项目编号*", "项目名称*", "项目简称（选填）", "上级项目编号", "续签前项目编号",
         "项目起止时间", "省份", "市（区）", "细分业态", "汇总业态", "委托方式",
         "计费方式", "合同面积（㎡）", "合同金额（元）", "年化合同额（元）", "合同额具体信息",
         "服务期限", "合同状况", "首次服务日期", "年化合同收入（元）", "客户满意度（0-100）",
         "客户投诉（条）", "甲方名称", "项目地址", "项目负责人", "项目负责人工号*",
         "实际毛利率（%）", "服务状态", "备注"],
        [26, 18, 20, 22, 22, 24, 10, 10, 12, 12, 12, 12, 12, 14, 16, 16, 20, 18,
         12, 14, 16, 16, 12, 28, 24, 14, 16, 14, 12, 20],
        [
            ["XMSQ0199", "青浦区图书馆（2025期）", "青浦-区图书馆", "", "", "2025-02-28~2026-02-27",
             "上海", "青浦", "场馆", "公众物业", "多项单委", "包干", 16562,
             3199128, 3199110.84, "当期合同额 3199110.84", "服务期一年（公开）", "已签",
             "2020-01-01", 3199110.84, "", "", "上海市青浦区图书馆", "青浦区青龙路60号",
             "张某某", "KWL2699006", "", "历史", "测试（上一期，续签链起点）"],
            ["XMSQ0102", "上海市青浦区图书馆", "青浦-区图书馆", "", "XMSQ0199",
             "2026-02-28~2027-02-27", "上海", "青浦", "场馆", "公众物业", "多项单委",
             "包干", 16562, 3199128, 3199110.84, "当期合同额 3199110.84", "服务期一年（公开）",
             "已签", "2020-01-01", 3199110.84, "", "", "上海市青浦区图书馆", "青浦区青龙路60号",
             "张某某", "KWL2699006", "", "服务中", "测试（当前期，续签自 2025期）"],
            ["XMSQ0108", "国家税务总局上海市青浦区税务局", "青浦-税务局", "", "", "2026-01-01~2026-12-31",
             "上海", "青浦", "政府机构", "机关办公楼", "全委", "包干", 38999,
             7943908, 7943908, "当期合同额 7943908", "服务期一年（公开）", "已签",
             "2020-01-01", 7943908, "", "", "国家税务总局上海市青浦区税务局", "青浦区城中西路80号",
             "王某某", "KWL2699007", "", "服务中", "测试（父项目）"],
            ["XMSQ-5-1", "税务局本部", "税务局本部", "XMSQ0108", "",
             "2026-01-01~2026-12-31", "上海", "青浦", "政府机构", "机关办公楼", "",
             "包干", 8000, "", "", "", "", "", "2020-01-01", "", "", "",
             "国家税务总局上海市青浦区税务局", "青浦区城中西路80号", "赵某某", "KWL2699021", "", "服务中",
             "测试（子项目，无金蝶编码）"],
            ["XMSQ-5-2", "第十税务所", "第十税务所", "XMSQ0108", "",
             "2026-01-01~2026-12-31", "上海", "青浦", "政府机构", "机关办公楼",
             "多项单委", "包干", 5000, "", "", "", "", "", "2020-01-01", "", "", "",
             "国家税务总局上海市青浦区税务局", "青浦区徐泾镇盈港东路1838号", "赵某某", "KWL2699021", "", "服务中",
             "测试（子项目）"],
            ["XMSQ0146", "青浦区赵巷公园", "青浦-赵巷公园", "", "", "2026-01-01~2026-12-31",
             "上海", "青浦", "公园", "公众物业", "多项单委", "包干", 60000,
             800000, 800000, "", "服务期一年", "已签", "2024-01-01", 800000, "", "",
             "青浦区赵巷镇人民政府", "青浦区赵巷公园", "周某某", "KWL2699008", "", "服务中", "测试"],
            ["XMSQ0130", "青浦区赵巷镇政府", "青浦-赵巷镇政府", "", "", "2026-01-01~2026-12-31",
             "上海", "青浦", "政府机构", "机关办公楼", "全委", "包干", 14350,
             1435000, 1435000, "", "服务期一年", "已签", "2021-01-01", 1435000, "", "",
             "青浦区赵巷镇人民政府", "青浦区赵巷镇沙家埭路18号", "周某某", "KWL2699008", "", "服务中",
             "测试（董一青 1-2月）"],
            ["XMSQ01141", "青浦-北斗园区", "青浦-北斗园区", "", "", "2026-01-01~2026-12-31",
             "上海", "青浦", "园区", "商业办公", "全委", "包干", 50000,
             3600000, 3600000, "", "服务期一年", "已签", "2024-01-01", 3600000, "", "",
             "北斗园区运营方", "青浦区北斗园区", "吴某某", "KWL2699023", "", "服务中", "测试（董一青 3-4月）"],
            ["XMSQ0131", "青浦区青东办", "青浦-青东办", "", "", "2026-01-01~2026-12-31",
             "上海", "青浦", "机关办公楼", "机关办公楼", "全委", "包干", 10000,
             600000, 600000, "", "服务期一年", "已签", "2023-01-01", 600000, "", "",
             "青浦区机关事务管理局", "青浦区青东办", "郑某某", "KWL2699024", "", "服务中", "测试（徐娥英）"],
            ["XMSQ0153", "青浦工业园区大楼", "青浦-工业园区", "", "", "2026-01-01~2026-12-31",
             "上海", "青浦", "园区", "商业办公", "全委", "包干", 80000,
             1800000, 1800000, "", "服务期一年", "已签", "2005-01-01", 1800000, "", "",
             "青浦工业园区管理委员会", "青浦工业园区", "孙某某", "KWL2699025", "", "服务中", "测试（设备台账项目）"],
            ["XMSQ0176", "上海市环境监测中心", "徐汇-环境监测中心", "", "", "2026-01-01~2026-12-31",
             "上海", "徐汇", "机关办公楼", "机关办公楼", "全委", "包干", 30000,
             1500000, 1500000, "", "服务期一年", "已签", "2014-01-01", 1500000, "", "",
             "上海市环境监测中心", "上海市环境监测中心", "钱某某", "KWL2699026", "", "服务中", "测试（设备台账项目）"],
            ["XMSQ9031", "上海市青浦区档案馆", "青浦-档案馆", "", "", "2026-01-01~2026-12-31",
             "上海", "青浦", "政府机构", "机关办公楼", "全委", "包干", 12000,
             900000, 900000, "", "服务期一年", "已签", "2018-01-01", 900000, "", "",
             "上海市青浦区档案馆", "青浦区档案馆", "刘某某", "KWL2699105", "", "服务中", "测试（新增项目）"],
            ["XMSQ9032", "青浦区民防城运中心", "青浦民防城运", "", "", "2026-01-01~2026-12-31",
             "上海", "青浦", "机关办公楼", "机关办公楼", "全委", "包干", 8000,
             600000, 600000, "", "服务期一年", "已签", "2019-01-01", 600000, "", "",
             "青浦区民防办公室", "青浦区民防城运中心", "何某某", "KWL2699106", "", "服务中", "测试（新增项目）"],
            ["XMSQ9033", "上海大众工业学校", "嘉定-大众工业学校", "", "", "2026-01-01~2026-12-31",
             "上海", "嘉定", "学校", "学校", "全委", "包干", 40000,
             2200000, 2200000, "", "服务期一年", "已签", "2019-09-01", 2200000, "", "",
             "上海大众工业学校", "嘉定区环城路2290号", "罗某某", "KWL2699107", "", "服务中", "测试（新增项目）"],
            ["XMSQ9034", "上海市徐汇区群众艺术馆", "徐汇-群艺馆", "", "", "2026-01-01~2026-12-31",
             "上海", "徐汇", "场馆", "公众物业", "全委", "包干", 18000,
             1200000, 1200000, "", "服务期一年", "已签", "2017-01-01", 1200000, "", "",
             "上海市徐汇区文化和旅游局", "徐汇区群艺馆", "彭某某", "KWL2699108", "", "服务中", "测试（新增项目）"],
            ["XMSQ9035", "上海市松江区公安局", "松江-公安局", "", "", "2026-01-01~2026-12-31",
             "上海", "松江", "政府机构", "机关办公楼", "全委", "包干", 25000,
             1800000, 1800000, "", "服务期一年", "已签", "2016-01-01", 1800000, "", "",
             "上海市松江区公安局", "松江区公安局", "田某某", "KWL2699109", "", "服务中", "测试（新增项目）"],
        ],
        ["覆盖续签链（2025期→当前期）与项目从属（税务局→税务局本部/第十税务所）。"],
    )

    _make_test_file(
        "项目月度财务_测试数据.xlsx", "项目财务明细",
        ["项目编号*", "项目名称（选填）", "年月（YYYY-MM）*", "开票金额（元）", "收款金额（元）", "口径说明",
         "财务经办人工号*", "财务经办人（选填）", "备注"],
        [20, 26, 16, 16, 16, 30, 16, 14, 20],
        [
            ["XMSQ0102", "上海市青浦区图书馆", "2026-01", 266594, 266594, "测试口径", "KWL2699012", "财务员甲", "测试"],
            ["XMSQ0102", "上海市青浦区图书馆", "2026-02", 266594, 266594, "测试口径", "KWL2699012", "财务员甲", "测试"],
            ["XMSQ0102", "上海市青浦区图书馆", "2026-03", 266594, 266594, "测试口径", "KWL2699012", "财务员甲", "测试"],
            ["XMSQ0153", "青浦工业园区大楼", "2026-01", 150000, 140000, "测试口径", "KWL2699012", "财务员甲", "测试"],
            ["XMSQ0176", "上海市环境监测中心", "2026-02", 125000, 125000, "测试口径", "KWL2699012", "财务员甲", "测试"],
            ["XMSQ9031", "上海市青浦区档案馆", "2026-01", 75000, 75000, "测试口径", "KWL2699012", "财务员甲", "测试"],
            ["XMSQ9032", "青浦区民防城运中心", "2026-02", 50000, 48000, "测试口径", "KWL2699012", "财务员甲", "测试"],
            ["XMSQ9033", "上海大众工业学校", "2026-03", 183333, 180000, "测试口径", "KWL2699012", "财务员甲", "测试"],
            ["XMSQ9034", "上海市徐汇区群众艺术馆", "2026-01", 100000, 100000, "测试口径", "KWL2699012", "财务员甲", "测试"],
        ],
        ["测试项目月度财务（开票/收款）。"],
    )

    _make_test_file(
        "投标记录_测试数据.xlsx", "投标记录",
        ["项目编号*", "项目名称（选填）", "年份*", "投标类型", "中标结果", "中标日期", "投标金额（元）", "文档路径",
         "投标经办人工号*", "投标经办人（选填）", "备注"],
        [20, 26, 10, 14, 12, 14, 16, 50, 16, 14, 20],
        [
            ["XMSQ0102", "上海市青浦区图书馆", 2024, "中标通知书", "中标", "2024-02-01", 3199128,
             "1-青浦区域/102-青浦区图书馆", "KWL2699010", "陈某某", "测试"],
            ["XMSQ0102", "上海市青浦区图书馆", 2026, "中标通知书", "中标", "2026-02-01", 3199128,
             "1-青浦区域/102-青浦区图书馆", "KWL2699010", "陈某某", "测试"],
            ["XMSQ0108", "国家税务总局上海市青浦区税务局", 2026, "中标通知书", "中标", "2025-12-20", 7943908,
             "1-青浦区域/108-青浦区税务局", "KWL2699010", "陈某某", "测试"],
            ["XMSQ9031", "上海市青浦区档案馆", 2026, "中标通知书", "中标", "2026-01-10", 900000,
             "1-青浦区域/青浦区档案馆", "KWL2699010", "陈某某", "测试"],
            ["XMSQ9034", "上海市徐汇区群众艺术馆", 2025, "中标公告", "中标", "2025-06-15", 1200000,
             "4-中心区域/徐汇群艺馆", "KWL2699010", "陈某某", "测试"],
            ["XMSQ9033", "上海大众工业学校", 2026, "投标文件", "未知", "", 2200000,
             "3-嘉定区域/大众工业学校", "KWL2699010", "陈某某", "测试"],
        ],
        ["测试投标/中标事件记录。"],
    )

    _make_test_file(
        "人员信息_测试数据.xlsx", "人员主表",
        ["姓名*", "工号*", "组织名称", "岗位名称*", "当前服务项目编号（选填）", "服务起始年月", "服务结束年月",
         "职级", "员工层级", "所属组织名称", "入职日期", "离职日期", "司龄", "性别",
         "出生日期", "政治面貌", "联系电话", "备注"],
        [14, 16, 14, 20, 22, 14, 14, 10, 16, 14, 14, 14, 10, 8, 14, 12, 16, 20],
        [
            ["陆雷雷", "KWL2306998", "保安部", "保安_领班", "XMSQ0146", "2026-01", "2026-12",
             "P3", "一线员工", "保安部", "2025-01-01", "", "1.5年", "男", "1980-05-12", "汉族", "", "真实员工"],
            ["王惠云", "KWL2307010", "保安部", "保安_普保", "XMSQ0146", "2026-01", "2026-12",
             "P2", "一线员工", "保安部", "2024-06-01", "", "2年", "男", "1982-03-08", "汉族", "", "真实员工"],
            ["董一青", "KWL2307024", "管理处", "项目经理", "XMSQ01141", "2026-01", "2026-04",
             "P5", "项目管理人员", "管理处", "2023-02-01", "", "3年", "男", "1978-11-20", "汉族", "",
             "真实员工（1-2月赵巷镇政府，3-4月北斗园区，由考勤明细体现）"],
            ["徐娥英", "KWL2309539", "保洁部", "保洁_内场", "XMSQ0131", "2026-01", "2026-12",
             "P1", "一线员工", "保洁部", "2020-05-01", "", "6年", "女", "1975-09-15", "汉族", "", "真实员工"],
            ["测试甲", "KWL2699001", "会务部", "会务_接待", "XMSQ0102", "2026-01", "2026-12",
             "P3", "一线员工", "会务部", "2024-03-01", "", "2年", "女", "1990-01-01", "汉族", "", "构造员工"],
            ["测试乙", "KWL2699002", "保安部", "保安_领班", "XMSQ0102", "2026-01", "2026-12",
             "P2", "一线员工", "保安部", "2023-08-01", "", "2.5年", "男", "1985-06-15", "汉族", "", "构造员工"],
            ["测试丙", "KWL2699003", "工程部", "工程_强电", "XMSQ0153", "2026-01", "2026-12",
             "P3", "一线员工", "工程部", "2022-04-01", "", "4年", "男", "1988-12-01", "汉族", "", "构造员工"],
            ["测试丁", "KWL2699004", "保洁部", "保洁_外场", "XMSQ0176", "2026-01", "2026-12",
             "P1", "一线员工", "保洁部", "2023-01-10", "", "3年", "女", "1979-07-07", "汉族", "", "构造员工"],
            ["陈志强", "KWL2699101", "保安部", "保安_普保", "XMSQ0146", "2026-01", "2026-12",
             "P2", "一线员工", "保安部", "2023-05-20", "", "3年", "男", "1986-04-15", "汉族", "", "构造员工"],
            ["林芳", "KWL2699102", "保洁部", "保洁_外场", "XMSQ0146", "2026-01", "2026-12",
             "P1", "一线员工", "保洁部", "2022-08-01", "", "4年", "女", "1981-10-02", "汉族", "", "构造员工"],
            ["黄伟", "KWL2699103", "工程部", "工程_水电", "XMSQ0153", "2026-01", "2026-12",
             "P3", "一线员工", "工程部", "2021-03-15", "", "5年", "男", "1984-02-28", "汉族", "", "构造员工"],
            ["杨丽", "KWL2699104", "会务部", "会务_领班", "XMSQ0102", "2026-01", "2026-12",
             "P3", "一线员工", "会务部", "2022-11-01", "", "3.5年", "女", "1990-08-12", "汉族", "", "构造员工"],
            ["张三", "KWL2699005", "管理处", "品质专员", "XMSQ0102", "2026-01", "2026-12",
             "P3", "职能人员", "管理处", "2021-06-01", "", "5年", "男", "1983-03-03", "汉族", "", "构造员工（检查人）"],
            ["李四", "KWL2699202", "管理处", "品质专员", "XMSQ0108", "2026-01", "2026-12",
             "P3", "职能人员", "管理处", "2021-09-01", "", "5年", "男", "1982-07-19", "汉族", "", "构造员工（检查人）"],
            ["王五", "KWL2699203", "管理处", "品质专员", "XMSQ0176", "2026-01", "2026-12",
             "P3", "职能人员", "管理处", "2020-12-01", "", "5.5年", "男", "1984-11-11", "汉族", "", "构造员工（检查人）"],
            ["陈某某", "KWL2699010", "管理处", "投标专员", "", "2026-01", "2026-12",
             "P3", "职能人员", "管理处", "2021-04-01", "", "5年", "男", "1986-05-05", "汉族", "", "构造员工（投标经办人）"],
            ["采购员甲", "KWL2699011", "管理处", "采购专员", "", "2026-01", "2026-12",
             "P3", "职能人员", "管理处", "2020-03-01", "", "6年", "女", "1985-09-09", "汉族", "", "构造员工（采购经办人）"],
            ["财务员甲", "KWL2699012", "管理处", "财务专员", "", "2026-01", "2026-12",
             "P3", "职能人员", "管理处", "2019-08-01", "", "7年", "女", "1981-12-12", "汉族", "", "构造员工（财务经办人）"],
            ["设备员甲", "KWL2699013", "工程部", "设备管理员", "", "2026-01", "2026-12",
             "P3", "一线员工", "工程部", "2020-05-01", "", "6年", "男", "1983-01-25", "汉族", "", "构造员工（设备责任人）"],
            ["王片区", "KWL2699020", "管理处", "片区负责人", "", "2026-01", "2026-12",
             "P4", "项目管理人员", "管理处", "2018-01-01", "", "8年", "男", "1978-06-06", "汉族", "", "构造员工（片区负责人）"],
            ["李片区", "KWL2699022", "管理处", "片区负责人", "", "2026-01", "2026-12",
             "P4", "项目管理人员", "管理处", "2019-02-01", "", "7年", "女", "1980-04-04", "汉族", "", "构造员工（片区负责人）"],
            ["张某某", "KWL2699006", "管理处", "项目经理", "XMSQ0102", "2026-01", "2026-12",
             "P5", "项目管理人员", "管理处", "2019-05-01", "", "7年", "男", "1979-08-08", "汉族", "", "构造员工（项目负责人）"],
            ["王某某", "KWL2699007", "管理处", "项目经理", "XMSQ0108", "2026-01", "2026-12",
             "P5", "项目管理人员", "管理处", "2018-10-01", "", "8年", "男", "1977-02-02", "汉族", "", "构造员工（项目负责人）"],
            ["赵某某", "KWL2699021", "管理处", "项目经理", "XMSQ-5-1", "2026-01", "2026-12",
             "P4", "项目管理人员", "管理处", "2020-07-01", "", "6年", "男", "1981-10-10", "汉族", "", "构造员工（项目负责人）"],
            ["周某某", "KWL2699008", "管理处", "项目经理", "XMSQ0146", "2026-01", "2026-12",
             "P4", "项目管理人员", "管理处", "2019-11-01", "", "7年", "男", "1980-12-12", "汉族", "", "构造员工（项目负责人）"],
            ["吴某某", "KWL2699023", "管理处", "项目经理", "XMSQ01141", "2026-01", "2026-12",
             "P4", "项目管理人员", "管理处", "2020-01-01", "", "6年", "男", "1979-09-09", "汉族", "", "构造员工（项目负责人）"],
            ["郑某某", "KWL2699024", "管理处", "项目经理", "XMSQ0131", "2026-01", "2026-12",
             "P4", "项目管理人员", "管理处", "2020-02-01", "", "6年", "女", "1982-05-05", "汉族", "", "构造员工（项目负责人）"],
            ["孙某某", "KWL2699025", "管理处", "项目经理", "XMSQ0153", "2026-01", "2026-12",
             "P4", "项目管理人员", "管理处", "2019-03-01", "", "7年", "男", "1978-07-07", "汉族", "", "构造员工（项目负责人）"],
            ["钱某某", "KWL2699026", "管理处", "项目经理", "XMSQ0176", "2026-01", "2026-12",
             "P4", "项目管理人员", "管理处", "2019-04-01", "", "7年", "女", "1981-01-01", "汉族", "", "构造员工（项目负责人）"],
            ["刘某某", "KWL2699105", "管理处", "项目经理", "XMSQ9031", "2026-01", "2026-12",
             "P4", "项目管理人员", "管理处", "2020-06-01", "", "6年", "男", "1979-06-06", "汉族", "", "构造员工（项目负责人）"],
            ["何某某", "KWL2699106", "管理处", "项目经理", "XMSQ9032", "2026-01", "2026-12",
             "P4", "项目管理人员", "管理处", "2020-08-01", "", "6年", "女", "1983-03-03", "汉族", "", "构造员工（项目负责人）"],
            ["罗某某", "KWL2699107", "管理处", "项目经理", "XMSQ9033", "2026-01", "2026-12",
             "P4", "项目管理人员", "管理处", "2019-12-01", "", "6.5年", "男", "1978-11-11", "汉族", "", "构造员工（项目负责人）"],
            ["彭某某", "KWL2699108", "管理处", "项目经理", "XMSQ9034", "2026-01", "2026-12",
             "P4", "项目管理人员", "管理处", "2020-09-01", "", "6年", "女", "1980-10-10", "汉族", "", "构造员工（项目负责人）"],
            ["田某某", "KWL2699109", "管理处", "项目经理", "XMSQ9035", "2026-01", "2026-12",
             "P4", "项目管理人员", "管理处", "2019-01-01", "", "7.5年", "男", "1977-12-12", "汉族", "", "构造员工（项目负责人）"],
        ],
        ["服务起始/结束年月用于直接生成 服务于 边；董一青的跨项目流动由考勤明细体现。"],
    )

    def att_row(emp_id, name, proj_code, proj_name, ym, day_code, days, work, ot, hol, meal, ot_fee, hol_fee, duty, note):
        d = [day_code if i <= days else "" for i in range(1, 32)]
        return [name, emp_id, proj_code, proj_name, ym, *d, work, ot, hol, meal, ot_fee, hol_fee, duty, "", note]

    att_headers = ["姓名*", "工号*", "项目编号*", "项目名称（选填）", "年月（YYYY-MM）*", *days,
                   "上班天数", "平时加班小时", "国定加班小时", "餐费补助金额（元）",
                   "加班超时费金额（元）", "国定加班费金额（元）", "值班费金额（元）", "税后工资（元）", "备注"]
    att_widths = [14, 16, 20, 26, 16, *dw, 10, 12, 12, 16, 18, 18, 16, 16, 20]
    att_rows = [
        att_row("KWL2306998", "陆雷雷", "XMSQ0146", "青浦区赵巷公园", "2026-01", "日1", 22, 22, 74, 0, 308, 1748.25, 0, 4896.25, "测试"),
        att_row("KWL2306998", "陆雷雷", "XMSQ0146", "青浦区赵巷公园", "2026-02", "日1", 20, 20, 60, 0, 280, 1417.5, 0, 4620, "测试"),
        att_row("KWL2306998", "陆雷雷", "XMSQ0146", "青浦区赵巷公园", "2026-03", "日1", 21, 21, 70, 0, 294, 1653.75, 0, 4725, "测试"),
        att_row("KWL2307010", "王惠云", "XMSQ0146", "青浦区赵巷公园", "2026-01", "日", 21, 21, 57, 11, 147, 1346.625, 519.75, 0, "测试（日夜班示例）"),
        att_row("KWL2307024", "董一青", "XMSQ0130", "青浦区赵巷镇政府", "2026-01", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试（1月赵巷镇政府）"),
        att_row("KWL2307024", "董一青", "XMSQ0130", "青浦区赵巷镇政府", "2026-02", "常", 20, 20, 8, 0, 140, 189, 0, 0, "测试（2月赵巷镇政府）"),
        att_row("KWL2307024", "董一青", "XMSQ01141", "青浦-北斗园区", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试（3月北斗园区）"),
        att_row("KWL2307024", "董一青", "XMSQ01141", "青浦-北斗园区", "2026-04", "常", 21, 21, 5, 0, 147, 118.125, 0, 0, "测试（4月北斗园区）"),
        att_row("KWL2309539", "徐娥英", "XMSQ0131", "青浦区青东办", "2026-01", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"),
        att_row("KWL2309539", "徐娥英", "XMSQ0131", "青浦区青东办", "2026-02", "常", 20, 20, 0, 0, 140, 0, 0, 0, "测试"),
        att_row("KWL2309539", "徐娥英", "XMSQ0131", "青浦区青东办", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"),
        att_row("KWL2699001", "测试甲", "XMSQ0102", "上海市青浦区图书馆", "2026-01", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"),
        att_row("KWL2699001", "测试甲", "XMSQ0102", "上海市青浦区图书馆", "2026-02", "常", 20, 20, 4, 0, 140, 94.5, 0, 0, "测试"),
        att_row("KWL2699001", "测试甲", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "常", 21, 21, 6, 0, 147, 150.75, 0, 0, "测试"),
        att_row("KWL2699002", "测试乙", "XMSQ0102", "上海市青浦区图书馆", "2026-01", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"),
        att_row("KWL2699002", "测试乙", "XMSQ0102", "上海市青浦区图书馆", "2026-02", "常", 20, 20, 0, 0, 140, 0, 0, 0, "测试"),
        att_row("KWL2699003", "测试丙", "XMSQ0153", "青浦工业园区大楼", "2026-02", "常", 20, 20, 10, 0, 140, 236.25, 0, 0, "测试"),
        att_row("KWL2699004", "测试丁", "XMSQ0176", "上海市环境监测中心", "2026-02", "常", 20, 20, 0, 0, 140, 0, 0, 0, "测试"),
        att_row("KWL2699002", "测试乙", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"),
        att_row("KWL2699005", "张三", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"),
        att_row("KWL2699006", "张某某", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"),
        att_row("KWL2699101", "陈志强", "XMSQ0146", "青浦区赵巷公园", "2026-01", "日", 21, 21, 0, 0, 147, 0, 0, 0, "测试"),
        att_row("KWL2699101", "陈志强", "XMSQ0146", "青浦区赵巷公园", "2026-02", "日", 20, 20, 0, 0, 140, 0, 0, 0, "测试"),
        att_row("KWL2699101", "陈志强", "XMSQ0146", "青浦区赵巷公园", "2026-03", "日", 21, 21, 0, 0, 147, 0, 0, 0, "测试"),
        att_row("KWL2699102", "林芳", "XMSQ0146", "青浦区赵巷公园", "2026-01", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"),
        att_row("KWL2699102", "林芳", "XMSQ0146", "青浦区赵巷公园", "2026-02", "常", 20, 20, 0, 0, 140, 0, 0, 0, "测试"),
        att_row("KWL2699102", "林芳", "XMSQ0146", "青浦区赵巷公园", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"),
        att_row("KWL2699103", "黄伟", "XMSQ0153", "青浦工业园区大楼", "2026-01", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"),
        att_row("KWL2699103", "黄伟", "XMSQ0153", "青浦工业园区大楼", "2026-02", "常", 20, 20, 0, 0, 140, 0, 0, 0, "测试"),
        att_row("KWL2699103", "黄伟", "XMSQ0153", "青浦工业园区大楼", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"),
        att_row("KWL2699104", "杨丽", "XMSQ0102", "上海市青浦区图书馆", "2026-01", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"),
        att_row("KWL2699104", "杨丽", "XMSQ0102", "上海市青浦区图书馆", "2026-02", "常", 20, 20, 0, 0, 140, 0, 0, 0, "测试"),
        att_row("KWL2699104", "杨丽", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "常", 21, 21, 0, 0, 147, 0, 0, 0, "测试"),
    ]
    _make_test_file(
        "考勤与人员财务_测试数据.xlsx", "考勤与人员财务",
        att_headers, att_widths, att_rows,
        ["18 条记录覆盖 2026-01~04，含跨项目流动（董一青）；每日状态为简化测试值。"],
    )

    def shift_row(emp_id, name, proj_code, proj_name, ym, code, days):
        d = [code if i <= days else "OFF" for i in range(1, 32)]
        return [name, emp_id, proj_code, proj_name, ym, *d, "测试"]

    _make_test_file(
        "排班明细_测试数据.xlsx", "排班明细",
        ["姓名*", "工号*", "项目编号*", "项目名称（选填）", "年月（YYYY-MM）*", *days, "备注"],
        [14, 16, 20, 26, 16, *dw, 20],
        [
            shift_row("KWL2306998", "陆雷雷", "XMSQ0146", "青浦区赵巷公园", "2026-01", "A003", 22),
            shift_row("KWL2307024", "董一青", "XMSQ0130", "青浦区赵巷镇政府", "2026-01", "SQ-C13", 21),
            shift_row("KWL2307024", "董一青", "XMSQ0130", "青浦区赵巷镇政府", "2026-02", "SQ-C13", 20),
            shift_row("KWL2307024", "董一青", "XMSQ01141", "青浦-北斗园区", "2026-03", "SQ-C13", 21),
            shift_row("KWL2309539", "徐娥英", "XMSQ0131", "青浦区青东办", "2026-01", "A003", 21),
            shift_row("KWL2699001", "测试甲", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "A003", 21),
            shift_row("KWL2699002", "测试乙", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "A003", 21),
            shift_row("KWL2699005", "张三", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "A003", 21),
            shift_row("KWL2699006", "张某某", "XMSQ0102", "上海市青浦区图书馆", "2026-03", "A003", 21),
            shift_row("KWL2699101", "陈志强", "XMSQ0146", "青浦区赵巷公园", "2026-01", "A003", 21),
            shift_row("KWL2699102", "林芳", "XMSQ0146", "青浦区赵巷公园", "2026-02", "A003", 20),
            shift_row("KWL2699103", "黄伟", "XMSQ0153", "青浦工业园区大楼", "2026-03", "A003", 21),
            shift_row("KWL2699104", "杨丽", "XMSQ0102", "上海市青浦区图书馆", "2026-01", "SQ-C13", 21),
        ],
        ["排班测试行，班次代码常用值见模板说明。"],
    )

    _make_test_file(
        "岗位编制_测试数据.xlsx", "岗位编制",
        ["项目编号*", "项目名称（选填）", "岗位", "预算编制", "项目编制", "标准工时人数", "在岗人数", "备注"],
        [20, 26, 20, 10, 10, 14, 12, 20],
        [
            ["XMSQ0102", "上海市青浦区图书馆", "保安_领班", 1, 1, 1, 1, "测试"],
            ["XMSQ0102", "上海市青浦区图书馆", "保洁_主管", 2, 2, 2, 2, "测试"],
            ["XMSQ0102", "上海市青浦区图书馆", "会务_接待", 1, 1, 1, 1, "测试"],
            ["XMSQ0146", "青浦区赵巷公园", "保安_领班", 1, 1, 1, 1, "测试"],
            ["XMSQ9031", "上海市青浦区档案馆", "保安_领班", 1, 1, 1, 1, "测试"],
            ["XMSQ9032", "青浦区民防城运中心", "保洁_主管", 2, 2, 2, 2, "测试"],
            ["XMSQ9033", "上海大众工业学校", "保安_主管", 2, 2, 2, 2, "测试"],
            ["XMSQ0153", "青浦工业园区大楼", "工程_强电", 3, 3, 3, 2, "测试（在岗缺编示例）"],
        ],
        ["岗位编制测试行。"],
    )

    _make_test_file(
        "设备信息_测试数据.xlsx", "设备主表",
        ["项目编号*", "项目名称（选填）", "设备编号*", "设备类型", "名称", "规格型号参数", "制造厂商", "安装位置",
         "出厂日期", "启用日期", "入项目日期", "退项目日期", "出厂编号", "完好状况", "原值（元）",
         "设备责任人工号*", "设备责任人（选填）", "备注"],
        [20, 26, 20, 12, 16, 26, 24, 22, 14, 14, 14, 14, 16, 14, 14, 16, 14, 20],
        [
            ["XMSQ0153", "青浦工业园区大楼", "XF-PLB-01-B1-01", "消防", "喷淋泵", "200T/H 110M 55KW", "上海熊猫",
             "主楼地下水泵房", "2004-12", "2005-09", "2005-09", "", "10379", "使用正常", "",
             "KWL2699013", "设备员甲", "真实台账"],
            ["XMSQ0153", "青浦工业园区大楼", "XF-XFB-01-B1-01", "消防", "消防泵", "200T/H 110M 55KW", "上海熊猫",
             "主楼地下水泵房", "2004-12", "2005-09", "2005-09", "", "10478", "使用正常", "",
             "KWL2699013", "设备员甲", "真实台账"],
            ["XMSQ0153", "青浦工业园区大楼", "DT-KT-01-01", "电梯", "客梯", "OTIS3200 17站17门 1000KG", "中澳集团",
             "主楼中间", "2006-08", "", "2006-09", "", "07NA5711", "使用正常", "",
             "KWL2699013", "设备员甲", "真实台账"],
            ["XMSQ0153", "青浦工业园区大楼", "KT-LSJZ-01-B1-01", "空调", "冷水机组", "30HXC350A", "开利",
             "主楼地下室", "2005-03", "2005-09", "2005-09", "", "H350E1240", "使用正常", "",
             "KWL2699013", "设备员甲", "真实台账"],
            ["XMSQ0176", "上海市环境监测中心", "KT-DJ-01-13-1", "空调", "大金空调", "RUXYQ16AB", "上海大金",
             "1号13楼楼顶", "2013-07", "2014-03", "2014-03", "", "", "使用正常", "",
             "KWL2699013", "设备员甲", "真实台账"],
            ["XMSQ0176", "上海市环境监测中心", "DT-KT-01-01-01", "电梯", "电梯", "HGP 13层", "日立电梯",
             "行政楼", "2010-12", "2014-06", "2014-06", "", "09G035218", "使用正常", "",
             "KWL2699013", "设备员甲", "真实台账"],
            ["XMSQ0153", "青浦工业园区大楼", "GPS-RSB-01-B1-01", "给排水", "热水泵", "6T/H 40M 1.1KW", "凯利",
             "主楼地下泵房", "2004-05", "2005-09", "2005-09", "", "10801", "使用正常", "",
             "KWL2699013", "设备员甲", "真实台账"],
            ["XMSQ0176", "上海市环境监测中心", "XF-XFB-1-B1F-01", "消防", "消防泵", "XBD8/40-125L", "上海双解",
             "地下水泵房", "2013-07", "2014-06", "2014-06", "", "1307131", "使用正常", "",
             "KWL2699013", "设备员甲", "真实台账"],
        ],
        ["设备为真实台账片段（工业园区/环境监测中心）。"],
    )

    _make_test_file(
        "采购与维保_测试数据.xlsx", "采购与维保",
        ["记录类型*", "项目编号*", "项目名称（选填）", "供应商名称", "供应商编号（选填）", "类别", "名称/合同名称", "数量", "单位",
         "单价（元）", "金额（元）", "合同年限", "月份/日期", "当前状态", "经办人工号*", "经办人（选填）", "备注"],
        [14, 20, 26, 24, 14, 16, 24, 10, 8, 12, 14, 10, 14, 12, 16, 14, 20],
        [
            ["供应商", "", "", "上海熊猫", "SUP0001", "设备厂家", "", "", "", "", "", "", "", "",
             "KWL2699011", "采购员甲", "测试"],
            ["供应商", "", "", "上海大金", "SUP0002", "设备厂家", "", "", "", "", "", "", "", "",
             "KWL2699011", "采购员甲", "测试"],
            ["供应商", "", "", "开利", "SUP0003", "设备厂家", "", "", "", "", "", "", "", "",
             "KWL2699011", "采购员甲", "测试"],
            ["供应商", "", "", "日立电梯", "SUP0004", "维保单位", "", "", "", "", "", "", "", "",
             "KWL2699011", "采购员甲", "测试"],
            ["供应商", "", "", "洁力清洁用品", "SUP0005", "耗材供应商", "", "", "", "", "", "", "", "",
             "KWL2699011", "采购员甲", "测试"],
            ["维保合同", "XMSQ0153", "青浦工业园区大楼", "上海熊猫", "SUP0001", "电梯维保", "电梯维保合同", "", "", "",
             125000, 3, "2026-01", "", "KWL2699011", "采购员甲", "测试"],
            ["维保合同", "XMSQ0176", "上海市环境监测中心", "上海大金", "SUP0002", "空调维保", "空调维保合同", "", "", "",
             83000, 2, "2026-01", "", "KWL2699011", "采购员甲", "测试"],
            ["耗材采购", "XMSQ0102", "上海市青浦区图书馆", "洁力清洁用品", "SUP0005", "保洁耗材", "垃圾袋", 500, "卷", 2.5,
             1250, "", "2026-02", "", "KWL2699011", "采购员甲", "测试"],
            ["耗材采购", "XMSQ0153", "青浦工业园区大楼", "洁力清洁用品", "SUP0005", "工程耗材", "灯泡", 100, "个", 8,
             800, "", "2026-02", "", "KWL2699011", "采购员甲", "测试"],
            ["固定资产", "XMSQ0102", "上海市青浦区图书馆", "", "", "低值易耗", "塑封机", 1, "台", "", 159,
             "", "2025-02-05", "在用", "KWL2699011", "采购员甲", "测试"],
            ["固定资产", "XMSQ0102", "上海市青浦区图书馆", "", "", "低值易耗", "一米栏", 30, "根", "", 4200,
             "", "2025-03-12", "在用", "KWL2699011", "采购员甲", "测试"],
            ["维保合同", "XMSQ9031", "上海市青浦区档案馆", "开利", "SUP0003", "消防维保", "消防维保合同", "", "", "",
             60000, 2, "2026-01", "", "KWL2699011", "采购员甲", "测试"],
            ["耗材采购", "XMSQ9033", "上海大众工业学校", "洁力清洁用品", "SUP0005", "办公耗材", "打印纸", 50, "箱", 120,
             6000, "", "2026-03", "", "KWL2699011", "采购员甲", "测试"],
            ["固定资产", "XMSQ0153", "青浦工业园区大楼", "", "", "办公设备", "打印机", 2, "台", "", 3000,
             "", "2025-08-01", "在用", "KWL2699011", "采购员甲", "测试"],
        ],
        ["一张表四类记录（供应商/维保合同/耗材采购/固定资产），按记录类型填对应列。"],
    )

    _make_test_file(
        "检查记录_测试数据.xlsx", "检查记录",
        ["项目编号*", "项目名称（选填）", "检查类型", "检查日期", "检查得分（0-100）", "问题描述", "整改情况",
         "整改完成日期", "检查人工号*", "检查人（选填）", "备注"],
        [20, 26, 14, 14, 16, 40, 30, 14, 16, 12, 20],
        [
            ["XMSQ0102", "上海市青浦区图书馆", "品质检查", "2026-03-15", 92, "大厅地面清洁不及时", "已整改",
             "2026-03-20", "KWL2699005", "张三", "测试"],
            ["XMSQ0108", "国家税务总局上海市青浦区税务局", "安全检查", "2026-02-10", 88, "消防通道堆放杂物", "已清理",
             "2026-02-12", "KWL2699202", "李四", "测试"],
            ["XMSQ0176", "上海市环境监测中心", "消防检查", "2026-01-20", 95, "", "", "", "KWL2699203", "王五", "测试"],
            ["XMSQ9031", "上海市青浦区档案馆", "安全检查", "2026-02-18", 90, "档案库房灭火器压力不足", "已更换",
             "2026-02-20", "KWL2699005", "张三", "测试"],
            ["XMSQ9033", "上海大众工业学校", "消防检查", "2026-01-25", 93, "教学楼消防通道堆放杂物", "已清理",
             "2026-01-27", "KWL2699202", "李四", "测试"],
            ["XMSQ0146", "青浦区赵巷公园", "品质检查", "2026-04-10", 91, "公厕异味", "已处理",
             "2026-04-12", "KWL2699203", "王五", "测试"],
        ],
        ["测试品质/安全/消防检查记录。"],
    )

    _make_test_file(
        "片区信息_测试数据.xlsx", "片区主表",
        ["片区编号*", "片区名称*", "片区负责人工号*", "片区负责人（选填）", "备注"],
        [16, 20, 16, 14, 20],
        [
            ["PQ-001", "青东片区", "KWL2699022", "李片区", "测试"],
            ["PQ-002", "青中1区", "KWL2699020", "王片区", "测试"],
            ["PQ-003", "青中2区", "KWL2699020", "王片区", "测试"],
            ["PQ-004", "中心区域", "KWL2699020", "王片区", "测试"],
            ["PQ-005", "嘉定区域", "KWL2699020", "王片区", "测试"],
            ["PQ-006", "松江区域", "KWL2699020", "王片区", "测试"],
        ],
        ["片区主数据测试行，片区负责人为人员节点关联。"],
    )

    _make_test_file(
        "项目片区关系_测试数据.xlsx", "项目片区关系",
        ["项目编号*", "项目名称（选填）", "片区编号*", "片区名称（选填）", "备注"],
        [20, 26, 16, 20, 20],
        [
            ["XMSQ0102", "上海市青浦区图书馆", "PQ-003", "青中2区", "测试"],
            ["XMSQ0108", "国家税务总局上海市青浦区税务局", "PQ-002", "青中1区", "测试"],
            ["XMSQ-5-1", "税务局本部", "PQ-002", "青中1区", "测试"],
            ["XMSQ-5-2", "第十税务所", "PQ-001", "青东片区", "测试"],
            ["XMSQ-5-2", "第十税务所", "PQ-002", "青中1区", "测试（一个项目多片区示例）"],
            ["XMSQ0146", "青浦区赵巷公园", "PQ-001", "青东片区", "测试"],
            ["XMSQ0130", "青浦区赵巷镇政府", "PQ-001", "青东片区", "测试"],
            ["XMSQ01141", "青浦-北斗园区", "PQ-001", "青东片区", "测试"],
            ["XMSQ0131", "青浦区青东办", "PQ-001", "青东片区", "测试"],
            ["XMSQ0153", "青浦工业园区大楼", "PQ-002", "青中1区", "测试"],
            ["XMSQ0176", "上海市环境监测中心", "PQ-004", "中心区域", "测试"],
            ["XMSQ9031", "上海市青浦区档案馆", "PQ-002", "青中1区", "测试"],
            ["XMSQ9032", "青浦区民防城运中心", "PQ-001", "青东片区", "测试"],
            ["XMSQ9033", "上海大众工业学校", "PQ-005", "嘉定区域", "测试"],
            ["XMSQ9034", "上海市徐汇区群众艺术馆", "PQ-004", "中心区域", "测试"],
            ["XMSQ9035", "上海市松江区公安局", "PQ-006", "松江区域", "测试"],
        ],
        ["项目-片区 M:N 关系测试行（第十税务所属于两个片区作示例）。"],
    )

    _make_test_file(
        "人员证书_测试数据.xlsx", "人员证书",
        ["工号*", "姓名（选填）", "专业类别", "证书类别", "证书名称*", "备注"],
        [16, 14, 14, 14, 32, 20],
        [
            ["KWL2306998", "陆雷雷", "安防", "从业资格证", "保安员（三级）", "测试"],
            ["KWL2307010", "王惠云", "安防", "从业资格证", "保安员（五级）", "测试"],
            ["KWL2699103", "黄伟", "工程", "从业资格证", "低压电工作业", "测试"],
            ["KWL2699003", "测试丙", "工程", "从业资格证", "高压电工作业", "测试"],
            ["KWL2699003", "测试丙", "工程", "其他", "电梯维修证", "测试"],
            ["KWL2307024", "董一青", "综合管理", "职称证", "物业管理师", "测试"],
            ["KWL2699005", "张三", "综合管理", "从业资格证", "上物协物业经理证", "测试"],
            ["KWL2699005", "张三", "综合管理", "职称证", "物业管理师", "测试"],
            ["KWL2699104", "杨丽", "综合管理", "其他", "会务服务证", "测试"],
        ],
        ["人员证书测试行，覆盖安防/工程/综合管理及多证书人员（测试丙、张三）。"],
    )

    print("测试数据已生成（与模板一一对应）：")
    for f in sorted(OUT_DIR.glob("*.xlsx")):
        print(f"  {f.name}")


if __name__ == "__main__":
    main()