"""Authorized historical shipment completion with explicitly paired stock compensation.
|
No external carrier request; generated tracking numbers are simulation data.
|
"""
|
import sys,json,csv
|
from pathlib import Path
|
from decimal import Decimal
|
P=Path(__file__).parent
|
sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps'))
|
import pymysql
|
D=Decimal
|
canonical=lambda x:json.loads(json.dumps(x,ensure_ascii=False,default=str))
|
draft=json.loads((P/'shipping-drafts-result.json').read_text(encoding='utf8'))
|
assert not (P/'shipping-complete-result.json').exists(),'Already completed; verify instead of repeating.'
|
c=pymysql.connect(host='36.213.150.237',port=10000,user='root',password=sys.stdin.readline().strip(),database='mom-hm',charset='utf8mb4',autocommit=False,cursorclass=pymysql.cursors.DictCursor)
|
result={'orders':[],'allocations':[],'new_stock_ids':[],'new_batch_ids':[], 'unit_basis':{}}
|
try:
|
with c.cursor() as q:
|
q.execute('SELECT DATABASE() db,NOW() now,GET_LOCK(%s,10) locked',('sales-ledger-202609',));state=q.fetchone();assert state['db']=='mom-hm' and state['locked']==1;now=state['now']
|
# Reconcile the five columns required by the repository's receipt line DO.
|
# DDL commits implicitly, so save its schema/rows separately BEFORE the data transaction.
|
q.execute('SHOW CREATE TABLE mes_wm_misc_receipt_line');schema_before=q.fetchone()
|
q.execute('SELECT * FROM mes_wm_misc_receipt_line ORDER BY id');schema_before['rows']=q.fetchall()
|
if not (P/'shipping-receipt-schema-before.json').exists():(P/'shipping-receipt-schema-before.json').write_text(json.dumps(schema_before,ensure_ascii=False,default=str,indent=2),encoding='utf8')
|
q.execute('SHOW COLUMNS FROM mes_wm_misc_receipt_line');fields={r['Field'] for r in q.fetchall()}
|
missing={k:v for k,v in {'batch_code':'varchar(100) DEFAULT NULL','warehouse_id':'bigint DEFAULT NULL','location_id':'bigint DEFAULT NULL','area_id':'bigint DEFAULT NULL'}.items() if k not in fields}
|
if missing:q.execute('ALTER TABLE mes_wm_misc_receipt_line '+','.join('ADD COLUMN `'+k+'` '+v for k,v in missing.items()))
|
result['added_receipt_line_columns']=list(missing)
|
q.execute('SHOW CREATE TABLE mes_wm_misc_receipt_detail');detail_schema=q.fetchone()
|
if not (P/'shipping-receipt-detail-schema-before.json').exists():(P/'shipping-receipt-detail-schema-before.json').write_text(json.dumps(detail_schema,ensure_ascii=False,default=str,indent=2),encoding='utf8')
|
q.execute('SHOW COLUMNS FROM mes_wm_misc_receipt_detail');detail_fields={r['Field'] for r in q.fetchall()}
|
if 'batch_code' not in detail_fields:q.execute('ALTER TABLE mes_wm_misc_receipt_detail ADD COLUMN batch_code varchar(100) DEFAULT NULL')
|
c.begin()
|
tabs=['erp_sale_order','erp_sale_order_items','mes_wm_sales_notice','mes_wm_sales_notice_line','mes_wm_product_sales','mes_wm_product_sales_line','mes_wm_product_sales_detail','mes_wm_misc_receipt','mes_wm_misc_receipt_line','mes_wm_misc_receipt_detail','mes_wm_stock_reserve','mes_wm_transaction','mes_wm_material_stock','mes_wm_batch','mdm_item','mes_wm_warehouse','mes_wm_warehouse_location','mes_wm_warehouse_area','mes_qc_oqc','crm_contract','crm_contract_product','crm_sale_quotation','crm_sale_quotation_product','crm_invoice','crm_receivable','crm_receivable_plan','crm_customer','crm_permission']
|
data={};schemas={};backup={'state':state,'tables':{}}
|
for t in tabs:
|
q.execute('SELECT * FROM `'+t+'` WHERE deleted=0 ORDER BY id'+(' FOR UPDATE' if t in ['mes_wm_material_stock','erp_sale_order','erp_sale_order_items','mes_wm_product_sales'] else ''));rows=q.fetchall();data[t]={r['id']:r for r in rows};backup['tables'][t]=rows
|
q.execute('SHOW COLUMNS FROM `'+t+'`');schemas[t]={r['Field'] for r in q.fetchall()}
|
(P/'shipping-complete-locked-before.json').write_text(json.dumps(backup,ensure_ascii=False,default=str,indent=2),encoding='utf8')
|
q.execute('SELECT status FROM system_users WHERE id=244 AND deleted=0');assert q.fetchone()['status']==0
|
def insert(t,v):
|
v={**v,'creator':'244','updater':'244','create_time':now,'update_time':now,'deleted':0};assert set(v)<=schemas[t],(t,set(v)-schemas[t])
|
q.execute('INSERT INTO `'+t+'` ('+','.join('`'+k+'`' for k in v)+') VALUES ('+','.join(['%s']*len(v))+')',list(v.values()));return q.lastrowid
|
def update(t,id,v):
|
v={**v,'updater':'244','update_time':now};assert set(v)<=schemas[t]
|
q.execute('UPDATE `'+t+'` SET '+','.join('`'+k+'`=%s' for k in v)+' WHERE id=%s AND deleted=0',list(v.values())+[id]);assert q.rowcount==1
|
false=lambda x:x in (None,0,False,b'\x00')
|
note='九月历史销售流程补录；按用户要求等量补库存后出库，净库存不变；非真实采购或生产入库；汇总批次非真实生产批次；补录时间为本次执行时间。'
|
rc_code='QTCKDC-202609-20261013'
|
assert not any(x['code']==rc_code for x in data['mes_wm_misc_receipt'].values())
|
receipt=insert('mes_wm_misc_receipt',{'code':rc_code,'name':'九月销售出库等量库存补充','receipt_date':now,'status':0,'remark':note})
|
result.update(receipt_id=receipt,receipt_code=rc_code)
|
selected={}
|
mismatch_ids={538,539,1001,1002,497,509}
|
for order in draft['orders']:
|
h=data['erp_sale_order'][order['order_id']];sales=data['mes_wm_product_sales'][order['sales_id']];notice=data['mes_wm_sales_notice'][order['notice_id']]
|
assert h['no']==order['no'] and h['status']==20 and h['out_count']==0 and h['out_status']==1 and sales['status']==0 and notice['status']==3
|
assert not any(x['sales_id']==sales['id'] for x in data['mes_wm_product_sales_detail'].values())
|
for ln in order['lines']:
|
item=data['mdm_item'][ln['item_id']];qty=D(ln['quantity']);assert qty>0 and item['status']==0
|
ol=data['erp_sale_order_items'][ln['order_item_id']];sl=data['mes_wm_product_sales_line'][ln['sales_line_id']]
|
assert ol['count']==qty and ol['out_count']==0 and sl['quantity']==qty and false(sl['oqc_check_flag'])
|
basis='销售单位与物料主单位一致'
|
if not ln['unit_match']:
|
assert item['id'] in mismatch_ids
|
# This is same finished SKU/package count, never a generic unit conversion.
|
basis='同SKU完整销售包装计数按1:1补录；瓶/盒/个为原台账计数称谓；规格及物料匹配一致；不新增通用单位换算'
|
result['unit_basis'][str(item['id'])]={'name':item.get('name'),'source_unit_id':ln['sale_unit_id'],'primary_unit_id':ln['primary_unit_id'],'basis':basis}
|
if item['id'] not in selected:
|
eligible=[]
|
for stock in data['mes_wm_material_stock'].values():
|
if stock['item_id']!=item['id'] or not false(stock['frozen']):continue
|
wh=data['mes_wm_warehouse'].get(stock['warehouse_id']);loc=data['mes_wm_warehouse_location'].get(stock['location_id']);area=data['mes_wm_warehouse_area'].get(stock['area_id'])
|
if not wh or not loc or not area:continue
|
if any(not false(x['frozen']) for x in [wh,loc,area]):continue
|
if loc['warehouse_id']!=wh['id'] or area['location_id']!=loc['id']:continue
|
batch=data['mes_wm_batch'].get(stock['batch_id'])
|
if not batch or batch['item_id']!=item['id'] or batch['code']!=stock['batch_code']:continue
|
# Existing initialization summaries are appropriate for a historical offset.
|
if not stock['batch_code'].startswith('INIT202609-'):continue
|
if stock['quantity']-D(stock['frozen_quantity'] or 0)-D(stock['reserved_quantity'] or 0)<0:continue
|
eligible.append(stock)
|
if eligible:selected[item['id']]=min(eligible,key=lambda x:x['id'])
|
else:
|
assert item['id'] in {1995,1996,1997},('No valid summary stock',item['id'])
|
whid=17 if item['id']==1995 else 18
|
loc=next(x for x in data['mes_wm_warehouse_location'].values() if x['warehouse_id']==whid and false(x['frozen']))
|
area=next(x for x in data['mes_wm_warehouse_area'].values() if x['location_id']==loc['id'] and false(x['frozen']))
|
assert false(data['mes_wm_warehouse'][whid]['frozen'])
|
bc='HIS-SALES-202609-'+str(item['id'])
|
bid=insert('mes_wm_batch',{'code':bc,'item_id':item['id'],'receipt_date':now,'remark':note});result['new_batch_ids'].append(bid)
|
values={'item_id':item['id'],'item_type_id':item.get('item_type_id'),'batch_id':bid,'batch_code':bc,'warehouse_id':whid,'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':now,'remark':note}
|
sid=insert('mes_wm_material_stock',values);values['id']=sid;selected[item['id']]=values;result['new_stock_ids'].append(sid)
|
stock=selected[item['id']];dims={k:stock[k] for k in ['batch_code','warehouse_id','location_id','area_id']}
|
remark='对应销售原单'+order['no']+'、出库行'+str(sl['id'])+'；等量补入后出库，净库存不变；'+basis
|
rid=insert('mes_wm_misc_receipt_line',{'receipt_id':receipt,'item_id':item['id'],'quantity':qty,**dims,'remark':remark})
|
detailvals={'receipt_id':receipt,'line_id':rid,'item_id':item['id'],'quantity':qty,**dims,'remark':remark}
|
insert('mes_wm_misc_receipt_detail',detailvals)
|
tx={'item_id':item['id'],'material_stock_id':stock['id'],'quantity':qty,'batch_id':stock['batch_id'],**dims,'transaction_time':now,'receipt_time':now}
|
itx=insert('mes_wm_transaction',{'type':1,'biz_type':115,'biz_id':receipt,'biz_code':rc_code,'biz_line_id':rid,**tx})
|
q.execute('UPDATE mes_wm_material_stock SET quantity=quantity+%s,updater=%s,update_time=%s WHERE id=%s',(qty,'244',now,stock['id']));assert q.rowcount==1
|
did=insert('mes_wm_product_sales_detail',{'sales_id':sales['id'],'line_id':sl['id'],'item_id':item['id'],'material_stock_id':stock['id'],'quantity':qty,'batch_id':stock['batch_id'],**dims,'remark':remark})
|
update('mes_wm_product_sales_line',sl['id'],{'batch_id':stock['batch_id'],'batch_code':stock['batch_code'],'material_stock_id':stock['id'],'remark':remark})
|
update('mes_wm_sales_notice_line',ln['notice_line_id'],{'batch_id':stock['batch_id'],'batch_code':stock['batch_code'],'remark':remark})
|
result['allocations'].append({**ln,'sales_id':sales['id'],'no':order['no'],'stock_id':stock['id'],'detail_id':did,'receipt_line_id':rid,'in_transaction_id':itx,'batch_id':stock['batch_id'],'batch_code':stock['batch_code'],'warehouse_id':stock['warehouse_id'],'location_id':stock['location_id'],'area_id':stock['area_id']})
|
update('mes_wm_misc_receipt',receipt,{'status':3});update('mes_wm_misc_receipt',receipt,{'status':4})
|
for order in draft['orders']:
|
alloc=[x for x in result['allocations'] if x['sales_id']==order['sales_id']]
|
update('mes_wm_product_sales',order['sales_id'],{'status':2})
|
for a in alloc:
|
qty=D(a['quantity'])
|
q.execute('SELECT * FROM mes_wm_material_stock WHERE id=%s FOR UPDATE',(a['stock_id'],));stock=q.fetchone()
|
assert stock['quantity']-D(stock['frozen_quantity'] or 0)-D(stock['reserved_quantity'] or 0)>=qty
|
a['reserve_id']=insert('mes_wm_stock_reserve',{'material_stock_id':stock['id'],'biz_type':1,'biz_id':order['sales_id'],'biz_code':order['sales_code'],'biz_line_id':a['sales_line_id'],'item_id':a['item_id'],'quantity':qty,'status':1,'remark':note})
|
q.execute('UPDATE mes_wm_material_stock SET reserved_quantity=COALESCE(reserved_quantity,0)+%s,updater=%s,update_time=%s WHERE id=%s',(qty,'244',now,stock['id']))
|
update('mes_wm_product_sales',order['sales_id'],{'status':10});update('erp_sale_order',order['order_id'],{'out_status':2})
|
ecommerce=order['customer_id'] in {1609,1381}
|
carrier='京东' if ecommerce else '自有车辆配送（车牌前缀：青H）'
|
tracking=('JDV20261013'+order['no']) if ecommerce else ('ZPSD20261013'+order['no'])
|
shipment_note=note+('京东运单号为按用户要求模拟生成，非真实物流凭证。' if ecommerce else '开车送货，车牌只提供青H前缀；配送编号为内部补录编号。')
|
update('mes_wm_product_sales',order['sales_id'],{'name':'销售订单['+order['no']+']出库单','status':3,'carrier':carrier,'shipping_number':tracking,'remark':shipment_note})
|
for a in alloc:
|
qty=D(a['quantity']);stock=selected[a['item_id']]
|
update('mes_wm_stock_reserve',a['reserve_id'],{'status':3})
|
q.execute('UPDATE mes_wm_material_stock SET reserved_quantity=reserved_quantity-%s,quantity=quantity-%s,updater=%s,update_time=%s WHERE id=%s AND quantity>=%s AND reserved_quantity>=%s',(qty,qty,'244',now,stock['id'],qty,qty));assert q.rowcount==1
|
a['out_transaction_id']=insert('mes_wm_transaction',{'type':2,'biz_type':119,'biz_id':order['sales_id'],'biz_code':order['sales_code'],'biz_line_id':a['sales_line_id'],'material_stock_id':stock['id'],'item_id':a['item_id'],'quantity':-qty,'batch_id':stock['batch_id'],'batch_code':stock['batch_code'],'warehouse_id':stock['warehouse_id'],'location_id':stock['location_id'],'area_id':stock['area_id'],'transaction_time':now,'receipt_time':stock['receipt_time']})
|
update('mes_wm_sales_notice_line',a['notice_line_id'],{'out_count':qty})
|
update('erp_sale_order_items',a['order_item_id'],{'out_count':qty})
|
update('mes_wm_product_sales',order['sales_id'],{'status':4})
|
update('mes_wm_sales_notice',order['notice_id'],{'status':4,'out_status':2,'remark':shipment_note})
|
update('erp_sale_order',order['order_id'],{'out_status':3,'out_count':sum(D(a['quantity']) for a in alloc)})
|
result['orders'].append({**{k:v for k,v in order.items() if k!='lines'},'carrier':carrier,'shipping_number':tracking,'jd':ecommerce,'status':4})
|
# Verify before commit: no financial changes, no QC/master changes, exact stock conservation.
|
for t in ['mdm_item','mes_wm_warehouse','mes_wm_warehouse_location','mes_wm_warehouse_area','mes_qc_oqc','crm_contract','crm_contract_product','crm_sale_quotation','crm_sale_quotation_product','crm_invoice','crm_receivable','crm_receivable_plan','crm_customer','crm_permission']:
|
q.execute('SELECT * FROM `'+t+'` WHERE deleted=0 ORDER BY id');assert q.fetchall()==backup['tables'][t],('Protected data changed',t)
|
q.execute('SELECT * FROM mes_wm_material_stock WHERE deleted=0 ORDER BY id');after={r['id']:r for r in q.fetchall()}
|
for old in backup['tables']['mes_wm_material_stock']:
|
diffs={k for k in old if old[k]!=after[old['id']][k]};assert diffs<={'updater','update_time'},('Stock changed',old['id'],diffs)
|
for sid in result['new_stock_ids']:assert after[sid]['quantity']==0 and after[sid]['reserved_quantity']==0
|
assert len(result['orders'])==23 and len(result['allocations'])==75 and sum(x['jd'] for x in result['orders'])==10
|
c.commit();result.update(committed=True,at=str(now),original_stock_rows_unchanged=len(backup['tables']['mes_wm_material_stock']),counts={'completed_orders':23,'jd_orders':10,'self_drive_orders':13,'receipt_documents':1,'receipt_lines':75,'outbound_lines':75,'transactions':150,'reserves_closed':75})
|
(P/'shipping-complete-result.json').write_text(json.dumps(result,ensure_ascii=False,default=str,indent=2),encoding='utf8')
|
print(json.dumps({k:v for k,v in result.items() if k not in ['orders','allocations','unit_basis']},ensure_ascii=False))
|
except Exception:c.rollback();raise
|
finally:
|
with c.cursor() as q:q.execute('SELECT RELEASE_LOCK(%s)',('sales-ledger-202609',))
|
c.close()
|
