Merge pull request #26 from smilix/joined_at_info

adds the joined_at column and support in the db/web

6 files changed, 15 insertions, 10 deletions

diff --git a/sql/tables.sql b/sql/tables.sql
index a912740..fbe4d0e 100644
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -3,7 +3,7 @@ CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY NOT NULL CHECK (id <
 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, 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, disabled BOOLEAN NOT NULL DEFAULT 0, hidden BOOLEAN NOT NULL DEFAULT 0, sound_theme 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, disabled BOOLEAN NOT NULL DEFAULT 0, hidden BOOLEAN NOT NULL DEFAULT 0, sound_theme TEXT, joined_at INTEGER NOT NULL DEFAULT 0);
 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);
diff --git a/src/database/database.vala b/src/database/database.vala
index 6bcbd70..fb7bde6 100644
--- a/src/database/database.vala
+++ b/src/database/database.vala
@@ -108,7 +108,7 @@ public class DataBase : Object {
 		queries["user_theme_set"]    = "UPDATE users SET sound_theme=? 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, pgp, hidden, disabled, sound_theme FROM users WHERE id = ?";
+		queries["userinfo"]          = "SELECT firstname, lastname, email, gender, street, plz, city, pgp, hidden, disabled, sound_theme, joined_at FROM users WHERE id = ?";
 		queries["userauth"]          = "SELECT superuser, auth_users, auth_products, auth_cashbox FROM authentication WHERE user = ?";
 		queries["userauth_set"]      = "UPDATE authentication SET auth_users = ?, auth_products = ?, auth_cashbox = ? 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;";
@@ -121,7 +121,7 @@ public class DataBase : Object {
 		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', 'pgp', 'hidden', 'disabled', 'sound_theme') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (select sound_theme from users where id = ?))";
+		queries["user_replace"]      = "INSERT OR REPLACE INTO users ('id', 'email', 'firstname', 'lastname', 'gender', 'street', 'plz', 'city', 'pgp', 'hidden', 'disabled', 'joined_at', 'sound_theme') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (select sound_theme from users where id = ?))";
 		queries["user_auth_create"]  = "INSERT OR IGNORE INTO authentication (user) VALUES (?)";
 		queries["user_disable"]      = "UPDATE users SET disabled = ? WHERE id = ?";
 		queries["last_timestamp"]    = "SELECT timestamp FROM sales ORDER BY timestamp DESC LIMIT 1";
@@ -610,6 +610,7 @@ public class DataBase : Object {
 			result.hidden      = statements["userinfo"].column_int(8) == 1;
 			result.disabled	   = statements["userinfo"].column_int(9) == 1;
 			result.soundTheme  = statements["userinfo"].column_text(10);
+			result.joined_at   = statements["userinfo"].column_int64(11);
 		} else if(rc == Sqlite.DONE) {
 			throw new DatabaseError.USER_NOT_FOUND("user not found");
 		} else {
@@ -872,7 +873,8 @@ public class DataBase : Object {
 		statements["user_replace"].bind_text(9, u.pgp);
 		statements["user_replace"].bind_int(10, u.hidden ? 1 : 0);
 		statements["user_replace"].bind_int(11, u.disabled ? 1 : 0);
-		statements["user_replace"].bind_int(12, u.id);
+		statements["user_replace"].bind_int64(12, u.joined_at);
+		statements["user_replace"].bind_int(13, u.id);
 
 		int rc = statements["user_replace"].step();
 		if(rc != Sqlite.DONE)
diff --git a/src/database/db-interface.vala b/src/database/db-interface.vala
index 6142fcf..bd94ce1 100644
--- a/src/database/db-interface.vala
+++ b/src/database/db-interface.vala
@@ -121,6 +121,7 @@ public struct UserInfo {
 	public string postcode;
 	public string city;
 	public string pgp;
+	public int64 joined_at;
 	public bool disabled;
 	public bool hidden;
 	public string soundTheme;
@@ -135,6 +136,7 @@ public struct UserInfo {
 		if(postcode != x.postcode) return false;
 		if(city != x.city) return false;
 		if(pgp != x.pgp) return false;
+		if(joined_at != x.joined_at) return false;
 		if(disabled != x.disabled) return false;
 		if(hidden != x.hidden) return false;
 
diff --git a/src/web/csv.vala b/src/web/csv.vala
index bb1b166..cb1c057 100644
--- a/src/web/csv.vala
+++ b/src/web/csv.vala
@@ -62,9 +62,10 @@ public class CSVMemberFile {
 				m.postcode = 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]);
-				m.hidden = int.parse(csv_value(linedata[9])) != 0;
-				m.disabled = int.parse(csv_value(linedata[10])) != 0;
+				m.joined_at = int.parse(csv_value(linedata[8]));
+				m.pgp = csv_value(linedata[9]);
+				m.hidden = int.parse(csv_value(linedata[10])) != 0;
+				m.disabled = int.parse(csv_value(linedata[11])) != 0;
 				m.soundTheme = "";
 				if(csv_value(linedata[0]) != "EXTERNEMITGLIEDSNUMMER")
 					members += m;
diff --git a/src/web/web.vala b/src/web/web.vala
index c63db73..e60b325 100644
--- a/src/web/web.vala
+++ b/src/web/web.vala
@@ -214,10 +214,10 @@ public class WebServer {
 				foreach(var member in csvimport.get_members()) {
 					if(db.user_exists(member.id) && !db.user_equals(member)) {
 						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><td>$(dbmember.pgp)</td><td>$(dbmember.hidden)</td><td>$(dbmember.disabled)</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><td>$(dbmember.hidden)</td><td>$(dbmember.disabled)</td><td>$(dbmember.joined_at)</td></tr>";
 					}
 					if(!db.user_exists(member.id) || !db.user_equals(member)) {
-						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><td>$(member.hidden)</td><td>$(member.disabled)</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><td>$(member.hidden)</td><td>$(member.disabled)</td><td>$(member.joined_at)</td></tr>";
 					}
 				}
 				t.replace("DATA1", data1);
diff --git a/templates/users/import.html b/templates/users/import.html
index a9d7ee3..1a0e6ef 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><th>PGP</th><th>Hidden</th><th>Disabled</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><th>Hidden</th><th>Disabled</th><th>Joined at</th></tr>
 		</thead>
 		<tbody>
 			{{{DATA1}}}