From 025fcee561ef9e94580ddf6652bfd1437c3f2524 Mon Sep 17 00:00:00 2001 From: Sebastian Reichel Date: Tue, 15 Sep 2015 00:42:50 +0200 Subject: web: new product list sorted by best before date --- TODO.md | 1 - src/database/database.vala | 62 ++++++++++++++++++++++++++++++++------ src/database/db-interface.vala | 10 +++++- src/web/web.vala | 36 +++++++++++++++++++++- templates/products/bestbefore.html | 8 +++++ templates/products/index.html | 2 ++ 6 files changed, 106 insertions(+), 13 deletions(-) create mode 100644 templates/products/bestbefore.html 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(); + + 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 += @"$(e.ean)$(e.name)$(e.amount)$(bbd)"; + } + + 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 @@ + + + + + + {{{DATA}}} + +
EANNameAmountBest Before Date
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 @@ +product list with best before dates + -- cgit v1.2.3
EANNameAmountMemberpriceGuestprice