Attendance Overtime

Should we not implement this for companies in need of processing Overtime on Salaries?

If overtime ticked user inputs the hours and this should be approved before submitting …
Those hours would serve for calculating the OV on the salary.

1 Like

A nice hack would be to auto calculate the overtime from the check_in and chcek_out and use that to display “Overtime(Hours)” in a custom report.


To achieve the above result in V-12, you need to create a new Script Report duplicating the standard “Monthly Attendance Sheet” and overwrite the python file with the below customized version:


from __future__ import unicode_literals
import frappe
from frappe.utils import cstr, cint, getdate
from frappe import msgprint, _
from calendar import monthrange


def execute(filters=None):
    if not filters:
        filters = {}

    conditions, filters = get_conditions(filters)
    columns = get_columns(filters)
    att_map = get_attendance_list(conditions, filters)
    emp_map = get_employee_details(filters)
    shift_duration_dict = {}

    holiday_list = [emp_map[d]["holiday_list"]
                    for d in emp_map if emp_map[d]["holiday_list"]]
    default_holiday_list = frappe.get_cached_value(
        'Company',  filters.get("company"),  "default_holiday_list")
    holiday_list.append(default_holiday_list)
    holiday_list = list(set(holiday_list))
    holiday_map = get_holiday(holiday_list, filters["month"])

    data = []
    leave_types = frappe.db.sql(
        """select name from `tabLeave Type`""", as_list=True)
    leave_list = [d[0] for d in leave_types]
    columns.extend(leave_list)
    columns.extend([_("Employee Name") + "::10"])

    for emp in sorted(att_map):
        emp_det = emp_map.get(emp)
        if not emp_det:
            continue

        # row = [emp, emp_det.employee_name, emp_det.branch, emp_det.department, emp_det.designation,
        #        emp_det.company]
        row = [emp]
        status_row = []
        total_p = total_a = total_l = total_ot = 0.0
        for day in range(filters["total_days_in_month"]):
            att_map_list = att_map.get(emp).get(day + 1, ["None", None, 0])
            [status, shift_type, working_hours] = att_map_list
            status_map = {"Present": "P", "Absent": "A", "Half Day": "HD",
                          "On Leave": "L", "None": "", "Holiday": "<b>H</b>"}
            if status == "None" and holiday_map:
                emp_holiday_list = emp_det.holiday_list if emp_det.holiday_list else default_holiday_list
                if emp_holiday_list in holiday_map and (day+1) in holiday_map[emp_holiday_list]:
                    status = "Holiday"
            status_row.append(status_map[status])

            if status == "Present":
                total_p += 1
            elif status == "Absent":
                total_a += 1
            elif status == "On Leave":
                total_l += 1
            elif status == "Half Day":
                total_p += 0.5
                total_a += 0.5
                total_l += 0.5

            if shift_type:
                if not (shift_type in shift_duration_dict):
                    shift_duration_dict[shift_type] = get_shift_duration(
                        shift_type)
                shift_duration = shift_duration_dict[shift_type]
                if working_hours > shift_duration:
                    total_ot += working_hours - shift_duration

        if not filters.get("employee"):
            filters.update({"employee": emp})
            conditions += " and employee = %(employee)s"
        elif not filters.get("employee") == emp:
            filters.update({"employee": emp})

        leave_details = frappe.db.sql("""select leave_type, status, count(*) as count from `tabAttendance`\
			where leave_type is not NULL %s group by leave_type, status""" % conditions, filters, as_dict=1)

        time_default_counts = frappe.db.sql("""select (select count(*) from `tabAttendance` where \
			late_entry = 1 %s) as late_entry_count, (select count(*) from tabAttendance where \
			early_exit = 1 %s) as early_exit_count""" % (conditions, conditions), filters)

        row += [round(total_ot, 1), total_p, total_l, total_a, time_default_counts[0][0],
                time_default_counts[0][1]] + status_row
        leaves = {}
        for d in leave_details:
            if d.status == "Half Day":
                d.count = d.count * 0.5
            if d.leave_type in leaves:
                leaves[d.leave_type] += d.count
            else:
                leaves[d.leave_type] = d.count

        for d in leave_list:
            if d in leaves:
                row.append(leaves[d])
            else:
                row.append("0.0")

        row.extend([emp_det.employee_name])
        data.append(row)
    return columns, data


