Merge pull request #16629 from Anurag810/gross-and-net-profit-report
feat: Gross and Net Profit Report
diff --git a/erpnext/accounts/doctype/account/account.json b/erpnext/accounts/doctype/account/account.json
index e47f8d2..460c025 100644
--- a/erpnext/accounts/doctype/account/account.json
+++ b/erpnext/accounts/doctype/account/account.json
@@ -632,6 +632,39 @@
"set_only_once": 0,
"translatable": 0,
"unique": 0
+ },
+ {
+ "allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
+ "depends_on": "eval:(doc.report_type == 'Profit and Loss' && !doc.is_group)",
+ "fieldname": "include_in_gross",
+ "fieldtype": "Check",
+ "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": "Include in gross",
+ "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
}
],
"has_web_view": 0,
@@ -645,7 +678,7 @@
"issingle": 0,
"istable": 0,
"max_attachments": 0,
- "modified": "2019-01-07 16:52:02.557837",
+ "modified": "2019-03-04 14:42:07.208893",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Account",
diff --git a/erpnext/accounts/report/financial_statements.py b/erpnext/accounts/report/financial_statements.py
index 2c7bd72..e9aecfb 100644
--- a/erpnext/accounts/report/financial_statements.py
+++ b/erpnext/accounts/report/financial_statements.py
@@ -126,7 +126,7 @@
def get_data(
company, root_type, balance_must_be, period_list, filters=None,
accumulated_values=1, only_current_fiscal_year=True, ignore_closing_entries=False,
- ignore_accumulated_values_for_fy=False):
+ ignore_accumulated_values_for_fy=False , total = True):
accounts = get_accounts(company, root_type)
if not accounts:
@@ -154,7 +154,7 @@
out = prepare_data(accounts, balance_must_be, period_list, company_currency)
out = filter_out_zero_value_rows(out, parent_children_map)
- if out:
+ if out and total:
add_total_row(out, root_type, balance_must_be, period_list, company_currency)
return out
@@ -218,6 +218,9 @@
"year_start_date": year_start_date,
"year_end_date": year_end_date,
"currency": company_currency,
+ "include_in_gross": d.include_in_gross,
+ "account_type": d.account_type,
+ "is_group": d.is_group,
"opening_balance": d.get("opening_balance", 0.0) * (1 if balance_must_be=="Debit" else -1),
"account_name": ('%s - %s' %(_(d.account_number), _(d.account_name))
if d.account_number else _(d.account_name))
@@ -285,7 +288,7 @@
def get_accounts(company, root_type):
return frappe.db.sql("""
- select name, account_number, parent_account, lft, rgt, root_type, report_type, account_name
+ select name, account_number, parent_account, lft, rgt, root_type, report_type, account_name, include_in_gross, account_type, is_group, lft, rgt
from `tabAccount`
where company=%s and root_type=%s order by lft""", (company, root_type), as_dict=True)
diff --git a/erpnext/accounts/report/gross_and_net_profit_report/__init__.py b/erpnext/accounts/report/gross_and_net_profit_report/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/report/gross_and_net_profit_report/__init__.py
diff --git a/erpnext/accounts/report/gross_and_net_profit_report/gross_and_net_profit_report.html b/erpnext/accounts/report/gross_and_net_profit_report/gross_and_net_profit_report.html
new file mode 100644
index 0000000..40ba20c
--- /dev/null
+++ b/erpnext/accounts/report/gross_and_net_profit_report/gross_and_net_profit_report.html
@@ -0,0 +1 @@
+{% include "accounts/report/financial_statements.html" %}
\ No newline at end of file
diff --git a/erpnext/accounts/report/gross_and_net_profit_report/gross_and_net_profit_report.js b/erpnext/accounts/report/gross_and_net_profit_report/gross_and_net_profit_report.js
new file mode 100644
index 0000000..63ac281
--- /dev/null
+++ b/erpnext/accounts/report/gross_and_net_profit_report/gross_and_net_profit_report.js
@@ -0,0 +1,51 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Gross and Net Profit Report"] = {
+ "filters": [
+
+ ]
+}
+frappe.require("assets/erpnext/js/financial_statements.js", function() {
+ frappe.query_reports["Gross and Net Profit Report"] = $.extend({},
+ erpnext.financial_statements);
+
+ frappe.query_reports["Gross and Net Profit Report"]["filters"].push(
+ {
+ "fieldname":"project",
+ "label": __("Project"),
+ "fieldtype": "MultiSelect",
+ get_data: function() {
+ var projects = frappe.query_report.get_filter_value("project") || "";
+
+ const values = projects.split(/\s*,\s*/).filter(d => d);
+ const txt = projects.match(/[^,\s*]*$/)[0] || '';
+ let data = [];
+
+ frappe.call({
+ type: "GET",
+ method:'frappe.desk.search.search_link',
+ async: false,
+ no_spinner: true,
+ args: {
+ doctype: "Project",
+ txt: txt,
+ filters: {
+ "name": ["not in", values]
+ }
+ },
+ callback: function(r) {
+ data = r.results;
+ }
+ });
+ return data;
+ }
+ },
+ {
+ "fieldname": "accumulated_values",
+ "label": __("Accumulated Values"),
+ "fieldtype": "Check"
+ }
+ );
+});
diff --git a/erpnext/accounts/report/gross_and_net_profit_report/gross_and_net_profit_report.json b/erpnext/accounts/report/gross_and_net_profit_report/gross_and_net_profit_report.json
new file mode 100644
index 0000000..994b47f
--- /dev/null
+++ b/erpnext/accounts/report/gross_and_net_profit_report/gross_and_net_profit_report.json
@@ -0,0 +1,30 @@
+{
+ "add_total_row": 0,
+ "creation": "2019-02-08 10:58:55.763090",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2019-02-08 10:58:55.763090",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Gross and Net Profit Report",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "GL Entry",
+ "report_name": "Gross and Net Profit Report",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Accounts User"
+ },
+ {
+ "role": "Accounts Manager"
+ },
+ {
+ "role": "Auditor"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/accounts/report/gross_and_net_profit_report/gross_and_net_profit_report.py b/erpnext/accounts/report/gross_and_net_profit_report/gross_and_net_profit_report.py
new file mode 100644
index 0000000..6550981
--- /dev/null
+++ b/erpnext/accounts/report/gross_and_net_profit_report/gross_and_net_profit_report.py
@@ -0,0 +1,154 @@
+# 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
+from erpnext.accounts.report.financial_statements import (get_period_list, get_columns, get_data)
+import copy
+
+
+def execute(filters=None):
+ period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year,
+ filters.periodicity, filters.accumulated_values, filters.company)
+
+ columns, data = [], []
+
+ income = get_data(filters.company, "Income", "Credit", period_list, filters = filters,
+ accumulated_values=filters.accumulated_values,
+ ignore_closing_entries=True, ignore_accumulated_values_for_fy= True, total= False)
+
+ expense = get_data(filters.company, "Expense", "Debit", period_list, filters=filters,
+ accumulated_values=filters.accumulated_values,
+ ignore_closing_entries=True, ignore_accumulated_values_for_fy= True, total= False)
+
+ columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company)
+
+
+ gross_income = get_revenue(income, period_list)
+
+ gross_expense = get_revenue(expense, period_list)
+
+ if(len(gross_income)==0 and len(gross_expense)== 0):
+ data.append({"account_name": "'" + _("Nothing is included in gross") + "'",
+ "account": "'" + _("Nothing is included in gross") + "'"})
+
+ return columns, data
+
+ data.append({"account_name": "'" + _("Included in Gross Profit") + "'",
+ "account": "'" + _("Included in Gross Profit") + "'"})
+
+ data.append({})
+ data.extend(gross_income or [])
+
+ data.append({})
+ data.extend(gross_expense or [])
+
+ data.append({})
+ gross_profit = get_profit(gross_income, gross_expense, period_list, filters.company, 'Gross Profit',filters.presentation_currency)
+ data.append(gross_profit)
+
+ non_gross_income = get_revenue(income, period_list, 0)
+ data.append({})
+ data.extend(non_gross_income or [])
+
+ non_gross_expense = get_revenue(expense, period_list, 0)
+ data.append({})
+ data.extend(non_gross_expense or [])
+
+ net_profit = get_net_profit(non_gross_income, gross_income, gross_expense, non_gross_expense, period_list, filters.company,filters.presentation_currency)
+ data.append({})
+ data.append(net_profit)
+
+ return columns, data
+
+def get_revenue(data, period_list, include_in_gross=1):
+ revenue = [item for item in data if item['include_in_gross']==include_in_gross or item['is_group']==1]
+
+ data_to_be_removed =True
+ while data_to_be_removed:
+ revenue, data_to_be_removed = remove_parent_with_no_child(revenue, period_list)
+ revenue = adjust_account(revenue, period_list)
+ return copy.deepcopy(revenue)
+
+def remove_parent_with_no_child(data, period_list):
+ data_to_be_removed = False
+ for parent in data:
+ if 'is_group' in parent and parent.get("is_group") == 1:
+ have_child = False
+ for child in data:
+ if 'parent_account' in child and child.get("parent_account") == parent.get("account"):
+ have_child = True
+ break
+
+ if not have_child:
+ data_to_be_removed = True
+ data.remove(parent)
+
+ return data, data_to_be_removed
+
+def adjust_account(data, period_list, consolidated= False):
+ leaf_nodes = [item for item in data if item['is_group'] == 0]
+ totals = {}
+ for node in leaf_nodes:
+ set_total(node, node["total"], data, totals)
+ for d in data:
+ for period in period_list:
+ key = period if consolidated else period.key
+ d[key] = totals[d["account"]]
+ d['total'] = totals[d["account"]]
+ return data
+
+def set_total(node, value, complete_list, totals):
+ if not totals.get(node['account']):
+ totals[node["account"]] = 0
+ totals[node["account"]] += value
+
+ parent = node['parent_account']
+ if not parent == '':
+ return set_total(next(item for item in complete_list if item['account'] == parent), value, complete_list, totals)
+
+
+def get_profit(gross_income, gross_expense, period_list, company, profit_type, currency=None, consolidated=False):
+
+ profit_loss = {
+ "account_name": "'" + _(profit_type) + "'",
+ "account": "'" + _(profit_type) + "'",
+ "warn_if_negative": True,
+ "currency": currency or frappe.get_cached_value('Company', company, "default_currency")
+ }
+
+ has_value = False
+
+ for period in period_list:
+ key = period if consolidated else period.key
+ profit_loss[key] = flt(gross_income[0].get(key, 0)) - flt(gross_expense[0].get(key, 0))
+
+ if profit_loss[key]:
+ has_value=True
+
+ if has_value:
+ return profit_loss
+
+def get_net_profit(non_gross_income, gross_income, gross_expense, non_gross_expense, period_list, company, currency=None, consolidated=False):
+ profit_loss = {
+ "account_name": "'" + _("Net Profit") + "'",
+ "account": "'" + _("Net Profit") + "'",
+ "warn_if_negative": True,
+ "currency": currency or frappe.get_cached_value('Company', company, "default_currency")
+ }
+
+ has_value = False
+
+ for period in period_list:
+ key = period if consolidated else period.key
+ total_income = flt(gross_income[0].get(key, 0)) + flt(non_gross_income[0].get(key, 0))
+ total_expense = flt(gross_expense[0].get(key, 0)) + flt(non_gross_expense[0].get(key, 0))
+ profit_loss[key] = flt(total_income) - flt(total_expense)
+
+ if profit_loss[key]:
+ has_value=True
+
+ if has_value:
+ return profit_loss