From 54828fef6bd859234b353685dade7d6841d7dd18 Mon Sep 17 00:00:00 2001 From: Holger Cremer Date: Tue, 13 Jun 2017 22:14:26 +0200 Subject: adds the joined_at column and support in the db/web --- src/database/database.vala | 8 +++++--- src/database/db-interface.vala | 2 ++ src/web/csv.vala | 7 ++++--- src/web/web.vala | 4 ++-- 4 files changed, 13 insertions(+), 8 deletions(-) (limited to 'src') 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 += @"$(dbmember.id)$(dbmember.firstname)$(dbmember.lastname)$(dbmember.email)$(dbmember.gender)$(dbmember.street)$(dbmember.postcode)$(dbmember.city)$(dbmember.pgp)$(dbmember.hidden)$(dbmember.disabled)"; + data1 += @"$(dbmember.id)$(dbmember.firstname)$(dbmember.lastname)$(dbmember.email)$(dbmember.gender)$(dbmember.street)$(dbmember.postcode)$(dbmember.city)$(dbmember.pgp)$(dbmember.hidden)$(dbmember.disabled)$(dbmember.joined_at)"; } if(!db.user_exists(member.id) || !db.user_equals(member)) { - data1 += @"$(member.id)$(member.firstname)$(member.lastname)$(member.email)$(member.gender)$(member.street)$(member.postcode)$(member.city)$(member.pgp)$(member.hidden)$(member.disabled)"; + data1 += @"$(member.id)$(member.firstname)$(member.lastname)$(member.email)$(member.gender)$(member.street)$(member.postcode)$(member.city)$(member.pgp)$(member.hidden)$(member.disabled)$(member.joined_at)"; } } t.replace("DATA1", data1); -- cgit v1.2.3