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/tables.sql  |  8 ++++++++
 sql/trigger.sql | 27 +++++++++++++++++++++++++++
 sql/views.sql   | 22 ++++++++++++++++++++++
 3 files changed, 57 insertions(+)
 create mode 100644 sql/tables.sql
 create mode 100644 sql/trigger.sql
 create mode 100644 sql/views.sql

(limited to 'sql')

diff --git a/sql/tables.sql b/sql/tables.sql
new file mode 100644
index 0000000..c4a43d4
--- /dev/null
+++ b/sql/tables.sql
@@ -0,0 +1,8 @@
+BEGIN TRANSACTION;
+CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY NOT NULL, name TEXT, amount INTEGER NOT NULL DEFAULT 0);
+CREATE TABLE IF NOT EXISTS sells (user INTEGER NOT NULL REFERENCES users, product INTEGER NOT NULL REFERENCES products, timestamp INTEGER NOT NULL DEFAULT 0);
+CREATE TABLE IF NOT EXISTS restock (user INTEGER NOT NULL REFERENCES users, product INTEGER NOT NULL REFERENCES products, amount INTEGER NOT NULL DEFAULT 0, timestamp INTEGER NOT NULL DEFAULT 0, price INTEGER NOT NULL DEFAULT 0);
+CREATE TABLE IF NOT EXISTS prices (product INTEGER NOT NULL REFERENCES products, valid_from INTEGER NOT NULL DEFAULT 0, memberprice INTEGER NOT NULL DEFAULT 0, guestprice INTEGER NOT NULL DEFAULT 0);
+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);
+CREATE TABLE IF NOT EXISTS authentication(user INTEGER PRIMARY KEY NOT NULL REFERENCES users, password TEXT, session CHARACTER(20), superuser BOOLEAN NOT NULL DEFAULT false, disabled BOOLEAN NOT NULL DEFAULT false);
+COMMIT;
diff --git a/sql/trigger.sql b/sql/trigger.sql
new file mode 100644
index 0000000..8a9bede
--- /dev/null
+++ b/sql/trigger.sql
@@ -0,0 +1,27 @@
+BEGIN TRANSACTION;
+CREATE TRIGGER IF NOT EXISTS update_product_amount_on_restock_insert AFTER INSERT ON restock BEGIN
+	UPDATE products SET amount = products.amount + NEW.amount WHERE products.id = NEW.product;
+END;
+
+CREATE TRIGGER IF NOT EXISTS update_product_amount_on_restock_delete AFTER DELETE ON restock BEGIN
+	UPDATE products SET amount = products.amount - OLD.amount WHERE products.id = OLD.product;
+END;
+
+CREATE TRIGGER IF NOT EXISTS update_product_amount_on_restock_update AFTER UPDATE ON restock BEGIN
+	UPDATE products SET amount = products.amount - OLD.amount WHERE products.id = OLD.product;
+	UPDATE products SET amount = products.amount + NEW.amount WHERE products.id = NEW.product;
+END;
+
+CREATE TRIGGER IF NOT EXISTS update_product_amount_on_sells_insert AFTER INSERT ON sells BEGIN
+	UPDATE products SET amount = products.amount - 1 WHERE products.id = NEW.product;
+END;
+
+CREATE TRIGGER IF NOT EXISTS update_product_amount_on_sells_delete AFTER DELETE ON sells BEGIN
+	UPDATE products SET amount = products.amount + 1 WHERE products.id = OLD.product;
+END;
+
+CREATE TRIGGER IF NOT EXISTS update_product_amount_on_sells_update AFTER UPDATE ON sells BEGIN
+	UPDATE products SET amount = products.amount + 1 WHERE products.id = OLD.product;
+	UPDATE products SET amount = products.amount - 1 WHERE products.id = NEW.product;
+END;
+COMMIT;
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