From 8504754a40d47b69b0aa1e786f0b1db45405a3fd Mon Sep 17 00:00:00 2001 From: Sebastian Reichel Date: Sat, 15 Jun 2013 22:29:34 +0200 Subject: cashbox tracking --- sql/tables.sql | 1 + sql/views.sql | 17 +++++++++++++++++ 2 files changed, 18 insertions(+) (limited to 'sql') diff --git a/sql/tables.sql b/sql/tables.sql index 91a36d1..4a40d4d 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -6,5 +6,6 @@ CREATE TABLE IF NOT EXISTS prices (product INTEGER NOT NULL REFERENCES products, 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, pgp 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 0, disabled BOOLEAN NOT NULL DEFAULT 0); CREATE TABLE IF NOT EXISTS supplier(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, city TEXT, postal_code TEXT, street TEXT, phone TEXT, website TEXT); +CREATE TABLE IF NOT EXISTS cashbox_diff(id INTEGER PRIMARY KEY AUTOINCREMENT, user INTEGER NOT NULL REFERENCES users, amount INTEGER NOT NULL, timestamp INTEGER NOT NULL DEFAULT 0); CREATE INDEX IF NOT EXISTS invoiceindex ON sales (user ASC, timestamp DESC); COMMIT; diff --git a/sql/views.sql b/sql/views.sql index 4b138fe..d40ac32 100644 --- a/sql/views.sql +++ b/sql/views.sql @@ -20,4 +20,21 @@ CREATE VIEW IF NOT EXISTS invoice AS END AS price FROM sales INNER JOIN products ON sales.product = products.id ORDER BY timestamp; +CREATE VIEW IF NOT EXISTS current_cashbox_status AS + SELECT ( + ( + SELECT SUM( + ( + SELECT guestprice + FROM prices + WHERE product = s.product AND valid_from <= s.timestamp + ORDER BY valid_from DESC LIMIT 1 + ) + ) FROM sales s WHERE user = 0 + ) + + + ( + SELECT SUM(amount) FROM cashbox_diff + ) + ) AS amount; COMMIT; -- cgit v1.2.3