AiDOPWarehouse/doc/generate_uat_report_all.py

"""
生成 S1-S8 综合 UAT 测试报告
"""
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.utils import get_column_letter

wb = Workbook()

# ── 样式 ──
thin = Border(left=Side('thin'), right=Side('thin'), top=Side('thin'), bottom=Side('thin'))
hf = Font(name='微软雅黑', size=11, bold=True)
tf = Font(name='微软雅黑', size=14, bold=True)
nf = Font(name='微软雅黑', size=10)
sf = Font(name='微软雅黑', size=9)
ca = Alignment(horizontal='center', vertical='center', wrap_text=True)
la = Alignment(horizontal='left', vertical='center', wrap_text=True)
hfill = PatternFill(start_color='D9E1F2', end_color='D9E1F2', fill_type='solid')
lfill = PatternFill(start_color='F2F2F2', end_color='F2F2F2', fill_type='solid')
yfill = PatternFill(start_color='FFF2CC', end_color='FFF2CC', fill_type='solid')

def wm(ws, r, cs, ce, v, font=nf, align=ca, fill=None):
    if cs != ce:
        ws.merge_cells(start_row=r, start_column=cs, end_row=r, end_column=ce)
    c = ws.cell(row=r, column=cs, value=v)
    c.font = font; c.alignment = align
    if fill: c.fill = fill
    for x in range(cs, ce+1):
        ws.cell(row=r, column=x).border = thin
        if fill: ws.cell(row=r, column=x).fill = fill

def ab(ws, r, cs, ce):
    for x in range(cs, ce+1): ws.cell(row=r, column=x).border = thin

