uweziegenhagen.de

import pandas as pd # pandas selbst
 
import jinja2
import os
import codecs
 
def cleanPLZ(stringToClean):
    return stringToClean.replace('.0','')
 
# Eine Funktion, die die Adresse vorbereitet
# keine überflüssigen Leerzeichen, wenn Feld nicht gefüllt ist
def prepareAddress(id, vorname, name, strasse, plz, ort):
    address = '' # + str(id) + ': '
    if len(vorname)==0:
        address = address + name
    else:
        address = address + vorname + ' ' + name
    if len(strasse)>0:
        address = address + ", " + strasse    
    if len(plz)>0:
        address = address + ", " + plz + ' ' + ort
    return address
 
# Zerlege die Gesamtsumme in einzelne Bestandteile, um Zahlwort auszugeben
# Siehe http://www.steuer-schutzbrief.de/fileadmin/downloads/BMF-Schreiben/BMF-Schreiben-Zuwendungsbestaetigung-2012-08-30.pdf
def kardinal(summenstring,separator,indicator):
	zahlen = {"1" : "Eins", "2":"Zwei", "3":"Drei", "4":"Vier","5":"Fünf","6":"Sechs","7":"Sieben","8":"Acht","9":"Neun","0":"Null"}
	zahlwort = ''
	zahl = summenstring.split(',')[0]
	for i in zahl:
		zahlwort = zahlwort + zahlen[i]+ separator
	return indicator + separator + zahlwort + indicator
 
# Diverse Konfigurationsvariablen
# http://chrisalbon.com/python/pandas_list_unique_values_in_column.html
# Set ipython's max row display
pd.set_option('display.max_row', 10000)
# Set iPython's max column width to 50
pd.set_option('display.max_columns', 50)
# A set number format to 2 digits
pd.set_option('display.float_format', lambda x: '%.2f' % x)
# http://stackoverflow.com/questions/20625582/how-to-deal-with-this-pandas-warning
# komische Fehlermeldung beim Drop von Spalten loswerden
pd.options.mode.chained_assignment = None  # default='warn'
 
##################### Prepare Stammdaten ##################### 
# lade Daten
stammdaten = pd.read_excel('Stammdaten.xlsx', 'Tabelle1')
 
# Remove NaN values by " for strings
stammdaten['Vorname'].fillna(value='',inplace=True)
stammdaten['Name'].fillna(value='',inplace=True)
stammdaten['Adresszusatz'].fillna(value='',inplace=True)
stammdaten['Strasse'].fillna(value='',inplace=True)
stammdaten['PLZ'].fillna(value='',inplace=True)
stammdaten['Ort'].fillna(value='',inplace=True)
stammdaten['eMail'].fillna(value='',inplace=True)
stammdaten['Mitgliedsart'].fillna(value='',inplace=True)
 
# convert PLZ to string
# apply str function first, then run cleanPLZ on the string
stammdaten['PLZ']= stammdaten.PLZ.apply(str)
stammdaten['PLZ']= stammdaten.PLZ.apply(cleanPLZ)
 
# entferne ehemalige Mitglieder
stammdaten  = stammdaten[stammdaten.Mitgliedsart != 'E']
 
################################ Prepare Buchungen
# lade die Buchungen
buchungen = pd.read_excel('Buchungen.xlsx', 'Tabelle1')
buchungen[['Klasse']] = buchungen[['Klasse']].astype(str)
buchungen[['Betrag']] = buchungen[['Betrag']].astype(float)
 
# Change format of 'Buchungstag' to datetime
buchungen['Buchungstag'] = pd.to_datetime(buchungen['Buchungstag'],dayfirst=True)
 
# Remove NaN values by " for strings or 0 for numbers 
buchungen['Vorgang'].fillna(value=0,inplace=True)
buchungen['Empfänger'].fillna(value='',inplace=True)
buchungen['Verwendungszweck'].fillna(value='',inplace=True)
buchungen['Kategorie'].fillna(value='',inplace=True)
buchungen['Klasse'].fillna(value='',inplace=True)
buchungen['Relevant']=True
buchungen.Kategorie.str.match('^Aufnahmegebühr|Zweckspende|Mitgliedsbeitrag|Spende$')
 
# entferne irrelevante Buchungen
buchungen = buchungen[buchungen.Relevant != False]
 
# http://stackoverflow.com/questions/20937538/how-to-display-pandas-dataframe-using-a-format-string-for-columns
#pd.options.display.float_format = '{:,.2f} EUR'.format
 
class CommaFloatFormatter:
    def __mod__(self, x):
        return str(x).replace('.',',')
 
latex_jinja_env = jinja2.Environment(
    block_start_string = '\BLOCK{',
    block_end_string = '}',
    variable_start_string = '\VAR{',
    variable_end_string = '}',
    comment_start_string = '\#{',
    comment_end_string = '}',
    line_statement_prefix = '%-',
    line_comment_prefix = '%#',
    trim_blocks = True,
    autoescape = False,
    loader = jinja2.FileSystemLoader(os.path.abspath('.'))
)
 
