AiDOPWarehouse/ai-dop-platform/tools/gen_plan_v2_original_roles.py

# -*- coding: utf-8 -*-
from pathlib import Path
import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side

out = Path(r"d:/Projects/Ai-DOP/AI-DOP项目总计划_20260328_v2_按原分工.xlsx")
wb = openpyxl.Workbook()
ws1 = wb.active
ws1.title = "项目总计划"
ws2 = wb.create_sheet("每周详细计划")

header_fill = PatternFill("solid", fgColor="1F4E78")
sub_fill = PatternFill("solid", fgColor="D9E1F2")
white_font = Font(color="FFFFFF", bold=True)
bold = Font(bold=True)
center = Alignment(horizontal="center", vertical="center", wrap_text=True)
left = Alignment(horizontal="left", vertical="top", wrap_text=True)
thin = Side(style="thin", color="D9D9D9")
border = Border(left=thin, right=thin, top=thin, bottom=thin)

ws1.append(["阶段", "时间范围", "目标产出", "负责人（与原表一致）", "验收标准", "状态"])
for c in range(1, 7):
    ws1.cell(1, c).fill = header_fill
    ws1.cell(1, c).font = white_font
    ws1.cell(1, c).alignment = center
    ws1.cell(1, c).border = border

overall_rows = [
    [
        "1. 需求与立项",
        "2026/03/02 - 2026/03/16",
        "需求讨论、业务需求与IT对接、立项/规范",
        "@郭峰@卿劢@胡仲海（需求）；@卿劢@郭峰（IT对接）",
        "需求范围与接口口径确认",
        "进行中",
    ],
    [
        "2. 系统分析与设计",
        "2026/03/10 - 2026/04/10",
        "技术栈选型、源数据接口、架构/数据库、移动端设计",
        "@郭峰（技术栈/架构/库/移动）；@方英明@卿劢（标准化源数据接口）",
        "设计评审通过",
        "进行中",
    ],
    [
        "3. 研发（按模块分工）",
        "2026/03/19 - 2026/05/29",
        "环境&框架；S0/S8/S9；S1-S4；S5-S7；APP",
        "见下方「模块分工矩阵」",
        "各模块开发与KPI联调完成",
        "进行中",
    ],
    [
        "4. 测试与验收",
        "2026/06/01 - 2026/06/12",
        "SIT、UAT",
        "SIT：方英明@郭峰；UAT：@卿劢@郭峰",
        "测试问题闭环、UAT条件满足",
        "未开始",
    ],
    ["5. 交付", "2026/06/29 - 2026/06/30", "交付验收", "@郭峰", "交付清单签收", "未开始"],
]
r = 2
for row in overall_rows:
    ws1.append(row)
    for c in range(1, 7):
        ws1.cell(r, c).alignment = left if c in (3, 4, 5) else center
        ws1.cell(r, c).border = border
    r += 1

r += 1
ws1.merge_cells(start_row=r, start_column=1, end_row=r, end_column=6)
ws1.cell(r, 1).value = "模块分工矩阵（与《项目计划跟进表》一致）"
ws1.cell(r, 1).fill = sub_fill
ws1.cell(r, 1).font = bold
ws1.cell(r, 1).alignment = left
r += 1
matrix = [
    ["工作项", "负责人", "计划周期（原表）", "说明", "", ""],
    ["开发环境搭建&系统框架搭建", "@郭峰", "2026/03/19 - 2026/03/20", "", "", ""],
    ["AI智能体监控调研&ChatBI调研", "@刘文博", "2026/03/16 - 2026/03/20", "", "", ""],
    ["S0/S8/S9模块和KPI开发", "@刘文博", "2026/03/23 - 2026/04/30", "", "", ""],
    ["S1-S4模块和KPI开发", "@彭熙玉", "2026/04/01 - 2026/05/29", "4/1起投入", "", ""],
    ["S5-S7模块和KPI开发", "@方英明", "2026/05/06 - 2026/05/22", "", "", ""],
    ["APP端全功能开发", "郭峰@方英明", "2026/04/13 - 2026/05/29", "", "", ""],
    ["系统集成测试 (SIT)", "方英明@郭峰", "2026/06/01 - 2026/06/05", "", "", ""],
    ["用户验收测试 (UAT)", "@卿劢@郭峰", "2026/06/08 - 2026/06/12", "", "", ""],
]
for row in matrix:
    ws1.append(row)
    for c in range(1, 7):
        ws1.cell(r, c).border = border
        ws1.cell(r, c).alignment = left
    r += 1

r += 1
ws1.merge_cells(start_row=r, start_column=1, end_row=r, end_column=6)
ws1.cell(r, 1).value = "关键里程碑"
ws1.cell(r, 1).fill = sub_fill
ws1.cell(r, 1).font = bold
r += 1
for m in [
    ("M1", "2026/04/01", "@彭熙玉 开始 S1-S4 开发（原表计划起）"),
    ("M2", "2026/05/29", "系统完成开发（原表节点）"),
    ("M3", "2026/06/12", "系统完成测试（原表节点）"),
    ("M4", "2026/06/30", "交付验收（原表）"),
]:
    ws1.append([m[0], m[1], m[2], "", "", ""])
    for c in range(1, 7):
        ws1.cell(r, c).border = border
    r += 1

