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 --- assets/main.js | 22 ++++++++ src/controller/payments.rs | 4 ++ src/db/payments.rs | 103 +++++++++++++++++++++++++++--------- src/queries.rs | 32 ++++++++++- src/templates.rs | 2 + templates/payment/create.html | 6 ++- templates/payment/table.html | 19 +++++-- templates/payment/table/search.html | 20 +++++++ templates/payment/update.html | 18 +++++-- 9 files changed, 192 insertions(+), 34 deletions(-) diff --git a/assets/main.js b/assets/main.js index 3dd4b6e..1b5620f 100644 --- a/assets/main.js +++ b/assets/main.js @@ -9,6 +9,7 @@ if (path == '/login') { } else if (path == '/') { // Payment table allow_select_reset() + allow_date_reset() } else if (path == '/payment') { // Payment creation @@ -67,6 +68,7 @@ function allow_select_reset() { button.className = 'g-Form__ResetSelect' button.onclick = function() { select.selectedIndex = 0 + button.style = 'visibility: hidden' } if (select.selectedIndex === 0) { button.style = 'visibility: hidden' @@ -79,6 +81,26 @@ function allow_select_reset() { }) } +function allow_date_reset() { + document.querySelectorAll('input[type="date"]').forEach(input => { + const button = document.createElement('input') + button.type = 'button' + button.value = 'Effacer' + button.className = 'g-Form__ResetSelect' + button.onclick = function() { + input.value = '' + button.style = 'visibility: hidden' + } + if (input.value === '') { + button.style = 'visibility: hidden' + } + input.onchange = function() { + button.style = 'visibility: visible' + } + input.parentNode.appendChild(button) + }) +} + function control_remove_button() { const removeInput = document.getElementsByName('remove-input')[0] const removeButton = document.getElementById('remove-button') 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 { + 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, +) -> 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 { 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, pub user: Option, pub category: Option, + pub start_date: Option, + pub end_date: Option, } 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) -> Result { "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) { diff --git a/templates/payment/create.html b/templates/payment/create.html index 4fc3245..519729e 100644 --- a/templates/payment/create.html +++ b/templates/payment/create.html @@ -12,9 +12,13 @@ class="g-Link g-Media__Large" href="/{{ payments_params( page=query.page, + frequency=query.frequency, name=query.name, cost=query.cost, - frequency=query.frequency + user=query.user, + category=query.category, + start_date=query.start_date, + end_date=query.end_date ) }}" > Retour aux paiements diff --git a/templates/payment/table.html b/templates/payment/table.html index c187f17..b0a6cee 100644 --- a/templates/payment/table.html +++ b/templates/payment/table.html @@ -28,9 +28,13 @@ class="g-Paragraph g-Button__Validate" href="/payment{{ payments_params( page=query.page, + frequency=query.frequency, name=query.name, cost=query.cost, - frequency=query.frequency + user=query.user, + category=query.category, + start_date=query.start_date, + end_date=query.end_date ) }}" > Ajouter un paiement @@ -52,9 +56,13 @@ class="g-Table__Row {% if query.highlight == payment.id %} g-Table__Row--Highlight {% endif %}" href="/payment/{{ payment.id }}{{ payments_params( page=query.page, + frequency=query.frequency, name=query.name, cost=query.cost, - frequency=query.frequency + user=query.user, + category=query.category, + start_date=query.start_date, + end_date=query.end_date ) }}" > {{ payment.name }} @@ -82,10 +90,13 @@ {{ paging::paging( url="/" ~ payments_params( + frequency=query.frequency, name=query.name, cost=query.cost, - frequency=query.frequency, - category=query.category + user=query.user, + category=query.category, + start_date=query.start_date, + end_date=query.end_date ), page=page, max_page=max_page diff --git a/templates/payment/table/search.html b/templates/payment/table/search.html index 8805cbb..e46b582 100644 --- a/templates/payment/table/search.html +++ b/templates/payment/table/search.html @@ -63,6 +63,26 @@ + + + + diff --git a/templates/payment/update.html b/templates/payment/update.html index 002117e..07549de 100644 --- a/templates/payment/update.html +++ b/templates/payment/update.html @@ -12,9 +12,13 @@ class="g-Link g-Media__Large" href="/{{ payments_params( page=query.page, + frequency=query.frequency, name=query.name, cost=query.cost, - frequency=query.frequency + user=query.user, + category=query.category, + start_date=query.start_date, + end_date=query.end_date ) }}" > Retour aux paiements @@ -35,9 +39,13 @@ class="g-Form" action="/payment/{{ payment.id }}/update{{ payments_params( page=query.page, + frequency=query.frequency, name=query.name, cost=query.cost, - frequency=query.frequency, + user=query.user, + category=query.category, + start_date=query.start_date, + end_date=query.end_date, highlight=query.highlight ) }}" method="POST" @@ -128,9 +136,13 @@ class="g-Form" action="/payment/{{ payment.id }}/delete{{ payments_params( page=query.page, + frequency=query.frequency, name=query.name, cost=query.cost, - frequency=query.frequency, + user=query.user, + category=query.category, + start_date=query.start_date, + end_date=query.end_date, highlight=query.highlight ) }}" method="POST" -- cgit v1.2.3