mescloud/_build_mes_summary.py

# -*- coding: utf-8 -*-
import os
import re
from datetime import datetime
from openpyxl import load_workbook

BASE = r"c:\work\demo\dayang\mescloud"
EXTRACT = os.path.join(BASE, "_extract_mes_progress_output.txt")
STATUS_XLSX = r"c:\Users\j2742\Desktop\temp\大洋\实施文档\MES实施现状-260716.xlsx"
PIC_DIR = r"c:\Users\j2742\Desktop\temp\大洋\pic"
OUT = os.path.join(BASE, "_mes_milestone_summary.txt")

CELL_RE = re.compile(r"^([A-Z]+)(\d+): (.+)$")


def fmt_val(v):
    if v is None:
        return ""
    if isinstance(v, datetime):
        return v.strftime("%Y-%m-%d")
    return str(v).strip()


def extract_xlsx(path, max_rows=200, max_cols=30):
    lines = []
    wb = load_workbook(path, data_only=True)
    lines.append(f"文件: {path}")
    lines.append(f"工作表: {', '.join(wb.sheetnames)}")
    for name in wb.sheetnames:
        ws = wb[name]
        lines.append("")
        lines.append(f"--- 工作表: {name} (维度 {ws.dimensions}) ---")
        for row in ws.iter_rows(min_row=1, max_row=max_rows, min_col=1, max_col=max_cols):
            for cell in row:
                v = cell.value
                if v is not None and str(v).strip() != "":
                    lines.append(f"  {cell.coordinate}: {fmt_val(v)}")
    wb.close()
    return lines


def parse_extract_sheet(text, sheet_name):
    pattern = rf"^SHEET: {re.escape(sheet_name)}\s*$"
    lines = text.splitlines()
    start = None
    for i, line in enumerate(lines):
        if re.match(pattern, line.strip()):
            start = i
            break
    if start is None:
        return []
    out = []
    for line in lines[start + 1:]:
        stripped = line.strip()
        if stripped.startswith("SHEET:"):
            break
        if stripped.startswith("--- Non-empty cells"):
            break
        m = CELL_RE.match(stripped)
        if m:
            out.append((m.group(1), int(m.group(2)), m.group(3)))
    return out


def cells_to_rows(cells):
    rows = {}
    for col, row, val in cells:
        rows.setdefault(row, {})[col] = val
    return rows


def list_images(root):
    exts = {".png", ".jpg", ".jpeg", ".gif", ".bmp", ".webp", ".ico"}
    found = []
    for dirpath, _, filenames in os.walk(root):
        for fn in filenames:
            if os.path.splitext(fn)[1].lower() in exts:
                rel = os.path.relpath(os.path.join(dirpath, fn), root)
                found.append(rel.replace("\\", "/"))
    return sorted(found, key=lambda x: x.lower())


with open(EXTRACT, "r", encoding="utf-8") as f:
    extract_text = f.read()

