352 lines
16 KiB
JavaScript
352 lines
16 KiB
JavaScript
'use strict';
|
||
// bs_config (this store's own config table) lives on the app's own database.
|
||
// fetchGroupBalance/fetchReservesSurplus/etc. below all receive their pool
|
||
// as a parameter from the caller (routes/balanceSheet.js, which imports the
|
||
// SAP pool directly for its OACT/JDT1 queries), so they're unaffected here.
|
||
const { getPool } = require('./appSqlPool');
|
||
|
||
// Balance sheet group structure (fixed, not user-configurable)
|
||
const BS_ITEMS = [
|
||
// key, label, section (L=Liabilities, A=Assets), subsection, source
|
||
{ key:'share_capital', label:'Share Capital', sec:'L', sub:'SF', src:'fixed' },
|
||
{ key:'reserves_surplus', label:'Reserves & Surplus', sec:'L', sub:'SF', src:'reserves_formula' },
|
||
{ key:'profit_loss', label:'Profit & Loss', sec:'L', sub:'SF', src:'pl_net' },
|
||
{ key:'lt_borrowings', label:'Long-term Borrowings', sec:'L', sub:'NCL', src:'ledger' },
|
||
{ key:'other_lt_liab', label:'Other Long-term Liabilities', sec:'L', sub:'NCL', src:'ledger' },
|
||
{ key:'lt_provisions', label:'Long-term Provisions', sec:'L', sub:'NCL', src:'ledger' },
|
||
{ key:'st_borrowings', label:'Short-term Borrowings', sec:'L', sub:'CL', src:'ledger' },
|
||
{ key:'trade_payables', label:'Trade Payables', sec:'L', sub:'CL', src:'ledger' },
|
||
{ key:'other_current_liab', label:'Other Current Liabilities', sec:'L', sub:'CL', src:'ledger' },
|
||
{ key:'st_provisions', label:'Short-term Provisions', sec:'L', sub:'CL', src:'provisions' },
|
||
{ key:'fixed_assets', label:'Fixed Assets (Tangible & Intangible)', sec:'A', sub:'NCA', src:'ledger' },
|
||
{ key:'non_current_invest', label:'Non-current Investments', sec:'A', sub:'NCA', src:'ledger' },
|
||
{ key:'lt_loans_advances', label:'Long-term Loans & Advances', sec:'A', sub:'NCA', src:'ledger' },
|
||
{ key:'other_non_current', label:'Other Non-current Assets', sec:'A', sub:'NCA', src:'ledger' },
|
||
{ key:'current_invest', label:'Current Investments', sec:'A', sub:'CA', src:'ledger' },
|
||
{ key:'inventories', label:'Inventories', sec:'A', sub:'CA', src:'closing_stock' },
|
||
{ key:'trade_receivables', label:'Trade Receivables', sec:'A', sub:'CA', src:'ledger' },
|
||
{ key:'cash_equivalents', label:'Cash and Cash Equivalents', sec:'A', sub:'CA', src:'ledger' },
|
||
{ key:'st_loans_advances', label:'Short-term Loans & Advances', sec:'A', sub:'CA', src:'ledger' },
|
||
{ key:'other_current_assets', label:'Other Current Assets', sec:'A', sub:'CA', src:'ledger' },
|
||
];
|
||
|
||
// Default account codes per group
|
||
// Default opening account codes for reserves_surplus formula
|
||
const DEFAULT_OPENING_ACCOUNTS = {
|
||
reserves_surplus: ['5100001001'],
|
||
};
|
||
|
||
const DEFAULT_ACCOUNTS = {
|
||
reserves_surplus: ['2120020001'],
|
||
trade_payables: ['2140000000'],
|
||
other_current_liab: ['2200000000'],
|
||
st_provisions: ['2210000000'],
|
||
fixed_assets: ['1100000000'],
|
||
trade_receivables: ['1250000000'],
|
||
cash_equivalents: ['1220000000'],
|
||
st_loans_advances: ['1260000000'],
|
||
other_current_assets: ['1280000000','1290000000','1330000000'],
|
||
};
|
||
const DEFAULT_FIXED = { share_capital: 100 }; // in Lakh
|
||
|
||
async function bootstrap() {
|
||
const pool = await getPool();
|
||
await pool.request().query(`
|
||
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='bs_config' AND xtype='U')
|
||
CREATE TABLE bs_config (
|
||
group_key NVARCHAR(50) NOT NULL PRIMARY KEY,
|
||
acct_codes NVARCHAR(MAX) NOT NULL DEFAULT '[]',
|
||
opening_acct_codes NVARCHAR(MAX) NOT NULL DEFAULT '[]',
|
||
fixed_value DECIMAL(18,4) NULL,
|
||
notes NVARCHAR(500) NULL,
|
||
updated_at DATETIME DEFAULT GETDATE()
|
||
)
|
||
`);
|
||
|
||
// Add opening_acct_codes column if upgrading from older schema
|
||
await pool.request().query(`
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM sys.columns
|
||
WHERE name='opening_acct_codes' AND object_id=OBJECT_ID('bs_config')
|
||
)
|
||
ALTER TABLE bs_config ADD opening_acct_codes NVARCHAR(MAX) NOT NULL DEFAULT '[]'
|
||
`);
|
||
|
||
// Seed defaults if empty
|
||
const check = await pool.request().query(`SELECT COUNT(*) AS cnt FROM bs_config`);
|
||
if (check.recordset[0].cnt === 0) {
|
||
for (const item of BS_ITEMS) {
|
||
const codes = JSON.stringify(DEFAULT_ACCOUNTS[item.key] || []);
|
||
const ocodes = JSON.stringify(DEFAULT_OPENING_ACCOUNTS[item.key] || []);
|
||
const fv = DEFAULT_FIXED[item.key] != null ? DEFAULT_FIXED[item.key] : null;
|
||
await pool.request()
|
||
.input('k', item.key).input('c', codes).input('o', ocodes).input('f', fv)
|
||
.query(`INSERT INTO bs_config (group_key,acct_codes,opening_acct_codes,fixed_value) VALUES (@k,@c,@o,@f)`);
|
||
}
|
||
} else {
|
||
// Migration: fill in opening_acct_codes defaults for items that still have '[]'
|
||
for (const [key, defaults] of Object.entries(DEFAULT_OPENING_ACCOUNTS)) {
|
||
await pool.request()
|
||
.input('k', key)
|
||
.input('o', JSON.stringify(defaults))
|
||
.query(`
|
||
UPDATE bs_config
|
||
SET opening_acct_codes = @o, updated_at = GETDATE()
|
||
WHERE group_key = @k
|
||
AND (opening_acct_codes IS NULL OR opening_acct_codes = '[]')
|
||
`);
|
||
}
|
||
// Migration: fill in main acct_codes defaults if still '[]'
|
||
for (const [key, defaults] of Object.entries(DEFAULT_ACCOUNTS)) {
|
||
if (!defaults.length) continue;
|
||
await pool.request()
|
||
.input('k', key)
|
||
.input('c', JSON.stringify(defaults))
|
||
.query(`
|
||
UPDATE bs_config
|
||
SET acct_codes = @c, updated_at = GETDATE()
|
||
WHERE group_key = @k
|
||
AND (acct_codes IS NULL OR acct_codes = '[]')
|
||
`);
|
||
}
|
||
}
|
||
}
|
||
|
||
async function getConfig() {
|
||
const pool = await getPool();
|
||
const r = await pool.request().query(`SELECT group_key,acct_codes,opening_acct_codes,fixed_value,notes FROM bs_config`);
|
||
const map = {};
|
||
r.recordset.forEach(row => {
|
||
map[row.group_key] = {
|
||
acct_codes: JSON.parse(row.acct_codes || '[]'),
|
||
opening_acct_codes: JSON.parse(row.opening_acct_codes || '[]'),
|
||
fixed_value: row.fixed_value != null ? parseFloat(row.fixed_value) : null,
|
||
notes: row.notes || '',
|
||
};
|
||
});
|
||
return map;
|
||
}
|
||
|
||
async function saveConfig(groupKey, acctCodes, openingAcctCodes, fixedValue, notes) {
|
||
const pool = await getPool();
|
||
const codes = JSON.stringify(Array.isArray(acctCodes) ? acctCodes : []);
|
||
const ocodes = JSON.stringify(Array.isArray(openingAcctCodes) ? openingAcctCodes : []);
|
||
const fv = fixedValue != null && fixedValue !== '' ? parseFloat(fixedValue) : null;
|
||
await pool.request()
|
||
.input('k', groupKey).input('c', codes).input('o', ocodes).input('f', fv).input('n', notes || null)
|
||
.query(`
|
||
MERGE bs_config AS T
|
||
USING (SELECT @k AS group_key) AS S ON T.group_key = S.group_key
|
||
WHEN MATCHED THEN UPDATE SET acct_codes=@c, opening_acct_codes=@o, fixed_value=@f, notes=@n, updated_at=GETDATE()
|
||
WHEN NOT MATCHED THEN INSERT (group_key,acct_codes,opening_acct_codes,fixed_value,notes) VALUES (@k,@c,@o,@f,@n);
|
||
`);
|
||
}
|
||
|
||
// Derive a 4-char prefix from a 10-digit parent account code
|
||
// e.g. '2140000000' → trim trailing zeros '214' (length<4) → fallback to slice(0,4) = '2140'
|
||
function acctPrefix(code) {
|
||
const trimmed = String(code).replace(/0+$/, '');
|
||
return trimmed.length >= 4 ? trimmed : String(code).slice(0, 4);
|
||
}
|
||
|
||
// Fetch D-C balance for OACT accounts matching any configured prefix.
|
||
// from = period start (period balance, matching trial balance) OR null (cumulative up to asOf).
|
||
async function fetchGroupBalance(pool, acctCodes, from, asOf) {
|
||
if (!acctCodes || !acctCodes.length) return 0;
|
||
const req = pool.request().input('asOf', asOf);
|
||
if (from) req.input('from', from);
|
||
const conditions = acctCodes.map((c, i) => {
|
||
req.input(`p${i}`, acctPrefix(c) + '%');
|
||
return `T0.[AcctCode] LIKE @p${i}`;
|
||
}).join(' OR ');
|
||
const dateFilter = from
|
||
? `T1.[RefDate] BETWEEN @from AND @asOf`
|
||
: `T1.[RefDate] <= @asOf`;
|
||
|
||
const r = await req.query(`
|
||
SELECT ISNULL(SUM(
|
||
CASE WHEN ${dateFilter}
|
||
THEN ISNULL(T1.[Debit],0) - ISNULL(T1.[Credit],0)
|
||
ELSE 0 END
|
||
), 0) AS balance
|
||
FROM OACT T0
|
||
LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
|
||
WHERE ${conditions}
|
||
`);
|
||
return parseFloat(r.recordset[0]?.balance || 0);
|
||
}
|
||
|
||
// Compute netTax using EXACT same formula as Monthly Accounts P&L Summary frontend.
|
||
// stockDetail: { open: { conv }, close: { conv } } — matches renderSummary's convChange.
|
||
function calcNetTaxFromAccounts(rows, openingStock, closingStock, stockDetail) {
|
||
const S = { Revenue:0, Purchase:0, Employee:0, Factory:0, Admin:0, SND:0, Finance:0, OtherIncome:0 };
|
||
rows.forEach(r => {
|
||
const sheet = r.sheet || '';
|
||
if (S[sheet] === undefined) return;
|
||
const prov = r.override_val != null ? Number(r.override_val) : (r.provision != null ? Number(r.provision) : 0);
|
||
const net = sheet === 'Revenue'
|
||
? (Number(r.period_credit)||0) - (Number(r.period_debit)||0) + prov
|
||
: (Number(r.period_debit)||0) - (Number(r.period_credit)||0) + prov;
|
||
S[sheet] += net;
|
||
});
|
||
const matCost = (Number(openingStock)||0) + S.Purchase - (Number(closingStock)||0);
|
||
// convChange mirrors renderSummary: openConv - closeConv (change in conversion/WIP cost)
|
||
const openConv = Number(stockDetail?.open?.conv) || 0;
|
||
const closeConv = Number(stockDetail?.close?.conv) || 0;
|
||
const convChange = openConv - closeConv;
|
||
const grossP = S.Revenue - (matCost + convChange);
|
||
const totalExp = S.Employee + S.Factory + S.Admin + S.SND + S.Finance;
|
||
const netOps = grossP - totalExp;
|
||
const oiT = -S.OtherIncome;
|
||
return netOps + oiT;
|
||
}
|
||
|
||
// Fetch net profit from snapshot: parses the saved JSON using the same formula.
|
||
// `pool` (kept for call-site compatibility) is the SAP pool the caller
|
||
// already has for its other queries — pl_snapshots itself now lives on the
|
||
// app DB, so this reads it via costingStore.getSnapshot() instead.
|
||
async function fetchNetProfitFromSnapshot(pool, snapshotId) {
|
||
try {
|
||
const cs = require('./costingStore');
|
||
const snap = await cs.getSnapshot(parseInt(snapshotId));
|
||
if (!snap) return 0;
|
||
const parsed = JSON.parse(snap.data_json);
|
||
const accounts = parsed.accounts || parsed;
|
||
const openingStock = parsed.opening_stock != null ? parsed.opening_stock : 0;
|
||
const closingStock = parsed.closing_stock != null ? parsed.closing_stock : 0;
|
||
const stockDetail = parsed.stock_detail || null;
|
||
return calcNetTaxFromAccounts(accounts, openingStock, closingStock, stockDetail);
|
||
} catch(e) {
|
||
console.error('[fetchNetProfitFromSnapshot]', e.message);
|
||
return 0;
|
||
}
|
||
}
|
||
|
||
// Fetch P&L net profit from live database using the same formula as Monthly Accounts
|
||
async function fetchNetProfit(pool, from, to) {
|
||
try {
|
||
const cs = require('./costingStore');
|
||
const [tb, mapping, overrides] = await Promise.all([
|
||
cs.fetchTrialBalance(from, to),
|
||
cs.getMapping(),
|
||
cs.getOverrides(from, to),
|
||
]);
|
||
const mapByCode = {}; mapping.forEach(m => { mapByCode[m.acct_code] = m; });
|
||
const ovByCode = {}; overrides.forEach(o => { ovByCode[o.acct_code] = o; });
|
||
|
||
// Build account rows in the same format as the snapshot/frontend
|
||
const rows = tb.map(row => ({
|
||
sheet: mapByCode[row.acct_code]?.sheet ?? (row.group_mask === 4 ? 'Revenue' : 'Other'),
|
||
period_debit: Number(row.period_debit) || 0,
|
||
period_credit: Number(row.period_credit) || 0,
|
||
override_val: ovByCode[row.acct_code]?.override_val ?? null,
|
||
}));
|
||
|
||
const stockOpen = Number(ovByCode['__STOCK_OPEN__']?.override_val) || 0;
|
||
const stockClose = Number(ovByCode['__STOCK_CLOSE__']?.override_val) || 0;
|
||
const stockDetail = {
|
||
open: { conv: Number(ovByCode['__OPEN_CONV__']?.override_val) || 0 },
|
||
close: { conv: Number(ovByCode['__CLOSE_CONV__']?.override_val) || 0 },
|
||
};
|
||
return calcNetTaxFromAccounts(rows, stockOpen, stockClose, stockDetail);
|
||
} catch(_) { return 0; }
|
||
}
|
||
|
||
// Return date string one day before the given date (for opening balance query)
|
||
function dayBefore(dateStr) {
|
||
const d = new Date(dateStr + 'T00:00:00Z');
|
||
d.setUTCDate(d.getUTCDate() - 1);
|
||
return d.toISOString().slice(0, 10);
|
||
}
|
||
|
||
// Gross opening stock = RM + WIP + FG + Trading (no conv deduction), falls back to __STOCK_OPEN__
|
||
function getGrossOpeningStock(overrides) {
|
||
const get = key => parseFloat(overrides.find(o => o.acct_code === key)?.override_val || 0) || 0;
|
||
const gross = get('__OPEN_RM__') + get('__OPEN_WIP__') + get('__OPEN_FG__') + get('__OPEN_TRADE__');
|
||
return gross > 0 ? gross : get('__STOCK_OPEN__');
|
||
}
|
||
|
||
// ── Reserves & Surplus formula:
|
||
// Total Opening = opening_balance(5100001001) − gross opening stock
|
||
// Reserves&Surplus = −(balance(2120020001, asOf) + Total Opening)
|
||
async function fetchReservesSurplus(pool, mainAcctCodes, openingAcctCodes, from, to, asOf) {
|
||
try {
|
||
const cs = require('./costingStore');
|
||
|
||
// 1. Main account cumulative balance up to BS date
|
||
const mainBalance = await fetchGroupBalance(pool, mainAcctCodes, null, asOf);
|
||
|
||
// 2. Opening account cumulative balance strictly before the period start
|
||
const openingBalance = openingAcctCodes.length
|
||
? await fetchGroupBalance(pool, openingAcctCodes, null, dayBefore(from))
|
||
: 0;
|
||
|
||
// 3. Gross opening stock: RM + WIP + FG + Trading (no conv deduction)
|
||
const overrides = await cs.getOverrides(from, to);
|
||
const openingStock = getGrossOpeningStock(overrides);
|
||
|
||
// Total Opening = opening_balance(5100001001) − gross opening stock
|
||
const totalOpening = openingBalance - openingStock;
|
||
|
||
// Reserves & Surplus = −(mainBalance + Total Opening)
|
||
return -(mainBalance + totalOpening);
|
||
} catch(e) {
|
||
console.error('[fetchReservesSurplus]', e.message);
|
||
return 0;
|
||
}
|
||
}
|
||
|
||
// Same as above but returns intermediate values for debugging
|
||
async function fetchReservesSurplusDebug(pool, mainAcctCodes, openingAcctCodes, from, to, asOf) {
|
||
const cs = require('./costingStore');
|
||
const mainBalance = await fetchGroupBalance(pool, mainAcctCodes, null, asOf);
|
||
const openingBalance = openingAcctCodes.length
|
||
? await fetchGroupBalance(pool, openingAcctCodes, null, dayBefore(from))
|
||
: 0;
|
||
const overrides = await cs.getOverrides(from, to);
|
||
const openingStock = getGrossOpeningStock(overrides);
|
||
const totalOpening = openingBalance - openingStock;
|
||
const result = -(mainBalance + totalOpening);
|
||
return {
|
||
result,
|
||
mainAcctCodes,
|
||
openingAcctCodes,
|
||
mainBalance, // D-C for 2120020001 up to asOf
|
||
openingBalance, // D-C for 5100001001 up to dayBefore(from)
|
||
openingStock, // gross opening stock (RM+WIP+FG+Trading)
|
||
totalOpening, // openingBalance - openingStock
|
||
formula: `-(${mainBalance} + (${openingBalance} - ${openingStock})) = ${result}`,
|
||
dayBeforeFrom: dayBefore(from),
|
||
};
|
||
}
|
||
|
||
// Fetch closing stock from pl_overrides — gross total (RM + WIP + FG + Trading, no conv deduction)
|
||
async function fetchClosingStock(pool, from, to) {
|
||
try {
|
||
const cs = require('./costingStore');
|
||
const overrides = await cs.getOverrides(from, to);
|
||
const get = key => parseFloat(overrides.find(o => o.acct_code === key)?.override_val || 0) || 0;
|
||
const rm = get('__CLOSE_RM__');
|
||
const wip = get('__CLOSE_WIP__');
|
||
const fg = get('__CLOSE_FG__');
|
||
const trade = get('__CLOSE_TRADE__');
|
||
const gross = rm + wip + fg + trade;
|
||
// Fall back to __STOCK_CLOSE__ if individual components not yet saved
|
||
if (gross === 0) return get('__STOCK_CLOSE__');
|
||
return gross;
|
||
} catch(_) { return 0; }
|
||
}
|
||
|
||
// Fetch total provisions (sum of all non-stock pl_overrides)
|
||
async function fetchTotalProvisions(pool, from, to) {
|
||
try {
|
||
const cs = require('./costingStore');
|
||
const overrides = await cs.getOverrides(from, to);
|
||
return overrides
|
||
.filter(o => !o.acct_code.startsWith('__'))
|
||
.reduce((s, o) => s + (parseFloat(o.override_val) || 0), 0);
|
||
} catch(_) { return 0; }
|
||
}
|
||
|
||
module.exports = { bootstrap, BS_ITEMS, getConfig, saveConfig, fetchGroupBalance, fetchReservesSurplus, fetchReservesSurplusDebug, fetchNetProfit, fetchNetProfitFromSnapshot, fetchClosingStock, fetchTotalProvisions };
|