"""Classify existing customer follow-up records from their explicit content prefix."""
|
import sys,json,csv
|
from pathlib import Path
|
from collections import Counter
|
P=Path(__file__).parent
|
sys.stdout.reconfigure(encoding='utf8')
|
sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps'))
|
import pymysql
|
canon=lambda x:json.loads(json.dumps(x,ensure_ascii=False,default=str))
|
c=pymysql.connect(host='36.213.150.237',port=10000,user='root',password=sys.stdin.readline().strip(),database='mom-hm',charset='utf8mb4',autocommit=False,cursorclass=pymysql.cursors.DictCursor)
|
try:
|
with c.cursor() as q:
|
q.execute('SELECT DATABASE() db,NOW() now,GET_LOCK(%s,10) locked',('crm-follow-types',));state=q.fetchone();assert state['db']=='mom-hm' and state['locked']==1
|
q.execute('SELECT * FROM crm_follow_up_record WHERE deleted=0 ORDER BY id FOR UPDATE');records=q.fetchall()
|
q.execute("SELECT * FROM system_dict_data WHERE dict_type='crm_follow_up_type' ORDER BY id FOR UPDATE");dicts=q.fetchall()
|
backup=P/('follow-types-backup-'+state['now'].strftime('%Y%m%d-%H%M%S')+'.json')
|
backup.write_text(json.dumps({'state':state,'records':records,'dicts':dicts},ensure_ascii=False,default=str,indent=2),encoding='utf8')
|
active=[d for d in dicts if d['deleted'] in (0,b'\x00') and d['status']==0]
|
labels={d['label']:int(d['value']) for d in active}
|
assert '资料梳理' in labels
|
mapping={'档案梳理':'档案梳理','信息整理':'信息整理','跟进资料核对':'资料核对','资料跟进':'资料梳理'}
|
prefixes={str(x['content'] or '').split('：',1)[0] for x in records if x['biz_type']==2}
|
used={int(x['value']) for x in dicts if str(x['value']).isdigit()};added=[]
|
for prefix,label in mapping.items():
|
if prefix not in prefixes or label in labels:continue
|
value=max(used or {0})+1;used.add(value);labels[label]=value
|
q.execute('INSERT INTO system_dict_data (sort,label,value,dict_type,status,color_type,remark,creator,updater,create_time,update_time,deleted) VALUES (%s,%s,%s,%s,0,%s,%s,%s,%s,%s,%s,0)',(value,label,str(value),'crm_follow_up_type','info','依据客户跟进内容分类；不代表已进行电话或实地拜访','291','291',state['now'],state['now']))
|
added.append({'id':q.lastrowid,'label':label,'value':value})
|
plan=[];unmatched=[]
|
for x in records:
|
if x['biz_type']!=2:continue
|
prefix=str(x['content'] or '').split('：',1)[0]
|
if prefix not in mapping:unmatched.append({'id':x['id'],'type':x['type'],'reason':'内容未明确类型，保留现有值'});continue
|
label=mapping[prefix];target=labels[label]
|
changed=x['type']!=target
|
if changed:
|
q.execute('UPDATE crm_follow_up_record SET type=%s,updater=%s,update_time=%s WHERE id=%s AND deleted=0',(target,'291',state['now'],x['id']));assert q.rowcount==1
|
plan.append({'id':x['id'],'customer_id':x['biz_id'],'old_type':x['type'],'type':target,'label':label,'changed':changed})
|
q.execute('SELECT * FROM crm_follow_up_record WHERE deleted=0 ORDER BY id');after={x['id']:x for x in q.fetchall()};planned={x['id']:x for x in plan}
|
assert set(after)=={x['id'] for x in records}
|
for old in records:
|
new=after[old['id']];p=planned.get(old['id']);diff={k for k in old if old[k]!=new[k]}
|
assert diff<=({'type','updater','update_time'} if p and p['changed'] else set()),(old['id'],diff)
|
if p:assert new['type']==p['type']
|
q.execute("SELECT * FROM system_dict_data WHERE dict_type='crm_follow_up_type' ORDER BY id");after_dicts={x['id']:x for x in q.fetchall()}
|
for d in dicts:assert after_dicts[d['id']]==d
|
assert set(after_dicts)-{x['id'] for x in dicts}=={x['id'] for x in added}
|
c.commit()
|
# Verify committed rows without rewriting record content, creators, or business dates.
|
q.execute('SELECT * FROM crm_follow_up_record WHERE deleted=0 ORDER BY id');assert q.fetchall()==list(after.values())
|
summary={'committed':True,'verified':True,'at':str(state['now']),'added_types':added,'matched':len(plan),'changed':sum(x['changed'] for x in plan),'type_distribution':dict(Counter(x['label'] for x in plan)),'preserved_unmatched':unmatched,'backup':backup.name,'content_creator_and_original_dates_unchanged':True}
|
(P/'follow-types-result.json').write_text(json.dumps(summary,ensure_ascii=False,indent=2),encoding='utf8')
|
(P/'follow-types-mapping.json').write_text(json.dumps(plan,ensure_ascii=False,indent=2),encoding='utf8')
|
with (P/'客户跟进类型补充明细.csv').open('w',encoding='utf-8-sig',newline='') as f:
|
w=csv.writer(f);w.writerow(['跟进ID','客户ID','原类型值','新类型值','类型名称','已修改'])
|
for x in plan:w.writerow([x['id'],x['customer_id'],x['old_type'],x['type'],x['label'],x['changed']])
|
print(json.dumps(summary,ensure_ascii=False))
|
except Exception:c.rollback();raise
|
finally:
|
with c.cursor() as q:q.execute('SELECT RELEASE_LOCK(%s)',('crm-follow-types',))
|
c.close()
|