# ── 场景定义: (用例编号, 测试内容, 测试步骤, 系统功能, 预期结果) ──
modules = [
  ("S1产销协同", [
    ("产品设计", [
      ("产品设计列表查询", "1.进入产品设计菜单\n2.输入产品编码/名称查询\n3.点击重置", "列表查询", "可按编码、名称筛选，显示单据号、产品信息、状态"),
      ("新建产品设计-BOM自动填充", "1.点击新建\n2.选择产品编码\n3.观察BOM和工艺子表", "自动填充", "选择产品后BOM子表自动填入物料清单，工艺子表自动填入工序"),
      ("产品设计保存与编辑", "1.填写完整信息后保存\n2.选择记录点击编辑\n3.修改后保存", "增删改", "保存后列表刷新；编辑后字段正确更新；删除后记录移除"),
    ]),
    ("合同评审", [
      ("合同评审列表查询", "1.进入合同评审菜单\n2.输入合同号/客户查询", "列表查询", "可按合同号、客户、日期筛选，显示状态、创建时间等"),
      ("合同评审新增与提交审批", "1.点击新增\n2.填写合同信息\n3.保存后提交审批", "新增+审批", "保存成功；提交后状态变为审批中，审批流自动创建"),
      ("合同评审编辑与删除", "1.选择记录编辑修改后保存\n2.选择未审批记录删除", "编辑/删除", "编辑后字段正确更新；已审批记录不可删除"),
    ]),
    ("订单评审", [
      ("订单评审列表查询", "1.进入订单评审\n2.输入订单号/客户查询", "列表查询", "可按订单号、客户、状态筛选，显示审批进度标签"),
      ("订单评审新增与变更", "1.新增订单评审并保存\n2.对已审批订单发起变更\n3.修改内容后保存", "新增+变更", "保存成功；变更后变更次数+1，审批流重新发起"),
      ("订单评审-交期确认", "1.选择审批完成的订单\n2.点击交期确认\n3.确认操作", "交期确认", "确认后订单状态更新"),
    ]),
    ("订单交付与发货", [
      ("订单交付列表查询", "1.进入订单交付菜单\n2.按状态筛选", "列表查询", "显示交付状态（待交付/部分/已交付），筛选条件生效"),
      ("订单发货新增", "1.点击新增\n2.选择销售单号\n3.填写发货信息\n4.保存", "新增发货", "销售单号下拉搜索回显正确；保存后发货单号自动生成"),
      ("发货单编辑与删除", "1.编辑修改后保存\n2.删除未发货单据", "编辑/删除", "编辑后字段更新；已发货单据不可删除"),
    ]),
    ("工单下达与计划联动", [
      ("工单下达操作", "1.勾选待下达工单\n2.点击下达\n3.确认", "工单下达", "下达后状态变更，不可重复下达，MES可见"),
      ("计划联动看板查询", "1.进入计划联动看板\n2.按计划编号查询", "列表查询", "显示联动计划列表，来源和目标关系正确"),
    ]),
  ]),
  ("S2制造协同", [
    ("生产排程", [
      ("排程列表查询", "1.进入生产排程菜单\n2.按工单号/产线/日期查询", "列表查询", "可按工单号、产线、日期筛选，显示排产状态"),
      ("执行排产操作", "1.选择待排产工单\n2.点击排产按钮\n3.确认", "排产执行", "排产完成后工单状态更新，排产结果写入"),
      ("订单生产排程看板", "1.进入订单排程看板\n2.查看甘特图展示", "甘特图", "按工单维度显示甘特图，包含时间轴、产线、状态"),
      ("产线生产排程看板", "1.进入产线排程看板\n2.查看甘特图展示", "甘特图", "按产线维度显示甘特图，设备占用信息正确"),
    ]),
    ("可执行日计划", [
      ("可执行日计划查询", "1.进入可执行日计划\n2.按日期/产线筛选", "列表查询", "显示当日可执行工单，状态过滤正确"),
      ("日计划状态操作", "1.选择日计划记录\n2.执行状态变更操作", "状态管理", "状态正确流转，不可跳过中间状态"),
    ]),
    ("排产基础配置", [
      ("产线工作日历管理", "1.进入工作日历\n2.查看/编辑日历", "日历管理", "显示各产线工作日历，编辑后保存正确"),
      ("产线节假日管理", "1.进入节假日管理\n2.新增/编辑节假日", "节假日", "节假日设置正确，排产时自动跳过"),
    ]),
    ("工单执行看板", [
      ("工单执行进度看板", "1.进入工单进度看板\n2.观察进度数据", "进度看板", "显示各工单完成进度百分比，数据实时更新"),
      ("工单执行追踪", "1.进入工单追踪\n2.查看工单物料/工序明细", "工单追踪", "显示工单物料明细和工序明细，数据关联正确"),
    ]),
  ]),
  ("S3供应协同", [
    ("物料需求与采购", [
      ("物料需求计划查询", "1.进入物料需求计划\n2.按物料/日期查询", "列表查询", "显示物料需求清单，需求量、在途量、缺口量正确"),
      ("物料采购申请新增", "1.点击新增采购申请\n2.选择物料\n3.填写数量/交期\n4.保存", "新增申请", "保存成功，申请编号自动生成，审批流发起"),
      ("物料采购订单查询", "1.进入采购订单\n2.按订单号/供应商查询", "列表查询", "显示采购订单列表，状态、供应商信息正确"),
    ]),
    ("要货令与交货", [
      ("要货令新增", "1.点击新增要货令\n2.选择采购订单\n3.填写要求信息\n4.保存", "新增要货令", "保存成功，关联采购订单正确"),
      ("物料交货计划查询", "1.进入交货计划\n2.按供应商/物料查询", "列表查询", "显示交货计划列表，计划/实际数量对比"),
      ("交货单异常记录", "1.进入交货异常\n2.查看异常记录", "异常记录", "显示交货延迟、数量偏差等异常记录"),
    ]),
    ("委外加工", [
      ("委外加工订单管理", "1.进入委外订单\n2.新增/编辑委外订单", "委外管理", "保存成功，BOM自动填充，状态流转正确"),
      ("工序外协订单管理", "1.进入工序外协\n2.新增/编辑外协订单", "外协管理", "保存成功，工序信息关联正确"),
    ]),
    ("看板与齐套", [
      ("供应协同看板", "1.进入S3看板\n2.查看指标卡片", "看板展示", "显示采购相关KPI指标"),
      ("齐套看板", "1.进入齐套看板\n2.查看物料齐套状态", "齐套展示", "显示工单物料齐套率，缺料项高亮"),
    ]),
  ]),
  ("S4采购执行", [
    ("供应商交货管理", [
      ("供应商交货列表查询", "1.进入供应商交货管理\n2.按供应商/物料查询", "列表查询", "显示交货计划列表，支持发布操作"),
      ("供应商交期回复", "1.查看供应商回复的交期\n2.确认交期", "交期回复", "供应商回复后数据正确显示，确认操作生效"),
    ]),
    ("供应商发货单", [
      ("供应商发货单新增", "1.点击新增\n2.选择供应商\n3.填写发货信息\n4.保存", "新增发货", "保存成功，编号自动生成，物料明细正确"),
      ("发货单编辑与删除", "1.编辑修改后保存\n2.删除草稿状态单据", "编辑/删除", "编辑后字段更新；已提交单据不可删除"),
      ("发货单打印标签", "1.选择发货单\n2.点击打印标签", "标签打印", "标签数据正确生成，包含物料/数量/批次信息"),
    ]),
    ("采购退货与IQC", [
      ("采购退货单管理", "1.新增退货单\n2.选择收货单\n3.填写退货信息\n4.保存", "退货管理", "保存成功，关联收货单正确，退货数量校验通过"),
      ("IQC退货查询", "1.进入IQC退货\n2.按检验单/物料查询", "IQC查询", "显示IQC检验记录，不合格判定数据正确"),
    ]),
    ("供应商欠料看板", [
      ("欠料看板列表查询", "1.进入欠料看板\n2.按物料/供应商查询", "列表查询", "显示D0-D14欠料明细，供应商/物料信息正确"),
      ("欠料看板刷新", "1.点击刷新按钮\n2.观察数据更新", "数据刷新", "刷新后数据从WorkOrdDetailTotalKB重新加载"),
      ("生成催料待办", "1.点击生成催料待办按钮\n2.观察提示", "生成待办", "创建快照记录并自动发起审批流，提示信息正确"),
      ("审批中心查看欠料信息", "1.进入审批中心\n2.找到催料待办\n3.点击查看欠料信息", "欠料快照", "弹窗显示待办基本信息和欠料列表快照表格"),
      ("催料待办审批", "1.在待办中点击同意/拒绝\n2.填写意见\n3.确认", "审批操作", "审批完成后状态变更，从待办移至已办"),
    ]),
  ]),
  ("S5物料仓储", [
    ("来料检验", [
      ("IQC检验任务列表", "1.进入IQC任务列表\n2.查看待检任务", "任务列表", "显示待检来料任务，物料/供应商/数量信息正确"),
      ("IQC检验录入", "1.选择检验任务\n2.录入检验结果\n3.提交", "检验录入", "检验数据保存正确，判定结果自动生成"),
      ("IQC检验结果查询", "1.进入检验结果列表\n2.按物料/日期查询", "结果查询", "显示历史检验记录，合格率统计正确"),
    ]),
    ("仓库单据", [
      ("采购收货单管理", "1.进入采购收货\n2.新增/编辑收货单", "收货管理", "保存成功，关联采购订单，数量校验通过"),
      ("生产领料单管理", "1.进入生产领料\n2.新增领料单\n3.填写物料明细", "领料管理", "保存成功，关联工单，库存扣减正确"),
      ("生产退料单管理", "1.进入生产退料\n2.新增退料单", "退料管理", "保存成功，退料数量不超过已领数量"),
      ("生产入库单管理", "1.进入生产入库\n2.新增入库单", "入库管理", "保存成功，库存增加，批次号正确"),
    ]),
    ("库存查询", [
      ("库存查询", "1.进入库存查询\n2.按物料/库位查询", "库存查询", "显示实时库存数量，支持多维度筛选"),
      ("进出存查询", "1.进入进出存查询\n2.按日期范围查询", "进出存", "显示进/出/存三期数据，合计正确"),
      ("暂收在检列表", "1.进入暂收在检\n2.查看在检物料", "暂收在检", "显示IQC检验中的物料列表"),
    ]),
    ("盘点管理", [
      ("盘点标签确认", "1.进入盘点标签确认\n2.确认/驳回标签", "标签确认", "确认后标签状态变更，数据正确"),
      ("盘点结果查询", "1.进入盘点结果\n2.查看盈亏情况", "盘点结果", "显示盘点盈亏数据，差异分析正确"),
    ]),
  ]),
  ("S6生产执行", [
    ("生产指令与质检", [
      ("生产指令单查询", "1.进入生产指令单\n2.按工单号/日期查询", "指令单", "显示生产指令列表，工单/产线/数量信息正确"),
      ("过程检验单管理", "1.进入过程检验\n2.查看/录入检验数据", "过程检验", "检验数据保存正确，IPQC流程完整"),
    ]),
    ("设备与模工具", [
      ("生产设备台账", "1.进入设备台账\n2.查看设备列表\n3.新增/编辑设备", "设备管理", "设备信息完整（编号、名称、状态、产线）"),
      ("模工具台账管理", "1.进入模工具台账\n2.查看/管理模工具", "模工具", "模工具信息完整，状态流转正确"),
    ]),
    ("生产执行看板", [
      ("工单执行看板", "1.进入工单执行看板\n2.查看执行状态", "执行看板", "显示各工单实时执行进度"),
      ("车间效率看板", "1.进入效率看板\n2.查看效率指标", "效率看板", "显示产线效率、设备利用率等指标"),
    ]),
  ]),
  ("S7成品仓储", [
    ("FQC检验", [
      ("FQC检验任务列表", "1.进入FQC任务列表\n2.查看待检任务", "任务列表", "显示FQC待检任务，产品信息正确"),
      ("FQC检验录入", "1.选择检验任务\n2.录入检验结果\n3.提交审核", "检验录入", "检验数据保存正确，审批流触发"),
      ("FQC检验结果查询", "1.进入检验结果\n2.按产品/日期查询", "结果查询", "显示历史FQC检验记录"),
      ("成品报检", "1.进入成品报检\n2.新增报检申请", "成品报检", "保存成功，自动生成检验任务"),
    ]),
    ("成品入库与发货", [
      ("生产入库单管理", "1.进入生产入库\n2.新增入库单\n3.填写入库信息", "入库管理", "保存成功，库存增加，批次追溯正确"),
      ("销售发货通知", "1.进入发货通知\n2.查看/处理发货通知", "发货通知", "显示待发货通知列表，处理流程正确"),
    ]),
    ("成品仓储看板", [
      ("发货任务看板", "1.进入发货任务看板\n2.查看任务状态", "发货看板", "显示待发货/已发货任务，状态正确"),
      ("成品滞销看板", "1.进入滞销看板\n2.查看滞销产品", "滞销看板", "显示库龄超标的成品列表"),
    ]),
  ]),
  ("S8异常监控", [
    ("异常看板与大屏", [
      ("异常监控看板", "1.进入S8看板\n2.查看异常统计", "异常看板", "显示异常数量、分类、趋势统计"),
      ("交付异常大屏", "1.进入交付异常大屏\n2.查看异常数据", "交付大屏", "显示交付延迟、偏差等异常指标"),
      ("生产异常大屏", "1.进入生产异常大屏\n2.查看异常数据", "生产大屏", "显示排产异常、设备故障等指标"),
      ("供应异常大屏", "1.进入供应异常大屏\n2.查看异常数据", "供应大屏", "显示供应延迟、欠料等异常指标"),
    ]),
    ("异常管理", [
      ("异常列表查询", "1.进入异常列表\n2.按类型/日期查询", "异常列表", "显示异常记录列表，分类/状态/影响范围正确"),
      ("主动提报异常", "1.点击主动提报\n2.填写异常信息\n3.提交", "主动提报", "保存成功，异常自动分配处理人"),
      ("异常任务处理", "1.在待办中打开异常任务\n2.填写处理方案\n3.提交", "任务处理", "处理后任务状态更新，处理记录保存"),
    ]),
    ("订单追踪", [
      ("订单执行档案", "1.进入订单执行档案\n2.搜索订单号\n3.查看执行详情", "订单档案", "显示订单全生命周期执行数据"),
      ("订单链路全景", "1.进入订单链路\n2.查看全链路\n3.点击节点查看详情", "链路全景", "显示订单从评审→排产→采购→交付的全链路视图"),
    ]),
    ("配置管理", [
      ("场景配置管理", "1.进入场景配置\n2.新增/编辑场景\n3.保存", "场景配置", "保存成功，场景规则生效"),
      ("报警规则配置", "1.进入报警规则\n2.配置阈值和通知\n3.保存", "报警规则", "保存成功，规则触发时自动生成异常"),
    ]),
  ]),
]

