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 --- assets/main.css | 10 +-- src/controller/payments.rs | 11 ++- src/db/payments.rs | 142 ++++++++++++++++++++++++------------ src/queries.rs | 28 ++++--- src/templates.rs | 4 +- templates/category/table.html | 2 +- templates/payment/create.html | 3 +- templates/payment/table.html | 91 ++++++----------------- templates/payment/table/search.html | 68 +++++++++++++++++ templates/payment/update.html | 9 ++- 10 files changed, 227 insertions(+), 141 deletions(-) create mode 100644 templates/payment/table/search.html diff --git a/assets/main.css b/assets/main.css index 6a0be98..e69a0b2 100644 --- a/assets/main.css +++ b/assets/main.css @@ -210,6 +210,11 @@ body { text-align: right; } +.g-Table__NoResults { + margin: var(--size-camel) 0 var(--size-lion); + text-align: center; +} + /* Paging */ .g-Paging { @@ -330,11 +335,6 @@ body { /* Payment */ -.g-Payments__NoResults { - margin-top: var(--size-camel); - text-align: center; -} - .g-Payments__Header { display: flex; justify-content: space-between; 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"), }); diff --git a/templates/category/table.html b/templates/category/table.html index ad42258..e05c84b 100644 --- a/templates/category/table.html +++ b/templates/category/table.html @@ -9,7 +9,7 @@
{% if not categories %} -
+
Il n’y a aucune catégorie.
{% endif %} diff --git a/templates/payment/create.html b/templates/payment/create.html index 8defad3..4fc3245 100644 --- a/templates/payment/create.html +++ b/templates/payment/create.html @@ -12,7 +12,8 @@ class="g-Link g-Media__Large" href="/{{ payments_params( page=query.page, - search=query.search, + name=query.name, + cost=query.cost, frequency=query.frequency ) }}" > diff --git a/templates/payment/table.html b/templates/payment/table.html index da15b22..c187f17 100644 --- a/templates/payment/table.html +++ b/templates/payment/table.html @@ -9,84 +9,34 @@ {% block main %}
- {% if not payments %} -
+ {% if not payments %} +
Aucun paiement ne correspond à votre recherche.
- - - Nouveau - - {% else %} -
{{ count | numeric }} paiement{{ count | pluralize }} comptabilisant {{ total_cost | euros() }}.
+ {% endif %} - - Ajouter un paiement - - + + Ajouter un paiement + + + {% if payments %}
Nom @@ -102,7 +52,8 @@ class="g-Table__Row {% if query.highlight == payment.id %} g-Table__Row--Highlight {% endif %}" href="/payment/{{ payment.id }}{{ payments_params( page=query.page, - search=query.search, + name=query.name, + cost=query.cost, frequency=query.frequency ) }}" > @@ -131,14 +82,14 @@ {{ paging::paging( url="/" ~ payments_params( - search=query.search, + name=query.name, + cost=query.cost, frequency=query.frequency, category=query.category ), page=page, max_page=max_page ) }} - {% endif %}
diff --git a/templates/payment/table/search.html b/templates/payment/table/search.html new file mode 100644 index 0000000..8805cbb --- /dev/null +++ b/templates/payment/table/search.html @@ -0,0 +1,68 @@ +
+ + + + + + + + + + + + + +
diff --git a/templates/payment/update.html b/templates/payment/update.html index 4e244f4..002117e 100644 --- a/templates/payment/update.html +++ b/templates/payment/update.html @@ -12,7 +12,8 @@ class="g-Link g-Media__Large" href="/{{ payments_params( page=query.page, - search=query.search, + name=query.name, + cost=query.cost, frequency=query.frequency ) }}" > @@ -34,7 +35,8 @@ class="g-Form" action="/payment/{{ payment.id }}/update{{ payments_params( page=query.page, - search=query.search, + name=query.name, + cost=query.cost, frequency=query.frequency, highlight=query.highlight ) }}" @@ -126,7 +128,8 @@ class="g-Form" action="/payment/{{ payment.id }}/delete{{ payments_params( page=query.page, - search=query.search, + name=query.name, + cost=query.cost, frequency=query.frequency, highlight=query.highlight ) }}" -- cgit v1.2.3