import json,csv,collections,xml.etree.ElementTree as ET
|
from pathlib import Path
|
from decimal import Decimal,ROUND_HALF_UP
|
root=Path('scripts/stock-import-202609')
|
data=json.loads((root/'source.json').read_text(encoding='utf-8'),parse_float=Decimal)
|
plan=json.loads((root/'remote-plan.json').read_text(encoding='utf-8'))
|
db=ET.parse(root/'remote-snapshot.xml').getroot()
|
materials={m.findtext('code'):{c.tag:c.text for c in m} for m in db.find('materials')}
|
units={u.findtext('name').strip():int(u.findtext('id')) for u in db.find('units')}
|
unitcodes={u.findtext('code'):u.findtext('name').strip() for u in db.find('units')}
|
ledger=next(s for s in data if s['sheet']=='Sheet1')['rows']
|
detail=collections.defaultdict(list)
|
for r in next(s for s in data if s['sheet']=='整理表')['rows'][1:]:
|
if len(r)>5 and isinstance(r[5],(int,Decimal)):detail[str(r[0]).strip()].append(r)
|
warehouses=[
|
('CPK01','成品仓','销售成品及完成包装的产品，按批次和效期管理；对应原表CPK01'),
|
('YLK','原料仓','生产原辅料、食品添加剂及散装原料；对应原表YLK'),
|
('NBCK','内包材仓','直接接触产品的内袋、瓶、盖、膜等包材；对应原表NBCK'),
|
('WBCK','外包材仓','外盒、外箱、标签、手提袋等外包装；对应原表WBCK'),
|
('CCCP','虫草冷链仓','鲜草、冻干草及虫草散料专用；对应原表CCCP；实际温湿度条件按品种储存标准执行'),
|
('BCPK','半成品仓','待继续加工或包装的中间产品；对应原表BCPK'),
|
('QC_PENDING','待检仓','到货待检、复检及质量待确认物料；合格后办理移库'),
|
('THK','退货及不合格品仓','退货、拒收及不合格品隔离存放；对应原表THK，检验处置后再移库'),
|
('WIP_VIRTUAL_WAREHOUSE','虚拟线边仓','已发至车间但尚未消耗的在制库存；对应原表CCCJ、YFK，生产领料与实际消耗分开记账')]
|
mapping={k:k for k,_,_ in warehouses};mapping.update(CCCJ='WIP_VIRTUAL_WAREHOUSE',YFK='WIP_VIRTUAL_WAREHOUSE')
|
def fallback(r):
|
major=str(ledger[r['row']-1][2])
|
if major=='8':
|
name=r['name']
|
return 'NBCK' if any(k in name for k in ('复合膜','卷膜','玻璃瓶','瓶盖','内袋','铝箔袋','封口膜','垫片','光片')) else 'WBCK'
|
if major in ('6','7'):return 'CCCP' if any(k in r['name'] for k in ('虫草','鲜草','断草','肉节')) else 'YLK'
|
if major in ('1','2','Q','U','T'):return 'CPK01'
|
raise ValueError(('Unclassified',r))
|
def fmt(v):return format(Decimal(v).quantize(Decimal('.000001'),rounding=ROUND_HALF_UP),'f')
|
out=[];allocation=[]
|
for r in plan:
|
m=materials.get(r['code']);quantity=Decimal(r['quantity']);srcunit=r['unit'];note=''
|
if r['code']=='81013938':srcunit='克';note='台账单位个与整理表、包材盘点表GM冲突，按原表GM及系统克计量'
|
targetunit=unitcodes.get(next((u.findtext('code') for u in db.find('units') if m and u.findtext('id')==m['unit_measure_id']),''),srcunit) if m else srcunit
|
factor=Decimal(1)
|
if srcunit!=targetunit:
|
if srcunit=='公斤' and targetunit=='克':factor=Decimal(1000)
|
elif srcunit=='克' and targetunit=='公斤':factor=Decimal('.001')
|
else:
|
assert srcunit in ('盒','套','罐','袋','个') and targetunit in ('个','张'),(r,targetunit)
|
note+='；按同编码包装成品/包材的计数单位1:1录入'
|
if r['code'] in ('Q2100196','Q2100197'):note+='；用户确认按原编码2992个，原表900ml散货袋装，与系统规格不一致'
|
final=Decimal(fmt(quantity*factor));assert final>=0
|
groups=collections.defaultdict(Decimal)
|
sourceqty=Decimal(0)
|
for dr in detail[r['code']]:
|
loc=str(dr[4]).strip();qty=Decimal(dr[5])
|
# Allocation uses source quantities as proportions, preserving ledger target quantity.
|
sourceqty+=qty
|
if not loc:loc=fallback(r)
|
assert loc in mapping,(r['code'],loc)
|
groups[mapping[loc]]+=qty
|
if not groups:groups[fallback(r)]=Decimal(0)
|
if any(v<0 for v in groups.values()):
|
assert final==0,(r,groups)
|
groups={max(groups,key=groups.get):Decimal(0)}
|
positive={k:v for k,v in groups.items() if v>0}
|
if final==0:
|
groups={max(groups,key=groups.get):Decimal(0)}
|
else:
|
assert positive,(r,groups)
|
groups=positive
|
allocations={}
|
if len(groups)==1:allocations[next(iter(groups))]=final
|
else:
|
weight=sum(groups.values());assert weight>0
|
allocations={k:Decimal(fmt(final*v/weight)) for k,v in groups.items()}
|
allocations[max(groups,key=groups.get)]+=final-sum(allocations.values())
|
assert sum(allocations.values())==final
|
row=dict(r,target_unit=targetunit,unit_id=int(m['unit_measure_id']) if m else units[srcunit],target_quantity=fmt(final),expected_id=int(m['id']) if m else 0,expected_name=m['name'] if m else '',expected_batch=int(m['is_batch_managed']) if m else 1,note=note,source_locations=','.join(sorted({str(dr[4]).strip() or '空' for dr in detail[r['code']]})))
|
row['batch_code']='INIT202609-'+r['code']
|
out.append(row)
|
for wh,qty in allocations.items():allocation.append(dict(code=r['code'],name=r['name'],warehouse=wh,quantity=fmt(qty),unit=targetunit,batch_code=row['batch_code'],row=r['row'],note=note))
|
(root/'import-plan.json').write_text(json.dumps(dict(materials=out,stock=allocation,warehouses=warehouses),ensure_ascii=False,indent=2),encoding='utf-8')
|
with (root/'仓库分配清单.csv').open('w',encoding='utf-8-sig',newline='') as f:
|
w=csv.DictWriter(f,fieldnames=list(allocation[0]));w.writeheader();w.writerows(allocation)
|
def q(v):
|
if v is None:return 'NULL'
|
if isinstance(v,(int,Decimal)):return str(v)
|
return "'"+str(v).replace('\\','\\\\').replace("'","''").replace('\n','\\n').replace('\r','\\r')+"'"
|
sql=[]
|
sql.append('CREATE TEMPORARY TABLE stage_inventory_material(code VARCHAR(50) PRIMARY KEY,name VARCHAR(200),unit_id BIGINT,quantity DECIMAL(24,6),expected_id BIGINT,expected_name VARCHAR(200),batch_managed INT,batch_code VARCHAR(64),remark VARCHAR(500)) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci')
|
for r in out:
|
remark=f"来源2026年9月份盘点表(虫草核对确认).xls Sheet1第{r['row']}行；结存{r['quantity']}{r['unit']}；{r['note']}"
|
sql.append('INSERT INTO stage_inventory_material VALUES('+','.join(q(v) for v in (r['code'],r['name'],r['unit_id'],Decimal(r['target_quantity']),r['expected_id'],r['expected_name'],r['expected_batch'],r['batch_code'],remark)) +')')
|
sql.append('CREATE TEMPORARY TABLE stage_inventory_warehouse(code VARCHAR(100) PRIMARY KEY,name VARCHAR(100),remark VARCHAR(255)) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci')
|
for r in warehouses:sql.append('INSERT INTO stage_inventory_warehouse VALUES('+','.join(q(v) for v in r)+')')
|
sql.append('CREATE TEMPORARY TABLE stage_inventory_stock(code VARCHAR(50),warehouse_code VARCHAR(100),quantity DECIMAL(24,6),remark VARCHAR(255),PRIMARY KEY(code,warehouse_code)) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci')
|
for r in allocation:
|
remark=f"2026年9月台账Sheet1第{r['row']}行，按结存覆盖；按原表库位分仓；INIT202609为期初汇总批次，不代表真实生产批次；{r['note']}"
|
assert len(remark)<=255
|
sql.append('INSERT INTO stage_inventory_stock VALUES('+','.join(q(v) for v in (r['code'],r['warehouse'],Decimal(r['quantity']),remark))+')')
|
(root/'remote-import.sql').write_text(';\n'.join(sql)+';\n',encoding='utf-8')
|
print('materials',len(out),'new',sum(not r['expected_id'] for r in out),'stock',len(allocation),'positive',sum(Decimal(r['quantity'])>0 for r in allocation),'warehouse allocation',dict(collections.Counter(r['warehouse'] for r in allocation)))
|
