From 1ea4fdf8072d6be99a13cc796c9775c05f8c9498 Mon Sep 17 00:00:00 2001 From: Sebastian Reichel Date: Sun, 13 Sep 2015 23:05:18 +0200 Subject: Add support for aliased EANs Some products are sold under different EANs (e.g. per-country EANs). The alias table can be used to map multiple EANs to a single product. --- src/database/database.vala | 43 +++++++++++++++++ src/database/db-interface.vala | 8 ++++ src/scanner-session/scannersession.vala | 1 + src/web/web.vala | 84 +++++++++++++++++++++++++++++++++ 4 files changed, 136 insertions(+) (limited to 'src') diff --git a/src/database/database.vala b/src/database/database.vala index f0b90c1..b4c2e7c 100644 --- a/src/database/database.vala +++ b/src/database/database.vala @@ -129,6 +129,9 @@ public class DataBase : Object { queries["cashbox_add"] = "INSERT INTO cashbox_diff ('user', 'amount', 'timestamp') VALUES (?, ?, ?)"; queries["cashbox_history"] = "SELECT user, amount, timestamp FROM cashbox_diff ORDER BY timestamp DESC LIMIT 10"; queries["cashbox_changes"] = "SELECT user, amount, timestamp FROM cashbox_diff WHERE timestamp >= ? and timestamp < ? ORDER BY timestamp ASC"; + queries["alias_ean_add"] = "INSERT OR IGNORE INTO ean_aliases (id, real_ean) VALUES (?, ?)"; + queries["alias_ean_get"] = "SELECT real_ean FROM ean_aliases WHERE id = ?"; + queries["alias_ean_list"] = "SELECT id, real_ean FROM ean_aliases ORDER BY id ASC"; /* compile queries into statements */ foreach(var entry in queries.entries) { @@ -969,4 +972,44 @@ public class DataBase : Object { return result; } + + public void ean_alias_add(uint64 ean, uint64 real_ean) throws DatabaseError { + statements["alias_ean_add"].reset(); + statements["alias_ean_add"].bind_text(1, "%llu".printf(ean)); + statements["alias_ean_add"].bind_text(2, "%llu".printf(real_ean)); + + int rc = statements["alias_ean_add"].step(); + + if(rc != Sqlite.DONE) { + throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc); + } + } + + public uint64 ean_alias_get(uint64 ean) { + uint64 result = ean; + + statements["alias_ean_get"].reset(); + statements["alias_ean_get"].bind_text(1, "%llu".printf(ean)); + + if(statements["alias_ean_get"].step() == Sqlite.ROW) + result = statements["alias_ean_get"].column_int64(0); + + return result; + } + + public EanAlias[] ean_alias_list() { + EanAlias[] result = {}; + + statements["alias_ean_list"].reset(); + + while(statements["alias_ean_list"].step() == Sqlite.ROW) { + EanAlias entry = { + statements["alias_ean_list"].column_int64(0), + statements["alias_ean_list"].column_int64(1), + }; + + result += entry; + }; + return result; + } } diff --git a/src/database/db-interface.vala b/src/database/db-interface.vala index b61cba7..d469743 100644 --- a/src/database/db-interface.vala +++ b/src/database/db-interface.vala @@ -55,6 +55,9 @@ public interface Database : Object { public abstract void cashbox_add(int user, Price amount, int64 timestamp) throws IOError, DatabaseError; public abstract CashboxDiff[] cashbox_history() throws IOError; public abstract CashboxDiff[] cashbox_changes(int64 start, int64 stop) throws IOError; + 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 struct StockEntry { @@ -141,6 +144,11 @@ public struct CashboxDiff { public int64 timestamp; } +public struct EanAlias { + public uint64 ean; + public uint64 real_ean; +} + public struct StatsInfo { public int count_articles; public int count_users; diff --git a/src/scanner-session/scannersession.vala b/src/scanner-session/scannersession.vala index 002b7db..f3357af 100644 --- a/src/scanner-session/scannersession.vala +++ b/src/scanner-session/scannersession.vala @@ -169,6 +169,7 @@ public class ScannerSessionImplementation { string name = "unknown product"; try { + id = db.ean_alias_get(id); name = db.get_product_name(id); } catch(IOError e) { audio.play_user(theme, "error"); diff --git a/src/web/web.vala b/src/web/web.vala index a8ab59f..698e2c3 100644 --- a/src/web/web.vala +++ b/src/web/web.vala @@ -767,6 +767,87 @@ public class WebServer { } } + void handler_alias_list(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("aliases/index.html", l); + t.replace("TITLE", "KtT Shop System: Alias List"); + t.menu_set_active("aliases"); + + string table = ""; + foreach(var e in db.ean_alias_list()) { + var productname = db.get_product_name(e.real_ean); + table += @"$(e.ean)$(e.real_ean)$(productname)"; + } + + t.replace("DATA", table); + + if(l.superuser || l.auth_products) + t.replace("NEWALIAS", "block"); + else + t.replace("NEWALIAS", "none"); + + 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_alias_new(Soup.Server server, Soup.Message msg, string path, GLib.HashTable? query, Soup.ClientContext client) { + try { + var session = new WebSession(server, msg, path, query, client); + var template = new WebTemplate("aliases/new.html", session); + template.replace("TITLE", "KtT Shop System: New Alias"); + template.menu_set_active("aliases"); + + if(!session.superuser && !session.auth_products) { + handler_403(server, msg, path, query, client); + return; + } + + if(query != null && query.contains("ean") && query.contains("real_ean")) { + var ean = uint64.parse(query["ean"]); + var real_ean = uint64.parse(query["real_ean"]); + + if(ean > 0 && real_ean > 0) { + db.ean_alias_add(ean, real_ean); + template.replace("EAN", @"$ean"); + template.replace("REAL_EAN", @"$real_ean"); + template.replace("NEW.OK", "block"); + template.replace("NEW.FAIL", "none"); + } else { + template.replace("EAN", "virtual ean"); + template.replace("REAL_EAN", "real ean"); + template.replace("NEW.OK", "none"); + template.replace("NEW.FAIL", "block"); + } + } else { + template.replace("EAN", "virtual ean"); + template.replace("REAL_EAN", "real ean"); + template.replace("NEW.OK", "none"); + template.replace("NEW.FAIL", "block"); + } + + msg.set_response("text/html", Soup.MemoryUse.COPY, template.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); + } + } + #if 0 void handler_stats(Soup.Server server, Soup.Message msg, string path, GLib.HashTable? query, Soup.ClientContext client) { try { @@ -1206,6 +1287,9 @@ public class WebServer { srv.add_handler("/products", handler_products); srv.add_handler("/products/new", handler_products_new); + srv.add_handler("/aliases", handler_alias_list); + srv.add_handler("/aliases/new", handler_alias_new); + #if 0 /* stats */ srv.add_handler("/stats", handler_stats); -- cgit v1.2.3