mom-pro2-after.git

"""User-authorized, isolated demonstration data; default mode validates then rolls back."""
import sys,json,csv,re,collections,hashlib
from pathlib import Path
from decimal import Decimal,ROUND_HALF_UP
from datetime import datetime,timedelta,date
sys.stdout.reconfigure(encoding='utf-8')
sys.path.insert(0,str(Path('scripts/iqc-import-20261012/deps').resolve()))
import pymysql
P=Path(__file__).parent; D=Decimal
TAG='OCT0814-DEMO'
NOTE='10月8日至14日业务链演示补充；非真实交易、投料、检验或物流凭证；沿用现有示意BOM及工艺主数据。'
apply='--apply' in sys.argv
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=90,autocommit=False)
schema=json.loads((P/'schema-snapshot.json').read_text(encoding='utf8'))
a=json.loads((P/'assets.json').read_text(encoding='utf8'))
fields={t:{x['Field']:x for x in v['columns']} for t,v in schema.items() if isinstance(v,dict) and 'columns' in v}
inserted=collections.defaultdict(list); rows=collections.defaultdict(dict); sql_log=[]; links=[]; qc_links=[]; stock_keys={}; txids=[]
def dump(name,v): (P/name).write_text(json.dumps(v,ensure_ascii=False,indent=2,default=str),encoding='utf8')
def truth(v):return v in (1,True,b'\x01',"b'\\x01'")
def money(v):return D(v).quantize(D('.01'),rounding=ROUND_HALF_UP)
def qty(v):return D(v).quantize(D('.000001'),rounding=ROUND_HALF_UP)
def weekday(dt):
 while dt.weekday()>4:dt+=timedelta(days=1)
 return dt
def insert(t,v,user,at):
 v=dict(v);f=fields[t]
 for k,z in [('creator',str(user)),('updater',str(user)),('create_time',at),('update_time',at),('deleted',0),('tenant_id',0)]:
  if k in f:v.setdefault(k,z)
 if 'remark' in f:v.setdefault('remark',NOTE)
 assert set(v)<=set(f),(t,'unknown fields',set(v)-set(f))
 for k,z in list(v.items()):
  if f[k]['Type'].startswith('bit') and z is not None:v[k]=int(truth(z))
  if isinstance(z,str):
   m=re.match(r'(?:var)?char\((\d+)\)',f[k]['Type']);assert not m or len(z)<=int(m[1]),(t,k,'value too long')
 keys=list(v);sql='INSERT INTO `'+t+'` (`'+'`,`'.join(keys)+'`) VALUES ('+','.join(['%s']*len(keys))+')'
 q.execute(sql,tuple(v[k] for k in keys));rid=q.lastrowid
 assert rid and rid not in rows[t]
 inserted[t].append(rid);rows[t][rid]={'id':rid,**v};sql_log.append(q.mogrify(sql,tuple(v[k] for k in keys))+';')
 return rid
def update(t,rid,v):
 assert rid in inserted[t],('Refuse existing record update',t,rid)
 sql='UPDATE `'+t+'` SET '+','.join('`'+k+'`=%s' for k in v)+',update_time=update_time WHERE id=%s'
 args=tuple(v.values())+(rid,);q.execute(sql,args);assert q.rowcount==1
 rows[t][rid].update(v);sql_log.append(q.mogrify(sql,args)+';')
def stock(item,batch,wh,at):
 key=(item,batch,wh)
 if key in stock_keys:return stock_keys[key]
 loc=next(x for x in a['mes_wm_warehouse_location'] if x['warehouse_id']==wh and not truth(x['frozen']))
 area=next(x for x in a['mes_wm_warehouse_area'] if x['location_id']==loc['id'] and not truth(x['frozen']))
 b=rows['mes_wm_batch'][batch]
 sid=insert('mes_wm_material_stock',{'item_id':item,'batch_id':batch,'batch_code':b['code'],'warehouse_id':wh,'location_id':loc['id'],'area_id':area['id'],'quantity':D(0),'frozen_quantity':D(0),'reserved_quantity':D(0),'in_transit_quantity':D(0),'frozen':0,'receipt_time':at},244,at)
 stock_keys[key]=sid;return sid
def dims(sid):return {k:rows['mes_wm_material_stock'][sid][k] for k in ['batch_id','batch_code','warehouse_id','location_id','area_id']}
def tx(item,batch,wh,amount,biz,bid,code,line,at,user,related=None):
 sid=stock(item,batch,wh,at);s=rows['mes_wm_material_stock'][sid]
 new=s['quantity']+amount;assert new>=0,('Negative stock',sid,new)
 tid=insert('mes_wm_transaction',{'type':1 if amount>0 else 2,'biz_type':biz,'biz_id':bid,'biz_code':code,'biz_line_id':line,'material_stock_id':sid,'related_transaction_id':related,'item_id':item,'quantity':amount,**dims(sid),'transaction_time':at,'receipt_time':at},user,at)
 update('mes_wm_material_stock',sid,{'quantity':new});txids.append(tid);return tid,sid
