Calculation of Late Entries and Deduction of Salary

kid1194 · August 13, 2022, 9:58pm

@Past_Papers I think that I found a solution.

Customize the Salary Slip doctype and add a new int read only field called number_of_late_days
Create a Client Script for Salary Slip form and use the below code
Create a Salary Component and in the condition put number_of_late_days > 2 and in the formula put (number_of_late_days - 2) * 100
In the Salary Slip add the Salary Component you created for deduction.

frappe.ui.form.on('Salary Slip', {
    employee: function(frm) {
        frm.trigger('update_number_of_late_days');
    },
    start_date: function(frm) {
        frm.trigger('update_number_of_late_days');
    },
    end_date: function(frm) {
        frm.trigger('update_number_of_late_days');
    },
    update_number_of_late_days: function(frm) {
        if (!frm.is_new() && !frm.is_dirty()) return;
        if (!frm.doc.employee || !frm.doc.start_date || !frm.doc.end_date) return;
        frappe.db.get_list('Attendance', {
            fields: ['name'],
            filters: {
                employee: frm.doc.employee,
                late_entry: 1,
                attendance_date: ['between', [frm.doc.start_date, frm.doc.end_date]]
            }
        }).then(ret => {
            let count = ret.length, late_days = cint(frm.doc.number_of_late_days);
            if (count === late_days) return;
            frm.set_value('number_of_late_days', count);
            if (!frm.is_new()) {
                frm.dirty();
                frm.save();
            }
        });
    }
});

The following code will set the number of late days between the start and end dates of the Salary Slip.

Bait · August 14, 2022, 8:09am

How to calculate the number of hours and minutes of delay within a field using Client Script ?
Taking into account the number of minutes allowed inside the shift

Calculating the number of hours and minutes overtime using Client Script

kid1194 · August 14, 2022, 12:38pm

@Bait Assuming that the fieldnames are late_hours and late_minutes, the code is:

frappe.ui.form.on('Attendance', {
    shift: function(frm) {
        if (!frm.is_new() && !frm.is_dirty()) return;
        if (frm.doc.shift) {
            frappe.db.get_value('Shift Type', frm.doc.shift, ['start_time', 'enable_entry_grace_period', 'late_entry_grace_period'])
            .then(r => {
                let data = r.message,
                start_dt = moment(data.start_time, frappe.defaultTimeFormat),
                now_dt = moment();
                if (data.enable_entry_grace_period && data.late_entry_grace_period) {
                    start_dt.add(cint(data.late_entry_grace_period), 'minutes');
                }
                frm.set_value('late_hours', now_dt.diff(start_dt, 'hours'));
                frm.set_value('late_minutes', now_dt.diff(start_dt, 'minutes'));
            });
        } else {
            frm.set_value('late_hours', 0);
            frm.set_value('late_minutes', 0);
        }
    }
});

It will be triggered when selecting or changing the shift.

And regarding the overtime, please create a new post and explain in details what do you want to do and I will try my best to help.

kid1194 · August 14, 2022, 12:42pm

@Past_Papers Please set the post in the qoute as the solution for future reference for others.

Bait · August 14, 2022, 12:44pm

Thank you for your great interest and effort

Is it possible to display hours and minutes as the number of working hours within one field?
Example 8.7

kid1194 · August 14, 2022, 12:49pm

@Bait Assuming that the fieldname is late_working_hours, the code is:

frappe.ui.form.on('Attendance', {
    shift: function(frm) {
        if (!frm.is_new() && !frm.is_dirty()) return;
        if (frm.doc.shift) {
            frappe.db.get_value('Shift Type', frm.doc.shift, ['start_time', 'enable_entry_grace_period', 'late_entry_grace_period'])
            .then(r => {
                let data = r.message,
                start_dt = moment(data.start_time, frappe.defaultTimeFormat),
                now_dt = moment();
                if (data.enable_entry_grace_period && data.late_entry_grace_period) {
                    start_dt.add(cint(data.late_entry_grace_period), 'minutes');
                }
                let late_working_hours = now_dt.diff(start_dt, 'hours', true);
                frm.set_value('late_working_hours', late_working_hours > 0 ? late_working_hours : 0);
            });
        } else {
            frm.set_value('late_working_hours', 0);
        }
    }
});

