diff options
Diffstat (limited to 'backend/core')
46 files changed, 882 insertions, 5 deletions
diff --git a/backend/core/Cargo.lock b/backend/core/Cargo.lock index 0a4e0fd..a889422 100644 --- a/backend/core/Cargo.lock +++ b/backend/core/Cargo.lock @@ -18,6 +18,12 @@ dependencies = [ ] [[package]] +name = "anyhow" +version = "1.0.90" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "37bf3594c4c988a53154954629820791dde498571819ae4ca50ca811e060cc95" + +[[package]] name = "autocfg" version = "1.4.0" source = "registry+https://github.com/rust-lang/crates.io-index" @@ -27,6 +33,7 @@ checksum = "ace50bade8e6234aa140d9a2f552bbee1db4d353f69b8217bc503490fc1a9f26" name = "budgeting_app_core" version = "0.1.0" dependencies = [ + "anyhow", "chrono", "diesel", "diesel_migrations", diff --git a/backend/core/Cargo.toml b/backend/core/Cargo.toml index 09b1ebf..bf87568 100644 --- a/backend/core/Cargo.toml +++ b/backend/core/Cargo.toml @@ -4,6 +4,7 @@ version = "0.1.0" edition = "2021" [dependencies] +anyhow = { version = "1.0.0" } chrono = { version = "0.4.38", features = ["serde"] } diesel = { version = "2.2.0", features = ["sqlite"] } diesel_migrations = "2.2.0" diff --git a/backend/core/2024-08-31-084439_initial_setup/down.sql b/backend/core/migrations/2024-08-31-084439_initial_setup/down.sql index 8cf4b1e..8cf4b1e 100644 --- a/backend/core/2024-08-31-084439_initial_setup/down.sql +++ b/backend/core/migrations/2024-08-31-084439_initial_setup/down.sql diff --git a/backend/core/2024-08-31-084439_initial_setup/up.sql b/backend/core/migrations/2024-08-31-084439_initial_setup/up.sql index 3584ae7..3584ae7 100644 --- a/backend/core/2024-08-31-084439_initial_setup/up.sql +++ b/backend/core/migrations/2024-08-31-084439_initial_setup/up.sql diff --git a/backend/core/src/lib.rs b/backend/core/src/lib.rs index 7a56560..cb65fe1 100644 --- a/backend/core/src/lib.rs +++ b/backend/core/src/lib.rs @@ -1,6 +1,9 @@ use diesel_migrations::{embed_migrations, EmbeddedMigrations}; pub mod models; +pub mod queries; pub mod schema; +mod utils; + pub const MIGRATIONS: EmbeddedMigrations = embed_migrations!("migrations"); diff --git a/backend/core/src/models/budget_update.rs b/backend/core/src/models/budget_update.rs index c63e957..8600419 100644 --- a/backend/core/src/models/budget_update.rs +++ b/backend/core/src/models/budget_update.rs @@ -5,7 +5,7 @@ use crate::models::category::Category; use super::date_time_utc::DateTimeUtc; -#[derive(Clone, Serialize, Queryable, Identifiable, Selectable, Associations, Debug, PartialEq)] +#[derive(Clone, Serialize, Queryable, Identifiable, Selectable, Associations, Debug, PartialEq, Insertable)] #[diesel(table_name = crate::schema::budget_updates)] #[diesel(belongs_to(Category))] pub struct BudgetUpdate { diff --git a/backend/core/src/models/category.rs b/backend/core/src/models/category.rs index 5cb2609..595cff6 100644 --- a/backend/core/src/models/category.rs +++ b/backend/core/src/models/category.rs @@ -1,7 +1,7 @@ use diesel::prelude::*; use serde::{Deserialize, Serialize}; -#[derive(Queryable, Identifiable, Selectable, Debug, PartialEq, Serialize, Deserialize)] +#[derive(Queryable, Identifiable, Selectable, Debug, PartialEq, Serialize, Deserialize, Insertable)] #[diesel(table_name = crate::schema::categories)] pub struct Category { pub id: i32, diff --git a/backend/core/src/models/category_transfer.rs b/backend/core/src/models/category_transfer.rs index 6cbda0e..a048a07 100644 --- a/backend/core/src/models/category_transfer.rs +++ b/backend/core/src/models/category_transfer.rs @@ -1,6 +1,6 @@ use diesel::prelude::*; -#[derive(Queryable, Identifiable, Selectable, Debug, PartialEq)] +#[derive(Queryable, Identifiable, Selectable, Debug, PartialEq, Insertable)] #[diesel(table_name = crate::schema::category_transfers)] pub struct CategoryTransfer { pub id: i32, diff --git a/backend/core/src/models/date_time_utc.rs b/backend/core/src/models/date_time_utc.rs index 03d5802..52c0cc6 100644 --- a/backend/core/src/models/date_time_utc.rs +++ b/backend/core/src/models/date_time_utc.rs @@ -1,3 +1,4 @@ +use anyhow::Result; use chrono::{DateTime, Datelike, Utc}; use diesel::{ backend::Backend, @@ -44,6 +45,10 @@ impl DateTimeUtc { Self(date_time_utc) } + pub fn from_date_str(date_time_utc: &str) -> Result<Self> { + Ok(Self(date_time_utc.parse::<DateTime<Utc>>()?)) + } + pub fn now() -> Self { Self(Utc::now()) } diff --git a/backend/core/src/models/transaction.rs b/backend/core/src/models/transaction.rs index 36f33f8..5f85689 100644 --- a/backend/core/src/models/transaction.rs +++ b/backend/core/src/models/transaction.rs @@ -4,7 +4,7 @@ use crate::models::account::Account; use super::date_time_utc::DateTimeUtc; -#[derive(Queryable, Identifiable, Selectable, Associations, Debug, PartialEq)] +#[derive(Queryable, Identifiable, Selectable, Insertable, Associations, Debug, PartialEq)] #[diesel(table_name = crate::schema::transactions)] #[diesel(belongs_to(Account))] pub struct Transaction { diff --git a/backend/core/src/models/transaction_categorisation.rs b/backend/core/src/models/transaction_categorisation.rs index 60c2b98..45591a5 100644 --- a/backend/core/src/models/transaction_categorisation.rs +++ b/backend/core/src/models/transaction_categorisation.rs @@ -1,6 +1,6 @@ use diesel::prelude::*; -#[derive(Queryable, Identifiable, Selectable, Debug, PartialEq)] +#[derive(Queryable, Identifiable, Selectable, Insertable, Debug, PartialEq)] #[diesel(table_name = crate::schema::transaction_categorisations)] pub struct TransactionCategorisation { pub id: i32, diff --git a/backend/core/src/queries/accounts/delete_all.rs b/backend/core/src/queries/accounts/delete_all.rs new file mode 100644 index 0000000..84d7bf6 --- /dev/null +++ b/backend/core/src/queries/accounts/delete_all.rs @@ -0,0 +1,11 @@ +use anyhow::{Context, Result}; +use diesel::{RunQueryDsl, SqliteConnection}; + +use crate::schema::accounts::dsl::accounts; + +pub fn delete_all_accounts(connection: &mut SqliteConnection) -> Result<usize> { + let num_rows_deleted = diesel::delete(accounts) + .execute(connection) + .with_context(|| "failed to delete all accounts")?; + Ok(num_rows_deleted) +} diff --git a/backend/core/src/queries/accounts/get_all.rs b/backend/core/src/queries/accounts/get_all.rs new file mode 100644 index 0000000..ab53729 --- /dev/null +++ b/backend/core/src/queries/accounts/get_all.rs @@ -0,0 +1,10 @@ +use anyhow::{Context, Result}; +use crate::{models::account::Account, schema::accounts::dsl::accounts}; +use diesel::{QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection}; + +pub fn get_all_accounts(connection: &mut SqliteConnection) -> Result<Vec<Account>> { + let all_accounts = accounts.select(Account::as_select()) + .load(connection) + .with_context(|| "failed to get all accounts")?; + Ok(all_accounts) +} diff --git a/backend/core/src/queries/accounts/insert.rs b/backend/core/src/queries/accounts/insert.rs new file mode 100644 index 0000000..7020b68 --- /dev/null +++ b/backend/core/src/queries/accounts/insert.rs @@ -0,0 +1,28 @@ +use anyhow::{Context, Result}; +use crate::{ + models::account::Account, + schema::accounts::dsl::accounts as accounts_table, +}; +use diesel::prelude::*; + +pub fn insert_accounts( + accounts: &[Account], + connection: &mut SqliteConnection, +) -> Result<usize> { + let num_accounts_inserted = diesel::insert_into(accounts_table) + .values(accounts) + .execute(connection) + .with_context(|| err_msg(accounts))?; + Ok(num_accounts_inserted) +} + +fn err_msg(accounts: &[Account]) -> String { + format!( + "failed to insert accounts: [{}]", + accounts + .iter() + .map(|a| format!("\"{}\": \"{}\"", a.id, a.name)) + .collect::<Vec<String>>() + .join(", ") + ) +} diff --git a/backend/core/src/queries/accounts/mod.rs b/backend/core/src/queries/accounts/mod.rs new file mode 100644 index 0000000..222b180 --- /dev/null +++ b/backend/core/src/queries/accounts/mod.rs @@ -0,0 +1,7 @@ +mod delete_all; +mod get_all; +mod insert; + +pub use delete_all::delete_all_accounts; +pub use get_all::get_all_accounts; +pub use insert::insert_accounts; diff --git a/backend/core/src/queries/budget_updates/delete_all.rs b/backend/core/src/queries/budget_updates/delete_all.rs new file mode 100644 index 0000000..b7ad082 --- /dev/null +++ b/backend/core/src/queries/budget_updates/delete_all.rs @@ -0,0 +1,11 @@ +use anyhow::{Context, Result}; +use diesel::{RunQueryDsl, SqliteConnection}; + +use crate::schema::budget_updates::dsl::budget_updates; + +pub fn delete_all_budget_updates(connection: &mut SqliteConnection) -> Result<usize> { + let num_rows_deleted = diesel::delete(budget_updates) + .execute(connection) + .with_context(|| "failed to delete all budget updates")?; + Ok(num_rows_deleted) +} diff --git a/backend/core/src/queries/budget_updates/get_all.rs b/backend/core/src/queries/budget_updates/get_all.rs new file mode 100644 index 0000000..9c9310b --- /dev/null +++ b/backend/core/src/queries/budget_updates/get_all.rs @@ -0,0 +1,16 @@ +use anyhow::{Context, Result}; +use crate::{ + models::budget_update::BudgetUpdate, + schema::budget_updates::dsl::budget_updates as budget_updates_table, +}; +use diesel::{QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection}; + +pub fn get_all_budget_updates( + connection: &mut SqliteConnection, +) -> Result<Vec<BudgetUpdate>> { + let all_budget_updates = budget_updates_table + .select(BudgetUpdate::as_select()) + .load::<BudgetUpdate>(connection) + .with_context(|| "failed to get all budget updates")?; + Ok(all_budget_updates) +} diff --git a/backend/core/src/queries/budget_updates/get_first_with_date.rs b/backend/core/src/queries/budget_updates/get_first_with_date.rs new file mode 100644 index 0000000..1df3ae2 --- /dev/null +++ b/backend/core/src/queries/budget_updates/get_first_with_date.rs @@ -0,0 +1,22 @@ +use anyhow::{Context, Result}; +use crate::{ + models::budget_update::BudgetUpdate, + schema::budget_updates::{ + self as budget_updates_schema, dsl::budget_updates as budget_updates_table, + }, +}; +use diesel::{ + ExpressionMethods, QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection, +}; + +pub fn get_first_budget_update_with_date( + date: &str, + connection: &mut SqliteConnection, +) -> Result<BudgetUpdate> { + let first_budget_update = budget_updates_table + .select(BudgetUpdate::as_select()) + .filter(budget_updates_schema::date.eq(date)) + .first(connection) + .with_context(|| format!("failed to get first budget update with date {}", date))?; + Ok(first_budget_update) +} diff --git a/backend/core/src/queries/budget_updates/get_most_recent_with_category_id.rs b/backend/core/src/queries/budget_updates/get_most_recent_with_category_id.rs new file mode 100644 index 0000000..4e13c50 --- /dev/null +++ b/backend/core/src/queries/budget_updates/get_most_recent_with_category_id.rs @@ -0,0 +1,21 @@ +use anyhow::{Context, Result}; +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, ExpressionMethods, QueryDsl, RunQueryDsl, SqliteConnection}; + +pub fn get_most_recent_budget_update_date_with_category_id( + category_id: i32, + connection: &mut SqliteConnection, +) -> Result<Option<String>> { + let most_recent_date = 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) + .with_context(|| format!("failed to get most recent budget update date with category ID {}", category_id))?; + Ok(most_recent_date) +} diff --git a/backend/core/src/queries/budget_updates/insert.rs b/backend/core/src/queries/budget_updates/insert.rs new file mode 100644 index 0000000..e8ae271 --- /dev/null +++ b/backend/core/src/queries/budget_updates/insert.rs @@ -0,0 +1,28 @@ +use anyhow::{Context, Result}; +use crate::{ + models::budget_update::BudgetUpdate, + schema::budget_updates::dsl::budget_updates as budget_updates_table, +}; +use diesel::prelude::*; + +pub fn insert_budget_updates( + budget_updates: &[BudgetUpdate], + connection: &mut SqliteConnection, +) -> Result<usize> { + let num_rows_inserted = diesel::insert_into(budget_updates_table) + .values(budget_updates) + .execute(connection) + .with_context(|| err_msg(budget_updates))?; + Ok(num_rows_inserted) +} + +fn err_msg(budget_updates: &[BudgetUpdate]) -> String { + format!( + "failed to insert budget updates: [{}]", + budget_updates + .iter() + .map(|a| a.id.to_string()) + .collect::<Vec<String>>() + .join(", ") + ) +} diff --git a/backend/core/src/queries/budget_updates/mod.rs b/backend/core/src/queries/budget_updates/mod.rs new file mode 100644 index 0000000..337197e --- /dev/null +++ b/backend/core/src/queries/budget_updates/mod.rs @@ -0,0 +1,11 @@ +mod delete_all; +mod get_all; +mod get_first_with_date; +mod get_most_recent_with_category_id; +mod insert; + +pub use delete_all::delete_all_budget_updates; +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; +pub use insert::insert_budget_updates; diff --git a/backend/core/src/queries/categories/delete_all.rs b/backend/core/src/queries/categories/delete_all.rs new file mode 100644 index 0000000..1fd2581 --- /dev/null +++ b/backend/core/src/queries/categories/delete_all.rs @@ -0,0 +1,11 @@ +use anyhow::{Context, Result}; +use diesel::{RunQueryDsl, SqliteConnection}; + +use crate::schema::categories::dsl::categories; + +pub fn delete_all_categories(connection: &mut SqliteConnection) -> Result<usize> { + let num_rows_deleted = diesel::delete(categories) + .execute(connection) + .with_context(|| "failed to delete all categories")?; + Ok(num_rows_deleted) +} diff --git a/backend/core/src/queries/categories/get_all.rs b/backend/core/src/queries/categories/get_all.rs new file mode 100644 index 0000000..431ed9c --- /dev/null +++ b/backend/core/src/queries/categories/get_all.rs @@ -0,0 +1,11 @@ +use anyhow::{Context, Result}; +use crate::{models::category::Category, schema::categories::dsl::categories as categories_table}; +use diesel::{QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection}; + +pub fn get_all_categories(connection: &mut SqliteConnection) -> Result<Vec<Category>> { + let all_categories = categories_table + .select(Category::as_select()) + .load(connection) + .with_context(|| "failed to get all categories")?; + Ok(all_categories) +} diff --git a/backend/core/src/queries/categories/get_all_balances.rs b/backend/core/src/queries/categories/get_all_balances.rs new file mode 100644 index 0000000..da4b0cd --- /dev/null +++ b/backend/core/src/queries/categories/get_all_balances.rs @@ -0,0 +1,47 @@ +use anyhow::{Context, Result}; +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::SqliteConnection; + +pub fn get_all_category_balances( + connection: &mut SqliteConnection, +) -> Result<Vec<CategoryBalance>> { + get_all_category_balances_without_context(connection) + .with_context(|| "failed to get all category balances") +} + +fn get_all_category_balances_without_context( + connection: &mut SqliteConnection, +) -> Result<Vec<CategoryBalance>> { + 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::<CategoryBalance>::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/core/src/queries/categories/get_all_ids.rs b/backend/core/src/queries/categories/get_all_ids.rs new file mode 100644 index 0000000..8505e71 --- /dev/null +++ b/backend/core/src/queries/categories/get_all_ids.rs @@ -0,0 +1,11 @@ +use anyhow::{Context, Result}; +use crate::schema::categories::{self as categories_schema, dsl::categories as categories_table}; +use diesel::{QueryDsl, RunQueryDsl, SqliteConnection}; + +pub fn get_all_category_ids(connection: &mut SqliteConnection) -> Result<Vec<i32>> { + let all_category_ids = categories_table + .select(categories_schema::id) + .load(connection) + .with_context(|| "failed to get all category IDs")?; + Ok(all_category_ids) +} diff --git a/backend/core/src/queries/categories/get_most_recent_budget_per_category_id.rs b/backend/core/src/queries/categories/get_most_recent_budget_per_category_id.rs new file mode 100644 index 0000000..b8f1a76 --- /dev/null +++ b/backend/core/src/queries/categories/get_most_recent_budget_per_category_id.rs @@ -0,0 +1,47 @@ +use anyhow::{Context, Result}; +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::SqliteConnection; + +pub fn get_most_recent_category_budget_per_category_id( + connection: &mut SqliteConnection, +) -> Result<Vec<CategoryBudget>> { + get_most_recent_category_budget_per_category_id_without_context(connection) + .with_context(|| "failed to get most recent category budget per category ID") +} + +fn get_most_recent_category_budget_per_category_id_without_context( + connection: &mut SqliteConnection, +) -> Result<Vec<CategoryBudget>> { + let category_ids: Vec<i32> = get_all_category_ids(connection)?; + + let mut budgets = Vec::<CategoryBudget>::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/core/src/queries/categories/insert.rs b/backend/core/src/queries/categories/insert.rs new file mode 100644 index 0000000..bccba72 --- /dev/null +++ b/backend/core/src/queries/categories/insert.rs @@ -0,0 +1,28 @@ +use anyhow::{Context, Result}; +use crate::{ + models::category::Category, + schema::categories::dsl::categories as categories_table, +}; +use diesel::prelude::*; + +pub fn insert_categories( + categories: &[Category], + connection: &mut SqliteConnection, +) -> Result<usize> { + let num_categories_inserted = diesel::insert_into(categories_table) + .values(categories) + .execute(connection) + .with_context(|| err_msg(categories))?; + Ok(num_categories_inserted) +} + +fn err_msg(categories: &[Category]) -> String { + format!( + "failed to insert categories: [{}]", + categories + .iter() + .map(|a| format!("\"{}\": \"{}\"", a.id, a.name)) + .collect::<Vec<String>>() + .join(", ") + ) +} diff --git a/backend/core/src/queries/categories/mod.rs b/backend/core/src/queries/categories/mod.rs new file mode 100644 index 0000000..edeeebf --- /dev/null +++ b/backend/core/src/queries/categories/mod.rs @@ -0,0 +1,13 @@ +mod delete_all; +mod get_all; +mod get_all_balances; +mod get_all_ids; +mod get_most_recent_budget_per_category_id; +mod insert; + +pub use delete_all::delete_all_categories; +pub use get_all::get_all_categories; +pub use get_all_balances::get_all_category_balances; +pub use get_all_ids::get_all_category_ids; +pub use get_most_recent_budget_per_category_id::get_most_recent_category_budget_per_category_id; +pub use insert::insert_categories; diff --git a/backend/core/src/queries/category_transfers/delete_all.rs b/backend/core/src/queries/category_transfers/delete_all.rs new file mode 100644 index 0000000..406b80b --- /dev/null +++ b/backend/core/src/queries/category_transfers/delete_all.rs @@ -0,0 +1,7 @@ +use diesel::{result::Error, RunQueryDsl, SqliteConnection}; + +use crate::schema::category_transfers::dsl::category_transfers; + +pub fn delete_all_category_transfers(connection: &mut SqliteConnection) -> Result<usize, Error> { + diesel::delete(category_transfers).execute(connection) +} diff --git a/backend/core/src/queries/category_transfers/insert.rs b/backend/core/src/queries/category_transfers/insert.rs new file mode 100644 index 0000000..e1db167 --- /dev/null +++ b/backend/core/src/queries/category_transfers/insert.rs @@ -0,0 +1,15 @@ +use crate::{ + models::category_transfer::CategoryTransfer, + schema::category_transfers::dsl::category_transfers as category_transfers_table, +}; +use diesel::prelude::*; +use diesel::result::Error; + +pub fn insert_category_transfers( + category_transfers: &[CategoryTransfer], + connection: &mut SqliteConnection, +) -> Result<usize, Error> { + diesel::insert_into(category_transfers_table) + .values(category_transfers) + .execute(connection) +} diff --git a/backend/core/src/queries/category_transfers/mod.rs b/backend/core/src/queries/category_transfers/mod.rs new file mode 100644 index 0000000..68f32f4 --- /dev/null +++ b/backend/core/src/queries/category_transfers/mod.rs @@ -0,0 +1,9 @@ +mod delete_all; +mod insert; +mod sum_quantity_per_from_category_id; +mod sum_quantity_per_to_category_id; + +pub use delete_all::delete_all_category_transfers; +pub use insert::insert_category_transfers; +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/core/src/queries/category_transfers/sum_quantity_per_from_category_id.rs b/backend/core/src/queries/category_transfers/sum_quantity_per_from_category_id.rs new file mode 100644 index 0000000..cd4137d --- /dev/null +++ b/backend/core/src/queries/category_transfers/sum_quantity_per_from_category_id.rs @@ -0,0 +1,17 @@ +use crate::schema::category_transfers::{ + self as category_transfers_schema, + dsl::category_transfers as category_transfers_table, +}; +use diesel::{dsl::sum, result::Error, QueryDsl, RunQueryDsl, SqliteConnection}; + +pub fn sum_category_transfer_quantity_per_from_category_id( + connection: &mut SqliteConnection, +) -> Result<Vec<(i32, Option<i64>)>, 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<i64>)>(connection) +} diff --git a/backend/core/src/queries/category_transfers/sum_quantity_per_to_category_id.rs b/backend/core/src/queries/category_transfers/sum_quantity_per_to_category_id.rs new file mode 100644 index 0000000..f5fb561 --- /dev/null +++ b/backend/core/src/queries/category_transfers/sum_quantity_per_to_category_id.rs @@ -0,0 +1,17 @@ +use crate::schema::category_transfers::{ + self as category_transfers_schema, + dsl::category_transfers as category_transfers_table, +}; +use diesel::{dsl::sum, result::Error, QueryDsl, RunQueryDsl, SqliteConnection}; + +pub fn sum_category_transfer_quantity_per_to_category_id( + connection: &mut SqliteConnection, +) -> Result<Vec<(i32, Option<i64>)>, 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<i64>)>(connection) +} diff --git a/backend/core/src/queries/clear.rs b/backend/core/src/queries/clear.rs new file mode 100644 index 0000000..dbe0f84 --- /dev/null +++ b/backend/core/src/queries/clear.rs @@ -0,0 +1,19 @@ +use crate::queries::{ + accounts::delete_all_accounts, + budget_updates::delete_all_budget_updates, + categories::delete_all_categories, + category_transfers::delete_all_category_transfers, + transactions::delete_all_transactions, + transaction_categorisations::delete_all_transaction_categorisations, +}; +use diesel::{result::Error, SqliteConnection}; + +pub fn clear(connection: &mut SqliteConnection) -> Result<(), Error> { + delete_all_accounts(connection)?; + delete_all_budget_updates(connection)?; + delete_all_categories(connection)?; + delete_all_category_transfers(connection)?; + delete_all_transactions(connection)?; + delete_all_transaction_categorisations(connection)?; + Ok(()) +} diff --git a/backend/core/src/queries/mod.rs b/backend/core/src/queries/mod.rs new file mode 100644 index 0000000..f0852ce --- /dev/null +++ b/backend/core/src/queries/mod.rs @@ -0,0 +1,7 @@ +pub mod accounts; +pub mod budget_updates; +pub mod categories; +pub mod category_transfers; +pub mod clear; +pub mod transactions; +pub mod transaction_categorisations; diff --git a/backend/core/src/queries/transaction_categorisations/delete_all.rs b/backend/core/src/queries/transaction_categorisations/delete_all.rs new file mode 100644 index 0000000..e80a0ca --- /dev/null +++ b/backend/core/src/queries/transaction_categorisations/delete_all.rs @@ -0,0 +1,7 @@ +use diesel::{result::Error, RunQueryDsl, SqliteConnection}; + +use crate::schema::transaction_categorisations::dsl::transaction_categorisations; + +pub fn delete_all_transaction_categorisations(connection: &mut SqliteConnection) -> Result<usize, Error> { + diesel::delete(transaction_categorisations).execute(connection) +} diff --git a/backend/core/src/queries/transaction_categorisations/insert.rs b/backend/core/src/queries/transaction_categorisations/insert.rs new file mode 100644 index 0000000..3da1c33 --- /dev/null +++ b/backend/core/src/queries/transaction_categorisations/insert.rs @@ -0,0 +1,15 @@ +use crate::{ + models::transaction_categorisation::TransactionCategorisation, + schema::transaction_categorisations::dsl::transaction_categorisations as transaction_categorisations_table, +}; +use diesel::prelude::*; +use diesel::result::Error; + +pub fn insert_transaction_categorisations( + transaction_categorisations: &[TransactionCategorisation], + connection: &mut SqliteConnection, +) -> Result<usize, Error> { + diesel::insert_into(transaction_categorisations_table) + .values(transaction_categorisations) + .execute(connection) +} diff --git a/backend/core/src/queries/transaction_categorisations/mod.rs b/backend/core/src/queries/transaction_categorisations/mod.rs new file mode 100644 index 0000000..3664fa6 --- /dev/null +++ b/backend/core/src/queries/transaction_categorisations/mod.rs @@ -0,0 +1,7 @@ +mod delete_all; +mod insert; +mod sum_quantity_per_category_id; + +pub use delete_all::delete_all_transaction_categorisations; +pub use insert::insert_transaction_categorisations; +pub use sum_quantity_per_category_id::sum_transaction_categorisation_quantity_per_category_id; diff --git a/backend/core/src/queries/transaction_categorisations/sum_quantity_per_category_id.rs b/backend/core/src/queries/transaction_categorisations/sum_quantity_per_category_id.rs new file mode 100644 index 0000000..7f93e0c --- /dev/null +++ b/backend/core/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<Vec<(i32, Option<i64>)>, 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<i64>)>(connection) +} diff --git a/backend/core/src/queries/transactions/delete_all.rs b/backend/core/src/queries/transactions/delete_all.rs new file mode 100644 index 0000000..43b7976 --- /dev/null +++ b/backend/core/src/queries/transactions/delete_all.rs @@ -0,0 +1,7 @@ +use diesel::{result::Error, RunQueryDsl, SqliteConnection}; + +use crate::schema::transactions::dsl::transactions; + +pub fn delete_all_transactions(connection: &mut SqliteConnection) -> Result<usize, Error> { + diesel::delete(transactions).execute(connection) +} diff --git a/backend/core/src/queries/transactions/insert.rs b/backend/core/src/queries/transactions/insert.rs new file mode 100644 index 0000000..32f3dc2 --- /dev/null +++ b/backend/core/src/queries/transactions/insert.rs @@ -0,0 +1,15 @@ +use crate::{ + models::transaction::Transaction, + schema::transactions::dsl::transactions as transactions_table, +}; +use diesel::prelude::*; +use diesel::result::Error; + +pub fn insert_transactions( + transactions: &[Transaction], + connection: &mut SqliteConnection, +) -> Result<usize, Error> { + diesel::insert_into(transactions_table) + .values(transactions) + .execute(connection) +} diff --git a/backend/core/src/queries/transactions/mod.rs b/backend/core/src/queries/transactions/mod.rs new file mode 100644 index 0000000..40855f3 --- /dev/null +++ b/backend/core/src/queries/transactions/mod.rs @@ -0,0 +1,5 @@ +mod delete_all; +mod insert; + +pub use delete_all::delete_all_transactions; +pub use insert::insert_transactions; diff --git a/backend/core/src/utils/calculate_budgets_accrual.rs b/backend/core/src/utils/calculate_budgets_accrual.rs new file mode 100644 index 0000000..bbc197c --- /dev/null +++ b/backend/core/src/utils/calculate_budgets_accrual.rs @@ -0,0 +1,162 @@ +use crate::models::{ + budget_update::BudgetUpdate, + category::Category, + date_time_utc::DateTimeUtc +}; + +pub fn calculate_budgets_accrual(category: &Category, budget_updates: &[BudgetUpdate]) -> i64 { + let mut budget_updates = budget_updates + .iter() + .filter(|bu| bu.category_id == category.id) + .collect::<Vec<&BudgetUpdate>>(); + budget_updates.sort_by_key(|bu| bu.date); + budget_updates.reverse(); + + let mut budgets_accrual = 0.0_f64; + let mut calculated_back_to_date = DateTimeUtc::now(); + + for budget_update in budget_updates { + let days_on_this_budget = calculated_back_to_date.day_diff(&budget_update.date); + if days_on_this_budget == 0_u32 { + continue; + } + let budget_per_day = budget_update.new_budget as f64 / budget_update.new_period as f64; + budgets_accrual += days_on_this_budget as f64 * budget_per_day; + calculated_back_to_date = budget_update.date; + } + + budgets_accrual.floor() as i64 +} + +#[cfg(test)] +mod test { + use chrono::{Days, Timelike, Utc}; + + use crate::{ + models::{budget_update::BudgetUpdate, category::Category, date_time_utc::DateTimeUtc}, + utils::calculate_budgets_accrual, + }; + + #[test] + fn when_no_budget_updates_then_return_zero() { + let category = Category { + id: 0, + name: String::from("Groceries"), + }; + let budget_updates = []; + + let result = calculate_budgets_accrual(&category, &budget_updates); + + assert_eq!(result, 0_i64); + } + + #[test] + fn when_one_budget_update_for_another_category_then_return_zero() { + let category = Category { + id: 0, + name: String::from("Groceries"), + }; + let budget_updates = [BudgetUpdate { + id: 0, + category_id: 1, + date: DateTimeUtc::now(), + new_budget: 100, + new_period: 1, + }]; + + let result = calculate_budgets_accrual(&category, &budget_updates); + + assert_eq!(result, 0_i64); + } + + #[test] + fn when_one_budget_update_then_return_quantity_times_days_in_past() { + let category = Category { + id: 0, + name: String::from("Groceries"), + }; + let budget_updates = [BudgetUpdate { + id: 0, + category_id: 0, + date: DateTimeUtc::new(Utc::now().checked_sub_days(Days::new(120)).unwrap()), + new_budget: 100, + new_period: 1, + }]; + + let result = calculate_budgets_accrual(&category, &budget_updates); + + assert_eq!(result, 12_000); + } + + #[test] + fn when_two_budget_updates_then_calculates_as_expected() { + let category = Category { + id: 0, + name: String::from("Groceries"), + }; + let budget_updates = [ + BudgetUpdate { + id: 0, + category_id: 0, + date: DateTimeUtc::new(Utc::now().checked_sub_days(Days::new(60)).unwrap()), + new_budget: 200, + new_period: 1, + }, + BudgetUpdate { + id: 0, + category_id: 0, + date: DateTimeUtc::new(Utc::now().checked_sub_days(Days::new(120)).unwrap()), + new_budget: 100, + new_period: 1, + }, + ]; + + let result = calculate_budgets_accrual(&category, &budget_updates); + + assert_eq!(result, 18_000); + } + + #[test] + fn when_two_budget_updates_on_same_day_then_uses_latest_only() { + let category = Category { + id: 0, + name: String::from("Groceries"), + }; + let budget_updates = [ + BudgetUpdate { + id: 0, + category_id: 0, + date: DateTimeUtc::new( + Utc::now() + .checked_sub_days(Days::new(120)) + .unwrap() + .with_hour(13) + .unwrap(), + ), + new_budget: 200, + new_period: 1, + }, + BudgetUpdate { + id: 0, + category_id: 0, + date: DateTimeUtc::new( + Utc::now() + .checked_sub_days(Days::new(120)) + .unwrap() + .with_hour(12) + .unwrap(), + ), + new_budget: 100, + new_period: 1, + }, + ]; + let mut budget_updates_reversed = budget_updates.clone(); + budget_updates_reversed.reverse(); + + let result_leftways = calculate_budgets_accrual(&category, &budget_updates); + let result_rightways = calculate_budgets_accrual(&category, &budget_updates_reversed); + + assert_eq!(result_leftways, 24_000); + assert_eq!(result_rightways, 24_000); + } +} diff --git a/backend/core/src/utils/calculate_category_balance.rs b/backend/core/src/utils/calculate_category_balance.rs new file mode 100644 index 0000000..5eca05b --- /dev/null +++ b/backend/core/src/utils/calculate_category_balance.rs @@ -0,0 +1,142 @@ +use crate::{ + models::{budget_update::BudgetUpdate, category::Category}, + utils::{calculate_budgets_accrual, find_by_id_or}, +}; + +pub fn calculate_category_balance( + category: &Category, + transaction_sums: &[(i32, Option<i64>)], + category_transfer_from_sums: &[(i32, Option<i64>)], + category_transfer_to_sums: &[(i32, Option<i64>)], + budget_updates: &[BudgetUpdate], +) -> i64 { + let transaction_sum = find_by_id_or(transaction_sums, category.id, 0); + let category_transfer_from_sum = find_by_id_or(category_transfer_from_sums, category.id, 0); + let category_transfer_to_sum = find_by_id_or(category_transfer_to_sums, category.id, 0); + let budget_accruals_sum = calculate_budgets_accrual(category, budget_updates); + + transaction_sum + budget_accruals_sum + category_transfer_to_sum - category_transfer_from_sum +} + +#[cfg(test)] +mod test { + use chrono::{Days, Utc}; + + use crate::{ + models::{budget_update::BudgetUpdate, category::Category, date_time_utc::DateTimeUtc}, + utils::calculate_category_balance + }; + + #[test] + fn when_no_data_then_returns_zero() { + let category = Category { + id: 0, + name: String::new(), + }; + + let result = calculate_category_balance(&category, &[], &[], &[], &[]); + + assert_eq!(result, 0_i64); + } + + #[test] + fn when_data_only_for_another_category_then_returns_zero() { + let category = Category { + id: 0, + name: String::new(), + }; + let transaction_sums = [(1, Some(100))]; + let category_transfer_from_sums = [(1, Some(91))]; + let category_transfer_to_sums = [(2, Some(79))]; + let budget_updates = [BudgetUpdate { + id: 0, + category_id: 1, + date: DateTimeUtc::new(Utc::now().checked_sub_days(Days::new(120)).unwrap()), + new_budget: 77, + new_period: 1, + }]; + + let result = calculate_category_balance( + &category, + &transaction_sums, + &category_transfer_from_sums, + &category_transfer_to_sums, + &budget_updates, + ); + + assert_eq!(result, 0_i64); + } + + #[test] + fn when_transaction_sum_is_none_then_returns_zero() { + let category = Category { + id: 0, + name: String::new(), + }; + let transaction_sums = [(0, None)]; + let category_transfer_from_sums = []; + let category_transfer_to_sums = []; + let budget_updates = []; + + let result = calculate_category_balance( + &category, + &transaction_sums, + &category_transfer_from_sums, + &category_transfer_to_sums, + &budget_updates, + ); + + assert_eq!(result, 0_i64); + } + + #[test] + fn when_category_transfers_are_none_then_returns_zero() { + let category = Category { + id: 0, + name: String::new(), + }; + let transaction_sums = []; + let category_transfer_from_sums = [(0, None)]; + let category_transfer_to_sums = [(0, None)]; + let budget_updates = []; + + let result = calculate_category_balance( + &category, + &transaction_sums, + &category_transfer_from_sums, + &category_transfer_to_sums, + &budget_updates, + ); + + assert_eq!(result, 0_i64); + } + + #[test] + fn when_some_data_then_calculates_as_expected() { + let category = Category { + id: 0, + name: String::new(), + }; + let transaction_sums = [(0, Some(100))]; + let category_transfer_from_sums = [(0, Some(91))]; + let category_transfer_to_sums = [(0, Some(79))]; + let budget_updates = [BudgetUpdate { + id: 0, + category_id: 0, + date: DateTimeUtc::new(Utc::now().checked_sub_days(Days::new(120)).unwrap()), + new_budget: 77, + new_period: 1, + }]; + + let result = calculate_category_balance( + &category, + &transaction_sums, + &category_transfer_from_sums, + &category_transfer_to_sums, + &budget_updates, + ); + + let expected_result = 100 + (-91) + 79 + 77 * 120; + assert_eq!(result, expected_result); + } +} diff --git a/backend/core/src/utils/find_by_id_or.rs b/backend/core/src/utils/find_by_id_or.rs new file mode 100644 index 0000000..f5add30 --- /dev/null +++ b/backend/core/src/utils/find_by_id_or.rs @@ -0,0 +1,53 @@ +pub fn find_by_id_or<Id: PartialEq, Val: Copy>( + arr: &[(Id, Option<Val>)], + id: Id, + default: Val, +) -> Val { + arr.iter() + .find(|(elem_id, _)| *elem_id == id) + .map_or(default, |row| match row { + (_, Some(quantity)) => *quantity, + (_, _) => default, + }) +} + +#[cfg(test)] +mod test { + use super::find_by_id_or; + + #[test] + fn when_no_elements_then_return_default() { + let arr = []; + + let result = find_by_id_or(&arr, 1, 42); + + assert_eq!(result, 42); + } + + #[test] + fn when_id_not_in_elements_then_return_default() { + let arr = [(0, Some(100)), (2, Some(200))]; + + let result = find_by_id_or(&arr, 1, 42); + + assert_eq!(result, 42); + } + + #[test] + fn when_value_is_none_then_return_default() { + let arr = [(1, None)]; + + let result = find_by_id_or(&arr, 1, 42); + + assert_eq!(result, 42); + } + + #[test] + fn when_value_is_some_then_return_value() { + let arr = [(1, Some(67))]; + + let result = find_by_id_or(&arr, 1, 42); + + assert_eq!(result, 67); + } +} diff --git a/backend/core/src/utils/mod.rs b/backend/core/src/utils/mod.rs new file mode 100644 index 0000000..84a27af --- /dev/null +++ b/backend/core/src/utils/mod.rs @@ -0,0 +1,7 @@ +mod calculate_budgets_accrual; +mod calculate_category_balance; +mod find_by_id_or; + +pub use calculate_budgets_accrual::calculate_budgets_accrual; +pub use calculate_category_balance::calculate_category_balance; +pub use find_by_id_or::find_by_id_or; |