sections = []
sections.append("=" * 80)
sections.append("六安裕洋 MES 实施里程碑与截图资源汇总")
sections.append(f"生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
sections.append("数据来源: 大洋-京宜安MES实施进度.xlsx、MES实施现状-260716.xlsx、pic/ 目录")
sections.append("=" * 80)

# A
sections.append("")
sections.append("A. 总进度 — 各周完成率（来源：总进度）")
sections.append("-" * 80)
zong_cells = parse_extract_sheet(extract_text, "总进度")
zong_rows = cells_to_rows(zong_cells)
sections.append("汇总表:")
sections.append("  实施阶段 | 开始时间 | 结束时间 | 具体任务(周) | 任务数 | 完成数 | 合格率")
for r in sorted(k for k in zong_rows if k >= 3):
    d = zong_rows[r]
    sections.append(
        f"  {d.get('A','')} | {d.get('B','')} | {d.get('C','')} | {d.get('D','')} | "
        f"{d.get('E','')} | {d.get('F','')} | {d.get('G','')}"
    )

# B
sections.append("")
sections.append("B. 全流程详细实施计划 — 阶段一至七（关键交付与状态）")
sections.append("-" * 80)
plan_cells = parse_extract_sheet(extract_text, "全流程详细实施计划")
plan_rows = cells_to_rows(plan_cells)

phases = []
for r in sorted(plan_rows):
    a = plan_rows[r].get("A", "")
    if a and "阶段" in a:
        phases.append((r, a.replace("\n", " ")))

for idx, (prow, pname) in enumerate(phases):
    end_row = phases[idx + 1][0] if idx + 1 < len(phases) else max(plan_rows) + 1
    sections.append("")
    sections.append(f"【{pname}】")
    for row in range(prow + 1, end_row):
        d = plan_rows.get(row, {})
        sub = d.get("B", "")
        if sub in ("项目准备", "软件框架搭建", "系统蓝图设计", "系统开发", "系统上线", "后续对接", "交付软件", "序号"):
            continue
        task = d.get("C", "")
        if not task and not d.get("H"):
            continue
        seq = d.get("B", "")
        content = d.get("H", "")
        status = d.get("P", "")
        deliverable = d.get("J", "")
        parts = []
        if seq:
            parts.append(f"{seq}. {task}" if task else f"{seq}.")
        else:
            parts.append(task)
        if content:
            parts.append(f"工作: {content}")
        if deliverable:
            parts.append(f"输出: {deliverable}")
        if status:
            parts.append(f"状态: {status}")
        sections.append("  " + " | ".join(p for p in parts if p))

# C
sections.append("")
sections.append("C. 七月份第一周 / 七月份第二周 — 任务明细")
sections.append("-" * 80)

WEEKLY_COLS = ("B", "C", "D", "E", "F", "G", "H", "I", "J", "K")

def format_weekly_task(row_num, d):
    lines = []
    title = d.get("B", "") or d.get("C", "")
    seq = d.get("A", "")
    head = f"  行{row_num}"
    if seq:
        head += f" / 序号{seq}"
    lines.append(head + ":")
    lines.append(f"    任务名称: {title}")
    if d.get("C"):
        lines.append(f"    工作内容: {d.get('C')}")
    lines.append(f"    甲方对接人: {d.get('D', '')}")
    lines.append(f"    乙方对接人: {d.get('E', '')}")
    if d.get("F") or d.get("G"):
        lines.append(f"    计划: {d.get('F', '')} ~ {d.get('G', '')}")
    lines.append(f"    状态: {d.get('H', '')}")
    if d.get("I"):
        lines.append(f"    进展说明: {d.get('I')}")
    if d.get("J"):
        lines.append(f"    备注: {d.get('J')}")
    k = d.get("K", "")
    if k and not str(k).startswith("=DISPIMG"):
        lines.append(f"    备注(K): {k}")
    return lines

for sheet in ("七月份第一周", "七月份第二周"):
    sections.append("")
    sections.append(f"=== {sheet} ===")
    cells = parse_extract_sheet(extract_text, sheet)
    rows = cells_to_rows(cells)
    data_rows = [r for r in sorted(rows) if r >= 3 and any(rows[r].get(c) for c in WEEKLY_COLS)]
    for r in data_rows:
        sections.extend(format_weekly_task(r, rows[r]))

# D
sections.append("")
sections.append("D. 问题清单（来源：问题清单工作表）")
sections.append("-" * 80)
prob_cells = parse_extract_sheet(extract_text, "问题清单")
prob_rows = cells_to_rows(prob_cells)
for r in sorted(prob_rows):
    if r < 3:
        continue
    d = prob_rows[r]
    if not (d.get("B") or d.get("C")):
        continue
    sections.append(
        f"  {d.get('A', r-2)}. {d.get('B', '')} | 内容: {d.get('C', '')} | "
        f"对接人: {d.get('D', '')} | 后续: {d.get('E', '')} | 进度: {d.get('F', '')}"
    )

# E
sections.append("")
sections.append("E. pic/ 目录截图文件完整列表")
sections.append("-" * 80)
imgs = list_images(PIC_DIR)
sections.append(f"共 {len(imgs)} 个图片文件:")
for im in imgs:
    sections.append(f"  {im}")

# F
sections.append("")
sections.append("F. MES实施现状-260716.xlsx 完整内容")
sections.append("-" * 80)
sections.extend(extract_xlsx(STATUS_XLSX))

with open(OUT, "w", encoding="utf-8") as f:
    f.write("\n".join(sections) + "\n")

print(f"Wrote {OUT}")
print(f"Lines: {len(sections)}")