This will display ot as a float but if you want one number after decimal, you will have to round up or down the value.

Bait · August 14, 2022, 2:45pm

Thank you for your interest
I tried the code but it gives 0
Although in the picture
The late time should be 0.15

kid1194 · August 14, 2022, 3:45pm

@Bait I misunderstood you when you wanted a decimal value. To make things clear, based on the image you posted, the decimal value is of the following format: {late_hours}.{late_minutes}.

Example:
If late_hours = 1 and late_minutes = 25, the value becomes 1.25

If so, then use the following code:

frappe.ui.form.on('Attendance', {
    shift: function(frm) {
        if (!frm.is_new() && !frm.is_dirty()) return;
        if (frm.doc.shift) {
            frappe.db.get_value('Shift Type', frm.doc.shift, ['start_time', 'enable_entry_grace_period', 'late_entry_grace_period'])
            .then(r => {
                let data = r.message,
                start_dt = moment(data.start_time, frappe.defaultTimeFormat),
                now_dt = moment();
                if (data.enable_entry_grace_period && data.late_entry_grace_period) {
                    start_dt.add(cint(data.late_entry_grace_period), 'minutes');
                }
                let late_working_hours = flt(now_dt.diff(start_dt, 'hours') + '.' + now_dt.diff(start_dt, 'minutes'));
                frm.set_value('late_working_hours', late_working_hours > 0 ? late_working_hours : 0);
            });
        } else {
            frm.set_value('late_working_hours', 0);
        }
    }
});

Or use the following code if you want to compare the shift start time against the attendance in time, assuming it’s fieldname is in_time:

frappe.ui.form.on('Attendance', {
    shift: function(frm) {
        if (!frm.is_new() && !frm.is_dirty()) return;
        if (frm.doc.shift) {
            frappe.db.get_value('Shift Type', frm.doc.shift, ['start_time', 'enable_entry_grace_period', 'late_entry_grace_period'])
            .then(r => {
                let data = r.message,
                start_dt = moment(data.start_time, frappe.defaultTimeFormat),
                in_dt = moment(frm.doc.in_time, frappe.defaultDatetimeFormat);
                if (data.enable_entry_grace_period && data.late_entry_grace_period) {
                    start_dt.add(cint(data.late_entry_grace_period), 'minutes');
                }
                let late_working_hours = flt(in_dt.diff(start_dt, 'hours') + '.' + in_dt.diff(start_dt, 'minutes'));
                frm.set_value('late_working_hours', late_working_hours > 0 ? late_working_hours : 0);
            });
        } else {
            frm.set_value('late_working_hours', 0);
        }
    }
});

Important Note
The code will be triggered if all the following conditions are met:

The shift value has changed
The form is new or modified and not yet saved

Past_Papers · August 14, 2022, 5:44pm

Thanks… This code works great and calculate the number of late days correctly but

Create a Salary Component and in the condition put number_of_late_days > 2 and in the formula put (number_of_late_days - 2) * 100

I didn’t understand this condition and formula so could you please guide me how it works because when I am trying this it gives wrong output.

Past_Papers · August 14, 2022, 7:40pm

One more thing is it possible to write a condition in salary component like:
If number_of_late_days <=2 there will be no deduction in salary but when number_of_late_days = 3 deduct 100 and if number_of_late_days = 4 deduct 200

kid1194 · August 14, 2022, 8:16pm

@Past_Papers Sorry for the late reply.

Create a new Salary Component
In the condition field, add the following:

number_of_late_days > 2

This means that the formula will not be used unless the condition above is met.

