A local-first personal finance app
import { send } from '@actual-app/core/platform/client/connection';
import * as monthUtils from '@actual-app/core/shared/months';
import { q } from '@actual-app/core/shared/query';
import type {
CategoryEntity,
CategoryGroupEntity,
RuleConditionEntity,
SpendingAverageRange,
SpendingEntity,
SpendingMonthEntity,
} from '@actual-app/core/types/models';
// @ts-strict-ignore
import { keyBy } from 'es-toolkit';
import { resolveSpendingAverageRange } from '#components/reports/spendingAverageRange';
import { fromDateRepr } from '#components/reports/util';
import type { useSpreadsheet } from '#hooks/useSpreadsheet';
import { aqlQuery } from '#queries/aqlQuery';
import {
filterCategoriesByConditions,
isSupportedCategoryCondition,
} from './budgetDataQuery';
import { makeQuery } from './makeQuery';
type createSpendingSpreadsheetProps = {
conditions?: RuleConditionEntity[];
conditionsOp?: 'and' | 'or';
compare?: string;
compareTo?: string;
averageRange?: SpendingAverageRange;
budgetType?: 'envelope' | 'tracking';
};
export function getSpendingBudgetFilters({
categories,
categoryGroups,
conditions,
conditionsOp,
}: {
categories: CategoryEntity[];
categoryGroups: CategoryGroupEntity[];
conditions: RuleConditionEntity[];
conditionsOp?: 'and' | 'or';
}) {
const expenseCategories = categories.filter(category => !category.is_income);
const budgetConditions = conditions.filter(
cond =>
!cond.customName &&
(cond.field === 'category' || cond.field === 'category_group'),
);
if (
budgetConditions.length === 0 ||
!budgetConditions.every(isSupportedCategoryCondition)
) {
return [
{
category: {
$oneof: expenseCategories.map(category => category.id),
},
},
];
}
const matchingCategoryIds = filterCategoriesByConditions(
expenseCategories,
categoryGroups,
budgetConditions,
conditionsOp ?? 'and',
).map(category => category.id);
return [{ category: { $oneof: matchingCategoryIds } }];
}
export function createSpendingSpreadsheet({
conditions = [],
conditionsOp,
compare,
compareTo,
averageRange,
budgetType = 'envelope',
}: createSpendingSpreadsheetProps) {
const compareMonth = compare ?? monthUtils.currentMonth();
const compareToMonth = compareTo ?? monthUtils.subMonths(compareMonth, 1);
const endDate = monthUtils.getMonthEnd(compareMonth + '-01');
const startDateTo = compareToMonth + '-01';
const endDateTo = monthUtils.getMonthEnd(compareToMonth + '-01');
const interval = 'Daily';
const compareInterval = monthUtils.dayRangeInclusive(
compareMonth + '-01',
endDate,
);
return async (
spreadsheet: ReturnType<typeof useSpreadsheet>,
setData: (data: SpendingEntity) => void,
) => {
const earliestTrans =
averageRange?.mode === 'all-time'
? await send('get-earliest-transaction')
: null;
const earliestMonth = earliestTrans
? monthUtils.monthFromDate(fromDateRepr(earliestTrans.date))
: null;
const resolvedAverageRange = resolveSpendingAverageRange({
averageRange,
compare: compareMonth,
earliestMonth,
});
const averageMonths = new Set(resolvedAverageRange.months);
const startDate = (resolvedAverageRange.startMonth ?? compareMonth) + '-01';
const { filters } = await send('make-filters-from-conditions', {
conditions: conditions.filter(cond => !cond.customName),
});
const conditionsOpKey = conditionsOp === 'or' ? '$or' : '$and';
const [assets, debts] = await Promise.all([
aqlQuery(
makeQuery(
'assets',
startDate,
endDate,
interval,
conditionsOpKey,
filters,
),
).then(({ data }) => data),
aqlQuery(
makeQuery(
'debts',
startDate,
endDate,
interval,
conditionsOpKey,
filters,
),
).then(({ data }) => data),
]);
const [assetsTo, debtsTo] = await Promise.all([
aqlQuery(
makeQuery(
'assets',
startDateTo,
endDateTo,
interval,
conditionsOpKey,
filters,
),
).then(({ data }) => data),
aqlQuery(
makeQuery(
'debts',
startDateTo,
endDateTo,
interval,
conditionsOpKey,
filters,
),
).then(({ data }) => data),
]);
const overlapAssets =
endDateTo < startDate || startDateTo > endDate ? assetsTo : [];
const overlapDebts =
endDateTo < startDate || startDateTo > endDate ? debtsTo : [];
const combineAssets = [...assets, ...overlapAssets];
const combineDebts = [...debts, ...overlapDebts];
const totalsByDate = new Map<
string,
{ perIntervalAssets: number; perIntervalDebts: number }
>();
combineAssets
.filter(e => !e.categoryIncome && !e.accountOffBudget)
.forEach(asset => {
const totals = totalsByDate.get(asset.date) ?? {
perIntervalAssets: 0,
perIntervalDebts: 0,
};
totals.perIntervalAssets += asset.amount;
totalsByDate.set(asset.date, totals);
});
combineDebts
.filter(e => !e.categoryIncome && !e.accountOffBudget)
.forEach(debt => {
const totals = totalsByDate.get(debt.date) ?? {
perIntervalAssets: 0,
perIntervalDebts: 0,
};
totals.perIntervalDebts += debt.amount;
totalsByDate.set(debt.date, totals);
});
const budgetMonth = parseInt(compareMonth.replace('-', ''), 10);
const budgetTable =
budgetType === 'tracking' ? 'reflect_budgets' : 'zero_budgets';
const { list: categories, grouped: categoryGroups } =
await send('get-categories');
const budgetFilters = getSpendingBudgetFilters({
categories,
categoryGroups,
conditions,
conditionsOp,
});
const [budgets] = await Promise.all([
aqlQuery(
q(budgetTable)
.filter({
$and: [{ month: { $eq: budgetMonth } }, ...budgetFilters],
})
.groupBy([{ $id: '$category' }])
.select([
{ category: { $id: '$category' } },
{ amount: { $sum: '$amount' } },
]),
).then(({ data }) => data),
]);
const dailyBudget =
budgets &&
budgets.reduce((a, v) => a + v.amount, 0) / compareInterval.length;
const intervals = monthUtils.dayRangeInclusive(startDate, endDate);
if (endDateTo < startDate || startDateTo > endDate) {
intervals.push(...monthUtils.dayRangeInclusive(startDateTo, endDateTo));
}
const days = [...Array(29).keys()]
.filter(f => f > 0)
.map(n => n.toString().padStart(2, '0'));
let totalAssets = 0;
let totalDebts = 0;
let totalBudget = 0;
const months = monthUtils.rangeInclusive(startDate, endDate).map(month => {
return { month, perMonthAssets: 0, perMonthDebts: 0 };
});
if (endDateTo < startDate || startDateTo > endDate) {
months.unshift({
month: compareToMonth,
perMonthAssets: 0,
perMonthDebts: 0,
});
}
const intervalData = days.map(day => {
let averageSum = 0;
let monthCount = 0;
const dayData = months.map(month => {
const data = intervals.reduce((arr, intervalItem) => {
const offsetDay =
Number(intervalItem.substring(8, 10)) >= 28
? '28'
: intervalItem.substring(8, 10);
let perIntervalAssets = 0;
let perIntervalDebts = 0;
if (
month.month === monthUtils.getMonth(intervalItem) &&
day === offsetDay
) {
const totals = totalsByDate.get(intervalItem);
perIntervalAssets += totals?.perIntervalAssets ?? 0;
perIntervalDebts += totals?.perIntervalDebts ?? 0;
totalAssets += perIntervalAssets;
totalDebts += perIntervalDebts;
let cumulativeAssets = 0;
let cumulativeDebts = 0;
if (month.month === compareMonth) {
totalBudget -= dailyBudget;
}
months.map(m => {
if (m.month === month.month) {
cumulativeAssets = m.perMonthAssets += perIntervalAssets;
cumulativeDebts = m.perMonthDebts += perIntervalDebts;
}
return null;
});
if (averageMonths.has(month.month)) {
if (day === '28') {
if (monthUtils.getMonthEnd(intervalItem) === intervalItem) {
averageSum += cumulativeAssets + cumulativeDebts;
monthCount += 1;
}
} else {
averageSum += cumulativeAssets + cumulativeDebts;
monthCount += 1;
}
}
arr.push({
date: intervalItem,
totalDebts: perIntervalDebts,
totalAssets: perIntervalAssets,
totalTotals: perIntervalDebts + perIntervalAssets,
cumulative:
intervalItem <= monthUtils.currentDay()
? cumulativeDebts + cumulativeAssets
: null,
});
}
return arr;
}, []);
const maxCumulative = data.reduce((a, b) =>
b.cumulative === null ? a : b,
).cumulative;
const totalDaily = data.reduce((a, v) => a + v.totalTotals, 0);
return {
date: data[0].date,
cumulative: maxCumulative,
daily: totalDaily,
month: month.month,
};
});
const indexedData: SpendingMonthEntity = keyBy(dayData, d => d.month);
return {
months: indexedData,
day,
average: monthCount === 0 ? 0 : Math.round(averageSum / monthCount),
compare: dayData.filter(c => c.month === compareMonth)[0].cumulative,
compareTo: dayData.filter(c => c.month === compareToMonth)[0]
.cumulative,
budget: totalBudget,
};
});
setData({
intervalData,
averageRange: resolvedAverageRange,
startDate,
endDate,
totalDebts,
totalAssets,
totalTotals: totalAssets + totalDebts,
});
};
}