# 生产系统开发报价单生成脚本（单价 1200 元/人天）
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

RATE = 1200
OUT = "output/生产系统/生产系统开发报价单.xlsx"

FONT = "Microsoft YaHei"
DARK = "1F4E79"
LIGHT = "DDEBF7"
GREEN = "C6E0B4"

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

# 明细数据：(类别, 工作项, 人天)
items = [
    ("公共底座", "项目初始化+模块接入+前端脚手架+CI/CD", 3),
    ("公共底座", "登录+用户权限管理", 3),
    ("公共底座", "文件服务+Excel/报表导出", 3),
    ("公共底座", "OPC/Modbus数据采集服务（协议接入+点位配置+断线重连+采集入库）", 8),
    ("公共底座", "DCS历史站历史数据接入", 3),
    ("报表系统", "自定义生产日报表（模板配置+自动生成）", 6),
    ("报表系统", "生产过程数据查询、统计", 4),
    ("报表系统", "多批次数据对比分析", 4),
    ("报表系统", "趋势、异常、合格率分析", 5),
    ("批次管理", "Batch批次管理（定义/启停/列表查询）", 6),
    ("批次管理", "每釜过程数据记录（温度/压力/流量/液位曲线）", 6),
    ("批次管理", "原料、设备、人员、生产时间基础资料管理", 5),
    ("批次管理", "阶段性及最终物料指标录入与批次关联", 4),
    ("批次管理", "批次数据查询与追溯", 4),
    ("批次管理", "AI训练/推理数据集构建（抽取/清洗/版本管理）", 5),
    ("AI指标预测", "生产数据清洗与特征提取", 6),
    ("AI指标预测", "纯度预测模型建立与训练（单设备基准模型）", 8),
    ("AI指标预测", "多设备模型复制框架（按设备独立训练与管理）", 4),
    ("AI指标预测", "实时预测服务与结果展示", 5),
    ("AI指标预测", "预测值与实际检测值对比", 3),
    ("AI指标预测", "模型持续训练与优化", 5),
    ("工艺反向优化", "生产工艺-物料指标关联模型", 5),
    ("工艺反向优化", "根据目标指标反向计算工艺参数（寻优算法）", 8),
    ("工艺反向优化", "原料配比优化（原液添加量建议）", 5),
    ("工艺反向优化", "最佳工艺方案生成与展示", 4),
    ("工艺反向优化", "AI工艺调整建议", 3),
    ("工艺反向优化", "实际执行结果记录与模型迭代", 3),
    ("DCS智能控制", "AI模型/Agent服务接入与调度", 6),
    ("DCS智能控制", "DCS实时数据分析与调整方案生成", 5),
    ("DCS智能控制", "参数上下限与安全约束控制", 4),
    ("DCS智能控制", "半自动控制（人工确认后经OPC下发指令）", 6),
    ("DCS智能控制", "控制执行记录与追溯", 2),
    ("测试与交付", "联调+自测+Bug修复", 14),
    ("测试与交付", "系统测试/UAT", 9),
    ("测试与交付", "部署上线", 4),
    ("项目管理", "项目管理+需求+文档+沟通", 16),
]

# 汇总行：(名称, 范围说明)，区间由类别在明细中的行号推出
summary_rows = [
    ("公共底座", "脚手架、权限、OPC/Modbus采集、DCS历史数据接入"),
    ("报表系统", "生产日报表、查询统计、对比与合格率分析"),
    ("批次管理", "批次与过程数据记录、指标关联、追溯、AI数据集"),
    ("AI指标预测", "纯度预测模型、实时预测、持续优化"),
    ("工艺反向优化", "反向寻优、原料配比、最佳工艺方案"),
    ("DCS智能控制", "实时分析、安全约束、半自动控制"),
    ("测试与交付", "联调自测、系统测试/UAT、部署上线"),
    ("项目管理", "项目管理、需求、文档、沟通"),
]

wb = openpyxl.Workbook()

# ---------- 报价明细 ----------
ws = wb.active
ws.title = "报价明细"
ws.merge_cells("A1:E1")
c = ws["A1"]
c.value = "生产系统 — 开发报价单"
c.font = Font(name=FONT, size=15, bold=True, color="FFFFFF")
c.fill = PatternFill("solid", fgColor=DARK)
c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws.row_dimensions[1].height = 34

headers = ["序号", "类别", "功能模块 / 工作项", "人天", "金额（元）"]
for i, h in enumerate(headers, 1):
    c = ws.cell(row=3, column=i, value=h)
    c.font = Font(name=FONT, size=11, bold=True, color="FFFFFF")
    c.fill = PatternFill("solid", fgColor=DARK)
    c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    c.border = border
ws.row_dimensions[3].height = 24

