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/generate-invoice.py | 99 ++++++--------------------------------------- 1 file changed, 13 insertions(+), 86 deletions(-) (limited to 'invoice/generate-invoice.py') 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