feat(Cost Center): Distributed Cost Center (#21531)
* feat: Distributed Cost Center
Squashed commit of the following:
commit 274236eb92dfddfd015583ee1e6b5e1937366d28
Author: Vignesh <kaviya.3111999@gmail.com>
Date: Mon Mar 23 18:32:26 2020 +0530
Fix: Indentation
commit beae0b9849f333dd1d9f29c27192ea7de2e4e809
Author: Vignesh <kaviya.3111999@gmail.com>
Date: Mon Mar 23 18:26:50 2020 +0530
The validations are added to distributed cost center.
commit 3f66943adacb5797338ef3510251bf6633be3dba
Author: Vignesh <kaviya.3111999@gmail.com>
Date: Thu Mar 19 22:17:25 2020 +0530
The distributed cost centers are shown with the percentage allocation in the reports
commit 1ac11df522096fa187d2f5863b4c970e18b37fac
Author: Vignesh <kaviya.3111999@gmail.com>
Date: Thu Mar 19 13:36:04 2020 +0530
The child table distributed cost center are added to cost center doctype and then validate percentage allocation.
* fix(Distributed Cost Center): validation and filters
* fix(Distributed Cost Center): financial statement query
* fix(Distributed Cost Center): add test cases
Co-authored-by: Vignesh S <svignesh0308@gmail.com>
* fix(Distributed Cost Center): budget variance, general ledger, profitability analysis reports.
* Merge branch 'develop' into develop-distributed-cost-center
* fix(Distributed Cost Center): Added DCC reflection in actual amount column of Budget Variance Report.
* Update creation field in general ledger report
Co-authored-by: Nabin Hait <nabinhait@gmail.com>
* creation field updated in gl_entries query
Co-authored-by: Nabin Hait <nabinhait@gmail.com>
Co-authored-by: KaviyaPeriyasamy <kaviya.3111999@gmail.com>
Co-authored-by: Vignesh S <svignesh0308@gmail.com>
Co-authored-by: KaviyaPeriyasamy <kaviyaperiyasamy22@gmail.com>
Co-authored-by: Kaviya Periyasamy <36359901+KaviyaPeriyasamy@users.noreply.github.com>
Co-authored-by: Nabin Hait <nabinhait@gmail.com>diff --git a/erpnext/accounts/doctype/cost_center/cost_center.js b/erpnext/accounts/doctype/cost_center/cost_center.js
index 9e2f6ee..f341f78 100644
--- a/erpnext/accounts/doctype/cost_center/cost_center.js
+++ b/erpnext/accounts/doctype/cost_center/cost_center.js
@@ -14,7 +14,18 @@
is_group: 1
}
}
- })
+ });
+
+ frm.set_query("cost_center", "distributed_cost_center", function() {
+ return {
+ filters: {
+ company: frm.doc.company,
+ is_group: 0,
+ enable_distributed_cost_center: 0,
+ name: ['!=', frm.doc.name]
+ }
+ };
+ });
},
refresh: function(frm) {
if (!frm.is_new()) {
diff --git a/erpnext/accounts/doctype/cost_center/cost_center.json b/erpnext/accounts/doctype/cost_center/cost_center.json
index 5013c92..c9bbbab 100644
--- a/erpnext/accounts/doctype/cost_center/cost_center.json
+++ b/erpnext/accounts/doctype/cost_center/cost_center.json
@@ -16,6 +16,9 @@
"cb0",
"is_group",
"disabled",
+ "section_break_9",
+ "enable_distributed_cost_center",
+ "distributed_cost_center",
"lft",
"rgt",
"old_parent"
@@ -119,6 +122,24 @@
"fieldname": "disabled",
"fieldtype": "Check",
"label": "Disabled"
+ },
+ {
+ "default": "0",
+ "fieldname": "enable_distributed_cost_center",
+ "fieldtype": "Check",
+ "label": "Enable Distributed Cost Center"
+ },
+ {
+ "depends_on": "eval:doc.is_group==0",
+ "fieldname": "section_break_9",
+ "fieldtype": "Section Break"
+ },
+ {
+ "depends_on": "enable_distributed_cost_center",
+ "fieldname": "distributed_cost_center",
+ "fieldtype": "Table",
+ "label": "Distributed Cost Center",
+ "options": "Distributed Cost Center"
}
],
"icon": "fa fa-money",
diff --git a/erpnext/accounts/doctype/cost_center/cost_center.py b/erpnext/accounts/doctype/cost_center/cost_center.py
index 0294e78..12094d4 100644
--- a/erpnext/accounts/doctype/cost_center/cost_center.py
+++ b/erpnext/accounts/doctype/cost_center/cost_center.py
@@ -19,6 +19,24 @@
def validate(self):
self.validate_mandatory()
self.validate_parent_cost_center()
+ self.validate_distributed_cost_center()
+
+ def validate_distributed_cost_center(self):
+ if cint(self.enable_distributed_cost_center):
+ if not self.distributed_cost_center:
+ frappe.throw(_("Please enter distributed cost center"))
+ if sum(x.percentage_allocation for x in self.distributed_cost_center) != 100:
+ frappe.throw(_("Total percentage allocation for distributed cost center should be equal to 100"))
+ if not self.get('__islocal'):
+ if not cint(frappe.get_cached_value("Cost Center", {"name": self.name}, "enable_distributed_cost_center")) \
+ and self.check_if_part_of_distributed_cost_center():
+ frappe.throw(_("Cannot enable Distributed Cost Center for a Cost Center already allocated in another Distributed Cost Center"))
+ if next((True for x in self.distributed_cost_center if x.cost_center == x.parent), False):
+ frappe.throw(_("Parent Cost Center cannot be added in Distributed Cost Center"))
+ if check_if_distributed_cost_center_enabled(list(x.cost_center for x in self.distributed_cost_center)):
+ frappe.throw(_("A Distributed Cost Center cannot be added in the Distributed Cost Center allocation table."))
+ else:
+ self.distributed_cost_center = []
def validate_mandatory(self):
if self.cost_center_name != self.company and not self.parent_cost_center:
@@ -43,12 +61,15 @@
return 1
def convert_ledger_to_group(self):
+ if cint(self.enable_distributed_cost_center):
+ frappe.throw(_("Cost Center with enabled distributed cost center can not be converted to group"))
+ if self.check_if_part_of_distributed_cost_center():
+ frappe.throw(_("Cost Center Already Allocated in a Distributed Cost Center cannot be converted to group"))
if self.check_gle_exists():
frappe.throw(_("Cost Center with existing transactions can not be converted to group"))
- else:
- self.is_group = 1
- self.save()
- return 1
+ self.is_group = 1
+ self.save()
+ return 1
def check_gle_exists(self):
return frappe.db.get_value("GL Entry", {"cost_center": self.name})
@@ -57,6 +78,9 @@
return frappe.db.sql("select name from `tabCost Center` where \
parent_cost_center = %s and docstatus != 2", self.name)
+ def check_if_part_of_distributed_cost_center(self):
+ return frappe.db.get_value("Distributed Cost Center", {"cost_center": self.name})
+
def before_rename(self, olddn, newdn, merge=False):
# Add company abbr if not provided
from erpnext.setup.doctype.company.company import get_name_with_abbr
@@ -100,3 +124,7 @@
if account_number and not new_account[0].isdigit():
new_account = account_number + " - " + new_account
return new_account
+
+def check_if_distributed_cost_center_enabled(cost_center_list):
+ value_list = frappe.get_list("Cost Center", {"name": ["in", cost_center_list]}, "enable_distributed_cost_center", as_list=1)
+ return next((True for x in value_list if x[0]), False)
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/cost_center/test_cost_center.py b/erpnext/accounts/doctype/cost_center/test_cost_center.py
index 8f23d90..b5fc7e3 100644
--- a/erpnext/accounts/doctype/cost_center/test_cost_center.py
+++ b/erpnext/accounts/doctype/cost_center/test_cost_center.py
@@ -22,6 +22,33 @@
self.assertRaises(frappe.ValidationError, cost_center.save)
+ def test_validate_distributed_cost_center(self):
+
+ if not frappe.db.get_value('Cost Center', {'name': '_Test Cost Center - _TC'}):
+ frappe.get_doc(test_records[0]).insert()
+
+ if not frappe.db.get_value('Cost Center', {'name': '_Test Cost Center 2 - _TC'}):
+ frappe.get_doc(test_records[1]).insert()
+
+ invalid_distributed_cost_center = frappe.get_doc({
+ "company": "_Test Company",
+ "cost_center_name": "_Test Distributed Cost Center",
+ "doctype": "Cost Center",
+ "is_group": 0,
+ "parent_cost_center": "_Test Company - _TC",
+ "enable_distributed_cost_center": 1,
+ "distributed_cost_center": [{
+ "cost_center": "_Test Cost Center - _TC",
+ "percentage_allocation": 40
+ }, {
+ "cost_center": "_Test Cost Center 2 - _TC",
+ "percentage_allocation": 50
+ }
+ ]
+ })
+
+ self.assertRaises(frappe.ValidationError, invalid_distributed_cost_center.save)
+
def create_cost_center(**args):
args = frappe._dict(args)
if args.cost_center_name:
diff --git a/erpnext/accounts/doctype/distributed_cost_center/__init__.py b/erpnext/accounts/doctype/distributed_cost_center/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/doctype/distributed_cost_center/__init__.py
diff --git a/erpnext/accounts/doctype/distributed_cost_center/distributed_cost_center.json b/erpnext/accounts/doctype/distributed_cost_center/distributed_cost_center.json
new file mode 100644
index 0000000..45b0e2d
--- /dev/null
+++ b/erpnext/accounts/doctype/distributed_cost_center/distributed_cost_center.json
@@ -0,0 +1,40 @@
+{
+ "actions": [],
+ "creation": "2020-03-19 12:34:01.500390",
+ "doctype": "DocType",
+ "editable_grid": 1,
+ "engine": "InnoDB",
+ "field_order": [
+ "cost_center",
+ "percentage_allocation"
+ ],
+ "fields": [
+ {
+ "fieldname": "cost_center",
+ "fieldtype": "Link",
+ "in_list_view": 1,
+ "label": "Cost Center",
+ "options": "Cost Center",
+ "reqd": 1
+ },
+ {
+ "fieldname": "percentage_allocation",
+ "fieldtype": "Float",
+ "in_list_view": 1,
+ "label": "Percentage Allocation",
+ "reqd": 1
+ }
+ ],
+ "istable": 1,
+ "links": [],
+ "modified": "2020-03-19 12:54:43.674655",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Distributed Cost Center",
+ "owner": "Administrator",
+ "permissions": [],
+ "quick_entry": 1,
+ "sort_field": "modified",
+ "sort_order": "DESC",
+ "track_changes": 1
+}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/distributed_cost_center/distributed_cost_center.py b/erpnext/accounts/doctype/distributed_cost_center/distributed_cost_center.py
new file mode 100644
index 0000000..48c589f
--- /dev/null
+++ b/erpnext/accounts/doctype/distributed_cost_center/distributed_cost_center.py
@@ -0,0 +1,10 @@
+# -*- coding: utf-8 -*-
+# Copyright (c) 2020, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+# import frappe
+from frappe.model.document import Document
+
+class DistributedCostCenter(Document):
+ pass
diff --git a/erpnext/accounts/report/budget_variance_report/budget_variance_report.py b/erpnext/accounts/report/budget_variance_report/budget_variance_report.py
index 05dc282..9c9ada8 100644
--- a/erpnext/accounts/report/budget_variance_report/budget_variance_report.py
+++ b/erpnext/accounts/report/budget_variance_report/budget_variance_report.py
@@ -29,37 +29,60 @@
for dimension in dimensions:
dimension_items = cam_map.get(dimension)
if dimension_items:
- for account, monthwise_data in iteritems(dimension_items):
- row = [dimension, account]
- totals = [0, 0, 0]
- for year in get_fiscal_years(filters):
- last_total = 0
- for relevant_months in period_month_ranges:
- period_data = [0, 0, 0]
- for month in relevant_months:
- if monthwise_data.get(year[0]):
- month_data = monthwise_data.get(year[0]).get(month, {})
- for i, fieldname in enumerate(["target", "actual", "variance"]):
- value = flt(month_data.get(fieldname))
- period_data[i] += value
- totals[i] += value
-
- period_data[0] += last_total
-
- if filters.get("show_cumulative"):
- last_total = period_data[0] - period_data[1]
-
- period_data[2] = period_data[0] - period_data[1]
- row += period_data
- totals[2] = totals[0] - totals[1]
- if filters["period"] != "Yearly":
- row += totals
- data.append(row)
+ data = get_final_data(dimension, dimension_items, filters, period_month_ranges, data, 0)
+ else:
+ DCC_allocation = frappe.db.sql('''SELECT parent, sum(percentage_allocation) as percentage_allocation
+ FROM `tabDistributed Cost Center`
+ WHERE cost_center IN %(dimension)s
+ AND parent NOT IN %(dimension)s
+ GROUP BY parent''',{'dimension':[dimension]})
+ if DCC_allocation:
+ filters['budget_against_filter'] = [DCC_allocation[0][0]]
+ cam_map = get_dimension_account_month_map(filters)
+ dimension_items = cam_map.get(DCC_allocation[0][0])
+ if dimension_items:
+ data = get_final_data(dimension, dimension_items, filters, period_month_ranges, data, DCC_allocation[0][1])
chart = get_chart_data(filters, columns, data)
return columns, data, None, chart
+def get_final_data(dimension, dimension_items, filters, period_month_ranges, data, DCC_allocation):
+
+ for account, monthwise_data in iteritems(dimension_items):
+ row = [dimension, account]
+ totals = [0, 0, 0]
+ for year in get_fiscal_years(filters):
+ last_total = 0
+ for relevant_months in period_month_ranges:
+ period_data = [0, 0, 0]
+ for month in relevant_months:
+ if monthwise_data.get(year[0]):
+ month_data = monthwise_data.get(year[0]).get(month, {})
+ for i, fieldname in enumerate(["target", "actual", "variance"]):
+ value = flt(month_data.get(fieldname))
+ period_data[i] += value
+ totals[i] += value
+
+ period_data[0] += last_total
+
+ if DCC_allocation:
+ period_data[0] = period_data[0]*(DCC_allocation/100)
+ period_data[1] = period_data[1]*(DCC_allocation/100)
+
+ if(filters.get("show_cumulative")):
+ last_total = period_data[0] - period_data[1]
+
+ period_data[2] = period_data[0] - period_data[1]
+ row += period_data
+ totals[2] = totals[0] - totals[1]
+ if filters["period"] != "Yearly" :
+ row += totals
+ data.append(row)
+
+ return data
+
+
def get_columns(filters):
columns = [
{
@@ -366,7 +389,7 @@
budget_values[i] += values[index]
actual_values[i] += values[index+1]
index += 3
-
+
return {
'data': {
'labels': labels,
diff --git a/erpnext/accounts/report/financial_statements.py b/erpnext/accounts/report/financial_statements.py
index 4a35a66..0339e49 100644
--- a/erpnext/accounts/report/financial_statements.py
+++ b/erpnext/accounts/report/financial_statements.py
@@ -387,11 +387,41 @@
key: value
})
+ distributed_cost_center_query = ""
+ if filters and filters.get('cost_center'):
+ distributed_cost_center_query = """
+ UNION ALL
+ SELECT posting_date,
+ account,
+ debit*(DCC_allocation.percentage_allocation/100) as debit,
+ credit*(DCC_allocation.percentage_allocation/100) as credit,
+ is_opening,
+ fiscal_year,
+ debit_in_account_currency*(DCC_allocation.percentage_allocation/100) as debit_in_account_currency,
+ credit_in_account_currency*(DCC_allocation.percentage_allocation/100) as credit_in_account_currency,
+ account_currency
+ FROM `tabGL Entry`,
+ (
+ SELECT parent, sum(percentage_allocation) as percentage_allocation
+ FROM `tabDistributed Cost Center`
+ WHERE cost_center IN %(cost_center)s
+ AND parent NOT IN %(cost_center)s
+ GROUP BY parent
+ ) as DCC_allocation
+ WHERE company=%(company)s
+ {additional_conditions}
+ AND posting_date <= %(to_date)s
+ AND cost_center = DCC_allocation.parent
+ """.format(additional_conditions=additional_conditions.replace("and cost_center in %(cost_center)s ", ''))
+
gl_entries = frappe.db.sql("""select posting_date, account, debit, credit, is_opening, fiscal_year, debit_in_account_currency, credit_in_account_currency, account_currency from `tabGL Entry`
where company=%(company)s
{additional_conditions}
and posting_date <= %(to_date)s
- order by account, posting_date""".format(additional_conditions=additional_conditions), gl_filters, as_dict=True) #nosec
+ {distributed_cost_center_query}
+ order by account, posting_date""".format(
+ additional_conditions=additional_conditions,
+ distributed_cost_center_query=distributed_cost_center_query), gl_filters, as_dict=True) #nosec
if filters and filters.get('presentation_currency'):
convert_to_presentation_currency(gl_entries, get_currency(filters))
@@ -489,4 +519,4 @@
"width": 150
})
- return columns
+ return columns
\ No newline at end of file
diff --git a/erpnext/accounts/report/general_ledger/general_ledger.py b/erpnext/accounts/report/general_ledger/general_ledger.py
index f83a259..fcd36e4 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.py
+++ b/erpnext/accounts/report/general_ledger/general_ledger.py
@@ -128,18 +128,53 @@
filters['company_fb'] = frappe.db.get_value("Company",
filters.get("company"), 'default_finance_book')
+ distributed_cost_center_query = ""
+ if filters and filters.get('cost_center'):
+ select_fields_with_percentage = """, debit*(DCC_allocation.percentage_allocation/100) as debit, credit*(DCC_allocation.percentage_allocation/100) as credit, debit_in_account_currency*(DCC_allocation.percentage_allocation/100) as debit_in_account_currency,
+ credit_in_account_currency*(DCC_allocation.percentage_allocation/100) as credit_in_account_currency """
+
+ distributed_cost_center_query = """
+ UNION ALL
+ SELECT name as gl_entry,
+ posting_date,
+ account,
+ party_type,
+ party,
+ voucher_type,
+ voucher_no,
+ cost_center, project,
+ against_voucher_type,
+ against_voucher,
+ account_currency,
+ remarks, against,
+ is_opening, `tabGL Entry`.creation {select_fields_with_percentage}
+ FROM `tabGL Entry`,
+ (
+ SELECT parent, sum(percentage_allocation) as percentage_allocation
+ FROM `tabDistributed Cost Center`
+ WHERE cost_center IN %(cost_center)s
+ AND parent NOT IN %(cost_center)s
+ GROUP BY parent
+ ) as DCC_allocation
+ WHERE company=%(company)s
+ {conditions}
+ AND posting_date <= %(to_date)s
+ AND cost_center = DCC_allocation.parent
+ """.format(select_fields_with_percentage=select_fields_with_percentage, conditions=get_conditions(filters).replace("and cost_center in %(cost_center)s ", ''))
+
gl_entries = frappe.db.sql(
"""
select
name as gl_entry, posting_date, account, party_type, party,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher, account_currency,
- remarks, against, is_opening {select_fields}
+ remarks, against, is_opening, creation {select_fields}
from `tabGL Entry`
where company=%(company)s {conditions}
+ {distributed_cost_center_query}
{order_by_statement}
""".format(
- select_fields=select_fields, conditions=get_conditions(filters),
+ select_fields=select_fields, conditions=get_conditions(filters), distributed_cost_center_query=distributed_cost_center_query,
order_by_statement=order_by_statement
),
filters, as_dict=1)
diff --git a/erpnext/accounts/report/profitability_analysis/profitability_analysis.py b/erpnext/accounts/report/profitability_analysis/profitability_analysis.py
index 6e9b31f..60e675f 100644
--- a/erpnext/accounts/report/profitability_analysis/profitability_analysis.py
+++ b/erpnext/accounts/report/profitability_analysis/profitability_analysis.py
@@ -105,6 +105,7 @@
def prepare_data(accounts, filters, total_row, parent_children_map, based_on):
data = []
+ new_accounts = accounts
company_currency = frappe.get_cached_value('Company', filters.get("company"), "default_currency")
for d in accounts:
@@ -118,6 +119,19 @@
"currency": company_currency,
"based_on": based_on
}
+ if based_on == 'cost_center':
+ cost_center_doc = frappe.get_doc("Cost Center",d.name)
+ if not cost_center_doc.enable_distributed_cost_center:
+ DCC_allocation = frappe.db.sql("""SELECT parent, sum(percentage_allocation) as percentage_allocation
+ FROM `tabDistributed Cost Center`
+ WHERE cost_center IN %(cost_center)s
+ AND parent NOT IN %(cost_center)s
+ GROUP BY parent""",{'cost_center': [d.name]})
+ if DCC_allocation:
+ for account in new_accounts:
+ if account['name'] == DCC_allocation[0][0]:
+ for value in value_fields:
+ d[value] += account[value]*(DCC_allocation[0][1]/100)
for key in value_fields:
row[key] = flt(d.get(key, 0.0), 3)