199 lines
9.6 KiB
Python
199 lines
9.6 KiB
Python
# -*- 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)
|