FEC report for France (#12446)
* FEC report for France
* Codacy correction
diff --git a/erpnext/docs/user/manual/en/regional/france/__init__.py b/erpnext/docs/user/manual/en/regional/france/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/docs/user/manual/en/regional/france/__init__.py
diff --git "a/erpnext/docs/user/manual/en/regional/france/fichier_des_\303\251critures_comptables.md" "b/erpnext/docs/user/manual/en/regional/france/fichier_des_\303\251critures_comptables.md"
new file mode 100644
index 0000000..ac82aea
--- /dev/null
+++ "b/erpnext/docs/user/manual/en/regional/france/fichier_des_\303\251critures_comptables.md"
@@ -0,0 +1,24 @@
+# Le Fichier des Écritures Comptables [FEC]
+
+Since 2014, a legal requirement makes it mandatory for companies operating in France to provide a file of their general accounting postings by fiscal year corresponding to an electronic accounting journal.
+
+For ERPNext users this file can be generated using a report available if you system's country is France.
+
+
+### Requirements
+
+To generate the report correctly, your Chart of Account needs to be setup according to the french accounting rules.
+
+All accounts need to have a number in line with the General Chart of Account (PCG) and a name.
+
+The SIREN number of your company can be added in the "Company" doctype.
+
+
+### CSV generation
+
+You can generate the required CSV file by clicking on "Export" in the top right corner of the report.
+
+
+### Testing Instructions
+
+To test the validity of your file, the tax administration provides a testing tool at the following address: [Outil de test des fichiers des écritures comptables (FEC)](http://www.economie.gouv.fr/dgfip/outil-test-des-fichiers-des-ecritures-comptables-fec)
diff --git a/erpnext/docs/user/manual/en/regional/index.txt b/erpnext/docs/user/manual/en/regional/index.txt
new file mode 100644
index 0000000..1a6cf1b
--- /dev/null
+++ b/erpnext/docs/user/manual/en/regional/index.txt
@@ -0,0 +1,3 @@
+france
+india
+united_arab_emirates
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index d99523f..ff29d47 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -486,5 +486,6 @@
erpnext.patches.v10_0.add_guardian_role_for_parent_portal
erpnext.patches.v10_0.set_numeric_ranges_in_template_if_blank
erpnext.patches.v10_0.update_reserved_qty_for_purchase_order
+erpnext.patches.v10_0.fichier_des_ecritures_comptables_for_france
erpnext.patches.v10_0.update_assessment_plan
erpnext.patches.v10_0.update_assessment_result
diff --git a/erpnext/patches/v10_0/fichier_des_ecritures_comptables_for_france.py b/erpnext/patches/v10_0/fichier_des_ecritures_comptables_for_france.py
new file mode 100644
index 0000000..ada6e4c
--- /dev/null
+++ b/erpnext/patches/v10_0/fichier_des_ecritures_comptables_for_france.py
@@ -0,0 +1,10 @@
+# Copyright (c) 2018, Frappe and Contributors
+# License: GNU General Public License v3. See license.txt
+
+from __future__ import unicode_literals
+import frappe
+from erpnext.setup.doctype.company.company import install_country_fixtures
+
+def execute():
+ for d in frappe.get_all('Company', filters = {'country': 'France'}):
+ install_country_fixtures(d.name)
diff --git a/erpnext/regional/france/__init__.py b/erpnext/regional/france/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/regional/france/__init__.py
diff --git a/erpnext/regional/france/setup.py b/erpnext/regional/france/setup.py
new file mode 100644
index 0000000..db6419e
--- /dev/null
+++ b/erpnext/regional/france/setup.py
@@ -0,0 +1,33 @@
+# Copyright (c) 2018, Frappe Technologies Pvt. Ltd. and Contributors
+# License: GNU General Public License v3. See license.txt
+
+from __future__ import unicode_literals
+
+import frappe
+from frappe.custom.doctype.custom_field.custom_field import create_custom_fields
+
+def setup(company=None, patch=True):
+ make_custom_fields()
+ add_custom_roles_for_reports()
+
+def make_custom_fields():
+ custom_fields = {
+ 'Company': [
+ dict(fieldname='siren_number', label='SIREN Number',
+ fieldtype='Data', insert_after='website')
+ ]
+ }
+
+ create_custom_fields(custom_fields)
+
+def add_custom_roles_for_reports():
+ report_name = 'Fichier des Ecritures Comptables [FEC]'
+
+ if not frappe.db.get_value('Custom Role', dict(report=report_name)):
+ frappe.get_doc(dict(
+ doctype='Custom Role',
+ report=report_name,
+ roles= [
+ dict(role='Accounts Manager')
+ ]
+ )).insert()
diff --git "a/erpnext/regional/report/fichier_des_ecritures_comptables_\133fec\135/__init__.py" "b/erpnext/regional/report/fichier_des_ecritures_comptables_\133fec\135/__init__.py"
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ "b/erpnext/regional/report/fichier_des_ecritures_comptables_\133fec\135/__init__.py"
diff --git "a/erpnext/regional/report/fichier_des_ecritures_comptables_\133fec\135/fichier_des_ecritures_comptables_\133fec\135.js" "b/erpnext/regional/report/fichier_des_ecritures_comptables_\133fec\135/fichier_des_ecritures_comptables_\133fec\135.js"
new file mode 100644
index 0000000..41f375c
--- /dev/null
+++ "b/erpnext/regional/report/fichier_des_ecritures_comptables_\133fec\135/fichier_des_ecritures_comptables_\133fec\135.js"
@@ -0,0 +1,124 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Fichier des Ecritures Comptables [FEC]"] = {
+ "filters": [{
+ "fieldname": "company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "default": frappe.defaults.get_user_default("Company"),
+ "reqd": 1
+ },
+ {
+ "fieldname": "fiscal_year",
+ "label": __("Fiscal Year"),
+ "fieldtype": "Link",
+ "options": "Fiscal Year",
+ "default": frappe.defaults.get_user_default("fiscal_year"),
+ "reqd": 1,
+ "on_change": function(query_report) {
+ var fiscal_year = query_report.get_values().fiscal_year;
+ if (!fiscal_year) {
+ return;
+ }
+ frappe.model.with_doc("Fiscal Year", fiscal_year, function(r) {
+ var fy = frappe.model.get_doc("Fiscal Year", fiscal_year);
+ frappe.query_report_filters_by_name.from_date.set_input(fy.year_start_date);
+ frappe.query_report_filters_by_name.to_date.set_input(fy.year_end_date);
+ query_report.trigger_refresh();
+ });
+ }
+ }
+
+ ],
+
+ onload: function(query_report) {
+ query_report.page.add_inner_button(__("Export"), function() {
+ var fiscal_year = query_report.get_values().fiscal_year;
+ var company = query_report.get_values().company;
+
+ frappe.call({
+ method: "frappe.client.get_value",
+ args: {
+ 'doctype': "Company",
+ 'fieldname': ['siren_number'],
+ 'filters': {
+ 'name': company
+ }
+ },
+ callback: function(data) {
+ var company_data = data.message.siren_number;
+ if (company_data === null || company_data === undefined) {
+ msgprint(__("Please register the SIREN number in the company information file"))
+ } else {
+ frappe.call({
+ method: "frappe.client.get_value",
+ args: {
+ 'doctype': "Fiscal Year",
+ 'fieldname': ['year_end_date'],
+ 'filters': {
+ 'name': fiscal_year
+ }
+ },
+ callback: function(data) {
+ var fy = data.message.year_end_date;
+ var title = company_data + "FEC" + moment(fy).format('YYYYMMDD');
+ var result = $.map(frappe.slickgrid_tools.get_view_data(query_report.columns, query_report.dataView),
+ function(row) {
+ return [row.splice(1)];
+ });
+ downloadify(result, null, title);
+ }
+ });
+
+ }
+ }
+ });
+
+ });
+ }
+}
+
+var downloadify = function(data, roles, title) {
+ if (roles && roles.length && !has_common(roles, roles)) {
+ msgprint(__("Export not allowed. You need {0} role to export.", [frappe.utils.comma_or(roles)]));
+ return;
+ }
+
+ var filename = title + ".csv";
+ var csv_data = to_tab_csv(data);
+ var a = document.createElement('a');
+
+ if ("download" in a) {
+ // Used Blob object, because it can handle large files
+ var blob_object = new Blob([csv_data], {
+ type: 'text/csv;charset=UTF-8'
+ });
+ a.href = URL.createObjectURL(blob_object);
+ a.download = filename;
+
+ } else {
+ // use old method
+ a.href = 'data:attachment/csv,' + encodeURIComponent(csv_data);
+ a.download = filename;
+ a.target = "_blank";
+ }
+
+ document.body.appendChild(a);
+ a.click();
+
+ document.body.removeChild(a);
+};
+
+var to_tab_csv = function(data) {
+ var res = [];
+ $.each(data, function(i, row) {
+ row = $.map(row, function(col) {
+ return typeof(col) === "string" ? ('"' + col.replace(/"/g, '""') + '"') : col;
+ });
+ res.push(row.join("\t"));
+ });
+ return res.join("\n");
+};
diff --git "a/erpnext/regional/report/fichier_des_ecritures_comptables_\133fec\135/fichier_des_ecritures_comptables_\133fec\135.json" "b/erpnext/regional/report/fichier_des_ecritures_comptables_\133fec\135/fichier_des_ecritures_comptables_\133fec\135.json"
new file mode 100644
index 0000000..9b48e11
--- /dev/null
+++ "b/erpnext/regional/report/fichier_des_ecritures_comptables_\133fec\135/fichier_des_ecritures_comptables_\133fec\135.json"
@@ -0,0 +1,19 @@
+{
+ "add_total_row": 0,
+ "apply_user_permissions": 0,
+ "creation": "2018-01-10 15:10:16.650129",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2018-01-11 10:27:25.595485",
+ "modified_by": "Administrator",
+ "module": "Regional",
+ "name": "Fichier des Ecritures Comptables [FEC]",
+ "owner": "Administrator",
+ "ref_doctype": "GL Entry",
+ "report_name": "Fichier des Ecritures Comptables [FEC]",
+ "report_type": "Script Report",
+ "roles": []
+}
\ No newline at end of file
diff --git "a/erpnext/regional/report/fichier_des_ecritures_comptables_\133fec\135/fichier_des_ecritures_comptables_\133fec\135.py" "b/erpnext/regional/report/fichier_des_ecritures_comptables_\133fec\135/fichier_des_ecritures_comptables_\133fec\135.py"
new file mode 100644
index 0000000..781082e
--- /dev/null
+++ "b/erpnext/regional/report/fichier_des_ecritures_comptables_\133fec\135/fichier_des_ecritures_comptables_\133fec\135.py"
@@ -0,0 +1,178 @@
+# Copyright (c) 2018, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe.utils import format_datetime
+from frappe import _
+
+
+def execute(filters=None):
+ account_details = {}
+ for acc in frappe.db.sql("""select name, is_group from tabAccount""", as_dict=1):
+ account_details.setdefault(acc.name, acc)
+
+ validate_filters(filters, account_details)
+
+ filters = set_account_currency(filters)
+
+ columns = get_columns(filters)
+
+ res = get_result(filters)
+
+ return columns, res
+
+
+def validate_filters(filters, account_details):
+ if not filters.get('company'):
+ frappe.throw(_('{0} is mandatory').format(_('Company')))
+
+ if not filters.get('fiscal_year'):
+ frappe.throw(_('{0} is mandatory').format(_('Fiscal Year')))
+
+
+def set_account_currency(filters):
+
+ filters["company_currency"] = frappe.db.get_value("Company", filters.company, "default_currency")
+
+ return filters
+
+
+def get_columns(filters):
+ columns = [
+ _("JournalCode") + "::90", _("JournalLib") + "::90",
+ _("EcritureNum") + ":Dynamic Link:90", _("EcritureDate") + "::90",
+ _("CompteNum") + ":Link/Account:100", _("CompteLib") + ":Link/Account:200",
+ _("CompAuxNum") + "::90", _("CompAuxLib") + "::90",
+ _("PieceRef") + "::90", _("PieceDate") + "::90",
+ _("EcritureLib") + "::90", _("Debit") + "::90", _("Credit") + "::90",
+ _("EcritureLet") + "::90", _("DateLet") +
+ "::90", _("ValidDate") + "::90",
+ _("Montantdevise") + "::90", _("Idevise") + "::90"
+ ]
+
+ return columns
+
+
+def get_result(filters):
+ gl_entries = get_gl_entries(filters)
+
+ result = get_result_as_list(gl_entries, filters)
+
+ return result
+
+
+def get_gl_entries(filters):
+
+ group_by_condition = "group by voucher_type, voucher_no, account" \
+ if filters.get("group_by_voucher") else "group by gl.name"
+
+ gl_entries = frappe.db.sql("""
+ select
+ gl.posting_date as GlPostDate, gl.account, gl.transaction_date,
+ sum(gl.debit) as debit, sum(gl.credit) as credit,
+ sum(gl.debit_in_account_currency) as debitCurr, sum(gl.credit_in_account_currency) as creditCurr,
+ gl.voucher_type, gl.voucher_no, gl.against_voucher_type,
+ gl.against_voucher, gl.account_currency, gl.against,
+ gl.party_type, gl.party, gl.is_opening,
+ inv.name as InvName, inv.posting_date as InvPostDate,
+ pur.name as PurName, inv.posting_date as PurPostDate,
+ jnl.cheque_no as JnlRef, jnl.posting_date as JnlPostDate,
+ pay.name as PayName, pay.posting_date as PayPostDate,
+ cus.customer_name, cus.name as cusName,
+ sup.supplier_name, sup.name as supName
+
+ from `tabGL Entry` gl
+ left join `tabSales Invoice` inv on gl.against_voucher = inv.name
+ left join `tabPurchase Invoice` pur on gl.against_voucher = pur.name
+ left join `tabJournal Entry` jnl on gl.against_voucher = jnl.name
+ left join `tabPayment Entry` pay on gl.against_voucher = pay.name
+ left join `tabCustomer` cus on gl.party = cus.customer_name
+ left join `tabSupplier` sup on gl.party = sup.supplier_name
+ where gl.company=%(company)s and gl.fiscal_year=%(fiscal_year)s
+ {group_by_condition}
+ order by GlPostDate, voucher_no"""
+ .format(group_by_condition=group_by_condition), filters, as_dict=1)
+
+ return gl_entries
+
+
+def get_result_as_list(data, filters):
+ result = []
+
+ company_currency = frappe.db.get_value("Company", filters.company, "default_currency")
+ accounts = frappe.get_all("Account", filters={"Company": filters.company}, fields=["name", "account_number"])
+
+ for d in data:
+
+ JournalCode = d.get("voucher_no").split("-")[0]
+
+ EcritureNum = d.get("voucher_no").split("-")[-1]
+
+ EcritureDate = format_datetime(d.get("GlPostDate"), "yyyyMMdd")
+
+ account_number = [account.account_number for account in accounts if account.name == d.get("account")]
+ if account_number[0] is not None:
+ CompteNum = account_number[0]
+ else:
+ frappe.throw(_("Account number for account {0} is not available.<br> Please setup your Chart of Accounts correctly.").format(account.name))
+
+ if d.get("party_type") == "Customer":
+ CompAuxNum = d.get("cusName")
+ CompAuxLib = d.get("customer_name")
+
+ elif d.get("party_type") == "Supplier":
+ CompAuxNum = d.get("supName")
+ CompAuxLib = d.get("supplier_name")
+
+ else:
+ CompAuxNum = ""
+ CompAuxLib = ""
+
+ ValidDate = format_datetime(d.get("GlPostDate"), "yyyyMMdd")
+
+ if d.get("is_opening") == "Yes":
+ PieceRef = _("Opening Entry Journal")
+ PieceDate = format_datetime(d.get("GlPostDate"), "yyyyMMdd")
+
+ elif d.get("against_voucher_type") == "Sales Invoice":
+ PieceRef = _(d.get("InvName"))
+ PieceDate = format_datetime(d.get("InvPostDate"), "yyyyMMdd")
+
+ elif d.get("against_voucher_type") == "Purchase Invoice":
+ PieceRef = _(d.get("PurName"))
+ PieceDate = format_datetime(d.get("PurPostDate"), "yyyyMMdd")
+
+ elif d.get("against_voucher_type") == "Journal Entry":
+ PieceRef = _(d.get("JnlRef"))
+ PieceDate = format_datetime(d.get("JnlPostDate"), "yyyyMMdd")
+
+ elif d.get("against_voucher_type") == "Payment Entry":
+ PieceRef = _(d.get("PayName"))
+ PieceDate = format_datetime(d.get("PayPostDate"), "yyyyMMdd")
+
+ elif d.get("voucher_type") == "Period Closing Voucher":
+ PieceRef = _("Period Closing Journal")
+ PieceDate = format_datetime(d.get("GlPostDate"), "yyyyMMdd")
+
+ else:
+ PieceRef = _("No Reference")
+ PieceDate = format_datetime(d.get("GlPostDate"), "yyyyMMdd")
+
+ debit = '{:.2f}'.format(d.get("debit")).replace(".", ",")
+
+ credit = '{:.2f}'.format(d.get("credit")).replace(".", ",")
+
+ Idevise = d.get("account_currency")
+
+ if Idevise != company_currency:
+ Montantdevise = '{:.2f}'.format(d.get("debitCurr")).replace(".", ",") if d.get("debitCurr") != 0 else '{:.2f}'.format(d.get("creditCurr")).replace(".", ",")
+ else:
+ Montantdevise = '{:.2f}'.format(d.get("debit")).replace(".", ",") if d.get("debit") != 0 else '{:.2f}'.format(d.get("credit")).replace(".", ",")
+
+ row = [JournalCode, d.get("voucher_type"), EcritureNum, EcritureDate, CompteNum, d.get("account"), CompAuxNum, CompAuxLib,
+ PieceRef, PieceDate, d.get("voucher_no"), debit, credit, "", "", ValidDate, Montantdevise, Idevise]
+
+ result.append(row)
+
+ return result