Merge branch 'PawanMeh-fixes_8466' into develop
diff --git a/erpnext/crm/report/campaign_efficiency/__init__.py b/erpnext/crm/report/campaign_efficiency/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/crm/report/campaign_efficiency/__init__.py
diff --git a/erpnext/crm/report/campaign_efficiency/campaign_efficiency.js b/erpnext/crm/report/campaign_efficiency/campaign_efficiency.js
new file mode 100644
index 0000000..2b25f1d
--- /dev/null
+++ b/erpnext/crm/report/campaign_efficiency/campaign_efficiency.js
@@ -0,0 +1,19 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+frappe.query_reports["Campaign Efficiency"] = {
+ "filters": [
+ {
+ "fieldname": "from_date",
+ "label": __("From Date"),
+ "fieldtype": "Date",
+ "default": frappe.defaults.get_user_default("year_start_date"),
+ },
+ {
+ "fieldname": "to_date",
+ "label": __("To Date"),
+ "fieldtype": "Date",
+ "default": frappe.defaults.get_user_default("year_end_date"),
+ }
+ ]
+ };
+
diff --git a/erpnext/crm/report/campaign_efficiency/campaign_efficiency.json b/erpnext/crm/report/campaign_efficiency/campaign_efficiency.json
new file mode 100644
index 0000000..986d9f3
--- /dev/null
+++ b/erpnext/crm/report/campaign_efficiency/campaign_efficiency.json
@@ -0,0 +1,30 @@
+{
+ "add_total_row": 0,
+ "apply_user_permissions": 1,
+ "creation": "2017-04-17 00:20:27.248275",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "letter_head": "",
+ "modified": "2017-04-17 00:20:27.248275",
+ "modified_by": "Administrator",
+ "module": "CRM",
+ "name": "Campaign Efficiency",
+ "owner": "Administrator",
+ "ref_doctype": "Lead",
+ "report_name": "Campaign Efficiency",
+ "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/campaign_efficiency/campaign_efficiency.py b/erpnext/crm/report/campaign_efficiency/campaign_efficiency.py
new file mode 100644
index 0000000..b20fe15
--- /dev/null
+++ b/erpnext/crm/report/campaign_efficiency/campaign_efficiency.py
@@ -0,0 +1,89 @@
+# 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 _
+
+def execute(filters=None):
+ columns, data = [], []
+ columns=get_columns()
+ data=get_lead_data(filters, "Campaign Name")
+ return columns, data
+
+def get_columns():
+ return [
+ _("Campaign Name") + ":data:130",
+ _("Lead Count") + ":Int:80",
+ _("Opp Count") + ":Int:80",
+ _("Quot Count") + ":Int:80",
+ _("Order Count") + ":Int:100",
+ _("Order Value") + ":Float:100",
+ _("Opp/Lead %") + ":Float:100",
+ _("Quot/Lead %") + ":Float:100",
+ _("Order/Quot %") + ":Float:100"
+ ]
+
+def get_lead_data(filters, based_on):
+ based_on_field = frappe.scrub(based_on)
+ conditions = get_filter_conditions(filters)
+
+ lead_details = frappe.db.sql("""
+ select {based_on_field}, name
+ from `tabLead`
+ where {based_on_field} is not null and {based_on_field} != '' {conditions}
+ """.format(based_on_field=based_on_field, conditions=conditions), filters, as_dict=1)
+
+ lead_map = frappe._dict()
+ for d in lead_details:
+ lead_map.setdefault(d.get(based_on_field), []).append(d.name)
+
+ data = []
+ for based_on_value, leads in lead_map.items():
+ row = {
+ based_on: based_on_value,
+ "Lead Count": len(leads)
+ }
+ row["Quot Count"]= get_lead_quotation_count(leads)
+ row["Opp Count"] = get_lead_opp_count(leads)
+ row["Order Count"] = get_quotation_ordered_count(leads)
+ row["Order Value"] = get_order_amount(leads)
+
+ row["Opp/Lead %"] = row["Opp Count"] / row["Lead Count"] * 100
+ row["Quot/Lead %"] = row["Quot Count"] / row["Lead Count"] * 100
+
+ row["Order/Quot %"] = row["Order Count"] / (row["Quot Count"] or 1) * 100
+
+ data.append(row)
+
+ return data
+
+def get_filter_conditions(filters):
+ conditions=""
+ if filters.from_date:
+ conditions += " and date(creation) >= %(from_date)s"
+ if filters.to_date:
+ conditions += " and date(creation) <= %(to_date)s"
+
+ return conditions
+
+def get_lead_quotation_count(leads):
+ return frappe.db.sql("""select count(name) from `tabQuotation`
+ where lead in (%s)""" % ', '.join(["%s"]*len(leads)), tuple(leads))[0][0]
+
+def get_lead_opp_count(leads):
+ return frappe.db.sql("""select count(name) from `tabOpportunity`
+ where lead in (%s)""" % ', '.join(["%s"]*len(leads)), tuple(leads))[0][0]
+
+def get_quotation_ordered_count(leads):
+ return frappe.db.sql("""select count(name)
+ from `tabQuotation` where status = 'Ordered'
+ and lead in (%s)""" % ', '.join(["%s"]*len(leads)), tuple(leads))[0][0]
+
+def get_order_amount(leads):
+ return frappe.db.sql("""select sum(base_net_amount)
+ from `tabSales Order Item`
+ where prevdoc_docname in (
+ select name from `tabQuotation` where status = 'Ordered'
+ and lead in (%s)
+ )""" % ', '.join(["%s"]*len(leads)), tuple(leads))[0][0]
\ No newline at end of file
diff --git a/erpnext/crm/report/lead_owner_efficiency/__init__.py b/erpnext/crm/report/lead_owner_efficiency/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/crm/report/lead_owner_efficiency/__init__.py
diff --git a/erpnext/crm/report/lead_owner_efficiency/lead_owner_efficiency.js b/erpnext/crm/report/lead_owner_efficiency/lead_owner_efficiency.js
new file mode 100644
index 0000000..bbfd6ac
--- /dev/null
+++ b/erpnext/crm/report/lead_owner_efficiency/lead_owner_efficiency.js
@@ -0,0 +1,17 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+ frappe.query_reports["Lead Owner Efficiency"] = {
+ "filters": [
+ {
+ "fieldname": "from_date",
+ "label": __("From Date"),
+ "fieldtype": "Date",
+ "default": frappe.defaults.get_user_default("year_start_date"),
+ },
+ {
+ "fieldname": "to_date",
+ "label": __("To Date"),
+ "fieldtype": "Date",
+ "default": frappe.defaults.get_user_default("year_end_date"),
+ }
+ ]};
diff --git a/erpnext/crm/report/lead_owner_efficiency/lead_owner_efficiency.json b/erpnext/crm/report/lead_owner_efficiency/lead_owner_efficiency.json
new file mode 100644
index 0000000..b6dadef
--- /dev/null
+++ b/erpnext/crm/report/lead_owner_efficiency/lead_owner_efficiency.json
@@ -0,0 +1,30 @@
+{
+ "add_total_row": 0,
+ "apply_user_permissions": 1,
+ "creation": "2017-04-17 00:39:39.885905",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "letter_head": "",
+ "modified": "2017-04-17 00:45:10.139004",
+ "modified_by": "Administrator",
+ "module": "CRM",
+ "name": "Lead Owner Efficiency",
+ "owner": "Administrator",
+ "ref_doctype": "Lead",
+ "report_name": "Lead Owner Efficiency",
+ "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/lead_owner_efficiency/lead_owner_efficiency.py b/erpnext/crm/report/lead_owner_efficiency/lead_owner_efficiency.py
new file mode 100644
index 0000000..8134bc2
--- /dev/null
+++ b/erpnext/crm/report/lead_owner_efficiency/lead_owner_efficiency.py
@@ -0,0 +1,26 @@
+# 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.crm.report.campaign_efficiency.campaign_efficiency import get_lead_data
+
+def execute(filters=None):
+ columns, data = [], []
+ columns=get_columns()
+ data=get_lead_data(filters, "Lead Owner")
+ return columns, data
+
+def get_columns():
+ return [
+ _("Lead Owner") + ":Data:130",
+ _("Lead Count") + ":Int:80",
+ _("Opp Count") + ":Int:80",
+ _("Quot Count") + ":Int:80",
+ _("Order Count") + ":Int:100",
+ _("Order Value") + ":Float:100",
+ _("Opp/Lead %") + ":Float:100",
+ _("Quot/Lead %") + ":Float:100",
+ _("Order/Quot %") + ":Float:100"
+ ]
\ No newline at end of file