# ═══════════════════════════════════════════
# Sheet 0: 目录
# ═══════════════════════════════════════════
ws_dir = wb.active
ws_dir.title = "目录"
for i, w in enumerate([6, 20, 20, 18, 18, 18], 1):
    ws_dir.column_dimensions[get_column_letter(i)].width = w

row = 1
wm(ws_dir, row, 1, 6, "AiDOP系统 UAT 测试报告", tf, ca)
row += 1
wm(ws_dir, row, 1, 6, "S1-S8 全模块综合测试", hf, ca)
row += 2

dh = ["序号", "模块名称", "测试场景数", "用例总数", "计划完成时间", "责任人"]
for ci, h in enumerate(dh, 1):
    ws_dir.cell(row=row, column=ci, value=h).font = hf
    ws_dir.cell(row=row, column=ci).alignment = ca
    ws_dir.cell(row=row, column=ci).fill = hfill
    ab(ws_dir, row, ci, ci)
row += 1

for idx, (mname, cats) in enumerate(modules, 1):
    total_cases = sum(len(cs) for _, cs in cats)
    wm(ws_dir, row, 1, 1, str(idx), nf, ca)
    wm(ws_dir, row, 2, 2, mname, nf, ca)
    wm(ws_dir, row, 3, 3, str(len(cats)), nf, ca)
    wm(ws_dir, row, 4, 4, str(total_cases), nf, ca)
    wm(ws_dir, row, 5, 5, "", nf, ca)
    wm(ws_dir, row, 6, 6, "", nf, ca)
    row += 1

