1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
use rusqlite::{Connection, Result, Rows, Statement};
use rusqlite::config::DbConfig::{*};
use chrono::Utc;
use std::path::PathBuf;
pub struct Database {
date: i64,
person: String,
amount: i32,
note: Option<String>,
}
impl Database {
pub fn new(person: String, amount: i32, note: Option<String>, is_debt: bool) -> Database {
Database {
date: Utc::now().timestamp(),
amount: if !is_debt {amount} else {-amount},
person,
note,
}
}
pub fn add_register(&self, filepath: &PathBuf) -> Result<()> {
let conn = Connection::open(filepath)?;
conn.set_db_config(SQLITE_DBCONFIG_ENABLE_FKEY, true)?;
conn.execute("
INSERT INTO Registers (agent_id, register_date, amount, note)
VALUES (
(SELECT id FROM Agents WHERE name=?1),
?2, ?3, ?4)",
(&self.person,
&self.date,
self.amount,
&self.note))?;
println!("Register added successfully");
Ok(())
}
pub fn add_agent(&self, filepath: &PathBuf) -> Result<()> {
let conn = Connection::open(filepath)?;
conn.execute("
INSERT INTO Agents (name)
VALUES (?1)", (&self.person,))?;
println!("agent '{}' successfully", &self.person);
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, r.note
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}+{:-<20}+", "", "", "", "");
println!("|{:^10}|{:^20}|{:^10}|{:^20}|", "date", "name", "amount", "note");
println!("+{:-<10}+{:-<20}+{:-<10}+{:-<20}+", "", "", "", "");
while let Some(row) = regs.next()? {
let date: String = row.get(0)?;
let name: String = row.get(1)?;
let amount: i32 = row.get(2)?;
let note: Option<String> = row.get(3)?;
println!("|{}|{:>20}|{:>10}|{:>20}|", date, name, amount, note.unwrap_or("".to_string()));
total += amount;
}
println!("+{:-<10}+{:-<20}+{:-<10}+{:-<20}+", "", "", "", "");
println!("|{:^10}|{:>52}|", "total", total);
println!("+{:-<10}+{:-<52}+", "", "");
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("
CREATE TABLE Agents (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE)", ())
.expect("SQL initialization error");
conn.execute("
CREATE TABLE Registers (
id INTEGER PRIMARY KEY,
agent_id INTEGER NOT NULL,
register_date INTEGER NOT NULL,
amount INTEGER NOT NULL,
note TEXT,
FOREIGN KEY(agent_id) REFERENCES Agents(id))", ())
.expect("SQL initialization error");
println!("'{}' database created", &filepath.display());
Ok(())
}
}
|
