Merge pull request #20428 from 0Pranav/territory-sales-pipeline
feat: add territory wise sales report
diff --git a/erpnext/config/crm.py b/erpnext/config/crm.py
index cf10219..09c2a65 100644
--- a/erpnext/config/crm.py
+++ b/erpnext/config/crm.py
@@ -117,6 +117,13 @@
"name": "Lead Owner Efficiency",
"doctype": "Lead",
"dependencies": ["Lead"]
+ },
+ {
+ "type": "report",
+ "is_query_report": True,
+ "name": "Territory-wise Sales",
+ "doctype": "Opportunity",
+ "dependencies": ["Opportunity"]
}
]
},
diff --git a/erpnext/crm/doctype/opportunity/opportunity.json b/erpnext/crm/doctype/opportunity/opportunity.json
index 66e3ca4..0e2068a 100644
--- a/erpnext/crm/doctype/opportunity/opportunity.json
+++ b/erpnext/crm/doctype/opportunity/opportunity.json
@@ -22,6 +22,7 @@
"sales_stage",
"order_lost_reason",
"mins_to_first_response",
+ "expected_closing",
"next_contact",
"contact_by",
"contact_date",
@@ -157,6 +158,11 @@
"read_only": 1
},
{
+ "fieldname": "expected_closing",
+ "fieldtype": "Date",
+ "label": "Expected Closing Date"
+ },
+ {
"collapsible": 1,
"collapsible_depends_on": "contact_by",
"fieldname": "next_contact",
diff --git a/erpnext/selling/report/territory_wise_sales/__init__.py b/erpnext/selling/report/territory_wise_sales/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/selling/report/territory_wise_sales/__init__.py
diff --git a/erpnext/selling/report/territory_wise_sales/territory_wise_sales.js b/erpnext/selling/report/territory_wise_sales/territory_wise_sales.js
new file mode 100644
index 0000000..bef800f
--- /dev/null
+++ b/erpnext/selling/report/territory_wise_sales/territory_wise_sales.js
@@ -0,0 +1,22 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+
+frappe.query_reports["Territory-wise Sales"] = {
+ "breadcrumb":"Selling",
+ "filters": [
+ {
+ fieldname:"transaction_date",
+ label: __("Transaction Date"),
+ fieldtype: "DateRange",
+ default: [frappe.datetime.add_months(frappe.datetime.get_today(),-1), frappe.datetime.get_today()],
+ },
+ {
+ fieldname: "company",
+ label: __("Company"),
+ fieldtype: "Link",
+ options: "Company",
+ }
+ ]
+};
diff --git a/erpnext/selling/report/territory_wise_sales/territory_wise_sales.json b/erpnext/selling/report/territory_wise_sales/territory_wise_sales.json
new file mode 100644
index 0000000..b98d1a2
--- /dev/null
+++ b/erpnext/selling/report/territory_wise_sales/territory_wise_sales.json
@@ -0,0 +1,27 @@
+{
+ "add_total_row": 0,
+ "creation": "2020-01-31 10:34:33.319047",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2020-01-31 10:34:33.319047",
+ "modified_by": "Administrator",
+ "module": "Selling",
+ "name": "Territory-wise Sales",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Opportunity",
+ "report_name": "Territory-wise Sales",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Sales User"
+ },
+ {
+ "role": "Sales Manager"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/selling/report/territory_wise_sales/territory_wise_sales.py b/erpnext/selling/report/territory_wise_sales/territory_wise_sales.py
new file mode 100644
index 0000000..f2db478
--- /dev/null
+++ b/erpnext/selling/report/territory_wise_sales/territory_wise_sales.py
@@ -0,0 +1,159 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from erpnext import get_default_currency
+from frappe import _
+
+def execute(filters=None):
+ filters = frappe._dict(filters)
+ columns = get_columns()
+ data = get_data(filters)
+ return columns, data
+
+
+def get_columns():
+ currency = get_default_currency()
+ return [
+ {
+ "label": _("Territory"),
+ "fieldname": "territory",
+ "fieldtype": "Link",
+ "options": "Territory"
+ },
+ {
+ "label": _("Opportunity Amount"),
+ "fieldname": "opportunity_amount",
+ "fieldtype": "Currency",
+ "options": currency
+ },
+ {
+ "label": _("Quotation Amount"),
+ "fieldname": "quotation_amount",
+ "fieldtype": "Currency",
+ "options": currency
+ },
+ {
+ "label": _("Order Amount"),
+ "fieldname": "order_amount",
+ "fieldtype": "Currency",
+ "options": currency
+ },
+ {
+ "label": _("Billing Amount"),
+ "fieldname": "billing_amount",
+ "fieldtype": "Currency",
+ "options": currency
+ }
+ ]
+
+def get_data(filters=None):
+ data = []
+
+ opportunities = get_opportunities(filters)
+ quotations = get_quotations(opportunities)
+ sales_orders = get_sales_orders(quotations)
+ sales_invoices = get_sales_invoice(sales_orders)
+
+ for territory in frappe.get_all("Territory"):
+ territory_opportunities = []
+ if opportunities:
+ territory_opportunities = list(filter(lambda x: x.territory == territory.name, opportunities))
+ t_opportunity_names = []
+ if territory_opportunities:
+ t_opportunity_names = [t.name for t in territory_opportunities]
+
+ territory_quotations = []
+ if t_opportunity_names and quotations:
+ territory_quotations = list(filter(lambda x: x.opportunity in t_opportunity_names, quotations))
+ t_quotation_names = []
+ if territory_quotations:
+ t_quotation_names = [t.name for t in territory_quotations]
+
+ territory_orders = []
+ if t_quotation_names and sales_orders:
+ list(filter(lambda x: x.quotation in t_quotation_names, sales_orders))
+ t_order_names = []
+ if territory_orders:
+ t_order_names = [t.name for t in territory_orders]
+
+ territory_invoices = list(filter(lambda x: x.sales_order in t_order_names, sales_invoices)) if t_order_names and sales_invoices else []
+
+ territory_data = {
+ "territory": territory.name,
+ "opportunity_amount": _get_total(territory_opportunities, "opportunity_amount"),
+ "quotation_amount": _get_total(territory_quotations),
+ "order_amount": _get_total(territory_orders),
+ "billing_amount": _get_total(territory_invoices)
+ }
+ data.append(territory_data)
+
+ return data
+
+def get_opportunities(filters):
+ conditions = ""
+
+ if filters.get('transaction_date'):
+ conditions = " WHERE transaction_date between {0} and {1}".format(
+ frappe.db.escape(filters['transaction_date'][0]),
+ frappe.db.escape(filters['transaction_date'][1]))
+
+ if filters.company:
+ if conditions:
+ conditions += " AND"
+ else:
+ conditions += " WHERE"
+ conditions += " company = %(company)s"
+
+
+ return frappe.db.sql("""
+ SELECT name, territory, opportunity_amount
+ FROM `tabOpportunity` {0}
+ """.format(conditions), filters, as_dict=1) #nosec
+
+def get_quotations(opportunities):
+ if not opportunities:
+ return []
+
+ opportunity_names = [o.name for o in opportunities]
+
+ return frappe.db.sql("""
+ SELECT `name`,`base_grand_total`, `opportunity`
+ FROM `tabQuotation`
+ WHERE docstatus=1 AND opportunity in ({0})
+ """.format(', '.join(["%s"]*len(opportunity_names))), tuple(opportunity_names), as_dict=1) #nosec
+
+def get_sales_orders(quotations):
+ if not quotations:
+ return []
+
+ quotation_names = [q.name for q in quotations]
+
+ return frappe.db.sql("""
+ SELECT so.`name`, so.`base_grand_total`, soi.prevdoc_docname as quotation
+ FROM `tabSales Order` so, `tabSales Order Item` soi
+ WHERE so.docstatus=1 AND so.name = soi.parent AND soi.prevdoc_docname in ({0})
+ """.format(', '.join(["%s"]*len(quotation_names))), tuple(quotation_names), as_dict=1) #nosec
+
+def get_sales_invoice(sales_orders):
+ if not sales_orders:
+ return []
+
+ so_names = [so.name for so in sales_orders]
+
+ return frappe.db.sql("""
+ SELECT si.name, si.base_grand_total, sii.sales_order
+ FROM `tabSales Invoice` si, `tabSales Invoice Item` sii
+ WHERE si.docstatus=1 AND si.name = sii.parent AND sii.sales_order in ({0})
+ """.format(', '.join(["%s"]*len(so_names))), tuple(so_names), as_dict=1) #nosec
+
+def _get_total(doclist, amount_field="base_grand_total"):
+ if not doclist:
+ return 0
+
+ total = 0
+ for doc in doclist:
+ total += doc.get(amount_field, 0)
+
+ return total