web: new product list sorted by best before date

6 files changed, 106 insertions, 13 deletions

diff --git a/TODO.md b/TODO.md
index ce23adf..980e2c8 100644
--- a/TODO.md
+++ b/TODO.md
@@ -46,7 +46,6 @@
  * Support generating a barcode userlist (USERLIST-PDF)
  * Support generating product price list (PRODUCTLIST-PDF)
  * Support generating shopping list (PRODUCTLIST-PDF)
- * Support to generate a sorted best before dates list
  * Implement a more fine-grained authentication system
  * OpenID based login
 
diff --git a/src/database/database.vala b/src/database/database.vala
index b4c2e7c..702ad2c 100644
--- a/src/database/database.vala
+++ b/src/database/database.vala
@@ -94,7 +94,8 @@ public class DataBase : Object {
 		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";
 		queries["prices"]            = "SELECT valid_from, memberprice, guestprice FROM prices WHERE product = ? ORDER BY valid_from ASC;";
-		queries["restocks"]          = "SELECT timestamp, amount, price, supplier, best_before_date FROM restock WHERE product = ? ORDER BY timestamp ASC;";
+		queries["restocks_asc"]      = "SELECT timestamp, amount, price, supplier, best_before_date FROM restock WHERE product = ? ORDER BY timestamp ASC;";
+		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";
@@ -304,21 +305,23 @@ public class DataBase : Object {
 		return result;
 	}
 
-	public RestockEntry[] get_restocks(uint64 product) {
+	public RestockEntry[] get_restocks(uint64 product, bool descending) {
 		RestockEntry[] result = {};
 
-		statements["restocks"].reset();
-		statements["restocks"].bind_text(1, "%llu".printf(product));
-		while(statements["restocks"].step() == Sqlite.ROW) {
+		var statement = statements[descending ? "restocks_desc" : "restocks_asc"];
+
+		statement.reset();
+		statement.bind_text(1, "%llu".printf(product));
+		while(statement.step() == Sqlite.ROW) {
 			RestockEntry entry = {
-				statements["restocks"].column_int64(0),
-				statements["restocks"].column_int(1)
+				statement.column_int64(0),
+				statement.column_int(1)
 			};
 
-			Price p = statements["restocks"].column_int(2);
+			Price p = statement.column_int(2);
 			entry.price = @"$p";
-			entry.supplier = statements["restocks"].column_int(3);
-			entry.best_before_date = statements["restocks"].column_int64(4);
+			entry.supplier = statement.column_int(3);
+			entry.best_before_date = statement.column_int64(4);
 
 			result += entry;
 		}
@@ -1012,4 +1015,43 @@ public class DataBase : Object {
 		};
 		return result;
 	}
+
+	public static int sortBestBeforeEntry(BestBeforeEntry? a, BestBeforeEntry? b) {
+		if(a.best_before_date == b.best_before_date)
+			return 0;
+		else if(a.best_before_date < b.best_before_date)
+			return -1;
+		else
+			return 1;
+	}
+
+	public BestBeforeEntry?[] bestbeforelist() {
+		var bbdlist = new GLib.GenericArray<BestBeforeEntry?>();
+
+		foreach(var product in get_stock()) {
+			var amount = product.amount;
+			var pid = uint64.parse(product.id);
+
+			if(amount <= 0)
+				continue;
+
+			foreach(var restock in get_restocks(pid, true)) {
+				if (restock.amount > amount) {
+					BestBeforeEntry entry = { pid, product.name, amount, restock.best_before_date };
+					bbdlist.add(entry);
+				} else {
+					BestBeforeEntry entry = { pid, product.name, restock.amount, restock.best_before_date };
+					bbdlist.add(entry);
+				}
+
+				amount -= restock.amount;
+				if(amount <= 0)
+					break;
+			}
+		}
+
+		bbdlist.sort(sortBestBeforeEntry);
+
+		return bbdlist.data;
+	}
 }
diff --git a/src/database/db-interface.vala b/src/database/db-interface.vala
index d469743..c8f56e8 100644
--- a/src/database/db-interface.vala
+++ b/src/database/db-interface.vala
@@ -17,7 +17,7 @@
 public interface Database : Object {
 	public abstract StockEntry[] get_stock() throws IOError;
 	public abstract PriceEntry[] get_prices(uint64 product) throws IOError;
-	public abstract RestockEntry[] get_restocks(uint64 product) throws IOError;
+	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 int get_product_amount(uint64 article) throws IOError, DatabaseError;
@@ -58,6 +58,7 @@ public interface Database : Object {
 	public abstract void ean_alias_add(uint64 ean, uint64 real_ean) throws IOError, DatabaseError;
 	public abstract uint64 ean_alias_get(uint64 ean) throws IOError;
 	public abstract EanAlias[] ean_alias_list() throws IOError;
+	public abstract BestBeforeEntry[] bestbeforelist() throws IOError;
 }
 
 public struct StockEntry {
@@ -82,6 +83,13 @@ public struct RestockEntry {
 	public int64 best_before_date;
 }
 
+public struct BestBeforeEntry {
+	public uint64 ean;
+	public string name;
+	public int amount;
+	public int64 best_before_date;
+}
+
 public struct Supplier {
 	public int64 id;
 	public string name;
diff --git a/src/web/web.vala b/src/web/web.vala
index 698e2c3..2f3d5b4 100644
--- a/src/web/web.vala
+++ b/src/web/web.vala
@@ -512,6 +512,39 @@ public class WebServer {
 		}
 	}
 
+	void handler_product_bestbefore(Soup.Server server, Soup.Message msg, string path, GLib.HashTable? query, Soup.ClientContext client) {
+		try {
+			var l = new WebSession(server, msg, path, query, client);
+			var t = new WebTemplate("products/bestbefore.html", l);
+			t.replace("TITLE", "KtT Shop System: Best Before List");
+			t.menu_set_active("products");
+
+			string table = "";
+			foreach(var e in db.bestbeforelist()) {
+				string bbd;
+				if(e.best_before_date > 0)
+					bbd = (new DateTime.from_unix_local(e.best_before_date)).format("%Y-%m-%d");
+				else
+					bbd = "";
+
+				table += @"<tr><td><a href=\"/products/$(e.ean)\">$(e.ean)</a></td><td><a href=\"/products/$(e.ean)\">$(e.name)</a></td><td>$(e.amount)</td><td>$(bbd)</td></tr>";
+			}
+
+			t.replace("DATA", table);
+
+			msg.set_response("text/html", Soup.MemoryUse.COPY, t.data);
+		} catch(TemplateError e) {
+			stderr.printf(e.message+"\n");
+			handler_404(server, msg, path, query, client);
+		} catch(DatabaseError e) {
+			stderr.printf(e.message+"\n");
+			handler_400(server, msg, path, query, client);
+		} catch(IOError e) {
+			stderr.printf(e.message+"\n");
+			handler_400(server, msg, path, query, client);
+		}
+	}
+
 	void handler_product_togglestate(Soup.Server server, Soup.Message msg, string path, GLib.HashTable? query, Soup.ClientContext client, uint64 id) {
 		try {
 			var l = new WebSession(server, msg, path, query, client);
@@ -578,7 +611,7 @@ public class WebServer {
 
 			/* restocks */
 			string restocks = "";
-			foreach(var e in db.get_restocks(id)) {
+			foreach(var e in db.get_restocks(id, false)) {
 				var time = new DateTime.from_unix_local(e.timestamp);
 				var supplier = db.get_supplier(e.supplier).name;
 				if(supplier == "Unknown")
@@ -1286,6 +1319,7 @@ public class WebServer {
 		/* products */
 		srv.add_handler("/products", handler_products);
 		srv.add_handler("/products/new", handler_products_new);
+		srv.add_handler("/products/bestbefore", handler_product_bestbefore);
 
 		srv.add_handler("/aliases", handler_alias_list);
 		srv.add_handler("/aliases/new", handler_alias_new);
diff --git a/templates/products/bestbefore.html b/templates/products/bestbefore.html
new file mode 100644
index 0000000..dbc7f47
--- /dev/null
+++ b/templates/products/bestbefore.html
@@ -0,0 +1,8 @@
+<table class="sortable table table-bordered table-striped table-condensed">
+	<thead>
+		<tr><th>EAN</th></th><th>Name</th><th>Amount</th><th>Best Before Date</th></tr>
+	</thead>
+	<tbody>
+		{{{DATA}}}
+	</tbody>
+</table>
diff --git a/templates/products/index.html b/templates/products/index.html
index 0035f7e..4f9bed3 100644
--- a/templates/products/index.html
+++ b/templates/products/index.html
@@ -1,3 +1,5 @@
+<a href="/products/bestbefore">product list with best before dates</a>
+
 <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>