feat: new Report "Lost Quotations" (#38309)
diff --git a/erpnext/crm/doctype/competitor/competitor.json b/erpnext/crm/doctype/competitor/competitor.json
index 280441f..fd6da23 100644
--- a/erpnext/crm/doctype/competitor/competitor.json
+++ b/erpnext/crm/doctype/competitor/competitor.json
@@ -29,8 +29,16 @@
}
],
"index_web_pages_for_search": 1,
- "links": [],
- "modified": "2021-10-21 12:43:59.106807",
+ "links": [
+ {
+ "is_child_table": 1,
+ "link_doctype": "Competitor Detail",
+ "link_fieldname": "competitor",
+ "parent_doctype": "Quotation",
+ "table_fieldname": "competitors"
+ }
+ ],
+ "modified": "2023-11-23 19:33:54.284279",
"modified_by": "Administrator",
"module": "CRM",
"name": "Competitor",
@@ -64,5 +72,6 @@
"quick_entry": 1,
"sort_field": "modified",
"sort_order": "DESC",
+ "states": [],
"track_changes": 1
}
\ No newline at end of file
diff --git a/erpnext/selling/report/lost_quotations/__init__.py b/erpnext/selling/report/lost_quotations/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/selling/report/lost_quotations/__init__.py
diff --git a/erpnext/selling/report/lost_quotations/lost_quotations.js b/erpnext/selling/report/lost_quotations/lost_quotations.js
new file mode 100644
index 0000000..78e76cb
--- /dev/null
+++ b/erpnext/selling/report/lost_quotations/lost_quotations.js
@@ -0,0 +1,40 @@
+// Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+frappe.query_reports["Lost Quotations"] = {
+ filters: [
+ {
+ fieldname: "company",
+ label: __("Company"),
+ fieldtype: "Link",
+ options: "Company",
+ default: frappe.defaults.get_user_default("Company"),
+ },
+ {
+ label: "Timespan",
+ fieldtype: "Select",
+ fieldname: "timespan",
+ options: [
+ "Last Week",
+ "Last Month",
+ "Last Quarter",
+ "Last 6 months",
+ "Last Year",
+ "This Week",
+ "This Month",
+ "This Quarter",
+ "This Year",
+ ],
+ default: "This Year",
+ reqd: 1,
+ },
+ {
+ fieldname: "group_by",
+ label: __("Group By"),
+ fieldtype: "Select",
+ options: ["Lost Reason", "Competitor"],
+ default: "Lost Reason",
+ reqd: 1,
+ },
+ ],
+};
diff --git a/erpnext/selling/report/lost_quotations/lost_quotations.json b/erpnext/selling/report/lost_quotations/lost_quotations.json
new file mode 100644
index 0000000..8915bab
--- /dev/null
+++ b/erpnext/selling/report/lost_quotations/lost_quotations.json
@@ -0,0 +1,30 @@
+{
+ "add_total_row": 0,
+ "columns": [],
+ "creation": "2023-11-23 18:00:19.141922",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "filters": [],
+ "idx": 0,
+ "is_standard": "Yes",
+ "letter_head": null,
+ "letterhead": null,
+ "modified": "2023-11-23 19:27:28.854108",
+ "modified_by": "Administrator",
+ "module": "Selling",
+ "name": "Lost Quotations",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Quotation",
+ "report_name": "Lost Quotations",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Sales User"
+ },
+ {
+ "role": "Sales Manager"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/selling/report/lost_quotations/lost_quotations.py b/erpnext/selling/report/lost_quotations/lost_quotations.py
new file mode 100644
index 0000000..7c0bfbd
--- /dev/null
+++ b/erpnext/selling/report/lost_quotations/lost_quotations.py
@@ -0,0 +1,98 @@
+# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from typing import Literal
+
+import frappe
+from frappe import _
+from frappe.model.docstatus import DocStatus
+from frappe.query_builder.functions import Coalesce, Count, Round, Sum
+from frappe.utils.data import get_timespan_date_range
+
+
+def execute(filters=None):
+ columns = get_columns(filters.get("group_by"))
+ from_date, to_date = get_timespan_date_range(filters.get("timespan").lower())
+ data = get_data(filters.get("company"), from_date, to_date, filters.get("group_by"))
+ return columns, data
+
+
+def get_columns(group_by: Literal["Lost Reason", "Competitor"]):
+ return [
+ {
+ "fieldname": "lost_reason" if group_by == "Lost Reason" else "competitor",
+ "label": _("Lost Reason") if group_by == "Lost Reason" else _("Competitor"),
+ "fieldtype": "Link",
+ "options": "Quotation Lost Reason" if group_by == "Lost Reason" else "Competitor",
+ "width": 200,
+ },
+ {
+ "filedname": "lost_quotations",
+ "label": _("Lost Quotations"),
+ "fieldtype": "Int",
+ "width": 150,
+ },
+ {
+ "filedname": "lost_quotations_pct",
+ "label": _("Lost Quotations %"),
+ "fieldtype": "Percent",
+ "width": 200,
+ },
+ {
+ "fieldname": "lost_value",
+ "label": _("Lost Value"),
+ "fieldtype": "Currency",
+ "width": 150,
+ },
+ {
+ "filedname": "lost_value_pct",
+ "label": _("Lost Value %"),
+ "fieldtype": "Percent",
+ "width": 200,
+ },
+ ]
+
+
+def get_data(
+ company: str, from_date: str, to_date: str, group_by: Literal["Lost Reason", "Competitor"]
+):
+ """Return quotation value grouped by lost reason or competitor"""
+ if group_by == "Lost Reason":
+ fieldname = "lost_reason"
+ dimension = frappe.qb.DocType("Quotation Lost Reason Detail")
+ elif group_by == "Competitor":
+ fieldname = "competitor"
+ dimension = frappe.qb.DocType("Competitor Detail")
+ else:
+ frappe.throw(_("Invalid Group By"))
+
+ q = frappe.qb.DocType("Quotation")
+
+ lost_quotation_condition = (
+ (q.status == "Lost")
+ & (q.docstatus == DocStatus.submitted())
+ & (q.transaction_date >= from_date)
+ & (q.transaction_date <= to_date)
+ & (q.company == company)
+ )
+
+ from_lost_quotations = frappe.qb.from_(q).where(lost_quotation_condition)
+ total_quotations = from_lost_quotations.select(Count(q.name))
+ total_value = from_lost_quotations.select(Sum(q.base_net_total))
+
+ query = (
+ frappe.qb.from_(q)
+ .select(
+ Coalesce(dimension[fieldname], _("Not Specified")),
+ Count(q.name).distinct(),
+ Round((Count(q.name).distinct() / total_quotations * 100), 2),
+ Sum(q.base_net_total),
+ Round((Sum(q.base_net_total) / total_value * 100), 2),
+ )
+ .left_join(dimension)
+ .on(dimension.parent == q.name)
+ .where(lost_quotation_condition)
+ .groupby(dimension[fieldname])
+ )
+
+ return query.run()
diff --git a/erpnext/setup/doctype/quotation_lost_reason/quotation_lost_reason.json b/erpnext/setup/doctype/quotation_lost_reason/quotation_lost_reason.json
index 5d778ee..0eae08e 100644
--- a/erpnext/setup/doctype/quotation_lost_reason/quotation_lost_reason.json
+++ b/erpnext/setup/doctype/quotation_lost_reason/quotation_lost_reason.json
@@ -1,83 +1,58 @@
{
- "allow_copy": 0,
- "allow_import": 1,
- "allow_rename": 0,
- "autoname": "field:order_lost_reason",
- "beta": 0,
- "creation": "2013-01-10 16:34:24",
- "custom": 0,
- "docstatus": 0,
- "doctype": "DocType",
- "document_type": "Setup",
- "editable_grid": 0,
+ "actions": [],
+ "allow_import": 1,
+ "autoname": "field:order_lost_reason",
+ "creation": "2013-01-10 16:34:24",
+ "doctype": "DocType",
+ "document_type": "Setup",
+ "engine": "InnoDB",
+ "field_order": [
+ "order_lost_reason"
+ ],
"fields": [
{
- "allow_on_submit": 0,
- "bold": 0,
- "collapsible": 0,
- "fieldname": "order_lost_reason",
- "fieldtype": "Data",
- "hidden": 0,
- "ignore_user_permissions": 0,
- "ignore_xss_filter": 0,
- "in_filter": 0,
- "in_list_view": 1,
- "label": "Quotation Lost Reason",
- "length": 0,
- "no_copy": 0,
- "oldfieldname": "order_lost_reason",
- "oldfieldtype": "Data",
- "permlevel": 0,
- "print_hide": 0,
- "print_hide_if_no_value": 0,
- "read_only": 0,
- "report_hide": 0,
- "reqd": 1,
- "search_index": 0,
- "set_only_once": 0,
- "unique": 0
+ "fieldname": "order_lost_reason",
+ "fieldtype": "Data",
+ "in_list_view": 1,
+ "label": "Quotation Lost Reason",
+ "oldfieldname": "order_lost_reason",
+ "oldfieldtype": "Data",
+ "reqd": 1,
+ "unique": 1
}
- ],
- "hide_heading": 0,
- "hide_toolbar": 0,
- "icon": "fa fa-flag",
- "idx": 1,
- "image_view": 0,
- "in_create": 0,
-
- "is_submittable": 0,
- "issingle": 0,
- "istable": 0,
- "max_attachments": 0,
- "modified": "2016-07-25 05:24:25.533953",
- "modified_by": "Administrator",
- "module": "Setup",
- "name": "Quotation Lost Reason",
- "owner": "Administrator",
+ ],
+ "icon": "fa fa-flag",
+ "idx": 1,
+ "links": [
+ {
+ "is_child_table": 1,
+ "link_doctype": "Quotation Lost Reason Detail",
+ "link_fieldname": "lost_reason",
+ "parent_doctype": "Quotation",
+ "table_fieldname": "lost_reasons"
+ }
+ ],
+ "modified": "2023-11-23 19:31:02.743353",
+ "modified_by": "Administrator",
+ "module": "Setup",
+ "name": "Quotation Lost Reason",
+ "naming_rule": "By fieldname",
+ "owner": "Administrator",
"permissions": [
{
- "amend": 0,
- "apply_user_permissions": 0,
- "cancel": 0,
- "create": 1,
- "delete": 1,
- "email": 1,
- "export": 0,
- "if_owner": 0,
- "import": 0,
- "permlevel": 0,
- "print": 1,
- "read": 1,
- "report": 1,
- "role": "Sales Master Manager",
- "set_user_permissions": 0,
- "share": 1,
- "submit": 0,
+ "create": 1,
+ "delete": 1,
+ "email": 1,
+ "print": 1,
+ "read": 1,
+ "report": 1,
+ "role": "Sales Master Manager",
+ "share": 1,
"write": 1
}
- ],
- "quick_entry": 1,
- "read_only": 0,
- "read_only_onload": 0,
- "track_seen": 0
+ ],
+ "quick_entry": 1,
+ "sort_field": "modified",
+ "sort_order": "DESC",
+ "states": []
}
\ No newline at end of file