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.

9 files changed, 169 insertions, 1 deletions

diff --git a/TODO.md b/TODO.md
index 41c62fd..ce23adf 100644
--- a/TODO.md
+++ b/TODO.md
@@ -4,7 +4,6 @@
  * support "make install"
 
 #### CORE
- * add EAN alias table
  * disallow buying for disabled users
  * support user discounts
  * remove hardcoded stuff from invoice and pdf-invoice
diff --git a/sql/tables.sql b/sql/tables.sql
index a81d60c..ac9a8c2 100644
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -7,5 +7,6 @@ CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY NOT NULL, email TEXT, f
 CREATE TABLE IF NOT EXISTS authentication(user INTEGER PRIMARY KEY NOT NULL REFERENCES users, password TEXT, session CHARACTER(20), superuser BOOLEAN NOT NULL DEFAULT 0, auth_users BOOLEAN NOT NULL DEFAULT 0, auth_products BOOLEAN NOT NULL DEFAULT 0, auth_cashbox BOOLEAN NOT NULL DEFAULT 0, disabled BOOLEAN NOT NULL DEFAULT 0);
 CREATE TABLE IF NOT EXISTS supplier(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, city TEXT, postal_code TEXT, street TEXT, phone TEXT, website TEXT);
 CREATE TABLE IF NOT EXISTS cashbox_diff(id INTEGER PRIMARY KEY AUTOINCREMENT, user INTEGER NOT NULL REFERENCES users, amount INTEGER NOT NULL, timestamp INTEGER NOT NULL DEFAULT 0);
+CREATE TABLE IF NOT EXISTS ean_aliases (id INTEGER PRIMARY KEY NOT NULL, real_ean INTEGER NOT NULL REFERENCES products);
 CREATE INDEX IF NOT EXISTS invoiceindex ON sales (user ASC, timestamp DESC);
 COMMIT;
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);
diff --git a/templates/aliases/index.html b/templates/aliases/index.html
new file mode 100644
index 0000000..c914968
--- /dev/null
+++ b/templates/aliases/index.html
@@ -0,0 +1,17 @@
+<table class="sortable table table-bordered table-striped table-condensed">
+	<thead>
+		<tr><th>EAN</th><th>Real EAN</th></th><th>Name</th></tr>
+	</thead>
+	<tbody>
+		{{{DATA}}}
+	</tbody>
+</table>
+
+<div id="newalias" style="display: {{{NEWALIAS}}};">
+	<form action="/aliases/new" class="form-horizontal">
+		<legend>New Alias</legend>
+		<input class="input-medium" name="ean" type="number" min="0" placeholder="EAN" />
+		<input class="input-medium" name="real_ean" type="number" min="0" placeholder="Real EAN" />
+		<button type="submit" class="btn btn-primary"><i class="icon-plus"></i></button>
+	</form>
+</div>
diff --git a/templates/aliases/new.html b/templates/aliases/new.html
new file mode 100644
index 0000000..34b610e
--- /dev/null
+++ b/templates/aliases/new.html
@@ -0,0 +1,14 @@
+<h2>Add new product alias</h2>
+
+<div id="new-successful" class="alert alert-success" style="display: {{{NEW.OK}}};">
+	<p>Successfully created new alias: {{{EAN}}} ➜ {{{REAL_EAN}}}</p>
+</div>
+
+<div id="new-failed" class="alert alert-error" style="display: {{{NEW.FAIL}}};">
+	<h4>Error</h4>
+	Creating new product alias failed. Please make sure
+	you have sufficient permissions and gave valid values
+	for EAN and real EAN.
+</div>
+
+<a href=".">Back to alias list</a>
diff --git a/templates/menu.html b/templates/menu.html
index d92d778..764068a 100644
--- a/templates/menu.html
+++ b/templates/menu.html
@@ -4,6 +4,7 @@
 	<ul class="nav">
 		<li class="{{{MENU.home}}}"><a href="/">Home</a></li>
 		<li class="{{{MENU.products}}}"><a href="/products">Products</a></li>
+		<li class="{{{MENU.aliases}}}"><a href="/aliases">Aliases</a></li>
 		<li class="{{{MENU.cashbox}}} {{{AUTH_CASHBOX}}}"><a href="/cashbox">Cashbox</a></li>
 <!--
 		<li class="{{{MENU.stats}}} dropdown">