aps-agent/kangni-analysis/make_gantt.py

# -*- coding: utf-8 -*-
"""康尼 APS 计划排产模块 24 周甘特排期表"""
from datetime import date, timedelta
from pathlib import Path
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

OUT = Path(__file__).resolve().with_name("康尼APS计划排产模块甘特排期表.xlsx")

START = date(2026, 9, 7)  # 第1周周一
WEEKS = 26

PHASE_COLORS = {
    0: "4472C4", 1: "70AD47", 2: "ED7D31", 3: "FFC000", 4: "C00000", 5: "7030A0",
}

# (阶段, 编号, 任务, 责任方, 交付物/验收, 开始周, 结束周)
TASKS = [
    (0, "0.1", "排程策略定版（交期/齐套/产能/人员技能优先权重）", "康尼计划组+乙方", "《排程策略与规则说明书》", 1, 2),
    (0, "0.2", "锁定规则确认（T+7/日排3天/滚动5天锁定/变更条件）", "康尼计划组", "锁定与变更规则矩阵", 2, 3),
    (0, "0.3", "排程颗粒度对齐（小时级 vs 分钟级）", "双方", "颗粒度确认纪要", 2, 2),
    (0, "0.4", "计划订单转换权归属（SAP vs MOM）", "康尼IT+SAP顾问", "系统边界确认书", 3, 3),
    (0, "0.5", "跨部门待决项闭环（编码/PM码/包装/流水追溯规则）", "康尼牵头", "待决事项闭环清单", 3, 4),
    (1, "1.1", "BOM 定额/层级/替代料核查 + 检修订单BOM补齐", "康尼工艺部", "BOM核查报告", 3, 6),
    (1, "1.2", "标准工时盘点与缺失工序补测", "康尼工艺+班组", "标准工时库", 4, 8),
    (1, "1.3", "工位能力矩阵建模（轨交3线+民品2线新建）", "乙方+康尼生产部", "工序-工位能力匹配矩阵", 4, 7),
    (1, "1.4", "弹性人力产能模型（多/少人模式、产值与加班约束参数化）", "乙方", "人力产能模型", 5, 7),
    (1, "1.5", "多工厂日历/班组/班次配置", "乙方", "工厂日历配置", 6, 8),
    (2, "2.1", "排程约束建模（能力/物料状态/组件时间关联/在制占用）", "乙方", "约束模型配置", 7, 11),
    (2, "2.2", "排程功能配置（正倒排/多方案/甘特拖拽/锁定/KPI）", "乙方", "排程功能就绪", 8, 12),
    (2, "2.3", "SAP集成（计划订单拉取/齐套检查IFS012/报工IFS202）", "乙方+SAP顾问", "接口联调通过", 9, 14),
    (2, "2.4", "预警规则（交期冲突/变更影响仿真/在途物料提醒）", "乙方", "预警规则上线", 11, 14),
    (3, "3.1", "近6个月历史订单回测，测定计划准确率基线", "双方", "回测报告（基线95%）", 13, 15),
    (3, "3.2", "10%产能预留假设仿真验证", "乙方", "验证报告", 15, 16),
    (3, "3.3", "压测：全量排程≤10分钟、插单what-if重算时效", "乙方", "压测报告", 16, 17),
    (3, "3.4", "四类场景演练（插单/待料/维修/多-少人切换）", "双方", "场景通关记录", 17, 18),
    (4, "4.1", "流水线1双轨运行（Excel vs 系统，偏差<5%）", "康尼计划组+乙方驻场", "双轨比对周报×4", 19, 22),
    (4, "4.2", "单轨切换（计划编制4h→1h）", "康尼计划组", "切换确认单", 23, 23),
    (4, "4.3", "看板报表上线（缺料表/产能评估/计划完成率）", "乙方", "3类看板可用", 20, 24),
    (5, "5.1", "推广至岸电7500/民品7600 + 二期深化衔接", "双方", "推广计划", 25, 26),
]

