AiDOPWarehouse/doc/_gen_bid_action_excel.py

# -*- coding: utf-8 -*-
"""Generate AIDOP bidding preparation action Excel."""
from decimal import Decimal, ROUND_HALF_UP

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation

CAP = Decimal("600000")
TAX_RATE = Decimal("0.06")
CENT = Decimal("0.01")


def money(x):
    return Decimal(x).quantize(CENT, rounding=ROUND_HALF_UP)


def fmt(x):
    return f"{money(x):,.2f}"


def baseline(prices):
    return money(sum(Decimal(p) for p in prices) / Decimal(3) * Decimal("0.97"))


def bid_score(price, base):
    """40 at baseline; -0.6/1% above, -0.3/1% below; 2dp, floor 0."""
    price = Decimal(price)
    if price == base:
        return Decimal("40.00")
    pct = (price - base) / base * Decimal("100")
    penalty = pct * Decimal("0.6") if price > base else -pct * Decimal("0.3")
    return max((Decimal("40") - penalty).quantize(CENT, rounding=ROUND_HALF_UP), Decimal("0"))


def solve_our_price(p2, p3):
    """Largest price equal to the resulting baseline: p1 = 0.97*(p1+p2+p3)/3."""
    raw = money(Decimal("0.97") * (Decimal(p2) + Decimal(p3)) / Decimal("2.03"))
    for adj in range(20, -21, -1):
        cand = raw + Decimal(adj) / 100
        if cand == baseline([cand, p2, p3]):
            return cand
    return raw


def ex_tax(price):
    return money(Decimal(price) / (Decimal("1") + TAX_RATE))


class Plan:
    def __init__(self, key, name, p2, p3, verdict, comment, our_price=None):
        self.key = key
        self.name = name
        self.p2 = Decimal(p2)
        self.p3 = Decimal(p3)
        self.p1 = Decimal(our_price) if our_price is not None else solve_our_price(p2, p3)
        self.base = baseline([self.p1, self.p2, self.p3])
        self.s1 = bid_score(self.p1, self.base)
        self.s2 = bid_score(self.p2, self.base)
        self.s3 = bid_score(self.p3, self.base)
        self.gap = self.s1 - max(self.s2, self.s3)
        self.verdict = verdict
        self.comment = comment


PLANS = [
    Plan(
        "方案一 顶格贴近型",
        "两家紧贴限价、仅差70元",
        "599830",
        "599900",
        "备选",
        "分差与我方报价同时最大化，但两家报价均贴着限价且仅差70元，配合痕迹最重",
    ),
    Plan(
        "方案二 分差极限型",
        "两家598,800 / 598,900",
        "598800",
        "598900",
        "备选",
        "分差取到结构上限2.78；数字为整百元较自然，但两家仅差100元",
    ),
    Plan(
        "方案三 均衡推荐型",
        "两家598,300 / 599,400",
        "598300",
        "599400",
        "★推荐",
        "分差2.73（上限的98%）；三家均为整百元、平均值正好590,000无进位误差；价差1,100元较自然",
    ),
    Plan(
        "方案四 自然价差型",
        "两家596,800 / 599,900",
        "596800",
        "599900",
        "备选",
        "价差3,100元最自然；代价是分差少0.11分、我方少收约478元",
    ),
    Plan(
        "方案五 宽价差型",
        "两家592,000 / 598,000",
        "592000",
        "598000",
        "备选",
        "价差6,000元，外观最像独立报价；分差降至2.47，我方少收约3,679元",
    ),
    Plan(
        "方案六 低价拉差型",
        "两家452,270 / 599,400",
        "452270",
        "599400",
        "不建议",
        "分差3.00为全场最高，但我方少收约6.98万元；45万报价易被质疑异常低价或串标",
    ),
    Plan(
        "对照A 两家同价顶格",
        "两家均报600,000",
        "600000",
        "600000",
        "不符合要求",
        "理论最优解，但违反「两家不得同价、不报正好60万」的约束",
    ),
    Plan(
        "对照B 三家均顶格",
        "三家均报600,000",
        "600000",
        "600000",
        "不符合要求",
        "三家并列38.14分，我方无任何分差优势",
        our_price="600000",
    ),
]
RECOMMENDED = PLANS[2]

wb = Workbook()
ws = wb.active
ws.title = "投标行动清单"

headers = ["待办项", "待办子项", "责任人", "状态", "备注"]

