1308 lines
68 KiB
JavaScript
1308 lines
68 KiB
JavaScript
const { all, now, one, parseJson } = require('../db');
|
||
const { annualSchedule, bonusAmount, depreciableBasis, getMethodRule, money, section179Amount } = require('../depreciation');
|
||
const { ruleSetForBook } = require('./ruleSets');
|
||
const { assetFromRow } = require('./assets');
|
||
|
||
const BOOKS = ['GAAP', 'FEDERAL', 'STATE', 'AMT', 'ACE', 'USER'];
|
||
|
||
function currentYear() {
|
||
return new Date().getFullYear();
|
||
}
|
||
|
||
function yearOf(value) {
|
||
if (!value) return null;
|
||
return new Date(`${value}T00:00:00`).getFullYear();
|
||
}
|
||
|
||
function monthOf(value) {
|
||
if (!value) return null;
|
||
return new Date(`${value}T00:00:00`).getMonth() + 1;
|
||
}
|
||
|
||
// A date falls in the period when the year matches and (no month filter, or the month matches).
|
||
function inPeriod(date, year, month) {
|
||
if (yearOf(date) !== year) return false;
|
||
if (month && monthOf(date) !== month) return false;
|
||
return true;
|
||
}
|
||
|
||
const REVALUATION_TYPES = ['revaluation', 'write_up', 'write_down'];
|
||
|
||
// Adjustment rows joined to their asset, filtered by period and (optionally) a set of types.
|
||
function adjustmentRows(year, month, types, entityId) {
|
||
return all(
|
||
`SELECT adj.*, a.asset_id, a.description, a.category, e.name AS entity_name, u.name AS created_by_name
|
||
FROM asset_adjustments adj
|
||
JOIN assets a ON a.id = adj.asset_id
|
||
LEFT JOIN entities e ON e.id = a.entity_id
|
||
LEFT JOIN users u ON u.id = adj.created_by
|
||
WHERE (? IS NULL OR a.entity_id = ?)
|
||
ORDER BY adj.adjustment_date`,
|
||
[entityId || null, entityId || null]
|
||
).filter((row) => inPeriod(row.adjustment_date, year, month) && (!types || types.includes(row.type)));
|
||
}
|
||
|
||
// Generic aggregation: group items by keyFn, summing the given numeric fields and counting rows.
|
||
function groupSummary(items, keyFn, fields) {
|
||
const groups = {};
|
||
for (const item of items) {
|
||
const key = keyFn(item) || 'Uncategorized';
|
||
const group = groups[key] || (groups[key] = { key, count: 0 });
|
||
group.count += 1;
|
||
for (const field of fields) group[field] = (group[field] || 0) + Number(item[field] || 0);
|
||
}
|
||
return Object.values(groups).map((group) => {
|
||
const out = { key: group.key, count: group.count };
|
||
for (const field of fields) out[field] = money(group[field]);
|
||
return out;
|
||
});
|
||
}
|
||
|
||
function sumBy(rows, keys) {
|
||
const totals = {};
|
||
for (const key of keys) totals[key] = money(rows.reduce((sum, row) => sum + Number(row[key] || 0), 0));
|
||
return totals;
|
||
}
|
||
|
||
// Columns helper: { key, label, format } where format drives client + export rendering.
|
||
function col(key, label, format = 'text') {
|
||
return { key, label, format };
|
||
}
|
||
|
||
// ---- Asset/book loading ----------------------------------------------------
|
||
|
||
function assetBookRows(bookType, options = {}) {
|
||
return all(
|
||
`SELECT a.*, b.book_type, b.cost AS book_cost, b.depreciation_method, b.convention,
|
||
b.useful_life_months AS book_life, b.section_179_amount, b.bonus_percent,
|
||
b.business_use_percent AS book_bup, b.investment_use_percent AS book_ivp,
|
||
b.manual_depreciation, e.name AS entity_name
|
||
FROM assets a
|
||
JOIN asset_books b ON b.asset_id = a.id
|
||
LEFT JOIN entities e ON e.id = a.entity_id
|
||
WHERE b.book_type = ? AND b.active = 1
|
||
AND (? IS NULL OR a.entity_id = ?)
|
||
AND (? IS NULL OR a.category = ?)
|
||
AND (? IS NULL OR a.status = ?)
|
||
ORDER BY a.asset_id`,
|
||
[
|
||
bookType,
|
||
options.entity_id || null, options.entity_id || null,
|
||
options.category || null, options.category || null,
|
||
options.status || null, options.status || null
|
||
]
|
||
);
|
||
}
|
||
|
||
function toAssetBook(row) {
|
||
return {
|
||
asset: assetFromRow(row),
|
||
entity_name: row.entity_name,
|
||
book: {
|
||
book_type: row.book_type,
|
||
cost: row.book_cost,
|
||
depreciation_method: row.depreciation_method,
|
||
convention: row.convention,
|
||
useful_life_months: row.book_life,
|
||
section_179_amount: row.section_179_amount,
|
||
bonus_percent: row.bonus_percent,
|
||
business_use_percent: row.book_bup,
|
||
investment_use_percent: row.book_ivp,
|
||
manual_depreciation: row.manual_depreciation
|
||
}
|
||
};
|
||
}
|
||
|
||
function computeYear(asset, book, rules, year) {
|
||
const rows = annualSchedule(asset, book, rules, { startYear: year - 1, endYear: year });
|
||
const end = rows.find((row) => row.year === year);
|
||
const cost = Number(book.cost ?? asset.acquisition_cost ?? 0);
|
||
const depreciation = end ? Number(end.depreciation) : 0;
|
||
const accumulatedEnd = end ? Number(end.accumulated) : Number(asset.prior_depreciation || 0);
|
||
const accumulatedBegin = money(accumulatedEnd - depreciation);
|
||
return {
|
||
cost,
|
||
depreciation: money(depreciation),
|
||
accumulated_begin: accumulatedBegin,
|
||
accumulated_end: money(accumulatedEnd),
|
||
nbv_begin: money(cost - accumulatedBegin),
|
||
nbv_end: money(cost - accumulatedEnd),
|
||
method: end ? end.method : book.depreciation_method,
|
||
methodLabel: end ? end.methodLabel : book.depreciation_method
|
||
};
|
||
}
|
||
|
||
function lifetimeAccumulated(asset, book, rules) {
|
||
const rows = annualSchedule(asset, book, rules, { startYear: currentYear(), endYear: currentYear() });
|
||
return rows.length ? Number(rows[rows.length - 1].accumulated) : Number(asset.prior_depreciation || 0);
|
||
}
|
||
|
||
// ---- Report builders -------------------------------------------------------
|
||
|
||
function depreciationExpense(options, titleBook) {
|
||
const book = titleBook || options.book || 'GAAP';
|
||
const year = Number(options.year) || currentYear();
|
||
const rules = ruleSetForBook(book);
|
||
const rows = assetBookRows(book, options).map(toAssetBook).map(({ asset, book: bk, entity_name }) => {
|
||
const c = computeYear(asset, bk, rules, year);
|
||
return {
|
||
asset_id: asset.asset_id,
|
||
description: asset.description,
|
||
entity: entity_name,
|
||
category: asset.category,
|
||
method: c.methodLabel,
|
||
cost: c.cost,
|
||
depreciation: c.depreciation,
|
||
accumulated: c.accumulated_end,
|
||
nbv: c.nbv_end
|
||
};
|
||
});
|
||
return {
|
||
columns: [
|
||
col('asset_id', 'Asset ID'), col('description', 'Description'), col('category', 'Category'),
|
||
col('method', 'Method'), col('cost', 'Cost', 'currency'), col('depreciation', 'Depreciation', 'currency'),
|
||
col('accumulated', 'Accum. depr.', 'currency'), col('nbv', 'Net book value', 'currency')
|
||
],
|
||
rows,
|
||
totals: sumBy(rows, ['cost', 'depreciation', 'accumulated', 'nbv'])
|
||
};
|
||
}
|
||
|
||
function monthlyDepreciation(options) {
|
||
const book = options.book || 'GAAP';
|
||
const year = Number(options.year) || currentYear();
|
||
const annual = depreciationExpense({ ...options, book }).totals.depreciation;
|
||
const monthly = money(annual / 12);
|
||
const rows = Array.from({ length: 12 }, (_, index) => ({ period: `M${index + 1}`, depreciation: monthly }));
|
||
return {
|
||
columns: [col('period', 'Month'), col('depreciation', 'Depreciation', 'currency')],
|
||
rows,
|
||
totals: { depreciation: money(monthly * 12) },
|
||
chart: { type: 'bar', labels: rows.map((r) => r.period), values: rows.map((r) => r.depreciation) }
|
||
};
|
||
}
|
||
|
||
function quarterlyDepreciation(options) {
|
||
const annual = depreciationExpense(options).totals.depreciation;
|
||
const quarter = money(annual / 4);
|
||
const rows = ['Q1', 'Q2', 'Q3', 'Q4'].map((period) => ({ period, depreciation: quarter }));
|
||
return {
|
||
columns: [col('period', 'Quarter'), col('depreciation', 'Depreciation', 'currency')],
|
||
rows,
|
||
totals: { depreciation: money(quarter * 4) },
|
||
chart: { type: 'bar', labels: rows.map((r) => r.period), values: rows.map((r) => r.depreciation) }
|
||
};
|
||
}
|
||
|
||
function ytdDepreciation(options) {
|
||
const month = Math.min(12, Math.max(1, Number(options.month) || 12));
|
||
const base = depreciationExpense(options);
|
||
const rows = base.rows.map((row) => ({ ...row, ytd: money((row.depreciation * month) / 12) }));
|
||
return {
|
||
columns: [
|
||
col('asset_id', 'Asset ID'), col('description', 'Description'), col('method', 'Method'),
|
||
col('depreciation', 'Annual', 'currency'), col('ytd', `YTD through M${month}`, 'currency'),
|
||
col('accumulated', 'Accum. depr.', 'currency'), col('nbv', 'Net book value', 'currency')
|
||
],
|
||
rows,
|
||
totals: sumBy(rows, ['depreciation', 'ytd', 'accumulated', 'nbv'])
|
||
};
|
||
}
|
||
|
||
function netBookValue(options) {
|
||
const book = options.book || 'GAAP';
|
||
const year = Number(options.year) || currentYear();
|
||
const rules = ruleSetForBook(book);
|
||
const rows = assetBookRows(book, options).map(toAssetBook).map(({ asset, book: bk, entity_name }) => {
|
||
const c = computeYear(asset, bk, rules, year);
|
||
return {
|
||
asset_id: asset.asset_id, description: asset.description, entity: entity_name,
|
||
cost: c.cost, accumulated: c.accumulated_end, nbv: c.nbv_end
|
||
};
|
||
});
|
||
return {
|
||
columns: [
|
||
col('asset_id', 'Asset ID'), col('description', 'Description'), col('entity', 'Entity'),
|
||
col('cost', 'Cost', 'currency'), col('accumulated', 'Accum. depr.', 'currency'), col('nbv', 'Net book value', 'currency')
|
||
],
|
||
rows,
|
||
totals: sumBy(rows, ['cost', 'accumulated', 'nbv'])
|
||
};
|
||
}
|
||
|
||
function lifetimeDepreciation(options) {
|
||
const book = options.book || 'GAAP';
|
||
const rules = ruleSetForBook(book);
|
||
const rows = assetBookRows(book, options).map(toAssetBook).map(({ asset, book: bk, entity_name }) => {
|
||
const cost = Number(bk.cost ?? asset.acquisition_cost ?? 0);
|
||
const accumulated = lifetimeAccumulated(asset, bk, rules);
|
||
return {
|
||
asset_id: asset.asset_id, description: asset.description, entity: entity_name,
|
||
in_service_date: asset.in_service_date, cost,
|
||
lifetime_depreciation: money(accumulated), nbv: money(cost - accumulated)
|
||
};
|
||
});
|
||
return {
|
||
columns: [
|
||
col('asset_id', 'Asset ID'), col('description', 'Description'), col('in_service_date', 'In service', 'date'),
|
||
col('cost', 'Cost', 'currency'), col('lifetime_depreciation', 'Lifetime depr.', 'currency'), col('nbv', 'Net book value', 'currency')
|
||
],
|
||
rows,
|
||
totals: sumBy(rows, ['cost', 'lifetime_depreciation', 'nbv'])
|
||
};
|
||
}
|
||
|
||
function acquisitions(options) {
|
||
const year = Number(options.year) || currentYear();
|
||
const month = options.txn_month ? Number(options.txn_month) : null;
|
||
const rows = all(
|
||
`SELECT a.asset_id, a.description, a.category, a.acquired_date, a.vendor, a.invoice_number, a.acquisition_cost
|
||
FROM assets a WHERE a.acquired_date IS NOT NULL
|
||
AND (? IS NULL OR a.entity_id = ?)
|
||
ORDER BY a.acquired_date`,
|
||
[options.entity_id || null, options.entity_id || null]
|
||
).filter((row) => inPeriod(row.acquired_date, year, month))
|
||
.map((row) => ({ ...row, acquisition_cost: money(row.acquisition_cost) }));
|
||
return {
|
||
columns: [
|
||
col('asset_id', 'Asset ID'), col('description', 'Description'), col('category', 'Category'),
|
||
col('acquired_date', 'Acquired', 'date'), col('vendor', 'Vendor'), col('invoice_number', 'Invoice'),
|
||
col('acquisition_cost', 'Cost', 'currency')
|
||
],
|
||
rows,
|
||
totals: sumBy(rows, ['acquisition_cost'])
|
||
};
|
||
}
|
||
|
||
function disposalRows(year, entityId) {
|
||
return all(
|
||
`SELECT d.*, a.asset_id, a.description, a.category, a.acquired_date
|
||
FROM disposals d JOIN assets a ON a.id = d.asset_id
|
||
WHERE (? IS NULL OR a.entity_id = ?)
|
||
ORDER BY d.disposal_date`,
|
||
[entityId || null, entityId || null]
|
||
).filter((row) => yearOf(row.disposal_date) === year);
|
||
}
|
||
|
||
function disposals(options) {
|
||
const year = Number(options.year) || currentYear();
|
||
const month = options.txn_month ? Number(options.txn_month) : null;
|
||
const rows = disposalRows(year, options.entity_id)
|
||
.filter((row) => !month || monthOf(row.disposal_date) === month)
|
||
.map((row) => ({
|
||
asset_id: row.asset_id,
|
||
description: row.description,
|
||
method: row.method,
|
||
disposal_date: row.disposal_date,
|
||
proceeds: money(row.disposal_price - row.disposal_expense),
|
||
net_book_value: money(row.net_book_value),
|
||
gain_loss: money(row.gain_loss),
|
||
recapture_section: row.recapture_section ? `§${row.recapture_section}` : '',
|
||
ordinary_recapture: money(row.ordinary_recapture || 0),
|
||
farm_recapture: money((row.section_1252_recapture || 0) + (row.section_1255_recapture || 0)),
|
||
section_1231_gain: money(row.section_1231_gain || 0)
|
||
}));
|
||
return {
|
||
columns: [
|
||
col('asset_id', 'Asset ID'), col('description', 'Description'), col('method', 'Type'),
|
||
col('disposal_date', 'Date', 'date'), col('proceeds', 'Net proceeds', 'currency'),
|
||
col('net_book_value', 'Net book value', 'currency'), col('gain_loss', 'Gain / (loss)', 'currency'),
|
||
col('recapture_section', 'Recapture'), col('ordinary_recapture', 'Ordinary recapture', 'currency'),
|
||
col('farm_recapture', '§1252/§1255 (farm)', 'currency'), col('section_1231_gain', '§1231 gain / (loss)', 'currency')
|
||
],
|
||
rows,
|
||
totals: sumBy(rows, ['proceeds', 'net_book_value', 'gain_loss', 'ordinary_recapture', 'farm_recapture', 'section_1231_gain'])
|
||
};
|
||
}
|
||
|
||
function adjustments(options) {
|
||
const year = Number(options.year) || currentYear();
|
||
const rows = all(
|
||
`SELECT adj.*, a.asset_id, a.description FROM asset_adjustments adj
|
||
JOIN assets a ON a.id = adj.asset_id
|
||
WHERE (? IS NULL OR a.entity_id = ?)
|
||
ORDER BY adj.adjustment_date`,
|
||
[options.entity_id || null, options.entity_id || null]
|
||
).filter((row) => yearOf(row.adjustment_date) === year)
|
||
.map((row) => ({
|
||
asset_id: row.asset_id, description: row.description, type: row.type,
|
||
book_type: row.book_type, adjustment_date: row.adjustment_date,
|
||
amount: money(row.amount), reason: row.reason
|
||
}));
|
||
return {
|
||
columns: [
|
||
col('asset_id', 'Asset ID'), col('description', 'Description'), col('type', 'Type'),
|
||
col('book_type', 'Book'), col('adjustment_date', 'Date', 'date'), col('amount', 'Amount', 'currency'), col('reason', 'Reason')
|
||
],
|
||
rows,
|
||
totals: sumBy(rows, ['amount'])
|
||
};
|
||
}
|
||
|
||
function rollforward(options) {
|
||
const book = options.book || 'GAAP';
|
||
const year = Number(options.year) || currentYear();
|
||
const rules = ruleSetForBook(book);
|
||
const groups = {};
|
||
for (const { asset, book: bk } of assetBookRows(book, options).map(toAssetBook)) {
|
||
const c = computeYear(asset, bk, rules, year);
|
||
const key = asset.category || 'Uncategorized';
|
||
groups[key] = groups[key] || { category: key, beginning_nbv: 0, additions: 0, depreciation: 0, disposals: 0, ending_nbv: 0 };
|
||
groups[key].beginning_nbv += c.nbv_begin;
|
||
groups[key].depreciation += c.depreciation;
|
||
if (yearOf(asset.acquired_date) === year) groups[key].additions += c.cost;
|
||
groups[key].ending_nbv += c.nbv_end;
|
||
}
|
||
for (const row of disposalRows(year, options.entity_id)) {
|
||
const key = row.category || 'Uncategorized';
|
||
groups[key] = groups[key] || { category: key, beginning_nbv: 0, additions: 0, depreciation: 0, disposals: 0, ending_nbv: 0 };
|
||
groups[key].disposals += Number(row.net_book_value || 0);
|
||
}
|
||
const rows = Object.values(groups).map((row) => ({
|
||
category: row.category,
|
||
beginning_nbv: money(row.beginning_nbv),
|
||
additions: money(row.additions),
|
||
depreciation: money(row.depreciation),
|
||
disposals: money(row.disposals),
|
||
ending_nbv: money(row.ending_nbv)
|
||
}));
|
||
return {
|
||
columns: [
|
||
col('category', 'Category'), col('beginning_nbv', 'Beginning NBV', 'currency'),
|
||
col('additions', 'Additions', 'currency'), col('depreciation', 'Depreciation', 'currency'),
|
||
col('disposals', 'Disposals (NBV)', 'currency'), col('ending_nbv', 'Ending NBV', 'currency')
|
||
],
|
||
rows,
|
||
totals: sumBy(rows, ['beginning_nbv', 'additions', 'depreciation', 'disposals', 'ending_nbv'])
|
||
};
|
||
}
|
||
|
||
function projection(options) {
|
||
const book = options.book || 'GAAP';
|
||
const startYear = Number(options.startYear) || currentYear();
|
||
const endYear = Number(options.endYear) || startYear + 5;
|
||
const rules = ruleSetForBook(book);
|
||
const items = assetBookRows(book, options).map(toAssetBook);
|
||
const rows = [];
|
||
for (let year = startYear; year <= endYear; year += 1) {
|
||
let depreciation = 0;
|
||
let nbv = 0;
|
||
for (const { asset, book: bk } of items) {
|
||
const c = computeYear(asset, bk, rules, year);
|
||
depreciation += c.depreciation;
|
||
nbv += c.nbv_end;
|
||
}
|
||
rows.push({ year, depreciation: money(depreciation), ending_nbv: money(nbv) });
|
||
}
|
||
return {
|
||
columns: [col('year', 'Year', 'number'), col('depreciation', 'Projected depreciation', 'currency'), col('ending_nbv', 'Ending NBV', 'currency')],
|
||
rows,
|
||
totals: { depreciation: money(rows.reduce((sum, row) => sum + row.depreciation, 0)) },
|
||
chart: { type: 'bar', labels: rows.map((r) => String(r.year)), values: rows.map((r) => r.depreciation) }
|
||
};
|
||
}
|
||
|
||
function journalDepreciation(options) {
|
||
const book = options.book || 'GAAP';
|
||
const year = Number(options.year) || currentYear();
|
||
const factor = options.monthlyFactor || 1;
|
||
const rules = ruleSetForBook(book);
|
||
const accounts = {};
|
||
for (const { asset, book: bk } of assetBookRows(book, options).map(toAssetBook)) {
|
||
const c = computeYear(asset, bk, rules, year);
|
||
const amount = money(c.depreciation * factor);
|
||
if (!amount) continue;
|
||
const expense = asset.gl_expense_account || '6400';
|
||
const accum = asset.gl_accumulated_account || '1590';
|
||
accounts[expense] = (accounts[expense] || 0) + amount;
|
||
accounts[`__accum__${accum}`] = (accounts[`__accum__${accum}`] || 0) + amount;
|
||
}
|
||
const rows = [];
|
||
for (const [key, amount] of Object.entries(accounts)) {
|
||
if (key.startsWith('__accum__')) {
|
||
rows.push({ account: key.replace('__accum__', ''), memo: 'Accumulated depreciation', debit: 0, credit: money(amount) });
|
||
} else {
|
||
rows.push({ account: key, memo: 'Depreciation expense', debit: money(amount), credit: 0 });
|
||
}
|
||
}
|
||
rows.sort((a, b) => b.debit - a.debit);
|
||
return {
|
||
columns: [col('account', 'GL account'), col('memo', 'Memo'), col('debit', 'Debit', 'currency'), col('credit', 'Credit', 'currency')],
|
||
rows,
|
||
totals: sumBy(rows, ['debit', 'credit'])
|
||
};
|
||
}
|
||
|
||
function journalDisposals(options) {
|
||
const year = Number(options.year) || currentYear();
|
||
const month = options.txn_month ? Number(options.txn_month) : null;
|
||
const rows = [];
|
||
for (const d of disposalRows(year, options.entity_id)) {
|
||
if (month && monthOf(d.disposal_date) !== month) continue;
|
||
const proceeds = money(d.disposal_price - d.disposal_expense);
|
||
rows.push({ asset_id: d.asset_id, memo: 'Cash / proceeds', debit: proceeds, credit: 0 });
|
||
rows.push({ asset_id: d.asset_id, memo: 'Accumulated depreciation', debit: money(d.accumulated_depreciation), credit: 0 });
|
||
rows.push({ asset_id: d.asset_id, memo: 'Asset cost', debit: 0, credit: money(d.disposed_cost) });
|
||
if (d.gain_loss >= 0) rows.push({ asset_id: d.asset_id, memo: 'Gain on disposal', debit: 0, credit: money(d.gain_loss) });
|
||
else rows.push({ asset_id: d.asset_id, memo: 'Loss on disposal', debit: money(Math.abs(d.gain_loss)), credit: 0 });
|
||
}
|
||
return {
|
||
columns: [col('asset_id', 'Asset ID'), col('memo', 'Memo'), col('debit', 'Debit', 'currency'), col('credit', 'Credit', 'currency')],
|
||
rows,
|
||
totals: sumBy(rows, ['debit', 'credit'])
|
||
};
|
||
}
|
||
|
||
// MACRS property class for Form 4562, Part III line 19 (and ADS line 20), by recovery period.
|
||
function form4562Class(months) {
|
||
const y = Number(months) / 12;
|
||
if (months >= 468) return { label: '(i) Nonresidential real property', sort: 9, recovery: '39 yrs' };
|
||
if (months >= 330) return { label: '(h) Residential rental property', sort: 8, recovery: '27.5 yrs' };
|
||
if (y >= 24) return { label: '(g) 25-year property', sort: 7, recovery: '25 yrs' };
|
||
if (y >= 18) return { label: '(f) 20-year property', sort: 6, recovery: '20 yrs' };
|
||
if (y >= 13) return { label: '(e) 15-year property', sort: 5, recovery: '15 yrs' };
|
||
if (y >= 9) return { label: '(d) 10-year property', sort: 4, recovery: '10 yrs' };
|
||
if (y >= 6.5) return { label: '(c) 7-year property', sort: 3, recovery: '7 yrs' };
|
||
if (y >= 4.5) return { label: '(b) 5-year property', sort: 2, recovery: '5 yrs' };
|
||
if (y >= 2.5) return { label: '(a) 3-year property', sort: 1, recovery: '3 yrs' };
|
||
return { label: 'Other', sort: 10, recovery: `${Math.round(y * 10) / 10} yrs` };
|
||
}
|
||
|
||
function form4562Method(methodRule) {
|
||
if (!methodRule) return 'S/L';
|
||
const f = methodRule.formula;
|
||
if (f === 'macrs_declining_balance' || f === 'macrs_table' || f === 'declining_balance') {
|
||
return Number(methodRule.rateMultiplier) === 1.5 ? '150 DB' : '200 DB';
|
||
}
|
||
if (f === 'sum_of_years_digits') return 'SYD';
|
||
return 'S/L';
|
||
}
|
||
|
||
function form4562Convention(bk, methodRule, year, companyId) {
|
||
if (!methodRule || methodRule.family !== 'MACRS') return '—';
|
||
const conv = methodRule.defaultConvention || bk.convention || 'half_year';
|
||
if (conv === 'mid_month' || Number(bk.useful_life_months) >= 330) return 'MM';
|
||
if (conv === 'half_year' && require('./midQuarter').applies(companyId, 'FEDERAL', year)) return 'MQ';
|
||
return conv === 'half_year' ? 'HY' : conv;
|
||
}
|
||
|
||
// IRS Form 4562 (Depreciation and Amortization) filing worksheet for the Federal book and tax year:
|
||
// Part I (§179 with the dollar limit, phase-out, income limitation and carryover), Part II (special/bonus
|
||
// allowance), Part III (MACRS — prior-year line 17 and current-year line 19 by property class), Part IV
|
||
// (summary total), and Part V (listed property). Computed from the asset register; assist only.
|
||
function form4562(options) {
|
||
const year = Number(options.year) || currentYear();
|
||
const companyId = options.entity_id;
|
||
const rules = ruleSetForBook('FEDERAL');
|
||
const limitation = require('./section179Limitation').summary(companyId, 'FEDERAL', year, options.taxable_income);
|
||
|
||
let line14Bonus = 0; // special (bonus) allowance, non-listed
|
||
let line16Other = 0; // other depreciation incl. ACRS / non-MACRS
|
||
let line17Prior = 0; // MACRS placed in service before this year
|
||
let line21Listed = 0; // listed property depreciation deduction
|
||
const classMap = new Map();
|
||
const listedRows = [];
|
||
|
||
for (const { asset, book: bk } of assetBookRows('FEDERAL', { entity_id: companyId }).map(toAssetBook)) {
|
||
const methodRule = getMethodRule(rules, bk.depreciation_method || 'straight_line');
|
||
const c = computeYear(asset, bk, rules, year);
|
||
if (!c.depreciation) continue;
|
||
const placedThisYear = yearOf(asset.in_service_date || asset.acquired_date) === year;
|
||
const s179a = placedThisYear ? section179Amount(asset, bk) : 0;
|
||
const bonusa = placedThisYear ? bonusAmount(asset, bk) : 0;
|
||
const regular = money(c.depreciation - s179a - bonusa); // MACRS/other portion (excludes §179 & bonus)
|
||
|
||
if (asset.listed_property || asset.passenger_auto) {
|
||
line21Listed += money(regular + bonusa);
|
||
listedRows.push({
|
||
type: asset.description, in_service: asset.in_service_date || asset.acquired_date,
|
||
business_use: `${Number(asset.business_use_percent || 100)}%`,
|
||
cost: money(c.cost), basis: money(depreciableBasis(asset, bk, methodRule)),
|
||
recovery: form4562Class(bk.useful_life_months || asset.useful_life_months).recovery,
|
||
method: `${form4562Method(methodRule)} / ${form4562Convention(bk, methodRule, year, companyId)}`,
|
||
deduction: money(regular + bonusa), section_179: money(s179a)
|
||
});
|
||
continue;
|
||
}
|
||
|
||
line14Bonus += bonusa;
|
||
if (methodRule.family === 'MACRS') {
|
||
if (placedThisYear) {
|
||
const cls = form4562Class(bk.useful_life_months || asset.useful_life_months);
|
||
const e = classMap.get(cls.label) || { class: cls.label, sort: cls.sort, basis: 0, recovery: cls.recovery, convention: form4562Convention(bk, methodRule, year, companyId), method: form4562Method(methodRule), deduction: 0 };
|
||
e.basis = money(e.basis + depreciableBasis(asset, bk, methodRule));
|
||
e.deduction = money(e.deduction + regular);
|
||
classMap.set(cls.label, e);
|
||
} else {
|
||
line17Prior += money(c.depreciation);
|
||
}
|
||
} else {
|
||
line16Other += money(c.depreciation);
|
||
}
|
||
}
|
||
|
||
const line19Rows = [...classMap.values()].sort((a, b) => a.sort - b.sort);
|
||
const line19Total = money(line19Rows.reduce((s, r) => s + r.deduction, 0));
|
||
const line12 = limitation.allowed_deduction;
|
||
const total = money(line12 + line14Bonus + line16Other + line17Prior + line19Total + line21Listed);
|
||
const L = (part, line, description, amount) => ({ part, line, description, amount: money(amount) });
|
||
|
||
return {
|
||
title: `IRS Form 4562 worksheet — Federal, ${year}`,
|
||
columns: [col('part', 'Part'), col('line', 'Line'), col('description', 'Description'), col('amount', 'Amount', 'currency')],
|
||
rows: [
|
||
L('I', '1', 'Maximum §179 deduction', limitation.base_limit || 0),
|
||
L('I', '2', 'Total cost of §179 property placed in service', limitation.property_cost),
|
||
L('I', '3', 'Threshold cost of §179 property before reduction', limitation.phaseout_threshold || 0),
|
||
L('I', '4', 'Reduction in limitation (line 2 − line 3)', limitation.phaseout_reduction),
|
||
L('I', '5', 'Dollar limitation for the year (line 1 − line 4)', limitation.dollar_limit || 0),
|
||
L('I', '8', 'Total elected cost of §179 property', limitation.elected),
|
||
L('I', '9', 'Tentative deduction (smaller of line 5 or 8)', Math.min(limitation.dollar_limit || Infinity, limitation.elected)),
|
||
L('I', '10', 'Carryover of disallowed deduction from last year', limitation.prior_carryover),
|
||
L('I', '11', 'Business income limitation', limitation.taxable_income || 0),
|
||
L('I', '12', '§179 deduction (smaller of line 9+10 or 11)', limitation.allowed_deduction),
|
||
L('I', '13', 'Carryover of disallowed deduction to next year', limitation.carryover_to_next),
|
||
L('II', '14', 'Special depreciation allowance (bonus) — non-listed', line14Bonus),
|
||
L('II', '16', 'Other depreciation (including ACRS)', line16Other),
|
||
L('III', '17', 'MACRS for assets placed in service before this year', line17Prior),
|
||
L('III', '19', 'MACRS for assets placed in service this year (detail below)', line19Total),
|
||
L('IV', '21', 'Listed property (from Part V, detail below)', line21Listed),
|
||
L('IV', '22', 'TOTAL — add lines 12, 14, 16, 17, 19 and 21', total)
|
||
],
|
||
meta: {
|
||
note: `IRS Form 4562 filing worksheet — Federal book, ${year}. The §179 income limitation uses the figure saved on Financial → Section 179 (override with the report parameter). Assist only; validate against current tax law before filing.`,
|
||
sections: [
|
||
{
|
||
title: 'Part III, line 19 — MACRS property placed in service this tax year',
|
||
columns: [col('class', 'Classification'), col('basis', 'Basis for depreciation', 'currency'), col('recovery', 'Recovery'), col('convention', 'Conv.'), col('method', 'Method'), col('deduction', 'Deduction', 'currency')],
|
||
rows: line19Rows
|
||
},
|
||
{
|
||
title: 'Part V — Listed property (autos & other listed assets)',
|
||
columns: [col('type', 'Property'), col('in_service', 'In service', 'date'), col('business_use', 'Business use'), col('cost', 'Cost', 'currency'), col('basis', 'Basis', 'currency'), col('recovery', 'Recovery'), col('method', 'Method/Conv.'), col('deduction', 'Deduction', 'currency'), col('section_179', '§179', 'currency')],
|
||
rows: listedRows
|
||
}
|
||
]
|
||
}
|
||
};
|
||
}
|
||
|
||
function form4797(options) {
|
||
const year = Number(options.year) || currentYear();
|
||
const rows = disposalRows(year, options.entity_id).map((d) => ({
|
||
asset_id: d.asset_id,
|
||
description: d.description,
|
||
property_type: d.property_type || 'other',
|
||
acquired_date: d.acquired_date,
|
||
disposal_date: d.disposal_date,
|
||
proceeds: money(d.disposal_price - d.disposal_expense),
|
||
cost: money(d.disposed_cost),
|
||
accumulated: money(d.accumulated_depreciation),
|
||
gain_loss: money(d.gain_loss)
|
||
}));
|
||
return {
|
||
columns: [
|
||
col('asset_id', 'Asset ID'), col('description', 'Description'), col('property_type', 'Property type'),
|
||
col('acquired_date', 'Acquired', 'date'), col('disposal_date', 'Sold', 'date'),
|
||
col('proceeds', 'Proceeds', 'currency'), col('cost', 'Cost basis', 'currency'),
|
||
col('accumulated', 'Depr. allowed', 'currency'), col('gain_loss', 'Gain / (loss)', 'currency')
|
||
],
|
||
rows,
|
||
totals: sumBy(rows, ['proceeds', 'cost', 'accumulated', 'gain_loss']),
|
||
meta: { note: 'Summary for IRS Form 4797 (Sales of Business Property). §1245/§1250 recapture analysis should be reviewed before filing.' }
|
||
};
|
||
}
|
||
|
||
function personalPropertyTax(options) {
|
||
const book = options.book || 'GAAP';
|
||
const year = Number(options.year) || currentYear();
|
||
const rules = ruleSetForBook(book);
|
||
const groups = {};
|
||
for (const { asset, book: bk } of assetBookRows(book, options).map(toAssetBook)) {
|
||
const c = computeYear(asset, bk, rules, year);
|
||
const key = asset.location || 'Unassigned';
|
||
groups[key] = groups[key] || { location: key, count: 0, cost: 0, nbv: 0 };
|
||
groups[key].count += 1;
|
||
groups[key].cost += c.cost;
|
||
groups[key].nbv += c.nbv_end;
|
||
}
|
||
const rows = Object.values(groups).map((row) => ({ ...row, cost: money(row.cost), nbv: money(row.nbv) }));
|
||
return {
|
||
columns: [col('location', 'Location'), col('count', 'Assets', 'number'), col('cost', 'Cost', 'currency'), col('nbv', 'Taxable NBV', 'currency')],
|
||
rows,
|
||
totals: { count: rows.reduce((s, r) => s + r.count, 0), ...sumBy(rows, ['cost', 'nbv']) }
|
||
};
|
||
}
|
||
|
||
function constructionInProgress(options) {
|
||
const rows = all(
|
||
`SELECT asset_id, description, category, acquired_date, acquisition_cost, vendor
|
||
FROM assets WHERE status = 'cip' AND (? IS NULL OR entity_id = ?) ORDER BY acquired_date`,
|
||
[options.entity_id || null, options.entity_id || null]
|
||
).map((row) => ({ ...row, acquisition_cost: money(row.acquisition_cost) }));
|
||
return {
|
||
columns: [
|
||
col('asset_id', 'Project ID'), col('description', 'Description'), col('category', 'Category'),
|
||
col('acquired_date', 'Started', 'date'), col('vendor', 'Vendor'), col('acquisition_cost', 'Cost to date', 'currency')
|
||
],
|
||
rows,
|
||
totals: sumBy(rows, ['acquisition_cost'])
|
||
};
|
||
}
|
||
|
||
function ubia(options) {
|
||
const rows = all(
|
||
`SELECT asset_id, description, in_service_date, acquisition_cost, land_value
|
||
FROM assets WHERE status != 'disposed' AND (? IS NULL OR entity_id = ?) ORDER BY asset_id`,
|
||
[options.entity_id || null, options.entity_id || null]
|
||
).map((row) => ({
|
||
asset_id: row.asset_id, description: row.description, in_service_date: row.in_service_date,
|
||
ubia: money(Number(row.acquisition_cost || 0) - Number(row.land_value || 0))
|
||
}));
|
||
return {
|
||
columns: [
|
||
col('asset_id', 'Asset ID'), col('description', 'Description'),
|
||
col('in_service_date', 'In service', 'date'), col('ubia', 'UBIA', 'currency')
|
||
],
|
||
rows,
|
||
totals: sumBy(rows, ['ubia']),
|
||
meta: { note: 'Unadjusted Basis Immediately After Acquisition for the §199A qualified business income limitation.' }
|
||
};
|
||
}
|
||
|
||
function fixedAssetCard(options) {
|
||
const asset = options.asset_id
|
||
? assetFromRow(one('SELECT * FROM assets WHERE id = ? OR asset_id = ? LIMIT 1', [options.asset_id, options.asset_id]))
|
||
: null;
|
||
if (!asset) return { columns: [], rows: [], totals: {}, meta: { note: 'Select an asset to view its card.' } };
|
||
const year = Number(options.year) || currentYear();
|
||
const books = all('SELECT * FROM asset_books WHERE asset_id = ? AND active = 1 ORDER BY book_type', [asset.id]);
|
||
const rows = books.map((bookRow) => {
|
||
const book = { ...bookRow, useful_life_months: bookRow.useful_life_months, manual_depreciation: bookRow.manual_depreciation };
|
||
const rules = ruleSetForBook(bookRow.book_type);
|
||
const c = computeYear(asset, book, rules, year);
|
||
return {
|
||
book: bookRow.book_type, method: c.methodLabel, cost: c.cost,
|
||
depreciation: c.depreciation, accumulated: c.accumulated_end, nbv: c.nbv_end
|
||
};
|
||
});
|
||
return {
|
||
columns: [
|
||
col('book', 'Book'), col('method', 'Method'), col('cost', 'Cost', 'currency'),
|
||
col('depreciation', `${year} depr.`, 'currency'), col('accumulated', 'Accum. depr.', 'currency'), col('nbv', 'Net book value', 'currency')
|
||
],
|
||
rows,
|
||
totals: sumBy(rows, ['cost', 'depreciation', 'accumulated', 'nbv']),
|
||
meta: {
|
||
asset: {
|
||
asset_id: asset.asset_id, description: asset.description, category: asset.category, status: asset.status,
|
||
vendor: asset.vendor, serial_number: asset.serial_number, location: asset.location,
|
||
department: asset.department, custodian: asset.custodian, acquired_date: asset.acquired_date,
|
||
in_service_date: asset.in_service_date, barcode_value: asset.barcode_value
|
||
}
|
||
}
|
||
};
|
||
}
|
||
|
||
// ---- Report Builder (custom) ----------------------------------------------
|
||
|
||
const BUILDER_FIELDS = [
|
||
col('asset_id', 'Asset ID'), col('description', 'Description'), col('category', 'Category'),
|
||
col('status', 'Status'), col('entity_name', 'Entity'), col('location', 'Location'),
|
||
col('department', 'Department'), col('group_name', 'Group'), col('custodian', 'Custodian'),
|
||
col('vendor', 'Vendor'), col('serial_number', 'Serial'), col('invoice_number', 'Invoice'),
|
||
col('barcode_value', 'Barcode'), col('condition', 'Condition'), col('new_or_used', 'New/Used'),
|
||
col('acquisition_cost', 'Cost', 'currency'), col('salvage_value', 'Salvage', 'currency'),
|
||
col('land_value', 'Land value', 'currency'), col('useful_life_months', 'Life (months)', 'number'),
|
||
col('acquired_date', 'Acquired', 'date'), col('in_service_date', 'In service', 'date'),
|
||
col('gl_asset_account', 'Asset GL'), col('gl_expense_account', 'Expense GL'), col('gl_accumulated_account', 'Accum. GL')
|
||
];
|
||
|
||
function customReport(options) {
|
||
const requested = Array.isArray(options.columns) && options.columns.length
|
||
? options.columns
|
||
: ['asset_id', 'description', 'category', 'status', 'acquisition_cost'];
|
||
const columns = BUILDER_FIELDS.filter((field) => requested.includes(field.key));
|
||
const rows = all(
|
||
`SELECT a.*, e.name AS entity_name FROM assets a LEFT JOIN entities e ON e.id = a.entity_id
|
||
WHERE (? IS NULL OR a.entity_id = ?)
|
||
AND (? IS NULL OR a.category = ?)
|
||
AND (? IS NULL OR a.status = ?)
|
||
ORDER BY a.asset_id`,
|
||
[
|
||
options.entity_id || null, options.entity_id || null,
|
||
options.category || null, options.category || null,
|
||
options.status || null, options.status || null
|
||
]
|
||
).map((row) => {
|
||
const projected = {};
|
||
for (const column of columns) projected[column.key] = row[column.key];
|
||
return projected;
|
||
});
|
||
const currencyKeys = columns.filter((c) => c.format === 'currency').map((c) => c.key);
|
||
return { columns, rows, totals: sumBy(rows, currencyKeys) };
|
||
}
|
||
|
||
// ---- Maintenance & alerts --------------------------------------------------
|
||
|
||
function dateStr(date) {
|
||
return date.toISOString().slice(0, 10);
|
||
}
|
||
|
||
function freqLabel(value, unit) {
|
||
return `every ${value} ${unit}`;
|
||
}
|
||
|
||
function pmDue(options) {
|
||
const before = options.before_date || dateStr(new Date(Date.now() + 30 * 86400000));
|
||
const rows = all(
|
||
`SELECT ap.next_due_date, ap.last_completed_date, ap.frequency_value, ap.frequency_unit,
|
||
p.name AS plan_name, a.asset_id AS asset_code, a.description AS asset_description, u.name AS assigned_to_name
|
||
FROM asset_pm_plans ap
|
||
JOIN assets a ON a.id = ap.asset_id
|
||
LEFT JOIN pm_plans p ON p.id = ap.plan_id
|
||
LEFT JOIN users u ON u.id = ap.assigned_to
|
||
WHERE ap.active = 1 AND ap.next_due_date IS NOT NULL AND ap.next_due_date <= ?
|
||
AND (? IS NULL OR a.entity_id = ?)
|
||
ORDER BY ap.next_due_date`,
|
||
[before, options.entity_id || null, options.entity_id || null]
|
||
).map((row) => ({ ...row, plan_name: row.plan_name || 'Maintenance', frequency: freqLabel(row.frequency_value, row.frequency_unit) }));
|
||
return {
|
||
columns: [
|
||
col('asset_code', 'Asset'), col('asset_description', 'Description'), col('plan_name', 'Plan'),
|
||
col('frequency', 'Frequency'), col('next_due_date', 'Next due', 'date'), col('last_completed_date', 'Last done', 'date'),
|
||
col('assigned_to_name', 'Assigned to')
|
||
],
|
||
rows,
|
||
totals: {},
|
||
meta: { note: `${rows.length} PM schedule(s) due on or before ${before}.` }
|
||
};
|
||
}
|
||
|
||
function pmUncompleted(options) {
|
||
const today = dateStr(new Date());
|
||
const rows = all(
|
||
`SELECT ap.next_due_date, ap.last_completed_date, ap.frequency_value, ap.frequency_unit,
|
||
p.name AS plan_name, a.asset_id AS asset_code, a.description AS asset_description, u.name AS assigned_to_name
|
||
FROM asset_pm_plans ap
|
||
JOIN assets a ON a.id = ap.asset_id
|
||
LEFT JOIN pm_plans p ON p.id = ap.plan_id
|
||
LEFT JOIN users u ON u.id = ap.assigned_to
|
||
WHERE ap.active = 1 AND ap.next_due_date IS NOT NULL AND ap.next_due_date < ?
|
||
AND (? IS NULL OR a.entity_id = ?)
|
||
ORDER BY ap.next_due_date`,
|
||
[today, options.entity_id || null, options.entity_id || null]
|
||
).map((row) => ({
|
||
...row,
|
||
plan_name: row.plan_name || 'Maintenance',
|
||
frequency: freqLabel(row.frequency_value, row.frequency_unit),
|
||
days_overdue: Math.max(0, Math.round((new Date(`${today}T00:00:00`) - new Date(`${row.next_due_date}T00:00:00`)) / 86400000))
|
||
}));
|
||
return {
|
||
columns: [
|
||
col('asset_code', 'Asset'), col('asset_description', 'Description'), col('plan_name', 'Plan'),
|
||
col('next_due_date', 'Was due', 'date'), col('days_overdue', 'Days overdue', 'number'),
|
||
col('last_completed_date', 'Last done', 'date'), col('assigned_to_name', 'Assigned to')
|
||
],
|
||
rows,
|
||
totals: {},
|
||
meta: { note: `${rows.length} asset PM schedule(s) overdue / uncompleted as of ${today}.` }
|
||
};
|
||
}
|
||
|
||
function pmCompleted(options) {
|
||
const since = options.since_date || dateStr(new Date(Date.now() - 30 * 86400000));
|
||
const rows = all(
|
||
`SELECT c.completed_at, c.cost, c.rating_safety, c.rating_physical, c.rating_operating,
|
||
CASE WHEN c.signature IS NOT NULL THEN 'yes' ELSE 'no' END AS signed,
|
||
(SELECT COUNT(*) FROM pm_completion_photos ph WHERE ph.completion_id = c.id) AS photos,
|
||
u.name AS completed_by_name, a.asset_id AS asset_code, p.name AS plan_name
|
||
FROM pm_completions c
|
||
JOIN asset_pm_plans ap ON ap.id = c.asset_pm_id
|
||
JOIN assets a ON a.id = ap.asset_id
|
||
LEFT JOIN pm_plans p ON p.id = ap.plan_id
|
||
LEFT JOIN users u ON u.id = c.completed_by
|
||
WHERE c.completed_at >= ?
|
||
AND (? IS NULL OR a.entity_id = ?)
|
||
ORDER BY c.completed_at DESC`,
|
||
[since, options.entity_id || null, options.entity_id || null]
|
||
).map((row) => ({ ...row, plan_name: row.plan_name || 'Maintenance', completed_at: String(row.completed_at).slice(0, 10), cost: money(row.cost) }));
|
||
return {
|
||
columns: [
|
||
col('asset_code', 'Asset'), col('plan_name', 'Plan'), col('completed_at', 'Completed', 'date'),
|
||
col('completed_by_name', 'By'), col('rating_safety', 'Safety', 'number'), col('rating_operating', 'Operating', 'number'),
|
||
col('photos', 'Photos', 'number'), col('signed', 'Signed'), col('cost', 'Cost', 'currency')
|
||
],
|
||
rows,
|
||
totals: sumBy(rows, ['cost']),
|
||
meta: { note: `${rows.length} PM service(s) completed since ${since}.` }
|
||
};
|
||
}
|
||
|
||
function pmPlanPrint(options) {
|
||
const plan = options.pm_plan_id ? one('SELECT * FROM pm_plans WHERE id = ?', [options.pm_plan_id]) : null;
|
||
if (!plan) return { columns: [], rows: [], totals: {}, meta: { note: 'Select a PM plan to print.' } };
|
||
const steps = all('SELECT step_order, title, details, est_minutes FROM pm_plan_steps WHERE plan_id = ? ORDER BY step_order, id', [plan.id]);
|
||
const components = all('SELECT part_number, description, supplier, cost FROM pm_plan_components WHERE plan_id = ? ORDER BY sort_order, id', [plan.id]);
|
||
const rows = steps.map((step, index) => ({ step: index + 1, title: step.title, details: step.details, est_minutes: step.est_minutes }));
|
||
const estTotal = steps.reduce((sum, step) => sum + (Number(step.est_minutes) || 0), 0);
|
||
const partsTotal = components.reduce((sum, comp) => sum + (Number(comp.cost) || 0), 0);
|
||
const partsLines = components.map((comp) => `${comp.part_number ? comp.part_number + ' ' : ''}${comp.description || ''}${comp.supplier ? ` (${comp.supplier})` : ''} — ${money(comp.cost)}`).join('; ');
|
||
const noteParts = [
|
||
`Frequency: ${freqLabel(plan.frequency_value, plan.frequency_unit)}`,
|
||
`Estimated time: ${estTotal} min`
|
||
];
|
||
if (plan.description) noteParts.push(`Description: ${plan.description}`);
|
||
if (plan.instructions) noteParts.push(`Instructions: ${plan.instructions}`);
|
||
if (components.length) noteParts.push(`Parts (${money(partsTotal)}): ${partsLines}`);
|
||
return {
|
||
title: `PM plan — ${plan.name}`,
|
||
columns: [col('step', '#', 'number'), col('title', 'Step'), col('details', 'Details'), col('est_minutes', 'Est. min', 'number')],
|
||
rows,
|
||
totals: { est_minutes: estTotal },
|
||
meta: { note: noteParts.join(' · ') }
|
||
};
|
||
}
|
||
|
||
function alertsSince(options) {
|
||
const since = options.since_date || dateStr(new Date(Date.now() - 30 * 86400000));
|
||
const rows = all(
|
||
`SELECT al.severity, al.type, al.title, al.due_date, al.status, al.created_at, a.asset_id AS asset_code
|
||
FROM alerts al LEFT JOIN assets a ON a.id = al.asset_id
|
||
WHERE al.created_at >= ?
|
||
ORDER BY al.created_at DESC`,
|
||
[since]
|
||
).map((row) => ({ ...row, type: String(row.type).replace(/_/g, ' '), created_at: String(row.created_at).slice(0, 10) }));
|
||
return {
|
||
columns: [
|
||
col('severity', 'Severity'), col('type', 'Type'), col('title', 'Alert'), col('asset_code', 'Asset'),
|
||
col('due_date', 'Due', 'date'), col('status', 'Status'), col('created_at', 'Raised', 'date')
|
||
],
|
||
rows,
|
||
totals: {},
|
||
meta: { note: `${rows.length} alert(s) raised since ${since}.` }
|
||
};
|
||
}
|
||
|
||
// ---- Revaluation & write-off (transactions, journals, summaries) -----------
|
||
|
||
function revaluationTransactions(options) {
|
||
const year = Number(options.year) || currentYear();
|
||
const month = options.txn_month ? Number(options.txn_month) : null;
|
||
const rows = adjustmentRows(year, month, REVALUATION_TYPES, options.entity_id).map((r) => ({
|
||
adjustment_date: r.adjustment_date, asset_id: r.asset_id, description: r.description,
|
||
type: String(r.type).replace(/_/g, ' '), book_type: r.book_type, amount: money(r.amount), reason: r.reason
|
||
}));
|
||
return {
|
||
columns: [
|
||
col('adjustment_date', 'Date', 'date'), col('asset_id', 'Asset ID'), col('description', 'Description'),
|
||
col('type', 'Type'), col('book_type', 'Book'), col('amount', 'Amount', 'currency'), col('reason', 'Reason')
|
||
],
|
||
rows,
|
||
totals: sumBy(rows, ['amount'])
|
||
};
|
||
}
|
||
|
||
// Write-offs are impairment adjustments plus abandonment disposals (asset removed at no value).
|
||
function writeoffItems(year, month, entityId) {
|
||
const impairments = adjustmentRows(year, month, ['impairment'], entityId).map((r) => ({
|
||
date: r.adjustment_date, asset_id: r.asset_id, description: r.description, category: r.category,
|
||
source: 'Impairment', book_type: r.book_type, amount: money(r.amount), reason: r.reason
|
||
}));
|
||
const abandonments = disposalRows(year, entityId)
|
||
.filter((d) => d.method === 'abandonment' && (!month || monthOf(d.disposal_date) === month))
|
||
.map((d) => ({
|
||
date: d.disposal_date, asset_id: d.asset_id, description: d.description, category: d.category,
|
||
source: 'Abandonment', book_type: d.book_type, amount: money(d.net_book_value), reason: d.notes
|
||
}));
|
||
return [...impairments, ...abandonments].sort((a, b) => String(a.date).localeCompare(String(b.date)));
|
||
}
|
||
|
||
function writeoffTransactions(options) {
|
||
const year = Number(options.year) || currentYear();
|
||
const month = options.txn_month ? Number(options.txn_month) : null;
|
||
const rows = writeoffItems(year, month, options.entity_id);
|
||
return {
|
||
columns: [
|
||
col('date', 'Date', 'date'), col('asset_id', 'Asset ID'), col('description', 'Description'),
|
||
col('source', 'Source'), col('book_type', 'Book'), col('amount', 'Amount', 'currency'), col('reason', 'Reason')
|
||
],
|
||
rows: rows.map(({ category, ...rest }) => rest),
|
||
totals: sumBy(rows, ['amount'])
|
||
};
|
||
}
|
||
|
||
function journalRevaluation(options) {
|
||
const year = Number(options.year) || currentYear();
|
||
const month = options.txn_month ? Number(options.txn_month) : null;
|
||
const rows = [];
|
||
for (const r of adjustmentRows(year, month, REVALUATION_TYPES, options.entity_id)) {
|
||
const amount = money(r.amount);
|
||
if (!amount) continue;
|
||
// write_up / revaluation increase carrying value (Dr asset, Cr revaluation surplus); write_down reverses.
|
||
const up = r.type !== 'write_down';
|
||
rows.push({ asset_id: r.asset_id, memo: 'Asset carrying value', debit: up ? amount : 0, credit: up ? 0 : amount });
|
||
rows.push({ asset_id: r.asset_id, memo: 'Revaluation surplus / reserve', debit: up ? 0 : amount, credit: up ? amount : 0 });
|
||
}
|
||
return {
|
||
columns: [col('asset_id', 'Asset ID'), col('memo', 'Memo'), col('debit', 'Debit', 'currency'), col('credit', 'Credit', 'currency')],
|
||
rows,
|
||
totals: sumBy(rows, ['debit', 'credit'])
|
||
};
|
||
}
|
||
|
||
function journalWriteoff(options) {
|
||
const year = Number(options.year) || currentYear();
|
||
const month = options.txn_month ? Number(options.txn_month) : null;
|
||
const rows = [];
|
||
for (const item of writeoffItems(year, month, options.entity_id)) {
|
||
const amount = money(item.amount);
|
||
if (!amount) continue;
|
||
rows.push({ asset_id: item.asset_id, memo: `${item.source} loss / expense`, debit: amount, credit: 0 });
|
||
rows.push({ asset_id: item.asset_id, memo: 'Asset / accumulated depreciation', debit: 0, credit: amount });
|
||
}
|
||
return {
|
||
columns: [col('asset_id', 'Asset ID'), col('memo', 'Memo'), col('debit', 'Debit', 'currency'), col('credit', 'Credit', 'currency')],
|
||
rows,
|
||
totals: sumBy(rows, ['debit', 'credit'])
|
||
};
|
||
}
|
||
|
||
// ---- Summaries -------------------------------------------------------------
|
||
|
||
function bookSummaryItems(options) {
|
||
const book = options.book || 'GAAP';
|
||
const year = Number(options.year) || currentYear();
|
||
const rules = ruleSetForBook(book);
|
||
return assetBookRows(book, options).map(toAssetBook).map(({ asset, book: bk }) => {
|
||
const c = computeYear(asset, bk, rules, year);
|
||
return {
|
||
category: asset.category || 'Uncategorized',
|
||
cost: c.cost, depreciation: c.depreciation, accumulated: c.accumulated_end, nbv: c.nbv_end
|
||
};
|
||
});
|
||
}
|
||
|
||
function assetSummary(options) {
|
||
const rows = groupSummary(bookSummaryItems(options), (i) => i.category, ['cost', 'accumulated', 'nbv'])
|
||
.map((g) => ({ category: g.key || 'Uncategorized', count: g.count, cost: g.cost, accumulated: g.accumulated, nbv: g.nbv }));
|
||
return {
|
||
columns: [
|
||
col('category', 'Category'), col('count', 'Assets', 'number'), col('cost', 'Cost', 'currency'),
|
||
col('accumulated', 'Accum. depr.', 'currency'), col('nbv', 'Net book value', 'currency')
|
||
],
|
||
rows,
|
||
totals: { count: rows.reduce((s, r) => s + r.count, 0), ...sumBy(rows, ['cost', 'accumulated', 'nbv']) }
|
||
};
|
||
}
|
||
|
||
function depreciationSummary(options) {
|
||
const rows = groupSummary(bookSummaryItems(options), (i) => i.category, ['cost', 'depreciation', 'accumulated', 'nbv'])
|
||
.map((g) => ({ category: g.key, count: g.count, cost: g.cost, depreciation: g.depreciation, accumulated: g.accumulated, nbv: g.nbv }));
|
||
return {
|
||
columns: [
|
||
col('category', 'Category'), col('count', 'Assets', 'number'), col('cost', 'Cost', 'currency'),
|
||
col('depreciation', 'Depreciation', 'currency'), col('accumulated', 'Accum. depr.', 'currency'), col('nbv', 'Net book value', 'currency')
|
||
],
|
||
rows,
|
||
totals: { count: rows.reduce((s, r) => s + r.count, 0), ...sumBy(rows, ['cost', 'depreciation', 'accumulated', 'nbv']) }
|
||
};
|
||
}
|
||
|
||
function ytdDepreciationSummary(options) {
|
||
const month = Math.min(12, Math.max(1, Number(options.month) || 12));
|
||
const items = bookSummaryItems(options).map((i) => ({ ...i, ytd: money((i.depreciation * month) / 12) }));
|
||
const rows = groupSummary(items, (i) => i.category, ['depreciation', 'ytd', 'accumulated', 'nbv'])
|
||
.map((g) => ({ category: g.key, count: g.count, depreciation: g.depreciation, ytd: g.ytd, accumulated: g.accumulated, nbv: g.nbv }));
|
||
return {
|
||
columns: [
|
||
col('category', 'Category'), col('count', 'Assets', 'number'), col('depreciation', 'Annual', 'currency'),
|
||
col('ytd', `YTD through M${month}`, 'currency'), col('accumulated', 'Accum. depr.', 'currency'), col('nbv', 'Net book value', 'currency')
|
||
],
|
||
rows,
|
||
totals: { count: rows.reduce((s, r) => s + r.count, 0), ...sumBy(rows, ['depreciation', 'ytd', 'accumulated', 'nbv']) }
|
||
};
|
||
}
|
||
|
||
function purchaseSummary(options) {
|
||
const items = acquisitions(options).rows.map((r) => ({ category: r.category || 'Uncategorized', cost: r.acquisition_cost }));
|
||
const rows = groupSummary(items, (i) => i.category, ['cost']).map((g) => ({ category: g.key, count: g.count, cost: g.cost }));
|
||
return {
|
||
columns: [col('category', 'Category'), col('count', 'Purchases', 'number'), col('cost', 'Cost', 'currency')],
|
||
rows,
|
||
totals: { count: rows.reduce((s, r) => s + r.count, 0), ...sumBy(rows, ['cost']) }
|
||
};
|
||
}
|
||
|
||
function disposalSummary(options) {
|
||
const year = Number(options.year) || currentYear();
|
||
const month = options.txn_month ? Number(options.txn_month) : null;
|
||
const items = disposalRows(year, options.entity_id)
|
||
.filter((d) => !month || monthOf(d.disposal_date) === month)
|
||
.map((d) => ({
|
||
method: String(d.method).replace(/_/g, ' '),
|
||
proceeds: money(d.disposal_price - d.disposal_expense), nbv: money(d.net_book_value), gain_loss: money(d.gain_loss)
|
||
}));
|
||
const rows = groupSummary(items, (i) => i.method, ['proceeds', 'nbv', 'gain_loss'])
|
||
.map((g) => ({ method: g.key, count: g.count, proceeds: g.proceeds, nbv: g.nbv, gain_loss: g.gain_loss }));
|
||
return {
|
||
columns: [
|
||
col('method', 'Method'), col('count', 'Disposals', 'number'), col('proceeds', 'Net proceeds', 'currency'),
|
||
col('nbv', 'Net book value', 'currency'), col('gain_loss', 'Gain / (loss)', 'currency')
|
||
],
|
||
rows,
|
||
totals: { count: rows.reduce((s, r) => s + r.count, 0), ...sumBy(rows, ['proceeds', 'nbv', 'gain_loss']) }
|
||
};
|
||
}
|
||
|
||
function revaluationSummary(options) {
|
||
const year = Number(options.year) || currentYear();
|
||
const month = options.txn_month ? Number(options.txn_month) : null;
|
||
const items = adjustmentRows(year, month, REVALUATION_TYPES, options.entity_id)
|
||
.map((r) => ({ type: String(r.type).replace(/_/g, ' '), amount: money(r.amount) }));
|
||
const rows = groupSummary(items, (i) => i.type, ['amount']).map((g) => ({ type: g.key, count: g.count, amount: g.amount }));
|
||
return {
|
||
columns: [col('type', 'Type'), col('count', 'Count', 'number'), col('amount', 'Net amount', 'currency')],
|
||
rows,
|
||
totals: { count: rows.reduce((s, r) => s + r.count, 0), ...sumBy(rows, ['amount']) }
|
||
};
|
||
}
|
||
|
||
function writeoffSummary(options) {
|
||
const year = Number(options.year) || currentYear();
|
||
const month = options.txn_month ? Number(options.txn_month) : null;
|
||
const items = writeoffItems(year, month, options.entity_id);
|
||
const rows = groupSummary(items, (i) => i.category, ['amount']).map((g) => ({ category: g.key, count: g.count, amount: g.amount }));
|
||
return {
|
||
columns: [col('category', 'Category'), col('count', 'Write-offs', 'number'), col('amount', 'Amount', 'currency')],
|
||
rows,
|
||
totals: { count: rows.reduce((s, r) => s + r.count, 0), ...sumBy(rows, ['amount']) }
|
||
};
|
||
}
|
||
|
||
// ---- Registers, journals & history -----------------------------------------
|
||
|
||
function fixedAssetRegister(options) {
|
||
const book = options.book || 'GAAP';
|
||
const year = Number(options.year) || currentYear();
|
||
const rules = ruleSetForBook(book);
|
||
const rows = assetBookRows(book, options).map(toAssetBook).map(({ asset, book: bk }) => {
|
||
const c = computeYear(asset, bk, rules, year);
|
||
return {
|
||
asset_id: asset.asset_id, description: asset.description, category: asset.category, status: asset.status,
|
||
acquired_date: asset.acquired_date, in_service_date: asset.in_service_date,
|
||
location: asset.location, custodian: asset.custodian,
|
||
cost: c.cost, accumulated: c.accumulated_end, nbv: c.nbv_end
|
||
};
|
||
});
|
||
return {
|
||
columns: [
|
||
col('asset_id', 'Asset ID'), col('description', 'Description'), col('category', 'Category'), col('status', 'Status'),
|
||
col('acquired_date', 'Acquired', 'date'), col('in_service_date', 'In service', 'date'),
|
||
col('location', 'Location'), col('custodian', 'Custodian'),
|
||
col('cost', 'Cost', 'currency'), col('accumulated', 'Accum. depr.', 'currency'), col('nbv', 'Net book value', 'currency')
|
||
],
|
||
rows,
|
||
totals: sumBy(rows, ['cost', 'accumulated', 'nbv'])
|
||
};
|
||
}
|
||
|
||
// Comprehensive per-asset dossier: per-book financials (reused from the asset card) plus rich
|
||
// sections (maintenance, notes, warranties, adjustments, disposals) and photo thumbnails for PDF.
|
||
function assetJournal(options) {
|
||
const card = fixedAssetCard(options);
|
||
if (!card.meta || !card.meta.asset) return card;
|
||
const asset = one('SELECT * FROM assets WHERE id = ? OR asset_id = ? LIMIT 1', [options.asset_id, options.asset_id]);
|
||
const id = asset.id;
|
||
|
||
const maintenance = all(
|
||
`SELECT c.completed_at, p.name AS plan_name, u.name AS by_name, c.cost
|
||
FROM pm_completions c JOIN asset_pm_plans ap ON ap.id = c.asset_pm_id
|
||
LEFT JOIN pm_plans p ON p.id = ap.plan_id LEFT JOIN users u ON u.id = c.completed_by
|
||
WHERE ap.asset_id = ? ORDER BY c.completed_at DESC`, [id]
|
||
).map((r) => ({ completed_at: String(r.completed_at).slice(0, 10), plan_name: r.plan_name || 'Maintenance', by_name: r.by_name, cost: money(r.cost) }));
|
||
|
||
const notes = all(
|
||
`SELECT n.created_at, n.body, u.name AS by_name FROM asset_notes n
|
||
LEFT JOIN users u ON u.id = n.created_by WHERE n.asset_id = ? ORDER BY n.created_at DESC`, [id]
|
||
).map((r) => ({ created_at: String(r.created_at).slice(0, 10), by_name: r.by_name, body: r.body }));
|
||
|
||
const warranties = all(
|
||
'SELECT provider, start_date, expiration_date, warranty_limit, coverage_details FROM warranties WHERE asset_id = ? ORDER BY expiration_date', [id]
|
||
);
|
||
|
||
const adjustments = all(
|
||
'SELECT adjustment_date, type, book_type, amount, reason FROM asset_adjustments WHERE asset_id = ? ORDER BY adjustment_date DESC', [id]
|
||
).map((r) => ({ adjustment_date: r.adjustment_date, type: String(r.type).replace(/_/g, ' '), book_type: r.book_type, amount: money(r.amount), reason: r.reason }));
|
||
|
||
const disposalsList = all(
|
||
'SELECT disposal_date, method, net_book_value, gain_loss FROM disposals WHERE asset_id = ? ORDER BY disposal_date DESC', [id]
|
||
).map((r) => ({ disposal_date: r.disposal_date, method: String(r.method).replace(/_/g, ' '), net_book_value: money(r.net_book_value), gain_loss: money(r.gain_loss) }));
|
||
|
||
const sections = [
|
||
{ title: 'Maintenance history', columns: [col('completed_at', 'Completed', 'date'), col('plan_name', 'Plan'), col('by_name', 'By'), col('cost', 'Cost', 'currency')], rows: maintenance },
|
||
{ title: 'Notes', columns: [col('created_at', 'Date', 'date'), col('by_name', 'By'), col('body', 'Note')], rows: notes },
|
||
{ title: 'Warranties', columns: [col('provider', 'Provider'), col('start_date', 'Start', 'date'), col('expiration_date', 'Expires', 'date'), col('warranty_limit', 'Limit'), col('coverage_details', 'Coverage')], rows: warranties },
|
||
{ title: 'Adjustments & revaluations', columns: [col('adjustment_date', 'Date', 'date'), col('type', 'Type'), col('book_type', 'Book'), col('amount', 'Amount', 'currency'), col('reason', 'Reason')], rows: adjustments }
|
||
];
|
||
if (disposalsList.length) {
|
||
sections.push({ title: 'Disposals', columns: [col('disposal_date', 'Date', 'date'), col('method', 'Type'), col('net_book_value', 'NBV', 'currency'), col('gain_loss', 'Gain / (loss)', 'currency')], rows: disposalsList });
|
||
}
|
||
|
||
const photoRows = all('SELECT name, caption, data_base64 FROM asset_photos WHERE asset_id = ? ORDER BY sort_order, id LIMIT 12', [id]);
|
||
const photos = photoRows.map((p) => ({ caption: p.caption || p.name || 'Photo', data: p.data_base64 }));
|
||
const photoCount = one('SELECT COUNT(*) AS c FROM asset_photos WHERE asset_id = ?', [id]).c;
|
||
|
||
return { ...card, meta: { ...card.meta, layout: 'dossier', sections, photos, photo_count: photoCount } };
|
||
}
|
||
|
||
function assetHistory(options) {
|
||
const row = options.asset_id ? one('SELECT * FROM assets WHERE id = ? OR asset_id = ? LIMIT 1', [options.asset_id, options.asset_id]) : null;
|
||
const asset = row ? assetFromRow(row) : null;
|
||
if (!asset) return { columns: [], rows: [], totals: {}, meta: { note: 'Select an asset to view its history.' } };
|
||
const id = asset.id;
|
||
const events = [];
|
||
if (asset.acquired_date) events.push({ date: asset.acquired_date, event: 'Acquired', detail: [asset.vendor, asset.invoice_number ? `#${asset.invoice_number}` : ''].filter(Boolean).join(' '), by: '', amount: money(asset.acquisition_cost) });
|
||
if (asset.in_service_date) events.push({ date: asset.in_service_date, event: 'Placed in service', detail: asset.description, by: '', amount: '' });
|
||
for (const r of all('SELECT * FROM asset_adjustments WHERE asset_id = ?', [id])) {
|
||
events.push({ date: r.adjustment_date, event: String(r.type).replace(/_/g, ' '), detail: [r.book_type, r.reason].filter(Boolean).join(' · '), by: '', amount: money(r.amount) });
|
||
}
|
||
for (const d of all('SELECT * FROM disposals WHERE asset_id = ?', [id])) {
|
||
events.push({ date: d.disposal_date, event: `Disposal (${String(d.method).replace(/_/g, ' ')})`, detail: `Gain/(loss) ${money(d.gain_loss)}`, by: '', amount: money(d.net_book_value) });
|
||
}
|
||
for (const c of all('SELECT c.completed_at, c.cost, u.name AS by_name FROM pm_completions c JOIN asset_pm_plans ap ON ap.id = c.asset_pm_id LEFT JOIN users u ON u.id = c.completed_by WHERE ap.asset_id = ?', [id])) {
|
||
events.push({ date: String(c.completed_at).slice(0, 10), event: 'PM completed', detail: '', by: c.by_name, amount: money(c.cost) });
|
||
}
|
||
for (const n of all('SELECT n.created_at, n.body, u.name AS by_name FROM asset_notes n LEFT JOIN users u ON u.id = n.created_by WHERE n.asset_id = ?', [id])) {
|
||
events.push({ date: String(n.created_at).slice(0, 10), event: 'Note', detail: n.body, by: n.by_name, amount: '' });
|
||
}
|
||
for (const w of all('SELECT provider, start_date FROM warranties WHERE asset_id = ?', [id])) {
|
||
events.push({ date: w.start_date || '', event: 'Warranty added', detail: w.provider || '', by: '', amount: '' });
|
||
}
|
||
for (const lg of all("SELECT al.action, al.created_at, u.name AS by_name FROM audit_logs al LEFT JOIN users u ON u.id = al.actor_id WHERE al.entity_type = 'asset' AND al.entity_id = ?", [String(id)])) {
|
||
if (['dispose', 'adjust', 'reverse_disposal'].includes(lg.action)) continue;
|
||
events.push({ date: String(lg.created_at).slice(0, 10), event: String(lg.action).replace(/_/g, ' '), detail: '', by: lg.by_name, amount: '' });
|
||
}
|
||
events.sort((a, b) => String(a.date).localeCompare(String(b.date)));
|
||
return {
|
||
columns: [col('date', 'Date', 'date'), col('event', 'Event'), col('detail', 'Detail'), col('by', 'By'), col('amount', 'Amount', 'currency')],
|
||
rows: events,
|
||
totals: {},
|
||
meta: { asset: { asset_id: asset.asset_id, description: asset.description, status: asset.status }, note: `${events.length} event(s) recorded.` }
|
||
};
|
||
}
|
||
|
||
// ---- Registry --------------------------------------------------------------
|
||
|
||
const REPORTS = {
|
||
depreciation_expense: { title: 'Annual depreciation expense', group: 'Depreciation', params: ['book', 'year', 'entity_id'], build: (o) => depreciationExpense(o) },
|
||
monthly_depreciation: { title: '12-month depreciation expense', group: 'Depreciation', params: ['book', 'year', 'entity_id'], build: (o) => monthlyDepreciation(o) },
|
||
quarterly_depreciation: { title: 'Quarterly depreciation expense', group: 'Depreciation', params: ['book', 'year', 'entity_id'], build: (o) => quarterlyDepreciation(o) },
|
||
ytd_depreciation: { title: 'Annual & YTD depreciation', group: 'Depreciation', params: ['book', 'year', 'month', 'entity_id'], build: (o) => ytdDepreciation(o) },
|
||
net_book_value: { title: 'Net book value', group: 'Depreciation', params: ['book', 'year', 'entity_id'], build: (o) => netBookValue(o) },
|
||
lifetime_depreciation: { title: 'Lifetime depreciation', group: 'Depreciation', params: ['book', 'entity_id'], build: (o) => lifetimeDepreciation(o) },
|
||
projection: { title: 'Projection', group: 'Depreciation', params: ['book', 'startYear', 'endYear', 'entity_id'], build: (o) => projection(o) },
|
||
rollforward: { title: 'Annual rollforward', group: 'Activity', params: ['book', 'year', 'entity_id'], build: (o) => rollforward(o) },
|
||
acquisitions: { title: 'Annual acquisitions', group: 'Activity', params: ['year', 'entity_id'], build: (o) => acquisitions(o) },
|
||
disposals: { title: 'Annual disposals & gain/loss', group: 'Activity', params: ['year', 'entity_id'], build: (o) => disposals(o) },
|
||
adjustments: { title: 'Annual adjustments', group: 'Activity', params: ['year', 'entity_id'], build: (o) => adjustments(o) },
|
||
cip: { title: 'Construction-in-progress', group: 'Activity', params: ['entity_id'], build: (o) => constructionInProgress(o) },
|
||
journal_depreciation: { title: 'Journal entry: depreciation', group: 'Journal entries', params: ['book', 'year', 'entity_id'], build: (o) => journalDepreciation(o) },
|
||
journal_disposals: { title: 'Journal entry: disposals', group: 'Journal entries', params: ['year', 'entity_id'], build: (o) => journalDisposals(o) },
|
||
form_4562: { title: 'Federal Form 4562 (depreciation)', group: 'Tax', params: ['year', 'taxable_income', 'entity_id'], build: (o) => form4562(o) },
|
||
form_4797: { title: 'Federal Form 4797 (sales)', group: 'Tax', params: ['year', 'entity_id'], build: (o) => form4797(o) },
|
||
amt: { title: 'AMT depreciation', group: 'Tax', params: ['year', 'entity_id'], build: (o) => depreciationExpense(o, 'AMT') },
|
||
ace: { title: 'ACE depreciation', group: 'Tax', params: ['year', 'entity_id'], build: (o) => depreciationExpense(o, 'ACE') },
|
||
personal_property_tax: { title: 'Personal property tax', group: 'Tax', params: ['book', 'year', 'entity_id'], build: (o) => personalPropertyTax(o) },
|
||
ubia: { title: 'UBIA (§199A)', group: 'Tax', params: ['entity_id'], build: (o) => ubia(o) },
|
||
pm_due: { title: 'PM due before date', group: 'Maintenance', params: ['before_date', 'entity_id'], build: (o) => pmDue(o) },
|
||
pm_uncompleted: { title: 'Assets with uncompleted PM', group: 'Maintenance', params: ['entity_id'], build: (o) => pmUncompleted(o) },
|
||
pm_completed: { title: 'PM completed since date', group: 'Maintenance', params: ['since_date', 'entity_id'], build: (o) => pmCompleted(o) },
|
||
pm_plan: { title: 'PM plan (printable)', group: 'Maintenance', params: ['pm_plan_id'], build: (o) => pmPlanPrint(o) },
|
||
alerts_since: { title: 'Alerts since date', group: 'Maintenance', params: ['since_date'], build: (o) => alertsSince(o) },
|
||
fixed_asset_card: { title: 'Fixed asset card', group: 'Detail', params: ['asset_id', 'year'], build: (o) => fixedAssetCard(o) },
|
||
custom: { title: 'Report builder (custom)', group: 'Detail', params: ['columns', 'entity_id', 'category', 'status'], build: (o) => customReport(o) },
|
||
|
||
// Journals & registers
|
||
asset_journal: { title: 'Asset journal (full dossier)', group: 'Journals & registers', params: ['asset_id', 'year'], build: (o) => assetJournal(o) },
|
||
fixed_asset_journal: { title: 'Fixed asset journal (register)', group: 'Journals & registers', params: ['book', 'year', 'entity_id', 'category', 'status'], build: (o) => fixedAssetRegister(o) },
|
||
journal_depreciation_monthly: { title: 'Monthly depreciation journal entry', group: 'Journals & registers', params: ['book', 'year', 'month', 'entity_id'], build: (o) => journalDepreciation({ ...o, monthlyFactor: 1 / 12 }) },
|
||
journal_disposals_monthly: { title: 'Monthly disposal journal entry', group: 'Journals & registers', params: ['year', 'txn_month', 'entity_id'], build: (o) => journalDisposals(o) },
|
||
journal_revaluation: { title: 'Monthly revaluation journal entry', group: 'Journals & registers', params: ['book', 'year', 'txn_month', 'entity_id'], build: (o) => journalRevaluation(o) },
|
||
journal_writeoff: { title: 'Monthly write-off journal entry', group: 'Journals & registers', params: ['book', 'year', 'txn_month', 'entity_id'], build: (o) => journalWriteoff(o) },
|
||
|
||
// Transactions
|
||
txn_purchases: { title: 'Asset purchased (transactions)', group: 'Transactions', params: ['year', 'txn_month', 'entity_id'], build: (o) => acquisitions(o) },
|
||
txn_disposals: { title: 'Asset disposed (transactions)', group: 'Transactions', params: ['year', 'txn_month', 'entity_id'], build: (o) => disposals(o) },
|
||
txn_revaluations: { title: 'Asset revalued (transactions)', group: 'Transactions', params: ['year', 'txn_month', 'entity_id'], build: (o) => revaluationTransactions(o) },
|
||
txn_writeoffs: { title: 'Asset write-off (transactions)', group: 'Transactions', params: ['year', 'txn_month', 'entity_id'], build: (o) => writeoffTransactions(o) },
|
||
|
||
// Summaries
|
||
asset_summary: { title: 'Asset summary', group: 'Summaries', params: ['book', 'year', 'entity_id', 'category', 'status'], build: (o) => assetSummary(o) },
|
||
depreciation_summary: { title: 'Asset depreciation summary', group: 'Summaries', params: ['book', 'year', 'entity_id', 'category', 'status'], build: (o) => depreciationSummary(o) },
|
||
ytd_depreciation_summary: { title: 'YTD asset depreciation summary', group: 'Summaries', params: ['book', 'year', 'month', 'entity_id'], build: (o) => ytdDepreciationSummary(o) },
|
||
purchase_summary: { title: 'Asset purchase summary', group: 'Summaries', params: ['year', 'txn_month', 'entity_id'], build: (o) => purchaseSummary(o) },
|
||
disposal_summary: { title: 'Asset disposal summary', group: 'Summaries', params: ['year', 'txn_month', 'entity_id'], build: (o) => disposalSummary(o) },
|
||
revaluation_summary: { title: 'Asset revaluation summary', group: 'Summaries', params: ['year', 'txn_month', 'entity_id'], build: (o) => revaluationSummary(o) },
|
||
writeoff_summary: { title: 'Asset write-off summary', group: 'Summaries', params: ['year', 'txn_month', 'entity_id'], build: (o) => writeoffSummary(o) },
|
||
|
||
// Detail
|
||
asset_history: { title: 'Asset history', group: 'Detail', params: ['asset_id'], build: (o) => assetHistory(o) },
|
||
future_depreciation: { title: 'Future depreciation calculation', group: 'Depreciation', params: ['book', 'startYear', 'endYear', 'entity_id'], build: (o) => projection(o) }
|
||
};
|
||
|
||
function paramSpecs() {
|
||
const entities = all('SELECT id, name FROM entities ORDER BY name').map((row) => ({ title: row.name, value: row.id }));
|
||
const assets = all('SELECT id, asset_id, description FROM assets ORDER BY asset_id LIMIT 1000')
|
||
.map((row) => ({ title: `${row.asset_id} · ${row.description}`, value: row.id }));
|
||
const categories = all('SELECT DISTINCT category FROM assets ORDER BY category').map((row) => row.category).filter(Boolean);
|
||
const statuses = all('SELECT DISTINCT status FROM assets ORDER BY status').map((row) => row.status).filter(Boolean);
|
||
const year = currentYear();
|
||
const pmPlanOptions = all('SELECT id, name FROM pm_plans ORDER BY name').map((row) => ({ title: row.name, value: row.id }));
|
||
const today = new Date();
|
||
const bookCodes = (() => {
|
||
try {
|
||
const rows = all("SELECT code FROM books WHERE book_type != 'maintenance' ORDER BY sort_order, id");
|
||
if (rows.length) return rows.map((row) => row.code);
|
||
} catch {
|
||
// books table may be unavailable
|
||
}
|
||
return BOOKS;
|
||
})();
|
||
return {
|
||
book: { label: 'Book', type: 'select', options: bookCodes, default: bookCodes[0] || 'GAAP' },
|
||
year: { label: 'Year', type: 'number', default: year },
|
||
taxable_income: { label: 'Business taxable income (§179)', type: 'number', clearable: true, default: null },
|
||
month: { label: 'Through month', type: 'select', options: Array.from({ length: 12 }, (_, i) => i + 1), default: 12 },
|
||
txn_month: { label: 'Month (optional)', type: 'select', options: Array.from({ length: 12 }, (_, i) => i + 1), clearable: true, default: null },
|
||
startYear: { label: 'Start year', type: 'number', default: year },
|
||
endYear: { label: 'End year', type: 'number', default: year + 5 },
|
||
entity_id: { label: 'Entity', type: 'select', options: entities, clearable: true, default: null },
|
||
category: { label: 'Category', type: 'select', options: categories, clearable: true, default: null },
|
||
status: { label: 'Status', type: 'select', options: statuses, clearable: true, default: null },
|
||
asset_id: { label: 'Asset', type: 'select', options: assets, default: null },
|
||
columns: { label: 'Columns', type: 'multiselect', options: BUILDER_FIELDS.map((f) => ({ title: f.label, value: f.key })), default: ['asset_id', 'description', 'category', 'status', 'acquisition_cost'] },
|
||
before_date: { label: 'Due before', type: 'date', default: dateStr(new Date(today.getTime() + 30 * 86400000)) },
|
||
since_date: { label: 'Since', type: 'date', default: dateStr(new Date(today.getTime() - 30 * 86400000)) },
|
||
pm_plan_id: { label: 'PM plan', type: 'select', options: pmPlanOptions, default: pmPlanOptions[0]?.value || null }
|
||
};
|
||
}
|
||
|
||
function catalog() {
|
||
const reports = Object.entries(REPORTS).map(([key, def]) => ({ key, title: def.title, group: def.group, params: def.params }));
|
||
return { reports, params: paramSpecs() };
|
||
}
|
||
|
||
function runReport(type, options = {}) {
|
||
const def = REPORTS[type];
|
||
if (!def) {
|
||
const error = new Error(`Unknown report type: ${type}`);
|
||
error.status = 400;
|
||
throw error;
|
||
}
|
||
const result = def.build(options);
|
||
return { type, title: def.title, group: def.group, generatedAt: now(), options, ...result };
|
||
}
|
||
|
||
module.exports = { BOOKS, catalog, computeYear, runReport };
|