| 1 |
import { get } from "../../api"; |
| 2 |
import { getUrlPath } from "../../helpers"; |
| 3 |
import { _ } from "../../i18n"; |
| 4 |
import { getURLFilters } from "../../stores/filters"; |
| 5 |
import type { QueryResultTable } from "../query/query_table"; |
| 6 |
import { Route } from "../route"; |
| 7 |
import Holdings from "./Holdings.svelte"; |
| 8 |
|
| 9 |
export type HoldingsReportType = |
| 10 |
| "all" |
| 11 |
| "by_account" |
| 12 |
| "by_currency" |
| 13 |
| "by_cost_currency"; |
| 14 |
|
| 15 |
const to_report_type = (s: string | null): HoldingsReportType => |
| 16 |
s === "by_account" || s === "by_currency" || s === "by_cost_currency" |
| 17 |
? s |
| 18 |
: "all"; |
| 19 |
|
| 20 |
const QUERIES = { |
| 21 |
all: ` |
| 22 |
SELECT |
| 23 |
account, |
| 24 |
units(sum(position)) as units, |
| 25 |
cost_number as cost, |
| 26 |
first(getprice(currency, cost_currency)) as price, |
| 27 |
cost(sum(position)) as book_value, |
| 28 |
value(sum(position)) as market_value, |
| 29 |
safediv((abs(sum(number(value(position)))) - abs(sum(number(cost(position))))), sum(number(cost(position)))) * 100 as unrealized_profit_pct, |
| 30 |
cost_date as acquisition_date |
| 31 |
WHERE account_sortkey(account) ~ "^[01]" |
| 32 |
GROUP BY account, cost_date, currency, cost_currency, cost_number, account_sortkey(account) |
| 33 |
ORDER BY account_sortkey(account), currency, cost_date |
| 34 |
`.trim(), |
| 35 |
by_account: ` |
| 36 |
SELECT |
| 37 |
account, |
| 38 |
units(sum(position)) as units, |
| 39 |
cost(sum(position)) as book_value, |
| 40 |
value(sum(position)) as market_value, |
| 41 |
safediv((abs(sum(number(value(position)))) - abs(sum(number(cost(position))))), sum(number(cost(position)))) * 100 as unrealized_profit_pct |
| 42 |
WHERE account_sortkey(account) ~ "^[01]" |
| 43 |
GROUP BY account, cost_currency, account_sortkey(account), currency |
| 44 |
ORDER BY account_sortkey(account), currency |
| 45 |
`.trim(), |
| 46 |
by_currency: ` |
| 47 |
SELECT |
| 48 |
units(sum(position)) as units, |
| 49 |
safediv(number(only(first(cost_currency), cost(sum(position)))), number(only(first(currency), units(sum(position))))) as average_cost, |
| 50 |
first(getprice(currency, cost_currency)) as price, |
| 51 |
cost(sum(position)) as book_value, |
| 52 |
value(sum(position)) as market_value, |
| 53 |
safediv((abs(sum(number(value(position)))) - abs(sum(number(cost(position))))), sum(number(cost(position)))) * 100 as unrealized_profit_pct |
| 54 |
WHERE account_sortkey(account) ~ "^[01]" |
| 55 |
GROUP BY currency, cost_currency |
| 56 |
ORDER BY currency, cost_currency |
| 57 |
`.trim(), |
| 58 |
by_cost_currency: ` |
| 59 |
SELECT |
| 60 |
units(sum(position)) as units, |
| 61 |
cost(sum(position)) as book_value, |
| 62 |
value(sum(position)) as market_value, |
| 63 |
safediv((abs(sum(number(value(position)))) - abs(sum(number(cost(position))))), sum(number(cost(position)))) * 100 as unrealized_profit_pct |
| 64 |
WHERE account_sortkey(account) ~ "^[01]" |
| 65 |
GROUP BY cost_currency |
| 66 |
ORDER BY cost_currency |
| 67 |
`.trim(), |
| 68 |
}; |
| 69 |
|
| 70 |
export interface HoldingsReportProps { |
| 71 |
aggregation_key: HoldingsReportType; |
| 72 |
query_string: string; |
| 73 |
query_result_table: QueryResultTable; |
| 74 |
} |
| 75 |
|
| 76 |
export const holdings = new Route<HoldingsReportProps>( |
| 77 |
"holdings", |
| 78 |
Holdings, |
| 79 |
async (url) => { |
| 80 |
const [, key = ""] = getUrlPath(url)?.split("/") ?? []; |
| 81 |
const aggregation_key = to_report_type(key); |
| 82 |
const query_string = QUERIES[aggregation_key]; |
| 83 |
const query_result_table = await get("query", { |
| 84 |
query_string, |
| 85 |
...getURLFilters(url), |
| 86 |
}); |
| 87 |
if (query_result_table.t !== "table") { |
| 88 |
throw new Error("Internal error: expected a query result table"); |
| 89 |
} |
| 90 |
return { aggregation_key, query_string, query_result_table }; |
| 91 |
}, |
| 92 |
() => _("Holdings"), |
| 93 |
); |