fix(regional): header row in DATEV report (#21113)
* fix(regional): encoding of DATEV report
* feat(regional): filter datev report by voucher type
* fix: creation time, coa used, is frozen
* add voucher types: Payroll Entry, Bank Reconciliation, Asset, Stock Entry
* fix indentation
* fix indentation
* fix indentation
diff --git a/erpnext/regional/report/datev/datev.js b/erpnext/regional/report/datev/datev.js
index 1e000b6..d8638ab 100644
--- a/erpnext/regional/report/datev/datev.js
+++ b/erpnext/regional/report/datev/datev.js
@@ -21,6 +21,12 @@
"default": frappe.datetime.now_date(),
"fieldtype": "Date",
"reqd": 1
+ },
+ {
+ "fieldname": "voucher_type",
+ "label": __("Voucher Type"),
+ "fieldtype": "Select",
+ "options": "\nSales Invoice\nPurchase Invoice\nPayment Entry\nExpense Claim\nPayroll Entry\nBank Reconciliation\nAsset\nStock Entry"
}
],
onload: function(query_report) {
diff --git a/erpnext/regional/report/datev/datev.py b/erpnext/regional/report/datev/datev.py
index e9b4235..a657912 100644
--- a/erpnext/regional/report/datev/datev.py
+++ b/erpnext/regional/report/datev/datev.py
@@ -62,6 +62,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("""
SELECT
@@ -80,8 +81,10 @@
gl.posting_date as 'Belegdatum',
gl.voucher_no as 'Belegfeld 1',
gl.remarks as 'Buchungstext',
- gl.against_voucher_type as 'Beleginfo - Art 1',
- gl.against_voucher as 'Beleginfo - Inhalt 1'
+ gl.voucher_type as 'Beleginfo - Art 1',
+ gl.voucher_no as 'Beleginfo - Inhalt 1',
+ gl.against_voucher_type as 'Beleginfo - Art 2',
+ gl.against_voucher as 'Beleginfo - Inhalt 2'
FROM `tabGL Entry` gl
@@ -109,7 +112,8 @@
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""", filters, as_dict=as_dict)
+ {}
+ ORDER BY 'Belegdatum', gl.voucher_no""".format(filter_by_voucher), filters, as_dict=as_dict)
return gl_entries
@@ -281,24 +285,24 @@
def get_header(filters, csv_class):
coa = frappe.get_value("Company", filters.get("company"), "chart_of_accounts")
- coa_used = "SKR04" if "SKR04" in coa else ("SKR03" if "SKR03" in coa else "")
+ coa_used = "04" if "SKR04" in coa else ("03" if "SKR03" in coa else "")
header = [
# DATEV format
- # "DTVF" = created by DATEV software,
- # "EXTF" = created by other software
+ # "DTVF" = created by DATEV software,
+ # "EXTF" = created by other software
'"EXTF"',
# version of the DATEV format
- # 141 = 1.41,
- # 510 = 5.10,
- # 720 = 7.20
+ # 141 = 1.41,
+ # 510 = 5.10,
+ # 720 = 7.20
'700',
csv_class.DATA_CATEGORY,
'"%s"' % csv_class.FORMAT_NAME,
# Format version (regarding format name)
csv_class.FORMAT_VERSION,
# Generated on
- datetime.datetime.now().strftime("%Y%m%d%H%M%S"),
+ datetime.datetime.now().strftime("%Y%m%d%H%M%S") + '000',
# Imported on -- stays empty
'',
# Origin. Any two symbols, will be replaced by "SV" on import.
@@ -328,13 +332,21 @@
# R = Diktatkürzel
'',
# S = Buchungstyp
- # 1 = Transaction batch (Finanzbuchführung),
- # 2 = Annual financial statement (Jahresabschluss)
+ # 1 = Transaction batch (Finanzbuchführung),
+ # 2 = Annual financial statement (Jahresabschluss)
'1' if csv_class.DATA_CATEGORY == DataCategory.TRANSACTIONS else '',
# T = Rechnungslegungszweck
- '',
+ # 0 oder leer = vom Rechnungslegungszweck unabhängig
+ # 50 = Handelsrecht
+ # 30 = Steuerrecht
+ # 64 = IFRS
+ # 40 = Kalkulatorik
+ # 11 = Reserviert
+ # 12 = Reserviert
+ '0',
# 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"),
# reserviert
diff --git a/erpnext/regional/report/datev/datev_constants.py b/erpnext/regional/report/datev/datev_constants.py
index a4cd5fc..a059ed3 100644
--- a/erpnext/regional/report/datev/datev_constants.py
+++ b/erpnext/regional/report/datev/datev_constants.py
@@ -498,13 +498,27 @@
},
{
"label": "Beleginfo - Art 1",
- "fieldname": "Beleginfo - Art 2",
- "fieldtype": "Data",
+ "fieldname": "Beleginfo - Art 1",
+ "fieldtype": "Link",
+ "options": "DocType"
},
{
"label": "Beleginfo - Inhalt 1",
+ "fieldname": "Beleginfo - Inhalt 1",
+ "fieldtype": "Dynamic Link",
+ "options": "Beleginfo - Art 1"
+ },
+ {
+ "label": "Beleginfo - Art 2",
+ "fieldname": "Beleginfo - Art 2",
+ "fieldtype": "Link",
+ "options": "DocType"
+ },
+ {
+ "label": "Beleginfo - Inhalt 2",
"fieldname": "Beleginfo - Inhalt 2",
- "fieldtype": "Data",
+ "fieldtype": "Dynamic Link",
+ "options": "Beleginfo - Art 2"
}
]