feat: add voucher-specific data to DATEV export (bp #26589) (#27287)
* feat: add voucher-specific data to datev export
* refactor: def instead of lambda
diff --git a/erpnext/regional/germany/utils/datev/datev_csv.py b/erpnext/regional/germany/utils/datev/datev_csv.py
index 122c15f..c5c2bc4 100644
--- a/erpnext/regional/germany/utils/datev/datev_csv.py
+++ b/erpnext/regional/germany/utils/datev/datev_csv.py
@@ -33,6 +33,14 @@
if csv_class.DATA_CATEGORY == DataCategory.TRANSACTIONS:
result['Belegdatum'] = pd.to_datetime(result['Belegdatum'])
+ result['Beleginfo - Inhalt 6'] = pd.to_datetime(result['Beleginfo - Inhalt 6'])
+ result['Beleginfo - Inhalt 6'] = result['Beleginfo - Inhalt 6'].dt.strftime('%d%m%Y')
+
+ result['Fälligkeit'] = pd.to_datetime(result['Fälligkeit'])
+ result['Fälligkeit'] = result['Fälligkeit'].dt.strftime('%d%m%y')
+
+ result.sort_values(by='Belegdatum', inplace=True, kind='stable', ignore_index=True)
+
if csv_class.DATA_CATEGORY == DataCategory.ACCOUNT_NAMES:
result['Sprach-ID'] = 'de-DE'
diff --git a/erpnext/regional/report/datev/datev.py b/erpnext/regional/report/datev/datev.py
index 86aed2e..b21b0f8 100644
--- a/erpnext/regional/report/datev/datev.py
+++ b/erpnext/regional/report/datev/datev.py
@@ -44,6 +44,12 @@
"width": 100
},
{
+ "label": "BU-Schlüssel",
+ "fieldname": "BU-Schlüssel",
+ "fieldtype": "Data",
+ "width": 100
+ },
+ {
"label": "Belegdatum",
"fieldname": "Belegdatum",
"fieldtype": "Date",
@@ -114,6 +120,36 @@
"fieldname": "Beleginfo - Inhalt 4",
"fieldtype": "Data",
"width": 150
+ },
+ {
+ "label": "Beleginfo - Art 5",
+ "fieldname": "Beleginfo - Art 5",
+ "fieldtype": "Data",
+ "width": 150
+ },
+ {
+ "label": "Beleginfo - Inhalt 5",
+ "fieldname": "Beleginfo - Inhalt 5",
+ "fieldtype": "Data",
+ "width": 100
+ },
+ {
+ "label": "Beleginfo - Art 6",
+ "fieldname": "Beleginfo - Art 6",
+ "fieldtype": "Data",
+ "width": 150
+ },
+ {
+ "label": "Beleginfo - Inhalt 6",
+ "fieldname": "Beleginfo - Inhalt 6",
+ "fieldtype": "Date",
+ "width": 100
+ },
+ {
+ "label": "Fälligkeit",
+ "fieldname": "Fälligkeit",
+ "fieldtype": "Date",
+ "width": 100
}
]
@@ -161,6 +197,125 @@
def get_transactions(filters, as_dict=1):
+ def run(params_method, filters):
+ extra_fields, extra_joins, extra_filters = params_method(filters)
+ return run_query(filters, extra_fields, extra_joins, extra_filters, as_dict=as_dict)
+
+ def sort_by(row):
+ # "Belegdatum" is in the fifth column when list format is used
+ return row["Belegdatum" if as_dict else 5]
+
+ type_map = {
+ # specific query methods for some voucher types
+ "Payment Entry": get_payment_entry_params,
+ "Sales Invoice": get_sales_invoice_params,
+ "Purchase Invoice": get_purchase_invoice_params
+ }
+
+ only_voucher_type = filters.get("voucher_type")
+ transactions = []
+
+ for voucher_type, get_voucher_params in type_map.items():
+ if only_voucher_type and only_voucher_type != voucher_type:
+ continue
+
+ transactions.extend(run(params_method=get_voucher_params, filters=filters))
+
+ if not only_voucher_type or only_voucher_type not in type_map:
+ # generic query method for all other voucher types
+ filters["exclude_voucher_types"] = type_map.keys()
+ transactions.extend(run(params_method=get_generic_params, filters=filters))
+
+ return sorted(transactions, key=sort_by)
+
+
+def get_payment_entry_params(filters):
+ extra_fields = """
+ , 'Zahlungsreferenz' as 'Beleginfo - Art 5'
+ , pe.reference_no as 'Beleginfo - Inhalt 5'
+ , 'Buchungstag' as 'Beleginfo - Art 6'
+ , pe.reference_date as 'Beleginfo - Inhalt 6'
+ , '' as 'Fälligkeit'
+ """
+
+ extra_joins = """
+ LEFT JOIN `tabPayment Entry` pe
+ ON gl.voucher_no = pe.name
+ """
+
+ extra_filters = """
+ AND gl.voucher_type = 'Payment Entry'
+ """
+
+ return extra_fields, extra_joins, extra_filters
+
+
+def get_sales_invoice_params(filters):
+ extra_fields = """
+ , '' as 'Beleginfo - Art 5'
+ , '' as 'Beleginfo - Inhalt 5'
+ , '' as 'Beleginfo - Art 6'
+ , '' as 'Beleginfo - Inhalt 6'
+ , si.due_date as 'Fälligkeit'
+ """
+
+ extra_joins = """
+ LEFT JOIN `tabSales Invoice` si
+ ON gl.voucher_no = si.name
+ """
+
+ extra_filters = """
+ AND gl.voucher_type = 'Sales Invoice'
+ """
+
+ return extra_fields, extra_joins, extra_filters
+
+
+def get_purchase_invoice_params(filters):
+ extra_fields = """
+ , 'Lieferanten-Rechnungsnummer' as 'Beleginfo - Art 5'
+ , pi.bill_no as 'Beleginfo - Inhalt 5'
+ , 'Lieferanten-Rechnungsdatum' as 'Beleginfo - Art 6'
+ , pi.bill_date as 'Beleginfo - Inhalt 6'
+ , pi.due_date as 'Fälligkeit'
+ """
+
+ extra_joins = """
+ LEFT JOIN `tabPurchase Invoice` pi
+ ON gl.voucher_no = pi.name
+ """
+
+ extra_filters = """
+ AND gl.voucher_type = 'Purchase Invoice'
+ """
+
+ return extra_fields, extra_joins, extra_filters
+
+
+def get_generic_params(filters):
+ # produce empty fields so all rows will have the same length
+ extra_fields = """
+ , '' as 'Beleginfo - Art 5'
+ , '' as 'Beleginfo - Inhalt 5'
+ , '' as 'Beleginfo - Art 6'
+ , '' as 'Beleginfo - Inhalt 6'
+ , '' as 'Fälligkeit'
+ """
+ extra_joins = ""
+
+ if filters.get("exclude_voucher_types"):
+ # exclude voucher types that are queried by a dedicated method
+ exclude = "({})".format(', '.join("'{}'".format(key) for key in filters.get("exclude_voucher_types")))
+ extra_filters = "AND gl.voucher_type NOT IN {}".format(exclude)
+
+ # if voucher type filter is set, allow only this type
+ if filters.get("voucher_type"):
+ extra_filters += " AND gl.voucher_type = %(voucher_type)s"
+
+ return extra_fields, extra_joins, extra_filters
+
+
+def run_query(filters, extra_fields, extra_joins, extra_filters, as_dict=1):
"""
Get a list of accounting entries.
@@ -171,8 +326,7 @@
filters -- dict of filters to be passed to the sql query
as_dict -- return as list of dicts [0,1]
"""
- filter_by_voucher = 'AND gl.voucher_type = %(voucher_type)s' if filters.get('voucher_type') else ''
- gl_entries = frappe.db.sql("""
+ query = """
SELECT
/* either debit or credit amount; always positive */
@@ -187,6 +341,9 @@
/* against number or, if empty, party against number */
%(temporary_against_account_number)s as 'Gegenkonto (ohne BU-Schlüssel)',
+ /* disable automatic VAT deduction */
+ '40' as 'BU-Schlüssel',
+
gl.posting_date as 'Belegdatum',
gl.voucher_no as 'Belegfeld 1',
LEFT(gl.remarks, 60) as 'Buchungstext',
@@ -199,30 +356,34 @@
case gl.party_type when 'Customer' then 'Debitorennummer' when 'Supplier' then 'Kreditorennummer' else NULL end as 'Beleginfo - Art 4',
par.debtor_creditor_number as 'Beleginfo - Inhalt 4'
+ {extra_fields}
+
FROM `tabGL Entry` gl
/* Kontonummer */
- left join `tabAccount` acc
- on gl.account = acc.name
+ LEFT JOIN `tabAccount` acc
+ ON gl.account = acc.name
- left join `tabCustomer` cus
- on gl.party_type = 'Customer'
- and gl.party = cus.name
+ LEFT JOIN `tabParty Account` par
+ ON par.parent = gl.party
+ AND par.parenttype = gl.party_type
+ AND par.company = %(company)s
- left join `tabSupplier` sup
- on gl.party_type = 'Supplier'
- and gl.party = sup.name
-
- left join `tabParty Account` par
- on par.parent = gl.party
- and par.parenttype = gl.party_type
- and par.company = %(company)s
+ {extra_joins}
WHERE gl.company = %(company)s
AND DATE(gl.posting_date) >= %(from_date)s
AND DATE(gl.posting_date) <= %(to_date)s
- {}
- ORDER BY 'Belegdatum', gl.voucher_no""".format(filter_by_voucher), filters, as_dict=as_dict)
+
+ {extra_filters}
+
+ ORDER BY 'Belegdatum', gl.voucher_no""".format(
+ extra_fields=extra_fields,
+ extra_joins=extra_joins,
+ extra_filters=extra_filters
+ )
+
+ gl_entries = frappe.db.sql(query, filters, as_dict=as_dict)
return gl_entries