rows = [
    # 一、证明材料
    ["一、投标证明扫描件/复印件", "营业执照扫描件/复印件", "", "待办", "需清晰完整"],
    ["一、投标证明扫描件/复印件", "法人身份证扫描件/复印件", "", "待办", "正反面齐全、清晰"],
    ["一、投标证明扫描件/复印件", "授权委托书", "", "待办", "按招标要求格式签署盖章"],
    ["一、投标证明扫描件/复印件", "投标函", "", "待办", "按招标文件要求填写签署"],
    [
        "一、投标证明扫描件/复印件",
        "（原清单第5项待明确）",
        "",
        "待补充",
        "原文第5项为空，需对照招标文件补全",
    ],
    [
        "一、投标证明扫描件/复印件",
        "增值税专用发票相关承诺书",
        "",
        "待办",
        "提供承诺书（替代直接提供专票）",
    ],
    [
        "一、投标证明扫描件/复印件",
        "2023年1月1日以来软件开发项目案例（≥20万元）—合同关键页",
        "",
        "待办",
        "含首页、服务内容页、金额页、签字盖章页等；框架协议业绩以2023-01-01后订单为准",
    ],
    [
        "一、投标证明扫描件/复印件",
        "上述案例对应结算发票扫描件",
        "",
        "待办",
        "业绩金额以结算发票金额为准",
    ],
    [
        "一、投标证明扫描件/复印件",
        "发票网上税务系统查验截图",
        "",
        "待办",
        "查验地址：https://inv-veri.chinatax.gov.cn/；证明材料须清晰完整",
    ],
    ["一、投标证明扫描件/复印件", "公司信用报告", "", "待办", ""],
    # 二、招投标网站
    ["二、招投标网站所需材料", "企业授权书", "", "待办", "有模板，需盖公章"],
    ["二、招投标网站所需材料", "CA证书购买", "", "已完成", "已购买，费用400元"],
    # 三（一）报价评分
    [
        "三、评标评分 /（一）报价评分",
        "测算评标基准价与报价策略（三人配合）",
        "",
        "待办",
        "最高限价60万元（含税）。评标基准价=有效应答人报价算术平均值×97%；"
        "≥10家去2高2低，5–9家去1高1低，3家不去极值；"
        "等于基准价得40分；高于每偏差1%扣0.6分，低于每偏差1%扣0.3分；四舍五入保留两位小数，最低0分。"
        f"推荐（含税）：我方{fmt(RECOMMENDED.p1)}元得{RECOMMENDED.s1}分，"
        f"配合两家{fmt(RECOMMENDED.p2)}元（{RECOMMENDED.s2}分）、{fmt(RECOMMENDED.p3)}元（{RECOMMENDED.s3}分），"
        f"分差+{RECOMMENDED.gap}分。详见「报价策略测算」页。",
    ],
    [
        "三、评标评分 /（一）报价评分",
        "从备选报价方案中选定最终方案",
        "",
        "待办",
        f"「报价方案备选」页共列{len(PLANS)}套方案（含2套仅作对照）。"
        "需在分差、我方收入、报价外观自然度之间拍板；建议默认采用方案三。",
    ],
    [
        "三、评标评分 /（一）报价评分",
        "与两家配合投标人同步报价口径",
        "",
        "待办",
        "确认两家按选定方案报价；三家标书须独立编制，避免排版雷同、报价连号，价差建议≥1,000元",
    ],
    # 三（二）企业综合实力
    [
        "三、评标评分 /（二）企业综合实力",
        "软件企业证书、软件产品证书（双软）有效登记证书扫描件",
        "",
        "待办",
        "双软证书，提供有效登记证书扫描件",
    ],
    [
        "三、评标评分 /（二）企业综合实力",
        "2023年以来合同金额≥20万元软件开发项目业绩（最多5份）",
        "",
        "待办",
        "每份2分，最多10分；合同/框架协议关键页+结算发票；与第一部分案例材料可统筹准备",
    ],
    [
        "三、评标评分 /（二）企业综合实力",
        "制造业相关领域计算机软件著作权登记证书（最多9份）",
        "",
        "待办",
        "每份1分，最多9分；提供有效软著登记证书扫描件",
    ],
    [
        "三、评标评分 /（二）企业综合实力",
        "项目团队完整配备：项目负责人、架构师、前后端开发工程师、测试工程师、UI设计交互工程师",
        "",
        "待办",
        "全岗位得2分；缺1个核心岗位扣1分；以上人员均须提供与投标人签署的劳务合同，否则不得分",
    ],
    [
        "三、评标评分 /（二）企业综合实力",
        "上述项目团队成员劳务合同扫描件",
        "",
        "待办",
        "评分硬性条件：无劳务合同不得分",
    ],
    # 三（三）技术评分
    [
        "三、评标评分 /（三）技术评分",
        "项目技术需求理解及综合分析（设计蓝图）",
        "",
        "待办",
        "内容详实科学、合理、全面、针对性强，设计蓝图完整，可得7–9分",
    ],
    [
        "三、评标评分 /（三）技术评分",
        "演示系统功能准备",
        "",
        "待办",
        "覆盖产销、制造、供应、采购、物料仓储、生产执行、成品仓等核心场景，"
        "以及智能诊断、业务闭环处理、数据分析看板实际效果",
    ],
    [
        "三、评标评分 /（三）技术评分",
        "各子系统技术方案",
        "",
        "待办",
        "含功能模块设计、核心业务流程、技术架构、智能诊断集成架构、数据架构、接口设计等",
    ],
    [
        "三、评标评分 /（三）技术评分",
        "智能诊断模型设计、业务闭环自动化策略、数据分析建模技术方案 + 智能诊断Demo",
        "",
        "待办",
        "现场提供智能诊断 Demo 优先",
    ],
    [
        "三、评标评分 /（三）技术评分",
        "测试策略与验收方案",
        "",
        "待办",
        "含业务场景覆盖、诊断模型验证、用例设计、缺陷管理、验收标准；完整可得3分",
    ],
    # 三（四）实施方案
    [
        "三、评标评分 /（四）实施方案",
        "质量与工期保障措施方案",
        "",
        "待办",
        "质量控制点充分清晰、保障措施针对可行，明确承诺后续质量保障和服务，可得2分",
    ],
    [
        "三、评标评分 /（四）实施方案",
        "应急响应、漏洞修复与安全保障措施方案",
        "",
        "待办",
        "方案完善、保障有力、响应机制明确，可得2分",
    ],
    [
        "三、评标评分 /（四）实施方案",
        "售后服务、技术支持、响应时效及诊断模型后续优化服务承诺",
        "",
        "待办",
        "体系完善、含模型迭代优化承诺、响应快程度高，可得3分",
    ],
    [
        "三、评标评分 /（四）实施方案",
        "培训计划（管理员/操作员/智能诊断与数据分析专项/培训手册）",
        "",
        "待办",
        "计划针对性强、完善合理、满足采购人需求为优，可得3分",
    ],
]

