Merge branch 'fixes_8466' of https://github.com/PawanMeh/erpnext into PawanMeh-fixes_8466
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..3088332
--- /dev/null
+++ b/erpnext/crm/report/campaign_efficiency/campaign_efficiency.py
@@ -0,0 +1,70 @@
+# 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 flt,cstr
+from erpnext.accounts.report.financial_statements import get_period_list
+
+def execute(filters=None):
+ columns, data = [], []
+ columns=get_columns()
+ data=get_lead_data(filters)
+ return columns, data
+
+def get_columns():
+ columns = [_("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"
+ ]
+ return columns
+
+def get_lead_data(filters):
+ conditions=""
+ if filters.from_date:
+ conditions += " and date(creation) >= %(from_date)s"
+ if filters.to_date:
+ conditions += " and date(creation) <= %(to_date)s"
+ data = frappe.db.sql("""select campaign_name as "Campaign Name", count(name) as "Lead Count" from `tabLead`
+ where 1 = 1 %s group by campaign_name""" % (conditions,),filters, as_dict=1)
+ dl=list(data)
+ for row in dl:
+ is_quot_count_zero = False
+ row["Quot Count"]= get_lead_quotation_count(row["Campaign Name"])
+ row["Opp Count"] = get_lead_opp_count(row["Campaign Name"])
+ row["Order Count"] = get_quotation_ordered_count(row["Campaign Name"])
+ row["Order Value"] = get_order_amount(row["Campaign Name"])
+ row["Opp/Lead %"] = row["Opp Count"] / row["Lead Count"] * 100
+ row["Quot/Lead %"] = row["Quot Count"] / row["Lead Count"] * 100
+ #Handle div by zero and reset count to zero
+ if row["Quot Count"] == 0:
+ row["Quot Count"] = 1
+ is_quot_count_zero = True
+ row["Order/Quot %"] = row["Order Count"] / row["Quot Count"] * 100
+ if is_quot_count_zero == True:
+ row["Quot Count"] = 0
+ return dl
+
+def get_lead_quotation_count(campaign):
+ quotation_count = frappe.db.sql("""select count(name) from `tabQuotation`
+ where lead in (select name from `tabLead` where campaign_name = %s)""",campaign)
+ return flt(quotation_count[0][0]) if quotation_count else 0
+
+def get_lead_opp_count(campaign):
+ opportunity_count = frappe.db.sql("""select count(name) from `tabOpportunity`
+ where lead in (select name from `tabLead` where campaign_name = %s)""",campaign)
+ return flt(opportunity_count[0][0]) if opportunity_count else 0
+
+def get_quotation_ordered_count(campaign):
+ quotation_ordered_count = frappe.db.sql("""select count(name) from `tabQuotation` where status = 'Ordered'
+ and lead in (select name from `tabLead` where campaign_name = %s)""",campaign)
+ return flt(quotation_ordered_count[0][0]) if quotation_ordered_count else 0
+
+def get_order_amount(campaign):
+ ordered_count_amount = 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 (select name from `tabLead` where campaign_name = %s))""",campaign)
+ return flt(ordered_count_amount[0][0]) if ordered_count_amount else 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..1560f86
--- /dev/null
+++ b/erpnext/crm/report/lead_owner_efficiency/lead_owner_efficiency.py
@@ -0,0 +1,72 @@
+# 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 flt,cstr
+from erpnext.accounts.report.financial_statements import get_period_list
+
+def execute(filters=None):
+ columns, data = [], []
+ columns=get_columns()
+ data=get_lead_data(filters)
+ return columns, data
+
+def get_columns():
+ columns = [_("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"
+ ]
+ return columns
+
+def get_lead_data(filters):
+ conditions=""
+ if filters.from_date:
+ conditions += " and date(creation) >= %(from_date)s"
+ if filters.to_date:
+ conditions += " and date(creation) <= %(to_date)s"
+ data = frappe.db.sql("""select lead_owner as "Lead Owner", count(name) as "Lead Count"
+ from `tabLead` where 1 = 1 %s group by lead_owner""" % (conditions,),filters, as_dict=1)
+ dl=list(data)
+ for row in dl:
+ is_quot_count_zero = False
+ row["Quot Count"]= get_lead_quotation_count(row["Lead Owner"])
+ row["Opp Count"] = get_lead_opp_count(row["Lead Owner"])
+ row["Order Count"] = get_quotation_ordered_count(row["Lead Owner"])
+ row["Order Value"] = get_order_amount(row["Lead Owner"])
+ row["Opp/Lead %"] = row["Opp Count"] / row["Lead Count"] * 100
+ row["Quot/Lead %"] = row["Quot Count"] / row["Lead Count"] * 100
+ #Handle div by zero and reset count to zero
+ if row["Quot Count"] == 0:
+ row["Quot Count"] = 1
+ is_quot_count_zero = True
+ row["Order/Quot %"] = row["Order Count"] / row["Quot Count"] * 100
+ if is_quot_count_zero == True:
+ row["Quot Count"] = 0
+ return dl
+
+def get_lead_quotation_count(leadowner):
+ quotation_count = frappe.db.sql("""select count(name) from `tabQuotation`
+ where lead in (select name from `tabLead` where lead_owner = %s)""",leadowner)
+ return flt(quotation_count[0][0]) if quotation_count else 0
+
+def get_lead_opp_count(leadowner):
+ opportunity_count = frappe.db.sql("""select count(name) from `tabOpportunity`
+ where lead in (select name from `tabLead` where lead_owner = %s)""",leadowner)
+ return flt(opportunity_count[0][0]) if opportunity_count else 0
+
+def get_quotation_ordered_count(leadowner):
+ quotation_ordered_count = frappe.db.sql("""select count(name) from `tabQuotation`
+ where status = 'Ordered' and lead in
+ (select name from `tabLead` where lead_owner = %s)""",leadowner)
+ return flt(quotation_ordered_count[0][0]) if quotation_ordered_count else 0
+
+def get_order_amount(leadowner):
+ ordered_count_amount = 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
+ (select name from `tabLead` where lead_owner = %s))""",leadowner)
+ return flt(ordered_count_amount[0][0]) if ordered_count_amount else 0
\ No newline at end of file