cashbox tracking

2 files changed, 18 insertions, 0 deletions

diff --git a/sql/tables.sql b/sql/tables.sql
index 91a36d1..4a40d4d 100644
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -6,5 +6,6 @@ CREATE TABLE IF NOT EXISTS prices (product INTEGER NOT NULL REFERENCES products,
 CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY NOT NULL, email TEXT, firstname TEXT NOT NULL, lastname TEXT NOT NULL, gender TEXT, street TEXT, plz INTEGER, city TEXT, pgp TEXT);
 CREATE TABLE IF NOT EXISTS authentication(user INTEGER PRIMARY KEY NOT NULL REFERENCES users, password TEXT, session CHARACTER(20), superuser BOOLEAN NOT NULL DEFAULT 0, disabled BOOLEAN NOT NULL DEFAULT 0);
 CREATE TABLE IF NOT EXISTS supplier(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, city TEXT, postal_code TEXT, street TEXT, phone TEXT, website TEXT);
+CREATE TABLE IF NOT EXISTS cashbox_diff(id INTEGER PRIMARY KEY AUTOINCREMENT, user INTEGER NOT NULL REFERENCES users, amount INTEGER NOT NULL, timestamp INTEGER NOT NULL DEFAULT 0);
 CREATE INDEX IF NOT EXISTS invoiceindex ON sales (user ASC, timestamp DESC);
 COMMIT;
diff --git a/sql/views.sql b/sql/views.sql
index 4b138fe..d40ac32 100644
--- a/sql/views.sql
+++ b/sql/views.sql
@@ -20,4 +20,21 @@ CREATE VIEW IF NOT EXISTS invoice AS
 			END AS price
 		FROM sales INNER JOIN products ON sales.product = products.id
 		ORDER BY timestamp;
+CREATE VIEW IF NOT EXISTS current_cashbox_status AS
+	SELECT (
+		(
+			SELECT SUM(
+				(
+					SELECT guestprice
+						FROM prices
+						WHERE product = s.product AND valid_from <= s.timestamp
+						ORDER BY valid_from DESC LIMIT 1
+				)
+			) FROM sales s WHERE user = 0
+		)
+		+
+		(
+			SELECT SUM(amount) FROM cashbox_diff
+		)
+	) AS amount;
 COMMIT;