diff options
| -rw-r--r-- | sql/tables.sql | 2 | ||||
| -rw-r--r-- | sql/views.sql | 1 | ||||
| -rw-r--r-- | src/database/database.vala | 2 |
3 files changed, 3 insertions, 2 deletions
diff --git a/sql/tables.sql b/sql/tables.sql index 194f4f3..91a36d1 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -1,5 +1,5 @@ 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 products (id INTEGER PRIMARY KEY NOT NULL, name TEXT, amount INTEGER NOT NULL DEFAULT 0, deprecated BOOLEAN NOT NULL DEFAULT 0); CREATE TABLE IF NOT EXISTS sales (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, supplier INTEGER, best_before_date INTEGER); 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); diff --git a/sql/views.sql b/sql/views.sql index 8edfa9f..4b138fe 100644 --- a/sql/views.sql +++ b/sql/views.sql @@ -1,4 +1,5 @@ BEGIN TRANSACTION; +CREATE VIEW IF NOT EXISTS stock AS SELECT id, name, amount FROM products WHERE deprecated = 0 OR amount != 0; 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, diff --git a/src/database/database.vala b/src/database/database.vala index b9db150..05c789d 100644 --- a/src/database/database.vala +++ b/src/database/database.vala @@ -95,7 +95,7 @@ public class DataBase : Object { queries["restocks"] = "SELECT timestamp, amount, price, supplier, best_before_date FROM restock WHERE product = ? ORDER BY timestamp ASC;"; queries["profit_complex"] = "SELECT SUM(memberprice - (SELECT price FROM purchaseprices WHERE product = purch.product)) FROM sales purch, prices WHERE purch.product = prices.product AND purch.user > 0 AND purch.timestamp > ? AND purch.timestamp < ? AND prices.valid_from = (SELECT valid_from FROM prices WHERE product = purch.product AND valid_from < purch.timestamp ORDER BY valid_from DESC LIMIT 1);"; queries["sales_complex"] = "SELECT SUM(memberprice) FROM sales purch, prices WHERE purch.product = prices.product AND purch.user > 0 AND purch.timestamp > ? AND purch.timestamp < ? AND prices.valid_from = (SELECT valid_from FROM prices WHERE product = purch.product AND valid_from < purch.timestamp ORDER BY valid_from DESC LIMIT 1);"; - queries["stock_status"] = "SELECT id, name, amount, memberprice, guestprice FROM products, prices WHERE products.id = prices.product AND prices.valid_from = (SELECT valid_from FROM prices WHERE product = products.id ORDER BY valid_from DESC LIMIT 1) ORDER BY name"; + queries["stock_status"] = "SELECT id, name, amount, memberprice, guestprice FROM stock, prices WHERE stock.id = prices.product AND prices.valid_from = (SELECT valid_from FROM prices WHERE product = stock.id ORDER BY valid_from DESC LIMIT 1) ORDER BY name"; queries["stock_amount"] = "SELECT timestamp, amount FROM restock WHERE product = ? UNION ALL SELECT timestamp, -1 AS amount FROM sales WHERE product = ? ORDER BY timestamp DESC"; queries["session_set"] = "UPDATE authentication SET session=? WHERE user = ?"; queries["session_get"] = "SELECT user FROM authentication WHERE session = ?"; |