def master_template(base,code,name,types,items,at):
 if base:
  source=templates[base];inds=[x for x in a['mes_qc_template_indicator'] if x['template_id']==base]
 else:
  source={};inds=[{'indicator_id':8,'sort_order':10,'judge_flag':1,'check_method':'演示外观、数量核验，不作为真实检验标准'},{'indicator_id':9,'sort_order':20,'judge_flag':1,'check_method':'演示数量一致性核验，不作为真实检验标准'}]
 tid=insert('mes_qc_template',{'code':code,'name':name,'types':types,'status':0},289,at)
 templates[tid]=rows['mes_qc_template'][tid]
 for x in inds:
  v={k:x[k] for k in ['indicator_id','sort_order','judge_flag','check_method','standard_value','unit_measure_id','threshold_max','threshold_min','doc_url'] if k in x}
  v['template_id']=tid;rid=insert('mes_qc_template_indicator',v,289,at);a['mes_qc_template_indicator'].append(rows['mes_qc_template_indicator'][rid])
 for item in items:insert('mes_qc_template_item',{'template_id':tid,'item_id':item},289,at)
 return tid
def qc(kind,code,item,amount,template,at,user,extra):
 t='mes_qc_'+kind;v={'code':code,'name':code+'-演示检验','template_id':template,'item_id':item,'check_quantity':amount,'qualified_quantity':amount,'unqualified_quantity':D(0),'critical_quantity':0,'major_quantity':0,'minor_quantity':0,'critical_rate':D(0),'major_rate':D(0),'minor_rate':D(0),'check_result':1,'inspect_date':at,'inspector_user_id':user,'status':4,'sampling_location':'演示样品，非真实质检记录',**extra}
 qid=insert(t,v,user,at);typ={'iqc':1,'ipqc':2,'oqc':3}[kind]
 result=insert('mes_qc_indicator_result',{'code':code+'-R','qc_id':qid,'qc_type':typ,'item_id':item},user,at)
 inds=[x for x in a['mes_qc_template_indicator'] if x['template_id']==template]
 assert inds,('Template has no indicators',template)
 for ti in inds:
  ind=indicators[ti['indicator_id']]
  insert(t+'_line',{kind+'_id':qid,'indicator_id':ind['id'],'tool':ind.get('tool'),'check_method':ti.get('check_method'),'standard_value':ti.get('standard_value'),'unit_measure_id':ti.get('unit_measure_id'),'max_threshold':ti.get('threshold_max'),'min_threshold':ti.get('threshold_min'),'critical_quantity':0,'major_quantity':0,'minor_quantity':0},user,at)
  if ind.get('result_type') in [1,2]:
   low=ti.get('threshold_min');high=ti.get('threshold_max');std=ti.get('standard_value')
   value=(D(str(low))+D(str(high)))/2 if low is not None and high is not None else D(str(std)) if std is not None else D(str(low)) if low is not None else D(str(high))/2 if high is not None else D(0)
   val=str(int(value)) if ind.get('result_type')==2 else str(value)
  else:val='演示：符合现有方案描述（非真实实测）'
  insert('mes_qc_indicator_result_detail',{'result_id':result,'indicator_id':ind['id'],'measure_no':1,'value':val,'remark':'演示样例结果；不作为真实检测或放行依据。'},user,at)
 qc_links.append({'table':t,'id':qid,'code':code,'result_id':result,'template_id':template});return qid
def params(wo,task,rp,user,at,quantity):
 tid=rp.get('param_template_id')
 if not tid or tid not in pt:return
 template=pt[tid];details=[x for x in a['mes_pd_process_param_template_detail'] if x['template_id']==tid]
 if not details:return
 record=insert('mes_pro_work_order_param_record',{'work_order_id':wo,'task_id':task,'template_id':tid,'template_code':template['template_code'],'template_name':template['template_name'],'record_no':TAG+'-'+str(task)},user,at)
 for x in details:
  numbers=re.findall(r'\d+(?:\.\d+)?',x.get('remark') or '')
  value=numbers[0] if numbers else str(quantity) if '数量' in x['param_name'] else '1'
  if x['param_type'] not in [1,2]:value='演示：按现有模板记录'
  insert('mes_pro_work_order_param_record_detail',{'record_id':record,'template_detail_id':x['id'],'param_code':x['param_code'],'param_name':x['param_name'],'param_type':x['param_type'],'required':int(truth(x['required'])),'unit_measure_id':x.get('unit_measure_id'),'param_value':value,'remark':'演示参数值；不是现场实测参数，原工艺限值未改动。'},user,at)
 
