1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
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))
|