# 汇总
total_scenarios = sum(len(cats) for _, cats in modules)
total_cases_all = sum(sum(len(cs) for _, cs in cats) for _, cats in modules)
row += 1
wm(ws_dir, row, 1, 2, "合计", hf, ca, yfill)
wm(ws_dir, row, 3, 3, str(total_scenarios), hf, ca, yfill)
wm(ws_dir, row, 4, 4, str(total_cases_all), hf, ca, yfill)
wm(ws_dir, row, 5, 6, "", nf, ca, yfill)

row += 2
info = [
    "图例说明：",
    "Y : 实际结果与预期结果一致",
    "H : 严重缺陷（系统崩溃/数据错误影响其他流程）",
    "M : 中等缺陷（功能可用但有错误/不影响其他流程）",
    "L : 低程度缺陷（界面/提示/帮助文档问题）",
    "- : 不属于本次测试范围",
]
for line in info:
    wm(ws_dir, row, 1, 6, line, sf, la)
    row += 1

row += 1
for line in ["测试账号：", "Web地址：", "账号：        密码：", "库存组织："]:
    wm(ws_dir, row, 1, 6, line, sf, la)
    row += 1

row += 1
wm(ws_dir, row, 1, 3, "测试人姓名：", sf, ca)
wm(ws_dir, row, 4, 6, "负责顾问：", sf, ca)

