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 a node type ebkonto with an attribute ebiban containing the International Bank Account Number (IBAN) of the accounts that should be uploaded to that respective graph. Each bank account should only be configured in one of the configured graphs. If it is configured in more than one, the statements for that account will be uploaded to the first graph in which an ebkonto instance exists for it.

Moreover, a node type ebcamt053datei with attributes ebid, ebcamt053, and ebhochgeladenam has to exist. For each camt.053 XML file downloaded by epicsgraph.py, the IBAN is extracted and a graph for that IBAN is looked up. If the IBAN is not configured in any of the configured graphs, the file is ignored. The ebid of a camt.053 XML file is its SHA-256 hash. If a file with that hash is already present in the graph, it is not uploaded again. Otherwise, a new ebcamt053datei instance is created, the XML file is uploaded to its ebcamt053 attribute, and the current date is set in its ebhochgeladenam attribute. The calculation of the ebid as well as any further processing is done by the graph and not handled by epicsgraph.py.

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).