[Enhancement] Monthly Attendance Report (#13970)
* Update Attendance on Approval of Leave
* Separate out leaves on the basis of its type
* Remove commented code
* Make attendance records if not found
* Fix Codacy
* Replace bad code in attendance.py
diff --git a/erpnext/hr/doctype/attendance/attendance.py b/erpnext/hr/doctype/attendance/attendance.py
index 458b2dd..d1809c6 100644
--- a/erpnext/hr/doctype/attendance/attendance.py
+++ b/erpnext/hr/doctype/attendance/attendance.py
@@ -20,17 +20,19 @@
set_employee_name(self)
def check_leave_record(self):
- leave_record = frappe.db.sql("""select leave_type, half_day from `tabLeave Application`
+ leave_record = frappe.db.sql("""select leave_type, half_day, half_day_date from `tabLeave Application`
where employee = %s and %s between from_date and to_date and status = 'Approved'
and docstatus = 1""", (self.employee, self.attendance_date), as_dict=True)
if leave_record:
- if leave_record[0].half_day:
- self.status = 'Half Day'
- frappe.msgprint(_("Employee {0} on Half day on {1}").format(self.employee, self.attendance_date))
- else:
- self.status = 'On Leave'
- self.leave_type = leave_record[0].leave_type
- frappe.msgprint(_("Employee {0} on Leave on {1}").format(self.employee, self.attendance_date))
+ for d in leave_record:
+ if d.half_day_date == getdate(self.attendance_date):
+ self.status = 'Half Day'
+ frappe.msgprint(_("Employee {0} on Half day on {1}").format(self.employee, self.attendance_date))
+ else:
+ self.status = 'On Leave'
+ self.leave_type = d.leave_type
+ frappe.msgprint(_("Employee {0} on Leave on {1}").format(self.employee, self.attendance_date))
+
if self.status == "On Leave" and not leave_record:
frappe.throw(_("No leave record found for employee {0} for {1}").format(self.employee, self.attendance_date))
diff --git a/erpnext/hr/doctype/leave_application/leave_application.py b/erpnext/hr/doctype/leave_application/leave_application.py
index 8d19510..4e1b54b 100755
--- a/erpnext/hr/doctype/leave_application/leave_application.py
+++ b/erpnext/hr/doctype/leave_application/leave_application.py
@@ -5,12 +5,12 @@
import frappe
from frappe import _
from frappe.utils import cint, cstr, date_diff, flt, formatdate, getdate, get_link_to_form, \
- comma_or, get_fullname
+ comma_or, get_fullname, nowdate
from erpnext.hr.utils import set_employee_name
from erpnext.hr.doctype.leave_block_list.leave_block_list import get_applicable_block_dates
from erpnext.hr.doctype.employee.employee import get_holiday_list_for_employee
from erpnext.hr.doctype.employee_leave_approver.employee_leave_approver import get_approver_list
-
+from erpnext.buying.doctype.supplier_scorecard.supplier_scorecard import daterange
class LeaveDayBlockedError(frappe.ValidationError): pass
class OverlapError(frappe.ValidationError): pass
@@ -52,6 +52,7 @@
frappe.throw(_("Only Leave Applications with status 'Approved' and 'Rejected' can be submitted"))
self.validate_back_dated_application()
+ self.update_attendance()
# notify leave applier about approval
self.notify_employee(self.status)
@@ -100,6 +101,41 @@
frappe.throw(_("Leave cannot be applied/cancelled before {0}, as leave balance has already been carry-forwarded in the future leave allocation record {1}")
.format(formatdate(future_allocation[0].from_date), future_allocation[0].name))
+ def update_attendance(self):
+ if self.status == "Approved":
+ attendance = frappe.db.sql("""select name from `tabAttendance` where employee = %s\
+ and (attendance_date between %s and %s) and docstatus < 2""",(self.employee, self.from_date, self.to_date), as_dict=1)
+
+ if attendance:
+ for d in attendance:
+ doc = frappe.get_doc("Attendance", d.name)
+ if getdate(self.half_day_date) == doc.attendance_date:
+ status = "Half Day"
+ else:
+ status = "On Leave"
+ frappe.db.sql("""update `tabAttendance` set status = %s, leave_type = %s\
+ where name = %s""",(status, self.leave_type, d.name))
+
+ elif self.from_date <= nowdate():
+ for dt in daterange(getdate(self.from_date), getdate(self.to_date)):
+ date = dt.strftime("%Y-%m-%d")
+ if not date == self.half_day_date:
+ doc = frappe.new_doc("Attendance")
+ doc.employee = self.employee
+ doc.attendance_date = date
+ doc.company = self.company
+ doc.status = "On Leave"
+ doc.leave_type = self.leave_type
+ doc.submit()
+ else:
+ doc = frappe.new_doc("Attendance")
+ doc.employee = self.employee
+ doc.attendance_date = date
+ doc.company = self.company
+ doc.status = "Half Day"
+ doc.leave_type = self.leave_type
+ doc.submit()
+
def validate_salary_processed_days(self):
if not frappe.db.get_value("Leave Type", self.leave_type, "is_lwp"):
return
@@ -436,30 +472,17 @@
and company=%s""", (department, company))
match_conditions = "and employee in (\"%s\")" % '", "'.join(department_employees)
- add_leaves(events, start, end, filter_conditions=match_conditions)
+ add_leaves(events, start, end, match_conditions=match_conditions)
-def add_leaves(events, start, end, filter_conditions=None):
- conditions = []
-
- if filter_conditions:
- conditions.append(filter_conditions)
-
- if not cint(frappe.db.get_value("HR Settings", None, "show_leaves_of_all_department_members_in_calendar")):
- from frappe.desk.reportview import build_match_conditions
- match_conditions = build_match_conditions("Leave Application")
-
- if match_conditions:
- conditions.append(match_conditions)
-
+def add_leaves(events, start, end, match_conditions=None):
query = """select name, from_date, to_date, employee_name, half_day,
status, employee, docstatus
from `tabLeave Application` where
from_date <= %(end)s and to_date >= %(start)s <= to_date
and docstatus < 2
and status!="Rejected" """
-
- if conditions:
- query += ' and '.join(conditions)
+ if match_conditions:
+ query += match_conditions
for d in frappe.db.sql(query, {"start":start, "end": end}, as_dict=True):
e = {
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 698c4fb..0c338e0 100644
--- a/erpnext/hr/report/monthly_attendance_sheet/monthly_attendance_sheet.py
+++ b/erpnext/hr/report/monthly_attendance_sheet/monthly_attendance_sheet.py
@@ -22,6 +22,10 @@
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)
+
for emp in sorted(att_map):
emp_det = emp_map.get(emp)
if not emp_det:
@@ -49,10 +53,35 @@
elif status == "Half Day":
total_p += 0.5
total_a += 0.5
+ total_l += 0.5
row += [total_p, total_l, total_a]
- data.append(row)
+ 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)
+
+ 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")
+
+ data.append(row)
return columns, data
def get_columns(filters):