diff options
Diffstat (limited to 'src/db')
| -rw-r--r-- | src/db/payments.rs | 103 |
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 |