import sys,json,csv
|
from pathlib import Path
|
P=Path(__file__).parent
|
sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps'))
|
import pymysql
|
encode=lambda r:json.dumps(r,ensure_ascii=False,default=str,sort_keys=True)
|
connection_args=dict(host='36.213.150.237',port=10000,user='root',password=sys.stdin.readline().strip(),database='mom-hm',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
|
c=pymysql.connect(**connection_args,autocommit=False)
|
try:
|
with c.cursor() as q:
|
q.execute('SELECT DATABASE() db,NOW() now');state=q.fetchone();assert state['db']=='mom-hm';now=state['now']
|
q.execute('SHOW CREATE TABLE crm_customer');schema=q.fetchone()
|
scope='deleted=0 AND tenant_id=0' if '`tenant_id`' in schema['Create Table'] else 'deleted=0'
|
q.execute('SELECT * FROM crm_customer WHERE '+scope+' ORDER BY id FOR UPDATE');before=q.fetchall()
|
assert before,'No active customers found'
|
changed=[r for r in before if r['deal_status'] not in (1,True,b'\x01')]
|
(P/'before-customer-deal-status.json').write_text(encode({'schema':schema,'rows':before,'at':str(now)}),encoding='utf8')
|
q.execute('UPDATE crm_customer SET deal_status=1,updater=%s,update_time=%s WHERE '+scope+' AND (deal_status IS NULL OR deal_status=0)',('crm-deal-batch-20261013',now))
|
assert q.rowcount==len(changed)
|
q.execute('SELECT * FROM crm_customer WHERE '+scope+' ORDER BY id');after={r['id']:r for r in q.fetchall()}
|
changed_ids={r['id'] for r in changed}
|
assert len(after)==len(before)
|
for original in before:
|
current=after[original['id']];assert current['deal_status'] in (1,True,b'\x01')
|
allowed={'deal_status','updater','update_time'} if original['id'] in changed_ids else set()
|
assert all(current[k]==v for k,v in original.items() if k not in allowed)
|
c.commit()
|
result={'active_customers':len(before),'updated_customers':len(changed),'already_dealt':len(before)-len(changed),'deal_status':'已成交','executed_at':str(now)}
|
(P/'customer-deal-status-result.json').write_text(encode(result),encoding='utf8')
|
with (P/'客户成交状态调整清单.csv').open('w',encoding='utf-8-sig',newline='') as f:
|
w=csv.writer(f);w.writerow(['客户ID','客户名称','原成交状态','现成交状态','调整时间'])
|
for r in changed:w.writerow([r['id'],r['name'],'未成交' if r['deal_status'] is not None else '空','已成交',str(now)])
|
finally:c.close()
|
c=pymysql.connect(**connection_args)
|
try:
|
with c.cursor() as q:
|
q.execute('SELECT id,deal_status FROM crm_customer WHERE '+scope)
|
rows=q.fetchall();assert len(rows)==result['active_customers'] and all(r['deal_status'] in (1,True,b'\x01') for r in rows)
|
result['verified_after_commit']=True
|
(P/'customer-deal-status-result.json').write_text(encode(result),encoding='utf8')
|
print(encode(result))
|
finally:c.close()
|
