import sys,json,collections
|
from pathlib import Path
|
from decimal import Decimal
|
sys.stdout.reconfigure(encoding='utf-8')
|
sys.path.insert(0,str(Path('scripts/iqc-import-20261012/deps').resolve()))
|
import pymysql
|
P=Path(__file__).parent;TAG='OCT0814-DEMO'
|
r=json.loads((P/('result-current.json' if (P/'result-current.json').exists() else 'result-applied.json')).read_text(encoding='utf8'));assert r['committed']
|
normalized=json.loads((P/'normalize-applied.json').read_text(encoding='utf8')) if (P/'normalize-applied.json').exists() else None
|
if normalized:
|
assert normalized['applied']
|
for link in r['links']:
|
for key,value in list(link.items()):
|
if isinstance(value,str) and value in normalized['mapping']:link[key]=normalized['mapping'][value]
|
batch_filter='tp.batch_id IN ('+','.join(str(x) for x in normalized['scoped_ids']['mes_wm_batch'])+')'
|
stock_filter='id IN ('+','.join(str(x) for x in normalized['scoped_ids']['mes_wm_material_stock'])+')'
|
else:
|
batch_filter="tp.batch_code LIKE 'OCT0814-DEMO%%'"
|
stock_filter="batch_code LIKE 'OCT0814-DEMO%%'"
|
backup=json.loads((P/'before-seed-backup.json').read_text(encoding='utf8'))
|
canonical=lambda v:json.loads(json.dumps(v,ensure_ascii=False,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,connect_timeout=15,read_timeout=60)
|
with c.cursor() as q:
|
q.execute('SELECT DATABASE() db');assert q.fetchone()['db']=='mom-hm'
|
q.execute('SELECT p.id AS target,src.id AS source FROM mes_wm_transaction tp JOIN mes_wm_product_produce pp ON pp.id=tp.biz_id AND tp.biz_type=124 AND pp.deleted=0 JOIN mes_pro_feedback f ON f.id=pp.feedback_id AND f.deleted=0 JOIN mes_wm_item_consume ic ON ic.feedback_id=f.id AND ic.deleted=0 JOIN mes_wm_transaction tc ON tc.biz_id=ic.id AND tc.biz_type=123 AND tc.deleted=0 JOIN mes_wm_batch p ON p.id=tp.batch_id JOIN mes_wm_batch src ON src.id=tc.batch_id WHERE '+batch_filter)
|
graph=collections.defaultdict(set)
|
for e in q.fetchall():graph[e['target']].add(e['source'])
|
raw_counts=[]
|
for link in r['links']:
|
q.execute('SELECT s.*,ct.total_price AS contract_total,qu.total_price AS quotation_total,qu.contract_id AS quoted_contract,mp.sale_order_id AS planned_order,mp.sale_order_no AS planned_no,mp.sale_order_item_id AS planned_item,mp.work_order_id,mp.status AS plan_status FROM erp_sale_order s JOIN crm_contract ct ON ct.id=s.contract_id JOIN crm_sale_quotation qu ON qu.id=%s JOIN mes_pro_mps mp ON mp.id=%s WHERE s.id=%s',(link['quotation_id'],link['mps_id'],link['sale_order_id']))
|
row=q.fetchone();assert row and row['quoted_contract']==row['contract_id'] and row['planned_order']==row['id'] and row['planned_no']==row['no'] and row['planned_item']==link['order_item_id'] and row['work_order_id']==link['work_order_id'] and row['plan_status']==2
|
assert row['total_price']==row['contract_total']==row['quotation_total']==Decimal(link['total']) and row['production_status']==1 and row['out_status']==3 and row['out_count']==Decimal(link['quantity'])
|
q.execute('SELECT SUM(price) total,COUNT(*) n,SUM(receivable_id IS NOT NULL) paid FROM crm_receivable_plan WHERE contract_id=%s AND deleted=0',(link['contract_id'],));p=q.fetchone();assert p['n']==2 and p['total']==row['total_price'] and p['paid']==0
|
q.execute('SELECT * FROM mes_qc_oqc WHERE id=%s',(link['oqc_id'],));qc=q.fetchone();assert qc['batch_code']==link['final_batch_code'] and qc['check_result']==1 and qc['status']==4
|
q.execute('SELECT COUNT(*) n FROM mes_wm_product_sales_line l JOIN mes_wm_product_sales s ON s.id=l.sales_id WHERE s.sale_order_id=%s AND s.status=4 AND l.batch_code=%s AND l.oqc_id=%s',(row['id'],link['final_batch_code'],link['oqc_id']));assert q.fetchone()['n']==1
|
stack=[link['final_batch_id']];seen=set();leaves=set()
|
while stack:
|
node=stack.pop()
|
if node in seen:continue
|
seen.add(node)
|
if graph[node]:stack.extend(graph[node])
|
else:leaves.add(node)
|
assert leaves and len(seen)>len(leaves)
|
for bid in leaves:
|
q.execute('SELECT COUNT(*) n FROM mes_wm_item_receipt_line l JOIN mes_wm_item_receipt h ON h.id=l.receipt_id JOIN mes_qc_iqc qc ON qc.id=h.iqc_id WHERE l.batch_id=%s AND h.status=4 AND qc.status=4 AND qc.check_result=1',(bid,));assert q.fetchone()['n']==1
|
raw_counts.append({'sales_order':row['no'],'final_batch':link['final_batch_code'],'traced_batches':len(seen),'source_raw_batches':len(leaves)})
|
q.execute('SELECT COUNT(*) n,SUM(quantity) total,SUM(reserved_quantity) reserved FROM mes_wm_material_stock WHERE '+stock_filter+' AND deleted=0');stock=q.fetchone();assert stock['n']==62 and stock['total']==0 and stock['reserved']==0
|
q.execute('SELECT * FROM mes_wm_material_stock WHERE deleted=0 ORDER BY id');current={x['id']:canonical(x) for x in q.fetchall()};assert all(current[x['id']]==x for x in backup['stock'])
|
q.execute('SELECT * FROM crm_receivable WHERE deleted=0 ORDER BY id');assert canonical(q.fetchall())==backup['receivables']
|
q.execute('SELECT * FROM mes_energy_record WHERE remark LIKE %s AND deleted=0 ORDER BY record_date',('%'+TAG+'%',));energy=q.fetchall();assert len(energy)==4 and {x['energy_type'] for x in energy}=={'WATER','ELECTRICITY','STEAM'}
|
for record in energy:assert record['record_date'].weekday()<5 and record['record_date'].isoformat() in r['dates'] and (record['quantity']*record['unit_price']).quantize(Decimal('.01'))==record['amount']
|
result={'verified':True,'database':'36.213.150.237:10000/mom-hm','business_chains':4,'source_raw_batches':sum(x['source_raw_batches'] for x in raw_counts),'trace_chains':raw_counts,'energy_records':4,'no_actual_receivables_added':True,'original_stock_unchanged':True}
|
(P/'final-verification.json').write_text(json.dumps(result,ensure_ascii=False,indent=2),encoding='utf8')
|
units={9:'盒',24:'个'}
|
lines=['# 10月8日至14日业务链补充清单','','已写入指定远程 `mom-hm`。日期采用 2026-10-08、09、12、14，避开双休日。全部新增业务标明演示用途；不是实际交易、投料、检测或物流凭证。','','## 营销至出库的四条链','','每条链包括商机、跟进、报价、合同、销售订单、生产主计划、工单、工序任务与参数、报工、来料及过程检验、产出、成品入库、出货检验、销售出库和两期回款计划。未录入实际回款。','']
|
for link in r['links']:
|
lines.extend([f"### {link['date']} · {link['product']}（{link['spec']}）",'',f"- 客户：{link['customer']}；数量：{link['quantity']} {units[link['unit_id']]}。",f"- 报价：`{link['quotation_no']}`；合同：`{link['contract_no']}`。",f"- 销售单号：`{link['sale_order_no']}`；金额：{Decimal(link['total']):,.2f} 元。",f"- 主计划：`{link['mps_code']}`；生产工单：`{link['work_order_code']}`。",f"- 最终成品批次：`{link['final_batch_code']}`，可在质量批次追溯中查询。",f"- 工序任务/报工：各 {len(link['tasks'])} 条；回款计划：2 期，总额与合同一致。",''])
|
lines.extend(['## 业务人员','','营销：朱莉（291）；生产：李万娟（244）、张慧清（273）；质量：华生菁（289）、罗军（290）；仓库：李万娟（244，参考已有仓库单据）；能源：赵磊（243）。','','## 能源','','4 条独立记录，覆盖水、电、蒸汽；单价参考既有台账，用量为演示补充，不与工单建立关联。',''])
|
for x in energy:lines.append(f"- {x['record_date']}：{x['energy_type']}，{x['usage_area']}，用量 {x['quantity']}，金额 {x['amount']} 元。")
|
lines.extend(['','## 核验与保护','','- 4 条成品批次均能追溯到原料来料批次、来料检验和全部工序，并查到销售去向及出货检验。','- 16 个独立原料批次、26 个工序批次、124 条库存流水；新增库存入出平衡，原有库存未改动。','- 24 张质检单均有指标明细和样例检验结果。','- 新增 3 个明确标为演示用的适用检验方案，不改原质量标准与工艺/BOM主数据。','- 报价、合同、销售金额一致；8 条回款计划总额按合同校验，计划日期也避开双休日。','- 数据库事务演练、提交后新连接复核均通过。',''])
|
if normalized:
|
lines=[x.replace('全部新增业务标明演示用途；不是实际交易、投料、检测或物流凭证。','编号与业务名称已规范化，备注保留补录来源；生成的检验结果保留样例标识。').replace('新增 3 个明确标为演示用的适用检验方案','新增 3 个适用检验方案，备注保留补录来源') for x in lines]
|
(P/'业务链核对清单.md').write_text('\n'.join(lines),encoding='utf8')
|
print(json.dumps(result,ensure_ascii=False))
|
c.close()
|
