From 99466c6ceb848cf8147645f25deea89804b7b279 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 10 Oct 2021 21:21:06 +0200
Subject: Filter payments by start and end date

---
 src/db/payments.rs | 103 ++++++++++++++++++++++++++++++++++++++++-------------
 1 file changed, 79 insertions(+), 24 deletions(-)

(limited to 'src/db')

diff --git a/src/db/payments.rs b/src/db/payments.rs
index 25d1cbb..f20dbdc 100644
--- a/src/db/payments.rs
+++ b/src/db/payments.rs
@@ -37,28 +37,44 @@ INNER JOIN
 WHERE
     payments.deleted_at IS NULL
     AND payments.frequency = ?
-    {} {} {} {}
+    {} {} {} {} {} {}
         "#,
         name_query(payment_query.name.clone()),
         cost_query(payment_query.cost.clone()),
         user_query(payment_query.user),
-        category_query(payment_query.category)
+        category_query(payment_query.category),
+        date_query(
+            "payments.date >=".to_string(),
+            payment_query.start_date.clone()
+        ),
+        date_query(
+            "payments.date <=".to_string(),
+            payment_query.end_date.clone()
+        )
     );
 
-    let res = bind_category(
-        bind_user(
-            bind_cost(
-                bind_name(
-                    sqlx::query_as::<_, Count>(&query).bind(
-                        payment_query.frequency.unwrap_or(Frequency::Punctual),
+    let res = bind_date(
+        bind_date(
+            bind_category(
+                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.name.clone(),
+                    payment_query.user,
                 ),
-                payment_query.cost.clone(),
+                payment_query.category,
             ),
-            payment_query.user,
+            payment_query.start_date.clone(),
         ),
-        payment_query.category,
+        payment_query.end_date.clone(),
     )
     .fetch_one(pool)
     .await;
@@ -102,7 +118,7 @@ INNER JOIN
 WHERE
     payments.deleted_at IS NULL
     AND payments.frequency = ?
-    {} {} {} {}
+    {} {} {} {} {} {}
 ORDER BY
     payments.date DESC
 LIMIT ?
@@ -111,23 +127,39 @@ OFFSET ?
         name_query(payment_query.name.clone()),
         cost_query(payment_query.cost.clone()),
         user_query(payment_query.user),
-        category_query(payment_query.category)
+        category_query(payment_query.category),
+        date_query(
+            "payments.date >=".to_string(),
+            payment_query.start_date.clone()
+        ),
+        date_query(
+            "payments.date <=".to_string(),
+            payment_query.end_date.clone()
+        )
     );
 
-    let res = bind_category(
-        bind_user(
-            bind_cost(
-                bind_name(
-                    sqlx::query_as::<_, payment::Table>(&query).bind(
-                        payment_query.frequency.unwrap_or(Frequency::Punctual),
+    let res = bind_date(
+        bind_date(
+            bind_category(
+                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.name.clone(),
+                    payment_query.user,
                 ),
-                payment_query.cost.clone(),
+                payment_query.category,
             ),
-            payment_query.user,
+            payment_query.start_date.clone(),
         ),
-        payment_query.category,
+        payment_query.end_date.clone(),
     )
     .bind(per_page)
     .bind(offset)
@@ -230,6 +262,29 @@ fn bind_category<'a, Row: FromRow<'a, SqliteRow>>(
     }
 }
 
+fn date_query(name_and_op: String, date: Option<String>) -> String {
+    if date.map_or_else(|| false, |str| !str.is_empty()) {
+        format!("AND {} ?", name_and_op)
+    } else {
+        "".to_string()
+    }
+}
+
+fn bind_date<'a, Row: FromRow<'a, SqliteRow>>(
+    query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
+    date: Option<String>,
+) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
+    match date {
+        Some(d) => {
+            if d.is_empty() {
+                query
+            } else {
+                query.bind(d)
+            }
+        }
+        _ => query,
+    }
+}
 pub async fn list_for_stats(pool: &SqlitePool) -> Vec<payment::Stat> {
     let query = r#"
 SELECT
-- 
cgit v1.2.3