[Enhance] Running Balance in GL Report (#12491)
* Running Balance in GL Report
* Supplier invoice no in GL Report/Print Format
diff --git a/erpnext/accounts/report/general_ledger/general_ledger.html b/erpnext/accounts/report/general_ledger/general_ledger.html
index 91cc3c9..83325aa 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.html
+++ b/erpnext/accounts/report/general_ledger/general_ledger.html
@@ -19,36 +19,66 @@
<tr>
<th style="width: 15%">{%= __("Date") %}</th>
<th style="width: 15%">{%= __("Ref") %}</th>
- <th style="width: 40%">{%= __("Party") %}</th>
+ <th style="width: 25%">{%= __("Party") %}</th>
<th style="width: 15%">{%= __("Debit") %}</th>
<th style="width: 15%">{%= __("Credit") %}</th>
+ <th style="width: 15%">{%= __("Balance") %}</th>
</tr>
</thead>
<tbody>
{% for(var i=0, l=data.length; i<l; i++) { %}
<tr>
- {% if(data[i][__("Posting Date")]) { %}
- <td>{%= dateutil.str_to_user(data[i][__("Posting Date")]) %}</td>
- <td>{%= data[i][__("Voucher Type")] %}
- <br>{%= data[i][__("Voucher No")] %}</td>
+ {% if(data[i].posting_date) { %}
+ <td>{%= dateutil.str_to_user(data[i].posting_date) %}</td>
+ <td>{%= data[i].voucher_type %}
+ <br>{%= data[i].voucher_no %}</td>
<td>
{% if(!(filters.party || filters.account)) { %}
- {%= data[i][__("Party")] || data[i][__("Account")] %}
+ {%= data[i].party || data[i].account %}
<br>
{% } %}
- {{ __("Against") }}: {%= data[i][__("Against Account")] %}
- <br>{%= __("Remarks") %}: {%= data[i][__("Remarks")] %}</td>
- <td style="text-align: right">{%= format_currency(data[i][__("Debit")]) %}</td>
- <td style="text-align: right">{%= format_currency(data[i][__("Credit")]) %}</td>
+ {{ __("Against") }}: {%= data[i].against %}
+ <br>{%= __("Remarks") %}: {%= data[i].remarks %}
+ {% if(data[i].bill_no) { %}
+ <br>{%= __("Supplier Invoice No") %}: {%= data[i].bill_no %}
+ {% } %}
+ </td>
+ {% if(filters.print_in_account_currency) { %}
+ <td style="text-align: right">
+ {%= format_currency(data[i].debit_in_account_currency, data[i].account_currency) %}
+ </td>
+ <td style="text-align: right">
+ {%= format_currency(data[i].credit_in_account_currency, data[i].account_currency) %}
+ </td>
+ {% } else { %}
+ <td style="text-align: right">
+ {%= format_currency(data[i].debit) %}</td>
+ <td style="text-align: right">
+ {%= format_currency(data[i].credit) %}</td>
+ {% } %}
{% } else { %}
<td></td>
<td></td>
- <td><b>{%= frappe.format(data[i][__("Account")], {fieldtype: "Link"}) || " " %}</b></td>
- <td style="text-align: right">
- {%= data[i][__("Account")] && format_currency(data[i][__("Debit")]) %}</td>
- <td style="text-align: right">
- {%= data[i][__("Account")] && format_currency(data[i][__("Credit")]) %}</td>
+ <td><b>{%= frappe.format(data[i].account, {fieldtype: "Link"}) || " " %}</b></td>
+ {% if(filters.print_in_account_currency) { %}
+ <td style="text-align: right">
+ {%= data[i].account && format_currency(data[i].debit_in_account_currency, data[i].account_currency) %}</td>
+ <td style="text-align: right">
+ {%= data[i].account && format_currency(data[i].credit_in_account_currency, data[i].account_currency) %}</td>
+ {% } else { %}
+ <td style="text-align: right">
+ {%= data[i].account && format_currency(data[i].debit) %}
+ </td>
+ <td style="text-align: right">
+ {%= data[i].account && format_currency(data[i].credit) %}
+ </td>
+ {% } %}
+ {% } %}
+ {% if(filters.print_in_account_currency) { %}
+ <td style="text-align: right">{%= data[i].balance_in_account_currency %}</td>
+ {% } else { %}
+ <td style="text-align: right">{%= data[i].balance %}</td>
{% } %}
</tr>
{% } %}
diff --git a/erpnext/accounts/report/general_ledger/general_ledger.js b/erpnext/accounts/report/general_ledger/general_ledger.js
index 282c767..adefadd 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.js
+++ b/erpnext/accounts/report/general_ledger/general_ledger.js
@@ -105,6 +105,11 @@
"fieldname":"group_by_account",
"label": __("Group by Account"),
"fieldtype": "Check",
+ },
+ {
+ "fieldname":"print_in_account_currency",
+ "label": __("Print in Account Currency"),
+ "fieldtype": "Check",
}
]
}
diff --git a/erpnext/accounts/report/general_ledger/general_ledger.py b/erpnext/accounts/report/general_ledger/general_ledger.py
index eefb3d1..72fe793 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.py
+++ b/erpnext/accounts/report/general_ledger/general_ledger.py
@@ -3,12 +3,17 @@
from __future__ import unicode_literals
import frappe
-from frappe.utils import getdate, cstr, flt
+from frappe.utils import getdate, cstr, flt, fmt_money
from frappe import _, _dict
from erpnext.accounts.utils import get_account_currency
def execute(filters=None):
account_details = {}
+
+ if filters and filters.get('print_in_account_currency') and \
+ not filters.get('account'):
+ frappe.throw(_("Select an account to print in account currency"))
+
for acc in frappe.db.sql("""select name, is_group from tabAccount""", as_dict=1):
account_details.setdefault(acc.name, acc)
@@ -76,28 +81,6 @@
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",
- _("Project") + ":Link/Project:100", _("Cost Center") + ":Link/Cost Center:100",
- _("Against Voucher Type") + "::120", _("Against Voucher") + ":Dynamic Link/"+_("Against Voucher Type")+":160",
- _("Remarks") + "::400"
- ]
-
- return columns
-
def get_result(filters, account_details):
gl_entries = get_gl_entries(filters)
@@ -193,24 +176,6 @@
# closing
data.append(totals.closing)
- #total closing
- total_closing = totals.total_closing
- total_debit = totals.closing.get('debit', 0)
- total_credit = totals.closing.get('credit', 0)
- debit_in_account_currency = totals.closing.get('debit_in_account_currency', 0)
- credit_in_account_currency = totals.closing.get('credit_in_account_currency', 0)
-
- total_amount = total_debit - total_credit
-
- if total_amount > 0:
- total_closing['debit'] = total_amount
- total_closing['debit_in_account_currency'] = debit_in_account_currency - credit_in_account_currency
- else:
- total_closing['credit'] = abs(total_amount)
- total_closing['credit_in_account_currency'] = abs(debit_in_account_currency - credit_in_account_currency)
-
- data.append(totals.total_closing)
-
return data
def get_totals_dict():
@@ -225,8 +190,7 @@
return _dict(
opening = _get_debit_credit_dict(_('Opening')),
total = _get_debit_credit_dict(_('Total')),
- closing = _get_debit_credit_dict(_('Closing (Opening + Total)')),
- total_closing = _get_debit_credit_dict(_('Closing Balance (Dr - Cr)'))
+ closing = _get_debit_credit_dict(_('Closing (Opening + Total)'))
)
def initialize_gle_map(gl_entries):
@@ -270,17 +234,164 @@
return totals, entries
def get_result_as_list(data, filters):
- result = []
+ balance, balance_in_account_currency = 0, 0
+ inv_details = get_supplier_invoice_details()
+
for d in data:
- row = [d.get("posting_date"), d.get("account"), d.get("debit"), d.get("credit")]
+ if not d.posting_date:
+ balance, balance_in_account_currency = 0, 0
+
+ balance, label = get_balance(d, balance, 'debit', 'credit')
+ d['balance'] = '{0} {1}'.format(fmt_money(abs(balance)), label)
if filters.get("show_in_account_currency"):
- row += [d.get("debit_in_account_currency"), d.get("credit_in_account_currency")]
+ balance_in_account_currency, label = get_balance(d, balance_in_account_currency,
+ 'debit_in_account_currency', 'credit_in_account_currency')
+ d['balance_in_account_currency'] = '{0} {1}'.format(fmt_money(abs(balance_in_account_currency)), label)
+ else:
+ d['debit_in_account_currency'] = d.get('debit', 0)
+ d['credit_in_account_currency'] = d.get('credit', 0)
+ d['balance_in_account_currency'] = d.get('balance')
- row += [d.get("voucher_type"), d.get("voucher_no"), d.get("against"),
- d.get("party_type"), d.get("party"), d.get("project"), d.get("cost_center"), d.get("against_voucher_type"), d.get("against_voucher"), d.get("remarks")
- ]
+ d['account_currency'] = filters.account_currency
+ d['bill_no'] = inv_details.get(d.against_voucher, '')
- result.append(row)
+ return data
- return result
+def get_supplier_invoice_details():
+ inv_details = {}
+ for d in frappe.db.sql(""" select name, bill_no from `tabPurchase Invoice`
+ where docstatus = 1 and bill_no is not null and bill_no != '' """, as_dict=1):
+ inv_details[d.name] = d.bill_no
+
+ return inv_details
+
+def get_balance(row, balance, debit_field, credit_field):
+ balance += (row.get(debit_field, 0) - row.get(credit_field, 0))
+ label = 'DR' if balance > 0 else 'CR'
+
+ return balance, label
+
+def get_columns(filters):
+ columns = [
+ {
+ "label": _("Posting Date"),
+ "fieldname": "posting_date",
+ "fieldtype": "Date",
+ "width": 90
+ },
+ {
+ "label": _("Account"),
+ "fieldname": "account",
+ "fieldtype": "Link",
+ "options": "Account",
+ "width": 180
+ },
+ {
+ "label": _("Debit"),
+ "fieldname": "debit",
+ "fieldtype": "Float",
+ "width": 100
+ },
+ {
+ "label": _("Credit"),
+ "fieldname": "credit",
+ "fieldtype": "Float",
+ "width": 100
+ },
+ {
+ "label": _("Balance"),
+ "fieldname": "balance",
+ "fieldtype": "Data",
+ "width": 100
+ }
+ ]
+
+ if filters.get("show_in_account_currency"):
+ columns.extend([
+ {
+ "label": _("Debit") + " (" + filters.account_currency + ")",
+ "fieldname": "debit_in_account_currency",
+ "fieldtype": "Float",
+ "width": 100
+ },
+ {
+ "label": _("Credit") + " (" + filters.account_currency + ")",
+ "fieldname": "credit_in_account_currency",
+ "fieldtype": "Float",
+ "width": 100
+ },
+ {
+ "label": _("Balance") + " (" + filters.account_currency + ")",
+ "fieldname": "balance_in_account_currency",
+ "fieldtype": "Data",
+ "width": 100
+ }
+ ])
+
+ columns.extend([
+ {
+ "label": _("Voucher Type"),
+ "fieldname": "voucher_type",
+ "width": 120
+ },
+ {
+ "label": _("Voucher No"),
+ "fieldname": "voucher_no",
+ "fieldtype": "Dynamic Link",
+ "options": "voucher_type",
+ "width": 180
+ },
+ {
+ "label": _("Against Account"),
+ "fieldname": "against",
+ "width": 120
+ },
+ {
+ "label": _("Party Type"),
+ "fieldname": "party_type",
+ "width": 100
+ },
+ {
+ "label": _("Party"),
+ "fieldname": "party",
+ "width": 100
+ },
+ {
+ "label": _("Project"),
+ "options": "Project",
+ "fieldname": "project",
+ "width": 100
+ },
+ {
+ "label": _("Cost Center"),
+ "options": "Cost Center",
+ "fieldname": "cost_center",
+ "width": 100
+ },
+ {
+ "label": _("Against Voucher Type"),
+ "fieldname": "against_voucher_type",
+ "width": 100
+ },
+ {
+ "label": _("Against Voucher"),
+ "fieldname": "against_voucher",
+ "fieldtype": "Dynamic Link",
+ "options": "against_voucher_type",
+ "width": 100
+ },
+ {
+ "label": _("Supplier Invoice No"),
+ "fieldname": "bill_no",
+ "fieldtype": "Data",
+ "width": 100
+ },
+ {
+ "label": _("Remarks"),
+ "fieldname": "remarks",
+ "width": 400
+ }
+ ])
+
+ return columns