294 lines
12 KiB
JavaScript
294 lines
12 KiB
JavaScript
const { all, audit, now, one, parseJson, run, tx } = require('../db');
|
|
|
|
// Stored, editable general-ledger journal entries per book/company. Independent of the computed
|
|
// depreciation rollup (bookLedger) — this is a manual/imported journal layer. Every change is logged
|
|
// through audit() with full before/after JSON for the Activity & Audit Trail.
|
|
|
|
function round2(value) {
|
|
return Math.round((Number(value || 0) + Number.EPSILON) * 100) / 100;
|
|
}
|
|
|
|
function num(value) {
|
|
if (value === '' || value === null || value === undefined) return 0;
|
|
const n = Number(String(value).replace(/[$,]/g, ''));
|
|
return Number.isFinite(n) ? n : 0;
|
|
}
|
|
|
|
function strOrNull(value) {
|
|
const s = value === null || value === undefined ? '' : String(value).trim();
|
|
return s || null;
|
|
}
|
|
|
|
function yearOf(dateStr) {
|
|
return dateStr ? Number(String(dateStr).slice(0, 4)) || null : null;
|
|
}
|
|
|
|
function entryFromRow(row) {
|
|
return { ...row };
|
|
}
|
|
|
|
// ---- Read -------------------------------------------------------------------
|
|
|
|
function listEntries(filters = {}, companyId) {
|
|
const entries = all(
|
|
`SELECT g.*, a.asset_id AS asset_code
|
|
FROM gl_entries g
|
|
LEFT JOIN assets a ON a.id = g.asset_id
|
|
WHERE g.entity_id = ? AND g.book_code = ?
|
|
AND (? IS NULL OR g.fiscal_year = ?)
|
|
AND (? IS NULL OR g.account = ?)
|
|
AND (? IS NULL OR (g.account LIKE '%' || ? || '%' OR g.description LIKE '%' || ? || '%' OR g.reference LIKE '%' || ? || '%'))
|
|
ORDER BY g.entry_date DESC, g.id DESC
|
|
LIMIT 2000`,
|
|
[
|
|
companyId, filters.book,
|
|
filters.year || null, filters.year || null,
|
|
filters.account || null, filters.account || null,
|
|
filters.search || null, filters.search || null, filters.search || null, filters.search || null
|
|
]
|
|
).map(entryFromRow);
|
|
const debit = round2(entries.reduce((s, e) => s + Number(e.debit || 0), 0));
|
|
const credit = round2(entries.reduce((s, e) => s + Number(e.credit || 0), 0));
|
|
return { entries, summary: { count: entries.length, debit, credit, balance: round2(debit - credit) } };
|
|
}
|
|
|
|
function getEntry(id, companyId) {
|
|
return one('SELECT * FROM gl_entries WHERE id = ? AND entity_id = ?', [id, companyId]) || null;
|
|
}
|
|
|
|
// ---- Normalize / validate ---------------------------------------------------
|
|
|
|
// Tolerant field picker: matches a parsed row's keys case/space/underscore-insensitively.
|
|
function pick(raw, ...aliases) {
|
|
const map = {};
|
|
for (const key of Object.keys(raw || {})) map[key.toLowerCase().replace(/[\s_]+/g, '')] = raw[key];
|
|
for (const alias of aliases) {
|
|
const value = map[alias.toLowerCase().replace(/[\s_]+/g, '')];
|
|
if (value !== undefined && value !== null && value !== '') return value;
|
|
}
|
|
return undefined;
|
|
}
|
|
|
|
// Map a raw import row (tolerant headers) to the normalized GL entry shape.
|
|
function normalizeRow(raw) {
|
|
const entry_date = String(pick(raw, 'entry_date', 'date', 'posting_date', 'posted') || '').slice(0, 10);
|
|
return {
|
|
entry_date,
|
|
fiscal_year: yearOf(entry_date),
|
|
account: String(pick(raw, 'account', 'gl_account', 'account_number', 'acct') || '').trim(),
|
|
account_type: strOrNull(pick(raw, 'account_type', 'type', 'account_name')),
|
|
description: strOrNull(pick(raw, 'description', 'memo', 'desc', 'narrative')),
|
|
reference: strOrNull(pick(raw, 'reference', 'ref', 'document', 'doc')),
|
|
debit: round2(num(pick(raw, 'debit', 'dr'))),
|
|
credit: round2(num(pick(raw, 'credit', 'cr'))),
|
|
external_id: strOrNull(pick(raw, 'external_id', 'id', 'gl_id', 'entry_id'))
|
|
};
|
|
}
|
|
|
|
function entryError(entry) {
|
|
if (!entry.account) return 'Missing account';
|
|
if (!entry.entry_date) return 'Missing entry date';
|
|
if (round2(entry.debit) === 0 && round2(entry.credit) === 0) return 'Debit or credit is required';
|
|
return null;
|
|
}
|
|
|
|
// Validate + coerce a normalized entry coming from the CRUD API.
|
|
function validateEntry(body) {
|
|
const entry = {
|
|
entry_date: String(body.entry_date || '').slice(0, 10),
|
|
account: String(body.account || '').trim(),
|
|
account_type: strOrNull(body.account_type),
|
|
description: strOrNull(body.description),
|
|
reference: strOrNull(body.reference),
|
|
debit: round2(num(body.debit)),
|
|
credit: round2(num(body.credit)),
|
|
external_id: strOrNull(body.external_id),
|
|
asset_id: body.asset_id ? Number(body.asset_id) : null
|
|
};
|
|
entry.fiscal_year = yearOf(entry.entry_date);
|
|
const err = entryError(entry);
|
|
if (err) throw Object.assign(new Error(err), { status: 400 });
|
|
return entry;
|
|
}
|
|
|
|
// ---- Write (CRUD) -----------------------------------------------------------
|
|
|
|
function insertEntry(entry, book, userId, companyId, source) {
|
|
const ts = now();
|
|
const result = run(
|
|
`INSERT INTO gl_entries (entity_id, book_code, entry_date, fiscal_year, account, account_type, description, reference, debit, credit, asset_id, external_id, source, created_by, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
|
[
|
|
companyId, book, entry.entry_date, entry.fiscal_year, entry.account, entry.account_type,
|
|
entry.description, entry.reference, entry.debit, entry.credit, entry.asset_id || null,
|
|
entry.external_id, source || 'manual', userId, ts, ts
|
|
]
|
|
);
|
|
const created = getEntry(result.lastInsertRowid, companyId);
|
|
audit(userId, 'create', 'gl_entry', result.lastInsertRowid, null, created);
|
|
return created;
|
|
}
|
|
|
|
function createEntry(book, body, userId, companyId) {
|
|
const entry = validateEntry(body);
|
|
require('./closePeriods').assertPeriodOpen(companyId, book, entry.entry_date);
|
|
return insertEntry(entry, book, userId, companyId, 'manual');
|
|
}
|
|
|
|
function updateEntry(id, body, userId, companyId) {
|
|
const before = getEntry(id, companyId);
|
|
if (!before) return null;
|
|
const entry = validateEntry({ ...before, ...body });
|
|
// Block edits dated in (or moving into) a closed period for this entry's book.
|
|
require('./closePeriods').assertPeriodOpen(companyId, before.book_code, before.entry_date);
|
|
require('./closePeriods').assertPeriodOpen(companyId, before.book_code, entry.entry_date);
|
|
run(
|
|
`UPDATE gl_entries SET entry_date = ?, fiscal_year = ?, account = ?, account_type = ?, description = ?,
|
|
reference = ?, debit = ?, credit = ?, asset_id = ?, external_id = ?, updated_at = ? WHERE id = ?`,
|
|
[
|
|
entry.entry_date, entry.fiscal_year, entry.account, entry.account_type, entry.description,
|
|
entry.reference, entry.debit, entry.credit, entry.asset_id || null, entry.external_id, now(), id
|
|
]
|
|
);
|
|
const after = getEntry(id, companyId);
|
|
audit(userId, 'update', 'gl_entry', id, before, after);
|
|
return after;
|
|
}
|
|
|
|
function deleteEntry(id, userId, companyId) {
|
|
const before = getEntry(id, companyId);
|
|
if (!before) return false;
|
|
require('./closePeriods').assertPeriodOpen(companyId, before.book_code, before.entry_date);
|
|
run('DELETE FROM gl_entries WHERE id = ?', [id]);
|
|
audit(userId, 'delete', 'gl_entry', id, before, null);
|
|
return true;
|
|
}
|
|
|
|
// ---- Import (preview + commit) ----------------------------------------------
|
|
|
|
// Find an existing entry an import row should reconcile with: by external_id first, else the natural
|
|
// key (date + account + debit + credit + reference) within the same company + book.
|
|
function matchExisting(entry, companyId, book) {
|
|
if (entry.external_id) {
|
|
const byId = one('SELECT * FROM gl_entries WHERE entity_id = ? AND book_code = ? AND external_id = ?', [companyId, book, entry.external_id]);
|
|
if (byId) return byId;
|
|
}
|
|
return one(
|
|
`SELECT * FROM gl_entries
|
|
WHERE entity_id = ? AND book_code = ? AND entry_date = ? AND account = ?
|
|
AND ROUND(debit, 2) = ? AND ROUND(credit, 2) = ? AND IFNULL(reference, '') = IFNULL(?, '')`,
|
|
[companyId, book, entry.entry_date, entry.account, entry.debit, entry.credit, entry.reference]
|
|
);
|
|
}
|
|
|
|
function differs(existing, entry) {
|
|
return existing.entry_date !== entry.entry_date
|
|
|| existing.account !== entry.account
|
|
|| (existing.account_type || '') !== (entry.account_type || '')
|
|
|| (existing.description || '') !== (entry.description || '')
|
|
|| (existing.reference || '') !== (entry.reference || '')
|
|
|| round2(existing.debit) !== entry.debit
|
|
|| round2(existing.credit) !== entry.credit;
|
|
}
|
|
|
|
// Classify each raw import row as new / update / duplicate / error (no DB writes).
|
|
function previewImport(rawRows, book, companyId) {
|
|
const summary = { new: 0, update: 0, duplicate: 0, error: 0 };
|
|
const rows = (rawRows || []).map((raw, index) => {
|
|
const entry = normalizeRow(raw);
|
|
const err = entryError(entry);
|
|
if (err) { summary.error += 1; return { index, status: 'error', entry, error: err }; }
|
|
const existing = matchExisting(entry, companyId, book);
|
|
if (!existing) { summary.new += 1; return { index, status: 'new', entry, existing_id: null }; }
|
|
if (differs(existing, entry)) { summary.update += 1; return { index, status: 'update', entry, existing_id: existing.id }; }
|
|
summary.duplicate += 1;
|
|
return { index, status: 'duplicate', entry, existing_id: existing.id };
|
|
});
|
|
return { summary, rows };
|
|
}
|
|
|
|
// Apply classified rows by strategy. Re-validates company/book server-side.
|
|
// skip — insert only 'new'; conflicts skipped
|
|
// merge — insert 'new', update 'update'; identical duplicates skipped
|
|
// insert — insert every non-error row as a brand-new entry (ignore matches)
|
|
function commitImport(rows, strategy, book, userId, companyId) {
|
|
const result = { inserted: 0, updated: 0, skipped: 0, errors: 0 };
|
|
const mode = ['skip', 'merge', 'insert'].includes(strategy) ? strategy : 'merge';
|
|
tx(() => {
|
|
for (const row of rows || []) {
|
|
const entry = normalizeRow(row.entry || {});
|
|
if (entryError(entry)) { result.errors += 1; continue; }
|
|
const status = row.status;
|
|
if (mode === 'insert') { insertEntry(entry, book, userId, companyId, 'import'); result.inserted += 1; continue; }
|
|
if (status === 'new') { insertEntry(entry, book, userId, companyId, 'import'); result.inserted += 1; continue; }
|
|
if (mode === 'merge' && status === 'update') {
|
|
const existing = row.existing_id ? getEntry(row.existing_id, companyId) : matchExisting(entry, companyId, book);
|
|
if (existing) { updateEntry(existing.id, entry, userId, companyId); result.updated += 1; }
|
|
else { insertEntry(entry, book, userId, companyId, 'import'); result.inserted += 1; }
|
|
continue;
|
|
}
|
|
result.skipped += 1; // duplicates, or conflicts under 'skip'
|
|
}
|
|
});
|
|
audit(userId, 'import', 'gl_entries', book, null, { strategy: mode, ...result });
|
|
return result;
|
|
}
|
|
|
|
// ---- Export -----------------------------------------------------------------
|
|
|
|
function entriesReport(filters, companyId) {
|
|
const { entries, summary } = listEntries(filters, companyId);
|
|
return {
|
|
title: `${filters.book} general ledger entries${filters.year ? ` — ${filters.year}` : ''}`,
|
|
generatedAt: now(),
|
|
columns: [
|
|
{ key: 'entry_date', label: 'Date', format: 'text' },
|
|
{ key: 'account', label: 'Account', format: 'text' },
|
|
{ key: 'account_type', label: 'Account type', format: 'text' },
|
|
{ key: 'description', label: 'Description', format: 'text' },
|
|
{ key: 'reference', label: 'Reference', format: 'text' },
|
|
{ key: 'debit', label: 'Debit', format: 'currency' },
|
|
{ key: 'credit', label: 'Credit', format: 'currency' },
|
|
{ key: 'external_id', label: 'External ID', format: 'text' },
|
|
{ key: 'source', label: 'Source', format: 'text' }
|
|
],
|
|
rows: entries,
|
|
totals: { debit: summary.debit, credit: summary.credit },
|
|
meta: { note: `${summary.count} entr${summary.count === 1 ? 'y' : 'ies'} · debit ${summary.debit} · credit ${summary.credit} · balance ${summary.balance}` }
|
|
};
|
|
}
|
|
|
|
// Full change timeline for one entry, drawn from the audit log (create/update events, and any
|
|
// import-driven changes — all share entity_type 'gl_entry'). Returns null if the entry isn't in
|
|
// this company (so it can't be probed cross-company). Newest first.
|
|
function entryHistory(id, companyId) {
|
|
if (!getEntry(id, companyId)) return null;
|
|
return all(
|
|
`SELECT al.id, al.action, al.before_json, al.after_json, al.created_at, u.name AS actor_name
|
|
FROM audit_logs al LEFT JOIN users u ON u.id = al.actor_id
|
|
WHERE al.entity_type = 'gl_entry' AND al.entity_id = ?
|
|
ORDER BY al.id DESC`,
|
|
[String(id)]
|
|
).map((row) => ({
|
|
id: row.id,
|
|
action: row.action,
|
|
actor_name: row.actor_name,
|
|
created_at: row.created_at,
|
|
before: parseJson(row.before_json, null),
|
|
after: parseJson(row.after_json, null)
|
|
}));
|
|
}
|
|
|
|
module.exports = {
|
|
commitImport,
|
|
createEntry,
|
|
deleteEntry,
|
|
entriesReport,
|
|
entryHistory,
|
|
getEntry,
|
|
insertEntry,
|
|
listEntries,
|
|
previewImport,
|
|
updateEntry
|
|
};
|