for col, width in {"A": 20, "B": 28, "C": 36, "D": 32, "E": 28, "F": 10}.items():
    ws1.column_dimensions[col].width = width

ws2.append(
    [
        "周次",
        "日期范围",
        "周目标",
        "主要工作项",
        "责任分工（原表+小刘周计划）",
        "周验收物",
        "风险与依赖",
    ]
)
for c in range(1, 8):
    ws2.cell(1, c).fill = header_fill
    ws2.cell(1, c).font = white_font
    ws2.cell(1, c).alignment = center
    ws2.cell(1, c).border = border

weeks_data = [
    (
        "W0",
        "2026/03/16 - 2026/03/20",
        "AI智能体与ChatBI调研、环境",
        "S8/S9预研、S0参数梳理、.NET联调环境",
        "@刘文博（调研主责）；@郭峰（框架与环境）",
        "调研产出、环境就绪",
        "模型与接口选型",
    ),
    (
        "W1",
        "2026/03/23 - 2026/03/28",
        "S8 监控能力建设",
        "数据接入、规则引擎、AI识别接口",
        "@刘文博",
        "监控通道、规则后端、AI接口V0.5",
        "数据质量",
    ),
    (
        "W2",
        "2026/03/29 - 2026/04/04",
        "S8 闭环",
        "派单、回传、清明前封版",
        "@刘文博",
        "S8代码、流程图、闭环测试",
        "节前窗口",
    ),
    (
        "W3",
        "2026/04/05 - 2026/04/11",
        "S0 基础构建",
        "建模引擎、脚本、验证",
        "@刘文博；@彭熙玉自4/1起并行S1-S4准备",
        "S0脚本、引擎V0.5",
        "彭熙玉4/1起",
    ),
    (
        "W4",
        "2026/04/12 - 2026/04/18",
        "S0 优化与联调",
        "多变量推演、S0对外接口、与S8联动",
        "@刘文博",
        "S0完工、联调确认",
        "模块间契约",
    ),
    (
        "W5",
        "2026/04/19 - 2026/04/25",
        "S9 数据与ChatBI",
        "KPI引擎、九宫格接口、ChatBI解析",
        "@刘文博",
        "KPI引擎、接口、解析服务",
        "性能与SQL安全",
    ),
    (
        "W6",
        "2026/04/26 - 2026/04/30",
        "S9 总装",
        "前端联调、压测、P0/P1收敛",
        "@刘文博@郭峰",
        "S9集成报告、测试报告",
        "小刘表4/30节点",
    ),
    (
        "W7",
        "2026/05/04 - 2026/05/10",
        "S1-S4 开发推进",
        "产销、计划、供应、采购模块与KPI",
        "@彭熙玉主责原表S1-S4",
        "模块功能清单推进",
        "与S8/S9数据依赖",
    ),
    (
        "W8",
        "2026/05/11 - 2026/05/17",
        "S1-S4 联调",
        "与全链路指标联调",
        "@彭熙玉；@刘文博支撑S8/S9",
        "联调记录",
        "接口变更",
    ),
    (
        "W9",
        "2026/05/18 - 2026/05/22",
        "S5-S7 开发原表窗口",
        "仓储、生产、成品与KPI",
        "@方英明原表S5-S7",
        "S5-S7功能推进",
        "现场数据口径",
    ),
    (
        "W10",
        "2026/05/25 - 2026/05/29",
        "开发封版",
        "全员缺陷收敛、准备SIT",
        "@郭峰@彭熙玉@方英明@刘文博",
        "RC版本",
        "范围冻结",
    ),
    (
        "W11",
        "2026/06/01 - 2026/06/05",
        "SIT",
        "系统集成测试",
        "方英明@郭峰原表",
        "SIT报告",
        "缺陷修复速度",
    ),
    (
        "W12",
        "2026/06/08 - 2026/06/12",
        "UAT",
        "用户验收",
        "@卿劢@郭峰原表",
        "UAT记录",
        "变更控制",
    ),
    (
        "W13",
        "2026/06/15 - 2026/06/28",
        "交付准备",
        "部署、培训、清单",
        "@郭峰牵头",
        "交付包",
        "上线窗口",
    ),
    (
        "W14",
        "2026/06/29 - 2026/06/30",
        "正式交付",
        "交付验收",
        "@郭峰原表",
        "签收",
        "移交与运维",
    ),
]

row = 2
for w in weeks_data:
    ws2.append(list(w))
    for c in range(1, 8):
        ws2.cell(row, c).alignment = left if c >= 3 else center
        ws2.cell(row, c).border = border
    row += 1

for col, width in {"A": 8, "B": 24, "C": 22, "D": 40, "E": 38, "F": 26, "G": 22}.items():
    ws2.column_dimensions[col].width = width

wb.save(out)
print(out)