Merge branch 'develop' into employee-util-report
diff --git a/erpnext/projects/report/employee_hours_utilization_based_on_timesheet/__init__.py b/erpnext/projects/report/employee_hours_utilization_based_on_timesheet/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/projects/report/employee_hours_utilization_based_on_timesheet/__init__.py
diff --git a/erpnext/projects/report/employee_hours_utilization_based_on_timesheet/employee_hours_utilization_based_on_timesheet.js b/erpnext/projects/report/employee_hours_utilization_based_on_timesheet/employee_hours_utilization_based_on_timesheet.js
new file mode 100644
index 0000000..b11a1fc
--- /dev/null
+++ b/erpnext/projects/report/employee_hours_utilization_based_on_timesheet/employee_hours_utilization_based_on_timesheet.js
@@ -0,0 +1,48 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Employee Hours Utilization Based On Timesheet"] = {
+ "filters": [
+ {
+ fieldname: "company",
+ label: __("Company"),
+ fieldtype: "Link",
+ options: "Company",
+ default: frappe.defaults.get_user_default("Company"),
+ reqd: 1
+ },
+ {
+ fieldname: "from_date",
+ label: __("From Date"),
+ fieldtype: "Date",
+ default: frappe.defaults.get_global_default("year_start_date"),
+ reqd: 1
+ },
+ {
+ fieldname:"to_date",
+ label: __("To Date"),
+ fieldtype: "Date",
+ default: frappe.defaults.get_global_default("year_end_date"),
+ reqd: 1
+ },
+ {
+ fieldname: "employee",
+ label: __("Employee"),
+ fieldtype: "Link",
+ options: "Employee"
+ },
+ {
+ fieldname: "department",
+ label: __("Department"),
+ fieldtype: "Link",
+ options: "Department"
+ },
+ {
+ fieldname: "project",
+ label: __("Project"),
+ fieldtype: "Link",
+ options: "Project"
+ }
+ ]
+};
diff --git a/erpnext/projects/report/employee_hours_utilization_based_on_timesheet/employee_hours_utilization_based_on_timesheet.json b/erpnext/projects/report/employee_hours_utilization_based_on_timesheet/employee_hours_utilization_based_on_timesheet.json
new file mode 100644
index 0000000..5ff8186
--- /dev/null
+++ b/erpnext/projects/report/employee_hours_utilization_based_on_timesheet/employee_hours_utilization_based_on_timesheet.json
@@ -0,0 +1,22 @@
+{
+ "add_total_row": 0,
+ "columns": [],
+ "creation": "2021-04-05 19:23:43.838623",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "filters": [],
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2021-04-05 19:23:43.838623",
+ "modified_by": "Administrator",
+ "module": "Projects",
+ "name": "Employee Hours Utilization Based On Timesheet",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Timesheet",
+ "report_name": "Employee Hours Utilization Based On Timesheet",
+ "report_type": "Script Report",
+ "roles": []
+}
\ No newline at end of file
diff --git a/erpnext/projects/report/employee_hours_utilization_based_on_timesheet/employee_hours_utilization_based_on_timesheet.py b/erpnext/projects/report/employee_hours_utilization_based_on_timesheet/employee_hours_utilization_based_on_timesheet.py
new file mode 100644
index 0000000..48eb7b4
--- /dev/null
+++ b/erpnext/projects/report/employee_hours_utilization_based_on_timesheet/employee_hours_utilization_based_on_timesheet.py
@@ -0,0 +1,259 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe import _
+from frappe.utils import flt, getdate
+from six import iteritems
+
+def execute(filters=None):
+ return EmployeeHoursReport(filters).run()
+
+class EmployeeHoursReport:
+ '''Employee Hours Utilization Report Based On Timesheet'''
+ def __init__(self, filters=None):
+ self.filters = frappe._dict(filters or {})
+
+ self.from_date = getdate(self.filters.from_date)
+ self.to_date = getdate(self.filters.to_date)
+
+ self.validate_dates()
+
+ def validate_dates(self):
+ self.day_span = (self.to_date - self.from_date).days
+
+ if self.day_span <= 0:
+ frappe.throw(_('From Date must come before To Date'))
+
+ def run(self):
+ self.generate_columns()
+ self.generate_data()
+ self.generate_report_summary()
+ self.generate_chart_data()
+
+ return self.columns, self.data, None, self.chart, self.report_summary
+
+ def generate_columns(self):
+ self.columns = [
+ {
+ 'label': _('Employee'),
+ 'options': 'Employee',
+ 'fieldname': 'employee',
+ 'fieldtype': 'Link',
+ 'width': 230
+ },
+ {
+ 'label': _('Department'),
+ 'options': 'Department',
+ 'fieldname': 'department',
+ 'fieldtype': 'Link',
+ 'width': 170
+ },
+ {
+ 'label': _('Total Hours'),
+ 'fieldname': 'total_hours',
+ 'fieldtype': 'Float',
+ 'width': 150
+ },
+ {
+ 'label': _('Billed Hours'),
+ 'fieldname': 'billed_hours',
+ 'fieldtype': 'Float',
+ 'width': 150
+ },
+ {
+ 'label': _('Non-Billed Hours'),
+ 'fieldname': 'non_billed_hours',
+ 'fieldtype': 'Float',
+ 'width': 150
+ },
+ {
+ 'label': _('Untracked Hours'),
+ 'fieldname': 'untracked_hours',
+ 'fieldtype': 'Float',
+ 'width': 150
+ },
+ {
+ 'label': _('% Utilization (Billed Hours + Non-Billed Hours / Total Hours)'),
+ 'fieldname': 'per_util',
+ 'fieldtype': 'Percentage',
+ 'width': 200
+ }
+ ]
+
+ def generate_data(self):
+ self.generate_filtered_time_logs()
+ self.generate_stats_by_employee()
+ self.set_employee_department_and_name()
+
+ if self.filters.department:
+ self.filter_stats_by_department()
+
+ self.calculate_utilizations()
+
+ self.data = []
+
+ for emp, data in iteritems(self.stats_by_employee):
+ row = frappe._dict()
+ row['employee'] = emp
+ row.update(data)
+ self.data.append(row)
+
+ # Sort by descending order of percentage utilization
+ self.data.sort(key=lambda x: x['per_util'], reverse=True)
+
+ def filter_stats_by_department(self):
+ filtered_data = frappe._dict()
+ for emp, data in self.stats_by_employee.items():
+ if data['department'] == self.filters.department:
+ filtered_data[emp] = data
+
+ # Update stats
+ self.stats_by_employee = filtered_data
+
+ def generate_filtered_time_logs(self):
+ additional_filters = ''
+
+ filter_fields = ['employee', 'project', 'company']
+
+ for field in filter_fields:
+ if self.filters.get(field):
+ if field == 'project':
+ additional_filters += f"AND ttd.{field} = '{self.filters.get(field)}'"
+ else:
+ additional_filters += f"AND tt.{field} = '{self.filters.get(field)}'"
+
+ self.filtered_time_logs = frappe.db.sql('''
+ SELECT tt.employee AS employee, ttd.hours AS hours, ttd.billable AS billable, ttd.project AS project
+ FROM `tabTimesheet Detail` AS ttd
+ JOIN `tabTimesheet` AS tt
+ ON ttd.parent = tt.name
+ WHERE tt.employee IS NOT NULL
+ AND tt.start_date BETWEEN '{0}' AND '{1}'
+ AND tt.end_date BETWEEN '{0}' AND '{1}'
+ {2}
+ '''.format(self.filters.from_date, self.filters.to_date, additional_filters))
+
+ def generate_stats_by_employee(self):
+ self.stats_by_employee = frappe._dict()
+
+ for emp, hours, billable, project in self.filtered_time_logs:
+ self.stats_by_employee.setdefault(
+ emp, frappe._dict()
+ ).setdefault('billed_hours', 0.0)
+
+ self.stats_by_employee[emp].setdefault('non_billed_hours', 0.0)
+
+ if billable:
+ self.stats_by_employee[emp]['billed_hours'] += flt(hours, 2)
+ else:
+ self.stats_by_employee[emp]['non_billed_hours'] += flt(hours, 2)
+
+ def set_employee_department_and_name(self):
+ for emp in self.stats_by_employee:
+ emp_name = frappe.db.get_value(
+ 'Employee', emp, 'employee_name'
+ )
+ emp_dept = frappe.db.get_value(
+ 'Employee', emp, 'department'
+ )
+
+ self.stats_by_employee[emp]['department'] = emp_dept
+ self.stats_by_employee[emp]['employee_name'] = emp_name
+
+ def calculate_utilizations(self):
+ # (9.0) Will be fetched from HR settings
+ TOTAL_HOURS = flt(9.0 * self.day_span, 2)
+ for emp, data in iteritems(self.stats_by_employee):
+ data['total_hours'] = TOTAL_HOURS
+ data['untracked_hours'] = flt(TOTAL_HOURS - data['billed_hours'] - data['non_billed_hours'], 2)
+
+ # To handle overtime edge-case
+ if data['untracked_hours'] < 0:
+ data['untracked_hours'] = 0.0
+
+ data['per_util'] = flt(((data['billed_hours'] + data['non_billed_hours']) / TOTAL_HOURS) * 100, 2)
+
+ def generate_report_summary(self):
+ self.report_summary = []
+
+ if not self.data:
+ return
+
+ avg_utilization = 0.0
+ total_billed, total_non_billed = 0.0, 0.0
+ total_untracked = 0.0
+
+ for row in self.data:
+ avg_utilization += row['per_util']
+ total_billed += row['billed_hours']
+ total_non_billed += row['non_billed_hours']
+ total_untracked += row['untracked_hours']
+
+ avg_utilization /= len(self.data)
+ avg_utilization = flt(avg_utilization, 2)
+
+ THRESHOLD_PERCENTAGE = 70.0
+ self.report_summary = [
+ {
+ 'value': f'{avg_utilization}%',
+ 'indicator': 'Red' if avg_utilization < THRESHOLD_PERCENTAGE else 'Green',
+ 'label': _('Average Utilization'),
+ 'datatype': 'Percentage'
+ },
+ {
+ 'value': total_billed,
+ 'label': _('Total Billed Hours'),
+ 'datatype': 'Float'
+ },
+ {
+ 'value': total_non_billed,
+ 'label': _('Total Non-Billed Hours'),
+ 'datatype': 'Float'
+ },
+ {
+ 'value': total_untracked,
+ 'label': _('Total Untracked Hours'),
+ 'datatype': 'Float'
+ }
+ ]
+
+ def generate_chart_data(self):
+ self.chart = {}
+
+ labels = []
+ billed_hours = []
+ non_billed_hours = []
+ untracked_hours = []
+
+
+ for row in self.data:
+ labels.append(row.get('employee_name'))
+ billed_hours.append(row.get('billed_hours'))
+ non_billed_hours.append(row.get('non_billed_hours'))
+ untracked_hours.append(row.get('untracked_hours'))
+
+ self.chart = {
+ 'data': {
+ 'labels': labels[:30],
+ 'datasets': [
+ {
+ 'name': _('Billed Hours'),
+ 'values': billed_hours[:30]
+ },
+ {
+ 'name': _('Non-Billed Hours'),
+ 'values': non_billed_hours[:30]
+ },
+ {
+ 'name': _('Untracked Hours'),
+ 'values': untracked_hours[:30]
+ }
+ ]
+ },
+ 'type': 'bar',
+ 'barOptions': {
+ 'stacked': True
+ }
+ }
diff --git a/erpnext/projects/report/employee_hours_utilization_based_on_timesheet/test_employee_util.py b/erpnext/projects/report/employee_hours_utilization_based_on_timesheet/test_employee_util.py
new file mode 100644
index 0000000..977a10d
--- /dev/null
+++ b/erpnext/projects/report/employee_hours_utilization_based_on_timesheet/test_employee_util.py
@@ -0,0 +1,192 @@
+from __future__ import unicode_literals
+import unittest
+import frappe
+
+from frappe.utils.make_random import get_random
+from erpnext.projects.report.employee_hours_utilization_based_on_timesheet.employee_hours_utilization_based_on_timesheet import execute
+from erpnext.hr.doctype.employee.test_employee import make_employee
+from erpnext.projects.doctype.project.test_project import make_project
+
+class TestEmployeeUtilization(unittest.TestCase):
+ @classmethod
+ def setUpClass(cls):
+ # Create test employee
+ cls.test_emp1 = make_employee("test1@employeeutil.com", "_Test Company")
+ cls.test_emp2 = make_employee("test2@employeeutil.com", "_Test Company")
+
+ # Create test project
+ cls.test_project = make_project({"project_name": "_Test Project"})
+
+ # Create test timesheets
+ cls.create_test_timesheets()
+
+ @classmethod
+ def create_test_timesheets(cls):
+ timesheet1 = frappe.new_doc("Timesheet")
+ timesheet1.employee = cls.test_emp1
+ timesheet1.company = '_Test Company'
+
+ timesheet1.append("time_logs", {
+ "activity_type": get_random("Activity Type"),
+ "hours": 5,
+ "billable": 1,
+ "from_time": '2021-04-01 13:30:00.000000',
+ "to_time": '2021-04-01 18:30:00.000000'
+ })
+
+ timesheet1.save()
+ timesheet1.submit()
+
+ timesheet2 = frappe.new_doc("Timesheet")
+ timesheet2.employee = cls.test_emp2
+ timesheet2.company = '_Test Company'
+
+ timesheet2.append("time_logs", {
+ "activity_type": get_random("Activity Type"),
+ "hours": 10,
+ "billable": 0,
+ "from_time": '2021-04-01 13:30:00.000000',
+ "to_time": '2021-04-01 23:30:00.000000',
+ "project": cls.test_project.name
+ })
+
+ timesheet2.save()
+ timesheet2.submit()
+
+ @classmethod
+ def tearDownClass(cls):
+ # Delete time logs
+ frappe.db.sql("""
+ DELETE FROM `tabTimesheet Detail`
+ WHERE parent IN (
+ SELECT name
+ FROM `tabTimesheet`
+ WHERE company = '_Test Company'
+ )
+ """)
+
+ frappe.db.sql("DELETE FROM `tabTimesheet` WHERE company='_Test Company'")
+ frappe.db.sql(f"DELETE FROM `tabProject` WHERE name='{cls.test_project.name}'")
+
+ def test_utilization_report_with_required_filters_only(self):
+ filters = {
+ "company": "_Test Company",
+ "from_date": "2021-04-01",
+ "to_date": "2021-04-03"
+ }
+
+ report = execute(filters)
+
+ expected_data = self.get_expected_data_for_test_employees()
+ self.assertEqual(report[1], expected_data)
+
+ def test_utilization_report_for_single_employee(self):
+ filters = {
+ "company": "_Test Company",
+ "from_date": "2021-04-01",
+ "to_date": "2021-04-03",
+ "employee": self.test_emp1
+ }
+
+ report = execute(filters)
+
+ emp1_data = frappe.get_doc('Employee', self.test_emp1)
+ expected_data = [
+ {
+ 'employee': self.test_emp1,
+ 'employee_name': emp1_data.employee_name,
+ 'department': emp1_data.department,
+ 'billed_hours': 5.0,
+ 'non_billed_hours': 0.0,
+ 'total_hours': 18.0,
+ 'untracked_hours': 13.0,
+ 'per_util': 27.78
+ }
+ ]
+
+ self.assertEqual(report[1], expected_data)
+
+ def test_utilization_report_for_project(self):
+ filters = {
+ "company": "_Test Company",
+ "from_date": "2021-04-01",
+ "to_date": "2021-04-03",
+ "project": self.test_project.name
+ }
+
+ report = execute(filters)
+
+ emp2_data = frappe.get_doc('Employee', self.test_emp2)
+ expected_data = [
+ {
+ 'employee': self.test_emp2,
+ 'employee_name': emp2_data.employee_name,
+ 'department': emp2_data.department,
+ 'billed_hours': 0.0,
+ 'non_billed_hours': 10.0,
+ 'total_hours': 18.0,
+ 'untracked_hours': 8.0,
+ 'per_util': 55.56
+ }
+ ]
+
+ self.assertEqual(report[1], expected_data)
+
+ def test_utilization_report_for_department(self):
+ emp1_data = frappe.get_doc('Employee', self.test_emp1)
+ filters = {
+ "company": "_Test Company",
+ "from_date": "2021-04-01",
+ "to_date": "2021-04-03",
+ "department": emp1_data.department
+ }
+
+ report = execute(filters)
+
+ expected_data = self.get_expected_data_for_test_employees()
+ self.assertEqual(report[1], expected_data)
+
+ def test_report_summary_data(self):
+ filters = {
+ "company": "_Test Company",
+ "from_date": "2021-04-01",
+ "to_date": "2021-04-03"
+ }
+
+ report = execute(filters)
+ summary = report[4]
+ expected_summary_values = ['41.67%', 5.0, 10.0, 21.0]
+
+ self.assertEqual(len(summary), 4)
+
+ for i in range(4):
+ self.assertEqual(
+ summary[i]['value'], expected_summary_values[i]
+ )
+
+ def get_expected_data_for_test_employees(self):
+ emp1_data = frappe.get_doc('Employee', self.test_emp1)
+ emp2_data = frappe.get_doc('Employee', self.test_emp2)
+
+ return [
+ {
+ 'employee': self.test_emp2,
+ 'employee_name': emp2_data.employee_name,
+ 'department': emp2_data.department,
+ 'billed_hours': 0.0,
+ 'non_billed_hours': 10.0,
+ 'total_hours': 18.0,
+ 'untracked_hours': 8.0,
+ 'per_util': 55.56
+ },
+ {
+ 'employee': self.test_emp1,
+ 'employee_name': emp1_data.employee_name,
+ 'department': emp1_data.department,
+ 'billed_hours': 5.0,
+ 'non_billed_hours': 0.0,
+ 'total_hours': 18.0,
+ 'untracked_hours': 13.0,
+ 'per_util': 27.78
+ }
+ ]
\ No newline at end of file
diff --git a/erpnext/projects/workspace/projects/projects.json b/erpnext/projects/workspace/projects/projects.json
index 0ec1702..62ea3b6 100644
--- a/erpnext/projects/workspace/projects/projects.json
+++ b/erpnext/projects/workspace/projects/projects.json
@@ -131,6 +131,16 @@
"type": "Link"
},
{
+ "dependencies": "Timesheet",
+ "hidden": 0,
+ "is_query_report": 1,
+ "label": "Employee Hours Utilization",
+ "link_to": "Employee Hours Utilization Based On Timesheet",
+ "link_type": "Report",
+ "onboard": 0,
+ "type": "Link"
+ },
+ {
"dependencies": "Project",
"hidden": 0,
"is_query_report": 1,
@@ -161,7 +171,7 @@
"type": "Link"
}
],
- "modified": "2021-03-26 16:32:00.628561",
+ "modified": "2021-04-19 16:17:30.142545",
"modified_by": "Administrator",
"module": "Projects",
"name": "Projects",