37 lines
1.1 KiB
SQL
37 lines
1.1 KiB
SQL
-- name: CreateBudget :one
|
|
INSERT INTO budgets
|
|
(name, income_category_id, last_modification)
|
|
VALUES ($1, $2, NOW())
|
|
RETURNING *;
|
|
|
|
-- name: SetInflowCategory :exec
|
|
UPDATE budgets
|
|
SET income_category_id = $1
|
|
WHERE budgets.id = $2;
|
|
|
|
-- name: GetBudgetsForUser :many
|
|
SELECT budgets.* FROM budgets
|
|
LEFT JOIN user_budgets ON budgets.id = user_budgets.budget_id
|
|
WHERE user_budgets.user_id = $1;
|
|
|
|
-- name: GetBudget :one
|
|
SELECT * FROM budgets
|
|
WHERE id = $1;
|
|
|
|
-- name: GetFirstActivity :one
|
|
SELECT MIN(dates.min_date)::date as min_date
|
|
FROM (
|
|
SELECT MIN(assignments.date) as min_date
|
|
FROM assignments
|
|
INNER JOIN categories ON categories.id = assignments.category_id
|
|
INNER JOIN category_groups ON category_groups.id = categories.category_group_id
|
|
WHERE category_groups.budget_id = @budget_id
|
|
UNION
|
|
SELECT MIN(transactions.date) as min_date
|
|
FROM transactions
|
|
INNER JOIN accounts ON accounts.id = transactions.account_id
|
|
WHERE accounts.budget_id = @budget_id
|
|
) dates;
|
|
|
|
-- name: DeleteBudget :exec
|
|
DELETE FROM budgets WHERE id = $1; |