mileage-bonus/outputs/september-20261009/build_september.mjs

import fs from 'node:fs/promises';
import {Workbook,SpreadsheetFile} from '@oai/artifact-tool';
import {fileURLToPath} from 'node:url';
import path from 'node:path';
import {applyAugustLayout} from './august_layout.mjs';
const dir=path.dirname(fileURLToPath(import.meta.url));
const d=JSON.parse(await fs.readFile(path.join(dir,'september_calculation.json'),'utf8'));
const w=Workbook.create(),previews=[];
const meta=JSON.parse(await fs.readFile(path.join(dir,'../hydrogen-history-20260916/july_style_metadata.json'),'utf8'));
const department=p=>[...new Set(d.groups.filter(g=>g.sales===p).map(g=>g.department))].join('、');
const personalName=p=>(department(p).includes('、')?'跨部门':department(p).replace('业务',''))+'-'+p;
const money='#,##0.00;[Red](#,##0.00);0.00';
const col=n=>{let s='';for(;n;n=Math.floor((n-1)/26))s=String.fromCharCode(65+(n-1)%26)+s;return s;};
const esc=s=>String(s).replaceAll('"','""');
const put=(s,r,a)=>s.getRange(`A${r}:${col(a.length)}${r}`).values=[a.map(x=>x===undefined?null:x)];
function sh(name,cols,rows){const s=w.worksheets.add(name);s.showGridLines=true;s.getRange(`A1:${col(cols)}${rows}`).format={font:{name:'宋体',size:11,color:'#000000'},columnWidth:14,rowHeight:19,verticalAlignment:'center'};return s;}
function widths(s,vals,rows=20){for(const [c,v] of Object.entries(vals))s.getRange(`${c}1:${c}${rows}`).format.columnWidth=v;}
function head(s,r,a){put(s,r,a);s.getRange(`A${r}:${col(a.length)}${r}`).format={fill:'#D9E1F2',font:{name:'宋体',size:11,bold:true,color:'#000000'},wrapText:true,horizontalAlignment:'center',rowHeight:34,borders:{preset:'all',style:'thin',color:'#000000'}};}
function table(s,h,rs,ws={},nums=[]){s.getRange(`A1:${col(h.length)}${rs.length+1}`).format={font:{name:'宋体',size:11,color:'#000000'},rowHeight:19,verticalAlignment:'center',borders:{preset:'all',style:'thin',color:'#000000'}};head(s,1,h);if(rs.length)s.getRange(`A2:${col(h.length)}${rs.length+1}`).values=rs;for(const c of nums)s.getRange(`${c}2:${c}${rs.length+1}`).setNumberFormat(money);widths(s,ws,rs.length+1);s.freezePanes.freezeRows(1);}
const mainRules=sh('考核奖励规则',6,70),monthly=sh('里程明细9月',24,d.records.length+1),calc=sh('9月计算过程',29,d.groups.length+1),track=sh('车辆考核追踪',44,d.groups.length+1),ledger=sh('9月奖金发放记录',18,d.ledger.length+6),oldSum=sh('9月汇总',3,250),sumSheet=sh('9月汇总（新版界面）',14,38);
const personal={};for(const p of d.people)personal[p]=sh(personalName(p),14,d.groups.filter(x=>x.sales===p).length*10+20);
const detail=sh('业务考核视图',31,d.records.length+1),daily=sh('逐日取数依据',16,d.daily.length+1),gap=sh('里程缺口',10,d.gaps.length+1),rules=sh('规则与名单',9,205),history=sh('历史结算资格',9,d.historical_months.length+1),aug=sh('8月已发台账',10,d.august_ledger.length+3);
const dr=d.daily.length+1,lr=d.ledger.length+1,rr=d.records.length+1;
const L=c=>`'9月奖金发放记录'!$${c}$2:$${c}$${lr}`;
const pay=(p,plate=null,cat=null)=>`SUMIFS(${L('J')},${L('B')},${p}${plate?`,${L('A')},${plate}`:''}${cat?`,${L('E')},"${cat}"`:''})`;
table(rules,['项目','确认口径'],[
 ['测试差值阈值(km)',50],['本月自然日数',30],['锁定8月实际已发(元)',45065.47],
 ['测试日判定','仅9月名单车辆：每日仪表−GPS严格大于50公里。'],
 ['测试日里程','取当日GPS。'],['非测试日里程','有纯氢（含真实0）取纯氢，无纯氢取仪表。'],
 ['候选无法判定','缺仪表或GPS时不强判；有纯氢取纯氢，无纯氢兜底GPS。'],
 ['数据缺失','来源空白保留缺口，按已有里程累计；不自动将普通车辆改取GPS。'],
 ['重叠交接日','交接日计前段，后段次日起算；调整天数、目标及奖金折算。'],
 ['历史结算','1—8月已发及原8月扣减锁定，不追扣、不重复补发。'],
 ['历史里程','同车同负责人累计包含全部历史考核月，中间无考核不清零；8月原有缺口仍保留。'],
 ['新政策车辆','沿用新合同+新提车.xls原始39辆名单，本月考核匹配36辆。'],
 ['人员归属','按9月考核记录负责人分组；不因车辆转交自动转移旧负责人历史。'],
 ['离职及亏损','沿用原规则：伍仲文、岑彦、赵连飞、钱婧不发不转交；亏损暂不处理。'],
 ['补数更新','取数、里程、金额及汇总含公式。新增数据后需重新运行完整核算，以重新生成发放资格。']
 ],{A:25,B:91});
