Ein anderer Artikel beschreibt warum man manchmal besser zwei Anwendungen für die Buchhaltung und die Rechnungsschreibung verwendet.

Bei der folgenden Beschreibung handelt es sich weniger um fertigen Code für InvoicePlane oder GNUCash, da einige lokale Voraussetzungen Berücksichtigung finden müssen. Es ist eher ein Leitfaden für Linuxer was berücksichtigt werden muss. Den hier folgenden Ablauf und die beispielhaft gezeigten Code Snippets müssen also den eigenen Bedürfnissen entsprechend angepaßt und in sinnvolle eigene Abläufe/Programme intergriert werden. Das meiste der Beispiele hier wird in der MySQL Workbench, mit phpMyAdmin oder dem MySQL Client ausgeführt werden.

Voraussetzung ist das in GNUCash entweder mit Hilfe von AqBanking (und Kontoauszug abholen) oder manuell die Eingangs Zahlungen bereits erfaßt worden sind.


Export der offenen Posten aus InvoicePlane

Im Normalfall hilft es die offenen Posten zunächst bereit zu stellen für einen späteren Abgleich. Auf der InvoicePlane Installation hilft uns dabei ein View auf die Daten für den Export, hier wird ein Custom Feld beim Client client_custom_custno für die Kundennummer verwendet:

CREATE OR REPLACE
  VIEW ip_client_balance_combined
   (invoice_id, invoice_number, invoice_date_created, client_id, invoice_balance, client_custom_custno)
    AS 
    SELECT    i.invoice_id, i.invoice_number, i.invoice_date_created, c.client_id, ia.invoice_balance, cc.client_custom_custno
        FROM  ip_invoices AS i
        JOIN  ip_invoice_amounts AS ia ON ia.invoice_id = i.invoice_id
        JOIN  ip_clients AS c ON c.client_id = i.client_id
        JOIN  ip_client_custom AS cc ON cc.client_id = i.client_id
        WHERE i.invoice_status_id=2
        AND   ia.invoice_balance > 0
        AND   c.client_active = 1 
        ORDER BY i.invoice_date_created,i.invoice_number;
SELECT * FROM ip_client_balance_combined;

Die Ausgabe des mysql Client wird mit dem Parameter --skip-column-names in einen Bereich des Webauftrittes, der kennwortgeschützt ist, als TAB separierte CSV in einem Verzeichnis ip_exporter als open_balance.csv bereitgestellt. Bei uns ist das Bestandteil eines Skripts welches, cron gesteuert, jede Nacht eine neue Version bereitstellt.


Import auf dem GNUCash System

Die GNUCash Installation sollte aus Gründen der Vereinfachung auf MySQL/MariaDB laufen bzw. umgestellt werden. Der Import der Daten auf dem GNUCash System geht einfach mit

wget -q --http-user=[htpasswd user] --http-password=[das sichere Kennwort] https://deine.server.domain/invoiceplane/ip_exporter/open_balance.csv -O open_balance.csv

weiter und wird dann in die GNUCash Datenbank in die Tabelle ip_open_balance übertragen. Zuvor wird die Tabelle ip_open_balance erstellt bzw. geleert, da der aktuelle Stand eingefügt werden soll:

CREATE TABLE IF NOT EXISTS ip_open_balance (
  id int(11) NOT NULL AUTO_INCREMENT,
  invoice_id int(11) NOT NULL,
  invoice_number varchar(16) COLLATE utf8_bin NOT NULL,
  invoice_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  client_id int(11) NOT NULL,
  amount int(9) NOT NULL,
  custno varchar(32) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY invoice_id (invoice_id),
  UNIQUE KEY invoice_number (invoice_number),
  KEY client_id (client_id),
  KEY custno (custno),
  KEY amount (amount),
  KEY invoice_date (invoice_date)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
TRUNCATE ip_open_balance;

Wenn die Datenbank auf einem anderen System liegt einfach mit bash hinein skripten:

#!/bin/bash
while IFS=$'\t' read invoice_id, invoice_number, invoice_date, client_id, amount, custno
do
    amount=`echo ${amount} | tr -d '.'` # using integer numbers in gnucash
# import into sql
cat << SQL | mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASS} ${DB}
INSERT INTO ip_open_balance
        SET invoice_id=${invoice_id},
            invoice_number="${invoice_number}",
            invoice_date="${invoice_date}",
            client_id=${client_id},
            amount=${amount},
            custno="${custno}";
SQL
done < open_balance.csv

Das Matching vorbereiten

