aboutsummaryrefslogtreecommitdiff
path: root/src/db/incomes.rs
diff options
context:
space:
mode:
Diffstat (limited to 'src/db/incomes.rs')
-rw-r--r--src/db/incomes.rs656
1 files changed, 356 insertions, 300 deletions
diff --git a/src/db/incomes.rs b/src/db/incomes.rs
index 97cb2b7..90282c0 100644
--- a/src/db/incomes.rs
+++ b/src/db/incomes.rs
@@ -1,26 +1,51 @@
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 tokio_rusqlite::{named_params, Connection, Row};
+use crate::db::utils;
use crate::model::income::{Create, Form, Stat, Table, Update};
use crate::model::report::Report;
-pub async fn count(pool: &SqlitePool) -> i64 {
+fn row_to_table(row: &Row) -> Result<Table, rusqlite::Error> {
+ Ok(Table {
+ id: row.get(0)?,
+ date: row.get(1)?,
+ user: row.get(2)?,
+ amount: row.get(3)?,
+ })
+}
+
+fn row_to_form(row: &Row) -> Result<Form, rusqlite::Error> {
+ Ok(Form {
+ id: row.get(0)?,
+ amount: row.get(1)?,
+ user_id: row.get(2)?,
+ month: row.get(3)?,
+ year: row.get(4)?,
+ })
+}
+
+fn row_to_stat(row: &Row) -> Result<Stat, rusqlite::Error> {
+ Ok(Stat {
+ date: row.get(0)?,
+ amount: row.get(1)?,
+ })
+}
+
+pub async fn count(conn: &Connection) -> 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)
+ SELECT COUNT(*)
+ FROM incomes
+ WHERE incomes.deleted_at IS NULL
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+ let mut iter = stmt.query_map([], |row| row.get(0))?;
+ utils::one::<i64, _>(&mut iter)
+ })
.await;
match res {
@@ -32,32 +57,35 @@ WHERE
}
}
-pub async fn list(pool: &SqlitePool, page: i64, per_page: i64) -> Vec<Table> {
+pub async fn list(conn: &Connection, page: i64, per_page: i64) -> Vec<Table> {
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 ?
+ 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 :limit
+ OFFSET :offset
"#;
- let res = sqlx::query_as::<_, Table>(query)
- .bind(per_page)
- .bind((page - 1) * per_page)
- .fetch_all(pool)
- .await;
+ let res = conn.call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+
+ let incomes = stmt
+ .query_map(
+ named_params![":limit": per_page, ":offset": (page - 1) * per_page],
+ row_to_table
+ )?
+ .collect::<Result<Vec<Table>, _>>()?;
+
+ Ok(incomes)
+ })
+ .await;
match res {
Ok(incomes) => incomes,
@@ -68,31 +96,30 @@ OFFSET ?
}
}
-pub async fn get_row(pool: &SqlitePool, id: i64) -> i64 {
+pub async fn get_row(conn: &Connection, 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 = ?
+ SELECT row
+ FROM (
+ SELECT
+ ROW_NUMBER () OVER (ORDER BY date DESC) AS row,
+ id
+ FROM incomes
+ WHERE deleted_at IS NULL
+ )
+ WHERE id = :id
"#;
- let res = sqlx::query(query)
- .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], |row| row.get(0))?;
+ utils::one::<i64, _>(&mut iter)
+ })
.await;
match res {
- Ok(count) => count,
+ Ok(row) => row,
Err(err) => {
log::error!("Error getting income row: {:?}", err);
1
@@ -100,28 +127,31 @@ WHERE
}
}
-pub async fn get(pool: &SqlitePool, id: i64) -> Option<Form> {
+pub async fn get(conn: &Connection, id: i64) -> Option<Form> {
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
+ 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 = :id
+ AND deleted_at IS NULL
"#;
- let res = sqlx::query_as::<_, 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 {
- Ok(p) => Some(p),
+ Ok(income) => Some(income),
Err(err) => {
log::error!("Error looking for income {}: {:?}", id, err);
None
@@ -129,23 +159,28 @@ WHERE
}
}
-pub async fn create(pool: &SqlitePool, i: &Create) -> Option<i64> {
- let res = sqlx::query(
- r#"
-INSERT INTO
- incomes(user_id, date, amount)
-VALUES
- (?, ?, ?)
- "#,
- )
- .bind(i.user_id)
- .bind(i.date)
- .bind(i.amount)
- .execute(pool)
- .await;
+pub async fn create(conn: &Connection, i: Create) -> Option<i64> {
+ let query = r#"
+ INSERT INTO incomes(user_id, date, amount)
+ VALUES (:user_id, :date, :amount)
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ conn.execute(
+ query,
+ named_params![
+ ":user_id": i.user_id,
+ ":date": i.date,
+ ":amount": i.amount
+ ],
+ )?;
+ Ok(conn.last_insert_rowid())
+ })
+ .await;
match res {
- Ok(x) => Some(x.last_insert_rowid()),
+ Ok(income_id) => Some(income_id),
Err(err) => {
log::error!("Error creating income: {:?}", err);
None
@@ -154,31 +189,36 @@ VALUES
}
pub async fn defined_at(
- pool: &SqlitePool,
+ conn: &Connection,
user_id: i64,
date: NaiveDate,
) -> Vec<i64> {
let query = r#"
-SELECT
- id
-FROM
- incomes
-WHERE
- user_id = ?
- AND date = ?
- AND deleted_at IS NULL
+ SELECT id
+ FROM incomes
+ WHERE
+ user_id = :user_id
+ AND date = :date
+ AND deleted_at IS NULL
"#;
- let res = sqlx::query(query)
- .bind(user_id)
- .bind(date)
- .map(|row: SqliteRow| row.get("id"))
- .fetch_all(pool)
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+
+ let incomes = stmt
+ .query_map(
+ named_params![":user_id": user_id, ":date": date],
+ |row| row.get(0),
+ )?
+ .collect::<Result<Vec<i64>, _>>()?;
+
+ Ok(incomes)
+ })
.await;
match res {
Ok(ids) => ids,
- Err(Error::RowNotFound) => vec![],
Err(err) => {
log::error!("Error looking if income is defined: {:?}", err);
vec![]
@@ -186,26 +226,30 @@ WHERE
}
}
-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(i.date)
- .bind(i.amount)
- .bind(id)
- .execute(pool)
- .await;
+pub async fn update(conn: &Connection, id: i64, i: Update) -> bool {
+ let query = r#"
+ UPDATE incomes
+ SET
+ user_id = :user_id,
+ date = :date,
+ amount = :amount,
+ updated_at = datetime()
+ WHERE id = :id
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ Ok(conn.execute(
+ query,
+ named_params![
+ ":user_id": i.user_id,
+ ":date": i.date,
+ ":amount": i.amount,
+ ":id": id
+ ],
+ )?)
+ })
+ .await;
match res {
Ok(_) => true,
@@ -216,20 +260,12 @@ WHERE
}
}
-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;
+pub async fn delete(conn: &Connection, id: i64) -> bool {
+ let query = r#"UPDATE incomes 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,
@@ -240,37 +276,34 @@ WHERE
}
}
-pub async fn defined_for_all(pool: &SqlitePool) -> Option<NaiveDate> {
- 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;
+pub async fn defined_for_all(conn: &Connection) -> Option<NaiveDate> {
+ let 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;
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+ let mut iter = stmt.query_map([], |row| row.get(0))?;
+ utils::one::<NaiveDate, _>(&mut iter)
+ })
+ .await;
match res {
- Ok(d) => d,
+ Ok(d) => Some(d),
Err(err) => {
log::error!("Error looking for incomes defined for all: {:?}", err);
None
@@ -279,12 +312,17 @@ ON
}
pub async fn cumulative(
- pool: &SqlitePool,
+ conn: &Connection,
from: NaiveDate,
) -> HashMap<i64, i64> {
- let res = sqlx::query(&cumulative_query(from))
- .map(|row: SqliteRow| (row.get("user_id"), row.get("income")))
- .fetch_all(pool)
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(&cumulative_query(from))?;
+ let incomes = stmt
+ .query_map([], |row| Ok((row.get(0)?, row.get(1)?)))?
+ .collect::<Result<Vec<(i64, i64)>, _>>()?;
+ Ok(incomes)
+ })
.await;
match res {
@@ -306,34 +344,34 @@ pub async fn cumulative(
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
+ users.id AS user_id,
+ COALESCE(incomes.income, 0) AS income
FROM
- ({}) AS I1
- INNER JOIN
- ({}) AS I2
+ 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
- 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
+ users.id = incomes.user_id
"#,
bounded_query(">".to_string(), from.format("%Y-%m-%d").to_string()),
bounded_query("<".to_string(), "date()".to_string())
@@ -347,33 +385,33 @@ ON
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
+ 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
)
@@ -383,54 +421,63 @@ WHERE
///
/// 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<Stat> {
+pub async fn total_each_month(conn: &Connection) -> Vec<Stat> {
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 (
+ WITH RECURSIVE dates(date) AS (
+ VALUES((
SELECT
- amount
+ strftime('%Y-%m-01', MIN(date))
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
+ deleted_at IS NULL
+ ))
+ UNION ALL
+ SELECT
+ date(date, '+1 month')
FROM
- users
+ dates
+ WHERE
+ date < date(date(), '-1 month')
)
- ) AS amount
-FROM
- dates;
+ 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;
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+ let stats = stmt
+ .query_map([], row_to_stat)?
+ .collect::<Result<Vec<Stat>, _>>()?;
+
+ Ok(stats)
+ })
+ .await;
match res {
Ok(xs) => xs,
@@ -441,50 +488,59 @@ FROM
}
}
-pub async fn last_week(pool: &SqlitePool) -> Vec<Report> {
+pub async fn last_week(conn: &Connection) -> Vec<Report> {
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
+ 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;
+ 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,
+ Ok(xs) => xs,
Err(err) => {
log::error!("Error listing payments for report: {:?}", err);
vec![]
generated by cgit v1.2.3 (git 2.25.1) at 2025-02-02 00:40:03 +0000