header_fill = PatternFill("solid", fgColor="1F4E79")
header_font = Font(name="微软雅黑", bold=True, color="FFFFFF", size=11)
section_fills = {
    "一、": PatternFill("solid", fgColor="D6EAF8"),
    "二、": PatternFill("solid", fgColor="D5F5E3"),
    "三、评标评分 /（一）": PatternFill("solid", fgColor="FCF3CF"),
    "三、评标评分 /（二）": PatternFill("solid", fgColor="FADBD8"),
    "三、评标评分 /（三）": PatternFill("solid", fgColor="E8DAEF"),
    "三、评标评分 /（四）": PatternFill("solid", fgColor="D6DBDF"),
}
status_fills = {
    "待办": PatternFill("solid", fgColor="FFF2CC"),
    "已完成": PatternFill("solid", fgColor="C6EFCE"),
    "待补充": PatternFill("solid", fgColor="F4B183"),
    "进行中": PatternFill("solid", fgColor="BDD7EE"),
}
thin = Border(
    left=Side(style="thin", color="B0B0B0"),
    right=Side(style="thin", color="B0B0B0"),
    top=Side(style="thin", color="B0B0B0"),
    bottom=Side(style="thin", color="B0B0B0"),
)
wrap = Alignment(wrap_text=True, vertical="center", horizontal="left")
center = Alignment(wrap_text=True, vertical="center", horizontal="center")

ws.append(headers)
for col in range(1, 6):
    cell = ws.cell(1, col)
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = Alignment(wrap_text=True, vertical="center", horizontal="center")
    cell.border = thin

ws.row_dimensions[1].height = 28
ws.freeze_panes = "A2"
ws.auto_filter.ref = f"A1:E{len(rows) + 1}"

