Skip to content

A local-first personal finance app

2.3 KB
Raw Blame
import { q } from '@actual-app/core/shared/query';
import type { ObjectExpression } from '@actual-app/core/shared/query';

import { ReportOptions } from '#components/reports/ReportOptions';

export function makeQuery(
  name: string,
  startDate: string,
  endDate: string,
  interval: string,
  conditionsOpKey: string,
  filters: unknown[],
  groupBy?: string,
) {
  // Weekly and Quarterly have no AQL date transform, so they are queried at a
  // finer grain (days and months) and rolled up by the spreadsheets.
  const intervalGroup =
    interval === 'Monthly' || interval === 'Quarterly'
      ? { $month: '$date' }
      : interval === 'Yearly'
        ? { $year: '$date' }
        : { $day: '$date' };
  const intervalFilter =
    interval === 'Weekly'
      ? '$day'
      : interval === 'Quarterly'
        ? '$month'
        : '$' + ReportOptions.intervalMap.get(interval)?.toLowerCase() ||
          'month';

  const query = q('transactions')
    //Apply filters and split by "Group By"
    .filter({
      [conditionsOpKey]: filters,
    })
    //Apply month range filters
    .filter({
      $and: [
        { date: { $transform: intervalFilter, $gte: startDate } },
        { date: { $transform: intervalFilter, $lte: endDate } },
      ],
    })
    //Show assets or debts
    .filter(
      name === 'assets' ? { amount: { $gt: 0 } } : { amount: { $lt: 0 } },
    );

  const groupByFields: Array<ObjectExpression | string> = [
    intervalGroup,
    { $id: '$account' },
    { $id: '$payee' },
    { $id: '$category' },
    { $id: '$payee.transfer_acct.id' },
  ];
  const selectedFields: Array<ObjectExpression | string> = [
    { date: intervalGroup },
    { category: { $id: '$category.id' } },
    { categoryHidden: { $id: '$category.hidden' } },
    { categoryIncome: { $id: '$category.is_income' } },
    { categoryGroup: { $id: '$category.group.id' } },
    { categoryGroupHidden: { $id: '$category.group.hidden' } },
    { account: { $id: '$account.id' } },
    { accountOffBudget: { $id: '$account.offbudget' } },
    { payee: { $id: '$payee.id' } },
    { transferAccount: { $id: '$payee.transfer_acct.id' } },
    { amount: { $sum: '$amount' } },
  ];

  if (groupBy === 'Tag') {
    groupByFields.push('notes');
    selectedFields.push('notes');
  }

  return query.groupBy(groupByFields).select(selectedFields);
}