654 lines
31 KiB
HTML
654 lines
31 KiB
HTML
<!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>General Ledger | 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;color:#1a1a2e}
|
|
.hdr h1 span{color:#7c3aed}
|
|
.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:#7c3aed;color:#fff}.btn-primary:hover{background:#6d28d9}
|
|
.btn-primary:disabled{background:#b0bec5;cursor:not-allowed;opacity:.7}
|
|
.btn-outline{background:#fff;color:#7c3aed;border:1.5px solid #7c3aed}.btn-outline:hover{background:#f5f3ff}
|
|
.card{background:#fff;border-radius:14px;box-shadow:0 1px 8px rgba(0,0,0,.08);overflow:hidden}
|
|
.filter-bar{display:flex;align-items:flex-end;gap:12px;flex-wrap:wrap;padding:16px 20px;border-bottom:1px solid #e8eaed;background:#fff}
|
|
.fg{display:flex;flex-direction:column;gap:5px}
|
|
.fg label{font-size:10px;font-weight:700;color:#6b7280;text-transform:uppercase;letter-spacing:.6px}
|
|
.fg input,.fg select{height:36px;border:1.5px solid #dde1e7;border-radius:8px;padding:0 12px;font-size:13px;font-family:inherit;color:#1a1a2e;background:#fff;outline:none;transition:border-color .15s}
|
|
.fg input:focus,.fg select:focus{border-color:#7c3aed;box-shadow:0 0 0 3px rgba(124,58,237,.08)}
|
|
.tbl-wrap{overflow-x:auto;overflow-y:auto;max-height:62vh}
|
|
.tbl-wrap::-webkit-scrollbar{height:7px;width:7px}
|
|
.tbl-wrap::-webkit-scrollbar-track{background:#f1f3f5}
|
|
.tbl-wrap::-webkit-scrollbar-thumb{background:#c1c7ce;border-radius:4px}
|
|
.tbl-wrap::-webkit-scrollbar-thumb:hover{background:#9ca3af}
|
|
table{border-collapse:collapse;min-width:100%}
|
|
thead th{background:#f5f3ff;color:#4b5563;font-weight:700;padding:10px 12px;text-align:left;white-space:nowrap;position:sticky;top:0;z-index:2;border-bottom:2px solid #ddd6fe;font-size:10px;text-transform:uppercase;letter-spacing:.5px}
|
|
tbody tr{border-bottom:1px solid #f0f2f4;transition:background .1s}
|
|
tbody tr:hover{background:#f5f3ff}
|
|
tbody td{padding:9px 12px;white-space:nowrap;color:#374151;font-size:12px}
|
|
tbody td.num{text-align:right;font-variant-numeric:tabular-nums}
|
|
.acct-header td{background:#ede9fe;font-weight:700;color:#4c1d95;font-size:12px;border-bottom:2px solid #c4b5fd;padding:10px 12px}
|
|
.acct-footer td{background:#f5f3ff;font-weight:700;color:#1a1a2e;font-size:12px;border-top:2px solid #ddd6fe;padding:10px 12px}
|
|
.status-bar{display:flex;align-items:center;gap:10px;padding:10px 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}}
|
|
.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;max-width:360px}
|
|
.toast.show{transform:translateY(0);opacity:1}
|
|
.toast.success{background:#10b981}.toast.error{background:#ef4444}.toast.info{background:#3b82f6}
|
|
.empty{text-align:center;padding:56px 20px;color:#9ca3af;font-size:13px}
|
|
|
|
/* Account picker button */
|
|
.acct-pick-btn{height:36px;padding:0 14px;border:1.5px solid #dde1e7;border-radius:8px;background:#fff;font-size:13px;font-family:inherit;color:#1a1a2e;cursor:pointer;display:inline-flex;align-items:center;gap:8px;transition:border-color .15s;min-width:180px}
|
|
.acct-pick-btn:hover{border-color:#7c3aed}
|
|
.acct-pick-btn .badge{background:#7c3aed;color:#fff;border-radius:10px;padding:1px 8px;font-size:10px;font-weight:700;margin-left:4px}
|
|
|
|
/* Modal overlay */
|
|
.modal-overlay{position:fixed;inset:0;background:rgba(0,0,0,.35);z-index:600;display:none;backdrop-filter:blur(2px)}
|
|
.modal-overlay.show{display:flex;align-items:center;justify-content:center}
|
|
.modal{background:#fff;border-radius:14px;width:560px;max-width:95vw;max-height:80vh;display:flex;flex-direction:column;box-shadow:0 8px 40px rgba(0,0,0,.18)}
|
|
.modal-header{display:flex;align-items:center;justify-content:space-between;padding:16px 20px;border-bottom:1px solid #e8eaed}
|
|
.modal-header h3{font-size:15px;font-weight:700;color:#1a1a2e}
|
|
.modal-close{width:32px;height:32px;border:none;background:#edf2f7;border-radius:8px;cursor:pointer;font-size:18px;color:#6b7280;display:flex;align-items:center;justify-content:center}
|
|
.modal-close:hover{background:#e2e8f0;color:#1a1a2e}
|
|
.modal-search{padding:12px 20px;border-bottom:1px solid #e8eaed}
|
|
.modal-search input{width:100%;height:36px;border:1.5px solid #dde1e7;border-radius:8px;padding:0 12px;font-size:13px;font-family:inherit;outline:none}
|
|
.modal-search input:focus{border-color:#7c3aed;box-shadow:0 0 0 3px rgba(124,58,237,.08)}
|
|
.modal-body{flex:1;overflow-y:auto;padding:8px 0}
|
|
.modal-body::-webkit-scrollbar{width:6px}.modal-body::-webkit-scrollbar-thumb{background:#d1d5db;border-radius:3px}
|
|
.modal-footer{display:flex;align-items:center;justify-content:space-between;padding:12px 20px;border-top:1px solid #e8eaed;background:#f8fafb;border-radius:0 0 14px 14px}
|
|
.grp-tab{padding:4px 12px;border-radius:16px;font-size:11px;font-weight:700;cursor:pointer;border:1.5px solid #ddd6fe;background:#fff;color:#7c3aed;transition:all .15s;white-space:nowrap}
|
|
.grp-tab:hover{background:#f5f3ff}
|
|
.grp-tab.active{background:#7c3aed;color:#fff;border-color:#7c3aed}
|
|
.acct-group-hdr{display:flex;align-items:center;gap:8px;padding:8px 20px;background:#f5f3ff;font-size:12px;font-weight:700;color:#7c3aed;cursor:pointer;user-select:none;border-bottom:1px solid #ede9fe}
|
|
.acct-group-hdr:hover{background:#ede9fe}
|
|
.acct-group-hdr input[type=checkbox]{accent-color:#7c3aed;width:15px;height:15px;cursor:pointer}
|
|
.acct-row{display:flex;align-items:center;gap:8px;padding:6px 20px 6px 40px;font-size:12px;color:#374151;cursor:pointer;border-bottom:1px solid #f3f4f6}
|
|
.acct-row:hover{background:#faf5ff}
|
|
.acct-row input[type=checkbox]{accent-color:#7c3aed;width:14px;height:14px;cursor:pointer}
|
|
.acct-row .code{font-weight:600;color:#1a1a2e;min-width:70px}
|
|
.acct-row .name{flex:1}
|
|
</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>General Ledger</span> Report</h1>
|
|
<div style="display:flex;gap:8px;flex-wrap:wrap">
|
|
<button class="btn btn-outline" id="btnDownload" disabled>Download Excel</button>
|
|
<button class="btn btn-outline" id="btnPrint" disabled>Print Ledger</button>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="card">
|
|
<div class="filter-bar">
|
|
<div class="fg">
|
|
<label>From Date</label>
|
|
<input type="date" id="fromDate">
|
|
</div>
|
|
<div class="fg">
|
|
<label>To Date</label>
|
|
<input type="date" id="toDate">
|
|
</div>
|
|
<div class="fg">
|
|
<label>Accounts</label>
|
|
<button type="button" class="acct-pick-btn" id="btnPickAccounts">
|
|
Select Accounts <span class="badge" id="acctCount" style="display:none">0</span>
|
|
</button>
|
|
</div>
|
|
<button class="btn btn-primary" id="btnLoad">Load Report</button>
|
|
</div>
|
|
|
|
<div class="tbl-wrap" id="tblWrap">
|
|
<div class="empty" id="emptyMsg">Select accounts and date range, then click <b>Load Report</b>.</div>
|
|
<table id="glTable" style="display:none">
|
|
<thead>
|
|
<tr>
|
|
<th>Acct Code</th>
|
|
<th>Acct Name</th>
|
|
<th>Posting Date</th>
|
|
<th>Due Date</th>
|
|
<th>Doc Date</th>
|
|
<th>Series</th>
|
|
<th>Doc No</th>
|
|
<th>Trans No</th>
|
|
<th>Seq</th>
|
|
<th>Remarks</th>
|
|
<th>Offset Acct</th>
|
|
<th>Offset Name</th>
|
|
<th style="text-align:right">Deb/Cred</th>
|
|
<th style="text-align:right">Debit</th>
|
|
<th style="text-align:right">Credit</th>
|
|
<th style="text-align:right">Cumulative Bal</th>
|
|
<th>Ref1</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody id="glBody"></tbody>
|
|
</table>
|
|
</div>
|
|
|
|
<div class="status-bar">
|
|
<div class="status-dot" id="statusDot"></div>
|
|
<span id="statusText">Ready</span>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Account Picker Modal -->
|
|
<div class="modal-overlay" id="acctModal">
|
|
<div class="modal">
|
|
<div class="modal-header">
|
|
<h3>Select Accounts</h3>
|
|
<button class="modal-close" id="acctModalClose">×</button>
|
|
</div>
|
|
<div class="modal-search">
|
|
<input type="text" id="acctSearch" placeholder="Search by code or name...">
|
|
<div id="groupTabs" style="display:flex;gap:6px;flex-wrap:wrap;margin-top:8px"></div>
|
|
</div>
|
|
<div class="modal-body" id="acctList"></div>
|
|
<div class="modal-footer">
|
|
<span id="acctSelectedInfo" style="font-size:12px;color:#6b7280">0 selected</span>
|
|
<div style="display:flex;gap:8px">
|
|
<button class="btn btn-outline" id="acctClearAll" style="height:32px;font-size:11px">Clear All</button>
|
|
<button class="btn btn-primary" id="acctDone" style="height:32px;font-size:11px">Done</button>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Toast -->
|
|
<div class="toast" id="toast"></div>
|
|
|
|
<script>
|
|
(function(){
|
|
const tok = sessionStorage.getItem('portal_token');
|
|
const hdr = { Authorization:'Bearer '+tok };
|
|
const hdrJson = { 'Content-Type':'application/json', Authorization:'Bearer '+tok };
|
|
|
|
// ── State ──────────────────────────────────────────────────────────────────
|
|
let allAccounts = []; // from API
|
|
let selectedAccts = new Set();
|
|
let reportData = null; // { data, openingBalances }
|
|
|
|
// ── DOM refs ───────────────────────────────────────────────────────────────
|
|
const $from = document.getElementById('fromDate');
|
|
const $to = document.getElementById('toDate');
|
|
const $btnLoad = document.getElementById('btnLoad');
|
|
const $btnDl = document.getElementById('btnDownload');
|
|
const $btnPr = document.getElementById('btnPrint');
|
|
const $table = document.getElementById('glTable');
|
|
const $body = document.getElementById('glBody');
|
|
const $empty = document.getElementById('emptyMsg');
|
|
const $statusDot= document.getElementById('statusDot');
|
|
const $statusTxt= document.getElementById('statusText');
|
|
const $modal = document.getElementById('acctModal');
|
|
const $acctList = document.getElementById('acctList');
|
|
const $acctSearch=document.getElementById('acctSearch');
|
|
const $acctCount= document.getElementById('acctCount');
|
|
const $acctInfo = document.getElementById('acctSelectedInfo');
|
|
|
|
// ── Default dates (current month) ─────────────────────────────────────────
|
|
const now = new Date();
|
|
$from.value = now.getFullYear() + '-' + String(now.getMonth()+1).padStart(2,'0') + '-01';
|
|
$to.value = now.toISOString().slice(0,10);
|
|
|
|
// ── Toast ──────────────────────────────────────────────────────────────────
|
|
function toast(msg, type='info') {
|
|
const t = document.getElementById('toast');
|
|
t.textContent = msg;
|
|
t.className = 'toast ' + type + ' show';
|
|
setTimeout(() => t.classList.remove('show'), 3500);
|
|
}
|
|
|
|
function setStatus(state, text) {
|
|
$statusDot.className = 'status-dot ' + state;
|
|
$statusTxt.textContent = text;
|
|
}
|
|
|
|
// ── Number formatting (Indian) ─────────────────────────────────────────────
|
|
function fmt(v) {
|
|
if (v == null || isNaN(v)) return '';
|
|
return Number(v).toLocaleString('en-IN', { minimumFractionDigits:2, maximumFractionDigits:2 });
|
|
}
|
|
function fmtDate(d) {
|
|
if (!d) return '';
|
|
const dt = new Date(d);
|
|
return dt.toLocaleDateString('en-IN', { day:'2-digit', month:'short', year:'numeric' });
|
|
}
|
|
|
|
// ── Load accounts ──────────────────────────────────────────────────────────
|
|
let accountsLoaded = false;
|
|
async function loadAccounts() {
|
|
try {
|
|
const r = await fetch('/api/general-ledger/accounts', { headers: hdr });
|
|
const j = await r.json();
|
|
if (j.success) { allAccounts = j.data; accountsLoaded = true; }
|
|
else { console.error('Accounts API error:', j.message); toast('Failed to load accounts: '+j.message,'error'); }
|
|
} catch (e) { console.error('Failed to load accounts', e); toast('Failed to load accounts','error'); }
|
|
}
|
|
loadAccounts();
|
|
|
|
// ── Account picker modal ───────────────────────────────────────────────────
|
|
document.getElementById('btnPickAccounts').addEventListener('click', async () => {
|
|
if (!accountsLoaded) {
|
|
toast('Loading accounts…','info');
|
|
await loadAccounts();
|
|
}
|
|
renderGroupTabs();
|
|
renderAccountList();
|
|
$modal.classList.add('show');
|
|
$acctSearch.value = '';
|
|
$acctSearch.focus();
|
|
});
|
|
document.getElementById('acctModalClose').addEventListener('click', () => $modal.classList.remove('show'));
|
|
document.getElementById('acctDone').addEventListener('click', () => $modal.classList.remove('show'));
|
|
document.getElementById('acctClearAll').addEventListener('click', () => {
|
|
selectedAccts.clear();
|
|
renderAccountList();
|
|
updateAcctBadge();
|
|
});
|
|
$modal.addEventListener('click', e => { if (e.target === $modal) $modal.classList.remove('show'); });
|
|
$acctSearch.addEventListener('input', () => renderAccountList());
|
|
|
|
let activeGroupFilter = null; // null = All, else GroupMask number
|
|
const $groupTabs = document.getElementById('groupTabs');
|
|
|
|
function renderGroupTabs() {
|
|
const gLabels = {};
|
|
allAccounts.forEach(a => { gLabels[a.GroupMask] = a.GroupLabel; });
|
|
let html = `<span class="grp-tab${activeGroupFilter==null?' active':''}" data-gf="all">All</span>`;
|
|
Object.keys(gLabels).sort((a,b)=>a-b).forEach(gm => {
|
|
html += `<span class="grp-tab${activeGroupFilter==gm?' active':''}" data-gf="${gm}">${gLabels[gm]}</span>`;
|
|
});
|
|
$groupTabs.innerHTML = html;
|
|
}
|
|
$groupTabs.addEventListener('click', e => {
|
|
const tab = e.target.closest('.grp-tab');
|
|
if (!tab) return;
|
|
const gf = tab.dataset.gf;
|
|
activeGroupFilter = gf === 'all' ? null : gf;
|
|
$groupTabs.querySelectorAll('.grp-tab').forEach(t => t.classList.remove('active'));
|
|
tab.classList.add('active');
|
|
// Auto-select all accounts in the clicked group
|
|
selectedAccts.clear();
|
|
if (gf === 'all') {
|
|
allAccounts.forEach(a => selectedAccts.add(String(a.AcctCode)));
|
|
} else {
|
|
allAccounts.filter(a => String(a.GroupMask) === gf).forEach(a => selectedAccts.add(String(a.AcctCode)));
|
|
}
|
|
updateAcctBadge();
|
|
renderAccountList();
|
|
});
|
|
|
|
function renderAccountList() {
|
|
const q = ($acctSearch.value || '').toLowerCase();
|
|
// Group by GroupMask
|
|
const groups = {};
|
|
for (const a of allAccounts) {
|
|
if (activeGroupFilter != null && String(a.GroupMask) !== String(activeGroupFilter)) continue;
|
|
const code = String(a.AcctCode);
|
|
const name = (a.AcctName || '').toLowerCase();
|
|
if (q && !code.includes(q) && !name.includes(q)) continue;
|
|
const key = a.GroupMask;
|
|
if (!groups[key]) groups[key] = { label: a.GroupLabel, items: [] };
|
|
groups[key].items.push(a);
|
|
}
|
|
|
|
let html = '';
|
|
for (const gm of Object.keys(groups).sort((a,b) => a - b)) {
|
|
const g = groups[gm];
|
|
const allChecked = g.items.every(a => selectedAccts.has(String(a.AcctCode)));
|
|
const someChecked = g.items.some(a => selectedAccts.has(String(a.AcctCode)));
|
|
html += `<div class="acct-group-hdr" data-gm="${gm}">
|
|
<input type="checkbox" ${allChecked ? 'checked' : ''} ${someChecked && !allChecked ? 'indeterminate' : ''} data-group="${gm}">
|
|
${g.label} (${g.items.length})
|
|
</div>`;
|
|
for (const a of g.items) {
|
|
const c = String(a.AcctCode);
|
|
html += `<div class="acct-row" data-code="${c}">
|
|
<input type="checkbox" ${selectedAccts.has(c) ? 'checked' : ''} data-acct="${c}">
|
|
<span class="code">${c}</span>
|
|
<span class="name">${a.AcctName || ''}</span>
|
|
</div>`;
|
|
}
|
|
}
|
|
if (!html) html = '<div style="padding:20px;text-align:center;color:#9ca3af">No accounts found</div>';
|
|
$acctList.innerHTML = html;
|
|
|
|
// Set indeterminate state (can't do via HTML attr)
|
|
$acctList.querySelectorAll('input[data-group]').forEach(cb => {
|
|
const gm = cb.dataset.group;
|
|
const g = groups[gm];
|
|
if (!g) return;
|
|
const allC = g.items.every(a => selectedAccts.has(String(a.AcctCode)));
|
|
const someC = g.items.some(a => selectedAccts.has(String(a.AcctCode)));
|
|
cb.indeterminate = someC && !allC;
|
|
});
|
|
|
|
updateAcctInfo();
|
|
}
|
|
|
|
$acctList.addEventListener('change', e => {
|
|
const cb = e.target;
|
|
if (cb.dataset.group) {
|
|
// Group toggle
|
|
const gm = cb.dataset.group;
|
|
const items = allAccounts.filter(a => String(a.GroupMask) === gm);
|
|
const q = ($acctSearch.value || '').toLowerCase();
|
|
const filtered = q ? items.filter(a => String(a.AcctCode).includes(q) || (a.AcctName||'').toLowerCase().includes(q)) : items;
|
|
if (cb.checked) filtered.forEach(a => selectedAccts.add(String(a.AcctCode)));
|
|
else filtered.forEach(a => selectedAccts.delete(String(a.AcctCode)));
|
|
renderAccountList();
|
|
} else if (cb.dataset.acct) {
|
|
if (cb.checked) selectedAccts.add(cb.dataset.acct);
|
|
else selectedAccts.delete(cb.dataset.acct);
|
|
renderAccountList();
|
|
}
|
|
updateAcctBadge();
|
|
});
|
|
|
|
function updateAcctBadge() {
|
|
const n = selectedAccts.size;
|
|
$acctCount.style.display = n ? '' : 'none';
|
|
$acctCount.textContent = n;
|
|
}
|
|
function updateAcctInfo() {
|
|
$acctInfo.textContent = selectedAccts.size + ' selected';
|
|
}
|
|
|
|
// ── Load report ────────────────────────────────────────────────────────────
|
|
$btnLoad.addEventListener('click', loadReport);
|
|
|
|
async function loadReport() {
|
|
if (!selectedAccts.size) return toast('Please select at least one account', 'error');
|
|
if (!$from.value || !$to.value) return toast('Please select date range', 'error');
|
|
|
|
$btnLoad.disabled = true;
|
|
setStatus('loading', 'Loading...');
|
|
$table.style.display = 'none';
|
|
$empty.style.display = 'none';
|
|
|
|
try {
|
|
const codes = Array.from(selectedAccts).join(',');
|
|
const url = `/api/general-ledger/data?from=${$from.value}&to=${$to.value}&accounts=${encodeURIComponent(codes)}`;
|
|
const r = await fetch(url, { headers: hdr });
|
|
const j = await r.json();
|
|
if (!j.success) throw new Error(j.message || 'Failed');
|
|
|
|
reportData = j;
|
|
renderTable(j.data, j.openingBalances);
|
|
setStatus('ok', `Loaded ${j.data.length} transactions`);
|
|
$btnDl.disabled = false;
|
|
$btnPr.disabled = false;
|
|
} catch (e) {
|
|
toast(e.message, 'error');
|
|
setStatus('err', 'Error: ' + e.message);
|
|
$empty.textContent = 'Failed to load data.';
|
|
$empty.style.display = '';
|
|
} finally {
|
|
$btnLoad.disabled = false;
|
|
}
|
|
}
|
|
|
|
// ── Render table ───────────────────────────────────────────────────────────
|
|
function renderTable(data, ob) {
|
|
$body.innerHTML = '';
|
|
if (!data.length) {
|
|
$empty.textContent = 'No transactions found for the selected filters.';
|
|
$empty.style.display = '';
|
|
$table.style.display = 'none';
|
|
return;
|
|
}
|
|
|
|
// Group by account
|
|
const grouped = {};
|
|
const order = [];
|
|
for (const row of data) {
|
|
const k = row.acct_code;
|
|
if (!grouped[k]) { grouped[k] = []; order.push(k); }
|
|
grouped[k].push(row);
|
|
}
|
|
|
|
const colCount = 17;
|
|
for (const acct of order) {
|
|
const rows = grouped[acct];
|
|
const acctName = rows[0].acct_name || '';
|
|
const opening = ob[acct] || 0;
|
|
|
|
// Account header
|
|
const hr = document.createElement('tr');
|
|
hr.className = 'acct-header';
|
|
hr.innerHTML = `<td colspan="${colCount}">${acct} - ${acctName} | Opening Balance: ${fmt(opening)}</td>`;
|
|
$body.appendChild(hr);
|
|
|
|
let totalDebit = 0, totalCredit = 0;
|
|
let runBal = opening;
|
|
|
|
for (const r of rows) {
|
|
const debit = Number(r.debit) || 0;
|
|
const credit = Number(r.credit) || 0;
|
|
totalDebit += debit;
|
|
totalCredit += credit;
|
|
runBal += (debit - credit);
|
|
|
|
const tr = document.createElement('tr');
|
|
tr.innerHTML = `
|
|
<td>${acct}</td>
|
|
<td style="max-width:160px;overflow:hidden;text-overflow:ellipsis" title="${acctName.replace(/"/g,'"')}">${acctName}</td>
|
|
<td>${fmtDate(r.posting_date)}</td>
|
|
<td>${fmtDate(r.due_date)}</td>
|
|
<td>${fmtDate(r.document_date)}</td>
|
|
<td>${r.series ?? ''}</td>
|
|
<td>${r.doc_no ?? ''}</td>
|
|
<td>${r.trans_no ?? ''}</td>
|
|
<td>${r.seq_no ?? ''}</td>
|
|
<td style="max-width:160px;overflow:hidden;text-overflow:ellipsis" title="${(r.remarks||'').replace(/"/g,'"')}">${r.remarks || ''}</td>
|
|
<td>${r.offset_acct || ''}</td>
|
|
<td style="max-width:140px;overflow:hidden;text-overflow:ellipsis" title="${(r.offset_acct_name||'').replace(/"/g,'"')}">${r.offset_acct_name || ''}</td>
|
|
<td class="num">${fmt(r.deb_cred)}</td>
|
|
<td class="num">${fmt(debit)}</td>
|
|
<td class="num">${fmt(credit)}</td>
|
|
<td class="num">${fmt(runBal)}</td>
|
|
<td>${r.ref1 || ''}</td>`;
|
|
$body.appendChild(tr);
|
|
}
|
|
|
|
// Footer
|
|
const closing = opening + totalDebit - totalCredit;
|
|
const fr = document.createElement('tr');
|
|
fr.className = 'acct-footer';
|
|
fr.innerHTML = `<td colspan="12" style="text-align:right">Account Total:</td>
|
|
<td class="num">${fmt(totalDebit - totalCredit)}</td>
|
|
<td class="num">${fmt(totalDebit)}</td>
|
|
<td class="num">${fmt(totalCredit)}</td>
|
|
<td class="num">${fmt(closing)}</td>
|
|
<td></td>`;
|
|
$body.appendChild(fr);
|
|
|
|
// Spacer
|
|
const sp = document.createElement('tr');
|
|
sp.innerHTML = `<td colspan="${colCount}" style="height:10px;background:#f0f2f5;border:none"></td>`;
|
|
$body.appendChild(sp);
|
|
}
|
|
|
|
$table.style.display = '';
|
|
}
|
|
|
|
// ── Excel download ─────────────────────────────────────────────────────────
|
|
$btnDl.addEventListener('click', downloadExcel);
|
|
|
|
function downloadExcel() {
|
|
if (!reportData) return;
|
|
const { data, openingBalances: ob } = reportData;
|
|
|
|
const wb = XLSX.utils.book_new();
|
|
const rows = [];
|
|
|
|
const hdrStyle = { font:{bold:true,color:{rgb:'FFFFFF'},sz:11}, fill:{fgColor:{rgb:'4C1D95'}}, alignment:{horizontal:'center'}, border:{bottom:{style:'thin',color:{rgb:'999999'}}} };
|
|
const acctHdrStyle = { font:{bold:true,color:{rgb:'FFFFFF'},sz:11}, fill:{fgColor:{rgb:'3B82F6'}}, alignment:{horizontal:'left'} };
|
|
const footerStyle = { font:{bold:true,sz:10}, fill:{fgColor:{rgb:'EDE9FE'}} };
|
|
const numFmt = '#,##,##0.00';
|
|
const numStyle = { numFmt, alignment:{horizontal:'right'} };
|
|
const footerNumStyle = { numFmt, font:{bold:true,sz:10}, fill:{fgColor:{rgb:'EDE9FE'}}, alignment:{horizontal:'right'} };
|
|
|
|
// Header row
|
|
const headers = ['Acct Code','Acct Name','Posting Date','Due Date','Doc Date','Series','Doc No','Trans No','Seq','Remarks','Offset Acct','Offset Name','Deb/Cred','Debit','Credit','Cumulative Bal','Ref1'];
|
|
rows.push(headers.map(h => ({ v:h, s:hdrStyle })));
|
|
|
|
// Group by account
|
|
const grouped = {};
|
|
const order = [];
|
|
for (const r of data) {
|
|
if (!grouped[r.acct_code]) { grouped[r.acct_code] = []; order.push(r.acct_code); }
|
|
grouped[r.acct_code].push(r);
|
|
}
|
|
|
|
for (const acct of order) {
|
|
const txns = grouped[acct];
|
|
const acctName = txns[0].acct_name || '';
|
|
const opening = ob[acct] || 0;
|
|
|
|
// Account header row
|
|
const acctRow = Array(17).fill({ v:'', s:acctHdrStyle });
|
|
acctRow[0] = { v:`${acct} - ${acctName} | Opening Balance: ${fmt(opening)}`, s:acctHdrStyle };
|
|
rows.push(acctRow);
|
|
|
|
let totalDebit = 0, totalCredit = 0, runBal = opening;
|
|
for (const r of txns) {
|
|
const d = Number(r.debit)||0, c = Number(r.credit)||0;
|
|
totalDebit += d; totalCredit += c;
|
|
runBal += (d - c);
|
|
rows.push([
|
|
acct,
|
|
acctName,
|
|
{ v: r.posting_date ? new Date(r.posting_date) : '', t: r.posting_date ? 'd' : 's', z:'DD-MMM-YYYY' },
|
|
{ v: r.due_date ? new Date(r.due_date) : '', t: r.due_date ? 'd' : 's', z:'DD-MMM-YYYY' },
|
|
{ v: r.document_date ? new Date(r.document_date) : '', t: r.document_date ? 'd' : 's', z:'DD-MMM-YYYY' },
|
|
r.series ?? '',
|
|
r.doc_no ?? '',
|
|
r.trans_no ?? '',
|
|
r.seq_no ?? '',
|
|
r.remarks || '',
|
|
r.offset_acct || '',
|
|
r.offset_acct_name || '',
|
|
{ v: Number(r.deb_cred)||0, t:'n', s:numStyle },
|
|
{ v: d, t:'n', s:numStyle },
|
|
{ v: c, t:'n', s:numStyle },
|
|
{ v: runBal, t:'n', s:numStyle },
|
|
r.ref1 || ''
|
|
]);
|
|
}
|
|
|
|
// Footer
|
|
const closing = opening + totalDebit - totalCredit;
|
|
const fr = Array(17).fill({ v:'', s:footerStyle });
|
|
fr[0] = { v:'Account Total', s:footerStyle };
|
|
fr[12] = { v: totalDebit - totalCredit, t:'n', s:footerNumStyle };
|
|
fr[13] = { v: totalDebit, t:'n', s:footerNumStyle };
|
|
fr[14] = { v: totalCredit, t:'n', s:footerNumStyle };
|
|
fr[15] = { v: closing, t:'n', s:footerNumStyle };
|
|
rows.push(fr);
|
|
|
|
// Blank row
|
|
rows.push([]);
|
|
}
|
|
|
|
const ws = XLSX.utils.aoa_to_sheet(rows);
|
|
|
|
// Column widths
|
|
ws['!cols'] = [
|
|
{wch:12},{wch:25},{wch:13},{wch:13},{wch:13},{wch:8},{wch:10},{wch:10},{wch:5},
|
|
{wch:30},{wch:12},{wch:25},{wch:14},{wch:14},{wch:14},{wch:16},{wch:14}
|
|
];
|
|
|
|
XLSX.utils.book_append_sheet(wb, ws, 'General Ledger');
|
|
XLSX.writeFile(wb, `General_Ledger_${$from.value}_to_${$to.value}.xlsx`);
|
|
toast('Excel downloaded', 'success');
|
|
}
|
|
|
|
// ── Print ─────────────────────────────────────────────────────────────────
|
|
$btnPr.addEventListener('click', () => {
|
|
if (!reportData) return;
|
|
const {data, openingBalances: ob} = reportData;
|
|
if (!data.length) return toast('No data to print','error');
|
|
|
|
const grouped={}, order=[];
|
|
for(const r of data){if(!grouped[r.acct_code]){grouped[r.acct_code]=[];order.push(r.acct_code);}grouped[r.acct_code].push(r);}
|
|
|
|
let tbl='';
|
|
for(const acct of order){
|
|
const txns=grouped[acct], acctName=txns[0].acct_name||'', opening=ob[acct]||0;
|
|
tbl+=`<tr class="ah"><td colspan="17">${acct} - ${acctName} | Opening Balance: ${fmt(opening)}</td></tr>`;
|
|
let td=0,tc=0,rb=opening;
|
|
for(const r of txns){
|
|
const d=Number(r.debit)||0, c=Number(r.credit)||0;
|
|
td+=d;tc+=c;rb+=(d-c);
|
|
tbl+=`<tr>
|
|
<td>${acct}</td><td>${acctName}</td>
|
|
<td>${fmtDate(r.posting_date)}</td><td>${fmtDate(r.due_date)}</td><td>${fmtDate(r.document_date)}</td>
|
|
<td>${r.series??''}</td><td>${r.doc_no??''}</td><td>${r.trans_no??''}</td><td>${r.seq_no??''}</td>
|
|
<td class="rm">${r.remarks||''}</td>
|
|
<td>${r.offset_acct||''}</td><td class="rm">${r.offset_acct_name||''}</td>
|
|
<td class="n">${fmt(r.deb_cred)}</td><td class="n">${fmt(d)}</td><td class="n">${fmt(c)}</td>
|
|
<td class="n">${fmt(rb)}</td><td>${r.ref1||''}</td>
|
|
</tr>`;
|
|
}
|
|
const cl=opening+td-tc;
|
|
tbl+=`<tr class="af"><td colspan="12" style="text-align:right">Account Total:</td>
|
|
<td class="n">${fmt(td-tc)}</td><td class="n">${fmt(td)}</td><td class="n">${fmt(tc)}</td>
|
|
<td class="n">${fmt(cl)}</td><td></td></tr>`;
|
|
tbl+=`<tr><td colspan="17" style="height:6px;border:none"></td></tr>`;
|
|
}
|
|
|
|
const w=window.open('','_blank','width=1400,height=900');
|
|
w.document.write(`<!DOCTYPE html><html><head><meta charset="UTF-8">
|
|
<title>General Ledger - ${$from.value} to ${$to.value}</title>
|
|
<style>
|
|
@page{size:A4 landscape;margin:4mm}
|
|
*{box-sizing:border-box;margin:0;padding:0}
|
|
body{font-family:Arial,Helvetica,sans-serif;color:#1a1a2e;padding:4px;zoom:0.7}
|
|
h2{font-size:11px;font-weight:700;margin-bottom:4px;color:#4C1D95;border-bottom:1px solid #7c3aed;padding-bottom:2px}
|
|
.sub{font-size:9px;color:#6b7280;margin-bottom:6px}
|
|
table{border-collapse:collapse;width:100%}
|
|
th,td{font-size:7px!important;padding:2px 4px!important;white-space:nowrap;line-height:1.3!important;border-bottom:1px solid #e5e7eb}
|
|
th{background:#4C1D95!important;color:#fff!important;font-weight:700;text-align:left;-webkit-print-color-adjust:exact;print-color-adjust:exact}
|
|
.n{text-align:right;font-variant-numeric:tabular-nums}
|
|
.rm{max-width:100px;overflow:hidden;text-overflow:ellipsis}
|
|
.ah td{background:#3B82F6!important;color:#fff!important;font-weight:700;font-size:8px!important;-webkit-print-color-adjust:exact;print-color-adjust:exact}
|
|
.af td{background:#EDE9FE!important;font-weight:700;-webkit-print-color-adjust:exact;print-color-adjust:exact}
|
|
</style><link rel="stylesheet" href="/theme.css?v=3"/>
|
|
</head><body>
|
|
<h2>General Ledger Report</h2>
|
|
<div class="sub">${$from.value} to ${$to.value} | ${order.length} account(s) | ${data.length} transaction(s)</div>
|
|
<table>
|
|
<thead><tr>
|
|
<th>Code</th><th>Name</th><th>Post Date</th><th>Due Date</th><th>Doc Date</th>
|
|
<th>Series</th><th>Doc No</th><th>Trans No</th><th>Seq</th><th>Remarks</th>
|
|
<th>Offset Acct</th><th>Offset Name</th><th class="n">Deb/Cred</th><th class="n">Debit</th>
|
|
<th class="n">Credit</th><th class="n">Cum. Bal</th><th>Ref1</th>
|
|
</tr></thead>
|
|
<tbody>${tbl}</tbody>
|
|
</table>
|
|
<script>window.onload=()=>{window.print();}<\/script>
|
|
</body></html>`);
|
|
w.document.close();
|
|
});
|
|
|
|
})();
|
|
</script>
|
|
</body>
|
|
</html>
|