head(rules,19,['9月新政策考核目标','月奖励（元）']);
[['交投40辆4.5T普货',150],['交投190辆4.5T冷链车',150],['羚牛136辆4.5T冷链车',300],['恒运50辆4.5T普货',300],['羚牛100辆18T',150]].forEach((r,i)=>put(rules,20+i,r));
head(rules,27,['新政策车牌','9月是否有考核']);d.policy_plates.forEach((p,i)=>put(rules,28+i,[p,d.records.some(r=>r['车牌号']===p)?'是':'否']));
rules.getRange('D1:E1').values=[['9月测试候选车牌','9月是否有考核']];rules.getRange('D1:E1').format={fill:'#D9E1F2',font:{bold:true},rowHeight:38};
rules.getRange(`D2:E${d.tests.length+1}`).values=d.tests.map(p=>[p,d.records.some(r=>r['车牌号']===p)?'是':'否']);widths(rules,{D:23,E:23,G:25,H:82},205);
rules.getRange('G1:H1').values=[['数据来源','文件']];let sr=2;for(const [key,v]of Object.entries(d.sources))rules.getRange(`G${sr}:H${sr++}`).values=[[key,path.basename(v.path)]];
rules.getRange('G10:H10').values=[['8月基线','里程任务考核_8月核算_纯氢修正版.xlsx，按用户确认锁定45,065.47元。']];
previews.push(['规则与名单','A1:B16']);
table(daily,['考核源表行','车牌号','业务负责人','日期','仪表(km)','GPS(km)','纯氢(km)','测试候选','仪表−GPS(km)','测试日判定','采用来源','计入里程(km)','缺口标识','仪表来源单元格','GPS来源单元格','纯氢来源单元格'],d.daily.map(x=>[x.source_row,x.plate,x.sales,new Date(x.date+'T00:00:00Z'),x.meter,x.gps,x.hydrogen,x.test_candidate?'是':'否',null,null,null,null,null,x.provenance.meter||'未覆盖',x.provenance.gps||'未覆盖',x.provenance.hydrogen||'未覆盖']),{A:13,B:17,C:14,D:15,E:16,F:16,G:16,H:12,I:20,J:16,K:27,L:20,M:13,N:90,O:90,P:90},['E','F','G','I','L']);
daily.getRange(`D2:D${dr}`).setNumberFormat('yyyy-mm-dd');
daily.getRange(`I2:M${dr}`).formulas=d.daily.map((_,i)=>{const r=i+2;return[
 `=IF(AND(ISNUMBER(E${r}),ISNUMBER(F${r})),E${r}-F${r},"")`,
 `=IF(H${r}="否","不适用",IF(AND(ISNUMBER(E${r}),ISNUMBER(F${r})),IF(I${r}>'规则与名单'!$B$2,"是","否"),"未判定"))`,
 `=IF(J${r}="是",IF(ISNUMBER(F${r}),"测试日GPS","缺失"),IF(ISNUMBER(G${r}),"非测试日纯氢",IF(AND(H${r}="是",J${r}="未判定"),IF(ISNUMBER(F${r}),"测试名单无纯氢GPS","缺失"),IF(ISNUMBER(E${r}),"非测试日仪表","缺失"))))`,
 `=IF(K${r}="缺失","",IF(K${r}="非测试日纯氢",G${r},IF(K${r}="非测试日仪表",E${r},F${r})))`,
 `=IF(K${r}="缺失",1,0)`];});
