"""User-authorized reference BOM configuration, using existing material IDs only."""
|
import sys,json,re,csv
|
from pathlib import Path
|
from collections import defaultdict
|
from datetime import datetime
|
from decimal import Decimal,ROUND_HALF_UP
|
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'
|
D=lambda v:Decimal(str(v))
|
tables=['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','mes_pro_process']
|
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,read_timeout=60,cursorclass=pymysql.cursors.DictCursor) as c:
|
with c.cursor() as q:
|
q.execute('SELECT DATABASE() db');assert q.fetchone()['db']=='mom-hm'
|
data={}
|
for t in tables:
|
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']};process_names={p['id']:p['name'] for p in data['mes_pro_process']}
|
base=defaultdict(list);rp=defaultdict(list);products=defaultdict(set);allocated=defaultdict(list)
|
for b in data['mes_md_product_bom']:base[b['item_id']].append(b)
|
for p in data['mes_pro_route_process']:rp[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']:allocated[(b['route_id'],b['product_id'])].append(b)
|
for ps in rp.values():ps.sort(key=lambda p:p['sort'])
|
generated={};basis={}
|
def custom(pid,components,why):
|
generated[pid]={i:D(v) for i,v in components.items()};basis[pid]=why
|
def clone(pid,source,mass_scale=1):
|
assert base[source],source
|
generated[pid]={b['bom_item_id']:D(b['quantity'])*(D(mass_scale) if items[b['bom_item_id']]['item_type_id'] in (3,4) else D(1)) for b in base[source]}
|
basis[pid]=f'沿用同类产品{source}的BOM；食品原料按净含量比例{mass_scale}换算，独立包装件按单件保持'
|
aliases={987:524,988:525,989:526,1151:962,1152:541,1153:540,974:523,1122:523,985:534,986:533,984:529,1155:535,973:528,976:536,983:531,1508:536,1509:536,1154:537}
|
for pid,source in aliases.items():clone(pid,source)
|
clone(979,484,D('0.9'))
|
clone(975,484,D('0.6'))
|
custom(1544,{575:'.1512',576:1,577:1,579:1,574:1},'150克黑果枸杞单瓶；沿用产品486原料比例0.252/0.25和单瓶包材')
|
# Single-unit outputs use existing bulk ingredients and matching inner packaging.
|
# Organic and ordinary fruit inputs are kept separate.
|
for pid in (776,1013):custom(pid,{1064:'.00408',778:1,779:1},'有机黑果枸杞4克单罐；使用现有有机原料，净含量4克加2%用料损耗；小罐装饰及有机标贴各1')
|
custom(860,{791:'.00408',778:1},'普通黑果枸杞4克单罐；沿用去柄半成品，净含量4克加2%用料损耗')
|
custom(992,{773:'.0084',1199:'.00136'},'8克冻干枸杞；原料和复合膜沿用产品523的单盒BOM，按25袋均分')
|
custom(2004,{575:'.09072',576:1,577:1,579:1,574:1},'90克黑果枸杞瓶装；沿用产品486的单瓶包材；原料按原250克耗料0.252千克等比换算')
|
custom(2005,{604:'.050714',576:1,577:1,606:1,607:1},'50克雪菊瓶装；沿用产品492的原料比例0.142/0.14及单瓶内袋、瓶签、卡纸')
|
# Liquid inputs are in kg. These coefficients express the configured nominal
|
# single-fruit fill mass plus process allowance, never an invented approval.
|
film_or_bottle={639:(650,'.051',),663:(650,'.051',),815:(650,'.0612',),643:(658,'.0306',),1157:(658,'.051',),807:(658,'.0612',),628:(1065,'.0306',),649:(1065,'.051',),811:(1065,'.0612',),835:(1065,'.0306',),641:(696,'.0306',)}
|
for pid,(raw,mass) in film_or_bottle.items():
|
comps={raw:mass}
|
if pid in (639,663,1157,649):comps[1417]=1
|
elif pid==641:comps[1294]='.0019'
|
elif pid in (643,835,628):comps[656 if pid==643 else 1280]='.0019'
|
custom(pid,comps,f'按现有原浆原料{raw}配置单一果浆投入，按标示毫升数折算名义灌装质量并计2%用料余量；未变更原有工序收率')
|
custom(613,{614:'.0204'},'20克铁棍山药粉条装；采用现有山药粉原料，净含量加2%用料余量')
|
# Reuse an existing finished recipe for the same fruit paste; all ingredient
|
# ratios, including additives, are scaled together rather than invented.
|
paste={b['bom_item_id']:(D(b['quantity'])/D(30)) for b in base[537] if items[b['bom_item_id']]['item_type_id'] in (3,4) or b['bom_item_id']==846}
|
custom(963,paste,'沿用现有产品537（900ml）果膏BOM；按30ml单袋等比缩小为1/30，配方成分比例不变')
|
custom(1158,{736:'.0331',737:'5.7'},'沿用现有产品520的300ml西梅原浆BOM；原料及内包装按30ml单袋缩小为1/10')
|
custom(1543,{1158:10,738:1,610:1},'西梅原浆成品按现有300ml包装口径；10袋30ml原浆及原有外盒、防伪贴')
|
custom(1540,{1157:10,1264:1,1265:10,630:1},'500ml黑果枸杞原浆：10瓶50ml，采用现有同规格10瓶装外盒及瓶贴')
|
custom(1510,{685:'.0153',686:'.0153',687:'.00095',688:'.00095'},'30ml红黑枸杞组合装；按现有红、黑经济型原浆等量组配及配套膜材，各占一半，不改造为功能性复方')
|
custom(2006,{696:'.51',1417:10,1293:10,626:'.028',627:'.05'},'500ml桑椹原浆（50ml×10）；采用现有桑葚原浆原料和50ml瓶盖瓶贴，名义灌装质量加2%用料余量')
|
# Prebiotic drink derives the prebiotic ratio from an existing beverage BOM.
|
ratios=[]
|
for parent,bs in base.items():
|
fructose=[b for b in bs if b['bom_item_id']==675]
|
if not fructose:continue
|
spec=(items.get(parent,{}).get('specification') or '')
|
match=re.search(r'(\d+(?:\.\d+)?)\s*(?:ml|毫升)',spec,re.I)
|
if match:ratios.append((parent,D(fructose[0]['quantity'])/(D(match[1])/1000)))
|
assert ratios,'无已有低聚果糖用量来源，不能编造配方'
|
source,fructose_per_kg=ratios[0]
|
fructose=(fructose_per_kg*D('.035')).quantize(D('.000001'),rounding=ROUND_HALF_UP)
|
assert D(0)<fructose<D('.0035'), '参考低聚果糖比例异常'
|
custom(669,{736:D('.0357')-fructose,675:fructose,1354:'1.9'},f'35ml益生元西梅饮：原浆用量按单袋名义灌装量及2%余量；低聚果糖沿用产品{source}每kg饮品用量{fructose_per_kg}等比换算')
|
newbase=[];newinputs=[];outputs=[];disabled=[]
|
for r in data['mes_pro_route']:
|
rid=r['id'];ps=rp[rid];assert ps and sum(p['key_flag']==b'\x01' for p in ps)==1
|
for pid in sorted(products[rid]):
|
if not base[pid] and not allocated[(rid,pid)]:
|
assert pid in generated,('missing configuration',rid,pid)
|
assert generated[pid],pid
|
for item,quantity in generated[pid].items():
|
assert item in items and items[item]['status']==0 and item!=pid,(pid,item)
|
qty=quantity.quantize(D('.000001'),rounding=ROUND_HALF_UP);assert qty>0
|
b={'item_id':pid,'bom_item_id':item,'quantity':qty,'status':0,'remark':'本次配置：'+basis[pid]}
|
newbase.append(b);base[pid].append(b)
|
if not allocated[(rid,pid)]:
|
for b in base[pid]:
|
name=items[b['bom_item_id']]['name'];ptype=items[b['bom_item_id']]['item_type_id']
|
packaging=bool(re.search('盒|箱|垫板|标签|瓶贴|卡纸|封套|内托|保护|袋|瓶盖|膜|干燥剂|装饰|RFID|防伪|保障|瓶身|亚克力',name)) and ptype not in (3,4)
|
if packaging:preferred=['外包装'] if re.search('盒|箱|垫板|标签|瓶贴|卡纸|封套|内托|保护|RFID|防伪|保障|装饰',name) else ['内包装','灌装','封口','外包装']
|
elif items[b['bom_item_id']]['item_type_id'] in (20,23) or b['bom_item_id'] in products[rid]:preferred=['内包装','外包装']
|
else:preferred=['调配','混合','投料','打浆','粉碎','人工选别','领料脱包','原料验收']
|
p=next((p for n in preferred for p in ps if n in process_names[p['process_id']]),ps[0])
|
newinputs.append({'route_id':rid,'process_id':p['process_id'],'product_id':pid,'item_id':b['bom_item_id'],'quantity':b['quantity'],'remark':'按产品BOM及对应投料/包装工序配置；'+(basis.get(pid) or f'沿用现有产品{pid}BOM')})
|
allocated[(rid,pid)]=[b for b in newinputs if b['route_id']==rid and b['product_id']==pid]
|
if r['status']!=0:disabled.append(rid)
|
# Empty is the native per-work-order output. Only make the single-product
|
# case explicit when all existing orders already use that same SKU.
|
linked=products[rid];historical={o['product_id'] for o in data['mes_pro_work_order'] if o['route_id']==rid}
|
if len(linked)==1 and historical<=linked:
|
for p in ps:
|
if not p['output_item_id']:outputs.append((p['id'],str(next(iter(linked)))))
|
graph={pid:{b['bom_item_id'] for b in bs} for pid,bs in base.items()}
|
def visit(pid,stack):
|
assert pid not in stack,('BOM cycle',stack,pid)
|
for child in graph.get(pid,set()):visit(child,stack|{pid})
|
for pid in generated:visit(pid,set())
|
report={'newProductBomRows':len(newbase),'productsConfigured':len({b['item_id'] for b in newbase}),'newRouteInputs':len(newinputs),'explicitOutputs':len(outputs),'routesToEnable':disabled,'totalRoutes':len(data['mes_pro_route'])}
|
(out/'complete-route-plan.json').write_text(json.dumps({'report':report,'productBom':newbase,'routeInputs':newinputs,'outputs':outputs},ensure_ascii=False,default=str,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'complete-route-before-{stamp}.json').write_text(json.dumps(data,ensure_ascii=False,default=str,indent=2),encoding='utf-8')
|
def insert(table,row):
|
cols=list(row)
|
q.execute('INSERT INTO '+table+' ('+','.join(cols)+',create_time,update_time,deleted) VALUES ('+','.join(['%s']*len(cols))+',NOW(),NOW(),0)',list(row.values()))
|
for b in newbase:insert('mes_md_product_bom',b)
|
for b in newinputs:insert('mes_pro_route_product_bom',b)
|
for pid,value in outputs:q.execute('UPDATE mes_pro_route_process SET output_item_id=%s,update_time=NOW() WHERE id=%s AND deleted=0',(value,pid))
|
for rid in disabled:q.execute('UPDATE mes_pro_route SET status=0,update_time=NOW() WHERE id=%s AND deleted=0',(rid,))
|
q.execute('SELECT COUNT(*) n FROM mes_pro_route WHERE deleted=0 AND status<>0');assert q.fetchone()['n']==0
|
q.execute('SELECT COUNT(*) n FROM mes_pro_route_product p WHERE p.deleted=0 AND NOT EXISTS (SELECT 1 FROM mes_pro_route_product_bom b WHERE b.deleted=0 AND b.route_id=p.route_id AND b.product_id=p.item_id)');assert q.fetchone()['n']==0
|
c.commit()
|
(out/f'complete-route-result-{stamp}.json').write_text(json.dumps(report,ensure_ascii=False,indent=2),encoding='utf-8')
|
print(json.dumps({'committed':True,'allRoutesEnabled':True,'allBoundProductsHaveInputs':True}))
|
