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.
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.
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.
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)
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?