add: view subcommand implemented

1 files changed, 63 insertions, 2 deletions

diff --git a/src/database.rs b/src/database.rs
index 8321723..14f04d9 100644
--- a/src/database.rs
+++ b/src/database.rs
@@ -1,5 +1,4 @@
-use rusqlite::Connection;
-use rusqlite::Result;
+use rusqlite::{Connection, Result, Rows, Statement};
 use rusqlite::config::DbConfig::{*};
 use chrono::Utc;
 use std::path::PathBuf;
@@ -46,6 +45,68 @@ impl Database {
         Ok(())
     }
 
+    pub fn view_history(filepath: &PathBuf, filter: Option<String>) -> Result<()> {
+        let conn = Connection::open(filepath)?;
+        let mut hist_query: String = "
+            SELECT date(r.register_date, 'auto'), a.name, r.amount
+            FROM Registers r
+            INNER JOIN Agents a
+            ON a.id = r.agent_id
+        ".to_owned();
+
+        let mut stmt: Statement;
+        let mut regs: Rows;
+
+        match filter {
+            Some(name) => {
+                hist_query.push_str("WHERE name = ?1");
+                stmt = conn.prepare(hist_query.as_str())?;
+                regs = stmt.query([name])?;
+            },
+            None => {
+                stmt = conn.prepare(hist_query.as_str())?;
+                regs = stmt.query([])?;
+            }
+        };
+        let mut total = 0;
+        println!("+{:-<10}+{:-<20}+{:-<10}+", "", "", "");
+        println!("|{:^10}|{:^20}|{:^10}|", "date", "name", "amount");
+        println!("+{:-<10}+{:-<20}+{:-<10}+", "", "", "");
+        while let Some(row) = regs.next()? {
+            let date: String = row.get(0)?;
+            let name: String = row.get(1)?;
+            let amount: i32 = row.get(2)?;
+
+            println!("|{}|{:>20}|{:>10}|", date, name, amount);
+            total += amount;
+        }
+        println!("+{:-<10}+{:-<20}+{:-<10}+", "", "", "");
+        println!("|{:^10}{:>32}|", "total", total);
+        println!("+{:-<10}+{:-<20}+{:-<10}+", "", "", "");
+        Ok(())
+    }
+
+    pub fn view_total(filepath: &PathBuf) -> Result<()> {
+        let conn = Connection::open(filepath)?;
+        let mut stmt = conn.prepare("
+            SELECT a.name, sum(r.amount)
+            FROM Registers r
+            INNER JOIN Agents a
+            ON a.id = r.agent_id
+            GROUP BY a.name")?;
+        let mut agents = stmt.query([])?;
+        println!("+{:->20}+{:->11}+", "", "");
+        println!("|{:^20}| {:^10}|", "name", "amount");
+        println!("+{:->20}+{:->11}+", "", "");
+        while let Some(row) = agents.next()? {
+            let name: String = row.get(0)?;
+            let amount: i32 = row.get(1)?;
+            println!("|{:>20}| {:>10}|", name, amount);
+        }
+        println!("+{:->20}+{:->11}+", "", "");
+        Ok(())
+    }
+
     pub fn init_database(filepath: &PathBuf) -> Result<()> {
         let conn = Connection::open(filepath).expect("Database file creation Error");
         conn.execute("