Hello Community, I am trying to create a script report to fetch a customer’s ledger transaction balances and I wrote these report scripts:
def fetch_customer():
logged_in_user = frappe.session.user
member = frappe.db.get_value("Member", {"email_id": logged_in_user}, "name")
linked_customer = frappe.db.get_value("Member", {"name": member}, "customer")
return linked_customer
def execute(filters=None):
columns, data = [], []
columns = [
_("Posting Date") + ":Date:100",
_("Account") + ":Link/Account:350",
_("Debit") + ":Currency:150",
_("Credit") + ":Currency:150",
_("Balance") + ":Currency:150"
]
linked_customer = fetch_customer()
sd_account = frappe.db.get_value("Custom Settings", None, "special_deposit_account")
where_conditions = ["party_type = 'Customer'", "is_cancelled = 0"]
if linked_customer:
where_conditions.append(f"party = '{linked_customer}'")
if sd_account:
where_conditions.append(f"account = '{sd_account}'")
where_conditions_str = " AND ".join(where_conditions)
data = frappe.db.sql(f"""
SELECT posting_date, account, debit, credit
FROM `tabGL Entry`
WHERE {where_conditions_str}
ORDER BY posting_date
""", as_dict=1)
data_with_balance = get_result_as_list(data)
return columns, data_with_balance
def get_balance(row, balance, debit_field, credit_field):
balance += row.get(debit_field, 0) - row.get(credit_field, 0)
return balance
def get_result_as_list(data):
running_balance = 0
for d in data:
running_balance = d.get("debit", 0) - d.get("credit", 0)
d["balance"] = running_balance
return data
Everything looks good at first glance but the row balances do not follow the convention of showing the balance based on the previous row balance and current row credit and debit. each row balance is isolated. But the balance in the total row is correct. If I modify this running_balance = d.get("debit", 0) - d.get("credit", 0)
and make it running_balance += d.get("debit", 0) - d.get("credit", 0)
then the row balances will be correct but the total balance becomes a sum of the row balance which is wrong. I can’t seem to figure out what I might be doing wrong so any help will be appreciated.