1use chrono::{Datelike, Days, Months, NaiveDate, NaiveDateTime, NaiveTime};
2use diesel::dsl::{count_star, sql};
3use diesel::prelude::*;
4use diesel::result::Error;
5use diesel::sql_types::{Integer, Text};
6use rex_shared::models::{Cent, LAST_POSSIBLE_TIME};
7use std::collections::HashMap;
8
9use crate::ConnCache;
10use crate::models::{AmountNature, DateNature, FetchNature, Tag, TxMethod, TxTag, TxType};
11use crate::schema::{tx_tags, txs};
12
13pub static EMPTY: Vec<i32> = Vec::new();
14
15pub struct NewSearch<'a> {
16 pub date: Option<DateNature>,
17 pub details: Option<&'a str>,
18 pub tx_type: Option<&'a str>,
19 pub from_method: Option<i32>,
20 pub to_method: Option<i32>,
21 pub amount: Option<AmountNature>,
22 pub tags: Option<Vec<i32>>,
23}
24
25impl<'a> NewSearch<'a> {
26 #[must_use]
27 pub fn new(
28 date: Option<DateNature>,
29 details: Option<&'a str>,
30 tx_type: Option<&'a str>,
31 from_method: Option<i32>,
32 to_method: Option<i32>,
33 amount: Option<AmountNature>,
34 tags: Option<Vec<i32>>,
35 ) -> Self {
36 Self {
37 date,
38 details,
39 tx_type,
40 from_method,
41 to_method,
42 amount,
43 tags,
44 }
45 }
46
47 pub fn search_txs(&self, db_conn: &mut impl ConnCache) -> Result<Vec<FullTx>, Error> {
48 use crate::schema::txs::dsl::{
49 amount, date, details, from_method, id, to_method, tx_type, txs,
50 };
51
52 let mut query = txs.into_boxed();
53
54 if let Some(d) = self.date.as_ref() {
55 match d {
56 DateNature::Exact(d) => {
57 let start_date = NaiveDate::from_ymd_opt(d.year(), d.month(), d.day()).unwrap();
58 let end_date = start_date;
59
60 let start_date = start_date.and_time(NaiveTime::MIN);
61 let end_date = end_date.and_time(LAST_POSSIBLE_TIME);
62
63 query = query.filter(date.between(start_date, end_date));
64 }
65 DateNature::ByMonth {
66 start_date,
67 end_date,
68 }
69 | DateNature::ByYear {
70 start_date,
71 end_date,
72 } => {
73 query = query.filter(date.between(start_date, end_date));
74 }
75 }
76 }
77
78 if let Some(d) = self.details {
79 query = query.filter(details.like(format!("%{d}%")));
80 }
81
82 if let Some(t) = self.tx_type {
83 query = query.filter(tx_type.eq(t));
84 }
85
86 if let Some(m) = self.from_method {
87 query = query.filter(from_method.eq(m));
88 }
89
90 if let Some(m) = self.to_method {
91 query = query.filter(to_method.eq(m));
92 }
93
94 if let Some(a) = self.amount.as_ref() {
95 match a {
96 AmountNature::Exact(a) => {
97 query = query.filter(amount.eq(a.value()));
98 }
99 AmountNature::MoreThan(a) => {
100 query = query.filter(amount.gt(a.value()));
101 }
102 AmountNature::MoreThanEqual(a) => {
103 query = query.filter(amount.ge(a.value()));
104 }
105 AmountNature::LessThan(a) => {
106 query = query.filter(amount.lt(a.value()));
107 }
108 AmountNature::LessThanEqual(a) => {
109 query = query.filter(amount.le(a.value()));
110 }
111 }
112 }
113
114 if let Some(tag_ids) = self.tags.as_ref()
115 && !tag_ids.is_empty()
116 {
117 let subquery = tx_tags::table
118 .filter(tx_tags::tag_id.eq_any(tag_ids))
119 .group_by(tx_tags::tx_id)
120 .select((tx_tags::tx_id, count_star()))
121 .having(count_star().eq(tag_ids.len() as i64));
122
123 query = query.filter(id.eq_any(subquery.select(tx_tags::tx_id)));
124 }
125
126 let result = query.select(Tx::as_select()).load(db_conn.conn())?;
127
128 FullTx::convert_to_full_tx(result, db_conn)
129 }
130}
131
132#[derive(Clone, Debug)]
133pub struct FullTx {
134 pub id: i32,
135 pub date: NaiveDateTime,
136 pub details: Option<String>,
137 pub from_method: TxMethod,
138 pub to_method: Option<TxMethod>,
139 pub amount: Cent,
140 pub tx_type: TxType,
141 pub tags: Vec<Tag>,
142 pub display_order: i32,
143}
144
145#[derive(Clone, Queryable, Selectable, Insertable)]
146pub struct Tx {
147 pub id: i32,
148 date: NaiveDateTime,
149 details: Option<String>,
150 pub from_method: i32,
151 pub to_method: Option<i32>,
152 pub amount: i64,
153 pub tx_type: String,
154 display_order: i32,
155}
156
157#[derive(Clone, Insertable)]
158#[diesel(table_name = txs)]
159pub struct NewTx<'a> {
160 pub date: NaiveDateTime,
161 pub details: Option<&'a str>,
162 pub from_method: i32,
163 pub to_method: Option<i32>,
164 pub amount: i64,
165 pub tx_type: &'a str,
166}
167
168impl<'a> NewTx<'a> {
169 #[must_use]
170 pub fn new(
171 date: NaiveDateTime,
172 details: Option<&'a str>,
173 from_method: i32,
174 to_method: Option<i32>,
175 amount: i64,
176 tx_type: &'a str,
177 ) -> Self {
178 NewTx {
179 date,
180 details,
181 from_method,
182 to_method,
183 amount,
184 tx_type,
185 }
186 }
187
188 pub fn insert(self, db_conn: &mut impl ConnCache) -> Result<Tx, Error> {
189 use crate::schema::txs::dsl::txs;
190
191 diesel::insert_into(txs)
192 .values(self)
193 .returning(Tx::as_returning())
194 .get_result(db_conn.conn())
195 }
196}
197
198impl FullTx {
199 pub fn get_txs(
200 d: NaiveDate,
201 nature: FetchNature,
202 db_conn: &mut impl ConnCache,
203 ) -> Result<Vec<Self>, Error> {
204 let all_txs = Tx::get_txs(d, nature, db_conn)?;
205
206 FullTx::convert_to_full_tx(all_txs, db_conn)
207 }
208
209 pub fn get_tx_by_id(id_num: i32, db_conn: &mut impl ConnCache) -> Result<Self, Error> {
210 let tx = Tx::get_tx_by_id(id_num, db_conn)?;
211
212 Ok(FullTx::convert_to_full_tx(vec![tx], db_conn)?
213 .pop()
214 .unwrap())
215 }
216
217 pub fn convert_to_full_tx(
218 txs: Vec<Tx>,
219 db_conn: &mut impl ConnCache,
220 ) -> Result<Vec<FullTx>, Error> {
221 let tx_ids = txs.iter().map(|t| t.id).collect::<Vec<i32>>();
222
223 let tx_tags = TxTag::get_by_tx_ids(tx_ids, db_conn)?;
224
225 let mut tx_tags_map = HashMap::new();
226
227 for tag in tx_tags {
228 tx_tags_map
229 .entry(tag.tx_id)
230 .or_insert(Vec::new())
231 .push(tag.tag_id);
232 }
233
234 let mut to_return = Vec::new();
235
236 for tx in txs {
237 let tags: Vec<Tag> = {
238 let tag_ids = tx_tags_map.get(&tx.id).unwrap_or(&EMPTY);
239 let mut v = Vec::with_capacity(tag_ids.len());
240 for tag_id in tag_ids {
241 v.push(db_conn.cache().tags.get(tag_id).unwrap().clone());
242 }
243 v
244 };
245
246 let full_tx = FullTx {
247 id: tx.id,
248 date: tx.date,
249 details: tx.details,
250 from_method: db_conn
251 .cache()
252 .tx_methods
253 .get(&tx.from_method)
254 .unwrap()
255 .clone(),
256 to_method: tx
257 .to_method
258 .map(|method_id| db_conn.cache().tx_methods.get(&method_id).unwrap().clone()),
259 amount: Cent::new(tx.amount),
260 tx_type: tx.tx_type.as_str().into(),
261 tags,
262 display_order: tx.display_order,
263 };
264
265 to_return.push(full_tx);
266 }
267
268 Ok(to_return)
269 }
270
271 pub fn get_changes(&self, db_conn: &impl ConnCache) -> HashMap<i32, String> {
272 let mut map = HashMap::new();
273
274 for method_id in db_conn.cache().tx_methods.keys() {
275 let mut no_impact = true;
276
277 if self.from_method.id == *method_id {
278 no_impact = false;
279 }
280
281 if let Some(to_method) = &self.to_method
282 && to_method.id == *method_id
283 {
284 no_impact = false;
285 }
286
287 if no_impact {
288 map.insert(*method_id, "0.00".to_string());
289 continue;
290 }
291
292 match self.tx_type {
293 TxType::Income | TxType::Borrow | TxType::LendRepay => {
294 map.insert(*method_id, format!("↑{:.2}", self.amount.dollar()));
295 }
296 TxType::Expense | TxType::Lend | TxType::BorrowRepay => {
297 map.insert(*method_id, format!("↓{:.2}", self.amount.dollar()));
298 }
299 TxType::Transfer => {
300 if self.from_method.id == *method_id {
301 map.insert(*method_id, format!("↓{:.2}", self.amount.dollar()));
302 } else {
303 map.insert(*method_id, format!("↑{:.2}", self.amount.dollar()));
304 }
305 }
306 }
307 }
308
309 map
310 }
311
312 pub fn empty_changes(db_conn: &impl ConnCache) -> HashMap<i32, String> {
313 let mut map = HashMap::new();
314
315 for method_id in db_conn.cache().tx_methods.keys() {
316 map.insert(*method_id, "0.00".to_string());
317 }
318
319 map
320 }
321
322 pub fn get_changes_partial(
323 from_method: i32,
324 to_method: Option<i32>,
325 tx_type: TxType,
326 amount: Cent,
327 db_conn: &impl ConnCache,
328 ) -> HashMap<i32, String> {
329 let mut map = HashMap::new();
330
331 for method_id in db_conn.cache().tx_methods.keys() {
332 let mut no_impact = true;
333
334 if from_method == *method_id {
335 no_impact = false;
336 }
337
338 if let Some(to_method) = &to_method
339 && to_method == method_id
340 {
341 no_impact = false;
342 }
343
344 if no_impact {
345 map.insert(*method_id, "0.00".to_string());
346 continue;
347 }
348
349 match tx_type {
350 TxType::Income | TxType::Borrow | TxType::LendRepay => {
351 map.insert(*method_id, format!("↑{:.2}", amount.dollar()));
352 }
353 TxType::Expense | TxType::Lend | TxType::BorrowRepay => {
354 map.insert(*method_id, format!("↓{:.2}", amount.dollar()));
355 }
356 TxType::Transfer => {
357 if from_method == *method_id {
358 map.insert(*method_id, format!("↓{:.2}", amount.dollar()));
359 } else {
360 map.insert(*method_id, format!("↑{:.2}", amount.dollar()));
361 }
362 }
363 }
364 }
365
366 map
367 }
368
369 #[must_use]
370 pub fn to_array(&self, is_search: bool) -> Vec<String> {
371 let mut method = self.from_method.name.clone();
372
373 if let Some(to_method) = &self.to_method {
374 method = format!("{} → {}", self.from_method.name, to_method.name);
375 }
376
377 let date = if is_search {
378 self.date.format("%Y-%m-%d").to_string()
379 } else {
380 self.date.format("%a %d %I:%M %p").to_string()
381 };
382
383 vec![
384 date,
385 self.details.clone().unwrap_or_default(),
386 method,
387 format!("{:.2}", self.amount.dollar()),
388 self.tx_type.to_string(),
389 self.tags
390 .iter()
391 .map(|t| t.name.clone())
392 .collect::<Vec<String>>()
393 .join(", "),
394 ]
395 }
396
397 pub fn set_display_order(&self, db_conn: &mut impl ConnCache) -> Result<usize, Error> {
398 use crate::schema::txs::dsl::{display_order, id, txs};
399
400 diesel::update(txs.filter(id.eq(self.id)))
401 .set(display_order.eq(self.display_order))
402 .execute(db_conn.conn())
403 }
404}
405
406impl Tx {
407 pub fn insert(self, db_conn: &mut impl ConnCache) -> Result<Self, Error> {
408 use crate::schema::txs::dsl::txs;
409
410 diesel::insert_into(txs)
411 .values(self)
412 .returning(Tx::as_returning())
413 .get_result(db_conn.conn())
414 }
415
416 pub fn get_tx_by_id(id_num: i32, db_conn: &mut impl ConnCache) -> Result<Self, Error> {
417 use crate::schema::txs::dsl::{id, txs};
418
419 txs.filter(id.eq(id_num))
420 .select(Self::as_select())
421 .first(db_conn.conn())
422 }
423
424 pub fn get_txs(
425 d: NaiveDate,
426 nature: FetchNature,
427 db_conn: &mut impl ConnCache,
428 ) -> Result<Vec<Self>, Error> {
429 let d = d.and_time(NaiveTime::MIN);
430
431 use crate::schema::txs::dsl::{date, display_order, id, txs};
432
433 let dates = match nature {
434 FetchNature::Monthly => {
435 let start_date = NaiveDate::from_ymd_opt(d.year(), d.month(), 1).unwrap();
436
437 let end_date = start_date + Months::new(1) - Days::new(1);
438
439 let start_date = start_date.and_time(NaiveTime::MIN);
440 let end_date = end_date.and_time(LAST_POSSIBLE_TIME);
441
442 Some((start_date, end_date))
443 }
444 FetchNature::Yearly => {
445 let start_date = NaiveDate::from_ymd_opt(d.year(), 1, 1).unwrap();
446
447 let end_date = start_date + Months::new(12) - Days::new(1);
448
449 let start_date = start_date.and_time(NaiveTime::MIN);
450 let end_date = end_date.and_time(LAST_POSSIBLE_TIME);
451
452 Some((start_date, end_date))
453 }
454 FetchNature::All => None,
455 };
456
457 let mut query = txs.into_boxed();
458
459 if let Some((start_date, end_date)) = dates {
460 query = query.filter(date.ge(start_date)).filter(date.le(end_date));
461 }
462
463 query
464 .order((
465 sql::<Text>("DATE(date) ASC"),
466 sql::<Integer>("CASE WHEN display_order = 0 THEN 1 ELSE 0 END ASC"),
467 display_order.asc(),
468 id.asc(),
469 ))
470 .select(Tx::as_select())
471 .load(db_conn.conn())
472 }
473
474 pub fn delete_tx(id: i32, db_conn: &mut impl ConnCache) -> Result<usize, Error> {
475 use crate::schema::txs::dsl::txs;
476
477 diesel::delete(txs.find(id)).execute(db_conn.conn())
478 }
479
480 #[must_use]
481 pub fn from_new_tx(new_tx: NewTx, id: i32) -> Self {
482 Self {
483 id,
484 date: new_tx.date,
485 details: new_tx.details.map(std::string::ToString::to_string),
486 from_method: new_tx.from_method,
487 to_method: new_tx.to_method,
488 amount: new_tx.amount,
489 tx_type: new_tx.tx_type.to_string(),
490 display_order: 0,
491 }
492 }
493
494 pub fn get_all_details(db_conn: &mut impl ConnCache) -> Result<Vec<String>, Error> {
495 use crate::schema::txs::dsl::{details, txs};
496
497 let result: Vec<Option<String>> = txs
498 .select(details)
499 .filter(details.is_not_null())
500 .load(db_conn.conn())?;
501
502 Ok(result.into_iter().flatten().collect())
503 }
504}