EPICS Graph Import

This PHP script uses the Ruby EBICS client EPICS to get bank statements in MT940 format and store them in configured graphs.

Installation

wkhtmltopdf, Ruby and the EPICS gem have to be installed:

# apt install wkhtmltox ruby
# gem install epics

Then, you can run

# composer create-project --stability dev graphit/epicsgraph <target folder>

to install the script and its dependencies (the graph client library) into a given target folder.

EBICS Initialisation

First, we need the following information from the bank:

URL of the EBICS server,
host ID of the EBICS server ("BANK1" in the example below),
partner ID of the company ("COMPANY" in the example), and
user ID of the authorised person ("USER1" in the example).

With this information, we can start the initialisation process in an interactive Ruby session:

# irb
irb(main):001:0> require 'epics'
=> true
irb(main):002:0> e=Epics::Client.setup('PaSsW0Rd', 'https://ebics.example.com/ebicsweb/ebicsweb',
        'BANK1', 'USER1', 'COMPANY')
=> #<Epics::Client:00000000
     @keys=["A006", "X002", "E002"],
     @user_id="USER1",
     @partner_id="COMPANY"
irb(main):003:0> e.INI
=> true
irb(main):004:0> e.HIA
=> true
irb(main):005:0> e.save_keys('keys/Bank1.01.Pre-INI.keys')
=> 0000
irb(main):006:0> e.save_ini_letter('Bank 1, Inc.', 'keys/Bank1.02.INI-Letter.html')
=> "keys/Bank1.02.INI-Letter.html"
irb(main):007:0> ^D

The keys are saved for the later finalisation of the initialisation and the initialisation letter is converted to PDF by wkhtmltopdf in order to print and sign it and send it to the bank.

# wkhtmltopdf keys/Bank1.02.INI-Letter.html keys/Bank1.02.INI-Letter.pdf

Once, the bank has authorised the keys, we can finalise the initialisation process:

# irb
irb(main):001:0> require 'epics'
=> true
irb(main):002:0> e = Epics::Client.new(File.open('keys/Bank1.01.Pre-INI.keys'), 'PaSsW0Rd',
        'https://ebics.example.com/ebicsweb/ebicsweb', 'BANK1', 'USER1', 'COMPANY')
=> #<Epics::Client:0000000
     @keys=["A006", "X002", "E002"],
     @user_id="USER1",
     @partner_id="COMPANY"
irb(main):003:0> e.HPB
=> [#<Epics::Key:0x00000000000000 @key=#<OpenSSL::PKey::RSA:0x00000000000000>>,
        #<Epics::Key:0x00000000000000 @key=#<OpenSSL::PKey::RSA:0x00000000000000>>]
irb(main):004:0> e.save_keys('keys/Bank1.keys')
=> 0000
irb(main):005:0> ^D

The initialisation is now finished and the EBICS account can be used.

If it is necessary, the available transaction types for an EBICS account can be retrieved by e.HAA (after require 'epics' and e = Epics::Client.new(…)). A comprehensive overview of transaction types (in German) can, e.g., be found in the windataWiki: http://wiki.windata.de/index.php?title=EBICS-Auftragsarten

Script Configuration

The epicsgraph.py script needs configuration information for one or more EBICS accounts and one or more graphs, where the account statements will be stored. These information are stored in a JSON file config.json in the script folder. An example can be found in config.json.example:

{ "ebics":
  [ { "url": "https://ebics.example.com/ebicsweb/ebicsweb",
      "host": "BANK1",
      "partner": "COMPANY",
      "user": "USER1",
      "keyfile": "/path/to/epicsgraph/keys/Bank1.keys",
      "password": "PaSsW0Rd" } ],
  "graphs":
  [ "tls://graph.example.com:6669" ],
  "cafile": "/path/to/ca.crt.pem",
  "lofile": "/path/to/local.srv.pem" }

config.json as well as the keys/ subfolder are included in the .gitignore file, since they contain sensitive information (the keys and passwords for the EBICS accounts) and should never be committed to a Git repository. A separate backup of these should be kept in a safe place. The file name epics-config-keys.tar.gz is included in .gitignore for this purpose:

# tar cvzf epics-config-keys.tar.gz config.json keys/

Graph Configuration

epicsgraph.py assumes the existence of node types bankkonto and kontokorrentkreditkonto, both with attributes kontonummer for the account number and bankleitzahl for the bank code, respectively. The instances of bankkonto in all configured graphs will be the ones for which account statements are uploaded to the respective graphs. For instances of kontokorrentkreditkonto, account balances will be set in the graphs.

Moreover, a node type mt940datei with an edge type bankkonto_mt940datei and attributes name and datei has to exist. epicsgraph.py generates names epics-{$bankleitzahl}-{$kontonummer}-{$date}[-v{$n}].mt940 for the mt940datei instances, which are uploaded to the graph. It is the responsibility of the graph to further process these MT940 files.

If an mt940datei instance for an account and a date already exists in the graph, the contents are compared and a new instance with a suffix -v2 is created only if the contents differ. If -v2 already exists, its contents are compared to the new contents and -v3 is created if they differ etc.

A node type kontokorrentkreditkontomonat with edge types kontokorrentkreditkonto_kontokorrentkreditkontomonat and finanzwesenmonat_kontokorrentkreditkontomonat and attributes monat, datum and kontostand has to exist, where epicsgraph.sty records the latest account balances found for a given month. Instances of kontokorrentkreditkontomonat are created if necessary.

Usage

If epicsgraph is called without commandline arguments, it will get the account statements for the previous seven days (not including the current day) and will only generate output in case of errors. This is intended to be used as a cronjob.

If a different date span is intended, exactly two arguments, the start and end date of the span, are required in YYYY-MM-DD format.

If epicsgraph is called with the option -v, it generates more verbose output also in success cases.

With the option -r the raw MT940 data is echoed instead of processing and importing into the graph(s).