From 8504754a40d47b69b0aa1e786f0b1db45405a3fd Mon Sep 17 00:00:00 2001
From: Sebastian Reichel <sre@ring0.de>
Date: Sat, 15 Jun 2013 22:29:34 +0200
Subject: cashbox tracking

---
 sql/views.sql | 17 +++++++++++++++++
 1 file changed, 17 insertions(+)

(limited to 'sql/views.sql')

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;
-- 
cgit v1.2.3

