diff --git a/erpnext/projects/doctype/project/project.py b/erpnext/projects/doctype/project/project.py index 38539130dd49280eeedc0a65b32c568f5282875b..7a241ac2fc7b92e9156718abbefbecf4b3c923e5 100644 --- a/erpnext/projects/doctype/project/project.py +++ b/erpnext/projects/doctype/project/project.py @@ -297,6 +297,8 @@ class Project(Document): Min(TimesheetDetail.from_time).as_("start_date"), Max(TimesheetDetail.to_time).as_("end_date"), Sum(TimesheetDetail.hours).as_("time"), + Sum(TimesheetDetail.base_costing_amount).as_("base_costing_amount"), + Sum(TimesheetDetail.base_billing_amount).as_("base_billing_amount"), ) .where((TimesheetDetail.project == self.name) & (TimesheetDetail.docstatus == 1)) ).run(as_dict=True)[0] @@ -304,8 +306,8 @@ class Project(Document): self.actual_start_date = from_time_sheet.start_date self.actual_end_date = from_time_sheet.end_date - self.total_costing_amount = from_time_sheet.costing_amount - self.total_billable_amount = from_time_sheet.billing_amount + self.total_costing_amount = from_time_sheet.base_costing_amount + self.total_billable_amount = from_time_sheet.base_billing_amount self.actual_time = from_time_sheet.time self.update_purchase_costing() diff --git a/erpnext/projects/doctype/project/test_project.py b/erpnext/projects/doctype/project/test_project.py index 8caaa756862de023d5138468c340fb85926a67ac..6c9082bbdd4f289a1b94cd7c7cd4bf5d45e97141 100644 --- a/erpnext/projects/doctype/project/test_project.py +++ b/erpnext/projects/doctype/project/test_project.py @@ -20,6 +20,26 @@ class TestProject(ERPNextTestSuite): super().setUpClass() cls.make_projects() + def test_project_total_costing_and_billing_amount(self): + from erpnext.projects.doctype.timesheet.test_timesheet import make_timesheet + from erpnext.setup.doctype.employee.test_employee import make_employee + + project_name = "Test Project Costing" + employee = make_employee("employee@frappe.io") + project = make_project({"project_name": project_name}) + timesheet = make_timesheet( + employee=employee, + is_billable=1, + currency="USD", + project=project.name, + simulate=True, + exchange_rate=80, + ) + timesheet.reload() + project.reload() + self.assertEqual(project.total_costing_amount, 3200) + self.assertEqual(project.total_billable_amount, 8000) + def test_project_with_template_having_no_parent_and_depend_tasks(self): project_name = "Test Project with Template - No Parent and Dependend Tasks" frappe.db.sql(""" delete from tabTask where project = %s """, project_name) diff --git a/erpnext/projects/doctype/task/task.py b/erpnext/projects/doctype/task/task.py index 00fbd1ee66f4b911f087e67cbed067c93579d386..cc16dc7aa9dbf08b0fed75c3ca506c163747964f 100755 --- a/erpnext/projects/doctype/task/task.py +++ b/erpnext/projects/doctype/task/task.py @@ -8,7 +8,8 @@ import frappe from frappe import _, throw from frappe.desk.form.assign_to import clear, close_all_assignments from frappe.model.mapper import get_mapped_doc -from frappe.utils import ( +from frappe.query_builder.functions import Max, Min, Sum +from frappe.utils import ( # @dokos add_days, add_to_date, cstr, @@ -222,15 +223,22 @@ class Task(NestedSet): clear(self.doctype, self.name) def update_time_and_costing(self): - tl = frappe.db.sql( - """select min(from_time) as start_date, max(to_time) as end_date, - sum(billing_amount) as total_billing_amount, sum(costing_amount) as total_costing_amount, - sum(hours) as time from `tabTimesheet Detail` where task = %s and docstatus=1""", - self.name, - as_dict=1, - )[0] - self.total_costing_amount = tl.total_costing_amount - self.total_billing_amount = tl.total_billing_amount + TimesheetDetail = frappe.qb.DocType("Timesheet Detail") + tl = ( + frappe.qb.from_(TimesheetDetail) + .select( + Min(TimesheetDetail.from_time).as_("start_date"), + Max(TimesheetDetail.to_time).as_("end_date"), + Sum(TimesheetDetail.billing_amount).as_("total_billing_amount"), + Sum(TimesheetDetail.costing_amount).as_("total_costing_amount"), + Sum(TimesheetDetail.hours).as_("time"), + Sum(TimesheetDetail.base_costing_amount).as_("base_costing_amount"), + Sum(TimesheetDetail.base_billing_amount).as_("base_billing_amount"), + ) + .where((TimesheetDetail.task == self.name) & (TimesheetDetail.docstatus == 1)) + ).run(as_dict=True)[0] + self.total_costing_amount = tl.base_costing_amount + self.total_billing_amount = tl.base_billing_amount self.actual_time = tl.time self.act_start_date = tl.start_date self.act_end_date = tl.end_date diff --git a/erpnext/projects/doctype/task/test_task.py b/erpnext/projects/doctype/task/test_task.py index 002a591debce1fcdff7fff259372265e520ea01c..410c4a538828676e9ef851529750d1cb0fa426e3 100644 --- a/erpnext/projects/doctype/task/test_task.py +++ b/erpnext/projects/doctype/task/test_task.py @@ -1,6 +1,5 @@ # Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors # License: GNU General Public License v3. See license.txt -import unittest import frappe from frappe.tests import IntegrationTestCase @@ -16,6 +15,32 @@ class TestTask(ERPNextTestSuite): super().setUpClass() cls.make_projects() + def test_task_total_costing_and_billing_amount(self): + from erpnext.projects.doctype.project.test_project import make_project + from erpnext.projects.doctype.timesheet.test_timesheet import make_timesheet + from erpnext.setup.doctype.employee.test_employee import make_employee + + project_name = "Test Project Costing" + employee = make_employee("employee@frappe.io") + project = make_project({"project_name": project_name}) + task = create_task("_Test Task 1") + task.project = project.name + task.save() + timesheet = make_timesheet( + employee=employee, + is_billable=1, + currency="USD", + project=project.name, + simulate=True, + exchange_rate=80, + task=task.name, + ) + timesheet.reload() + project.reload() + task.reload() + self.assertEqual(task.total_costing_amount, 3200) + self.assertEqual(task.total_billing_amount, 8000) + def test_circular_reference(self): task1 = create_task("_Test Task 1", add_days(nowdate(), -15), add_days(nowdate(), -10)) task2 = create_task("_Test Task 2", add_days(nowdate(), 11), add_days(nowdate(), 15), task1.name) diff --git a/erpnext/projects/doctype/timesheet/test_timesheet.py b/erpnext/projects/doctype/timesheet/test_timesheet.py index f623809b237ffab03f8d2e275d2e82aeb95e683e..91ab5ab0df5f330c40f1ee8f7b4eb0177c182acf 100644 --- a/erpnext/projects/doctype/timesheet/test_timesheet.py +++ b/erpnext/projects/doctype/timesheet/test_timesheet.py @@ -217,11 +217,14 @@ def make_timesheet( project=None, task=None, company=None, + currency=None, + exchange_rate=None, ): update_activity_type(activity_type) timesheet = frappe.new_doc("Timesheet") timesheet.employee = employee timesheet.company = company or "_Test Company" + timesheet.exchange_rate = exchange_rate timesheet_detail = timesheet.append("time_logs", {}) timesheet_detail.is_billable = is_billable timesheet_detail.activity_type = activity_type @@ -230,6 +233,7 @@ def make_timesheet( timesheet_detail.to_time = timesheet_detail.from_time + datetime.timedelta(hours=timesheet_detail.hours) timesheet_detail.project = project timesheet_detail.task = task + timesheet_detail.currency = currency for data in timesheet.get("time_logs"): if simulate: