Merge pull request #18200 from alyf-de/datev_report_headers
feat(regional): enable automatic processing of DATEV reports
diff --git a/erpnext/regional/doctype/datev_settings/__init__.py b/erpnext/regional/doctype/datev_settings/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/regional/doctype/datev_settings/__init__.py
diff --git a/erpnext/regional/doctype/datev_settings/datev_settings.js b/erpnext/regional/doctype/datev_settings/datev_settings.js
new file mode 100644
index 0000000..69747b0
--- /dev/null
+++ b/erpnext/regional/doctype/datev_settings/datev_settings.js
@@ -0,0 +1,8 @@
+// Copyright (c) 2019, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+frappe.ui.form.on('DATEV Settings', {
+ // refresh: function(frm) {
+
+ // }
+});
diff --git a/erpnext/regional/doctype/datev_settings/datev_settings.json b/erpnext/regional/doctype/datev_settings/datev_settings.json
new file mode 100644
index 0000000..6860ed3
--- /dev/null
+++ b/erpnext/regional/doctype/datev_settings/datev_settings.json
@@ -0,0 +1,105 @@
+{
+ "autoname": "field:client",
+ "creation": "2019-08-13 23:56:34.259906",
+ "doctype": "DocType",
+ "editable_grid": 1,
+ "engine": "InnoDB",
+ "field_order": [
+ "client",
+ "column_break_2",
+ "client_number",
+ "section_break_4",
+ "consultant",
+ "column_break_6",
+ "consultant_number"
+ ],
+ "fields": [
+ {
+ "fieldname": "client",
+ "fieldtype": "Link",
+ "in_list_view": 1,
+ "label": "Client",
+ "options": "Company",
+ "reqd": 1,
+ "unique": 1
+ },
+ {
+ "fieldname": "client_number",
+ "fieldtype": "Data",
+ "in_list_view": 1,
+ "label": "Client ID",
+ "reqd": 1
+ },
+ {
+ "fieldname": "consultant",
+ "fieldtype": "Link",
+ "in_list_view": 1,
+ "label": "Consultant",
+ "options": "Supplier"
+ },
+ {
+ "fieldname": "consultant_number",
+ "fieldtype": "Data",
+ "in_list_view": 1,
+ "label": "Consultant ID",
+ "reqd": 1
+ },
+ {
+ "fieldname": "column_break_2",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "section_break_4",
+ "fieldtype": "Section Break"
+ },
+ {
+ "fieldname": "column_break_6",
+ "fieldtype": "Column Break"
+ }
+ ],
+ "modified": "2019-08-14 00:03:26.616460",
+ "modified_by": "Administrator",
+ "module": "Regional",
+ "name": "DATEV Settings",
+ "owner": "Administrator",
+ "permissions": [
+ {
+ "create": 1,
+ "delete": 1,
+ "email": 1,
+ "export": 1,
+ "print": 1,
+ "read": 1,
+ "report": 1,
+ "role": "System Manager",
+ "share": 1,
+ "write": 1
+ },
+ {
+ "create": 1,
+ "delete": 1,
+ "email": 1,
+ "export": 1,
+ "print": 1,
+ "read": 1,
+ "report": 1,
+ "role": "Accounts Manager",
+ "share": 1,
+ "write": 1
+ },
+ {
+ "create": 1,
+ "email": 1,
+ "export": 1,
+ "print": 1,
+ "read": 1,
+ "report": 1,
+ "role": "Accounts User",
+ "share": 1
+ }
+ ],
+ "quick_entry": 1,
+ "sort_field": "modified",
+ "sort_order": "DESC",
+ "track_changes": 1
+}
\ No newline at end of file
diff --git a/erpnext/regional/doctype/datev_settings/datev_settings.py b/erpnext/regional/doctype/datev_settings/datev_settings.py
new file mode 100644
index 0000000..cff5bba
--- /dev/null
+++ b/erpnext/regional/doctype/datev_settings/datev_settings.py
@@ -0,0 +1,10 @@
+# -*- coding: utf-8 -*-
+# Copyright (c) 2019, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+# import frappe
+from frappe.model.document import Document
+
+class DATEVSettings(Document):
+ pass
diff --git a/erpnext/regional/doctype/datev_settings/test_datev_settings.py b/erpnext/regional/doctype/datev_settings/test_datev_settings.py
new file mode 100644
index 0000000..0271329
--- /dev/null
+++ b/erpnext/regional/doctype/datev_settings/test_datev_settings.py
@@ -0,0 +1,10 @@
+# -*- coding: utf-8 -*-
+# Copyright (c) 2019, Frappe Technologies Pvt. Ltd. and Contributors
+# See license.txt
+from __future__ import unicode_literals
+
+# import frappe
+import unittest
+
+class TestDATEVSettings(unittest.TestCase):
+ pass
diff --git a/erpnext/regional/report/datev/datev.py b/erpnext/regional/report/datev/datev.py
index 50aed08..ee8735f 100644
--- a/erpnext/regional/report/datev/datev.py
+++ b/erpnext/regional/report/datev/datev.py
@@ -8,6 +8,7 @@
all required columns. Used to import the data into the DATEV Software.
"""
from __future__ import unicode_literals
+import datetime
import json
from six import string_types
import frappe
@@ -17,24 +18,28 @@
def execute(filters=None):
"""Entry point for frappe."""
- validate_filters(filters)
+ validate(filters)
result = get_gl_entries(filters, as_dict=0)
columns = get_columns()
return columns, result
-def validate_filters(filters):
- """Make sure all mandatory filters are present."""
+def validate(filters):
+ """Make sure all mandatory filters and settings are present."""
if not filters.get('company'):
- frappe.throw(_('{0} is mandatory').format(_('Company')))
+ frappe.throw(_('<b>Company</b> is a mandatory filter.'))
if not filters.get('from_date'):
- frappe.throw(_('{0} is mandatory').format(_('From Date')))
+ frappe.throw(_('<b>From Date</b> is a mandatory filter.'))
if not filters.get('to_date'):
- frappe.throw(_('{0} is mandatory').format(_('To Date')))
+ frappe.throw(_('<b>To Date</b> is a mandatory filter.'))
+ try:
+ frappe.get_doc('DATEV Settings', filters.get('company'))
+ except frappe.DoesNotExistError:
+ frappe.throw(_('Please create <b>DATEV Settings</b> for Company <b>{}</b>.').format(filters.get('company')))
def get_columns():
"""Return the list of columns that will be shown in query report."""
@@ -158,13 +163,84 @@
return gl_entries
-def get_datev_csv(data):
+def get_datev_csv(data, filters):
"""
Fill in missing columns and return a CSV in DATEV Format.
+ For automatic processing, DATEV requires the first line of the CSV file to
+ hold meta data such as the length of account numbers oder the category of
+ the data.
+
Arguments:
data -- array of dictionaries
+ filters -- dict
"""
+ header = [
+ # A = DATEV format
+ # DTVF = created by DATEV software,
+ # EXTF = created by other software
+ "EXTF",
+ # B = version of the DATEV format
+ # 141 = 1.41,
+ # 510 = 5.10,
+ # 720 = 7.20
+ "510",
+ # C = Data category
+ # 21 = Transaction batch (Buchungsstapel),
+ # 67 = Buchungstextkonstanten,
+ # 16 = Debitors/Creditors,
+ # 20 = Account names (Kontenbeschriftungen)
+ "21",
+ # D = Format name
+ # Buchungsstapel,
+ # Buchungstextkonstanten,
+ # Debitoren/Kreditoren,
+ # Kontenbeschriftungen
+ "Buchungsstapel",
+ # E = Format version (regarding format name)
+ "",
+ # F = Generated on
+ datetime.datetime.now().strftime("%Y%m%d"),
+ # G = Imported on -- stays empty
+ "",
+ # H = Origin (SV = other (?), RE = KARE)
+ "SV",
+ # I = Exported by
+ frappe.session.user,
+ # J = Imported by -- stays empty
+ "",
+ # K = Tax consultant number (Beraternummer)
+ frappe.get_value("DATEV Settings", filters.get("company"), "consultant_number") or "",
+ "",
+ # L = Tax client number (Mandantennummer)
+ frappe.get_value("DATEV Settings", filters.get("company"), "client_number") or "",
+ "",
+ # M = Start of the fiscal year (Wirtschaftsjahresbeginn)
+ frappe.utils.formatdate(frappe.defaults.get_user_default("year_start_date"), "yyyyMMdd"),
+ # N = Length of account numbers (Sachkontenlänge)
+ "4",
+ # O = Transaction batch start date (YYYYMMDD)
+ frappe.utils.formatdate(filters.get('from_date'), "yyyyMMdd"),
+ # P = Transaction batch end date (YYYYMMDD)
+ frappe.utils.formatdate(filters.get('to_date'), "yyyyMMdd"),
+ # Q = Description (for example, "January - February 2019 Transactions")
+ "{} - {} Buchungsstapel".format(
+ frappe.utils.formatdate(filters.get('from_date'), "MMMM yyyy"),
+ frappe.utils.formatdate(filters.get('to_date'), "MMMM yyyy")
+ ),
+ # R = Diktatkürzel
+ "",
+ # S = Buchungstyp
+ # 1 = Transaction batch (Buchungsstapel),
+ # 2 = Annual financial statement (Jahresabschluss)
+ "1",
+ # T = Rechnungslegungszweck
+ "",
+ # U = Festschreibung
+ "",
+ # V = Kontoführungs-Währungskennzeichen des Geldkontos
+ frappe.get_value("Company", filters.get("company"), "default_currency")
+ ]
columns = [
# All possible columns must tbe listed here, because DATEV requires them to
# be present in the CSV.
@@ -324,9 +400,10 @@
data_df = pd.DataFrame.from_records(data)
result = empty_df.append(data_df)
- result["Belegdatum"] = pd.to_datetime(result["Belegdatum"])
+ result['Belegdatum'] = pd.to_datetime(result['Belegdatum'])
- return result.to_csv(
+ header = ';'.join(header).encode('latin_1')
+ data = result.to_csv(
sep=b';',
# European decimal seperator
decimal=',',
@@ -342,6 +419,7 @@
columns=columns
)
+ return header + b'\r\n' + data
@frappe.whitelist()
def download_datev_csv(filters=None):
@@ -359,15 +437,9 @@
if isinstance(filters, string_types):
filters = json.loads(filters)
- validate_filters(filters)
+ validate(filters)
data = get_gl_entries(filters, as_dict=1)
- filename = 'DATEV_Buchungsstapel_{}-{}_bis_{}'.format(
- filters.get('company'),
- filters.get('from_date'),
- filters.get('to_date')
- )
-
- frappe.response['result'] = get_datev_csv(data)
- frappe.response['doctype'] = filename
+ frappe.response['result'] = get_datev_csv(data, filters)
+ frappe.response['doctype'] = 'EXTF_Buchungsstapel'
frappe.response['type'] = 'csv'