"""Synchronize quotation names using explicit contract foreign keys."""
|
import sys,json
|
from pathlib import Path
|
P=Path(__file__).parent
|
sys.stdout.reconfigure(encoding='utf8')
|
sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps'))
|
import pymysql
|
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',('sales-ledger-202609',));state=q.fetchone();assert state['db']=='mom-hm' and state['locked']==1
|
q.execute('SELECT * FROM crm_contract WHERE deleted=0 ORDER BY id FOR UPDATE');contracts=q.fetchall();byid={x['id']:x for x in contracts}
|
q.execute('SELECT * FROM crm_sale_quotation WHERE deleted=0 ORDER BY id FOR UPDATE');before=q.fetchall()
|
path=P/('quotation-name-sync-before-'+state['now'].strftime('%Y%m%d-%H%M%S')+'.json')
|
path.write_text(json.dumps({'state':state,'contracts':contracts,'quotations':before},ensure_ascii=False,default=str,indent=2),encoding='utf8')
|
changes=[];unlinked=[];matched=0
|
for old in before:
|
ct=byid.get(old['contract_id'])
|
if not ct:unlinked.append({'id':old['id'],'no':old['no'],'reason':'未关联有效合同，保留原名称'});continue
|
assert ct['name'] and ct['name'].strip() and ct['customer_id']==old['customer_id'],('Invalid linked contract',old['id'])
|
matched+=1
|
if old['name']==ct['name']:continue
|
q.execute('UPDATE crm_sale_quotation SET name=%s,updater=%s,update_time=%s WHERE id=%s AND deleted=0',(ct['name'],'291',state['now'],old['id']));assert q.rowcount==1
|
changes.append({'id':old['id'],'no':old['no'],'contract_id':ct['id'],'old_name':old['name'],'new_name':ct['name']})
|
q.execute('SELECT * FROM crm_sale_quotation WHERE deleted=0 ORDER BY id');after=q.fetchall();changed={x['id']:x for x in changes};assert len(before)==len(after)
|
for old,new in zip(before,after):
|
assert old['id']==new['id'];diff={k for k in old if old[k]!=new[k]};assert diff<=({'name','updater','update_time'} if old['id'] in changed else set())
|
if new['contract_id'] in byid:assert new['name']==byid[new['contract_id']]['name']
|
q.execute('SELECT * FROM crm_contract WHERE deleted=0 ORDER BY id');assert q.fetchall()==contracts
|
c.commit()
|
q.execute('SELECT * FROM crm_sale_quotation WHERE deleted=0 ORDER BY id');assert q.fetchall()==after
|
result={'committed':True,'verified':True,'at':str(state['now']),'matched':matched,'changed':len(changes),'changes':changes,'unlinked_preserved':unlinked,'backup':path.name,'other_quotation_fields_and_contracts_unchanged':True}
|
(P/'quotation-name-sync-result.json').write_text(json.dumps(result,ensure_ascii=False,indent=2),encoding='utf8')
|
print(json.dumps({k:v for k,v in result.items() if k!='changes'},ensure_ascii=False))
|
except Exception:c.rollback();raise
|
finally:
|
with c.cursor() as q:q.execute('SELECT RELEASE_LOCK(%s)',('sales-ledger-202609',))
|
c.close()
|
