import sys,json,csv,re
|
from pathlib import Path
|
from datetime import datetime
|
from collections import defaultdict
|
sys.stdout.reconfigure(encoding='utf-8')
|
sys.path.insert(0,str(Path(__file__).parent/'iqc-import-20261012/deps'))
|
import pymysql
|
apply='--apply' in sys.argv
|
out=Path(__file__).parent/'schedule-diagnosis-20261015'
|
with pymysql.connect(host='36.213.150.237',port=10000,user='root',password=sys.stdin.readline().strip(),database='mom-hm',charset='utf8mb4',connect_timeout=15,cursorclass=pymysql.cursors.DictCursor) as c:
|
with c.cursor() as q:
|
data={}
|
for t in ['mes_pro_route','mes_pro_route_process','mes_pro_route_product','mes_pro_route_product_bom','mes_md_product_bom','mes_md_item','mes_pro_work_order']:
|
q.execute('SELECT * FROM '+t+' WHERE deleted=0 ORDER BY id'+(' FOR UPDATE' if apply else ''));data[t]=q.fetchall()
|
items={i['id']:i for i in data['mes_md_item']}
|
processes=defaultdict(list);products=defaultdict(set);bom=defaultdict(list);base=defaultdict(list)
|
for p in data['mes_pro_route_process']:processes[p['route_id']].append(p)
|
for p in data['mes_pro_route_product']:products[p['route_id']].add(p['item_id'])
|
for b in data['mes_pro_route_product_bom']:bom[(b['route_id'],b['product_id'])].append(b)
|
for b in data['mes_md_product_bom']:base[b['item_id']].append(b)
|
# 502 is an existing gift-box assembly product: keep its real BOM quantities.
|
additions=[]
|
if 502 in products[57] and not bom[(57,502)]:
|
for b in base[502]:
|
process=10 if b['bom_item_id'] in (628,643) else 11
|
assert process in {p['process_id'] for p in processes[57]}
|
assert b['bom_item_id'] in items and b['quantity']>0
|
additions.append({'route_id':57,'process_id':process,'product_id':502,'item_id':b['bom_item_id'],'quantity':b['quantity'],'remark':f"沿用产品BOM行{b['id']}原物料及用量；成品小袋用于内包装组装，盒箱及标签用于外包装"})
|
bom[(57,502)].extend(additions)
|
output_changes=[];enable=[];blocked=[];missing=[]
|
for r in data['mes_pro_route']:
|
ps=processes[r['id']]
|
absent=[pid for pid in products[r['id']] if not bom[(r['id'],pid)]]
|
for pid in absent:missing.append({'routeId':r['id'],'routeName':r['name'],'productId':pid,'productName':items[pid]['name'],'specification':items[pid]['specification']})
|
valid=bool(ps) and any(p['key_flag']==b'\x01' for p in ps) and not absent
|
if r['status']!=0:
|
if valid:enable.append(r['id'])
|
else:blocked.append({'routeId':r['id'],'routeName':r['name'],'missingProductIds':absent})
|
# A single-product route can use an explicit product without changing behavior.
|
# Shared routes must retain the native work-order-product fallback.
|
linked=products[r['id']]
|
historical={o['product_id'] for o in data['mes_pro_work_order'] if o['route_id']==r['id']}
|
if len(linked)==1 and historical<=linked:
|
pid=next(iter(linked))
|
if items[pid]['status']==0:
|
for p in ps:
|
if not p['output_item_id']:output_changes.append({'id':p['id'],'output':str(pid)})
|
# Concrete reference candidates, kept outside live production formulas.
|
candidates={987:524,988:525,989:526,1013:524,776:524,979:484,1151:962,1152:541,1153:540,974:523,992:523,1122:523,985:534,986:533,984:529,1157:507,1540:529,1155:535,973:528,976:536,983:531,1508:536,1509:536,975:484,1544:484,1154:537,1158:520,1543:520,860:540,639:500,663:509,815:533,643:507,807:529,628:497,649:504,811:531,835:497,963:537,613:494,641:501,669:511,1510:514,2004:484,2005:492,2006:501}
|
refs=[]
|
for row in missing:
|
source=candidates.get(row['productId'])
|
if source in items:
|
for b in base[source]:
|
refs.append({**row,'sourceProductId':source,'sourceProductName':items[source]['name'],'sourceSpecification':items[source]['specification'],'materialId':b['bom_item_id'],'materialName':items[b['bom_item_id']]['name'],'sourceQuantity':str(b['quantity']),'review':'参考同类产品；原料与包材需按目标净含量、包装数量及单位换算，原浆浓度和配方需工艺复核；尚未写入正式BOM'})
|
with (out/'投入配置参考清单.csv').open('w',encoding='utf-8-sig',newline='') as f:
|
writer=csv.DictWriter(f,fieldnames=list(refs[0]) if refs else ['productId']);writer.writeheader();writer.writerows(refs)
|
report={'inputAdded':len(additions),'explicitOutputsAdded':len(output_changes),'routesToEnable':enable,'blockedRoutes':blocked,'missingProductCount':len(missing),'referenceRows':len(refs),'nativeOutputFallback':'多产品共用路线按工单产品产出，避免固定SKU串料'}
|
(out/'route-config-plan.json').write_text(json.dumps(report,ensure_ascii=False,indent=2),encoding='utf-8')
|
print(json.dumps(report,ensure_ascii=False))
|
if apply:
|
stamp=datetime.now().strftime('%Y%m%d%H%M%S')
|
(out/f'route-config-before-{stamp}.json').write_text(json.dumps(data,ensure_ascii=False,default=str,indent=2),encoding='utf-8')
|
for b in additions:
|
cols=list(b)
|
q.execute('INSERT INTO mes_pro_route_product_bom ('+','.join(cols)+',create_time,update_time,deleted) VALUES ('+','.join(['%s']*len(cols))+',NOW(),NOW(),0)',list(b.values()))
|
for row in output_changes:q.execute('UPDATE mes_pro_route_process SET output_item_id=%s,update_time=NOW() WHERE id=%s AND deleted=0',(row['output'],row['id']))
|
for rid in enable:q.execute('UPDATE mes_pro_route SET status=0,update_time=NOW() WHERE id=%s AND deleted=0',(rid,))
|
for rid in enable:
|
q.execute('SELECT status FROM mes_pro_route WHERE id=%s',(rid,));assert q.fetchone()['status']==0
|
for row in output_changes:
|
q.execute('SELECT output_item_id FROM mes_pro_route_process WHERE id=%s',(row['id'],));assert q.fetchone()['output_item_id']==row['output']
|
q.execute('SELECT status,COUNT(*) n FROM mes_pro_route WHERE deleted=0 GROUP BY status');report['routeStatusesAfter']=q.fetchall()
|
c.commit()
|
(out/f'route-config-result-{stamp}.json').write_text(json.dumps(report,ensure_ascii=False,default=str,indent=2),encoding='utf-8')
|
print(json.dumps({'committed':True,'routeStatusesAfter':report['routeStatusesAfter']},ensure_ascii=False))
|