try:
 with c.cursor() as q:
  q.execute('SELECT DATABASE() db,NOW() now,GET_LOCK(%s,10) locked',(TAG,));state=q.fetchone();assert state['db']=='mom-hm' and state['locked']==1
  q.execute('SELECT COUNT(*) n FROM erp_sale_order WHERE no LIKE %s',(TAG+'%',));assert q.fetchone()['n']==0,'This dataset already exists; verify instead of repeating.'
  q.execute('SELECT COUNT(*) n FROM mes_energy_record WHERE remark LIKE %s',('%'+TAG+'%',));assert q.fetchone()['n']==0
  q.execute('SELECT * FROM mes_wm_material_stock WHERE deleted=0 ORDER BY id');old_stock=q.fetchall()
  q.execute('SELECT * FROM crm_receivable WHERE deleted=0 ORDER BY id');old_receivables=q.fetchall()
  protected={}
  for t in ['mdm_item','mes_md_item','mes_pro_route','mes_pro_route_product','mes_pro_route_process','mes_pro_route_product_bom','mes_qc_indicator','mes_wm_warehouse','mes_wm_warehouse_location','mes_wm_warehouse_area','crm_customer']:
   q.execute('SELECT * FROM `'+t+'` WHERE deleted=0 ORDER BY id');protected[t]=q.fetchall()
  dump('before-seed-backup.json',{'stock':old_stock,'receivables':old_receivables,'protected':protected,'at':state['now']})
  mdm={x['id']:x for x in a['mdm_item']};mes={x['id']:x for x in a['mes_md_item']};customers={x['id']:x for x in a['crm_customer']};templates={x['id']:x for x in a['mes_qc_template']};indicators={x['id']:x for x in a['mes_qc_indicator']};pt={x['id']:x for x in a['mes_pd_process_param_template']}
  active={x['id'] for x in a['users'] if x['status']==0};assert {291,244,273,289,290,243}<=active
  cfg=[('2026-10-08',977,1353,D(10),D('399.00'),12),('2026-10-09',999,1381,D(12),D('239.00'),12),('2026-10-12',497,1359,D(24),D('17.00'),14),('2026-10-14',507,1358,D(8),D('158.00'),35)]
  raw_items=sorted({b['item_id'] for day,pid,cid,n,price,qt in cfg for b in a['mes_pro_route_product_bom'] if b['product_id']==pid})
  assert all(x in mdm and x in mes for x in raw_items)
  base=datetime(2026,10,8,8)
  raw_template=master_template(None,TAG+'-IQC','演示用来料外观数量核验','1',raw_items,base)
  yan_template=master_template(12,TAG+'-IPQC','演示用燕窝过程检验（参考已有方案）','2',[977,999],base)
  yan_out_template=master_template(12,TAG+'-OQC','演示用燕窝出货检验（参考已有方案）','3',[977,999],base)
  for index,(day,pid,cid,count,price,quality_template) in enumerate(cfg,1):
   if pid in [977,999]:quality_template=yan_out_template
   day=datetime.fromisoformat(day);assert day.weekday()<5 and day.date()<=date(2026,10,14)
   at=lambda minutes:day+timedelta(hours=8,minutes=minutes)
   item=mdm[pid];mitem=mes[pid];assert mitem['mdm_item_id']==pid and mitem['unit_measure_id']==item['unit_measure_id']
   assert customers[cid]['owner_user_id']==291
   gross_unit=money(price*D('1.13'));net=money(price*count);tax=money(net*D('.13'));total=net+tax
   prefix=TAG+'-'+day.strftime('%m%d')+'-'+str(index)
   business=insert('crm_business',{'name':'演示-'+item['name']+'-'+day.strftime('%m%d'),'customer_id':cid,'owner_user_id':291,'follow_up_status':1,'status_type_id':1,'status_id':5,'end_status':1,'deal_time':at(25),'total_product_price':net,'discount_percent':D(0),'total_price':total},291,at(0))
   insert('crm_business_product',{'business_id':business,'item_id':pid,'item_unit_id':item['unit_measure_id'],'item_price':price,'business_price':price,'count':count,'total_price':net},291,at(0))
   quote_code=prefix+'-BJ';quote=insert('crm_sale_quotation',{'no':quote_code,'name':'演示报价-'+item['name'],'customer_id':cid,'business_id':business,'owner_user_id':291,'status':10,'quotation_time':at(10),'valid_until':weekday(at(10)+timedelta(days=30)),'total_product_price':net,'discount_percent':D(0),'discount_price':D(0),'total_price':total,'tax_rate':D(13),'tax_price':tax,'remark':NOTE+' 报价为演示价格，非现行报价政策；'+TAG},291,at(10))
   insert('crm_sale_quotation_product',{'quotation_id':quote,'item_id':pid,'item_unit_id':item['unit_measure_id'],'item_price':price,'quotation_price':price,'count':count,'total_price':net,'tax_percent':D(13),'tax_price':tax},291,at(10))
   contract_code=prefix+'-HT';contract=insert('crm_contract',{'no':contract_code,'name':'演示合同-'+item['name'],'customer_id':cid,'business_id':business,'owner_user_id':291,'audit_status':20,'order_date':at(20),'start_time':at(20),'end_time':weekday(at(20)+timedelta(days=30)),'total_product_price':total,'total_price':total,'discount_percent':D(0),'deposit_price':D(0),'sign_user_id':291},291,at(20))
   insert('crm_contract_product',{'contract_id':contract,'item_id':pid,'item_unit_id':item['unit_measure_id'],'item_price':gross_unit,'contract_price':gross_unit,'count':count,'total_price':total},291,at(20))
   sale_code=prefix+'-XS';order=insert('erp_sale_order',{'no':sale_code,'status':20,'customer_id':cid,'contract_id':contract,'contract_no':contract_code,'sale_user_id':291,'order_time':at(25),'total_count':count,'total_product_price':net,'total_tax_price':tax,'total_price':total,'discount_percent':D(0),'discount_price':D(0),'deposit_price':D(0),'out_count':count,'out_status':3,'production_status':1,'production_finish_time':at(345)},291,at(25))
   order_item=insert('erp_sale_order_items',{'order_id':order,'product_id':pid,'product_unit_id':item['unit_measure_id'],'product_price':price,'count':count,'total_price':net,'tax_percent':D(13),'tax_price':tax,'out_count':count,'need_production':1},291,at(25))
   update('crm_contract',contract,{'order_id':order,'order_no':sale_code});update('crm_sale_quotation',quote,{'contract_id':contract,'contract_no':contract_code});update('crm_business',business,{'order_id':order,'order_no':sale_code})
   plan_ids=[]
   for period,portion,days in [(1,money(total*D('.3')),7),(2,total-money(total*D('.3')),30)]:
    due=weekday(at(480)+timedelta(days=days));remind=weekday(due-timedelta(days=3))
    plan_ids.append(insert('crm_receivable_plan',{'period':period,'customer_id':cid,'contract_id':contract,'owner_user_id':291,'return_time':due,'return_type':8,'price':portion,'remind_days':3,'remind_time':remind,'receivable_id':None,'remark':NOTE+' 约定回款日期与比例为演示计划；未录入实际回款。'},291,at(22)))
   for biz,bid in [(4,business),(5,contract),(9,quote)]+[(8,p) for p in plan_ids]:insert('crm_permission',{'biz_type':biz,'biz_id':bid,'user_id':291,'level':1},291,at(25))
   insert('crm_follow_up_record',{'biz_type':4,'biz_id':business,'type':1,'content':'演示跟进：客户询价→报价确认→签约→转销售生产；'+TAG,'next_time':weekday(at(480)+timedelta(days=1))},291,at(5))
   route=next(x for x in a['mes_pro_route_product'] if x['item_id']==pid)['route_id'];processes=sorted([x for x in a['mes_pro_route_process'] if x['route_id']==route],key=lambda x:x['sort'])
   boms=[x for x in a['mes_pro_route_product_bom'] if x['route_id']==route and x['product_id']==pid];assert processes and boms
   wo_code=prefix+'-WO';wo=insert('mes_pro_work_order',{'code':wo_code,'name':'演示生产-'+item['name'],'type':1,'order_source_type':1,'order_source_code':sale_code,'product_id':pid,'quantity':count,'quantity_produced':count,'quantity_scheduled':count,'quantity_changed':D(0),'client_id':cid,'route_id':route,'auto_finish_flag':1,'request_date':at(330),'finish_date':at(345),'status':2,'is_feedback_approve':0},244,at(45))
   mps_code=prefix+'-MPS';mps=insert('mes_pro_mps',{'code':mps_code,'status':2,'sale_order_id':order,'sale_order_no':sale_code,'sale_order_item_id':order_item,'product_id':pid,'quantity':count,'quantity_produced':count,'unit_measure_id':item['unit_measure_id'],'client_id':cid,'request_date':at(330),'issue_date':at(45),'finish_date':at(345),'work_order_id':wo,'work_order_code':wo_code,'merge_flag':0},244,at(35));update('erp_sale_order_items',order_item,{'mps_id':mps})
   for b in boms:insert('mes_pro_work_order_bom',{'work_order_id':wo,'process_id':b['process_id'],'item_id':b['item_id'],'quantity':qty(D(b['quantity'])*count)},244,at(45))
   raw={}
   for n,b in enumerate(boms,1):
    raw_id=b['item_id'];rq=qty(D(b['quantity'])*count);assert rq>0
    code=prefix+'-RAW'+str(n)
    notice=insert('mes_wm_arrival_notice',{'code':code+'-DH','name':'演示来料-'+mdm[raw_id]['name'],'arrival_date':at(50),'status':4},244,at(50))
    nl=insert('mes_wm_arrival_notice_line',{'notice_id':notice,'item_id':raw_id,'arrival_quantity':rq,'qualified_quantity':rq,'iqc_check_flag':1,'iqc_check_result':1},244,at(50))
    iqc=qc('iqc',code+'-IQC',raw_id,rq,raw_template,at(60),290,{'source_doc_type':100,'source_doc_id':notice,'source_line_id':nl,'source_doc_code':code+'-DH','received_quantity':rq,'receive_date':at(50),'vendor_batch':code+'-PC','vendor_name':'演示批次来料（不代表真实供应商交付）'})
    update('mes_wm_arrival_notice_line',nl,{'iqc_id':iqc})
    batch=insert('mes_wm_batch',{'code':code+'-PC','item_id':raw_id,'produce_date':at(50).date(),'receipt_date':at(70).date(),'quality_status':1,'lot_number':TAG+'-RAW','purchase_order_code':None},244,at(65))
    wh=next((x['warehouse_id'] for x in a['mes_wm_material_stock'] if x['item_id']==raw_id and x['warehouse_id'] not in [20,21,23]),24)
    rec=insert('mes_wm_item_receipt',{'code':code+'-RK','name':'演示来料入库-'+mdm[raw_id]['name'],'iqc_id':iqc,'notice_id':notice,'receipt_date':at(70),'status':4},244,at(65))
    rl=insert('mes_wm_item_receipt_line',{'receipt_id':rec,'arrival_notice_line_id':nl,'item_id':raw_id,'enter_unit_measure_id':mdm[raw_id]['unit_measure_id'],'enter_quantity':rq,'received_quantity':rq,'batch_id':batch,'batch_code':code+'-PC','production_date':at(50).date(),'lot_number':TAG+'-RAW'},244,at(65))
    itx,sid=tx(raw_id,batch,wh,rq,110,rec,code+'-RK',rl,at(70),244)
    insert('mes_wm_item_receipt_detail',{'receipt_id':rec,'line_id':rl,'material_stock_id':sid,'item_id':raw_id,'quantity':rq,**{k:v for k,v in dims(sid).items() if k!='batch_code'}},244,at(70))
    raw.setdefault(b['process_id'],[]).append({'item':raw_id,'qty':rq,'batch':batch,'stock':sid,'bom':b})
   previous=None;task_ids=[];feedback_ids=[];all_batches=[]
   for j,rp in enumerate(processes):
    process=rp['process_id'];station=next(x['id'] for x in a['mes_md_workstation'] if x['process_id']==process);user=273 if j%2 else 244
    start=at(90+j*25);finish=start+timedelta(minutes=20);pcode=prefix+'-P'+str(j+1)
    snapshot={k:rp.get(k) for k in ['process_id','param_template_id','sort','next_process_id','link_type','prepare_time','wait_time','color_code','key_flag','check_flag','backflush_flag','output_item_id']};snapshot['work_order_id']=wo
    insert('mes_pro_work_order_process',snapshot,244,at(45))
    task=insert('mes_pro_task',{'code':pcode+'-TK','name':'演示工序-'+str(process),'work_order_id':wo,'workstation_id':station,'route_id':route,'process_id':process,'param_template_id':rp.get('param_template_id'),'item_id':pid,'quantity':count,'produced_quantity':count,'qualify_quantity':count,'unqualify_quantity':D(0),'client_id':cid,'start_time':start,'end_time':finish,'finish_date':finish+timedelta(minutes=3),'first_feedback_time':finish,'last_feedback_time':finish,'status':4},user,start);task_ids.append(task)
    params(wo,task,rp,user,finish,count)
    feedback=insert('mes_pro_feedback',{'code':pcode+'-FB','type':1,'feedback_time':finish,'workstation_id':station,'route_id':route,'process_id':process,'work_order_id':wo,'task_id':task,'item_id':pid,'scheduled_quantity':count,'feedback_quantity':count,'qualified_quantity':count,'unqualified_quantity':D(0),'uncheck_quantity':D(0),'labor_scrap_quantity':D(0),'material_scrap_quantity':D(0),'other_scrap_quantity':D(0),'feedback_user_id':user,'approve_user_id':244,'status':4},user,finish);feedback_ids.append(feedback)
    inputs=[]
    if process in raw:
     issue=insert('mes_wm_product_issue',{'code':pcode+'-LL','name':'演示生产领料','work_order_id':wo,'task_id':task,'workstation_id':station,'issue_date':start-timedelta(minutes=5),'required_time':start,'status':4},244,start-timedelta(minutes=10))
     for r in raw[process]:
      il=insert('mes_wm_product_issue_line',{'issue_id':issue,'task_id':task,'item_id':r['item'],'enter_unit_measure_id':mdm[r['item']]['unit_measure_id'],'enter_quantity':r['qty'],'quantity':r['qty'],'batch_id':r['batch']},244,start-timedelta(minutes=10))
      insert('mes_wm_product_issue_detail',{'issue_id':issue,'line_id':il,'material_stock_id':r['stock'],'item_id':r['item'],'quantity':r['qty'],**dims(r['stock'])},244,start-timedelta(minutes=10))
      otx,_=tx(r['item'],r['batch'],rows['mes_wm_material_stock'][r['stock']]['warehouse_id'],-r['qty'],116,issue,pcode+'-LL',il,start-timedelta(minutes=5),244)
      _,ws=tx(r['item'],r['batch'],23,r['qty'],116,issue,pcode+'-LL',il,start-timedelta(minutes=5),244,otx)
      insert('mes_pro_task_issue',{'task_id':task,'work_order_id':wo,'workstation_id':station,'source_doc_type':'PRODUCT_ISSUE','source_doc_id':issue,'source_line_id':il,'source_doc_code':pcode+'-LL','batch_code':dims(ws)['batch_code'],'item_id':r['item'],'unit_measure_id':mdm[r['item']]['unit_measure_id'],'issued_quantity':r['qty'],'available_quantity':D(0),'used_quantity':r['qty']},user,start)
      inputs.append({'item':r['item'],'qty':r['qty'],'batch':r['batch'],'stock':ws})
    if previous:inputs.append({'item':pid,'qty':count,'batch':previous['batch'],'stock':previous['stock']})
    consume=insert('mes_wm_item_consume',{'code':pcode+'-XH','name':'演示工序投入消耗','work_order_id':wo,'task_id':task,'workstation_id':station,'process_id':process,'feedback_id':feedback,'consume_date':start,'status':4},user,start)
    assert inputs,('Process missing material input',process)
    for inp in inputs:
     cl=insert('mes_wm_item_consume_line',{'consume_id':consume,'item_id':inp['item'],'quantity':inp['qty'],'batch_id':inp['batch'],'batch_code':dims(inp['stock'])['batch_code']},user,start)
     insert('mes_wm_item_consume_detail',{'consume_id':consume,'line_id':cl,'material_stock_id':inp['stock'],'item_id':inp['item'],'quantity':inp['qty'],**dims(inp['stock'])},user,start)
     tx(inp['item'],inp['batch'],23,-inp['qty'],123,consume,pcode+'-XH',cl,start,user)
    batch_code=pcode+'-PC';batch=insert('mes_wm_batch',{'code':batch_code,'item_id':pid,'produce_date':finish.date(),'expire_date':(finish+timedelta(days=180)).date(),'work_order_id':wo,'task_id':task,'workstation_id':station,'client_id':cid,'sales_order_code':sale_code,'lot_number':TAG+'-'+day.strftime('%m%d'),'quality_status':1},user,finish)
    produce=insert('mes_wm_product_produce',{'code':pcode+'-CC','name':'演示工序产出','work_order_id':wo,'feedback_id':feedback,'workstation_id':station,'process_id':process,'task_id':task,'produce_date':finish+timedelta(minutes=3),'status':4},user,finish)
    pl=insert('mes_wm_product_produce_line',{'produce_id':produce,'feedback_id':feedback,'item_id':pid,'quantity':count,'batch_id':batch,'batch_code':batch_code,'expire_date':(finish+timedelta(days=180)).date(),'lot_number':TAG+'-'+day.strftime('%m%d'),'quality_status':1},user,finish)
    if truth(rp['check_flag']):
     qc('ipqc',pcode+'-IPQC',pid,count,yan_template if pid in [977,999] else quality_template,finish+timedelta(minutes=2),289,{'type':5,'source_doc_type':124,'source_doc_id':produce,'source_line_id':pl,'source_doc_code':pcode+'-CC','work_order_id':wo,'task_id':task,'workstation_id':station,'process_id':process})
    _,sid=tx(pid,batch,23,count,124,produce,pcode+'-CC',pl,finish+timedelta(minutes=3),user)
    insert('mes_wm_product_produce_detail',{'produce_id':produce,'line_id':pl,'item_id':pid,'quantity':count,**dims(sid)},user,finish+timedelta(minutes=3))
    previous={'batch':batch,'stock':sid,'code':batch_code};all_batches.append(batch)
   assert raw.keys()<=set(x['process_id'] for x in processes)
   batch=previous['batch'];batch_code=previous['code']
   update('mes_pro_work_order',wo,{'batch_code':batch_code})
   rec=insert('mes_wm_product_receipt',{'code':prefix+'-RK','name':'演示成品入库-'+item['name'],'work_order_id':wo,'item_id':pid,'receipt_date':at(390),'status':4},244,at(375))
   rl=insert('mes_wm_product_receipt_line',{'receipt_id':rec,'item_id':pid,'quantity':count,'material_stock_id':previous['stock'],'batch_id':batch,'batch_code':batch_code},244,at(375))
   otx,_=tx(pid,batch,23,-count,118,rec,prefix+'-RK',rl,at(390),244)
   _,fg_stock=tx(pid,batch,18,count,118,rec,prefix+'-RK',rl,at(390),244,otx)
   insert('mes_wm_product_receipt_detail',{'receipt_id':rec,'line_id':rl,'material_stock_id':fg_stock,'item_id':pid,'quantity':count,**{k:v for k,v in dims(fg_stock).items() if k!='batch_code'}},244,at(390))
   notice=insert('mes_wm_sales_notice',{'code':prefix+'-FH','name':'演示发货通知-'+item['name'],'client_id':cid,'sale_order_id':order,'sale_order_code':sale_code,'sales_date':at(400),'delivery_date':at(450),'status':4,'out_status':2},291,at(400))
   nl=insert('mes_wm_sales_notice_line',{'notice_id':notice,'sale_order_item_id':order_item,'item_id':pid,'batch_id':batch,'batch_code':batch_code,'quantity':count,'out_count':count,'oqc_check_flag':1},291,at(400))
   sales=insert('mes_wm_product_sales',{'code':prefix+'-CK','name':'演示销售出库-'+item['name'],'client_id':cid,'sale_order_id':order,'notice_id':notice,'sales_order_code':sale_code,'sales_date':at(450),'carrier':'演示自配送','shipping_number':prefix+'-SHIP','status':4},244,at(410))
   sl=insert('mes_wm_product_sales_line',{'sales_id':sales,'sale_order_item_id':order_item,'notice_line_id':nl,'item_id':pid,'enter_unit_measure_id':item['unit_measure_id'],'enter_quantity':count,'batch_id':batch,'batch_code':batch_code,'material_stock_id':fg_stock,'quantity':count,'oqc_check_flag':1,'quality_status':1},244,at(410))
   oqc=qc('oqc',prefix+'-OQC',pid,count,quality_template,at(420),290,{'source_doc_type':119,'source_doc_id':sales,'source_line_id':sl,'source_doc_code':prefix+'-CK','client_id':cid,'batch_code':batch_code,'out_quantity':count,'out_date':at(450)})
   update('mes_wm_product_sales_line',sl,{'oqc_id':oqc})
   insert('mes_wm_product_sales_detail',{'sales_id':sales,'line_id':sl,'material_stock_id':fg_stock,'item_id':pid,'quantity':count,**dims(fg_stock)},244,at(450))
   insert('mes_wm_stock_reserve',{'material_stock_id':fg_stock,'biz_type':1,'biz_id':sales,'biz_code':prefix+'-CK','biz_line_id':sl,'item_id':pid,'quantity':count,'status':3},244,at(440))
   tx(pid,batch,18,-count,119,sales,prefix+'-CK',sl,at(450),244)
   links.append({'date':str(day.date()),'product':item['name'],'spec':item['specification'],'quantity':count,'unit_id':item['unit_measure_id'],'customer':customers[cid]['name'],'business':business,'quotation_id':quote,'quotation_no':quote_code,'contract_id':contract,'contract_no':contract_code,'sale_order_id':order,'sale_order_no':sale_code,'order_item_id':order_item,'total':total,'receivable_plans':plan_ids,'mps_id':mps,'mps_code':mps_code,'work_order_id':wo,'work_order_code':wo_code,'tasks':task_ids,'feedbacks':feedback_ids,'batches':all_batches,'final_batch_id':batch,'final_batch_code':batch_code,'receipt_id':rec,'notice_id':notice,'sales_id':sales,'oqc_id':oqc})
  energy=[('2026-10-08','WATER','原浆调配区','滋补品原浆','清洗、调配用水',D('32.600'),D('5.2000')),('2026-10-09','ELECTRICITY','燕窝加工区','燕窝','加工及包装设备用电',D('1268.500'),D('.8300')),('2026-10-12','STEAM','原浆杀菌区','滋补品原浆','加热杀菌用汽',D('1.850'),D('248.0000')),('2026-10-14','ELECTRICITY','动力站','公共能耗','动力设备运行用电',D('1586.200'),D('.8300'))]
  for day,kind,area,category,purpose,n,unit_price in energy:
   dt=datetime.fromisoformat(day);assert dt.weekday()<5
   insert('mes_energy_record',{'record_date':dt.date(),'energy_type':kind,'usage_area':area,'product_category':category,'usage_purpose':purpose,'quantity':n,'unit_price':unit_price,'amount':money(n*unit_price),'recorder':'赵磊','remark':NOTE+' 单价参考现有能耗台账，用量为演示补充；'+TAG},243,dt+timedelta(hours=16))
  # Complete business, quantity, traceability and preservation checks before commit.
  for link in links:
   q.execute('SELECT * FROM crm_sale_quotation WHERE id=%s',(link['quotation_id'],));quotation=q.fetchone();assert quotation['contract_id']==link['contract_id'] and quotation['total_price']==link['total']
   q.execute('SELECT * FROM crm_contract WHERE id=%s',(link['contract_id'],));contract=q.fetchone();assert contract['order_id']==link['sale_order_id'] and contract['total_price']==link['total'] and contract['audit_status']==20
   q.execute('SELECT * FROM crm_contract_product WHERE contract_id=%s',(link['contract_id'],));cp=q.fetchone();assert money(cp['contract_price']*cp['count'])==cp['total_price']
   q.execute('SELECT * FROM erp_sale_order WHERE id=%s',(link['sale_order_id'],));order=q.fetchone();assert order['total_price']==link['total'] and order['out_count']==link['quantity'] and order['production_status']==1 and order['out_status']==3
   q.execute('SELECT SUM(price) total FROM crm_receivable_plan WHERE contract_id=%s AND deleted=0',(link['contract_id'],));assert q.fetchone()['total']==link['total']
   q.execute('SELECT * FROM mes_pro_mps WHERE id=%s',(link['mps_id'],));mp=q.fetchone();assert mp['sale_order_id']==order['id'] and mp['sale_order_no']==order['no'] and mp['quantity']==link['quantity'] and mp['quantity_produced']==link['quantity']
   q.execute('SELECT * FROM erp_sale_order_items WHERE id=%s',(link['order_item_id'],));oi=q.fetchone();assert oi['mps_id']==mp['id'] and oi['out_count']==oi['count']==mp['quantity']
   q.execute('SELECT COUNT(*) n FROM mes_pro_task WHERE work_order_id=%s AND status=4 AND deleted=0',(link['work_order_id'],));assert q.fetchone()['n']==len(link['tasks'])
   # Same joins as the real batch forward/backward APIs.
   q.execute('SELECT COUNT(*) n FROM mes_wm_transaction t JOIN mes_wm_product_produce pp ON t.biz_id=pp.id AND t.biz_type=124 JOIN mes_pro_feedback f ON f.id=pp.feedback_id JOIN mes_wm_item_consume ic ON ic.feedback_id=f.id JOIN mes_wm_transaction raw ON raw.biz_id=ic.id AND raw.biz_type=123 WHERE t.batch_code=%s',(link['final_batch_code'],));assert q.fetchone()['n']>0
   q.execute('SELECT COUNT(*) n FROM mes_wm_product_sales_line sl JOIN mes_wm_product_sales ss ON ss.id=sl.sales_id JOIN mes_qc_oqc o ON o.id=sl.oqc_id WHERE sl.batch_code=%s AND ss.sale_order_id=%s AND o.check_result=1 AND o.status=4',(link['final_batch_code'],order['id']));assert q.fetchone()['n']==1
  for t,ids in inserted.items():
   q.execute('SELECT COUNT(*) n FROM `'+t+'` WHERE id IN ('+','.join(['%s']*len(ids))+')',ids);assert q.fetchone()['n']==len(ids)
  mappings={(x['template_id'],x['item_id']) for x in a['mes_qc_template_item']}|{(x['template_id'],x['item_id']) for x in rows['mes_qc_template_item'].values()}
  for check in qc_links:
   row=rows[check['table']][check['id']];kind={'mes_qc_iqc':'1','mes_qc_ipqc':'2','mes_qc_oqc':'3'}[check['table']]
   assert kind in templates[check['template_id']]['types'].split(',') and (check['template_id'],row['item_id']) in mappings,('Inapplicable quality template',check)
  for t,records in rows.items():
   for record in records.values():
    created=record.get('create_time')
    assert not created or (datetime(2026,10,8)<=created<=state['now'] and created.weekday()<5),('Invalid workday timestamp',t,record['id'])
  for record in rows['crm_receivable_plan'].values():assert record['return_time'].weekday()<5 and record['receivable_id'] is None
  q.execute('SELECT * FROM mes_wm_material_stock WHERE deleted=0 ORDER BY id');stock_after={x['id']:x for x in q.fetchall()}
  assert all(stock_after[x['id']]==x for x in old_stock),'Original inventory was modified'
  assert all(stock_after[x]['quantity']==0 and stock_after[x]['reserved_quantity']==0 for x in inserted['mes_wm_material_stock']),'Isolated demonstration stock not balanced'
  balances=collections.defaultdict(Decimal)
  for row in rows['mes_wm_transaction'].values():balances[row['material_stock_id']]+=row['quantity']
  assert all(v==0 for v in balances.values()),'Transaction ledger not balanced'
  q.execute('SELECT * FROM crm_receivable WHERE deleted=0 ORDER BY id');assert q.fetchall()==old_receivables,'Actual receivables changed'
  for t,before in protected.items():
   q.execute('SELECT * FROM `'+t+'` WHERE deleted=0 ORDER BY id');assert q.fetchall()==before,('Protected master changed',t)
  result={'mode':'apply' if apply else 'dry-run','committed':apply,'dates':['2026-10-08','2026-10-09','2026-10-12','2026-10-14'],'business_chains':4,'energy_records':4,'actual_receivables_added':0,'counts':{t:len(v) for t,v in inserted.items()},'traceability_verified':True,'original_inventory_unchanged':True,'new_stock_balanced':True,'links':links,'quality':qc_links}
  dump('plan-'+('applied' if apply else 'dry-run')+'.json',result)
  if apply:c.commit()
  else:c.rollback()
  dump('result-'+('applied' if apply else 'dry-run')+'.json',result)
  (P/('executed.sql' if apply else 'preview.sql')).write_text('START TRANSACTION;\n'+'\n'.join(sql_log)+'\n'+('COMMIT;' if apply else 'ROLLBACK;'),encoding='utf8')
  print(json.dumps({k:v for k,v in result.items() if k not in ['links','quality']},ensure_ascii=False))
except Exception:
 c.rollback();raise
finally:
 with c.cursor() as q:q.execute('SELECT RELEASE_LOCK(%s)',(TAG,))
 c.close()