Skip to content

Instantly share code, notes, and snippets.

@nip10

nip10/budgets.ts

Created December 8, 2025 00:04
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save nip10/5b89173eee74496d019c93c386d56e92 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save nip10/5b89173eee74496d019c93c386d56e92 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
budgets.ts
export async function getBudgetTemplateByYearAndMonth(req: Request, res: Response, next: NextFunction) {
const {
params: { budgetKey, year, month },
} = await zParse(getBudgetTemplateByYearAndMonthRequestSchema, req);
const budget = await DI.em.findOne(
Budget,
{
budgetKey,
appuserKey: req.user,
deletedAt: null,
},
{
populate: ['currencyKey', 'budgetTemplatesCategory', 'budgetTemplatesCategory.accounts'],
populateWhere: PopulateHint.INFER,
},
);
if (!budget) {
return next(new NotFoundError('Budget not found'));
}
const budgetIncomes = await DI.em.find(BudgetTemplateIncome, {
budgetKey: { budgetKey },
});
const budgetTemplates = budget.budgetTemplatesCategory.getItems();
if (!budgetTemplates.length) {
return next(new NotFoundError('Budget template not found'));
}
const currentMonthBudgetTemplates = budgetTemplates.filter((bt) => bt.year === year && bt.month === month);
if (currentMonthBudgetTemplates.length === 0) {
return next(new NotFoundError('Budget templates not found'));
}
const accounts = currentMonthBudgetTemplates.flatMap((template) => template.accounts.getItems());
const knex = DI.em.getKnex();
const childCategoryAmountsSubquery = knex
.select(
'child_category.category_key AS child_category_key',
'child_category.name AS child_category_name',
'child_category.icon AS child_category_icon',
'child_category.text_color AS child_category_text_color',
'child_category.bg_color AS child_category_bg_color',
'child_category.is_system_category AS child_category_is_system_category',
'parent_category.name AS parent_category_name',
knex.raw('ABS(SUM(COALESCE(transaction.appuser_settlement_amount, 0))) AS category_amount'),
'budget_template_category.amount_budgeted AS category_budgeted_amount',
'budget_template_category.recurrency AS category_recurrency',
)
.from('budget')
.join('budget_template_category', 'budget.budget_key', '=', 'budget_template_category.budget_key')
.join('category as child_category', 'budget_template_category.category_key', '=', 'child_category.category_key')
.leftJoin(
'budget_template_category_accounts',
'budget.budget_key',
'=',
'budget_template_category_accounts.budget_template_category_budget_template_category_key',
)
.leftJoin(
'category as parent_category',
'child_category.parent_category_category_key',
'=',
'parent_category.category_key',
)
.leftJoin(
'appuser_transaction_category',
'child_category.category_key',
'=',
'appuser_transaction_category.category_key',
)
.leftJoin('transaction', function () {
this.on('appuser_transaction_category.transaction_key', '=', 'transaction.transaction_key')
.andOnIn(
'transaction.account_key',
accounts.map((a) => a.accountKey),
)
.andOn(knex.raw("date_part('year', transaction.transaction_date) = budget_template_category.year"))
.andOn(knex.raw("date_part('month', transaction.transaction_date) = budget_template_category.month"));
})
.whereIn(
'budget_template_category.budget_template_category_key',
currentMonthBudgetTemplates.map((template) => template.budgetTemplateCategoryKey),
)
.whereNotNull('parent_category.name')
.whereNull('child_category.deleted_at')
.whereNull('parent_category.deleted_at')
.where(function () {
this.where('child_category.is_system_category', true).orWhere(
'child_category.owner_appuser_key',
req.user!.appuserKey,
);
})
.where(function () {
this.where('parent_category.is_system_category', true)
.orWhere('parent_category.owner_appuser_key', req.user!.appuserKey)
.orWhereNull('parent_category.owner_appuser_key');
})
.groupBy(
'child_category.category_key',
'parent_category.name',
'child_category.name',
'budget_template_category.amount_budgeted',
'budget_template_category.recurrency',
);
const parentCategoryAmountsSubQuery = knex
.select(
'parent_category.category_key AS parent_category_key',
'parent_category.name AS parent_category_name',
'parent_category.icon AS parent_category_icon',
'parent_category.text_color AS parent_category_text_color',
'parent_category.bg_color AS parent_category_bg_color',
'parent_category.is_system_category AS parent_category_is_system_category',
knex.raw('SUM(COALESCE(child_category_amounts.category_amount, 0)) AS category_amount'),
knex.raw('SUM(COALESCE(child_category_amounts.category_budgeted_amount, 0)) AS category_budgeted_amount'),
)
.from('category as parent_category')
.leftJoin(
'category as child_category',
'parent_category.category_key',
'=',
'child_category.parent_category_category_key',
)
.leftJoin('child_category_amounts', 'child_category.category_key', '=', 'child_category_amounts.child_category_key')
.leftJoin('budget_template_category as bc', 'parent_category.category_key', '=', 'bc.category_key')
.whereNull('parent_category.parent_category_category_key')
.whereNull('parent_category.deleted_at')
.whereNull('child_category.deleted_at')
.where(function () {
this.where('parent_category.is_system_category', true).orWhere(
'parent_category.owner_appuser_key',
req.user!.appuserKey,
);
})
.groupBy('parent_category.category_key', 'parent_category.name');
const unionQuery = knex
.unionAll(function () {
this.select(
'child_category_name',
'parent_category_name',
'category_amount',
'category_recurrency',
'child_category_icon',
'child_category_text_color',
'child_category_bg_color',
'child_category_is_system_category',
knex.raw('null as parent_category_icon'),
knex.raw('null as parent_category_text_color'),
knex.raw('null as parent_category_bg_color'),
knex.raw('null as parent_category_is_system_category'),
knex.raw('COALESCE(category_budgeted_amount, 0) as category_budgeted_amount'),
'child_category_key',
knex.raw('null as parent_category_key'),
)
.from('child_category_amounts')
.unionAll(function () {
this.select(
knex.raw('CAST(null AS text) as child_category_name'),
'parent_category_name',
'category_amount',
knex.raw('null as category_recurrency'),
knex.raw('null as child_category_icon'),
knex.raw('null as child_category_text_color'),
knex.raw('null as child_category_bg_color'),
knex.raw('null as child_category_is_system_category'),
'parent_category_icon',
'parent_category_text_color',
'parent_category_bg_color',
'parent_category_is_system_category',
knex.raw('COALESCE(category_budgeted_amount, 0) as category_budgeted_amount'),
knex.raw('null as child_category_key'),
'parent_category_key',
).from('parent_category_amounts');
});
})
.as('t');
const budgetAmount = (await knex
.with('child_category_amounts', childCategoryAmountsSubquery)
.with('parent_category_amounts', parentCategoryAmountsSubQuery)
.select(
't.child_category_name',
't.parent_category_name',
't.category_amount',
't.category_budgeted_amount',
't.category_recurrency',
knex.raw('COALESCE(t.child_category_icon, t.parent_category_icon) as category_icon'),
knex.raw('COALESCE(t.child_category_text_color, t.parent_category_text_color) as category_text_color'),
knex.raw('COALESCE(t.child_category_bg_color, t.parent_category_bg_color) as category_bg_color'),
knex.raw(
'COALESCE(t.child_category_is_system_category, t.parent_category_is_system_category) as category_is_system_category',
),
knex.raw('COALESCE(t.parent_category_key, t.child_category_key) as category_key'),
)
.from(unionQuery)
.whereNotNull('parent_category_name')
.orderBy('parent_category_name')
.orderBy('child_category_name', QueryOrder.DESC)) as {
child_category_name: string | null;
parent_category_name: string;
category_amount: number;
category_recurrency: Recurrency;
category_budgeted_amount: number;
child_category_icon: string;
child_category_text_color: string;
child_category_bg_color: string;
category_is_system_category: boolean;
parent_category_icon: string;
parent_category_text_color: string;
parent_category_bg_color: string;
category_key: number;
}[];
// Create a date object for the 15th of the month to avoid timezone edge cases
const currentDate = new Date(year, month - 1, 15); // month - 1 because Date constructor expects 0-based month
const nextMonth = addMonths(currentDate, 1);
const previousMonth = subMonths(currentDate, 1);
return res.json({
hasPreviousMonth: budgetTemplates.some(
(budgetTemplate) =>
budgetTemplate.month === previousMonth.getMonth() + 1 && budgetTemplate.year === previousMonth.getFullYear(),
),
hasNextMonth: budgetTemplates.some(
(budgetTemplate) =>
budgetTemplate.month === nextMonth.getMonth() + 1 && budgetTemplate.year === nextMonth.getFullYear(),
),
budget: {
budgetKey: budget.budgetKey,
currencyCode: budget.currencyKey.code,
name: budget.name,
description: budget.description,
month: budget.budgetTemplatesCategory[0].month,
year: budget.budgetTemplatesCategory[0].year,
accounts: budget.budgetTemplatesCategory[0].accounts.getItems().map((account) => ({
accountKey: account.accountKey,
accountName: account.accountName,
})),
categories: budgetAmount,
incomes: budgetIncomes,
},
});
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment