292 lines
11 KiB
JavaScript
292 lines
11 KiB
JavaScript
'use strict';
|
|
// This file straddles two databases: fetchTrialBalance/fetchAllTrialBalance
|
|
// read real SAP ledger data (OACT/JDT1/OJDT) via sapPool; everything else
|
|
// (pl_account_map, pl_overrides, pl_snapshots — this app's own config/cache
|
|
// tables) lives on the app's own database via getPool (appSqlPool).
|
|
// getAllOverrides is the one spot that used to JOIN an app table with OACT
|
|
// in a single query — now split into two queries + a JS-side merge, since
|
|
// the two tables can no longer live on the same server.
|
|
const { getPool } = require('./appSqlPool');
|
|
const { getPool: getSapPool } = require('./sqlPool');
|
|
|
|
const SHEETS = ['Revenue', 'Purchase', 'Employee', 'Factory', 'Admin', 'SND', 'Finance', 'Other'];
|
|
|
|
async function bootstrap() {
|
|
const pool = await getPool();
|
|
|
|
await pool.request().query(`
|
|
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='pl_account_map' AND xtype='U')
|
|
CREATE TABLE pl_account_map (
|
|
acct_code NVARCHAR(50) NOT NULL PRIMARY KEY,
|
|
sheet NVARCHAR(30) NOT NULL DEFAULT 'Other',
|
|
sort_order INT NOT NULL DEFAULT 999,
|
|
head NVARCHAR(100) NOT NULL DEFAULT '',
|
|
updated_at DATETIME DEFAULT GETDATE()
|
|
)
|
|
`);
|
|
|
|
await pool.request().query(`
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM sys.columns
|
|
WHERE name='head' AND object_id=OBJECT_ID('pl_account_map')
|
|
)
|
|
ALTER TABLE pl_account_map ADD head NVARCHAR(100) NOT NULL DEFAULT ''
|
|
`);
|
|
|
|
await pool.request().query(`
|
|
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='pl_overrides' AND xtype='U')
|
|
CREATE TABLE pl_overrides (
|
|
id INT IDENTITY(1,1) PRIMARY KEY,
|
|
acct_code NVARCHAR(50) NOT NULL,
|
|
period_from NVARCHAR(10) NOT NULL,
|
|
period_to NVARCHAR(10) NOT NULL,
|
|
override_val DECIMAL(18,2) NULL,
|
|
note NVARCHAR(500) NULL,
|
|
updated_by NVARCHAR(100) NULL,
|
|
updated_at DATETIME DEFAULT GETDATE()
|
|
)
|
|
`);
|
|
|
|
await pool.request().query(`
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM sys.indexes
|
|
WHERE name='UQ_pl_overrides_code_period' AND object_id=OBJECT_ID('pl_overrides')
|
|
)
|
|
ALTER TABLE pl_overrides
|
|
ADD CONSTRAINT UQ_pl_overrides_code_period UNIQUE (acct_code, period_from, period_to)
|
|
`);
|
|
}
|
|
|
|
async function fetchTrialBalance(fromDate, toDate) {
|
|
const pool = await getSapPool();
|
|
const r = await pool.request()
|
|
.input('fromDate', fromDate)
|
|
.input('toDate', toDate)
|
|
.query(`
|
|
WITH base AS (
|
|
SELECT
|
|
T0.[AcctCode] AS acct_code,
|
|
T0.[AcctName] AS acct_name,
|
|
T0.[GroupMask] AS group_mask,
|
|
SUM(CASE WHEN T1.[RefDate] < @fromDate
|
|
THEN ISNULL(T1.[Debit],0) - ISNULL(T1.[Credit],0) ELSE 0 END) AS opening_balance,
|
|
SUM(CASE WHEN T1.[RefDate] BETWEEN @fromDate AND @toDate
|
|
THEN ISNULL(T1.[Debit],0) ELSE 0 END) AS period_debit,
|
|
SUM(CASE WHEN T1.[RefDate] BETWEEN @fromDate AND @toDate
|
|
THEN ISNULL(T1.[Credit],0) ELSE 0 END) AS period_credit
|
|
FROM OACT T0
|
|
LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
|
|
LEFT JOIN OJDT T2 ON T1.[TransId] = T2.[TransId]
|
|
WHERE T0.[GroupMask] IN (4, 5)
|
|
GROUP BY T0.[AcctCode], T0.[AcctName], T0.[GroupMask]
|
|
HAVING SUM(ISNULL(T1.[Debit],0)) <> 0 OR SUM(ISNULL(T1.[Credit],0)) <> 0
|
|
)
|
|
SELECT
|
|
acct_code, acct_name, group_mask,
|
|
opening_balance,
|
|
period_debit,
|
|
period_credit,
|
|
opening_balance + period_debit - period_credit AS closing_balance
|
|
FROM base
|
|
ORDER BY group_mask, acct_code
|
|
`);
|
|
return r.recordset || [];
|
|
}
|
|
|
|
async function fetchAllTrialBalance(fromDate, toDate) {
|
|
const pool = await getSapPool();
|
|
const r = await pool.request()
|
|
.input('fromDate', fromDate)
|
|
.input('toDate', toDate)
|
|
.query(`
|
|
WITH base AS (
|
|
SELECT
|
|
T0.[AcctCode] AS acct_code,
|
|
T0.[AcctName] AS acct_name,
|
|
T0.[GroupMask] AS group_mask,
|
|
SUM(CASE WHEN T1.[RefDate] < @fromDate
|
|
THEN ISNULL(T1.[Debit],0) - ISNULL(T1.[Credit],0) ELSE 0 END) AS opening_balance,
|
|
SUM(CASE WHEN T1.[RefDate] BETWEEN @fromDate AND @toDate
|
|
THEN ISNULL(T1.[Debit],0) ELSE 0 END) AS period_debit,
|
|
SUM(CASE WHEN T1.[RefDate] BETWEEN @fromDate AND @toDate
|
|
THEN ISNULL(T1.[Credit],0) ELSE 0 END) AS period_credit
|
|
FROM OACT T0
|
|
LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
|
|
LEFT JOIN OJDT T2 ON T1.[TransId] = T2.[TransId]
|
|
GROUP BY T0.[AcctCode], T0.[AcctName], T0.[GroupMask]
|
|
HAVING SUM(ISNULL(T1.[Debit],0)) <> 0 OR SUM(ISNULL(T1.[Credit],0)) <> 0
|
|
)
|
|
SELECT
|
|
acct_code, acct_name, group_mask,
|
|
opening_balance,
|
|
period_debit,
|
|
period_credit,
|
|
opening_balance + period_debit - period_credit AS closing_balance
|
|
FROM base
|
|
ORDER BY group_mask, acct_code
|
|
`);
|
|
return r.recordset || [];
|
|
}
|
|
|
|
async function getMapping() {
|
|
const pool = await getPool();
|
|
const r = await pool.request().query(
|
|
`SELECT acct_code, sheet, sort_order, head FROM pl_account_map ORDER BY sort_order, acct_code`
|
|
);
|
|
return r.recordset || [];
|
|
}
|
|
|
|
async function saveMapping(entries) {
|
|
const pool = await getPool();
|
|
for (const e of entries) {
|
|
await pool.request()
|
|
.input('code', e.acct_code)
|
|
.input('sheet', e.sheet || 'Other')
|
|
.input('ord', e.sort_order != null ? e.sort_order : 999)
|
|
.input('head', e.head || '')
|
|
.query(`
|
|
IF EXISTS (SELECT 1 FROM pl_account_map WHERE acct_code=@code)
|
|
UPDATE pl_account_map
|
|
SET sheet=@sheet, sort_order=@ord, head=@head, updated_at=GETDATE()
|
|
WHERE acct_code=@code
|
|
ELSE
|
|
INSERT INTO pl_account_map (acct_code, sheet, sort_order, head)
|
|
VALUES (@code, @sheet, @ord, @head)
|
|
`);
|
|
}
|
|
}
|
|
|
|
async function getOverrides(fromDate, toDate) {
|
|
const pool = await getPool();
|
|
const r = await pool.request()
|
|
.input('from', fromDate)
|
|
.input('to', toDate)
|
|
.query(`
|
|
SELECT acct_code, override_val, note
|
|
FROM pl_overrides
|
|
WHERE period_from=@from AND period_to=@to
|
|
`);
|
|
return r.recordset || [];
|
|
}
|
|
|
|
// All non-null, non-stock provisions across every period, with account names —
|
|
// used for the month-wise Provision Summary tab. pl_overrides/pl_account_map
|
|
// live on the app DB, AcctName comes from OACT on the SAP DB — different
|
|
// servers now, so this is two queries + a JS-side merge instead of one JOIN.
|
|
async function getAllOverrides() {
|
|
const pool = await getPool();
|
|
const r = await pool.request().query(`
|
|
SELECT po.acct_code, po.period_from, po.period_to, po.override_val, po.note,
|
|
po.updated_by, po.updated_at,
|
|
pam.sheet AS sheet, pam.head AS head
|
|
FROM pl_overrides po
|
|
LEFT JOIN pl_account_map pam ON pam.acct_code = po.acct_code
|
|
WHERE po.override_val IS NOT NULL
|
|
AND po.acct_code NOT LIKE '[_][_]%'
|
|
ORDER BY po.period_from, po.acct_code
|
|
`);
|
|
const rows = r.recordset || [];
|
|
const codes = [...new Set(rows.map(row => row.acct_code))];
|
|
const nameByCode = {};
|
|
if (codes.length) {
|
|
const sapPool = await getSapPool();
|
|
const req = sapPool.request();
|
|
const inList = codes.map((c, i) => { req.input(`c${i}`, c); return `@c${i}`; }).join(',');
|
|
const oa = await req.query(`SELECT AcctCode, AcctName FROM OACT WHERE AcctCode IN (${inList})`);
|
|
(oa.recordset || []).forEach(row => { nameByCode[row.AcctCode] = row.AcctName; });
|
|
}
|
|
return rows.map(row => ({ ...row, acct_name: nameByCode[row.acct_code] || null }));
|
|
}
|
|
|
|
async function deleteOverride(acctCode, fromDate, toDate) {
|
|
const pool = await getPool();
|
|
await pool.request()
|
|
.input('code', acctCode)
|
|
.input('from', fromDate)
|
|
.input('to', toDate)
|
|
.query(`DELETE FROM pl_overrides WHERE acct_code=@code AND period_from=@from AND period_to=@to`);
|
|
}
|
|
|
|
async function saveOverride(acctCode, fromDate, toDate, overrideVal, note, updatedBy) {
|
|
const pool = await getPool();
|
|
const val = (overrideVal !== null && overrideVal !== undefined && overrideVal !== '')
|
|
? parseFloat(overrideVal) : null;
|
|
await pool.request()
|
|
.input('code', acctCode)
|
|
.input('from', fromDate)
|
|
.input('to', toDate)
|
|
.input('val', val)
|
|
.input('note', note || null)
|
|
.input('by', updatedBy || null)
|
|
.query(`
|
|
IF EXISTS (
|
|
SELECT 1 FROM pl_overrides
|
|
WHERE acct_code=@code AND period_from=@from AND period_to=@to
|
|
)
|
|
UPDATE pl_overrides
|
|
SET override_val=@val, note=@note, updated_by=@by, updated_at=GETDATE()
|
|
WHERE acct_code=@code AND period_from=@from AND period_to=@to
|
|
ELSE
|
|
INSERT INTO pl_overrides (acct_code, period_from, period_to, override_val, note, updated_by)
|
|
VALUES (@code, @from, @to, @val, @note, @by)
|
|
`);
|
|
}
|
|
|
|
// ── Snapshots ─────────────────────────────────────────────────────────────────
|
|
async function bootstrapSnapshots() {
|
|
const pool = await getPool();
|
|
await pool.request().query(`
|
|
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='pl_snapshots' AND xtype='U')
|
|
CREATE TABLE pl_snapshots (
|
|
id INT IDENTITY(1,1) PRIMARY KEY,
|
|
period_from NVARCHAR(10) NOT NULL,
|
|
period_to NVARCHAR(10) NOT NULL,
|
|
snap_name NVARCHAR(200) NOT NULL DEFAULT '',
|
|
data_json NVARCHAR(MAX) NOT NULL,
|
|
saved_by NVARCHAR(100) NULL,
|
|
saved_at DATETIME DEFAULT GETDATE()
|
|
)
|
|
`);
|
|
}
|
|
|
|
async function saveSnapshot(periodFrom, periodTo, snapName, dataJson, savedBy) {
|
|
const pool = await getPool();
|
|
await pool.request()
|
|
.input('from', periodFrom)
|
|
.input('to', periodTo)
|
|
.input('name', snapName || '')
|
|
.input('data', dataJson)
|
|
.input('by', savedBy || null)
|
|
.query(`
|
|
INSERT INTO pl_snapshots (period_from, period_to, snap_name, data_json, saved_by)
|
|
VALUES (@from, @to, @name, @data, @by)
|
|
`);
|
|
}
|
|
|
|
async function getSnapshots() {
|
|
const pool = await getPool();
|
|
const r = await pool.request().query(`
|
|
SELECT id, period_from, period_to, snap_name, saved_by, saved_at
|
|
FROM pl_snapshots ORDER BY saved_at DESC
|
|
`);
|
|
return r.recordset || [];
|
|
}
|
|
|
|
async function getSnapshot(id) {
|
|
const pool = await getPool();
|
|
const r = await pool.request()
|
|
.input('id', id)
|
|
.query(`SELECT data_json FROM pl_snapshots WHERE id=@id`);
|
|
return r.recordset[0] || null;
|
|
}
|
|
|
|
async function deleteSnapshot(id) {
|
|
const pool = await getPool();
|
|
await pool.request().input('id', id).query(`DELETE FROM pl_snapshots WHERE id=@id`);
|
|
}
|
|
|
|
module.exports = {
|
|
bootstrap, fetchTrialBalance, fetchAllTrialBalance, getMapping, saveMapping, getOverrides, getAllOverrides, saveOverride, deleteOverride, SHEETS,
|
|
bootstrapSnapshots, saveSnapshot, getSnapshots, getSnapshot, deleteSnapshot
|
|
};
|