Good day
There is a bug in the project management module ( V13 an V14 ). It is on the issues list but I would like
to assist in fixing this.
Usage description…
For each project one can define a set of tasks to be performed and as these tasks are completed,
a timesheet is generated, capturing the costs … billing and costing.
Description of bug…
It works fine if the project currency is the same as the system currency. If they are not, then
the timesheet costing is correct. The task costing was incorrect, but is now correct. The project
costing is incorrect. If i say incorrect, I mean it is out by the currency relationship.
In other words… the task and project costing were both in the “foreign” currency and not in the
local/system currency. Numerically in the foreign currency, but with the symbol for the local
currency.
I have been looking at the code and made some adjustments that fixed the task costing, but the
project costing is still out by a factor equal to the currency relationship.
If someone could please give a hand to get to the last step.
Here is what I found…
In timesheet.py, on_submit, calls update_task_and_project
update_task_and_project , calls task.update_time_and_costing … and … project.update_project
In task.py…
I modified , update_time_and_costing by adding “base_” in two places in the sql query
( base_billing_amount … and … base_costing_amount
The new code looks like this…
task.py
def update_time_and_costing(self):
tl = frappe.db.sql(
"""select min(from_time) as start_date, max(to_time) as end_date,
sum(base_billing_amount) as total_billing_amount, sum(base_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]
if self.status == "Open":
self.status = "Working"
self.total_costing_amount = tl.total_costing_amount
self.total_billing_amount = tl.total_billing_amount
self.actual_time = tl.time
self.act_start_date = tl.start_date
self.act_end_date = tl.end_date
If you look at the mariadb tables, then it becomes clear that foreign currency is calculated
as “costing_amount” and that same amount is converted in the the local currency and
calculated as “base_costing_amount”. So the sql query in task.py was summing the foreign currency
columns. That fixed the task costing which now seems correct.
I found a similar problem in project.py…I added “base_” in two places…
In project.py, update_project calls update_costing and … db_update
The new code looks like this…
project.py
def update_costing(self):
from frappe.query_builder.functions import Max, Min, Sum
TimesheetDetail = frappe.qb.DocType("Timesheet Detail")
from_time_sheet = (
frappe.qb.from_(TimesheetDetail)
.select(
Sum(TimesheetDetail.base_costing_amount).as_("costing_amount"),
Sum(TimesheetDetail.base_billing_amount).as_("billing_amount"),
Min(TimesheetDetail.from_time).as_("start_date"),
Max(TimesheetDetail.to_time).as_("end_date"),
Sum(TimesheetDetail.hours).as_("time"),
)
.where((TimesheetDetail.project == self.name) & (TimesheetDetail.docstatus == 1))
).run(as_dict=True)[0]
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.actual_time = from_time_sheet.time
self.update_purchase_costing()
self.update_sales_amount()
self.update_billed_amount()
self.calculate_gross_margin()
This, however does not fix the project costing. I cannot locate the “db_update” method.
Would appreciate some assistance so that I can make this suggestion in the issues list ?