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.

4 files changed, 136 insertions, 0 deletions

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 += @"<tr><td>$(e.ean)</td><td><a href=\"/products/$(e.real_ean)\">$(e.real_ean)</a></td><td><a href=\"/products/$(e.real_ean)\">$(productname)</a></td></tr>";
+			}
+
+			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<string,string>? 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);