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/db/payments.rs | 142 +++++++++++++++++++++++++++++++++++------------------
 1 file changed, 94 insertions(+), 48 deletions(-)

(limited to 'src/db')

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<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#"
@@ -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::<Vec<String>>()
-        .join(" ")
+fn name_query(name: Option<String>) -> 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<String>,
+) -> 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>) -> 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<String>,
+) -> 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<i64>) -> 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<i64>,
 ) -> 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<i64>) -> String {
     if category.is_some() {
-        "AND category_id = ?".to_string()
+        "AND payments.category_id = ?".to_string()
     } else {
         "".to_string()
     }
-- 
cgit v1.2.3