From 6e695bf7a0253b4f6d1db78fa4310616d8a1357f Mon Sep 17 00:00:00 2001 From: Joris Date: Sun, 10 Oct 2021 19:27:22 +0200 Subject: Search by name, cost and user --- src/controller/payments.rs | 11 +++- src/db/payments.rs | 142 ++++++++++++++++++++++++++++++--------------- src/queries.rs | 28 +++++---- src/templates.rs | 4 +- 4 files changed, 124 insertions(+), 61 deletions(-) (limited to 'src') diff --git a/src/controller/payments.rs b/src/controller/payments.rs index 883f9e1..66ec056 100644 --- a/src/controller/payments.rs +++ b/src/controller/payments.rs @@ -22,6 +22,7 @@ pub async fn table( let payments = db::payments::list_for_table(&wallet.pool, &query, PER_PAGE).await; let max_page = (count.count as f32 / PER_PAGE as f32).ceil() as i64; + let users = db::users::list(&wallet.pool).await; let categories = db::categories::list(&wallet.pool).await; let mut context = Context::new(); @@ -33,6 +34,7 @@ pub async fn table( context.insert("query", &query); context.insert("count", &count.count); context.insert("total_cost", &count.total_cost); + context.insert("users", &users); context.insert("categories", &categories); utils::template( @@ -98,9 +100,11 @@ pub async fn create( let page = (row - 1) / PER_PAGE + 1; let query = queries::Payments { page: Some(page), - search: None, + name: None, + cost: None, frequency: Some(create_payment.frequency), highlight: Some(id), + user: None, category: None, }; utils::redirect(&format!( @@ -176,11 +180,14 @@ pub async fn update( let row = db::payments::get_row(&wallet.pool, id, frequency).await; let page = (row - 1) / PER_PAGE + 1; + // TODO: keep name, cost, user and category when updating a line let query = queries::Payments { page: Some(page), - search: None, + name: None, + cost: None, frequency: Some(frequency), highlight: Some(id), + user: None, category: None, }; utils::redirect(&format!("/{}", queries::payments_url(query))) diff --git a/src/db/payments.rs b/src/db/payments.rs index 3b85dab..25d1cbb 100644 --- a/src/db/payments.rs +++ b/src/db/payments.rs @@ -23,8 +23,6 @@ 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 @@ -39,18 +37,26 @@ INNER JOIN WHERE payments.deleted_at IS NULL AND payments.frequency = ? - {} - {} + {} {} {} {} "#, - search_query(search.clone()), + name_query(payment_query.name.clone()), + cost_query(payment_query.cost.clone()), + user_query(payment_query.user), category_query(payment_query.category) ); let res = bind_category( - bind_search( - sqlx::query_as::<_, Count>(&query) - .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)), - search, + bind_user( + bind_cost( + bind_name( + sqlx::query_as::<_, Count>(&query).bind( + payment_query.frequency.unwrap_or(Frequency::Punctual), + ), + payment_query.name.clone(), + ), + payment_query.cost.clone(), + ), + payment_query.user, ), payment_query.category, ) @@ -75,7 +81,6 @@ pub async fn list_for_table( 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#" @@ -97,22 +102,30 @@ INNER JOIN WHERE payments.deleted_at IS NULL AND payments.frequency = ? - {} - {} + {} {} {} {} ORDER BY payments.date DESC LIMIT ? OFFSET ? "#, - search_query(search.clone()), + name_query(payment_query.name.clone()), + cost_query(payment_query.cost.clone()), + user_query(payment_query.user), category_query(payment_query.category) ); let res = bind_category( - bind_search( - sqlx::query_as::<_, payment::Table>(&query) - .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)), - search, + bind_user( + bind_cost( + bind_name( + sqlx::query_as::<_, payment::Table>(&query).bind( + payment_query.frequency.unwrap_or(Frequency::Punctual), + ), + payment_query.name.clone(), + ), + payment_query.cost.clone(), + ), + payment_query.user, ), payment_query.category, ) @@ -130,45 +143,78 @@ OFFSET ? } } -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"); - - search - .split_ascii_whitespace() - .map(|_| { - format!( - r#" -AND ( - {} LIKE ? - OR payments.cost LIKE ? - OR {} LIKE ? - OR strftime('%d/%m/%Y', date) LIKE ? -) - "#, - payments_name, users_name - ) - }) - .collect::>() - .join(" ") +fn name_query(name: Option) -> String { + if name.map_or_else(|| false, |str| !str.is_empty()) { + format!( + "AND {} LIKE ?", + utils::format_key_for_search("payments.name") + ) + } else { + "".to_string() + } +} + +fn bind_name<'a, Row: FromRow<'a, SqliteRow>>( + query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>, + name: Option, +) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> { + match name { + Some(str) => { + if str.is_empty() { + query + } else { + query.bind(text::format_search(&str)) + } + } + _ => query, + } +} + +fn cost_query(cost: Option) -> String { + if cost.map_or_else(|| false, |str| !str.is_empty()) { + "AND payments.cost = ?".to_string() + } else { + "".to_string() + } +} + +fn bind_cost<'a, Row: FromRow<'a, SqliteRow>>( + query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>, + cost: Option, +) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> { + match cost { + Some(str) => { + if str.is_empty() { + query + } else { + query.bind(str) + } + } + _ => query, + } } -fn bind_search<'a, Row: FromRow<'a, SqliteRow>>( +fn user_query(user: Option) -> String { + if user.is_some() { + "AND payments.user_id = ?".to_string() + } else { + "".to_string() + } +} + +fn bind_user<'a, Row: FromRow<'a, SqliteRow>>( query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>, - search: String, + user: Option, ) -> 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()) - }) + match user { + Some(id) => query.bind(id), + _ => query, + } } fn category_query(category: Option) -> String { if category.is_some() { - "AND category_id = ?".to_string() + "AND payments.category_id = ?".to_string() } else { "".to_string() } diff --git a/src/queries.rs b/src/queries.rs index df57bd8..f10c7a1 100644 --- a/src/queries.rs +++ b/src/queries.rs @@ -4,9 +4,11 @@ use serde::{Deserialize, Serialize}; #[derive(Deserialize, Serialize, Clone)] pub struct Payments { pub page: Option, - pub search: Option, + pub name: Option, + pub cost: Option, pub frequency: Option, pub highlight: Option, + pub user: Option, pub category: Option, } @@ -18,15 +20,6 @@ pub fn payments_url(q: Payments) -> String { Some(p) => params.push(format!("page={}", p)), }; - match q.search { - Some(s) => { - if !s.is_empty() { - params.push(format!("search={}", s)); - } - } - _ => (), - }; - match q.frequency { Some(Frequency::Monthly) => { params.push("frequency=Monthly".to_string()) @@ -39,6 +32,21 @@ pub fn payments_url(q: Payments) -> String { _ => (), }; + match q.name { + Some(str) => params.push(format!("name={}", str)), + _ => (), + }; + + match q.cost { + Some(n) => params.push(format!("cost={}", n)), + _ => (), + }; + + match q.user { + Some(id) => params.push(format!("user={}", id)), + _ => (), + }; + match q.category { Some(id) => params.push(format!("category={}", id)), _ => (), diff --git a/src/templates.rs b/src/templates.rs index 89b0ed8..2c8e72a 100644 --- a/src/templates.rs +++ b/src/templates.rs @@ -33,9 +33,11 @@ pub fn get() -> Tera { fn payments_params(args: &HashMap) -> Result { let q = json!({ "page": args.get("page"), - "search": args.get("search"), + "name": args.get("name"), + "cost": args.get("cost"), "frequency": args.get("frequency"), "highlight": args.get("highlight"), + "user": args.get("user"), "category": args.get("category"), }); -- cgit v1.2.3