import csv, json
from decimal import Decimal as D
from pathlib import Path

ROOT = Path(__file__).parent
Z = D('0')
with (ROOT/'source.csv').open(encoding='utf-8-sig', newline='') as f:
    rows = list(csv.DictReader(f))
for r in rows:
    for k in list(r):
        if k != '产品': r[k] = D(r[k])
    r['预算收入'] = r['预算量']*r['预算价']; r['实际收入'] = r['实际量']*r['实际价']
    r['预算变动成本'] = r['预算量']*r['预算单位成本']; r['实际变动成本'] = r['实际量']*r['实际单位成本']
    r['预算贡献'] = r['预算收入']-r['预算变动成本']; r['实际贡献'] = r['实际收入']-r['实际变动成本']
    r['预算单位贡献'] = r['预算价']-r['预算单位成本']; r['实际单位贡献'] = r['实际价']-r['实际单位成本']
common=[r for r in rows if r['预算量'] and r['实际量']]
B=sum((r['预算量'] for r in common),Z); A=sum((r['实际量'] for r in common),Z)
for r in common:
    s=r['预算量']/B; expected=A*s
    r['预期量']=expected
    r['收入量效应']=(A-B)*s*r['预算价']
    r['收入组合效应']=(r['实际量']-expected)*r['预算价']
    r['收入价格效应']=r['实际量']*(r['实际价']-r['预算价'])
    r['成本纯量效应']=(A-B)*s*r['预算单位成本']
    r['成本组合效应']=(r['实际量']-expected)*r['预算单位成本']
    r['成本率效应']=r['实际量']*(r['实际单位成本']-r['预算单位成本'])
    assert r['收入量效应']+r['收入组合效应']+r['收入价格效应']==r['实际收入']-r['预算收入']
    assert r['成本纯量效应']+r['成本组合效应']+r['成本率效应']==r['实际变动成本']-r['预算变动成本']

def total(key, subset=rows): return sum((r[key] for r in subset),Z)
# Totals and bridges
rev_b,rev_a=total('预算收入'),total('实际收入'); vc_b,vc_a=total('预算变动成本'),total('实际变动成本')
fix_b,fix_a=D(6000),D(6800); oneoff=D(500)
con_b,con_a=rev_b-vc_b,rev_a-vc_a; op_b,op_a=con_b-fix_b,con_a-fix_a
revenue_bridge={k:total(k,common) for k in ['收入量效应','收入组合效应','收入价格效应']}
revenue_bridge['新品']=next(r['实际收入'] for r in rows if r['产品']=='N')
revenue_bridge['退出产品']=-next(r['预算收入'] for r in rows if r['产品']=='D')
cost_bridge={k:total(k,common) for k in ['成本纯量效应','成本组合效应','成本率效应']}
cost_bridge['新品']=next(r['实际变动成本'] for r in rows if r['产品']=='N')
cost_bridge['退出产品']=-next(r['预算变动成本'] for r in rows if r['产品']=='D')
assert sum(revenue_bridge.values(),Z)==rev_a-rev_b
assert sum(cost_bridge.values(),Z)==vc_a-vc_b
assert con_a-con_b==(rev_a-rev_b)-(vc_a-vc_b)
assert op_a-op_b==(con_a-con_b)-(fix_a-fix_b)
assert (fix_a-oneoff)-fix_b==D(300)
# product contribution differences, prioritized with explicit measure
for r in rows: r['贡献差异']=r['实际贡献']-r['预算贡献']
# driver records, sourced directly from computed rows
records=[]
for r in common:
    for key,measure in [('收入量效应','收入量'),('收入组合效应','收入组合'),('收入价格效应','收入价格'),('成本纯量效应','变动成本纯量'),('成本组合效应','变动成本组合'),('成本率效应','单位成本率')]:
        v=r[key]
        records.append({'产品':r['产品'],'指标':measure,'金额':v,'方向':'有利' if ((v>0) if '收入' in measure else (v<0)) else '不利'})
# sensitivity to threshold
q=next(r for r in rows if r['产品']=='Q'); threshold=D(15)
needed=threshold-q['实际单位贡献']
assert needed==D(3) and q['实际单位贡献']<threshold
assert next(r for r in rows if r['产品']=='P')['贡献差异']==D(-1100)
assert q['贡献差异']==D(-720)

def fmt(x):
    if isinstance(x,D): return format(x.quantize(D('0.01')), 'f')
    if isinstance(x,dict): return {k:fmt(v) for k,v in x.items()}
    if isinstance(x,list): return [fmt(v) for v in x]
    return x
out={'rows':rows,'totals':{'预算收入':rev_b,'实际收入':rev_a,'预算变动成本':vc_b,'实际变动成本':vc_a,'预算贡献':con_b,'实际贡献':con_a,'预算固定费':fix_b,'实际固定费':fix_a,'预算营业利润':op_b,'实际营业利润':op_a,'剔除搬迁后历史营业利润':op_a+oneoff,'收入变化率':(rev_a-rev_b)/rev_b*100,'营业利润变化率':(op_a-op_b)/op_b*100,'实际贡献率':con_a/rev_a*100},'revenue_bridge':revenue_bridge,'cost_bridge':cost_bridge,'fixed_bridge':{'预算固定费':fix_b,'经常性固定费增加':fix_a-fix_b-oneoff,'搬迁一次性':oneoff,'实际固定费':fix_a},'profit_bridge':{'收入增加':rev_a-rev_b,'变动成本增加':vc_a-vc_b,'贡献变化':con_a-con_b,'固定费增加':fix_a-fix_b,'营业利润变化':op_a-op_b},'drivers':records,'threshold':{'产品':'Q','实际单位贡献':q['实际单位贡献'],'触发线':threshold,'达到触发线所需单价上调(成本不变)':needed,'达到触发线所需单位成本下降(价格不变)':needed}}
(ROOT/'workpaper_output.json').write_text(json.dumps(fmt(out),ensure_ascii=False,indent=2)+'\n',encoding='utf-8')
print(json.dumps(fmt(out),ensure_ascii=False,indent=2))