From 11052951b74b9ad4b6a9412ae490086235f9154b Mon Sep 17 00:00:00 2001 From: Joris Date: Sun, 3 Jan 2021 13:40:40 +0100 Subject: Rewrite in Rust --- src/db/payments.rs | 525 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 525 insertions(+) create mode 100644 src/db/payments.rs (limited to 'src/db/payments.rs') diff --git a/src/db/payments.rs b/src/db/payments.rs new file mode 100644 index 0000000..0197375 --- /dev/null +++ b/src/db/payments.rs @@ -0,0 +1,525 @@ +use sqlx::error::Error; +use sqlx::sqlite::{Sqlite, SqliteArguments}; +use sqlx::sqlite::{SqlitePool, SqliteRow}; +use sqlx::FromRow; +use sqlx_core::row::Row; +use std::collections::HashMap; +use std::iter::FromIterator; + +use crate::db::utils; +use crate::model::frequency::Frequency; +use crate::model::payment; +use crate::model::report::Report; +use crate::queries; +use crate::utils::text; + +#[derive(FromRow)] +pub struct Count { + pub count: i64, + pub total_cost: i64, +} + +pub async fn count( + pool: &SqlitePool, + payment_query: &queries::Payments, +) -> Count { + let search = payment_query.search.clone().unwrap_or("".to_string()); + + let query = format!( + r#" +SELECT + COUNT(*) AS count, + SUM(payments.cost) AS total_cost +FROM + payments +INNER JOIN + users ON users.id = payments.user_id +INNER JOIN + categories ON categories.id = payments.category_id +WHERE + payments.deleted_at IS NULL + AND payments.frequency = ? + {} + "#, + search_query(search.clone()) + ); + + let res = bind_search( + sqlx::query_as::<_, Count>(&query) + .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)), + search, + ) + .fetch_one(pool) + .await; + + match res { + Ok(count) => count, + Err(err) => { + error!("Error counting payments: {:?}", err); + Count { + count: 0, + total_cost: 0, + } + } + } +} + +pub async fn list_for_table( + pool: &SqlitePool, + payment_query: &queries::Payments, + per_page: i64, +) -> Vec { + let offset = (payment_query.page.unwrap_or(1) - 1) * per_page; + let search = payment_query.search.clone().unwrap_or("".to_string()); + + let query = format!( + r#" +SELECT + payments.id, + payments.name, + payments.cost, + users.name AS user, + categories.name AS category_name, + categories.color AS category_color, + strftime('%d/%m/%Y', date) AS date, + payments.frequency AS frequency +FROM + payments +INNER JOIN + users ON users.id = payments.user_id +INNER JOIN + categories ON categories.id = payments.category_id +WHERE + payments.deleted_at IS NULL + AND payments.frequency = ? + {} +ORDER BY + payments.date DESC +LIMIT ? +OFFSET ? + "#, + search_query(search.clone()) + ); + + let res = bind_search( + sqlx::query_as::<_, payment::Table>(&query) + .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)), + search, + ) + .bind(per_page) + .bind(offset) + .fetch_all(pool) + .await; + + match res { + Ok(payments) => payments, + Err(err) => { + error!("Error listing payments: {:?}", err); + vec![] + } + } +} + +fn search_query(search: String) -> String { + let payments_name = utils::format_key_for_search("payments.name"); + let users_name = utils::format_key_for_search("users.name"); + let categories_name = utils::format_key_for_search("categories.name"); + + search + .split_ascii_whitespace() + .map(|_| { + format!( + r#" +AND ( + {} LIKE ? + OR payments.cost LIKE ? + OR {} LIKE ? + OR {} LIKE ? + OR strftime('%d/%m/%Y', date) LIKE ? +) + "#, + payments_name, users_name, categories_name + ) + }) + .collect::>() + .join(" ") +} + +fn bind_search<'a, Row: FromRow<'a, SqliteRow>>( + query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>, + search: String, +) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> { + search.split_ascii_whitespace().fold(query, |q, word| { + let s = format!("%{}%", text::format_search(&word.to_string())); + q.bind(s.clone()) + .bind(s.clone()) + .bind(s.clone()) + .bind(s.clone()) + .bind(s.clone()) + }) +} + +pub async fn list_for_stats(pool: &SqlitePool) -> Vec { + let query = r#" +SELECT + strftime('%Y-%m-01', payments.date) AS start_date, + SUM(payments.cost) AS cost, + payments.category_id AS category_id +FROM + payments +WHERE + payments.deleted_at IS NULL + AND payments.frequency = 'Punctual' +GROUP BY + start_date, + payments.category_id; + "#; + + let result = sqlx::query_as::<_, payment::Stat>(query) + .fetch_all(pool) + .await; + + match result { + Ok(payments) => payments, + Err(err) => { + error!("Error listing payments for statistics: {:?}", err); + vec![] + } + } +} + +pub async fn get_row(pool: &SqlitePool, id: i64, frequency: Frequency) -> i64 { + let query = r#" +SELECT + row +FROM ( + SELECT + ROW_NUMBER () OVER (ORDER BY date DESC) AS row, + id + FROM + payments + WHERE + deleted_at IS NULL + AND frequency = ? +) +WHERE + id = ? + "#; + + let res = sqlx::query(query) + .bind(frequency) + .bind(id) + .map(|row: SqliteRow| row.get("row")) + .fetch_one(pool) + .await; + + match res { + Ok(count) => count, + Err(err) => { + error!("Error getting payment row: {:?}", err); + 1 + } + } +} + +pub async fn get_for_form(pool: &SqlitePool, id: i64) -> Option { + let query = r#" +SELECT + id, + name, + cost, + user_id, + category_id, + strftime('%Y-%m-%d', date) AS date, + frequency AS frequency +FROM + payments +WHERE + id = ? + AND deleted_at IS NULL + "#; + + let res = sqlx::query_as::<_, payment::Form>(query) + .bind(id) + .fetch_one(pool) + .await; + + match res { + Ok(p) => Some(p), + Err(err) => { + error!("Error looking for payment {}: {:?}", id, err); + None + } + } +} + +pub async fn create(pool: &SqlitePool, p: &payment::Create) -> Option { + let res = sqlx::query( + r#" +INSERT INTO + payments(name, cost, user_id, category_id, date, frequency, created_at) +VALUES + (?, ?, ?, ?, ?, ?, datetime()) + "#, + ) + .bind(p.name.clone()) + .bind(p.cost) + .bind(p.user_id) + .bind(p.category_id) + .bind(p.date) + .bind(p.frequency) + .execute(pool) + .await; + + match res { + Ok(x) => Some(x.last_insert_rowid()), + Err(err) => { + error!("Error creating payment: {:?}", err); + None + } + } +} + +pub async fn update(pool: &SqlitePool, id: i64, p: &payment::Update) -> bool { + let res = sqlx::query( + r#" +UPDATE + payments +SET + name = ?, + cost = ?, + user_id = ?, + category_id = ?, + date = ?, + updated_at = datetime() +WHERE + id = ? + "#, + ) + .bind(p.name.clone()) + .bind(p.cost) + .bind(p.user_id) + .bind(p.category_id) + .bind(p.date) + .bind(id) + .execute(pool) + .await; + + match res { + Ok(_) => true, + Err(err) => { + error!("Error updating payment {}: {:?}", id, err); + false + } + } +} + +pub async fn delete(pool: &SqlitePool, id: i64) -> bool { + let res = sqlx::query( + r#" +UPDATE + payments +SET + deleted_at = datetime() +WHERE + id = ? + "#, + ) + .bind(id) + .execute(pool) + .await; + + match res { + Ok(_) => true, + Err(err) => { + error!("Error deleting payment {}: {:?}", id, err); + false + } + } +} + +pub async fn search_category( + pool: &SqlitePool, + payment_name: String, +) -> Option { + let query = format!( + r#" +SELECT + category_id +FROM + payments +WHERE + deleted_at IS NULL + AND {} LIKE ? +ORDER BY + updated_at, created_at + "#, + utils::format_key_for_search("name") + ); + + let res = sqlx::query(&query) + .bind(text::format_search(&format!("%{}%", payment_name))) + .map(|row: SqliteRow| row.get("category_id")) + .fetch_one(pool) + .await; + + match res { + Ok(category) => Some(category), + Err(Error::RowNotFound) => None, + Err(err) => { + error!( + "Error looking for the category of {}: {:?}", + payment_name, err + ); + None + } + } +} + +pub async fn is_category_used(pool: &SqlitePool, category_id: i64) -> bool { + let query = r#" +SELECT + 1 +FROM + payments +WHERE + category_id = ? + AND deleted_at IS NULL +LIMIT + 1 + "#; + + let res = sqlx::query(&query).bind(category_id).fetch_one(pool).await; + + match res { + Ok(_) => true, + Err(Error::RowNotFound) => false, + Err(err) => { + error!( + "Error looking if category {} is used: {:?}", + category_id, err + ); + false + } + } +} + +pub async fn repartition(pool: &SqlitePool) -> HashMap { + let query = r#" +SELECT + users.id AS user_id, + COALESCE(payments.sum, 0) AS sum +FROM + users +LEFT OUTER JOIN ( + SELECT + user_id, + SUM(cost) AS sum + FROM + payments + WHERE + deleted_at IS NULL + AND frequency = 'Punctual' + GROUP BY + user_id +) payments +ON + users.id = payments.user_id"#; + + let res = sqlx::query(&query) + .map(|row: SqliteRow| (row.get("user_id"), row.get("sum"))) + .fetch_all(pool) + .await; + + match res { + Ok(costs) => HashMap::from_iter(costs), + Err(err) => { + error!("Error getting payments repartition: {:?}", err); + HashMap::new() + } + } +} + +pub async fn create_monthly_payments(pool: &SqlitePool) -> () { + let query = r#" +INSERT INTO + payments(name, cost, user_id, category_id, date, frequency, created_at) +SELECT + name, + cost, + user_id, + category_id, + date() AS date, + 'Punctual' AS frequency, + datetime() AS created_at +FROM + payments +WHERE + frequency = 'Monthly' + AND deleted_at IS NULL + "#; + + let res = sqlx::query(query).execute(pool).await; + + match res { + Ok(_) => (), + Err(err) => { + error!("Error creating monthly payments: {:?}", err); + () + } + } +} + +pub async fn last_week(pool: &SqlitePool) -> Vec { + let query = r#" +SELECT + strftime('%d/%m/%Y', payments.date) AS date, + (payments.name || ' (' || users.name || ')') AS name, + payments.cost AS amount, + (CASE + WHEN + payments.deleted_at IS NOT NULL + THEN + 'Deleted' + WHEN + payments.updated_at IS NOT NULL + AND payments.created_at < date('now', 'weekday 0', '-13 days') + THEN + 'Updated' + ELSE + 'Created' + END) AS action +FROM + payments +INNER JOIN + users +ON + payments.user_id = users.id +WHERE + payments.frequency = 'Punctual' + AND ( + ( + payments.created_at >= date('now', 'weekday 0', '-13 days') + AND payments.created_at < date('now', 'weekday 0', '-6 days') + ) OR ( + payments.updated_at >= date('now', 'weekday 0', '-13 days') + AND payments.updated_at < date('now', 'weekday 0', '-6 days') + ) OR ( + payments.deleted_at >= date('now', 'weekday 0', '-13 days') + AND payments.deleted_at < date('now', 'weekday 0', '-6 days') + ) + ) +ORDER BY + payments.date + "#; + + let res = sqlx::query_as::<_, Report>(query).fetch_all(pool).await; + + match res { + Ok(payments) => payments, + Err(err) => { + error!("Error listing payments for report: {:?}", err); + vec![] + } + } +} -- cgit v1.2.3