initial support for suppliers & best before dates

5 files changed, 137 insertions, 11 deletions

diff --git a/sql/tables.sql b/sql/tables.sql
index 2e1fc03..f42e927 100644
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -1,8 +1,9 @@
 BEGIN TRANSACTION;
 CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY NOT NULL, name TEXT, amount INTEGER NOT NULL DEFAULT 0);
 CREATE TABLE IF NOT EXISTS sales (user INTEGER NOT NULL REFERENCES users, product INTEGER NOT NULL REFERENCES products, timestamp INTEGER NOT NULL DEFAULT 0);
-CREATE TABLE IF NOT EXISTS restock (user INTEGER NOT NULL REFERENCES users, product INTEGER NOT NULL REFERENCES products, amount INTEGER NOT NULL DEFAULT 0, timestamp INTEGER NOT NULL DEFAULT 0, price INTEGER NOT NULL DEFAULT 0);
+CREATE TABLE IF NOT EXISTS restock (user INTEGER NOT NULL REFERENCES users, product INTEGER NOT NULL REFERENCES products, amount INTEGER NOT NULL DEFAULT 0, timestamp INTEGER NOT NULL DEFAULT 0, price INTEGER NOT NULL DEFAULT 0, supplier INTEGER, best_before_date INTEGER);
 CREATE TABLE IF NOT EXISTS prices (product INTEGER NOT NULL REFERENCES products, valid_from INTEGER NOT NULL DEFAULT 0, memberprice INTEGER NOT NULL DEFAULT 0, guestprice INTEGER NOT NULL DEFAULT 0);
 CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY NOT NULL, email TEXT, firstname TEXT NOT NULL, lastname TEXT NOT NULL, gender TEXT, street TEXT, plz INTEGER, city TEXT, pgp TEXT);
 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, 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);
 COMMIT;