MILESTONES = [
    (4, "M1 蓝图冻结", "排程规则说明书签字"),
    (8, "M2 数据就绪", "工时/能力矩阵/BOM核查通过"),
    (14, "M3 集成贯通", "SAP计划订单自动拉通"),
    (18, "M4 模拟达标", "回测与压测指标达标"),
    (24, "M5 试点上线", "流水线1单轨运行"),
]

HOLIDAYS = {1: "国庆", 22: "春节前", 23: "春节", 24: "春节后"}

def wk_monday(w):
    return START + timedelta(weeks=w - 1)

def wk_sunday(w):
    return wk_monday(w) + timedelta(days=6)

wb = Workbook()
ws = wb.active
ws.title = "甘特排期"

thin = Side(style="thin", color="BFBFBF")
border = Border(left=thin, right=thin, top=thin, bottom=thin)

# 标题行
ws.merge_cells("A1:G1")
ws["A1"] = "康尼科技 MOM 项目 · APS 计划排产模块 24 周实施排期（V1.0，起点 2026-09-07）"
ws["A1"].font = Font(bold=True, size=14, color="1F4E79")
ws.row_dimensions[1].height = 24

# 表头
HEAD = ["阶段", "编号", "任务", "责任方", "交付物/验收", "起", "止"]
for c, h in enumerate(HEAD, 1):
    cell = ws.cell(row=3, column=c, value=h)
    cell.font = Font(bold=True, color="FFFFFF", size=10)
    cell.fill = PatternFill("solid", fgColor="1F4E79")
    cell.alignment = Alignment(horizontal="center", vertical="center")
    cell.border = border

for w in range(1, WEEKS + 1):
    cell = ws.cell(row=3, column=7 + w, value=f"W{w}")
    cell.font = Font(bold=True, color="FFFFFF", size=9)
    cell.fill = PatternFill("solid", fgColor="1F4E79")
    cell.alignment = Alignment(horizontal="center", vertical="center")
    cell.border = border
    d = ws.cell(row=4, column=7 + w, value=wk_monday(w).strftime("%m/%d"))
    d.font = Font(size=8, color="808080")
    d.alignment = Alignment(horizontal="center")
    d.border = border

ws.cell(row=4, column=1, value="（周一起始日期）").font = Font(size=8, color="808080")

# 假期提示行
ws.cell(row=5, column=1, value="节假日").font = Font(size=9, bold=True, color="C00000")
for w, name in HOLIDAYS.items():
    cell = ws.cell(row=5, column=7 + w, value=name)
    cell.font = Font(size=7, color="C00000")
    cell.alignment = Alignment(horizontal="center", textRotation=90)
    cell.fill = PatternFill("solid", fgColor="FCE4EC")
    cell.border = border

# 里程碑行
ws.cell(row=6, column=1, value="★ 里程碑").font = Font(size=9, bold=True, color="C00000")
for w, name, desc in MILESTONES:
    cell = ws.cell(row=6, column=7 + w, value="◆")
    cell.font = Font(size=12, bold=True, color="C00000")
    cell.alignment = Alignment(horizontal="center")
    cell.border = border

# 任务行
row = 8
for phase, no, task, owner, deliv, s, e in TASKS:
    vals = [f"阶段{phase}", no, task, owner, deliv, f"W{s}", f"W{e}"]
    for c, v in enumerate(vals, 1):
        cell = ws.cell(row=row, column=c, value=v)
        cell.font = Font(size=9)
        cell.border = border
        cell.alignment = Alignment(vertical="center", wrap_text=(c == 3))
    color = PHASE_COLORS[phase]
    for w in range(1, WEEKS + 1):
        cell = ws.cell(row=row, column=7 + w)
        cell.border = border
        if s <= w <= e:
            cell.fill = PatternFill("solid", fgColor=color)
    ws.row_dimensions[row].height = 30
    row += 1

# 里程碑说明
row += 1
ws.cell(row=row, column=1, value="里程碑说明：").font = Font(bold=True, size=10)
row += 1
for w, name, desc in MILESTONES:
    ws.cell(row=row, column=1, value=f"{name}（W{w}，{wk_sunday(w).strftime('%Y-%m-%d')} 前）").font = Font(size=9, bold=True)
    ws.cell(row=row, column=3, value=desc).font = Font(size=9)
    row += 1

row += 1
notes = [
    "注1：W1 含国庆假期（2026-10-01~07），阶段0关键会议建议避开；",
    "注2：W22~W24 覆盖 2027 年春节（2027-02-06 除夕前后），双轨末期与单轨切换预留假期缓冲，M5 最迟顺延至 2027 年 3 月上旬；",
    "注3：阶段0 五项规则未闭环签字前，阶段2 开发不得开工（计划前置卡点）；",
    "注4：双轨期严格限定 4 周且仅流水线 1，乙方驻场支持，防止计划员工作量反增。",
]
for n in notes:
    ws.cell(row=row, column=1, value=n).font = Font(size=9, color="595959")
    row += 1

# 列宽
widths = [7, 6, 46, 18, 24, 5, 5]
for c, wd in enumerate(widths, 1):
    ws.column_dimensions[get_column_letter(c)].width = wd
for w in range(1, WEEKS + 1):
    ws.column_dimensions[get_column_letter(7 + w)].width = 5

ws.freeze_panes = "H7"

# ---- Sheet2 风险与前置输入 ----
ws2 = wb.create_sheet("风险与前置输入")
ws2.append(["关键风险与对策"])
ws2["A1"].font = Font(bold=True, size=12, color="1F4E79")
risks = [
    ("风险", "等级", "对策"),
    ("排程策略/锁定规则跨部门达不成一致", "高", "M1 前组织计划+生产+工艺+市场专题会，逐条签字"),
    ("标准工时缺失导致排程失真", "高", "阶段1设补测专项，缺失工时按班组经验值+回测修正"),
    ("民品工位主数据从零建设", "中", "先产线级粗颗粒，后工位级细颗粒"),
    ("PLM 未上线，工艺数据源头不稳", "中", "一期以 SAP 工艺路线为准，预留 PLM 接口"),
    ("双轨期计划员工作量反增", "中", "双轨限 4 周+只跑 1 条线，乙方驻场"),
    ("春节假期压缩双轨窗口", "中", "W22~24 预留缓冲，M5 可顺延至 3 月上旬"),
]
for r in risks:
    ws2.append(list(r))
for c in range(1, 4):
    ws2.cell(row=2, column=c).font = Font(bold=True, color="FFFFFF")
    ws2.cell(row=2, column=c).fill = PatternFill("solid", fgColor="1F4E79")
for rr in range(3, 3 + len(risks) - 1):
    for c in range(1, 4):
        ws2.cell(row=rr, column=c).border = border
        ws2.cell(row=rr, column=c).alignment = Alignment(vertical="center", wrap_text=True)
ws2.column_dimensions["A"].width = 42
ws2.column_dimensions["B"].width = 8
ws2.column_dimensions["C"].width = 56

ws2.append([])
ws2.append(["待康尼确认的前置输入"])
ws2.cell(row=ws2.max_row, column=1).font = Font(bold=True, size=12, color="1F4E79")
for item in [
    "1. 蓝图阶段实际启动时间与双方投入人力；",
    "2. SAP 价格数据（产值类排程 KPI 核算，8.18 纪要待办）；",
    "3. 检修订单计划与 BOM（8.18 纪要待办，影响返修排程）；",
    "4. 试点产线选择是否认可“流水线 1”。",
]:
    ws2.append([item])
    ws2.cell(row=ws2.max_row, column=1).font = Font(size=10)

wb.save(OUT)
print("saved:", OUT)