fix: leave balance reports (#18984)
* fix: process allocation expiry
* fix: leave balance summary filter
* fix: opening and closing balance
* fix: check for department leave approvers
* fix: minor changes
* fix: consider leave approver in employee
* Update employee_leave_balance_summary.py
diff --git a/erpnext/hr/report/employee_leave_balance/employee_leave_balance.py b/erpnext/hr/report/employee_leave_balance/employee_leave_balance.py
index 22f0203..7717ba0 100644
--- a/erpnext/hr/report/employee_leave_balance/employee_leave_balance.py
+++ b/erpnext/hr/report/employee_leave_balance/employee_leave_balance.py
@@ -8,6 +8,8 @@
from erpnext.hr.doctype.leave_application.leave_application \
import get_leave_balance_on, get_leaves_for_period
+from erpnext.hr.report.employee_leave_balance_summary.employee_leave_balance_summary \
+ import get_department_leave_approver_map
def execute(filters=None):
leave_types = frappe.db.sql_list("select name from `tabLeave Type` order by name asc")
@@ -19,7 +21,7 @@
def get_columns(leave_types):
columns = [
- _("Employee") + ":Link/Employee:150",
+ _("Employee") + ":Link.Employee:150",
_("Employee Name") + "::200",
_("Department") +"::150"
]
@@ -52,11 +54,13 @@
active_employees = frappe.get_all("Employee",
filters=conditions,
- fields=["name", "employee_name", "department", "user_id"])
+ fields=["name", "employee_name", "department", "user_id", "leave_approver"])
+
+ department_approver_map = get_department_leave_approver_map(filters.get('department'))
data = []
for employee in active_employees:
- leave_approvers = get_approvers(employee.department)
+ leave_approvers = department_approver_map.get(employee.department_name, []).append(employee.leave_approver)
if (len(leave_approvers) and user in leave_approvers) or (user in ["Administrator", employee.user_id]) or ("HR Manager" in frappe.get_roles(user)):
row = [employee.name, employee.employee_name, employee.department]
@@ -66,46 +70,13 @@
filters.from_date, filters.to_date) * -1
# opening balance
- opening = get_total_allocated_leaves(employee.name, leave_type, filters.from_date, filters.to_date)
+ opening = get_leave_balance_on(employee.name, leave_type, filters.from_date)
# closing balance
- closing = flt(opening) - flt(leaves_taken)
+ closing = get_leave_balance_on(employee.name, leave_type, filters.to_date)
row += [opening, leaves_taken, closing]
data.append(row)
- return data
-
-def get_approvers(department):
- if not department:
- return []
-
- approvers = []
- # get current department and all its child
- department_details = frappe.db.get_value("Department", {"name": department}, ["lft", "rgt"], as_dict=True)
- department_list = frappe.db.sql("""select name from `tabDepartment`
- where lft >= %s and rgt <= %s order by lft desc
- """, (department_details.lft, department_details.rgt), as_list = True)
-
- # retrieve approvers list from current department and from its subsequent child departments
- for d in department_list:
- approvers.extend([l.leave_approver for l in frappe.db.sql("""select approver from `tabDepartment Approver` \
- where parent = %s and parentfield = 'leave_approvers'""", (d), as_dict=True)])
-
- return approvers
-
-def get_total_allocated_leaves(employee, leave_type, from_date, to_date):
- ''' Returns leave allocation between from date and to date '''
- leave_allocation_records = frappe.db.get_all('Leave Ledger Entry', filters={
- 'docstatus': 1,
- 'is_expired': 0,
- 'leave_type': leave_type,
- 'employee': employee,
- 'transaction_type': 'Leave Allocation'
- }, or_filters={
- 'from_date': ['between', (from_date, to_date)],
- 'to_date': ['between', (from_date, to_date)]
- }, fields=['SUM(leaves) as leaves'])
-
- return flt(leave_allocation_records[0].get('leaves')) if leave_allocation_records else flt(0)
\ No newline at end of file
+ return data
\ No newline at end of file
diff --git a/erpnext/hr/report/employee_leave_balance_summary/employee_leave_balance_summary.js b/erpnext/hr/report/employee_leave_balance_summary/employee_leave_balance_summary.js
index 838f4ad..3fb8f6e 100644
--- a/erpnext/hr/report/employee_leave_balance_summary/employee_leave_balance_summary.js
+++ b/erpnext/hr/report/employee_leave_balance_summary/employee_leave_balance_summary.js
@@ -5,6 +5,20 @@
frappe.query_reports['Employee Leave Balance Summary'] = {
filters: [
{
+ fieldname:'from_date',
+ label: __('From Date'),
+ fieldtype: 'Date',
+ reqd: 1,
+ default: frappe.defaults.get_default('year_start_date')
+ },
+ {
+ fieldname:'to_date',
+ label: __('To Date'),
+ fieldtype: 'Date',
+ reqd: 1,
+ default: frappe.defaults.get_default('year_end_date')
+ },
+ {
fieldname:'company',
label: __('Company'),
fieldtype: 'Link',
@@ -19,18 +33,10 @@
options: 'Employee',
},
{
- fieldname:'from_date',
- label: __('From Date'),
- fieldtype: 'Date',
- reqd: 1,
- default: frappe.defaults.get_default('year_start_date')
- },
- {
- fieldname:'to_date',
- label: __('To Date'),
- fieldtype: 'Date',
- reqd: 1,
- default: frappe.defaults.get_default('year_end_date')
+ fieldname:'department',
+ label: __('Department'),
+ fieldtype: 'Link',
+ options: 'Department',
}
]
};
diff --git a/erpnext/hr/report/employee_leave_balance_summary/employee_leave_balance_summary.py b/erpnext/hr/report/employee_leave_balance_summary/employee_leave_balance_summary.py
index 64a5c1c..15a5da0 100644
--- a/erpnext/hr/report/employee_leave_balance_summary/employee_leave_balance_summary.py
+++ b/erpnext/hr/report/employee_leave_balance_summary/employee_leave_balance_summary.py
@@ -5,9 +5,7 @@
import frappe
from frappe.utils import flt
from frappe import _
-from erpnext.hr.doctype.leave_application.leave_application import get_leaves_for_period
-
-from erpnext.hr.report.employee_leave_balance.employee_leave_balance import get_total_allocated_leaves
+from erpnext.hr.doctype.leave_application.leave_application import get_leaves_for_period, get_leave_balance_on
def execute(filters=None):
if filters.to_date <= filters.from_date:
@@ -58,16 +56,14 @@
def get_data(filters):
leave_types = frappe.db.sql_list("SELECT `name` FROM `tabLeave Type` ORDER BY `name` ASC")
- conditions = {
- 'status': 'Active',
- }
+ conditions = get_conditions(filters)
- if filters.get('employee'):
- conditions['name'] = filters.get('employee')
+ user = frappe.session.user
+ department_approver_map = get_department_leave_approver_map(filters.get('department'))
- active_employees = frappe.get_all('Employee',
+ active_employees = frappe.get_list('Employee',
filters=conditions,
- fields=['name', 'employee_name', 'department', 'user_id'])
+ fields=['name', 'employee_name', 'department', 'user_id', 'leave_approver'])
data = []
@@ -76,21 +72,59 @@
'leave_type': leave_type
})
for employee in active_employees:
- row = frappe._dict({
- 'employee': employee.name,
- 'employee_name': employee.employee_name
- })
+
+ leave_approvers = department_approver_map.get(employee.department_name, []).append(employee.leave_approver)
- leaves_taken = get_leaves_for_period(employee.name, leave_type,
- filters.from_date, filters.to_date) * -1
+ if (len(leave_approvers) and user in leave_approvers) or (user in ["Administrator", employee.user_id]) \
+ or ("HR Manager" in frappe.get_roles(user)):
+ row = frappe._dict({
+ 'employee': employee.name,
+ 'employee_name': employee.employee_name
+ })
- opening = get_total_allocated_leaves(employee.name, leave_type, filters.from_date, filters.to_date)
- closing = flt(opening) - flt(leaves_taken)
+ leaves_taken = get_leaves_for_period(employee.name, leave_type,
+ filters.from_date, filters.to_date) * -1
- row.opening_balance = opening
- row.leaves_taken = leaves_taken
- row.closing_balance = closing
- row.indent = 1
- data.append(row)
+ opening = get_leave_balance_on(employee.name, leave_type, filters.from_date)
+ closing = get_leave_balance_on(employee.name, leave_type, filters.to_date)
+
+ row.opening_balance = opening
+ row.leaves_taken = leaves_taken
+ row.closing_balance = closing
+ row.indent = 1
+ data.append(row)
return data
+
+def get_conditions(filters):
+ conditions={
+ 'status': 'Active',
+ }
+ if filters.get('employee'):
+ conditions['name'] = filters.get('employee')
+
+ if filters.get('employee'):
+ conditions['name'] = filters.get('employee')
+
+ return conditions
+
+def get_department_leave_approver_map(department=None):
+ conditions=''
+ if department:
+ conditions='and department_name = %(department)s or parent_department = %(department)s'%{'department': department}
+
+ # get current department and all its child
+ department_list = frappe.db.sql_list(''' SELECT name FROM `tabDepartment` WHERE disabled=0 {0}'''.format(conditions)) #nosec
+
+ # retrieve approvers list from current department and from its subsequent child departments
+ approver_list = frappe.get_all('Department Approver', filters={
+ 'parentfield': 'leave_approvers',
+ 'parent': ('in', department_list)
+ }, fields=['parent', 'approver'], as_list=1)
+
+ approvers = {}
+
+ for k, v in approver_list:
+ approvers.setdefault(k, []).append(v)
+
+ return approvers