#!/usr/bin/env python3 # -*- coding: utf-8 -*- """产品系列资料补齐 —— 排期 + 甘特图（Excel & HTML） 排期口径（2026-10-26 用户拍板）： - 时间窗 2026-11-01 ~ 2027-01-31 - 责任人先用占位符（研发A/B/C、产品D/E） - 工作日 = 周一至周五，跳过 2027-01-01 元旦 - 优先级 P0 → P1 → P2，同优先级按销售额降序 - 每个任务由 1 个占位资源独占完成，资源空闲即接下一项 """ import base64 import datetime as dt import json 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_XLSX = os.path.join(ROOT, '经营分析', '产品系列资料补齐_排期与甘特图_2026-11至2027-01.xlsx') OUT_HTML = os.path.join(ROOT, '销售分析', '产品系列资料补齐_甘特图_2026-11至2027-01.html') START, END = dt.date(2026, 11, 1), dt.date(2027, 1, 31) HOLIDAYS = {dt.date(2027, 1, 1)} RESOURCES = ['研发-占位A', '研发-占位B', '研发-占位C', '产品-占位D', '产品-占位E'] def workdays(a, b): d, out = a, [] while d <= b: if d.weekday() < 5 and d not in HOLIDAYS: out.append(d) d += dt.timedelta(days=1) return out def load_tasks(): ws = openpyxl.load_workbook(SRC)['系列缺口任务清单'] rows = [] for r in ws.iter_rows(min_row=2, values_only=True): rows.append({'id': r[0], 'rep': r[1], 'series': r[2], 'cat': r[3], 'finished': r[4], 'sales_wan': r[5], 'customers': r[6], 'sole_share': r[7], 'docs': r[8], 'missing': r[9], 'special': r[10], 'priority': r[11], 'action': r[12], 'dept': r[13], 'days': float(r[14] or 0)}) return rows def schedule(tasks): cal = workdays(START, END) free = {r: 0 for r in RESOURCES} # 资源 → 下一个可用日索引 order = {'P0': 0, 'P1': 1, 'P2': 2} tasks.sort(key=lambda t: (order[t['priority']], -t['sales_wan'])) for t in tasks: res = min(free, key=lambda r: (free[r], RESOURCES.index(r))) s = free[res] dur = max(1, int(round(t['days']))) e = min(s + dur - 1, len(cal) - 1) t['owner'] = res t['start'], t['end'] = cal[s], cal[e] t['dur'] = e - s + 1 free[res] = e + 1 return tasks, free def build_xlsx(tasks): wb = openpyxl.Workbook() H, F = Font(color='FFFFFF', bold=True), PatternFill('solid', fgColor='1F4E79') ws = wb.active ws.title = '甘特数据' heads = ['任务ID', '优先级', '系列', '代表货号', '任务内容', '缺失项', '专项参数', '预计人日', '排定工期(工作日)', '负责人(占位)', '开始', '完成', '2026销售额(万)', '客户数', '单客金额占比%', '资料状态依据'] ws.append(heads) for t in tasks: ws.append([t['id'], t['priority'], t['series'], t['rep'], t['action'], t['missing'], t['special'], t['days'], t['dur'], t['owner'], t['start'], t['end'], t['sales_wan'], t['customers'], t['sole_share'], '未收录' if t['docs'] == 0 else '部分']) for c in ws[1]: c.font = H; c.fill = F; c.alignment = Alignment(horizontal='center', wrap_text=True) widths = [8, 7, 30, 12, 46, 26, 18, 8, 10, 12, 11, 11, 13, 7, 11, 9] for i, w in enumerate(widths, 1): ws.column_dimensions[get_column_letter(i)].width = w ws.freeze_panes = 'C2' # 日历视图 ws2 = wb.create_sheet('日历视图') weeks = [] d = START while d <= END: ws2.cell(2, 3 + len(weeks)).value = d.strftime('%m/%d') ws2.cell(2, 3 + len(weeks)).font = Font(size=8) weeks.append(d) d += dt.timedelta(days=1) ws2.cell(1, 1, '任务'); ws2.cell(1, 2, '负责人'); ws2.cell(1, 3, '工期/起止') ws2.cell(2, 1, ''); ws2.cell(2, 2, '') for i, t in enumerate(tasks, 3): ws2.cell(i, 1, f"{t['id']} {t['rep']}").font = Font(size=9, bold=True) ws2.cell(i, 2, t['owner']).font = Font(size=8) ws2.cell(i, 3, f"{t['dur']}d").font = Font(size=8) for j, d in enumerate(weeks): c = ws2.cell(i, 4 + j) if t['start'] <= d <= t['end']: c.fill = PatternFill('solid', fgColor={'P0': 'C0392B', 'P1': 'E67E22', 'P2': '2E86C1'}[t['priority']]) ws2.column_dimensions['A'].width = 26 ws2.column_dimensions['B'].width = 12 ws2.column_dimensions['C'].width = 7 for j in range(len(weeks)): ws2.column_dimensions[get_column_letter(4 + j)].width = 2.4 ws2.freeze_panes = 'D3' ws3 = wb.create_sheet('口径与资源') for line in [ ['时间窗', '2026-11-01 ~ 2027-01-31'], ['工作日', '周一至周五，跳过 2027-01-01'], ['排期规则', 'P0 → P1 → P2；同优先级按 2026 销售额降序；每项由 1 名占位资源独占完成'], ['资源', '、'.join(RESOURCES) + '（占位，待你指派真人后一键替换）'], ['负载', ' / '.join('%s %d项' % (r, sum(1 for t in tasks if t['owner'] == r)) for r in RESOURCES)], ['总人日', '%.0f 人日' % sum(t['days'] for t in tasks)], ['最晚完成', max(t['end'] for t in tasks).isoformat()], ['说明', '人日=按缺失项 2 天/项 + 无资料建档 2 天 + DG/NA 专项 2 天/项 估算；排定工期按 1 人独占折算工作日'], ]: ws3.append(line) ws3.column_dimensions['A'].width = 12 ws3.column_dimensions['B'].width = 110 wb.save(OUT_XLSX) def build_html(tasks): logo = base64.b64encode(open(os.path.expanduser('~/work/公司资料/logo_transparent.png'), 'rb').read()).decode() cal = workdays(START, END) idx = {d: i for i, d in enumerate(cal)} months = [] for d in cal: key = (d.year, d.month) if not months or months[-1][0] != key: months.append([key, 0]) months[-1][1] += 1 colors = {'P0': '#c0392b', 'P1': '#e67e22', 'P2': '#2e86c1'} rows = [] for i, t in enumerate(tasks): s, e = idx[t['start']], idx[t['end']] rows.append({ 'id': t['id'], 'rep': t['rep'], 'series': t['series'], 'pri': t['priority'], 'owner': t['owner'], 'start': t['start'].isoformat(), 'end': t['end'].isoformat(), 's': s, 'w': e - s + 1, 'days': t['dur'], 'act': t['action'], 'sales': t['sales_wan'], 'cust': t['customers'], 'sole': t['sole_share'], 'missing': t['missing'], 'color': colors[t['priority']], }) n = len(cal) month_html = ''.join( '
%d年%d月
' % (cnt * 9, k[0], k[1]) for k, cnt in months) week_html = '' for i, d in enumerate(cal): cls = 'wk' if d.weekday() == 0 else '' week_html += '
%s
' % (cls, i * 9, d.strftime('%d') if d.weekday() == 0 else '') row_html = '' for r in rows: row_html += f'''
{r['pri']} {r['rep']} {r['series']}
{r['owner']} · {r['days']}d · {r['start'][5:]}→{r['end'][5:]} · {r['sales']}万 · {r['cust']}家客户
{r['days']}d
''' html = f''' 产品系列资料补齐 · 甘特图（2026-11 ~ 2027-01）

