From dcb612d921391ee439144148f9fc47caad3912a7 Mon Sep 17 00:00:00 2001 From: Sebastian Reichel Date: Fri, 5 Oct 2012 06:07:52 +0200 Subject: merge user import script into the web interface Users can be imported from *.csv files via /users/import. --- Makefile | 4 +- invoice/import-users.py | 23 ---------- src/admin.vala | 74 +++++++++++++++++++++++++++++++ src/db.vala | 103 ++++++++++++++++++++++++++++++++++++++------ src/main.vala | 5 +++ src/web.vala | 101 ++++++++++++++++++++++++++++++++++++++++++- templates/users/import.html | 39 +++++++++++++++++ 7 files changed, 311 insertions(+), 38 deletions(-) delete mode 100755 invoice/import-users.py create mode 100644 src/admin.vala create mode 100644 templates/users/import.html diff --git a/Makefile b/Makefile index 77bdb1e..cc8c6b9 100644 --- a/Makefile +++ b/Makefile @@ -1,6 +1,6 @@ -SRC=src/main.vala src/device.vala src/db.vala src/audio.vala src/web.vala src/graph-data.vala src/template.vala src/session.vala src/price.vapi +SRC=src/main.vala src/device.vala src/db.vala src/audio.vala src/web.vala src/graph-data.vala src/template.vala src/session.vala src/admin.vala src/price.vapi DEPS=--pkg posix --pkg linux --pkg libsoup-2.4 --pkg sqlite3 --pkg gee-1.0 --pkg gio-2.0 --pkg gstreamer-0.10 -FLAGS=-X -w +FLAGS=-X -w --enable-experimental barcode-scanner: $(SRC) valac-0.16 --output $@ $(FLAGS) $(DEPS) $^ diff --git a/invoice/import-users.py b/invoice/import-users.py deleted file mode 100755 index 4ed484c..0000000 --- a/invoice/import-users.py +++ /dev/null @@ -1,23 +0,0 @@ -#!/usr/bin/env python3 -import csv, sqlite3, sys - -title_to_gender = { - "m": "masculinum", - "w": "femininum" -} - -data = csv.reader(open(sys.argv[1], 'r', encoding='utf-8'), delimiter=';', quotechar='"') -connection = sqlite3.connect('shop.db') -c = connection.cursor() - -# skip header line -data.__next__() - -for row in data: - print(row) - gender = title_to_gender.get(row[7], "unknown") - t = (int(row[0]), row[1], row[2], row[3], gender, row[4], int(row[5]), row[6]) - c.execute("INSERT OR REPLACE INTO users ('id', 'email', 'firstname', 'lastname', 'gender', 'street', 'plz', 'city') VALUES (?, ?, ?, ?, ?, ?, ?, ?);", t) - -connection.commit() -c.close() diff --git a/src/admin.vala b/src/admin.vala new file mode 100644 index 0000000..37a9703 --- /dev/null +++ b/src/admin.vala @@ -0,0 +1,74 @@ +/* Copyright 2012, Sebastian Reichel + * + * Permission to use, copy, modify, and/or distribute this software for any + * purpose with or without fee is hereby granted, provided that the above + * copyright notice and this permission notice appear in all copies. + * + * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES + * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF + * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR + * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES + * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN + * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF + * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. + */ + +public class CSVMemberFile { + private UserInfo[] members; + + public Gee.List missing_unblocked_members() { + var result = new Gee.ArrayList(); + var dbusers = db.get_member_ids(); + + foreach(var u in dbusers) { + bool found=false; + foreach(var m in members) { + if(u == m.id) { + found=true; + break; + } + } + + if(!found) { + if(!db.user_is_disabled(u)) + result.add(u); + } + } + + return result; + } + + private string[] csv_split(string line) { + return /;(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/.split(line); + } + + private string csv_value(string value) { + if(value[0] == '"' && value[value.length-1] == '"') + return value.substring(1,value.length-2); + else + return value; + } + + public CSVMemberFile(string data) { + foreach(var line in data.split("\n")) { + var linedata = csv_split(line); + if(linedata.length >= 8) { + var m = UserInfo(); + m.id = int.parse(csv_value(linedata[0])); + m.email = csv_value(linedata[1]); + m.firstname = csv_value(linedata[2]); + m.lastname = csv_value(linedata[3]); + m.street = csv_value(linedata[4]); + 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"; + if(csv_value(linedata[0]) != "EXTERNEMITGLIEDSNUMMER") + members += m; + } + } + } + + public UserInfo[] get_members() { + return members; + } +} diff --git a/src/db.vala b/src/db.vala index 43db62b..d261eed 100644 --- a/src/db.vala +++ b/src/db.vala @@ -42,8 +42,32 @@ public struct UserInfo { public string email; public string gender; public string street; - public int plz; + public int postcode; public string city; + + public bool equals(UserInfo x) { + if(id != x.id) return false; + if(firstname != x.firstname) return false; + if(lastname != x.lastname) return false; + if(email != x.email) return false; + if(gender != x.gender) return false; + if(street != x.street) return false; + if(postcode != x.postcode) return false; + if(city != x.city) return false; + + return true; + } + + public bool exists_in_db() { + if(id in db.get_member_ids()) + return true; + else + return false; + } + + public bool equals_db() { + return this.equals(db.get_user_info(id)); + } } public struct UserAuth { @@ -171,6 +195,10 @@ public class Database { queries["stock_value"] = "SELECT SUM(amount * price) FROM products INNER JOIN purchaseprices ON products.id = purchaseprices.product"; 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_auth_create"] = "INSERT OR IGNORE INTO authentication (user) VALUES (?)"; + queries["user_disable"] = "UPDATE authentication SET disabled = ? WHERE user = ?"; /* compile queries into statements */ foreach(var entry in queries.entries) { @@ -380,7 +408,7 @@ public class Database { rc = statements["purchase"].step(); if(rc != Sqlite.DONE) - error("[interner Fehler: %d]".printf(rc)); + error("[internal error: %d]".printf(rc)); return true; } else { @@ -400,7 +428,7 @@ public class Database { case Sqlite.DONE: return "unbekanntes Produkt: %llu".printf(article); default: - return "[interner Fehler: %d]".printf(rc); + return "[internal error: %d]".printf(rc); } } @@ -417,7 +445,7 @@ public class Database { warning("unbekanntes Produkt: %llu".printf(article)); return -1; default: - warning("[interner Fehler: %d]".printf(rc)); + warning("[internal error: %d]".printf(rc)); return -1; } } @@ -442,7 +470,7 @@ public class Database { write_to_log("unbekanntes Produkt: %llu\n", article); return 0; default: - write_to_log("[interner Fehler: %d]\n", rc); + write_to_log("[internal error: %d]\n", rc); return 0; } } @@ -465,7 +493,7 @@ public class Database { write_to_log("undo not possible without purchases"); return false; default: - error("[interner Fehler: %d]".printf(rc)); + error("[internal error: %d]".printf(rc)); } statements["undo"].reset(); @@ -473,7 +501,7 @@ public class Database { rc = statements["undo"].step(); if(rc != Sqlite.DONE) - error("[interner Fehler: %d]".printf(rc)); + error("[internal error: %d]".printf(rc)); return true; } @@ -495,7 +523,7 @@ public class Database { rc = statements["stock"].step(); if(rc != Sqlite.DONE) - error("[interner Fehler: %d]".printf(rc)); + error("[internal error: %d]".printf(rc)); return true; } @@ -511,7 +539,7 @@ public class Database { int rc = statements["product_create"].step(); if(rc != Sqlite.DONE) { - warning("[interner Fehler: %d]".printf(rc)); + warning("[internal error: %d]".printf(rc)); return false; } @@ -527,7 +555,7 @@ public class Database { int rc = statements["price_create"].step(); if(rc != Sqlite.DONE) { - warning("[interner Fehler: %d]".printf(rc)); + warning("[internal error: %d]".printf(rc)); return false; } @@ -561,7 +589,7 @@ public class Database { int rc = statements["session_set"].step(); if(rc != Sqlite.DONE) - error("[interner Fehler: %d]".printf(rc)); + error("[internal error: %d]".printf(rc)); } public int get_user_by_sessionid(string sessionid) throws WebSessionError { @@ -587,7 +615,7 @@ public class Database { result.email = statements["userinfo"].column_text(2); result.gender = statements["userinfo"].column_text(3); result.street = statements["userinfo"].column_text(4); - result.plz = statements["userinfo"].column_int(5); + result.postcode = statements["userinfo"].column_int(5); result.city = statements["userinfo"].column_text(6); } @@ -597,6 +625,8 @@ public class Database { public UserAuth get_user_auth(int user) { var result = UserAuth(); result.id = user; + result.disabled = false; + result.superuser = false; statements["userauth"].reset(); statements["userauth"].bind_int(1, user); @@ -737,4 +767,53 @@ public class Database { return result; } + + public Gee.List get_member_ids() { + var result = new Gee.ArrayList(); + + statements["user_get_ids"].reset(); + while(statements["user_get_ids"].step() == Sqlite.ROW) + result.add(statements["user_get_ids"].column_int(0)); + + return result; + } + + public void user_disable(int user, bool value) { + int rc; + + /* create user auth line if not existing */ + statements["user_auth_create"].reset(); + statements["user_auth_create"].bind_int(1, user); + rc = statements["user_auth_create"].step(); + if(rc != Sqlite.DONE) + error("[internal error: %d]".printf(rc)); + + /* set disabled flag */ + statements["user_disable"].reset(); + statements["user_disable"].bind_int(1, value ? 1 : 0); + statements["user_disable"].bind_int(2, user); + rc = statements["user_disable"].step(); + if(rc != Sqlite.DONE) + error("[internal error: %d]".printf(rc)); + } + + public void user_replace(UserInfo u) { + statements["user_replace"].reset(); + statements["user_replace"].bind_int(1, u.id); + statements["user_replace"].bind_text(2, u.email); + statements["user_replace"].bind_text(3, u.firstname); + statements["user_replace"].bind_text(4, u.lastname); + statements["user_replace"].bind_text(5, u.gender); + statements["user_replace"].bind_text(6, u.street); + statements["user_replace"].bind_int(7, u.postcode); + statements["user_replace"].bind_text(8, u.city); + + int rc = statements["user_replace"].step(); + if(rc != Sqlite.DONE) + error("[internal error: %d]".printf(rc)); + } + + public bool user_is_disabled(int user) { + return get_user_auth(user).disabled; + } } diff --git a/src/main.vala b/src/main.vala index 74736d2..86f61dd 100644 --- a/src/main.vala +++ b/src/main.vala @@ -15,8 +15,12 @@ public Device dev; public Database db; +public AudioPlayer audio; +public CSVMemberFile csvimport; public static int main(string[] args) { + Gst.init(ref args); + if(args.length < 2) { stderr.printf("%s \n", args[0]); return 1; @@ -24,6 +28,7 @@ public static int main(string[] args) { dev = new Device(args[1], 9600, 8, 1); db = new Database("shop.db"); + audio = new AudioPlayer(); dev.received_barcode.connect((data) => { if(interpret(data)) diff --git a/src/web.vala b/src/web.vala index 562a1ba..e90934a 100644 --- a/src/web.vala +++ b/src/web.vala @@ -72,6 +72,104 @@ public class WebServer { } } + void handler_user_import(Soup.Server server, Soup.Message msg, string path, GLib.HashTable? query, Soup.ClientContext client) { + try { + var session = new WebSession(server, msg, path, query, client); + if(!session.superuser) { + handler_403(server, msg, path, query, client); + return; + } + var t = new WebTemplate("users/import.html", session); + t.replace("TITLE", "KtT Shop System: User Import"); + t.menu_set_active("users"); + + Soup.Buffer filedata; + var postdata = Soup.Form.decode_multipart(msg, "file", null, null, out filedata); + if(postdata == null || !postdata.contains("step")) { + t.replace("DATA1", ""); + t.replace("DATA2", ""); + t.replace("STEP1", "block"); + t.replace("STEP2", "none"); + t.replace("STEP23", "none"); + t.replace("STEP3", "none"); + msg.set_response("text/html", Soup.MemoryUse.COPY, t.data); + return; + } else { + if(filedata != null) { + string text = (string) filedata.data; + text = text.substring(0,(long) filedata.length-1); + csvimport = new CSVMemberFile(text); + } + + if(csvimport == null) { + handler_403(server, msg, path, query, client); + return; + } + + /* new & changed users */ + string data1 = ""; + foreach(var member in csvimport.get_members()) { + if(member.exists_in_db() && !member.equals_db()) { + 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)"; + } + if(!member.exists_in_db() || !member.equals_db()) { + data1 += @"$(member.id)$(member.firstname)$(member.lastname)$(member.email)$(member.gender)$(member.street)$(member.postcode)$(member.city)"; + } + } + t.replace("DATA1", data1); + + /* removed users */ + Gee.List blockedusers = csvimport.missing_unblocked_members(); + if(blockedusers.size > 0) { + string data2 = "Disabling the following users, because they are no longer found in the member CSV:
    "; + + foreach(var member in blockedusers) { + try { + string name = db.get_username(member); + data2 += @"
  • $name ($member)
    • "; + } catch(Error e) {} + } + + data2 += "
