diff options
Diffstat (limited to 'db/scripts/water_init.sql')
-rw-r--r-- | db/scripts/water_init.sql | 27 |
1 files changed, 26 insertions, 1 deletions
diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql index 6a4de24..3b79ed5 100644 --- a/db/scripts/water_init.sql +++ b/db/scripts/water_init.sql | |||
@@ -18,7 +18,7 @@ CREATE TABLE IF NOT EXISTS Statistics ( | |||
18 | CREATE TABLE IF NOT EXISTS Preferences ( | 18 | CREATE TABLE IF NOT EXISTS Preferences ( |
19 | id INTEGER PRIMARY KEY, | 19 | id INTEGER PRIMARY KEY, |
20 | color TEXT NOT NULL DEFAULT "#000000", | 20 | color TEXT NOT NULL DEFAULT "#000000", |
21 | user_id INT NOT NULL, | 21 | user_id INT UNIQUE NOT NULL, |
22 | size_id INT NOT NULL DEFAULT 1, | 22 | size_id INT NOT NULL DEFAULT 1, |
23 | FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE, | 23 | FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE, |
24 | FOREIGN KEY(size_id) REFERENCES Sizes(id) | 24 | FOREIGN KEY(size_id) REFERENCES Sizes(id) |
@@ -70,3 +70,28 @@ END; | |||
70 | -- | 70 | -- |
71 | CREATE VIEW IF NOT EXISTS aggregated_stats AS | 71 | CREATE VIEW IF NOT EXISTS aggregated_stats AS |
72 | SELECT u.uuid, SUM(s.quantity * s.size) from Statistics s INNER JOIN Users u ON u.id = s.user_id INNER JOIN Preferences p ON p.user_id = u.id INNER JOIN Size s ON s.id = p.size_id; | 72 | SELECT u.uuid, SUM(s.quantity * s.size) from Statistics s INNER JOIN Users u ON u.id = s.user_id INNER JOIN Preferences p ON p.user_id = u.id INNER JOIN Size s ON s.id = p.size_id; |
73 | |||
74 | CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS | ||
75 | SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color | ||
76 | FROM users | ||
77 | LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day') | ||
78 | LEFT JOIN preferences ON users.id = preferences.user_id | ||
79 | GROUP BY users.name; | ||
80 | |||
81 | |||
82 | CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS | ||
83 | WITH DateSequence(Dates) AS | ||
84 | ( | ||
85 | SELECT Date(CURRENT_DATE, '-7 day') | ||
86 | UNION ALL | ||
87 | SELECT Date(Dates, '+1 day') | ||
88 | FROM DateSequence | ||
89 | WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE) | ||
90 | ) | ||
91 | SELECT DateSequence.Dates as 'date', | ||
92 | IFNULL(SUM(statistics.quantity), 0) AS 'total' | ||
93 | FROM DateSequence | ||
94 | LEFT JOIN statistics | ||
95 | ON Date(statistics.date) = DateSequence.Dates | ||
96 | GROUP BY DateSequence.Dates | ||
97 | ORDER BY DateSequence.Dates; \ No newline at end of file | ||