CsvToSepaDD.py

#!/usr/bin/python3

import argparse
import csv
import datetime
import pprint
import string
import sys
import pathlib

# automate loading of used modules
Script = pathlib.Path(sys.argv[0])
sys.path.append(f'{Script.parent}/PySepaDD')
sys.path.append(f'{Script.parent}/PyIbanCheck')

import PySepaDD
from ibancheck import IBANcheck

# if true, all debits will occur as a single item at the creditor's bank
# account.  Otherwise, for each debtor there will be a single item.
DEFAULT_BATCH = True
DEFAULT_CURRENCY = 'EUR'

class spgVereinExcelDialect(csv.Dialect):
    '''
    Describes the default properties of a CSV file generated by SPG Verein
    using the "Excel" preset.
    '''
    delimiter = ';'
    quotechar = '"'
    lineterminator = '\r\n'
    quoting = csv.QUOTE_MINIMAL
    strict = True

csv.register_dialect('spg-verein-excel', spgVereinExcelDialect)

class calcDialect(csv.Dialect):
    '''
    Describes the default properties of a CSV file generated by
    Libreoffice/OpenOffice Calc.
    '''
    delimiter = ','
    quotechar = '"'
    doublequote = True
    skipinitialspace = False
    lineterminator = '\n'
    quoting = csv.QUOTE_MINIMAL
    strict = True

csv.register_dialect('calc-default', calcDialect)



def parseDate(dateString):
    '''
    Returns the given string converted to a date object
    '''
    allowedFormats = [
            '%Y-%m-%d',
            '%d.%m.%Y',
            '%d.%m.%y',
        ]
    for form in allowedFormats:
        try:
            return datetime.datetime.strptime(dateString, form)
        except ValueError:
            pass

    raise ValueError('%s: date format not recognized' % dateString)



def euroToCents(amountString):
    '''
    Converts a string representing an amount of euros into an amount of cents
    represented as int
    '''

    delim = None
    if ',' in amountString:
        delim = ','
    elif '.' in amountString:
        delim = '.'


    if not delim:
        cents = amountString + '00'
        return int(cents)

    else:
        delimIndex = amountString.find(delim)
        cents = amountString[:delimIndex]
        amountString = amountString[delimIndex + 1:]

        for iteration in ['ten cents', 'one cents']:
            if amountString:
                cents += amountString[0]
                amountString = amountString[1:]
            else:
                cents += '0'

        if amountString:
            print >> sys.stderr, 'Warning: amount had more than two decimal places, ignoring the remainder'

    return int(cents)




def csvToSepa(args):
    '''
    Converts the SEPA direct debit data from a given CSV file to SEPA XML
    '''

    ibc = IBANcheck()

    config = None
    with open(args.configfile, 'r') as f:
        try:
            config = eval(f.read())
        except SyntaxError:
            print(f'\nDie Datei "{args.configfile}" ist vermutlich keine gültige Konfigurationsdatei!')
            print('Bitte die Reihenfolge der Parameter beachten!')
            print('weitere Informationen: CsvToSepaDD.py convert --help')
            exit()

    with open(args.inputfile, 'r') as inFile, open(args.outputfile, 'w') as outFile:
        csvReader = csv.DictReader(inFile, dialect=config['csv_dialect'])
        sepaWriter = PySepaDD.PySepaDD(config)

        # check for all required fields:
        #
        # amount is as full currency, e. g. 42, 42.0, 42.00, 42,0, 42,00
        # type may be any of:
        #     FRST - first of a sequence of debits
        #     RCUR - recurring debit of a sequence. Must not be used if there
        #            was no debit of type FRST yet in the past!
        #     OOFF - non-recurring debit, mandate is valid only for one
        #            transaction
        #     FNAL   final debit in a sequence
        # collection_date is the date when the debit will be executed
        # mandate_id is a unique value representing the debtor's mandate
        # mandate_date is the date when the mandate was created/granted/signed
        #     by the debtor
        # FIXME: separaten kontoinhaber unterstuetzen?
        requiredFields = ['first_name', 'last_name', 'IBAN', 'BIC', 'amount',
                'type', 'collection_date', 'mandate_id', 'mandate_date',
                'description']
        error = False
        for field in requiredFields:
            if not field in csvReader.fieldnames:
                print >> sys.stderr, 'missing field in CSV header: %s' % field
                error = True
        if error:
            raise KeyError

        for row in csvReader:
            row['IBAN'] = row['IBAN'].replace(' ','').upper()
            if not ibc.check(row['IBAN']):
                print(f'IBAN ({row["IBAN"]}) is not valid. Name: {row["first_name"]} {row["last_name"]}',file=sys.stderr)

            row['IBAN'] = row['IBAN'].replace(' ','').upper()
            payment = {
                    'name': f'{row["first_name"]} {row["last_name"]}',
                    'IBAN': row['IBAN'].replace(' ','').upper(),
                    'BIC': row['BIC'],
                    'amount': euroToCents(row['amount']),
                    'type': row['type'],
                    'collection_date': parseDate(row['collection_date']),
                    'mandate_id': row['mandate_id'],
                    'mandate_date': parseDate(row['mandate_date']),
                    'description': row['description'],
            }
            sepaWriter.add_payment(payment)


        sepaXml = sepaWriter.export()
        outFile.write(sepaXml)



def createConfig(args):
    '''Interactively creates a configuation file'''

    name = input('your name: ')
    iban = input('your IBAN: ').replace(' ','')
    bic = input('your BIC: ')
    creditorId = input('your creditor id: ')
    csvDialect = input('CSV dialect [%s]: ' % ' '.join(sorted(csv.list_dialects())))

    # we use a PySepaDD-compatible configuration dict for simplicity
    config = {
            'name': name,
            'IBAN': iban,
            'BIC':  bic,
            'creditor_id': creditorId,
            'currency': DEFAULT_CURRENCY,
            'batch': DEFAULT_BATCH,
            'csv_dialect': csvDialect,
    }

    with open(args.configfile, 'w') as f:
        pprint.pprint(config, stream=f, indent=4)

    print (f'''Configuration written to file {args.configfile}. 
You can edit this file with a text 
editor if you need to change something later.''')



if __name__ == '__main__':

    parser = argparse.ArgumentParser(description='Create SEPA XML direct debit files from CSV')
    subparsers = parser.add_subparsers()

    genConfigParser = subparsers.add_parser('genconfig', help='generate a configuration file')
    genConfigParser.set_defaults(func=createConfig)
    genConfigParser.add_argument('configfile', help='name of the configuration file')

    convertParser = subparsers.add_parser('convert', help='convert a CSV file to a SEPA XML file')
    convertParser.set_defaults(func=csvToSepa)
    convertParser.add_argument('configfile', help='configuration file to use')
    convertParser.add_argument('inputfile', help='input file')
    convertParser.add_argument('outputfile', help='output file')

    try:
        args = parser.parse_args()
    except SystemExit:
        exit()
    try:
        args.func(args)
    except AttributeError:
        parser.parse_args(['-h'])