Als erstes müssen die Kontoauszüge von der Bank geholt werden und elektronisch (z.B. mit AqBanking) oder manuell in GNUCash erfaßt werden. An dieser Stelle wird viel vereinfacht, wenn AqBanking bereits den fertigen Verwendungszweck zur Verfügung gestellt hat. Die möglichen Matches auf Zahlungen können eingeschränkt werden und hier ist es wichtig seinen Kontenrahmen und die Zuordnung zu kennen. Beim SKR03 liegen die in Frage kommenden Konten meist im Bereich zwischen 1000 - Kasse bis 1299 - Bankkonten. Hier ein erster Blick darauf:

CREATE OR REPLACE
  VIEW ip_income_transactions
   (account_type,account,from_account,value,entry_date,post_date,description,splits_guid)
    AS 
    SELECT a.account_type, a.code AS account, s.memo AS from_account, s.value_num AS value, t.enter_date, t.post_date, t.description, s.guid
      FROM splits AS s
      JOIN transactions AS t ON s.tx_guid = t.guid
      LEFT JOIN accounts AS a ON s.account_guid = a.guid
     WHERE s.account_guid IN (SELECT guid FROM accounts 
                               WHERE (account_type='ASSET' OR account_type='CASH') 
                                 AND code >= 1000
                                 AND code <= 1299
       AND s.value_num != 0
       AND s.memo !=""
       AND t.description REGEXP (SELECT GROUP_CONCAT(invoice_number SEPARATOR '|') FROM ip_open_balance)
       ORDER BY t.post_date DESC;

Für Sätze die später als bezahlt Sätze erkannt werden wird hier eine Tabelle ip_matching_records verwendet:

CREATE TABLE IF NOT EXISTS ip_matching_records (
  invoice_number varchar(16) CHARACTER SET utf8 NOT NULL DEFAULT '',
  count int(11) NOT NULL DEFAULT '0',
  transaction_id int(11) NOT NULL DEFAULT '0',
  account varchar(2048) CHARACTER SET utf8 DEFAULT NULL,
  from_account varchar(2048) CHARACTER SET utf8 DEFAULT NULL,
  value int(11) NOT NULL,
  post_date timestamp NULL DEFAULT '0000-00-00 00:00:00',
  description varchar(2048) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (invoice_number,transaction_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
TRUNCATE TABLE ip_matching_records;

In diesem, nur beispielhaften, Ablauf finden Anzahlungen oder Teilzahlungen keine Berücksichtigung (bzw. nur angedeutet).

Für das Matching selbst kann man wiederverwendbaren Code, eine SQL PROCEDURE, in MySQL/MariaDB erstellen, der eigenen Kreativität sind keine Grenzen gesetzt. Hier ein Beispiel, dass der Reihe nach prüft und als Ergebnis immer nur einen einzigen Satz haben will. Die Qualität des Matches nimmt dabei ab:

  1. Rechnungsnummer, Kundennummer, Betrag identisch? (Hinweis: Match Qualität sehr gut)
  2. Rechnungsnummer, Betrag identisch? (Hinweis: Match Qualität gut)
  3. Rechnungsnummer, Kundennummer identisch? (Achtung: abweichender Betrag)
  4. Rechnungsnummer identisch, Betrag größer Null? (Achtung: Abweichender Betrag und Rückbuchungen/-lastschriften werde so nicht berücksichtigt)
DELIMITER //
CREATE PROCEDURE ip_get_matches(IN _InvNo CHAR(16), IN _CustNo CHAR(16), IN _Amount DECIMAL(11,2), OUT Recs INT)
MODIFIES SQL DATA
BEGIN
DECLARE Recs INT DEFAULT 0;
SET Recs = (SELECT count( * )
           FROM ip_income_transactions
          WHERE description LIKE CONCAT('"%', _InvNo, '%"') 
            AND description LIKE CONCAT('"%', _CustNo, '%"')
            AND value=_Amount
       GROUP BY splits_guid);
IF Recs = 1 THEN  
INSERT IGNORE INTO ip_matching_records (SELECT "_InvNo", count( * ) AS count, splits_guid, account, from_account, value, post_date, description
           FROM ip_income_transactions
          WHERE description LIKE CONCAT('"%', _InvNo, '%"') 
            AND description LIKE CONCAT('"%', _CustNo, '%"') 
            AND value=_Amount
       GROUP BY splits_guid);
ELSE
SET Recs = (SELECT count( * )
           FROM ip_income_transactions
          WHERE description LIKE CONCAT('"%', _InvNo, '%"') 
            AND value=_Amount
       GROUP BY splits_guid);
IF Recs = 1 THEN  
INSERT IGNORE INTO ip_matching_records (SELECT "_InvNo", count( * ) AS count, splits_guid, account, from_account, value, post_date, description
           FROM ip_income_transactions
          WHERE description LIKE CONCAT('"%', _InvNo, '%"') 
            AND value=_Amount
       GROUP BY splits_guid);
END IF;
END IF;
IF Recs <> 1 THEN
SET Recs = (SELECT count( * )
           FROM ip_income_transactions
          WHERE description LIKE CONCAT('"%', _InvNo, '%"') 
            AND description LIKE CONCAT('"%', _CustNo, '%"')
       GROUP BY splits_guid);
IF Recs = 1 THEN  
INSERT IGNORE INTO ip_matching_records (SELECT "_InvNo", count( * ) AS count, splits_guid, account, from_account, value, post_date, description
           FROM ip_income_transactions
          WHERE description LIKE CONCAT('"%', _InvNo, '%"') 
            AND description LIKE CONCAT('"%', _CustNo, '%"') 
       GROUP BY splits_guid);
ELSE
SET Recs = (SELECT count( * )
           FROM ip_income_transactions
          WHERE description LIKE CONCAT('"%', _InvNo, '%"') 
            AND value > 0
       GROUP BY splits_guid);
IF Recs = 1 THEN  
INSERT IGNORE INTO ip_matching_records (SELECT "_InvNo", count( * ) AS count, splits_guid, account, from_account, value, post_date, description
           FROM ip_income_transactions
          WHERE description LIKE CONCAT('"%', _InvNo, '%"') 
            AND value > 0
       GROUP BY splits_guid);
END IF;
END IF;
END IF;
END//
DELIMITER ;

Matching ausführen

Eine weitere SQL Procedure ip_match_records macht den eigentlichen Zuordnungslauf und kann um weitere Plausibilität erweitert werden (z.B. kann eine Zahlung nicht vor der Rechnungstellung erfolgt sein etc.):

DELIMITER //
CREATE PROCEDURE ip_match_records() BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE _InvNo CHAR(16);
  DECLARE _CustNo CHAR(16);
  DECLARE _Amount DEC(11,2);
  DECLARE cur CURSOR FOR SELECT invoice_number,custno,amount FROM ip_open_balance;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

  OPEN cur;

  RecLoop: LOOP
    FETCH cur INTO _InvNo,_CustNo,_Amount;
    IF done THEN
      LEAVE RecLoop;
    END IF;
    CALL ip_get_matches(_InvNo,_CustNo,_Amount);
  END LOOP RecLoop;

  CLOSE cur;
END//
DELIMITER ;
CALL ip_match_records;

Als Ergebnis sollten wir jetzt valide Zahlungssätze aus der Buchhaltung in der Tabelle ip_matching_records haben, saldieren unsere offenen Posten und löschen Ausgeglichene.

UPDATE ip_open_balance AS ob JOIN ip_matching_records AS m ON ob.invoice_number=m.invoice_number SET ob.amount=ob.amount-m.value;
DELETE FROM ip_open_balance WHERE amount=0;

Export Aufbereitung für den Import in InvoicePlane

Die matching Records werden jetzt für den Export aufbereitet. Gut beschrieben stehen die Importformate für InvoicePlane im Netz. Und wir wollen die Informationen (Verwendungszweck u.a.) aus den Buchungen auch in InvoicePlane wiederfinden.

CREATE TABLE IF NOT EXISTS ip_payment_exporter (
  invoice_number varchar(16) COLLATE utf8_bin NOT NULL,
  payment_method varchar(16) COLLATE utf8_bin NOT NULL DEFAULT 'IMPORTER',
  payment_date date NOT NULL,
  payment_amount decimal(11,2) NOT NULL,
  payment_note varchar(2048) COLLATE utf8_bin NOT NULL,
  KEY payment_date (payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO ip_payment_exporter 
        (invoice_number,payment_method,payment_date,payment_amount,payment_note) 
        (SELECT invoice_number,
                CONCAT('GC-Konto ', account),
                post_date,
                ROUND(value/100,2),
                CONCAT(description, '; ', from_account) 
         FROM ip_matching_records);

In diesem Fall läuft die Datenbank auf einem anderen System daher ist ein einfacher Export nicht möglich, er würde auf dem DB System landen. Wir behelfen uns mit einem kleinen Trick in bash und bereiten es selbst auf:

#!/bin/bash
echo "SELECT   '\"',
         CONCAT_WS('\",\"',invoice_number, payment_method, payment_date, payment_amount, payment_note),
         '\"'
    FROM ip_payment_exporter" \
    | mysql -r -h${DB_HOST} -u${DB_USER} -p${DB_PASS} ${DB} \
    | sed -e 's/"\t/"/g' -e 's/\t"/"/g' -e 's/ "/"/g' -e 's/" /"/g' >> payments.csv

Finale - Daten zurück übertragen und importieren

Die Ausgabe wurde in der Komma separierten payments.csv gefangen, welche direkt von InvoicePlane importiert werden kann.

Vorher werden die Daten auf das Zielsystem (InvoicePlane) übertragen.

#!/bin/bash
curl --user [htpasswd user]:[das sichere Kennwort] \
                --upload-file payments.csv \
                https://deine.server.domain/invoiceplane/ip_exporter/ip_payment_import.php

Wer doppelte Zahlungseingänge (besonders beim Testen) vermeiden will, kann das rigoros tun:

/* Avoid duplicates - a just in case */
ALTER TABLE ip_payment_methods ADD UNIQUE ( payment_method_name );
/* could be a problem if one sends equal amounts on the same day for the same invoice -> choose your poison */
ALTER TABLE ip_payments ADD UNIQUE ( payment_unique (invoice_id,payment_method_id,payment_date,payment_amount,payment_note(255));

Das Empfänger Skript auf dem InvoicePlane Server, ip_payment_import.php, stark vereinfacht (Beispiel aus php.net):

\<\?php
/* PUT data comes in on the stdin stream */

$putdata = fopen("php://input", "r");

/* Open a file for writing */
$fp = fopen("/var/www/invoiceplane/uploads/import/payments.csv", "w");

/* Read the data 1 KB at a time
   and write to the file */
while ($data = fread($putdata, 1024))
  fwrite($fp, $data);

/* Close the streams */
fclose($fp);
fclose($putdata);
\?\>

Leider ist in InvoicePlane derzeit der Import mit Befehlszeile noch nicht vorgesehen und erfordert nun einen manuellen Eingriff in den Ablauf. Dazu wurde die payments.csv vorher auf das InvoicePlane Zielsystem in den Ordner ./uploads/import geladen um nun in Settings den Import auszuführen (https://deine.server.domain/invoiceplane/import/form).


Betrachtungen am Ende

Wer viele kleine Rechnungen hat wird den Vorteil der Automatisierung bald zu schätzen wissen.

Wer das alles hier nicht versteht und Hilfe oder Beratung braucht, kann natürlich auch uns einen Auftrag dazu erteilen → Bitte Kontakt Formular verwenden. Ihr Bearbeitungs Vorgang in diesem Bereich beschränkt sich dann auf die Erstellung der Rechnungen und auf das Abholen der Kontoauszüge, der Rest geht weitgehend automatisch.


Die Verbesserung

Nach diesen Voruntersuchungen war es wünschenswert an dieser Stelle mit DOM Objekten und einer XML Schnittstelle zu arbeiten. Auf diesem Weg sind auch andere Informationen strukturiert und ohne Konflikt in beide Richtungen übertragbar ohne das Gesamtsystem zu beeinträchtigen. Ein einfaches XML dient als Basis:

Beispiel Payment XML

Nur einer der Vorteile des XML Formates: Der Import aller Daten ist in einer einzigen Übertragungsdatei möglich, sogar mit unterschiedlichen und wechselnden Satzarten.

Ein weiterer Vorteil entsteht dadurch, dass zusätzliche Felder - die anders behandelt werden sollen - flexibel eingefügt werden können; Vorausgesetzt der Importablauf in InvoicePlane stellt hooks zur Verfügung die eigene AddOns/Erweiterungen unterstützen.

Erweitertes Beispiel:

XML Payment Beispiel erweitert

Damit das reibungslos funktioniert, werden zeckmäßiger Weise die offen Posten ebenfalls als XML Datei unmittelbar vor dem Matching aktuell abgeholt und direkt für die Zuordung zu den Eingangs Zahlungen verwendet. Im Ergebniss werden die Zahlungssätze nun unmittelbar als XML zurückgesandt und direkt den Rechnungen zugeordnet.

Und so machen wir das heute, schließlich bin ich EDfauler und nicht EDfleißiger. Die Kontauszüge werden automtisch jeden Tag vor dem Mahnlauf geholt, die Rechnungen direkt als "Bezahlt" markiert zurück auf das Invoice System und dabei die Importdatei für GNUcash geschrieben. Leider muss man die noch manuell importieren, zumindest solange bis ich herausgefunden habe, wie auch das automatisch geht. Für Tipps dankbar...