From 186049b3ed33f025eeb87eb34c19a28e1d5ba70a Mon Sep 17 00:00:00 2001
From: Sebastian Reichel <sre@ring0.de>
Date: Tue, 2 Oct 2012 01:05:51 +0200
Subject: restructure code, switch from GTK to Web based UI

 - move barcode generation scripts into generation/
 - move code to src/
 - remove database analysis from invoice/graph
 - put database creation sql files into sql/
 - remove glade builder file
 - add new templates/ directory, which contains files
   used by the Web-UI
---
 sql/views.sql | 22 ++++++++++++++++++++++
 1 file changed, 22 insertions(+)
 create mode 100644 sql/views.sql

(limited to 'sql/views.sql')

diff --git a/sql/views.sql b/sql/views.sql
new file mode 100644
index 0000000..6ab5ef2
--- /dev/null
+++ b/sql/views.sql
@@ -0,0 +1,22 @@
+BEGIN TRANSACTION;
+CREATE VIEW IF NOT EXISTS purchaseprices AS SELECT product, SUM(price * amount) / SUM(amount) AS price FROM restock GROUP BY product;
+CREATE VIEW IF NOT EXISTS invoice AS 
+	SELECT user, timestamp, id AS productid, name AS productname,
+		CASE
+			WHEN user < 0 THEN
+				(SELECT price
+					FROM purchaseprices
+					WHERE purchaseprices.product = id)
+			else
+				(SELECT
+					CASE
+						WHEN user=0 THEN guestprice
+						else memberprice
+					END
+					FROM prices
+					WHERE product = id AND valid_from <= timestamp
+					ORDER BY valid_from DESC LIMIT 1)
+			END AS price
+		FROM sells INNER JOIN products ON sells.product = products.id
+		ORDER BY timestamp;
+COMMIT;
-- 
cgit v1.2.3

