Bild 1: Erfassen eines Belegs in lexoffice
Im Beitrag “Zugriff auf lexoffice per REST-API und VBA” (www.access-im-unternehmen.de/1422) haben wir bereits gezeigt, wie wir in lexoffice Rechnungen auf Basis von Rechnungsdaten aus einer Access-Datenbank erstellen können. In manchen Fällen reichen die Möglichkeiten von lexoffice nicht aus, um die gewünschten Rechnungen zu erstellen. Dann kann man lexoffice aber immer noch für die Buchhaltung nutzen. Die Rechnung erstellt man dann in Access statt direkt in lexoffice und überträgt dann ein PDF-Dokument mit der Rechnung sowie die begleitenden Daten wie den Umsatz, die enthaltenen Steuern und weitere Informationen per Rest API nach lexoffice. Dieser Beitrag zeigt, wie wir dies bewerkstelligen können.
Mit dem Ziel für diesen Beitrag automatisieren wir einen Vorgang, den wir sonst manuell erledigen könnten. Dazu startet man in lexoffice das Erfassen eines neuen Belegs (siehe Bild 1). Hier kann man einen Beleg hochladen. lexoffice versucht dann, die benötigten Informationen für den rechten Bereich automatisch zu ermitteln. Gelingt dies nicht oder nur teilweise, arbeiten wir von Hand nach.
Bild 1: Erfassen eines Belegs in lexoffice
Mit der nachfolgend vorgestellten Lösung wollen wir solche Unwägbarkeiten verhindern und gleichzeitig die benötigte Zeit minimieren. Statt das PDF manuell in das Formular zu ziehen und abzuwarten, bis lexoffice das Dokument analysiert hat, übergeben wir das Dokument samt den benötigten Daten per Webservice. Wir man diesen bedient, haben wir schon ausführlich in dem oben genannten Beitrag beschrieben.
Wir brauchen also nur auf den dort erwähnten Techniken aufzubauen und diese an die neue Aufgabe anzupassen. Wer schon einmal in der Dokumentation der Rest API stöbern möchte: Während wir im ersten Beitrag Elemente des Typs Invoice erstellt haben (Rechnung), geht es nun um das Hochladen von Elementen des Typs Voucher (Beleg). Beide verwenden ein Contact-Element, das je nach Anwendungszweck als Debitor oder Kreditor verwendet wird. Da wir im ersten Teil bereits die Techniken zum Hochladen von Kundendaten in Form eines Contact-Elements erledigt haben, brauchen wir uns auch darauf nicht mehr zu konzentrieren, sondern können uns dem Upload von Elementen des Typs Voucher kümmern.
Datenmodell für Belege
Für das Erfassen eines Belegs benötigen wir mehr Daten als für das Erstellen von Rechnungen. In Bild 2 sehen wir alle benötigten Tabellen. Die Tabelle tblContacts kennen wir bereits aus dem oben genannten Beitrag. Jedem Voucher aus der Tabelle tblVouchers weisen wir genau einen Kontakt zu, der je nach der Art der Positionen des Belegs Kreditor oder Debitor ist. Damit kommen wir gleich zur Tabelle tblVouchers, welche die grundlegenden Informationen zum Beleg enthält.
Bild 2: Datenmodell der Beispieldatenbank
Belegtyp
Dazu gehört der Typ des Belegs. Diese haben wir in einer eigenen Lookuptabelle namens tblVoucherTypes abgelegt (siehe Bild 3). Diese enthält neben dem Primärschlüsselfeld ein Feld namens VoucherType, das die Beschreibung enthält, die wir in die JSON-Datei mit den Informationen zum Erfassen des Belegs schreiben. Ein weiteres Feld namens VoucherTypeGerman enthält den Wert, den wir im Formular zur Auswahl des Belegtyps anbieten wollen.
Bild 3: Belegtypen
Belegstatus
Ein weiteres Feld namens VoucherStatusID dient dazu, den Status des Belegs zu übermitteln. Auch dafür haben wir eine Lookuptabelle angelegt. Diese heißt tblVoucherStatus und enthält ebenfalls ein Feld für die Angabe des Wertes für die JSON-Datei und eines für die deutsche Version des Wertes. Diese Tabelle enthält die Werte aus Bild 4.
Bild 4: Tabelle mit den verschiedenen Belegstatus
Steuertyp
Die nächste Lookuptabelle namens tblTaxTypes liefert die Werte für das Feld TaxTypeID. Hier legen wir fest, ob wir in den Belegpositionen die Netto- oder die Bruttowerte angeben (siehe Bild 5).
Bild 5: Tabelle mit den Steuertypen
Fremdschlüsselfelder der Tabelle tblVoucher
Die Felder VoucherTypeID, VoucherStatusID und TaxTypeID der Tabelle tblVoucher richten wir jeweils als Nachschlagefelder mit den zuvor vorgestellten Tabellen als Datensatzherkunft ein. Dabei geben wir als anzuzeigendes Feld jeweils das Feld mit der deutschen Bezeichnung der jeweiligen Werte an.
Weitere Felder der Tabelle tblVoucher
Außerdem enthält die Tabelle noch Felder zum Speichern der Rechnungsnummer (VoucherID), das Rechnungsdatum (VoucherDate), das Versanddatum (ShippingDate) und das Fälligkeitsdatum (DueDate). Außerdem enthält die Tabelle die Bruttosumme (TotalGrossAmount) und die Summe der Mehrwertsteuer (TotalTaxAmount).
Die nächsten beiden Felder UseCollectiveContact und ContactID geben an, ob wir als Lieferant oder Kunde den Wert Sammelkunde angeben wollen oder einen bereits gespeicherten Kontakt. Mit Remark geben wir einen Bemerkungstext an und mit Filename den Namen des Belegs, beispielsweise in Form einer PDF-Datei.
Die übrigen Felder haben folgenden Nutzen:
- LexOfficeID: Speichert die ID, unter welcher der Beleg in lexoffice angelegt wurde.
- LexOfficeRequest: Speichert den Request, den wir zum Anlegen des Belegs an lexoffice geschickt haben.
- LexOfficeResponse: Speichert die Antwort von lexoffice auf diese Anfrage.
Belegpositionen
Im Gegensatz zu einer Rechnung, die verschiedene Positionen mit den zu bezahlenden Produkten oder Leistungen enthält, tragen wir für einen Beleg Zusammenfassungen von Beträgen mit bestimmten Mehrwertsteuersätzen und Buchungskategorien ein. Diese werden jedoch ähnlich wie die Rechnungspositionen in einer eigenen Tabelle gespeichert, wobei jede Belegposition ebenfalls mit einem Datensatz aus der Belegtabelle, hier tblVouchers, verknüpft.
Die Tabelle zum Speichern dieser Daten heißt tblVoucherItems und sieht mit einigen Beispieldaten wie in Bild 6 aus. Neben dem Primärschlüsselfeld enthält sie ein Fremdschlüsselfeld, über das die Positionen mit einem der Belege aus der Tabelle tblVouchers verknüpft werden. In dieser Tabelle speichern wir den Betrag der Position (Amount), die Mehrwertsteuer zu dieser Position (TaxAmount) und den Steuersatz (TaxRatePercentage).
Bild 6: Tabelle mit den Positionen eines Belegs
Hier ist anzumerken, dass das Feld Amount sowohl den Netto- als auch den Bruttobetrag enthalten kann. Welcher eingetragen werden muss, hängt davon ab, welchen Wert das Feld TaxTypeID der Tabelle tblVouchers für diesen Beleg aufweist – mehr dazu weiter unten in der Beschreibung der Formulare.
Belegkategorien
lexoffice bietet eine ganze Reihe von Kategorien an, welche im Grunde Vereinfachungen für die üblicherweise verwendeten Buchungskonten sind (zum Beispiel 8300 für Erlöse mit 7% Mehrwertsteuer). Diese weisen wir einer Belegposition über das Fremdschlüsselfeld CategoryID zu, das wir als Nachschlagefeld auslegen. Dieses Feld ist mit der Tabelle tblCategories verknüpft. Diese Tabelle sieht wie in Bild 7 aus.
Bild 7: Einträge der Tabelle tblCategories
Diese Tabelle haben wir nicht von Hand gefüllt, sondern wir haben die Daten per Rest API von lexoffice eingelesen. Und das wird auch unsere Programmierübung zum Aufwärmen.
Kategorien von lexoffice einlesen
Die Kategorien können wir automatisiert von lexoffice einlesen. Dazu nutzen wir die Prozedur aus Listing 1. Die Prozedur heißt GetCategories und sie verwendet den folgenden Endpunkt der Rest API von lexoffice:
Public Sub GetCategories() Dim db As DAO.Database Dim strRequest As String Dim strURL As String Dim strResponse As String Dim objJSON As Object Dim i As Integer Dim strCategoryGUID As String Dim strCategory As String Dim strType As String Dim strContactRequired As String Dim strSplitAllowed As String Dim strGroupName As String Set db = CurrentDb strURL = "https://api.lexoffice.io/v1/posting-categories" If Request(strURL, "GET", strRequest, strResponse, "", "", 0) = True Then Set objJSON = ParseJson(strResponse) db.Execute "DELETE FROM tblCategories", dbFailOnError For i = 1 To objJSON.Count strCategoryGUID = objJSON.Item(i).Item("id") strCategory = objJSON.Item(i).Item("name") strType = objJSON.Item(i).Item("type") strContactRequired = IIf(objJSON.Item(i).Item("contactRequired") = "Wahr", -1, 0) strSplitAllowed = IIf(objJSON.Item(i).Item("splitAllowed") = "Wahr", -1, 0) strGroupName = objJSON.Item(i).Item("groupName") db.Execute "INSERT INTO tblCategories(CategoryGUID, Category, Type, ContactRequired, SplitAllowed, " _ & "GroupName) VALUES(''" & strCategoryGUID & "'', ''" & strCategory & "'', ''" & strType & "'', " _ & strContactRequired & ", " & strSplitAllowed & ", ''" & strGroupName & "'')", dbFailOnError Next i End If End Sub
Listing 1: Diese Prozedur liest die Kategorien von lexoffice ein.
https://api.lexoffice.io/v1/posting-categories
Dies liefert ohne weitere Angaben alle Kategorien zurück. Dazu rufen wir die Funktion Request auf, die wir bereits im oben erwähnten Beitrag ausführlich beschrieben haben Diese Funktion führt den eigentliche Request aus und liefert mit dem Parameter strResponse das Ergebnis zurück. Mit der Funktion ParseJson, die wir im Beitrag JSON-Daten auslesen (www.access-im-unternehmen.de/1403) beschrieben haben, übertragen wir die Inhalte des JSON-Dokuments in eine für uns besser verarbeitbare Form.
Bevor wir diese verarbeiten, löschen wir alle vorhandenen Einträge der Tabelle tblCategories. Anschließend durchlaufen wir bereits in einer For…Next-Schleife alle Elemente des Objekts objJSON. Darin legen wir jeweils einen neuen Datensatz in der Tabelle tblCategories an, wobei wir die Informationen zuvor über das in objJSON geschaffene Objektmodell auslesen.
Die Grundlagen dafür beschreiben wir ebenfalls im Beitrag JSON-Daten auslesen (www.access-im-unternehmen.de/1403).
Formulare zur Eingabe der Belege und Belegpositionen
Bevor wir die Prozeduren zum Übertragen von Belegen an lexoffice erstellen, wollen wir ein Formular schaffen, mit dem wir die zu übertragenden Daten komfortabel eingeben können. Dazu erstellen wir zunächst ein Unterformular namens sfmVouchers. Diesem weisen wir über die Eigenschaft Datensatzquelle die Tabelle tblVoucherItems zu und ziehen die Felder CategoryID, Amount, TaxAmount und TaxRatePercent in den Detailbereich. Außerdem stellen wir die Eigenschaft Standardansicht auf Datenblatt ein (siehe Bild 8).
Bild 8: Das Unterformular sfmVouchers
Das Hauptformular frmVouchers soll die Tabelle tblVouchers als Datensatzquelle verwenden. Aus dieser ziehen wir alle Felder in den oberen Bereich des Formulars und ordnen diese wie in Bild 9 an.
[
Bild 9: Das Formular frmVouchers
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
den kompletten Artikel im PDF-Format mit Beispieldatenbank
diesen und alle anderen Artikel mit dem Jahresabo