产品系列资料补齐 · 排期甘特图

时间窗 2026-11-01 → 2027-01-31 ｜ 责任人暂用占位符（研发A/B/C、产品D/E），指派真人后一键替换 ｜ 数据源：产品系列资料归档与缺口清单 v3
{len(tasks)}任务数（P0 {sum(1 for t in tasks if t['priority']=='P0')} / P1 {sum(1 for t in tasks if t['priority']=='P1')} / P2 {sum(1 for t in tasks if t['priority']=='P2')}）
{sum(t['days'] for t in tasks):.0f}总人日（估算）
{max(t['end'] for t in tasks).strftime('%m-%d')}最晚完成日
{len(RESOURCES)}占位资源
{sum(1 for t in tasks if t['docs']==0)}需先建档（未收录）
P0 千万级 P1 30–100万 P2 <30万
{month_html}
{week_html}
{row_html}
排期规则：P0→P1→P2，同优先级按销售额降序；每项 1 名资源独占，工作日=周一至周五（跳元旦）。宝锐生物 · 营销中心
''' html = BK.inject(html, theme='light', tag='营销中心 · 产品资料') open(OUT_HTML, 'w', encoding='utf-8').write(html) if __name__ == '__main__': tasks = load_tasks() tasks, free = schedule(tasks) build_xlsx(tasks) build_html(tasks) print('任务 %d ｜人日 %.0f' % (len(tasks), sum(t['days'] for t in tasks))) for r in RESOURCES: ts = [t for t in tasks if t['owner'] == r] print(' %s: %d 项，%s → %s' % (r, len(ts), ts[0]['start'] if ts else '-', max(t['end'] for t in ts) if ts else '-')) print('最晚完成', max(t['end'] for t in tasks)) print('✅', OUT_XLSX) print('✅', OUT_HTML)