#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""产品系列资料补齐 V03：项目级甘特工作台 + 项目标准日期填写表
每个任务按具体项目列示：冻融/加速/效期/运输/系列建档/DG/NA
项目后贴标签：核心缺失项 / 建档类 / 专项参数
"""
import base64
import datetime as dt
import json
import math
import os
import sys
import openpyxl
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter
sys.path.insert(0, '/Users/liuxinyuan/Desktop/Hermes输出-工作类/脚本')
import brand_kit as BK
ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
SRC = os.path.join(ROOT, '销售分析', '产品系列资料归档与缺口清单_v3_2026-10-26.xlsx')
OUT_HTML = os.path.join(ROOT, '销售分析', '产品系列资料补齐_项目级甘特工作台_20261030_V03.html')
OUT_XLSX = os.path.join(ROOT, '经营分析', '产品系列资料补齐_项目标准日期填写表_20261030_V03.xlsx')
START = '2026-11-01'
WINDOW_END = '2027-01-31'
HOLIDAY = '2027-01-01'
ITEMS = [
{'key': 'freeze', 'name': '冻融稳定性', 'type': '核心缺失项', 'default': 2},
{'key': 'accel', 'name': '加速稳定性', 'type': '核心缺失项', 'default': 2},
{'key': 'shelf', 'name': '效期/长期/实时稳定性', 'type': '核心缺失项', 'default': 2},
{'key': 'transport', 'name': '运输/物流稳定性', 'type': '核心缺失项', 'default': 2},
{'key': 'archive', 'name': '系列档案建档', 'type': '建档类', 'default': 2},
{'key': 'dg', 'name': 'DG可冻干专项参数', 'type': '专项参数', 'default': 2},
{'key': 'na', 'name': 'NA低背景专项参数', 'type': '专项参数', 'default': 2},
]
ITEM_BY_NAME = {x['name']: x['key'] for x in ITEMS}
ITEM_BY_KEY = {x['key']: x for x in ITEMS}
RESOURCE_NAMES = ['研发-占位A', '研发-占位B', '研发-占位C', '产品-占位D', '产品-占位E', '研发-占位F', '产品-占位G', '资源-占位H']
def load_tasks():
ws = openpyxl.load_workbook(SRC, data_only=True)['系列缺口任务清单']
tasks = []
for r in ws.iter_rows(min_row=2, values_only=True):
if not r[0]:
continue
missing = [x.strip() for x in str(r[9] or '').split('、') if x.strip() and x.strip() != '无']
special = str(r[10] or '')
keys = []
for m in missing:
if m in ITEM_BY_NAME:
keys.append(ITEM_BY_NAME[m])
if int(r[8] or 0) == 0:
keys.append('archive')
if 'DG' in special:
keys.append('dg')
if 'NA' in special:
keys.append('na')
# de-dup, keep order
keys = list(dict.fromkeys(keys))
tasks.append({
'id': str(r[0]), 'rep': str(r[1] or ''), 'series': str(r[2] or ''),
'priority': str(r[11] or 'P2'), 'sales': float(r[5] or 0),
'customers': int(r[6] or 0), 'docs': int(r[8] or 0),
'missing': str(r[9] or ''), 'special': special,
'action': str(r[12] or ''), 'n_items': len(keys), 'item_keys': keys,
})
return tasks
def item_days(p):
return {x['key']: float(p.get(x['key'], 2)) for x in ITEMS}
def calc(t, p):
days = item_days(p)
base = sum(days[k] for k in t['item_keys'])
buf_raw = base * float(p.get('buffer', 30)) / 100.0
buffer = int(math.ceil(buf_raw - 1e-9))
total = int(math.ceil(base + buf_raw - 1e-9))
return {'base': round(base, 2), 'buffer': buffer, 'total': total}
def workdays(start, count):
d = dt.date.fromisoformat(start)
out = []
while len(out) < count:
if d.weekday() < 5 and d.isoformat() != HOLIDAY:
out.append(d)
d += dt.timedelta(days=1)
return out
def schedule(tasks, p, resources):
cal = workdays(p.get('start', START), 900)
res = RESOURCE_NAMES[:int(resources)]
free = {r: 0 for r in res}
order = {'P0': 0, 'P1': 1, 'P2': 2}
out = []
for t in sorted(tasks, key=lambda x: (order.get(x['priority'], 9), -x['sales'])):
c = calc(t, p)
r = min(res, key=lambda x: (free[x], res.index(x)))
s = free[r]
e = s + c['total'] - 1
row = dict(t)
row.update(c)
row.update({'owner': r, 'start': cal[s].isoformat(), 'end': cal[e].isoformat(),
's': s, 'e': e, 'window_status': '超窗' if cal[e].isoformat() > WINDOW_END else '窗口内'})
out.append(row)
free[r] = e + 1
return out, cal
def build_xlsx(tasks, p):
counts = {x['key']: sum(1 for t in tasks if x['key'] in t['item_keys']) for x in ITEMS}
wb = openpyxl.Workbook()
H = Font(name='Arial', bold=True, color='FFFFFF', size=10)
HF = PatternFill('solid', fgColor='1F4E79')
label_fill = PatternFill('solid', fgColor='FCE4D6')
input_fill = PatternFill('solid', fgColor='FFF2CC')
core_fill = PatternFill('solid', fgColor='DDEBF7')
archive_fill = PatternFill('solid', fgColor='E2EFDA')
special_fill = PatternFill('solid', fgColor='FCE4D6')
warn_fill = PatternFill('solid', fgColor='F8CBAD')
ok_fill = PatternFill('solid', fgColor='C6EFCE')
def header(ws, n):
for c in ws[1][:n]:
c.font = H; c.fill = HF; c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
ws.freeze_panes = 'A2'; ws.sheet_view.showGridLines = False
# 1 项目标准日期
ws = wb.active; ws.title = '项目标准日期'
ws.append(['项目名称', '类型标签', '适用任务数', '标准人日（天/项）', '填写说明'])
for it in ITEMS:
ws.append([it['name'], it['type'], counts[it['key']], it['default'], '请填你确认的标准人日；当前2D为占位值'])
header(ws, 5)
for i in range(2, 2 + len(ITEMS)):
ws.cell(i, 4).fill = input_fill
ws.cell(i, 4).font = Font(bold=True, size=11)
ws.cell(i, 2).fill = core_fill if ws.cell(i, 2).value == '核心缺失项' else (archive_fill if ws.cell(i, 2).value == '建档类' else special_fill)
for c in ws[i]:
c.alignment = Alignment(vertical='center', wrap_text=True)
for i, w in enumerate([24, 12, 10, 17, 45], 1):
ws.column_dimensions[get_column_letter(i)].width = w
# 2 任务项目明细
ws2 = wb.create_sheet('任务项目明细')
ws2.append(['任务ID', '优先级', '代表货号', '产品系列', '具体项目（含标签）', '项目数', '当前基准人日', '当前计划工期'])
for t in tasks:
names = []
for k in t['item_keys']:
it = ITEM_BY_KEY[k]
names.append(f"{it['name']}[{it['type']}]")
c = calc(t, p)
ws2.append([t['id'], t['priority'], t['rep'], t['series'], '；'.join(names), t['n_items'], c['base'], c['total']])
header(ws2, 8)
for row in ws2.iter_rows(min_row=2, max_row=ws2.max_row):
for c in row:
c.alignment = Alignment(vertical='top', wrap_text=True)
row[4].fill = label_fill
for i, w in enumerate([8, 7, 11, 28, 72, 7, 12, 12], 1):
ws2.column_dimensions[get_column_letter(i)].width = w
# 3 项目出现矩阵
ws3 = wb.create_sheet('项目出现矩阵')
ws3.append(['任务ID', '代表货号'] + [it['name'] for it in ITEMS] + ['项目数'])
for t in tasks:
ws3.append([t['id'], t['rep']] + [1 if it['key'] in t['item_keys'] else 0 for it in ITEMS] + [t['n_items']])
header(ws3, 2 + len(ITEMS) + 1)
for i, w in enumerate([8, 11] + [11] * len(ITEMS) + [7], 1):
ws3.column_dimensions[get_column_letter(i)].width = w
# 4 甘特预览（当前2D占位）
ws4 = wb.create_sheet('甘特预览_2D占位')
sched, _ = schedule(tasks, p, int(p.get('resources', 5)))
ws4.append(['任务ID', '优先级', '代表货号', '产品系列', '具体项目', '基准人日', '容错人日', '计划工期', '负责人', '开始', '完成', '窗口状态'])
for t in sched:
ws4.append([t['id'], t['priority'], t['rep'], t['series'],
'；'.join(f"{ITEM_BY_KEY[k]['name']}[{ITEM_BY_KEY[k]['type']}]" for k in t['item_keys']),
t['base'], t['buffer'], t['total'], t['owner'], t['start'], t['end'], t['window_status']])
header(ws4, 12)
for row in ws4.iter_rows(min_row=2, max_row=ws4.max_row):
for c in row:
c.alignment = Alignment(vertical='top', wrap_text=True)
row[11].fill = ok_fill if row[11].value == '窗口内' else warn_fill
for i, w in enumerate([8, 7, 11, 28, 72, 9, 9, 9, 14, 11, 11, 9], 1):
ws4.column_dimensions[get_column_letter(i)].width = w
# 5 优先级汇总
ws5 = wb.create_sheet('优先级汇总')
ws5.append(['优先级', '任务数', '基准人日', '容错人日', '计划人日', '占计划%', '最早开始', '最晚完成', '任务ID'])
all_total = sum(t['total'] for t in sched)
for pr in ['P0', 'P1', 'P2', 'P3']:
rows = [t for t in sched if t['priority'] == pr]
base = sum(t['base'] for t in rows); buf = sum(t['buffer'] for t in rows); tot = sum(t['total'] for t in rows)
ws5.append([pr, len(rows), base, buf, tot, round(tot / all_total * 100, 1) if all_total else 0,
min((t['start'] for t in rows), default='—'), max((t['end'] for t in rows), default='—'),
'、'.join(t['id'] for t in rows) or '—'])
header(ws5, 9)
for row in ws5.iter_rows(min_row=2, max_row=ws5.max_row):
for c in row:
c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
for i, w in enumerate([8, 8, 10, 10, 10, 9, 11, 11, 60], 1):
ws5.column_dimensions[get_column_letter(i)].width = w
# 6 说明
ws5 = wb.create_sheet('填写说明')
notes = [
['用途', '这是项目级标准日期填写表；请先在“项目标准日期”填7个项目的人日，再据此重算甘特。'],
['项目标签', '核心缺失项=冻融/加速/效期/运输；建档类=系列档案建档；专项参数=DG可冻干/NA低背景。'],
['任务拆解', '每个任务的具体项目在“任务项目明细”逐行列示，不再汇总成“核心缺失项”或“无资料建档”。'],
['当前2D', '所有项目暂按2D占位；这不是最终标准，等待你填写后替换。'],
['容错', '计划工期=ceil(Σ项目标准人日×(1+30%))；每个任务单独向上取整。'],
['窗口', '5人方案最晚2027-02-15，超4项；6人方案最晚2027-01-22，可落窗。'],
['下一步', '把“项目标准日期”7个数字发我，或在HTML工作台直接填；我据此锁最终甘特。'],
]
for row in notes:
ws5.append(row)
ws5.column_dimensions['A'].width = 14; ws5.column_dimensions['B'].width = 105
for row in ws5.iter_rows(min_row=1, max_row=ws5.max_row):
row[0].font = Font(bold=True)
row[1].alignment = Alignment(wrap_text=True, vertical='top')
ws5.sheet_view.showGridLines = False
wb.save(OUT_XLSX)
def build_html(tasks, p):
data = json.dumps(tasks, ensure_ascii=False)
items = json.dumps(ITEMS, ensure_ascii=False)
defaults = json.dumps({'buffer': 30, 'resources': 5, 'start': START, 'window_end': WINDOW_END, 'itemDays': {x['key']: 2 for x in ITEMS}}, ensure_ascii=False)
html = r'''
产品系列资料补齐 · 项目级甘特工作台