A local-first personal finance app
import { v4 as uuidv4 } from 'uuid';
import { createApp } from '#server/app';
import { aqlQuery } from '#server/aql';
import * as db from '#server/db';
import { ValidationError } from '#server/errors';
import { requiredFields } from '#server/models';
import { mutator } from '#server/mutators';
import { undoable } from '#server/undo';
import { q } from '#shared/query';
import type { CustomReportData, CustomReportEntity } from '#types/models';
export const reportModel = {
validate(
report: Omit<CustomReportEntity, 'tombstone'>,
{ update }: { update?: boolean } = {},
) {
requiredFields('Report', report, ['conditionsOp'], update);
if (!update || 'conditionsOp' in report) {
if (!['and', 'or'].includes(report.conditionsOp)) {
throw new ValidationError(
'Invalid filter conditionsOp: ' + report.conditionsOp,
);
}
}
if (report.groupBy === 'Tag' && report.balanceType === 'Budgeted') {
throw new ValidationError('Budgeted reports cannot be split by tags.');
}
const scope = report.tagScope;
if (
scope !== undefined &&
(scope === null ||
!['all', 'selected'].includes(scope.mode) ||
(scope.mode === 'selected' &&
(!Array.isArray(scope.tagIds) ||
scope.tagIds.some(id => typeof id !== 'string'))))
) {
throw new ValidationError('Invalid report tag scope.');
}
return report;
},
toJS(row: CustomReportData): CustomReportEntity {
const { tagScope, ...metadata } = row.metadata ?? {};
return {
id: row.id,
name: row.name ?? '',
startDate: row.start_date,
endDate: row.end_date,
isDateStatic: row.date_static === 1,
dateRange: row.date_range,
mode: row.mode,
groupBy: row.group_by,
tagScope,
sortBy: row.sort_by,
interval: row.interval,
balanceType: row.balance_type,
showEmpty: row.show_empty === 1,
showOffBudget: row.show_offbudget === 1,
showHiddenCategories: row.show_hidden === 1,
showUncategorized: row.show_uncategorized === 1,
trimIntervals: row.trim_intervals === 1,
showTrendLines: row.show_trend_lines === 1,
includeCurrentInterval: row.include_current === 1,
graphType: row.graph_type,
conditions: row.conditions ?? [],
conditionsOp: row.conditions_op ?? 'and',
metadata,
};
},
fromJS(report: CustomReportEntity): CustomReportData {
return {
id: report.id,
name: report.name,
start_date: report.startDate,
end_date: report.endDate,
date_static: report.isDateStatic ? 1 : 0,
date_range: report.dateRange,
mode: report.mode,
group_by: report.groupBy,
sort_by: report.sortBy ?? 'desc',
interval: report.interval,
balance_type: report.balanceType,
show_empty: report.showEmpty ? 1 : 0,
show_offbudget: report.showOffBudget ? 1 : 0,
show_hidden: report.showHiddenCategories ? 1 : 0,
show_uncategorized: report.showUncategorized ? 1 : 0,
trim_intervals: report.trimIntervals ? 1 : 0,
show_trend_lines: report.showTrendLines ? 1 : 0,
include_current: report.includeCurrentInterval ? 1 : 0,
graph_type: report.graphType,
conditions: report.conditions,
conditions_op: report.conditionsOp,
metadata: { ...report.metadata, tagScope: report.tagScope },
};
},
};
// Sort reports by alphabetical order
function sort(reports: CustomReportEntity[]) {
return reports.sort((a, b) =>
a.name && b.name
? a.name.trim().localeCompare(b.name.trim(), undefined, {
ignorePunctuation: true,
})
: 0,
);
}
async function getReports() {
// Use aql because it auto deserialized json columns e.g. conditions
const { data }: { data: CustomReportData[] } = await aqlQuery(
q('custom_reports').select('*'),
);
return sort(data.map(r => reportModel.toJS(r)));
}
async function reportNameExists(
name: string,
reportId: string,
newItem: boolean,
) {
const idForName = await db.first<Pick<db.DbCustomReport, 'id'>>(
'SELECT id from custom_reports WHERE tombstone = 0 AND name = ?',
[name],
);
//no existing name found
if (idForName === null) {
return false;
}
//for update/rename
if (!newItem) {
/*
-if the found item is the same as the existing item
then no name change was made.
-if they are not the same then there is another
item with that name already.
*/
return idForName.id !== reportId;
}
//default return: item was found but does not match current name
return true;
}
async function createReport(report: CustomReportEntity) {
const reportId = uuidv4();
const item: CustomReportEntity = {
...report,
id: reportId,
};
if (!item.name) {
throw new Error('Report name is required');
}
const nameExists = await reportNameExists(item.name, item.id ?? '', true);
if (nameExists) {
throw new Error('There is already a report named ' + item.name);
}
// Create the report here based on the info
await db.insertWithSchema('custom_reports', reportModel.fromJS(item));
return reportId;
}
async function updateReport(item: CustomReportEntity) {
if (!item.name) {
throw new Error('Report name is required');
}
if (!item.id) {
throw new Error('Report recall error');
}
const nameExists = await reportNameExists(item.name, item.id, false);
if (nameExists) {
throw new Error('There is already a report named ' + item.name);
}
await db.updateWithSchema('custom_reports', reportModel.fromJS(item));
}
async function deleteReport(id: CustomReportEntity['id']) {
await db.delete_('custom_reports', id);
}
export type ReportsHandlers = {
'report/get': typeof getReports;
'report/create': typeof createReport;
'report/update': typeof updateReport;
'report/delete': typeof deleteReport;
};
// Expose functions to the client
export const app = createApp<ReportsHandlers>();
app.method('report/get', getReports);
app.method('report/create', mutator(undoable(createReport)));
app.method('report/update', mutator(undoable(updateReport)));
app.method('report/delete', mutator(undoable(deleteReport)));