From eb51bdbfef8c2aacf0fdfde279a40de7f74c8d86 Mon Sep 17 00:00:00 2001
From: Zach Berwaldt <zberwaldt@tutamail.com>
Date: Mon, 18 Mar 2024 21:27:24 -0400
Subject: clean up, add better error handling

---
 db/README.md       | 10 +++++++++-
 db/scripts/init.sh |  6 ++----
 2 files changed, 11 insertions(+), 5 deletions(-)

(limited to 'db')

diff --git a/db/README.md b/db/README.md
index f36e555..1d5ad71 100644
--- a/db/README.md
+++ b/db/README.md
@@ -1,3 +1,11 @@
 # The Database
 
-This document describes how to set up the database for your instance of the water application.
\ No newline at end of file
+This document describes how to set up the database for your instance of the water application.
+
+## Setup
+
+The first step is to decide where you are going to keep your database. Once you have decided on where that is going to be add it to your environment:
+
+```sh
+export DB_PATH="path/to/database"
+```
\ No newline at end of file
diff --git a/db/scripts/init.sh b/db/scripts/init.sh
index 1a8bbde..3baec36 100644
--- a/db/scripts/init.sh
+++ b/db/scripts/init.sh
@@ -1,10 +1,8 @@
 PROJECT_DIR=$(pwd)
 
-DB_PATH="$PROJECT_DIR/db/test.sqlite3"
-
-SQL_DIR="$PROJECT_DIR/db/sql"
-
+DB_PATH="$PROJECT_DIR/test.sqlite3"
 
+SQL_DIR="$PROJECT_DIR/sql"
 
 insert_user() {
     read -p "Enter a username: " username
-- 
cgit v1.1


From fd6f6f169f9ff9a1247228fb34dc9654a9584915 Mon Sep 17 00:00:00 2001
From: Zach Berwaldt <zberwaldt@tutamail.com>
Date: Thu, 21 Mar 2024 11:23:42 -0400
Subject: fix bugs, redo layout, reorg.

---
 db/sql/views.sql | 6 +++---
 1 file changed, 3 insertions(+), 3 deletions(-)

(limited to 'db')

diff --git a/db/sql/views.sql b/db/sql/views.sql
index c56286d..3ca2cf9 100644
--- a/db/sql/views.sql
+++ b/db/sql/views.sql
@@ -4,7 +4,7 @@ CREATE VIEW IF NOT EXISTS aggregated_stats AS
 CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS
 SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color
 FROM users
-LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day')
+LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now')
 LEFT JOIN preferences ON users.id = preferences.user_id
 GROUP BY users.name;
 
@@ -12,11 +12,11 @@ GROUP BY users.name;
 CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS
   WITH DateSequence(Dates) AS
     (
-     SELECT Date(CURRENT_DATE, '-7 day')
+     SELECT Date(CURRENT_DATE, '-6 day')
      UNION ALL
      SELECT Date(Dates, '+1 day')
      FROM DateSequence
-     WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE)
+     WHERE Date(Dates, '+1 day') <= Date(CURRENT_DATE)
     )
 SELECT DateSequence.Dates as 'date',
   IFNULL(SUM(statistics.quantity), 0) AS 'total'
-- 
cgit v1.1