"""Customer assignment correction requested by the user; no financial amount changes."""
|
import sys,json
|
from pathlib import Path
|
from decimal import Decimal
|
P=Path(__file__).parent
|
sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps'))
|
import pymysql
|
plan=json.loads((P/'import-plan.json').read_text(encoding='utf8'))
|
regroup=json.loads((P/'source-order-regroup-result.json').read_text(encoding='utf8'))
|
assert not (P/'ecommerce-customer-correction-result.json').exists(),'Correction already completed; verify current data.'
|
selected=[(g,r) for g,r in zip(plan['groups'],regroup['groups']) if '（电商）' in g['original'] or g['customer_name']=='电商']
|
assert len(selected)==8 and sum(len(r['orders']) for g,r in selected)==8
|
docids=[r['contract_id'] for r in regroup['groups']]
|
c=pymysql.connect(host='36.213.150.237',port=10000,user='root',password=sys.stdin.readline().strip(),database='mom-hm',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor,autocommit=False)
|
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_customer WHERE name='电商' AND deleted=0 FOR UPDATE");found=q.fetchall();assert len(found)==1 and found[0]['owner_user_id']==291;target=found[0]
|
q.execute('SELECT COUNT(*) n FROM crm_permission WHERE biz_type=2 AND biz_id=%s AND user_id=291 AND level=1 AND deleted=0',(target['id'],));assert q.fetchone()['n']==1
|
# Capture scoped headers plus all live detail and permission records before changing customer fields.
|
backup={'state':state,'target_customer':target,'tables':{}}
|
for t,field in [('erp_sale_order','contract_id'),('crm_contract','id'),('crm_sale_quotation','contract_id'),('crm_invoice','contract_id'),('crm_receivable','contract_id'),('crm_receivable_plan','contract_id')]:
|
q.execute('SELECT * FROM `'+t+'` WHERE `'+field+'` IN ('+','.join(['%s']*len(docids))+') AND deleted=0 ORDER BY id',docids);backup['tables'][t]=q.fetchall()
|
for t in ['crm_contract_product','crm_sale_quotation_product','erp_sale_order_items','crm_permission']:
|
q.execute('SELECT * FROM `'+t+'` WHERE deleted=0 ORDER BY id');backup['tables'][t]=q.fetchall()
|
q.execute('SELECT * FROM crm_customer WHERE id IN (%s,%s) ORDER BY id',(target['id'],1381));backup['tables']['crm_customer']=q.fetchall()
|
(P/'ecommerce-customer-correction-before.json').write_text(json.dumps(backup,ensure_ascii=False,default=str,indent=2),encoding='utf8')
|
tables={t:{r['id']:r for r in rs} for t,rs in backup['tables'].items()}
|
affected={t:set() for t in ['erp_sale_order','crm_contract','crm_sale_quotation','crm_invoice','crm_receivable','crm_receivable_plan']}
|
for g,r in selected:
|
ct=tables['crm_contract'][r['contract_id']];oldid=ct['customer_id']
|
assert oldid in [1381,target['id']],(g['key'],oldid)
|
if oldid==target['id']:continue
|
affected['crm_contract'].add(ct['id'])
|
affected['erp_sale_order'].update(o['order_id'] for o in r['orders'])
|
for t in ['crm_sale_quotation','crm_invoice','crm_receivable','crm_receivable_plan']:
|
affected[t].update(id for id,x in tables[t].items() if x['contract_id']==ct['id'])
|
assert len(affected['erp_sale_order'])==7
|
for t,ids in affected.items():
|
if not ids:continue
|
for id in ids:assert tables[t][id]['customer_id']==1381
|
q.execute('UPDATE `'+t+'` SET customer_id=%s,updater=%s,update_time=%s WHERE id IN ('+','.join(['%s']*len(ids))+')',[target['id'],'291',state['now'],*sorted(ids)])
|
for id in affected['crm_contract']:
|
name=tables['crm_contract'][id]['name'];assert '北京同仁堂健康药业电子商务有限公司' in name
|
q.execute('UPDATE crm_contract SET name=%s WHERE id=%s',(name.replace('北京同仁堂健康药业电子商务有限公司','电商'),id))
|
# Keep original invoice titles and source remarks: customer assignment is distinct from legal invoice-title evidence.
|
for t,rs in backup['tables'].items():
|
for old in rs:
|
q.execute('SELECT * FROM `'+t+'` WHERE id=%s',(old['id'],));new=q.fetchone();changed={k for k in old if old[k]!=new[k]}
|
allowed={'customer_id','updater','update_time'} | ({'name'} if t=='crm_contract' else set())
|
assert not changed or old['id'] in affected.get(t,set()) and changed<=allowed,(t,old['id'],changed)
|
nos=[o['source_no'] for g,r in selected for o in r['orders']]
|
q.execute('SELECT no,customer_id,total_price FROM erp_sale_order WHERE no IN ('+','.join(['%s']*len(nos))+') AND deleted=0',nos);orders=q.fetchall();assert len(orders)==8 and all(x['customer_id']==target['id'] for x in orders)
|
total=sum(x['total_price'] for x in orders);assert total==Decimal('2030440.62')
|
c.commit()
|
result={'committed':True,'at':str(state['now']),'target_customer_id':target['id'],'target_customer_name':'电商','changed_counts':{t:len(ids) for t,ids in affected.items()},'changed_ids':{t:sorted(ids) for t,ids in affected.items()},'order_numbers':nos,'orders':8,'total':str(total),'preserved_invoice_titles':True,'selected_contract_ids':[r['contract_id'] for g,r in selected]}
|
(P/'ecommerce-customer-correction-result.json').write_text(json.dumps(result,ensure_ascii=False,indent=2),encoding='utf8')
|
print(json.dumps(result,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()
|
