106 lines
3.9 KiB
JavaScript
106 lines
3.9 KiB
JavaScript
'use strict';
|
|
const express = require('express');
|
|
const router = express.Router();
|
|
const { verifyToken } = require('../middleware/auth');
|
|
const { getPool } = require('../services/sqlPool');
|
|
|
|
// ── GET /api/general-ledger/accounts ─────────────────────────────────────────
|
|
router.get('/accounts', verifyToken, async (_req, res) => {
|
|
try {
|
|
const pool = await getPool();
|
|
const result = await pool.request().query(`
|
|
SELECT AcctCode, AcctName, GroupMask,
|
|
CASE GroupMask
|
|
WHEN 1 THEN 'Assets'
|
|
WHEN 2 THEN 'Liabilities'
|
|
WHEN 3 THEN 'Equity'
|
|
WHEN 4 THEN 'Revenue'
|
|
WHEN 5 THEN 'Expenditure'
|
|
ELSE 'Group ' + CAST(GroupMask AS VARCHAR)
|
|
END AS GroupLabel
|
|
FROM OACT
|
|
ORDER BY GroupMask, AcctCode
|
|
`);
|
|
res.json({ success: true, data: result.recordset });
|
|
} catch (err) {
|
|
console.error('[GeneralLedger] accounts error:', err);
|
|
res.status(500).json({ success: false, message: err.message });
|
|
}
|
|
});
|
|
|
|
// ── GET /api/general-ledger/data ─────────────────────────────────────────────
|
|
router.get('/data', verifyToken, async (req, res) => {
|
|
try {
|
|
const { from, to, accounts } = req.query;
|
|
if (!from || !to) return res.status(400).json({ success: false, message: 'from and to dates required' });
|
|
if (!accounts) return res.status(400).json({ success: false, message: 'accounts required' });
|
|
|
|
// Sanitise: only allow numeric account codes (prevent SQL injection)
|
|
const acctList = accounts.split(',').map(c => c.trim()).filter(c => /^\d+$/.test(c));
|
|
if (acctList.length === 0) return res.status(400).json({ success: false, message: 'No valid account codes provided' });
|
|
|
|
const inClause = acctList.map(c => `'${c}'`).join(',');
|
|
|
|
const pool = await getPool();
|
|
|
|
// Opening balances
|
|
const obResult = await pool.request()
|
|
.input('from', from)
|
|
.query(`
|
|
SELECT T0.Account AS acct_code,
|
|
SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) AS opening_balance
|
|
FROM JDT1 T0
|
|
WHERE T0.RefDate < @from
|
|
AND T0.Account IN (${inClause})
|
|
GROUP BY T0.Account
|
|
`);
|
|
|
|
const openingBalances = {};
|
|
for (const row of obResult.recordset) {
|
|
openingBalances[row.acct_code] = row.opening_balance;
|
|
}
|
|
|
|
// Transaction data
|
|
const txResult = await pool.request()
|
|
.input('from', from)
|
|
.input('to', to)
|
|
.query(`
|
|
SELECT
|
|
T2.AcctCode AS acct_code,
|
|
T2.AcctName AS acct_name,
|
|
T0.RefDate AS posting_date,
|
|
T0.DueDate AS due_date,
|
|
T0.TaxDate AS document_date,
|
|
T1.Series AS series,
|
|
T1.Number AS doc_no,
|
|
T0.TransId AS trans_no,
|
|
T0.Line_ID AS seq_no,
|
|
T0.LineMemo AS remarks,
|
|
T0.ContraAct AS offset_acct,
|
|
T3.AcctName AS offset_acct_name,
|
|
(T0.Debit - T0.Credit) AS deb_cred,
|
|
T0.Debit AS debit,
|
|
T0.Credit AS credit,
|
|
SUM(T0.Debit - T0.Credit) OVER (
|
|
PARTITION BY T0.Account
|
|
ORDER BY T0.RefDate, T0.TransId, T0.Line_ID
|
|
) AS cumulative_balance,
|
|
T0.Ref1 AS ref1
|
|
FROM JDT1 T0
|
|
INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
|
|
LEFT JOIN OACT T2 ON T0.Account = T2.AcctCode
|
|
LEFT JOIN OACT T3 ON T0.ContraAct = T3.AcctCode
|
|
WHERE T0.RefDate BETWEEN @from AND @to
|
|
AND T0.Account IN (${inClause})
|
|
ORDER BY T0.Account, T0.RefDate, T0.TransId, T0.Line_ID
|
|
`);
|
|
|
|
res.json({ success: true, data: txResult.recordset, openingBalances });
|
|
} catch (err) {
|
|
console.error('[GeneralLedger] data error:', err);
|
|
res.status(500).json({ success: false, message: err.message });
|
|
}
|
|
});
|
|
|
|
module.exports = router;
|