feat: profitability report default working hours and tests
diff --git a/erpnext/hr/doctype/hr_settings/hr_settings.json b/erpnext/hr/doctype/hr_settings/hr_settings.json
index 09666c5..4fa50c4 100644
--- a/erpnext/hr/doctype/hr_settings/hr_settings.json
+++ b/erpnext/hr/doctype/hr_settings/hr_settings.json
@@ -10,6 +10,7 @@
"retirement_age",
"emp_created_by",
"column_break_4",
+ "default_working_hours",
"stop_birthday_reminders",
"expense_approver_mandatory_in_expense_claim",
"leave_settings",
@@ -143,13 +144,19 @@
"fieldname": "send_leave_notification",
"fieldtype": "Check",
"label": "Send Leave Notification"
+ },
+ {
+ "default": "8",
+ "fieldname": "default_working_hours",
+ "fieldtype": "Int",
+ "label": "Default Working Hours"
}
],
"icon": "fa fa-cog",
"idx": 1,
"issingle": 1,
"links": [],
- "modified": "2021-03-14 02:04:22.907159",
+ "modified": "2021-03-25 13:18:21.648077",
"modified_by": "Administrator",
"module": "HR",
"name": "HR Settings",
diff --git a/erpnext/projects/doctype/timesheet/test_timesheet.py b/erpnext/projects/doctype/timesheet/test_timesheet.py
index f7c764e..d21ac0f 100644
--- a/erpnext/projects/doctype/timesheet/test_timesheet.py
+++ b/erpnext/projects/doctype/timesheet/test_timesheet.py
@@ -151,11 +151,11 @@
settings.save()
-def make_salary_structure_for_timesheet(employee):
+def make_salary_structure_for_timesheet(employee, company=None):
salary_structure_name = "Timesheet Salary Structure Test"
frequency = "Monthly"
- salary_structure = make_salary_structure(salary_structure_name, frequency, dont_submit=True)
+ salary_structure = make_salary_structure(salary_structure_name, frequency, company=company, dont_submit=True)
salary_structure.salary_component = "Timesheet Component"
salary_structure.salary_slip_based_on_timesheet = 1
salary_structure.hour_rate = 50.0
diff --git a/erpnext/projects/report/profitability/profitability.js b/erpnext/projects/report/profitability/profitability.js
index dbf9187..6cb6e39 100644
--- a/erpnext/projects/report/profitability/profitability.js
+++ b/erpnext/projects/report/profitability/profitability.js
@@ -5,16 +5,26 @@
frappe.query_reports["Profitability"] = {
"filters": [
{
+ "fieldname": "company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "default": frappe.defaults.get_user_default("Company"),
+ "reqd": 1
+ },
+ {
"fieldname": "start_date",
"label": __("Start Date"),
"fieldtype": "Date",
- "reqd": 1
+ "reqd": 1,
+ "default": frappe.datetime.add_months(frappe.datetime.get_today(), -1)
},
{
"fieldname": "end_date",
"label": __("End Date"),
"fieldtype": "Date",
- "reqd": 1
+ "reqd": 1,
+ "default": frappe.datetime.now_date()
},
{
"fieldname": "customer_name",
diff --git a/erpnext/projects/report/profitability/profitability.py b/erpnext/projects/report/profitability/profitability.py
index 0edecd8..8c052b5 100644
--- a/erpnext/projects/report/profitability/profitability.py
+++ b/erpnext/projects/report/profitability/profitability.py
@@ -4,12 +4,80 @@
from __future__ import unicode_literals
import frappe
from frappe import _
+from frappe.utils import nowdate, time_diff_in_hours
def execute(filters=None):
columns, data = [], []
data = get_data(filters)
columns = get_columns()
- return columns, data
+ charts = get_chart_data(data)
+ return columns, data, None, charts
+
+def get_data(filters):
+ conditions = get_conditions(filters)
+ default_working_hours = frappe.db.get_single_value("HR Settings", "default_working_hours")
+ sql = """
+ select
+ *,
+ t.gross_pay * t.utilization as fractional_cost,
+ t.grand_total - t.gross_pay * t.utilization as profit
+ from
+ (select
+ si.customer_name,tabTimesheet.title,tabTimesheet.employee,si.grand_total,si.name as voucher_no,
+ ss.gross_pay,ss.total_working_days,tabTimesheet.end_date,tabTimesheet.total_billed_hours,tabTimesheet.name as timesheet,
+ tabTimesheet.total_billed_hours/(ss.total_working_days * %s) as utilization
+ from
+ `tabSalary Slip Timesheet` as sst join `tabTimesheet` on tabTimesheet.name = sst.time_sheet
+ join `tabSales Invoice Timesheet` as sit on sit.time_sheet = tabTimesheet.name
+ join `tabSales Invoice` as si on si.name = sit.parent and si.status != "Cancelled"
+ join `tabSalary Slip` as ss on ss.name = sst.parent and ss.status != "Cancelled" """%(default_working_hours)
+ if conditions:
+ sql += """
+ where
+ %s) as t"""%(conditions)
+ data = frappe.db.sql(sql,filters, as_dict=True)
+ return data
+
+def get_conditions(filters):
+ conditions = []
+ if filters.get("company"):
+ conditions.append("tabTimesheet.company='%s'"%filters.get("company"))
+ if filters.get("customer_name"):
+ conditions.append("si.customer_name='%s'"%filters.get("customer_name"))
+ if filters.get("start_date"):
+ conditions.append("tabTimesheet.start_date>='%s'"%filters.get("start_date"))
+ if filters.get("end_date"):
+ conditions.append("tabTimesheet.end_date<='%s'"%filters.get("end_date"))
+ if filters.get("employee"):
+ conditions.append("tabTimesheet.employee='%s'"%filters.get("employee"))
+
+ conditions = " and ".join(conditions)
+ return conditions
+
+def get_chart_data(data):
+ if not data:
+ return None
+
+ labels = []
+ utilization = []
+
+ for entry in data:
+ labels.append(entry.get("title") + " - " + str(entry.get("end_date")))
+ utilization.append(entry.get("utilization"))
+ charts = {
+ 'data': {
+ 'labels': labels,
+ 'datasets': [
+ {
+ 'name': 'Utilization',
+ 'values': utilization
+ }
+ ]
+ },
+ 'type': 'bar',
+ 'colors': ['#84BDD5']
+ }
+ return charts
def get_columns():
return [
@@ -30,7 +98,21 @@
"fieldname": "employee",
"label": _("Employee"),
"fieldtype": "Link",
- "options": "employee",
+ "options": "Employee",
+ "width": 150
+ },
+ {
+ "fieldname": "voucher_no",
+ "label": _("Sales Invoice"),
+ "fieldtype": "Link",
+ "options": "Sales Invoice",
+ "width": 200
+ },
+ {
+ "fieldname": "timesheet",
+ "label": _("Timesheet"),
+ "fieldtype": "Link",
+ "options": "Timesheet",
"width": 150
},
{
@@ -64,7 +146,7 @@
"fieldname": "total_billed_hours",
"label": _("Total Billed Hours"),
"fieldtype": "Int",
- "width": 120
+ "width": 100
},
{
"fieldname": "utilization",
@@ -78,42 +160,4 @@
"fieldtype": "Int",
"width": 100
}
- ]
-
-def get_data(filters):
- conditions = get_conditions(filters)
- sql = """
- select
- *,
- t.gross_pay * t.utilization as fractional_cost,
- t.grand_total - t.gross_pay * t.utilization as profit
- from
- (select
- si.customer_name,tabTimesheet.title,tabTimesheet.employee,si.grand_total,si.name as voucher_no,
- ss.gross_pay,ss.total_working_days,tabTimesheet.end_date,tabTimesheet.total_billed_hours,
- tabTimesheet.total_billed_hours/(ss.total_working_days * 8) as utilization
- from
- `tabSalary Slip Timesheet` as sst join `tabTimesheet` on tabTimesheet.name = sst.time_sheet
- join `tabSales Invoice Timesheet` as sit on sit.time_sheet = tabTimesheet. name
- join `tabSales Invoice` as si on si. name = sit.parent and si.status != "Cancelled"
- join `tabSalary Slip` as ss on ss.name = sst.parent and ss.status != "Cancelled" """
- if conditions:
- sql += """
- where
- %s) as t"""%(conditions)
- data = frappe.db.sql(sql,filters, as_dict=True)
-
- return data
-
-def get_conditions(filters):
- conditions = []
- if filters.get("customer_name"):
- conditions.append("si.customer_name='%s'"%filters.get("customer_name"))
- if filters.get("start_date"):
- conditions.append("tabTimesheet.start_date>='%s'"%filters.get("start_date"))
- if filters.get("end_date"):
- conditions.append("tabTimesheet.end_date<='%s'"%filters.get("end_date"))
- if filters.get("employee"):
- conditions.append("tabTimesheet.employee='%s'"%filters.get("employee"))
- conditions = " and ".join(conditions)
- return conditions
\ No newline at end of file
+ ]
\ No newline at end of file
diff --git a/erpnext/projects/report/profitability/test_profitability.py b/erpnext/projects/report/profitability/test_profitability.py
new file mode 100644
index 0000000..dfdef0d
--- /dev/null
+++ b/erpnext/projects/report/profitability/test_profitability.py
@@ -0,0 +1,52 @@
+from __future__ import unicode_literals
+import unittest
+import frappe
+import datetime
+from frappe.utils import getdate, nowdate, add_days, add_months
+from erpnext.hr.doctype.employee.test_employee import make_employee
+from erpnext.projects.doctype.timesheet.test_timesheet import make_salary_structure_for_timesheet, make_timesheet
+from erpnext.projects.doctype.timesheet.timesheet import make_salary_slip, make_sales_invoice
+from erpnext.projects.report.profitability.profitability import execute
+
+class TestProfitability(unittest.TestCase):
+ @classmethod
+ def setUp(self):
+ emp = make_employee("test_employee_9@salary.com", company="_Test Company")
+ if not frappe.db.exists("Salary Component", "Timesheet Component"):
+ frappe.get_doc({"doctype": "Salary Component", "salary_component": "Timesheet Component"}).insert()
+ make_salary_structure_for_timesheet(emp, company="_Test Company")
+ self.timesheet = make_timesheet(emp, simulate = True, billable=1)
+ self.salary_slip = make_salary_slip(self.timesheet.name)
+ self.salary_slip.submit()
+ self.sales_invoice = make_sales_invoice(self.timesheet.name, '_Test Item', '_Test Customer')
+ self.sales_invoice.due_date = nowdate()
+ self.sales_invoice.submit()
+
+ def test_profitability(self):
+ filters = {
+ 'company': '_Test Company',
+ 'start_date': getdate(),
+ 'end_date': getdate()
+ }
+
+ report = execute(filters)
+ expected_data = [
+ {
+ "customer_name": "_Test Customer",
+ "title": "test_employee_9@salary.com",
+ "grand_total": 100.0,
+ "gross_pay": 78100.0,
+ "profit": -19425.0,
+ "total_billed_hours": 2.0,
+ "utilization": 0.25,
+ "fractional_cost": 19525.0,
+ "total_working_days": 1.0
+ }
+ ]
+ for key in ["customer_name","title","grand_total","gross_pay","profit","total_billed_hours","utilization","fractional_cost","total_working_days"]:
+ self.assertEqual(expected_data[0].get(key), report[1][0].get(key))
+
+ def tearDown(self):
+ frappe.get_doc("Sales Invoice", self.sales_invoice.name).cancel()
+ frappe.get_doc("Salary Slip", self.salary_slip.name).cancel()
+ frappe.get_doc("Timesheet", self.timesheet.name).cancel()
\ No newline at end of file
diff --git a/erpnext/projects/workspace/projects/projects.json b/erpnext/projects/workspace/projects/projects.json
index dbbd7e1..8703ffb 100644
--- a/erpnext/projects/workspace/projects/projects.json
+++ b/erpnext/projects/workspace/projects/projects.json
@@ -15,6 +15,7 @@
"hide_custom": 0,
"icon": "project",
"idx": 0,
+ "is_default": 0,
"is_standard": 1,
"label": "Projects",
"links": [
@@ -130,6 +131,16 @@
"type": "Link"
},
{
+ "dependencies": "Timesheet, Sales Invoice, Salary Slip",
+ "hidden": 0,
+ "is_query_report": 1,
+ "label": "Profitability",
+ "link_to": "Profitability",
+ "link_type": "Report",
+ "onboard": 0,
+ "type": "Link"
+ },
+ {
"dependencies": "Project",
"hidden": 0,
"is_query_report": 1,
@@ -150,7 +161,7 @@
"type": "Link"
}
],
- "modified": "2020-12-01 13:38:37.856224",
+ "modified": "2021-03-25 13:25:17.609608",
"modified_by": "Administrator",
"module": "Projects",
"name": "Projects",