Rewrite in Rust

1 files changed, 525 insertions, 0 deletions

diff --git a/src/db/payments.rs b/src/db/payments.rs
new file mode 100644
index 0000000..0197375
--- /dev/null
+++ b/src/db/payments.rs
@@ -0,0 +1,525 @@
+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 crate::db::utils;
+use crate::model::frequency::Frequency;
+use crate::model::payment;
+use crate::model::report::Report;
+use crate::queries;
+use crate::utils::text;
+
+#[derive(FromRow)]
+pub struct Count {
+    pub count: i64,
+    pub total_cost: i64,
+}
+
+pub async fn count(
+    pool: &SqlitePool,
+    payment_query: &queries::Payments,
+) -> Count {
+    let search = payment_query.search.clone().unwrap_or("".to_string());
+
+    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 = ?
+    {}
+        "#,
+        search_query(search.clone())
+    );
+
+    let res = bind_search(
+        sqlx::query_as::<_, Count>(&query)
+            .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)),
+        search,
+    )
+    .fetch_one(pool)
+    .await;
+
+    match res {
+        Ok(count) => count,
+        Err(err) => {
+            error!("Error counting payments: {:?}", err);
+            Count {
+                count: 0,
+                total_cost: 0,
+            }
+        }
+    }
+}
+
+pub async fn list_for_table(
+    pool: &SqlitePool,
+    payment_query: &queries::Payments,
+    per_page: i64,
+) -> Vec<payment::Table> {
+    let offset = (payment_query.page.unwrap_or(1) - 1) * per_page;
+    let search = payment_query.search.clone().unwrap_or("".to_string());
+
+    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 ?
+        "#,
+        search_query(search.clone())
+    );
+
+    let res = bind_search(
+        sqlx::query_as::<_, payment::Table>(&query)
+            .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)),
+        search,
+    )
+    .bind(per_page)
+    .bind(offset)
+    .fetch_all(pool)
+    .await;
+
+    match res {
+        Ok(payments) => payments,
+        Err(err) => {
+            error!("Error listing payments: {:?}", err);
+            vec![]
+        }
+    }
+}
+
+fn search_query(search: String) -> String {
+    let payments_name = utils::format_key_for_search("payments.name");
+    let users_name = utils::format_key_for_search("users.name");
+    let categories_name = utils::format_key_for_search("categories.name");
+
+    search
+        .split_ascii_whitespace()
+        .map(|_| {
+            format!(
+                r#"
+AND (
+    {} LIKE ?
+    OR payments.cost LIKE ?
+    OR {} LIKE ?
+    OR {} LIKE ?
+    OR strftime('%d/%m/%Y', date) LIKE ?
+)
+        "#,
+                payments_name, users_name, categories_name
+            )
+        })
+        .collect::<Vec<String>>()
+        .join(" ")
+}
+
+fn bind_search<'a, Row: FromRow<'a, SqliteRow>>(
+    query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
+    search: String,
+) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
+    search.split_ascii_whitespace().fold(query, |q, word| {
+        let s = format!("%{}%", text::format_search(&word.to_string()));
+        q.bind(s.clone())
+            .bind(s.clone())
+            .bind(s.clone())
+            .bind(s.clone())
+            .bind(s.clone())
+    })
+}
+
+pub async fn list_for_stats(pool: &SqlitePool) -> 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;
+    "#;
+
+    let result = sqlx::query_as::<_, payment::Stat>(query)
+        .fetch_all(pool)
+        .await;
+
+    match result {
+        Ok(payments) => payments,
+        Err(err) => {
+            error!("Error listing payments for statistics: {:?}", err);
+            vec![]
+        }
+    }
+}
+
+pub async fn get_row(pool: &SqlitePool, 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 = ?
+    "#;
+
+    let res = sqlx::query(query)
+        .bind(frequency)
+        .bind(id)
+        .map(|row: SqliteRow| row.get("row"))
+        .fetch_one(pool)
+        .await;
+
+    match res {
+        Ok(count) => count,
+        Err(err) => {
+            error!("Error getting payment row: {:?}", err);
+            1
+        }
+    }
+}
+
+pub async fn get_for_form(pool: &SqlitePool, 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
+    "#;
+
+    let res = sqlx::query_as::<_, payment::Form>(query)
+        .bind(id)
+        .fetch_one(pool)
+        .await;
+
+    match res {
+        Ok(p) => Some(p),
+        Err(err) => {
+            error!("Error looking for payment {}: {:?}", id, err);
+            None
+        }
+    }
+}
+
+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, created_at)
+VALUES
+    (?, ?, ?, ?, ?, ?, datetime())
+    "#,
+    )
+    .bind(p.name.clone())
+    .bind(p.cost)
+    .bind(p.user_id)
+    .bind(p.category_id)
+    .bind(p.date)
+    .bind(p.frequency)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(x) => Some(x.last_insert_rowid()),
+        Err(err) => {
+            error!("Error creating payment: {:?}", err);
+            None
+        }
+    }
+}
+
+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;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            error!("Error updating payment {}: {:?}", id, err);
+            false
+        }
+    }
+}
+
+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;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            error!("Error deleting payment {}: {:?}", id, err);
+            false
+        }
+    }
+}
+
+pub async fn search_category(
+    pool: &SqlitePool,
+    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(&format!("%{}%", payment_name)))
+        .map(|row: SqliteRow| row.get("category_id"))
+        .fetch_one(pool)
+        .await;
+
+    match res {
+        Ok(category) => Some(category),
+        Err(Error::RowNotFound) => None,
+        Err(err) => {
+            error!(
+                "Error looking for the category of {}: {:?}",
+                payment_name, err
+            );
+            None
+        }
+    }
+}
+
+pub async fn is_category_used(pool: &SqlitePool, category_id: i64) -> bool {
+    let query = r#"
+SELECT
+    1
+FROM
+    payments
+WHERE
+    category_id = ?
+    AND deleted_at IS NULL
+LIMIT
+    1
+    "#;
+
+    let res = sqlx::query(&query).bind(category_id).fetch_one(pool).await;
+
+    match res {
+        Ok(_) => true,
+        Err(Error::RowNotFound) => false,
+        Err(err) => {
+            error!(
+                "Error looking if category {} is used: {:?}",
+                category_id, err
+            );
+            false
+        }
+    }
+}
+
+pub async fn repartition(pool: &SqlitePool) -> 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)
+        .await;
+
+    match res {
+        Ok(costs) => HashMap::from_iter(costs),
+        Err(err) => {
+            error!("Error getting payments repartition: {:?}", err);
+            HashMap::new()
+        }
+    }
+}
+
+pub async fn create_monthly_payments(pool: &SqlitePool) -> () {
+    let query = r#"
+INSERT INTO
+    payments(name, cost, user_id, category_id, date, frequency, created_at)
+SELECT
+    name,
+    cost,
+    user_id,
+    category_id,
+    date() AS date,
+    'Punctual' AS frequency,
+    datetime() AS created_at
+FROM
+    payments
+WHERE
+    frequency = 'Monthly'
+    AND deleted_at IS NULL
+    "#;
+
+    let res = sqlx::query(query).execute(pool).await;
+
+    match res {
+        Ok(_) => (),
+        Err(err) => {
+            error!("Error creating monthly payments: {:?}", err);
+            ()
+        }
+    }
+}
+
+pub async fn last_week(pool: &SqlitePool) -> 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
+    "#;
+
+    let res = sqlx::query_as::<_, Report>(query).fetch_all(pool).await;
+
+    match res {
+        Ok(payments) => payments,
+        Err(err) => {
+            error!("Error listing payments for report: {:?}", err);
+            vec![]
+        }
+    }
+}