I made a custom report, How do i prevent The Amount in Account Currency from Totalling per parent
it looks like this
The Code
# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
# License: GNU General Public License v3. See license.txt
from __future__ import unicode_literals
import frappe
from frappe import _
from frappe.utils import cint, flt, getdate, formatdate, cstr
value_fields = ("amount", "amount_in_account_currency")
def sort_root_accounts(roots):
"""Sort root types as Asset, Liability, Equity, Income, Expense"""
def compare_roots(a, b):
if a.report_type != b.report_type and a.report_type == "Balance Sheet":
return -1
if a.root_type != b.root_type and a.root_type == "Asset":
return -1
if a.root_type == "Liability" and b.root_type == "Equity":
return -1
if a.root_type == "Income" and b.root_type == "Expense":
return -1
return 1
roots.sort(compare_roots)
def filter_accounts(accounts, depth=10):
parent_children_map = {}
accounts_by_name = {}
for d in accounts:
accounts_by_name[d.name] = d
parent_children_map.setdefault(d.parent_account or None, []).append(d)
filtered_accounts = []
def add_to_list(parent, level):
if level < depth:
children = parent_children_map.get(parent) or []
if parent == None:
sort_root_accounts(children)
for child in children:
child.indent = level
filtered_accounts.append(child)
add_to_list(child.name, level + 1)
add_to_list(None, 0)
return filtered_accounts, accounts_by_name
def set_gl_entries_by_account(company, from_date, to_date, root_lft, root_rgt, gl_entries_by_account,
ignore_closing_entries=False):
"""Returns a dict like { "account": [gl entries], ... }"""
additional_conditions = []
if ignore_closing_entries:
additional_conditions.append("and ifnull(voucher_type, '')!='Period Closing Voucher'")
gl_entries = frappe.db.sql("""select
posting_date,
account,
sum(debit) as debit,
sum(credit) as credit,
sum(debit_in_account_currency) as debit_in_account_currency,
sum(credit_in_account_currency) as credit_in_account_currency,
(SELECT root_type FROM tabAccount WHERE name = account) as root_type,
account_currency,
is_opening
from `tabGL Entry`
where company=%(company)s
{additional_conditions}
and posting_date <= %(to_date)s
and account in (select name from `tabAccount`
where lft >= %(lft)s and rgt <= %(rgt)s)
group by account
order by account, posting_date""".format(additional_conditions="\n".join(additional_conditions)),
{
"company": company,
"from_date": from_date,
"to_date": to_date,
"lft": root_lft,
"rgt": root_rgt
},
as_dict=True)
for entry in gl_entries:
gl_entries_by_account.setdefault(entry.account, []).append(entry)
return gl_entries_by_account
def execute(filters=None):
validate_filters(filters)
data = get_data(filters)
columns = get_columns()
return columns, data
def validate_filters(filters):
filters.from_date = getdate(filters.from_date)
filters.to_date = getdate(filters.to_date)
if filters.from_date > filters.to_date:
frappe.throw(_("From Date cannot be greater than To Date"))
def get_data(filters):
accounts = frappe.db.sql("""
select name,
parent_account,
account_name,
root_type,
report_type,
account_currency,
lft,
rgt
from `tabAccount`
where company=%s
and root_type != 'Income'
and root_type != 'Expense'
order by account_code""", filters.company, as_dict=True)
if not accounts:
return None
accounts, accounts_by_name = filter_accounts(accounts)
min_lft, max_rgt = frappe.db.sql("""select min(lft), max(rgt) from `tabAccount`
where company=%s""", (filters.company,))[0]
gl_entries_by_account = {}
set_gl_entries_by_account(filters.company, filters.from_date,
filters.to_date, min_lft, max_rgt, gl_entries_by_account, ignore_closing_entries=not flt(filters.with_period_closing_entry))
total_row = calculate_values(accounts, gl_entries_by_account, filters)
accumulate_values_into_parents(accounts, accounts_by_name)
data = prepare_data(accounts, filters, total_row)
return data
def calculate_values(accounts, gl_entries_by_account, filters):
init = {
"amount": 0.0,
"amount_in_account_currency": 0.0,
}
total_row = {
"account": None,
"account_name": _("Total"),
"warn_if_negative": True,
"amount": 0.0,
"amount_in_account_currency":""
}
sub_liminal = 0
sub_mow = 0
for d in accounts:
d.update(init.copy())
for entry in gl_entries_by_account.get(d.name, []):
if entry.root_type == 'Asset':
d["amount"] += flt(entry.debit) - flt(entry.credit)
d["amount_in_account_currency"] += flt(entry.debit_in_account_currency) - flt(entry.credit_in_account_currency)
sub_liminal += flt(entry.debit) - flt(entry.credit)
else:
d["amount"] += flt(entry.credit) - flt(entry.debit)
d["amount_in_account_currency"] += flt(entry.credit_in_account_currency) - flt(entry.debit_in_account_currency)
sub_mow += flt(entry.credit) - flt(entry.debit)
total_row["amount"] += sub_liminal - sub_mow
return total_row
def accumulate_values_into_parents(accounts, accounts_by_name):
for d in reversed(accounts):
if d.parent_account:
for key in value_fields:
accounts_by_name[d.parent_account][key] += d[key]
def prepare_data(accounts, filters, total_row):
company_name = get_company(filters.company)
report_name = get_reportname()
blank = get_blank()
year_name = get_year(filters.from_date, filters.to_date)
show_zero_values = cint(filters.show_zero_values)
data = []
data.append(report_name)
data.append(company_name)
data.append(year_name)
data.append(blank)
accounts_with_zero_value = []
for d in accounts:
has_value = False
row = {
"account_name": d.account_name,
"account": d.name,
"parent_account": d.parent_account,
"indent": d.indent,
"from_date": filters.from_date,
"to_date": filters.to_date,
"account_currency": d.account_currency
}
for key in value_fields:
row[key] = d.get(key, 0.0)
if row[key]:
has_value = True
if show_zero_values:
data.append(row)
else:
if not has_value:
accounts_with_zero_value.append(d.name)
elif d.parent_account not in accounts_with_zero_value:
data.append(row)
data.extend([{},total_row])
return data
def get_company(company):
company_name = {
"account_name": company,
"account": company,
}
return company_name
def get_reportname():
report_name = {
"account_name": "Revaluation",
"account": "Revaluation",
}
return report_name
def get_year(from_date, to_date):
new_from = from_date.strftime("%Y-%m-%d")
new_to = to_date.strftime("%Y-%m-%d")
year_name = {
"account_name": "From " + new_from + " To " + new_to,
"account": "From " + new_from + " To " + new_to,
}
return year_name
def get_blank():
blank = {
"account_name": None,
"account": None,
}
return blank
def get_columns():
return [
{
"fieldname": "account",
"label": _("Account"),
"fieldtype": "Link",
"options": "Account",
"width": 300
},
{
"fieldname": "account_currency",
"label": _("Account Currency"),
"width": 120
},
{
"fieldname": "amount",
"label": _("Amount"),
"fieldtype": "Float",
"width": 120
},
{
"fieldname": "amount_in_account_currency",
"label": _("Amount in Account Currency"),
"fieldtype": "Float",
"width": 120
},
]
