diff options
| -rw-r--r-- | create_db.sql | 14 | ||||
| -rwxr-xr-x | invoice/generate-invoice.py | 112 | ||||
| -rw-r--r-- | invoice/ktt-template.lco | 1 |
3 files changed, 94 insertions, 33 deletions
diff --git a/create_db.sql b/create_db.sql index 8c348b6..14e6046 100644 --- a/create_db.sql +++ b/create_db.sql @@ -24,9 +24,9 @@ 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(4001686721445,'Haribo Colorado',18); +INSERT INTO products VALUES(4003586000477,'Chipsfrisch Ungarisch',18); +INSERT INTO products VALUES(4003586000491,'Chipsfrisch 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); @@ -35,7 +35,7 @@ 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(4104450004383,'Vilsa Limette',64); INSERT INTO products VALUES(4104450005878,'Vilsa Classic',1); INSERT INTO products VALUES(5000159407236,'Mars',16); INSERT INTO products VALUES(5000159407397,'Snickers',6); @@ -43,11 +43,11 @@ 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(5449000017888,'Coca Cola',56); +INSERT INTO products VALUES(5449000017895,'Coca 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(5449000134264,'Coca 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); diff --git a/invoice/generate-invoice.py b/invoice/generate-invoice.py index 6688d83..e414b5a 100755 --- a/invoice/generate-invoice.py +++ b/invoice/generate-invoice.py @@ -1,6 +1,6 @@ #!/usr/bin/env python3 # -*- coding: utf-8 -*- -import datetime, sqlite3, os, sys, smtplib, subprocess, time, tempfile +import datetime, sqlite3, os, sys, smtplib, subprocess, time, tempfile, email.utils from email.mime.multipart import MIMEMultipart from email.mime.application import MIMEApplication from email.mime.text import MIMEText @@ -73,7 +73,7 @@ def get_invoice_data(user, start=0, stop=0): return result -def generate_invoice_tex(user, title, subject, start=0, stop=0): +def generate_invoice_tex(user, title, subject, start=0, stop=0, temporary=False): userinfo = get_user_info(user) result = "\\documentclass[ktt-template,12pt,pagesize=auto,enlargefirstpage=on,paper=a4]{scrlttr2}\n\n" @@ -98,10 +98,13 @@ def generate_invoice_tex(user, title, subject, start=0, stop=0): 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|l|}\n" + result += "\t\t\t\\begin{longtable}{|p{2cm}|p{1.8cm}|p{5cm}|p{2cm}|}\n" result += "\t\t\t\t\\hline\n" - result += "\t\t\t\tDatum & Uhrzeit & Artikel & Preis\\\\\n" + result += "\t\t\t\t\\textbf{Datum} & \\textbf{Uhrzeit} & \\textbf{Artikel} & \\textbf{Preis}\\\\\n" result += "\t\t\t\t\\hline\n" + result += "\\endhead\n" + result += "\t\t\t\t\\hline\n" + result += "\\endfoot\n" lastdate = "" total = 0 @@ -109,20 +112,27 @@ def generate_invoice_tex(user, title, subject, start=0, stop=0): total += row["price"] 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) + result += "\t\t\t\t%s\t& %s\t& %s\t& \\EUR{%d,%02d}\\\\\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["time"], row["product"], row["price"] / 100, row["price"] % 100) + result += "\t\t\t\t%s\t& %s\t& %s\t& \\EUR{%d,%02d}\\\\\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) - result += "\t\t\t\t\\hline\n" + result += "\t\t\t\t\\multicolumn{3}{|l|}{Summe:} & \\EUR{%d,%02d}\\\\\n" % (total / 100, total % 100) 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" + if temporary is True: + result += "\t\tBei dieser Abrechnung handelt es sich lediglich um einen Zwischenstand. Die\n" + result += "\t\tHauptrechnung wird einmal monatlich getrennt zugestellt und der Gesamtbetrag\n" + result += "\t\twird dann vom angegebenen Bankkonto eingezogen.\n\n" + else: + result += "\t\tDer Gesamtbetrag wird in den nächsten Tagen von dem angegebenen Bankkonto\n" + result += "\t\teingezogen.\n\n" + result += "\t\t\\closing{Mit freundlichen Grüßen}\n\n" result += "\t\\end{letter}\n" @@ -130,7 +140,7 @@ def generate_invoice_tex(user, title, subject, start=0, stop=0): return result -def generate_invoice_text(user, title, subject, start=0, stop=0): +def generate_invoice_text(user, title, subject, start=0, stop=0, temporary=False): userinfo = get_user_info(user) result = "" @@ -168,11 +178,20 @@ def generate_invoice_text(user, title, subject, start=0, stop=0): 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" + if temporary is True: + result += "Bei dieser Abrechnung handelt es sich lediglich um einen Zwischenstand. Die\n" + result += "Hauptrechnung wird einmal monatlich getrennt zugestellt und der Gesamtbetrag\n" + result += "wird dann vom angegebenen Bankkonto eingezogen.\n\n" + else: + result += "Der Gesamtbetrag wird in den nächsten Tagen von dem angegebenen Bankkonto\n" + result += "eingezogen.\n\n" + return result -def generate_mail(receiver, subject, message, pdfdata, cc = None): +def generate_mail(receiver, subject, message, pdfdata, timestamp=time.time(), cc = None): msg = MIMEMultipart() - msg["From"] = "KtT Shop System <shop@kreativitaet-trifft-technik.de>" + msg["From"] = "KtT-Shopsystem <shop@kreativitaet-trifft-technik.de>" + msg["Date"] = email.utils.formatdate(timestamp, True) try: if receiver.encode("ascii"): @@ -207,7 +226,7 @@ def send_mail(mail, receiver): server.sendmail(mail["From"], receiver, maildata) server.quit() -def get_users_with_purches(start, stop): +def get_users_with_purchases(start, stop): result = [] connection = sqlite3.connect('shop.db') @@ -234,27 +253,70 @@ def daily(timestamp = time.time()): 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 %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) + title = "Getränkerechnung %04d-%02d-%02d" % (dstart.year, dstart.month, dstart.day) + 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): + for user in get_users_with_purchases(start, stop): userinfo = get_user_info(user) 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) + tex = generate_invoice_tex(user, title, subject, start, stop, True) + msg = generate_invoice_text(user, title, subject, start, stop, True) pdf = generate_pdf(tex) - mail = generate_mail(receiver, title, msg, pdf) + mail = generate_mail(receiver, title, msg, pdf, timestamp) 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()") + requested = datetime.datetime.fromtimestamp(timestamp) + # timestamps for previous month + dstop = requested.replace(hour = 8, minute = 0, second = 0, day = 16) - datetime.timedelta(seconds = 1) + if dstop > requested: + dstop = dstop.replace(month = dstop.month -1) + dstart = dstop.replace(month = dstop.month -1) + stop = int(dstop.strftime("%s")) + start = int(dstart.strftime("%s")) + + title = "Getränkerechnung %04d/%02d" % (dstart.year, dstart.month) + number = 0 + + for user in get_users_with_purchases(start, stop): + number += 1 + subject = "Rechnung Nr.%04d%02d5%03d" % (dstart.year, dstart.month, number) + userinfo = get_user_info(user) + if userinfo is not None: + receiver = "%s %s <%s>" % (userinfo["firstname"], userinfo["lastname"], userinfo["email"]) + tex = generate_invoice_tex(user, title, subject, start, stop, False) + msg = generate_invoice_text(user, title, subject, start, stop, False) + pdf = generate_pdf(tex) + mail = generate_mail(receiver, title, msg, pdf, timestamp, cc = "schatzmeister@kreativitaet-trifft-technik.de") + send_mail(mail, [userinfo["email"], "schatzmeister@kreativitaet-trifft-oldenburg.de"]) + print("Sent invoice to", userinfo["firstname"], userinfo["lastname"]) + else: + print("Can't send invoice for missing user with the following id:", user) + +def backup(): + timestamp = time.time() + dt = datetime.datetime.fromtimestamp(timestamp) -def backup(timestamp = time.time()): - print("backup()") + msg = MIMEMultipart() + msg["From"] = "KtT-Shopsystem <shop@kreativitaet-trifft-technik.de>" + msg["Date"] = email.utils.formatdate(timestamp, True) + msg["To"] = "KtT-Shopsystem Backups <shop-backup@kreativitaet-trifft-technik.de>" + msg["Subject"] = "Backup KtT-Shopsystem %04d-%02d-%02d %02d:%02d" % (dt.year, dt.month, dt.day, dt.hour, dt.minute) + msg.preamble = "Please use a MIME aware email client!" + + msg.attach(MIMEText("You can find a backup of 'shop.db' attached to this mail.", 'plain', 'utf-8')) + + dbfile = open('shop.db', 'rb') + attachment = MIMEApplication(dbfile.read()) + attachment.add_header('Content-Disposition', 'attachment', filename = 'shop.db') + msg.attach(attachment) + dbfile.close() + + send_mail(msg, "shop-backup@kreativitaet-trifft-technik.de") def get_stock_data(): connection = sqlite3.connect('shop.db') @@ -292,7 +354,7 @@ def gen_stock_asciitable(): def gen_stock_mail(): msg = MIMEMultipart() - msg["From"] = "KtT Shop System <shop@kreativitaet-trifft-technik.de>" + msg["From"] = "KtT-Shopsystem <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!" @@ -302,14 +364,12 @@ def gen_stock_mail(): def weekly(): send_mail(gen_stock_mail(), "einkauf@kreativitaet-trifft-technik.de") -def backup(): - pass # TODO - if sys.argv[1] == "daily": daily() + backup() elif sys.argv[1] == "weekly": weekly() elif sys.argv[1] == "monthly": - print("TODO: not yet implemented") + monthly() else: print("not supported!") diff --git a/invoice/ktt-template.lco b/invoice/ktt-template.lco index 22d527f..599afe8 100644 --- a/invoice/ktt-template.lco +++ b/invoice/ktt-template.lco @@ -6,6 +6,7 @@ \usepackage{graphicx} \usepackage{pgf} \usepackage{calc} +\usepackage[right]{eurosym} %% %% This is file `DIN.lco', |