invoice generator

4 files changed, 317 insertions, 0 deletions

diff --git a/invoice/footer-line.png b/invoice/footer-line.png
new file mode 100644
index 0000000..b02cbaa
--- /dev/null
+++ b/invoice/footer-line.png
diff --git a/invoice/generate-invoice.py b/invoice/generate-invoice.py
new file mode 100755
index 0000000..0c1cdbe
--- /dev/null
+++ b/invoice/generate-invoice.py
@@ -0,0 +1,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))
diff --git a/invoice/ktt-template.lco b/invoice/ktt-template.lco
new file mode 100644
index 0000000..ed3a9ad
--- /dev/null
+++ b/invoice/ktt-template.lco
@@ -0,0 +1,222 @@
+\usepackage{wallpaper}
+\usepackage{ifpdf}
+\usepackage{longtable}
+\usepackage[utf8]{inputenc}
+\usepackage[german]{babel}
+\usepackage{graphicx}
+\usepackage{pgf}
+\usepackage{calc}
+
+%%
+%% This is file `DIN.lco',
+%% generated with the docstrip utility.
+%%
+%% The original source files were:
+%%
+%% scrkvers.dtx  (with options: `trace')
+%% scrklco.dtx  (with options: `lco,DIN,head')
+%% scrklco.dtx  (with options: `lco,DIN,body')
+%% 
+%% Copyright (c) 1994-2012
+%% Markus Kohm and any individual authors listed elsewhere in this file.
+%% 
+%% This file was generated from file(s) of the KOMA-Script bundle.
+%% ---------------------------------------------------------------
+%% 
+%% It may be distributed under the conditions of the
+%% LaTeX Project Public License in the version distributed together
+%% with KOMA-Script, see file `lppl.txt' or `lppl-de.txt'.
+%% 
+%% This file may only be distributed together with a copy of the
+%% KOMA-Script bundle.  You may however distribute the
+%% KOMA-Script bundle without all such generated files.  See also
+%% `lppl.txt' or `lppl-de.txt' for additional information.
+%% 
+%% The list of files belonging to KOMA-Script distribution is given in
+%% the file `manifest.txt'.  See also `lppl.txt' or `lppl-de.txt' for
+%% additional information.
+%% 
+%% If this file is a beta version, you may not be allowed to distribute
+%% it.  Look at the comments below to see if this is the case.
+%% 
+%% English and German manuals are part of KOMA-Script bundle.
+%% ----------------------------------------------------------
+%% 
+%% See `README'.
+%% 
+%% The KOMA-Script bundle (but maybe not this file) was based upon the
+%% LaTeX 2.09 Script family created by Frank Neukam 1993 and the LaTeX2e
+%% standard classes created by The LaTeX3 Project 1994-1996.  You may
+%% find a complete unmodified copy of LaTeX2e at
+%% <http://www.ctan.org/pub/tex-archive/macros/latex/base/>.
+%% 
+%%% From File: scrkvers.dtx
+\begingroup
+  \makeatletter
+  \ifx\KOMAScriptVersion\undefined
+    \newcommand*{\@CheckKOMAScriptVersion}[1]{%
+      \gdef\KOMAScriptVersion{#1}%
+    }%
+  \else
+    \newcommand*{\@CheckKOMAScriptVersion}[1]{%
+      \def\@tempa{#1}%
+      \ifx\KOMAScriptVersion\@tempa\else
+        \@latex@warning@no@line{%
+          \noexpand\KOMAScriptVersion\space is
+          `\KOMAScriptVersion',\MessageBreak
+          but `#1' was expected!\MessageBreak
+          You should not use classes, packages or files
+          from\MessageBreak
+          different KOMA-Script versions%
+        }%
+      \fi
+    }
+  \fi
+  \@CheckKOMAScriptVersion{2012/03/08 v3.10a KOMA-Script}%
+\endgroup
+%%% From File: scrklco.dtx
+\ProvidesFile{%
+  ktt-template%
+  .lco}[\KOMAScriptVersion\space letter-class-option]
+\providecommand*\LCOWarningNoLine[2]{%
+  \LCOWarning{#1}{#2\@gobble}%
+}
+\providecommand*\LCOWarning[2]{%
+  \GenericWarning{%
+    (#1)\@spaces\@spaces\@spaces\@spaces\@spaces\@spaces\@spaces
+  }{%
+    Letter class option  #1 Warning: #2%
+  }%
+}
+%%% From File: scrklco.dtx
+\@ifundefined{scr@fromname@var}{%
+  \LCOWarningNoLine{%
+    DIN%
+  }{%
+    This letter class option file was made only\MessageBreak
+    to be used with KOMA-Script letter class\MessageBreak
+    `scrlttr2'. Use with other classes may result\MessageBreak
+    in a lot of errors%
+  }%
+}{}
+\LetterOptionNeedsPapersize{%
+  DIN%
+}{a4}
+\setkomavar*{fromzipcode}{%
+  D}
+\@setplength{foldmarkhpos}{3.5mm}
+\@setplength{tfoldmarkvpos}{%
+  105mm}
+\@setplength{mfoldmarkvpos}{\z@}
+\@setplength{bfoldmarkvpos}{%
+  210mm}
+\@setplength{lfoldmarkhpos}{\z@}
+\@setplength{toaddrvpos}{%
+  45mm}
+\@setplength{toaddrhpos}{%
+  20mm}
+\@setplength{toaddrwidth}{%
+  85mm}
+\@setplength{toaddrheight}{%
+  45mm}
+\@setplength{toaddrindent}{%
+  \z@}
+\@setplength{backaddrheight}{%
+  5mm}
+\@setplength{specialmailindent}{\fill}
+\@setplength{specialmailrightindent}{1em}
+\@setplength{locwidth}{%
+  \z@}
+\@setplength{firstheadvpos}{%
+  8mm}
+\@setplength{firstheadwidth}{%
+  \paperwidth}
+\ifdim \useplength{toaddrhpos}>\z@
+  \@addtoplength[-2]{firstheadwidth}{\useplength{toaddrhpos}}
+\else
+  \@addtoplength[2]{firstheadwidth}{\useplength{toaddrhpos}}
+\fi
+\@setplength{firstfootwidth}{\useplength{firstheadwidth}}
+\ifnum \scr@compatibility >\@nameuse{scr@v@2.9t}\relax
+  \@setplength{firstfootvpos}{\paperheight}
+  \@addtoplength{firstfootvpos}{-30mm}
+\else
+  \@setplength{firstfootvpos}{1in}
+  \@addtoplength{firstfootvpos}{\topmargin}
+  \@addtoplength{firstfootvpos}{\headheight}
+  \@addtoplength{firstfootvpos}{\headsep}
+  \@addtoplength{firstfootvpos}{\textheight}
+  \@addtoplength{firstfootvpos}{\footskip}
+\fi
+\@setplength{refvpos}{%
+  98.5mm}
+\@setplength{refaftervskip}{%
+  \baselineskip}
+\@setplength{refwidth}{0pt}
+\@setplength{sigindent}{0mm}
+\@setplength{sigbeforevskip}{2\baselineskip}
+\let\raggedsignature=\centering
+
+%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+
+\usepackage{calc}
+\makeatletter
+\@setplength{refwidth}{\textwidth}
+\@setplength{refhpos}{\useplength{toaddrhpos}}
+\setlength{\oddsidemargin}{\useplength{toaddrhpos}-1in}
+\makeatother
+
+
+\setlength{\parskip}{2ex}
+\setlength{\parindent}{0pt}
+
+\KOMAoptions{fromlogo=true}
+\setkomavar{fromname}{Kreativität trifft Technik e.V.}
+\setkomavar{fromaddress}{Binsenstraße 3\\26129 Oldenburg}
+\setkomavar{fromemail}{vorstand@kreativitaet-trifft-technik.de}
+\setkomavar{fromurl}{www.kreativitaet-trifft-technik.de}
+\setkomavar{fromlogo}{\includegraphics[scale=0.7]{logo}}
+\newcommand\twitter{@KtT\_OL}
+\newcommand\amtsgericht{\textbf{Amtsgericht Oldenburg}\\VR 201044}
+\newcommand\finanzamt{\textbf{Finanzamt Oldenburg}\\\textbf{Steuer Nr.:} 64/220/18413}
+\newcommand\vorstand{\textbf{Vorstand}\\Patrick Günther, Martin Hilscher, Eike Frost}
+\setkomavar{frombank}{\textbf{Raiffeisenbank Oldenburg}\\
+		\textbf{Kontonummer}: 370 18 500\\
+		\textbf{Bankleitzahl}: 280 602 28
+}
+
+\firsthead{%
+	\hfill\includegraphics[scale=0.7]{logo}
+}
+
+\firstfoot{%
+	\scriptsize{
+		\parbox{ \useplength{firstfootwidth} }{ 
+			%\rule{180mm}{0.5pt} 
+			\begin{tabular}{ll}
+				\textbf{\usekomavar{fromname}}\\
+				\usekomavar{fromaddress}\\
+				\\
+				\amtsgericht
+			\end{tabular}\hfill
+			\begin{tabular}{ll}
+				\textbf{Mail:} \usekomavar{fromemail}\\
+				\textbf{Web:} \usekomavar{fromurl}\\
+				\textbf{Twitter:} \twitter\\
+				\\
+				\vorstand
+			\end{tabular}\hfill
+			\begin{tabular}{ll}
+				\usekomavar{frombank}\\
+				\\
+				\finanzamt
+			\end{tabular}
+       }}
+}
+
+\LLCornerWallPaper{1}{footer-line}
+
+\endinput
+%%
+%% End of file `DIN.lco'.
+% vim: set filetype=tex  :EOF
diff --git a/invoice/logo.png b/invoice/logo.png
new file mode 100644
index 0000000..788a208
--- /dev/null
+++ b/invoice/logo.png