"""Link non-Beijing-Tongrentang quotations to existing contract opportunities."""
|
import sys,json
|
from pathlib import Path
|
from datetime import datetime
|
from decimal import Decimal
|
P=Path(__file__).parent
|
sys.stdout.reconfigure(encoding='utf8')
|
sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps'))
|
import pymysql
|
assert not (P/'result.json').exists(),'Already processed; verify instead of repeating.'
|
minimum=datetime(2026,9,17)
|
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)
|
result={'quotes':[],'new_business_product_ids':[],'new_permission_ids':[],'business_headers_created':0,'excluded_customer_keyword':'北京同仁堂'}
|
try:
|
with c.cursor() as q:
|
q.execute("SET time_zone='+08:00'")
|
q.execute('SELECT DATABASE() db,NOW() now,GET_LOCK(%s,10) locked',('quotation-opportunities-20261014',));state=q.fetchone();assert state['db']=='mom-hm' and state['locked']==1;now=state['now']
|
tabs=['crm_customer','crm_business','crm_business_product','crm_sale_quotation','crm_sale_quotation_product','crm_contract','crm_contract_product','erp_sale_order','erp_sale_order_items','crm_permission','crm_invoice','crm_receivable','crm_receivable_plan']
|
before={};data={};schemas={}
|
for t in tabs:
|
q.execute('SELECT * FROM '+t+' WHERE deleted=0 ORDER BY id FOR UPDATE');rs=q.fetchall();before[t]=rs;data[t]={x['id']:x for x in rs}
|
q.execute('SHOW COLUMNS FROM '+t);schemas[t]={x['Field'] for x in q.fetchall()}
|
q.execute('SELECT status FROM system_users WHERE id=291 AND deleted=0');assert q.fetchone()['status']==0
|
backup=P/('locked-before-'+now.strftime('%Y%m%d-%H%M%S')+'.json')
|
backup.write_text(json.dumps({'state':state,'tables':before},ensure_ascii=False,default=str,indent=2),encoding='utf8')
|
allowed={};expected={}
|
def insert(t,v,day):
|
v={**v,'creator':'291','updater':'291','create_time':day,'update_time':day,'deleted':0};assert set(v)<=schemas[t]
|
q.execute('INSERT INTO '+t+' ('+','.join('`'+k+'`' for k in v)+') VALUES ('+','.join(['%s']*len(v))+')',list(v.values()));return q.lastrowid
|
def update(t,id,v,day):
|
v={**v,'updater':'291','update_time':day};assert set(v)<=schemas[t]
|
q.execute('UPDATE '+t+' SET '+','.join('`'+k+'`=%s' for k in v)+' WHERE deleted=0 AND id=%s',[*v.values(),id]);assert q.rowcount==1
|
allowed.setdefault((t,id),set()).update(v);expected.setdefault((t,id),{}).update(v)
|
quotes=[x for x in before['crm_sale_quotation'] if '北京同仁堂' not in data['crm_customer'][x['customer_id']]['name']]
|
seen=set()
|
for qt in quotes:
|
ct=data['crm_contract'][qt['contract_id']];assert ct['customer_id']==qt['customer_id'] and ct['total_price']==qt['total_price']
|
b=data['crm_business'].get(ct['business_id']);assert b and b['customer_id']==qt['customer_id'] and b['total_price']==qt['total_price']
|
assert qt['business_id'] in (None,b['id']) and b['id'] not in seen;seen.add(b['id'])
|
order=data['erp_sale_order'][ct['order_id']];assert order['contract_id']==ct['id'] and order['customer_id']==qt['customer_id']
|
assert b['order_id'] in (None,order['id']) and b['order_no'] in (None,'',order['no'])
|
day=max(qt['quotation_time'],qt['create_time'],minimum);assert day<=now
|
update('crm_sale_quotation',qt['id'],{'business_id':b['id'],'quotation_time':day,'create_time':day},max(qt['update_time'],day))
|
quote_lines=[x for x in before['crm_sale_quotation_product'] if x['quotation_id']==qt['id']]
|
contract_lines=[x for x in before['crm_contract_product'] if x['contract_id']==ct['id']];assert contract_lines
|
assert sorted((x['item_id'],x['item_unit_id'],x['count']) for x in quote_lines)==sorted((x['item_id'],x['item_unit_id'],x['count']) for x in contract_lines)
|
for l in quote_lines:
|
if l['create_time']!=day:update('crm_sale_quotation_product',l['id'],{'create_time':day},max(l['update_time'],day))
|
existing_products=[x for x in before['crm_business_product'] if x['business_id']==b['id']]
|
assert not existing_products,'Existing business product rows require separate reconciliation'
|
total=sum(x['total_price'] for x in contract_lines);assert total==ct['total_product_price']==ct['total_price'] and ct['discount_percent']==0
|
for l in contract_lines:
|
id=insert('crm_business_product',{'business_id':b['id'],'item_id':l['item_id'],'item_unit_id':l['item_unit_id'],'item_price':l['item_price'],'business_price':l['contract_price'],'count':l['count'],'total_price':l['total_price']},day);result['new_business_product_ids'].append(id)
|
update('crm_business',b['id'],{'order_id':order['id'],'order_no':order['no'],'total_product_price':total,'discount_percent':Decimal(0)},now)
|
permissions=[x for x in before['crm_permission'] if x['biz_type']==4 and x['biz_id']==b['id'] and x['user_id']==b['owner_user_id']]
|
if not permissions:result['new_permission_ids'].append(insert('crm_permission',{'biz_type':4,'biz_id':b['id'],'user_id':b['owner_user_id'],'level':1},day))
|
else:assert any(x['level']==1 for x in permissions),'Business owner permission is incomplete'
|
result['quotes'].append({'id':qt['id'],'no':qt['no'],'customer':data['crm_customer'][qt['customer_id']]['name'],'business_id':b['id'],'contract_id':ct['id'],'order_id':order['id'],'date':str(day),'old_date':str(qt['quotation_time']),'date_changed':day!=qt['quotation_time'] or day!=qt['create_time'],'product_lines':len(contract_lines)})
|
assert len(quotes)==16 and len(result['new_business_product_ids'])==34
|
after={}
|
for t,rows in before.items():
|
q.execute('SELECT * FROM '+t+' WHERE deleted=0 ORDER BY id');rs=q.fetchall();after[t]=rs;byid={x['id']:x for x in rs}
|
for old in rows:
|
new=byid[old['id']];diff={k for k in old if old[k]!=new[k]};assert diff<=allowed.get((t,old['id']),set()),(t,old['id'],diff)
|
for k,v in expected.get((t,old['id']),{}).items():assert new[k]==v
|
newids=set(result['new_business_product_ids']) if t=='crm_business_product' else set(result['new_permission_ids']) if t=='crm_permission' else set()
|
assert set(byid)-{x['id'] for x in rows}==newids
|
for x in result['quotes']:
|
qt=next(r for r in after['crm_sale_quotation'] if r['id']==x['id']);b=next(r for r in after['crm_business'] if r['id']==x['business_id'])
|
assert minimum<=qt['quotation_time']==qt['create_time']<=now and qt['business_id']==b['id'] and qt['customer_id']==b['customer_id']
|
assert sum(r['total_price'] for r in after['crm_business_product'] if r['business_id']==b['id'])==b['total_product_price']==b['total_price']
|
c.commit()
|
for t,rs in after.items():
|
q.execute('SELECT * FROM '+t+' WHERE deleted=0 ORDER BY id');assert q.fetchall()==rs
|
result.update(committed=True,verified=True,at=str(now),quotes_linked=len(quotes),quote_dates_changed=sum(x['date_changed'] for x in result['quotes']),business_products_added=len(result['new_business_product_ids']),backup=backup.name,beijing_tongrentang_data_unchanged=True,contract_invoice_receipt_order_amounts_dates_statuses_unchanged=True)
|
(P/'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 not in ['quotes','new_business_product_ids','new_permission_ids']},ensure_ascii=False))
|
except Exception:c.rollback();raise
|
finally:
|
with c.cursor() as q:q.execute('SELECT RELEASE_LOCK(%s)',('quotation-opportunities-20261014',))
|
c.close()
|
