From 11052951b74b9ad4b6a9412ae490086235f9154b Mon Sep 17 00:00:00 2001 From: Joris Date: Sun, 3 Jan 2021 13:40:40 +0100 Subject: Rewrite in Rust --- src/db/incomes.rs | 494 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 494 insertions(+) create mode 100644 src/db/incomes.rs (limited to 'src/db/incomes.rs') diff --git a/src/db/incomes.rs b/src/db/incomes.rs new file mode 100644 index 0000000..cbbfce7 --- /dev/null +++ b/src/db/incomes.rs @@ -0,0 +1,494 @@ +use chrono::NaiveDate; +use sqlx::error::Error; +use sqlx::sqlite::{SqlitePool, SqliteRow}; +use sqlx_core::row::Row; +use std::collections::HashMap; +use std::iter::FromIterator; + +use crate::model::income::{Create, Form, Stat, Table, Update}; +use crate::model::report::Report; + +pub async fn count(pool: &SqlitePool) -> i64 { + let query = r#" +SELECT + COUNT(*) AS count +FROM + incomes +WHERE + incomes.deleted_at IS NULL + "#; + + let res = sqlx::query(&query) + .map(|row: SqliteRow| row.get("count")) + .fetch_one(pool) + .await; + + match res { + Ok(count) => count, + Err(err) => { + error!("Error counting incomes: {:?}", err); + 0 + } + } +} + +pub async fn list(pool: &SqlitePool, page: i64, per_page: i64) -> Vec { + let query = r#" +SELECT + incomes.id, + users.name AS user, + strftime('%m/%Y', incomes.date) AS date, + incomes.amount +FROM + incomes +INNER JOIN + users +ON + incomes.user_id = users.id +WHERE + incomes.deleted_at IS NULL +ORDER BY + incomes.date DESC +LIMIT ? +OFFSET ? + "#; + + let res = sqlx::query_as::<_, Table>(query) + .bind(per_page) + .bind((page - 1) * per_page) + .fetch_all(pool) + .await; + + match res { + Ok(incomes) => incomes, + Err(err) => { + error!("Error listing incomes: {:?}", err); + vec![] + } + } +} + +pub async fn get_row(pool: &SqlitePool, id: i64) -> i64 { + let query = r#" +SELECT + row +FROM ( + SELECT + ROW_NUMBER () OVER (ORDER BY date DESC) AS row, + id + FROM + incomes + WHERE + deleted_at IS NULL +) +WHERE + id = ? + "#; + + let res = sqlx::query(query) + .bind(id) + .map(|row: SqliteRow| row.get("row")) + .fetch_one(pool) + .await; + + match res { + Ok(count) => count, + Err(err) => { + error!("Error getting income row: {:?}", err); + 1 + } + } +} + +pub async fn get(pool: &SqlitePool, id: i64) -> Option { + let query = r#" +SELECT + id, + amount, + user_id, + CAST(strftime('%m', date) AS INTEGER) as month, + CAST(strftime('%Y', date) AS INTEGER) as year +FROM + incomes +WHERE + id = ? + AND deleted_at IS NULL + "#; + + let res = sqlx::query_as::<_, Form>(query) + .bind(id) + .fetch_one(pool) + .await; + + match res { + Ok(p) => Some(p), + Err(err) => { + error!("Error looking for income {}: {:?}", id, err); + None + } + } +} + +pub async fn create(pool: &SqlitePool, i: &Create) -> Option { + let res = sqlx::query( + r#" +INSERT INTO + incomes(user_id, date, amount, created_at) +VALUES + (?, ?, ?, datetime()) + "#, + ) + .bind(i.user_id) + .bind(NaiveDate::from_ymd(i.year, i.month, 1)) + .bind(i.amount) + .execute(pool) + .await; + + match res { + Ok(x) => Some(x.last_insert_rowid()), + Err(err) => { + error!("Error creating income: {:?}", err); + None + } + } +} + +pub async fn defined_at( + pool: &SqlitePool, + user_id: i64, + month: u32, + year: i32, +) -> Vec { + let query = r#" +SELECT + id +FROM + incomes +WHERE + user_id = ? + AND date = ? + AND deleted_at IS NULL + "#; + + let res = sqlx::query(&query) + .bind(user_id) + .bind(NaiveDate::from_ymd(year, month, 1)) + .map(|row: SqliteRow| row.get("id")) + .fetch_all(pool) + .await; + + match res { + Ok(ids) => ids, + Err(Error::RowNotFound) => vec![], + Err(err) => { + error!("Error looking if income is defined: {:?}", err); + vec![] + } + } +} + +pub async fn update(pool: &SqlitePool, id: i64, i: &Update) -> bool { + let res = sqlx::query( + r#" +UPDATE + incomes +SET + user_id = ?, + date = ?, + amount = ?, + updated_at = datetime() +WHERE + id = ? + "#, + ) + .bind(i.user_id) + .bind(NaiveDate::from_ymd(i.year, i.month, 1)) + .bind(i.amount) + .bind(id) + .execute(pool) + .await; + + match res { + Ok(_) => true, + Err(err) => { + error!("Error updating income {}: {:?}", id, err); + false + } + } +} + +pub async fn delete(pool: &SqlitePool, id: i64) -> bool { + let res = sqlx::query( + r#" +UPDATE + incomes +SET + deleted_at = datetime() +WHERE + id = ? + "#, + ) + .bind(id) + .execute(pool) + .await; + + match res { + Ok(_) => true, + Err(err) => { + error!("Error deleting income {}: {:?}", id, err); + false + } + } +} + +pub async fn defined_for_all(pool: &SqlitePool) -> Option { + let res = sqlx::query( + r#" +SELECT + (CASE COUNT(users.id) == COUNT(min_income.date) + WHEN 1 THEN MIN(min_income.date) + ELSE NULL + END) AS date +FROM + users +LEFT OUTER JOIN ( + SELECT + user_id, + MIN(date) AS date + FROM + incomes + WHERE + deleted_at IS NULL + GROUP BY + user_id +) min_income +ON + users.id = min_income.user_id; + "#, + ) + .map(|row: SqliteRow| row.get("date")) + .fetch_one(pool) + .await; + + match res { + Ok(d) => d, + Err(err) => { + error!("Error looking for incomes defined for all: {:?}", err); + None + } + } +} + +pub async fn cumulative( + pool: &SqlitePool, + from: NaiveDate, +) -> HashMap { + let res = sqlx::query(&cumulative_query(from)) + .map(|row: SqliteRow| (row.get("user_id"), row.get("income"))) + .fetch_all(pool) + .await; + + match res { + Ok(incomes) => HashMap::from_iter(incomes), + Err(err) => { + error!("Error computing cumulative income: {:?}", err); + HashMap::new() + } + } +} + +/// Select cumulative income of users from the given date and until now. +/// +/// Associate each month income to its start and end bounds, +/// then compute the total income of each period, +/// sum it to get the final result. +/// +/// Considering each month to be 365 / 12 days long. +fn cumulative_query(from: NaiveDate) -> String { + format!( + r#" +SELECT + users.id AS user_id, + COALESCE(incomes.income, 0) AS income +FROM + users +LEFT OUTER JOIN ( + SELECT + user_id, + CAST(ROUND(SUM(count)) AS INTEGER) AS income + FROM ( + SELECT + I1.user_id, + ((JULIANDAY(MIN(I2.date)) - JULIANDAY(I1.date)) * I1.amount * 12 / 365) AS count + FROM + ({}) AS I1 + INNER JOIN + ({}) AS I2 + ON + I2.date > I1.date + AND I2.user_id == I1.user_id + GROUP BY + I1.date, I1.user_id + ) + GROUP BY + user_id +) incomes +ON + users.id = incomes.user_id + "#, + bounded_query(">".to_string(), from.format("%Y-%m-%d").to_string()), + bounded_query("<".to_string(), "date()".to_string()) + ) +} + +/// Select bounded incomes to the operator and date. +/// +/// It filters incomes according to the operator and date, +/// and adds the income at this date. +fn bounded_query(op: String, date: String) -> String { + format!( + r#" +SELECT + user_id, + date, + amount +FROM ( + SELECT + user_id, + {} AS date, + amount, + MAX(date) AS max_date + FROM + incomes + WHERE + date <= {} + AND deleted_at IS NULL + GROUP BY + user_id +) UNION +SELECT + user_id, + date, + amount +FROM + incomes +WHERE + date {} {} + AND deleted_at IS NULL + "#, + date, date, op, date + ) +} + +/// Select total income each month. +/// +/// For each month, from the first defined income and until now, +/// compute the total income of the users. +pub async fn total_each_month(pool: &SqlitePool) -> Vec { + let query = r#" +WITH RECURSIVE dates(date) AS ( + VALUES(( + SELECT + strftime('%Y-%m-01', MIN(date)) + FROM + incomes + WHERE + deleted_at IS NULL + )) + UNION ALL + SELECT + date(date, '+1 month') + FROM + dates + WHERE + date < date(date(), '-1 month') +) +SELECT + strftime('%Y-%m-01', dates.date) AS date, + ( + SELECT + SUM(amount) AS amount + FROM ( + SELECT ( + SELECT + amount + FROM + incomes + WHERE + user_id = users.id + AND date < date(dates.date, '+1 month') + AND deleted_at IS NULL + ORDER BY + date DESC + LIMIT + 1 + ) AS amount + FROM + users + ) + ) AS amount +FROM + dates; + "#; + + let res = sqlx::query_as::<_, Stat>(query).fetch_all(pool).await; + + match res { + Ok(xs) => xs, + Err(err) => { + error!("Error listing incomes for statistics: {:?}", err); + vec![] + } + } +} + +pub async fn last_week(pool: &SqlitePool) -> Vec { + let query = r#" +SELECT + strftime('%m/%Y', incomes.date) AS date, + users.name AS name, + incomes.amount AS amount, + (CASE + WHEN + incomes.deleted_at IS NOT NULL + THEN + 'Deleted' + WHEN + incomes.updated_at IS NOT NULL + AND incomes.created_at < date('now', 'weekday 0', '-13 days') + THEN + 'Updated' + ELSE + 'Created' + END) AS action +FROM + incomes +INNER JOIN + users +ON + incomes.user_id = users.id +WHERE + ( + incomes.created_at >= date('now', 'weekday 0', '-13 days') + AND incomes.created_at < date('now', 'weekday 0', '-6 days') + ) OR ( + incomes.updated_at >= date('now', 'weekday 0', '-13 days') + AND incomes.updated_at < date('now', 'weekday 0', '-6 days') + ) OR ( + incomes.deleted_at >= date('now', 'weekday 0', '-13 days') + AND incomes.deleted_at < date('now', 'weekday 0', '-6 days') + ) +ORDER BY + incomes.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![] + } + } +} -- cgit v1.2.3