160 lines
6.4 KiB
JavaScript
160 lines
6.4 KiB
JavaScript
'use strict';
|
||
// cf_items (this store's own config table) lives on the app's own database —
|
||
// only fetchItemValue/fetchItemClosingBalance below touch real SAP data
|
||
// (OACT/JDT1), and those receive their pool as a parameter from the caller
|
||
// (routes/cashFlow.js, which imports the SAP pool directly), so they're
|
||
// unaffected by this import.
|
||
const { getPool } = require('./appSqlPool');
|
||
|
||
async function bootstrap() {
|
||
const pool = await getPool();
|
||
await pool.request().query(`
|
||
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='cf_items' AND xtype='U')
|
||
CREATE TABLE cf_items (
|
||
id INT IDENTITY(1,1) PRIMARY KEY,
|
||
section NVARCHAR(20) NOT NULL DEFAULT 'Assets',
|
||
item_type NVARCHAR(20) NOT NULL DEFAULT 'working',
|
||
sort_order INT NOT NULL DEFAULT 999,
|
||
label NVARCHAR(200) NOT NULL,
|
||
acct_codes NVARCHAR(MAX) NOT NULL DEFAULT '[]',
|
||
notes NVARCHAR(500) NULL,
|
||
updated_at DATETIME DEFAULT GETDATE()
|
||
)
|
||
`);
|
||
// Migration: add item_type if upgrading
|
||
await pool.request().query(`
|
||
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE name='item_type' AND object_id=OBJECT_ID('cf_items'))
|
||
ALTER TABLE cf_items ADD item_type NVARCHAR(20) NOT NULL DEFAULT 'working'
|
||
`);
|
||
// Migration: rename 'capital' item_type items to section='Capital' if they are in Assets section
|
||
await pool.request().query(`
|
||
UPDATE cf_items SET section='Capital' WHERE section='Assets' AND item_type='capital'
|
||
`);
|
||
}
|
||
|
||
async function getItems() {
|
||
const pool = await getPool();
|
||
const r = await pool.request().query(
|
||
`SELECT id, section, item_type, sort_order, label, acct_codes, notes
|
||
FROM cf_items ORDER BY
|
||
CASE section WHEN 'Assets' THEN 1 WHEN 'Liabilities' THEN 2 WHEN 'Capital' THEN 3 WHEN 'Cash' THEN 4 ELSE 5 END,
|
||
sort_order, id`
|
||
);
|
||
return r.recordset.map(row => ({
|
||
id: row.id,
|
||
section: row.section,
|
||
item_type: row.item_type || 'working',
|
||
sort_order: row.sort_order,
|
||
label: row.label,
|
||
acct_codes: JSON.parse(row.acct_codes || '[]'),
|
||
notes: row.notes || '',
|
||
}));
|
||
}
|
||
|
||
async function saveItem({ id, section, item_type, label, acct_codes, sort_order, notes }) {
|
||
const pool = await getPool();
|
||
const codes = JSON.stringify(Array.isArray(acct_codes) ? acct_codes : []);
|
||
const ord = sort_order != null ? parseInt(sort_order) : 999;
|
||
const type = item_type || 'working';
|
||
if (id) {
|
||
await pool.request()
|
||
.input('id', id).input('sec', section).input('typ', type)
|
||
.input('lbl', label).input('c', codes).input('ord', ord).input('n', notes || null)
|
||
.query(`UPDATE cf_items SET section=@sec, item_type=@typ, label=@lbl, acct_codes=@c, sort_order=@ord, notes=@n, updated_at=GETDATE() WHERE id=@id`);
|
||
} else {
|
||
await pool.request()
|
||
.input('sec', section).input('typ', type)
|
||
.input('lbl', label).input('c', codes).input('ord', ord).input('n', notes || null)
|
||
.query(`INSERT INTO cf_items (section, item_type, label, acct_codes, sort_order, notes) VALUES (@sec, @typ, @lbl, @c, @ord, @n)`);
|
||
}
|
||
}
|
||
|
||
async function deleteItem(id) {
|
||
const pool = await getPool();
|
||
await pool.request().input('id', id).query(`DELETE FROM cf_items WHERE id=@id`);
|
||
}
|
||
|
||
// Derive 4-char prefix for LIKE matching (same logic as balanceSheetStore)
|
||
function acctPrefix(code) {
|
||
const trimmed = String(code).replace(/0+$/, '');
|
||
return trimmed.length >= 4 ? trimmed : String(code).slice(0, 4);
|
||
}
|
||
|
||
// Period net change from live DB (Debit − Credit for the date range)
|
||
async function fetchItemValue(pool, acctCodes, from, to) {
|
||
if (!acctCodes || !acctCodes.length) return 0;
|
||
const req = pool.request().input('from', from).input('to', to);
|
||
const conditions = acctCodes.map((c, i) => {
|
||
req.input(`p${i}`, acctPrefix(c) + '%');
|
||
return `T0.[AcctCode] LIKE @p${i}`;
|
||
}).join(' OR ');
|
||
const r = await req.query(`
|
||
SELECT ISNULL(SUM(
|
||
CASE WHEN T1.[RefDate] BETWEEN @from AND @to
|
||
THEN ISNULL(T1.[Debit],0) - ISNULL(T1.[Credit],0)
|
||
ELSE 0 END
|
||
), 0) AS net_change
|
||
FROM OACT T0
|
||
LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
|
||
WHERE ${conditions}
|
||
`);
|
||
return parseFloat(r.recordset[0]?.net_change || 0);
|
||
}
|
||
|
||
// Closing balance from live DB — cumulative Debit − Credit up to asOf (= 'to' date)
|
||
async function fetchItemClosingBalance(pool, acctCodes, asOf) {
|
||
if (!acctCodes || !acctCodes.length) return 0;
|
||
const req = pool.request().input('asOf', asOf);
|
||
const conditions = acctCodes.map((c, i) => {
|
||
req.input(`p${i}`, acctPrefix(c) + '%');
|
||
return `T0.[AcctCode] LIKE @p${i}`;
|
||
}).join(' OR ');
|
||
const r = await req.query(`
|
||
SELECT ISNULL(SUM(
|
||
CASE WHEN T1.[RefDate] <= @asOf
|
||
THEN ISNULL(T1.[Debit],0) - ISNULL(T1.[Credit],0)
|
||
ELSE 0 END
|
||
), 0) AS closing_balance
|
||
FROM OACT T0
|
||
LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
|
||
WHERE ${conditions}
|
||
`);
|
||
return parseFloat(r.recordset[0]?.closing_balance || 0);
|
||
}
|
||
|
||
// Period net change from snapshot accounts array
|
||
function fetchItemValueFromSnapshot(accounts, acctCodes) {
|
||
if (!acctCodes || !acctCodes.length) return 0;
|
||
const prefixes = acctCodes.map(c => acctPrefix(c));
|
||
return accounts
|
||
.filter(a => prefixes.some(p => String(a.acct_code).startsWith(p)))
|
||
.reduce((s, a) => s + ((Number(a.period_debit) || 0) - (Number(a.period_credit) || 0)), 0);
|
||
}
|
||
|
||
// Closing balance from snapshot: opening_balance + period_debit − period_credit
|
||
function fetchItemClosingBalanceFromSnapshot(accounts, acctCodes) {
|
||
if (!acctCodes || !acctCodes.length) return 0;
|
||
const prefixes = acctCodes.map(c => acctPrefix(c));
|
||
return accounts
|
||
.filter(a => prefixes.some(p => String(a.acct_code).startsWith(p)))
|
||
.reduce((s, a) =>
|
||
s + (Number(a.opening_balance) || 0) + (Number(a.period_debit) || 0) - (Number(a.period_credit) || 0), 0);
|
||
}
|
||
|
||
// Opening balance from snapshot: the opening_balance field (before period start)
|
||
function fetchOpeningBalanceFromSnapshot(accounts, acctCodes) {
|
||
if (!acctCodes || !acctCodes.length) return 0;
|
||
const prefixes = acctCodes.map(c => acctPrefix(c));
|
||
return accounts
|
||
.filter(a => prefixes.some(p => String(a.acct_code).startsWith(p)))
|
||
.reduce((s, a) => s + (Number(a.opening_balance) || 0), 0);
|
||
}
|
||
|
||
module.exports = {
|
||
bootstrap, getItems, saveItem, deleteItem,
|
||
fetchItemValue, fetchItemClosingBalance,
|
||
fetchItemValueFromSnapshot, fetchItemClosingBalanceFromSnapshot,
|
||
fetchOpeningBalanceFromSnapshot,
|
||
acctPrefix,
|
||
};
|