import sys,json,csv
|
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'))
|
fix=json.loads((P/'ecommerce-customer-correction-result.json').read_text(encoding='utf8'))
|
before=json.loads((P/'ecommerce-customer-correction-before.json').read_text(encoding='utf8'))
|
shipping_path=P/'shipping-drafts-result.json'
|
shipping_ids={o['order_id'] for o in json.loads(shipping_path.read_text(encoding='utf8'))['orders']} if shipping_path.exists() else set()
|
completed_path=P/'shipping-complete-result.json'
|
completed=json.loads(completed_path.read_text(encoding='utf8')) if completed_path.exists() else {}
|
completed_ids={o['order_id'] for o in completed.get('orders',[])}
|
completed_line_ids={a['order_item_id'] for a in completed.get('allocations',[])}
|
name_fix_path=P/'contract-name-prefix-result.json'
|
name_fixes={x['id']:x for x in json.loads(name_fix_path.read_text(encoding='utf8'))['changes']} if name_fix_path.exists() else {}
|
quote_fix_path=P/'quotation-name-sync-result.json'
|
quote_name_fixes={x['id']:x for x in json.loads(quote_fix_path.read_text(encoding='utf8'))['changes']} if quote_fix_path.exists() else {}
|
date_fix_path=P/'business-create-times-result.json'
|
date_fixes={(x['table'],x['id']):x for x in json.loads(date_fix_path.read_text(encoding='utf8'))['changes']} if date_fix_path.exists() else {}
|
remark_fix_path=P/'contract-order-remarks-cleaned-result.json'
|
remark_fixes={(x['table'],x['id']):x for x in json.loads(remark_fix_path.read_text(encoding='utf8'))['changes']} if remark_fix_path.exists() else {}
|
if date_fix_path.exists():
|
date_result=json.loads(date_fix_path.read_text(encoding='utf8'))
|
latest_backup=json.loads((P/date_result['backup']).read_text(encoding='utf8'))
|
# Customer follow-up activity continued after the original sales correction.
|
before['tables']['crm_customer']=latest_backup['tables']['crm_customer']
|
D=Decimal
|
canonical=lambda v:json.loads(json.dumps(v,default=str))
|
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)
|
docids=[r['contract_id'] for r in regroup['groups']]
|
with c.cursor() as q:
|
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);tables[t]={r['id']:r for r in 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');tables[t]={r['id']:r for r in q.fetchall()}
|
q.execute('SELECT * FROM crm_customer WHERE deleted=0 ORDER BY id');tables['crm_customer']={r['id']:r for r in q.fetchall()}
|
for t,rows in before['tables'].items():
|
if t!='crm_customer':assert {r['id'] for r in rows}==set(tables[t]),('Unexpected creation or removal',t)
|
affected=set(fix['changed_ids'].get(t,[]))
|
for old in rows:
|
new=canonical(tables[t][old['id']]);diff={k for k in old if old[k]!=new[k]}
|
allowed=({'customer_id','updater','update_time'} | ({'name'} if t=='crm_contract' else set())) if old['id'] in affected else set()
|
if t=='erp_sale_order' and old['id'] in shipping_ids:allowed|={'out_status','updater','update_time'}
|
if t=='erp_sale_order' and old['id'] in completed_ids:allowed|={'out_count'}
|
if t=='erp_sale_order_items' and old['id'] in completed_line_ids:allowed|={'out_count','updater','update_time'}
|
if t=='crm_contract' and old['id'] in name_fixes:
|
allowed|={'name','updater','update_time'}
|
assert new['name']==name_fixes[old['id']]['new_name']
|
if t=='crm_sale_quotation' and old['id'] in quote_name_fixes:
|
allowed|={'name','updater','update_time'}
|
assert new['name']==quote_name_fixes[old['id']]['new_name']
|
if (t,old['id']) in date_fixes:
|
allowed|={'create_time','updater','update_time'}
|
assert new['create_time']==date_fixes[(t,old['id'])]['create_time']
|
if (t,old['id']) in remark_fixes:
|
allowed|={'remark','updater'}
|
assert new['remark']==remark_fixes[(t,old['id'])]['remark']
|
assert not diff or diff<=allowed,(t,old['id'],diff)
|
detail=[];seen=set();gross=D(0);ecommerce=[];paid=D(0);pending=D(0)
|
for g,r in zip(plan['groups'],regroup['groups']):
|
ct=tables['crm_contract'][r['contract_id']];customer=tables['crm_customer'][ct['customer_id']]
|
assert customer['owner_user_id']==291 and ct['total_price']==D(g['gross'])
|
quotes=[x for x in tables['crm_sale_quotation'].values() if x['contract_id']==ct['id']]
|
assert len(quotes)==1 and quotes[0]['customer_id']==ct['customer_id'] and quotes[0]['total_price']==ct['total_price']
|
if ct['id'] in fix['selected_contract_ids']:assert ct['customer_id']==fix['target_customer_id']
|
if g['key'] in ['202609-L060','202609-L061']:assert ct['customer_id']==1381
|
assert sum(D(o['gross']) for o in r['orders'])==ct['total_price']
|
for o in r['orders']:
|
order=tables['erp_sale_order'][o['order_id']]
|
assert order['no']==o['source_no'] and order['customer_id']==ct['customer_id'] and order['contract_id']==ct['id'] and order['total_price']==D(o['gross'])
|
assert order['total_product_price']+order['total_tax_price']-order['discount_price']==order['total_price']
|
lines=[x for x in tables['erp_sale_order_items'].values() if x['order_id']==order['id']]
|
ids={x['id'] for x in lines};assert ids==set(o['order_item_ids']) and not ids&seen;seen|=ids
|
assert sum(x['count'] for x in lines)==order['total_count'] and sum(x['total_price'] for x in lines)==order['total_product_price'] and sum(x['tax_price'] for x in lines)==order['total_tax_price']
|
for row,id in zip(o['source_rows'],o['order_item_ids']):
|
source=next(a for a in g['lines'] if a['row']==row);assert source['source_no']==order['no'] and tables['erp_sale_order_items'][id]['count']==D(source['quantity'])
|
gross+=order['total_price'];x={'no':order['no'],'id':order['id'],'customer':customer['name'],'lines':len(lines),'gross':str(order['total_price']),'contract_id':ct['id'],'invoice_id':r['invoice_id']};detail.append(x)
|
if customer['name']=='电商':ecommerce.append(x)
|
if r['invoice_id']:
|
inv=tables['crm_invoice'][r['invoice_id']];assert inv['customer_id']==ct['customer_id'] and inv['contract_id']==ct['id'] and inv['price']==ct['total_price']
|
if r['receivable_id']:
|
rc=tables['crm_receivable'][r['receivable_id']];assert rc['customer_id']==ct['customer_id'] and rc['contract_id']==ct['id'] and rc['price']==D(g['received']);paid+=rc['price']
|
if r['plan_id']:
|
rp=tables['crm_receivable_plan'][r['plan_id']];assert rp['customer_id']==ct['customer_id'] and rp['contract_id']==ct['id'] and rp['price']==D(g['outstanding']);pending+=rp['price']
|
assert len(detail)==24 and len(seen)==76 and gross==D(plan['totals']['gross'])
|
assert paid==D(plan['totals']['received']) and pending==D(plan['totals']['outstanding']) and paid+pending==gross
|
assert len(ecommerce)==8 and {x['no'] for x in ecommerce}==set(fix['order_numbers']) and sum(D(x['gross']) for x in ecommerce)==D('2030440.62')
|
q.execute('SELECT COUNT(*) n FROM erp_sale_order WHERE customer_id=%s AND deleted=0',(fix['target_customer_id'],));full_count=q.fetchone()['n']
|
report={'passed':True,'orders':24,'lines':76,'total':str(gross),'ecommerce_orders':8,'ecommerce_customer_all_active_orders':full_count,'ecommerce_total':'2030440.62','ecommerce_order_numbers':[x['no'] for x in ecommerce],'all_unchanged_except_confirmed_customer_fields':True}
|
(P/'current-sales-verification.json').write_text(json.dumps(report,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','合并开票ID'])
|
for x in detail:w.writerow([x['no'],x['id'],x['customer'],x['lines'],x['gross'],x['contract_id'],x['invoice_id']])
|
c.close();print(json.dumps(report,ensure_ascii=False))
|
