From b55a06210ac8551ac0d9a29ddfaad764f584849e Mon Sep 17 00:00:00 2001 From: Sebastian Reichel Date: Sat, 23 Feb 2013 18:25:51 +0100 Subject: out all db related methods into a class --- invoice/dbhelper.py | 80 ++++++++++++++++++++++++++++++++++++ invoice/generate-invoice.py | 99 ++++++--------------------------------------- 2 files changed, 93 insertions(+), 86 deletions(-) create mode 100644 invoice/dbhelper.py 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"]) -- cgit v1.2.3