diff options
author | Sebastian Reichel <sre@ring0.de> | 2012-10-02 01:05:51 +0200 |
---|---|---|
committer | Sebastian Reichel <sre@ring0.de> | 2012-10-02 01:05:51 +0200 |
commit | 186049b3ed33f025eeb87eb34c19a28e1d5ba70a (patch) | |
tree | 5d892564001404fe979e18eac0e65dfcad65ed5e /sql/views.sql | |
parent | 9713c98dbceb54d8d00c186ba8f41f3a5befcfd1 (diff) | |
download | serial-barcode-scanner-186049b3ed33f025eeb87eb34c19a28e1d5ba70a.tar.bz2 |
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
Diffstat (limited to 'sql/views.sql')
-rw-r--r-- | sql/views.sql | 22 |
1 files changed, 22 insertions, 0 deletions
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; |