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 type {
AccountEntity,
balanceTypeOpType,
CategoryEntity,
CategoryGroupEntity,
CustomReportTagScope,
DataEntity,
GroupedEntity,
IntervalEntity,
PayeeEntity,
RuleConditionEntity,
sortByOpType,
TagEntity,
} from '@actual-app/core/types/models';
import type { SyncedPrefs } from '@actual-app/core/types/prefs';
import * as d from 'date-fns';
import {
getIntervalFormat,
groupBySelections,
ReportOptions,
} from '#components/reports/ReportOptions';
import type { QueryDataEntity } from '#components/reports/ReportOptions';
import { resolveTagScope } from '#components/reports/tagScope';
import type { useSpreadsheet } from '#hooks/useSpreadsheet';
import { calculateLegend } from './calculateLegend';
import { fetchSpreadsheetQueryData } from './fetchSpreadsheetQueryData';
import { filterEmptyRows } from './filterEmptyRows';
import {
filterHiddenItems,
filterReportTransactions,
} from './filterHiddenItems';
import { recalculate } from './recalculate';
import { reportCategoryLists } from './reportCategoryLists';
import { sortData } from './sortData';
import { groupQueryDataByTags } from './tagGroups';
import {
determineIntervalRange,
trimIntervalDataToRange,
trimIntervalsToRange,
} from './trimIntervals';
export type createCustomSpreadsheetProps = {
startDate: string;
endDate: string;
interval: string;
categories: { list: CategoryEntity[]; grouped: CategoryGroupEntity[] };
budgetType?: SyncedPrefs['budgetType'];
conditions: RuleConditionEntity[];
conditionsOp: string;
showEmpty: boolean;
showOffBudget: boolean;
showHiddenCategories: boolean;
showUncategorized: boolean;
trimIntervals: boolean;
groupBy?: string;
balanceTypeOp?: balanceTypeOpType;
sortByOp?: sortByOpType;
payees?: PayeeEntity[];
accounts?: AccountEntity[];
tags?: TagEntity[];
tagScope?: CustomReportTagScope;
graphType?: string;
firstDayOfWeekIdx?: SyncedPrefs['firstDayOfWeekIdx'];
dateFormat?: SyncedPrefs['dateFormat'];
};
export function createCustomSpreadsheet({
startDate,
endDate,
interval,
categories,
budgetType = 'envelope',
conditions = [],
conditionsOp,
showEmpty,
showOffBudget,
showHiddenCategories,
showUncategorized,
trimIntervals,
groupBy = '',
balanceTypeOp = 'totalDebts',
sortByOp = 'desc',
payees = [],
accounts = [],
tags = [],
tagScope,
graphType,
firstDayOfWeekIdx,
dateFormat,
}: createCustomSpreadsheetProps) {
const [categoryList, categoryGroup] = reportCategoryLists({
categories,
conditions,
conditionsOp,
showOffBudget,
showHiddenCategories,
showUncategorized,
});
const [initialGroups, groupByLabel] = groupBySelections(
groupBy,
categoryList,
categoryGroup,
payees,
accounts,
);
return async (
spreadsheet: ReturnType<typeof useSpreadsheet>,
setData: (data: DataEntity) => void,
) => {
let groupByList = initialGroups;
let scopeTagNames: string[] | undefined;
const emptyData: DataEntity = {
data: [],
intervalData: [],
legend: [],
startDate,
endDate,
totalAssets: 0,
totalDebts: 0,
netAssets: 0,
netDebts: 0,
totalTotals: 0,
totalBudgeted: 0,
};
if (groupBy !== 'Tag' && groupByList.length === 0) {
setData(emptyData);
return;
}
const { filters } = await send('make-filters-from-conditions', {
conditions: conditions.filter(cond => !cond.customName),
});
const conditionsOpKey = conditionsOp === 'or' ? '$or' : '$and';
let assets: QueryDataEntity[];
let debts: QueryDataEntity[];
({ assets, debts } = await fetchSpreadsheetQueryData({
balanceTypeOp,
startDate,
endDate,
interval,
categories: categories.list,
categoryGroups: categories.grouped,
conditions,
conditionsOp,
conditionsOpKey,
filters,
budgetType,
groupBy,
}));
if (groupBy === 'Tag') {
const resolvedScope = resolveTagScope(tags, tagScope);
scopeTagNames = resolvedScope.map(tag => tag.tag);
const groupedQueryData = groupQueryDataByTags({
assets: filterReportTransactions(
assets,
showOffBudget,
showHiddenCategories,
showUncategorized,
),
debts: filterReportTransactions(
debts,
showOffBudget,
showHiddenCategories,
showUncategorized,
),
tags: resolvedScope,
showEmpty,
});
assets = groupedQueryData.assets;
debts = groupedQueryData.debts;
groupByList = groupedQueryData.groups;
}
if (groupByList.length === 0) {
setData(emptyData);
return;
}
if (interval === 'Weekly' && balanceTypeOp !== 'totalBudgeted') {
debts = debts.map(d => {
return {
...d,
date: monthUtils.weekFromDate(d.date, firstDayOfWeekIdx),
};
});
assets = assets.map(d => {
return {
...d,
date: monthUtils.weekFromDate(d.date, firstDayOfWeekIdx),
};
});
}
if (interval === 'Quarterly' && balanceTypeOp !== 'totalBudgeted') {
debts = debts.map(d => ({
...d,
date: monthUtils.quarterFromDate(d.date),
}));
assets = assets.map(d => ({
...d,
date: monthUtils.quarterFromDate(d.date),
}));
}
const intervals =
interval === 'Weekly'
? monthUtils.weekRangeInclusive(startDate, endDate, firstDayOfWeekIdx)
: monthUtils[
ReportOptions.intervalRange.get(interval) || 'rangeInclusive'
](startDate, endDate);
let totalAssets = 0;
let totalDebts = 0;
let netAssets = 0;
let netDebts = 0;
const groupsByCategory =
groupByLabel === 'category' || groupByLabel === 'categoryGroup';
const intervalData = intervals.reduce(
(arr: IntervalEntity[], intervalItem, index) => {
let perIntervalAssets = 0;
let perIntervalDebts = 0;
let perIntervalNetAssets = 0;
let perIntervalNetDebts = 0;
let perIntervalTotals = 0;
const stacked: Record<string, number> = {};
groupByList.map(item => {
let stackAmounts = 0;
const intervalAssets = filterHiddenItems(
item,
assets,
showOffBudget,
showHiddenCategories,
showUncategorized,
groupsByCategory,
)
.filter(
asset =>
asset.date === intervalItem &&
(asset[groupByLabel] === (item.id ?? null) ||
(item.uncategorized_id && groupsByCategory)),
)
.reduce((a, v) => a + v.amount, 0);
perIntervalAssets += intervalAssets;
const intervalDebts = filterHiddenItems(
item,
debts,
showOffBudget,
showHiddenCategories,
showUncategorized,
groupsByCategory,
)
.filter(
debt =>
debt.date === intervalItem &&
(debt[groupByLabel] === (item.id ?? null) ||
(item.uncategorized_id && groupsByCategory)),
)
.reduce((a, v) => a + v.amount, 0);
perIntervalDebts += intervalDebts;
const netAmounts = intervalAssets + intervalDebts;
if (balanceTypeOp === 'totalAssets') {
stackAmounts += intervalAssets;
}
if (balanceTypeOp === 'totalDebts') {
stackAmounts += Math.abs(intervalDebts);
}
if (balanceTypeOp === 'netAssets') {
stackAmounts += netAmounts > 0 ? netAmounts : 0;
}
if (balanceTypeOp === 'netDebts') {
stackAmounts = netAmounts < 0 ? Math.abs(netAmounts) : 0;
}
if (
balanceTypeOp === 'totalTotals' ||
balanceTypeOp === 'totalBudgeted'
) {
stackAmounts += netAmounts;
}
// Use id as key to prevent collisions when categories have the same name
stacked[item.id || item.name] = stackAmounts;
perIntervalTotals += netAmounts;
return null;
});
perIntervalNetAssets = perIntervalTotals > 0 ? perIntervalTotals : 0;
perIntervalNetDebts = perIntervalTotals < 0 ? perIntervalTotals : 0;
totalAssets += perIntervalAssets;
totalDebts += perIntervalDebts;
netAssets += perIntervalNetAssets;
netDebts += perIntervalNetDebts;
arr.push({
date: d.format(
d.parseISO(intervalItem),
getIntervalFormat(interval, dateFormat),
),
...stacked,
intervalStartDate: index === 0 ? startDate : intervalItem,
intervalEndDate:
index + 1 === intervals.length
? endDate
: monthUtils.subDays(intervals[index + 1], 1),
totalAssets: perIntervalAssets,
totalDebts: perIntervalDebts,
netAssets: perIntervalNetAssets,
netDebts: perIntervalNetDebts,
// `totalBudgeted` intentionally mirrors `totalTotals`; the difference is
// the underlying dataset when `balanceTypeOp === 'totalBudgeted'`.
totalTotals: perIntervalTotals,
totalBudgeted: perIntervalTotals,
});
return arr;
},
[],
);
const calcData: GroupedEntity[] = groupByList.map(item => {
const calc = recalculate({
item,
intervals,
assets,
debts,
groupByLabel,
showOffBudget,
showHiddenCategories,
showUncategorized,
startDate,
endDate,
});
return { ...calc };
});
// First, filter rows so trimming reflects the visible dataset
const calcDataFiltered = calcData.filter(i =>
filterEmptyRows({ showEmpty, data: i, balanceTypeOp }),
);
// Determine interval range across filtered groups and main intervalData
const { startIndex, endIndex } = determineIntervalRange(
calcDataFiltered,
intervalData,
trimIntervals,
balanceTypeOp,
);
// Trim only if enabled
const trimmedIntervalData = trimIntervals
? trimIntervalDataToRange(intervalData, startIndex, endIndex)
: intervalData;
if (trimIntervals) {
// Keep group data in sync with the trimmed range
trimIntervalsToRange(calcDataFiltered, startIndex, endIndex);
}
const sortedCalcDataFiltered = [...calcDataFiltered].sort(
sortData({ balanceTypeOp, sortByOp }),
);
const legend = calculateLegend(
trimmedIntervalData,
sortedCalcDataFiltered,
groupBy,
graphType,
balanceTypeOp,
);
setData({
data: sortedCalcDataFiltered,
intervalData: trimmedIntervalData,
legend,
startDate,
endDate,
totalAssets,
totalDebts,
netAssets,
netDebts,
// `totalBudgeted` intentionally mirrors `totalTotals`; the difference is
// the underlying dataset when `balanceTypeOp === 'totalBudgeted'`.
totalTotals: totalAssets + totalDebts,
totalBudgeted: totalAssets + totalDebts,
scopeTagNames,
});
};
}