ERPNext Foundation ERPNext Cloud User Manual Blog Discuss Frappé* Donate

Consolidated FS by Currency


#1

Im trying to Make a simple consolidation FS
What i did was

  1. Modified the financial_statements.py and and profit and loss report
  2. Change Period list to Companies Instead of Dates
  3. Added Currency Filter

it looks like this.
TEST2 and TEST 3 are companies

Im currently stuck with these problems:

  1. That amounts are showing on all companies
  2. Accounts are not merging . tried to remove “- TST3” from account name when querying but no accounts are showing.

already tried this:

Code for FS

Copyright © 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 _, _dict
from frappe.utils import (flt, getdate, get_first_day, get_last_day,
add_months, add_days, formatdate)

def get_period_list(filters):
period_list = []
companies = frappe.db.sql(""“select name FROM tabCompany WHERE default_currency = %(currency)s “””.format(), filters, as_dict=1)

for i in companies:
period_list.append(_dict({ “companies”: i.name }))

for opts in period_list:
key = opts[“companies”]
label = opts[“companies”]
company = opts[“companies”]
opts.update({
“key”: key,
“label”: label,
“company”: company,
“from_date”: filters.from_date,
“to_date”: filters.to_date,
})

return period_list

def get_data(currency, root_type, balance_must_be, period_list, ignore_closing_entries=False):
accounts = get_accounts(currency, root_type)
if not accounts:
return None

accounts, accounts_by_name = filter_accounts(accounts)

gl_entries_by_account = {}
for root in frappe.db.sql(""“select lft, rgt from tabAccount
where root_type=%s and ifnull(parent_account, ‘’) = ‘’”"", root_type, as_dict=1):
set_gl_entries_by_account(currency, period_list[0][“from_date”], period_list[0][“to_date”], root.lft, root.rgt, gl_entries_by_account,ignore_closing_entries=ignore_closing_entries)

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)

if out:
add_total_row(out, balance_must_be, period_list)

return out

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:
d[period.key] = flt(entry.debit) - flt(entry.credit)

def accumulate_values_into_parents(accounts, accounts_by_name, period_list):
“”“accumulate children’s values in parent accounts”""
for d in reversed(accounts):
if d.parent_account:
for period in period_list:
accounts_by_name[d.parent_account][period.key] = accounts_by_name[d.parent_account].get(period.key, 0.0) + d.get(period.key, 0.0)

def prepare_data(accounts, balance_must_be, period_list):
out = []
year_start_date = period_list[0][“from_date”]
year_end_date = period_list[0][“to_date”]
for d in accounts:
# add to output
has_value = False
row = {
“account_name”: d.account_name,
“account”: d.name,
“parent_account”: d.parent_account,
“indent”: flt(d.indent),
“from_date”: year_start_date,
“to_date”: year_end_date
}
for period in period_list:
if d.get(period.key):
# change sign based on Debit or Credit, since calculation is done using (debit - credit)
d[period.key] *= (1 if balance_must_be==“Debit” else -1)

  	row[period.key] = flt(d.get(period.key, 0.0), 3)
  	if abs(row[period.key]) >= 0.005:
  		# ignore zero values
  		has_value = True
  if has_value:
  	out.append(row)

return out

def add_total_row(out, balance_must_be, period_list):
total_row = {
“account_name”: “’” + _(“Total ({0})”).format(balance_must_be) + “’”,
“account”: None
}

for row in out:
if not row.get(“parent_account”):
for period in period_list:
total_row.setdefault(period.key, 0.0)
total_row[period.key] += row.get(period.key, 0.0)

  	row[period.key] = ""

out.append(total_row)

blank row after Total

out.append({})

def get_accounts(currency, root_type):
return frappe.db.sql(""“select name, parent_account, lft, rgt, root_type, report_type, account_name from tabAccount
where account_currency=%s and root_type=%s order by lft”"", (currency, root_type), as_dict=True)

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 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 set_gl_entries_by_account(currency, 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’”)

if from_date:
additional_conditions.append(“and posting_date >= %(from_date)s”)

gl_entries = frappe.db.sql(""“select posting_date, account, debit, credit, is_opening from tabGL Entry
WHERE account_currency = %(currency)s
{additional_conditions}
and posting_date <= %(to_date)s
and account in (select name from tabAccount
where lft >= %(lft)s and rgt <= %(rgt)s)
order by account, posting_date”"".format(additional_conditions="\n".join(additional_conditions)),
{
“currency”: currency,
“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 get_columns(period_list):
columns = [{
“fieldname”: “account”,
“label”: _(“Account”),
“fieldtype”: “Link”,
“options”: “Account”,
“width”: 300
}]
for period in period_list:
columns.append({
“fieldname”: period.key,
“label”: period.label,
“fieldtype”: “Currency”,
“width”: 150
})

return columns


#2

Send a pull request!

@Fred1 @Tropicalrambler also interested and we can collaborate on pull request.


#3

finally got some time to work on this again
managed to finish the Consolidated FS but for v5
will try to convert the code to v7 compatible.


#4

HI there,
Any update on this?
I am very interested for this feature


#5

Is there any update on those reports?its very interesting feature.