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 ;