Filter payments by start and end date

4 files changed, 115 insertions, 26 deletions

diff --git a/src/controller/payments.rs b/src/controller/payments.rs
index 66ec056..42d3e3c 100644
--- a/src/controller/payments.rs
+++ b/src/controller/payments.rs
@@ -106,6 +106,8 @@ pub async fn create(
                         highlight: Some(id),
                         user: None,
                         category: None,
+                        start_date: None,
+                        end_date: None,
                     };
                     utils::redirect(&format!(
                         "/{}",
@@ -189,6 +191,8 @@ pub async fn update(
                     highlight: Some(id),
                     user: None,
                     category: None,
+                    start_date: None,
+                    end_date: None,
                 };
                 utils::redirect(&format!("/{}", queries::payments_url(query)))
             } else {
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
diff --git a/src/queries.rs b/src/queries.rs
index f10c7a1..db098e7 100644
--- a/src/queries.rs
+++ b/src/queries.rs
@@ -10,6 +10,8 @@ pub struct Payments {
     pub highlight: Option<i64>,
     pub user: Option<i64>,
     pub category: Option<i64>,
+    pub start_date: Option<String>,
+    pub end_date: Option<String>,
 }
 
 pub fn payments_url(q: Payments) -> String {
@@ -33,12 +35,20 @@ pub fn payments_url(q: Payments) -> String {
     };
 
     match q.name {
-        Some(str) => params.push(format!("name={}", str)),
+        Some(str) => {
+            if !str.is_empty() {
+                params.push(format!("name={}", str))
+            }
+        }
         _ => (),
     };
 
     match q.cost {
-        Some(n) => params.push(format!("cost={}", n)),
+        Some(str) => {
+            if !str.is_empty() {
+                params.push(format!("cost={}", str))
+            }
+        }
         _ => (),
     };
 
@@ -52,6 +62,24 @@ pub fn payments_url(q: Payments) -> String {
         _ => (),
     };
 
+    match q.start_date {
+        Some(str) => {
+            if !str.is_empty() {
+                params.push(format!("start_date={}", str))
+            }
+        }
+        _ => (),
+    };
+
+    match q.end_date {
+        Some(str) => {
+            if !str.is_empty() {
+                params.push(format!("end_date={}", str))
+            }
+        }
+        _ => (),
+    };
+
     if params.is_empty() {
         "".to_string()
     } else {
diff --git a/src/templates.rs b/src/templates.rs
index 2c8e72a..390a3aa 100644
--- a/src/templates.rs
+++ b/src/templates.rs
@@ -39,6 +39,8 @@ fn payments_params(args: &HashMap<String, Value>) -> Result<Value> {
         "highlight": args.get("highlight"),
         "user": args.get("user"),
         "category": args.get("category"),
+        "start_date": args.get("start_date"),
+        "end_date": args.get("end_date"),
     });
 
     match serde_json::from_value(q) {