import json,re,csv
|
from pathlib import Path
|
from decimal import Decimal,ROUND_HALF_UP
|
from datetime import datetime,timedelta
|
P=Path(__file__).parent
|
source=json.loads((P/'ledger-extracted.json').read_text(encoding='utf8'))
|
db=json.loads((P/'before-ledger-database.json').read_text(encoding='utf8'))
|
D=lambda v:Decimal(str(v or 0))
|
money=lambda v:D(v).quantize(Decimal('.01'),rounding=ROUND_HALF_UP)
|
six=lambda v:D(v).quantize(Decimal('.000001'),rounding=ROUND_HALF_UP)
|
sheet=source['sheets']['26.9月'];rows={r['row']:r['values'][:19] for r in sheet['rows'] if 4<=r['row']<=81}
|
groups=[]
|
for start,v in rows.items():
|
if v[14] is None:continue
|
stop=start
|
for merged in sheet['merged_ranges']:
|
m=re.fullmatch(r'O(\d+):O(\d+)',merged)
|
if m and int(m[1])==start:stop=int(m[2])
|
groups.append((start,stop))
|
assert sorted(r for a,b in groups for r in range(a,b+1))==list(rows)
|
channels={str(r['values'][0]):r['values'][1] for r in source['sheets']['渠道分类']['rows'][1:] if r['values'][0] is not None}
|
codes={str(i['code']):i for i in db['mdm_item']['rows']}
|
byid={i['id']:i for i in db['mdm_item']['rows']}
|
aliases={'22102180':964,'12100271':538,'12100273':539,'QZ000524':1522,'22102181':1001,'22102185':1001,'12100593':554}
|
newitems={'QZ000515':{'name':'鲜冬虫夏草','specification':'2A级 (0.6-0.8克/根) *10根','unit_measure_id':8,'category_id':byid[1521]['category_id'],'item_type':2},
|
'UA000009':{'name':'贝母粉','specification':'50克','unit_measure_id':31,'category_id':byid[1090]['category_id'],'item_type':2},
|
'U2100796':{'name':'黑果枸杞','specification':'50克','unit_measure_id':31,'category_id':byid[1544]['category_id'],'item_type':2}}
|
units={u['code']:u['id'] for u in db['mes_md_unit_measure']['rows']}
|
customers={x['name']:x for x in db['crm_customer']['rows']}
|
plan={'source':source['source'],'sha256':source['sha256'],'sheet':'26.9月','groups':[],'excluded':[],'new_customers':{},'new_items':newitems,'aliases':aliases,'rules':{'currency':'按合并金额四舍五入至分；原金额及尾差另存','dates':'有票按开票日补录反推业务日期；无票按备注日期；非原合同签约日期','plans':'仅未收余额，开票/销售日后30天测算，非约定账期','receivables':'用户确认到账日同开票日；无票电商按备注销售日；无发票附件不强制绑定invoice_id'}}
|
for start,stop in groups:
|
v=rows[start];gross=money(v[14]);original='；'.join(str(vv) for vv in v[17:19] if vv)
|
if gross<0:
|
plan['excluded'].append({'rows':[start,stop],'gross':str(gross),'original':original,'reason':'用户明确合并金额负数不录入'});continue
|
addr=str(v[3]);name=channels.get(addr)
|
if addr=='399':name='399零号店'
|
elif start in [69,72,73,74,77,78]:
|
name={69:'海南万茗堂科技有限公司',72:'广州北纬纯净食品有限公司',73:'广州北纬纯净食品有限公司',74:'广州北纬纯净食品有限公司',77:'青海南玻新能源科技有限公司',78:'青海爱嘉星际文化旅游有限公'}[start]
|
elif not name:name='电商'
|
# Preserve the truncated name as written; do not invent a registered full name.
|
if name not in customers:plan['new_customers'][name]={'source_address':addr,'remark':f'九月销售台账补录；来源客户号{addr}；原备注：{original}'[:500]}
|
invoice=v[15]=='是';date_text=str(v[16])
|
if invoice:
|
parts=date_text.split('.');day=datetime(2000+int(parts[0]),int(parts[1]),int(parts[2]))
|
else:
|
m=re.search(r'(2026)\.(\d+)\.(\d+)',original);assert m,(start,original)
|
day=datetime(*map(int,m.groups()))
|
nums=re.findall(r'(?<!\d)\d{20}(?!\d)',original)
|
assert bool(nums)==invoice,(start,nums)
|
received=money(12600) if '只回了12600' in str(v[17]) else (gross if '已回款' in str(v[17]) else D(0))
|
lines=[]
|
for r in range(start,stop+1):
|
a=rows[r];code=str(a[5]);item=codes.get(code) or byid.get(aliases.get(code))
|
assert item or code in newitems,(r,code)
|
qty=D(a[9]);assert qty!=0
|
lines.append({'row':r,'source_no':str(a[2]),'source_code':code,'source_name':a[6],'item_id':item['id'] if item else None,'new_item_code':code if not item else None,'unit_id':units[a[8]],'quantity':str(qty),'net_raw':str(D(a[10])),'tax_raw':str(D(a[11])),'gross_raw':str(D(a[12])),'tax_percent':str(D(a[7])*100),'net_price':str(six(D(a[10])/qty)),'gross_price':str(six(D(a[12])/qty)),'net':str(money(a[10])),'tax':str(money(a[11])),'gross':str(money(a[12])),'source_unit_price':str(D(a[13]))})
|
net=sum(D(a['net']) for a in lines);tax=sum(D(a['tax']) for a in lines)
|
# Quote/order amounts retain source net and tax; an explicit header rounding adjustment reconciles the ledger gross.
|
adjustment=net+tax-gross
|
# Contract amounts have no tax columns. Keep tax-inclusive line amounts; allocate currency rounding to the last line, separately documented.
|
contract_delta=gross-sum(D(a['gross']) for a in lines)
|
lines[-1]['contract_rounding_adjustment']=str(contract_delta)
|
lines[-1]['contract_total']=str(D(lines[-1]['gross'])+contract_delta)
|
for a in lines[:-1]:a['contract_total']=a['gross'];a['contract_rounding_adjustment']='0'
|
key=f'202609-L{start:03d}'
|
record={'key':key,'rows':[start,stop],'customer_name':name,'existing_customer_id':customers.get(name,{}).get('id'),'address':addr,'day':day.strftime('%Y-%m-%d %H:%M:%S'),'plan_day':(day+timedelta(days=30)).strftime('%Y-%m-%d %H:%M:%S'),'invoice':invoice,'invoice_numbers':nums,'invoice_type':1 if '专票' in original else 2,'original':original,'payment_status':v[17],'source_numbers':list(dict.fromkeys(a['source_no'] for a in lines)),'net':str(net),'tax':str(tax),'gross':str(gross),'gross_raw':str(D(v[14])),'rounding_adjustment':str(adjustment),'received':str(received),'outstanding':str(gross-received),'lines':lines}
|
assert net+tax-adjustment==gross and received<=gross and gross>0
|
assert sum(D(a['contract_total']) for a in lines)==gross
|
plan['groups'].append(record)
|
plan['totals']={k:str(sum(D(g[k]) for g in plan['groups'])) for k in ['net','tax','gross','received','outstanding','rounding_adjustment']}
|
plan['counts']={'sales':len(plan['groups']),'lines':sum(len(g['lines']) for g in plan['groups']),'invoices':sum(g['invoice'] for g in plan['groups']),'receivables':sum(D(g['received'])>0 for g in plan['groups']),'plans':sum(D(g['outstanding'])>0 for g in plan['groups']),'physical_invoice_numbers':sum(len(g['invoice_numbers']) for g in plan['groups']),'new_customers':len(plan['new_customers']),'new_items':len(newitems)}
|
(P/'import-plan.json').write_text(json.dumps(plan,ensure_ascii=False,indent=2),encoding='utf8')
|
with (P/'九月销售导入核对清单.csv').open('w',encoding='utf-8-sig',newline='') as f:
|
w=csv.writer(f);w.writerow(['行号','客户','原单号','合并金额原值','系统含税金额','净额','税额','尾差调整','发票号','回款','计划余额','计划测算日'])
|
for g in plan['groups']:w.writerow([str(g['rows']),g['customer_name'],'/'.join(g['source_numbers']),g['gross_raw'],g['gross'],g['net'],g['tax'],g['rounding_adjustment'],'/'.join(g['invoice_numbers']),g['received'],g['outstanding'],g['plan_day'] if D(g['outstanding'])>0 else ''])
|
print(json.dumps({'counts':plan['counts'],'totals':plan['totals'],'new_customers':list(plan['new_customers']),'excluded':plan['excluded']},ensure_ascii=False,indent=2))
|
