From 27b95c9186bd45edd4c982a7cc89cf69cf2c5d46 Mon Sep 17 00:00:00 2001 From: Joe Carstairs Date: Wed, 16 Oct 2024 12:51:21 +0100 Subject: All backend stuff lives in backend folder --- backend/app/src/queries/accounts/get_all.rs | 7 ++++ backend/app/src/queries/accounts/insert.rs | 14 +++++++ backend/app/src/queries/accounts/mod.rs | 5 +++ backend/app/src/queries/budget_updates/get_all.rs | 13 +++++++ .../queries/budget_updates/get_first_with_date.rs | 19 ++++++++++ .../get_most_recent_with_category_id.rs | 18 +++++++++ backend/app/src/queries/budget_updates/mod.rs | 7 ++++ backend/app/src/queries/categories/get_all.rs | 8 ++++ backend/app/src/queries/categories/get_all_ids.rs | 8 ++++ backend/app/src/queries/categories/mod.rs | 5 +++ .../app/src/queries/category_balances/get_all.rs | 43 ++++++++++++++++++++++ backend/app/src/queries/category_balances/mod.rs | 3 ++ .../get_most_recent_per_category_id.rs | 42 +++++++++++++++++++++ backend/app/src/queries/category_budgets/mod.rs | 3 ++ backend/app/src/queries/category_transfers/mod.rs | 5 +++ .../sum_quantity_per_from_category_id.rs | 17 +++++++++ .../sum_quantity_per_to_category_id.rs | 17 +++++++++ backend/app/src/queries/mod.rs | 7 ++++ .../src/queries/transaction_categorisations/mod.rs | 3 ++ .../sum_quantity_per_category_id.rs | 17 +++++++++ .../sum_quantity_per_to_category_id.rs | 0 21 files changed, 261 insertions(+) create mode 100644 backend/app/src/queries/accounts/get_all.rs create mode 100644 backend/app/src/queries/accounts/insert.rs create mode 100644 backend/app/src/queries/accounts/mod.rs create mode 100644 backend/app/src/queries/budget_updates/get_all.rs create mode 100644 backend/app/src/queries/budget_updates/get_first_with_date.rs create mode 100644 backend/app/src/queries/budget_updates/get_most_recent_with_category_id.rs create mode 100644 backend/app/src/queries/budget_updates/mod.rs create mode 100644 backend/app/src/queries/categories/get_all.rs create mode 100644 backend/app/src/queries/categories/get_all_ids.rs create mode 100644 backend/app/src/queries/categories/mod.rs create mode 100644 backend/app/src/queries/category_balances/get_all.rs create mode 100644 backend/app/src/queries/category_balances/mod.rs create mode 100644 backend/app/src/queries/category_budgets/get_most_recent_per_category_id.rs create mode 100644 backend/app/src/queries/category_budgets/mod.rs create mode 100644 backend/app/src/queries/category_transfers/mod.rs create mode 100644 backend/app/src/queries/category_transfers/sum_quantity_per_from_category_id.rs create mode 100644 backend/app/src/queries/category_transfers/sum_quantity_per_to_category_id.rs create mode 100644 backend/app/src/queries/mod.rs create mode 100644 backend/app/src/queries/transaction_categorisations/mod.rs create mode 100644 backend/app/src/queries/transaction_categorisations/sum_quantity_per_category_id.rs create mode 100644 backend/app/src/queries/transaction_categorisations/sum_quantity_per_to_category_id.rs (limited to 'backend/app/src/queries') diff --git a/backend/app/src/queries/accounts/get_all.rs b/backend/app/src/queries/accounts/get_all.rs new file mode 100644 index 0000000..12941ac --- /dev/null +++ b/backend/app/src/queries/accounts/get_all.rs @@ -0,0 +1,7 @@ +use diesel::{result::Error, QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection}; + +use crate::{models::account::Account, schema::accounts::dsl::accounts}; + +pub fn get_all_accounts(connection: &mut SqliteConnection) -> Result, Error> { + accounts.select(Account::as_select()).load(connection) +} diff --git a/backend/app/src/queries/accounts/insert.rs b/backend/app/src/queries/accounts/insert.rs new file mode 100644 index 0000000..c20cf60 --- /dev/null +++ b/backend/app/src/queries/accounts/insert.rs @@ -0,0 +1,14 @@ +use diesel::prelude::*; +use diesel::result::Error; + +use crate::models::account::Account; +use crate::schema::accounts::dsl::accounts as accounts_table; + +pub fn insert_account( + accounts: &[Account], + connection: &mut SqliteConnection, +) -> Result { + diesel::insert_into(accounts_table) + .values(accounts) + .execute(connection) +} diff --git a/backend/app/src/queries/accounts/mod.rs b/backend/app/src/queries/accounts/mod.rs new file mode 100644 index 0000000..94fa360 --- /dev/null +++ b/backend/app/src/queries/accounts/mod.rs @@ -0,0 +1,5 @@ +mod get_all; +mod insert; + +pub use get_all::get_all_accounts; +pub use insert::insert_account; diff --git a/backend/app/src/queries/budget_updates/get_all.rs b/backend/app/src/queries/budget_updates/get_all.rs new file mode 100644 index 0000000..97f2f2f --- /dev/null +++ b/backend/app/src/queries/budget_updates/get_all.rs @@ -0,0 +1,13 @@ +use crate::{ + models::budget_update::BudgetUpdate, + schema::budget_updates::dsl::budget_updates as budget_updates_table, +}; +use diesel::{result::Error, QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection}; + +pub fn get_all_budget_updates( + connection: &mut SqliteConnection, +) -> Result, Error> { + budget_updates_table + .select(BudgetUpdate::as_select()) + .load::(connection) +} diff --git a/backend/app/src/queries/budget_updates/get_first_with_date.rs b/backend/app/src/queries/budget_updates/get_first_with_date.rs new file mode 100644 index 0000000..a2824be --- /dev/null +++ b/backend/app/src/queries/budget_updates/get_first_with_date.rs @@ -0,0 +1,19 @@ +use crate::{ + models::budget_update::BudgetUpdate, + schema::budget_updates::{ + self as budget_updates_schema, dsl::budget_updates as budget_updates_table, + }, +}; +use diesel::{ + result::Error, ExpressionMethods, QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection, +}; + +pub fn get_first_budget_update_with_date( + date: &str, + connection: &mut SqliteConnection, +) -> Result { + budget_updates_table + .select(BudgetUpdate::as_select()) + .filter(budget_updates_schema::date.eq(date)) + .first(connection) +} diff --git a/backend/app/src/queries/budget_updates/get_most_recent_with_category_id.rs b/backend/app/src/queries/budget_updates/get_most_recent_with_category_id.rs new file mode 100644 index 0000000..4753092 --- /dev/null +++ b/backend/app/src/queries/budget_updates/get_most_recent_with_category_id.rs @@ -0,0 +1,18 @@ +use crate::{ + models::date_time_utc::DateTimeUtc, + schema::budget_updates::{ + self as budget_updates_schema, dsl::budget_updates as budget_updates_table, + }, +}; +use diesel::{dsl::max, result::Error, ExpressionMethods, QueryDsl, RunQueryDsl, SqliteConnection}; + +pub fn get_most_recent_budget_update_date_with_category_id( + category_id: i32, + connection: &mut SqliteConnection, +) -> Result, Error> { + budget_updates_table + .select(max(budget_updates_schema::date)) + .filter(budget_updates_schema::category_id.eq(category_id)) + .filter(budget_updates_schema::date.le(DateTimeUtc::now().to_day_str())) + .first(connection) +} diff --git a/backend/app/src/queries/budget_updates/mod.rs b/backend/app/src/queries/budget_updates/mod.rs new file mode 100644 index 0000000..3b514dd --- /dev/null +++ b/backend/app/src/queries/budget_updates/mod.rs @@ -0,0 +1,7 @@ +mod get_all; +mod get_first_with_date; +mod get_most_recent_with_category_id; + +pub use get_all::get_all_budget_updates; +pub use get_first_with_date::get_first_budget_update_with_date; +pub use get_most_recent_with_category_id::get_most_recent_budget_update_date_with_category_id; diff --git a/backend/app/src/queries/categories/get_all.rs b/backend/app/src/queries/categories/get_all.rs new file mode 100644 index 0000000..db5a073 --- /dev/null +++ b/backend/app/src/queries/categories/get_all.rs @@ -0,0 +1,8 @@ +use crate::{models::category::Category, schema::categories::dsl::categories as categories_table}; +use diesel::{result::Error, QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection}; + +pub fn get_all_categories(connection: &mut SqliteConnection) -> Result, Error> { + categories_table + .select(Category::as_select()) + .load(connection) +} diff --git a/backend/app/src/queries/categories/get_all_ids.rs b/backend/app/src/queries/categories/get_all_ids.rs new file mode 100644 index 0000000..7c7c912 --- /dev/null +++ b/backend/app/src/queries/categories/get_all_ids.rs @@ -0,0 +1,8 @@ +use crate::schema::categories::{self as categories_schema, dsl::categories as categories_table}; +use diesel::{result::Error, QueryDsl, RunQueryDsl, SqliteConnection}; + +pub fn get_all_category_ids(connection: &mut SqliteConnection) -> Result, Error> { + categories_table + .select(categories_schema::id) + .load(connection) +} diff --git a/backend/app/src/queries/categories/mod.rs b/backend/app/src/queries/categories/mod.rs new file mode 100644 index 0000000..80356b3 --- /dev/null +++ b/backend/app/src/queries/categories/mod.rs @@ -0,0 +1,5 @@ +mod get_all; +mod get_all_ids; + +pub use self::get_all::get_all_categories; +pub use self::get_all_ids::get_all_category_ids; diff --git a/backend/app/src/queries/category_balances/get_all.rs b/backend/app/src/queries/category_balances/get_all.rs new file mode 100644 index 0000000..713fbeb --- /dev/null +++ b/backend/app/src/queries/category_balances/get_all.rs @@ -0,0 +1,43 @@ +use crate::{ + models::category_balance::CategoryBalance, + queries::{ + budget_updates::get_all_budget_updates, + categories::get_all_categories, + category_transfers::{ + sum_category_transfer_quantity_per_from_category_id, + sum_category_transfer_quantity_per_to_category_id, + }, + transaction_categorisations::sum_transaction_categorisation_quantity_per_category_id, + }, + utils::calculate_category_balance, +}; +use diesel::{result::Error, SqliteConnection}; + +pub fn get_all_category_balances( + connection: &mut SqliteConnection, +) -> Result, Error> { + let categories = get_all_categories(connection)?; + let transaction_sums = sum_transaction_categorisation_quantity_per_category_id(connection)?; + let category_transfer_from_sums = + sum_category_transfer_quantity_per_from_category_id(connection)?; + let category_transfer_to_sums = sum_category_transfer_quantity_per_to_category_id(connection)?; + let budget_updates = get_all_budget_updates(connection)?; + + let mut balances = Vec::::new(); + for category in categories { + let balance = calculate_category_balance( + &category, + &transaction_sums, + &category_transfer_from_sums, + &category_transfer_to_sums, + &budget_updates, + ); + + balances.push(CategoryBalance { + category_id: category.id, + balance: balance.try_into().unwrap(), + }) + } + + Ok(balances) +} diff --git a/backend/app/src/queries/category_balances/mod.rs b/backend/app/src/queries/category_balances/mod.rs new file mode 100644 index 0000000..3fc1be2 --- /dev/null +++ b/backend/app/src/queries/category_balances/mod.rs @@ -0,0 +1,3 @@ +mod get_all; + +pub use get_all::get_all_category_balances; diff --git a/backend/app/src/queries/category_budgets/get_most_recent_per_category_id.rs b/backend/app/src/queries/category_budgets/get_most_recent_per_category_id.rs new file mode 100644 index 0000000..6b76efd --- /dev/null +++ b/backend/app/src/queries/category_budgets/get_most_recent_per_category_id.rs @@ -0,0 +1,42 @@ +use crate::{ + models::{budget::Budget, category_budget::CategoryBudget}, + queries::{ + budget_updates::{ + get_first_budget_update_with_date, get_most_recent_budget_update_date_with_category_id, + }, + categories::get_all_category_ids, + }, +}; +use diesel::{result::Error, SqliteConnection}; + +pub fn get_most_recent_category_budget_per_category_id( + connection: &mut SqliteConnection, +) -> Result, Error> { + let category_ids: Vec = get_all_category_ids(connection)?; + + let mut budgets = Vec::::new(); + for category_id in category_ids { + let most_recent_budget_update_date = + get_most_recent_budget_update_date_with_category_id(category_id, connection)?; + + if most_recent_budget_update_date.is_none() { + budgets.push(CategoryBudget { + category_id, + budget: Option::None, + }); + continue; + } + + let most_recent_budget_update = get_first_budget_update_with_date( + most_recent_budget_update_date.unwrap().as_str(), + connection, + )?; + + budgets.push(CategoryBudget { + category_id, + budget: Some(Budget::from(&most_recent_budget_update)), + }); + } + + Ok(budgets) +} diff --git a/backend/app/src/queries/category_budgets/mod.rs b/backend/app/src/queries/category_budgets/mod.rs new file mode 100644 index 0000000..29fc246 --- /dev/null +++ b/backend/app/src/queries/category_budgets/mod.rs @@ -0,0 +1,3 @@ +mod get_most_recent_per_category_id; + +pub use self::get_most_recent_per_category_id::get_most_recent_category_budget_per_category_id; diff --git a/backend/app/src/queries/category_transfers/mod.rs b/backend/app/src/queries/category_transfers/mod.rs new file mode 100644 index 0000000..85eb1c8 --- /dev/null +++ b/backend/app/src/queries/category_transfers/mod.rs @@ -0,0 +1,5 @@ +mod sum_quantity_per_from_category_id; +mod sum_quantity_per_to_category_id; + +pub use sum_quantity_per_from_category_id::sum_category_transfer_quantity_per_from_category_id; +pub use sum_quantity_per_to_category_id::sum_category_transfer_quantity_per_to_category_id; diff --git a/backend/app/src/queries/category_transfers/sum_quantity_per_from_category_id.rs b/backend/app/src/queries/category_transfers/sum_quantity_per_from_category_id.rs new file mode 100644 index 0000000..ae3a51a --- /dev/null +++ b/backend/app/src/queries/category_transfers/sum_quantity_per_from_category_id.rs @@ -0,0 +1,17 @@ +use diesel::{dsl::sum, result::Error, QueryDsl, RunQueryDsl, SqliteConnection}; + +use crate::schema::category_transfers::{ + self as category_transfers_schema, dsl::category_transfers as category_transfers_table, +}; + +pub fn sum_category_transfer_quantity_per_from_category_id( + connection: &mut SqliteConnection, +) -> Result)>, Error> { + category_transfers_table + .group_by(category_transfers_schema::from_category_id) + .select(( + category_transfers_schema::from_category_id, + sum(category_transfers_schema::quantity), + )) + .load::<(i32, Option)>(connection) +} diff --git a/backend/app/src/queries/category_transfers/sum_quantity_per_to_category_id.rs b/backend/app/src/queries/category_transfers/sum_quantity_per_to_category_id.rs new file mode 100644 index 0000000..fe6f3a7 --- /dev/null +++ b/backend/app/src/queries/category_transfers/sum_quantity_per_to_category_id.rs @@ -0,0 +1,17 @@ +use diesel::{dsl::sum, result::Error, QueryDsl, RunQueryDsl, SqliteConnection}; + +use crate::schema::category_transfers::{ + self as category_transfers_schema, dsl::category_transfers as category_transfers_table, +}; + +pub fn sum_category_transfer_quantity_per_to_category_id( + connection: &mut SqliteConnection, +) -> Result)>, Error> { + category_transfers_table + .group_by(category_transfers_schema::to_category_id) + .select(( + category_transfers_schema::to_category_id, + sum(category_transfers_schema::quantity), + )) + .load::<(i32, Option)>(connection) +} diff --git a/backend/app/src/queries/mod.rs b/backend/app/src/queries/mod.rs new file mode 100644 index 0000000..43328e2 --- /dev/null +++ b/backend/app/src/queries/mod.rs @@ -0,0 +1,7 @@ +pub mod accounts; +pub mod budget_updates; +pub mod categories; +pub mod category_balances; +pub mod category_budgets; +pub mod category_transfers; +pub mod transaction_categorisations; diff --git a/backend/app/src/queries/transaction_categorisations/mod.rs b/backend/app/src/queries/transaction_categorisations/mod.rs new file mode 100644 index 0000000..d4217cc --- /dev/null +++ b/backend/app/src/queries/transaction_categorisations/mod.rs @@ -0,0 +1,3 @@ +mod sum_quantity_per_category_id; + +pub use sum_quantity_per_category_id::sum_transaction_categorisation_quantity_per_category_id; diff --git a/backend/app/src/queries/transaction_categorisations/sum_quantity_per_category_id.rs b/backend/app/src/queries/transaction_categorisations/sum_quantity_per_category_id.rs new file mode 100644 index 0000000..7f93e0c --- /dev/null +++ b/backend/app/src/queries/transaction_categorisations/sum_quantity_per_category_id.rs @@ -0,0 +1,17 @@ +use crate::schema::transaction_categorisations::{ + self as transaction_categorisations_schema, + dsl::transaction_categorisations as transaction_categorisations_table, +}; +use diesel::{dsl::sum, result::Error, QueryDsl, RunQueryDsl, SqliteConnection}; + +pub fn sum_transaction_categorisation_quantity_per_category_id( + connection: &mut SqliteConnection, +) -> Result)>, Error> { + transaction_categorisations_table + .group_by(transaction_categorisations_schema::category_id) + .select(( + transaction_categorisations_schema::category_id, + sum(transaction_categorisations_schema::quantity), + )) + .load::<(i32, Option)>(connection) +} diff --git a/backend/app/src/queries/transaction_categorisations/sum_quantity_per_to_category_id.rs b/backend/app/src/queries/transaction_categorisations/sum_quantity_per_to_category_id.rs new file mode 100644 index 0000000..e69de29 -- cgit v1.2.3