# ═══════════════════════════════════════════
# 各模块 Sheet
# ═══════════════════════════════════════════
for midx, (mname, cats) in enumerate(modules, 1):
    ws = wb.create_sheet(title=f"{midx} {mname}")
    widths = [8, 8, 20, 20, 20, 20, 14, 14, 24, 10, 10, 12]
    for i, w in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(i)].width = w

    row = 1
    # 标题行
    wm(ws, row, 1, 8, "详细测试结果", hf, ca, hfill)
    wm(ws, row, 9, 9, "第一轮测试", hf, ca, hfill)
    wm(ws, row, 10, 11, "第二轮测试", hf, ca, hfill)
    wm(ws, row, 12, 12, "测试内容", hf, ca, hfill)
    row += 1

    # 计数行
    wm(ws, row, 1, 8, "计数", nf, ca, lfill)
    wm(ws, row, 9, 9, "第一轮", nf, ca, lfill)
    wm(ws, row, 10, 11, "第二轮", nf, ca, lfill)
    total_c = sum(len(cs) for _, cs in cats)
    wm(ws, row, 12, 12, f"{mname}({total_c}例)", nf, ca, yfill)
    row += 1

    # 测试人/日期
    wm(ws, row, 1, 8, "测试人", nf, ca)
    for cs2, ce2 in [(9,9),(10,11),(12,12)]:
        wm(ws, row, cs2, ce2, "", nf, ca)
    row += 1
    wm(ws, row, 1, 8, "测试日期", nf, ca)
    for cs2, ce2 in [(9,9),(10,11),(12,12)]:
        wm(ws, row, cs2, ce2, "", nf, ca)
    row += 1

    # 缺陷统计行
    for dlbl, dval in [("缺陷","高(H)"),("缺陷","中(M)"),("缺陷","低(L)")]:
        wm(ws, row, 1, 8, dlbl, nf, ca)
        wm(ws, row, 9, 9, dval, nf, ca)
        wm(ws, row, 10, 11, dval, nf, ca)
        wm(ws, row, 12, 12, "", nf, ca)
        row += 1

    # 空行
    wm(ws, row, 1, 12, "", nf, ca)
    row += 1

    # 表头行
    hdr = [(1,1,"用例编号"),(2,3,"测试内容"),(4,6,"测试步骤"),(7,8,"系统功能"),
           (9,9,"预期结果"),(10,10,"测试结果\n1st"),(11,11,"测试结果\n2nd"),(12,12,"角色")]
    for cs2, ce2, htxt in hdr:
        wm(ws, row, cs2, ce2, htxt, hf, ca, hfill)
    row += 1

    # 用例数据
    case_seq = 1
    for cname, cases in cats:
        # 分类标题行
        wm(ws, row, 1, 12, f"▸ {cname}", hf, la, lfill)
        row += 1

        for content, steps, func, expected in cases:
            cid = f"{midx}.{case_seq}"
            wm(ws, row, 1, 1, cid, nf, ca)
            wm(ws, row, 2, 3, content, nf, la)
            wm(ws, row, 4, 6, steps, nf, la)
            wm(ws, row, 7, 8, func, nf, ca)
            wm(ws, row, 9, 9, expected, nf, la)
            ws.cell(row=row, column=10, value="Y").font = nf
            ws.cell(row=row, column=10).alignment = ca
            ws.cell(row=row, column=10).border = thin
            ws.cell(row=row, column=11).font = nf
            ws.cell(row=row, column=11).alignment = ca
            ws.cell(row=row, column=11).border = thin
            wm(ws, row, 12, 12, "操作员", nf, ca)
            ws.row_dimensions[row].height = max(40, steps.count('\n') * 14 + 28)
            row += 1
            case_seq += 1

    # 图例行
    row += 1
    for line in ["Y : 一致", "H : 严重缺陷", "M : 中等缺陷", "L : 低程度缺陷", "- : 不在范围"]:
        wm(ws, row, 1, 8, line, sf, la)
        row += 1

# ── 保存 ──
out = r"d:\DEMONET\doc\UAT测试报告.xlsx"
wb.save(out)
print(f"✅ 已生成: {out}  ({total_scenarios}场景 / {total_cases_all}用例)")