aboutsummaryrefslogtreecommitdiff
path: root/src/db
diff options
context:
space:
mode:
Diffstat (limited to 'src/db')
-rw-r--r--src/db/payments.rs103
1 files changed, 79 insertions, 24 deletions
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