From 99fa72e2b9d572e00cd232bd55a3fa24722c3134 Mon Sep 17 00:00:00 2001
From: Joe Carstairs <me@joeac.net>
Date: Sun, 17 Nov 2024 12:16:19 +0000
Subject: Refactors workspace

---
 .../src/transaction_categorisations.rs             | 59 ++++++++++++++++++++++
 1 file changed, 59 insertions(+)
 create mode 100644 rust/schist_queries/src/transaction_categorisations.rs

(limited to 'rust/schist_queries/src/transaction_categorisations.rs')

diff --git a/rust/schist_queries/src/transaction_categorisations.rs b/rust/schist_queries/src/transaction_categorisations.rs
new file mode 100644
index 0000000..320782d
--- /dev/null
+++ b/rust/schist_queries/src/transaction_categorisations.rs
@@ -0,0 +1,59 @@
+use anyhow::{Context, Result};
+use diesel::{dsl::sum, QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection};
+use schist_models::transaction_categorisation::TransactionCategorisation;
+use schist_schema::schema::transaction_categorisations::{
+    self as transaction_categorisations_schema,
+    dsl::transaction_categorisations as transaction_categorisations_table,
+};
+
+pub fn delete_all_transaction_categorisations(connection: &mut SqliteConnection) -> Result<usize> {
+    let num_rows_deleted = diesel::delete(transaction_categorisations_table)
+        .execute(connection)
+        .with_context(|| "failed to delete all transaction categorisations")?;
+    Ok(num_rows_deleted)
+}
+
+pub fn get_all_transaction_categorisations(connection: &mut SqliteConnection) -> Result<Vec<TransactionCategorisation>> {
+    let all_transaction_categorisations = transaction_categorisations_table
+        .select(TransactionCategorisation::as_select())
+        .load(connection)
+        .with_context(|| "failed to get all transaction categorisations")?;
+    Ok(all_transaction_categorisations)
+}
+
+pub fn insert_transaction_categorisations(
+    transaction_categorisations: &[TransactionCategorisation],
+    connection: &mut SqliteConnection,
+) -> Result<usize> {
+    let num_rows_inserted = diesel::insert_into(transaction_categorisations_table)
+        .values(transaction_categorisations)
+        .execute(connection)
+        .with_context(|| insert_err_msg(&transaction_categorisations))?;
+    Ok(num_rows_inserted)
+}
+
+fn insert_err_msg(transaction_categorisations: &[TransactionCategorisation]) -> String {
+    format!(
+        "failed to insert transaction categorisations: [{}]",
+        transaction_categorisations
+            .iter()
+            .map(|tc| tc.id.to_string())
+            .collect::<Vec<String>>()
+            .join(", ")
+    )
+}
+
+pub fn sum_transaction_categorisation_quantity_per_category_id(
+    connection: &mut SqliteConnection,
+) -> Result<Vec<(i32, i64)>> {
+    let sum = transaction_categorisations_table
+        .group_by(transaction_categorisations_schema::category_id)
+        .select((
+            transaction_categorisations_schema::category_id,
+            sum(transaction_categorisations_schema::quantity),
+        ))
+        .load::<(i32, Option<i64>)>(connection)
+        .map(|vec| vec.iter().map(|(category_id, sum)| (*category_id, sum.unwrap_or(0))).collect())
+        .with_context(|| "failed to sum transaction categorisation quantity per category ID")?;
+    Ok(sum)
+}
-- 
cgit v1.2.3