#!/usr/bin/env python3 """Apply a conservative second-round phone enrichment to Yifangbao workbooks.""" from __future__ import annotations import argparse import re from copy import copy from pathlib import Path from openpyxl import load_workbook from openpyxl.comments import Comment from openpyxl.styles import PatternFill from openpyxl.utils import get_column_letter PHONE_RE = re.compile(r"(?:0\d{2,4}-?\d{6,8}(?:-\d{1,5})?|1[3-9]\d{9}|400-?\d{3}-?\d{4})") SECOND_ROUND = { "广州市易和信息技术有限公司": { "phone": "020-38889987", "source_type": "official_site", "confidence": "high", "source_url": "https://www.yhnet.com.cn/contact.php", "evidence": "公司官网“联系我们”页公开联系电话。", "note": "二次公开来源补充，官网来源。", }, "云南南天电子信息产业股份有限公司": { "phone": "0871-63366327", "source_type": "listed_company_disclosure", "confidence": "high", "source_url": "https://static.cninfo.com.cn/finalpage/2025-04-29/1223376651.PDF", "evidence": "上市公司公开披露文件列示联系电话。", "note": "二次公开来源补充，上市公司公告来源。", }, "福建联迪商用设备有限公司": { "phone": "400-658-0616", "source_type": "official_site", "confidence": "high", "source_url": "https://www.landicorp.com/", "evidence": "公司官网公开服务热线。", "note": "二次公开来源补充，官网来源。", }, "阳光智园科技有限公司": { "phone": "400-689-6600", "source_type": "official_site", "confidence": "high", "source_url": "https://www.ygzykj.com/home/ygzyFront.do", "evidence": "公司官网公开客服热线。", "note": "二次公开来源补充，官网来源。", }, "广东优信无限网络股份有限公司": { "phone": "400-101-2698", "source_type": "official_site", "confidence": "high", "source_url": "https://www.iyouxin.com/", "evidence": "公司官网公开服务热线。", "note": "二次公开来源补充，官网来源。", }, "佳都科技集团股份有限公司": { "phone": "020-85521717-0", "source_type": "official_site", "confidence": "high", "source_url": "https://www.pcitech.com/", "evidence": "公司官网公开总机电话。", "note": "二次公开来源补充，官网来源。", }, "南京吾爱网络技术有限公司": { "phone": "025-58851823", "source_type": "official_site", "confidence": "high", "source_url": "https://www.wuaitec.com/", "evidence": "公司官网公开联系电话。", "note": "二次公开来源补充，官网来源。", }, "南京小牛智能科技有限公司": { "phone": "025-85303775", "source_type": "official_site", "confidence": "high", "source_url": "https://www.xnzn.net/", "evidence": "公司官网公开联系电话。", "note": "二次公开来源补充，官网来源。", }, "山东威尔数据股份有限公司": { "phone": "0535-3971818", "source_type": "public_company_profile", "confidence": "high", "source_url": "https://xinsanban.eastmoney.com/f10/833552.html", "evidence": "新三板公开资料列示联系电话。", "note": "二次公开来源补充，公开资本市场资料来源。", }, "新开普电子股份有限公司": { "phone": "0371-56599758", "source_type": "public_company_profile", "confidence": "high", "source_url": "https://stock.quote.stockstar.com/corp_300248.shtml", "evidence": "上市公司公开资料列示联系电话。", "note": "二次公开来源补充，公开资本市场资料来源。", }, "河南产业互联网联合发展有限公司": { "phone": "0371-66676661", "source_type": "official_site", "confidence": "high", "source_url": "https://hncyhl.cn/", "evidence": "公司官网公开联系电话。", "note": "二次公开来源补充，官网来源。", }, "河北鑫考科技股份有限公司": { "phone": "400-189-0086", "source_type": "official_site", "confidence": "high", "source_url": "https://www.xinkaogufen.com/", "evidence": "公司官网公开客服电话。", "note": "二次公开来源补充，官网来源。", }, "四川新华物业有限公司": { "phone": "028-84339999", "source_type": "public_company_profile", "confidence": "medium", "source_url": "https://baike.fang.com/item/%E5%9B%9B%E5%B7%9D%E6%96%B0%E5%8D%8E%E7%89%A9%E4%B8%9A%E6%9C%89%E9%99%90%E5%85%AC%E5%8F%B8/3761700", "evidence": "公开企业资料页列示联系方式，非官网来源。", "note": "二次公开来源补充，建议后续再用企业信息平台复核。", }, "杭州企智互联科技有限公司": { "phone": "0571-57175790", "source_type": "public_company_profile", "confidence": "medium", "source_url": "https://www.qixin.com/company/102d6067-109d-4f95-8a5e-77bb44db4afe", "evidence": "公开企业信息页检索结果显示联系电话，非官网来源。", "note": "二次公开来源补充，建议后续再用企业信息平台复核。", }, "南京市市民卡支付有限公司": { "phone": "025-86526985", "source_type": "public_procurement_contact", "confidence": "review", "source_url": "https://mzj.nanjing.gov.cn/njsmzj/njsmzj/202511/t20251105_5683881.html", "evidence": "公开采购/单一来源材料列示该供应商联系人电话。", "note": "来源为项目联系人，非企业总机，已标红建议复核。", "review": True, }, "湖北数据集团有限公司": { "phone": "17671443290", "source_type": "public_procurement_contact", "confidence": "review", "source_url": "https://www.jianyu360.cn/nologin/content/SEEY1cEcz0eJDg4NGd2ZGIdIzM_QCBgZmh9KygvKy4eUXtwGCNUCXg.html", "evidence": "公开采购材料中出现该公司相关联系人电话。", "note": "来源为项目联系人，非企业总机，已标红建议复核。", "review": True, }, "中恭鸿建项目管理有限公司": { "phone": "15225049989", "source_type": "public_procurement_contact", "confidence": "review", "source_url": "https://www.pdsxcq.gov.cn/contents/12852/57457.html", "evidence": "公开采购公告列示代理机构联系人电话。", "note": "来源为项目联系人，非企业总机，已标红建议复核。", "review": True, }, "中国移动通信集团浙江有限公司金华分公司": { "phone": "13957990601", "source_type": "public_recruitment_contact", "confidence": "review", "source_url": "https://www.dooynet.com/company/view/id/528234", "evidence": "公开招聘/企业信息页列示分公司联系人电话。", "note": "来源为招聘/项目联系人，非企业总机，已标红建议复核。", "review": True, }, "中国移动通信集团内蒙古有限公司通辽分公司": { "phone": "15004754007", "source_type": "public_procurement_contact", "confidence": "review", "source_url": "https://www.donews.com/news/detail/4/5149259", "evidence": "公开采购材料列示分公司项目联系人电话。", "note": "来源为项目联系人，非企业总机，已标红建议复核。", "review": True, }, "中国移动通信集团江苏有限公司常州分公司": { "phone": "13906129389", "source_type": "public_project_contact", "confidence": "review", "source_url": "https://gjxy.sosoq.net/zhigongkejichuangxinchengguoku/3669.html", "evidence": "公开项目/创新成果页列示分公司联系人电话。", "note": "来源为项目联系人，非企业总机，已标红建议复核。", "review": True, }, "中国移动通信集团重庆有限公司": { "phone": "13883319436", "source_type": "public_procurement_contact", "confidence": "review", "source_url": "https://hhxdata-obs.hhxck.com/hhx-oss/2024/11/21/f3cbd725dd364e229346de24b0d619da.pdf", "evidence": "公开采购材料列示公司项目联系人电话。", "note": "来源为项目联系人，非企业总机，已标红建议复核。", "review": True, }, } def clean(value) -> str: return str(value or "").strip() def normalize_phone(value: str) -> str: return re.sub(r"\D", "", value or "") def is_valid_phone(value: str) -> bool: return bool(PHONE_RE.search(value or "")) def clone_cell_style(dst, src) -> None: if not src.has_style: return dst.font = copy(src.font) dst.fill = copy(src.fill) dst.border = copy(src.border) dst.alignment = copy(src.alignment) dst.number_format = src.number_format dst.protection = copy(src.protection) def recompute_sheets(wb, ws, input_path: Path, output_path: Path) -> dict: stats = { "records": max(0, ws.max_row - 2), "winner_named": 0, "original_winner_phone": 0, "second_round_new": 0, "second_round_review": 0, "final_available": 0, "gaps": 0, } gap_rows = [["Excel行号", "项目名称", "发布省份", "中标单位", "缺口说明"]] for row in range(3, ws.max_row + 1): winner = clean(ws.cell(row, 13).value) phone = clean(ws.cell(row, 15).value or ws.cell(row, 20).value) note = clean(ws.cell(row, 32).value) source_type = clean(ws.cell(row, 22).value) if winner: stats["winner_named"] += 1 if clean(ws.cell(row, 15).value): stats["original_winner_phone"] += 1 if source_type == "二次公开来源补充": stats["second_round_new"] += 1 if "需复核" in note or clean(ws.cell(row, 23).value) == "review": stats["second_round_review"] += 1 if phone: stats["final_available"] += 1 elif winner: stats["gaps"] += 1 gap_note = note or "公开来源未找到可自动复用的完整电话，需人工核实。" gap_rows.append([row, ws.cell(row, 2).value, ws.cell(row, 5).value, winner, gap_note]) for sheet_name in ("补联SOP", "缺口清单"): if sheet_name in wb.sheetnames: del wb[sheet_name] yellow = PatternFill("solid", fgColor="FFFF00") gap_fill = PatternFill("solid", fgColor="FCE4D6") sop = wb.create_sheet("补联SOP") summary_rows = [ ["项目", "乙方宝中标公示项目电话补全（二次增补后）"], ["输入文件", str(input_path)], ["输出文件", str(output_path)], ["原始记录数", stats["records"]], ["中标单位非空记录数", stats["winner_named"]], ["中标单位电话可用记录数", stats["final_available"]], ["二次公开来源新增记录数", stats["second_round_new"]], ["其中需人工复核/已标红", stats["second_round_review"]], ["剩余缺口记录数", stats["gaps"]], ["颜色说明", "黄色为确认新增或补充信息区域；红色为来源需复核的电话或缺口说明。"], ["口径", "官网、公告、资本市场公开资料优先；公开采购/招聘联系人可补但标红，提示后续复核。"], ] for item in summary_rows: sop.append(item) sop.column_dimensions["A"].width = 28 sop.column_dimensions["B"].width = 110 for row in sop.iter_rows(): for cell in row: cell.alignment = copy(ws["A3"].alignment) cell.border = copy(ws["A3"].border) sop["A1"].fill = gap_fill sop["B1"].fill = gap_fill gap = wb.create_sheet("缺口清单") for item in gap_rows: gap.append(item) for row in gap.iter_rows(): for cell in row: cell.alignment = copy(ws["A3"].alignment) cell.border = copy(ws["A3"].border) for col, width in enumerate([10, 48, 12, 32, 58], start=1): gap.column_dimensions[get_column_letter(col)].width = width gap.cell(1, col).fill = gap_fill gap.freeze_panes = "A2" for col in range(20, 33): for row in range(1, ws.max_row + 1): if row <= 2 or ws.cell(row, col).value: ws.cell(row, col).fill = yellow return stats def apply(workbook_path: Path, output_path: Path, input_path: Path) -> dict: wb = load_workbook(workbook_path) ws = wb["已补电话-新增标黄"] yellow = PatternFill("solid", fgColor="FFFF00") red = PatternFill("solid", fgColor="F4CCCC") applied = 0 review = 0 skipped_buyer_phone = 0 for row in range(3, ws.max_row + 1): winner = clean(ws.cell(row, 13).value) if not winner or winner not in SECOND_ROUND: continue if clean(ws.cell(row, 15).value or ws.cell(row, 20).value): continue info = SECOND_ROUND[winner] phone = info["phone"] buyer_phone = clean(ws.cell(row, 12).value) if buyer_phone and normalize_phone(buyer_phone) == normalize_phone(phone): skipped_buyer_phone += 1 continue if not is_valid_phone(phone): continue fill = red if info.get("review") else yellow if info.get("review"): review += 1 ws.cell(row, 15).value = phone ws.cell(row, 15).fill = fill ws.cell(row, 15).comment = Comment(info["note"], "Codex") values = { 20: phone, 21: phone, 22: "二次公开来源补充", 23: info["confidence"], 24: info["source_url"], 25: phone if info["confidence"] == "high" else "", 31: info["evidence"], 32: info["note"], } for col, value in values.items(): ws.cell(row, col).value = value ws.cell(row, col).fill = fill if info.get("review") else yellow applied += 1 stats = recompute_sheets(wb, ws, input_path, output_path) output_path.parent.mkdir(parents=True, exist_ok=True) wb.save(output_path) stats.update({"applied": applied, "review": review, "skipped_buyer_phone": skipped_buyer_phone, "output": str(output_path)}) return stats def main() -> int: parser = argparse.ArgumentParser() parser.add_argument("--workbook", required=True, type=Path) parser.add_argument("--output", required=True, type=Path) parser.add_argument("--input", default=Path("乙方宝中标公示 0631-0715.xls"), type=Path) args = parser.parse_args() print(apply(args.workbook, args.output, args.input)) return 0 if __name__ == "__main__": raise SystemExit(main())