Search by name, cost and user

4 files changed, 124 insertions, 61 deletions

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<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()
     }
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<i64>,
-    pub search: Option<String>,
+    pub name: Option<String>,
+    pub cost: Option<String>,
     pub frequency: Option<Frequency>,
     pub highlight: Option<i64>,
+    pub user: Option<i64>,
     pub category: Option<i64>,
 }
 
@@ -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<String, Value>) -> Result<Value> {
     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"),
     });