Files
sap-erp/public/costing-comparison.html
John eead8f5ffd
SAP-ERP Portal CI/CD / build (push) Successful in 3m57s
sale order
2026-10-05 18:45:17 +05:30

886 lines
45 KiB
HTML
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
<!DOCTYPE html>
<html lang="en">
<head>
<script src="/auth-guard.js?v=4"></script>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>P&L Comparison | MITRA ERP Portal</title>
<link rel="preconnect" href="https://fonts.googleapis.com">
<link href="https://fonts.googleapis.com/css2?family=Space+Grotesk:wght@400;600;700&display=swap" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/xlsx-js-style@1.2.0/dist/xlsx.bundle.js"></script>
<style>
*{box-sizing:border-box;margin:0;padding:0}
body{font-family:'Space Grotesk',sans-serif;background:#f0f2f5;color:#1a1a2e;min-height:100vh}
.pg{padding:20px 24px}
.hdr{display:flex;align-items:center;justify-content:space-between;flex-wrap:wrap;gap:12px;margin-bottom:18px}
.hdr h1{font-size:20px;font-weight:700}.hdr h1 span{color:#047e7e}
.btn{height:36px;padding:0 16px;border-radius:8px;font-size:12px;font-weight:700;font-family:inherit;border:none;cursor:pointer;transition:all .15s;display:inline-flex;align-items:center;gap:6px;white-space:nowrap}
.btn-primary{background:#047e7e;color:#fff}.btn-primary:hover{background:#035b5b}
.btn-primary:disabled{background:#b0bec5;cursor:not-allowed;opacity:.7}
.btn-outline{background:#fff;color:#047e7e;border:1.5px solid #047e7e}.btn-outline:hover{background:#f0fafa}
.btn-sm{height:30px;padding:0 12px;font-size:11px}
.btn-purple{background:#7c3aed;color:#fff}.btn-purple:hover{background:#6d28d9}
.card{background:#fff;border-radius:14px;box-shadow:0 1px 8px rgba(0,0,0,.08);overflow:hidden}
/* ── Snapshot picker ── */
.snap-picker{padding:16px 20px;border-bottom:1px solid #e8eaed;background:#fff}
.snap-picker-title{font-size:11px;font-weight:700;color:#6b7280;text-transform:uppercase;letter-spacing:.6px;margin-bottom:10px}
.snap-list{display:flex;flex-wrap:wrap;gap:8px;max-height:180px;overflow-y:auto;padding:2px}
.snap-list::-webkit-scrollbar{width:5px}.snap-list::-webkit-scrollbar-thumb{background:#c1c7ce;border-radius:4px}
.snap-item{display:flex;align-items:center;gap:8px;padding:7px 12px;border-radius:8px;border:1.5px solid #e8eaed;background:#fafbfc;cursor:pointer;transition:all .15s;font-size:11px;font-weight:600;color:#374151;max-width:340px}
.snap-item:hover{border-color:#047e7e;background:#f0fafa;color:#047e7e}
.snap-item.selected{border-color:#047e7e;background:#e0f4f4;color:#004d40}
.snap-item .snap-tag{font-size:9px;font-weight:700;padding:2px 6px;border-radius:4px;background:#047e7e;color:#fff;flex-shrink:0}
.snap-item .snap-meta{font-size:10px;color:#9ca3af;font-weight:400}
.snap-empty{font-size:12px;color:#9ca3af;padding:16px 0}
/* ── Selected bar ── */
.sel-bar{display:flex;align-items:center;gap:10px;padding:10px 20px;border-bottom:1px solid #e8eaed;background:#f8fafb;flex-wrap:wrap;min-height:52px}
.sel-bar-label{font-size:10px;font-weight:700;color:#6b7280;text-transform:uppercase;letter-spacing:.6px;white-space:nowrap;flex-shrink:0}
.sel-chips{display:flex;flex-wrap:wrap;gap:6px;flex:1}
.sel-chip{display:inline-flex;align-items:center;gap:5px;padding:4px 10px;border-radius:6px;background:#e0f4f4;border:1.5px solid #80cbc4;font-size:11px;font-weight:600;color:#004d40}
.sel-chip button{background:none;border:none;cursor:pointer;color:#047e7e;font-size:14px;line-height:1;padding:0 0 0 2px}
.sel-chip button:hover{color:#dc2626}
/* ── Toggles ── */
.toggle-row{display:flex;align-items:center;gap:16px;padding:8px 20px;background:#f8fafb;border-bottom:1px solid #e8eaed;font-size:12px;color:#374151;flex-wrap:wrap}
.toggle-row label{display:flex;align-items:center;gap:6px;cursor:pointer;font-weight:600}
.toggle-row input[type=checkbox]{width:15px;height:15px;accent-color:#047e7e;cursor:pointer}
/* ── Progress ── */
.progress-bar{height:3px;background:#e0f4f4;overflow:hidden}
.progress-fill{height:100%;background:#047e7e;transition:width .3s;width:0%}
/* ── Table ── */
.cmp-wrap{overflow-x:auto;overflow-y:auto;max-height:65vh}
.cmp-wrap::-webkit-scrollbar{height:7px;width:7px}
.cmp-wrap::-webkit-scrollbar-track{background:#f1f3f5}
.cmp-wrap::-webkit-scrollbar-thumb{background:#c1c7ce;border-radius:4px}
table.cmp{border-collapse:collapse;font-size:12px}
/* sticky col */
.cmp th.part,.cmp td.part{position:sticky;left:0;z-index:3;min-width:210px;max-width:210px;font-size:12px}
.cmp thead th.part{background:#0f4c4c}
.cmp tbody td.part{background:#fff;z-index:2;border-right:2px solid #d1dae3;font-weight:600;color:#1a1a2e}
/* thead row 1 — period names — sticky top */
.cmp thead tr:first-child th{background:#0f4c4c;color:#fff;font-weight:700;padding:10px 14px;text-align:center;white-space:nowrap;font-size:11px;letter-spacing:.2px;border-right:1px solid rgba(255,255,255,.12);position:sticky;top:0;z-index:4}
.cmp thead tr:first-child th.part{text-align:left;font-size:12px;letter-spacing:.3px;border-right:2px solid rgba(255,255,255,.25);background:#047e7e;z-index:5}
/* thead row 2 — Total / % Rev — sticky below row 1 */
.cmp thead tr.sub-hdr th{background:#065f46;color:rgba(255,255,255,.9);font-weight:700;padding:6px 14px;text-align:right;font-size:10px;text-transform:uppercase;letter-spacing:.4px;border-right:1px solid rgba(255,255,255,.08);position:sticky;top:44px;z-index:4}
.cmp thead tr.sub-hdr th.part{text-align:left;font-size:10px;color:rgba(255,255,255,.7);z-index:5}
.cmp thead tr.sub-hdr th.bdr{border-left:2px solid rgba(255,255,255,.2)}
/* body cells */
.cmp tbody td{padding:6px 14px;border-bottom:1px solid #f0f2f4;vertical-align:middle;white-space:nowrap;color:#374151}
.cmp tbody td.num{text-align:right;font-variant-numeric:tabular-nums;font-weight:600;color:#1a1a2e}
.cmp tbody td.pct{text-align:right;color:#9ca3af;font-size:10px;font-weight:400;min-width:56px}
.cmp tbody td.bdr{border-left:2px solid #e8eaed}
.cmp tr.sub:hover td:not(.part){background:#f5fefe}
/* Revenue row */
.cmp tr.rev td{background:#e0f4f4;border-top:2px solid #047e7e;border-bottom:2px solid #80cbc4;padding:9px 14px}
.cmp tr.rev td.part{background:#e0f4f4;color:#004d40;font-weight:700;font-size:13px}
.cmp tr.rev td.num{color:#047e7e;font-size:13px;font-weight:800}
/* sub-items (Opening Stock, Purchases etc.) */
.cmp tr.sub td.part{padding-left:28px;color:#047e7e;font-weight:600}
.cmp tr.sub td.num{color:#374151;font-weight:600}
/* section headers */
.cmp tr.sec-hdr td{background:#f0f4f8;font-weight:700;color:#1a1a2e;padding:8px 14px;border-top:1px solid #d1dae3}
.cmp tr.sec-hdr td.part{background:#f0f4f8}
/* total rows */
.cmp tr.tot td{background:#e8f4f4;font-weight:700;border-top:2px solid #80cbc4;padding:7px 14px}
.cmp tr.tot td.part{color:#004d40;background:#e8f4f4;font-size:12px}
.cmp tr.tot td.num{color:#047e7e}
/* gross profit */
.cmp tr.gp td{background:#e8f5e9;font-weight:700;border-top:2px solid #66bb6a;padding:8px 14px}
.cmp tr.gp td.part{background:#e8f5e9;color:#1b5e20;font-size:13px}
.cmp tr.gp td.num{color:#2e7d32;font-size:13px;font-weight:800}
/* net profit */
.cmp tr.grand td{background:#004d40;color:#fff;font-weight:800;font-size:13px;padding:10px 14px;border-top:2px solid #047e7e}
.cmp tr.grand td.part{background:#004d40;color:#fff;font-size:13px}
.cmp tr.grand td.pct{color:rgba(255,255,255,.6)}
/* blank spacer */
.cmp tr.blank td{height:10px;border:none!important;background:#fff!important}
.cmp tr.blank td.part{background:#fff!important}
/* colors */
.neg{color:#dc2626!important;font-weight:700!important}
/* ── Status ── */
.status-bar{display:flex;align-items:center;gap:10px;padding:8px 20px;background:#f8fafb;border-top:1px solid #e8eaed;font-size:12px;color:#6b7280;border-radius:0 0 14px 14px}
.status-dot{width:8px;height:8px;border-radius:50%;background:#d1d5db;flex-shrink:0}
.status-dot.ok{background:#10b981}.status-dot.err{background:#ef4444}
.status-dot.loading{background:#f59e0b;animation:pulse 1s infinite}
@keyframes pulse{0%,100%{opacity:1}50%{opacity:.4}}
.spinner{display:inline-block;width:14px;height:14px;border:2px solid rgba(255,255,255,.4);border-top-color:#fff;border-radius:50%;animation:spin .6s linear infinite;vertical-align:middle;margin-right:4px}
@keyframes spin{to{transform:rotate(360deg)}}
.toast{position:fixed;bottom:24px;right:24px;z-index:9999;padding:12px 20px;border-radius:10px;font-size:13px;font-weight:600;color:#fff;box-shadow:0 4px 20px rgba(0,0,0,.15);transform:translateY(20px);opacity:0;transition:all .3s;pointer-events:none}
.toast.show{transform:translateY(0);opacity:1}
.toast.success{background:#10b981}.toast.error{background:#ef4444}.toast.info{background:#047e7e}
.empty{text-align:center;padding:56px 20px;color:#9ca3af;font-size:13px}
</style>
<link rel="stylesheet" href="/theme.css?v=3"/>
</head>
<body>
<script src="/sidebar.js?v=31"></script>
<div class="pg">
<div class="hdr">
<h1><span>P&amp;L</span> Comparison</h1>
<div style="display:flex;gap:8px">
<button class="btn btn-primary" id="btnCompare" onclick="runComparison()" disabled>Compare</button>
<button class="btn btn-outline btn-sm" id="btnPrint" onclick="printSection('cmpPane','P&L Comparison')" disabled>Print</button>
<button class="btn btn-outline btn-sm" id="btnExport" disabled>Download Excel</button>
</div>
</div>
<div class="card">
<!-- Snapshot picker -->
<div class="snap-picker">
<div class="snap-picker-title">Select snapshots to compare &mdash; click to add/remove</div>
<div class="snap-list" id="snapList">
<div class="snap-empty">Loading snapshots…</div>
</div>
</div>
<!-- Selected bar -->
<div class="sel-bar">
<span class="sel-bar-label">Comparing</span>
<div class="sel-chips" id="selChips"><span style="font-size:11px;color:#9ca3af">No snapshots selected yet</span></div>
<button class="btn btn-sm btn-outline" onclick="clearSelection()">Clear All</button>
</div>
<!-- Toggles -->
<div class="toggle-row">
<label><input type="checkbox" id="chkPct" checked> Show % of Revenue</label>
</div>
<!-- Progress -->
<div class="progress-bar"><div class="progress-fill" id="progressFill"></div></div>
<!-- Table -->
<div id="cmpPane"><div class="empty">Select snapshots above and click Compare</div></div>
<div class="status-bar">
<div class="status-dot" id="statusDot"></div>
<span id="statusMsg">Select snapshots to compare</span>
</div>
</div>
</div>
<div class="toast" id="toast"></div>
<script>
(function(){
const tok=sessionStorage.getItem('portal_token');
if(!tok){window.location.href='/';return;}
const hdrs={'Authorization':'Bearer '+tok,'Content-Type':'application/json'};
let allSnaps=[]; // full list from API
let selectedIds=[]; // ordered list of selected snapshot IDs
let periodsData=[]; // loaded comparison data
// ── Load snapshot list ────────────────────────────────────────────────────────
async function loadSnapshotList(){
try{
const r=await fetch('/api/costing/snapshots',{headers:hdrs});
const j=await r.json(); if(!j.success) return;
// Exclude Trial Balance snapshots (saved with a "TB: " name prefix) — only
// Monthly Accounts P&L snapshots are relevant to this comparison.
allSnaps=j.data.filter(s=>!/^TB:/i.test(s.snap_name||''));
renderSnapList();
}catch(e){
document.getElementById('snapList').innerHTML=`<div class="snap-empty">Failed to load snapshots: ${esc(e.message)}</div>`;
}
}
function renderSnapList(){
const el=document.getElementById('snapList');
if(!allSnaps.length){
el.innerHTML='<div class="snap-empty">No snapshots saved yet. Go to <b>Monthly Accounts</b>, load data and click <b>Save Snapshot</b>.</div>';
return;
}
el.innerHTML=allSnaps.map(s=>{
const sel=selectedIds.includes(String(s.id));
return `<div class="snap-item${sel?' selected':''}" onclick="toggleSnap('${s.id}')">
<span class="snap-tag">${sel?'✓':'+'}${selectedIds.indexOf(String(s.id))>=0?' #'+(selectedIds.indexOf(String(s.id))+1):''}</span>
<div>
<div>${esc(s.snap_name)}</div>
</div>
</div>`;
}).join('');
}
function toggleSnap(id){
const idx=selectedIds.indexOf(id);
if(idx>=0) selectedIds.splice(idx,1);
else selectedIds.push(id);
renderSnapList();
renderSelChips();
document.getElementById('btnCompare').disabled=selectedIds.length<1;
}
window.toggleSnap=toggleSnap;
function clearSelection(){selectedIds=[];renderSnapList();renderSelChips();document.getElementById('btnCompare').disabled=true;}
window.clearSelection=clearSelection;
function renderSelChips(){
const el=document.getElementById('selChips');
if(!selectedIds.length){
el.innerHTML='<span style="font-size:11px;color:#9ca3af">No snapshots selected yet</span>';
return;
}
el.innerHTML=selectedIds.map((id,i)=>{
const s=allSnaps.find(x=>String(x.id)===id);
if(!s) return '';
return `<span class="sel-chip">
<span style="font-size:9px;color:#9ca3af">#${i+1}</span>
${esc(s.snap_name)}
<button onclick="toggleSnap('${id}')" title="Remove">×</button>
</span>`;
}).join('');
}
// ── Run comparison ─────────────────────────────────────────────────────────────
async function runComparison(){
if(!selectedIds.length){showToast('Select at least one snapshot','error');return;}
const btn=document.getElementById('btnCompare');
btn.disabled=true; btn.innerHTML='<span class="spinner"></span>Loading…';
setStatus('Loading snapshot data…','loading');
setProgress(0);
try{
let done=0;
const results=await Promise.all(selectedIds.map(async id=>{
const r=await fetch(`/api/costing/snapshot/${id}`,{headers:hdrs});
const j=await r.json(); if(!j.success) throw new Error(j.message);
const d=j.data;
const data=(d.accounts||[]).map(r=>({...r,provision:r.override_val!=null?Number(r.override_val):null}));
const det=d.stock_detail||{open:{rm:0,wip:0,fg:0,trade:0,conv:0},close:{rm:0,wip:0,fg:0,trade:0,conv:0}};
const stock={open:d.opening_stock||0,close:d.closing_stock||0,detail:det};
const snap=allSnaps.find(s=>String(s.id)===id);
done++; setProgress(Math.round(done/selectedIds.length*100));
return{label:snap?snap.snap_name:id, from:snap?.period_from||'', to:snap?.period_to||'', data, stock, pl:calcPL(data,stock)};
}));
periodsData=results;
renderTable();
document.getElementById('btnExport').disabled=false;
document.getElementById('btnPrint').disabled=false;
setStatus(`Comparing ${results.length} snapshot${results.length>1?'s':''}`, 'ok');
showToast('Comparison ready','success');
}catch(e){
setStatus('Error: '+e.message,'err');
showToast('Error: '+e.message,'error');
}finally{
btn.disabled=false; btn.innerHTML='Compare';
}
}
window.runComparison=runComparison;
document.getElementById('chkPct').addEventListener('change',()=>{if(periodsData.length) renderTable();});
// ── P&L calculations ──────────────────────────────────────────────────────────
function finalVal(r,sk){
const p=r.provision!=null?Number(r.provision):0;
return sk==='Revenue'?r.period_credit-r.period_debit+p:r.period_debit-r.period_credit+p;
}
function calcPL(data,stock){
const tot={Revenue:0,Purchase:0,Employee:0,Factory:0,Admin:0,SND:0,Finance:0,OtherIncome:0,Other:0};
data.forEach(r=>{const s=r.sheet||'Other';if(tot[s]!==undefined)tot[s]+=finalVal(r,s);});
const rev=tot.Revenue, purchT=tot.Purchase;
const matCost=stock.open+purchT-stock.close;
const convChange=(stock.detail.open.conv||0)-(stock.detail.close.conv||0);
const totalCons=matCost+convChange;
const grossP=rev-totalCons;
const empT=tot.Employee, factT=tot.Factory, adminT=tot.Admin, sndT=tot.SND, finT=tot.Finance;
const oiT=-(tot.OtherIncome||0);
const totalExp=empT+factT+adminT+sndT+finT;
const netOps=grossP-totalExp;
const netTax=netOps+oiT;
return{tot,rev,purchT,matCost,convChange,totalCons,grossP,empT,factT,adminT,sndT,finT,oiT,totalExp,netOps,netTax,stock};
}
function getHeadTotals(data,sheetKey){
const headMap={},headOrder=[];
data.filter(r=>r.sheet===sheetKey).forEach(r=>{
const h=(r.head||'').trim()||r.acct_name;
if(!headMap[h]){headMap[h]={total:0,minOrd:r.sort_order};headOrder.push(h);}
headMap[h].total+=finalVal(r,sheetKey);
headMap[h].minOrd=Math.min(headMap[h].minOrd,r.sort_order);
});
headOrder.sort((a,b)=>headMap[a].minOrd-headMap[b].minOrd);
return headOrder.map(h=>({head:h,total:headMap[h].total}));
}
function getHeadVal(data,sheetKey,head){
return data.filter(r=>r.sheet===sheetKey&&((r.head||'').trim()||r.acct_name)===head)
.reduce((s,r)=>s+finalVal(r,sheetKey),0);
}
function getAcctVal(data,code,sheetKey){
const r=data.find(x=>x.acct_code===code&&x.sheet===sheetKey);
return r?finalVal(r,sheetKey):0;
}
function mergedEmpRows(){
const seen=new Set(),result=[];
periodsData.forEach(p=>{
const rows=p.data.filter(r=>r.sheet==='Employee');
const headMap={},headOrder=[];
rows.forEach(r=>{
const h=(r.head||'').trim()||r.acct_name;
if(!headMap[h]){headMap[h]={accts:[],minOrd:r.sort_order};headOrder.push(h);}
if(h==='Direct Expenses') headMap[h].accts.push({code:r.acct_code,name:r.acct_name,ord:r.sort_order});
headMap[h].minOrd=Math.min(headMap[h].minOrd,r.sort_order);
});
headOrder.sort((a,b)=>headMap[a].minOrd-headMap[b].minOrd);
headOrder.forEach(h=>{
if(h==='Direct Expenses'){
headMap[h].accts.sort((a,b)=>a.ord-b.ord).forEach(a=>{
if(!seen.has('a:'+a.code)){seen.add('a:'+a.code);result.push({type:'acct',label:a.name,key:a.code});}
});
}else{
if(!seen.has('h:'+h)){seen.add('h:'+h);result.push({type:'head',label:h,key:h});}
}
});
});
return result;
}
function mergedHeads(sheetKey){
const seen=new Set(),result=[];
periodsData.forEach(p=>{
getHeadTotals(p.data,sheetKey).forEach(({head})=>{
if(!seen.has(head)){seen.add(head);result.push(head);}
});
});
return result;
}
// ── Render table ──────────────────────────────────────────────────────────────
function renderTable(){
const showPct=document.getElementById('chkPct').checked;
const nc=periodsData.length;
const fmt=v=>Math.abs(v||0).toLocaleString('en-IN',{minimumFractionDigits:2,maximumFractionDigits:2});
const pctOf=(v,b)=>b!==0?(Math.abs(v)/Math.abs(b)*100).toFixed(1)+'%':'—';
const sign=v=>v<0?'-':'';
const showNet=nc>=2;
// Build header
let th1='<th class="part" rowspan="2">Particulars</th>';
let th2='';
periodsData.forEach(p=>{
const span=showPct?'2':'1';
const fromFmt=fmtDateLong(p.from), toFmt=fmtDateLong(p.to);
th1+=`<th class="bdr" colspan="${span}" style="text-align:center;padding:10px 14px">
<div style="font-size:12px;font-weight:700;letter-spacing:.2px">${esc(p.label)}</div>
<div style="font-size:9px;font-weight:400;opacity:.7;margin-top:2px">${fromFmt} &rarr; ${toFmt}</div>
</th>`;
th2+=`<th class="bdr" style="min-width:130px">Total (&#8377;)</th>${showPct?'<th style="min-width:60px">% Rev</th>':''}`;
});
if(showNet){
th1+=`<th class="bdr" colspan="2" style="text-align:center;padding:10px 14px">
<div style="font-size:12px;font-weight:700;letter-spacing:.2px">Net Change</div>
<div style="font-size:9px;font-weight:400;opacity:.7;margin-top:2px">${esc(periodsData[nc-1].label)} &minus; ${esc(periodsData[0].label)}</div>
</th>`;
th2+=`<th class="bdr" style="min-width:120px">Amount (&#8377;)</th><th style="min-width:60px">% Chg</th>`;
}
const netCols=showNet?2:0;
const revNetChange=showNet?((periodsData[nc-1].pl.rev||0)-(periodsData[0].pl.rev||0)):0;
function netCell(vals,color){
const base=vals[0]||0, last=vals[nc-1]||0;
const v=last-base;
const clr=color?color(v):(v<0?'#dc2626':'#047e7e');
const pctStr=pctOf(v,revNetChange);
return `<td class="num bdr" style="color:${clr}">${sign(v)}${fmt(v)}</td><td class="pct" style="color:${clr}">${pctStr}</td>`;
}
function numCells(vals,noPct){
let html=periodsData.map((p,i)=>{
const v=vals[i]||0;
const neg=v<0?' neg':'';
const pct=(showPct&&!noPct)?`<td class="pct">${pctOf(v,p.pl.rev)}</td>`:(showPct?'<td class="pct"></td>':'');
return `<td class="num bdr${neg}">${sign(v)}${fmt(v)}</td>${pct}`;
}).join('');
if(showNet)html+=netCell(vals);
return html;
}
function convCells(vals){
let html=periodsData.map((p,i)=>{
const v=vals[i]||0;
const pct=showPct?`<td class="pct">${pctOf(v,p.pl.rev)}</td>`:'';
return `<td class="num bdr" style="color:${v<0?'#dc2626':'#047e7e'}">${sign(v)}${fmt(v)}</td>${pct}`;
}).join('');
if(showNet)html+=netCell(vals);
return html;
}
const blank=()=>`<tr class="blank"><td class="part"></td><td colspan="${nc*(showPct?2:1)+netCols}"></td></tr>`;
const secHdr=lbl=>`<tr class="sec-hdr"><td class="part">${esc(lbl)}</td><td colspan="${nc*(showPct?2:1)+netCols}"></td></tr>`;
const subRow=(lbl,vals,cls='sub',noPct)=>`<tr class="${cls}"><td class="part">${esc(lbl)}</td>${numCells(vals,noPct)}</tr>`;
const totRow=(lbl,vals,cls='tot')=>`<tr class="${cls}"><td class="part">${esc(lbl)}</td>${numCells(vals)}</tr>`;
function buildSection(sheetKey,secLabel,totLabel){
const heads=mergedHeads(sheetKey);
if(!heads.length) return '';
let html=secHdr(secLabel);
heads.forEach(h=>{html+=subRow(h,periodsData.map(p=>getHeadVal(p.data,sheetKey,h)));});
html+=totRow(totLabel,periodsData.map(p=>p.pl.tot[sheetKey]||0));
html+=blank();
return html;
}
function buildEmpSection(){
const rows=mergedEmpRows();
if(!rows.length) return '';
let html=secHdr('Employee Benefit Expenses');
rows.forEach(({type,label,key})=>{
const vals=periodsData.map(p=>type==='acct'?getAcctVal(p.data,key,'Employee'):getHeadVal(p.data,'Employee',key));
html+=subRow(label,vals);
});
html+=totRow('Total Employee Benefit Expenses',periodsData.map(p=>p.pl.empT));
html+=blank();
return html;
}
const grandRow=`<tr class="grand">
<td class="part">Net Profit before Tax</td>
${periodsData.map(p=>{
const v=p.pl.netTax;
const pct=showPct?`<td class="pct" style="color:rgba(255,255,255,.6)">${pctOf(v,p.pl.rev)}</td>`:'';
return `<td class="num bdr" style="color:${v>=0?'#6ee7b7':'#fca5a5'}">${sign(v)}${fmt(v)}</td>${pct}`;
}).join('')}
${showNet?netCell(periodsData.map(p=>p.pl.netTax),v=>v>=0?'#6ee7b7':'#fca5a5'):''}
</tr>`;
const tbody=`
${totRow('Revenue from Operations',periodsData.map(p=>p.pl.rev),'rev')}
${blank()}
${subRow('Opening Stock',periodsData.map(p=>p.stock.open),'sub',true)}
${subRow('Purchases',periodsData.map(p=>p.pl.purchT),'sub',true)}
${subRow('Closing Stock',periodsData.map(p=>p.stock.close),'sub',true)}
${totRow('Consumption of Material',periodsData.map(p=>p.pl.matCost))}
<tr class="sub"><td class="part" style="padding-left:24px;color:#6b7280">Change in Conversion Cost</td>${convCells(periodsData.map(p=>p.pl.convChange))}</tr>
${totRow('Total Consumption',periodsData.map(p=>p.pl.totalCons))}
${blank()}
${totRow('Gross Profit',periodsData.map(p=>p.pl.grossP),'gp')}
${blank()}
${buildEmpSection()}
${buildSection('Factory','Factory / Utility Expenses','Total Factory Expenses')}
${buildSection('Admin','Administration Expenses','Total Administration Expenses')}
${buildSection('SND','Selling &amp; Distribution Expenses','Total Selling &amp; Distribution Expenses')}
${buildSection('Finance','Finance Cost','Total Finance Cost')}
${totRow('Total Expenses',periodsData.map(p=>p.pl.totalExp))}
${blank()}
${totRow('Net Profit from Operations',periodsData.map(p=>p.pl.netOps))}
${periodsData.some(p=>p.pl.oiT!==0)?subRow('Other Income',periodsData.map(p=>p.pl.oiT)):''}
${grandRow}`;
document.getElementById('cmpPane').innerHTML=`<div class="cmp-wrap">
<table class="cmp">
<thead>
<tr>${th1}</tr>
<tr class="sub-hdr">${th2}</tr>
</thead>
<tbody>${tbody}</tbody>
</table>
<div style="padding:6px 12px;font-size:10px;color:#9ca3af">* All values in &#8377;. Override/provision values included from each snapshot.</div>
</div>`;
// Fix second header row's sticky top to exactly match first row height
requestAnimationFrame(()=>{
const row1=document.querySelector('.cmp thead tr:first-child');
if(row1){
const h=row1.getBoundingClientRect().height;
document.querySelectorAll('.cmp thead tr.sub-hdr th').forEach(th=>th.style.top=h+'px');
}
});
}
// ── Monthly comparison sheet (LB / Provision / Total / Net + cumulative) ────────
function acctLPTN(r,sheetKey){
const lb=sheetKey==='Revenue'?(r.period_credit-r.period_debit):(r.period_debit-r.period_credit);
const prov=r.provision!=null?Number(r.provision):0;
const total=lb+prov;
return{lb,prov,total,net:-total};
}
function getAcctLPTN(data,code,sheetKey){
const r=data.find(x=>x.acct_code===code&&x.sheet===sheetKey);
return r?acctLPTN(r,sheetKey):{lb:0,prov:0,total:0,net:0};
}
function mergedAccts(sheetKey){
const seen=new Set(),result=[];
periodsData.forEach(p=>{
p.data.filter(r=>r.sheet===sheetKey).sort((a,b)=>(a.sort_order||999)-(b.sort_order||999)).forEach(r=>{
if(!seen.has(r.acct_code)){seen.add(r.acct_code);result.push({code:r.acct_code,name:r.acct_name,head:(r.head||'').trim()});}
});
});
return result;
}
function buildMonthlySheet(sheetKey){
const accts=mergedAccts(sheetKey);
if(!accts.length) return null;
const FC=2; // fixed cols: Code, Account Name
const blocks=[];
periodsData.forEach((p,i)=>{
if(i===0) blocks.push({title:p.label,type:'cum',idx:i});
else{ blocks.push({title:p.label,type:'delta',idx:i}); blocks.push({title:'Upto '+p.label,type:'cum',idx:i}); }
});
const hdr1=['Code','Account Name'], hdr2=['',''];
const merges=[{s:{r:0,c:0},e:{r:1,c:0}},{s:{r:0,c:1},e:{r:1,c:1}}];
const blockStarts=[];
let col=FC;
blocks.forEach(b=>{
blockStarts.push(col);
hdr1.push(b.title,'','','');
hdr2.push('LB','Provision','Total','Net');
merges.push({s:{r:0,c:col},e:{r:0,c:col+3}});
col+=4;
});
const blockVals=(b,code)=>{
const cur=getAcctLPTN(periodsData[b.idx].data,code,sheetKey);
if(b.type==='cum') return [cur.lb,cur.prov,cur.total,cur.net];
const prev=getAcctLPTN(periodsData[b.idx-1].data,code,sheetKey);
const lb=cur.lb-prev.lb, prov=cur.prov-prev.prov, total=cur.total-prev.total;
return [lb,prov,total,-total];
};
// Group accounts by head
const headOrder=[],headMap={};
accts.forEach(a=>{
const h=a.head||a.name;
if(!headMap[h]){headMap[h]=[];headOrder.push(h);}
headMap[h].push(a);
});
const rows=[hdr1,hdr2];
const rowTypes=[]; // 'hdr','data','sub','grand' per row (starting from row index 2)
const subRanges=[]; // {headRow, dataStart, dataEnd, subRow} for SUM formulas
let allDataRows=[];
headOrder.forEach(h=>{
const grpAccts=headMap[h];
// Head section header row
const hdrIdx=rows.length;
rows.push([h]);rowTypes.push('hdr');
const dataStart=rows.length;
grpAccts.forEach(a=>{
const row=[a.code,a.name];
blocks.forEach(b=>row.push(...blockVals(b,a.code)));
rows.push(row);rowTypes.push('data');
allDataRows.push(rows.length-1);
});
const dataEnd=rows.length-1;
// Subtotal row for this head
const subRow=['','Total '+h];
blocks.forEach(b=>{
let lb=0,prov=0,total=0;
grpAccts.forEach(a=>{const v=blockVals(b,a.code);lb+=v[0];prov+=v[1];total+=v[2];});
subRow.push(lb,prov,total,-total);
});
rows.push(subRow);rowTypes.push('sub');
subRanges.push({head:h,dataStart,dataEnd,subRow:rows.length-1});
rows.push([]);rowTypes.push('blank'); // blank separator
});
// Grand total row
const grandRow=['','Grand Total'];
blocks.forEach(b=>{
let lb=0,prov=0,total=0;
accts.forEach(a=>{const v=blockVals(b,a.code);lb+=v[0];prov+=v[1];total+=v[2];});
grandRow.push(lb,prov,total,-total);
});
rows.push(grandRow);rowTypes.push('grand');
const totalCols=FC+blocks.length*4;
// Build lookup maps for cross-sheet references
const headSubRows={};subRanges.forEach(s=>{headSubRows[s.head]=s.subRow;});
const acctRows={};
let ai=0;
headOrder.forEach(h=>{headMap[h].forEach(a=>{acctRows[a.code]=allDataRows[ai++];});});
return{rows,merges,cols:totalCols,blockStarts,dataStartRow:2,acctCount:accts.length,rowTypes,subRanges,allDataRows,grandRow:rows.length-1,headSubRows,acctRows};
}
function applyMonthlyFormulas(ws,info){
const ec=XLSX.utils.encode_cell;
function setF(addr,f){const c=ws[addr];const v=c?.v||0;const s=c?.s;ws[addr]={t:'n',v,f};if(s)ws[addr].s=s;}
info.blockStarts.forEach(c=>{
const lbCol=c, prCol=c+1, totCol=c+2, netCol=c+3;
// Data rows: Total=LB+Provision, Net=-Total
(info.allDataRows||[]).forEach(r=>{
const lbA=ec({r,c:lbCol}), prA=ec({r,c:prCol}), totA=ec({r,c:totCol});
setF(totA,`${lbA}+${prA}`);
setF(ec({r,c:netCol}),`-${totA}`);
});
// Subtotal rows: SUM of their data range
(info.subRanges||[]).forEach(({dataStart,dataEnd,subRow})=>{
[lbCol,prCol,totCol,netCol].forEach(cc=>{
setF(ec({r:subRow,c:cc}),`SUM(${ec({r:dataStart,c:cc})}:${ec({r:dataEnd,c:cc})})`);
});
});
// Grand total: SUM of subtotal rows
if(info.grandRow!=null&&(info.subRanges||[]).length){
[lbCol,prCol,totCol,netCol].forEach(cc=>{
const parts=info.subRanges.map(s=>ec({r:s.subRow,c:cc}));
setF(ec({r:info.grandRow,c:cc}),parts.join('+'));
});
}
});
}
// ── Excel styles ──────────────────────────────────────────────────────────────
const XB={top:{style:'thin',color:{rgb:'B0BEC5'}},bottom:{style:'thin',color:{rgb:'B0BEC5'}},left:{style:'thin',color:{rgb:'B0BEC5'}},right:{style:'thin',color:{rgb:'B0BEC5'}}};
const XBD={top:{style:'thin',color:{rgb:'78909C'}},bottom:{style:'thin',color:{rgb:'78909C'}},left:{style:'thin',color:{rgb:'78909C'}},right:{style:'thin',color:{rgb:'78909C'}}};
const XS={
hdr:{font:{bold:true,sz:11,color:{rgb:'FFFFFF'}},fill:{fgColor:{rgb:'1A5276'}},alignment:{horizontal:'center',vertical:'center',wrapText:true},border:XBD},
subHdr:{font:{bold:true,sz:10,color:{rgb:'1A5276'}},fill:{fgColor:{rgb:'D6EAF8'}},alignment:{horizontal:'center'},border:XBD},
secHdr:{font:{bold:true,sz:10,color:{rgb:'374151'}},fill:{fgColor:{rgb:'F2F3F4'}},border:XB},
secHdrNum:{font:{bold:true,sz:10,color:{rgb:'374151'}},fill:{fgColor:{rgb:'F2F3F4'}},border:XB,alignment:{horizontal:'right'}},
rev:{font:{bold:true,sz:10,color:{rgb:'FFFFFF'}},fill:{fgColor:{rgb:'0E6655'}},border:XBD},
revNum:{font:{bold:true,sz:10,color:{rgb:'FFFFFF'}},fill:{fgColor:{rgb:'0E6655'}},border:XBD,numFmt:'#,##,##0.00',alignment:{horizontal:'right'}},
revPct:{font:{bold:true,sz:10,color:{rgb:'FFFFFF'}},fill:{fgColor:{rgb:'0E6655'}},border:XBD,alignment:{horizontal:'right'}},
tot:{font:{bold:true,sz:10,color:{rgb:'1A5276'}},fill:{fgColor:{rgb:'EBF5FB'}},border:XBD},
totNum:{font:{bold:true,sz:10,color:{rgb:'1A5276'}},fill:{fgColor:{rgb:'EBF5FB'}},border:XBD,numFmt:'#,##,##0.00',alignment:{horizontal:'right'}},
totPct:{font:{bold:true,sz:10,color:{rgb:'1A5276'}},fill:{fgColor:{rgb:'EBF5FB'}},border:XBD,alignment:{horizontal:'right'}},
gp:{font:{bold:true,sz:10,color:{rgb:'FFFFFF'}},fill:{fgColor:{rgb:'1B7A43'}},border:XBD},
gpNum:{font:{bold:true,sz:10,color:{rgb:'FFFFFF'}},fill:{fgColor:{rgb:'1B7A43'}},border:XBD,numFmt:'#,##,##0.00',alignment:{horizontal:'right'}},
gpPct:{font:{bold:true,sz:10,color:{rgb:'FFFFFF'}},fill:{fgColor:{rgb:'1B7A43'}},border:XBD,alignment:{horizontal:'right'}},
grand:{font:{bold:true,sz:11,color:{rgb:'FFFFFF'}},fill:{fgColor:{rgb:'1A1A2E'}},border:XBD},
grandNum:{font:{bold:true,sz:11,color:{rgb:'FFFFFF'}},fill:{fgColor:{rgb:'1A1A2E'}},border:XBD,numFmt:'#,##,##0.00',alignment:{horizontal:'right'}},
grandPct:{font:{bold:true,sz:11,color:{rgb:'FFFFFF'}},fill:{fgColor:{rgb:'1A1A2E'}},border:XBD,alignment:{horizontal:'right'}},
sub:{font:{sz:10},border:XB},
subNum:{font:{sz:10},border:XB,numFmt:'#,##,##0.00',alignment:{horizontal:'right'}},
subPct:{font:{sz:10},border:XB,alignment:{horizontal:'right'}},
};
function xlsStyleRow(ws,r,maxC,lblStyle,numStyle,pctStyle){
for(let c=0;c<=maxC;c++){
const addr=XLSX.utils.encode_cell({r,c});
if(!ws[addr]) ws[addr]={t:'s',v:''};
if(c===0) ws[addr].s=lblStyle;
else if(c%2===1) ws[addr].s=numStyle; // val col
else ws[addr].s=pctStyle; // pct col
}
}
// ── Excel export ───────────────────────────────────────────────────────────────
document.getElementById('btnExport').addEventListener('click',()=>{
if(!periodsData.length){showToast('Run comparison first','error');return;}
const labels=periodsData.map(p=>p.label);
const nc=periodsData.length, showNet=nc>=2;
const ec=XLSX.utils.encode_cell;
const pR=(v,b)=>b?+(Math.abs(v)/Math.abs(b)*100).toFixed(1):0;
const revNC=showNet?((periodsData[nc-1].pl.rev||0)-(periodsData[0].pl.rev||0)):0;
const wb=XLSX.utils.book_new();
// ── Build monthly detail sheets FIRST (need metadata for cross-sheet refs) ──
const mm={}; // monthlyMeta: key→{label,blockStarts,headSubRows,acctRows,grandRow}
const SDEFS=[['Revenue','Revenue'],['Purchase','Purchases'],['Employee','Employee'],['Factory','Factory Expenses'],
['Admin','Admin'],['SND','S&D'],['Finance','Finance Cost'],['OtherIncome','Other Income'],['Other','Other']];
const detailSheets=[];
SDEFS.forEach(([key,label])=>{
const built=buildMonthlySheet(key);
if(!built)return;
mm[key]={label,blockStarts:built.blockStarts,headSubRows:built.headSubRows,acctRows:built.acctRows,grandRow:built.grandRow};
detailSheets.push({key,label,built});
});
// Cumulative Total column on a detail sheet for period i
function cumTC(meta,i){const bi=i===0?0:2*i;return meta.blockStarts[bi]+2;}
// Cross-sheet ref formula
function sRef(label,r,c){return`'${label}'!${ec({r,c})}`;}
// ── P&L Comparison header (back to simple: Total + %Rev per period) ──
const hdr=['Particulars'];labels.forEach(l=>hdr.push(l,'% Revenue'));
if(showNet)hdr.push(`Net Change (${labels[nc-1]} - ${labels[0]})`,'% Chg');
const rows=[hdr];
const ri={};
function addRow(lbl,vals,tag,noPct){
const r=[lbl];
periodsData.forEach((p,i)=>{r.push(vals[i]||0,noPct?'':pR(vals[i]||0,p.pl.rev)+'%');});
if(showNet){const d=(vals[nc-1]||0)-(vals[0]||0);r.push(d,pR(d,revNC)+'%');}
if(tag)ri[tag]=rows.length;
rows.push(r);
}
function addBlank(){rows.push([]);}
function addSec(lbl){rows.push([lbl]);}
// Track which rows should get cross-sheet formulas
const xRefs=[]; // {row, sheetKey, type:'grand'|'head'|'acct', key}
addRow('Revenue from Operations',periodsData.map(p=>p.pl.rev),'rev');
xRefs.push({tag:'rev',sk:'Revenue',type:'grand'});
addBlank();
addRow('Opening Stock',periodsData.map(p=>p.stock.open),'openStk',true);
addRow('Purchases',periodsData.map(p=>p.pl.purchT),'purch',true);
xRefs.push({tag:'purch',sk:'Purchase',type:'grand'});
addRow('Closing Stock',periodsData.map(p=>p.stock.close),'closeStk',true);
addRow('Consumption of Material',periodsData.map(p=>p.pl.matCost),'matCost');
addRow('Change in Conversion Cost',periodsData.map(p=>p.pl.convChange),'convChg');
addRow('Total Consumption',periodsData.map(p=>p.pl.totalCons),'totalCons');
addBlank();
addRow('Gross Profit',periodsData.map(p=>p.pl.grossP),'grossP');
addBlank();
addSec('Employee Benefit Expenses');
const empS=rows.length;
mergedEmpRows().forEach(({type,label,key})=>{
const vals=periodsData.map(p=>type==='acct'?getAcctVal(p.data,key,'Employee'):getHeadVal(p.data,'Employee',key));
addRow(' '+label,vals);
if(type==='acct') xRefs.push({row:rows.length-1,sk:'Employee',type:'acct',key});
else xRefs.push({row:rows.length-1,sk:'Employee',type:'head',key});
});
const empE=rows.length-1;
addRow('Total Employee Benefit Expenses',periodsData.map(p=>p.pl.empT),'empT');
addBlank();
const secInfo=[];
[['Factory','Factory / Utility Expenses','factT'],['Admin','Administration Expenses','adminT'],['SND','Selling & Distribution Expenses','sndT'],['Finance','Finance Cost','finT']].forEach(([k,l,tag])=>{
addSec(l);
const ss=rows.length;
mergedHeads(k).forEach(h=>{
addRow(' '+h,periodsData.map(p=>getHeadVal(p.data,k,h)));
xRefs.push({row:rows.length-1,sk:k,type:'head',key:h});
});
const se=rows.length-1;
addRow('Total '+l,periodsData.map(p=>p.pl.tot[k]||0),tag);
secInfo.push({tag,ss,se});
addBlank();
});
addRow('Total Expenses',periodsData.map(p=>p.pl.totalExp),'totalExp');
addBlank();
addRow('Net Profit from Operations',periodsData.map(p=>p.pl.netOps),'netOps');
const hasOI=periodsData.some(p=>p.pl.oiT!==0);
if(hasOI){addRow('Other Income',periodsData.map(p=>p.pl.oiT),'oiT');xRefs.push({tag:'oiT',sk:'OtherIncome',type:'grand'});}
addRow('Net Profit before Tax',periodsData.map(p=>p.pl.netTax),'netTax');
const ws=XLSX.utils.aoa_to_sheet(rows);
const valC=i=>1+i*2, netAmtC=1+nc*2;
function setF(r,c,f){const a=ec({r,c});if(!ws[a])return;const s=ws[a].s;ws[a]={t:'n',v:ws[a].v||0,f};if(s)ws[a].s=s;}
function netF(r){if(showNet)setF(r,netAmtC,`${ec({r,c:valC(nc-1)})}-${ec({r,c:valC(0)})}`);}
// ── Cross-sheet reference formulas ──
xRefs.forEach(x=>{
const r=x.row!=null?x.row:ri[x.tag];
const meta=mm[x.sk];if(!meta||r==null)return;
for(let i=0;i<nc;i++){
let refRow;
if(x.type==='grand') refRow=meta.grandRow;
else if(x.type==='head') refRow=meta.headSubRows[x.key];
else if(x.type==='acct') refRow=meta.acctRows[x.key];
if(refRow==null)continue;
setF(r,valC(i),sRef(meta.label,refRow,cumTC(meta,i)));
}
netF(r);
});
// ── Calculated row formulas ──
function calcF(tag,fn){const r=ri[tag];if(r==null)return;for(let i=0;i<nc;i++)setF(r,valC(i),fn(i));netF(r);}
function cRef(tag,i){return ec({r:ri[tag],c:valC(i)});}
calcF('matCost',i=>`${cRef('openStk',i)}+${cRef('purch',i)}-${cRef('closeStk',i)}`);
calcF('totalCons',i=>`${cRef('matCost',i)}+${cRef('convChg',i)}`);
calcF('grossP',i=>`${cRef('rev',i)}-${cRef('totalCons',i)}`);
if(empS<=empE)calcF('empT',i=>`SUM(${ec({r:empS,c:valC(i)})}:${ec({r:empE,c:valC(i)})})`);
secInfo.forEach(({tag,ss,se})=>{if(ss<=se)calcF(tag,i=>`SUM(${ec({r:ss,c:valC(i)})}:${ec({r:se,c:valC(i)})})`);});
calcF('totalExp',i=>{const p=['empT','factT','adminT','sndT','finT'].filter(t=>ri[t]!=null).map(t=>cRef(t,i));return p.join('+');});
calcF('netOps',i=>`${cRef('grossP',i)}-${cRef('totalExp',i)}`);
calcF('netTax',i=>hasOI&&ri.oiT!=null?`${cRef('netOps',i)}+${cRef('oiT',i)}`:`${cRef('netOps',i)}`);
// ── Styles ──
const maxCol=hdr.length-1;
const tagSt={rev:'rev',grossP:'gp',netTax:'grand'};
const totSet=new Set(['matCost','totalCons','empT','factT','adminT','sndT','finT','totalExp','netOps']);
if(hasOI)totSet.add('oiT');
xlsStyleRow(ws,0,maxCol,XS.hdr,XS.hdr,XS.hdr);
for(let r=1;r<rows.length;r++){
const lbl=rows[r][0]||'';
const isEmpty=rows[r].length<=1&&!lbl;
let tag=null;for(const[t,rv] of Object.entries(ri)){if(rv===r){tag=t;break;}}
if(isEmpty){for(let c=0;c<=maxCol;c++){const a=ec({r,c});if(!ws[a])ws[a]={t:'s',v:''};ws[a].s={border:XB};}}
else if(tagSt[tag]){const b=tagSt[tag];xlsStyleRow(ws,r,maxCol,XS[b],XS[b+'Num'],XS[b+'Pct']);}
else if(totSet.has(tag)){xlsStyleRow(ws,r,maxCol,XS.tot,XS.totNum,XS.totPct);}
else if(lbl&&!lbl.startsWith(' ')&&rows[r].length<=1){for(let c=0;c<=maxCol;c++){const a=ec({r,c});if(!ws[a])ws[a]={t:'s',v:''};ws[a].s=c===0?XS.secHdr:XS.secHdrNum;}}
else{xlsStyleRow(ws,r,maxCol,XS.sub,XS.subNum,XS.subPct);}
}
ws['!rows']=[{hpt:32}];
XLSX.utils.book_append_sheet(wb,ws,'P&L Comparison');
const w=[{wch:36}];labels.forEach(()=>w.push({wch:18},{wch:10}));if(showNet)w.push({wch:18},{wch:10});
ws['!cols']=w;
// ── Monthly comparison sheets (styled) ───────────────────────────────────
detailSheets.forEach(({key,label,built})=>{
const ws2=XLSX.utils.aoa_to_sheet(built.rows);
ws2['!merges']=built.merges;
ws2['!cols']=[{wch:14},{wch:30}].concat(Array(built.cols-2).fill({wch:14}));
applyMonthlyFormulas(ws2,built);
// Style monthly sheet
const mc=built.cols-1;
const txtS={font:{sz:10},border:XB};
const numS={font:{sz:10},border:XB,numFmt:'#,##,##0.00',alignment:{horizontal:'right'}};
const subTxtS={font:{bold:true,sz:10,color:{rgb:'1A5276'}},fill:{fgColor:{rgb:'EBF5FB'}},border:XBD};
const subNumS={font:{bold:true,sz:10,color:{rgb:'1A5276'}},fill:{fgColor:{rgb:'EBF5FB'}},border:XBD,numFmt:'#,##,##0.00',alignment:{horizontal:'right'}};
const grandTxtS={font:{bold:true,sz:11,color:{rgb:'FFFFFF'}},fill:{fgColor:{rgb:'1A1A2E'}},border:XBD};
const grandNumS={font:{bold:true,sz:11,color:{rgb:'FFFFFF'}},fill:{fgColor:{rgb:'1A1A2E'}},border:XBD,numFmt:'#,##,##0.00',alignment:{horizontal:'right'}};
// Header row 0 (merged titles)
for(let c=0;c<=mc;c++){const a=XLSX.utils.encode_cell({r:0,c});if(!ws2[a])ws2[a]={t:'s',v:''};ws2[a].s=XS.hdr;}
// Header row 1 (LB/Provision/Total/Net)
for(let c=0;c<=mc;c++){const a=XLSX.utils.encode_cell({r:1,c});if(!ws2[a])ws2[a]={t:'s',v:''};ws2[a].s=XS.subHdr;}
// Data/section rows
const rt=built.rowTypes||[];
for(let r=2;r<built.rows.length;r++){
const type=rt[r-2]||'data';
for(let c=0;c<=mc;c++){
const a=XLSX.utils.encode_cell({r,c});
if(!ws2[a])ws2[a]={t:'s',v:''};
if(type==='hdr') ws2[a].s=c===0?XS.secHdr:XS.secHdrNum;
else if(type==='sub') ws2[a].s=c<2?subTxtS:subNumS;
else if(type==='grand') ws2[a].s=c<2?grandTxtS:grandNumS;
else if(type==='blank') ws2[a].s={border:XB};
else ws2[a].s=c<2?txtS:numS;
}
}
ws2['!rows']=[{hpt:28},{hpt:24}];
XLSX.utils.book_append_sheet(wb,ws2,label);
});
XLSX.writeFile(wb,'PL_Comparison.xlsx');
showToast('Excel downloaded','success');
});
// ── Helpers ───────────────────────────────────────────────────────────────────
function setStatus(m,t=''){const d=document.getElementById('statusDot'),x=document.getElementById('statusMsg');if(d)d.className='status-dot'+(t?' '+t:'');if(x)x.textContent=m;}
function setProgress(p){const e=document.getElementById('progressFill');if(e)e.style.width=p+'%';}
function printSection(id,title){
const el=document.getElementById(id);
if(!el||!el.innerHTML.trim()){alert('No data to print.');return;}
const w=window.open('','_blank','width=1200,height=800');
w.document.write(`<!DOCTYPE html><html><head><meta charset="UTF-8"><title>${title||'Print'}</title>
<style>
@page{size:A4 landscape;margin:5mm}
*{box-sizing:border-box;margin:0;padding:0}
body{font-family:Arial,Helvetica,sans-serif;color:#1a1a2e;padding:1px;zoom:0.82}
h2{font-size:9px;font-weight:700;margin-bottom:3px;color:#004d40;padding-bottom:2px;border-bottom:1px solid #047e7e}
table{border-collapse:collapse;width:100%}
th,td{font-size:7px!important;padding:1.5px 4px!important;white-space:nowrap;line-height:1.25!important}
th{background:#047e7e!important;color:#fff!important;font-weight:700;text-align:right;-webkit-print-color-adjust:exact;print-color-adjust:exact}
th:first-child,td:first-child,.part{text-align:left!important}
td{border-bottom:1px solid #f0f2f4;vertical-align:middle}
td:not(:first-child){text-align:right;font-variant-numeric:tabular-nums}
tr.rev td,tr.tot td{background:#e0f4f4!important;font-weight:700;border-top:1px solid #80cbc4;-webkit-print-color-adjust:exact;print-color-adjust:exact}
tr.gp td{background:#bbf7d0!important;font-weight:800;border-top:2px solid #4ade80;-webkit-print-color-adjust:exact;print-color-adjust:exact}
tr.grand td{background:#004d40!important;color:#fff!important;font-weight:800;-webkit-print-color-adjust:exact;print-color-adjust:exact}
tr.sec-hdr td{background:#f0f4f8!important;font-weight:700;border-top:1px solid #d1dae3;-webkit-print-color-adjust:exact;print-color-adjust:exact}
tr.sub td:first-child{padding-left:12px!important;color:#047e7e}
tr.blank{display:none!important}
div[style*="color:#9ca3af"]{display:none!important}
.neg{color:#dc2626!important}
.cmp-wrap{overflow:visible!important;max-height:none!important}
.part{position:static!important}
thead th:first-child{text-align:left}
</style><link rel="stylesheet" href="/theme.css?v=3"/>
</head><body>
<h2>${title||''}</h2>
${el.innerHTML}
<script>window.onload=()=>{window.print();}<\/script>
</body></html>`);
w.document.close();
}
window.printSection=printSection;
function esc(s){return String(s||'').replace(/&/g,'&amp;').replace(/</g,'&lt;').replace(/>/g,'&gt;').replace(/"/g,'&quot;');}
function fmtDateLong(d){if(!d)return '';const[y,m,day]=d.split('-');const mn=['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];return `${parseInt(day)} ${mn[parseInt(m)-1]} ${y}`;}
function showToast(m,t='info'){const e=document.getElementById('toast');e.textContent=m;e.className='toast '+t+' show';setTimeout(()=>e.classList.remove('show'),3500);}
loadSnapshotList();
})();
</script>
</body>
</html>