From af1177e814d19e63ce39c42fc7c5888e4b3d9604 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sat, 9 Oct 2021 10:27:45 +0200
Subject: Search by category

---
 src/controller/payments.rs |  4 ++++
 src/db/payments.rs         | 48 ++++++++++++++++++++++++++++++++++++----------
 src/queries.rs             |  6 ++++++
 src/templates.rs           |  1 +
 4 files changed, 49 insertions(+), 10 deletions(-)

(limited to 'src')

diff --git a/src/controller/payments.rs b/src/controller/payments.rs
index ab4bd92..883f9e1 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 categories = db::categories::list(&wallet.pool).await;
 
     let mut context = Context::new();
     context.insert("header", &templates::Header::Payments);
@@ -32,6 +33,7 @@ pub async fn table(
     context.insert("query", &query);
     context.insert("count", &count.count);
     context.insert("total_cost", &count.total_cost);
+    context.insert("categories", &categories);
 
     utils::template(
         &wallet.assets,
@@ -99,6 +101,7 @@ pub async fn create(
                         search: None,
                         frequency: Some(create_payment.frequency),
                         highlight: Some(id),
+                        category: None,
                     };
                     utils::redirect(&format!(
                         "/{}",
@@ -178,6 +181,7 @@ pub async fn update(
                     search: None,
                     frequency: Some(frequency),
                     highlight: Some(id),
+                    category: None,
                 };
                 utils::redirect(&format!("/{}", queries::payments_url(query)))
             } else {
diff --git a/src/db/payments.rs b/src/db/payments.rs
index 35e7f68..624ba9f 100644
--- a/src/db/payments.rs
+++ b/src/db/payments.rs
@@ -39,15 +39,20 @@ INNER JOIN
 WHERE
     payments.deleted_at IS NULL
     AND payments.frequency = ?
+    {}
     {}
         "#,
-        search_query(search.clone())
+        search_query(search.clone()),
+        category_query(payment_query.category)
     );
 
-    let res = bind_search(
-        sqlx::query_as::<_, Count>(&query)
-            .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)),
-        search,
+    let res = bind_category(
+        bind_search(
+            sqlx::query_as::<_, Count>(&query)
+                .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)),
+            search,
+        ),
+        payment_query.category,
     )
     .fetch_one(pool)
     .await;
@@ -93,18 +98,23 @@ WHERE
     payments.deleted_at IS NULL
     AND payments.frequency = ?
     {}
+    {}
 ORDER BY
     payments.date DESC
 LIMIT ?
 OFFSET ?
         "#,
-        search_query(search.clone())
+        search_query(search.clone()),
+        category_query(payment_query.category)
     );
 
-    let res = bind_search(
-        sqlx::query_as::<_, payment::Table>(&query)
-            .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)),
-        search,
+    let res = bind_category(
+        bind_search(
+            sqlx::query_as::<_, payment::Table>(&query)
+                .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)),
+            search,
+        ),
+        payment_query.category,
     )
     .bind(per_page)
     .bind(offset)
@@ -159,6 +169,24 @@ fn bind_search<'a, Row: FromRow<'a, SqliteRow>>(
     })
 }
 
+fn category_query(category: Option<i64>) -> String {
+    if category.is_some() {
+        "AND category_id = ?".to_string()
+    } else {
+        "".to_string()
+    }
+}
+
+fn bind_category<'a, Row: FromRow<'a, SqliteRow>>(
+    query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
+    category: Option<i64>,
+) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
+    match category {
+        Some(id) => query.bind(id),
+        _ => query,
+    }
+}
+
 pub async fn list_for_stats(pool: &SqlitePool) -> Vec<payment::Stat> {
     let query = r#"
 SELECT
diff --git a/src/queries.rs b/src/queries.rs
index a7ba28c..df57bd8 100644
--- a/src/queries.rs
+++ b/src/queries.rs
@@ -7,6 +7,7 @@ pub struct Payments {
     pub search: Option<String>,
     pub frequency: Option<Frequency>,
     pub highlight: Option<i64>,
+    pub category: Option<i64>,
 }
 
 pub fn payments_url(q: Payments) -> String {
@@ -38,6 +39,11 @@ pub fn payments_url(q: Payments) -> String {
         _ => (),
     };
 
+    match q.category {
+        Some(id) => params.push(format!("category={}", id)),
+        _ => (),
+    };
+
     if params.is_empty() {
         "".to_string()
     } else {
diff --git a/src/templates.rs b/src/templates.rs
index 7e3753a..89b0ed8 100644
--- a/src/templates.rs
+++ b/src/templates.rs
@@ -36,6 +36,7 @@ fn payments_params(args: &HashMap<String, Value>) -> Result<Value> {
         "search": args.get("search"),
         "frequency": args.get("frequency"),
         "highlight": args.get("highlight"),
+        "category": args.get("category"),
     });
 
     match serde_json::from_value(q) {
-- 
cgit v1.2.3