aboutsummaryrefslogtreecommitdiff
path: root/src/db/payments.rs
diff options
context:
space:
mode:
Diffstat (limited to 'src/db/payments.rs')
-rw-r--r--src/db/payments.rs827
1 files changed, 382 insertions, 445 deletions
diff --git a/src/db/payments.rs b/src/db/payments.rs
index 25b10f4..540a006 100644
--- a/src/db/payments.rs
+++ b/src/db/payments.rs
@@ -1,10 +1,6 @@
-use sqlx::error::Error;
-use sqlx::sqlite::{Sqlite, SqliteArguments};
-use sqlx::sqlite::{SqlitePool, SqliteRow};
-use sqlx::FromRow;
-use sqlx_core::row::Row;
use std::collections::HashMap;
use std::iter::FromIterator;
+use tokio_rusqlite::{Connection, Row, params_from_iter, types::ToSql, named_params};
use crate::db::utils;
use crate::model::frequency::Frequency;
@@ -13,71 +9,75 @@ use crate::model::report::Report;
use crate::queries;
use crate::utils::text;
-#[derive(FromRow)]
pub struct Count {
pub count: i64,
pub total_cost: i64,
}
+fn row_to_count(row: &Row) -> Result<Count, rusqlite::Error> {
+ Ok(Count {
+ count: row.get(0)?,
+ total_cost: row.get(1)?,
+ })
+}
+
+fn row_to_table(row: &Row) -> Result<payment::Table, rusqlite::Error> {
+ Ok(payment::Table {
+ id: row.get(0)?,
+ name: row.get(1)?,
+ cost: row.get(2)?,
+ user: row.get(3)?,
+ category_name: row.get(4)?,
+ category_color: row.get(5)?,
+ date: row.get(6)?,
+ frequency: row.get(7)?
+ })
+}
+
+fn row_to_stat(row: &Row) -> Result<payment::Stat, rusqlite::Error> {
+ Ok(payment::Stat {
+ start_date: row.get(0)?,
+ cost: row.get(1)?,
+ category_id: row.get(2)?,
+ })
+}
+
+fn row_to_form(row: &Row) -> Result<payment::Form, rusqlite::Error> {
+ Ok(payment::Form {
+ id: row.get(0)?,
+ name: row.get(1)?,
+ cost: row.get(2)?,
+ user_id: row.get(3)?,
+ category_id: row.get(4)?,
+ date: row.get(5)?,
+ frequency: row.get(6)?
+ })
+}
+
pub async fn count(
- pool: &SqlitePool,
+ conn: &Connection,
payment_query: &queries::Payments,
) -> Count {
- let query = format!(
- r#"
-SELECT
- COUNT(*) AS count,
- SUM(payments.cost) AS total_cost
-FROM
- payments
-INNER JOIN
- users ON users.id = payments.user_id
-INNER JOIN
- categories ON categories.id = payments.category_id
-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),
- 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_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.user,
- ),
- payment_query.category,
- ),
- payment_query.start_date.clone(),
- ),
- payment_query.end_date.clone(),
- )
- .fetch_one(pool)
- .await;
+ let mut query = format!(r#"
+ SELECT
+ COUNT(*) AS count,
+ SUM(payments.cost) AS total_cost
+ FROM payments
+ INNER JOIN users ON users.id = payments.user_id
+ INNER JOIN categories ON categories.id = payments.category_id
+ WHERE payments.deleted_at IS NULL
+ "#);
+
+ let mut params = Vec::<Box<dyn ToSql + Send>>::new();
+ complete_search_query(payment_query, &mut query, &mut params);
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(&query)?;
+ let mut iter = stmt.query_map(params_from_iter(params), row_to_count)?;
+ utils::one(&mut iter)
+ })
+ .await;
match res {
Ok(count) => count,
@@ -92,79 +92,42 @@ WHERE
}
pub async fn list_for_table(
- pool: &SqlitePool,
+ conn: &Connection,
payment_query: &queries::Payments,
per_page: i64,
) -> Vec<payment::Table> {
- let offset = (payment_query.page.unwrap_or(1) - 1) * per_page;
-
- let query = format!(
- r#"
-SELECT
- payments.id,
- payments.name,
- payments.cost,
- users.name AS user,
- categories.name AS category_name,
- categories.color AS category_color,
- strftime('%d/%m/%Y', date) AS date,
- payments.frequency AS frequency
-FROM
- payments
-INNER JOIN
- users ON users.id = payments.user_id
-INNER JOIN
- categories ON categories.id = payments.category_id
-WHERE
- payments.deleted_at IS NULL
- AND payments.frequency = ?
- {} {} {} {} {} {}
-ORDER BY
- payments.date DESC
-LIMIT ?
-OFFSET ?
- "#,
- name_query(payment_query.name.clone()),
- cost_query(payment_query.cost.clone()),
- user_query(payment_query.user),
- 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_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.user,
- ),
- payment_query.category,
- ),
- payment_query.start_date.clone(),
- ),
- payment_query.end_date.clone(),
- )
- .bind(per_page)
- .bind(offset)
- .fetch_all(pool)
- .await;
+ let mut query = format!(r#"
+ SELECT
+ payments.id,
+ payments.name,
+ payments.cost,
+ users.name AS user,
+ categories.name AS category_name,
+ categories.color AS category_color,
+ strftime('%d/%m/%Y', date) AS date,
+ payments.frequency AS frequency
+ FROM payments
+ INNER JOIN users ON users.id = payments.user_id
+ INNER JOIN categories ON categories.id = payments.category_id
+ WHERE payments.deleted_at IS NULL
+ "#);
+
+ let mut params = Vec::<Box<dyn ToSql + Send>>::new();
+ complete_search_query(payment_query, &mut query, &mut params);
+
+ query.push_str("ORDER BY payments.date DESC LIMIT ? OFFSET ?");
+ params.push(Box::new(per_page));
+ params.push(Box::new((payment_query.page.unwrap_or(1) - 1) * per_page));
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(&query)?;
+ let payments = stmt
+ .query_map(params_from_iter(params), row_to_table)?
+ .collect::<Result<Vec<payment::Table>, _>>()?;
+ Ok(payments)
+ })
+ .await;
match res {
Ok(payments) => payments,
@@ -175,137 +138,101 @@ OFFSET ?
}
}
-fn name_query(name: Option<String>) -> String {
- if name.map_or_else(|| false, |str| !str.is_empty()) {
- format!(
- "AND {} LIKE ?",
- utils::format_key_for_search("payments.name")
- )
- } else {
- "".to_string()
- }
-}
-
-fn bind_name<'a, Row: FromRow<'a, SqliteRow>>(
- query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
- name: Option<String>,
-) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
- match name {
- Some(str) => {
- if str.is_empty() {
- query
- } else {
- query.bind(text::format_search(&str))
- }
- }
- _ => query,
- }
+fn complete_search_query(
+ q: &queries::Payments,
+ query: &mut String,
+ params: &mut Vec<Box<dyn ToSql + Send>>
+) {
+ complete_frequency(q.frequency, query, params);
+ complete_name(q.name.clone(), query, params);
+ complete_cost(q.cost.clone(), query, params);
+ complete_user(q.user, query, params);
+ complete_category(q.category, query, params);
+ complete_date("payments.date >=".to_string(), q.start_date.clone(), query, params);
+ complete_date("payments.date <=".to_string(), q.end_date.clone(), query, params);
}
-fn cost_query(cost: Option<String>) -> String {
- if cost.map_or_else(|| false, |str| !str.is_empty()) {
- "AND payments.cost = ?".to_string()
- } else {
- "".to_string()
- }
+fn complete_frequency(frequency: Option<Frequency>, query: &mut String, params: &mut Vec<Box<dyn ToSql + Send>>) {
+ query.push_str("AND payments.frequency = ?");
+ params.push(Box::new(frequency.unwrap_or(Frequency::Punctual).to_string()));
}
-fn bind_cost<'a, Row: FromRow<'a, SqliteRow>>(
- query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
- cost: Option<String>,
-) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
- match cost {
- Some(str) => {
- if str.is_empty() {
- query
- } else {
- query.bind(str)
- }
+fn complete_name(name: Option<String>, query: &mut String, params: &mut Vec<Box<dyn ToSql + Send>>) {
+ if let Some(name) = name {
+ if !name.is_empty() {
+ query.push_str(format!(
+ "AND {} LIKE ?",
+ utils::format_key_for_search("payments.name")
+ ).as_str());
+ params.push(Box::new(name));
}
- _ => query,
- }
-}
-
-fn user_query(user: Option<i64>) -> String {
- if user.is_some() {
- "AND payments.user_id = ?".to_string()
- } else {
- "".to_string()
}
}
-fn bind_user<'a, Row: FromRow<'a, SqliteRow>>(
- query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
- user: Option<i64>,
-) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
- match user {
- Some(id) => query.bind(id),
- _ => query,
- }
-}
-
-fn category_query(category: Option<i64>) -> String {
- if category.is_some() {
- "AND payments.category_id = ?".to_string()
- } else {
- "".to_string()
+fn complete_cost(cost: Option<String>, query: &mut String, params: &mut Vec<Box<dyn ToSql + Send>>) {
+ if let Some(cost) = cost {
+ if !cost.is_empty() {
+ query.push_str("AND payments.cost = ?");
+ params.push(Box::new(cost))
+ }
}
}
-fn bind_category<'a, Row: FromRow<'a, SqliteRow>>(
- query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
- category: Option<i64>,
-) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
- match category {
- Some(id) => query.bind(id),
- _ => query,
+fn complete_user(user: Option<i64>, query: &mut String, params: &mut Vec<Box<dyn ToSql + Send>>) {
+ if let Some(user) = user {
+ query.push_str("AND payments.user_id = ?");
+ params.push(Box::new(user))
}
}
-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 complete_category(category: Option<i64>, query: &mut String, params: &mut Vec<Box<dyn ToSql + Send>>) {
+ if let Some(category) = category {
+ query.push_str("AND payments.category_id = ?");
+ params.push(Box::new(category));
}
}
-fn bind_date<'a, Row: FromRow<'a, SqliteRow>>(
- query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
+fn complete_date(
+ name_and_op: String,
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: &mut String,
+ params: &mut Vec<Box<dyn ToSql + Send>>
+) {
+ if let Some(date) = date {
+ if !date.is_empty() {
+ query.push_str(format!("AND {name_and_op} ?").as_str());
+ params.push(Box::new(date));
}
- _ => query,
}
}
-pub async fn list_for_stats(pool: &SqlitePool) -> Vec<payment::Stat> {
+
+pub async fn list_for_stats(conn: &Connection) -> Vec<payment::Stat> {
let query = r#"
-SELECT
- strftime('%Y-%m-01', payments.date) AS start_date,
- SUM(payments.cost) AS cost,
- payments.category_id AS category_id
-FROM
- payments
-WHERE
- payments.deleted_at IS NULL
- AND payments.frequency = 'Punctual'
-GROUP BY
- start_date,
- payments.category_id;
+ SELECT
+ strftime('%Y-%m-01', payments.date) AS start_date,
+ SUM(payments.cost) AS cost,
+ payments.category_id AS category_id
+ FROM
+ payments
+ WHERE
+ payments.deleted_at IS NULL
+ AND payments.frequency = 'Punctual'
+ GROUP BY
+ start_date,
+ payments.category_id;
"#;
- let result = sqlx::query_as::<_, payment::Stat>(query)
- .fetch_all(pool)
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(&query)?;
+ let payments = stmt
+ .query_map([], row_to_stat)?
+ .collect::<Result<Vec<payment::Stat>, _>>()?;
+ Ok(payments)
+ })
.await;
- match result {
+ match res {
Ok(payments) => payments,
Err(err) => {
log::error!("Error listing payments for statistics: {:?}", err);
@@ -314,29 +241,30 @@ GROUP BY
}
}
-pub async fn get_row(pool: &SqlitePool, id: i64, frequency: Frequency) -> i64 {
+pub async fn get_row(conn: &Connection, id: i64, frequency: Frequency) -> i64 {
let query = r#"
-SELECT
- row
-FROM (
- SELECT
- ROW_NUMBER () OVER (ORDER BY date DESC) AS row,
- id
- FROM
- payments
- WHERE
- deleted_at IS NULL
- AND frequency = ?
-)
-WHERE
- id = ?
+ SELECT row
+ FROM (
+ SELECT
+ ROW_NUMBER () OVER (ORDER BY date DESC) AS row,
+ id
+ FROM payments
+ WHERE
+ deleted_at IS NULL
+ AND frequency = :frequency
+ )
+ WHERE id = :id
"#;
- let res = sqlx::query(query)
- .bind(frequency)
- .bind(id)
- .map(|row: SqliteRow| row.get("row"))
- .fetch_one(pool)
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+ let mut iter = stmt.query_map(
+ named_params![":id": id, ":frequency": frequency.to_string()],
+ |row| row.get(0)
+ )?;
+ utils::one::<i64, _>(&mut iter)
+ })
.await;
match res {
@@ -348,26 +276,28 @@ WHERE
}
}
-pub async fn get_for_form(pool: &SqlitePool, id: i64) -> Option<payment::Form> {
+pub async fn get_for_form(conn: &Connection, id: i64) -> Option<payment::Form> {
let query = r#"
-SELECT
- id,
- name,
- cost,
- user_id,
- category_id,
- strftime('%Y-%m-%d', date) AS date,
- frequency AS frequency
-FROM
- payments
-WHERE
- id = ?
- AND deleted_at IS NULL
+ SELECT
+ id,
+ name,
+ cost,
+ user_id,
+ category_id,
+ strftime('%Y-%m-%d', date) AS date,
+ frequency AS frequency
+ FROM payments
+ WHERE
+ id = :id
+ AND deleted_at IS NULL
"#;
- let res = sqlx::query_as::<_, payment::Form>(query)
- .bind(id)
- .fetch_one(pool)
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+ let mut iter = stmt.query_map(named_params![":id": id], row_to_form)?;
+ utils::one(&mut iter)
+ })
.await;
match res {
@@ -379,26 +309,31 @@ WHERE
}
}
-pub async fn create(pool: &SqlitePool, p: &payment::Create) -> Option<i64> {
- let res = sqlx::query(
- r#"
-INSERT INTO
- payments(name, cost, user_id, category_id, date, frequency)
-VALUES
- (?, ?, ?, ?, ?, ?)
- "#,
- )
- .bind(p.name.clone())
- .bind(p.cost)
- .bind(p.user_id)
- .bind(p.category_id)
- .bind(p.date)
- .bind(p.frequency)
- .execute(pool)
- .await;
+pub async fn create(conn: &Connection, p: payment::Create) -> Option<i64> {
+ let query = r#"
+ INSERT INTO payments(name, cost, user_id, category_id, date, frequency)
+ VALUES (:name, :cost, :user_id, :category_id, :date, :frequency)
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ conn.execute(
+ query,
+ named_params![
+ ":name": p.name,
+ ":cost": p.cost,
+ ":user_id": p.user_id,
+ ":category_id": p.category_id,
+ ":date": p.date,
+ ":frequency": p.frequency.to_string()
+ ],
+ )?;
+ Ok(conn.last_insert_rowid())
+ })
+ .await;
match res {
- Ok(x) => Some(x.last_insert_rowid()),
+ Ok(payment_id) => Some(payment_id),
Err(err) => {
log::error!("Error creating payment: {:?}", err);
None
@@ -406,30 +341,35 @@ VALUES
}
}
-pub async fn update(pool: &SqlitePool, id: i64, p: &payment::Update) -> bool {
- let res = sqlx::query(
- r#"
-UPDATE
- payments
-SET
- name = ?,
- cost = ?,
- user_id = ?,
- category_id = ?,
- date = ?,
- updated_at = datetime()
-WHERE
- id = ?
- "#,
- )
- .bind(p.name.clone())
- .bind(p.cost)
- .bind(p.user_id)
- .bind(p.category_id)
- .bind(p.date)
- .bind(id)
- .execute(pool)
- .await;
+pub async fn update(conn: &Connection, id: i64, p: payment::Update) -> bool {
+ let query = r#"
+ UPDATE payments
+ SET
+ name = :name,
+ cost = :cost,
+ user_id = :user_id,
+ category_id = :category_id,
+ date = :date,
+ updated_at = datetime()
+ WHERE id = :id
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ Ok(conn.execute(
+ query,
+ named_params![
+ ":name": p.name,
+ ":cost": p.cost,
+ ":user_id": p.user_id,
+ ":category_id": p.category_id,
+ ":date": p.date,
+ ":id": id
+ ],
+ )?)
+ })
+ .await;
+
match res {
Ok(_) => true,
@@ -440,20 +380,12 @@ WHERE
}
}
-pub async fn delete(pool: &SqlitePool, id: i64) -> bool {
- let res = sqlx::query(
- r#"
-UPDATE
- payments
-SET
- deleted_at = datetime()
-WHERE
- id = ?
- "#,
- )
- .bind(id)
- .execute(pool)
- .await;
+pub async fn delete(conn: &Connection, id: i64) -> bool {
+ let query = r#"UPDATE payments SET deleted_at = datetime() WHERE id = :id"#;
+
+ let res = conn
+ .call(move |conn| Ok(conn.execute(query, named_params![":id": id])?))
+ .await;
match res {
Ok(_) => true,
@@ -465,33 +397,34 @@ WHERE
}
pub async fn search_category(
- pool: &SqlitePool,
+ conn: &Connection,
payment_name: String,
) -> Option<i64> {
- let query = format!(
- r#"
-SELECT
- category_id
-FROM
- payments
-WHERE
- deleted_at IS NULL
- AND {} LIKE ?
-ORDER BY
- updated_at, created_at
- "#,
- utils::format_key_for_search("name")
- );
-
- let res = sqlx::query(&query)
- .bind(text::format_search(&payment_name))
- .map(|row: SqliteRow| row.get("category_id"))
- .fetch_one(pool)
+ let query = format!(r#"
+ SELECT category_id
+ FROM payments
+ WHERE
+ deleted_at IS NULL
+ AND {} LIKE :name
+ ORDER BY updated_at, created_at
+ "#, utils::format_key_for_search("name"));
+
+ let payment_name_closure = payment_name.clone();
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(&query)?;
+ let mut iter = stmt.query_map(
+ named_params![":name": text::format_search(&payment_name_closure)],
+ |row| row.get(0)
+ )?;
+ utils::one::<i64, _>(&mut iter)
+ })
.await;
match res {
Ok(category) => Some(category),
- Err(Error::RowNotFound) => None,
+ Err(tokio_rusqlite::Error::Rusqlite(rusqlite::Error::QueryReturnedNoRows)) => None,
Err(err) => {
log::error!(
"Error looking for the category of {}: {:?}",
@@ -503,24 +436,25 @@ ORDER BY
}
}
-pub async fn is_category_used(pool: &SqlitePool, category_id: i64) -> bool {
+pub async fn is_category_used(conn: &Connection, category_id: i64) -> bool {
let query = r#"
-SELECT
- 1
-FROM
- payments
-WHERE
- category_id = ?
- AND deleted_at IS NULL
-LIMIT
- 1
+ SELECT 1
+ FROM payments
+ WHERE
+ category_id = :category_id
+ AND deleted_at IS NULL
+ LIMIT 1
"#;
- let res = sqlx::query(query).bind(category_id).fetch_one(pool).await;
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(&query)?;
+ Ok(stmt.exists(named_params![":category_id": category_id])?)
+ })
+ .await;
match res {
- Ok(_) => true,
- Err(Error::RowNotFound) => false,
+ Ok(is_used) => is_used,
Err(err) => {
log::error!(
"Error looking if category {} is used: {:?}",
@@ -532,31 +466,33 @@ LIMIT
}
}
-pub async fn repartition(pool: &SqlitePool) -> HashMap<i64, i64> {
+pub async fn repartition(conn: &Connection) -> HashMap<i64, i64> {
let query = r#"
-SELECT
- users.id AS user_id,
- COALESCE(payments.sum, 0) AS sum
-FROM
- users
-LEFT OUTER JOIN (
- SELECT
- user_id,
- SUM(cost) AS sum
- FROM
- payments
- WHERE
- deleted_at IS NULL
- AND frequency = 'Punctual'
- GROUP BY
- user_id
-) payments
-ON
- users.id = payments.user_id"#;
-
- let res = sqlx::query(query)
- .map(|row: SqliteRow| (row.get("user_id"), row.get("sum")))
- .fetch_all(pool)
+ SELECT
+ users.id,
+ COALESCE(payments.sum, 0)
+ FROM users
+ LEFT OUTER JOIN (
+ SELECT
+ user_id,
+ SUM(cost) AS sum
+ FROM payments
+ WHERE
+ deleted_at IS NULL
+ AND frequency = 'Punctual'
+ GROUP BY user_id
+ ) payments
+ ON users.id = payments.user_id
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+ let payments = stmt
+ .query_map([], |row| Ok((row.get(0)?, row.get(1)?)))?
+ .collect::<Result<Vec<(i64, i64)>, _>>()?;
+ Ok(payments)
+ })
.await;
match res {
@@ -568,25 +504,25 @@ ON
}
}
-pub async fn create_monthly_payments(pool: &SqlitePool) {
+pub async fn create_monthly_payments(conn: &Connection) {
let query = r#"
-INSERT INTO
- payments(name, cost, user_id, category_id, date, frequency)
-SELECT
- name,
- cost,
- user_id,
- category_id,
- date() AS date,
- 'Punctual' AS frequency
-FROM
- payments
-WHERE
- frequency = 'Monthly'
- AND deleted_at IS NULL
+ INSERT INTO payments(name, cost, user_id, category_id, date, frequency)
+ SELECT
+ name,
+ cost,
+ user_id,
+ category_id,
+ date() AS date,
+ 'Punctual' AS frequency
+ FROM payments
+ WHERE
+ frequency = 'Monthly'
+ AND deleted_at IS NULL
"#;
- let res = sqlx::query(query).execute(pool).await;
+ let res = conn
+ .call(move |conn| Ok(conn.execute(query, [])?))
+ .await;
match res {
Ok(_) => (),
@@ -594,50 +530,51 @@ WHERE
}
}
-pub async fn last_week(pool: &SqlitePool) -> Vec<Report> {
+pub async fn last_week(conn: &Connection) -> Vec<Report> {
let query = r#"
-SELECT
- strftime('%d/%m/%Y', payments.date) AS date,
- (payments.name || ' (' || users.name || ')') AS name,
- payments.cost AS amount,
- (CASE
- WHEN
- payments.deleted_at IS NOT NULL
- THEN
- 'Deleted'
- WHEN
- payments.updated_at IS NOT NULL
- AND payments.created_at < date('now', 'weekday 0', '-13 days')
- THEN
- 'Updated'
- ELSE
- 'Created'
- END) AS action
-FROM
- payments
-INNER JOIN
- users
-ON
- payments.user_id = users.id
-WHERE
- payments.frequency = 'Punctual'
- AND (
- (
- payments.created_at >= date('now', 'weekday 0', '-13 days')
- AND payments.created_at < date('now', 'weekday 0', '-6 days')
- ) OR (
- payments.updated_at >= date('now', 'weekday 0', '-13 days')
- AND payments.updated_at < date('now', 'weekday 0', '-6 days')
- ) OR (
- payments.deleted_at >= date('now', 'weekday 0', '-13 days')
- AND payments.deleted_at < date('now', 'weekday 0', '-6 days')
- )
- )
-ORDER BY
- payments.date
+ SELECT
+ strftime('%d/%m/%Y', payments.date) AS date,
+ (payments.name || ' (' || users.name || ')') AS name,
+ payments.cost AS amount,
+ (CASE
+ WHEN payments.deleted_at IS NOT NULL
+ THEN 'Deleted'
+ WHEN
+ payments.updated_at IS NOT NULL
+ AND payments.created_at < date('now', 'weekday 0', '-13 days')
+ THEN 'Updated'
+ ELSE 'Created'
+ END) AS action
+ FROM payments
+ INNER JOIN users
+ ON payments.user_id = users.id
+ WHERE
+ payments.frequency = 'Punctual'
+ AND (
+ (
+ payments.created_at >= date('now', 'weekday 0', '-13 days')
+ AND payments.created_at < date('now', 'weekday 0', '-6 days')
+ ) OR (
+ payments.updated_at >= date('now', 'weekday 0', '-13 days')
+ AND payments.updated_at < date('now', 'weekday 0', '-6 days')
+ ) OR (
+ payments.deleted_at >= date('now', 'weekday 0', '-13 days')
+ AND payments.deleted_at < date('now', 'weekday 0', '-6 days')
+ )
+ )
+ ORDER BY payments.date
"#;
- let res = sqlx::query_as::<_, Report>(query).fetch_all(pool).await;
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+ let xs = stmt
+ .query_map([], utils::row_to_report)?
+ .collect::<Result<Vec<Report>, _>>()?;
+
+ Ok(xs)
+ })
+ .await;
match res {
Ok(payments) => payments,
generated by cgit v1.2.3 (git 2.25.1) at 2025-02-02 00:51:19 +0000