1
use chrono::{DateTime, Utc};
2
use finance::error::FinanceError;
3
use num_rational::Rational64;
4
use sqlx::types::Uuid;
5

            
6
use super::super::{CmdError, ReportFilter};
7
use super::filter::SqlParam;
8
use super::tree::{
9
    AccountAmounts, AccountRow, ConversionTarget, accumulate_split, accumulate_split_converted,
10
};
11

            
12
14
pub(super) async fn fetch_accounts(
13
14
    conn: &mut sqlx::PgConnection,
14
14
) -> Result<Vec<AccountRow>, CmdError> {
15
14
    let rows = sqlx::query_file!("sql/report/accounts/with_names.sql")
16
14
        .fetch_all(conn)
17
14
        .await?;
18
14
    Ok(rows
19
14
        .into_iter()
20
14
        .map(|r| AccountRow {
21
32
            account_id: r.account_id,
22
32
            parent_id: r.parent_id,
23
32
            account_name: r.account_name,
24
32
        })
25
14
        .collect())
26
14
}
27

            
28
3
pub(super) async fn fetch_target_symbol(
29
3
    conn: &mut sqlx::PgConnection,
30
3
    target_commodity_id: Uuid,
31
3
) -> Result<String, CmdError> {
32
    Ok(
33
3
        sqlx::query_file_scalar!("sql/select/commodities/symbol.sql", &target_commodity_id)
34
3
            .fetch_optional(conn)
35
3
            .await?
36
3
            .unwrap_or_else(|| target_commodity_id.to_string()),
37
    )
38
3
}
39

            
40
4
pub(super) async fn fetch_balance_splits_no_conversion(
41
4
    conn: &mut sqlx::PgConnection,
42
4
    as_of: Option<DateTime<Utc>>,
43
4
) -> Result<AccountAmounts, CmdError> {
44
4
    let rows = sqlx::query_file!("sql/report/splits/all.sql", as_of)
45
4
        .fetch_all(conn)
46
4
        .await?;
47

            
48
4
    let mut amounts = AccountAmounts::new();
49
12
    for r in rows {
50
12
        accumulate_split(
51
12
            &mut amounts,
52
12
            r.account_id,
53
12
            r.commodity_id,
54
12
            Rational64::new(r.value_num, r.value_denom),
55
12
            &r.commodity_symbol,
56
12
        );
57
12
    }
58
4
    Ok(amounts)
59
4
}
60

            
61
2
pub(super) async fn fetch_balance_splits_with_conversion(
62
2
    conn: &mut sqlx::PgConnection,
63
2
    target_commodity_id: Uuid,
64
2
    target_symbol: &str,
65
2
    as_of: Option<DateTime<Utc>>,
66
2
) -> Result<AccountAmounts, CmdError> {
67
2
    let rows = sqlx::query_file!(
68
2
        "sql/report/splits/all_with_conversion.sql",
69
        &target_commodity_id,
70
        as_of
71
    )
72
2
    .fetch_all(conn)
73
2
    .await?;
74

            
75
2
    let target = ConversionTarget {
76
2
        commodity_id: target_commodity_id,
77
2
        symbol: target_symbol,
78
2
    };
79
2
    let mut amounts = AccountAmounts::new();
80
5
    for r in rows {
81
5
        accumulate_split_converted(
82
5
            &mut amounts,
83
5
            r.account_id,
84
5
            r.commodity_id,
85
5
            Rational64::new(r.value_num, r.value_denom),
86
5
            &r.commodity_symbol,
87
5
            &target,
88
5
            (r.price_num, r.price_denom),
89
        )
90
5
        .map_err(|e| CmdError::Finance(FinanceError::Report(e)))?;
91
    }
92
1
    Ok(amounts)
93
2
}
94

            
95
6
pub(super) async fn fetch_date_range_splits_no_conversion(
96
6
    conn: &mut sqlx::PgConnection,
97
6
    from: DateTime<Utc>,
98
6
    to: DateTime<Utc>,
99
6
) -> Result<AccountAmounts, CmdError> {
100
6
    let rows = sqlx::query_file!("sql/report/splits/date_range.sql", from, to)
101
6
        .fetch_all(conn)
102
6
        .await?;
103

            
104
6
    let mut amounts = AccountAmounts::new();
105
16
    for r in rows {
106
16
        accumulate_split(
107
16
            &mut amounts,
108
16
            r.account_id,
109
16
            r.commodity_id,
110
16
            Rational64::new(r.value_num, r.value_denom),
111
16
            &r.commodity_symbol,
112
16
        );
113
16
    }
114
6
    Ok(amounts)
115
6
}
116

            
117
1
pub(super) async fn fetch_date_range_splits_with_conversion(
118
1
    conn: &mut sqlx::PgConnection,
119
1
    target_commodity_id: Uuid,
120
1
    target_symbol: &str,
121
1
    from: DateTime<Utc>,
122
1
    to: DateTime<Utc>,
123
1
) -> Result<AccountAmounts, CmdError> {
124
1
    let rows = sqlx::query_file!(
125
1
        "sql/report/splits/date_range_with_conversion.sql",
126
        &target_commodity_id,
127
        from,
128
        to
129
    )
130
1
    .fetch_all(conn)
131
1
    .await?;
132

            
133
1
    let target = ConversionTarget {
134
1
        commodity_id: target_commodity_id,
135
1
        symbol: target_symbol,
136
1
    };
137
1
    let mut amounts = AccountAmounts::new();
138
4
    for r in rows {
139
4
        accumulate_split_converted(
140
4
            &mut amounts,
141
4
            r.account_id,
142
4
            r.commodity_id,
143
4
            Rational64::new(r.value_num, r.value_denom),
144
4
            &r.commodity_symbol,
145
4
            &target,
146
4
            (r.price_num, r.price_denom),
147
        )
148
4
        .map_err(|e| CmdError::Finance(FinanceError::Report(e)))?;
149
    }
150
1
    Ok(amounts)
151
1
}
152

            
153
1
pub(super) async fn fetch_balance_splits_filtered_no_conversion(
154
1
    conn: &mut sqlx::PgConnection,
155
1
    as_of: Option<DateTime<Utc>>,
156
1
    filter: &ReportFilter,
157
1
) -> Result<AccountAmounts, CmdError> {
158
    use sqlx::Row;
159

            
160
1
    let base_sql = concat!(
161
        "SELECT s.account_id, s.commodity_id, s.value_num, s.value_denom, ",
162
        "t_symbol.tag_value AS commodity_symbol ",
163
        "FROM splits AS s ",
164
        "INNER JOIN transactions AS t ON s.tx_id = t.id ",
165
        "INNER JOIN commodity_tags AS ct_symbol ON s.commodity_id = ct_symbol.commodity_id ",
166
        "INNER JOIN tags AS t_symbol ON (ct_symbol.tag_id = t_symbol.id AND t_symbol.tag_name = 'symbol') ",
167
        "WHERE ($1::timestamptz IS NULL OR t.post_date <= $1)"
168
    );
169

            
170
1
    let mut bind_offset: i32 = 1;
171
1
    let (where_clause, params) = filter.to_sql(&mut bind_offset);
172
1
    let full_sql = format!("{base_sql} AND {where_clause}");
173

            
174
1
    let mut query = sqlx::query(&full_sql).bind(as_of);
175
1
    for p in &params {
176
1
        query = match p {
177
1
            SqlParam::Uuid(v) => query.bind(*v),
178
            SqlParam::UuidVec(v) => query.bind(v),
179
            SqlParam::I64(v) => query.bind(*v),
180
            SqlParam::String(v) => query.bind(v.as_str()),
181
            SqlParam::StringVec(v) => query.bind(v),
182
        };
183
    }
184

            
185
1
    let rows = query.fetch_all(&mut *conn).await?;
186

            
187
1
    let mut amounts = AccountAmounts::new();
188
1
    for r in &rows {
189
1
        let account_id: Uuid = r.get("account_id");
190
1
        let commodity_id: Uuid = r.get("commodity_id");
191
1
        let value_num: i64 = r.get("value_num");
192
1
        let value_denom: i64 = r.get("value_denom");
193
1
        let commodity_symbol: String = r.get("commodity_symbol");
194
1
        accumulate_split(
195
1
            &mut amounts,
196
1
            account_id,
197
1
            commodity_id,
198
1
            Rational64::new(value_num, value_denom),
199
1
            &commodity_symbol,
200
1
        );
201
1
    }
202
1
    Ok(amounts)
203
1
}
204

            
205
pub(super) async fn fetch_balance_splits_filtered_with_conversion(
206
    conn: &mut sqlx::PgConnection,
207
    target_commodity_id: Uuid,
208
    target_symbol: &str,
209
    as_of: Option<DateTime<Utc>>,
210
    filter: &ReportFilter,
211
) -> Result<AccountAmounts, CmdError> {
212
    use sqlx::Row;
213

            
214
    let base_sql = concat!(
215
        "SELECT s.id AS split_id, s.account_id, s.commodity_id, s.value_num, s.value_denom, ",
216
        "t_symbol.tag_value AS commodity_symbol, ",
217
        "p.value_num AS price_num, p.value_denom AS price_denom ",
218
        "FROM splits AS s ",
219
        "INNER JOIN transactions AS t ON s.tx_id = t.id ",
220
        "INNER JOIN commodity_tags AS ct_symbol ON s.commodity_id = ct_symbol.commodity_id ",
221
        "INNER JOIN tags AS t_symbol ON (ct_symbol.tag_id = t_symbol.id AND t_symbol.tag_name = 'symbol') ",
222
        "LEFT JOIN prices AS p ON (s.id = p.commodity_split_id AND p.currency_id = $1) ",
223
        "WHERE ($2::timestamptz IS NULL OR t.post_date <= $2)"
224
    );
225

            
226
    let mut bind_offset: i32 = 2;
227
    let (where_clause, params) = filter.to_sql(&mut bind_offset);
228
    let full_sql = format!("{base_sql} AND {where_clause}");
229

            
230
    let mut query = sqlx::query(&full_sql).bind(target_commodity_id).bind(as_of);
231
    for p in &params {
232
        query = match p {
233
            SqlParam::Uuid(v) => query.bind(*v),
234
            SqlParam::UuidVec(v) => query.bind(v),
235
            SqlParam::I64(v) => query.bind(*v),
236
            SqlParam::String(v) => query.bind(v.as_str()),
237
            SqlParam::StringVec(v) => query.bind(v),
238
        };
239
    }
240

            
241
    let rows = query.fetch_all(&mut *conn).await?;
242

            
243
    let target = ConversionTarget {
244
        commodity_id: target_commodity_id,
245
        symbol: target_symbol,
246
    };
247
    let mut amounts = AccountAmounts::new();
248
    for r in &rows {
249
        let account_id: Uuid = r.get("account_id");
250
        let commodity_id: Uuid = r.get("commodity_id");
251
        let value_num: i64 = r.get("value_num");
252
        let value_denom: i64 = r.get("value_denom");
253
        let commodity_symbol: String = r.get("commodity_symbol");
254
        let price_num: Option<i64> = r.get("price_num");
255
        let price_denom: Option<i64> = r.get("price_denom");
256
        accumulate_split_converted(
257
            &mut amounts,
258
            account_id,
259
            commodity_id,
260
            Rational64::new(value_num, value_denom),
261
            &commodity_symbol,
262
            &target,
263
            (price_num, price_denom),
264
        )
265
        .map_err(|e| CmdError::Finance(FinanceError::Report(e)))?;
266
    }
267
    Ok(amounts)
268
}
269

            
270
1
pub(super) async fn fetch_date_range_splits_filtered_no_conversion(
271
1
    conn: &mut sqlx::PgConnection,
272
1
    from: DateTime<Utc>,
273
1
    to: DateTime<Utc>,
274
1
    filter: &ReportFilter,
275
1
) -> Result<AccountAmounts, CmdError> {
276
    use sqlx::Row;
277

            
278
1
    let base_sql = concat!(
279
        "SELECT s.account_id, s.commodity_id, s.value_num, s.value_denom, t.post_date, ",
280
        "t_symbol.tag_value AS commodity_symbol ",
281
        "FROM splits AS s ",
282
        "INNER JOIN transactions AS t ON s.tx_id = t.id ",
283
        "INNER JOIN commodity_tags AS ct_symbol ON s.commodity_id = ct_symbol.commodity_id ",
284
        "INNER JOIN tags AS t_symbol ON (ct_symbol.tag_id = t_symbol.id AND t_symbol.tag_name = 'symbol') ",
285
        "WHERE t.post_date >= $1 AND t.post_date < $2"
286
    );
287

            
288
1
    let mut bind_offset: i32 = 2;
289
1
    let (where_clause, params) = filter.to_sql(&mut bind_offset);
290
1
    let full_sql = format!("{base_sql} AND {where_clause}");
291

            
292
1
    let mut query = sqlx::query(&full_sql).bind(from).bind(to);
293
1
    for p in &params {
294
1
        query = match p {
295
1
            SqlParam::Uuid(v) => query.bind(*v),
296
            SqlParam::UuidVec(v) => query.bind(v),
297
            SqlParam::I64(v) => query.bind(*v),
298
            SqlParam::String(v) => query.bind(v.as_str()),
299
            SqlParam::StringVec(v) => query.bind(v),
300
        };
301
    }
302

            
303
1
    let rows = query.fetch_all(&mut *conn).await?;
304

            
305
1
    let mut amounts = AccountAmounts::new();
306
2
    for r in &rows {
307
2
        let account_id: Uuid = r.get("account_id");
308
2
        let commodity_id: Uuid = r.get("commodity_id");
309
2
        let value_num: i64 = r.get("value_num");
310
2
        let value_denom: i64 = r.get("value_denom");
311
2
        let commodity_symbol: String = r.get("commodity_symbol");
312
2
        accumulate_split(
313
2
            &mut amounts,
314
2
            account_id,
315
2
            commodity_id,
316
2
            Rational64::new(value_num, value_denom),
317
2
            &commodity_symbol,
318
2
        );
319
2
    }
320
1
    Ok(amounts)
321
1
}
322

            
323
pub(super) async fn fetch_date_range_splits_filtered_with_conversion(
324
    conn: &mut sqlx::PgConnection,
325
    target_commodity_id: Uuid,
326
    target_symbol: &str,
327
    from: DateTime<Utc>,
328
    to: DateTime<Utc>,
329
    filter: &ReportFilter,
330
) -> Result<AccountAmounts, CmdError> {
331
    use sqlx::Row;
332

            
333
    let base_sql = concat!(
334
        "SELECT s.id AS split_id, s.account_id, s.commodity_id, s.value_num, s.value_denom, ",
335
        "t_symbol.tag_value AS commodity_symbol, t.post_date, ",
336
        "p.value_num AS price_num, p.value_denom AS price_denom ",
337
        "FROM splits AS s ",
338
        "INNER JOIN transactions AS t ON s.tx_id = t.id ",
339
        "INNER JOIN commodity_tags AS ct_symbol ON s.commodity_id = ct_symbol.commodity_id ",
340
        "INNER JOIN tags AS t_symbol ON (ct_symbol.tag_id = t_symbol.id AND t_symbol.tag_name = 'symbol') ",
341
        "LEFT JOIN prices AS p ON (s.id = p.commodity_split_id AND p.currency_id = $1) ",
342
        "WHERE t.post_date >= $2 AND t.post_date < $3"
343
    );
344

            
345
    let mut bind_offset: i32 = 3;
346
    let (where_clause, params) = filter.to_sql(&mut bind_offset);
347
    let full_sql = format!("{base_sql} AND {where_clause}");
348

            
349
    let mut query = sqlx::query(&full_sql)
350
        .bind(target_commodity_id)
351
        .bind(from)
352
        .bind(to);
353
    for p in &params {
354
        query = match p {
355
            SqlParam::Uuid(v) => query.bind(*v),
356
            SqlParam::UuidVec(v) => query.bind(v),
357
            SqlParam::I64(v) => query.bind(*v),
358
            SqlParam::String(v) => query.bind(v.as_str()),
359
            SqlParam::StringVec(v) => query.bind(v),
360
        };
361
    }
362

            
363
    let rows = query.fetch_all(&mut *conn).await?;
364

            
365
    let target = ConversionTarget {
366
        commodity_id: target_commodity_id,
367
        symbol: target_symbol,
368
    };
369
    let mut amounts = AccountAmounts::new();
370
    for r in &rows {
371
        let account_id: Uuid = r.get("account_id");
372
        let commodity_id: Uuid = r.get("commodity_id");
373
        let value_num: i64 = r.get("value_num");
374
        let value_denom: i64 = r.get("value_denom");
375
        let commodity_symbol: String = r.get("commodity_symbol");
376
        let price_num: Option<i64> = r.get("price_num");
377
        let price_denom: Option<i64> = r.get("price_denom");
378
        accumulate_split_converted(
379
            &mut amounts,
380
            account_id,
381
            commodity_id,
382
            Rational64::new(value_num, value_denom),
383
            &commodity_symbol,
384
            &target,
385
            (price_num, price_denom),
386
        )
387
        .map_err(|e| CmdError::Finance(FinanceError::Report(e)))?;
388
    }
389
    Ok(amounts)
390
}