Prepare PGP support

 - add database field for PGP Key ID
 - add PGP Key ID support to CSV import
 - show PGP Key ID in user profile

6 files changed, 14 insertions, 7 deletions

diff --git a/sql/tables.sql b/sql/tables.sql
index 21f007b..2e1fc03 100644
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -3,6 +3,6 @@ CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY NOT NULL, name TEXT,
 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 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);
+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);
 COMMIT;
diff --git a/src/admin.vala b/src/admin.vala
index 37a9703..4e32a0a 100644
--- a/src/admin.vala
+++ b/src/admin.vala
@@ -52,7 +52,7 @@ public class CSVMemberFile {
 	public CSVMemberFile(string data) {
 		foreach(var line in data.split("\n")) {
 			var linedata = csv_split(line);
-			if(linedata.length >= 8) {
+			if(linedata.length >= 9) {
 				var m = UserInfo();
 				m.id = int.parse(csv_value(linedata[0]));
 				m.email = csv_value(linedata[1]);
@@ -62,6 +62,7 @@ public class CSVMemberFile {
 				m.postcode = int.parse(csv_value(linedata[5]));
 				m.city = csv_value(linedata[6]);
 				m.gender = csv_value(linedata[7]) == "m" ? "masculinum" : csv_value(linedata[7]) == "w" ? "femininum" : "unknown";
+				m.pgp = csv_value(linedata[8]);
 				if(csv_value(linedata[0]) != "EXTERNEMITGLIEDSNUMMER")
 					members += m;
 			}
diff --git a/src/db.vala b/src/db.vala
index e3988e4..677fa56 100644
--- a/src/db.vala
+++ b/src/db.vala
@@ -44,6 +44,7 @@ public struct UserInfo {
 	public string street;
 	public int postcode;
 	public string city;
+	public string pgp;
 
 	public bool equals(UserInfo x) {
 		if(id != x.id) return false;
@@ -54,6 +55,7 @@ public struct UserInfo {
 		if(street != x.street) return false;
 		if(postcode != x.postcode) return false;
 		if(city != x.city) return false;
+		if(pgp != x.pgp) return false;
 
 		return true;
 	}
@@ -182,7 +184,7 @@ public class Database {
 		queries["username"]          = "SELECT firstname, lastname FROM users WHERE id = ?";
 		queries["password_get"]      = "SELECT password FROM authentication WHERE user = ?";
 		queries["password_set"]      = "UPDATE authentication SET password=? WHERE user = ?";
-		queries["userinfo"]          = "SELECT firstname, lastname, email, gender, street, plz, city FROM users WHERE id = ?";
+		queries["userinfo"]          = "SELECT firstname, lastname, email, gender, street, plz, city, pgp FROM users WHERE id = ?";
 		queries["userauth"]          = "SELECT disabled, superuser FROM authentication WHERE user = ?";
 		queries["profit_by_product"] = "SELECT name, SUM(memberprice - (SELECT price FROM purchaseprices WHERE product = purch.product)) AS price FROM sales purch, prices, products WHERE purch.product = products.id AND 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) GROUP BY name ORDER BY price;";
 		queries["invoice"]           = "SELECT timestamp, productid, productname, price FROM invoice WHERE user = ? AND timestamp >= ? AND timestamp < ?;";
@@ -194,7 +196,7 @@ public class Database {
 		queries["total_sales"]       = "SELECT SUM(price) FROM invoice WHERE user >= 0 AND timestamp >= ?";
 		queries["total_profit"]      = "SELECT SUM(price - (SELECT price FROM purchaseprices WHERE product = productid)) FROM invoice WHERE user >= 0 AND timestamp >= ?";
 		queries["user_get_ids"]      = "SELECT id FROM users WHERE id > 0";
-		queries["user_replace"]      = "INSERT OR REPLACE INTO users ('id', 'email', 'firstname', 'lastname', 'gender', 'street', 'plz', 'city') VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
+		queries["user_replace"]      = "INSERT OR REPLACE INTO users ('id', 'email', 'firstname', 'lastname', 'gender', 'street', 'plz', 'city', 'pgp') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
 		queries["user_auth_create"]	 = "INSERT OR IGNORE INTO authentication (user) VALUES (?)";
 		queries["user_disable"]		 = "UPDATE authentication SET disabled = ? WHERE user = ?";
 
@@ -609,6 +611,7 @@ public class Database {
 			result.street    = statements["userinfo"].column_text(4);
 			result.postcode  = statements["userinfo"].column_int(5);
 			result.city      = statements["userinfo"].column_text(6);
+			result.pgp       = statements["userinfo"].column_text(7);
 		}
 
 		return result;
@@ -799,6 +802,7 @@ public class Database {
 		statements["user_replace"].bind_text(6, u.street);
 		statements["user_replace"].bind_int(7, u.postcode);
 		statements["user_replace"].bind_text(8, u.city);
+		statements["user_replace"].bind_text(9, u.pgp);
 
 		int rc = statements["user_replace"].step();
 		if(rc != Sqlite.DONE)
diff --git a/src/web.vala b/src/web.vala
index 2bb59f8..14c9604 100644
--- a/src/web.vala
+++ b/src/web.vala
@@ -139,10 +139,10 @@ public class WebServer {
 				foreach(var member in csvimport.get_members()) {
 					if(member.exists_in_db() && !member.equals_db()) {
 						var dbmember = db.get_user_info(member.id);
-						data1 += @"<tr class=\"error\"><td><i class=\"icon-minus-sign\"></i><td>$(dbmember.id)</td><td>$(dbmember.firstname)</td><td>$(dbmember.lastname)</td><td>$(dbmember.email)</td><td>$(dbmember.gender)</td><td>$(dbmember.street)</td><td>$(dbmember.postcode)</td><td>$(dbmember.city)</td></tr>";
+						data1 += @"<tr class=\"error\"><td><i class=\"icon-minus-sign\"></i><td>$(dbmember.id)</td><td>$(dbmember.firstname)</td><td>$(dbmember.lastname)</td><td>$(dbmember.email)</td><td>$(dbmember.gender)</td><td>$(dbmember.street)</td><td>$(dbmember.postcode)</td><td>$(dbmember.city)</td><td>$(dbmember.pgp)</td></tr>";
 					}
 					if(!member.exists_in_db() || !member.equals_db()) {
-						data1 += @"<tr class=\"success\"><td><i class=\"icon-plus-sign\"></td><td>$(member.id)</td><td>$(member.firstname)</td><td>$(member.lastname)</td><td>$(member.email)</td><td>$(member.gender)</td><td>$(member.street)</td><td>$(member.postcode)</td><td>$(member.city)</td></tr>";
+						data1 += @"<tr class=\"success\"><td><i class=\"icon-plus-sign\"></td><td>$(member.id)</td><td>$(member.firstname)</td><td>$(member.lastname)</td><td>$(member.email)</td><td>$(member.gender)</td><td>$(member.street)</td><td>$(member.postcode)</td><td>$(member.city)</td><td>$(member.pgp)</td></tr>";
 					}
 				}
 				t.replace("DATA1", data1);
@@ -219,6 +219,7 @@ public class WebServer {
 			t.replace("STREET", userinfo.street);
 			t.replace("POSTALCODE", "%d".printf(userinfo.postcode));
 			t.replace("CITY", userinfo.city);
+			t.replace("PGPKEYID", userinfo.pgp);
 
 			var userauth = db.get_user_auth(id);
 			t.replace("DISABLED", userauth.disabled ? "true" : "false");
diff --git a/templates/users/entry.html b/templates/users/entry.html
index 740804e..ee1959c 100644
--- a/templates/users/entry.html
+++ b/templates/users/entry.html
@@ -15,6 +15,7 @@
 		<tr><th>Firstname</th><td>{{{FIRSTNAME}}}</td></tr>
 		<tr><th>Lastname</th><td>{{{LASTNAME}}}</td></tr>
 		<tr><th>E-Mail</th><td>{{{EMAIL}}}</td></tr>
+		<tr><th>PGP Key ID</th><td>{{{PGPKEYID}}}</td></tr>
 		<tr><th>Gender</th><td>{{{GENDER}}}</td></tr>
 		<tr><th>Street</th><td>{{{STREET}}}</td></tr>
 		<tr><th>PLZ</th><td>{{{POSTALCODE}}}</td></tr>
diff --git a/templates/users/import.html b/templates/users/import.html
index 108c867..bdffcc4 100644
--- a/templates/users/import.html
+++ b/templates/users/import.html
@@ -21,7 +21,7 @@
 <div style="display: {{{STEP23}}}">
 	<table class="table table-bordered">
 		<thead>
-			<tr><th><i class="icon-wrench"></i></th><th>ID</th><th>Firstname</th><th>Lastname</th><th>E-Mail</th><th>Gender</th><th>Street</th><th>Postcode</th><th>City</th></tr>
+			<tr><th><i class="icon-wrench"></i></th><th>ID</th><th>Firstname</th><th>Lastname</th><th>E-Mail</th><th>Gender</th><th>Street</th><th>Postcode</th><th>City</th><th>PGP</th></tr>
 		</thead>
 		<tbody>
 			{{{DATA1}}}