From dbcace5e7de23a48e8686171c7a412a950acc764 Mon Sep 17 00:00:00 2001 From: Sebastian Reichel Date: Wed, 28 Oct 2015 05:16:52 +0100 Subject: add product category --- src/database/database.vala | 48 ++++++++++++++++++++++++++++++++++++++++------ 1 file changed, 42 insertions(+), 6 deletions(-) (limited to 'src/database/database.vala') diff --git a/src/database/database.vala b/src/database/database.vala index eab88b6..5df63b6 100644 --- a/src/database/database.vala +++ b/src/database/database.vala @@ -82,6 +82,7 @@ public class DataBase : Object { /* setup queries */ queries["product_name"] = "SELECT name FROM products WHERE id = ?"; + queries["product_category"] = "SELECT categories.name FROM categories, products WHERE products.category = categories.id AND products.id = ?"; queries["product_amount"] = "SELECT amount FROM products WHERE id = ?"; queries["product_deprecated"]= "SELECT deprecated FROM products WHERE id = ?"; queries["product_set_deprecated"] = "UPDATE products SET deprecated=? WHERE id = ?"; @@ -89,7 +90,7 @@ public class DataBase : Object { queries["purchase"] = "INSERT INTO sales ('user', 'product', 'timestamp') VALUES (?, ?, ?)"; queries["last_purchase"] = "SELECT product FROM sales WHERE user = ? ORDER BY timestamp DESC LIMIT 1"; queries["undo"] = "DELETE FROM sales WHERE user = ? ORDER BY timestamp DESC LIMIT 1"; - queries["product_create"] = "INSERT INTO products ('id', 'name', 'amount') VALUES (?, ?, ?)"; + queries["product_create"] = "INSERT INTO products ('id', 'name', 'category', 'amount') VALUES (?, ?, ?, ?)"; queries["price_create"] = "INSERT INTO prices ('product', 'valid_from', 'memberprice', 'guestprice') VALUES (?, ?, ?, ?)"; queries["stock"] = "INSERT INTO restock ('user', 'product', 'amount', 'price', 'timestamp', 'supplier', 'best_before_date') VALUES (?, ?, ?, ?, ?, ?, ?)"; queries["price"] = "SELECT memberprice, guestprice FROM prices WHERE product = ? AND valid_from <= ? ORDER BY valid_from DESC LIMIT 1"; @@ -98,7 +99,7 @@ public class DataBase : Object { queries["restocks_desc"] = "SELECT timestamp, amount, price, supplier, best_before_date FROM restock WHERE product = ? ORDER BY timestamp DESC;"; 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 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_status"] = "SELECT stock.id, stock.name, categories.name, amount, memberprice, guestprice FROM stock, prices, categories WHERE stock.id = prices.product AND categories.id = stock.category AND prices.valid_from = (SELECT valid_from FROM prices WHERE product = stock.id ORDER BY valid_from DESC LIMIT 1) ORDER BY categories.name, stock.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 = ?"; @@ -121,6 +122,7 @@ public class DataBase : Object { queries["user_auth_create"] = "INSERT OR IGNORE INTO authentication (user) VALUES (?)"; queries["user_disable"] = "UPDATE authentication SET disabled = ? WHERE user = ?"; queries["last_timestamp"] = "SELECT timestamp FROM sales ORDER BY timestamp DESC LIMIT 1"; + queries["category_list"] = "SELECT id, name FROM categories"; queries["supplier_list"] = "SELECT id, name, postal_code, city, street, phone, website FROM supplier"; queries["supplier_get"] = "SELECT id, name, postal_code, city, street, phone, website FROM supplier WHERE id = ?"; queries["supplier_add"] = "INSERT INTO supplier('name', 'postal_code', 'city', 'street', 'phone', 'website') VALUES (?, ?, ?, ?, ?, ?)"; @@ -276,9 +278,10 @@ public class DataBase : Object { StockEntry entry = { statements["stock_status"].column_text(0), statements["stock_status"].column_text(1), - statements["stock_status"].column_int(2), + statements["stock_status"].column_text(2), statements["stock_status"].column_int(3), - statements["stock_status"].column_int(4) + statements["stock_status"].column_int(4), + statements["stock_status"].column_int(5) }; result += entry; @@ -361,6 +364,22 @@ public class DataBase : Object { } } + public string get_product_category(uint64 article) throws DatabaseError { + statements["product_category"].reset(); + statements["product_category"].bind_text(1, "%llu".printf(article)); + + int rc = statements["product_category"].step(); + + switch(rc) { + case Sqlite.ROW: + return statements["product_category"].column_text(0); + case Sqlite.DONE: + throw new DatabaseError.PRODUCT_NOT_FOUND("unknown product: %llu", article); + default: + throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc); + } + } + public int get_product_amount(uint64 article) throws DatabaseError { statements["product_amount"].reset(); statements["product_amount"].bind_text(1, "%llu".printf(article)); @@ -484,11 +503,12 @@ public class DataBase : Object { throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc); } - public void new_product(uint64 id, string name, int memberprice, int guestprice) throws DatabaseError { + public void new_product(uint64 id, string name, int category, int memberprice, int guestprice) throws DatabaseError { statements["product_create"].reset(); statements["product_create"].bind_text(1, @"$id"); statements["product_create"].bind_text(2, name); - statements["product_create"].bind_int(3, 0); + statements["product_create"].bind_int(3, category); + statements["product_create"].bind_int(4, 0); int rc = statements["product_create"].step(); if(rc != Sqlite.DONE) { @@ -824,6 +844,22 @@ public class DataBase : Object { return statements["last_timestamp"].column_int64(0); } + public Category[] get_category_list() { + Category[] result = {}; + + statements["category_list"].reset(); + while(statements["category_list"].step() == Sqlite.ROW) { + Category entry = { + statements["category_list"].column_int(0), + statements["category_list"].column_text(1) + }; + + result += entry; + } + + return result; + } + public Supplier[] get_supplier_list() { Supplier[] result = {}; -- cgit v1.2.3