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) VALUES (?, ?, ?) "#, ) .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![] } } }