feat: Project Billing Summary for timesheet
diff --git a/erpnext/projects/report/billing_summary.py b/erpnext/projects/report/billing_summary.py
new file mode 100644
index 0000000..e34e90b
--- /dev/null
+++ b/erpnext/projects/report/billing_summary.py
@@ -0,0 +1,137 @@
+# 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 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": _("Total Billable Hours"),
+ "fieldtype": "Int",
+ "fieldname": "total_billable_hours",
+ "width": 50
+ },
+ {
+ "label": _("Total Hours"),
+ "fieldtype": "Int",
+ "fieldname": "total_hours",
+ "width": 50
+ },
+ {
+ "label": _("Amount"),
+ "fieldtype": "Int",
+ "fieldname": "amount",
+ "width": 100
+ }
+ ]
+
+def get_data(filters):
+ data = []
+
+ if "employee" in filters:
+ record= frappe.db.sql('''SELECT
+ employee, employee_name, name, total_billable_hours, total_hours, total_billable_amount
+ FROM
+ `tabTimesheet`
+ WHERE
+ employee = %s and (start_date <= %s and end_date >= %s)''',(filters.employee, filters.to_date, filters.from_date),
+ as_dict=1
+ )
+
+ elif "project" in filters:
+ record= frappe.db.sql('''SELECT
+ employee, employee_name, name, total_billable_hours, total_hours, total_billable_amount
+ FROM
+ `tabTimesheet`
+ WHERE
+ start_date <= %s and end_date >= %s''',(filters.to_date, filters.from_date),
+ as_dict=1
+ )
+ else:
+ record = {}
+
+
+ for entries in record:
+
+ timesheet_details_filter = {"parent": entries.name}
+
+ if "project" in filters:
+ timesheet_details_filter["project"] = filters.project
+
+ timesheet_details = frappe.get_all(
+ "Timesheet Detail",
+ filters = timesheet_details_filter,
+ fields=["*"]
+ )
+
+ total_hours = 0
+ total_billable_hours = 0
+ total_amount = 0
+ check_entries = False
+
+ for time in timesheet_details:
+
+ check_entries = True
+
+ time_start = time.from_time
+ time_end = frappe.utils.add_to_date(time.from_time, hours=time.hours)
+
+ from_date = frappe.utils.get_datetime(filters.from_date)
+ to_date = frappe.utils.get_datetime(filters.to_date)
+
+ if time_start <= from_date and time_end <= to_date:
+ total_hours, total_billable_hours, total_amount = get_billable_and_total_hours(time, time_end, from_date, total_hours, total_billable_hours, total_amount)
+ elif time_start >= from_date and time_end >= to_date:
+ total_hours, total_billable_hours, total_amount = get_billable_and_total_hours(time, to_date, time_start, total_hours, total_billable_hours, total_amount)
+ elif time_start >= from_date and time_end <= to_date:
+ total_hours, total_billable_hours, total_amount = get_billable_and_total_hours(time, time_end, time_start, total_hours, total_billable_hours, total_amount)
+
+ row = {
+ "employee": entries.employee,
+ "employee_name": entries.employee_name,
+ "timesheet": entries.name,
+ "total_billable_hours": total_billable_hours,
+ "total_hours": total_hours,
+ "amount": total_amount
+ }
+
+ if check_entries:
+ data.append(row)
+ check_entries = False
+
+ return data
+
+
+def get_billable_and_total_hours(time, end, start, total_hours, total_billable_hours, total_amount):
+ total_hours += abs(time_diff_in_hours(end, start))
+ if time.billable:
+ total_billable_hours += abs(time_diff_in_hours(end, start))
+ total_amount += total_billable_hours * time.billing_rate
+
+ return total_hours, total_billable_hours, total_amount
\ No newline at end of file
diff --git a/erpnext/projects/report/employee_billing_summary/employee_billing_summary.py b/erpnext/projects/report/employee_billing_summary/employee_billing_summary.py
index 491fa76..cd5ad78 100644
--- a/erpnext/projects/report/employee_billing_summary/employee_billing_summary.py
+++ b/erpnext/projects/report/employee_billing_summary/employee_billing_summary.py
@@ -4,117 +4,11 @@
from __future__ import unicode_literals
import frappe
from frappe import _
-from frappe.utils import time_diff_in_hours
+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
-
-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": _("Date"),
- "fieldtype": "Date",
- "fieldname": "date",
- "width": 150
- },
- {
- "label": _("Total Billable Hours"),
- "fieldtype": "Int",
- "fieldname": "total_billable_hours",
- "width": 50
- },
- {
- "label": _("Total Hours"),
- "fieldtype": "Int",
- "fieldname": "total_hours",
- "width": 50
- },
- {
- "label": _("Amount"),
- "fieldtype": "Int",
- "fieldname": "amount",
- "width": 50
- }
- ]
-
-def get_data(filters):
- data = []
- if "employee" in filters:
- record= frappe.db.sql('''SELECT
- employee, employee_name, name, total_billable_hours, total_hours, total_billable_amount
- FROM
- `tabTimesheet`
- WHERE
- employee = %s and (start_date <= %s and end_date >= %s)''',(filters.employee, filters.to_date, filters.from_date),
- as_dict=1
- )
- for entries in record:
-
- timesheet_details = frappe.get_all(
- "Timesheet Detail",
- filters={"parent": entries.name},
- fields=["*"]
- )
-
- total_hours = 0
- total_billable_hours = 0
- total_amount = 0
-
- for time in timesheet_details:
- time_start = time.from_time
- time_end = frappe.utils.add_to_date(time.from_time, hours=time.hours)
-
- from_date = frappe.utils.get_datetime(filters.from_date)
- to_date = frappe.utils.get_datetime(filters.to_date)
-
- if time_start <= from_date and time_end <= to_date:
- total_hours, total_billable_hours, total_amount = get_billable_and_total_hours(time, time_end, from_date, total_hours, total_billable_hours, total_amount)
- elif time_start >= from_date and time_end >= to_date:
- total_hours, total_billable_hours, total_amount = get_billable_and_total_hours(time, to_date, time_start, total_hours, total_billable_hours, total_amount)
- elif time_start >= from_date and time_end <= to_date:
- total_hours, total_billable_hours, total_amount = get_billable_and_total_hours(time, time_end, time_start, total_hours, total_billable_hours, total_amount)
-
- row = {
- "employee": entries.employee,
- "employee_name": entries.employee_name,
- "timesheet": entries.name,
- "total_billable_hours": total_billable_hours,
- "total_hours": total_hours,
- "amount": total_amount
- }
-
- data.append(row)
- return data
-
-def get_billable_and_total_hours(time, end, start, total_hours, total_billable_hours, total_amount):
- total_hours += abs(time_diff_in_hours(end, start))
- if time.billable:
- total_billable_hours += abs(time_diff_in_hours(end, start))
- total_amount += total_billable_hours * time.billing_rate
-
- return total_hours, total_billable_hours, total_amount
+ return columns, data
\ No newline at end of file
diff --git a/erpnext/projects/report/project_billing_summary/__init__.py b/erpnext/projects/report/project_billing_summary/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/projects/report/project_billing_summary/__init__.py
diff --git a/erpnext/projects/report/project_billing_summary/project_billing_summary.js b/erpnext/projects/report/project_billing_summary/project_billing_summary.js
new file mode 100644
index 0000000..18dbbd1
--- /dev/null
+++ b/erpnext/projects/report/project_billing_summary/project_billing_summary.js
@@ -0,0 +1,26 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Project Billing Summary"] = {
+ "filters": [
+ {
+ fieldname: "project",
+ label: __("Project"),
+ fieldtype: "Link",
+ options: "Project",
+ },
+ {
+ fieldname:"from_date",
+ label: __("From Date"),
+ fieldtype: "Date",
+ default: frappe.datetime.get_today()
+ },
+ {
+ fieldname:"to_date",
+ label: __("To Date"),
+ fieldtype: "Date",
+ default: frappe.datetime.add_days(frappe.datetime.get_today(), 30)
+ },
+ ]
+}
diff --git a/erpnext/projects/report/project_billing_summary/project_billing_summary.json b/erpnext/projects/report/project_billing_summary/project_billing_summary.json
new file mode 100644
index 0000000..a3f91c8
--- /dev/null
+++ b/erpnext/projects/report/project_billing_summary/project_billing_summary.json
@@ -0,0 +1,36 @@
+{
+ "add_total_row": 0,
+ "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-03-11 16:22:39.460524",
+ "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
new file mode 100644
index 0000000..cd5ad78
--- /dev/null
+++ b/erpnext/projects/report/project_billing_summary/project_billing_summary.py
@@ -0,0 +1,14 @@
+# 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.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
\ No newline at end of file