j&dZddlZddlZddlZddlZddlZddlZdZdZdZ ejdZ ejdZ dZ dd Zd ZGd d Zd ZdZddZdZdZdZedk(rej0eyy)up contact_lib.py — 全源联系人聚合入库模块 (2026-09-10 设计定稿) ============================================================ 统一联系人库 contact.db: 从 CCPC(ccpc.db) + ZC(ZC.db) 两源聚合联系人, 按 company+contact_name 去重合并(要素并集, 项目累加), 全要素保留。 设计(用户 2026-09-10 拍板): - 保留联系人归属公司; 可查"某公司所有联系人" - 全要素: 职责(role/position/department) + 座机(landline) + 手机(phone) + 邮箱(email) - 同公司内去重, 不同公司不去重; 跨源同公司同人合并(要素并集) - 由 ccpc_zc_sync.py Phase3 调用(编排器), 也可 CLI 单跑 Schema: contacts(id, company, contact_name, department, position, role, phone, landline, email, address, remarks) contact_projects(contact_id, source, project_key, project_name) 行为: - 全量重建(源库权威), 幂等 - 结果与现库完全一致时静默(exit 0 无输出) —— 配合编排器"无变化静默"语义 用法: python3 contact_lib.py # 重建并写库 python3 contact_lib.py force # 同(默认即全量) Nz/root/contact.dbz /root/ccpc.dbz /root/ZC.dbu[\s\-—–_()（）]+z ^1[3-9]\d{9}$cb|sytjdt|jS)N) PHONE_NORMsubstrstrip)vs /root/gov_crawler/contact_lib.py norm_phoner %s&  >>"c!f % + + --c|syg}t|jdD]V}t|}|stj |r't|r|t|k(rA||vsF|j |Xdj |S)u座机清洗(2026-09-10): 源数据 99% 的 'landline' 是手机号重复存 → 手机格式(1[3-9]+9位) 或 与手机同号 的值一律剔除; 多值逐项过滤; 真座机(区号开头)保留r/ / )rsplitr MOBILE_REmatchappendjoin)r phone_valkeeppartps r clean_landliner+s  DA S!  t   ??1   i Q*Y*?%?  D= KKN  ::d r c|xsdjdDcgc]#}|js|j%}}|r1|j|vr|j|jdj|Scc}w)u5字段并集: 非空值去重, 多值用 ' / ' 连接rr)rrrr)abxvalss r merge_valr>sg !Ru5 C!AGGI CD CQWWYd " AGGI ::d  Ds BBceZdZdZdZy) ContactMergerci|_yN)by_key)selfs r __init__zContactMerger.__init__Gs  r c |xsdj}|xsdj}|r|sy||f}|jj|}|||ddddddddgd }||j|<dD])}|j|st||||||<+|jdrt|dt |d|d<|jdr6t |d|jdxsd}|rt|d||d<|jdr't|d|dxsdj|d<|r||dvr|dj |y ) NrF) company contact_name departmentpositionrolephonelandlineemailaddressremarksprojects)r*r+r,r0r1r-r.r/r2T)rr$getrr rr) r%r(nameprojectfieldskeyrecf_lns r addzContactMerger.addJss=b'') !!#dokkooc" ;%t!#R"!B2"!#C #DKK I 6Azz!}"3q66!95A 6 ::g $S\:fWo3NOCL ::j !  !3VZZ5H5NBOC"+C OS"AJ ::g $S\F7O4Ir3P3P3RSCL wc*o5  O " "7 +r N)__name__ __module__ __qualname__r&r;r r r!r!Fs r r!ctjt}tj|_|j dj }|j dDcic]}|d|d|df}}|jd}|D]T}|j|dd\}}|j|d |d d ||fd d |d |d|d|d|dd  sP|dz }V|Scc}w)uGCCPC 源: cceup_contacts + cceup_projects(id→project_id/project_name)z SELECT ct.project_id AS fk, ct.company, ct.contact_name, ct.phone, ct.landline, ct.email, ct.role, ct.address FROM cceup_contacts ct z7SELECT id, project_id, project_name FROM cceup_projectsid project_id project_namerfk)rrr(r)ccpcrr,r-r.r/r0r*r+r,r-r.r/r0r1) sqlite3connectCCPC_DBRow row_factoryexecutefetchallcloser3r;)mconrowsrpmapnpidpnames r load_ccpcrXhs //' "CkkCO ;;    DG;;ADC DaAdGa oq'89 9 DD DIIK A XXagx0 U 559q0632F!F)1W:J-qz1Y>* %ooj){{.//7xz kk9::B(* ! 9NCB OOC $ + +QBK 8 9kkRS\\^o BFAsa&)C JJS VDHHS",=%>$??@ A B  ).N* s+G"2DG GGc|ytjtj|dj j S)NF) ensure_ascii)hashlibsha256jsondumpsencode hexdigest)itemss r content_hashr|s6 } >>$**U?FFH I S S UUr ct|}t|}ttd}||k(rytjt}|j }|j d|j d|j d|j d|j d|j dt|jD]}|j|}|j d|d |d |d |d |d |d|d|d|d|df |j}|jd|dD cgc] \} } } || | | fc} } } |j|j djd} |j djd} |j| | fScc} } } w)NFz%DROP TABLE IF EXISTS contact_projectszDROP TABLE IF EXISTS contactsaqCREATE TABLE contacts ( id INTEGER PRIMARY KEY AUTOINCREMENT, company TEXT NOT NULL, contact_name TEXT NOT NULL DEFAULT '', department TEXT DEFAULT '', position TEXT DEFAULT '', role TEXT DEFAULT '', phone TEXT DEFAULT '', landline TEXT DEFAULT '', email TEXT DEFAULT '', address TEXT DEFAULT '', remarks TEXT DEFAULT '')zPCREATE UNIQUE INDEX idx_contacts_company_name ON contacts(company, contact_name)zCREATE TABLE contact_projects ( contact_id INTEGER NOT NULL, source TEXT NOT NULL, project_key TEXT NOT NULL, project_name TEXT DEFAULT '')z;CREATE INDEX idx_cp_contact ON contact_projects(contact_id)zINSERT INTO contacts (company, contact_name, department, position, role, phone, landline, email, address, remarks) VALUES (?,?,?,?,?,?,?,?,?,?)r(r)r*r+r,r-r.r/r0r1z]INSERT INTO contact_projects (contact_id, source, project_key, project_name) VALUES (?,?,?,?)r2zSELECT COUNT(*) FROM contactsrz%SELECT COUNT(*) FROM contact_projects)rrr|rHrIrccursorrMr^r$ lastrowid executemanycommitfetchonerO)rg new_itemsnew_hcur_hrQcr7rSrkrlrmrnrUncs r write_dbrs I  #E $ (E ~ //* %C AII56II-.II=>II`aIIDEIIKLfmm$ ? MM#   O y\1^,a oq}aPVi wZ:' AiL!I, P Q kk k/0} = =)!Rc1b"  = ? ?JJL 12;;=a@A : ; D D Fq IBIIK r7N >sGc t}t|}t|}||z}t|}|dury|\}}t |j Dchc]}|d c}}t d|d|d|d|d|d|d ycc}w) NFru联系人库: 源行 z (ccpc z / zc u) → 去重后 u 人 / u 公司 / u 项目关联)r!rXr\rlenr$print) rPn_ccpcn_zcsrc_rowsresult n_contactsn_projk n_companys r mainrsA q\F 1:D}H a[F J188,aQqT,-I !(76(&FVWaVbbi Kz& 89 -s B__main__)rr#)__doc__r`resysrHrurwrcrJr[compilerrr rrr!rXr\rrr|rrr<exitr?r r rs0+**   RZZ1 2 BJJ' ( . &D , ,BV 'T   z CHHTVr