summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
-rw-r--r--invoice/dbhelper.py80
-rwxr-xr-xinvoice/generate-invoice.py99
2 files changed, 93 insertions, 86 deletions
diff --git a/invoice/dbhelper.py b/invoice/dbhelper.py
new file mode 100644
index 0000000..b04030d
--- /dev/null
+++ b/invoice/dbhelper.py
@@ -0,0 +1,80 @@
+#!/usr/bin/env python3
+#-*- coding: utf-8 -*-
+
+import sqlite3
+
+class DB(object):
+ def __init__(self, dbfile='shop.db'):
+ self.__connection = sqlite3.connect(dbfile)
+
+ def get_user_info(self, userid):
+ c = self.__connection.cursor()
+ c.execute("SELECT id, email, firstname, lastname, gender, street, plz, city FROM users WHERE id = ?;", (userid,))
+ row = c.fetchone()
+ c.close()
+
+ 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_invoice_data(self, user, start=0, stop=0):
+ c = self.__connection.cursor()
+ startcondition = ""
+ stopcondition = ""
+
+ if start > 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'), productname, price FROM invoice WHERE user = ?" + startcondition + stopcondition + " ORDER BY timestamp;", (user,))
+
+ result = []
+ for row in c:
+ result.append({
+ "date": row[0],
+ "time": row[1],
+ "product": row[2],
+ "price": row[3],
+ })
+
+ c.close()
+
+ return result
+
+ def get_invoice_amount(self, user, start=0, stop=0):
+ query = "SELECT SUM(price) FROM invoice WHERE user = ? AND timestamp >= ? AND timestamp <= ?";
+ amount = 0
+
+ c = self.__connection.cursor()
+ c.execute(query, (user, start, stop))
+
+ for row in c:
+ amount += row[0]
+
+ c.close()
+ return amount
+
+ def get_users_with_purchases(self, start, stop):
+ result = []
+
+ c = self.__connection.cursor()
+
+ c.execute("SELECT user FROM sales WHERE timestamp >= ? AND timestamp <= ? GROUP BY user ORDER BY user;", (start,stop))
+
+ for row in c:
+ result.append(row[0])
+
+ c.close()
+
+ return result
diff --git a/invoice/generate-invoice.py b/invoice/generate-invoice.py
index b710da5..8ed8bd1 100755
--- a/invoice/generate-invoice.py
+++ b/invoice/generate-invoice.py
@@ -1,91 +1,18 @@
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
-import datetime, sqlite3, os, sys, smtplib, subprocess, time, tempfile, email.utils
+import datetime, 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
from email.header import Header
+from dbhelper import DB
from config import *
-def get_user_info(userid):
- connection = sqlite3.connect('shop.db')
- c = connection.cursor()
- c.execute("SELECT id, email, firstname, lastname, gender, street, plz, city FROM users WHERE id = ?;", (userid,))
- row = c.fetchone()
- c.close()
-
- 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_invoice_data(user, 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
-
- c.execute("SELECT date(timestamp, 'unixepoch', 'localtime'), time(timestamp, 'unixepoch', 'localtime'), productname, price FROM invoice WHERE user = ?" + startcondition + stopcondition + " ORDER BY timestamp;", (user,))
-
- result = []
- for row in c:
- result.append({
- "date": row[0],
- "time": row[1],
- "product": row[2],
- "price": row[3],
- })
-
- c.close()
-
- return result
-
-def get_invoice_amount(user, start=0, stop=0):
- query = "SELECT SUM(price) FROM invoice WHERE user = ? AND timestamp >= ? AND timestamp <= ?";
- amount = 0
-
- connection = sqlite3.connect('shop.db')
- c = connection.cursor()
- c.execute(query, (user, start, stop))
-
- for row in c:
- amount += row[0]
-
- c.close()
- return amount
-
-def get_users_with_purchases(start, stop):
- result = []
-
- connection = sqlite3.connect('shop.db')
- c = connection.cursor()
-
- c.execute("SELECT user FROM sales WHERE timestamp >= ? AND timestamp <= ? GROUP BY user ORDER BY user;", (start,stop))
-
- for row in c:
- result.append(row[0])
-
- c.close()
-
- return result
+db = DB()
def generate_invoice_tex(user, title, subject, start=0, stop=0, temporary=False):
- userinfo = get_user_info(user)
+ userinfo = db.get_user_info(user)
result = "\\documentclass[ktt-template,12pt,pagesize=auto,enlargefirstpage=on,paper=a4]{scrlttr2}\n\n"
result+= "\\title{%s}\n" % title
@@ -119,7 +46,7 @@ def generate_invoice_tex(user, title, subject, start=0, stop=0, temporary=False)
lastdate = ""
total = 0
- for row in get_invoice_data(user, start, stop):
+ for row in db.get_invoice_data(user, start, stop):
total += row["price"]
row["product"] = row["product"].replace("&", "\\&")
@@ -154,7 +81,7 @@ def generate_invoice_tex(user, title, subject, start=0, stop=0, temporary=False)
return result
def generate_invoice_text(user, title, subject, start=0, stop=0, temporary=False):
- userinfo = get_user_info(user)
+ userinfo = db.get_user_info(user)
result = ""
if userinfo["gender"] == "masculinum":
@@ -169,14 +96,14 @@ def generate_invoice_text(user, title, subject, start=0, stop=0, temporary=False
lastdate = ""
total = 0
namelength = 0
- for row in get_invoice_data(user, start, stop):
+ for row in db.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):
+ for row in db.get_invoice_data(user, start, stop):
total += row["price"]
if lastdate != row["date"]:
@@ -265,8 +192,8 @@ def daily(timestamp = time.time()):
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_purchases(start, stop):
- userinfo = get_user_info(user)
+ for user in db.get_users_with_purchases(start, stop):
+ userinfo = db.get_user_info(user)
if userinfo is not None:
receiver = "%s %s <%s>" % (userinfo["firstname"], userinfo["lastname"], userinfo["email"])
msg = generate_invoice_text(user, title, subject, start, stop, True)
@@ -290,17 +217,17 @@ def monthly(timestamp = time.time()):
invoices = {}
invoicedata = []
- for user in get_users_with_purchases(start, stop):
+ for user in db.get_users_with_purchases(start, stop):
number += 1
subject = "Rechnung Nr. %04d%02d5%03d" % (dstart.year, dstart.month, number)
- userinfo = get_user_info(user)
+ userinfo = db.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)
invoices["%04d%02d5%03d_%s_%s.pdf" % (dstart.year, dstart.month, number, userinfo["firstname"], userinfo["lastname"])] = pdf
- amount = get_invoice_amount(user, start, stop)
+ amount = db.get_invoice_amount(user, start, stop)
invoicedata.append({"userid": user, "lastname": userinfo["lastname"], "firstname": userinfo["firstname"], "invoiceid": "%04d%02d5%03d" % (dstart.year, dstart.month, number), "amount": amount})
mail = generate_mail(receiver, title, msg, pdf, timestamp)
send_mail(mail, userinfo["email"])