invoice generator

1 files changed, 95 insertions, 0 deletions

diff --git a/invoice/generate-invoice.py b/invoice/generate-invoice.py
new file mode 100755
index 0000000..0c1cdbe
--- /dev/null
+++ b/invoice/generate-invoice.py
@@ -0,0 +1,95 @@
+#!/usr/bin/env python3
+# -*- coding: utf-8 -*-
+import datetime, sqlite3, sys
+
+def invoice(user, title, subject, tofirstname, tolastname, toaddress, start=0, stop=0):
+	connection = sqlite3.connect('shop.db')
+	c = connection.cursor()
+	startcondition = ""
+	stopcondition = ""
+
+	if start > 0:
+		startcondition = " AND timestamp >= %d" % start
+	if stop > 0:
+		stopcondition = " AND timestamp <= %d" % stop
+		
+
+	result = "\\documentclass[ktt-template,12pt,pagesize=auto,enlargefirstpage=on,paper=a4]{scrlttr2}\n\n"
+	result+= "\\title{%s}\n" % title
+	result+= "\\author{Kreativität trifft Technik}\n"
+	result+= "\\date{\\today}\n\n"
+
+	result+= "\\setkomavar{subject}{%s}\n" % subject
+	result+= "\\setkomavar{toname}{%s %s}\n" % (tofirstname, tolastname)
+	result+= "\\setkomavar{toaddress}{%s}\n\n" % toaddress
+
+	result+= "\\begin{document}\n"
+	result+= "\t\\begin{letter}{}\n"
+	result+= "\t\t\\opening{Sehr geehrter Herr %s,}\n\n" % tolastname
+
+	result+= "\t\twir erlauben uns, Ihnen für den Verzehr von Speisen und Getränken wie folgt zu berechnen:\n\n"
+
+	result += "\t\t\\begin{footnotesize}\n"
+	result += "\t\t\t\\begin{longtable}{|l|l|l|}\n"
+	result += "\t\t\t\t\\hline\n"
+	result += "\t\t\t\tDatum		& Uhrzeit	& Artikel\\\\\n"
+	result += "\t\t\t\t\\hline\n"
+
+	c.execute("SELECT date(timestamp, 'unixepoch', 'localtime'), time(timestamp, 'unixepoch', 'localtime'), products.name FROM purchases, products WHERE user = ? AND products.id = purchases.product" + startcondition + stopcondition + " ORDER BY timestamp;", (user,))
+	lastdate = ""
+	for row in c:
+		if lastdate != row[0]:
+			result += "\t\t\t\t%s\t& %s\t& %s\\\\\n" % (row[0], row[1], row[2])
+			lastdate = row[0]
+		else:
+			result += "\t\t\t\t%s\t& %s\t& %s\\\\\n" % ("           ", row[1], row[2])
+
+	result += "\t\t\t\t\\hline\n"
+	result += "\t\t\t\\end{longtable}\n"
+	result += "\t\t\\end{footnotesize}\n\n"
+
+	result += "\t\tUmsatzsteuer wird nicht erhoben, da Kreativität trifft Technik e.V. als Kleinunternehmen\n"
+	result += "\t\tder Regelung des § 19 Abs. 1 UStG unterfällt.\n\n"
+
+	result += "\t\t\\closing{Mit freundlichen Grüßen}\n\n"
+
+	result += "\t\\end{letter}\n"
+	result += "\\end{document}"
+
+	c.close()
+
+	return result
+
+def get_users_with_purches(start, stop):
+	result = []
+
+	connection = sqlite3.connect('shop.db')
+	c = connection.cursor()
+
+	c.execute("SELECT user FROM purchases WHERE timestamp >= ? AND timestamp <= ? GROUP BY user ORDER BY user;", (start,stop))
+
+	for row in c:
+		result.append(row[0])
+
+	c.close()
+
+	return result
+
+##############################
+# User Code
+##############################
+
+
+user = 2342
+start = int(datetime.datetime(2012, 5, 19, 0, 0, 0).strftime("%s"))
+stop = int(datetime.datetime(2012, 5, 19, 23, 59, 59).strftime("%s"))
+title = "Getränke Rechnung 05/2012" # TODO: autogen from supplied start/stop information
+subject = "Rechnung Nr. 2012050001" # TODO: autogen somehow
+
+tofirstname = "VORNAME" # TODO: autogen from user db
+tolastname = "NACHNAME" # TODO: autogen from user db
+toaddress = "ADDRESS" # TODO: autogen from user db
+
+# this can be used to find users, which should get invoice
+print("users:", get_users_with_purches(start, stop), file=sys.stderr)
+print(invoice(user, title, subject, tofirstname, tolastname, toaddress, start, stop))