Merge pull request #22487 from frappe/mergify/bp/version-13-beta-pre-release/pr-21690
fix(regional): DATEV report (bp #21690)
diff --git a/erpnext/regional/report/datev/datev.py b/erpnext/regional/report/datev/datev.py
index a8e40cc..7fec94e 100644
--- a/erpnext/regional/report/datev/datev.py
+++ b/erpnext/regional/report/datev/datev.py
@@ -8,17 +8,18 @@
all required columns. Used to import the data into the DATEV Software.
"""
from __future__ import unicode_literals
+
import datetime
import json
-import zlib
import zipfile
import six
+import frappe
+import pandas as pd
+
+from frappe import _
from csv import QUOTE_NONNUMERIC
from six import BytesIO
from six import string_types
-import frappe
-from frappe import _
-import pandas as pd
from .datev_constants import DataCategory
from .datev_constants import Transactions
from .datev_constants import DebtorsCreditors
@@ -130,8 +131,10 @@
SELECT
acc.account_number as 'Konto',
- cus.customer_name as 'Name (Adressatentyp Unternehmen)',
- case cus.customer_type when 'Individual' then 1 when 'Company' then 2 else 0 end as 'Adressatentyp',
+ CASE cus.customer_type WHEN 'Company' THEN cus.customer_name ELSE null END as 'Name (Adressatentyp Unternehmen)',
+ CASE cus.customer_type WHEN 'Individual' THEN con.last_name ELSE null END as 'Name (Adressatentyp natürl. Person)',
+ CASE cus.customer_type WHEN 'Individual' THEN con.first_name ELSE null END as 'Vorname (Adressatentyp natürl. Person)',
+ CASE cus.customer_type WHEN 'Individual' THEN '1' WHEN 'Company' THEN '2' ELSE '0' end as 'Adressatentyp',
adr.address_line1 as 'Straße',
adr.pincode as 'Postleitzahl',
adr.city as 'Ort',
@@ -140,8 +143,7 @@
con.email_id as 'E-Mail',
coalesce(con.mobile_no, con.phone) as 'Telefon',
cus.website as 'Internet',
- cus.tax_id as 'Steuernummer',
- ccl.credit_limit as 'Kreditlimit (Debitor)'
+ cus.tax_id as 'Steuernummer'
FROM `tabParty Account` par
@@ -160,10 +162,6 @@
left join `tabContact` con
on con.name = cus.customer_primary_contact
- left join `tabCustomer Credit Limit` ccl
- on ccl.parent = cus.name
- and ccl.company = par.company
-
WHERE par.company = %(company)s
AND par.parenttype = 'Customer'""", filters, as_dict=1)
@@ -179,8 +177,10 @@
SELECT
acc.account_number as 'Konto',
- sup.supplier_name as 'Name (Adressatentyp Unternehmen)',
- case sup.supplier_type when 'Individual' then '1' when 'Company' then '2' else '0' end as 'Adressatentyp',
+ CASE sup.supplier_type WHEN 'Company' THEN sup.supplier_name ELSE null END as 'Name (Adressatentyp Unternehmen)',
+ CASE sup.supplier_type WHEN 'Individual' THEN con.last_name ELSE null END as 'Name (Adressatentyp natürl. Person)',
+ CASE sup.supplier_type WHEN 'Individual' THEN con.first_name ELSE null END as 'Vorname (Adressatentyp natürl. Person)',
+ CASE sup.supplier_type WHEN 'Individual' THEN '1' WHEN 'Company' THEN '2' ELSE '0' end as 'Adressatentyp',
adr.address_line1 as 'Straße',
adr.pincode as 'Postleitzahl',
adr.city as 'Ort',
@@ -226,9 +226,18 @@
def get_account_names(filters):
- return frappe.get_list("Account",
- fields=["account_number as Konto", "name as Kontenbeschriftung"],
- filters={"company": filters.get("company"), "is_group": "0"})
+ return frappe.db.sql("""
+ SELECT
+
+ account_number as 'Konto',
+ LEFT(account_name, 40) as 'Kontenbeschriftung',
+ 'de-DE' as 'Sprach-ID'
+
+ FROM `tabAccount`
+ WHERE company = %(company)s
+ AND is_group = 0
+ AND account_number != ''
+ """, filters, as_dict=1)
def get_datev_csv(data, filters, csv_class):
@@ -287,9 +296,7 @@
def get_header(filters, csv_class):
- coa = frappe.get_value("Company", filters.get("company"), "chart_of_accounts")
- description = filters.get("voucher_type", csv_class.FORMAT_NAME)
- coa_used = "04" if "SKR04" in coa else ("03" if "SKR03" in coa else "")
+ description = filters.get('voucher_type', csv_class.FORMAT_NAME)
header = [
# DATEV format
@@ -316,19 +323,19 @@
# J = Imported by -- stays empty
'',
# K = Tax consultant number (Beraternummer)
- frappe.get_value("DATEV Settings", filters.get("company"), "consultant_number"),
+ filters.get('consultant_number', '0000000'),
# L = Tax client number (Mandantennummer)
- frappe.get_value("DATEV Settings", filters.get("company"), "client_number"),
+ filters.get('client_number', '00000'),
# 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',
+ '%d' % filters.get('acc_len', 4),
# O = Transaction batch start date (YYYYMMDD)
- frappe.utils.formatdate(filters.get('from_date'), "yyyyMMdd"),
+ frappe.utils.formatdate(filters.get('from_date'), "yyyyMMdd") if csv_class.DATA_CATEGORY == DataCategory.TRANSACTIONS else '',
# P = Transaction batch end date (YYYYMMDD)
- frappe.utils.formatdate(filters.get('to_date'), "yyyyMMdd"),
+ frappe.utils.formatdate(filters.get('to_date'), "yyyyMMdd") if csv_class.DATA_CATEGORY == DataCategory.TRANSACTIONS else '',
# Q = Description (for example, "Sales Invoice") Max. 30 chars
- '"{}"'.format(_(description)),
+ '"{}"'.format(_(description)) if csv_class.DATA_CATEGORY == DataCategory.TRANSACTIONS else '',
# R = Diktatkürzel
'',
# S = Buchungstyp
@@ -343,12 +350,12 @@
# 40 = Kalkulatorik
# 11 = Reserviert
# 12 = Reserviert
- '0',
+ '0' if csv_class.DATA_CATEGORY == DataCategory.TRANSACTIONS else '',
# U = Festschreibung
# TODO: Filter by Accounting Period. In export for closed Accounting Period, this will be "1"
'0',
# V = Default currency, for example, "EUR"
- '"%s"' % frappe.get_value("Company", filters.get("company"), "default_currency"),
+ '"%s"' % filters.get('default_currency', 'EUR') if csv_class.DATA_CATEGORY == DataCategory.TRANSACTIONS else '',
# reserviert
'',
# Derivatskennzeichen
@@ -358,7 +365,7 @@
# reserviert
'',
# SKR
- '"%s"' % coa_used,
+ '"%s"' % filters.get('skr', '04'),
# Branchen-Lösungs-ID
'',
# reserviert
@@ -389,6 +396,18 @@
validate(filters)
+ # set chart of accounts used
+ coa = frappe.get_value('Company', filters.get('company'), 'chart_of_accounts')
+ filters['skr'] = '04' if 'SKR04' in coa else ('03' if 'SKR03' in coa else '')
+
+ # set account number length
+ account_numbers = frappe.get_list('Account', fields=['account_number'], filters={'is_group': 0, 'account_number': ('!=', '')})
+ filters['acc_len'] = max([len(a.account_number) for a in account_numbers])
+
+ filters['consultant_number'] = frappe.get_value('DATEV Settings', filters.get('company'), 'consultant_number')
+ filters['client_number'] = frappe.get_value('DATEV Settings', filters.get('company'), 'client_number')
+ filters['default_currency'] = frappe.get_value('Company', filters.get('company'), 'default_currency')
+
# This is where my zip will be written
zip_buffer = BytesIO()
# This is my zip file
diff --git a/erpnext/regional/report/datev/datev_constants.py b/erpnext/regional/report/datev/datev_constants.py
index a059ed3..e063703 100644
--- a/erpnext/regional/report/datev/datev_constants.py
+++ b/erpnext/regional/report/datev/datev_constants.py
@@ -465,60 +465,71 @@
"label": "Umsatz (ohne Soll/Haben-Kz)",
"fieldname": "Umsatz (ohne Soll/Haben-Kz)",
"fieldtype": "Currency",
+ "width": 100
},
{
"label": "Soll/Haben-Kennzeichen",
"fieldname": "Soll/Haben-Kennzeichen",
"fieldtype": "Data",
+ "width": 100
},
{
"label": "Konto",
"fieldname": "Konto",
"fieldtype": "Data",
+ "width": 100
},
{
"label": "Gegenkonto (ohne BU-Schlüssel)",
"fieldname": "Gegenkonto (ohne BU-Schlüssel)",
"fieldtype": "Data",
+ "width": 100
},
{
"label": "Belegdatum",
"fieldname": "Belegdatum",
"fieldtype": "Date",
+ "width": 100
},
{
"label": "Belegfeld 1",
"fieldname": "Belegfeld 1",
"fieldtype": "Data",
+ "width": 150
},
{
"label": "Buchungstext",
"fieldname": "Buchungstext",
"fieldtype": "Text",
+ "width": 300
},
{
"label": "Beleginfo - Art 1",
"fieldname": "Beleginfo - Art 1",
"fieldtype": "Link",
- "options": "DocType"
+ "options": "DocType",
+ "width": 100
},
{
"label": "Beleginfo - Inhalt 1",
"fieldname": "Beleginfo - Inhalt 1",
"fieldtype": "Dynamic Link",
- "options": "Beleginfo - Art 1"
+ "options": "Beleginfo - Art 1",
+ "width": 150
},
{
"label": "Beleginfo - Art 2",
"fieldname": "Beleginfo - Art 2",
"fieldtype": "Link",
- "options": "DocType"
+ "options": "DocType",
+ "width": 100
},
{
"label": "Beleginfo - Inhalt 2",
"fieldname": "Beleginfo - Inhalt 2",
"fieldtype": "Dynamic Link",
- "options": "Beleginfo - Art 2"
+ "options": "Beleginfo - Art 2",
+ "width": 150
}
]