diff options
| -rw-r--r-- | sql/tables.sql | 1 | ||||
| -rw-r--r-- | sql/views.sql | 17 | ||||
| -rw-r--r-- | src/database/database.vala | 27 | ||||
| -rw-r--r-- | src/database/db-interface.vala | 2 |
4 files changed, 47 insertions, 0 deletions
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; diff --git a/src/database/database.vala b/src/database/database.vala index 05c789d..8c48588 100644 --- a/src/database/database.vala +++ b/src/database/database.vala @@ -123,6 +123,8 @@ public class DataBase : Object { queries["supplier_add"] = "INSERT INTO supplier('name', 'postal_code', 'city', 'street', 'phone', 'website') VALUES (?, ?, ?, ?, ?, ?)"; queries["users_with_sales"] = "SELECT user FROM sales WHERE timestamp > ? AND timestamp < ? GROUP BY user"; queries["user_invoice_sum"] = "SELECT SUM(price) FROM invoice WHERE user = ? AND timestamp > ? AND timestamp < ?"; + queries["cashbox_status"] = "SELECT amount FROM current_cashbox_status"; + queries["cashbox_add"] = "INSERT INTO cashbox_diff ('user', 'amount', 'timestamp') VALUES (?, ?, ?)"; /* compile queries into statements */ foreach(var entry in queries.entries) { @@ -867,4 +869,29 @@ public class DataBase : Object { return result; } + + public Price cashbox_status() { + Price result = 0; + + statements["cashbox_status"].reset(); + + if(statements["cashbox_status"].step() == Sqlite.ROW) + result = statements["cashbox_status"].column_int(0); + + return result; + } + + public void cashbox_add(int user, Price amount, int64 timestamp) throws DatabaseError { + statements["cashbox_add"].reset(); + statements["cashbox_add"].bind_int(1, user); + statements["cashbox_add"].bind_int(2, amount); + statements["cashbox_add"].bind_int64(3, timestamp); + + int rc = statements["cashbox_add"].step(); + + if(rc != Sqlite.DONE) { + throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc); + } + + } } diff --git a/src/database/db-interface.vala b/src/database/db-interface.vala index 610dff7..6ec77eb 100644 --- a/src/database/db-interface.vala +++ b/src/database/db-interface.vala @@ -49,6 +49,8 @@ public interface Database : Object { public abstract void add_supplier(string name, string postal_code, string city, string street, string phone, string website) throws IOError, DatabaseError; public abstract int[] get_users_with_sales(int64 timestamp_from, int64 timestamp_to) throws IOError; public abstract Price get_user_invoice_sum(int user, int64 timestamp_from, int64 timestamp_to) throws IOError; + public abstract Price cashbox_status() throws IOError; + public abstract void cashbox_add(int user, Price amount, int64 timestamp) throws IOError, DatabaseError; } public struct StockEntry { |