feat: profitability report
diff --git a/erpnext/projects/report/profitability/__init__.py b/erpnext/projects/report/profitability/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/projects/report/profitability/__init__.py
diff --git a/erpnext/projects/report/profitability/profitability.js b/erpnext/projects/report/profitability/profitability.js
new file mode 100644
index 0000000..dbf9187
--- /dev/null
+++ b/erpnext/projects/report/profitability/profitability.js
@@ -0,0 +1,32 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Profitability"] = {
+ "filters": [
+ {
+ "fieldname": "start_date",
+ "label": __("Start Date"),
+ "fieldtype": "Date",
+ "reqd": 1
+ },
+ {
+ "fieldname": "end_date",
+ "label": __("End Date"),
+ "fieldtype": "Date",
+ "reqd": 1
+ },
+ {
+ "fieldname": "customer_name",
+ "label": __("Customer"),
+ "fieldtype": "Link",
+ "options": "Customer"
+ },
+ {
+ "fieldname": "employee",
+ "label": __("Employee"),
+ "fieldtype": "Link",
+ "options": "Employee"
+ }
+ ]
+};
diff --git a/erpnext/projects/report/profitability/profitability.json b/erpnext/projects/report/profitability/profitability.json
new file mode 100644
index 0000000..4f91acc
--- /dev/null
+++ b/erpnext/projects/report/profitability/profitability.json
@@ -0,0 +1,39 @@
+{
+ "add_total_row": 0,
+ "columns": [],
+ "creation": "2021-03-18 10:19:40.124932",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "filters": [],
+ "idx": 0,
+ "is_standard": "Yes",
+ "json": "{}",
+ "modified": "2021-03-18 10:20:15.559305",
+ "modified_by": "Administrator",
+ "module": "Projects",
+ "name": "Profitability",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Timesheet",
+ "report_name": "Profitability",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Projects User"
+ },
+ {
+ "role": "HR User"
+ },
+ {
+ "role": "Manufacturing User"
+ },
+ {
+ "role": "Employee"
+ },
+ {
+ "role": "Accounts User"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/projects/report/profitability/profitability.py b/erpnext/projects/report/profitability/profitability.py
new file mode 100644
index 0000000..48adf97
--- /dev/null
+++ b/erpnext/projects/report/profitability/profitability.py
@@ -0,0 +1,119 @@
+# 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 _
+
+def execute(filters=None):
+ columns, data = [], []
+ data = get_data(filters)
+ columns = get_columns()
+ return columns, data
+
+def get_columns():
+ return [
+ {
+ "fieldname": "customer_name",
+ "label": _("Customer"),
+ "fieldtype": "Link",
+ "options": "Customer",
+ "width": 150
+ },
+ {
+ "fieldname": "title",
+ "label": _("Name"),
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "fieldname": "employee",
+ "label": _("Employee"),
+ "fieldtype": "Link",
+ "options": "employee",
+ "width": 150
+ },
+ {
+ "fieldname": "grand_total",
+ "label": _("Bill Amount"),
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 120
+ },
+ {
+ "fieldname": "gross_pay",
+ "label": _("Cost"),
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 120
+ },
+ {
+ "fieldname": "profit",
+ "label": _("Profit"),
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 120
+ },
+ {
+ "fieldname": "end_date",
+ "label": _("End Date"),
+ "fieldtype": "Date",
+ "width": 120
+ },
+ {
+ "fieldname": "total_billed_hours",
+ "label": _("Total Billed Hours"),
+ "fieldtype": "Int",
+ "width": 120
+ },
+ {
+ "fieldname": "utilization",
+ "label": _("Utilization"),
+ "fieldtype": "Percentage",
+ "width": 120
+ },
+ {
+ "fieldname": "fractional_cost",
+ "label": _("Fractional Cost"),
+ "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 """
+ 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