feat: report Timesheet Billing Summary (#37451)
diff --git a/erpnext/projects/report/billing_summary.py b/erpnext/projects/report/billing_summary.py
deleted file mode 100644
index ac1524a..0000000
--- a/erpnext/projects/report/billing_summary.py
+++ /dev/null
@@ -1,155 +0,0 @@
-# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
-# For license information, please see license.txt
-
-
-import frappe
-from frappe import _
-from frappe.utils import flt, time_diff_in_hours
-
-
-def get_columns():
- return [
- {
- "label": _("Employee ID"),
- "fieldtype": "Link",
- "fieldname": "employee",
- "options": "Employee",
- "width": 300,
- },
- {
- "label": _("Employee Name"),
- "fieldtype": "data",
- "fieldname": "employee_name",
- "hidden": 1,
- "width": 200,
- },
- {
- "label": _("Timesheet"),
- "fieldtype": "Link",
- "fieldname": "timesheet",
- "options": "Timesheet",
- "width": 150,
- },
- {"label": _("Working Hours"), "fieldtype": "Float", "fieldname": "total_hours", "width": 150},
- {
- "label": _("Billable Hours"),
- "fieldtype": "Float",
- "fieldname": "total_billable_hours",
- "width": 150,
- },
- {"label": _("Billing Amount"), "fieldtype": "Currency", "fieldname": "amount", "width": 150},
- ]
-
-
-def get_data(filters):
- data = []
- if filters.from_date > filters.to_date:
- frappe.msgprint(_("From Date can not be greater than To Date"))
- return data
-
- timesheets = get_timesheets(filters)
-
- filters.from_date = frappe.utils.get_datetime(filters.from_date)
- filters.to_date = frappe.utils.add_to_date(
- frappe.utils.get_datetime(filters.to_date), days=1, seconds=-1
- )
-
- timesheet_details = get_timesheet_details(filters, timesheets.keys())
-
- for ts, ts_details in timesheet_details.items():
- total_hours = 0
- total_billing_hours = 0
- total_amount = 0
-
- for row in ts_details:
- from_time, to_time = filters.from_date, filters.to_date
-
- if row.to_time < from_time or row.from_time > to_time:
- continue
-
- if row.from_time > from_time:
- from_time = row.from_time
-
- if row.to_time < to_time:
- to_time = row.to_time
-
- activity_duration, billing_duration = get_billable_and_total_duration(row, from_time, to_time)
-
- total_hours += activity_duration
- total_billing_hours += billing_duration
- total_amount += billing_duration * flt(row.billing_rate)
-
- if total_hours:
- data.append(
- {
- "employee": timesheets.get(ts).employee,
- "employee_name": timesheets.get(ts).employee_name,
- "timesheet": ts,
- "total_billable_hours": total_billing_hours,
- "total_hours": total_hours,
- "amount": total_amount,
- }
- )
-
- return data
-
-
-def get_timesheets(filters):
- record_filters = [
- ["start_date", "<=", filters.to_date],
- ["end_date", ">=", filters.from_date],
- ]
- if not filters.get("include_draft_timesheets"):
- record_filters.append(["docstatus", "=", 1])
- else:
- record_filters.append(["docstatus", "!=", 2])
- if "employee" in filters:
- record_filters.append(["employee", "=", filters.employee])
-
- timesheets = frappe.get_all(
- "Timesheet", filters=record_filters, fields=["employee", "employee_name", "name"]
- )
- timesheet_map = frappe._dict()
- for d in timesheets:
- timesheet_map.setdefault(d.name, d)
-
- return timesheet_map
-
-
-def get_timesheet_details(filters, timesheet_list):
- timesheet_details_filter = {"parent": ["in", timesheet_list]}
-
- if "project" in filters:
- timesheet_details_filter["project"] = filters.project
-
- timesheet_details = frappe.get_all(
- "Timesheet Detail",
- filters=timesheet_details_filter,
- fields=[
- "from_time",
- "to_time",
- "hours",
- "is_billable",
- "billing_hours",
- "billing_rate",
- "parent",
- ],
- )
-
- timesheet_details_map = frappe._dict()
- for d in timesheet_details:
- timesheet_details_map.setdefault(d.parent, []).append(d)
-
- return timesheet_details_map
-
-
-def get_billable_and_total_duration(activity, start_time, end_time):
- precision = frappe.get_precision("Timesheet Detail", "hours")
- activity_duration = time_diff_in_hours(end_time, start_time)
- billing_duration = 0.0
- if activity.is_billable:
- billing_duration = activity.billing_hours
- if activity_duration != activity.billing_hours:
- billing_duration = activity_duration * activity.billing_hours / activity.hours
-
- return flt(activity_duration, precision), flt(billing_duration, precision)
diff --git a/erpnext/projects/report/employee_billing_summary/__init__.py b/erpnext/projects/report/employee_billing_summary/__init__.py
deleted file mode 100644
index e69de29..0000000
--- a/erpnext/projects/report/employee_billing_summary/__init__.py
+++ /dev/null
diff --git a/erpnext/projects/report/employee_billing_summary/employee_billing_summary.js b/erpnext/projects/report/employee_billing_summary/employee_billing_summary.js
deleted file mode 100644
index 2c25465..0000000
--- a/erpnext/projects/report/employee_billing_summary/employee_billing_summary.js
+++ /dev/null
@@ -1,34 +0,0 @@
-// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
-// For license information, please see license.txt
-
-
-frappe.query_reports["Employee Billing Summary"] = {
- "filters": [
- {
- fieldname: "employee",
- label: __("Employee"),
- fieldtype: "Link",
- options: "Employee",
- reqd: 1
- },
- {
- fieldname:"from_date",
- label: __("From Date"),
- fieldtype: "Date",
- default: frappe.datetime.add_months(frappe.datetime.month_start(), -1),
- reqd: 1
- },
- {
- fieldname:"to_date",
- label: __("To Date"),
- fieldtype: "Date",
- default: frappe.datetime.add_days(frappe.datetime.month_start(), -1),
- reqd: 1
- },
- {
- fieldname:"include_draft_timesheets",
- label: __("Include Timesheets in Draft Status"),
- fieldtype: "Check",
- },
- ]
-}
diff --git a/erpnext/projects/report/employee_billing_summary/employee_billing_summary.py b/erpnext/projects/report/employee_billing_summary/employee_billing_summary.py
deleted file mode 100644
index a2f7378..0000000
--- a/erpnext/projects/report/employee_billing_summary/employee_billing_summary.py
+++ /dev/null
@@ -1,15 +0,0 @@
-# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
-# For license information, please see license.txt
-
-
-import frappe
-
-from erpnext.projects.report.billing_summary import get_columns, get_data
-
-
-def execute(filters=None):
- filters = frappe._dict(filters or {})
- columns = get_columns()
-
- data = get_data(filters)
- return columns, data
diff --git a/erpnext/projects/report/project_billing_summary/project_billing_summary.js b/erpnext/projects/report/project_billing_summary/project_billing_summary.js
deleted file mode 100644
index fce0c68..0000000
--- a/erpnext/projects/report/project_billing_summary/project_billing_summary.js
+++ /dev/null
@@ -1,34 +0,0 @@
-// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
-// For license information, please see license.txt
-
-
-frappe.query_reports["Project Billing Summary"] = {
- "filters": [
- {
- fieldname: "project",
- label: __("Project"),
- fieldtype: "Link",
- options: "Project",
- reqd: 1
- },
- {
- fieldname:"from_date",
- label: __("From Date"),
- fieldtype: "Date",
- default: frappe.datetime.add_months(frappe.datetime.month_start(), -1),
- reqd: 1
- },
- {
- fieldname:"to_date",
- label: __("To Date"),
- fieldtype: "Date",
- default: frappe.datetime.add_days(frappe.datetime.month_start(),-1),
- reqd: 1
- },
- {
- fieldname:"include_draft_timesheets",
- label: __("Include Timesheets in Draft Status"),
- fieldtype: "Check",
- },
- ]
-}
diff --git a/erpnext/projects/report/project_billing_summary/project_billing_summary.json b/erpnext/projects/report/project_billing_summary/project_billing_summary.json
deleted file mode 100644
index 817d0cd..0000000
--- a/erpnext/projects/report/project_billing_summary/project_billing_summary.json
+++ /dev/null
@@ -1,36 +0,0 @@
-{
- "add_total_row": 1,
- "creation": "2019-03-11 16:22:39.460524",
- "disable_prepared_report": 0,
- "disabled": 0,
- "docstatus": 0,
- "doctype": "Report",
- "idx": 0,
- "is_standard": "Yes",
- "modified": "2019-06-13 15:54:55.255947",
- "modified_by": "Administrator",
- "module": "Projects",
- "name": "Project Billing Summary",
- "owner": "Administrator",
- "prepared_report": 0,
- "ref_doctype": "Timesheet",
- "report_name": "Project Billing Summary",
- "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/project_billing_summary/project_billing_summary.py b/erpnext/projects/report/project_billing_summary/project_billing_summary.py
deleted file mode 100644
index a2f7378..0000000
--- a/erpnext/projects/report/project_billing_summary/project_billing_summary.py
+++ /dev/null
@@ -1,15 +0,0 @@
-# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
-# For license information, please see license.txt
-
-
-import frappe
-
-from erpnext.projects.report.billing_summary import get_columns, get_data
-
-
-def execute(filters=None):
- filters = frappe._dict(filters or {})
- columns = get_columns()
-
- data = get_data(filters)
- return columns, data
diff --git a/erpnext/projects/report/project_billing_summary/__init__.py b/erpnext/projects/report/timesheet_billing_summary/__init__.py
similarity index 100%
rename from erpnext/projects/report/project_billing_summary/__init__.py
rename to erpnext/projects/report/timesheet_billing_summary/__init__.py
diff --git a/erpnext/projects/report/timesheet_billing_summary/timesheet_billing_summary.js b/erpnext/projects/report/timesheet_billing_summary/timesheet_billing_summary.js
new file mode 100644
index 0000000..1efd0c6
--- /dev/null
+++ b/erpnext/projects/report/timesheet_billing_summary/timesheet_billing_summary.js
@@ -0,0 +1,67 @@
+// Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+frappe.query_reports["Timesheet Billing Summary"] = {
+ tree: true,
+ initial_depth: 0,
+ filters: [
+ {
+ fieldname: "employee",
+ label: __("Employee"),
+ fieldtype: "Link",
+ options: "Employee",
+ on_change: function (report) {
+ unset_group_by(report, "employee");
+ },
+ },
+ {
+ fieldname: "project",
+ label: __("Project"),
+ fieldtype: "Link",
+ options: "Project",
+ on_change: function (report) {
+ unset_group_by(report, "project");
+ },
+ },
+ {
+ fieldname: "from_date",
+ label: __("From Date"),
+ fieldtype: "Date",
+ default: frappe.datetime.add_months(
+ frappe.datetime.month_start(),
+ -1
+ ),
+ },
+ {
+ fieldname: "to_date",
+ label: __("To Date"),
+ fieldtype: "Date",
+ default: frappe.datetime.add_days(
+ frappe.datetime.month_start(),
+ -1
+ ),
+ },
+ { // NOTE: `update_group_by_options` expects this filter to be the fifth in the list
+ fieldname: "group_by",
+ label: __("Group By"),
+ fieldtype: "Select",
+ options: [
+ "",
+ { value: "employee", label: __("Employee") },
+ { value: "project", label: __("Project") },
+ { value: "date", label: __("Start Date") },
+ ],
+ },
+ {
+ fieldname: "include_draft_timesheets",
+ label: __("Include Timesheets in Draft Status"),
+ fieldtype: "Check",
+ },
+ ],
+};
+
+function unset_group_by(report, fieldname) {
+ if (report.get_filter_value(fieldname) && report.get_filter_value("group_by") == fieldname) {
+ report.set_filter_value("group_by", "");
+ }
+}
diff --git a/erpnext/projects/report/employee_billing_summary/employee_billing_summary.json b/erpnext/projects/report/timesheet_billing_summary/timesheet_billing_summary.json
similarity index 61%
rename from erpnext/projects/report/employee_billing_summary/employee_billing_summary.json
rename to erpnext/projects/report/timesheet_billing_summary/timesheet_billing_summary.json
index e5626a0..0f070cb 100644
--- a/erpnext/projects/report/employee_billing_summary/employee_billing_summary.json
+++ b/erpnext/projects/report/timesheet_billing_summary/timesheet_billing_summary.json
@@ -1,36 +1,42 @@
{
"add_total_row": 1,
- "creation": "2019-03-08 15:08:19.929728",
- "disable_prepared_report": 0,
+ "columns": [],
+ "creation": "2023-10-10 23:53:43.692067",
"disabled": 0,
"docstatus": 0,
"doctype": "Report",
+ "filters": [],
"idx": 0,
"is_standard": "Yes",
- "modified": "2019-06-13 15:54:49.213973",
+ "letter_head": "ALYF GmbH",
+ "letterhead": null,
+ "modified": "2023-10-11 00:58:30.639078",
"modified_by": "Administrator",
"module": "Projects",
- "name": "Employee Billing Summary",
+ "name": "Timesheet Billing Summary",
"owner": "Administrator",
"prepared_report": 0,
"ref_doctype": "Timesheet",
- "report_name": "Employee Billing Summary",
+ "report_name": "Timesheet Billing Summary",
"report_type": "Script Report",
"roles": [
{
"role": "Projects User"
},
{
- "role": "HR User"
+ "role": "Employee"
+ },
+ {
+ "role": "Accounts User"
},
{
"role": "Manufacturing User"
},
{
- "role": "Employee"
+ "role": "HR User"
},
{
- "role": "Accounts User"
+ "role": "Employee Self Service"
}
]
}
\ No newline at end of file
diff --git a/erpnext/projects/report/timesheet_billing_summary/timesheet_billing_summary.py b/erpnext/projects/report/timesheet_billing_summary/timesheet_billing_summary.py
new file mode 100644
index 0000000..a6e7150
--- /dev/null
+++ b/erpnext/projects/report/timesheet_billing_summary/timesheet_billing_summary.py
@@ -0,0 +1,146 @@
+import frappe
+from frappe import _
+from frappe.model.docstatus import DocStatus
+
+
+def execute(filters=None):
+ group_fieldname = filters.pop("group_by", None)
+
+ filters = frappe._dict(filters or {})
+ columns = get_columns(filters, group_fieldname)
+
+ data = get_data(filters, group_fieldname)
+ return columns, data
+
+
+def get_columns(filters, group_fieldname=None):
+ group_columns = {
+ "date": {
+ "label": _("Date"),
+ "fieldtype": "Date",
+ "fieldname": "date",
+ "width": 150,
+ },
+ "project": {
+ "label": _("Project"),
+ "fieldtype": "Link",
+ "fieldname": "project",
+ "options": "Project",
+ "width": 200,
+ "hidden": int(bool(filters.get("project"))),
+ },
+ "employee": {
+ "label": _("Employee ID"),
+ "fieldtype": "Link",
+ "fieldname": "employee",
+ "options": "Employee",
+ "width": 200,
+ "hidden": int(bool(filters.get("employee"))),
+ },
+ }
+ columns = []
+ if group_fieldname:
+ columns.append(group_columns.get(group_fieldname))
+ columns.extend(
+ column for column in group_columns.values() if column.get("fieldname") != group_fieldname
+ )
+ else:
+ columns.extend(group_columns.values())
+
+ columns.extend(
+ [
+ {
+ "label": _("Employee Name"),
+ "fieldtype": "data",
+ "fieldname": "employee_name",
+ "hidden": 1,
+ },
+ {
+ "label": _("Timesheet"),
+ "fieldtype": "Link",
+ "fieldname": "timesheet",
+ "options": "Timesheet",
+ "width": 150,
+ },
+ {"label": _("Working Hours"), "fieldtype": "Float", "fieldname": "hours", "width": 150},
+ {
+ "label": _("Billing Hours"),
+ "fieldtype": "Float",
+ "fieldname": "billing_hours",
+ "width": 150,
+ },
+ {
+ "label": _("Billing Amount"),
+ "fieldtype": "Currency",
+ "fieldname": "billing_amount",
+ "width": 150,
+ },
+ ]
+ )
+
+ return columns
+
+
+def get_data(filters, group_fieldname=None):
+ _filters = []
+ if filters.get("employee"):
+ _filters.append(("employee", "=", filters.get("employee")))
+ if filters.get("project"):
+ _filters.append(("Timesheet Detail", "project", "=", filters.get("project")))
+ if filters.get("from_date"):
+ _filters.append(("Timesheet Detail", "from_time", ">=", filters.get("from_date")))
+ if filters.get("to_date"):
+ _filters.append(("Timesheet Detail", "to_time", "<=", filters.get("to_date")))
+ if not filters.get("include_draft_timesheets"):
+ _filters.append(("docstatus", "=", DocStatus.submitted()))
+ else:
+ _filters.append(("docstatus", "in", (DocStatus.submitted(), DocStatus.draft())))
+
+ data = frappe.get_list(
+ "Timesheet",
+ fields=[
+ "name as timesheet",
+ "`tabTimesheet`.employee",
+ "`tabTimesheet`.employee_name",
+ "`tabTimesheet Detail`.from_time as date",
+ "`tabTimesheet Detail`.project",
+ "`tabTimesheet Detail`.hours",
+ "`tabTimesheet Detail`.billing_hours",
+ "`tabTimesheet Detail`.billing_amount",
+ ],
+ filters=_filters,
+ order_by="`tabTimesheet Detail`.from_time",
+ )
+
+ return group_by(data, group_fieldname) if group_fieldname else data
+
+
+def group_by(data, fieldname):
+ groups = {row.get(fieldname) for row in data}
+ grouped_data = []
+ for group in sorted(groups):
+ group_row = {
+ fieldname: group,
+ "hours": sum(row.get("hours") for row in data if row.get(fieldname) == group),
+ "billing_hours": sum(row.get("billing_hours") for row in data if row.get(fieldname) == group),
+ "billing_amount": sum(row.get("billing_amount") for row in data if row.get(fieldname) == group),
+ "indent": 0,
+ "is_group": 1,
+ }
+ if fieldname == "employee":
+ group_row["employee_name"] = next(
+ row.get("employee_name") for row in data if row.get(fieldname) == group
+ )
+
+ grouped_data.append(group_row)
+ for row in data:
+ if row.get(fieldname) != group:
+ continue
+
+ _row = row.copy()
+ _row[fieldname] = None
+ _row["indent"] = 1
+ _row["is_group"] = 0
+ grouped_data.append(_row)
+
+ return grouped_data
diff --git a/erpnext/projects/workspace/projects/projects.json b/erpnext/projects/workspace/projects/projects.json
index 94ae9c0..e6bead9 100644
--- a/erpnext/projects/workspace/projects/projects.json
+++ b/erpnext/projects/workspace/projects/projects.json
@@ -155,9 +155,9 @@
"dependencies": "Project",
"hidden": 0,
"is_query_report": 1,
- "label": "Project Billing Summary",
+ "label": "Timesheet Billing Summary",
"link_count": 0,
- "link_to": "Project Billing Summary",
+ "link_to": "Timesheet Billing Summary",
"link_type": "Report",
"onboard": 0,
"type": "Link"
@@ -192,7 +192,7 @@
"type": "Link"
}
],
- "modified": "2023-07-04 14:39:08.935853",
+ "modified": "2023-10-10 23:54:33.082108",
"modified_by": "Administrator",
"module": "Projects",
"name": "Projects",
@@ -234,8 +234,8 @@
"type": "DocType"
},
{
- "label": "Project Billing Summary",
- "link_to": "Project Billing Summary",
+ "label": "Timesheet Billing Summary",
+ "link_to": "Timesheet Billing Summary",
"type": "Report"
},
{