Consolidated financial statement (#13678)
* added fields to support treeview
* tree file added, nestedset implemented
* patch added to reload doc and configure lft rgt
* Added consolidated financial statement report
diff --git a/erpnext/accounts/report/balance_sheet/balance_sheet.py b/erpnext/accounts/report/balance_sheet/balance_sheet.py
index 97990fa..c71ecf4 100644
--- a/erpnext/accounts/report/balance_sheet/balance_sheet.py
+++ b/erpnext/accounts/report/balance_sheet/balance_sheet.py
@@ -58,7 +58,7 @@
return columns, data, message, chart
-def get_provisional_profit_loss(asset, liability, equity, period_list, company):
+def get_provisional_profit_loss(asset, liability, equity, period_list, company, consolidated=False):
provisional_profit_loss = {}
total_row = {}
if asset and (liability or equity):
@@ -73,22 +73,23 @@
has_value = False
for period in period_list:
+ key = period if consolidated else period.key
effective_liability = 0.0
if liability:
- effective_liability += flt(liability[-2].get(period.key))
+ effective_liability += flt(liability[-2].get(key))
if equity:
- effective_liability += flt(equity[-2].get(period.key))
+ effective_liability += flt(equity[-2].get(key))
- provisional_profit_loss[period.key] = flt(asset[-2].get(period.key)) - effective_liability
- total_row[period.key] = effective_liability + provisional_profit_loss[period.key]
+ provisional_profit_loss[key] = flt(asset[-2].get(key)) - effective_liability
+ total_row[key] = effective_liability + provisional_profit_loss[key]
- if provisional_profit_loss[period.key]:
+ if provisional_profit_loss[key]:
has_value = True
- total += flt(provisional_profit_loss[period.key])
+ total += flt(provisional_profit_loss[key])
provisional_profit_loss["total"] = total
- total_row_total += flt(total_row[period.key])
+ total_row_total += flt(total_row[key])
total_row["total"] = total_row_total
if has_value:
diff --git a/erpnext/accounts/report/cash_flow/cash_flow.py b/erpnext/accounts/report/cash_flow/cash_flow.py
index c81db38..56de941 100644
--- a/erpnext/accounts/report/cash_flow/cash_flow.py
+++ b/erpnext/accounts/report/cash_flow/cash_flow.py
@@ -18,6 +18,60 @@
period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year,
filters.periodicity, filters.accumulated_values, filters.company)
+ cash_flow_accounts = get_cash_flow_accounts()
+
+ # compute net profit / loss
+ income = get_data(filters.company, "Income", "Credit", period_list,
+ accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True)
+ expense = get_data(filters.company, "Expense", "Debit", period_list,
+ accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True)
+
+ net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company)
+
+ data = []
+ company_currency = frappe.db.get_value("Company", filters.company, "default_currency")
+
+ for cash_flow_account in cash_flow_accounts:
+ section_data = []
+ data.append({
+ "account_name": cash_flow_account['section_header'],
+ "parent_account": None,
+ "indent": 0.0,
+ "account": cash_flow_account['section_header']
+ })
+
+ if len(data) == 1:
+ # add first net income in operations section
+ if net_profit_loss:
+ net_profit_loss.update({
+ "indent": 1,
+ "parent_account": cash_flow_accounts[0]['section_header']
+ })
+ data.append(net_profit_loss)
+ section_data.append(net_profit_loss)
+
+ for account in cash_flow_account['account_types']:
+ account_data = get_account_type_based_data(filters.company,
+ account['account_type'], period_list, filters.accumulated_values)
+ account_data.update({
+ "account_name": account['label'],
+ "account": account['label'],
+ "indent": 1,
+ "parent_account": cash_flow_account['section_header'],
+ "currency": company_currency
+ })
+ data.append(account_data)
+ section_data.append(account_data)
+
+ add_total_row_account(data, section_data, cash_flow_account['section_footer'],
+ period_list, company_currency)
+
+ add_total_row_account(data, data, _("Net Change in Cash"), period_list, company_currency)
+ columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company)
+
+ return columns, data
+
+def get_cash_flow_accounts():
operation_accounts = {
"section_name": "Operations",
"section_footer": _("Net Cash from Operations"),
@@ -49,80 +103,17 @@
}
# combine all cash flow accounts for iteration
- cash_flow_accounts = [operation_accounts, investing_accounts, financing_accounts]
-
- # compute net profit / loss
- income = get_data(filters.company, "Income", "Credit", period_list,
- accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True)
- expense = get_data(filters.company, "Expense", "Debit", period_list,
- accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True)
-
- net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company)
-
- data = []
- company_currency = frappe.db.get_value("Company", filters.company, "default_currency")
-
- for cash_flow_account in cash_flow_accounts:
- section_data = []
- data.append({
- "account_name": cash_flow_account['section_header'],
- "parent_account": None,
- "indent": 0.0,
- "account": cash_flow_account['section_header']
- })
-
- if len(data) == 1:
- # add first net income in operations section
- if net_profit_loss:
- net_profit_loss.update({
- "indent": 1,
- "parent_account": operation_accounts['section_header']
- })
- data.append(net_profit_loss)
- section_data.append(net_profit_loss)
-
- for account in cash_flow_account['account_types']:
- account_data = get_account_type_based_data(filters.company,
- account['account_type'], period_list, filters.accumulated_values)
- account_data.update({
- "account_name": account['label'],
- "account": account['label'],
- "indent": 1,
- "parent_account": cash_flow_account['section_header'],
- "currency": company_currency
- })
- data.append(account_data)
- section_data.append(account_data)
-
- add_total_row_account(data, section_data, cash_flow_account['section_footer'],
- period_list, company_currency)
-
- add_total_row_account(data, data, _("Net Change in Cash"), period_list, company_currency)
- columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company)
-
- return columns, data
-
+ return [operation_accounts, investing_accounts, financing_accounts]
def get_account_type_based_data(company, account_type, period_list, accumulated_values):
data = {}
total = 0
for period in period_list:
start_date = get_start_date(period, accumulated_values, company)
- gl_sum = frappe.db.sql_list("""
- select sum(credit) - sum(debit)
- from `tabGL Entry`
- where company=%s and posting_date >= %s and posting_date <= %s
- and voucher_type != 'Period Closing Voucher'
- and account in ( SELECT name FROM tabAccount WHERE account_type = %s)
- """, (company, start_date if accumulated_values else period['from_date'],
- period['to_date'], account_type))
- if gl_sum and gl_sum[0]:
- amount = gl_sum[0]
- if account_type == "Depreciation":
- amount *= -1
- else:
- amount = 0
+ amount = get_account_type_based_gl_data(company, start_date, period['to_date'], account_type)
+ if amount and account_type == "Depreciation":
+ amount *= -1
total += amount
data.setdefault(period["key"], amount)
@@ -130,16 +121,28 @@
data["total"] = total
return data
+def get_account_type_based_gl_data(company, start_date, end_date, account_type):
+ gl_sum = frappe.db.sql_list("""
+ select sum(credit) - sum(debit)
+ from `tabGL Entry`
+ where company=%s and posting_date >= %s and posting_date <= %s
+ and voucher_type != 'Period Closing Voucher'
+ and account in ( SELECT name FROM tabAccount WHERE account_type = %s)
+ """, (company, start_date, end_date, account_type))
+
+ return gl_sum[0] if gl_sum and gl_sum[0] else 0
def get_start_date(period, accumulated_values, company):
+ if not accumulated_values and period.get('from_date'):
+ return period['from_date']
+
start_date = period["year_start_date"]
if accumulated_values:
start_date = get_fiscal_year(period.to_date, company=company)[1]
return start_date
-
-def add_total_row_account(out, data, label, period_list, currency):
+def add_total_row_account(out, data, label, period_list, currency, consolidated = False):
total_row = {
"account_name": "'" + _("{0}").format(label) + "'",
"account": "'" + _("{0}").format(label) + "'",
@@ -148,8 +151,9 @@
for row in data:
if 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)
+ key = period if consolidated else period['key']
+ total_row.setdefault(key, 0.0)
+ total_row[key] += row.get(key, 0.0)
total_row.setdefault("total", 0.0)
total_row["total"] += row["total"]
diff --git a/erpnext/accounts/report/consolidated_financial_statement/__init__.py b/erpnext/accounts/report/consolidated_financial_statement/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/report/consolidated_financial_statement/__init__.py
diff --git a/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.js b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.js
new file mode 100644
index 0000000..63f263f
--- /dev/null
+++ b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.js
@@ -0,0 +1,46 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Consolidated Financial Statement"] = {
+ "filters": [
+ {
+ "fieldname":"company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "default": frappe.defaults.get_user_default("Company"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"from_fiscal_year",
+ "label": __("Start Year"),
+ "fieldtype": "Link",
+ "options": "Fiscal Year",
+ "default": frappe.defaults.get_user_default("fiscal_year"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"to_fiscal_year",
+ "label": __("End Year"),
+ "fieldtype": "Link",
+ "options": "Fiscal Year",
+ "default": frappe.defaults.get_user_default("fiscal_year"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"report",
+ "label": __("Report"),
+ "fieldtype": "Select",
+ "options": ["Profit and Loss Statement", "Balance Sheet", "Cash Flow"],
+ "default": "Balance Sheet",
+ "reqd": 1
+ },
+ {
+ "fieldname":"accumulated_in_group_company",
+ "label": __("Accumulated Values in Group Company"),
+ "fieldtype": "Check",
+ "default": 0
+ },
+ ]
+}
diff --git a/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.json b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.json
new file mode 100644
index 0000000..e03f1af
--- /dev/null
+++ b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.json
@@ -0,0 +1,35 @@
+{
+ "add_total_row": 0,
+ "creation": "2018-04-14 16:01:07.919565",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "letter_head": "Test AEF",
+ "modified": "2018-04-14 16:01:07.919565",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Consolidated Financial Statement",
+ "owner": "Administrator",
+ "ref_doctype": "Account",
+ "report_name": "Consolidated Financial Statement",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Accounts User"
+ },
+ {
+ "role": "Auditor"
+ },
+ {
+ "role": "Sales User"
+ },
+ {
+ "role": "Purchase User"
+ },
+ {
+ "role": "Accounts Manager"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py
new file mode 100644
index 0000000..ec2de2b
--- /dev/null
+++ b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py
@@ -0,0 +1,408 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe import _
+from frappe.utils import flt, cint
+from erpnext.accounts.report.financial_statements import get_fiscal_year_data, sort_accounts
+from erpnext.accounts.report.balance_sheet.balance_sheet import (get_provisional_profit_loss,
+ check_opening_balance, get_chart_data)
+from erpnext.accounts.report.profit_and_loss_statement.profit_and_loss_statement import (get_net_profit_loss,
+ get_chart_data as get_pl_chart_data)
+from erpnext.accounts.report.cash_flow.cash_flow import (get_cash_flow_accounts, get_account_type_based_gl_data,
+ add_total_row_account)
+
+def execute(filters=None):
+ columns, data, message, chart = [], [], [], []
+ fiscal_year = get_fiscal_year_data(filters.get('from_fiscal_year'), filters.get('to_fiscal_year'))
+ companies_column, companies = get_companies(filters)
+ columns = get_columns(companies_column)
+
+ if filters.get('report') == "Balance Sheet":
+ data, message, chart = get_balance_sheet_data(fiscal_year, companies, columns, filters)
+ elif filters.get('report') == "Profit and Loss Statement":
+ data, message, chart = get_profit_loss_data(fiscal_year, companies, columns, filters)
+ else:
+ if cint(frappe.db.get_single_value('Accounts Settings', 'use_custom_cash_flow')):
+ from erpnext.accounts.report.cash_flow.custom_cash_flow import execute as execute_custom
+ return execute_custom(filters=filters)
+
+ data = get_cash_flow_data(fiscal_year, companies, filters)
+
+ return columns, data, message, chart
+
+def get_balance_sheet_data(fiscal_year, companies, columns, filters):
+ asset = get_data(companies, "Asset", "Debit", fiscal_year, filters=filters)
+
+ liability = get_data(companies, "Liability", "Credit", fiscal_year, filters=filters)
+
+ equity = get_data(companies, "Equity", "Credit", fiscal_year, filters=filters)
+
+ data = []
+ data.extend(asset or [])
+ data.extend(liability or [])
+ data.extend(equity or [])
+
+ provisional_profit_loss, total_credit = get_provisional_profit_loss(asset, liability, equity,
+ companies, filters.get('company'), True)
+
+ message, opening_balance = check_opening_balance(asset, liability, equity)
+
+ if opening_balance and round(opening_balance,2) !=0:
+ unclosed ={
+ "account_name": "'" + _("Unclosed Fiscal Years Profit / Loss (Credit)") + "'",
+ "account": "'" + _("Unclosed Fiscal Years Profit / Loss (Credit)") + "'",
+ "warn_if_negative": True,
+ "currency": frappe.db.get_value("Company", filters.company, "default_currency")
+ }
+ for company in companies:
+ unclosed[company] = opening_balance
+ if provisional_profit_loss:
+ provisional_profit_loss[company] = provisional_profit_loss[company] - opening_balance
+
+ unclosed["total"]=opening_balance
+ data.append(unclosed)
+
+ if provisional_profit_loss:
+ data.append(provisional_profit_loss)
+ if total_credit:
+ data.append(total_credit)
+
+ chart = get_chart_data(filters, columns, asset, liability, equity)
+
+ return data, message, chart
+
+def get_profit_loss_data(fiscal_year, companies, columns, filters):
+ income, expense, net_profit_loss = get_income_expense_data(companies, fiscal_year, filters)
+
+ data = []
+ data.extend(income or [])
+ data.extend(expense or [])
+ if net_profit_loss:
+ data.append(net_profit_loss)
+
+ chart = get_pl_chart_data(filters, columns, income, expense, net_profit_loss)
+
+ return data, None, chart
+
+def get_income_expense_data(companies, fiscal_year, filters):
+ income = get_data(companies, "Income", "Credit", fiscal_year, filters, True)
+
+ expense = get_data(companies, "Expense", "Debit", fiscal_year, filters, True)
+
+ net_profit_loss = get_net_profit_loss(income, expense, companies, filters.company, True)
+
+ return income, expense, net_profit_loss
+
+def get_cash_flow_data(fiscal_year, companies, filters):
+ cash_flow_accounts = get_cash_flow_accounts()
+
+ income, expense, net_profit_loss = get_income_expense_data(companies, fiscal_year, filters)
+
+ data = []
+ company_currency = frappe.db.get_value("Company", filters.company, "default_currency")
+
+ for cash_flow_account in cash_flow_accounts:
+ section_data = []
+ data.append({
+ "account_name": cash_flow_account['section_header'],
+ "parent_account": None,
+ "indent": 0.0,
+ "account": cash_flow_account['section_header']
+ })
+
+ if len(data) == 1:
+ # add first net income in operations section
+ if net_profit_loss:
+ net_profit_loss.update({
+ "indent": 1,
+ "parent_account": cash_flow_accounts[0]['section_header']
+ })
+ data.append(net_profit_loss)
+ section_data.append(net_profit_loss)
+
+ for account in cash_flow_account['account_types']:
+ account_data = get_account_type_based_data(account['account_type'], companies, fiscal_year)
+ account_data.update({
+ "account_name": account['label'],
+ "account": account['label'],
+ "indent": 1,
+ "parent_account": cash_flow_account['section_header'],
+ "currency": company_currency
+ })
+ data.append(account_data)
+ section_data.append(account_data)
+
+ add_total_row_account(data, section_data, cash_flow_account['section_footer'],
+ companies, company_currency, True)
+
+ add_total_row_account(data, data, _("Net Change in Cash"), companies, company_currency, True)
+
+ return data
+
+def get_account_type_based_data(account_type, companies, fiscal_year):
+ data = {}
+ total = 0
+ for company in companies:
+ amount = get_account_type_based_gl_data(company,
+ fiscal_year.year_start_date, fiscal_year.year_end_date, account_type)
+
+ if amount and account_type == "Depreciation":
+ amount *= -1
+
+ total += amount
+ data.setdefault(company, amount)
+
+ data["total"] = total
+ return data
+
+def get_columns(companies):
+ columns = [{
+ "fieldname": "account",
+ "label": _("Account"),
+ "fieldtype": "Link",
+ "options": "Account",
+ "width": 300
+ }]
+
+ columns.append({
+ "fieldname": "currency",
+ "label": _("Currency"),
+ "fieldtype": "Link",
+ "options": "Currency",
+ "hidden": 1
+ })
+
+ for company in companies:
+ columns.append({
+ "fieldname": company,
+ "label": company,
+ "fieldtype": "Currency",
+ "width": 150
+ })
+
+ return columns
+
+def get_data(companies, root_type, balance_must_be, fiscal_year, filters=None, ignore_closing_entries=False):
+ accounts, accounts_by_name = get_account_heads(root_type,
+ companies, filters)
+
+ company_currency = get_company_currency(filters)
+
+ 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(fiscal_year.year_start_date,
+ fiscal_year.year_end_date, root.lft, root.rgt, filters,
+ gl_entries_by_account, accounts_by_name, ignore_closing_entries=False)
+
+ calculate_values(accounts_by_name, gl_entries_by_account, companies, fiscal_year, filters)
+ accumulate_values_into_parents(accounts, accounts_by_name, companies)
+ out = prepare_data(accounts, fiscal_year, balance_must_be, companies, company_currency)
+
+ if out:
+ add_total_row(out, root_type, balance_must_be, companies, company_currency)
+
+ return out
+
+def get_company_currency(filters=None):
+ return frappe.db.get_value("Company", filters.get('company'), "default_currency")
+
+def calculate_values(accounts_by_name, gl_entries_by_account, companies, fiscal_year, filters):
+ for entries in gl_entries_by_account.values():
+ for entry in entries:
+ key = entry.account_number or entry.account_name
+ d = accounts_by_name.get(key)
+ if d:
+ for company in companies:
+ # check if posting date is within the period
+ if (entry.company == company or (filters.get('accumulated_in_group_company'))
+ and entry.company in companies.get(company)):
+ d[company] = d.get(company, 0.0) + flt(entry.debit) - flt(entry.credit)
+
+ if entry.posting_date < fiscal_year.year_start_date:
+ d["opening_balance"] = d.get("opening_balance", 0.0) + flt(entry.debit) - flt(entry.credit)
+
+def accumulate_values_into_parents(accounts, accounts_by_name, companies):
+ """accumulate children's values in parent accounts"""
+ for d in reversed(accounts):
+ if d.parent_account:
+ account = d.parent_account.split('-')[0].strip()
+ for company in companies:
+ accounts_by_name[account][company] = \
+ accounts_by_name[account].get(company, 0.0) + d.get(company, 0.0)
+
+ accounts_by_name[account]["opening_balance"] = \
+ accounts_by_name[account].get("opening_balance", 0.0) + d.get("opening_balance", 0.0)
+
+def get_account_heads(root_type, companies, filters):
+ accounts = get_accounts(root_type, filters)
+
+ if not accounts:
+ return None
+
+ accounts, accounts_by_name, parent_children_map = filter_accounts(accounts)
+
+ return accounts, accounts_by_name
+
+def get_companies(filters):
+ companies = {}
+ all_companies = get_subsidiary_companies(filters.get('company'))
+ companies.setdefault(filters.get('company'), all_companies)
+
+ for d in all_companies:
+ if d not in companies:
+ subsidiary_companies = get_subsidiary_companies(d)
+ companies.setdefault(d, subsidiary_companies)
+
+ return all_companies, companies
+
+def get_subsidiary_companies(company):
+ lft, rgt = frappe.db.get_value('Company',
+ company, ["lft", "rgt"])
+
+ return frappe.db.sql_list("""select name from `tabCompany`
+ where lft >= {0} and rgt <= {1}""".format(lft, rgt))
+
+def get_accounts(root_type, filters):
+ return frappe.db.sql(""" select name, is_group, company,
+ parent_account, lft, rgt, root_type, report_type, account_name, account_number
+ from
+ `tabAccount` where company = %s and root_type = %s
+ """ , (filters.get('company'), root_type), as_dict=1)
+
+def prepare_data(accounts, fiscal_year, balance_must_be, companies, company_currency):
+ data = []
+ year_start_date = fiscal_year.year_start_date
+ year_end_date = fiscal_year.year_end_date
+
+ for d in accounts:
+ # add to output
+ has_value = False
+ total = 0
+ row = frappe._dict({
+ "account_name": _(d.account_name),
+ "account": _(d.account_name),
+ "parent_account": _(d.parent_account),
+ "indent": flt(d.indent),
+ "year_start_date": year_start_date,
+ "year_end_date": year_end_date,
+ "currency": company_currency,
+ "opening_balance": d.get("opening_balance", 0.0) * (1 if balance_must_be == "Debit" else -1)
+ })
+ for company in companies:
+ if d.get(company) and balance_must_be == "Credit":
+ # change sign based on Debit or Credit, since calculation is done using (debit - credit)
+ d[company] *= -1
+
+ row[company] = flt(d.get(company, 0.0), 3)
+
+ if abs(row[company]) >= 0.005:
+ # ignore zero values
+ has_value = True
+ total += flt(row[company])
+
+ row["has_value"] = has_value
+ row["total"] = total
+ data.append(row)
+
+ return data
+
+def set_gl_entries_by_account(from_date, to_date, root_lft, root_rgt, filters, gl_entries_by_account,
+ accounts_by_name, ignore_closing_entries=False):
+ """Returns a dict like { "account": [gl entries], ... }"""
+
+ company_lft, company_rgt = frappe.db.get_value('Company',
+ filters.get('company'), ["lft", "rgt"])
+
+ additional_conditions = get_additional_conditions(from_date, ignore_closing_entries)
+
+ gl_entries = frappe.db.sql("""select gl.posting_date, gl.account, gl.debit, gl.credit, gl.is_opening, gl.company,
+ gl.fiscal_year, gl.debit_in_account_currency, gl.credit_in_account_currency, gl.account_currency,
+ acc.account_name, acc.account_number
+ from `tabGL Entry` gl, `tabAccount` acc where acc.name = gl.account and gl.company in
+ (select name from `tabCompany` where lft >= %(company_lft)s and rgt <= %(company_rgt)s)
+ {additional_conditions} and gl.posting_date <= %(to_date)s and acc.lft >= %(lft)s and acc.rgt <= %(rgt)s
+ order by gl.account, gl.posting_date""".format(additional_conditions=additional_conditions),
+ {
+ "from_date": from_date,
+ "to_date": to_date,
+ "lft": root_lft,
+ "rgt": root_rgt,
+ "company_lft": company_lft,
+ "company_rgt": company_rgt,
+ },
+ as_dict=True)
+
+ for entry in gl_entries:
+ key = entry.account_number or entry.account_name
+ validate_entries(key, entry, accounts_by_name)
+ gl_entries_by_account.setdefault(key, []).append(entry)
+
+ return gl_entries_by_account
+
+def validate_entries(key, entry, accounts_by_name):
+ if key not in accounts_by_name:
+ field = "Account number" if entry.account_number else "Account name"
+ frappe.throw(_("{0} {1} is not present in the parent company").format(field, key))
+
+def get_additional_conditions(from_date, ignore_closing_entries):
+ additional_conditions = []
+
+ if ignore_closing_entries:
+ additional_conditions.append("ifnull(gl.voucher_type, '')!='Period Closing Voucher'")
+
+ if from_date:
+ additional_conditions.append("gl.posting_date >= %(from_date)s")
+
+ return " and {}".format(" and ".join(additional_conditions)) if additional_conditions else ""
+
+def add_total_row(out, root_type, balance_must_be, companies, company_currency):
+ total_row = {
+ "account_name": "'" + _("Total {0} ({1})").format(_(root_type), _(balance_must_be)) + "'",
+ "account": "'" + _("Total {0} ({1})").format(_(root_type), _(balance_must_be)) + "'",
+ "currency": company_currency
+ }
+
+ for row in out:
+ if not row.get("parent_account"):
+ for company in companies:
+ total_row.setdefault(company, 0.0)
+ total_row[company] += row.get(company, 0.0)
+ row[company] = 0.0
+
+ total_row.setdefault("total", 0.0)
+ total_row["total"] += flt(row["total"])
+ row["total"] = ""
+
+ if "total" in total_row:
+ out.append(total_row)
+
+ # blank row after Total
+ out.append({})
+
+def filter_accounts(accounts, depth=10):
+ parent_children_map = {}
+ accounts_by_name = {}
+ for d in accounts:
+ key = d.account_number or d.account_name
+ accounts_by_name[key] = 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 []
+ sort_accounts(children, is_root=True if parent==None else False)
+
+ 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, parent_children_map
diff --git a/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.py b/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.py
index c8d9857..249d9d3 100644
--- a/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.py
+++ b/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.py
@@ -33,7 +33,11 @@
return columns, data, None, chart
+<<<<<<< HEAD
def get_net_profit_loss(income, expense, period_list, company, currency=None):
+=======
+def get_net_profit_loss(income, expense, period_list, company, consolidated=False):
+>>>>>>> Consolidated financial statement (#13678)
total = 0
net_profit_loss = {
"account_name": "'" + _("Profit for the year") + "'",
@@ -45,21 +49,21 @@
has_value = False
for period in period_list:
- total_income = flt(income[-2][period.key], 3) if income else 0
- total_expense = flt(expense[-2][period.key], 3) if expense else 0
+ key = period if consolidated else period.key
+ total_income = flt(income[-2][key], 3) if income else 0
+ total_expense = flt(expense[-2][key], 3) if expense else 0
- net_profit_loss[period.key] = total_income - total_expense
+ net_profit_loss[key] = total_income - total_expense
- if net_profit_loss[period.key]:
+ if net_profit_loss[key]:
has_value=True
- total += flt(net_profit_loss[period.key])
+ total += flt(net_profit_loss[key])
net_profit_loss["total"] = total
if has_value:
return net_profit_loss
-
def get_chart_data(filters, columns, income, expense, net_profit_loss):
labels = [d.get("label") for d in columns[2:]]
diff --git a/erpnext/config/accounts.py b/erpnext/config/accounts.py
index 088a119..6c7c298 100644
--- a/erpnext/config/accounts.py
+++ b/erpnext/config/accounts.py
@@ -128,6 +128,12 @@
"doctype": "GL Entry",
"is_query_report": True
},
+ {
+ "type": "report",
+ "name": "Consolidated Financial Statement",
+ "doctype": "GL Entry",
+ "is_query_report": True
+ },
]
},
{
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index f2dd923..3fffade 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -526,3 +526,4 @@
erpnext.patches.v11_0.rename_field_max_days_allowed
erpnext.patches.v11_0.create_salary_structure_assignments
erpnext.patches.v11_0.rename_health_insurance
+erpnext.patches.v11_0.rebuild_tree_for_company
diff --git a/erpnext/patches/v11_0/rebuild_tree_for_company.py b/erpnext/patches/v11_0/rebuild_tree_for_company.py
new file mode 100644
index 0000000..0fc4780
--- /dev/null
+++ b/erpnext/patches/v11_0/rebuild_tree_for_company.py
@@ -0,0 +1,6 @@
+import frappe
+from frappe.utils.nestedset import rebuild_tree
+
+def execute():
+ frappe.reload_doc("setup", "doctype", "company")
+ rebuild_tree('Company', 'parent_company')
diff --git a/erpnext/setup/doctype/company/company.json b/erpnext/setup/doctype/company/company.json
index 31e1859..eb861d5 100644
--- a/erpnext/setup/doctype/company/company.json
+++ b/erpnext/setup/doctype/company/company.json
@@ -42,6 +42,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -73,6 +74,7 @@
"reqd": 1,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -105,6 +107,7 @@
"reqd": 1,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -135,6 +138,38 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
+ "unique": 0
+ },
+ {
+ "allow_bulk_edit": 0,
+ "allow_on_submit": 0,
+ "bold": 1,
+ "collapsible": 0,
+ "columns": 0,
+ "fieldname": "is_group",
+ "fieldtype": "Check",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_global_search": 0,
+ "in_list_view": 1,
+ "in_standard_filter": 0,
+ "label": "Is Group",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -163,6 +198,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -193,6 +229,39 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
+ "unique": 0
+ },
+ {
+ "allow_bulk_edit": 0,
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
+ "fieldname": "parent_company",
+ "fieldtype": "Link",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_global_search": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "Parent Comapny",
+ "length": 0,
+ "no_copy": 0,
+ "options": "Company",
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -223,6 +292,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -253,6 +323,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -284,6 +355,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -313,6 +385,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -344,6 +417,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -373,6 +447,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -404,6 +479,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -435,6 +511,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -465,6 +542,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -495,6 +573,7 @@
"reqd": 1,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -524,6 +603,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -554,6 +634,7 @@
"reqd": 1,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -585,6 +666,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -617,6 +699,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -649,6 +732,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -679,6 +763,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -709,6 +794,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -742,6 +828,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -773,6 +860,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -806,6 +894,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -837,6 +926,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -868,6 +958,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -899,6 +990,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -928,6 +1020,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0,
"width": "50%"
},
@@ -962,6 +1055,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -993,6 +1087,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1024,6 +1119,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1055,6 +1151,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1087,6 +1184,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1118,6 +1216,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1147,6 +1246,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1178,6 +1278,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1207,6 +1308,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1240,6 +1342,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1272,6 +1375,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1302,6 +1406,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1333,6 +1438,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1364,6 +1470,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1394,6 +1501,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1423,6 +1531,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1453,6 +1562,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1483,6 +1593,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1513,6 +1624,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1544,6 +1656,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1575,6 +1688,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1605,6 +1719,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1634,6 +1749,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1665,6 +1781,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1696,6 +1813,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1726,6 +1844,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1755,6 +1874,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1784,6 +1904,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0,
"width": "50%"
},
@@ -1817,6 +1938,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1849,6 +1971,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1881,6 +2004,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1912,6 +2036,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -1943,6 +2068,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0,
"width": "50%"
},
@@ -1976,6 +2102,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -2006,6 +2133,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
@@ -2036,6 +2164,100 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
+ "unique": 0
+ },
+ {
+ "allow_bulk_edit": 0,
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
+ "fieldname": "lft",
+ "fieldtype": "Int",
+ "hidden": 1,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_global_search": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "Lft",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 1,
+ "print_hide_if_no_value": 0,
+ "read_only": 1,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 1,
+ "set_only_once": 0,
+ "translatable": 0,
+ "unique": 0
+ },
+ {
+ "allow_bulk_edit": 0,
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
+ "fieldname": "rgt",
+ "fieldtype": "Int",
+ "hidden": 1,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_global_search": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "Rgt",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 1,
+ "print_hide_if_no_value": 0,
+ "read_only": 1,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 1,
+ "set_only_once": 0,
+ "translatable": 0,
+ "unique": 0
+ },
+ {
+ "allow_bulk_edit": 0,
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
+ "fieldname": "old_parent",
+ "fieldtype": "Data",
+ "hidden": 1,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_global_search": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "old_parent",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 1,
+ "print_hide_if_no_value": 0,
+ "read_only": 1,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "translatable": 0,
"unique": 0
}
],
@@ -2052,15 +2274,14 @@
"istable": 0,
"max_attachments": 0,
"menu_index": 0,
- "modified": "2018-02-14 15:54:26.776363",
- "modified_by": "achilles@erpnext.com",
+ "modified": "2018-04-09 01:54:56.828976",
+ "modified_by": "Administrator",
"module": "Setup",
"name": "Company",
"owner": "Administrator",
"permissions": [
{
"amend": 0,
- "apply_user_permissions": 0,
"cancel": 0,
"create": 1,
"delete": 1,
@@ -2080,7 +2301,6 @@
},
{
"amend": 0,
- "apply_user_permissions": 0,
"cancel": 0,
"create": 0,
"delete": 0,
@@ -2100,7 +2320,6 @@
},
{
"amend": 0,
- "apply_user_permissions": 0,
"cancel": 0,
"create": 0,
"delete": 0,
@@ -2120,7 +2339,6 @@
},
{
"amend": 0,
- "apply_user_permissions": 0,
"cancel": 0,
"create": 0,
"delete": 0,
@@ -2140,7 +2358,6 @@
},
{
"amend": 0,
- "apply_user_permissions": 0,
"cancel": 0,
"create": 0,
"delete": 0,
@@ -2160,7 +2377,6 @@
},
{
"amend": 0,
- "apply_user_permissions": 0,
"cancel": 0,
"create": 0,
"delete": 0,
@@ -2180,7 +2396,6 @@
},
{
"amend": 0,
- "apply_user_permissions": 0,
"cancel": 0,
"create": 0,
"delete": 0,
diff --git a/erpnext/setup/doctype/company/company.py b/erpnext/setup/doctype/company/company.py
index cdcb059..236f0b8 100644
--- a/erpnext/setup/doctype/company/company.py
+++ b/erpnext/setup/doctype/company/company.py
@@ -11,8 +11,11 @@
from frappe.model.document import Document
from frappe.contacts.address_and_contact import load_address_and_contact
+from frappe.utils.nestedset import NestedSet
-class Company(Document):
+class Company(NestedSet):
+ nsm_parent_field = 'parent_company'
+
def onload(self):
load_address_and_contact(self, "company")
self.get("__onload")["transactions_exist"] = self.check_if_transactions_exist()
@@ -78,6 +81,7 @@
frappe.throw(_("Cannot change company's default currency, because there are existing transactions. Transactions must be cancelled to change the default currency."))
def on_update(self):
+ self.update_nsm_model()
if not frappe.db.sql("""select name from tabAccount
where company=%s and docstatus<2 limit 1""", self.name):
if not frappe.local.flags.ignore_chart_of_accounts:
@@ -245,10 +249,14 @@
def abbreviate(self):
self.abbr = ''.join([c[0].upper() for c in self.company_name.split()])
+ def update_nsm_model(self):
+ frappe.utils.nestedset.update_nsm(self)
+
def on_trash(self):
"""
Trash accounts and cost centers for this company if no gl entry exists
"""
+ self.update_nsm_model()
accounts = frappe.db.sql_list("select name from tabAccount where company=%s", self.name)
cost_centers = frappe.db.sql_list("select name from `tabCost Center` where company=%s", self.name)
warehouses = frappe.db.sql_list("select name from tabWarehouse where company=%s", self.name)
@@ -387,3 +395,32 @@
for company in companies:
update_company_monthly_sales(company)
frappe.db.commit()
+
+@frappe.whitelist()
+def get_children(doctype, parent=None, company=None, is_root=False):
+ if parent == None or parent == "All Companies":
+ parent = ""
+
+ return frappe.db.sql("""
+ select
+ name as value,
+ is_group as expandable
+ from
+ `tab{doctype}` comp
+ where
+ ifnull(parent_company, "")="{parent}"
+ """.format(
+ doctype = frappe.db.escape(doctype),
+ parent=frappe.db.escape(parent)
+ ), as_dict=1)
+
+@frappe.whitelist()
+def add_node():
+ from frappe.desk.treeview import make_tree_args
+ args = frappe.form_dict
+ args = make_tree_args(**args)
+
+ if args.parent_company == 'All Companies':
+ args.parent_company = None
+
+ frappe.get_doc(args).insert()
diff --git a/erpnext/setup/doctype/company/company_tree.js b/erpnext/setup/doctype/company/company_tree.js
new file mode 100644
index 0000000..19b276c
--- /dev/null
+++ b/erpnext/setup/doctype/company/company_tree.js
@@ -0,0 +1,33 @@
+frappe.treeview_settings["Company"] = {
+ ignore_fields:["parent_company"],
+ get_tree_nodes: 'erpnext.setup.doctype.company.company.get_children',
+ add_tree_node: 'erpnext.setup.doctype.company.company.add_node',
+ filters: [
+ {
+ fieldname: "company",
+ fieldtype:"Link",
+ options: "Company",
+ label: __("Company"),
+ get_query: function() {
+ return {
+ filters: [["Company", 'is_group', '=', 1]]
+ };
+ }
+ },
+ ],
+ breadcrumb: "Setup",
+ root_label: "All Companies",
+ get_tree_root: false,
+ menu_items: [
+ {
+ label: __("New Company"),
+ action: function() {
+ frappe.new_doc("Company", true);
+ },
+ condition: 'frappe.boot.user.can_create.indexOf("Company") !== -1'
+ }
+ ],
+ onload: function(treeview) {
+ treeview.make_tree();
+ }
+};
\ No newline at end of file