web: new product list sorted by best before date

2 files changed, 61 insertions, 11 deletions

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;