In the formula field, add the following:

(number_of_late_days - 2) * 100

What this formula will do is getting the value of number_of_late_days field from Salary Slip, reduce the value by 2 (the allowed number of late days) and finally multiply the result by 100 (the amount to be deducted per late day)

In the Salary Slip, add this component as a deduction component.

You will be able to see the result of the formula, which will be deducted from the base salary

kid1194 · August 14, 2022, 8:28pm

@Past_Papers Regarding your latest post, what you can do is create two separate components, the first with the condition and formula as in the above post and the other similar to the first but the condition is like this:

number_of_late_days > 3

Add both components to the Salary Slip, the first will deduct 100 for each late day if more than 2 and the second will deduct an extra 100 for each late day if more than 3. This will result in 100 deduction for 3 late days and 200 for 4 late days and more.

If you don’t want the second to double the deduction for the 3rd day, then you can set the formula to:

(number_of_late_days - 3) * 100

This will make the deduction like this:

1st = 0
2nd = 0
3rd = 100
4th = 200
5th = 200

Total deduction: 500 for 5 late days

Past_Papers · August 15, 2022, 8:10am

Thanks for all your help and guidance…

0 if number_of_late_days <= 2 else 100 if number_of_late_days == 3 else 200 if number_of_late_days == 4 else 300

I have tried this formula in salary component and it worked for me, but there is only one issue which is:

Let’s say if an employee is late 4 days in a month and absent on 1 day his Base Salary is 28000 so system first deduct one day salary because he is absent for one day and then should deduct 200 from his salary due to 4 late entries but what happened in my case it didn’t deduct one day absent salary and deduct 192.31 due to 4 late days but system should deduct 200 instead of 192.31 for late days…

See in this picture…

but it is calculating correctly without absent mark

Past_Papers · August 15, 2022, 11:12am

Add both components to the `Salary Slip`, the first will deduct 100 for each late day if more than 2 and the second will deduct an extra 100 for each late day if more than 3. This will result in 100 deduction for 3 late days and 200 for 4 late days and more.

If you don’t want the second to double the deduction for the 3rd day, then you can set the formula to:
(number_of_late_days - 3) * 100

First thing Absent deduction now resolved it’s my fault but still facing issue with number of late days deduction I also tried with your formula it gives the same result as I mentioned above

kid1194 · August 15, 2022, 1:15pm

@Past_Papers I believe that the 192.31 result you are getting because of some options in the Salary Component.
Make sure that all the options are unchecked, like Depends on Payment Days.

The two components you have created doesn’t depend on any of the options.

Past_Papers · August 15, 2022, 4:16pm

Thanks… I’ll check this…

There is one more issue which is:

When I am creating Payroll Entry and create Salary Slip from there it can not fetch number of late days due to which it didn’t compute salary deduction… Can you please help me how can I fetch values when creating Salary slip from Payroll Entry?

See in this picture…

kid1194 · August 15, 2022, 5:33pm

@Past_Papers The code will only trigger if the form is new or has some changes and hasn’t been saved yet. So, just make any change in the start date or end date and it will be triggered.

Here is a suggestion:

If you don’t want to do any change, you can add a button that is called get_number_of_late_days, place it under the field number_of_late_days and use the code below. When the button is clicked, it will trigger the function and it will bypass the form status check.

The function has two checks:

If the form is new or it has some unsaved changes
If the fields (employee, start_date, end_date) have a value

The button will bypass the first check and it will only be visible if the form is not new and has been saved.

