From 186049b3ed33f025eeb87eb34c19a28e1d5ba70a Mon Sep 17 00:00:00 2001 From: Sebastian Reichel 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