import openpyxl,json,hashlib,collections
from pathlib import Path
from datetime import datetime
from decimal import Decimal
P=Path('scripts/quotation-import');P.mkdir(exist_ok=True)
source=Path('D:/WechatRecord/xwechat_files/wxid_vmeut7wztkbp22_1546/temp/RWTemp/2026-10/c40afdcfa98e2b4561823e6babbefa33/销售报价单_2026.8.1-10.11.xlsx')
w=openpyxl.load_workbook(source,data_only=True)
quotes=[];products=collections.Counter();excluded=[]
def dec(v):return str(Decimal(str(v))) if v is not None else None
for s in w:
 if '汇总' in s.title or s.title=='编制说明':excluded.append(s.title);continue
 rows=list(s.values)
 assert rows[0][0]=='销 售 报 价 单',s.title
 h=dict(sheet=s.title,no=rows[1][1],owner=rows[1][4],quotation_time=rows[1][7].isoformat(sep=' '),valid_until=rows[1][9].isoformat(sep=' '),customer=rows[2][1],business=rows[2][5],contact=rows[2][9],tax=dec(rows[3][1]),remark=rows[3][4],items=[])
 assert rows[5][:10]==('序号','产品名称','规格型号','单位','辅单位1','辅单位2','原价(元)','报价(元)','数量','合计(元)'),s.title
 for n,r in enumerate(rows[6:],7):
  if isinstance(r[0],(int,float)):
   assert r[1] and r[2] and r[3],(s.title,n,r)
   x=dict(row=n,line=int(r[0]),name=r[1],specification=r[2],unit=r[3],unit2=r[4],unit3=r[5],original_price=dec(r[6]),price=dec(r[7]),count=dec(r[8]),total=dec(r[9]))
   h['items'].append(x);products[(x['name'],x['specification'],x['unit'])]+=1
  elif any(v is not None for v in r):
   h.setdefault('footers',[]).append(dict(row=n,values=list(r)))
 assert h['items'],s.title
 quotes.append(h)
data=dict(source=str(source),sha256=hashlib.sha256(source.read_bytes()).hexdigest(),excluded_sheets=excluded,quotes=quotes)
(P/'source.json').write_text(json.dumps(data,ensure_ascii=False,indent=2,default=str),encoding='utf-8')
(P/'source-products.json').write_text(json.dumps([dict(name=k[0],specification=k[1],unit=k[2],occurrences=v) for k,v in products.items()],ensure_ascii=False,indent=2),encoding='utf-8')
print('Quotes',len(quotes),'Lines',sum(len(x['items']) for x in quotes),'Unique products',len(products),'Excluded',excluded)
print('Owners',collections.Counter(x['owner'] for x in quotes))
print('Taxes',collections.Counter(x['tax'] for x in quotes))
print('Totals',sum(Decimal(x['total']) for q in quotes for x in q['items']))
print('Footers',quotes[0].get('footers'))