daily.freezePanes.freezeColumns(3);previews.push(['逐日取数依据','A1:M12']);
const recordHeaders=[...d.headers,'月度目标里程','奖励基准','缺口天数','新政策','测试候选','已有里程口径','核算源行','原考核天数','原应考核里程','取数说明'];
table(detail,recordHeaders,d.records.map(r=>[...d.headers.map(h=>['实际行驶里程(km)','完成率(%)','是否达标','应考核里程(km)'].includes(h)?null:r[h]),r['月度目标里程'],r['月奖励'],null,r['新政策']?'是':'否',r['测试候选']?'是':'否',null,r['源表行'],r['原考核天数'],r['原应考核里程(km)'],r['取数方式']]),{A:18,D:16,E:14,F:35,G:32,H:36,J:30,K:22,L:22,N:20,O:22,P:16,Q:25,V:20,W:18,X:15,Y:12,Z:14,AA:48,AB:14,AC:16,AD:22,AE:65},['N','O','W','AD']);
detail.getRange(`N2:Q${rr}`).formulas=d.records.map((r,i)=>{let j=i+2;return[`=ROUND(V${j}*M${j}/'规则与名单'!$B$3,2)`,`=ROUND(SUMIFS('逐日取数依据'!$L$2:$L$${dr},'逐日取数依据'!$A$2:$A$${dr},AB${j}),2)`,`=ROUND(O${j}/N${j}*100,2)`,`=IF(X${j}=M${j},"待补里程",IF(O${j}>=N${j},"达标",IF(X${j}>0,"已有里程未达标","未达标")))`];});
detail.getRange(`X2:X${rr}`).formulas=d.records.map((_,i)=>[`=SUMIFS('逐日取数依据'!$M$2:$M$${dr},'逐日取数依据'!$A$2:$A$${dr},AB${i+2})`]);
detail.getRange(`AA2:AA${rr}`).formulas=d.records.map((_,i)=>[`=IF(X${i+2}>0,"仅累计已有数据，缺失不代表实际零里程","数据齐全")`]);
detail.getRange(`Q2:Q${rr}`).conditionalFormats.add('containsText',{text:'待补',format:{fill:'#FFF2CC',font:{color:'#9C5700'}}});
detail.freezePanes.freezeColumns(1);previews.push(['业务考核视图','J1:Q12']);
table(gap,['车牌号','业务负责人','日期','考核源表行','需补来源','仪表(km)','GPS(km)','纯氢(km)','测试候选','缺口原因'],d.gaps.map(x=>[x.plate,x.sales,new Date(x.date+'T00:00:00Z'),x.source_row,x.needed,x.meter,x.gps,x.hydrogen,x.test_candidate?'是':'否',x.reason]),{A:18,B:16,C:17,D:17,E:15,F:18,G:18,H:18,I:16,J:68},['F','G','H']);gap.getRange(`C2:C${d.gaps.length+1}`).setNumberFormat('yyyy-mm-dd');previews.push(['里程缺口','A1:J12']);
table(history,['考核月','车牌号','原业务负责人','考核天数','当时奖励基准','截至8月已结算','考核目标(km)','已修正里程(km)','结算含义'],d.historical_months.map(x=>[x.month,x.plate,x.sales,x.days,x.reward,x.settled?'是':'否',x.target,x.actual,x.settled?'已发或已核定扣减，不重复补发':'可按后续累计达标判定补发']),{A:13,B:18,C:18,D:16,E:22,F:22,G:22,H:22,I:48},['E','G','H']);previews.push(['历史结算资格','A1:I12']);
table(aug,['车牌号','业务负责人','发放类别','考核月','原应发(元)','原扣减(元)','实际已发(元)','奖励基准','折算分子','折算分母'],d.august_ledger.map(l=>[l.plate,l.sales,l.category,l.assessment_month,l.gross_amount,l.withheld,l.amount,l.base,l.days,l.month_days]),{A:18,B:16,C:22,D:13,E:20,F:20,G:22,H:18,I:16,J:16},['E','F','G','H']);put(aug,d.august_ledger.length+2,['合计']);aug.getRange(`G${d.august_ledger.length+2}`).formulas=[[`=SUM(G2:G${d.august_ledger.length+1})`]];previews.push(['8月已发台账','A1:J12']);
const layout=applyAugustLayout({w,d,meta,mainRules,monthly,oldSum,sumSheet,ledger,calc,track,personal,detail,previews,put,head,table,widths,col,pay,L,money});
const correction=JSON.parse(await fs.readFile(path.join(dir,'cumulative_fix_audit.json'),'utf8'));
const correctionSheet=sh('9月修正对照',7,correction.changes.length+12);
put(correctionSheet,1,['中断月份累计承接修正']);
put(correctionSheet,3,['原核算金额',correction.previous_total,'修正后金额',correction.corrected_total]);
correctionSheet.mergeCells('A5:G6');put(correctionSheet,5,['原程序遇到上月无考核记录时重置累计，历史補发却仍查更早月份。现按同车同负责人全部考核月累计判定，历史已发不变。']);correctionSheet.getRange('A5:G6').format.wrapText=true;
head(correctionSheet,8,['车牌号','业务负责人','发放类别','原金额','修正金额','变化金额','原因']);
correction.changes.forEach((r,i)=>{put(correctionSheet,i+9,[r.plate,r.sales,r.category,r.old,r.new,null,r.new===0?'纳入中断前历史后累计未达标，取消误补发':'纳入中断前历史后累计达标，补发本月']);correctionSheet.getRange(`F${i+9}`).formulas=[[`=E${i+9}-D${i+9}`]];});
widths(correctionSheet,{A:18,B:16,C:22,D:18,E:18,F:18,G:64},correction.changes.length+12);correctionSheet.getRange(`D9:F${correction.changes.length+8}`).setNumberFormat(money);correctionSheet.getRange('B3:D3').setNumberFormat(money);previews.push(['9月修正对照','A1:G18']);
w.recalculate();
if(Math.abs(sumSheet.getRange('J2').values[0][0]-d.summary.amount)>1e-7)throw Error('Summary amount mismatch');
if(Math.abs(aug.getRange(`G${d.august_ledger.length+2}`).values[0][0]-45065.47)>1e-7)throw Error('Historical actuals changed');
const computedDaily=daily.getRange(`K2:M${dr}`).values;
d.daily.forEach((x,i)=>{let [mode,v,missing]=computedDaily[i];if(mode!==x.selection||missing!==(x.included===null?1:0)||x.included!==null&&Math.abs(v-x.included)>1e-8||x.included===null&&v!==''&&v!==null)throw Error('Daily selection mismatch '+i);});
const recVals=detail.getRange(`N2:Q${rr}`).values;
d.records.forEach((r,i)=>{if(Math.abs(recVals[i][0]-r['应考核里程(km)'])>1e-6||Math.abs(recVals[i][1]-r['实际行驶里程(km)'])>1e-6)throw Error('Record mileage mismatch '+i);});
if(Math.abs(oldSum.getRange(layout.oldTotal).values[0][0]-d.summary.amount)>1e-7)throw Error('Vertical summary mismatch');
for(const [p,info] of Object.entries(layout.personControls)){const expected=d.ledger.filter(l=>l.sales===p).reduce((a,l)=>a+l.amount,0);if(Math.abs(w.worksheets.getItem(info.sheet).getRange(info.total).values[0][0]-expected)>1e-7)throw Error('Personal mismatch '+p);}
await fs.writeFile(path.join(dir,'layout_controls.json'),JSON.stringify(layout,null,2));
const errors=await w.inspect({kind:'match',searchTerm:'#REF!|#DIV/0!|#VALUE!|#NAME\\?|#N/A|#NUM!|#NULL!|#SPILL!|#CALC!',options:{useRegex:true,maxResults:20},maxChars:3000});await fs.writeFile(path.join(dir,'formula_scan.ndjson'),errors.ndjson);
const snap=await w.inspect({kind:'table',range:'9月汇总（新版界面）!J1:N3',include:'values,formulas',maxChars:1600});await fs.writeFile(path.join(dir,'summary_inspect.ndjson'),snap.ndjson);
for(const [i,[name,range]]of previews.entries()){const img=await w.render({sheetName:name,range,scale:1});await fs.writeFile(path.join(dir,`preview_${i}.png`),new Uint8Array(await img.arrayBuffer()));}
await fs.writeFile(path.join(dir,'preview_index.json'),JSON.stringify(previews));
const out=await SpreadsheetFile.exportXlsx(w);await out.save(path.join(dir,'里程任务考核_2026年9月_含缺口核算.xlsx'));
console.log(JSON.stringify({amount:d.summary.amount,sheets:w.worksheets.items.length,daily:d.daily.length,records:d.records.length}));