diff options
Diffstat (limited to 'backend/src')
42 files changed, 892 insertions, 28 deletions
diff --git a/backend/src/databases.rs b/backend/src/databases.rs index 461fc44..7202e78 100644 --- a/backend/src/databases.rs +++ b/backend/src/databases.rs @@ -1,4 +1,11 @@ use rocket_sync_db_pools::{database, diesel}; -#[database("user_data")] -pub struct UserDataDb(diesel::SqliteConnection); +cfg_if::cfg_if! { + if #[cfg(test)] { + #[database("user_data.test")] + pub struct UserDataDb(diesel::SqliteConnection); + } else { + #[database("user_data")] + pub struct UserDataDb(diesel::SqliteConnection); + } +} diff --git a/backend/src/main.rs b/backend/src/main.rs index db47ceb..cdf4699 100644 --- a/backend/src/main.rs +++ b/backend/src/main.rs @@ -2,14 +2,17 @@ use diesel::SqliteConnection; use diesel_migrations::MigrationHarness; use diesel_migrations::{embed_migrations, EmbeddedMigrations}; use rocket::fairing::AdHoc; +use rocket::http::ContentType; use rocket::{launch, routes, Build, Rocket}; use crate::databases::UserDataDb; pub mod databases; pub mod models; +pub mod queries; pub mod routes; pub mod schema; +pub mod utils; pub const MIGRATIONS: EmbeddedMigrations = embed_migrations!("migrations"); @@ -31,11 +34,23 @@ async fn rocket() -> Rocket<Build> { user_data_db.run(run_database_migrations).await; }) })) + .attach(AdHoc::on_response("Add CORS headers", |_req, res| { + Box::pin(async move { + res.set_header(ContentType::JSON); + res.set_header(rocket::http::Header::new( + "Access-Control-Allow-Origin", + "http://localhost:5173", + )); + }) + })) .mount( "/", routes![ routes::account::get_all_accounts, - routes::account::create_account + routes::account::create_account, + routes::balance::get_all_category_balances, + routes::budget::get_all_category_budgets, + routes::category::get_all_categories, ], ) } diff --git a/backend/src/models/account.rs b/backend/src/models/account.rs index 1a2aa41..c10bd5f 100644 --- a/backend/src/models/account.rs +++ b/backend/src/models/account.rs @@ -1,6 +1,8 @@ use diesel::prelude::*; use serde::{Deserialize, Serialize}; +use super::date_time_utc::DateTimeUtc; + #[derive( Insertable, Queryable, Identifiable, Selectable, Debug, PartialEq, Clone, Serialize, Deserialize, )] @@ -9,5 +11,5 @@ pub struct Account { pub id: i32, pub name: String, pub opening_balance: i32, - pub opening_date: String, + pub opening_date: DateTimeUtc, } diff --git a/backend/src/models/budget.rs b/backend/src/models/budget.rs new file mode 100644 index 0000000..cc18f2e --- /dev/null +++ b/backend/src/models/budget.rs @@ -0,0 +1,44 @@ +use serde::Serialize; + +use super::budget_update::BudgetUpdate; + +#[derive(Debug, Eq, PartialEq, Serialize)] +pub struct Budget { + pub quantity: i32, + pub period: i32, +} + +impl From<&BudgetUpdate> for Budget { + fn from(budget_update: &BudgetUpdate) -> Self { + Self { + quantity: budget_update.new_budget, + period: budget_update.new_period, + } + } +} + +#[cfg(test)] +mod test { + use chrono::Utc; + + use crate::models::date_time_utc::DateTimeUtc; + + use super::*; + + #[test] + fn when_from_budget_update_then_returns_budget() { + let quantity = 2; + let period = 3; + let budget_update = BudgetUpdate { + id: 0, + category_id: 1, + date: DateTimeUtc::new(Utc::now()), + new_budget: quantity, + new_period: period, + }; + + let budget = Budget::from(&budget_update); + + assert_eq!(budget, Budget { quantity, period }); + } +} diff --git a/backend/src/models/budget_update.rs b/backend/src/models/budget_update.rs index 3843455..ea93c16 100644 --- a/backend/src/models/budget_update.rs +++ b/backend/src/models/budget_update.rs @@ -2,13 +2,15 @@ use diesel::prelude::*; use crate::models::category::Category; -#[derive(Queryable, Identifiable, Selectable, Associations, Debug, PartialEq)] +use super::date_time_utc::DateTimeUtc; + +#[derive(Clone, Queryable, Identifiable, Selectable, Associations, Debug, PartialEq)] #[diesel(table_name = crate::schema::budget_updates)] #[diesel(belongs_to(Category))] pub struct BudgetUpdate { pub id: i32, pub category_id: i32, - pub date: String, + pub date: DateTimeUtc, pub new_budget: i32, pub new_period: i32, } diff --git a/backend/src/models/category.rs b/backend/src/models/category.rs index d7e25aa..5cb2609 100644 --- a/backend/src/models/category.rs +++ b/backend/src/models/category.rs @@ -1,10 +1,9 @@ use diesel::prelude::*; +use serde::{Deserialize, Serialize}; -#[derive(Queryable, Identifiable, Selectable, Debug, PartialEq)] +#[derive(Queryable, Identifiable, Selectable, Debug, PartialEq, Serialize, Deserialize)] #[diesel(table_name = crate::schema::categories)] pub struct Category { pub id: i32, pub name: String, - pub initial_budget: i32, - pub initial_period: i32, } diff --git a/backend/src/models/category_balance.rs b/backend/src/models/category_balance.rs new file mode 100644 index 0000000..796b40b --- /dev/null +++ b/backend/src/models/category_balance.rs @@ -0,0 +1,7 @@ +use serde::Serialize; + +#[derive(Serialize)] +pub struct CategoryBalance { + pub category_id: i32, + pub balance: i32, +} diff --git a/backend/src/models/category_budget.rs b/backend/src/models/category_budget.rs new file mode 100644 index 0000000..9796261 --- /dev/null +++ b/backend/src/models/category_budget.rs @@ -0,0 +1,9 @@ +use serde::Serialize; + +use super::budget::Budget; + +#[derive(Serialize)] +pub struct CategoryBudget { + pub category_id: i32, + pub budget: Option<Budget>, +} diff --git a/backend/src/models/date_time_utc.rs b/backend/src/models/date_time_utc.rs new file mode 100644 index 0000000..6c5dc33 --- /dev/null +++ b/backend/src/models/date_time_utc.rs @@ -0,0 +1,111 @@ +use chrono::{DateTime, Datelike, Utc}; +use diesel::{ + backend::Backend, + deserialize::{self, FromSql, FromSqlRow}, + expression::AsExpression, + serialize::{self, ToSql}, + sql_types::Text, + sqlite::Sqlite, +}; +use serde::{Deserialize, Serialize}; + +#[derive( + Eq, + PartialEq, + Ord, + PartialOrd, + Serialize, + Deserialize, + Copy, + Clone, + Debug, + AsExpression, + FromSqlRow, +)] +#[diesel(sql_type=Text)] +pub struct DateTimeUtc(DateTime<Utc>); + +impl DateTimeUtc { + pub fn day_diff(&self, other: &DateTimeUtc) -> u32 { + self.0 + .num_days_from_ce() + .abs_diff(other.0.num_days_from_ce()) + } + + pub fn new(date_time_utc: DateTime<Utc>) -> Self { + Self(date_time_utc) + } + + pub fn now() -> Self { + Self(Utc::now()) + } +} + +impl ToSql<Text, Sqlite> for DateTimeUtc { + fn to_sql<'b>( + &'b self, + out: &mut diesel::serialize::Output<'b, '_, Sqlite>, + ) -> serialize::Result { + out.set_value(self.0.to_rfc3339()); + Ok(diesel::serialize::IsNull::No) + } +} + +impl FromSql<Text, Sqlite> for DateTimeUtc { + fn from_sql(bytes: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<DateTimeUtc> { + let str: String = FromSql::<Text, Sqlite>::from_sql(bytes)?; + let date_time = str.as_str().parse::<DateTime<Utc>>()?; + Ok(DateTimeUtc(date_time)) + } +} + +#[cfg(test)] +mod test { + use chrono::TimeZone; + + use super::*; + + #[test] + fn given_midnight_when_day_diff_next_midnight_less_1s_then_return_0() { + let start = DateTimeUtc::new(Utc.with_ymd_and_hms(2000, 1, 1, 0, 0, 0).unwrap().to_utc()); + let end = DateTimeUtc::new( + Utc.with_ymd_and_hms(2000, 1, 1, 23, 59, 59) + .unwrap() + .to_utc(), + ); + + let day_diff = start.day_diff(&end); + let neg_day_diff = end.day_diff(&start); + + assert_eq!(day_diff, 0); + assert_eq!(day_diff, neg_day_diff); + } + + #[test] + fn given_midnight_when_day_diff_next_midnight_then_return_1() { + let start = DateTimeUtc::new(Utc.with_ymd_and_hms(2000, 1, 1, 0, 0, 0).unwrap().to_utc()); + let end = DateTimeUtc::new(Utc.with_ymd_and_hms(2000, 1, 2, 0, 0, 0).unwrap().to_utc()); + + let day_diff = start.day_diff(&end); + let neg_day_diff = end.day_diff(&start); + + assert_eq!(day_diff, 1); + assert_eq!(day_diff, neg_day_diff); + } + + #[test] + fn given_midnight_less_1s_when_day_diff_midnight_then_return_1() { + let start = DateTimeUtc::new( + Utc.with_ymd_and_hms(2000, 1, 1, 23, 59, 59) + .unwrap() + .to_utc(), + ); + let end = DateTimeUtc::new(Utc.with_ymd_and_hms(2000, 1, 2, 0, 0, 0).unwrap().to_utc()); + + let day_diff = start.day_diff(&end); + let neg_day_diff = end.day_diff(&start); + + assert_eq!(day_diff, 1); + assert_eq!(day_diff, neg_day_diff); + } +} diff --git a/backend/src/models/mod.rs b/backend/src/models/mod.rs index 68f5209..b790fcb 100644 --- a/backend/src/models/mod.rs +++ b/backend/src/models/mod.rs @@ -1,6 +1,10 @@ pub mod account; +pub mod budget; pub mod budget_update; pub mod category; +pub mod category_balance; +pub mod category_budget; pub mod category_transfer; +pub mod date_time_utc; pub mod transaction; pub mod transaction_categorisation; diff --git a/backend/src/models/transaction.rs b/backend/src/models/transaction.rs index a83a0ad..36f33f8 100644 --- a/backend/src/models/transaction.rs +++ b/backend/src/models/transaction.rs @@ -2,6 +2,8 @@ use diesel::prelude::*; use crate::models::account::Account; +use super::date_time_utc::DateTimeUtc; + #[derive(Queryable, Identifiable, Selectable, Associations, Debug, PartialEq)] #[diesel(table_name = crate::schema::transactions)] #[diesel(belongs_to(Account))] @@ -10,5 +12,6 @@ pub struct Transaction { pub description: String, pub payee: String, pub quantity: i32, + pub date: DateTimeUtc, pub account_id: i32, } diff --git a/backend/src/queries/accounts/get_all.rs b/backend/src/queries/accounts/get_all.rs new file mode 100644 index 0000000..12941ac --- /dev/null +++ b/backend/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<Vec<Account>, Error> { + accounts.select(Account::as_select()).load(connection) +} diff --git a/backend/src/queries/accounts/insert.rs b/backend/src/queries/accounts/insert.rs new file mode 100644 index 0000000..c20cf60 --- /dev/null +++ b/backend/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<usize, Error> { + diesel::insert_into(accounts_table) + .values(accounts) + .execute(connection) +} diff --git a/backend/src/queries/accounts/mod.rs b/backend/src/queries/accounts/mod.rs new file mode 100644 index 0000000..94fa360 --- /dev/null +++ b/backend/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/src/queries/budget_updates/get_all.rs b/backend/src/queries/budget_updates/get_all.rs new file mode 100644 index 0000000..97f2f2f --- /dev/null +++ b/backend/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<Vec<BudgetUpdate>, Error> { + budget_updates_table + .select(BudgetUpdate::as_select()) + .load::<BudgetUpdate>(connection) +} diff --git a/backend/src/queries/budget_updates/get_first_with_date.rs b/backend/src/queries/budget_updates/get_first_with_date.rs new file mode 100644 index 0000000..a2824be --- /dev/null +++ b/backend/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<BudgetUpdate, Error> { + budget_updates_table + .select(BudgetUpdate::as_select()) + .filter(budget_updates_schema::date.eq(date)) + .first(connection) +} diff --git a/backend/src/queries/budget_updates/get_most_recent_with_category_id.rs b/backend/src/queries/budget_updates/get_most_recent_with_category_id.rs new file mode 100644 index 0000000..a52e592 --- /dev/null +++ b/backend/src/queries/budget_updates/get_most_recent_with_category_id.rs @@ -0,0 +1,14 @@ +use crate::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<Option<String>, Error> { + budget_updates_table + .select(max(budget_updates_schema::date)) + .filter(budget_updates_schema::category_id.eq(category_id)) + .first(connection) +} diff --git a/backend/src/queries/budget_updates/mod.rs b/backend/src/queries/budget_updates/mod.rs new file mode 100644 index 0000000..3b514dd --- /dev/null +++ b/backend/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/src/queries/categories/get_all.rs b/backend/src/queries/categories/get_all.rs new file mode 100644 index 0000000..db5a073 --- /dev/null +++ b/backend/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<Vec<Category>, Error> { + categories_table + .select(Category::as_select()) + .load(connection) +} diff --git a/backend/src/queries/categories/get_all_ids.rs b/backend/src/queries/categories/get_all_ids.rs new file mode 100644 index 0000000..7c7c912 --- /dev/null +++ b/backend/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<Vec<i32>, Error> { + categories_table + .select(categories_schema::id) + .load(connection) +} diff --git a/backend/src/queries/categories/mod.rs b/backend/src/queries/categories/mod.rs new file mode 100644 index 0000000..80356b3 --- /dev/null +++ b/backend/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/src/queries/category_balances/get_all.rs b/backend/src/queries/category_balances/get_all.rs new file mode 100644 index 0000000..713fbeb --- /dev/null +++ b/backend/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<Vec<CategoryBalance>, 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::<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/src/queries/category_balances/mod.rs b/backend/src/queries/category_balances/mod.rs new file mode 100644 index 0000000..3fc1be2 --- /dev/null +++ b/backend/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/src/queries/category_budgets/get_all.rs b/backend/src/queries/category_budgets/get_all.rs new file mode 100644 index 0000000..6a8b040 --- /dev/null +++ b/backend/src/queries/category_budgets/get_all.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_all_category_budgets( + connection: &mut SqliteConnection, +) -> Result<Vec<CategoryBudget>, Error> { + 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/src/queries/category_budgets/mod.rs b/backend/src/queries/category_budgets/mod.rs new file mode 100644 index 0000000..1a46cc3 --- /dev/null +++ b/backend/src/queries/category_budgets/mod.rs @@ -0,0 +1,3 @@ +mod get_all; + +pub use self::get_all::get_all_category_budgets; diff --git a/backend/src/queries/category_transfers/mod.rs b/backend/src/queries/category_transfers/mod.rs new file mode 100644 index 0000000..85eb1c8 --- /dev/null +++ b/backend/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/src/queries/category_transfers/sum_quantity_per_from_category_id.rs b/backend/src/queries/category_transfers/sum_quantity_per_from_category_id.rs new file mode 100644 index 0000000..ae3a51a --- /dev/null +++ b/backend/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<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/src/queries/category_transfers/sum_quantity_per_to_category_id.rs b/backend/src/queries/category_transfers/sum_quantity_per_to_category_id.rs new file mode 100644 index 0000000..fe6f3a7 --- /dev/null +++ b/backend/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<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/src/queries/mod.rs b/backend/src/queries/mod.rs new file mode 100644 index 0000000..43328e2 --- /dev/null +++ b/backend/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/src/queries/transaction_categorisations/mod.rs b/backend/src/queries/transaction_categorisations/mod.rs new file mode 100644 index 0000000..d4217cc --- /dev/null +++ b/backend/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/src/queries/transaction_categorisations/sum_quantity_per_category_id.rs b/backend/src/queries/transaction_categorisations/sum_quantity_per_category_id.rs new file mode 100644 index 0000000..7f93e0c --- /dev/null +++ b/backend/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/src/queries/transaction_categorisations/sum_quantity_per_to_category_id.rs b/backend/src/queries/transaction_categorisations/sum_quantity_per_to_category_id.rs new file mode 100644 index 0000000..e69de29 --- /dev/null +++ b/backend/src/queries/transaction_categorisations/sum_quantity_per_to_category_id.rs diff --git a/backend/src/routes/account.rs b/backend/src/routes/account.rs index 418ec96..7bbdb4f 100644 --- a/backend/src/routes/account.rs +++ b/backend/src/routes/account.rs @@ -1,24 +1,19 @@ -use diesel::prelude::*; use diesel::result::DatabaseErrorKind; +use rocket::http::Status; use rocket::serde::json::Json; use rocket::{get, post, Responder}; use crate::databases::UserDataDb; use crate::models::account::Account; -use crate::schema::accounts::dsl::accounts; +use crate::queries::accounts::{get_all_accounts as get_all_accounts_query, insert_account}; #[get("/account")] -pub async fn get_all_accounts(user_data_db: UserDataDb) -> Json<Vec<Account>> { +pub async fn get_all_accounts(user_data_db: UserDataDb) -> Result<Json<Vec<Account>>, Status> { user_data_db - .run(|connection| { - Json( - accounts - .select(Account::as_select()) - .load(connection) - .expect("Error getting accounts"), - ) - }) + .run(get_all_accounts_query) .await + .map(Json) + .map_err(|_| Status { code: 500 }) } #[derive(Responder)] @@ -39,14 +34,10 @@ pub async fn create_account( user_data_db: UserDataDb, ) -> CreateAccountResponder { let account = account.into_inner(); - let account_clone = account.clone(); + let accounts_vec = vec![account.clone()]; let num_rows_inserted = user_data_db - .run(|connection| { - diesel::insert_into(accounts) - .values(vec![account_clone]) - .execute(connection) - }) + .run(move |connection| insert_account(&accounts_vec, connection)) .await; match num_rows_inserted { diff --git a/backend/src/routes/balance.rs b/backend/src/routes/balance.rs new file mode 100644 index 0000000..f423c90 --- /dev/null +++ b/backend/src/routes/balance.rs @@ -0,0 +1,17 @@ +use rocket::{get, http::Status, serde::json::Json}; + +use crate::{ + databases::UserDataDb, models::category_balance::CategoryBalance, + queries::category_balances::get_all_category_balances as get_all_category_balances_query, +}; + +#[get("/balance/category")] +pub async fn get_all_category_balances( + user_data_db: UserDataDb, +) -> Result<Json<Vec<CategoryBalance>>, Status> { + user_data_db + .run(get_all_category_balances_query) + .await + .map(Json) + .map_err(|_| Status { code: 500 }) +} diff --git a/backend/src/routes/budget.rs b/backend/src/routes/budget.rs new file mode 100644 index 0000000..024706e --- /dev/null +++ b/backend/src/routes/budget.rs @@ -0,0 +1,18 @@ +use rocket::get; +use rocket::http::Status; +use rocket::serde::json::Json; + +use crate::databases::UserDataDb; +use crate::models::category_budget::CategoryBudget; +use crate::queries; + +#[get("/budget/category")] +pub async fn get_all_category_budgets( + user_data_db: UserDataDb, +) -> Result<Json<Vec<CategoryBudget>>, Status> { + user_data_db + .run(queries::category_budgets::get_all_category_budgets) + .await + .map(Json) + .map_err(|_| Status { code: 500 }) +} diff --git a/backend/src/routes/category.rs b/backend/src/routes/category.rs new file mode 100644 index 0000000..01fd8d1 --- /dev/null +++ b/backend/src/routes/category.rs @@ -0,0 +1,16 @@ +use rocket::get; +use rocket::http::Status; +use rocket::serde::json::Json; + +use crate::databases::UserDataDb; +use crate::models::category::Category; +use crate::queries::categories::get_all_categories as get_all_categories_query; + +#[get("/category")] +pub async fn get_all_categories(user_data_db: UserDataDb) -> Result<Json<Vec<Category>>, Status> { + user_data_db + .run(get_all_categories_query) + .await + .map(Json) + .map_err(|_| Status { code: 500 }) +} diff --git a/backend/src/routes/mod.rs b/backend/src/routes/mod.rs index b0edc6c..1d4408a 100644 --- a/backend/src/routes/mod.rs +++ b/backend/src/routes/mod.rs @@ -1 +1,4 @@ pub mod account; +pub mod balance; +pub mod budget; +pub mod category; diff --git a/backend/src/schema.rs b/backend/src/schema.rs index 942e913..372f8f3 100644 --- a/backend/src/schema.rs +++ b/backend/src/schema.rs @@ -23,8 +23,6 @@ diesel::table! { categories (id) { id -> Integer, name -> Text, - initial_budget -> Integer, - initial_period -> Integer, } } @@ -54,6 +52,7 @@ diesel::table! { description -> Text, payee -> Text, quantity -> Integer, + date -> Text, account_id -> Integer, } } diff --git a/backend/src/utils/calculate_budgets_accrual.rs b/backend/src/utils/calculate_budgets_accrual.rs new file mode 100644 index 0000000..f14260d --- /dev/null +++ b/backend/src/utils/calculate_budgets_accrual.rs @@ -0,0 +1,159 @@ +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, + }; + + use super::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/src/utils/calculate_category_balance.rs b/backend/src/utils/calculate_category_balance.rs new file mode 100644 index 0000000..3c1c62f --- /dev/null +++ b/backend/src/utils/calculate_category_balance.rs @@ -0,0 +1,141 @@ +use crate::models::{budget_update::BudgetUpdate, category::Category}; + +use super::{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/src/utils/find_by_id_or.rs b/backend/src/utils/find_by_id_or.rs new file mode 100644 index 0000000..f5add30 --- /dev/null +++ b/backend/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/src/utils/mod.rs b/backend/src/utils/mod.rs new file mode 100644 index 0000000..84a27af --- /dev/null +++ b/backend/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; |