Merge branch 'master' into eventbased

7 files changed, 275 insertions, 115 deletions

diff --git a/create_db.sql b/create_db.sql
index 8540b70..8c348b6 100644
--- a/create_db.sql
+++ b/create_db.sql
@@ -2,36 +2,54 @@ CREATE TABLE products (id INTEGER PRIMARY KEY NOT NULL, name TEXT, amount INTEGE
 CREATE TABLE purchases (user INTEGER NOT NULL DEFAULT 0, product INTEGER NOT NULL DEFAULT 0, timestamp INTEGER NOT NULL DEFAULT 0);
 CREATE TABLE restock (user INTEGER NOT NULL DEFAULT 0, product INTEGER NOT NULL DEFAULT 0, amount INTEGER NOT NULL DEFAULT 0, timestamp INTEGER NOT NULL DEFAULT 0);
 CREATE TABLE prices (product INTEGER NOT NULL DEFAULT 0, valid_from INTEGER NOT NULL DEFAULT 0, memberprice INTEGER NOT NULL DEFAULT 0, guestprice INTEGER NOT NULL DEFAULT 0);
-CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, username TEXT, firstname TEXT NOT NULL, lastname TEXT NOT NULL, street TEXT, city TEXT, email TEXT);
+CREATE TABLE 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);
 BEGIN TRANSACTION;