frappe.ui.form.on('Salary Slip', {
    refresh: function(frm) {
        frm.toggle_display('get_number_of_late_days', !frm.is_new());
    },
    employee: function(frm) {
        frm.trigger('update_number_of_late_days');
    },
    start_date: function(frm) {
        frm.trigger('update_number_of_late_days');
    },
    end_date: function(frm) {
        frm.trigger('update_number_of_late_days');
    },
    get_number_of_late_days: function(frm) {
        frm.trigger('sync_number_of_late_days');
    },
    update_number_of_late_days: function(frm) {
        if (!frm.is_new() && !frm.is_dirty()) return;
        frm.trigger('sync_number_of_late_days');
    },
    sync_number_of_late_days: function(frm) {
        if (!frm.doc.employee || !frm.doc.start_date || !frm.doc.end_date) return;
        frappe.db.get_list('Attendance', {
            fields: ['name'],
            filters: {
                employee: frm.doc.employee,
                late_entry: 1,
                attendance_date: ['between', [frm.doc.start_date, frm.doc.end_date]]
            }
        }).then(ret => {
            let count = ret.length, late_days = cint(frm.doc.number_of_late_days);
            if (count === late_days) return;
            frm.set_value('number_of_late_days', count);
            if (!frm.is_new()) {
                frm.dirty();
                frm.save();
            }
        });
    }
});

Past_Papers · August 16, 2022, 9:05am

The code will only trigger if the form is new or has some changes and hasn’t been saved yet. So, just make any change in the start date or end date and it will be triggered.

I understand your point that I have to make change in start date or end date and it works but the problem is: there are many employees in company so I have to use payroll entry and due to this I have to change each employee’s date in salary slip which takes much time so that’s why I am asking is there any way to create Salary slip from Payroll Entry with this functionality?


If you don’t want to do any change, you can add a button that is called **get_number_of_late_days**, place it under the field **number_of_late_days** and use the code below. When the button is clicked, it will trigger the function and it will bypass the form status check.

The function has two checks:

1. If the form is new or it has some unsaved changes
2. If the fields (employee, start_date, end_date) have a value

The button will bypass the first check and it will only be visible if the form is not new and has been saved.

Means I have to create a button and when creating salary slip it will not fetch number of late days until I press this button… Am I right?

kid1194 · August 16, 2022, 10:03pm

Yes, that’s right. I can make it auto but for better results it must be this way.

But if you don’t want to even press a button, then use the following code. Create the button for manual update, just in case you needed to update the numberof late days when it’s value is not zero and none of the required fields have been changed.

frappe.ui.form.on('Salary Slip', {
    refresh: function(frm) {
        if (cint(frm.doc.number_of_late_days) === 0) frm.trigger('sync_number_of_late_days');
        frm.toggle_display('get_number_of_late_days', !frm.is_new());
    },
    employee: function(frm) {
        frm.trigger('update_number_of_late_days');
    },
    start_date: function(frm) {
        frm.trigger('update_number_of_late_days');
    },
    end_date: function(frm) {
        frm.trigger('update_number_of_late_days');
    },
    get_number_of_late_days: function(frm) {
        frm.trigger('sync_number_of_late_days');
    },
    update_number_of_late_days: function(frm) {
        if (!frm.is_new() && !frm.is_dirty()) return;
        frm.trigger('sync_number_of_late_days');
    },
    sync_number_of_late_days: function(frm) {
        if (!frm.doc.employee || !frm.doc.start_date || !frm.doc.end_date) return;
        frappe.db.get_list('Attendance', {
            fields: ['name'],
            filters: {
                employee: frm.doc.employee,
                late_entry: 1,
                attendance_date: ['between', [frm.doc.start_date, frm.doc.end_date]]
            }
        }).then(ret => {
            let count = ret.length, late_days = cint(frm.doc.number_of_late_days);
            if (count === late_days) return;
            frm.set_value('number_of_late_days', count);
            if (!frm.is_new()) {
                frm.dirty();
                frm.save();
            }
        });
    }
});

The code will work as follows:

After the form is rendered, it will check if the value of number_of_late_days is zero, it will call the function
The update function will be triggered when any of the fields employee, start_date and end_date changes
The update function will check if the values of employee, start_date and end_date are available, it will update the number of late days

Past_Papers · August 19, 2022, 6:08am

Thanks… It works