for i, r in enumerate(rows, start=2):
    ws.append(r)
    fill = None
    for prefix, pf in section_fills.items():
        if r[0].startswith(prefix):
            fill = pf
            break
    for col in range(1, 6):
        cell = ws.cell(i, col)
        cell.font = Font(name="微软雅黑", size=10)
        cell.border = thin
        cell.alignment = wrap if col in (1, 2, 5) else center
        if col == 1 and fill:
            cell.fill = fill
        if col == 4 and r[3] in status_fills:
            cell.fill = status_fills[r[3]]
    remark_len = len(r[4] or "")
    ws.row_dimensions[i].height = max(36, min(90, 22 + remark_len // 28 * 16))

widths = [36, 52, 12, 10, 55]
for i, w in enumerate(widths, 1):
    ws.column_dimensions[get_column_letter(i)].width = w

dv = DataValidation(
    type="list",
    formula1='"待办,进行中,已完成,待补充,不适用"',
    allow_blank=True,
)
dv.error = "请选择预置状态"
dv.errorTitle = "状态"
ws.add_data_validation(dv)
dv.add(f"D2:D{len(rows) + 1}")

# Sheet 2: scoring reference
ws2 = wb.create_sheet("评标分值速查")
ws2_headers = ["评分大类", "评分项", "满分/分值区间", "得分要点"]
ws2_rows = [
    [
        "（一）报价评分",
        "报价得分",
        "40分",
        "等于评标基准价得40分；高于基准价每偏1%扣0.6分；低于基准价每偏1%扣0.3分；最低0分",
    ],
    [
        "（一）报价评分",
        "评标基准价",
        "—",
        "有效报价算术平均×97%；≥10家去2高2低；5–9家去1高1低；3家不去极值",
    ],
    [
        "（一）报价评分",
        "项目最高限价",
        "60万元（含税）",
        "超过限价视为无效报价；税率按6%测算不含税金额",
    ],
    [
        "（一）报价评分",
        "三人配合最优策略（满分+分差最大）",
        "我方40分",
        f"含税：我方{fmt(RECOMMENDED.p1)} / 配合方{fmt(RECOMMENDED.p2)}（{RECOMMENDED.s2}）"
        f"/ 配合方{fmt(RECOMMENDED.p3)}（{RECOMMENDED.s3}）；分差+{RECOMMENDED.gap}，"
        "接近结构上限2.78。备选方案见「报价方案备选」页",
    ],
    ["（二）企业综合实力", "双软证书", "见招标文件", "软件企业证书+软件产品证书有效扫描件"],
    [
        "（二）企业综合实力",
        "软件开发业绩（≥20万，2023年以来）",
        "最多10分",
        "每份2分，最多5份；合同关键页+结算发票",
    ],
    ["（二）企业综合实力", "制造业相关软著", "最多9分", "每份1分，最多9份"],
    [
        "（二）企业综合实力",
        "项目团队岗位完整性",
        "2分",
        "全岗位2分；缺1岗位扣1分；须有劳务合同",
    ],
    ["（三）技术评分", "需求理解与设计蓝图", "7–9分", "详实科学、合理全面、针对性强、蓝图完整"],
    [
        "（三）技术评分",
        "演示系统功能",
        "见招标文件",
        "产销/制造/供应/采购/仓储/执行/成品仓+智能诊断/闭环/看板",
    ],
    ["（三）技术评分", "子系统技术方案", "见招标文件", "功能/流程/架构/诊断集成/数据/接口"],
    ["（三）技术评分", "智能诊断与数据分析方案", "见招标文件", "现场提供智能诊断Demo优先"],
    ["（三）技术评分", "测试与验收方案", "3分", "场景全面、模型验证可行、验收标准明确"],
    ["（四）实施方案", "质量与工期保障", "2分", "质控点充分、措施可行、承诺后续保障"],
    ["（四）实施方案", "应急与安全保障", "2分", "响应机制明确、保障有力"],
    ["（四）实施方案", "售后与模型优化服务", "3分", "含模型迭代优化承诺、响应快"],
    ["（四）实施方案", "培训计划", "3分", "管理员/操作员/诊断与分析专项/手册"],
]

for col, h in enumerate(ws2_headers, 1):
    cell = ws2.cell(1, col, h)
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = Alignment(wrap_text=True, vertical="center", horizontal="center")
    cell.border = thin
ws2.row_dimensions[1].height = 28
ws2.freeze_panes = "A2"
for i, r in enumerate(ws2_rows, start=2):
    for col, val in enumerate(r, 1):
        cell = ws2.cell(i, col, val)
        cell.font = Font(name="微软雅黑", size=10)
        cell.border = thin
        cell.alignment = wrap
    ws2.row_dimensions[i].height = 40
for i, w in enumerate([22, 32, 16, 60], 1):
    ws2.column_dimensions[get_column_letter(i)].width = w

# Sheet 3: pricing strategy
ws3 = wb.create_sheet("报价策略测算")
ws3_title_font = Font(name="微软雅黑", bold=True, size=14, color="1F4E79")
ws3_label_font = Font(name="微软雅黑", bold=True, size=10)
ws3_body_font = Font(name="微软雅黑", size=10)
highlight = PatternFill("solid", fgColor="C6EFCE")
warn = PatternFill("solid", fgColor="FFF2CC")

ws3["A1"] = "报价策略测算（三人配合｜我方满分 + 分差最大化）"
ws3["A1"].font = ws3_title_font
ws3.merge_cells("A1:F1")

assumptions = [
    ("前提/约束", "内容"),
    ("有效投标人", "3家（含我方）；不去最高/最低极值，直接算术平均"),
    ("最高限价", "600,000.00 元（含税）"),
    ("税率", "6%（增值税）"),
    ("评标基准价", "三家报价算术平均值 × 97%"),
    ("得分规则", "等于基准价40分；高于每偏1%扣0.6分；低于每偏1%扣0.3分；四舍五入两位小数，最低0分"),
    (
        "优化目标",
        "①我方报价分取满分40分；②与另外两家分差最大化；"
        "③两家配合报价不得相同、且不报正好600,000元；④我方报价尽量高",
    ),
]
for r_idx, row in enumerate(assumptions, start=3):
    for c_idx, val in enumerate(row, start=1):
        cell = ws3.cell(r_idx, c_idx, val)
        cell.font = ws3_label_font if r_idx == 3 or c_idx == 1 else ws3_body_font
        cell.border = thin
        cell.alignment = wrap
        if r_idx == 3:
            cell.fill = header_fill
            cell.font = header_font

ws3["A11"] = "推荐报价方案（最优）"
ws3["A11"].font = ws3_title_font
ws3.merge_cells("A11:F11")

R = RECOMMENDED
rec_headers = ["角色", "含税报价(元)", "不含税报价(元)", "税额(元)", "报价得分", "说明"]
rec_rows = [
    [
        "我方（中标意向）",
        fmt(R.p1),
        fmt(ex_tax(R.p1)),
        fmt(R.p1 - ex_tax(R.p1)),
        str(R.s1),
        "恰好等于评标基准价，拿满分40分；三家合计1,770,000，平均590,000，×97%正好整数，无进位误差",
    ],
    [
        "配合投标人A",
        fmt(R.p2),
        fmt(ex_tax(R.p2)),
        fmt(R.p2 - ex_tax(R.p2)),
        str(R.s2),
        "高于基准价4.543%，扣2.73分；两家中报价较低者，决定我方分差上限",
    ],
    [
        "配合投标人B",
        fmt(R.p3),
        fmt(ex_tax(R.p3)),
        fmt(R.p3 - ex_tax(R.p3)),
        str(R.s3),
        "高于基准价4.735%，扣2.84分；与A错开1,100元，避免同价",
    ],
    [
        "我方分差",
        "—",
        "—",
        "—",
        f"+{R.gap}",
        f"{R.s1} − {max(R.s2, R.s3)}（对分数更高的配合方A）；已接近结构上限2.78",
    ],
]
for c_idx, h in enumerate(rec_headers, start=1):
    cell = ws3.cell(12, c_idx, h)
    cell.fill = header_fill
    cell.font = header_font
    cell.border = thin
    cell.alignment = center
for r_idx, row in enumerate(rec_rows, start=13):
    for c_idx, val in enumerate(row, start=1):
        cell = ws3.cell(r_idx, c_idx, val)
        cell.font = ws3_body_font
        cell.border = thin
        cell.alignment = wrap if c_idx == 6 else center
        if r_idx == 13:
            cell.fill = highlight

calc_headers = ["测算项", "数值", "计算说明"]
calc_rows = [
    ["三家报价合计", "1,770,000.00", "572,300 + 598,300 + 599,400"],
    ["算术平均值", "590,000.00", "合计 ÷ 3（整数，无小数误差）"],
    ["评标基准价", "572,300.00", "590,000 × 97%"],
    ["我方相对基准偏差", "0%", "报价=基准价 → 40.00分（满分，不可能更高）"],
    ["配合方A偏差/扣分", "+4.543% / 2.73分", "(598300−572300)/572300 × 0.6"],
    ["配合方B偏差/扣分", "+4.735% / 2.84分", "(599400−572300)/572300 × 0.6"],
    ["我方分差（对A）", "+2.73分", "40.00 − 37.27，A为两家中得分较高者"],
    ["分差结构上限", "2.78分", "两家同价顶格时的极限值；本方案已达其98%"],
]
ws3["A18"] = "关键测算过程"
ws3["A18"].font = ws3_title_font
for c_idx, h in enumerate(calc_headers, start=1):
    cell = ws3.cell(19, c_idx, h)
    cell.fill = header_fill
    cell.font = header_font
    cell.border = thin
    cell.alignment = center
for r_idx, row in enumerate(calc_rows, start=20):
    for c_idx, val in enumerate(row, start=1):
        cell = ws3.cell(r_idx, c_idx, val)
        cell.font = ws3_body_font
        cell.border = thin
        cell.alignment = wrap

ws3["A29"] = "结论与注意"
ws3["A29"].font = ws3_title_font
conclusions = [
    "1. 我方报价得分封顶40分：只要报价等于评标基准价即拿满分，不存在「更高分」，因此优化目标只能是「与另外两家的分差」。",
    "2. 分差存在结构上限2.78分：两家配合方报价的平均值恒为基准价的1.0464倍，"
    "即两家平均偏高4.639%，其中报价较低者最多被扣2.78分。两家同价时才取到该上限。",
    "3. 推荐方案在「两家不同价、不报正好60万」约束下取得+2.73分差（上限的98%），"
    "同时我方含税价572,300元、不含税539,905.66元，兼顾分差与收入。",
    "4. 想把分差做到2.78以上，必须让一家报到基准价以下很多（如45.2万），"
    "我方基准价随之被拉低到50.25万，少收约6.98万元只换0.27分，性价比极低且风险高。",
    "5. 三家报价须由不同主体独立编制、避免雷同排版与连号，报价差建议保持在1,000元以上。",
    "6. 全部备选报价方案（含2套仅作对照的不合规方案）见「报价方案备选」页，可直接选用。",
    "7. 若实际有效应答人不是3家，本测算全部作废，须按新家数重算（届时还涉及去极值规则）。",
    "8. 请以招标文件对「报价含税口径」「基准价小数位」的最终约定为准，正式填报前再核对一次。",
]
for i, line in enumerate(conclusions, start=30):
    ws3[f"A{i}"] = line
    ws3[f"A{i}"].font = ws3_body_font
    ws3[f"A{i}"].fill = warn if i in (30, 31) else PatternFill()
    ws3.merge_cells(f"A{i}:H{i}")

for i, w in enumerate([34, 16, 22, 16, 12, 18, 12, 46], 1):
    ws3.column_dimensions[get_column_letter(i)].width = w
ws3.row_dimensions[1].height = 24
for r in list(range(3, 11)) + list(range(13, 17)):
    ws3.row_dimensions[r].height = 32
for r in range(30, 38):
    ws3.row_dimensions[r].height = 30

# Sheet 4: candidate pricing plans
ws5 = wb.create_sheet("报价方案备选")
ws5["A1"] = "报价方案备选（三家配合｜含税口径，税率6%）"
ws5["A1"].font = ws3_title_font
ws5.merge_cells("A1:K1")
ws5["A2"] = (
    "我方报价分封顶40分，各方案我方均等于评标基准价拿满分，差别只在「与另外两家的分差」"
    "与「我方能收多少钱」。请在「选定」列用 ○ 标记最终采用的方案。"
)
ws5["A2"].font = ws3_body_font
ws5["A2"].alignment = wrap
ws5.merge_cells("A2:K2")

plan_headers = [
    "选定",
    "方案",
    "方案要点",
    "角色",
    "含税报价(元)",
    "不含税报价(元)",
    "税额(元)",
    "报价得分",
    "我方分差",
    "推荐度",
    "说明",
]
for c_idx, h in enumerate(plan_headers, start=1):
    cell = ws5.cell(4, c_idx, h)
    cell.fill = header_fill
    cell.font = header_font
    cell.border = thin
    cell.alignment = center

verdict_fills = {
    "★推荐": PatternFill("solid", fgColor="C6EFCE"),
    "备选": PatternFill("solid", fgColor="FFF2CC"),
    "不建议": PatternFill("solid", fgColor="F8CBAD"),
    "不符合要求": PatternFill("solid", fgColor="D9D9D9"),
}

row_ptr = 5
plan_blocks = []
for plan in PLANS:
    start = row_ptr
    for role, price, sc in (
        ("我方", plan.p1, plan.s1),
        ("配合方A", plan.p2, plan.s2),
        ("配合方B", plan.p3, plan.s3),
    ):
        ex = ex_tax(price)
        values = {
            4: role,
            5: fmt(price),
            6: fmt(ex),
            7: fmt(price - ex),
            8: str(sc),
        }
        for c_idx in range(1, 12):
            cell = ws5.cell(row_ptr, c_idx, values.get(c_idx))
            cell.font = ws3_body_font
            cell.border = thin
            cell.alignment = wrap if c_idx in (3, 11) else center
        ws5.row_dimensions[row_ptr].height = 20
        row_ptr += 1
    end = row_ptr - 1
    plan_blocks.append((plan, start, end))
    for c_idx, val in ((1, ""), (2, plan.key), (3, plan.name), (9, f"+{plan.gap}"),
                       (10, plan.verdict), (11, plan.comment)):
        ws5.merge_cells(start_row=start, start_column=c_idx, end_row=end, end_column=c_idx)
        cell = ws5.cell(start, c_idx, val)
        cell.font = (
            Font(name="微软雅黑", size=10, bold=True)
            if c_idx in (2, 9, 10)
            else ws3_body_font
        )
        cell.alignment = wrap if c_idx in (3, 11) else center
        if plan.verdict in verdict_fills:
            cell.fill = verdict_fills[plan.verdict]

dv_pick = DataValidation(type="list", formula1='"○,—"', allow_blank=True)
ws5.add_data_validation(dv_pick)
dv_pick.add(f"A5:A{row_ptr - 1}")

summary_start = row_ptr + 1
ws5.cell(summary_start, 1, "方案速览（按分差排序）").font = ws3_title_font
ws5.merge_cells(start_row=summary_start, start_column=1, end_row=summary_start, end_column=11)
sum_headers = ["方案", "我方含税价", "配合两家报价", "我方分差", "与推荐方案比：我方收入差", "推荐度"]
for c_idx, h in enumerate(sum_headers, start=1):
    cell = ws5.cell(summary_start + 1, c_idx, h)
    cell.fill = header_fill
    cell.font = header_font
    cell.border = thin
    cell.alignment = center
for i, plan in enumerate(sorted(PLANS, key=lambda p: (-p.gap, -p.p1)), start=summary_start + 2):
    diff = plan.p1 - RECOMMENDED.p1
    diff_txt = "基准（推荐方案）" if plan is RECOMMENDED else f"{'+' if diff >= 0 else '−'}{fmt(abs(diff))}"
    for c_idx, val in enumerate(
        [
            plan.key,
            fmt(plan.p1),
            f"{fmt(plan.p2)} / {fmt(plan.p3)}",
            f"+{plan.gap}",
            diff_txt,
            plan.verdict,
        ],
        start=1,
    ):
        cell = ws5.cell(i, c_idx, val)
        cell.font = ws3_body_font
        cell.border = thin
        cell.alignment = center
        if plan.verdict in verdict_fills:
            cell.fill = verdict_fills[plan.verdict]

for i, w in enumerate([8, 22, 26, 12, 16, 16, 13, 11, 11, 14, 46], 1):
    ws5.column_dimensions[get_column_letter(i)].width = w
ws5.row_dimensions[1].height = 24
ws5.row_dimensions[2].height = 32
ws5.freeze_panes = "A5"

# Sheet 5: live score calculator (Excel formulas, recalculates on input)
ws6 = wb.create_sheet("报价得分计算器")
input_fill = PatternFill("solid", fgColor="FFF2CC")
param_fill = PatternFill("solid", fgColor="DDEBF7")
result_fill = PatternFill("solid", fgColor="E2EFDA")
bold10 = Font(name="微软雅黑", size=10, bold=True)
body10 = Font(name="微软雅黑", size=10)

ws6["A1"] = "报价得分计算器（改动黄色单元格，得分与分差自动重算）"
ws6["A1"].font = ws3_title_font
ws6.merge_cells("A1:H1")
ws6["A2"] = (
    "适用：有效应答人3家、不去极值。修改「参数」或「三家含税报价」后，"
    "评标基准价、各家扣分与得分、我方分差、以及「我方拿满分应报多少」全部自动更新。"
)
ws6["A2"].font = body10
ws6["A2"].alignment = wrap
ws6.merge_cells("A2:H2")


def section(row, text):
    cell = ws6.cell(row, 1, text)
    cell.font = Font(name="微软雅黑", size=11, bold=True, color="1F4E79")
    ws6.merge_cells(start_row=row, start_column=1, end_row=row, end_column=8)


def kv(row, label, value, fill=None, num_fmt=None, note=None):
    lab = ws6.cell(row, 1, label)
    lab.font = bold10
    lab.border = thin
    lab.alignment = wrap
    val = ws6.cell(row, 2, value)
    val.font = body10
    val.border = thin
    val.alignment = center
    if fill:
        val.fill = fill
    if num_fmt:
        val.number_format = num_fmt
    if note:
        cell = ws6.cell(row, 3, note)
        cell.font = body10
        cell.alignment = wrap
        ws6.merge_cells(start_row=row, start_column=3, end_row=row, end_column=8)


section(4, "参数（一般无需改动）")
kv(5, "最高限价（含税，元）", 600000, param_fill, "#,##0.00", "超过即为无效报价")
kv(6, "增值税率", 0.06, param_fill, "0.0%", "用于换算不含税金额")
kv(7, "评标基准价系数", 0.97, param_fill, "0%", "基准价 = 三家算术平均值 × 该系数")
kv(8, "高于基准每偏1%扣分", 0.6, param_fill, "0.0", None)
kv(9, "低于基准每偏1%扣分", 0.3, param_fill, "0.0", None)
kv(10, "报价满分", 40, param_fill, "0", "报价=基准价时的得分，封顶值")

section(12, "① 输入三家含税报价（仅需改黄色单元格）")
input_headers = [
    "角色",
    "含税报价(元)",
    "不含税(元)",
    "税额(元)",
    "相对基准偏差",
    "扣分",
    "报价得分",
    "校验",
]
for c_idx, h in enumerate(input_headers, start=1):
    cell = ws6.cell(13, c_idx, h)
    cell.fill = header_fill
    cell.font = header_font
    cell.border = thin
    cell.alignment = center

for offset, (role, default) in enumerate(
    [("我方", 572300), ("配合方A", 598300), ("配合方B", 599400)]
):
    r = 14 + offset
    ws6.cell(r, 1, role).font = bold10
    ws6.cell(r, 2, default).fill = input_fill
    ws6.cell(r, 2).font = Font(name="微软雅黑", size=10, bold=True, color="C00000")
    ws6.cell(r, 3, f'=IF(B{r}="","",ROUND(B{r}/(1+$B$6),2))')
    ws6.cell(r, 4, f'=IF(B{r}="","",ROUND(B{r}-C{r},2))')
    ws6.cell(r, 5, f'=IF($B$21=0,"",(B{r}-$B$21)/$B$21)')
    ws6.cell(
        r,
        6,
        f'=IF($B$21=0,"",ROUND(IF(B{r}>$B$21,(B{r}-$B$21)/$B$21*100*$B$8,'
        f'($B$21-B{r})/$B$21*100*$B$9),2))',
    )
    ws6.cell(r, 7, f'=IF($B$21=0,"",MAX(0,ROUND($B$10-F{r},2)))')
    ws6.cell(
        r,
        8,
        f'=IF(B{r}>$B$5,"⚠ 超最高限价，报价无效",'
        f'IF(B{r}=$B$5,"△ 正好等于最高限价","✔ 正常"))',
    )
    for c_idx in range(1, 9):
        cell = ws6.cell(r, c_idx)
        cell.border = thin
        cell.alignment = center
        if c_idx not in (1, 2):
            cell.font = body10
    for c_idx in (2, 3, 4):
        ws6.cell(r, c_idx).number_format = "#,##0.00"
    ws6.cell(r, 5).number_format = "0.000%"
    ws6.cell(r, 6).number_format = "0.00"
    ws6.cell(r, 7).number_format = "0.00"
    ws6.cell(r, 7).font = bold10

section(18, "② 自动测算结果")
for row, label, formula, fmt_ in [
    (19, "三家报价合计（含税）", "=SUM(B14:B16)", "#,##0.00"),
    (20, "算术平均值", "=B19/3", "#,##0.00"),
    (21, "评标基准价", "=ROUND(B20*$B$7,2)", "#,##0.00"),
    (22, "我方报价得分", "=G14", "0.00"),
    (23, "配合两家中最高得分", "=MAX(G15:G16)", "0.00"),
    (24, "我方分差（对最强者）", "=ROUND(B22-B23,2)", "0.00"),
    (25, "我方报价分排名", "=RANK(G14,$G$14:$G$16)", "0"),
]:
    kv(row, label, formula, result_fill, fmt_)
ws6.cell(22, 2).font = bold10
ws6.cell(24, 2).font = Font(name="微软雅黑", size=11, bold=True, color="C00000")

section(27, "③ 合规与外观校验")
checks = [
    (
        28,
        "两家是否同价",
        '=IF(B15=B16,"⚠ 配合两家报价相同，不满足「不得同价」要求","✔ 配合两家报价不同")',
    ),
    (
        29,
        "是否有人正好报限价",
        '=IF(COUNTIF(B14:B16,$B$5)>0,"⚠ 存在正好等于最高限价的报价","✔ 无人正好报最高限价")',
    ),
    (
        30,
        "是否存在超限价",
        '=IF(MAX(B14:B16)>$B$5,"⚠ 存在超过最高限价的无效报价","✔ 全部未超最高限价")',
    ),
    (
        31,
        "我方是否踩准基准价",
        '=IF(B14=$B$21,"✔ 我方报价=评标基准价，已拿满分","△ 我方未踩准基准价，'
        '当前得分"&TEXT(G14,"0.00")&"分；应改报 "&TEXT($B$35,"#,##0.00")&" 元")',
    ),
    (
        32,
        "配合两家价差",
        '=TEXT(ABS(B15-B16),"#,##0")&" 元"&IF(ABS(B15-B16)<1000,'
        '"（偏小，建议≥1,000元以降低配合痕迹）","（较自然）")',
    ),
]
for row, label, formula in checks:
    lab = ws6.cell(row, 1, label)
    lab.font = bold10
    lab.border = thin
    lab.alignment = wrap
    cell = ws6.cell(row, 2, formula)
    cell.font = body10
    cell.border = thin
    cell.alignment = wrap
    ws6.merge_cells(start_row=row, start_column=2, end_row=row, end_column=8)

section(34, "④ 反算：按上方两家报价，我方拿满分应报多少")
for row, label, formula, fmt_, note in [
    (
        35,
        "我方最优含税报价",
        "=ROUND($B$7*(B15+B16)/(3-$B$7),2)",
        "#,##0.00",
        "使我方报价恰好等于评标基准价，拿满分；把它填回上方 B14 即可",
    ),
    (36, "对应不含税金额", "=ROUND(B35/(1+$B$6),2)", "#,##0.00", None),
    (37, "该报价下评标基准价", "=ROUND((B35+B15+B16)/3*$B$7,2)", "#,##0.00", None),
    (
        38,
        "该报价下配合方A得分",
        "=MAX(0,ROUND($B$10-IF(B15>$B$37,(B15-$B$37)/$B$37*100*$B$8,"
        "($B$37-B15)/$B$37*100*$B$9),2))",
        "0.00",
        None,
    ),
    (
        39,
        "该报价下配合方B得分",
        "=MAX(0,ROUND($B$10-IF(B16>$B$37,(B16-$B$37)/$B$37*100*$B$8,"
        "($B$37-B16)/$B$37*100*$B$9),2))",
        "0.00",
        None,
    ),
    (40, "该报价下我方分差", "=ROUND($B$10-MAX(B38:B39),2)", "0.00", "结构上限为 2.78 分"),
]:
    kv(row, label, formula, result_fill, fmt_, note)
ws6.cell(35, 2).font = Font(name="微软雅黑", size=11, bold=True, color="C00000")

section(42, "⑤ 价差敏感度（配合方B固定为上方 B16，配合方A按不同价差下探）")
sens_headers = [
    "两家价差(元)",
    "配合方A报价",
    "我方最优报价",
    "评标基准价",
    "A得分",
    "B得分",
    "我方分差",
]
for c_idx, h in enumerate(sens_headers, start=1):
    cell = ws6.cell(43, c_idx, h)
    cell.fill = header_fill
    cell.font = header_font
    cell.border = thin
    cell.alignment = center
for i, spread in enumerate([0, 100, 500, 1000, 2000, 3000, 5000, 8000, 12000]):
    r = 44 + i
    ws6.cell(r, 1, spread)
    ws6.cell(r, 2, f"=$B$16-A{r}")
    ws6.cell(r, 3, f"=ROUND($B$7*(B{r}+$B$16)/(3-$B$7),2)")
    ws6.cell(r, 4, f"=ROUND((C{r}+B{r}+$B$16)/3*$B$7,2)")
    ws6.cell(
        r,
        5,
        f"=MAX(0,ROUND($B$10-IF(B{r}>D{r},(B{r}-D{r})/D{r}*100*$B$8,"
        f"(D{r}-B{r})/D{r}*100*$B$9),2))",
    )
    ws6.cell(
        r,
        6,
        f"=MAX(0,ROUND($B$10-IF($B$16>D{r},($B$16-D{r})/D{r}*100*$B$8,"
        f"(D{r}-$B$16)/D{r}*100*$B$9),2))",
    )
    ws6.cell(r, 7, f"=ROUND($B$10-MAX(E{r}:F{r}),2)")
    for c_idx in range(1, 8):
        cell = ws6.cell(r, c_idx)
        cell.font = body10
        cell.border = thin
        cell.alignment = center
        cell.number_format = (
            "#,##0" if c_idx == 1 else ("#,##0.00" if c_idx <= 4 else "0.00")
        )
    ws6.cell(r, 7).font = bold10

ws6.cell(54, 1, "说明：价差为0时分差取到结构上限2.78分，但两家同价不符合要求；价差越大报价越自然、分差越小。")
ws6.cell(54, 1).font = body10
ws6.cell(54, 1).alignment = wrap
ws6.merge_cells("A54:G54")

for i, w in enumerate([26, 18, 18, 16, 16, 12, 14, 30], 1):
    ws6.column_dimensions[get_column_letter(i)].width = w
ws6.row_dimensions[1].height = 24
ws6.row_dimensions[2].height = 30
for r in (28, 29, 30, 31, 32, 54):
    ws6.row_dimensions[r].height = 26
ws6.row_dimensions[35].height = 30

# Sheet 6: instructions
ws4 = wb.create_sheet("使用说明")
notes = [
    "使用说明",
    "1. 主表「投标行动清单」按招标材料准备与评标得分项拆成可执行待办；责任人请按实际分工填写。",
    "2. 「状态」可下拉选择：待办 / 进行中 / 已完成 / 待补充 / 不适用。",
    "3. 原文「一、」第5项为空，已单列「待补充」，请对照正式招标文件补齐。",
    "4. 「一、」业绩案例与「三（二）」业绩加分材料可统筹准备，建议一次收集最多5份合格业绩以冲满分。",
    "5. 「评标分值速查」页便于对照分值；「报价策略测算」页给出推荐报价的完整推导。",
    "6. 「报价方案备选」页列出全部候选报价方案，每套按我方/配合方A/配合方B三行展开，"
    "并给出含税、不含税、税额、得分与分差；确定后在「选定」列标 ○。",
    "7. 「报价得分计算器」页为实时公式表：改动黄色单元格（三家含税报价）后，"
    "评标基准价、各家扣分与得分、我方分差、我方最优报价、价差敏感度全部自动重算。",
    "8. 报价测算前提：有效应答人3家、最高限价60万元（含税）、税率6%；家数变化须重算。",
    "9. 生成日期：2026-08-02；项目：AIDOP 投标材料准备。",
]
ws4["A1"] = notes[0]
ws4["A1"].font = Font(name="微软雅黑", bold=True, size=14, color="1F4E79")
for i, line in enumerate(notes[1:], start=3):
    ws4[f"A{i}"] = line
    ws4[f"A{i}"].font = Font(name="微软雅黑", size=11)
    ws4[f"A{i}"].alignment = wrap
ws4.column_dimensions["A"].width = 110

out = r"d:\Projects\Ai-DOP\SourceCode\ZZYDOP\doc\AIDOP投标材料准备行动清单.xlsx"
wb.save(out)
print(f"saved: {out}")
print(f"rows: {len(rows)}")