cat_ranges = {}  # 类别 -> (起始行, 结束行)
row = 4
for idx, (cat, name, days) in enumerate(items, 1):
    ws.cell(row=row, column=1, value=idx)
    ws.cell(row=row, column=2, value=cat)
    ws.cell(row=row, column=3, value=name)
    ws.cell(row=row, column=4, value=days)
    ws.cell(row=row, column=5, value=f"=D{row}*{RATE}")
    for col in range(1, 6):
        c = ws.cell(row=row, column=col)
        c.font = Font(name=FONT, size=11)
        c.border = border
        if col == 3:
            c.alignment = Alignment(horizontal="left", vertical="center", wrap_text=True)
        elif col in (4, 5):
            c.alignment = Alignment(horizontal="right", vertical="center", wrap_text=True)
        else:
            c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    if cat not in cat_ranges:
        cat_ranges[cat] = [row, row]
    cat_ranges[cat][1] = row
    row += 1

total_row = row
# 类别列合并 + 填色
for cat, (r1, r2) in cat_ranges.items():
    if r2 > r1:
        ws.merge_cells(start_row=r1, start_column=2, end_row=r2, end_column=2)
    a = ws.cell(row=r1, column=2)
    a.fill = PatternFill("solid", fgColor=LIGHT)
    for r in range(r1, r2 + 1):
        ws.cell(row=r, column=2).fill = PatternFill("solid", fgColor=LIGHT)

ws.merge_cells(start_row=total_row, start_column=1, end_row=total_row, end_column=3)
ws.cell(row=total_row, column=1, value="合计")
ws.cell(row=total_row, column=4, value=f"=SUM(D4:D{total_row-1})")
ws.cell(row=total_row, column=5, value=f"=SUM(E4:E{total_row-1})")
for col in range(1, 6):
    c = ws.cell(row=total_row, column=col)
    c.font = Font(name=FONT, size=11, bold=True)
    c.fill = PatternFill("solid", fgColor=GREEN)
    c.border = border
    c.alignment = Alignment(
        horizontal="right" if col in (4, 5) else "center", vertical="center"
    )

for col, w in zip("ABCDE", [6, 14, 52, 9, 15]):
    ws.column_dimensions[col].width = w

# ---------- 报价汇总 ----------
ws2 = wb.create_sheet("报价汇总", 0)
ws2.merge_cells("A1:E1")
c = ws2["A1"]
c.value = "生产系统 — 开发报价汇总"
c.font = Font(name=FONT, size=15, bold=True, color="FFFFFF")
c.fill = PatternFill("solid", fgColor=DARK)
c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws2.row_dimensions[1].height = 34

headers2 = ["序号", "功能 / 模块", "范围说明", "人天", "金额（元）"]
for i, h in enumerate(headers2, 1):
    c = ws2.cell(row=3, column=i, value=h)
    c.font = Font(name=FONT, size=11, bold=True, color="FFFFFF")
    c.fill = PatternFill("solid", fgColor=DARK)
    c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    c.border = border
ws2.row_dimensions[3].height = 24

r = 4
for i, (name, scope) in enumerate(summary_rows, 1):
    r1, r2 = cat_ranges[name]
    ws2.cell(row=r, column=1, value=i)
    ws2.cell(row=r, column=2, value=name)
    ws2.cell(row=r, column=3, value=scope)
    ws2.cell(row=r, column=4, value=f"=SUM(报价明细!D{r1}:D{r2})")
    ws2.cell(row=r, column=5, value=f"=SUM(报价明细!E{r1}:E{r2})")
    for col in range(1, 6):
        c = ws2.cell(row=r, column=col)
        c.font = Font(name=FONT, size=11)
        c.border = border
        if col == 3:
            c.alignment = Alignment(horizontal="left", vertical="center", wrap_text=True)
        elif col in (4, 5):
            c.alignment = Alignment(horizontal="right", vertical="center", wrap_text=True)
        else:
            c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    r += 1

sum_total = r
ws2.merge_cells(start_row=sum_total, start_column=1, end_row=sum_total, end_column=3)
ws2.cell(row=sum_total, column=1, value="合计")
ws2.cell(row=sum_total, column=4, value=f"=SUM(D4:D{sum_total-1})")
ws2.cell(row=sum_total, column=5, value=f"=SUM(E4:E{sum_total-1})")
for col in range(1, 6):
    c = ws2.cell(row=sum_total, column=col)
    c.font = Font(name=FONT, size=11, bold=True)
    c.fill = PatternFill("solid", fgColor=GREEN)
    c.border = border
    c.alignment = Alignment(
        horizontal="right" if col in (4, 5) else "center", vertical="center"
    )

for col, w in zip("ABCDE", [6, 16, 46, 10, 16]):
    ws2.column_dimensions[col].width = w

wb.save(OUT)
print("saved:", OUT, "| 明细行数:", len(items), "| 合计人天:", sum(d for _, _, d in items))