diff options
Diffstat (limited to 'src/db')
| -rw-r--r-- | src/db/categories.rs | 132 | ||||
| -rw-r--r-- | src/db/incomes.rs | 494 | ||||
| -rw-r--r-- | src/db/jobs.rs | 56 | ||||
| -rw-r--r-- | src/db/mod.rs | 6 | ||||
| -rw-r--r-- | src/db/payments.rs | 525 | ||||
| -rw-r--r-- | src/db/users.rs | 144 | ||||
| -rw-r--r-- | src/db/utils.rs | 3 |
7 files changed, 1360 insertions, 0 deletions
diff --git a/src/db/categories.rs b/src/db/categories.rs new file mode 100644 index 0000000..05b1323 --- /dev/null +++ b/src/db/categories.rs @@ -0,0 +1,132 @@ +use sqlx::sqlite::SqlitePool; + +use crate::model::category::{Category, Create, Update}; + +pub async fn list(pool: &SqlitePool) -> Vec<Category> { + let res = sqlx::query_as::<_, Category>( + r#" +SELECT + id, + name, + color +FROM + categories +WHERE + deleted_at IS NULL +ORDER BY + name + "#, + ) + .fetch_all(pool) + .await; + + match res { + Ok(categories) => categories, + Err(err) => { + error!("Error listing categories: {:?}", err); + vec![] + } + } +} + +pub async fn get(pool: &SqlitePool, id: i64) -> Option<Category> { + let query = r#" +SELECT + id, + name, + color +FROM + categories +WHERE + id = ? + AND deleted_at IS NULL + "#; + + let res = sqlx::query_as::<_, Category>(query) + .bind(id) + .fetch_one(pool) + .await; + + match res { + Ok(p) => Some(p), + Err(err) => { + error!("Error looking for category {}: {:?}", id, err); + None + } + } +} + +pub async fn create(pool: &SqlitePool, c: &Create) -> Option<i64> { + let res = sqlx::query( + r#" +INSERT INTO + categories(name, color, created_at) +VALUES + (?, ?, datetime()) + "#, + ) + .bind(c.name.clone()) + .bind(c.color.clone()) + .execute(pool) + .await; + + match res { + Ok(x) => Some(x.last_insert_rowid()), + Err(err) => { + error!("Error creating category: {:?}", err); + None + } + } +} + +pub async fn update(pool: &SqlitePool, id: i64, c: &Update) -> bool { + let res = sqlx::query( + r#" +UPDATE + categories +SET + name = ?, + color = ?, + updated_at = datetime() +WHERE + id = ? + "#, + ) + .bind(c.name.clone()) + .bind(c.color.clone()) + .bind(id) + .execute(pool) + .await; + + match res { + Ok(_) => true, + Err(err) => { + error!("Error updating category {}: {:?}", id, err); + false + } + } +} + +pub async fn delete(pool: &SqlitePool, id: i64) -> bool { + let res = sqlx::query( + r#" +UPDATE + categories +SET + deleted_at = datetime() +WHERE + id = ? + "#, + ) + .bind(id) + .execute(pool) + .await; + + match res { + Ok(_) => true, + Err(err) => { + error!("Error deleting category {}: {:?}", id, err); + false + } + } +} diff --git a/src/db/incomes.rs b/src/db/incomes.rs new file mode 100644 index 0000000..cbbfce7 --- /dev/null +++ b/src/db/incomes.rs @@ -0,0 +1,494 @@ +use chrono::NaiveDate; +use sqlx::error::Error; +use sqlx::sqlite::{SqlitePool, SqliteRow}; +use sqlx_core::row::Row; +use std::collections::HashMap; +use std::iter::FromIterator; + +use crate::model::income::{Create, Form, Stat, Table, Update}; +use crate::model::report::Report; + +pub async fn count(pool: &SqlitePool) -> i64 { + let query = r#" +SELECT + COUNT(*) AS count +FROM + incomes +WHERE + incomes.deleted_at IS NULL + "#; + + let res = sqlx::query(&query) + .map(|row: SqliteRow| row.get("count")) + .fetch_one(pool) + .await; + + match res { + Ok(count) => count, + Err(err) => { + error!("Error counting incomes: {:?}", err); + 0 + } + } +} + +pub async fn list(pool: &SqlitePool, page: i64, per_page: i64) -> Vec<Table> { + let query = r#" +SELECT + incomes.id, + users.name AS user, + strftime('%m/%Y', incomes.date) AS date, + incomes.amount +FROM + incomes +INNER JOIN + users +ON + incomes.user_id = users.id +WHERE + incomes.deleted_at IS NULL +ORDER BY + incomes.date DESC +LIMIT ? +OFFSET ? + "#; + + let res = sqlx::query_as::<_, Table>(query) + .bind(per_page) + .bind((page - 1) * per_page) + .fetch_all(pool) + .await; + + match res { + Ok(incomes) => incomes, + Err(err) => { + error!("Error listing incomes: {:?}", err); + vec![] + } + } +} + +pub async fn get_row(pool: &SqlitePool, id: i64) -> i64 { + let query = r#" +SELECT + row +FROM ( + SELECT + ROW_NUMBER () OVER (ORDER BY date DESC) AS row, + id + FROM + incomes + WHERE + deleted_at IS NULL +) +WHERE + id = ? + "#; + + let res = sqlx::query(query) + .bind(id) + .map(|row: SqliteRow| row.get("row")) + .fetch_one(pool) + .await; + + match res { + Ok(count) => count, + Err(err) => { + error!("Error getting income row: {:?}", err); + 1 + } + } +} + +pub async fn get(pool: &SqlitePool, id: i64) -> Option<Form> { + let query = r#" +SELECT + id, + amount, + user_id, + CAST(strftime('%m', date) AS INTEGER) as month, + CAST(strftime('%Y', date) AS INTEGER) as year +FROM + incomes +WHERE + id = ? + AND deleted_at IS NULL + "#; + + let res = sqlx::query_as::<_, Form>(query) + .bind(id) + .fetch_one(pool) + .await; + + match res { + Ok(p) => Some(p), + Err(err) => { + error!("Error looking for income {}: {:?}", id, err); + None + } + } +} + +pub async fn create(pool: &SqlitePool, i: &Create) -> Option<i64> { + let res = sqlx::query( + r#" +INSERT INTO + incomes(user_id, date, amount, created_at) +VALUES + (?, ?, ?, datetime()) + "#, + ) + .bind(i.user_id) + .bind(NaiveDate::from_ymd(i.year, i.month, 1)) + .bind(i.amount) + .execute(pool) + .await; + + match res { + Ok(x) => Some(x.last_insert_rowid()), + Err(err) => { + error!("Error creating income: {:?}", err); + None + } + } +} + +pub async fn defined_at( + pool: &SqlitePool, + user_id: i64, + month: u32, + year: i32, +) -> Vec<i64> { + let query = r#" +SELECT + id +FROM + incomes +WHERE + user_id = ? + AND date = ? + AND deleted_at IS NULL + "#; + + let res = sqlx::query(&query) + .bind(user_id) + .bind(NaiveDate::from_ymd(year, month, 1)) + .map(|row: SqliteRow| row.get("id")) + .fetch_all(pool) + .await; + + match res { + Ok(ids) => ids, + Err(Error::RowNotFound) => vec![], + Err(err) => { + error!("Error looking if income is defined: {:?}", err); + vec![] + } + } +} + +pub async fn update(pool: &SqlitePool, id: i64, i: &Update) -> bool { + let res = sqlx::query( + r#" +UPDATE + incomes +SET + user_id = ?, + date = ?, + amount = ?, + updated_at = datetime() +WHERE + id = ? + "#, + ) + .bind(i.user_id) + .bind(NaiveDate::from_ymd(i.year, i.month, 1)) + .bind(i.amount) + .bind(id) + .execute(pool) + .await; + + match res { + Ok(_) => true, + Err(err) => { + error!("Error updating income {}: {:?}", id, err); + false + } + } +} + +pub async fn delete(pool: &SqlitePool, id: i64) -> bool { + let res = sqlx::query( + r#" +UPDATE + incomes +SET + deleted_at = datetime() +WHERE + id = ? + "#, + ) + .bind(id) + .execute(pool) + .await; + + match res { + Ok(_) => true, + Err(err) => { + error!("Error deleting income {}: {:?}", id, err); + false + } + } +} + +pub async fn defined_for_all(pool: &SqlitePool) -> Option<NaiveDate> { + let res = sqlx::query( + r#" +SELECT + (CASE COUNT(users.id) == COUNT(min_income.date) + WHEN 1 THEN MIN(min_income.date) + ELSE NULL + END) AS date +FROM + users +LEFT OUTER JOIN ( + SELECT + user_id, + MIN(date) AS date + FROM + incomes + WHERE + deleted_at IS NULL + GROUP BY + user_id +) min_income +ON + users.id = min_income.user_id; + "#, + ) + .map(|row: SqliteRow| row.get("date")) + .fetch_one(pool) + .await; + + match res { + Ok(d) => d, + Err(err) => { + error!("Error looking for incomes defined for all: {:?}", err); + None + } + } +} + +pub async fn cumulative( + pool: &SqlitePool, + from: NaiveDate, +) -> HashMap<i64, i64> { + let res = sqlx::query(&cumulative_query(from)) + .map(|row: SqliteRow| (row.get("user_id"), row.get("income"))) + .fetch_all(pool) + .await; + + match res { + Ok(incomes) => HashMap::from_iter(incomes), + Err(err) => { + error!("Error computing cumulative income: {:?}", err); + HashMap::new() + } + } +} + +/// Select cumulative income of users from the given date and until now. +/// +/// Associate each month income to its start and end bounds, +/// then compute the total income of each period, +/// sum it to get the final result. +/// +/// Considering each month to be 365 / 12 days long. +fn cumulative_query(from: NaiveDate) -> String { + format!( + r#" +SELECT + users.id AS user_id, + COALESCE(incomes.income, 0) AS income +FROM + users +LEFT OUTER JOIN ( + SELECT + user_id, + CAST(ROUND(SUM(count)) AS INTEGER) AS income + FROM ( + SELECT + I1.user_id, + ((JULIANDAY(MIN(I2.date)) - JULIANDAY(I1.date)) * I1.amount * 12 / 365) AS count + FROM + ({}) AS I1 + INNER JOIN + ({}) AS I2 + ON + I2.date > I1.date + AND I2.user_id == I1.user_id + GROUP BY + I1.date, I1.user_id + ) + GROUP BY + user_id +) incomes +ON + users.id = incomes.user_id + "#, + bounded_query(">".to_string(), from.format("%Y-%m-%d").to_string()), + bounded_query("<".to_string(), "date()".to_string()) + ) +} + +/// Select bounded incomes to the operator and date. +/// +/// It filters incomes according to the operator and date, +/// and adds the income at this date. +fn bounded_query(op: String, date: String) -> String { + format!( + r#" +SELECT + user_id, + date, + amount +FROM ( + SELECT + user_id, + {} AS date, + amount, + MAX(date) AS max_date + FROM + incomes + WHERE + date <= {} + AND deleted_at IS NULL + GROUP BY + user_id +) UNION +SELECT + user_id, + date, + amount +FROM + incomes +WHERE + date {} {} + AND deleted_at IS NULL + "#, + date, date, op, date + ) +} + +/// Select total income each month. +/// +/// For each month, from the first defined income and until now, +/// compute the total income of the users. +pub async fn total_each_month(pool: &SqlitePool) -> Vec<Stat> { + let query = r#" +WITH RECURSIVE dates(date) AS ( + VALUES(( + SELECT + strftime('%Y-%m-01', MIN(date)) + FROM + incomes + WHERE + deleted_at IS NULL + )) + UNION ALL + SELECT + date(date, '+1 month') + FROM + dates + WHERE + date < date(date(), '-1 month') +) +SELECT + strftime('%Y-%m-01', dates.date) AS date, + ( + SELECT + SUM(amount) AS amount + FROM ( + SELECT ( + SELECT + amount + FROM + incomes + WHERE + user_id = users.id + AND date < date(dates.date, '+1 month') + AND deleted_at IS NULL + ORDER BY + date DESC + LIMIT + 1 + ) AS amount + FROM + users + ) + ) AS amount +FROM + dates; + "#; + + let res = sqlx::query_as::<_, Stat>(query).fetch_all(pool).await; + + match res { + Ok(xs) => xs, + Err(err) => { + error!("Error listing incomes for statistics: {:?}", err); + vec![] + } + } +} + +pub async fn last_week(pool: &SqlitePool) -> Vec<Report> { + let query = r#" +SELECT + strftime('%m/%Y', incomes.date) AS date, + users.name AS name, + incomes.amount AS amount, + (CASE + WHEN + incomes.deleted_at IS NOT NULL + THEN + 'Deleted' + WHEN + incomes.updated_at IS NOT NULL + AND incomes.created_at < date('now', 'weekday 0', '-13 days') + THEN + 'Updated' + ELSE + 'Created' + END) AS action +FROM + incomes +INNER JOIN + users +ON + incomes.user_id = users.id +WHERE + ( + incomes.created_at >= date('now', 'weekday 0', '-13 days') + AND incomes.created_at < date('now', 'weekday 0', '-6 days') + ) OR ( + incomes.updated_at >= date('now', 'weekday 0', '-13 days') + AND incomes.updated_at < date('now', 'weekday 0', '-6 days') + ) OR ( + incomes.deleted_at >= date('now', 'weekday 0', '-13 days') + AND incomes.deleted_at < date('now', 'weekday 0', '-6 days') + ) +ORDER BY + incomes.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![] + } + } +} diff --git a/src/db/jobs.rs b/src/db/jobs.rs new file mode 100644 index 0000000..88c2005 --- /dev/null +++ b/src/db/jobs.rs @@ -0,0 +1,56 @@ +use sqlx::error::Error; +use sqlx::sqlite::SqlitePool; + +use crate::model::job::Job; + +pub async fn should_run(pool: &SqlitePool, job: Job) -> bool { + let run_from = match job { + Job::WeeklyReport => "date('now', 'weekday 0', '-6 days')", + Job::MonthlyPayment => "date('now', 'start of month')", + }; + + let query = format!( + r#" +SELECT + 1 +FROM + jobs +WHERE + name = ? + AND last_execution < {} + "#, + run_from + ); + + let res = sqlx::query(&query).bind(job).fetch_one(pool).await; + + match res { + Ok(_) => true, + Err(Error::RowNotFound) => false, + Err(err) => { + error!("Error looking if job should run: {:?}", err); + false + } + } +} + +pub async fn actualize_last_execution(pool: &SqlitePool, job: Job) -> () { + let query = r#" +UPDATE + jobs +SET + last_execution = datetime() +WHERE + name = ? + "#; + + let res = sqlx::query(query).bind(job).execute(pool).await; + + match res { + Ok(_) => (), + Err(err) => { + error!("Error actualizing job last execution: {:?}", err); + () + } + } +} diff --git a/src/db/mod.rs b/src/db/mod.rs new file mode 100644 index 0000000..a0aa3dc --- /dev/null +++ b/src/db/mod.rs @@ -0,0 +1,6 @@ +pub mod categories; +pub mod incomes; +pub mod jobs; +pub mod payments; +pub mod users; +mod utils; 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<payment::Table> { + 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::<Vec<String>>() + .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<payment::Stat> { + 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<payment::Form> { + 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<i64> { + 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<i64> { + 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<i64, i64> { + 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<Report> { + 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![] + } + } +} diff --git a/src/db/users.rs b/src/db/users.rs new file mode 100644 index 0000000..82326a9 --- /dev/null +++ b/src/db/users.rs @@ -0,0 +1,144 @@ +use sqlx::error::Error; +use sqlx::sqlite::{SqlitePool, SqliteRow}; +use sqlx_core::row::Row; + +use crate::model::user::User; + +pub async fn list(pool: &SqlitePool) -> Vec<User> { + let res = sqlx::query_as::<_, User>( + r#" +SELECT + id, + name, + email +FROM + users +ORDER BY + name + "#, + ) + .fetch_all(pool) + .await; + + match res { + Ok(users) => users, + Err(err) => { + error!("Error listing users: {:?}", err); + vec![] + } + } +} + +pub async fn set_login_token( + pool: &SqlitePool, + email: String, + login_token: String, +) -> bool { + let res = sqlx::query( + r#" +UPDATE + users +SET + login_token = ?, + updated_at = datetime() +WHERE + email = ? + "#, + ) + .bind(login_token) + .bind(email) + .execute(pool) + .await; + + match res { + Ok(_) => true, + Err(err) => { + error!("Error updating login token: {:?}", err); + false + } + } +} + +pub async fn remove_login_token(pool: &SqlitePool, id: i64) -> bool { + let res = sqlx::query( + r#" +UPDATE + users +SET + login_token = NULL, + updated_at = datetime() +WHERE + id = ? + "#, + ) + .bind(id) + .execute(pool) + .await; + + match res { + Ok(_) => true, + Err(err) => { + error!("Error removing login token: {:?}", err); + false + } + } +} + +pub async fn get_by_login_token( + pool: &SqlitePool, + login_token: String, +) -> Option<User> { + let res = sqlx::query_as::<_, User>( + r#" +SELECT + id, + name, + email +FROM + users +WHERE + login_token = ? + "#, + ) + .bind(login_token) + .fetch_one(pool) + .await; + + match res { + Ok(user) => Some(user), + Err(Error::RowNotFound) => None, + Err(err) => { + error!("Error getting user from login token: {:?}", err); + None + } + } +} + +pub async fn get_password_hash( + pool: &SqlitePool, + email: String, +) -> Option<String> { + let res = sqlx::query( + r#" +SELECT + password +FROM + users +WHERE + email = ? + "#, + ) + .bind(email) + .map(|row: SqliteRow| row.get("password")) + .fetch_one(pool) + .await; + + match res { + Ok(hash) => Some(hash), + Err(Error::RowNotFound) => None, + Err(err) => { + error!("Error getting password hash: {:?}", err); + None + } + } +} diff --git a/src/db/utils.rs b/src/db/utils.rs new file mode 100644 index 0000000..621a69c --- /dev/null +++ b/src/db/utils.rs @@ -0,0 +1,3 @@ +pub fn format_key_for_search(value: &str) -> String { + format!("replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower({}), 'à', 'a'), 'â', 'a'), 'ç', 'c'), 'è', 'e'), 'é', 'e'), 'ê', 'e'), 'ë', 'e'), 'î', 'i'), 'ï', 'i'), 'ô', 'o'), 'ù', 'u'), 'û', 'u'), 'ü', 'u')", value) +} |