Feat: Monthly attendance sheet Enhancements and UI/UX Improvements (#21331)
* refactor: Employee attendance sheet redesign
* feat: Added weekly off status in monthly Attendance sheet
Co-authored-by: Nabin Hait <nabinhait@gmail.com>
diff --git a/erpnext/hr/doctype/holiday/holiday.json b/erpnext/hr/doctype/holiday/holiday.json
index 6498530..6bd0ab0 100644
--- a/erpnext/hr/doctype/holiday/holiday.json
+++ b/erpnext/hr/doctype/holiday/holiday.json
@@ -1,87 +1,60 @@
{
- "allow_copy": 0,
- "allow_import": 0,
- "allow_rename": 0,
- "beta": 0,
- "creation": "2013-02-22 01:27:46",
- "custom": 0,
- "docstatus": 0,
- "doctype": "DocType",
- "document_type": "Setup",
- "editable_grid": 1,
+ "actions": [],
+ "creation": "2013-02-22 01:27:46",
+ "doctype": "DocType",
+ "document_type": "Setup",
+ "editable_grid": 1,
+ "engine": "InnoDB",
+ "field_order": [
+ "holiday_date",
+ "column_break_2",
+ "weekly_off",
+ "section_break_4",
+ "description"
+ ],
"fields": [
{
- "allow_on_submit": 0,
- "bold": 0,
- "collapsible": 0,
- "fieldname": "holiday_date",
- "fieldtype": "Date",
- "hidden": 0,
- "ignore_user_permissions": 0,
- "ignore_xss_filter": 0,
- "in_filter": 0,
- "in_list_view": 1,
- "label": "Date",
- "length": 0,
- "no_copy": 0,
- "oldfieldname": "holiday_date",
- "oldfieldtype": "Date",
- "permlevel": 0,
- "print_hide": 0,
- "print_hide_if_no_value": 0,
- "read_only": 0,
- "report_hide": 0,
- "reqd": 1,
- "search_index": 0,
- "set_only_once": 0,
- "unique": 0
- },
+ "fieldname": "holiday_date",
+ "fieldtype": "Date",
+ "in_list_view": 1,
+ "label": "Date",
+ "oldfieldname": "holiday_date",
+ "oldfieldtype": "Date",
+ "reqd": 1
+ },
{
- "allow_on_submit": 0,
- "bold": 0,
- "collapsible": 0,
- "fieldname": "description",
- "fieldtype": "Text Editor",
- "hidden": 0,
- "ignore_user_permissions": 0,
- "ignore_xss_filter": 0,
- "in_filter": 0,
- "in_list_view": 1,
- "label": "Description",
- "length": 0,
- "no_copy": 0,
- "permlevel": 0,
- "print_hide": 0,
- "print_hide_if_no_value": 0,
- "print_width": "300px",
- "read_only": 0,
- "report_hide": 0,
- "reqd": 1,
- "search_index": 0,
- "set_only_once": 0,
- "unique": 0,
+ "fieldname": "description",
+ "fieldtype": "Text Editor",
+ "in_list_view": 1,
+ "label": "Description",
+ "print_width": "300px",
+ "reqd": 1,
"width": "300px"
+ },
+ {
+ "default": "0",
+ "fieldname": "weekly_off",
+ "fieldtype": "Check",
+ "label": "Weekly Off"
+ },
+ {
+ "fieldname": "column_break_2",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "section_break_4",
+ "fieldtype": "Section Break"
}
- ],
- "hide_heading": 0,
- "hide_toolbar": 0,
- "idx": 1,
- "image_view": 0,
- "in_create": 0,
-
- "is_submittable": 0,
- "issingle": 0,
- "istable": 1,
- "max_attachments": 0,
- "modified": "2016-07-11 03:28:00.660849",
- "modified_by": "Administrator",
- "module": "HR",
- "name": "Holiday",
- "owner": "Administrator",
- "permissions": [],
- "quick_entry": 0,
- "read_only": 0,
- "read_only_onload": 0,
- "sort_order": "ASC",
- "track_seen": 0
+ ],
+ "idx": 1,
+ "istable": 1,
+ "links": [],
+ "modified": "2020-04-18 19:03:23.507845",
+ "modified_by": "Administrator",
+ "module": "HR",
+ "name": "Holiday",
+ "owner": "Administrator",
+ "permissions": [],
+ "sort_field": "modified",
+ "sort_order": "ASC"
}
\ No newline at end of file
diff --git a/erpnext/hr/doctype/holiday_list/holiday_list.py b/erpnext/hr/doctype/holiday_list/holiday_list.py
index 8c7b6f7..76dc942 100644
--- a/erpnext/hr/doctype/holiday_list/holiday_list.py
+++ b/erpnext/hr/doctype/holiday_list/holiday_list.py
@@ -23,6 +23,7 @@
ch = self.append('holidays', {})
ch.description = self.weekly_off
ch.holiday_date = d
+ ch.weekly_off = 1
ch.idx = last_idx + i + 1
def validate_values(self):
diff --git a/erpnext/hr/report/monthly_attendance_sheet/monthly_attendance_sheet.js b/erpnext/hr/report/monthly_attendance_sheet/monthly_attendance_sheet.js
index 348c5e7..bd4ed3c 100644
--- a/erpnext/hr/report/monthly_attendance_sheet/monthly_attendance_sheet.js
+++ b/erpnext/hr/report/monthly_attendance_sheet/monthly_attendance_sheet.js
@@ -31,6 +31,18 @@
"options": "Company",
"default": frappe.defaults.get_user_default("Company"),
"reqd": 1
+ },
+ {
+ "fieldname":"group_by",
+ "label": __("Group By"),
+ "fieldtype": "Select",
+ "options": ["","Branch","Grade","Department","Designation"]
+ },
+ {
+ "fieldname":"summarized_view",
+ "label": __("Summarized View"),
+ "fieldtype": "Check",
+ "Default": 0,
}
],
diff --git a/erpnext/hr/report/monthly_attendance_sheet/monthly_attendance_sheet.py b/erpnext/hr/report/monthly_attendance_sheet/monthly_attendance_sheet.py
index 9a9e42e..d98ed1b 100644
--- a/erpnext/hr/report/monthly_attendance_sheet/monthly_attendance_sheet.py
+++ b/erpnext/hr/report/monthly_attendance_sheet/monthly_attendance_sheet.py
@@ -7,65 +7,127 @@
from frappe import msgprint, _
from calendar import monthrange
+status_map = {
+ "Absent": "A",
+ "Half Day": "HD",
+ "Holiday": "<b>H</b>",
+ "Weekly Off": "<b>WO</b>",
+ "On Leave": "L",
+ "Present": "P",
+ "Work From Home": "WFH"
+ }
+
+day_abbr = [
+ "Mon",
+ "Tue",
+ "Wed",
+ "Thu",
+ "Fri",
+ "Sat",
+ "Sun"
+]
+
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)
- holiday_list = [emp_map[d]["holiday_list"] for d in emp_map if emp_map[d]["holiday_list"]]
+ if filters.group_by:
+ emp_map, group_by_parameters = get_employee_details(filters.group_by, filters.company)
+ holiday_list = []
+ for parameter in group_by_parameters:
+ h_list = [emp_map[parameter][d]["holiday_list"] for d in emp_map[parameter] if emp_map[parameter][d]["holiday_list"]]
+ holiday_list += h_list
+ else:
+ emp_map = get_employee_details(filters.group_by, filters.company)
+ 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([_("Total Late Entries") + ":Float:120", _("Total Early Exits") + ":Float:120"])
- for emp in sorted(att_map):
- emp_det = emp_map.get(emp)
- if not emp_det:
+ leave_list = None
+ if filters.summarized_view:
+ leave_types = frappe.db.sql("""select name from `tabLeave Type`""", as_list=True)
+ leave_list = [d[0] + ":Float:120" for d in leave_types]
+ columns.extend(leave_list)
+ columns.extend([_("Total Late Entries") + ":Float:120", _("Total Early Exits") + ":Float:120"])
+
+ if filters.group_by:
+ for parameter in group_by_parameters:
+ data.append([ "<b>"+ parameter + "</b>"])
+ record = add_data(emp_map[parameter], att_map, filters, holiday_map, conditions, leave_list=leave_list)
+ data += record
+ else:
+ record = add_data(emp_map, att_map, filters, holiday_map, conditions, leave_list=leave_list)
+ data += record
+
+ return columns, data
+
+
+def add_data(employee_map, att_map, filters, holiday_map, conditions, leave_list=None):
+
+ record = []
+ for emp in employee_map:
+ emp_det = employee_map.get(emp)
+ if not emp_det or emp not in att_map:
continue
- row = [emp, emp_det.employee_name, emp_det.branch, emp_det.department, emp_det.designation,
- emp_det.company]
+ row = []
+ if filters.group_by:
+ row += [" "]
+ row += [emp, emp_det.employee_name]
- total_p = total_a = total_l = 0.0
+ total_p = total_a = total_l = total_h = total_um= 0.0
for day in range(filters["total_days_in_month"]):
+ status = None
status = att_map.get(emp).get(day + 1)
- status_map = {
- "Absent": "A",
- "Half Day": "HD",
- "Holiday":"<b>H</b>",
- "On Leave": "L",
- "Present": "P",
- "Work From Home": "WFH"
- }
if status is 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"
- row.append(status_map.get(status, ""))
+ if emp_holiday_list in holiday_map:
+ for idx, ele in enumerate(holiday_map[emp_holiday_list]):
+ if day+1 == holiday_map[emp_holiday_list][idx][0]:
+ if holiday_map[emp_holiday_list][idx][1]:
+ status = "Weekly Off"
+ else:
+ status = "Holiday"
+ total_h += 1
- 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
- row += [total_p, total_l, total_a]
+ # if emp_holiday_list in holiday_map and (day+1) in holiday_map[emp_holiday_list][0]:
+ # if holiday_map[emp_holiday_list][1]:
+ # status= "Weekly Off"
+ # else:
+ # status = "Holiday"
+
+ # += 1
+
+ if not filters.summarized_view:
+ row.append(status_map.get(status, ""))
+ else:
+ 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
+ elif not status:
+ total_um += 1
+
+ if filters.summarized_view:
+ row += [total_p, total_l, total_a, total_h, total_um]
if not filters.get("employee"):
filters.update({"employee": emp})
@@ -73,43 +135,53 @@
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)
+ if filters.summarized_view:
+ 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)
+ 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)
- 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
+ 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")
+ for d in leave_list:
+ if d in leaves:
+ row.append(leaves[d])
+ else:
+ row.append("0.0")
- row.extend([time_default_counts[0][0],time_default_counts[0][1]])
- data.append(row)
- return columns, data
+ row.extend([time_default_counts[0][0],time_default_counts[0][1]])
+ record.append(row)
+
+
+ return record
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 = []
+
+ if filters.group_by:
+ columns = [_(filters.group_by)+ ":Link/Branch:120"]
+
+ columns += [
+ _("Employee") + ":Link/Employee:120", _("Employee Name") + ":Link/Employee:120"
]
- for day in range(filters["total_days_in_month"]):
- columns.append(cstr(day+1) +"::20")
-
- columns += [_("Total Present") + ":Float:80", _("Total Leaves") + ":Float:80", _("Total Absent") + ":Float:80"]
+ if not filters.summarized_view:
+ for day in range(filters["total_days_in_month"]):
+ date = str(filters.year) + "-" + str(filters.month)+ "-" + str(day+1)
+ day_name = day_abbr[getdate(date).weekday()]
+ columns.append(cstr(day+1)+ " " +day_name +"::65")
+ else:
+ columns += [_("Total Present") + ":Float:120", _("Total Leaves") + ":Float:120", _("Total Absent") + ":Float:120", _("Total Holidays") + ":Float:120", _("Unmarked Days")+ ":Float:120"]
return columns
def get_attendance_list(conditions, filters):
@@ -140,19 +212,43 @@
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)
+def get_employee_details(group_by, company):
+ emp_map = {}
+ query = """select name, employee_name, designation, department, branch, company,
+ holiday_list from `tabEmployee` where company = '%s' """ % frappe.db.escape(company)
- return emp_map
+ if group_by:
+ group_by = group_by.lower()
+ query += " order by " + group_by + " ASC"
+
+ employee_details = frappe.db.sql(query , as_dict=1)
+
+ group_by_parameters = []
+ if group_by:
+
+ group_by_parameters = list(set(detail.get(group_by, "") for detail in employee_details if detail.get(group_by, "")))
+ for parameter in group_by_parameters:
+ emp_map[parameter] = {}
+
+
+ for d in employee_details:
+ if group_by and len(group_by_parameters):
+ if d.get(group_by, None):
+
+ emp_map[d.get(group_by)][d.name] = d
+ else:
+ emp_map[d.name] = d
+
+ if not group_by:
+ return emp_map
+ else:
+ return emp_map, group_by_parameters
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`
+ holiday_map.setdefault(d, frappe.db.sql('''select day(holiday_date), weekly_off from `tabHoliday`
where parent=%s and month(holiday_date)=%s''', (d, month)))
return holiday_map