fix(dashboard): simpler way to get account timeline from first principles
diff --git a/erpnext/accounts/dashboard_chart_source/account_balance_timeline/account_balance_timeline.py b/erpnext/accounts/dashboard_chart_source/account_balance_timeline/account_balance_timeline.py
index f98a236..101e308 100644
--- a/erpnext/accounts/dashboard_chart_source/account_balance_timeline/account_balance_timeline.py
+++ b/erpnext/accounts/dashboard_chart_source/account_balance_timeline/account_balance_timeline.py
@@ -9,6 +9,8 @@
from frappe.utils import add_to_date, date_diff, getdate, nowdate
from erpnext.accounts.report.general_ledger.general_ledger import execute
+from frappe.utils.nestedset import get_descendants_of
+
@frappe.whitelist()
@cache_source
def get(filters=None):
@@ -19,35 +21,56 @@
from_date = get_from_date_from_timespan(timespan)
to_date = nowdate()
- filters = frappe._dict({
- "company": company,
- "from_date": from_date,
- "to_date": to_date,
- "account": account,
- "group_by": "Group by Voucher (Consolidated)"
- })
- report_results = execute(filters=filters)[1]
- interesting_fields = ["posting_date", "balance"]
+ # fetch dates to plot
+ dates = get_dates_from_timegrain(from_date, to_date, timegrain)
- _results = []
- for row in report_results[1:-2]:
- _results.append([row[key] for key in interesting_fields])
+ # get all the entries for this account and its descendants
+ gl_entries = get_gl_entries(account, to_date)
- _results = add_opening_balance(from_date, _results, report_results[0])
- grouped_results = groupby(_results, key=itemgetter(0))
- results = [list(values)[-1] for key, values in grouped_results]
- results = add_missing_dates(results, from_date, to_date)
- results = granulate_results(results, from_date, to_date, timegrain)
+ # compile balance values
+ result = build_result(account, dates, gl_entries)
return {
- "labels": [result[0] for result in results],
+ "labels": [r[0].strftime('%Y-%m-%d') for r in result],
"datasets": [{
"name": account,
- "values": [result[1] for result in results]
+ "values": [r[1] for r in result]
}]
}
+def build_result(account, dates, gl_entries):
+ result = [[getdate(date), 0.0] for date in dates]
+
+ # start with the first date
+ date_index = 0
+
+ # get balances in debit
+ for entry in gl_entries:
+
+ # find the date index after the posting date
+ while getdate(entry.posting_date) > result[date_index][0]:
+ date_index += 1
+
+ result[date_index][1] += entry.debit - entry.credit
+
+ # if account type is credit, switch balances
+ if frappe.db.get_value('Account', account, 'root_type') not in ('Asset', 'Expense'):
+ for r in result:
+ r[1] = -1 * r[1]
+
+ return result
+
+def get_gl_entries(account, to_date):
+ child_accounts = get_descendants_of('Account', account, ignore_permissions=True)
+
+ return frappe.db.get_all('GL Entry',
+ fields = ['posting_date', 'debit', 'credit'],
+ filters = [
+ dict(posting_date = ('<', to_date)),
+ dict(account = ('in', child_accounts))
+ ])
+
def get_from_date_from_timespan(timespan):
days = months = years = 0
if "Last Week" == timespan:
@@ -61,24 +84,6 @@
return add_to_date(None, years=years, months=months, days=days,
as_string=True, as_datetime=True)
-
-def add_opening_balance(from_date, _results, opening):
- if not _results or (_results[0][0] != getdate(from_date)):
- _results.insert(0, [from_date, opening.balance])
- return _results
-
-def add_missing_dates(incomplete_results, from_date, to_date):
- day_count = date_diff(to_date, from_date)
-
- results_dict = dict(incomplete_results)
- last_balance = incomplete_results[0][1]
- results = []
- for date in (add_to_date(getdate(from_date), days=n) for n in range(day_count + 1)):
- if date in results_dict:
- last_balance = results_dict[date]
- results.append([date, last_balance])
- return results
-
def get_dates_from_timegrain(from_date, to_date, timegrain):
days = months = years = 0
if "Daily" == timegrain:
@@ -94,7 +99,3 @@
while dates[-1] <= to_date:
dates.append(add_to_date(dates[-1], years=years, months=months, days=days))
return dates
-
-def granulate_results(incomplete_results, from_date, to_date, timegrain):
- dates = set(get_dates_from_timegrain(getdate(from_date), getdate(to_date), timegrain))
- return list(filter(lambda x: x[0] in dates,incomplete_results))