diff --git a/src/db.vala b/src/db.vala
index 1e81aad..dc1c834 100644
--- a/src/db.vala
+++ b/src/db.vala
@@ -33,6 +33,18 @@ public struct RestockEntry {
 	public int64 timestamp;
 	public int amount;
 	public string price;
+	public int supplier;
+	public int64 best_before_date;
+}
+
+public struct Supplier {
+	public int64 id;
+	public string name;
+	public string postal_code;
+	public string city;
+	public string street;
+	public string phone;
+	public string website;
 }
 
 public struct UserInfo {
@@ -125,6 +137,10 @@ public class Database {
 			return stmt.step();
 		}
 
+		public int bind_null(int index) {
+			return stmt.bind_null(index);
+		}
+
 		public int bind_int(int index, int value) {
 			return stmt.bind_int(index, value);
 		}
@@ -171,10 +187,10 @@ public class Database {
 		queries["undo"]              = "DELETE FROM sales WHERE user = ? ORDER BY timestamp DESC LIMIT 1";
 		queries["product_create"]    = "INSERT INTO products ('id', 'name', 'amount') VALUES (?, ?, ?)";
 		queries["price_create"]      = "INSERT INTO prices ('product', 'valid_from', 'memberprice', 'guestprice') VALUES (?, ?, ?, ?)";
-		queries["stock"]             = "INSERT INTO restock ('user', 'product', 'amount', 'price', 'timestamp') VALUES (?, ?, ?, ?, ?)";
+		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 FROM restock WHERE product = ? ORDER BY timestamp ASC;";
+		queries["restocks"]          = "SELECT timestamp, amount, price, supplier, best_before_date FROM restock WHERE product = ? ORDER BY timestamp ASC;";
 		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 products, prices WHERE products.id = prices.product AND prices.valid_from = (SELECT valid_from FROM prices WHERE product = products.id ORDER BY valid_from DESC LIMIT 1) ORDER BY name";
@@ -200,6 +216,9 @@ public class Database {
 		queries["user_auth_create"]  = "INSERT OR IGNORE INTO authentication (user) VALUES (?)";
 		queries["user_disable"]      = "UPDATE authentication SET disabled = ? WHERE user = ?";
 		queries["last_timestamp"]    = "SELECT timestamp FROM sales ORDER BY timestamp DESC LIMIT 1";
+		queries["supplier_list"]     = "SELECT id, name, postal_code, city, street, phone, website FROM supplier";
+		queries["supplier_get"]      = "SELECT id, name, postal_code, city, street, phone, website FROM supplier WHERE id = ?";
+		queries["supplier_add"]      = "INSERT INTO supplier('name', 'postal_code', 'city', 'street', 'phone', 'website') VALUES (?, ?, ?, ?, ?, ?)";
 
 		/* compile queries into statements */
 		foreach(var entry in queries.entries) {
@@ -378,6 +397,8 @@ public class Database {
 
 			Price p = statements["restocks"].column_int(2);
 			entry.price = @"$p";
+			entry.supplier = statements["restocks"].column_int(3);
+			entry.best_before_date = statements["restocks"].column_int64(4);
 
 			result.add(entry);
 		}
@@ -491,7 +512,7 @@ public class Database {
 		return true;
 	}
 
-	public bool restock(int user, uint64 product, uint amount, uint price) {
+	public bool restock(int user, uint64 product, uint amount, uint price, int supplier, int64 best_before_date) {
 		if(user > 0) {
 			int rc = 0;
 			int64 timestamp = (new DateTime.now_utc()).to_unix();
@@ -502,6 +523,14 @@ public class Database {
 			statements["stock"].bind_text(3, @"$amount");
 			statements["stock"].bind_text(4, @"$price");
 			statements["stock"].bind_int64(5, timestamp);
+			if(supplier > 0)
+				statements["stock"].bind_int(6, supplier);
+			else
+				statements["stock"].bind_null(6);
+			if(best_before_date > 0)
+				statements["stock"].bind_int64(7, best_before_date);
+			else
+				statements["stock"].bind_null(7);
 
 			rc = statements["stock"].step();
 			if(rc != Sqlite.DONE)
@@ -821,4 +850,70 @@ public class Database {
 			return 0;
 		return statements["last_timestamp"].column_int64(0);
 	}
+
+	public Gee.List<Supplier?> get_supplier_list() {
+		var result = new Gee.ArrayList<Supplier?>();
+		statements["supplier_list"].reset();
+
+		while(statements["supplier_list"].step() == Sqlite.ROW) {
+			Supplier entry = {
+				statements["supplier_list"].column_int64(0),
+				statements["supplier_list"].column_text(1),
+				statements["supplier_list"].column_text(2),
+				statements["supplier_list"].column_text(3),
+				statements["supplier_list"].column_text(4),
+				statements["supplier_list"].column_text(5),
+				statements["supplier_list"].column_text(6)
+			};
+
+			result.add(entry);
+		}
+
+		return result;
+	}
+
+	public Supplier get_supplier(int id) {
+		Supplier result = Supplier();
+
+		statements["supplier_get"].reset();
+		statements["supplier_get"].bind_int(1, id);
+
+		if(statements["supplier_get"].step() != Sqlite.ROW) {
+			result.id = 0;
+			result.name = "Unknown";
+			result.postal_code = "";
+			result.city = "";
+			result.street = "";
+			result.phone = "";
+			result.website = "";
+		} else {
+			result.id = statements["supplier_get"].column_int64(0);
+			result.name = statements["supplier_get"].column_text(1);
+			result.postal_code = statements["supplier_get"].column_text(2);
+			result.city = statements["supplier_get"].column_text(3);
+			result.street = statements["supplier_get"].column_text(4);
+			result.phone = statements["supplier_get"].column_text(5);
+			result.website = statements["supplier_get"].column_text(6);
+		}
+
+		return result;
+	}
+
+	public bool add_supplier(string name, string postal_code, string city, string street, string phone, string website) {
+		statements["supplier_add"].reset();
+		statements["supplier_add"].bind_text(1, name);
+		statements["supplier_add"].bind_text(2, postal_code);
+		statements["supplier_add"].bind_text(3, city);
+		statements["supplier_add"].bind_text(4, street);
+		statements["supplier_add"].bind_text(5, phone);
+		statements["supplier_add"].bind_text(6, website);
+		int rc = statements["supplier_add"].step();
+
+		if(rc != Sqlite.DONE) {
+			warning("[internal error: %d]".printf(rc));
+			return false;
+		}
+
+		return true;
+	}
 }
diff --git a/src/web.vala b/src/web.vala
index 0820bc8..81b8e67 100644
--- a/src/web.vala
+++ b/src/web.vala
@@ -501,6 +501,13 @@ public class WebServer {
 			}
 			t.replace("RESTOCKS", restocks);
 
+			/* suppliers */
+			string suppliers = "<option value=\"0\">Unknown</option>";
+			foreach(var e in db.get_supplier_list()) {
+				suppliers += "<option value=\"%lld\">%s</option>".printf(e.id, e.name);
+			}
+			t.replace("SUPPLIERS", suppliers);
+
 			msg.set_response("text/html", Soup.MemoryUse.COPY, t.data);
 		} catch(TemplateError e) {
 			stderr.printf(e.message+"\n");
@@ -567,12 +574,24 @@ public class WebServer {
 
 			if(query != null && query.contains("amount") && query.contains("price")) {
 				int amount = int.parse(query["amount"]);
-				Price price =  Price.parse(query["price"]);
+				int supplier = int.parse(query["supplier"]);
+				string best_before_date = query["best_before_date"];
+				Price price = Price.parse(query["price"]);
+				DateTime bbd;
+
+				var dateparts = best_before_date.split("-");
+				if(dateparts.length == 3) {
+					bbd = new DateTime.local(int.parse(dateparts[0]), int.parse(dateparts[1]), int.parse(dateparts[2]), 0, 0, 0);
+				} else {
+					bbd = new DateTime.from_unix_local(0);
+				}
 
 				if(amount >= 1 && price >= 1) {
-					if(db.restock(session.user, id, amount, price)) {
+					if(db.restock(session.user, id, amount, price, supplier, bbd.to_unix())) {
 						template.replace("AMOUNT", @"$amount");
 						template.replace("PRICE", @"$price");
+						template.replace("BESTBEFORE", bbd.format("%Y-%m-%d"));
+						template.replace("SUPPLIER", db.get_supplier(supplier).name);
 						template.replace("RESTOCK.OK", "block");
 						template.replace("RESTOCK.FAIL", "none");
 						msg.set_response("text/html", Soup.MemoryUse.COPY, template.data);
diff --git a/templates/products/entry.html b/templates/products/entry.html
index ffb0dcf..0cdd4a6 100644
--- a/templates/products/entry.html
+++ b/templates/products/entry.html
@@ -29,8 +29,12 @@
 
 <div id="restock" style="display: {{{ISADMIN}}};">
 	<form action="/products/{{{EAN}}}/restock" class="form-horizontal">
-		<input class="input-small" name="amount" type="number" min="1" placeholder="Amount" />
-		<input class="input-small" name="price"  type="number" step="0.01" min="0.01" placeholder="Price" />
+		<input class="input-small" name="amount" type="number" min="1" placeholder="Amount" title="Amount" />
+		<input class="input-small" name="price"  type="number" step="0.01" min="0.01" placeholder="Price" title="Price per Piece" />
+		<select name="supplier" title="Supplier">
+			{{{SUPPLIERS}}}
+		</select>
+		<input class="input-small" name="best_before_date"  type="date" title="Best Before Date (YYYY-MM-DD)" />
 		<button type="submit" class="btn btn-primary"><i class="icon-plus"></i></button>
 	</form>
 </div>
diff --git a/templates/products/restock.html b/templates/products/restock.html
index c314b29..70926c5 100644
--- a/templates/products/restock.html
+++ b/templates/products/restock.html
@@ -1,8 +1,15 @@
 <h2>Restock: {{{NAME}}}</h2>
 
-<div id="restock-successful" class="alert alert-success" style="display: {{{RESTOCK.OK}}};">
-	Successfully restocked <b>{{{AMOUNT}}}</b> items of
-	product <b>{{{NAME}}}</b>. Price per item: <b>{{{PRICE}}}</b>.
+<div id="restock-successful" class="alert" style="display: {{{RESTOCK.OK}}};">
+	<p>Successfully restocked:</p>
+
+	<table class="table table-hover">
+		<tr><th>Name</th><td>{{{NAME}}}</td></tr>
+		<tr><th>Amount</th><td>{{{AMOUNT}}}</td></tr>
+		<tr><th>Price</th><td>{{{PRICE}}}</td></tr>
+		<tr><th>Supplier</th><td>{{{SUPPLIER}}}</td></tr>
+		<tr><th>Best Before Date</th><td>{{{BESTBEFORE}}}</td></tr>
+	</table>
 </div>
 
 <div id="restock-failed" class="alert alert-error" style="display: {{{RESTOCK.FAIL}}};">