"; + t.replace("DATA2", data2); + } else { + t.replace("DATA2", ""); + } + + /* show correct blocks */ + t.replace("STEP1", "none"); + t.replace("STEP23", "block"); + if(postdata["step"] == "1") { + t.replace("STEP2", "block"); + t.replace("STEP3", "none"); + } else { + t.replace("STEP2", "none"); + t.replace("STEP3", "block"); + } + + if(postdata["step"] == "2") { + /* disable users */ + foreach(var member in csvimport.missing_unblocked_members()) { + db.user_disable(member, true); + } + + /* update users */ + foreach(var member in csvimport.get_members()) { + db.user_replace(member); + } + + csvimport = null; + } + } + + 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); + } + } + void handler_user_entry(Soup.Server server, Soup.Message msg, string path, GLib.HashTable? query, Soup.ClientContext client, int id) { try { var l = new WebSession(server, msg, path, query, client); @@ -91,7 +189,7 @@ public class WebServer { t.replace("EMAIL", userinfo.email); t.replace("GENDER", userinfo.gender); t.replace("STREET", userinfo.street); - t.replace("POSTALCODE", "%d".printf(userinfo.plz)); + t.replace("POSTALCODE", "%d".printf(userinfo.postcode)); t.replace("CITY", userinfo.city); var userauth = db.get_user_auth(id); @@ -586,6 +684,7 @@ public class WebServer { /* users */ srv.add_handler("/users", handler_users); + srv.add_handler("/users/import", handler_user_import); srv.run(); } diff --git a/templates/users/import.html b/templates/users/import.html new file mode 100644 index 0000000..108c867 --- /dev/null +++ b/templates/users/import.html @@ -0,0 +1,39 @@ +

Import Users

+ +
+ CSV Import Done! The System's Database has been updated. +
+ +
+
+
+ +
+ + +
+
+ + +
+
+ +
+ + + + + + {{{DATA1}}} + +
IDFirstnameLastnameE-MailGenderStreetPostcodeCity
+ + {{{DATA2}}}. + +
+
+ + +
+
+
-- cgit v1.2.3