import json,hashlib,collections,csv,re,unicodedata
|
from pathlib import Path
|
from decimal import Decimal,ROUND_HALF_UP
|
import xml.etree.ElementTree as ET
|
P=Path('scripts/quotation-import')
|
source=json.loads((P/'source.json').read_text(encoding='utf-8'))
|
db=ET.parse(P/'local-snapshot.xml')
|
materials={x.findtext('code'):{e.tag:e.text or '' for e in x} for x in db.findall('./materials/row')}
|
units={x.findtext('name').strip():{e.tag:e.text for e in x} for x in db.findall('./units/row')}
|
# Explicit correspondence of source product + package specification to the TC product series.
|
mapping=[
|
('即食小罐洞燕窝','840克（30克×28）','TC0025'),
|
('即食小罐洞燕窝','360克（30克×12）','TC0024'),
|
('洞燕窝即食碗装燕窝','1.2kg（100g*12碗/盒）','TC0035'),
|
('即食碗装洞燕窝（怡碗燕）','720g（90g×8）','TC0037'),
|
('即食小罐洞燕窝','210克（30克×7）','TC0022'),
|
('即食小罐洞燕窝（尊享款）','480克（30克×16）','TC0027'),
|
('黑果枸杞','90g','TC0016'),
|
('即食小罐洞燕窝（尊享款）','600g（30g*20瓶/盒）','TC0026'),
|
('洞燕窝即食碗装燕窝','1.5千克（150克×10）','TC0032'),
|
('枸杞原浆罐头','1.44L（60ml*24瓶/盒）','TC0009'),
|
('即食碗装红枣枸杞洞燕窝(自热型)','600克(100克/碗*6)','TC0030'),
|
('有机黑果枸杞','80克（4克×20）','TC0003'),
|
('黑果枸杞','60g','TC0017'),
|
('黑果枸杞原浆罐头','1.44L（60ml*24瓶/盒）','TC0007'),
|
('沙棘原浆罐头','1.44L（60ml*24瓶/盒）','TC0011'),
|
('黑果枸杞袋泡茶','60g（2g/袋*30）','TC0006'),
|
('总统牌洞燕盏即食燕窝罐头','110g','TC0029'),
|
('即食碗装鱼胶洞燕窝(自热型)','600克(100克/碗*6）','TC0031'),
|
('有机黑果枸杞','120克（4克×30）','TC0004'),
|
('黑果枸杞','180克（90克×2）','TC0005'),
|
('有机黑果枸杞','48克（4克×12）','TC0002'),
|
('洞燕窝即食碗装燕窝','600g（100g*6碗/盒）','TC0036'),
|
('枸杞原浆罐头','360ml（60ml*6瓶/盒）','TC0010'),
|
('沙棘原浆罐头','360ml（60ml*6瓶/盒）','TC0012'),
|
('黑果枸杞原浆罐头','360ml（60ml*6瓶/盒）','TC0008'),
|
('即食碗装洞燕窝（自热型）','600g（100g*6）','TC0034'),
|
('冻干枸杞','200g/盒（8g/袋×25）','TC0001'),
|
('洞燕窝即食碗装燕窝','900克（150克*6）','TC0033'),
|
('枸杞原浆罐头','900ml（30ml/袋×30）','TC0014'),
|
('黑果枸杞玫瑰果膏','900ml（30ml/袋×30）','TC0015'),
|
('即食藜麦粉R型','450克（30克×15)','TC0013'),
|
('黑果枸杞','80克（4g/罐×20）','TC0019'),
|
]
|
mapped={(a,b):c for a,b,c in mapping}
|
newkeys={('鲜冬虫夏草','4A级 （1.01-1.3g）/ 根*9根'),('雪菊','50g'),('黑果枸杞玫瑰果膏-30ml/袋','30ml/袋'),('黑果枸杞','120克（4g/罐×30）')}
|
rows=[];newproducts={};productmap={};quotes=[]
|
canonical_customers={}
|
assert len(source['quotes'])==345 and sum(len(q['items']) for q in source['quotes'])==1702
|
assert source['excluded_sheets']==['报价单汇总总表','编制说明']
|
assert len({q['no'] for q in source['quotes']})==345
|
for q in source['quotes']:
|
assert q['tax'] is None and q['contact'] is None
|
out=dict(q);out['name']=q['customer']+' 2026年8-10月产品报价单';out['items']=[]
|
customer_key=unicodedata.normalize('NFKC',q['customer']).casefold().strip()
|
out['source_customer']=q['customer'];out['customer']=canonical_customers.setdefault(customer_key,q['customer'])
|
assert len(out['name'])<=128 and len(q['no'])<=32 and len(q['customer'])<=255
|
total=Decimal(0);count=Decimal(0)
|
for x in q['items']:
|
key=(x['name'],x['specification']);unit=units[x['unit']]
|
if key in mapped:
|
code=mapped[key];m=materials[code];assert m['status']=='0'
|
itemid=int(m['id']);itemname=m['name'];expected_base_unit=int(m['unit_measure_id'])
|
else:
|
assert key in newkeys,key
|
code='SQ20261012-'+hashlib.sha256('|'.join(key).encode()).hexdigest()[:10].upper()
|
itemid=0;itemname=x['name'];expected_base_unit=int(unit['id'])
|
newproducts[code]=dict(code=code,name=x['name'],specification=x['specification'],unit_id=int(unit['id']),unit_name=x['unit'],item_type=2)
|
vals=[Decimal(x[k]) for k in ['original_price','price','count','total']]
|
assert vals[0]>=0 and vals[1]>=0 and vals[2]>0 and vals[3]>=0
|
assert all(v==v.quantize(Decimal('0.000001')) for v in vals)
|
calculated=(vals[1]*vals[2]).quantize(Decimal('.01'),rounding=ROUND_HALF_UP)
|
remark=f"源表第{x['row']}行；{x['name']}；规格{x['specification']}；单位{x['unit']}"
|
if calculated!=vals[3]:remark+=f"；原表均价已四舍五入，单价×数量={calculated}，原表小计={vals[3]}，按原表小计导入"
|
assert len(remark)<=256
|
y=dict(x,item_code=code,item_id=itemid,item_name=itemname,item_unit_id=int(unit['id']),expected_base_unit=expected_base_unit,remark=remark)
|
out['items'].append(y);total+=vals[3];count+=vals[2]
|
rows.append(dict(no=q['no'],customer=q['customer'],**y))
|
productmap[key]=dict(name=x['name'],specification=x['specification'],source_unit=x['unit'],item_code=code,item_name=itemname,action='补建销售产品' if not itemid else '使用已有产品')
|
foot=[f for f in q['footers'] if f['values'][0]=='合计'];assert len(foot)==1
|
assert Decimal(str(foot[0]['values'][9]))==total,(q['no'],total,foot)
|
assert Decimal(str(foot[0]['values'][8]))==count,(q['no'],count,foot)
|
out['total']=str(total)
|
out['remark']=q['remark']+f" 来源：销售报价单_2026.8.1-10.11.xlsx/{q['sheet']}；原负责人：朱莉；原表客户：{q['customer']}；原表商机名称：{q['business']}；未填税率，保持空白。"
|
if any(Decimal(x['price'])*Decimal(x['count'])!=Decimal(x['total']) for x in q['items']):out['remark']+=' 原表报价为四舍五入的成交均价，金额按原表明细小计保存。'
|
assert len(out['remark'])<=1024
|
quotes.append(out)
|
assert sum(Decimal(q['total']) for q in quotes)==Decimal('15392805.71')
|
plan=dict(owner_username='zhuli',owner_nickname='朱莉',status=0,source_sha256=source['sha256'],quotes=quotes,new_products=list(newproducts.values()),total='15392805.71')
|
(P/'local-plan.json').write_text(json.dumps(plan,ensure_ascii=False,indent=2),encoding='utf-8')
|
for filename,data in [('报价明细清单',rows),('产品匹配清单',list(productmap.values()))]:
|
fields=list(data[0])
|
with (P/f'{filename}.csv').open('w',encoding='utf-8-sig',newline='') as f:
|
w=csv.DictWriter(f,fields);w.writeheader();w.writerows(data)
|
print('Prepared',len(quotes),'quotations,',len(rows),'lines,',len(productmap),'products, new products=',list(newproducts.values()))
|
