1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
import { and, between, eq } from 'drizzle-orm';
import { db } from '@/db/client';
import { oneTimeExpenses } from '@/db/schema';
import type {
CreateOneTimeExpenseInput,
UpdateOneTimeExpenseInput,
} from './one-time-expenses.schema';
export async function findOneTimeExpensesByUser(
userId: number,
filters?: { month?: number; year?: number },
) {
const conditions = [eq(oneTimeExpenses.user_id, userId)];
// Filtra pelo mês/ano usando range de datas no formato YYYY-MM-DD
if (filters?.month !== undefined && filters?.year !== undefined) {
const year = filters.year;
const month = filters.month;
const startDate = `${year}-${String(month).padStart(2, '0')}-01`;
// Último dia do mês: pega o dia 0 do mês seguinte
const lastDay = new Date(year, month, 0).getDate();
const endDate = `${year}-${String(month).padStart(2, '0')}-${String(lastDay).padStart(2, '0')}`;
conditions.push(between(oneTimeExpenses.expense_date, startDate, endDate));
} else if (filters?.year !== undefined) {
const year = filters.year;
conditions.push(
between(oneTimeExpenses.expense_date, `${year}-01-01`, `${year}-12-31`),
);
}
return db
.select()
.from(oneTimeExpenses)
.where(and(...conditions))
.orderBy(oneTimeExpenses.expense_date);
}
export async function findOneTimeExpenseById(userId: number, id: number) {
const result = await db
.select()
.from(oneTimeExpenses)
.where(
and(eq(oneTimeExpenses.user_id, userId), eq(oneTimeExpenses.id, id)),
)
.limit(1);
return result[0] ?? null;
}
export async function createOneTimeExpense(
userId: number,
data: CreateOneTimeExpenseInput,
) {
const [created] = await db
.insert(oneTimeExpenses)
.values({
user_id: userId,
name: data.name,
amount: data.amount,
category: data.category,
type: data.type,
expense_date: data.expense_date,
notes: data.notes ?? null,
})
.returning();
return created;
}
export async function updateOneTimeExpense(
userId: number,
id: number,
data: UpdateOneTimeExpenseInput,
) {
const now = new Date().toISOString();
const [updated] = await db
.update(oneTimeExpenses)
.set({
...(data.name !== undefined && { name: data.name }),
...(data.amount !== undefined && { amount: data.amount }),
...(data.category !== undefined && { category: data.category }),
...(data.type !== undefined && { type: data.type }),
...(data.expense_date !== undefined && { expense_date: data.expense_date }),
...(data.notes !== undefined && { notes: data.notes }),
updated_at: now,
})
.where(
and(eq(oneTimeExpenses.user_id, userId), eq(oneTimeExpenses.id, id)),
)
.returning();
return updated ?? null;
}
export async function deleteOneTimeExpense(userId: number, id: number) {
const [deleted] = await db
.delete(oneTimeExpenses)
.where(
and(eq(oneTimeExpenses.user_id, userId), eq(oneTimeExpenses.id, id)),
)
.returning();
return deleted ?? null;
}
|
