import sys,json
|
from pathlib import Path
|
from decimal import Decimal
|
P=Path(__file__).parent
|
if (P/'source-order-regroup-result.json').exists():
|
import runpy
|
runpy.run_path(str(P/'verify_source_orders.py'),run_name='__main__')
|
sys.exit(0)
|
sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps'))
|
import pymysql
|
plan=json.loads((P/'import-plan.json').read_text(encoding='utf8'))
|
result=json.loads((P/'import-result.json').read_text(encoding='utf8'))
|
before=json.loads((P/'locked-before-import.json').read_text(encoding='utf8'))
|
D=Decimal
|
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)
|
def canonical(x):return json.loads(json.dumps(x,default=str))
|
report={'existing_rows_unchanged':{},'counts':{},'sums':{},'errors':[],'notes':['数据库读回核验；未执行线上HTTP界面验证','两票合并记录未拆分金额；缺原票附件的回款通过合同关联，原票号保存在备注','未约定账期，计划日期为开票或销售日后30天测算','报价状态按代码和前端CONVERTED=10，未采用过期表注释50']}
|
with c.cursor() as q:
|
for t,b in before['tables'].items():
|
q.execute('SELECT * FROM `'+t+'` ORDER BY id');current={r['id']:canonical(r) for r in q.fetchall()}
|
changed=[r['id'] for r in b['rows'] if current.get(r['id'])!=r]
|
report['existing_rows_unchanged'][t]={'rows':len(b['rows']),'changed_ids':changed}
|
assert not changed,(t,changed)
|
for t,prefix in [('crm_sale_quotation','BJ-'),('crm_contract','HT-'),('erp_sale_order','XSDD-'),('crm_invoice','KP-'),('crm_receivable','HK-')]:
|
q.execute('SELECT COUNT(*) n,SUM('+('price' if t in ['crm_invoice','crm_receivable'] else 'total_price')+') amount FROM `'+t+'` WHERE no LIKE %s AND deleted=0',(prefix+'202609-L%',));r=q.fetchone();report['counts'][t]=r['n'];report['sums'][t]=str(r['amount'])
|
assert r['n']==plan['counts'][{'crm_sale_quotation':'sales','crm_contract':'sales','erp_sale_order':'sales','crm_invoice':'invoices','crm_receivable':'receivables'}[t]]
|
if t not in ['crm_invoice']:assert r['amount']==D(plan['totals']['received' if t=='crm_receivable' else 'gross'])
|
q.execute("SELECT COUNT(*) n,SUM(p.price) amount FROM crm_receivable_plan p JOIN crm_contract c ON c.id=p.contract_id WHERE c.no LIKE 'HT-202609-L%%' AND p.deleted=0 AND c.deleted=0");r=q.fetchone();assert r['n']==plan['counts']['plans'] and r['amount']==D(plan['totals']['outstanding']);report['counts']['crm_receivable_plan']=r['n'];report['sums']['crm_receivable_plan']=str(r['amount'])
|
for g,r in zip(plan['groups'],result['groups']):
|
q.execute('SELECT id,owner_user_id,deleted FROM crm_customer WHERE id=%s',(r['customer_id'],));customer=q.fetchone();assert customer and customer['deleted']==0 and customer['owner_user_id']==291
|
q.execute('SELECT COUNT(*) n FROM crm_permission WHERE biz_type=2 AND biz_id=%s AND user_id=291 AND deleted=0',(r['customer_id'],));assert q.fetchone()['n']>0
|
q.execute('SELECT id,customer_id,audit_status,total_price,order_id,order_no FROM crm_contract WHERE id=%s AND deleted=0',(r['contract_id'],));ct=q.fetchone();assert ct['audit_status']==20 and ct['customer_id']==r['customer_id'] and ct['order_id']==r['order_id']
|
q.execute('SELECT id,contract_id,customer_id,status,total_price,order_time FROM erp_sale_order WHERE id=%s AND deleted=0',(r['order_id'],));order=q.fetchone();assert order['status']==20 and order['contract_id']==ct['id'] and order['customer_id']==ct['customer_id'] and order['total_price']==ct['total_price'] and str(order['order_time'])==g['day']
|
for a,l in zip(g['lines'],r['lines']):
|
q.execute('SELECT id,status FROM mdm_item WHERE id=%s AND deleted=0',(l['item_id'],));assert q.fetchone()['status']==0
|
q.execute('SELECT id,status FROM mes_md_unit_measure WHERE id=%s AND deleted=0',(l['unit_id'],));assert q.fetchone()['status']==0
|
for t,id,unit,parentkey,parent in [('crm_contract_product',l['contract_product_id'],'item_unit_id','contract_id',ct['id']),('crm_sale_quotation_product',l['quotation_product_id'],'item_unit_id','quotation_id',r['quotation_id']),('erp_sale_order_items',l['order_item_id'],'product_unit_id','order_id',r['order_id'])]:
|
q.execute('SELECT * FROM `'+t+'` WHERE id=%s AND deleted=0',(id,));item=q.fetchone();assert item['count']==D(a['quantity']) and item[unit]==l['unit_id'] and item[parentkey]==parent and item['total_price'] is not None
|
if r['invoice_id']:
|
q.execute('SELECT * FROM crm_invoice WHERE id=%s AND deleted=0',(r['invoice_id'],));inv=q.fetchone();assert inv['audit_status']==20 and inv['contract_id']==ct['id'] and inv['customer_id']==ct['customer_id'] and inv['price']==ct['total_price'] and inv['invoice_no']=='/'.join(g['invoice_numbers']) and str(inv['invoice_time'])==g['day']
|
if r['receivable_id']:
|
q.execute('SELECT * FROM crm_receivable WHERE id=%s AND deleted=0',(r['receivable_id'],));rc=q.fetchone();assert rc['audit_status']==20 and rc['contract_id']==ct['id'] and rc['price']==D(g['received']) and str(rc['return_time'])==g['day'] and rc['invoice_id'] is None
|
if r['plan_id']:
|
q.execute('SELECT * FROM crm_receivable_plan WHERE id=%s AND deleted=0',(r['plan_id'],));rp=q.fetchone();assert rp['contract_id']==ct['id'] and rp['receivable_id'] is None and rp['price']==D(g['outstanding']) and str(rp['return_time'])==g['plan_day']
|
for type,id in [(5,ct['id']),(9,r['quotation_id']),(10,r['invoice_id']),(7,r['receivable_id']),(8,r['plan_id'])]:
|
if id:q.execute('SELECT COUNT(*) n FROM crm_permission WHERE biz_type=%s AND biz_id=%s AND user_id=291 AND level=1 AND deleted=0',(type,id));assert q.fetchone()['n']==1
|
for code,id in result['new_items'].items():
|
q.execute('SELECT m.code,m.status,s.mdm_item_id,s.unit_measure_id FROM mdm_item m JOIN mes_md_item s ON s.id=m.id WHERE m.id=%s AND m.deleted=0 AND s.deleted=0',(id,));m=q.fetchone();assert m['code']==code and m['status']==0 and m['mdm_item_id']==id
|
q.execute("SELECT SUM(CASE WHEN `count`<0 THEN 1 ELSE 0 END) negative_lines FROM erp_sale_order_items i JOIN erp_sale_order o ON o.id=i.order_id WHERE o.no LIKE 'XSDD-202609-L%%' AND i.deleted=0 AND o.deleted=0");report['included_return_lines']=int(q.fetchone()['negative_lines']);assert report['included_return_lines']==1
|
q.execute("SELECT COUNT(*) n FROM crm_sale_quotation WHERE no IN ('BJ-202609-L062','BJ-202609-L071')");assert q.fetchone()['n']==0
|
c.close();report['passed']=True
|
(P/'verification-result.json').write_text(json.dumps(report,ensure_ascii=False,default=str,indent=2),encoding='utf8')
|
print(json.dumps({'passed':True,'counts':report['counts'],'sums':report['sums'],'existing_rows_checked':sum(v['rows'] for v in report['existing_rows_unchanged'].values()),'included_return_lines':report['included_return_lines']},ensure_ascii=False))
|
