Hi. I am trying to customize the project timesheet/invoicing process and running into some issues. My timesheets will be done by employee by week. Each employee will enter a timesheet per week and this timesheet will not have the project/customer defined at the header. Each line will relate to a project/task.
I have customized the “Fetch Timesheet” button on the Sales Invoice. Previously, if a project was not entered, it would add timesheets for other customers within the timeframe.
The issue I’m having now is when some hours from a timesheet are added to the sales invoice, the entire timesheet is being marked billed. I cannot create a second invoice for the remainder of the hours.
Here are my customized fetch timesheet client and server scripts:
def fetch_project_timesheets(sales_invoice_name, from_time, to_time, customer, project=None):
#sales_invoice = frappe.get_doc("Sales Invoice", sales_invoice_name)
timesheets = []
if project:
timesheet_data = get_projectwise_timesheet_data(project)
else:
# Fetch timesheets where the project is connected to the sales invoice customer
timesheet_data = frappe.db.sql("""
SELECT
ts.name AS time_sheet,
tsd.name,
tsd.billing_hours,
tsd.billing_amount,
tsd.activity_type,
tsd.description
FROM `tabTimesheet Detail` tsd
JOIN `tabTimesheet` ts ON ts.name = tsd.parent
JOIN `tabProject` p ON p.name = tsd.project
WHERE p.customer = %s
AND tsd.from_time >= %s
AND tsd.to_time <= %s
and tsd.billing_hours > 0
and tsd.docstatus = 1
""", (customer, from_time, to_time), as_dict=True)
for data in timesheet_data:
timesheets.append({
"time_sheet": data.time_sheet,
"billing_hours": data.billing_hours,
"billing_amount": data.billing_amount,
"timesheet_detail": data.name,
"activity_type": data.activity_type,
"description": data.description,
})
return timesheets
sales_invoice_name = frappe.request.form.get('sales_invoice_name','')
xfrom = frappe.request.form.get('from_time','')
to = frappe.request.form.get('to_time','')
project = frappe.request.form.get('project','')
customer = frappe.request.form.get('customer','')
frappe.response["message"]= fetch_project_timesheets(sales_invoice_name, xfrom, to, customer,project)
Client Script:
frappe.ui.form.on('Sales Invoice', {
refresh: function (frm) {
if (frm.doc.docstatus === 0 && !frm.doc.is_return) {
frm.add_custom_button(__("Fetch Project Timesheet"), function () {
let d = new frappe.ui.Dialog({
title: __("Fetch Project Timesheet"),
fields: [
{
label: __("From"),
fieldname: "from_time",
fieldtype: "Date",
reqd: 1,
},
{
fieldtype: "Column Break",
fieldname: "col_break_1",
},
{
label: __("To"),
fieldname: "to_time",
fieldtype: "Date",
reqd: 1,
},
{
label: __("Project"),
fieldname: "project",
fieldtype: "Link",
options: "Project",
default: frm.doc.project,
},
],
primary_action: function () {
const data = d.get_values();
frappe.call({
method: "fetch_project_timesheets",
args: {
sales_invoice_name: frm.doc.name,
from_time: data.from_time,
to_time: data.to_time,
project: data.project,
customer: frm.doc.customer
},
callback: function(r) {
if (r.message) {
frm.clear_table("timesheets");
frm.clear_table("items");
let total_billing_hours = 0;
let total_billing_amount = 0;
r.message.forEach(ts => {
let new_row = frm.add_child("timesheets");
new_row.time_sheet = ts.time_sheet;
new_row.billing_hours = ts.billing_hours;
new_row.billing_amount = ts.billing_amount;
new_row.timesheet_detail = ts.timesheet_detail;
new_row.activity_type = ts.activity_type;
new_row.description = ts.description;
total_billing_hours += ts.billing_hours;
total_billing_amount += ts.billing_amount;
});
let new_item_row = frm.add_child("items");
new_item_row.item_code = 'MFG-CON'; // Replace with appropriate item code
new_item_row.item_name = 'Consulting Services'; // Replace with appropriate item name
new_item_row.description = 'Total billing amount for consulting services';
new_item_row.qty = total_billing_hours;
new_item_row.rate = total_billing_amount / total_billing_hours;
new_item_row.amount = total_billing_amount;
new_item_row.uom = 'Hour'; // Replace with appropriate UOM
new_item_row.income_account = '4110 - Sales - PARA';
frm.refresh_field("items");
frm.refresh_field("timesheets");
//frm.events.calculate_billing_amount_for_timesheet(frm.doc);
}
}
});
d.hide();
},
primary_action_label: __("Get Timesheets"),
});
d.show();
});
}
if (frm.doc.is_debit_note) {
frm.set_df_property("return_against", "label", __("Adjustment Against"));
}
}});
Thanks!