1 files changed, 494 insertions, 0 deletions
diff --git a/src/db/incomes.rs b/src/db/incomes.rs
new file mode 100644
index 0000000..cbbfce7
--- /dev/null
+++ b/src/db/incomes.rs
@@ -0,0 +1,494 @@
+use chrono::NaiveDate;
+use sqlx::error::Error;
+use sqlx::sqlite::{SqlitePool, SqliteRow};
+use sqlx_core::row::Row;
+use std::collections::HashMap;
+use std::iter::FromIterator;
+
+use crate::model::income::{Create, Form, Stat, Table, Update};
+use crate::model::report::Report;
+
+pub async fn count(pool: &SqlitePool) -> i64 {
+    let query = r#"
+SELECT
+    COUNT(*) AS count
+FROM
+    incomes
+WHERE
+    incomes.deleted_at IS NULL
+        "#;
+
+    let res = sqlx::query(&query)
+        .map(|row: SqliteRow| row.get("count"))
+        .fetch_one(pool)
+        .await;
+
+    match res {
+        Ok(count) => count,
+        Err(err) => {
+            error!("Error counting incomes: {:?}", err);
+            0
+        }
+    }
+}
+
+pub async fn list(pool: &SqlitePool, page: i64, per_page: i64) -> Vec<Table> {
+    let query = r#"
+SELECT
+    incomes.id,
+    users.name AS user,
+    strftime('%m/%Y', incomes.date) AS date,
+    incomes.amount
+FROM
+    incomes
+INNER JOIN
+    users
+ON
+    incomes.user_id = users.id
+WHERE
+    incomes.deleted_at IS NULL
+ORDER BY
+    incomes.date DESC
+LIMIT ?
+OFFSET ?
+    "#;
+
+    let res = sqlx::query_as::<_, Table>(query)
+        .bind(per_page)
+        .bind((page - 1) * per_page)
+        .fetch_all(pool)
+        .await;
+
+    match res {
+        Ok(incomes) => incomes,
+        Err(err) => {
+            error!("Error listing incomes: {:?}", err);
+            vec![]
+        }
+    }
+}
+
+pub async fn get_row(pool: &SqlitePool, id: i64) -> i64 {
+    let query = r#"
+SELECT
+    row
+FROM (
+    SELECT 
+        ROW_NUMBER () OVER (ORDER BY date DESC) AS row,
+        id
+    FROM
+        incomes
+    WHERE
+        deleted_at IS NULL
+)
+WHERE
+    id = ?
+    "#;
+
+    let res = sqlx::query(query)
+        .bind(id)
+        .map(|row: SqliteRow| row.get("row"))
+        .fetch_one(pool)
+        .await;
+
+    match res {
+        Ok(count) => count,
+        Err(err) => {
+            error!("Error getting income row: {:?}", err);
+            1
+        }
+    }
+}
+
+pub async fn get(pool: &SqlitePool, id: i64) -> Option<Form> {
+    let query = r#"
+SELECT
+    id,
+    amount,
+    user_id,
+    CAST(strftime('%m', date) AS INTEGER) as month,
+    CAST(strftime('%Y', date) AS INTEGER) as year
+FROM
+    incomes
+WHERE
+    id = ?
+    AND deleted_at IS NULL
+    "#;
+
+    let res = sqlx::query_as::<_, Form>(query)
+        .bind(id)
+        .fetch_one(pool)
+        .await;
+
+    match res {
+        Ok(p) => Some(p),
+        Err(err) => {
+            error!("Error looking for income {}: {:?}", id, err);
+            None
+        }
+    }
+}
+
+pub async fn create(pool: &SqlitePool, i: &Create) -> Option<i64> {
+    let res = sqlx::query(
+        r#"
+INSERT INTO
+    incomes(user_id, date, amount, created_at)
+VALUES
+    (?, ?, ?, datetime())
+    "#,
+    )
+    .bind(i.user_id)
+    .bind(NaiveDate::from_ymd(i.year, i.month, 1))
+    .bind(i.amount)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(x) => Some(x.last_insert_rowid()),
+        Err(err) => {
+            error!("Error creating income: {:?}", err);
+            None
+        }
+    }
+}
+
+pub async fn defined_at(
+    pool: &SqlitePool,
+    user_id: i64,
+    month: u32,
+    year: i32,
+) -> Vec<i64> {
+    let query = r#"
+SELECT
+    id
+FROM
+    incomes
+WHERE
+    user_id = ?
+    AND date = ?
+    AND deleted_at IS NULL
+    "#;
+
+    let res = sqlx::query(&query)
+        .bind(user_id)
+        .bind(NaiveDate::from_ymd(year, month, 1))
+        .map(|row: SqliteRow| row.get("id"))
+        .fetch_all(pool)
+        .await;
+
+    match res {
+        Ok(ids) => ids,
+        Err(Error::RowNotFound) => vec![],
+        Err(err) => {
+            error!("Error looking if income is defined: {:?}", err);
+            vec![]
+        }
+    }
+}
+
+pub async fn update(pool: &SqlitePool, id: i64, i: &Update) -> bool {
+    let res = sqlx::query(
+        r#"
+UPDATE
+    incomes
+SET
+    user_id = ?,
+    date = ?,
+    amount = ?,
+    updated_at = datetime()
+WHERE
+    id = ?
+    "#,
+    )
+    .bind(i.user_id)
+    .bind(NaiveDate::from_ymd(i.year, i.month, 1))
+    .bind(i.amount)
+    .bind(id)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            error!("Error updating income {}: {:?}", id, err);
+            false
+        }
+    }
+}
+
+pub async fn delete(pool: &SqlitePool, id: i64) -> bool {
+    let res = sqlx::query(
+        r#"
+UPDATE
+    incomes
+SET
+    deleted_at = datetime()
+WHERE
+    id = ?
+    "#,
+    )
+    .bind(id)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            error!("Error deleting income {}: {:?}", id, err);
+            false
+        }
+    }
+}
+
+pub async fn defined_for_all(pool: &SqlitePool) -> Option<NaiveDate> {
+    let res = sqlx::query(
+        r#"
+SELECT
+    (CASE COUNT(users.id) == COUNT(min_income.date)
+        WHEN 1 THEN MIN(min_income.date)
+        ELSE NULL
+    END) AS date
+FROM
+    users
+LEFT OUTER JOIN (
+    SELECT
+        user_id,
+        MIN(date) AS date
+    FROM
+        incomes
+    WHERE
+        deleted_at IS NULL
+    GROUP BY
+        user_id
+) min_income
+ON
+    users.id = min_income.user_id;
+    "#,
+    )
+    .map(|row: SqliteRow| row.get("date"))
+    .fetch_one(pool)
+    .await;
+
+    match res {
+        Ok(d) => d,
+        Err(err) => {
+            error!("Error looking for incomes defined for all: {:?}", err);
+            None
+        }
+    }
+}
+
+pub async fn cumulative(
+    pool: &SqlitePool,
+    from: NaiveDate,
+) -> HashMap<i64, i64> {
+    let res = sqlx::query(&cumulative_query(from))
+        .map(|row: SqliteRow| (row.get("user_id"), row.get("income")))
+        .fetch_all(pool)
+        .await;
+
+    match res {
+        Ok(incomes) => HashMap::from_iter(incomes),
+        Err(err) => {
+            error!("Error computing cumulative income: {:?}", err);
+            HashMap::new()
+        }
+    }
+}
+
+/// Select cumulative income of users from the given date and until now.
+///
+/// Associate each month income to its start and end bounds,
+/// then compute the total income of each period,
+/// sum it to get the final result.
+///
+/// Considering each month to be 365 / 12 days long.
+fn cumulative_query(from: NaiveDate) -> String {
+    format!(
+        r#"
+SELECT
+    users.id AS user_id,
+    COALESCE(incomes.income, 0) AS income
+FROM
+    users
+LEFT OUTER JOIN (
+    SELECT
+        user_id,
+        CAST(ROUND(SUM(count)) AS INTEGER) AS income
+    FROM (
+        SELECT
+            I1.user_id,
+            ((JULIANDAY(MIN(I2.date)) - JULIANDAY(I1.date)) * I1.amount * 12 / 365) AS count
+        FROM
+            ({}) AS I1
+        INNER JOIN
+            ({}) AS I2
+        ON
+            I2.date > I1.date
+            AND I2.user_id == I1.user_id
+        GROUP BY
+            I1.date, I1.user_id
+    )
+    GROUP BY
+        user_id
+) incomes
+ON
+    users.id = incomes.user_id
+    "#,
+        bounded_query(">".to_string(), from.format("%Y-%m-%d").to_string()),
+        bounded_query("<".to_string(), "date()".to_string())
+    )
+}
+
+/// Select bounded incomes to the operator and date.
+///
+/// It filters incomes according to the operator and date,
+/// and adds the income at this date.
+fn bounded_query(op: String, date: String) -> String {
+    format!(
+        r#"
+SELECT
+    user_id,
+    date,
+    amount
+FROM (
+    SELECT
+        user_id,
+        {} AS date,
+        amount,
+        MAX(date) AS max_date
+    FROM
+        incomes
+    WHERE
+        date <= {}
+        AND deleted_at IS NULL
+    GROUP BY
+        user_id
+) UNION
+SELECT
+    user_id,
+    date,
+    amount
+FROM
+    incomes
+WHERE
+    date {} {}
+    AND deleted_at IS NULL
+    "#,
+        date, date, op, date
+    )
+}
+
+/// Select total income each month.
+///
+/// For each month, from the first defined income and until now,
+/// compute the total income of the users.
+pub async fn total_each_month(pool: &SqlitePool) -> Vec<Stat> {
+    let query = r#"
+WITH RECURSIVE dates(date) AS (
+    VALUES((
+        SELECT
+            strftime('%Y-%m-01', MIN(date))
+        FROM
+            incomes
+        WHERE
+            deleted_at IS NULL
+    ))
+    UNION ALL
+    SELECT
+        date(date, '+1 month')
+    FROM
+        dates
+    WHERE
+        date < date(date(), '-1 month')
+)
+SELECT
+    strftime('%Y-%m-01', dates.date) AS date,
+    (
+        SELECT
+            SUM(amount) AS amount
+        FROM (
+            SELECT (
+                SELECT
+                    amount
+                FROM
+                    incomes
+                WHERE
+                    user_id = users.id
+                    AND date < date(dates.date, '+1 month')
+                    AND deleted_at IS NULL
+                ORDER BY
+                    date DESC
+                LIMIT
+                    1
+            ) AS amount
+            FROM
+                users
+        )
+    ) AS amount
+FROM
+    dates;
+    "#;
+
+    let res = sqlx::query_as::<_, Stat>(query).fetch_all(pool).await;
+
+    match res {
+        Ok(xs) => xs,
+        Err(err) => {
+            error!("Error listing incomes for statistics: {:?}", err);
+            vec![]
+        }
+    }
+}
+
+pub async fn last_week(pool: &SqlitePool) -> Vec<Report> {
+    let query = r#"
+SELECT
+    strftime('%m/%Y', incomes.date) AS date,
+    users.name AS name,
+    incomes.amount AS amount,
+    (CASE
+        WHEN
+            incomes.deleted_at IS NOT NULL
+        THEN
+            'Deleted'
+        WHEN
+            incomes.updated_at IS NOT NULL
+            AND incomes.created_at < date('now', 'weekday 0', '-13 days')
+        THEN
+            'Updated'
+        ELSE
+            'Created'
+    END) AS action
+FROM
+    incomes
+INNER JOIN
+    users
+ON
+    incomes.user_id = users.id
+WHERE
+    (
+        incomes.created_at >= date('now', 'weekday 0', '-13 days')
+        AND incomes.created_at < date('now', 'weekday 0', '-6 days')
+    ) OR (
+        incomes.updated_at >= date('now', 'weekday 0', '-13 days')
+        AND incomes.updated_at < date('now', 'weekday 0', '-6 days')
+    ) OR (
+        incomes.deleted_at >= date('now', 'weekday 0', '-13 days')
+        AND incomes.deleted_at < date('now', 'weekday 0', '-6 days')
+    )
+ORDER BY
+    incomes.date
+    "#;
+
+    let res = sqlx::query_as::<_, Report>(query).fetch_all(pool).await;
+
+    match res {
+        Ok(payments) => payments,
+        Err(err) => {
+            error!("Error listing payments for report: {:?}", err);
+            vec![]
+        }
+    }
+}