feat(CRM): Sales Pipeline Analytics Report and Opportunity Summary by Sales Stage Report (#26639)
* feat: Sales Pipeline Analytics Report
* fix: sider Issues and added tests
* fix: Semgrep Issue
* feat: Opportunity Summary by Sales Stage Report
* fix: add some checks and tests
* fix: sider issues and test
* fix: additional checks for error handling and minor changes
* fix: remove unused conditions
* fix: Changes mentioned on PR
* fix: currency conversions and other changes
* fix: remove unused imports
* fix: correction for failing test case
* fix: recorrected failing test case
* fix: sider issues and resolve test case errors
* fix: rewrite query using query builder
* fix: test case changes
* fix: sider fixes and other changes
* fix: clear data before running test
* fix: test case fixed
* refactor: code formatting
- smaller functions
- variable and function naming
* refactor: improve code formatting
* fix: linter issues
* fix: linter issues
* fix: change indentation to tabs
* fix: linter issues
* fix: naming, code formatting
* fix: quarterly values not showing up in Sales Pipeline Analytics
* fix: typo in tests
Co-authored-by: Rucha Mahabal <ruchamahabal2@gmail.com>
diff --git a/erpnext/crm/report/opportunity_summary_by_sales_stage/__init__.py b/erpnext/crm/report/opportunity_summary_by_sales_stage/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/crm/report/opportunity_summary_by_sales_stage/__init__.py
diff --git a/erpnext/crm/report/opportunity_summary_by_sales_stage/opportunity_summary_by_sales_stage.js b/erpnext/crm/report/opportunity_summary_by_sales_stage/opportunity_summary_by_sales_stage.js
new file mode 100644
index 0000000..116db2f
--- /dev/null
+++ b/erpnext/crm/report/opportunity_summary_by_sales_stage/opportunity_summary_by_sales_stage.js
@@ -0,0 +1,65 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Opportunity Summary by Sales Stage"] = {
+ "filters": [
+ {
+ fieldname: "based_on",
+ label: __("Based On"),
+ fieldtype: "Select",
+ options: "Opportunity Owner\nSource\nOpportunity Type",
+ default: "Opportunity Owner"
+ },
+ {
+ fieldname: "data_based_on",
+ label: __("Data Based On"),
+ fieldtype: "Select",
+ options: "Number\nAmount",
+ default: "Number"
+ },
+ {
+ fieldname: "from_date",
+ label: __("From Date"),
+ fieldtype: "Date",
+
+ },
+ {
+ fieldname: "to_date",
+ label: __("To Date"),
+ fieldtype: "Date",
+ },
+ {
+ fieldname: "status",
+ label: __("Status"),
+ fieldtype: "MultiSelectList",
+ get_data: function() {
+ return [
+ {value: "Open", description: "Status"},
+ {value: "Converted", description: "Status"},
+ {value: "Quotation", description: "Status"},
+ {value: "Replied", description: "Status"}
+ ]
+ }
+ },
+ {
+ fieldname: "opportunity_source",
+ label: __("Oppoturnity Source"),
+ fieldtype: "Link",
+ options: "Lead Source",
+ },
+ {
+ fieldname: "opportunity_type",
+ label: __("Opportunity Type"),
+ fieldtype: "Link",
+ options: "Opportunity Type",
+ },
+ {
+ fieldname: "company",
+ label: __("Company"),
+ fieldtype: "Link",
+ options: "Company",
+ default: frappe.defaults.get_user_default("Company")
+ }
+ ]
+};
\ No newline at end of file
diff --git a/erpnext/crm/report/opportunity_summary_by_sales_stage/opportunity_summary_by_sales_stage.json b/erpnext/crm/report/opportunity_summary_by_sales_stage/opportunity_summary_by_sales_stage.json
new file mode 100644
index 0000000..3605aec
--- /dev/null
+++ b/erpnext/crm/report/opportunity_summary_by_sales_stage/opportunity_summary_by_sales_stage.json
@@ -0,0 +1,29 @@
+{
+ "add_total_row": 0,
+ "columns": [],
+ "creation": "2021-07-28 12:18:24.028737",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "filters": [],
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2021-07-28 12:18:24.028737",
+ "modified_by": "Administrator",
+ "module": "CRM",
+ "name": "Opportunity Summary by Sales Stage",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Opportunity",
+ "report_name": "Opportunity Summary by Sales Stage ",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Sales User"
+ },
+ {
+ "role": "Sales Manager"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/crm/report/opportunity_summary_by_sales_stage/opportunity_summary_by_sales_stage.py b/erpnext/crm/report/opportunity_summary_by_sales_stage/opportunity_summary_by_sales_stage.py
new file mode 100644
index 0000000..4cff13f
--- /dev/null
+++ b/erpnext/crm/report/opportunity_summary_by_sales_stage/opportunity_summary_by_sales_stage.py
@@ -0,0 +1,254 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+import json
+
+import frappe
+import pandas
+from frappe import _
+from frappe.utils import flt
+from six import iteritems
+
+from erpnext.setup.utils import get_exchange_rate
+
+
+def execute(filters=None):
+ return OpportunitySummaryBySalesStage(filters).run()
+
+class OpportunitySummaryBySalesStage(object):
+ def __init__(self,filters=None):
+ self.filters = frappe._dict(filters or {})
+
+ def run(self):
+ self.get_columns()
+ self.get_data()
+ self.get_chart_data()
+ return self.columns, self.data, None, self.chart
+
+ def get_columns(self):
+ self.columns = []
+
+ if self.filters.get('based_on') == 'Opportunity Owner':
+ self.columns.append({
+ 'label': _('Opportunity Owner'),
+ 'fieldname': 'opportunity_owner',
+ 'width': 200
+ })
+
+ if self.filters.get('based_on') == 'Source':
+ self.columns.append({
+ 'label': _('Source'),
+ 'fieldname': 'source',
+ 'fieldtype': 'Link',
+ 'options': 'Lead Source',
+ 'width': 200
+ })
+
+ if self.filters.get('based_on') == 'Opportunity Type':
+ self.columns.append({
+ 'label': _('Opportunity Type'),
+ 'fieldname': 'opportunity_type',
+ 'width': 200
+ })
+
+ self.set_sales_stage_columns()
+
+ def set_sales_stage_columns(self):
+ self.sales_stage_list = frappe.db.get_list('Sales Stage', pluck='name')
+
+ for sales_stage in self.sales_stage_list:
+ if self.filters.get('data_based_on') == 'Number':
+ self.columns.append({
+ 'label': _(sales_stage),
+ 'fieldname': sales_stage,
+ 'fieldtype': 'Int',
+ 'width': 150
+ })
+
+ elif self.filters.get('data_based_on') == 'Amount':
+ self.columns.append({
+ 'label': _(sales_stage),
+ 'fieldname': sales_stage,
+ 'fieldtype': 'Currency',
+ 'width': 150
+ })
+
+ def get_data(self):
+ self.data = []
+
+ based_on = {
+ 'Opportunity Owner': '_assign',
+ 'Source': 'source',
+ 'Opportunity Type': 'opportunity_type'
+ }[self.filters.get('based_on')]
+
+ data_based_on = {
+ 'Number': 'count(name) as count',
+ 'Amount': 'opportunity_amount as amount',
+ }[self.filters.get('data_based_on')]
+
+ self.get_data_query(based_on, data_based_on)
+
+ self.get_rows()
+
+ def get_data_query(self, based_on, data_based_on):
+ if self.filters.get('data_based_on') == 'Number':
+ group_by = '{},{}'.format('sales_stage', based_on)
+ self.query_result = frappe.db.get_list('Opportunity',
+ filters=self.get_conditions(),
+ fields=['sales_stage', data_based_on, based_on],
+ group_by=group_by
+ )
+
+ elif self.filters.get('data_based_on') == 'Amount':
+ self.query_result = frappe.db.get_list('Opportunity',
+ filters=self.get_conditions(),
+ fields=['sales_stage', based_on, data_based_on, 'currency']
+ )
+
+ self.convert_to_base_currency()
+
+ dataframe = pandas.DataFrame.from_records(self.query_result)
+ dataframe.replace(to_replace=[None], value='Not Assigned', inplace=True)
+ result = dataframe.groupby(['sales_stage', based_on], as_index=False)['amount'].sum()
+
+ self.grouped_data = []
+
+ for i in range(len(result['amount'])):
+ self.grouped_data.append({
+ 'sales_stage': result['sales_stage'][i],
+ based_on : result[based_on][i],
+ 'amount': result['amount'][i]
+ })
+
+ self.query_result = self.grouped_data
+
+ def get_rows(self):
+ self.data = []
+ self.get_formatted_data()
+
+ for based_on,data in iteritems(self.formatted_data):
+ row_based_on={
+ 'Opportunity Owner': 'opportunity_owner',
+ 'Source': 'source',
+ 'Opportunity Type': 'opportunity_type'
+ }[self.filters.get('based_on')]
+
+ row = {row_based_on: based_on}
+
+ for d in self.query_result:
+ sales_stage = d.get('sales_stage')
+ row[sales_stage] = data.get(sales_stage)
+
+ self.data.append(row)
+
+ def get_formatted_data(self):
+ self.formatted_data = frappe._dict()
+
+ for d in self.query_result:
+ data_based_on ={
+ 'Number': 'count',
+ 'Amount': 'amount'
+ }[self.filters.get('data_based_on')]
+
+ based_on ={
+ 'Opportunity Owner': '_assign',
+ 'Source': 'source',
+ 'Opportunity Type': 'opportunity_type'
+ }[self.filters.get('based_on')]
+
+ if self.filters.get('based_on') == 'Opportunity Owner':
+ if d.get(based_on) == '[]' or d.get(based_on) is None or d.get(based_on) == 'Not Assigned':
+ assignments = ['Not Assigned']
+ else:
+ assignments = json.loads(d.get(based_on))
+
+ sales_stage = d.get('sales_stage')
+ count = d.get(data_based_on)
+
+ if assignments:
+ if len(assignments) > 1:
+ for assigned_to in assignments:
+ self.set_formatted_data_based_on_sales_stage(assigned_to, sales_stage, count)
+ else:
+ assigned_to = assignments[0]
+ self.set_formatted_data_based_on_sales_stage(assigned_to, sales_stage, count)
+ else:
+ value = d.get(based_on)
+ sales_stage = d.get('sales_stage')
+ count = d.get(data_based_on)
+ self.set_formatted_data_based_on_sales_stage(value, sales_stage, count)
+
+ def set_formatted_data_based_on_sales_stage(self, based_on, sales_stage, count):
+ self.formatted_data.setdefault(based_on, frappe._dict()).setdefault(sales_stage, 0)
+ self.formatted_data[based_on][sales_stage] += count
+
+ def get_conditions(self):
+ filters = []
+
+ if self.filters.get('company'):
+ filters.append({'company': self.filters.get('company')})
+
+ if self.filters.get('opportunity_type'):
+ filters.append({'opportunity_type': self.filters.get('opportunity_type')})
+
+ if self.filters.get('opportunity_source'):
+ filters.append({'source': self.filters.get('opportunity_source')})
+
+ if self.filters.get('status'):
+ filters.append({'status': ('in',self.filters.get('status'))})
+
+ if self.filters.get('from_date') and self.filters.get('to_date'):
+ filters.append(['transaction_date', 'between', [self.filters.get('from_date'), self.filters.get('to_date')]])
+
+ return filters
+
+ def get_chart_data(self):
+ labels = []
+ datasets = []
+ values = [0] * 8
+
+ for sales_stage in self.sales_stage_list:
+ labels.append(sales_stage)
+
+ options = {
+ 'Number': 'count',
+ 'Amount': 'amount'
+ }[self.filters.get('data_based_on')]
+
+ for data in self.query_result:
+ for count in range(len(values)):
+ if data['sales_stage'] == labels[count]:
+ values[count] = values[count] + data[options]
+
+ datasets.append({'name':options, 'values':values})
+
+ self.chart = {
+ 'data':{
+ 'labels': labels,
+ 'datasets': datasets
+ },
+ 'type':'line'
+ }
+
+ def currency_conversion(self,from_currency,to_currency):
+ cacheobj = frappe.cache()
+
+ if cacheobj.get(from_currency):
+ return flt(str(cacheobj.get(from_currency),'UTF-8'))
+
+ else:
+ value = get_exchange_rate(from_currency,to_currency)
+ cacheobj.set(from_currency,value)
+ return flt(str(cacheobj.get(from_currency),'UTF-8'))
+
+ def get_default_currency(self):
+ company = self.filters.get('company')
+ return frappe.db.get_value('Company', company, 'default_currency')
+
+ def convert_to_base_currency(self):
+ default_currency = self.get_default_currency()
+ for data in self.query_result:
+ if data.get('currency') != default_currency:
+ opportunity_currency = data.get('currency')
+ value = self.currency_conversion(opportunity_currency,default_currency)
+ data['amount'] = data['amount'] * value
\ No newline at end of file
diff --git a/erpnext/crm/report/opportunity_summary_by_sales_stage/test_opportunity_summary_by_sales_stage.py b/erpnext/crm/report/opportunity_summary_by_sales_stage/test_opportunity_summary_by_sales_stage.py
new file mode 100644
index 0000000..13859d9
--- /dev/null
+++ b/erpnext/crm/report/opportunity_summary_by_sales_stage/test_opportunity_summary_by_sales_stage.py
@@ -0,0 +1,94 @@
+import unittest
+
+import frappe
+
+from erpnext.crm.report.opportunity_summary_by_sales_stage.opportunity_summary_by_sales_stage import (
+ execute,
+)
+from erpnext.crm.report.sales_pipeline_analytics.test_sales_pipeline_analytics import (
+ create_company,
+ create_customer,
+ create_opportunity,
+)
+
+
+class TestOpportunitySummaryBySalesStage(unittest.TestCase):
+ @classmethod
+ def setUpClass(self):
+ frappe.db.delete("Opportunity")
+ create_company()
+ create_customer()
+ create_opportunity()
+
+ def test_opportunity_summary_by_sales_stage(self):
+ self.check_for_opportunity_owner()
+ self.check_for_source()
+ self.check_for_opportunity_type()
+ self.check_all_filters()
+
+ def check_for_opportunity_owner(self):
+ filters = {
+ 'based_on': "Opportunity Owner",
+ 'data_based_on': "Number",
+ 'company': "Best Test"
+ }
+
+ report = execute(filters)
+
+ expected_data = [{
+ 'opportunity_owner': "Not Assigned",
+ 'Prospecting': 1
+ }]
+
+ self.assertEqual(expected_data, report[1])
+
+ def check_for_source(self):
+ filters = {
+ 'based_on': "Source",
+ 'data_based_on': "Number",
+ 'company': "Best Test"
+ }
+
+ report = execute(filters)
+
+ expected_data = [{
+ 'source': 'Cold Calling',
+ 'Prospecting': 1
+ }]
+
+ self.assertEqual(expected_data, report[1])
+
+ def check_for_opportunity_type(self):
+ filters = {
+ 'based_on': "Opportunity Type",
+ 'data_based_on': "Number",
+ 'company': "Best Test"
+ }
+
+ report = execute(filters)
+
+ expected_data = [{
+ 'opportunity_type': 'Sales',
+ 'Prospecting': 1
+ }]
+
+ self.assertEqual(expected_data, report[1])
+
+ def check_all_filters(self):
+ filters = {
+ 'based_on': "Opportunity Type",
+ 'data_based_on': "Number",
+ 'company': "Best Test",
+ 'opportunity_source': "Cold Calling",
+ 'opportunity_type': "Sales",
+ 'status': ["Open"]
+ }
+
+ report = execute(filters)
+
+ expected_data = [{
+ 'opportunity_type': 'Sales',
+ 'Prospecting': 1
+ }]
+
+ self.assertEqual(expected_data, report[1])
\ No newline at end of file
diff --git a/erpnext/crm/report/sales_pipeline_analytics/__init__.py b/erpnext/crm/report/sales_pipeline_analytics/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/crm/report/sales_pipeline_analytics/__init__.py
diff --git a/erpnext/crm/report/sales_pipeline_analytics/sales_pipeline_analytics.js b/erpnext/crm/report/sales_pipeline_analytics/sales_pipeline_analytics.js
new file mode 100644
index 0000000..1426f4b
--- /dev/null
+++ b/erpnext/crm/report/sales_pipeline_analytics/sales_pipeline_analytics.js
@@ -0,0 +1,70 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Sales Pipeline Analytics"] = {
+ "filters": [
+ {
+ fieldname: "pipeline_by",
+ label: __("Pipeline By"),
+ fieldtype: "Select",
+ options: "Owner\nSales Stage",
+ default: "Owner"
+ },
+ {
+ fieldname: "from_date",
+ label: __("From Date"),
+ fieldtype: "Date"
+ },
+ {
+ fieldname: "to_date",
+ label: __("To Date"),
+ fieldtype: "Date"
+ },
+ {
+ fieldname: "range",
+ label: __("Range"),
+ fieldtype: "Select",
+ options: "Monthly\nQuarterly",
+ default: "Monthly"
+ },
+ {
+ fieldname: "assigned_to",
+ label: __("Assigned To"),
+ fieldtype: "Link",
+ options: "User"
+ },
+ {
+ fieldname: "status",
+ label: __("Status"),
+ fieldtype: "Select",
+ options: "Open\nQuotation\nConverted\nReplied"
+ },
+ {
+ fieldname: "based_on",
+ label: __("Based On"),
+ fieldtype: "Select",
+ options: "Number\nAmount",
+ default: "Number"
+ },
+ {
+ fieldname: "company",
+ label: __("Company"),
+ fieldtype: "Link",
+ options: "Company",
+ default: frappe.defaults.get_user_default("Company")
+ },
+ {
+ fieldname: "opportunity_source",
+ label: __("Opportunity Source"),
+ fieldtype: "Link",
+ options: "Lead Source"
+ },
+ {
+ fieldname: "opportunity_type",
+ label: __("Opportunity Type"),
+ fieldtype: "Link",
+ options: "Opportunity Type"
+ },
+ ]
+};
diff --git a/erpnext/crm/report/sales_pipeline_analytics/sales_pipeline_analytics.json b/erpnext/crm/report/sales_pipeline_analytics/sales_pipeline_analytics.json
new file mode 100644
index 0000000..cffdddf
--- /dev/null
+++ b/erpnext/crm/report/sales_pipeline_analytics/sales_pipeline_analytics.json
@@ -0,0 +1,29 @@
+{
+ "add_total_row": 0,
+ "columns": [],
+ "creation": "2021-07-01 17:29:09.530787",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "filters": [],
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2021-07-01 17:45:17.612861",
+ "modified_by": "Administrator",
+ "module": "CRM",
+ "name": "Sales Pipeline Analytics",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Opportunity",
+ "report_name": "Sales Pipeline Analytics",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Sales User"
+ },
+ {
+ "role": "Sales Manager"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/crm/report/sales_pipeline_analytics/sales_pipeline_analytics.py b/erpnext/crm/report/sales_pipeline_analytics/sales_pipeline_analytics.py
new file mode 100644
index 0000000..7466982
--- /dev/null
+++ b/erpnext/crm/report/sales_pipeline_analytics/sales_pipeline_analytics.py
@@ -0,0 +1,333 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+import json
+from datetime import date
+
+import frappe
+import pandas
+from dateutil.relativedelta import relativedelta
+from frappe import _
+from frappe.utils import cint, flt
+from six import iteritems
+
+from erpnext.setup.utils import get_exchange_rate
+
+
+def execute(filters=None):
+ return SalesPipelineAnalytics(filters).run()
+
+class SalesPipelineAnalytics(object):
+ def __init__(self, filters=None):
+ self.filters = frappe._dict(filters or {})
+
+ def run(self):
+ self.get_columns()
+ self.get_data()
+ self.get_chart_data()
+
+ return self.columns, self.data, None, self.chart
+
+ def get_columns(self):
+ self.columns = []
+
+ self.set_range_columns()
+ self.set_pipeline_based_on_column()
+
+ def set_range_columns(self):
+ based_on = {
+ 'Number': 'Int',
+ 'Amount': 'Currency'
+ }[self.filters.get('based_on')]
+
+ if self.filters.get('range') == 'Monthly':
+ month_list = self.get_month_list()
+
+ for month in month_list:
+ self.columns.append({
+ 'fieldname': month,
+ 'fieldtype': based_on,
+ 'label': month,
+ 'width': 200
+ })
+
+ elif self.filters.get('range') == 'Quarterly':
+ for quarter in range(1, 5):
+ self.columns.append({
+ 'fieldname': f'Q{quarter}',
+ 'fieldtype': based_on,
+ 'label': f'Q{quarter}',
+ 'width': 200
+ })
+
+ def set_pipeline_based_on_column(self):
+ if self.filters.get('pipeline_by') == 'Owner':
+ self.columns.insert(0, {
+ 'fieldname': 'opportunity_owner',
+ 'label': _('Opportunity Owner'),
+ 'width': 200
+ })
+
+ elif self.filters.get('pipeline_by') == 'Sales Stage':
+ self.columns.insert(0, {
+ 'fieldname': 'sales_stage',
+ 'label': _('Sales Stage'),
+ 'width': 200
+ })
+
+ def get_fields(self):
+ self.based_on ={
+ 'Owner': '_assign as opportunity_owner',
+ 'Sales Stage': 'sales_stage'
+ }[self.filters.get('pipeline_by')]
+
+ self.data_based_on ={
+ 'Number': 'count(name) as count',
+ 'Amount': 'opportunity_amount as amount'
+ }[self.filters.get('based_on')]
+
+ self.group_by_based_on = {
+ 'Owner': '_assign',
+ 'Sales Stage': 'sales_stage'
+ }[self.filters.get('pipeline_by')]
+
+ self.group_by_period = {
+ 'Monthly': 'month(expected_closing)',
+ 'Quarterly': 'QUARTER(expected_closing)'
+ }[self.filters.get('range')]
+
+ self.pipeline_by = {
+ 'Owner': 'opportunity_owner',
+ 'Sales Stage': 'sales_stage'
+ }[self.filters.get('pipeline_by')]
+
+ self.duration = {
+ 'Monthly': 'monthname(expected_closing) as month',
+ 'Quarterly': 'QUARTER(expected_closing) as quarter'
+ }[self.filters.get('range')]
+
+ self.period_by = {
+ 'Monthly': 'month',
+ 'Quarterly': 'quarter'
+ }[self.filters.get('range')]
+
+ def get_data(self):
+ self.get_fields()
+
+ if self.filters.get('based_on') == 'Number':
+ self.query_result = frappe.db.get_list('Opportunity',
+ filters=self.get_conditions(),
+ fields=[self.based_on, self.data_based_on, self.duration],
+ group_by='{},{}'.format(self.group_by_based_on, self.group_by_period),
+ order_by=self.group_by_period
+ )
+
+ if self.filters.get('based_on') == 'Amount':
+ self.query_result = frappe.db.get_list('Opportunity',
+ filters=self.get_conditions(),
+ fields=[self.based_on, self.data_based_on, self.duration, 'currency']
+ )
+
+ self.convert_to_base_currency()
+
+ dataframe = pandas.DataFrame.from_records(self.query_result)
+ dataframe.replace(to_replace=[None], value='Not Assigned', inplace=True)
+ result = dataframe.groupby([self.pipeline_by, self.period_by], as_index=False)['amount'].sum()
+
+ self.grouped_data = []
+
+ for i in range(len(result['amount'])):
+ self.grouped_data.append({
+ self.pipeline_by : result[self.pipeline_by][i],
+ self.period_by : result[self.period_by][i],
+ 'amount': result['amount'][i]
+ })
+
+ self.query_result = self.grouped_data
+
+ self.get_periodic_data()
+ self.append_data(self.pipeline_by, self.period_by)
+
+ def get_conditions(self):
+ conditions = []
+
+ if self.filters.get('opportunity_source'):
+ conditions.append({'source': self.filters.get('opportunity_source')})
+
+ if self.filters.get('opportunity_type'):
+ conditions.append({'opportunity_type': self.filters.get('opportunity_type')})
+
+ if self.filters.get('status'):
+ conditions.append({'status': self.filters.get('status')})
+
+ if self.filters.get('company'):
+ conditions.append({'company': self.filters.get('company')})
+
+ if self.filters.get('from_date') and self.filters.get('to_date'):
+ conditions.append(['expected_closing', 'between',
+ [self.filters.get('from_date'), self.filters.get('to_date')]])
+
+ return conditions
+
+ def get_chart_data(self):
+ labels = []
+ datasets = []
+
+ self.append_to_dataset(datasets)
+
+ for column in self.columns:
+ if column['fieldname'] != 'opportunity_owner' and column['fieldname'] != 'sales_stage':
+ labels.append(column['fieldname'])
+
+ self.chart = {
+ 'data':{
+ 'labels': labels,
+ 'datasets': datasets
+ },
+ 'type':'line'
+ }
+
+ return self.chart
+
+ def get_periodic_data(self):
+ self.periodic_data = frappe._dict()
+
+ based_on = {
+ 'Number': 'count',
+ 'Amount': 'amount'
+ }[self.filters.get('based_on')]
+
+ pipeline_by = {
+ 'Owner': 'opportunity_owner',
+ 'Sales Stage': 'sales_stage'
+ }[self.filters.get('pipeline_by')]
+
+ frequency = {
+ 'Monthly': 'month',
+ 'Quarterly': 'quarter'
+ }[self.filters.get('range')]
+
+ for info in self.query_result:
+ if self.filters.get('range') == 'Monthly':
+ period = info.get(frequency)
+ if self.filters.get('range') == 'Quarterly':
+ period = f'Q{cint(info.get("quarter"))}'
+
+ value = info.get(pipeline_by)
+ count_or_amount = info.get(based_on)
+
+ if self.filters.get('pipeline_by') == 'Owner':
+ if value == 'Not Assigned' or value == '[]' or value is None:
+ assigned_to = ['Not Assigned']
+ else:
+ assigned_to = json.loads(value)
+ self.check_for_assigned_to(period, value, count_or_amount, assigned_to, info)
+
+ else:
+ self.set_formatted_data(period, value, count_or_amount, None)
+
+ def set_formatted_data(self, period, value, count_or_amount, assigned_to):
+ if assigned_to:
+ if len(assigned_to) > 1:
+ if self.filters.get('assigned_to'):
+ for user in assigned_to:
+ if self.filters.get('assigned_to') == user:
+ value = user
+ self.periodic_data.setdefault(value, frappe._dict()).setdefault(period, 0)
+ self.periodic_data[value][period] += count_or_amount
+ else:
+ for user in assigned_to:
+ value = user
+ self.periodic_data.setdefault(value, frappe._dict()).setdefault(period, 0)
+ self.periodic_data[value][period] += count_or_amount
+ else:
+ value = assigned_to[0]
+ self.periodic_data.setdefault(value, frappe._dict()).setdefault(period, 0)
+ self.periodic_data[value][period] += count_or_amount
+
+ else:
+ self.periodic_data.setdefault(value, frappe._dict()).setdefault(period, 0)
+ self.periodic_data[value][period] += count_or_amount
+
+ def check_for_assigned_to(self, period, value, count_or_amount, assigned_to, info):
+ if self.filters.get('assigned_to'):
+ for data in json.loads(info.get('opportunity_owner')):
+ if data == self.filters.get('assigned_to'):
+ self.set_formatted_data(period, data, count_or_amount, assigned_to)
+ else:
+ self.set_formatted_data(period, value, count_or_amount, assigned_to)
+
+ def get_month_list(self):
+ month_list= []
+ current_date = date.today()
+ month_number = date.today().month
+
+ for month in range(month_number,13):
+ month_list.append(current_date.strftime('%B'))
+ current_date = current_date + relativedelta(months=1)
+
+ return month_list
+
+ def append_to_dataset(self, datasets):
+ range_by = {
+ 'Monthly': 'month',
+ 'Quarterly': 'quarter'
+ }[self.filters.get('range')]
+
+ based_on = {
+ 'Amount': 'amount',
+ 'Number': 'count'
+ }[self.filters.get('based_on')]
+
+ if self.filters.get('range') == 'Quarterly':
+ frequency_list = [1,2,3,4]
+ count = [0] * 4
+
+ if self.filters.get('range') == 'Monthly':
+ frequency_list = self.get_month_list()
+ count = [0] * 12
+
+ for info in self.query_result:
+ for i in range(len(frequency_list)):
+ if info[range_by] == frequency_list[i]:
+ count[i] = count[i] + info[based_on]
+ datasets.append({'name': based_on, 'values': count})
+
+ def append_data(self, pipeline_by, period_by):
+ self.data = []
+ for pipeline,period_data in iteritems(self.periodic_data):
+ row = {pipeline_by : pipeline}
+ for info in self.query_result:
+ if self.filters.get('range') == 'Monthly':
+ period = info.get(period_by)
+
+ if self.filters.get('range') == 'Quarterly':
+ period = f'Q{cint(info.get(period_by))}'
+
+ count = period_data.get(period,0.0)
+ row[period] = count
+
+ self.data.append(row)
+
+ def get_default_currency(self):
+ company = self.filters.get('company')
+ return frappe.db.get_value('Company',company,['default_currency'])
+
+ def get_currency_rate(self, from_currency, to_currency):
+ cacheobj = frappe.cache()
+
+ if cacheobj.get(from_currency):
+ return flt(str(cacheobj.get(from_currency),'UTF-8'))
+
+ else:
+ value = get_exchange_rate(from_currency, to_currency)
+ cacheobj.set(from_currency, value)
+ return flt(str(cacheobj.get(from_currency),'UTF-8'))
+
+ def convert_to_base_currency(self):
+ default_currency = self.get_default_currency()
+ for data in self.query_result:
+ if data.get('currency') != default_currency:
+ opportunity_currency = data.get('currency')
+ value = self.get_currency_rate(opportunity_currency,default_currency)
+ data['amount'] = data['amount'] * value
\ No newline at end of file
diff --git a/erpnext/crm/report/sales_pipeline_analytics/test_sales_pipeline_analytics.py b/erpnext/crm/report/sales_pipeline_analytics/test_sales_pipeline_analytics.py
new file mode 100644
index 0000000..24c3839
--- /dev/null
+++ b/erpnext/crm/report/sales_pipeline_analytics/test_sales_pipeline_analytics.py
@@ -0,0 +1,238 @@
+import unittest
+
+import frappe
+
+from erpnext.crm.report.sales_pipeline_analytics.sales_pipeline_analytics import execute
+
+
+class TestSalesPipelineAnalytics(unittest.TestCase):
+ @classmethod
+ def setUpClass(self):
+ frappe.db.delete("Opportunity")
+ create_company()
+ create_customer()
+ create_opportunity()
+
+ def test_sales_pipeline_analytics(self):
+ self.check_for_monthly_and_number()
+ self.check_for_monthly_and_amount()
+ self.check_for_quarterly_and_number()
+ self.check_for_quarterly_and_amount()
+ self.check_for_all_filters()
+
+ def check_for_monthly_and_number(self):
+ filters = {
+ 'pipeline_by':"Owner",
+ 'range':"Monthly",
+ 'based_on':"Number",
+ 'status':"Open",
+ 'opportunity_type':"Sales",
+ 'company':"Best Test"
+ }
+
+ report = execute(filters)
+
+ expected_data = [
+ {
+ 'opportunity_owner':'Not Assigned',
+ 'August':1
+ }
+ ]
+
+ self.assertEqual(expected_data,report[1])
+
+ filters = {
+ 'pipeline_by':"Sales Stage",
+ 'range':"Monthly",
+ 'based_on':"Number",
+ 'status':"Open",
+ 'opportunity_type':"Sales",
+ 'company':"Best Test"
+ }
+
+ report = execute(filters)
+
+ expected_data = [
+ {
+ 'sales_stage':'Prospecting',
+ 'August':1
+ }
+ ]
+
+ self.assertEqual(expected_data,report[1])
+
+ def check_for_monthly_and_amount(self):
+ filters = {
+ 'pipeline_by':"Owner",
+ 'range':"Monthly",
+ 'based_on':"Amount",
+ 'status':"Open",
+ 'opportunity_type':"Sales",
+ 'company':"Best Test"
+ }
+
+ report = execute(filters)
+
+ expected_data = [
+ {
+ 'opportunity_owner':'Not Assigned',
+ 'August':150000
+ }
+ ]
+
+ self.assertEqual(expected_data,report[1])
+
+ filters = {
+ 'pipeline_by':"Sales Stage",
+ 'range':"Monthly",
+ 'based_on':"Amount",
+ 'status':"Open",
+ 'opportunity_type':"Sales",
+ 'company':"Best Test"
+ }
+
+ report = execute(filters)
+
+ expected_data = [
+ {
+ 'sales_stage':'Prospecting',
+ 'August':150000
+ }
+ ]
+
+ self.assertEqual(expected_data,report[1])
+
+ def check_for_quarterly_and_number(self):
+ filters = {
+ 'pipeline_by':"Owner",
+ 'range':"Quarterly",
+ 'based_on':"Number",
+ 'status':"Open",
+ 'opportunity_type':"Sales",
+ 'company':"Best Test"
+ }
+
+ report = execute(filters)
+
+ expected_data = [
+ {
+ 'opportunity_owner':'Not Assigned',
+ 'Q3':1
+ }
+ ]
+
+ self.assertEqual(expected_data,report[1])
+
+ filters = {
+ 'pipeline_by':"Sales Stage",
+ 'range':"Quarterly",
+ 'based_on':"Number",
+ 'status':"Open",
+ 'opportunity_type':"Sales",
+ 'company':"Best Test"
+ }
+
+ report = execute(filters)
+
+ expected_data = [
+ {
+ 'sales_stage':'Prospecting',
+ 'Q3':1
+ }
+ ]
+
+ self.assertEqual(expected_data,report[1])
+
+ def check_for_quarterly_and_amount(self):
+ filters = {
+ 'pipeline_by':"Owner",
+ 'range':"Quarterly",
+ 'based_on':"Amount",
+ 'status':"Open",
+ 'opportunity_type':"Sales",
+ 'company':"Best Test"
+ }
+
+ report = execute(filters)
+
+ expected_data = [
+ {
+ 'opportunity_owner':'Not Assigned',
+ 'Q3':150000
+ }
+ ]
+
+ self.assertEqual(expected_data,report[1])
+
+ filters = {
+ 'pipeline_by':"Sales Stage",
+ 'range':"Quarterly",
+ 'based_on':"Amount",
+ 'status':"Open",
+ 'opportunity_type':"Sales",
+ 'company':"Best Test"
+ }
+
+ report = execute(filters)
+
+ expected_data = [
+ {
+ 'sales_stage':'Prospecting',
+ 'Q3':150000
+ }
+ ]
+
+ self.assertEqual(expected_data,report[1])
+
+ def check_for_all_filters(self):
+ filters = {
+ 'pipeline_by':"Owner",
+ 'range':"Monthly",
+ 'based_on':"Number",
+ 'status':"Open",
+ 'opportunity_type':"Sales",
+ 'company':"Best Test",
+ 'opportunity_source':'Cold Calling',
+ 'from_date': '2021-08-01',
+ 'to_date':'2021-08-31'
+ }
+
+ report = execute(filters)
+
+ expected_data = [
+ {
+ 'opportunity_owner':'Not Assigned',
+ 'August': 1
+ }
+ ]
+
+ self.assertEqual(expected_data,report[1])
+
+def create_company():
+ doc = frappe.db.exists('Company','Best Test')
+ if not doc:
+ doc = frappe.new_doc('Company')
+ doc.company_name = 'Best Test'
+ doc.default_currency = "INR"
+ doc.insert()
+
+def create_customer():
+ doc = frappe.db.exists("Customer","_Test NC")
+ if not doc:
+ doc = frappe.new_doc("Customer")
+ doc.customer_name = '_Test NC'
+ doc.insert()
+
+def create_opportunity():
+ doc = frappe.db.exists({"doctype":"Opportunity","party_name":"_Test NC"})
+ if not doc:
+ doc = frappe.new_doc("Opportunity")
+ doc.opportunity_from = "Customer"
+ customer_name = frappe.db.get_value("Customer",{"customer_name":'_Test NC'},['customer_name'])
+ doc.party_name = customer_name
+ doc.opportunity_amount = 150000
+ doc.source = "Cold Calling"
+ doc.currency = "INR"
+ doc.expected_closing = "2021-08-31"
+ doc.company = 'Best Test'
+ doc.insert()
\ No newline at end of file
diff --git a/erpnext/crm/workspace/crm/crm.json b/erpnext/crm/workspace/crm/crm.json
index c363395..a661b62 100644
--- a/erpnext/crm/workspace/crm/crm.json
+++ b/erpnext/crm/workspace/crm/crm.json
@@ -148,6 +148,24 @@
"type": "Link"
},
{
+ "hidden": 0,
+ "is_query_report": 1,
+ "label": "Sales Pipeline Analytics",
+ "link_to": "Sales Pipeline Analytics",
+ "link_type": "Report",
+ "onboard": 0,
+ "type": "Link"
+ },
+ {
+ "hidden": 0,
+ "is_query_report": 1,
+ "label": "Opportunity Summary by Sales Stage",
+ "link_to": "Opportunity Summary by Sales Stage",
+ "link_type": "Report",
+ "onboard": 0,
+ "type": "Link"
+ },
+ {
"dependencies": "",
"hidden": 0,
"is_query_report": 0,
@@ -403,7 +421,7 @@
"type": "Link"
}
],
- "modified": "2021-08-05 12:15:56.913091",
+ "modified": "2021-08-19 19:08:08.728876",
"modified_by": "Administrator",
"module": "CRM",
"name": "CRM",