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) VALUES (?, ?, ?, ?, ?, ?) "#, ) .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) SELECT name, cost, user_id, category_id, date() AS date, 'Punctual' AS frequency 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![] } } }