Report to get Employeewise Balance Leave
diff --git a/erpnext/hr/search_criteria/employeewise_balance_leave_report/employeewise_balance_leave_report.js b/erpnext/hr/search_criteria/employeewise_balance_leave_report/employeewise_balance_leave_report.js
index 2439ffa..24d1774 100644
--- a/erpnext/hr/search_criteria/employeewise_balance_leave_report/employeewise_balance_leave_report.js
+++ b/erpnext/hr/search_criteria/employeewise_balance_leave_report/employeewise_balance_leave_report.js
@@ -1,3 +1,24 @@
this.mytabs.items['Select Columns'].hide();
-this.mytabs.tabs['More Filters'].hide();
\ No newline at end of file
+this.mytabs.tabs['More Filters'].hide();
+
+report.customize_filters = function() {
+ this.add_filter({
+ fieldname:'fiscal_year',
+ label:'Fiscal Year',
+ fieldtype:'Link',
+ ignore : 1,
+ options: 'Fiscal Year',
+ parent:'Leave Allocation',
+ in_first_page:1
+ });
+ this.add_filter({
+ fieldname:'employee_name',
+ label:'Employee Name',
+ fieldtype:'Data',
+ ignore : 1,
+ options: '',
+ parent:'Leave Allocation',
+ in_first_page:1
+ });
+}
\ No newline at end of file
diff --git a/erpnext/hr/search_criteria/employeewise_balance_leave_report/employeewise_balance_leave_report.py b/erpnext/hr/search_criteria/employeewise_balance_leave_report/employeewise_balance_leave_report.py
index c258d15..8c0c747 100644
--- a/erpnext/hr/search_criteria/employeewise_balance_leave_report/employeewise_balance_leave_report.py
+++ b/erpnext/hr/search_criteria/employeewise_balance_leave_report/employeewise_balance_leave_report.py
@@ -1,20 +1,55 @@
-leave_types = sql("select name from `tabLeave Type` where docstatus != 2 and name not in ('Compensatory Off','Leave Without Pay')")
-msgprint(leave_types)
+leave_types = sql("""
+ SELECT name FROM `tabLeave Type`
+ WHERE
+ docstatus!=2 AND
+ name NOT IN ('Compensatory Off', 'Leave Without Pay')""")
col=[]
+col.append(['Employee ID', 'Data', '150px', ''])
+col.append(['Employee Name', 'Data', '150px', ''])
+col.append(['Fiscal Year', 'Data', '150px', ''])
for e in leave_types:
- l = (len(e)*9)
- if l < 150 : col_width = '150px'
- else: col_width = '%spx'%(l)
-
- col.append([e,'Currency',col_width,''])
-
+ l = (len(e[0])*9)
+ if l < 150 : col_width = '150px'
+ else: col_width = '%spx'%(l)
+ col.append([e[0],'Currency',col_width,''])
col.append(['Total Balance','Currency','150px',''])
for c in col:
- colnames.append(c[0])
- coltypes.append(c[1])
- colwidths.append(c[2])
- coloptions.append(c[3])
- col_idx[c[0]] = len(colnames)
+ colnames.append(c[0])
+ coltypes.append(c[1])
+ colwidths.append(c[2])
+ coloptions.append(c[3])
+ col_idx[c[0]] = len(colnames)
+
+data = res
+res = []
+
+try:
+ for d in data:
+ exists = 0
+ ind = None
+
+ # Check if the employee record exists in list 'res'
+ for r in res:
+ if r[0] == d[0] and r[1] == d[1]:
+ exists = 1
+ ind = res.index(r)
+ break
+ if d[3] in colnames:
+ # If exists, then append the leave type data
+ if exists:
+ res[ind][colnames.index(d[3])] = d[4] - d[5]
+ res[ind][len(colnames)-1] = sum(res[ind][3:-1])
+ # Else create a new row in res
+ else:
+ new_row = [0.0 for c in colnames]
+ new_row[0] = d[0]
+ new_row[1] = d[1]
+ new_row[2] = d[2]
+ new_row[colnames.index(d[3])] = d[4] - d[5]
+ new_row[len(colnames)-1] = sum(new_row[3:-1])
+ res.append(new_row)
+except Exception, e:
+ msgprint(e)
\ No newline at end of file
diff --git a/erpnext/hr/search_criteria/employeewise_balance_leave_report/employeewise_balance_leave_report.sql b/erpnext/hr/search_criteria/employeewise_balance_leave_report/employeewise_balance_leave_report.sql
new file mode 100644
index 0000000..50811c0
--- /dev/null
+++ b/erpnext/hr/search_criteria/employeewise_balance_leave_report/employeewise_balance_leave_report.sql
@@ -0,0 +1,26 @@
+SELECT
+ leave_alloc.employee AS 'employee',
+ leave_alloc.employee_name AS 'employee_name',
+ leave_alloc.fiscal_year AS 'fiscal_year',
+ leave_alloc.leave_type AS 'leave_type',
+ leave_alloc.total_leaves_allocated AS 'total_leaves_allocated',
+ SUM(leave_app.total_leave_days) AS 'total_leaves_applied'
+FROM
+ `tabLeave Allocation` AS leave_alloc,
+ `tabLeave Application` AS leave_app
+WHERE
+ leave_alloc.employee=leave_app.employee AND
+ leave_alloc.leave_type=leave_app.leave_type AND
+ leave_alloc.fiscal_year=leave_app.fiscal_year AND
+ leave_alloc.docstatus=1 AND
+ leave_app.docstatus=1 AND
+ leave_alloc.fiscal_year LIKE '%(fiscal_year)s%%' AND
+ leave_alloc.employee_name LIKE '%(employee_name)s%%'
+GROUP BY
+ employee,
+ fiscal_year,
+ leave_type
+ORDER BY
+ employee,
+ fiscal_year,
+ leave_type
\ No newline at end of file
diff --git a/erpnext/hr/search_criteria/employeewise_balance_leave_report/employeewise_balance_leave_report.txt b/erpnext/hr/search_criteria/employeewise_balance_leave_report/employeewise_balance_leave_report.txt
index 48ef3bf..7a7f049 100644
--- a/erpnext/hr/search_criteria/employeewise_balance_leave_report/employeewise_balance_leave_report.txt
+++ b/erpnext/hr/search_criteria/employeewise_balance_leave_report/employeewise_balance_leave_report.txt
@@ -3,9 +3,9 @@
# These values are common in all dictionaries
{
- 'creation': '2010-12-14 10:23:25',
+ 'creation': '2010-12-14 10:33:09',
'docstatus': 0,
- 'modified': '2010-12-01 10:39:56',
+ 'modified': '2011-10-31 15:42:36',
'modified_by': 'Administrator',
'owner': 'harshada@webnotestech.com'
},
@@ -13,7 +13,8 @@
# These values are common for all Search Criteria
{
'columns': 'Employee\x01ID',
- 'criteria_name': 'Employeewise Balance Leave Report',
+ 'criteria_name': 'Employee Leave Balance Report',
+ 'description': 'Employeewise Balance Leave Report',
'doc_type': 'Employee',
'doctype': 'Search Criteria',
'filters': "{'Employee\x01Saved':1,'Employee\x01Submitted':1,'Employee\x01Gender':'','Employee\x01Month of Birth':'','Employee\x01Status':'Active'}",