[report][performance] Speed increased by approx 10 times in Trial Balance, General Ledger, AR/AP, Balance Sheet and P&L Statement
diff --git a/erpnext/accounts/doctype/journal_entry/journal_entry.py b/erpnext/accounts/doctype/journal_entry/journal_entry.py
index 831fcd7..753fcc3 100644
--- a/erpnext/accounts/doctype/journal_entry/journal_entry.py
+++ b/erpnext/accounts/doctype/journal_entry/journal_entry.py
@@ -544,7 +544,7 @@
and (ifnull(jv_detail.against_invoice, '') = '' and ifnull(jv_detail.against_voucher, '') = ''
and ifnull(jv_detail.against_jv, '') = '' )
and jv.docstatus = 1 and jv.{0} like %s order by jv.name desc limit %s, %s""".format(searchfield),
- (filters["account"], cstr(filters["party"]), "%{0}%".format(txt), start, page_len))
+ (filters.get("account"), cstr(filters.get("party")), "%{0}%".format(txt), start, page_len))
@frappe.whitelist()
def get_outstanding(args):
diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
index 52c9760..bdfdf0e 100644
--- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
+++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
@@ -51,7 +51,7 @@
currency_precision = get_currency_precision() or 2
dr_or_cr = "debit" if args.get("party_type") == "Customer" else "credit"
- voucher_details = self.get_voucher_details()
+ voucher_details = self.get_voucher_details(args.get("party_type"))
future_vouchers = self.get_entries_after(self.filters.report_date, args.get("party_type"))
@@ -153,23 +153,26 @@
return self.party_map
- def get_voucher_details(self):
+ def get_voucher_details(self, party_type):
voucher_details = frappe._dict()
+
+ if party_type == "Customer":
+ for si in frappe.db.sql("""select name, due_date
+ from `tabSales Invoice` where docstatus=1""", as_dict=1):
+ voucher_details.setdefault(si.name, si)
- for si in frappe.db.sql("""select name, due_date
- from `tabSales Invoice` where docstatus=1""", as_dict=1):
- voucher_details.setdefault(si.name, si)
-
- for pi in frappe.db.sql("""select name, due_date, bill_no, bill_date
- from `tabPurchase Invoice` where docstatus=1""", as_dict=1):
- voucher_details.setdefault(pi.name, pi)
+ if party_type == "Supplier":
+ for pi in frappe.db.sql("""select name, due_date, bill_no, bill_date
+ from `tabPurchase Invoice` where docstatus=1""", as_dict=1):
+ voucher_details.setdefault(pi.name, pi)
return voucher_details
def get_gl_entries(self, party_type):
if not hasattr(self, "gl_entries"):
conditions, values = self.prepare_conditions(party_type)
- self.gl_entries = frappe.db.sql("""select * from `tabGL Entry`
+ self.gl_entries = frappe.db.sql("""select posting_date, account, party_type, party, debit, credit,
+ voucher_type, voucher_no, against_voucher_type, against_voucher from `tabGL Entry`
where docstatus < 2 and party_type=%s {0} order by posting_date, party"""
.format(conditions), values, as_dict=True)
@@ -187,7 +190,7 @@
if self.filters.get(party_type_field):
conditions.append("party=%s")
- values.append(self.filters.get(party_type_field))
+ values.append(self.filters.get(party_type_field))
return " and ".join(conditions), values
diff --git a/erpnext/accounts/report/financial_statements.py b/erpnext/accounts/report/financial_statements.py
index c763778..b4fec98 100644
--- a/erpnext/accounts/report/financial_statements.py
+++ b/erpnext/accounts/report/financial_statements.py
@@ -83,7 +83,7 @@
gl_entries_by_account = get_gl_entries(company, period_list[0]["from_date"], period_list[-1]["to_date"],
accounts[0].lft, accounts[0].rgt, ignore_closing_entries=ignore_closing_entries)
- calculate_values(accounts, gl_entries_by_account, period_list)
+ calculate_values(accounts_by_name, gl_entries_by_account, period_list)
accumulate_values_into_parents(accounts, accounts_by_name, period_list)
out = prepare_data(accounts, balance_must_be, period_list)
@@ -92,16 +92,14 @@
return out
-def calculate_values(accounts, gl_entries_by_account, period_list):
- for d in accounts:
- for name in ([d.name] + (d.collapsed_children or [])):
- for entry in gl_entries_by_account.get(name, []):
- for period in period_list:
- entry.posting_date = getdate(entry.posting_date)
-
- # check if posting date is within the period
- if entry.posting_date <= period.to_date:
- d[period.key] = d.get(period.key, 0.0) + flt(entry.debit) - flt(entry.credit)
+def calculate_values(accounts_by_name, gl_entries_by_account, period_list):
+ for entries in gl_entries_by_account.values():
+ for entry in entries:
+ d = accounts_by_name.get(entry.account)
+ for period in period_list:
+ # check if posting date is within the period
+ if entry.posting_date <= period.to_date:
+ d[period.key] = d.get(period.key, 0.0) + flt(entry.debit) - flt(entry.credit)
def accumulate_values_into_parents(accounts, accounts_by_name, period_list):
@@ -159,22 +157,8 @@
out.append({})
def get_accounts(company, root_type):
- # root lft, rgt
- root_account = frappe.db.sql("""select lft, rgt from `tabAccount`
- where company=%s and root_type=%s and ifnull(parent_account, '') = ''
- order by lft limit 1""",
- (company, root_type), as_dict=True)
-
- if not root_account:
- return None
-
- lft, rgt = root_account[0].lft, root_account[0].rgt
-
- accounts = frappe.db.sql("""select * from `tabAccount`
- where company=%(company)s and lft >= %(lft)s and rgt <= %(rgt)s order by lft""",
- { "company": company, "lft": lft, "rgt": rgt }, as_dict=True)
-
- return accounts
+ return frappe.db.sql("""select name, parent_account, lft, rgt, root_type, report_type, account_name from `tabAccount`
+ where company=%s and root_type=%s order by lft""", (company, root_type), as_dict=True)
def filter_accounts(accounts, depth=10):
parent_children_map = {}
@@ -196,14 +180,6 @@
filtered_accounts.append(child)
add_to_list(child.name, level + 1)
- else:
- # include all children at level lower than the depth
- parent_account = accounts_by_name[parent]
- parent_account["collapsed_children"] = []
- for d in accounts:
- if d.lft > parent_account.lft and d.rgt < parent_account.rgt:
- parent_account["collapsed_children"].append(d.name)
-
add_to_list(None, 0)
return filtered_accounts, accounts_by_name
@@ -234,7 +210,7 @@
if from_date:
additional_conditions.append("and posting_date >= %(from_date)s")
- gl_entries = frappe.db.sql("""select * from `tabGL Entry`
+ gl_entries = frappe.db.sql("""select posting_date, account, debit, credit from `tabGL Entry`
where company=%(company)s
{additional_conditions}
and posting_date <= %(to_date)s
diff --git a/erpnext/accounts/report/general_ledger/general_ledger.py b/erpnext/accounts/report/general_ledger/general_ledger.py
index fcce345..b268156 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.py
+++ b/erpnext/accounts/report/general_ledger/general_ledger.py
@@ -66,7 +66,7 @@
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, is_opening, against
+ voucher_type, voucher_no, cost_center, remarks, against
from `tabGL Entry`
where company=%(company)s {conditions}
{group_by_condition}
@@ -91,6 +91,9 @@
if filters.get("party"):
conditions.append("party=%(party)s")
+
+ if not (filters.get("account") or filters.get("party") or filters.get("group_by_account")):
+ conditions.append("posting_date >=%(from_date)s")
from frappe.desk.reportview import build_match_conditions
match_conditions = build_match_conditions("GL Entry")
@@ -148,14 +151,15 @@
def get_accountwise_gle(filters, gl_entries, gle_map):
opening, total_debit, total_credit = 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)
- if gle.posting_date < getdate(filters.from_date):
+ if (filters.get("account") or filters.get("party") or filters.get("group_by_account")) \
+ and gle.posting_date < from_date:
gle_map[gle.account].opening += amount
if filters.get("account") or filters.get("party"):
opening += amount
- elif gle.posting_date <= getdate(filters.to_date):
+ 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)
diff --git a/erpnext/accounts/report/trial_balance/trial_balance.py b/erpnext/accounts/report/trial_balance/trial_balance.py
index 761d038..8187601 100644
--- a/erpnext/accounts/report/trial_balance/trial_balance.py
+++ b/erpnext/accounts/report/trial_balance/trial_balance.py
@@ -9,7 +9,7 @@
value_fields = ("opening_debit", "opening_credit", "debit", "credit", "closing_debit", "closing_credit")
-def execute(filters):
+def execute(filters=None):
validate_filters(filters)
data = get_data(filters)
columns = get_columns()
@@ -45,8 +45,8 @@
filters.to_date = filters.year_end_date
def get_data(filters):
- accounts = frappe.db.sql("""select * from `tabAccount` where company=%s order by lft""",
- filters.company, as_dict=True)
+ accounts = frappe.db.sql("""select name, parent_account, account_name, root_type, report_type, lft, rgt
+ from `tabAccount` where company=%s order by lft""", filters.company, as_dict=True)
if not accounts:
return None
@@ -56,17 +56,58 @@
min_lft, max_rgt = frappe.db.sql("""select min(lft), max(rgt) from `tabAccount`
where company=%s""", (filters.company,))[0]
- gl_entries_by_account = get_gl_entries(filters.company, None, filters.to_date, min_lft, max_rgt,
+ gl_entries_by_account = get_gl_entries(filters.company, filters.from_date, filters.to_date, min_lft, max_rgt,
ignore_closing_entries=not flt(filters.with_period_closing_entry))
- total_row = calculate_values(accounts, gl_entries_by_account, filters)
+ opening_balances = get_opening_balances(filters)
+
+ total_row = calculate_values(accounts, gl_entries_by_account, opening_balances, filters)
accumulate_values_into_parents(accounts, accounts_by_name)
data = prepare_data(accounts, filters, total_row)
return data
+
+def get_opening_balances(filters):
+ balance_sheet_opening = get_rootwise_opening_balances(filters, "Balance Sheet")
+ pl_opening = get_rootwise_opening_balances(filters, "Profit and Loss")
+
+ balance_sheet_opening.update(pl_opening)
+ return balance_sheet_opening
+
+
+def get_rootwise_opening_balances(filters, report_type):
+ additional_conditions = " and posting_date >= %(year_start_date)s" \
+ if report_type == "Profit and Loss" else ""
+
+ if not flt(filters.with_period_closing_entry):
+ additional_conditions += " and ifnull(voucher_type, '')!='Period Closing Voucher'"
+
+ gle = frappe.db.sql("""
+ select
+ account, sum(ifnull(debit, 0)) as opening_debit, sum(ifnull(credit, 0)) as opening_credit
+ from `tabGL Entry`
+ where
+ company=%(company)s
+ {additional_conditions}
+ and posting_date < %(from_date)s
+ and account in (select name from `tabAccount` where report_type=%(report_type)s)
+ group by account""".format(additional_conditions=additional_conditions),
+ {
+ "company": filters.company,
+ "from_date": filters.from_date,
+ "report_type": report_type,
+ "year_start_date": filters.year_start_date
+ },
+ as_dict=True)
+
+ opening = frappe._dict()
+ for d in gle:
+ opening.setdefault(d.account, d)
+
+ return opening
-def calculate_values(accounts, gl_entries_by_account, filters):
+def calculate_values(accounts, gl_entries_by_account, opening_balances, filters):
init = {
"opening_debit": 0.0,
"opening_credit": 0.0,
@@ -88,29 +129,15 @@
d.update(init.copy())
for entry in gl_entries_by_account.get(d.name, []):
- posting_date = getdate(entry.posting_date)
-
- # opening
- if posting_date < filters.from_date:
- is_valid_opening = (d.root_type in ("Asset", "Liability", "Equity") or
- (filters.year_start_date <= posting_date < filters.from_date))
-
- if is_valid_opening:
- d["opening_debit"] += flt(entry.debit)
- d["opening_credit"] += flt(entry.credit)
-
- elif posting_date <= filters.to_date:
-
- if entry.is_opening == "Yes" and d.root_type in ("Asset", "Liability", "Equity"):
- d["opening_debit"] += flt(entry.debit)
- d["opening_credit"] += flt(entry.credit)
-
- else:
- d["debit"] += flt(entry.debit)
- d["credit"] += flt(entry.credit)
+ d["debit"] += flt(entry.debit)
+ d["credit"] += flt(entry.credit)
total_row["debit"] += d["debit"]
total_row["credit"] += d["credit"]
+
+ # add opening
+ d["opening_debit"] = opening_balances.get(d.name, {}).get("opening_debit", 0)
+ d["opening_credit"] = opening_balances.get(d.name, {}).get("opening_credit", 0)
return total_row
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index b5056d7..298ff8e 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -91,7 +91,7 @@
# different filter for group and ledger - improved performance
if acc.is_group:
cond.append("""exists (
- select * from `tabAccount` ac where ac.name = gle.account
+ select name from `tabAccount` ac where ac.name = gle.account
and ac.lft >= %s and ac.rgt <= %s
)""" % (acc.lft, acc.rgt))
else:
@@ -397,7 +397,7 @@
for d in outstanding_voucher_list:
payment_amount = frappe.db.sql("""
- select ifnull(sum(ifnull({amount_query}, 0)), 0)
+ select ifnull(sum({amount_query}), 0)
from
`tabGL Entry`
where