"""Plan only explicit sales -> MPS -> production -> quality -> warehouse links."""
|
import json,math
|
from pathlib import Path
|
from datetime import datetime,date,time,timedelta
|
from decimal import Decimal
|
P=Path(__file__).parent
|
s=json.loads((P/'snapshot.json').read_text(encoding='utf8'));tables=s['tables']
|
rows=lambda t:tables.get(t,{}).get('rows',[])
|
data={t:{r['id']:r for r in v['rows']} for t,v in tables.items()}
|
dates={t:{c['Field']:c['Type'] for c in v['columns'] if c['Type'].startswith(('date','timestamp'))} for t,v in tables.items()}
|
now=datetime.fromisoformat(s['state']['now'])
|
holiday={date(2026,9,d) for d in range(25,28)}|{date(2026,10,d) for d in range(1,8)}
|
workday=lambda d:d.weekday()<5 and d not in holiday
|
def at(d,h,m=0):return datetime.combine(d,time(h,m))
|
def backwards(end,n):
|
days=[];d=end
|
while len(days)<n:
|
if workday(d):days.append(d)
|
d-=timedelta(days=1)
|
return list(reversed(days))
|
changes={};owner={};summary=[];task_times={};qc_times={};doc_events={}
|
def setvals(t,id,chain,v):
|
if (t,id) in owner:assert owner[t,id]==chain,('Shared row',t,id,owner[t,id],chain)
|
owner[t,id]=chain
|
old=data[t][id]
|
for field,value in v.items():
|
assert field in old,(t,field)
|
if isinstance(value,datetime):
|
if dates[t].get(field)=='date':value=value.date()
|
value=str(value)
|
if old[field]!=value:changes.setdefault((t,id),{})[field]=value
|
def event(t,id,chain,created,updated=None,**fields):
|
assert workday(created.date()) and 9<=created.hour<18 and created<=now,(t,id,created)
|
updated=updated or created
|
assert created<=updated<=now and workday(updated.date()) and 9<=updated.hour<18
|
setvals(t,id,chain,{'create_time':created,'update_time':updated,**fields})
|
doc_events[t,id]=created
|
def children(t,fk,pid,chain,eventtime):
|
for r in rows(t):
|
if r[fk]==pid:event(t,r['id'],chain,eventtime)
|
def slots(days):
|
out=[]
|
for i,d in enumerate(days):
|
for h0,m0,h1,m1 in [(9,0,12,0),(13,0,17,30)]:
|
start=at(d,h0,m0);end=at(d,h1,m1)
|
if i==0:start=max(start,at(d,13,30))
|
if i==len(days)-1:end=min(end,at(d,12,30))
|
while start<=end:
|
if start<end:out.append(start)
|
start+=timedelta(minutes=1)
|
return out
|
required=[r for r in rows('erp_sale_order_items') if r['need_production'] in (1,True,"b'\\x01'")]
|
order_ids={r['order_id'] for r in required};assert len(order_ids)==8
|
tx_kind={110:'mes_wm_item_receipt',115:'mes_wm_misc_receipt',116:'mes_wm_product_issue',118:'mes_wm_product_receipt',119:'mes_wm_product_sales',123:'mes_wm_item_consume',124:'mes_wm_product_produce'}
|
for oid in sorted(order_ids):
|
o=data['erp_sale_order'][oid];quantity=Decimal(o['total_count']);cycle=2 if quantity<=30 else (4 if oid==1060 else 3) if quantity<=1000 else (10 if quantity>10000 else 8)
|
end=date(2026,10,13) if oid<1059 else date(2026,10,12) if oid==1059 else date(2026,10,14)
|
days=backwards(end,cycle);start=days[0];order_time=at(start,9,20);close=at(end,13);inbound=at(end,13,30);notice_time=at(end,14);outbound=at(end,16,30)
|
assert outbound<=now and start>=date(2026,9,17)
|
mps=[r for r in rows('mes_pro_mps') if r['sale_order_id']==oid];assert mps
|
wo_ids={r['work_order_id'] for r in mps};assert None not in wo_ids
|
event('erp_sale_order',oid,oid,order_time,outbound,order_time=order_time,production_finish_time=close)
|
children('erp_sale_order_items','order_id',oid,oid,order_time)
|
# User explicitly allowed this single upstream exception to maintain chronology.
|
if oid==1062:
|
ct=data['crm_contract'][o['contract_id']];qt=next(r for r in rows('crm_sale_quotation') if r['contract_id']==ct['id']);b=data['crm_business'].get(ct['business_id'])
|
if b:
|
event('crm_business',b['id'],oid,at(start,9),at(start,9))
|
children('crm_business_product','business_id',b['id'],oid,at(start,9))
|
event('crm_sale_quotation',qt['id'],oid,at(start,9,5),quotation_time=at(start,9,5));children('crm_sale_quotation_product','quotation_id',qt['id'],oid,at(start,9,5))
|
event('crm_contract',ct['id'],oid,at(start,9,10),order_date=at(start,9,10),**({'start_time':at(start,9,10)} if ct['start_time'] else {}));children('crm_contract_product','contract_id',ct['id'],oid,at(start,9,10))
|
for mp in mps:event('mes_pro_mps',mp['id'],oid,at(start,9,30),close,issue_date=at(start,9,40),request_date=close,finish_date=close)
|
chain_tasks=[]
|
for wid in sorted(wo_ids):
|
event('mes_pro_work_order',wid,oid,at(start,9,45),close,request_date=close,finish_date=close)
|
for t in ['mes_pro_work_order_bom','mes_pro_work_order_process']:children(t,'work_order_id',wid,oid,at(start,9,45))
|
tasks=sorted([r for r in rows('mes_pro_task') if r['work_order_id']==wid],key=lambda x:(x['start_time'] or x['create_time'],x['id']));assert tasks
|
timeline=slots(days);assert len(timeline)>len(tasks)*20
|
for j,r in enumerate(tasks):
|
a=int(j*len(timeline)/len(tasks));bnd=int((j+.75)*len(timeline)/len(tasks));bnd=min(bnd,len(timeline)-7)
|
begin=timeline[a];finish=timeline[bnd];output=timeline[bnd+5];inspect=timeline[bnd+2];issue_create=timeline[max(0,a-15)] if a>=15 else at(start,13,10);issue=timeline[max(0,a-10)] if a>=10 else at(start,13,20)
|
task_times[r['id']]={'start':begin,'end':finish,'output':output,'inspect':inspect,'issue':issue,'issue_create':issue_create,'oid':oid}
|
chain_tasks.append(r['id'])
|
event('mes_pro_task',r['id'],oid,begin,output,start_time=begin,end_time=finish,finish_date=output,first_feedback_time=finish,last_feedback_time=finish)
|
children('mes_pro_task_assignee','task_id',r['id'],oid,begin)
|
for f in rows('mes_pro_feedback'):
|
if f['task_id']==r['id']:event('mes_pro_feedback',f['id'],oid,finish,feedback_time=finish)
|
for p in rows('mes_pro_work_order_param_record'):
|
if p['task_id']==r['id']:
|
event('mes_pro_work_order_param_record',p['id'],oid,finish);children('mes_pro_work_order_param_record_detail','record_id',p['id'],oid,finish)
|
# Strong production-document links, including legacy headers without task IDs.
|
for t in ['mes_wm_product_issue','mes_wm_item_consume','mes_wm_product_produce','mes_wm_product_receipt']:
|
for r in rows(t):
|
if r.get('work_order_id') not in wo_ids:continue
|
tid=r.get('task_id');tt=task_times.get(tid)
|
if not tt:
|
same=[x for x in chain_tasks if data['mes_pro_task'][x]['work_order_id']==r['work_order_id']]
|
tt=task_times[same[0] if t in ['mes_wm_product_issue','mes_wm_item_consume'] else same[-1]]
|
if t=='mes_wm_product_issue':created=tt['issue_create'];updated=tt['issue'];fields={'issue_date':updated,'required_time':tt['start']};fk='issue_id'
|
elif t=='mes_wm_item_consume':created=updated=tt['start'];fields={'consume_date':created};fk='consume_id'
|
elif t=='mes_wm_product_produce':created=tt['end'];updated=tt['output'];fields={'produce_date':updated};fk='produce_id'
|
else:created=at(end,13,20);updated=inbound;fields={'receipt_date':inbound};fk='receipt_id'
|
event(t,r['id'],oid,created,updated,**fields)
|
doc_events[t,r['id']]=updated
|
for child in [t+'_line',t+'_detail']:children(child,fk,r['id'],oid,updated)
|
for r in rows('mes_pro_task_issue'):
|
if r['task_id'] in chain_tasks:event('mes_pro_task_issue',r['id'],oid,task_times[r['task_id']]['issue'])
|
# Sales documents are scoped to the selected order, not product-name matches.
|
for t in ['mes_wm_sales_notice','mes_wm_product_sales']:
|
for r in rows(t):
|
if r['sale_order_id']!=oid:continue
|
ct=notice_time if t.endswith('notice') else at(end,14,10)
|
event(t,r['id'],oid,ct,outbound,sales_date=notice_time if t.endswith('notice') else outbound,**({'delivery_date':outbound} if t.endswith('notice') and r.get('delivery_date') else {}))
|
fk='notice_id' if t.endswith('notice') else 'sales_id'
|
for child in [t+'_line',t+'_detail']:children(child,fk,r['id'],oid,ct if child.endswith('_line') else outbound)
|
doc_events[t,r['id']]=outbound
|
sales_ids={id for t,id in owner if t=='mes_wm_product_sales' and owner[t,id]==oid}
|
for r in rows('mes_wm_stock_reserve'):
|
if r['biz_type']==1 and r['biz_id'] in sales_ids:event('mes_wm_stock_reserve',r['id'],oid,at(end,16),outbound)
|
# Include incoming documents only for stocks exclusively consumed in this chain.
|
inputs=set()
|
for t in ['mes_wm_product_issue_detail','mes_wm_item_consume_detail']:
|
inputs|={r['material_stock_id'] for r in rows(t) if owner.get((t,r['id']))==oid}
|
for sid in inputs:
|
movements=[r for r in rows('mes_wm_transaction') if r['material_stock_id']==sid]
|
negatives=[r for r in movements if Decimal(r['quantity'])<0]
|
if not negatives or any(owner.get((tx_kind.get(r['biz_type']),r['biz_id']))!=oid for r in negatives):continue
|
for tx in movements:
|
if tx['biz_type']!=110 or Decimal(tx['quantity'])<=0:continue
|
receipt=data['mes_wm_item_receipt'][tx['biz_id']]
|
event('mes_wm_item_receipt',receipt['id'],oid,at(start,10,50),at(start,11),receipt_date=at(start,11));doc_events['mes_wm_item_receipt',receipt['id']]=at(start,11)
|
for child in ['mes_wm_item_receipt_line','mes_wm_item_receipt_detail']:
|
children(child,'receipt_id',receipt['id'],oid,at(start,11))
|
for r in rows(child):
|
if r['receipt_id']==receipt['id'] and r.get('production_date'):setvals(child,r['id'],oid,{'production_date':at(start,9)})
|
if receipt['notice_id']:
|
nid=receipt['notice_id'];event('mes_wm_arrival_notice',nid,oid,at(start,10),arrival_date=at(start,10));children('mes_wm_arrival_notice_line','notice_id',nid,oid,at(start,10))
|
if receipt['iqc_id']:qc_times['mes_qc_iqc',receipt['iqc_id']]=(oid,at(start,10,30),at(start,10),None)
|
# Direct QC links and their result/header/line relationships.
|
for r in rows('mes_qc_ipqc'):
|
if r['work_order_id'] in wo_ids:
|
tt=task_times.get(r['task_id']) or task_times[next(x for x in chain_tasks if data['mes_pro_task'][x]['work_order_id']==r['work_order_id'])]
|
qc_times['mes_qc_ipqc',r['id']]=(oid,tt['inspect'],None,None)
|
oqc_ids={r['oqc_id'] for r in rows('mes_wm_product_sales_line') if owner.get(('mes_wm_product_sales_line',r['id']))==oid and r['oqc_id']}
|
for r in rows('mes_qc_oqc'):
|
if r['id'] in oqc_ids or (r['source_doc_type']==119 and r['source_doc_id'] in sales_ids):qc_times['mes_qc_oqc',r['id']]=(oid,at(end,15),None,outbound)
|
summary.append({'order_id':oid,'order_no':o['no'],'quantity':str(quantity),'cycle_workdays':cycle,'order_time':str(order_time),'production_finish':str(close),'outbound_time':str(outbound),'calendar_days':(end-start).days+1,'workdays':[str(d) for d in days],'mps_ids':[r['id'] for r in mps],'work_order_ids':sorted(wo_ids),'task_ids':chain_tasks})
|
for (t,id),(oid,inspect,received,out) in qc_times.items():
|
vals={'inspect_date':inspect}
|
if received:vals['receive_date']=received
|
if out:vals['out_date']=out
|
event(t,id,oid,inspect,**vals)
|
fk={'mes_qc_iqc':'iqc_id','mes_qc_ipqc':'ipqc_id','mes_qc_oqc':'oqc_id'}[t];children(t+'_line',fk,id,oid,inspect)
|
kind={'mes_qc_iqc':1,'mes_qc_ipqc':2,'mes_qc_oqc':3}[t]
|
for r in rows('mes_qc_indicator_result'):
|
if r['qc_id']==id and r['qc_type']==kind:
|
event('mes_qc_indicator_result',r['id'],oid,inspect);children('mes_qc_indicator_result_detail','result_id',r['id'],oid,inspect)
|
# Transactions must occur at their source document's business event, not creation.
|
for r in rows('mes_wm_transaction'):
|
key=(tx_kind.get(r['biz_type']),r['biz_id'])
|
if key in owner:
|
oid=owner[key];dt=doc_events[key];event('mes_wm_transaction',r['id'],oid,dt,transaction_time=dt,**({'erp_time':dt} if r['erp_time'] else {}))
|
# Retimestamp only dedicated stock/batches; shared opening inventory is protected.
|
def get(t,id,field):return changes.get((t,id),{}).get(field,data[t][id][field])
|
stock_ids={r['material_stock_id'] for r in rows('mes_wm_transaction') if ('mes_wm_transaction',r['id']) in owner}
|
for sid in stock_ids:
|
movements=[r for r in rows('mes_wm_transaction') if r['material_stock_id']==sid]
|
chainset={owner.get(('mes_wm_transaction',r['id'])) for r in movements}
|
if len(chainset)!=1 or None in chainset:continue
|
oid=next(iter(chainset));stock=data['mes_wm_material_stock'][sid]
|
incoming=[datetime.fromisoformat(get('mes_wm_transaction',r['id'],'transaction_time')) for r in movements if Decimal(r['quantity'])>0]
|
if not incoming:continue
|
first=min(incoming);last=max(datetime.fromisoformat(get('mes_wm_transaction',r['id'],'transaction_time')) for r in movements)
|
event('mes_wm_material_stock',sid,oid,first,last,receipt_time=first,**({'erp_time':first} if stock['erp_time'] else {}))
|
for r in movements:
|
if r['receipt_time']:setvals('mes_wm_transaction',r['id'],oid,{'receipt_time':first})
|
batchid=stock['batch_id']
|
if not batchid:continue
|
allstocks=[r for r in rows('mes_wm_material_stock') if r['batch_id']==batchid]
|
if not all(owner.get(('mes_wm_material_stock',r['id']))==oid or r['id']==sid for r in allstocks):continue
|
b=data['mes_wm_batch'][batchid];source=[x for x in rows('mes_wm_product_produce_line') if x['batch_id']==batchid and owner.get(('mes_wm_product_produce_line',x['id']))==oid]
|
produced=doc_events.get(('mes_wm_product_produce',source[0]['produce_id'])) if source else at(datetime.fromisoformat(next(x['order_time'] for x in summary if x['order_id']==oid)).date(),9)
|
# Multiple warehouse stock dimensions of the same batch need its earliest receipt.
|
earliest=min([datetime.fromisoformat(get('mes_wm_material_stock',r['id'],'receipt_time')) for r in allstocks if ('mes_wm_material_stock',r['id']) in owner]+[first])
|
event('mes_wm_batch',batchid,oid,produced,max(produced,earliest),**({'produce_date':produced} if b['produce_date'] else {}),**({'receipt_date':earliest} if b['receipt_date'] else {}))
|
for (t,id),v in changes.items():
|
for field,val in v.items():
|
if field in dates[t] and val is not None:
|
d=datetime.fromisoformat(val) if ' ' in val else datetime.combine(date.fromisoformat(val),time.min)
|
assert workday(d.date()) and d<=now,(t,id,field,val)
|
planned={**data[t][id],**v}
|
assert planned['create_time']<=planned['update_time']
|
plan={'generated_from':s['state'],'quantity_basis':'销售订单总数量','minimum_quote_date':'2026-09-17','holiday_source':'https://www.beijing.gov.cn/cs/gncs/zcwj/202603/t20260327_4568275.html','upstream_exception_order':1062,'chains':summary,'changes':[{'table':t,'id':id,'order_id':owner[t,id],'before':{k:data[t][id][k] for k in v},'after':v} for (t,id),v in changes.items()]}
|
(P/'plan.json').write_text(json.dumps(plan,ensure_ascii=False,indent=2),encoding='utf8')
|
print(json.dumps({'orders':summary,'changed_rows':len(changes),'changed_tables':len({t for t,id in changes})},ensure_ascii=False))
|