# Laden des Templates aus einer Datei
template = latex_jinja_env.get_template('Sammelbestaetigung_Geldzuwendung.tex')
 
for index, row in stammdaten.iterrows():
    print("ID:",row["ID"])
    address = prepareAddress(row["ID"],row['Vorname'],row['Name'],row['Strasse'],row['PLZ'],row['Ort'])
    print(address)
    beitraege = buchungen[buchungen.Klasse.str.contains('^' +  str(row["ID"]) + '$')]
    beitraege.drop('Klasse',axis=1,inplace=True)
    beitraege.drop('Verwendungszweck',axis=1,inplace=True)
    beitraege.drop('Relevant',axis=1,inplace=True)
    beitraege.drop('Empfänger',axis=1,inplace=True)
    beitraege.drop('Konto',axis=1,inplace=True)
    beitraege.drop('Vorgang',axis=1,inplace=True)
    gesamtsumme = beitraege.sum()[0]
 
    beitraege['Buchungstag'] = beitraege['Buchungstag'].apply(lambda x: x.strftime('%d-%m-%Y'))
    texbuchungen = beitraege.applymap(lambda x: str(x).replace('.',',0')).to_latex(index=False)    
    texbuchungen = beitraege.to_latex(index=False)    
    summe = str(gesamtsumme).replace('.',',0') + ' EUR'
    # kardinal(summe,'-','xxx')
    dokument = template.render(Spender=address, ID=row['ID'],Summe=summe,kardinal=kardinal(summe,'-','xxx'),Buchungen=texbuchungen)
    with codecs.open(''+str(row['ID']) + ".tex", "w","utf-8") as letter:
        letter.write(dokument);
        letter.close();
        os.system("pdflatex -interaction=batchmode " + str(row['ID']) + ".tex")
 
os.system("del *.log")
os.system("del *.aux")

import pandas as pd
import numpy as np
import time as t
 
# Anpassungen an der Pandas-Ausgabe
pd.set_option('display.float_format', lambda x: '%.2f' % x)
# http://stackoverflow.com/questions/11707586/python-pandas-widen-output-display
pd.set_option('display.height', 1000)
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)
 
# lies die Stammdaten aus Excel
mitglieder = pd.read_excel('Mitglieder.xlsx', 'Tabelle1')
# konvertier die Mitgliedsnummer in einen String
mitglieder['Klasse'] = mitglieder['Klasse'].apply(int).apply(str)
# Ehemalige Mitglieder interessieren nicht
mitglieder = mitglieder[mitglieder.Mitgliedsart.str.contains('A|F|O')]
 
# lies die Buchungen ein
buchungen = pd.read_excel('Buchungen.xlsx', 'Sheet')
# entferne alle Zeilen, die keinen 'Mitgliedbeitrag' enthalten
buchungen = buchungen[buchungen.Kategorie.str.contains('Mitgliedsbeitrag')]
 
# entferne Zeilen ohne Betrag oder Klasse
buchungen = buchungen[np.isfinite(buchungen['Betrag'])]
buchungen = buchungen[np.isfinite(buchungen['Klasse'])]
 
# Konvertiere den Buchungstag in ein Pandas-Datum
buchungen['Buchungstag'] = pd.to_datetime(buchungen['Buchungstag'],dayfirst=True,format='%d.%m.%Y') 
 
# Füge neue Spalten für Quartal und Monat hinzu
buchungen['Quartal'] = buchungen['Buchungstag'].dt.quarter
buchungen['Monat'] = buchungen['Buchungstag'].dt.month
 
# Wandle 'Klasse' von Float => Integer => String 
buchungen['Klasse'] = buchungen['Klasse'].apply(int).apply(str) 
 
# Führe einen right join durch, wir wollen auch die Datensätze von Mitgliedern haben, die noch aktiv sind, aber noch nichts bezahlt haben
buchungen = pd.merge(buchungen,mitglieder, how='right', on=['Klasse', 'Klasse'])
 
# Durch den right Join haben wir jetzt einige NaN (Not a Number) Zellen, die wir durch 0.0 ersetzen
# Dadurch erzeugen wir Dummy Datensätze, die aber nicht stören
buchungen['Betrag'].fillna(value=0,inplace=True)
buchungen['Buchungstag'].fillna(value=pd.Timestamp('20160101'),inplace=True)
# Einen Eintrag für die Monatsspalte nehmen wir auch vor, darüber wird pivotisiert
buchungen['Monat'] = buchungen['Buchungstag'].dt.month
 
# Erstellung der Pivot-Table
pivotTable = pd.pivot_table(buchungen,dropna=False,margins=True,index=['Name'],values=['Betrag'],columns=['Monat'],aggfunc=np.sum)
 
print(pivotTable)
 
# Optional: Rausschreiben der Tabelle nach Excel
#pivotTable.to_excel('pivotisiert.xlsx')