[enhance] Report for prospects engaged but not converted
diff --git a/erpnext/config/crm.py b/erpnext/config/crm.py
index 9935aef..ee3b9c1 100644
--- a/erpnext/config/crm.py
+++ b/erpnext/config/crm.py
@@ -46,6 +46,12 @@
},
{
"type": "report",
+ "name": "Prospects Engaged But Not Converted",
+ "doctype": "Lead",
+ "is_query_report": True
+ },
+ {
+ "type": "report",
"is_query_report": True,
"name": "Lead Details",
"doctype": "Lead"
diff --git a/erpnext/crm/report/prospects_engaged_but_not_converted/__init__.py b/erpnext/crm/report/prospects_engaged_but_not_converted/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/crm/report/prospects_engaged_but_not_converted/__init__.py
diff --git a/erpnext/crm/report/prospects_engaged_but_not_converted/prospects_engaged_but_not_converted.js b/erpnext/crm/report/prospects_engaged_but_not_converted/prospects_engaged_but_not_converted.js
new file mode 100644
index 0000000..6f37719
--- /dev/null
+++ b/erpnext/crm/report/prospects_engaged_but_not_converted/prospects_engaged_but_not_converted.js
@@ -0,0 +1,25 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+frappe.query_reports["Prospects Engaged But Not Converted"] = {
+ "filters": [
+ {
+ "fieldname": "lead",
+ "label": __("Lead"),
+ "fieldtype": "Link",
+ "options": "Lead"
+ },
+ {
+ "fieldname": "no_of_interaction",
+ "label": __("Number of Interaction"),
+ "fieldtype": "Int",
+ "default": 1
+ },
+ {
+ "fieldname": "lead_age",
+ "label": __("Minimum Lead Age (Days)"),
+ "fieldtype": "Int",
+ "default": 60
+ },
+ ]
+}
diff --git a/erpnext/crm/report/prospects_engaged_but_not_converted/prospects_engaged_but_not_converted.json b/erpnext/crm/report/prospects_engaged_but_not_converted/prospects_engaged_but_not_converted.json
new file mode 100644
index 0000000..9a21e0b
--- /dev/null
+++ b/erpnext/crm/report/prospects_engaged_but_not_converted/prospects_engaged_but_not_converted.json
@@ -0,0 +1,29 @@
+{
+ "add_total_row": 0,
+ "apply_user_permissions": 1,
+ "creation": "2017-04-04 08:25:40.491063",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2017-04-04 10:00:10.253224",
+ "modified_by": "Administrator",
+ "module": "CRM",
+ "name": "Prospects Engaged But Not Converted",
+ "owner": "Administrator",
+ "ref_doctype": "Lead",
+ "report_name": "Prospects Engaged But Not Converted",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Sales User"
+ },
+ {
+ "role": "Sales Manager"
+ },
+ {
+ "role": "System Manager"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/crm/report/prospects_engaged_but_not_converted/prospects_engaged_but_not_converted.py b/erpnext/crm/report/prospects_engaged_but_not_converted/prospects_engaged_but_not_converted.py
new file mode 100644
index 0000000..36a4ad6
--- /dev/null
+++ b/erpnext/crm/report/prospects_engaged_but_not_converted/prospects_engaged_but_not_converted.py
@@ -0,0 +1,74 @@
+# 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 add_days, now
+
+def execute(filters=None):
+ columns, data = [], []
+ set_defaut_value_for_filters(filters)
+ columns = get_columns()
+ data = get_data(filters)
+
+ return columns, data
+
+def set_defaut_value_for_filters(filters):
+ if not filters.get('no_of_interaction'): filters["no_of_interaction"] = 1
+ if not filters.get('lead_age'): filters["lead_age"] = 60
+
+def get_columns():
+ return [
+ _("Lead") + ":Link/Lead:100",
+ _("Name") + "::100",
+ _("Organization") + "::100",
+ _("Reference Document") + "::150",
+ _("Reference Name") + ":Dynamic Link/"+_("Reference Document")+":120",
+ _("Last Communication") + ":Data:200",
+ _("Last Communication Date") + ":Date:180"
+ ]
+
+def get_data(filters):
+ lead_details = []
+ lead_filters = get_lead_filters(filters)
+
+ for lead in frappe.get_all('Lead', fields = ['name', 'lead_name', 'company_name'], filters=lead_filters):
+ data = frappe.db.sql("""
+ select
+ `tabCommunication`.reference_doctype, `tabCommunication`.reference_name,
+ `tabCommunication`.content, `tabCommunication`.communication_date
+ from
+ (
+ (select name, lead from `tabOpportunity` where lead = %(lead)s)
+ union
+ (select name, lead from `tabQuotation` where lead = %(lead)s)
+ union
+ (select name, lead from `tabIssue` where lead = %(lead)s and status!='Closed')
+ union
+ (select %(lead)s, %(lead)s)
+ )
+ as ref_document, `tabCommunication`
+ where
+ `tabCommunication`.reference_name = ref_document.name and
+ `tabCommunication`.sent_or_received = 'Received'
+ order by
+ ref_document.lead, `tabCommunication`.creation desc limit %(limit)s""",
+ {'lead': lead.name, 'limit': filters.get('no_of_interaction')})
+
+ for lead_info in data:
+ lead_data = [lead.name, lead.lead_name, lead.company_name] + list(lead_info)
+ lead_details.append(lead_data)
+
+ return lead_details
+
+def get_lead_filters(filters):
+ lead_creation_date = get_creation_date_based_on_lead_age(filters)
+ lead_filters = [["status", "!=", "Converted"], ["creation", ">", lead_creation_date]]
+
+ if filters.get('lead'):
+ lead_filters.append(["name", "=", filters.get('lead')])
+ return lead_filters
+
+def get_creation_date_based_on_lead_age(filters):
+ return add_days(now(), (filters.get('lead_age') * -1))
\ No newline at end of file