add product category

7 files changed, 73 insertions, 13 deletions

diff --git a/sql/tables.sql b/sql/tables.sql
index ac9a8c2..211cda3 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, deprecated BOOLEAN NOT NULL DEFAULT 0);
+CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY NOT NULL, name TEXT, amount INTEGER NOT NULL DEFAULT 0, category INTEGER REFERENCES categories, 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);
@@ -8,5 +8,6 @@ CREATE TABLE IF NOT EXISTS authentication(user INTEGER PRIMARY KEY NOT NULL REFE
 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 TABLE IF NOT EXISTS ean_aliases (id INTEGER PRIMARY KEY NOT NULL, real_ean INTEGER NOT NULL REFERENCES products);
+CREATE TABLE IF NOT EXISTS categories (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
 CREATE INDEX IF NOT EXISTS invoiceindex ON sales (user ASC, timestamp DESC);
 COMMIT;
diff --git a/sql/views.sql b/sql/views.sql
index 4c0a56b..94cbabd 100644
--- a/sql/views.sql
+++ b/sql/views.sql
@@ -1,5 +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 stock AS SELECT id, name, category, 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 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€");
diff --git a/templates/products/entry.html b/templates/products/entry.html
index 80c1535..83d98b8 100644
--- a/templates/products/entry.html
+++ b/templates/products/entry.html
@@ -3,6 +3,7 @@
 	<table class="table table-bordered table-nonfluid">
 		<th>EAN</th><td>{{{EAN}}}</td></tr>
 		<th>Name</th><td>{{{NAME}}}</td></tr>
+		<th>Category</th><td>{{{CATEGORY}}}</td></tr>
 		<th>Amount</th><td>{{{AMOUNT}}}</td></tr>
 		<th>State</th><td><button id="statebutton" type="button" class="btn {{{BTNSTATE}}}" {{{ISADMIN2}}}>{{{STATE}}}</button></td></tr>
 	</table>
diff --git a/templates/products/index.html b/templates/products/index.html
index 4f9bed3..d80adbd 100644
--- a/templates/products/index.html
+++ b/templates/products/index.html
@@ -2,7 +2,7 @@
 
 <table class="sortable table table-bordered table-striped table-condensed">
 	<thead>
-		<tr><th>EAN</th></th><th>Name</th><th>Amount</th><th>Memberprice</th><th>Guestprice</th></tr>
+		<tr><th>EAN</th></th><th>Name</th><th>Category</th><th>Amount</th><th>Memberprice</th><th>Guestprice</th></tr>
 	</thead>
 	<tbody>
 		{{{DATA}}}
@@ -14,6 +14,9 @@
 		<legend>New Product</legend>
 		<input class="input-medium" name="id" type="number" min="0" placeholder="EAN" />
 		<input class="input-medium" name="name" type="text" placeholder="Name" />
+		<select name="category" title="Category">
+			{{{CATEGORIES}}}
+		</select>
 		<input class="input-medium" name="memberprice" type="number" step="0.01" min="0.01" placeholder="Memberprice" />
 		<input class="input-medium" name="guestprice"  type="number" step="0.01" min="0.01" placeholder="Guestprice" />
 		<button type="submit" class="btn btn-primary"><i class="icon-plus"></i></button>