import csv, io, hashlib from pathlib import Path from datetime import datetime from decimal import Decimal, ROUND_HALF_UP out=Path('output'); out.mkdir(exist_ok=True) inputs=list(Path('inputs').iterdir()) before={str(p):hashlib.sha256(p.read_bytes()).hexdigest() for p in inputs} def read(name): with open('inputs/'+name,newline='') as f: return list(csv.DictReader(f)) def write(name, rows, fields): with open(out/name,'w',newline='') as f: w=csv.DictWriter(f,fieldnames=fields); w.writeheader(); w.writerows(rows) def date(s): for fmt in ('%Y-%m-%d','%d/%m/%Y','%b %d %Y'): try: return datetime.strptime(s,fmt).strftime('%Y-%m-%d') except ValueError: pass raise ValueError(s) def money(s): return '' if not s.strip() else format(Decimal(s.replace('CAD','').replace('$','').strip()),'.2f') def october(s): return '2026-10-01'<=s<='2026-10-31' def normprod(s): p=s.strip().lower() assert p in ('workshop','course') return p.title() exceptions=[] def flag(r,source,entity,reason,detail,related=''): r['included_october']='no'; r['exclusion_reason']=reason exceptions.append(dict(source_file='inputs/'+source,row_id=r['row_id'],entity_id=entity,reason=reason,detail=detail,related_row_id=related)) sales=read('sales.csv'); sf=list(sales[0])+['included_october','exclusion_reason'] # Compare business fields, excluding source row ID; retain first duplicate. seen={}; latest={} for r in sales: sig=tuple((k,v) for k,v in r.items() if k!='row_id') r['included_october']='yes'; r['exclusion_reason']='' if sig in seen: flag(r,'sales.csv',r['order_id'],'duplicate','Identical business fields excluding row_id; retain first occurrence.',seen[sig]) else: seen[sig]=r['row_id'] prior=latest.get(r['order_id']) if prior is None or r['updated_at']>prior['updated_at']: latest[r['order_id']]=r for r in sales: if r['included_october']=='yes': if latest[r['order_id']] is not r: flag(r,'sales.csv',r['order_id'],'superseded','Conflicting order version; retain latest updated_at.',latest[r['order_id']]['row_id']) elif r['status']!='paid': flag(r,'sales.csv',r['order_id'],'non_paid_status','Status '+r['status']+' is not paid.') elif not october(date(r['date'])): flag(r,'sales.csv',r['order_id'],'outside_period','Order date outside October 1–31, 2026.') r['date']=date(r['date']); r['product']=normprod(r['product']); r['gross_cad']=money(r['gross_cad']) paid={k:r for k,r in latest.items() if r['status']=='paid'} refunds=read('refunds.csv'); rf=list(refunds[0])+['product','included_october','exclusion_reason']; seen={} for r in refunds: sig=tuple((k,v) for k,v in r.items() if k!='row_id') r['settled_date']=date(r['settled_date']); r['amount_cad']=money(r['amount_cad']) order=paid.get(r['order_id']); r['product']=order['product'] if order else '' r['included_october']='yes'; r['exclusion_reason']='' if sig in seen: flag(r,'refunds.csv',r['refund_id'],'duplicate','Identical business fields excluding row_id; retain first occurrence.',seen[sig]) elif r['status']!='settled': flag(r,'refunds.csv',r['refund_id'],'not_settled','Pending refund is not deducted.') elif not october(r['settled_date']): flag(r,'refunds.csv',r['refund_id'],'outside_period','Settlement falls in November; do not deduct in October.') elif order is None: flag(r,'refunds.csv',r['refund_id'],'invalid_order','No matching paid order for '+r['order_id']+'; amount excluded and product left unknown.') elif r['amount_cad']=='': flag(r,'refunds.csv',r['refund_id'],'missing_amount','Settled October refund for paid order; unknown amount preserved blank. Course and combined net results are provisional.',order['row_id']) seen.setdefault(sig,r['row_id']) ads=read('ads.csv'); af=list(ads[0])+['included_october','exclusion_reason']; seen={} for r in ads: sig=tuple((k,v) for k,v in r.items() if k!='row_id') r['date']=date(r['date']); r['product']=normprod(r['product']); r['channel']=r['channel'].strip().lower(); r['spend_cad']=money(r['spend_cad']) r['included_october']='yes'; r['exclusion_reason']='' if sig in seen: flag(r,'ads.csv',r['ad_id'],'duplicate','Identical business fields excluding row_id; retain first occurrence.',seen[sig]) elif not october(r['date']): flag(r,'ads.csv',r['ad_id'],'outside_period','Ad date falls in November; do not include in October spend.') seen.setdefault(sig,r['row_id']) write('cleaned-sales.csv',sales,sf); write('cleaned-refunds.csv',refunds,rf); write('cleaned-ads.csv',ads,af) write('exceptions.csv',exceptions,['source_file','row_id','entity_id','reason','detail','related_row_id']) inc=lambda rows:[r for r in rows if r['included_october']=='yes'] metrics={} for p in ['Workshop','Course','Combined']: subset=lambda rows:[r for r in inc(rows) if p=='Combined' or r['product']==p] s,rr,a=map(subset,[sales,refunds,ads]) n=len(s); gross=sum((Decimal(r['gross_cad']) for r in s),Decimal(0)); refunds_total=sum((Decimal(r['amount_cad']) for r in rr),Decimal(0)); spend=sum((Decimal(r['spend_cad']) for r in a),Decimal(0)); net=gross-refunds_total metrics[p]=(n,gross,refunds_total,net,spend,net-spend,(net/n).quantize(Decimal('.01'),rounding=ROUND_HALF_UP)) assert metrics['Workshop']==(5,1920,240,1680,300,1380,336) assert metrics['Course']==(4,900,60,840,150,690,210) assert metrics['Combined']==(9,2820,300,2520,450,2070,280) assert [len(inc(rows)) for rows in (sales,refunds,ads)]==[9,2,5] assert len(exceptions)==13 for rows,key in [(sales,'order_id'),(refunds,'refund_id'),(ads,'ad_id')]: ids=[r[key] for r in inc(rows)]; assert len(ids)==len(set(ids)) for r in rows: assert (r['included_october']=='no') == any(e['row_id']==r['row_id'] for e in exceptions) for order_id,order in paid.items(): applied=sum((Decimal(r['amount_cad']) for r in inc(refunds) if r['order_id']==order_id),Decimal(0)) assert applied<=Decimal(order['gross_cad']) for filename,rows in [('cleaned-sales.csv',sales),('cleaned-refunds.csv',refunds),('cleaned-ads.csv',ads)]: with open(out/filename,newline='') as f: saved=list(csv.DictReader(f)) assert saved==rows for r in saved: for k in ('date','settled_date'): if k in r: assert datetime.strptime(r[k],'%Y-%m-%d').strftime('%Y-%m-%d')==r[k] for k in ('gross_cad','amount_cad','spend_cad'): if k in r and r[k]!='': assert r[k]==format(Decimal(r[k]),'.2f') assert next(r for r in refunds if r['row_id']=='R06')['amount_cad']=='' assert before=={str(p):hashlib.sha256(p.read_bytes()).hexdigest() for p in inputs} print('PASS: metrics, row counts, uniqueness of included IDs, 13 exclusion links, known applied refund caps, CSV round trips, dates, money formatting, blank R06, and unchanged input SHA-256 hashes.') print(metrics)