Merge branch 'develop' into profitability-report
diff --git a/erpnext/hooks.py b/erpnext/hooks.py
index bb6cd8b..af357cd 100644
--- a/erpnext/hooks.py
+++ b/erpnext/hooks.py
@@ -262,7 +262,6 @@
],
"on_trash": "erpnext.regional.check_deletion_permission",
"validate": [
- "erpnext.regional.india.utils.validate_document_name",
"erpnext.regional.india.utils.update_taxable_values"
]
},
diff --git a/erpnext/hr/doctype/hr_settings/hr_settings.json b/erpnext/hr/doctype/hr_settings/hr_settings.json
index 09666c5..3553229 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",
+ "standard_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": "standard_working_hours",
+ "fieldtype": "Int",
+ "label": "Standard Working Hours"
}
],
"icon": "fa fa-cog",
"idx": 1,
"issingle": 1,
"links": [],
- "modified": "2021-03-14 02:04:22.907159",
+ "modified": "2021-04-16 15:45:18.467699",
"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/project_profitability/__init__.py b/erpnext/projects/report/project_profitability/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/projects/report/project_profitability/__init__.py
diff --git a/erpnext/projects/report/project_profitability/project_profitability.js b/erpnext/projects/report/project_profitability/project_profitability.js
new file mode 100644
index 0000000..13ae19b
--- /dev/null
+++ b/erpnext/projects/report/project_profitability/project_profitability.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["Project 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,
+ "default": frappe.datetime.add_months(frappe.datetime.get_today(), -1)
+ },
+ {
+ "fieldname": "end_date",
+ "label": __("End Date"),
+ "fieldtype": "Date",
+ "reqd": 1,
+ "default": frappe.datetime.now_date()
+ },
+ {
+ "fieldname": "customer_name",
+ "label": __("Customer"),
+ "fieldtype": "Link",
+ "options": "Customer"
+ },
+ {
+ "fieldname": "employee",
+ "label": __("Employee"),
+ "fieldtype": "Link",
+ "options": "Employee"
+ },
+ {
+ "fieldname": "project",
+ "label": __("Project"),
+ "fieldtype": "Link",
+ "options": "Project"
+ }
+ ]
+};
diff --git a/erpnext/projects/report/project_profitability/project_profitability.json b/erpnext/projects/report/project_profitability/project_profitability.json
new file mode 100644
index 0000000..0b092cd
--- /dev/null
+++ b/erpnext/projects/report/project_profitability/project_profitability.json
@@ -0,0 +1,44 @@
+{
+ "add_total_row": 0,
+ "columns": [],
+ "creation": "2021-04-16 15:50:28.914872",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "filters": [],
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2021-04-16 15:50:48.490866",
+ "modified_by": "Administrator",
+ "module": "Projects",
+ "name": "Project Profitability",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Timesheet",
+ "report_name": "Project Profitability",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "HR User"
+ },
+ {
+ "role": "Accounts User"
+ },
+ {
+ "role": "Employee"
+ },
+ {
+ "role": "Projects User"
+ },
+ {
+ "role": "Manufacturing User"
+ },
+ {
+ "role": "Employee Self Service"
+ },
+ {
+ "role": "HR Manager"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/projects/report/project_profitability/project_profitability.py b/erpnext/projects/report/project_profitability/project_profitability.py
new file mode 100644
index 0000000..405c6fd
--- /dev/null
+++ b/erpnext/projects/report/project_profitability/project_profitability.py
@@ -0,0 +1,239 @@
+# 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 erpnext.payroll.doctype.salary_structure_assignment.salary_structure_assignment import get_employee_currency
+from erpnext.accounts.utils import get_currency_precision
+from frappe.utils import flt
+from erpnext.setup.utils import get_exchange_rate
+
+def execute(filters=None):
+ columns, data = [], []
+ data = get_data(filters)
+ columns = get_columns()
+ charts = get_chart_data(data)
+ return columns, data, None, charts
+
+def get_data(filters):
+ data = get_rows(filters)
+ data = handle_multi_currency(data, filters)
+ data = calculate_cost_and_profit(data)
+ return data
+
+def get_rows(filters):
+ conditions = get_conditions(filters)
+ standard_working_hours = frappe.db.get_single_value("HR Settings", "standard_working_hours")
+ sql = """
+ SELECT
+ *
+ FROM
+ (SELECT
+ si.customer_name,si.base_grand_total,
+ si.name as voucher_no,tabTimesheet.employee,
+ tabTimesheet.title as employee_name,tabTimesheet.parent_project as project,
+ tabTimesheet.start_date,tabTimesheet.end_date,
+ tabTimesheet.total_billed_hours,tabTimesheet.name as timesheet,
+ ss.base_gross_pay,ss.total_working_days,
+ tabTimesheet.total_billed_hours/(ss.total_working_days * {0}) 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" """.format(standard_working_hours)
+ if conditions:
+ sql += """
+ WHERE
+ {0}) as t""".format(conditions)
+ return frappe.db.sql(sql,filters, as_dict=True)
+
+def handle_multi_currency(data, filters):
+ currency_precision = get_currency_precision() or 2
+ company_currency = frappe.get_cached_value("Company", filters.get("company"), "default_currency")
+
+ for row in data:
+ row.currency = company_currency
+
+ if filters.get("employee"):
+ party_currency = get_employee_currency(row.employee)
+
+ if filters.get("customer_name"):
+ party_currency = frappe.db.get_value("Customer", row.customer_name, ["default_currency"])
+
+ if party_currency and party_currency != company_currency:
+ exchange_rate = get_exchange_rate(company_currency, party_currency)
+ row.currency = party_currency
+
+ row.base_grand_total = flt(flt(row.base_grand_total) *
+ flt(exchange_rate), currency_precision)
+
+ row.base_gross_pay = flt(flt(row.base_gross_pay) *
+ flt(exchange_rate), currency_precision)
+
+ row.profit = flt(flt(row.profit) *
+ flt(exchange_rate), currency_precision)
+
+ row.fractional_cost = flt(flt(row.fractional_cost) *
+ flt(exchange_rate), currency_precision)
+
+ return data
+
+def calculate_cost_and_profit(data):
+ for row in data:
+ row.fractional_cost = row.base_gross_pay * row.utilization
+ row.profit = row.base_grand_total - row.base_gross_pay * row.utilization
+ return data
+
+def get_conditions(filters):
+ conditions = []
+
+ if filters.get("company"):
+ conditions.append("tabTimesheet.company='{0}'".format(filters.get("company")))
+
+ if filters.get("start_date"):
+ conditions.append("tabTimesheet.start_date>='{0}'".format(filters.get("start_date")))
+
+ if filters.get("end_date"):
+ conditions.append("tabTimesheet.end_date<='{0}'".format(filters.get("end_date")))
+
+ if filters.get("customer_name"):
+ conditions.append("si.customer_name='{0}'".format(filters.get("customer_name")))
+
+ if filters.get("employee"):
+ conditions.append("tabTimesheet.employee='{0}'".format(filters.get("employee")))
+
+ if filters.get("project"):
+ conditions.append("tabTimesheet.parent_project='{0}'".format(filters.get("project")))
+
+ 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("employee_name") + " - " + 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 [
+ {
+ "fieldname": "customer_name",
+ "label": _("Customer"),
+ "fieldtype": "Link",
+ "options": "Customer",
+ "width": 150
+ },
+ {
+ "fieldname": "employee",
+ "label": _("Employee"),
+ "fieldtype": "Link",
+ "options": "Employee",
+ "width": 130
+ },
+ {
+ "fieldname": "employee_name",
+ "label": _("Employee Name"),
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "fieldname": "voucher_no",
+ "label": _("Sales Invoice"),
+ "fieldtype": "Link",
+ "options": "Sales Invoice",
+ "width": 180
+ },
+ {
+ "fieldname": "timesheet",
+ "label": _("Timesheet"),
+ "fieldtype": "Link",
+ "options": "Timesheet",
+ "width": 130
+ },
+ {
+ "fieldname": "project",
+ "label": _("Project"),
+ "fieldtype": "Link",
+ "options": "Project",
+ "width": 100
+ },
+ {
+ "fieldname": "base_grand_total",
+ "label": _("Bill Amount"),
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 100
+ },
+ {
+ "fieldname": "base_gross_pay",
+ "label": _("Cost"),
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 100
+ },
+ {
+ "fieldname": "profit",
+ "label": _("Profit"),
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 100
+ },
+ {
+ "fieldname": "utilization",
+ "label": _("Utilization"),
+ "fieldtype": "Percentage",
+ "width": 120
+ },
+ {
+ "fieldname": "fractional_cost",
+ "label": _("Fractional Cost"),
+ "fieldtype": "Int",
+ "width": 100
+ },
+ {
+ "fieldname": "total_billed_hours",
+ "label": _("Total Billed Hours"),
+ "fieldtype": "Int",
+ "width": 100
+ },
+ {
+ "fieldname": "start_date",
+ "label": _("Start Date"),
+ "fieldtype": "Date",
+ "width": 120
+ },
+ {
+ "fieldname": "end_date",
+ "label": _("End Date"),
+ "fieldtype": "Date",
+ "width": 120
+ },
+ {
+ "label": _("Currency"),
+ "fieldname": "currency",
+ "fieldtype": "Link",
+ "options": "Currency",
+ "width": 100
+ }
+ ]
\ No newline at end of file
diff --git a/erpnext/projects/report/project_profitability/test_project_profitability.py b/erpnext/projects/report/project_profitability/test_project_profitability.py
new file mode 100644
index 0000000..96659bc
--- /dev/null
+++ b/erpnext/projects/report/project_profitability/test_project_profitability.py
@@ -0,0 +1,51 @@
+from __future__ import unicode_literals
+import unittest
+import frappe
+from frappe.utils import getdate, nowdate
+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.project_profitability.project_profitability import execute
+
+class TestProjectProfitability(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_project_profitability(self):
+ filters = {
+ 'company': '_Test Company',
+ 'start_date': getdate(),
+ 'end_date': getdate()
+ }
+
+ report = execute(filters)
+ expected_data = [
+ {
+ 'customer_name': '_Test Customer',
+ 'employee_name': 'test_employee_9@salary.com',
+ 'base_grand_total': 100.0,
+ 'base_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','employee_name','base_grand_total','base_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 0ec1702..b65e9aa 100644
--- a/erpnext/projects/workspace/projects/projects.json
+++ b/erpnext/projects/workspace/projects/projects.json
@@ -131,6 +131,16 @@
"type": "Link"
},
{
+ "dependencies": "Timesheet, Sales Invoice, Salary Slip",
+ "hidden": 0,
+ "is_query_report": 1,
+ "label": "Project Profitability",
+ "link_to": "Project Profitability",
+ "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-16 16:27:16.548780",
"modified_by": "Administrator",
"module": "Projects",
"name": "Projects",
diff --git a/erpnext/regional/india/utils.py b/erpnext/regional/india/utils.py
index 6338056..0c757e9 100644
--- a/erpnext/regional/india/utils.py
+++ b/erpnext/regional/india/utils.py
@@ -161,7 +161,7 @@
# Date was chosen as start of next FY to avoid irritating current users.
if country != "India" or getdate(doc.posting_date) < getdate("2021-04-01"):
return
-
+ print(doc.name)
if len(doc.name) > 16:
frappe.throw(_("Maximum length of document number should be 16 characters as per GST rules. Please change the naming series."))