-INSERT INTO products (id, name) VALUES(4029764001807,'Club Mate');
-INSERT INTO products (id, name) VALUES(5449000017888,'Coka Cola');
-INSERT INTO products (id, name) VALUES(5449000017895,'Coka Cola Light');
-INSERT INTO products (id, name) VALUES(5449000134264,'Coka Cola Zero');
-INSERT INTO products (id, name) VALUES(5449000017918,'Fanta');
-INSERT INTO products (id, name) VALUES(5449000017932,'Sprite');
-INSERT INTO products (id, name) VALUES(4104450004383,'Limette');
-INSERT INTO products (id, name) VALUES(4104450005878,'Vilsa Classic');
-INSERT INTO products (id, name) VALUES(5000159407236,'Mars');
-INSERT INTO products (id, name) VALUES(5000159407397,'Snickers');
-INSERT INTO products (id, name) VALUES(5000159418539,'Balisto Jogurt Beeren Mix');
-INSERT INTO products (id, name) VALUES(7613032625474,'Lion');
-INSERT INTO products (id, name) VALUES(7613032850340,'KitKat Chunky');
-INSERT INTO products (id, name) VALUES(40084015,'Duplo');
-INSERT INTO products (id, name) VALUES(40358802,'Knoppers');
-INSERT INTO products (id, name) VALUES(40084107,'Ü-Ei');
-INSERT INTO products (id, name) VALUES(40114606,'KitKat');
-INSERT INTO products (id, name) VALUES(40111315,'Twix');
-INSERT INTO products (id, name) VALUES(4003586000491,'Chips Frisch Oriento');
-INSERT INTO products (id, name) VALUES(8690504018568,'Üker Sesamsticks');
-INSERT INTO products (id, name) VALUES(4001686216125,'Haribo Salino');
-INSERT INTO products (id, name) VALUES(4001686312025,'Haribo Schnuller');
-INSERT INTO products (id, name) VALUES(4001686150689,'Haribo Konfekt');
-INSERT INTO products (id, name) VALUES(4001686128244,'Haribo Staffeten');
-INSERT INTO products (id, name) VALUES(4001686390085,'Haribo Phantasia');
-INSERT INTO products (id, name) VALUES(4001686310229,'Haribo Weinland');
-INSERT INTO products (id, name) VALUES(4001686386613,'Haribo Saftgoldbären');
-INSERT INTO products (id, name) VALUES(4001686301265,'Haribo Goldbären');
-INSERT INTO products (id, name) VALUES(4001686313046,'Haribo Saure Bohnen');
-INSERT INTO products (id, name) VALUES(4001686720028,'Haribo Colorado Mini');
+INSERT INTO products VALUES(40084015,'Duplo',42);
+INSERT INTO products VALUES(40084107,'Ü-Ei',64);
+INSERT INTO products VALUES(40111315,'Twix',30);
+INSERT INTO products VALUES(40114606,'KitKat',45);
+INSERT INTO products VALUES(40358802,'Knoppers',41);
+INSERT INTO products VALUES(76222498,'Milka Tender milch',15);
+INSERT INTO products VALUES(76222504,'Milka Tender nuss',13);
+INSERT INTO products VALUES(4001686128244,'Haribo Staffeten',28);
+INSERT INTO products VALUES(4001686150689,'Haribo Konfekt',19);
+INSERT INTO products VALUES(4001686216125,'Haribo Salino',23);
+INSERT INTO products VALUES(4001686301265,'Haribo Goldbären',24);
+INSERT INTO products VALUES(4001686309506,'Haribo Happy Cherries',10);
+INSERT INTO products VALUES(4001686310229,'Haribo Weinland',22);
+INSERT INTO products VALUES(4001686312025,'Haribo Schnuller',18);
+INSERT INTO products VALUES(4001686313046,'Haribo Saure Bohnen',17);
+INSERT INTO products VALUES(4001686315101,'Haribo Happy Cola',20);
+INSERT INTO products VALUES(4001686367346,'Haribo Tropifrutti',21);
+INSERT INTO products VALUES(4001686386613,'Haribo Saftgoldbären',3);
+INSERT INTO products VALUES(4001686390085,'Haribo Phantasia',11);
+INSERT INTO products VALUES(4001686720028,'Haribo Colorado Mini',0);
+INSERT INTO products VALUES(4001686721445,'Haribo Color-Rado',18);
+INSERT INTO products VALUES(4003586000477,'Chips Frisch Ungarisch',18);
+INSERT INTO products VALUES(4003586000491,'Chips Frisch Oriento',22);
+INSERT INTO products VALUES(4006220013185,'Orangen Saft',0);
+INSERT INTO products VALUES(4007495314014,'Gouda Käse-Sticks',28);
+INSERT INTO products VALUES(4029764001807,'Club Mate',112);
+INSERT INTO products VALUES(4047046003325,'Senseo Klassisch',29);
+INSERT INTO products VALUES(4047046003356,'Senseo Mild',16);
+INSERT INTO products VALUES(4047046003417,'Senseo Entkoffeiniert',16);
+INSERT INTO products VALUES(4047046005008,'Senseo Cappuccino Choco',8);
+INSERT INTO products VALUES(4104450004086,'Vilsa medium',19);
+INSERT INTO products VALUES(4104450004383,'Limette',64);
+INSERT INTO products VALUES(4104450005878,'Vilsa Classic',1);
+INSERT INTO products VALUES(5000159407236,'Mars',16);
+INSERT INTO products VALUES(5000159407397,'Snickers',6);
+INSERT INTO products VALUES(5000159407410,'Snickers (2x)',48);
+INSERT INTO products VALUES(5000159418539,'Balisto Jogurt Beeren Mix',36);
+INSERT INTO products VALUES(5000159418546,'Balisto Muesli-Mix',40);
+INSERT INTO products VALUES(5000159418577,'Balisto Korn-Mix',40);
+INSERT INTO products VALUES(5449000017888,'Coka Cola',56);
+INSERT INTO products VALUES(5449000017895,'Coka Cola Light',65);
+INSERT INTO products VALUES(5449000017918,'Fanta',67);
+INSERT INTO products VALUES(5449000017932,'Sprite',52);
+INSERT INTO products VALUES(5449000134264,'Coka Cola Zero',55);
+INSERT INTO products VALUES(7613032625474,'Lion',50);
+INSERT INTO products VALUES(7613032850340,'KitKat Chunky',22);
+INSERT INTO products VALUES(8410036002015,'Freixenet Cava Semi Seco 0.75L',3);
+INSERT INTO products VALUES(8690504018568,'Üker Sesamsticks',5);
 COMMIT;
