[report] General Ledger in multi currency
diff --git a/erpnext/accounts/doctype/journal_entry/journal_entry.py b/erpnext/accounts/doctype/journal_entry/journal_entry.py
index 57d9f0d..58464dc 100644
--- a/erpnext/accounts/doctype/journal_entry/journal_entry.py
+++ b/erpnext/accounts/doctype/journal_entry/journal_entry.py
@@ -258,7 +258,7 @@
for d in self.get("accounts"):
d.account_currency = frappe.db.get_value("Account", d.account, "account_currency") or self.company_currency
- if d.account_currency!=self.company_currency:
+ if d.account_currency!=self.company_currency and d.account_currency not in alternate_currency:
alternate_currency.append(d.account_currency)
if alternate_currency:
diff --git a/erpnext/accounts/report/general_ledger/general_ledger.py b/erpnext/accounts/report/general_ledger/general_ledger.py
index 1cc9f4f..fd91929 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.py
+++ b/erpnext/accounts/report/general_ledger/general_ledger.py
@@ -12,9 +12,12 @@
account_details.setdefault(acc.name, acc)
validate_filters(filters, account_details)
+
validate_party(filters)
+
+ filters = set_account_currency(filters)
- columns = get_columns()
+ columns = get_columns(filters)
res = get_result(filters, account_details)
@@ -43,36 +46,77 @@
frappe.throw(_("To filter based on Party, select Party Type first"))
elif not frappe.db.exists(party_type, party):
frappe.throw(_("Invalid {0}: {1}").format(party_type, party))
-
-def get_columns():
- return [_("Posting Date") + ":Date:90", _("Account") + ":Link/Account:200",
- _("Debit") + ":Float:100", _("Credit") + ":Float:100",
+
+def set_account_currency(filters):
+ if not (filters.get("account") or filters.get("party")):
+ return filters
+ else:
+ filters["company_currency"] = frappe.db.get_value("Company", filters.company, "default_currency")
+ account_currency = None
+
+ if filters.get("account"):
+ account_currency = frappe.db.get_value("Account", filters.account, "account_currency")
+ elif filters.get("party"):
+ gle_currency = frappe.db.get_value("GL Entry", {"party_type": filters.party_type,
+ "party": filters.party, "company": filters.company}, "account_currency")
+ if gle_currency:
+ account_currency = gle_currency
+ else:
+ account_currency = frappe.db.get_value(filters.party_type, filters.party, "default_currency")
+
+ filters["account_currency"] = account_currency or filters.company_currency
+
+ if filters.account_currency != filters.company_currency:
+ filters["show_in_account_currency"] = 1
+
+ return filters
+
+def get_columns(filters):
+ columns = [
+ _("Posting Date") + ":Date:90", _("Account") + ":Link/Account:200",
+ _("Debit") + ":Float:100", _("Credit") + ":Float:100"
+ ]
+
+ if filters.get("show_in_account_currency"):
+ columns += [
+ _("Debit") + " (" + filters.account_currency + ")" + ":Float:100",
+ _("Credit") + " (" + filters.account_currency + ")" + ":Float:100"
+ ]
+
+ columns += [
_("Voucher Type") + "::120", _("Voucher No") + ":Dynamic Link/Voucher Type:160",
_("Against Account") + "::120", _("Party Type") + "::80", _("Party") + "::150",
- _("Cost Center") + ":Link/Cost Center:100", _("Remarks") + "::400"]
+ _("Cost Center") + ":Link/Cost Center:100", _("Remarks") + "::400"
+ ]
+
+ return columns
def get_result(filters, account_details):
gl_entries = get_gl_entries(filters)
data = get_data_with_opening_closing(filters, account_details, gl_entries)
- result = get_result_as_list(data)
+ result = get_result_as_list(data, filters)
return result
def get_gl_entries(filters):
+ select_fields = """, sum(ifnull(debit_in_account_currency, 0)) as debit_in_account_currency,
+ sum(ifnull(credit_in_account_currency, 0)) as credit_in_account_currency""" \
+ if filters.get("show_in_account_currency") else ""
+
group_by_condition = "group by voucher_type, voucher_no, account, cost_center" \
if filters.get("group_by_voucher") else "group by name"
gl_entries = frappe.db.sql("""select posting_date, account, party_type, party,
- sum(ifnull(debit, 0)) as debit, sum(ifnull(credit, 0)) as credit,
- voucher_type, voucher_no, cost_center, remarks, against, is_opening
+ sum(ifnull(debit, 0)) as debit, sum(ifnull(credit, 0)) as credit,
+ voucher_type, voucher_no, cost_center, remarks, against, is_opening {select_fields}
from `tabGL Entry`
where company=%(company)s {conditions}
{group_by_condition}
order by posting_date, account"""\
- .format(conditions=get_conditions(filters), group_by_condition=group_by_condition),
- filters, as_dict=1)
+ .format(select_fields=select_fields, conditions=get_conditions(filters),
+ group_by_condition=group_by_condition), filters, as_dict=1)
return gl_entries
@@ -105,35 +149,51 @@
data = []
gle_map = initialize_gle_map(gl_entries)
- opening, total_debit, total_credit, gle_map = get_accountwise_gle(filters, gl_entries, gle_map)
+ opening, total_debit, total_credit, opening_in_account_currency, total_debit_in_account_currency, \
+ total_credit_in_account_currency, gle_map = get_accountwise_gle(filters, gl_entries, gle_map)
# Opening for filtered account
if filters.get("account") or filters.get("party"):
- data += [get_balance_row(_("Opening"), opening), {}]
+ data += [get_balance_row(_("Opening"), opening, opening_in_account_currency), {}]
for acc, acc_dict in gle_map.items():
if acc_dict.entries:
# Opening for individual ledger, if grouped by account
if filters.get("group_by_account"):
- data.append(get_balance_row(_("Opening"), acc_dict.opening))
+ data.append(get_balance_row(_("Opening"), acc_dict.opening,
+ acc_dict.opening_in_account_currency))
data += acc_dict.entries
# Totals and closing for individual ledger, if grouped by account
if filters.get("group_by_account"):
+ account_closing = acc_dict.opening + acc_dict.total_debit - acc_dict.total_credit
+ account_closing_in_account_currency = acc_dict.opening_in_account_currency \
+ + acc_dict.total_debit_in_account_currency - acc_dict.total_credit_in_account_currency
+
data += [{"account": "'" + _("Totals") + "'", "debit": acc_dict.total_debit,
"credit": acc_dict.total_credit},
get_balance_row(_("Closing (Opening + Totals)"),
- (acc_dict.opening + acc_dict.total_debit - acc_dict.total_credit)), {}]
+ account_closing, account_closing_in_account_currency), {}]
# Total debit and credit between from and to date
if total_debit or total_credit:
- data.append({"account": "'" + _("Totals") + "'", "debit": total_debit, "credit": total_credit})
+ data.append({
+ "account": "'" + _("Totals") + "'",
+ "debit": total_debit,
+ "credit": total_credit,
+ "debit_in_account_currency": total_debit_in_account_currency,
+ "credit_in_account_currency": total_credit_in_account_currency
+ })
# Closing for filtered account
if filters.get("account") or filters.get("party"):
+ closing = opening + total_debit - total_credit
+ closing_in_account_currency = opening_in_account_currency + \
+ total_debit_in_account_currency - total_credit_in_account_currency
+
data.append(get_balance_row(_("Closing (Opening + Totals)"),
- (opening + total_debit - total_credit)))
+ closing, closing_in_account_currency))
return data
@@ -142,46 +202,83 @@
for gle in gl_entries:
gle_map.setdefault(gle.account, frappe._dict({
"opening": 0,
+ "opening_in_account_currency": 0,
"entries": [],
"total_debit": 0,
+ "total_debit_in_account_currency": 0,
"total_credit": 0,
- "closing": 0
+ "total_credit_in_account_currency": 0,
+ "closing": 0,
+ "closing_in_account_currency": 0
}))
return gle_map
def get_accountwise_gle(filters, gl_entries, gle_map):
opening, total_debit, total_credit = 0, 0, 0
+ opening_in_account_currency, total_debit_in_account_currency, total_credit_in_account_currency = 0, 0, 0
+
from_date, to_date = getdate(filters.from_date), getdate(filters.to_date)
for gle in gl_entries:
amount = flt(gle.debit, 3) - flt(gle.credit, 3)
+ amount_in_account_currency = flt(gle.debit_in_account_currency, 3) - flt(gle.credit_in_account_currency, 3)
+
if (filters.get("account") or filters.get("party") or filters.get("group_by_account")) \
and (gle.posting_date < from_date or cstr(gle.is_opening) == "Yes"):
+
gle_map[gle.account].opening += amount
+ if filters.get("show_in_account_currency"):
+ gle_map[gle.account].opening_in_account_currency += amount_in_account_currency
+
if filters.get("account") or filters.get("party"):
opening += amount
+ if filters.get("show_in_account_currency"):
+ opening_in_account_currency += amount_in_account_currency
+
elif gle.posting_date <= to_date:
gle_map[gle.account].entries.append(gle)
gle_map[gle.account].total_debit += flt(gle.debit, 3)
gle_map[gle.account].total_credit += flt(gle.credit, 3)
-
+
total_debit += flt(gle.debit, 3)
total_credit += flt(gle.credit, 3)
+
+ if filters.get("show_in_account_currency"):
+ gle_map[gle.account].total_debit_in_account_currency += flt(gle.debit_in_account_currency, 3)
+ gle_map[gle.account].total_credit_in_account_currency += flt(gle.credit_in_account_currency, 3)
+
+ total_debit_in_account_currency += flt(gle.debit_in_account_currency, 3)
+ total_credit_in_account_currency += flt(gle.credit_in_account_currency, 3)
- return opening, total_debit, total_credit, gle_map
+ return opening, total_debit, total_credit, opening_in_account_currency, \
+ total_debit_in_account_currency, total_credit_in_account_currency, gle_map
-def get_balance_row(label, balance):
- return {
+def get_balance_row(label, balance, balance_in_account_currency=None):
+ balance_row = {
"account": "'" + label + "'",
"debit": balance if balance > 0 else 0,
- "credit": -1*balance if balance < 0 else 0,
+ "credit": -1*balance if balance < 0 else 0
}
+
+ if balance_in_account_currency != None:
+ balance_row.update({
+ "debit_in_account_currency": balance_in_account_currency if balance_in_account_currency > 0 else 0,
+ "credit_in_account_currency": -1*balance_in_account_currency if balance_in_account_currency < 0 else 0
+ })
+
+ return balance_row
-def get_result_as_list(data):
+def get_result_as_list(data, filters):
result = []
for d in data:
- result.append([d.get("posting_date"), d.get("account"), d.get("debit"),
- d.get("credit"), d.get("voucher_type"), d.get("voucher_no"),
- d.get("against"), d.get("party_type"), d.get("party"),
- d.get("cost_center"), d.get("remarks")])
+ row = [d.get("posting_date"), d.get("account"), d.get("debit"), d.get("credit")]
+
+ if filters.get("show_in_account_currency"):
+ row += [d.get("debit_in_account_currency"), d.get("credit_in_account_currency")]
+
+ row += [d.get("voucher_type"), d.get("voucher_no"), d.get("against"),
+ d.get("party_type"), d.get("party"), d.get("cost_center"), d.get("remarks")
+ ]
+
+ result.append(row)
return result