aboutsummaryrefslogtreecommitdiff
path: root/src/db
diff options
context:
space:
mode:
Diffstat (limited to 'src/db')
-rw-r--r--src/db/balancing.rs260
-rw-r--r--src/db/migrations/07-create-balancing-table.sql9
-rw-r--r--src/db/mod.rs2
3 files changed, 271 insertions, 0 deletions
diff --git a/src/db/balancing.rs b/src/db/balancing.rs
new file mode 100644
index 0000000..1641b97
--- /dev/null
+++ b/src/db/balancing.rs
@@ -0,0 +1,260 @@
+use tokio_rusqlite::{Connection, Row, named_params};
+
+use crate::db::utils;
+use crate::model::balancing::{Balancing, Create, Update, TableRow};
+
+fn row_to_balancing(row: &Row) -> Result<Balancing, rusqlite::Error> {
+ Ok(Balancing {
+ id: row.get(0)?,
+ source: row.get(1)?,
+ destination: row.get(2)?,
+ amount: row.get(3)?,
+ })
+}
+
+fn row_to_table_row(row: &Row) -> Result<TableRow, rusqlite::Error> {
+ Ok(TableRow {
+ id: row.get(0)?,
+ source: row.get(1)?,
+ destination: row.get(2)?,
+ amount: row.get(3)?,
+ })
+}
+
+pub async fn count(conn: &Connection) -> i64 {
+ let query = r#"
+ SELECT COUNT(*)
+ FROM balancing
+ WHERE balancing.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 {
+ Ok(count) => count,
+ Err(err) => {
+ log::error!("Error counting balancing: {:?}", err);
+ 0
+ }
+ }
+}
+
+pub async fn list_for_table(conn: &Connection, page: i64, per_page: i64) -> Vec<TableRow> {
+ let query = r#"
+ SELECT
+ balancing.id,
+ users_src.name,
+ users_dest.name,
+ balancing.amount
+ FROM balancing
+ INNER JOIN users AS users_src ON users_src.id = balancing.source
+ INNER JOIN users AS users_dest ON users_dest.id = balancing.destination
+ WHERE balancing.deleted_at IS NULL
+ ORDER BY balancing.created_at DESC
+ LIMIT :limit
+ OFFSET :offset
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+
+ stmt.query_map(
+ named_params![":limit": per_page, ":offset": (page - 1) * per_page],
+ row_to_table_row
+ )?
+ .collect::<Result<Vec<TableRow>, _>>()
+ })
+ .await;
+
+ match res {
+ Ok(xs) => xs,
+ Err(err) => {
+ log::error!("Error listing balancing: {:?}", err);
+ vec![]
+ }
+ }
+}
+pub async fn list(conn: &Connection) -> Vec<Balancing> {
+ let query = r#"
+ SELECT
+ id,
+ source,
+ destination,
+ amount
+ FROM balancing
+ WHERE deleted_at IS NULL
+ ORDER BY created_at DESC
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+
+ stmt.query_map([], row_to_balancing)?
+ .collect::<Result<Vec<Balancing>, _>>()
+ })
+ .await;
+
+ match res {
+ Ok(xs) => xs,
+ Err(err) => {
+ log::error!("Error listing balancing: {:?}", err);
+ vec![]
+ }
+ }
+}
+
+pub async fn get_row(conn: &Connection, id: i64) -> i64 {
+ let query = r#"
+ SELECT row
+ FROM (
+ SELECT
+ ROW_NUMBER () OVER (ORDER BY created_at DESC) AS row,
+ id
+ FROM balancing
+ WHERE deleted_at IS NULL
+ )
+ WHERE id = :id
+ "#;
+
+ 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(row) => row,
+ Err(err) => {
+ log::error!("Error getting balancing row: {:?}", err);
+ 1
+ }
+ }
+}
+
+pub async fn get(conn: &Connection, id: i64) -> Option<Balancing> {
+ let query = r#"
+ SELECT
+ id,
+ source,
+ destination,
+ amount
+ FROM balancing
+ WHERE
+ id = :id
+ AND deleted_at IS NULL
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+ let mut iter =
+ stmt.query_map(named_params![":id": id], row_to_balancing)?;
+ utils::one(&mut iter)
+ })
+ .await;
+
+ match res {
+ Ok(balancing) => Some(balancing),
+ Err(err) => {
+ log::error!("Error looking for balancing {}: {:?}", id, err);
+ None
+ }
+ }
+}
+
+pub async fn create(conn: &Connection, c: Create) -> Option<i64> {
+ let query = r#"INSERT INTO balancing(source, destination, amount) VALUES (:source, :destination, :amount)"#;
+
+ let res: Result<_, tokio_rusqlite::Error<rusqlite::Error>> = conn
+ .call(move |conn| {
+ conn.execute(
+ query,
+ named_params![
+ ":source": c.source,
+ ":destination": c.destination,
+ ":amount": c.amount,
+ ],
+ )?;
+ Ok(conn.last_insert_rowid())
+ })
+ .await;
+
+ match res {
+ Ok(balancing_id) => Some(balancing_id),
+ Err(err) => {
+ log::error!("Error creating balancing: {:?}", err);
+ None
+ }
+ }
+}
+
+pub async fn update(conn: &Connection, id: i64, c: Update) -> bool {
+ let query = r#"
+ UPDATE balancing
+ SET
+ source = :source,
+ destination = :destination,
+ amount = :amount,
+ updated_at = datetime()
+ WHERE id = :id
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ conn.execute(
+ query,
+ named_params![
+ ":source": c.source,
+ ":destination": c.destination,
+ ":amount": c.amount,
+ ":id": id
+ ],
+ )
+ })
+ .await;
+
+ match res {
+ Ok(_) => true,
+ Err(err) => {
+ log::error!("Error updating balancing {}: {:?}", id, err);
+ false
+ }
+ }
+}
+
+pub async fn delete(conn: &Connection, id: i64) -> bool {
+ let res = conn
+ .call(move |conn| {
+ conn.execute(
+ r#"
+ UPDATE
+ balancing
+ SET
+ deleted_at = datetime()
+ WHERE
+ id = :id
+ "#,
+ named_params![":id": id],
+ )
+ })
+ .await;
+
+ match res {
+ Ok(_) => true,
+ Err(err) => {
+ log::error!("Error deleting balancing {}: {:?}", id, err);
+ false
+ }
+ }
+}
diff --git a/src/db/migrations/07-create-balancing-table.sql b/src/db/migrations/07-create-balancing-table.sql
new file mode 100644
index 0000000..148657e
--- /dev/null
+++ b/src/db/migrations/07-create-balancing-table.sql
@@ -0,0 +1,9 @@
+CREATE TABLE IF NOT EXISTS "balancing"(
+ "id" INTEGER PRIMARY KEY,
+ "source" INTEGER NOT NULL REFERENCES "users",
+ "destination" INTEGER NOT NULL REFERENCES "users",
+ "amount" INTEGER NOT NULL,
+ "created_at" TEXT NULL DEFAULT (datetime('now')),
+ "updated_at" TEXT NULL,
+ "deleted_at" TEXT NULL
+) STRICT;
diff --git a/src/db/mod.rs b/src/db/mod.rs
index c444995..d257bf1 100644
--- a/src/db/mod.rs
+++ b/src/db/mod.rs
@@ -2,6 +2,7 @@ use anyhow::{Error, Result};
use rusqlite_migration::{M, Migrations};
use tokio_rusqlite::Connection;
+pub mod balancing;
pub mod categories;
pub mod incomes;
pub mod jobs;
@@ -28,6 +29,7 @@ async fn apply_migrations(conn: &Connection) -> Result<()> {
M::up(include_str!("migrations/04-plural-naming.sql")),
M::up(include_str!("migrations/05-strict-tables.sql")),
M::up(include_str!("migrations/06-remove-weekly-report-job.sql")),
+ M::up(include_str!("migrations/07-create-balancing-table.sql")),
]);
Ok(conn.call(move |conn| migrations.to_latest(conn)).await?)
generated by cgit v1.2.3 (git 2.25.1) at 2026-04-21 12:38:22 +0000