def get_columns(filters):
    # columns = [
    #     _("Employee") + ":Link/Employee:120", _("Employee Name") +
    #     "::140", _("Branch") + ":Link/Branch:120",
    #     _("Department") + ":Link/Department:120",
    #     _("Designation") + ":Link/Designation:120",
    #     _("Company") + ":Link/Company:120"
    # ]
    columns = [
        _("Employee") + ":Link/Employee:200",
        _("Overtime(Hours)") + "::80",
        _("Total Present") + ":Float:80",
        _("Total Leaves") + ":Float:80",
        _("Total Absent") + ":Float:80",
        _("Total Late Entries") + ":Float:120",
        _("Total Early Exits") + ":Float:120"
    ]

    for day in range(filters["total_days_in_month"]):
        if len(cstr(day+1)) < 2:
            columns.append(cstr(day+1) + "::20")
        else:
            columns.append(cstr(day+1) + "::40")

    columns += []
    return columns


def get_attendance_list(conditions, filters):
    attendance_list = frappe.db.sql("""select employee, day(attendance_date) as day_of_month,
		status, shift, working_hours from tabAttendance where docstatus = 1 %s order by employee, attendance_date""" %
                                    conditions, filters, as_dict=1)

    att_map = {}
    for d in attendance_list:
        att_map.setdefault(d.employee, frappe._dict()
                           ).setdefault(d.day_of_month, "")
        att_map[d.employee][d.day_of_month] = [
            d.status, d.shift, d.working_hours]

    return att_map


def get_shift_duration(shift_type):
    shift_hours = frappe.get_value('Shift Type', shift_type, [
                                   'start_time', 'end_time'])
    return (shift_hours[1]-shift_hours[0]).seconds/3600


def get_conditions(filters):
    if not (filters.get("month") and filters.get("year")):
        msgprint(_("Please select month and year"), raise_exception=1)

    filters["month"] = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov",
                        "Dec"].index(filters.month) + 1

    filters["total_days_in_month"] = monthrange(
        cint(filters.year), filters.month)[1]

    conditions = " and month(attendance_date) = %(month)s and year(attendance_date) = %(year)s"

    if filters.get("company"):
        conditions += " and company = %(company)s"
    if filters.get("employee"):
        conditions += " and employee = %(employee)s"

    return conditions, filters


def get_employee_details(filters):
    emp_map = frappe._dict()
    for d in frappe.db.sql("""select name, employee_name, designation, department, branch, company,
		holiday_list from tabEmployee where company = "%s" """ % (filters.get("company")), as_dict=1):
        emp_map.setdefault(d.name, d)

    return emp_map


def get_holiday(holiday_list, month):
    holiday_map = frappe._dict()
    for d in holiday_list:
        if d:
            holiday_map.setdefault(d, frappe.db.sql_list('''select day(holiday_date) from `tabHoliday`
				where parent=%s and month(holiday_date)=%s''', (d, month)))

    return holiday_map


@frappe.whitelist()
def get_attendance_years():
    year_list = frappe.db.sql_list(
        """select distinct YEAR(attendance_date) from tabAttendance ORDER BY YEAR(attendance_date) DESC""")
    if not year_list:
        year_list = [getdate().year]

    return "\n".join(str(year) for year in year_list)

3 Likes

Thanks. Long time this post… i have found my own way by adding custom fields and of course overwriting the py file.
Nice work.

Hello @manasan,
In which space, did you put the path of the above code in hooks.py to override the monthly attendance sheet?

1 Like