Press n or j to go to the next uncovered block, b, p or k for the previous block.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 | 51x 51x 51x 783x 783x 783x 783x 783x 783x 50x 50x 27x 27x 27x 27x 27x 23x 50x 50x 15x 15x 15x 15x 50x 50x 50x 50x 50x 50x 50x 50x 50x 50x 783x 94x 94x 94x 82x 82x 82x 82x 94x 94x 783x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 48x 3392x 2x 2x 3390x 48x 48x 783x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 46x 116x 46x 46x 183x 183x 183x 183x 183x 183x 183x 183x 183x 183x 183x 183x 183x 183x 183x 46x 46x 46x 783x 75x 75x 75x 75x 75x 48x 48x 48x 75x 783x 783x 783x 783x 51x | 'use strict';
Object.defineProperty(exports, "__esModule", { value: true });
module.exports = ({ apiOptions: { accounts, dateRange, stringList, validateDateRange, parseOptions }, accountFilter: { accountFilter, accountMatches }, core: { publicErrors: { createError, errorCodes }, }, investmentReturns: { calculatePerformance }, investmentFlows: { calculateFlows }, databaseValuationCommodity: { valuationCommodityFromDatabase }, external: { zod: { z } }, }) => {
const optionsSchema = z.strictObject({
accounts,
commodities: stringList.default([]),
...dateRange,
}).superRefine(validateDateRange);
function selectedCommodities(database, options) {
const included = options.commodities.filter((pattern) => !pattern.startsWith('~'));
if (included.length > 0) {
const available = database.prepare('SELECT symbol FROM commodity_declarations ORDER BY symbol').all()
.map((row) => row.symbol);
return [...new Set(included.flatMap((pattern) => available.filter((commodity) => accountMatches(commodity, [pattern]))))]
.filter((commodity) => accountMatches(commodity, options.commodities));
}
const clauses = ["p.posting_date <= COALESCE(?, '9999-12-31')"];
const parameters = [options.to ?? null];
if (options.accounts.length > 0) {
const filter = accountFilter('p.account', options.accounts);
clauses.push(filter.sql);
parameters.push(...filter.parameters);
}
const where = clauses.length > 0 ? `WHERE ${clauses.join('\n AND ')}` : '';
return database.prepare(`
SELECT DISTINCT r.amount_commodity AS commodity
FROM resolved_posting_amounts AS r
JOIN postings AS p ON p.id = r.posting_id
${where}
ORDER BY r.amount_commodity
`).all(...parameters).map((row) => row.commodity)
.filter((commodity) => accountMatches(commodity, options.commodities));
}
function selectionFilter(options, commodities, alias) {
const clauses = [`${alias}.amount_commodity IN (${commodities.map(() => '?').join(', ')})`];
const parameters = [...commodities];
if (options.accounts.length > 0) {
const filter = accountFilter('p.account', options.accounts);
clauses.push(filter.sql);
parameters.push(...filter.parameters);
}
return { sql: clauses.join('\n AND '), parameters };
}
function queryDailyValues(database, options, commodities, valuationCommodity) {
const filter = selectionFilter(options, commodities, 'r');
const reportEnd = `SELECT MIN(value, COALESCE(?, value)) AS value
FROM (
SELECT MAX(date) AS value FROM (
SELECT MAX(p.posting_date) AS date
FROM resolved_posting_amounts AS r
JOIN postings AS p ON p.id = r.posting_id
JOIN transactions AS t ON t.entry_id = p.transaction_id
WHERE ${filter.sql}
UNION ALL
SELECT MAX(date) AS date FROM prices
)
)`;
const parameters = [
...filter.parameters,
options.to ?? null,
...filter.parameters,
options.from ?? null,
];
return database.prepare(`
WITH RECURSIVE
selected_changes AS (
SELECT p.posting_date AS date, r.amount_commodity AS commodity, decimal_sum(r.amount_quantity) AS quantity
FROM resolved_posting_amounts AS r
JOIN postings AS p ON p.id = r.posting_id
JOIN transactions AS t ON t.entry_id = p.transaction_id
WHERE ${filter.sql}
GROUP BY p.posting_date, r.amount_commodity
),
commodity_bounds AS (
SELECT commodity, MIN(date) AS start_date
FROM selected_changes
GROUP BY commodity
),
report_end(value) AS MATERIALIZED (
${reportEnd}
),
positions(commodity, date, quantity) AS (
SELECT bounds.commodity, bounds.start_date, COALESCE(changes.quantity, '0')
FROM commodity_bounds AS bounds
JOIN report_end ON bounds.start_date <= report_end.value
LEFT JOIN selected_changes AS changes
ON changes.commodity = bounds.commodity AND changes.date = bounds.start_date
UNION ALL
SELECT positions.commodity, date(positions.date, '+1 day'),
decimal_add(positions.quantity, COALESCE(changes.quantity, '0'))
FROM positions
JOIN report_end ON positions.date < report_end.value
LEFT JOIN selected_changes AS changes
ON changes.commodity = positions.commodity
AND changes.date = date(positions.date, '+1 day')
)
SELECT positions.date,
decimal_sum(decimal_mul(positions.quantity, valuation_prices.rate)) AS value,
MIN(CASE WHEN valuation_prices.rate IS NULL AND decimal_cmp(positions.quantity, '0') != 0
THEN positions.commodity END) AS missing_commodity
FROM positions
LEFT JOIN valuation_prices
ON valuation_prices.commodity = positions.commodity AND valuation_prices.date = positions.date
WHERE positions.date >= COALESCE(
MIN(date(?, '-1 day'), (SELECT value FROM report_end)), '0000-00-00'
)
GROUP BY positions.date
ORDER BY positions.date
`).all(...parameters).map((row) => {
if (row.missing_commodity) {
throw createError(errorCodes.MISSING_VALUATION_DATA, `No price for ${row.missing_commodity} on or before ${row.date} can convert it to ${valuationCommodity}`);
}
return { date: row.date, value: Number(row.value) };
});
}
function queryDailyFlows(database, options, commodities, valuationCommodity) {
const filter = selectionFilter(options, commodities, 'r');
const rows = database.prepare(`
WITH selected_transactions AS (
SELECT DISTINCT p.transaction_id
FROM resolved_posting_amounts AS r
JOIN postings AS p ON p.id = r.posting_id
WHERE ${filter.sql}
AND p.posting_date >= COALESCE(?, '0000-00-00')
AND p.posting_date <= COALESCE(?, '9999-12-31')
)
SELECT p.*, r.amount_quantity AS quantity, r.amount_commodity AS commodity,
market.rate AS market_rate, lot.rate AS lot_rate, price.rate AS cost_rate
FROM postings AS p
JOIN selected_transactions AS selected ON selected.transaction_id = p.transaction_id
JOIN resolved_posting_amounts AS r ON r.posting_id = p.id
LEFT JOIN valuation_prices AS market
ON market.commodity = r.amount_commodity AND market.date = p.posting_date
LEFT JOIN valuation_prices AS lot
ON lot.commodity = p.lot_cost_commodity AND lot.date = p.posting_date
LEFT JOIN valuation_prices AS price
ON price.commodity = p.cost_commodity AND price.date = p.posting_date
ORDER BY p.transaction_id, p.position, r.position
`).all(...filter.parameters, options.from ?? null, options.to ?? null);
const selected = new Set(commodities);
const annotation = (quantity, commodity, total) => quantity === null ? null : {
amount: { quantity, commodity }, total: Boolean(total),
};
const postings = rows.map((row) => {
const selectedAccount = options.accounts.length === 0 ||
accountMatches(row.account, options.accounts);
return {
transactionId: row.transaction_id,
date: row.posting_date,
account: row.account,
amount: { quantity: row.quantity, commodity: row.commodity },
lotCost: annotation(row.lot_cost_quantity, row.lot_cost_commodity, row.lot_cost_is_total),
cost: annotation(row.cost_quantity, row.cost_commodity, row.cost_is_total),
marketRate: row.market_rate,
lotRate: row.lot_rate,
costRate: row.cost_rate,
selectedAccount,
selected: selectedAccount && selected.has(row.commodity),
};
});
return calculateFlows(postings, options, valuationCommodity);
}
function queryInvestmentPerformance(database, options, _caches) {
const reportOptions = parseOptions(optionsSchema, options, 'investmentPerformance');
const valuationCommodity = valuationCommodityFromDatabase(database);
const commodities = selectedCommodities(database, reportOptions);
if (commodities.length === 0)
return calculatePerformance([], [], reportOptions, [], valuationCommodity);
const values = queryDailyValues(database, reportOptions, commodities, valuationCommodity);
const flows = queryDailyFlows(database, reportOptions, commodities, valuationCommodity);
return calculatePerformance(values, flows, reportOptions, commodities, valuationCommodity);
}
return {
inputSchema: optionsSchema,
execute: queryInvestmentPerformance,
};
};
|