[fix] Bank Reconciliation in account currency
diff --git a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.js b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.js
index 9ffed71..3de8241 100644
--- a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.js
+++ b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.js
@@ -4,7 +4,7 @@
frappe.ui.form.on("Bank Reconciliation", {
setup: function(frm) {
frm.get_docfield("journal_entries").allow_bulk_edit = 1;
- frm.add_fetch("bank_account", "company", "company");
+ frm.add_fetch("bank_account", "account_currency", "account_currency");
},
onload: function(frm) {
diff --git a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.json b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.json
index df18ab9..59a3cfc 100644
--- a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.json
+++ b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.json
@@ -19,10 +19,12 @@
"in_filter": 0,
"in_list_view": 1,
"label": "Bank Account",
+ "length": 0,
"no_copy": 0,
"options": "Account",
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 0,
"report_hide": 0,
"reqd": 1,
@@ -34,17 +36,19 @@
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
- "fieldname": "company",
+ "fieldname": "account_currency",
"fieldtype": "Link",
"hidden": 1,
"ignore_user_permissions": 0,
"in_filter": 0,
- "in_list_view": 1,
- "label": "Company",
+ "in_list_view": 0,
+ "label": "Account Currency",
+ "length": 0,
"no_copy": 0,
- "options": "Company",
+ "options": "Currency",
"permlevel": 0,
"print_hide": 1,
+ "print_hide_if_no_value": 0,
"read_only": 0,
"report_hide": 0,
"reqd": 0,
@@ -63,9 +67,11 @@
"in_filter": 0,
"in_list_view": 1,
"label": "From Date",
+ "length": 0,
"no_copy": 0,
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 0,
"report_hide": 0,
"reqd": 1,
@@ -84,9 +90,11 @@
"in_filter": 0,
"in_list_view": 1,
"label": "To Date",
+ "length": 0,
"no_copy": 0,
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 0,
"report_hide": 0,
"reqd": 1,
@@ -105,9 +113,11 @@
"in_filter": 0,
"in_list_view": 1,
"label": "Include Reconciled Entries",
+ "length": 0,
"no_copy": 0,
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 0,
"report_hide": 0,
"reqd": 0,
@@ -126,10 +136,12 @@
"in_filter": 0,
"in_list_view": 0,
"label": "Get Relevant Entries",
+ "length": 0,
"no_copy": 0,
"options": "",
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 0,
"report_hide": 0,
"reqd": 0,
@@ -148,10 +160,12 @@
"in_filter": 0,
"in_list_view": 0,
"label": "Journal Entries",
+ "length": 0,
"no_copy": 0,
"options": "Bank Reconciliation Detail",
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 0,
"report_hide": 0,
"reqd": 0,
@@ -170,10 +184,12 @@
"in_filter": 0,
"in_list_view": 0,
"label": "Update Clearance Date",
+ "length": 0,
"no_copy": 0,
"options": "",
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 0,
"report_hide": 0,
"reqd": 0,
@@ -192,10 +208,12 @@
"in_filter": 0,
"in_list_view": 0,
"label": "Total Amount",
+ "length": 0,
"no_copy": 0,
- "options": "Company:company:default_currency",
+ "options": "account_currency",
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 1,
"report_hide": 0,
"reqd": 0,
@@ -213,7 +231,9 @@
"is_submittable": 0,
"issingle": 1,
"istable": 0,
- "modified": "2015-02-05 05:11:34.776660",
+ "max_attachments": 0,
+ "menu_index": 0,
+ "modified": "2015-11-30 12:44:45.105451",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Bank Reconciliation",
@@ -241,5 +261,6 @@
}
],
"read_only": 1,
- "read_only_onload": 0
+ "read_only_onload": 0,
+ "version": 0
}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py
index 731425c..3c280bd 100644
--- a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py
+++ b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py
@@ -18,9 +18,8 @@
condition = "and (clearance_date is null or clearance_date='0000-00-00')"
- dl = frappe.db.sql("""select t1.name, t1.cheque_no, t1.cheque_date, t2.debit,
- t2.credit, t1.posting_date, t2.against_account, t1.clearance_date,
- t2.reference_type, t2.reference_name
+ dl = frappe.db.sql("""select t1.name, t1.cheque_no, t1.cheque_date, t2.debit_in_account_currency,
+ t2.credit_in_account_currency, t1.posting_date, t2.against_account, t1.clearance_date
from
`tabJournal Entry` t1, `tabJournal Entry Account` t2
where
@@ -39,11 +38,11 @@
nl.voucher_id = d.name
nl.cheque_number = d.cheque_no
nl.cheque_date = d.cheque_date
- nl.debit = d.debit
- nl.credit = d.credit
+ nl.debit = d.debit_in_account_currency
+ nl.credit = d.credit_in_account_currency
nl.against_account = d.against_account
nl.clearance_date = d.clearance_date
- self.total_amount += flt(d.debit) - flt(d.credit)
+ self.total_amount += flt(d.debit_in_account_currency) - flt(d.credit_in_account_currency)
def update_details(self):
vouchers = []
diff --git a/erpnext/accounts/doctype/bank_reconciliation_detail/bank_reconciliation_detail.json b/erpnext/accounts/doctype/bank_reconciliation_detail/bank_reconciliation_detail.json
index ca4523c..d510f63 100644
--- a/erpnext/accounts/doctype/bank_reconciliation_detail/bank_reconciliation_detail.json
+++ b/erpnext/accounts/doctype/bank_reconciliation_detail/bank_reconciliation_detail.json
@@ -25,30 +25,7 @@
"options": "Journal Entry",
"permlevel": 0,
"print_hide": 0,
- "read_only": 0,
- "report_hide": 0,
- "reqd": 0,
- "search_index": 0,
- "set_only_once": 0,
- "unique": 0
- },
- {
- "allow_on_submit": 0,
- "bold": 0,
- "collapsible": 0,
- "fieldname": "clearance_date",
- "fieldtype": "Date",
- "hidden": 0,
- "ignore_user_permissions": 0,
- "in_filter": 0,
- "in_list_view": 1,
- "label": "Clearance Date",
- "length": 0,
- "no_copy": 0,
- "oldfieldname": "clearance_date",
- "oldfieldtype": "Date",
- "permlevel": 0,
- "print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 0,
"report_hide": 0,
"reqd": 0,
@@ -73,6 +50,108 @@
"oldfieldtype": "Data",
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 1,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0
+ },
+ {
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "fieldname": "debit",
+ "fieldtype": "Currency",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "in_filter": 0,
+ "in_list_view": 1,
+ "label": "Debit",
+ "length": 0,
+ "no_copy": 0,
+ "oldfieldname": "debit",
+ "oldfieldtype": "Currency",
+ "options": "account_currency",
+ "permlevel": 0,
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 1,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0
+ },
+ {
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "fieldname": "credit",
+ "fieldtype": "Currency",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "in_filter": 0,
+ "in_list_view": 1,
+ "label": "Credit",
+ "length": 0,
+ "no_copy": 0,
+ "oldfieldname": "credit",
+ "oldfieldtype": "Currency",
+ "options": "account_currency",
+ "permlevel": 0,
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 1,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0
+ },
+ {
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "fieldname": "column_break_5",
+ "fieldtype": "Column Break",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "in_filter": 0,
+ "in_list_view": 0,
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0,
+ "width": "50%"
+ },
+ {
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "fieldname": "posting_date",
+ "fieldtype": "Date",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "in_filter": 0,
+ "in_list_view": 0,
+ "label": "Posting Date",
+ "length": 0,
+ "no_copy": 0,
+ "oldfieldname": "posting_date",
+ "oldfieldtype": "Date",
+ "permlevel": 0,
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 1,
"report_hide": 0,
"reqd": 0,
@@ -97,128 +176,7 @@
"oldfieldtype": "Data",
"permlevel": 0,
"print_hide": 0,
- "read_only": 1,
- "report_hide": 0,
- "reqd": 0,
- "search_index": 0,
- "set_only_once": 0,
- "unique": 0
- },
- {
- "allow_on_submit": 0,
- "bold": 0,
- "collapsible": 0,
- "fieldname": "debit",
- "fieldtype": "Currency",
- "hidden": 0,
- "ignore_user_permissions": 0,
- "in_filter": 0,
- "in_list_view": 1,
- "label": "Debit",
- "length": 0,
- "no_copy": 0,
- "oldfieldname": "debit",
- "oldfieldtype": "Currency",
- "options": "Company:company:default_currency",
- "permlevel": 0,
- "print_hide": 0,
- "read_only": 1,
- "report_hide": 0,
- "reqd": 0,
- "search_index": 0,
- "set_only_once": 0,
- "unique": 0
- },
- {
- "allow_on_submit": 0,
- "bold": 0,
- "collapsible": 0,
- "fieldname": "credit",
- "fieldtype": "Currency",
- "hidden": 0,
- "ignore_user_permissions": 0,
- "in_filter": 0,
- "in_list_view": 1,
- "label": "Credit",
- "length": 0,
- "no_copy": 0,
- "oldfieldname": "credit",
- "oldfieldtype": "Currency",
- "options": "Company:company:default_currency",
- "permlevel": 0,
- "print_hide": 0,
- "read_only": 1,
- "report_hide": 0,
- "reqd": 0,
- "search_index": 0,
- "set_only_once": 0,
- "unique": 0
- },
- {
- "allow_on_submit": 0,
- "bold": 0,
- "collapsible": 0,
- "fieldname": "reference_type",
- "fieldtype": "Link",
- "hidden": 0,
- "ignore_user_permissions": 0,
- "in_filter": 0,
- "in_list_view": 0,
- "label": "Reference Type",
- "length": 0,
- "no_copy": 0,
- "options": "DocType",
- "permlevel": 0,
- "precision": "",
- "print_hide": 0,
- "read_only": 1,
- "report_hide": 0,
- "reqd": 0,
- "search_index": 0,
- "set_only_once": 0,
- "unique": 0
- },
- {
- "allow_on_submit": 0,
- "bold": 0,
- "collapsible": 0,
- "fieldname": "reference_name",
- "fieldtype": "Dynamic Link",
- "hidden": 0,
- "ignore_user_permissions": 0,
- "in_filter": 0,
- "in_list_view": 0,
- "label": "Reference Name",
- "length": 0,
- "no_copy": 0,
- "options": "reference_type",
- "permlevel": 0,
- "precision": "",
- "print_hide": 0,
- "read_only": 1,
- "report_hide": 0,
- "reqd": 0,
- "search_index": 0,
- "set_only_once": 0,
- "unique": 0
- },
- {
- "allow_on_submit": 0,
- "bold": 0,
- "collapsible": 0,
- "fieldname": "posting_date",
- "fieldtype": "Date",
- "hidden": 0,
- "ignore_user_permissions": 0,
- "in_filter": 0,
- "in_list_view": 0,
- "label": "Posting Date",
- "length": 0,
- "no_copy": 0,
- "oldfieldname": "posting_date",
- "oldfieldtype": "Date",
- "permlevel": 0,
- "print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 1,
"report_hide": 0,
"reqd": 0,
@@ -243,12 +201,38 @@
"oldfieldtype": "Date",
"permlevel": 0,
"print_hide": 0,
+ "print_hide_if_no_value": 0,
"read_only": 1,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
"unique": 0
+ },
+ {
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "fieldname": "clearance_date",
+ "fieldtype": "Date",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "in_filter": 0,
+ "in_list_view": 1,
+ "label": "Clearance Date",
+ "length": 0,
+ "no_copy": 0,
+ "oldfieldname": "clearance_date",
+ "oldfieldtype": "Date",
+ "permlevel": 0,
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0
}
],
"hide_heading": 0,
@@ -260,12 +244,14 @@
"issingle": 0,
"istable": 1,
"max_attachments": 0,
- "modified": "2015-11-16 06:29:42.254366",
+ "menu_index": 0,
+ "modified": "2015-11-30 12:52:01.090839",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Bank Reconciliation Detail",
"owner": "Administrator",
"permissions": [],
"read_only": 0,
- "read_only_onload": 0
+ "read_only_onload": 0,
+ "version": 0
}
\ No newline at end of file
diff --git a/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.py b/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.py
index e11878e..d57bf07 100644
--- a/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.py
+++ b/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.py
@@ -12,16 +12,18 @@
columns = get_columns()
if not filters.get("account"): return columns, []
+
+ account_currency = frappe.db.get_value("Account", filters.account, "account_currency")
data = get_entries(filters)
-
+
from erpnext.accounts.utils import get_balance_on
balance_as_per_system = get_balance_on(filters["account"], filters["report_date"])
total_debit, total_credit = 0,0
for d in data:
- total_debit += flt(d[2])
- total_credit += flt(d[3])
+ total_debit += flt(d.debit_in_account_currency)
+ total_credit += flt(d.credit_in_account_currency)
amounts_not_reflected_in_system = frappe.db.sql("""
select sum(jvd.debit_in_account_currency - jvd.credit_in_account_currency)
@@ -37,39 +39,112 @@
+ amounts_not_reflected_in_system
data += [
- get_balance_row(_("System Balance"), balance_as_per_system),
- [""]*len(columns),
- ["", '"' + _("Amounts not reflected in bank") + '"', total_debit, total_credit, "", "", "", "", ""],
- get_balance_row(_("Amounts not reflected in system"), amounts_not_reflected_in_system),
- [""]*len(columns),
- get_balance_row(_("Expected balance as per bank"), bank_bal)
+ get_balance_row(_("System Balance"), balance_as_per_system, account_currency),
+ {},
+ {
+ "journal_entry": '"' + _("Amounts not reflected in bank") + '"',
+ "debit": total_debit,
+ "credit": total_credit,
+ "account_currency": account_currency
+ },
+ get_balance_row(_("Amounts not reflected in system"), amounts_not_reflected_in_system,
+ account_currency),
+ {},
+ get_balance_row(_("Expected balance as per bank"), bank_bal, account_currency)
]
return columns, data
def get_columns():
- return [_("Posting Date") + ":Date:100", _("Journal Entry") + ":Link/Journal Entry:220",
- _("Debit") + ":Currency:120", _("Credit") + ":Currency:120",
- _("Against Account") + ":Link/Account:200", _("Reference") + "::100",
- _("Ref Date") + ":Date:110", _("Clearance Date") + ":Date:110", _("Currency") + ":Link/Currency:70"
+ return [
+ {
+ "fieldname": "posting_date",
+ "label": _("Posting Date"),
+ "fieldtype": "Date",
+ "width": 100
+ },
+ {
+ "fieldname": "journal_entry",
+ "label": _("Journal Entry"),
+ "fieldtype": "Link",
+ "options": "Journal Entry",
+ "width": 220
+ },
+ {
+ "fieldname": "debit",
+ "label": _("Debit"),
+ "fieldtype": "Currency",
+ "options": "account_currency",
+ "width": 120
+ },
+ {
+ "fieldname": "credit",
+ "label": _("Credit"),
+ "fieldtype": "Currency",
+ "options": "account_currency",
+ "width": 120
+ },
+ {
+ "fieldname": "against_account",
+ "label": _("Against Account"),
+ "fieldtype": "Link",
+ "options": "Account",
+ "width": 200
+ },
+ {
+ "fieldname": "reference",
+ "label": _("Reference"),
+ "fieldtype": "Data",
+ "width": 100
+ },
+ {
+ "fieldname": "ref_date",
+ "label": _("Ref Date"),
+ "fieldtype": "Date",
+ "width": 110
+ },
+ {
+ "fieldname": "clearance_date",
+ "label": _("Clearance Date"),
+ "fieldtype": "Date",
+ "width": 110
+ },
+ {
+ "fieldname": "account_currency",
+ "label": _("Currency"),
+ "fieldtype": "Link",
+ "options": "Currency",
+ "width": 100
+ }
]
def get_entries(filters):
entries = frappe.db.sql("""select
- jv.posting_date, jv.name, jvd.debit_in_account_currency, jvd.credit_in_account_currency,
- jvd.against_account, jv.cheque_no, jv.cheque_date, jv.clearance_date, jvd.account_currency
+ jv.posting_date, jv.name as journal_entry, jvd.debit_in_account_currency as debit,
+ jvd.credit_in_account_currency as credit, jvd.against_account,
+ jv.cheque_no as reference, jv.cheque_date as ref_date, jv.clearance_date, jvd.account_currency
from
`tabJournal Entry Account` jvd, `tabJournal Entry` jv
where jvd.parent = jv.name and jv.docstatus=1
and jvd.account = %(account)s and jv.posting_date <= %(report_date)s
and ifnull(jv.clearance_date, '4000-01-01') > %(report_date)s
and ifnull(jv.is_opening, 'No') = 'No'
- order by jv.name DESC""", filters, as_list=1)
+ order by jv.name DESC""", filters, as_dict=1)
return entries
-def get_balance_row(label, amount):
+def get_balance_row(label, amount, account_currency):
if amount > 0:
- return ["", '"' + label + '"', amount, 0, "", "", "", "", ""]
+ return {
+ "journal_entry": '"' + label + '"',
+ "debit": amount,
+ "credit": 0,
+ "account_currency": account_currency
+ }
else:
- return ["", '"' + label + '"', 0, abs(amount), "", "", "", "", ""]
+ return {
+ "journal_entry": '"' + label + '"',
+ "debit": 0,
+ "credit": abs(amount),
+ "account_currency": account_currency
+ }
\ No newline at end of file