Hello Community,
I am trying to fetch total working hours of an employee in salary slip for a month.
And I learned some basics of Database API and script API
When I am trying script as below in “System Console” the output is correct.
Output :
[{‘name’: ‘HR-ATT-2023-00464’, ‘employee_name’: ‘John Peter’, ‘working_hours’: 9.47}]
I want to insert working_hours: 9.47 into a field in Salary Slip
Please suggest how to achieve this in correct way?
I tried this client script but its not working.
Reference : How to fetch a field value in another doctype - #28 by NCP
frappe.ui.form.on("Salary Slip", "employee", function(frm) {
frappe.get_list("Attendance",
filters=[['attendance_date', 'between', ['frm.doc.start_date','frm.doc.end_date']],
['employee','=','frm.doc.employee'],
['status','=','Present']],
fields=['SUM(working_hours) as total_hours'], function(value) {
// var hours = value.total_hours;
console.log("Hours ---------->",total_hours);
frm.doc.custom_total_working_hours = value.total_hours;
});
});
May I know what I am missing in it?
Hi @umarless:
Better compute data from server side.
Create a server script, API type, named attendance_total_working_hours
init_date = frappe.form_dict.init_date
end_date = frappe.form_dict.end_date
filters = {
"init_date": init_date,
"end_date": end_date
}
query_str = """select sum(working_hours)
from tabAttendance
where attendance_date between %(init_date)s and %(end_date)s"""
total_hours = frappe.db.sql(query_str, values=filters)
frappe.response['total_hours'] = total_hours
From client script, use this:
totalHours = frappe.call({
method: "attendance_total_working_hours",
args: {
init_date: frm.doc.start_date,
end_date: frm.doc.end_date
},
callback: function(r) {
frm.doc.custom_total_working_hours = totalHours.responseJSON.total_hours[0][0]
}
});
Hope this helps.
Client Script
totalHours = frappe.call({
method: "attendance_total_working_hours",
args: {
init_date: frm.doc.start_date,
end_date: frm.doc.end_date
},
callback: function(r) {
frm.doc.custom_total_working_hours = totalHours.responseJSON.total_hours[0][0]
}
});
Server Script
init_date = frappe.form_dict.init_date
end_date = frappe.form_dict.end_date
filters = {
"init_date": init_date,
"end_date": end_date
}
query_str = """select sum(working_hours)
from tabAttendance
where attendance_date between %(init_date)s and %(end_date)s"""
total_hours = frappe.db.sql(query_str, values=filters)
frappe.response['total_hours'] = total_hours
Hi @umarless:
Maybe I didn’t understand the intended behavior … do you want this logic applied after employee selection? So:
frappe.ui.form.on("Salary Slip", "employee", function(frm) {
totalHours = frappe.call({
method: "attendance_total_working_hours",
args: {
init_date: frm.doc.start_date,
end_date: frm.doc.end_date
},
callback: function(r) {
frm.doc.custom_total_working_hours = totalHours.responseJSON.total_hours[0][0]
}
});
});
Anyway, I don’t know if are there some logic collision with HRMS default behavior.
Hope this helps.
How can I add filter for employee in this filter?
I added like this below, but its not working…
May I know what I am missing
