add product category

3 files changed, 65 insertions, 10 deletions

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 = {};
 
diff --git a/src/database/db-interface.vala b/src/database/db-interface.vala
index c8f56e8..f49a627 100644
--- a/src/database/db-interface.vala
+++ b/src/database/db-interface.vala
@@ -20,13 +20,14 @@ public interface Database : Object {
 	public abstract RestockEntry[] get_restocks(uint64 product, bool descending) throws IOError;
 	public abstract bool buy(int32 user, uint64 article) throws IOError, DatabaseError;
 	public abstract string get_product_name(uint64 article) throws IOError, DatabaseError;
+	public abstract string get_product_category(uint64 article) throws IOError, DatabaseError;
 	public abstract int get_product_amount(uint64 article) throws IOError, DatabaseError;
 	public abstract bool get_product_deprecated(uint64 article) throws IOError, DatabaseError;
 	public abstract void product_deprecate(uint64 article, bool value) throws IOError, DatabaseError;
 	public abstract Price get_product_price(int user, uint64 article) throws IOError, DatabaseError;
 	public abstract string undo(int32 user) throws IOError, DatabaseError;
 	public abstract void restock(int user, uint64 product, uint amount, uint price, int supplier, int64 best_before_date) throws IOError, DatabaseError;
-	public abstract void new_product(uint64 id, string name, int memberprice, int guestprice) throws IOError, DatabaseError;
+	public abstract void new_product(uint64 id, string name, int category, int memberprice, int guestprice) throws IOError, DatabaseError;
 	public abstract void new_price(uint64 product, int64 timestamp, int memberprice, int guestprice) throws IOError, DatabaseError;
 	public abstract bool check_user_password(int32 user, string password) throws IOError;
 	public abstract void set_user_password(int32 user, string password) throws IOError, DatabaseError;
@@ -46,6 +47,7 @@ public interface Database : Object {
 	public abstract bool user_exists(int user) throws IOError, DatabaseError;
 	public abstract bool user_equals(UserInfo u) throws IOError, DatabaseError;
 	public abstract int64 get_timestamp_of_last_purchase() throws IOError;
+	public abstract Category[] get_category_list() throws IOError;
 	public abstract Supplier[] get_supplier_list() throws IOError;
 	public abstract Supplier get_supplier(int id) throws IOError;
 	public abstract void add_supplier(string name, string postal_code, string city, string street, string phone, string website) throws IOError, DatabaseError;
@@ -61,9 +63,15 @@ public interface Database : Object {
 	public abstract BestBeforeEntry[] bestbeforelist() throws IOError;
 }
 
+public struct Category {
+	public int id;
+	public string name;
+}
+
 public struct StockEntry {
 	public string id;
 	public string name;
+	public string category;
 	public int amount;
 	public Price memberprice;
 	public Price guestprice;
diff --git a/src/web/web.vala b/src/web/web.vala
index e80ee7e..30dbcf8 100644
--- a/src/web/web.vala
+++ b/src/web/web.vala
@@ -489,11 +489,17 @@ public class WebServer {
 
 			string table = "";
 			foreach(var e in db.get_stock()) {
-				table += @"<tr><td><a href=\"/products/$(e.id)\">$(e.id)</a></td><td><a href=\"/products/$(e.id)\">$(e.name)</a></td><td>$(e.amount)</td><td>$(e.memberprice)€</td><td>$(e.guestprice)€</td></tr>";
+				table += @"<tr><td><a href=\"/products/$(e.id)\">$(e.id)</a></td><td><a href=\"/products/$(e.id)\">$(e.name)</a></td><td>$(e.category)</td><td>$(e.amount)</td><td>$(e.memberprice)€</td><td>$(e.guestprice)€</td></tr>";
 			}
 
 			t.replace("DATA", table);
 
+			string categories = "";
+			foreach(var c in db.get_category_list()) {
+				categories += "<option value=\"%lld\">%s</option>".printf(c.id, c.name);
+			}
+			t.replace("CATEGORIES", categories);
+
 			if(l.superuser || l.auth_products)
 				t.replace("NEWPRODUCT", "block");
 			else
@@ -584,6 +590,10 @@ public class WebServer {
 			string name = db.get_product_name(id);
 			t.replace("NAME", name);
 
+			/* category */
+			string category = db.get_product_category(id);
+			t.replace("CATEGORY", category);
+
 			/* amount */
 			t.replace("AMOUNT", "%d".printf(db.get_product_amount(id)));
 
@@ -663,11 +673,12 @@ public class WebServer {
 			if(query != null && query.contains("name") && query.contains("id") && query.contains("memberprice") && query.contains("guestprice")) {
 				var name = query["name"];
 				var ean = uint64.parse(query["id"]);
+				int category = int.parse(query["category"]);
 				Price memberprice = Price.parse(query["memberprice"]);
 				Price guestprice  = Price.parse(query["guestprice"]);
 
-				if(ean > 0 && memberprice > 0 && guestprice > 0) {
-					db.new_product(ean, name, memberprice, guestprice);
+				if(ean > 0 && memberprice > 0 && guestprice > 0 && category >= 0) {
+					db.new_product(ean, name, category, memberprice, guestprice);
 					template.replace("NAME", name);
 					template.replace("EAN", @"$ean");
 					template.replace("MEMBERPRICE", @"$memberprice€");