Project Margin Calculation Improvement (#11911)
* Project Margin Calculation Improvement
* Documentation modification
* Change Total Planned Sales to Total Sales Amount
* Change documentation screenshot
diff --git a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
index a91b4e1..76b50c2 100644
--- a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
+++ b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
@@ -143,6 +143,7 @@
self.update_time_sheet(self.name)
self.update_current_month_sales()
+ self.update_project()
def validate_pos_paid_amount(self):
if len(self.payments) == 0 and self.is_pos:
@@ -181,6 +182,7 @@
frappe.db.set(self, 'status', 'Cancelled')
self.update_current_month_sales()
+ self.update_project()
def update_current_month_sales(self):
if frappe.flags.in_test:
@@ -912,6 +914,13 @@
serial_no, sales_invoice
)))
+ def update_project(self):
+ if self.project:
+ project = frappe.get_doc("Project", self.project)
+ project.flags.dont_sync_tasks = True
+ project.update_billed_amount()
+ project.save()
+
def get_list_context(context=None):
from erpnext.controllers.website_list_for_contact import get_list_context
list_context = get_list_context(context)
@@ -991,4 +1000,4 @@
def set_account_for_mode_of_payment(self):
for data in self.payments:
if not data.account:
- data.account = get_bank_cash_account(data.mode_of_payment, self.company).get("account")
\ No newline at end of file
+ data.account = get_bank_cash_account(data.mode_of_payment, self.company).get("account")
diff --git a/erpnext/docs/assets/img/project/project_costing.png b/erpnext/docs/assets/img/project/project_costing.png
index 997e613..c55976d 100644
--- a/erpnext/docs/assets/img/project/project_costing.png
+++ b/erpnext/docs/assets/img/project/project_costing.png
Binary files differ
diff --git a/erpnext/docs/user/manual/en/projects/project.md b/erpnext/docs/user/manual/en/projects/project.md
index d835295..e1e936b 100644
--- a/erpnext/docs/user/manual/en/projects/project.md
+++ b/erpnext/docs/user/manual/en/projects/project.md
@@ -81,13 +81,13 @@
###Project Costing
-The Project Costing section helps you track the time and expenses incurred against the project.
+The Project Costing section helps you track the time, expenses and purchases incurred against the project.
<img class="screenshot" alt="Project - Costing" src="/docs/assets/img/project/project_costing.png">
-* The Costing Section is updated based on Time Logs made.
+* The Total Cost is composed of the costing amount from timesheets, the total cost from expense claims and the total cost from purchase invoices created against this project.
-* Gross Margin is the difference between Total Costing Amount and Total Billing Amount
+* The Gross Margin is the difference between Total Billed Amount and the Total Cost Amount for this project.
###Billing
diff --git a/erpnext/projects/doctype/project/project.json b/erpnext/projects/doctype/project/project.json
index 101e4ff..5d95bd3 100644
--- a/erpnext/projects/doctype/project/project.json
+++ b/erpnext/projects/doctype/project/project.json
@@ -893,7 +893,7 @@
"in_global_search": 0,
"in_list_view": 0,
"in_standard_filter": 0,
- "label": "Total Costing Amount (via Time Logs)",
+ "label": "Total Costing Amount (via Timesheets)",
"length": 0,
"no_copy": 0,
"permlevel": 0,
@@ -945,6 +945,36 @@
"bold": 0,
"collapsible": 0,
"columns": 0,
+ "fieldname": "total_purchase_cost",
+ "fieldtype": "Currency",
+ "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": "Total Purchase Cost (via Purchase Invoice)",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "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,
+ "unique": 0
+ },
+ {
+ "allow_bulk_edit": 0,
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
"fieldname": "company",
"fieldtype": "Link",
"hidden": 0,
@@ -975,36 +1005,6 @@
"bold": 0,
"collapsible": 0,
"columns": 0,
- "fieldname": "cost_center",
- "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": "Default Cost Center",
- "length": 0,
- "no_copy": 0,
- "options": "Cost Center",
- "permlevel": 0,
- "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,
- "unique": 0
- },
- {
- "allow_bulk_edit": 0,
- "allow_on_submit": 0,
- "bold": 0,
- "collapsible": 0,
- "columns": 0,
"fieldname": "column_break_28",
"fieldtype": "Column Break",
"hidden": 0,
@@ -1034,8 +1034,38 @@
"bold": 0,
"collapsible": 0,
"columns": 0,
+ "fieldname": "total_sales_amount",
+ "fieldtype": "Currency",
+ "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": "Total Sales Amount (via Sales Order)",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "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,
+ "unique": 0
+ },
+ {
+ "allow_bulk_edit": 0,
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
"description": "",
- "fieldname": "total_billing_amount",
+ "fieldname": "total_billable_amount",
"fieldtype": "Currency",
"hidden": 0,
"ignore_user_permissions": 0,
@@ -1044,7 +1074,7 @@
"in_global_search": 0,
"in_list_view": 0,
"in_standard_filter": 0,
- "label": "Total Billing Amount (via Time Logs)",
+ "label": "Total Billable Amount (via Timesheets)",
"length": 0,
"no_copy": 0,
"permlevel": 0,
@@ -1065,7 +1095,7 @@
"bold": 0,
"collapsible": 0,
"columns": 0,
- "fieldname": "total_purchase_cost",
+ "fieldname": "total_billed_amount",
"fieldtype": "Currency",
"hidden": 0,
"ignore_user_permissions": 0,
@@ -1074,7 +1104,7 @@
"in_global_search": 0,
"in_list_view": 0,
"in_standard_filter": 0,
- "label": "Total Purchase Cost (via Purchase Invoice)",
+ "label": "Total Billed Amount (via Sales Invoices)",
"length": 0,
"no_copy": 0,
"permlevel": 0,
@@ -1095,8 +1125,8 @@
"bold": 0,
"collapsible": 0,
"columns": 0,
- "fieldname": "total_sales_cost",
- "fieldtype": "Currency",
+ "fieldname": "cost_center",
+ "fieldtype": "Link",
"hidden": 0,
"ignore_user_permissions": 0,
"ignore_xss_filter": 0,
@@ -1104,14 +1134,14 @@
"in_global_search": 0,
"in_list_view": 0,
"in_standard_filter": 0,
- "label": "Total Sales Cost (via Sales Order)",
+ "label": "Default Cost Center",
"length": 0,
"no_copy": 0,
+ "options": "Cost Center",
"permlevel": 0,
- "precision": "",
"print_hide": 0,
"print_hide_if_no_value": 0,
- "read_only": 1,
+ "read_only": 0,
"remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
@@ -1255,7 +1285,7 @@
"issingle": 0,
"istable": 0,
"max_attachments": 4,
- "modified": "2017-07-26 14:36:20.857673",
+ "modified": "2017-12-10 08:40:46.843201",
"modified_by": "Administrator",
"module": "Projects",
"name": "Project",
diff --git a/erpnext/projects/doctype/project/project.py b/erpnext/projects/doctype/project/project.py
index 460ddc6..979c4fc 100644
--- a/erpnext/projects/doctype/project/project.py
+++ b/erpnext/projects/doctype/project/project.py
@@ -1,4 +1,4 @@
-# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
+# Copyright (c) 2017, Frappe Technologies Pvt. Ltd. and Contributors
# License: GNU General Public License v3. See license.txt
from __future__ import unicode_literals
@@ -25,6 +25,8 @@
from `tabTimesheet Detail` where project=%s and docstatus < 2 group by activity_type
order by total_hours desc''', self.name, as_dict=True))
+ self.update_costing()
+
def __setup__(self):
self.onload()
@@ -68,7 +70,7 @@
if self.expected_start_date and self.expected_end_date:
if getdate(self.expected_end_date) < getdate(self.expected_start_date):
frappe.throw(_("Expected End Date can not be less than Expected Start Date"))
-
+
def validate_weights(self):
sum = 0
for task in self.tasks:
@@ -174,28 +176,37 @@
self.actual_end_date = from_time_sheet.end_date
self.total_costing_amount = from_time_sheet.costing_amount
- self.total_billing_amount = from_time_sheet.billing_amount
+ self.total_billable_amount = from_time_sheet.billing_amount
self.actual_time = from_time_sheet.time
self.total_expense_claim = from_expense_claim.total_sanctioned_amount
+ self.update_purchase_costing()
+ self.update_sales_amount()
+ self.update_billed_amount()
- self.gross_margin = flt(self.total_billing_amount) - flt(self.total_costing_amount)
+ self.gross_margin = flt(self.total_billed_amount) - (flt(self.total_costing_amount) + flt(self.total_expense_claim) + flt(self.total_purchase_cost))
- if self.total_billing_amount:
- self.per_gross_margin = (self.gross_margin / flt(self.total_billing_amount)) *100
+ if self.total_billed_amount:
+ self.per_gross_margin = (self.gross_margin / flt(self.total_billed_amount)) *100
def update_purchase_costing(self):
total_purchase_cost = frappe.db.sql("""select sum(base_net_amount)
from `tabPurchase Invoice Item` where project = %s and docstatus=1""", self.name)
self.total_purchase_cost = total_purchase_cost and total_purchase_cost[0][0] or 0
-
- def update_sales_costing(self):
- total_sales_cost = frappe.db.sql("""select sum(base_grand_total)
+
+ def update_sales_amount(self):
+ total_sales_amount = frappe.db.sql("""select sum(base_grand_total)
from `tabSales Order` where project = %s and docstatus=1""", self.name)
- self.total_sales_cost = total_sales_cost and total_sales_cost[0][0] or 0
-
+ self.total_sales_amount = total_sales_amount and total_sales_amount[0][0] or 0
+
+ def update_billed_amount(self):
+ total_billed_amount = frappe.db.sql("""select sum(base_grand_total)
+ from `tabSales Invoice` where project = %s and docstatus=1""", self.name)
+
+ self.total_billed_amount = total_billed_amount and total_billed_amount[0][0] or 0
+
def send_welcome_email(self):
url = get_url("/project/?name={0}".format(self.name))
@@ -219,7 +230,7 @@
self.load_tasks()
self.sync_tasks()
self.update_dependencies_on_duplicated_project()
-
+
def update_dependencies_on_duplicated_project(self):
if self.flags.dont_sync_tasks: return
if not self.copied_from:
@@ -289,10 +300,10 @@
def get_users_for_project(doctype, txt, searchfield, start, page_len, filters):
conditions = []
- return frappe.db.sql("""select name, concat_ws(' ', first_name, middle_name, last_name)
+ return frappe.db.sql("""select name, concat_ws(' ', first_name, middle_name, last_name)
from `tabUser`
where enabled=1
- and name not in ("Guest", "Administrator")
+ and name not in ("Guest", "Administrator")
and ({key} like %(txt)s
or full_name like %(txt)s)
{fcond} {mcond}
diff --git a/erpnext/projects/doctype/project/test_project.js b/erpnext/projects/doctype/project/test_project.js
new file mode 100644
index 0000000..16494f6
--- /dev/null
+++ b/erpnext/projects/doctype/project/test_project.js
@@ -0,0 +1,23 @@
+/* eslint-disable */
+// rename this file from _test_[name] to test_[name] to activate
+// and remove above this line
+
+QUnit.test("test: Project", function (assert) {
+ let done = assert.async();
+
+ // number of asserts
+ assert.expect(1);
+
+ frappe.run_serially([
+ // insert a new Project
+ () => frappe.tests.make('Project', [
+ // values to be set
+ {key: 'value'}
+ ]),
+ () => {
+ assert.equal(cur_frm.doc.key, 'value');
+ },
+ () => done()
+ ]);
+
+});
diff --git a/erpnext/selling/doctype/sales_order/sales_order.py b/erpnext/selling/doctype/sales_order/sales_order.py
index bb6b0a0..573e9d9 100644
--- a/erpnext/selling/doctype/sales_order/sales_order.py
+++ b/erpnext/selling/doctype/sales_order/sales_order.py
@@ -110,7 +110,7 @@
for d in self.get("items"):
if not d.delivery_date:
d.delivery_date = self.delivery_date
-
+
if getdate(self.transaction_date) > getdate(d.delivery_date):
frappe.msgprint(_("Expected Delivery Date should be after Sales Order Date"),
indicator='orange', title=_('Warning'))
@@ -191,7 +191,7 @@
if self.project:
project = frappe.get_doc("Project", self.project)
project.flags.dont_sync_tasks = True
- project.update_sales_costing()
+ project.update_sales_amount()
project.save()
project_list.append(self.project)
@@ -492,7 +492,7 @@
target.ignore_pricing_rule = 1
target.run_method("set_missing_values")
target.run_method("calculate_taxes_and_totals")
-
+
# set company address
target.update(get_company_address(target.company))
if target.company_address:
@@ -820,4 +820,4 @@
order_by='is_default desc')
bom = bom[0].name if bom else None
- return bom
\ No newline at end of file
+ return bom