diff --git a/db.vala b/db.vala
index 5b32cd6..5bb3504 100644
--- a/db.vala
+++ b/db.vala
@@ -8,7 +8,7 @@ public class Database {
 	private Sqlite.Statement undo_stmt3;
 	private Sqlite.Statement stock_stmt1;
 	private Sqlite.Statement stock_stmt2;
-	uint64 user = 0;
+	int32 user = 0;
 	uint64 product = 0;
 	bool logged_in = false;
 	bool stock_mode = false;
@@ -71,7 +71,7 @@ public class Database {
 
 	}
 
-	public bool login(uint64 id) {
+	public bool login(int32 id) {
 		this.user = id;
 		this.logged_in = true;
 		return true;
@@ -79,6 +79,7 @@ public class Database {
 
 	public bool logout() {
 		this.user = 0;
+		this.stock_mode = false;
 		this.logged_in = false;
 		return true;
 	}
@@ -89,7 +90,7 @@ public class Database {
 			int64 timestamp = (new DateTime.now_utc()).to_unix();
 
 			this.purchase_stmt1.reset();
-			this.purchase_stmt1.bind_text(1, "%llu".printf(user));
+			this.purchase_stmt1.bind_text(1, "%d".printf(user));
 			this.purchase_stmt1.bind_text(2, "%llu".printf(article));
 			this.purchase_stmt1.bind_text(3, "%llu".printf(timestamp));
 
@@ -132,7 +133,7 @@ public class Database {
 			int rc = 0;
 
 			this.undo_stmt1.reset();
-			this.undo_stmt1.bind_text(1, "%llu".printf(user));
+			this.undo_stmt1.bind_text(1, "%d".printf(user));
 
 			rc = this.undo_stmt1.step();
 			switch(rc) {
@@ -140,14 +141,14 @@ public class Database {
 					pid = uint64.parse(this.undo_stmt1.column_text(0));
 					break;
 				case Sqlite.DONE:
-					stdout.printf("undo not possible without purchases");
+					stdout.printf("undo not possible without purchases\n");
 					return false;
 				default:
 					error("[interner Fehler: %d]".printf(rc));
 			}
 
 			this.undo_stmt2.reset();
-			this.undo_stmt2.bind_text(1, "%llu".printf(user));
+			this.undo_stmt2.bind_text(1, "%d".printf(user));
 
 			rc = this.undo_stmt2.step();
 			if(rc != Sqlite.DONE)
@@ -180,7 +181,7 @@ public class Database {
 			int64 timestamp = (new DateTime.now_utc()).to_unix();
 
 			this.stock_stmt1.reset();
-			this.stock_stmt1.bind_text(1, "%llu".printf(user));
+			this.stock_stmt1.bind_text(1, "%d".printf(user));
 			this.stock_stmt1.bind_text(2, "%llu".printf(product));
 			this.stock_stmt1.bind_text(3, "%llu".printf(amount));
 			this.stock_stmt1.bind_text(4, "%llu".printf(timestamp));
diff --git a/device.vala b/device.vala
index db2857e..92c7022 100644
--- a/device.vala
+++ b/device.vala
@@ -159,7 +159,6 @@ public class Device {
 
 	private ssize_t write(void *buf, size_t count) {
 		ssize_t size = Posix.write(fd, buf, count);
-		Posix.tcflush(fd, Posix.TCOFLUSH);
 		return size;
 	}
 
diff --git a/insert-prices.sql b/insert-prices.sql
index f86fb4d..7b06815 100644
--- a/insert-prices.sql
+++ b/insert-prices.sql
@@ -44,4 +44,5 @@ INSERT INTO prices (product, memberprice, guestprice) VALUES (76222504,80,100);
 INSERT INTO prices (product, memberprice, guestprice) VALUES (5000159407410,140,180);
 INSERT INTO prices (product, memberprice, guestprice) VALUES (5000159418546,80,100);
 INSERT INTO prices (product, memberprice, guestprice) VALUES (5000159418577,80,100);
+INSERT INTO prices (product, memberprice, guestprice) VALUES (4007495314014,100,130);
 COMMIT;
diff --git a/invoice/generate-invoice.py b/invoice/generate-invoice.py
index 9d59cea..6688d83 100755
--- a/invoice/generate-invoice.py
+++ b/invoice/generate-invoice.py
@@ -1,45 +1,33 @@
 #!/usr/bin/env python3
 # -*- coding: utf-8 -*-
-import datetime, sqlite3, os, sys, smtplib, subprocess, time
-import tempfile
+import datetime, sqlite3, os, sys, smtplib, subprocess, time, tempfile
 from email.mime.multipart import MIMEMultipart
 from email.mime.application import MIMEApplication
 from email.mime.text import MIMEText
 from email.header import Header
 
-SMTPSERVERNAME = 'SERVER'
-SMTPSERVERPORT = 587
-SMTPSERVERUSER = 'username'
-SMTPSERVERPASS = 'password'
+from config import *
 
 def get_user_info(userid):
-	result = {
-		"id": userid,
-		"username": "",
-		"email": "",
-		"firstname": "",
-		"lastname": "",
-		"street": "",
-		"city": ""
-	}
-
 	connection = sqlite3.connect('shop.db')
 	c = connection.cursor()
-
-	c.execute("SELECT id, username, email, firstname, lastname, street, city FROM users WHERE id = ?;", (userid,))
-
-	for row in c:
-		result["id"] = row[0]
-		result["username"] = row[1]
-		result["email"] = row[2]
-		result["firstname"] = row[3]
-		result["lastname"] = row[4]
-		result["street"] = row[5]
-		result["city"] = row[6]
-
+	c.execute("SELECT id, email, firstname, lastname, gender, street, plz, city FROM users WHERE id = ?;", (userid,))
+	row = c.fetchone()
 	c.close()
 
-	return result
+	if row is None:
+		return None
+	else:
+		return {
+			"id": row[0],
+			"email": row[1],
+			"firstname": row[2],
+			"lastname": row[3],
+			"gender": row[4],
+			"street": row[5],
+			"plz": row[6],
+			"city": row[7]
+		}
 
 def get_price_info(product, timestamp, member = True):
 	result = 0
@@ -59,7 +47,7 @@ def get_price_info(product, timestamp, member = True):
 
 	return result
 
-def invoice(user, title, subject, start=0, stop=0):
+def get_invoice_data(user, start=0, stop=0):
 	connection = sqlite3.connect('shop.db')
 	c = connection.cursor()
 	startcondition = ""
@@ -69,7 +57,23 @@ def invoice(user, title, subject, start=0, stop=0):
 		startcondition = " AND timestamp >= %d" % start
 	if stop > 0:
 		stopcondition = " AND timestamp <= %d" % stop
-	
+
+	c.execute("SELECT date(timestamp, 'unixepoch', 'localtime'), time(timestamp, 'unixepoch', 'localtime'), products.name, purchases.product, purchases.timestamp FROM purchases, products WHERE user = ? AND products.id = purchases.product" + startcondition + stopcondition + " ORDER BY timestamp;", (user,))
+
+	result = []
+	for row in c:
+		result.append({
+			"date": row[0],
+			"time": row[1],
+			"product": row[2],
+			"price": int(get_price_info(row[3], row[4], user != 0)),
+		})
+
+	c.close()
+
+	return result
+
+def generate_invoice_tex(user, title, subject, start=0, stop=0):
 	userinfo = get_user_info(user)
 
 	result = "\\documentclass[ktt-template,12pt,pagesize=auto,enlargefirstpage=on,paper=a4]{scrlttr2}\n\n"
@@ -79,11 +83,17 @@ def invoice(user, title, subject, start=0, stop=0):
 
 	result+= "\\setkomavar{subject}{%s}\n" % subject
 	result+= "\\setkomavar{toname}{%s %s}\n" % (userinfo["firstname"], userinfo["lastname"])
-	result+= "\\setkomavar{toaddress}{%s\\newline\\newline\\textbf{%s}}\n\n" % (userinfo["street"], userinfo["city"])
+	result+= "\\setkomavar{toaddress}{%s\\newline\\newline\\textbf{%d %s}}\n\n" % (userinfo["street"], userinfo["plz"], userinfo["city"])
 
 	result+= "\\begin{document}\n"
 	result+= "\t\\begin{letter}{}\n"
-	result+= "\t\t\\opening{Sehr geehrter Herr %s,}\n\n" % userinfo["lastname"]
+
+	if userinfo["gender"] == "masculinum":
+		result+= "\t\t\\opening{Sehr geehrter Herr %s,}\n\n" % userinfo["lastname"]
+	elif userinfo["gender"] == "femininum":
+		result+= "\t\t\\opening{Sehr geehrte Frau %s,}\n\n" % userinfo["lastname"]
+	else:
+		result+= "\t\t\\opening{Sehr geehrte/r Frau/Herr %s,}\n\n" % userinfo["lastname"]
 
 	result+= "\t\twir erlauben uns, Ihnen für den Verzehr von Speisen und Getränken wie folgt zu berechnen:\n\n"
 
@@ -93,18 +103,16 @@ def invoice(user, title, subject, start=0, stop=0):
 	result += "\t\t\t\tDatum		& Uhrzeit	& Artikel	& Preis\\\\\n"
 	result += "\t\t\t\t\\hline\n"
 
-	c.execute("SELECT date(timestamp, 'unixepoch', 'localtime'), time(timestamp, 'unixepoch', 'localtime'), products.name, purchases.product, purchases.timestamp FROM purchases, products WHERE user = ? AND products.id = purchases.product" + startcondition + stopcondition + " ORDER BY timestamp;", (user,))
 	lastdate = ""
 	total = 0
-	for row in c:
-		price = get_price_info(row[3], row[4], user != 0)
-		total += price
+	for row in get_invoice_data(user, start, stop):
+		total += row["price"]
 
-		if lastdate != row[0]:
-			result += "\t\t\t\t%s\t& %s\t& %s\t& %d,%02d Euro\\\\\n" % (row[0], row[1], row[2], price / 100, price % 100)
-			lastdate = row[0]
+		if lastdate != row["date"]:
+			result += "\t\t\t\t%s\t& %s\t& %s\t& %d,%02d Euro\\\\\n" % (row["date"], row["time"], row["product"], row["price"] / 100, row["price"] % 100)
+			lastdate = row["date"]
 		else:
-			result += "\t\t\t\t%s\t& %s\t& %s\t& %d,%02d Euro\\\\\n" % ("           ", row[1], row[2], price / 100, price % 100)
+			result += "\t\t\t\t%s\t& %s\t& %s\t& %d,%02d Euro\\\\\n" % ("           ", row["time"], row["product"], row["price"] / 100, row["price"] % 100)
 
 	result += "\t\t\t\t\\hline\n"
 	result += "\t\t\t\t\\multicolumn{3}{|l|}{Summe:} & %d,%02d Euro\\\\\n" % (total / 100, total % 100)
@@ -120,7 +128,45 @@ def invoice(user, title, subject, start=0, stop=0):
 	result += "\t\\end{letter}\n"
 	result += "\\end{document}"
 
-	c.close()
+	return result
+
+def generate_invoice_text(user, title, subject, start=0, stop=0):
+	userinfo = get_user_info(user)
+	result = ""
+
+	if userinfo["gender"] == "masculinum":
+		result+= "Sehr geehrter Herr %s,\n\n" % userinfo["lastname"]
+	elif userinfo["gender"] == "femininum":
+		result+= "Sehr geehrte Frau %s,\n\n" % userinfo["lastname"]
+	else:
+		result+= "Sehr geehrte/r Frau/Herr %s,\n\n" % userinfo["lastname"]
+
+	result+= "wir erlauben uns, Ihnen für den Verzehr von Speisen und Getränken wie folgt zu berechnen:\n\n"
+
+	lastdate = ""
+	total = 0
+	namelength = 0
+	for row in get_invoice_data(user, start, stop):
+		if len(row["product"]) > namelength:
+			namelength = len(row["product"])
+
+	result += " +------------+----------+-" + namelength * "-" + "-+----------+\n"
+	result += " | Datum      | Uhrzeit  | Artikel" + (namelength - len("Artikel")) * " " + " | Preis    |\n"
+	result += " +------------+----------+-" + namelength * "-" + "-+----------+\n"
+	for row in get_invoice_data(user, start, stop):
+		total += row["price"]
+
+		if lastdate != row["date"]:
+			result += " | %s | %s | %s | %3d,%02d € |\n" % (row["date"], row["time"], row["product"] + (namelength - len(row["product"])) * " ", row["price"] / 100, row["price"] % 100)
+			lastdate = row["date"]
+		else:
+			result += " | %s | %s | %s | %3d,%02d € |\n" % ("          ", row["time"], row["product"] + (namelength - len(row["product"])) * " ", row["price"] / 100, row["price"] % 100)
+	result += " +------------+----------+-" + namelength * "-" + "-+----------+\n"
+	result += " | Summe:                  " + namelength * " " + " | %3d,%02d € |\n" % (total / 100, total % 100)
+	result += " +-------------------------" + namelength * "-" + "-+----------+\n\n"
+
+	result += "Umsatzsteuer wird nicht erhoben, da Kreativität trifft Technik e.V. als Kleinunternehmen\n"
+	result += "der Regelung des § 19 Abs. 1 UStG unterfällt.\n\n"
 
 	return result
 
@@ -137,13 +183,14 @@ def generate_mail(receiver, subject, message, pdfdata, cc = None):
 	if cc != None:
 		msg["Cc"] = cc
 	msg["Subject"] = Header(subject, 'utf-8')
-	msg.preamble = "Please use a MIME aware email client"
+	msg.preamble = "Please use a MIME aware email client!"
 
 	msg.attach(MIMEText(message, 'plain', 'utf-8'))
 
-	pdf = MIMEApplication(pdfdata, 'pdf')
-	pdf.add_header('Content-Disposition', 'attachment', filename = 'rechnung.pdf')
-	msg.attach(pdf)
+	if pdfdata is not None:
+		pdf = MIMEApplication(pdfdata, 'pdf')
+		pdf.add_header('Content-Disposition', 'attachment', filename = 'rechnung.pdf')
+		msg.attach(pdf)
 
 	return msg
 
@@ -179,21 +226,90 @@ def daily(timestamp = time.time()):
 	requested = datetime.datetime.fromtimestamp(timestamp)
 
 	# timestamps for previous day
-	dstop = requested.replace(hour = 0, minute = 0, second = 0) - datetime.timedelta(seconds = 1)
-	dstart = requested.replace(hour = 0, minute = 0, second = 0) - datetime.timedelta(days = 1)
+	dstop = requested.replace(hour = 8, minute = 0, second = 0) - datetime.timedelta(seconds = 1)
+	dstart = requested.replace(hour = 8, minute = 0, second = 0) - datetime.timedelta(days = 1)
+	if dstop > requested:
+		dstop -= datetime.timedelta(days = 1)
+		dstart -= datetime.timedelta(days = 1)
 	stop = int(dstop.strftime("%s"))
 	start = int(dstart.strftime("%s"))
 
 	title = "Getränke Rechnung %04d-%02d-%02d" % (dstart.year, dstart.month, dstart.day)
-	subject = "Getränke Zwischenstand %02d.%02d.%04d" % (dstart.day, dstart.month, dstart.year)
+	subject = "Getränke Zwischenstand %02d.%02d.%04d %02d:%02d Uhr bis %02d.%02d.%04d %02d:%02d Uhr" % (dstart.day, dstart.month, dstart.year, dstart.hour, dstart.minute, dstop.day, dstop.month, dstop.year, dstop.hour, dstop.minute)
 
 	for user in get_users_with_purches(start, stop):
 		userinfo = get_user_info(user)
-		receiver = "%s %s <%s>" % (userinfo["firstname"], userinfo["lastname"], userinfo["email"])
-		tex  = invoice(user, title, subject, start, stop)
-		pdf  = generate_pdf(tex)
-		mail = generate_mail(receiver, title, subject, pdf)
-		send_mail(mail, userinfo["email"])
-		print(user)
-
-daily()
+		if userinfo is not None:
+			receiver = "%s %s <%s>" % (userinfo["firstname"], userinfo["lastname"], userinfo["email"])
+			tex  = generate_invoice_tex(user, title, subject, start, stop)
+			msg  = generate_invoice_text(user, title, subject, start, stop)
+			pdf  = generate_pdf(tex)
+			mail = generate_mail(receiver, title, msg, pdf)
+			send_mail(mail, userinfo["email"])
+			print("Sent invoice to", userinfo["firstname"], userinfo["lastname"])
+		else:
+			print("Can't send invoice for missing user with the following id:", user)
+
+def monthly(timestamp = time.time()):
+	print("monthly invoice()")
+
+def backup(timestamp = time.time()):
+	print("backup()")
+
+def get_stock_data():
+	connection = sqlite3.connect('shop.db')
+	c = connection.cursor()
+	result = []
+
+	c.execute("SELECT name,amount FROM products")
+
+	for row in c:
+		result.append((row[0],row[1]))
+
+	c.close()
+
+	return result
+
+def gen_stock_asciitable():
+	stock = get_stock_data()
+	longest_name = 0
+	longest_amount = 0
+	asciitable = ""
+	for element in stock:
+		if len(element[0]) > longest_name:
+			longest_name = len(element[0])
+		if len(str(element[1])) > longest_amount:
+			longest_amount = len(str(element[1]))
+
+	asciitable = "+-" + longest_name * "-" + "-+-" + longest_amount * "-" + "-+\n"
+	asciitable += "| " + "Produkt" + (longest_name - len("Produkt")) * " " + " | " + (longest_amount - 1) * " " + "#" + " |\n"
+	asciitable += "+-" + longest_name * "-" + "-+-" + longest_amount * "-" + "-+\n"
+	for product in stock:
+		asciitable += "| " + product[0] + (longest_name - len(product[0])) * " " + " | " + (longest_amount - len(str(product[1]))) * " " + str(product[1]) + " |\n"
+	asciitable += "+-" + longest_name * "-" + "-+-" + longest_amount * "-" + "-+\n"
+
+	return asciitable
+
+def gen_stock_mail():
+	msg = MIMEMultipart()
+	msg["From"] = "KtT Shop System <shop@kreativitaet-trifft-technik.de>"
+	msg["To"] = "KtT Einkaufsteam <einkauf@kreativitaet-trifft-technik.de>"
+	msg["Subject"] = Header("Aktueller Warenbestand", 'utf-8')
+	msg.preamble = "Please use a MIME aware email client!"
+	msg.attach(MIMEText(gen_stock_asciitable(), 'plain', 'utf-8'))
+	return msg
+
+def weekly():
+	send_mail(gen_stock_mail(), "einkauf@kreativitaet-trifft-technik.de")
+
+def backup():
+	pass # TODO
+
+if sys.argv[1] == "daily":
+	daily()
+elif sys.argv[1] == "weekly":
+	weekly()
+elif sys.argv[1] == "monthly":
+	print("TODO: not yet implemented")
+else:
+	print("not supported!")
diff --git a/invoice/import-users.py b/invoice/import-users.py
new file mode 100755
index 0000000..096b071
--- /dev/null
+++ b/invoice/import-users.py
@@ -0,0 +1,23 @@
+#!/usr/bin/env python3
+import csv, sqlite3, sys
+
+title_to_gender = {
+	"Herr": "masculinum",
+	"Frau": "femininum"
+}
+
+data = csv.reader(open(sys.argv[1], 'r', encoding='iso-8859-1'), 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[2], "unknown")
+	t = (int(row[0]), row[1], row[3], row[4], gender, row[5], int(row[6]), row[7])
+	c.execute("INSERT OR REPLACE INTO users ('id', 'email', 'firstname', 'lastname', 'gender', 'street', 'plz', 'city') VALUES (?, ?, ?, ?, ?, ?, ?, ?);", t)
+
+connection.commit()
+c.close()
diff --git a/main.vala b/main.vala
index b570b23..318653f 100644
--- a/main.vala
+++ b/main.vala
@@ -20,52 +20,54 @@ public static int main(string[] args) {
 }
 
 public static bool interpret(string data) {
+	int64 timestamp = (new DateTime.now_utc()).to_unix();
+
 	if(data.has_prefix("USER ")) {
 		string str_id = data.substring(5);
-		uint64 id = uint64.parse(str_id);
+		int32 id = int.parse(str_id);
 
 		/* check if data has valid format */
-		if(data != "USER %llu".printf(id)) {
-			stdout.printf("ungültige Benutzernummer: %s\n", data);
+		if(data != "USER %d".printf(id)) {
+			stdout.printf("[%lld] ungültige Benutzernummer: %s\n", timestamp, data);
 			return false;
 		}
 
 		if(db.is_logged_in()) {
-			stdout.printf("Last User forgot to logout!\n");
+			stdout.printf("[%lld] Last User forgot to logout!\n", timestamp);
 			db.logout();
 		}
 
-		stdout.printf("Login: %llu\n".printf(id));
+		stdout.printf("[%lld] Login: %d\n", timestamp, id);
 		return db.login(id);
 	} else if(data == "GUEST") {
 		if(db.is_logged_in()) {
-			stdout.printf("Last User forgot to logout!\n");
+			stdout.printf("[%lld] Last User forgot to logout!\n", timestamp);
 			db.logout();
 		}
 
-		stdout.printf("Login: Guest\n");
+		stdout.printf("[%lld] Login: Guest\n", timestamp);
 		return db.login(0);
 	} else if(data == "UNDO") {
 		if(!db.is_logged_in()) {
-			stdout.printf("Can't undo if not logged in!\n");
+			stdout.printf("[%lld] Can't undo if not logged in!\n", timestamp);
 			return false;
 		} else {
-			stdout.printf("Undo last purchase!\n");
+			stdout.printf("[%lld] Undo last purchase!\n", timestamp);
 			return db.undo();
 		}
 	} else if(data == "LOGOUT") {
 		if(db.is_logged_in()) {
-			stdout.printf("Logout!\n");
+			stdout.printf("[%lld] Logout!\n", timestamp);
 			return db.logout();
 		}
 
 		return false;
 	} else if(data == "STOCK") {
 		if(!db.is_logged_in()) {
-			stdout.printf("You must be logged in to go into the stock mode\n");
+			stdout.printf("[%lld] You must be logged in to go into the stock mode\n", timestamp);
 			return false;
 		} else {
-			stdout.printf("Going into stock mode!\n");
+			stdout.printf("[%lld] Going into stock mode!\n", timestamp);
 			return db.go_into_stock_mode();
 		}
 	} else if(db.is_in_stock_mode()) {
@@ -74,11 +76,11 @@ public static bool interpret(string data) {
 
 			/* check if data has valid format */
 			if(data != "%llu".printf(id)) {
-				stdout.printf("ungültiges Produkt: %s\n", data);
+				stdout.printf("[%lld] ungültiges Produkt: %s\n", timestamp, data);
 				return false;
 			}
 
-			stdout.printf("wähle Produkt: %s\n", db.get_product_name(id));
+			stdout.printf("[%lld] wähle Produkt: %s\n", timestamp, db.get_product_name(id));
 
 			return db.choose_stock_product(id);
 		} else {
@@ -86,11 +88,11 @@ public static bool interpret(string data) {
 
 			/* check if data has valid format */
 			if(data != "AMOUNT %llu".printf(amount)) {
-				stdout.printf("ungültiges Produkt: %s\n", data);
+				stdout.printf("[%lld] ungültiges Produkt: %s\n", timestamp, data);
 				return false;
 			}
 
-			stdout.printf("zum Bestand hinzufügen: %llu\n", amount);
+			stdout.printf("[%lld] zum Bestand hinzufügen: %llu\n", timestamp, amount);
 
 			return db.add_stock_product(amount);
 		}
@@ -99,15 +101,15 @@ public static bool interpret(string data) {
 
 		/* check if data has valid format */
 		if(data != "%llu".printf(id)) {
-			stdout.printf("ungültiges Produkt: %s\n", data);
+			stdout.printf("[%lld] ungültiges Produkt: %s\n", timestamp, data);
 			return false;
 		}
 
 		if(db.buy(id)) {
-			stdout.printf("gekaufter Artikel: %s\n", db.get_product_name(id));
+			stdout.printf("[%lld] gekaufter Artikel: %s\n", timestamp, db.get_product_name(id));
 			return true;
 		} else {
-			stdout.printf("Kauf fehlgeschlagen!\n");
+			stdout.printf("[%lld] Kauf fehlgeschlagen!\n", timestamp);
 			return false;
 		}
 	}