Merge branch 'develop' of https://github.com/frappe/erpnext into crm-reports
diff --git a/erpnext/crm/report/lead_details/lead_details.js b/erpnext/crm/report/lead_details/lead_details.js
new file mode 100644
index 0000000..f92070d
--- /dev/null
+++ b/erpnext/crm/report/lead_details/lead_details.js
@@ -0,0 +1,52 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Lead Details"] = {
+ "filters": [
+ {
+ "fieldname":"company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "default": frappe.defaults.get_user_default("Company"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"from_date",
+ "label": __("From Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.add_months(frappe.datetime.get_today(), -12),
+ "reqd": 1
+ },
+ {
+ "fieldname":"to_date",
+ "label": __("To Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.get_today(),
+ "reqd": 1
+ },
+ {
+ "fieldname":"status",
+ "label": __("Status"),
+ "fieldtype": "Select",
+ options: [
+ { "value": "Lead", "label": __("Lead") },
+ { "value": "Open", "label": __("Open") },
+ { "value": "Replied", "label": __("Replied") },
+ { "value": "Opportunity", "label": __("Opportunity") },
+ { "value": "Quotation", "label": __("Quotation") },
+ { "value": "Lost Quotation", "label": __("Lost Quotation") },
+ { "value": "Interested", "label": __("Interested") },
+ { "value": "Converted", "label": __("Converted") },
+ { "value": "Do Not Contact", "label": __("Do Not Contact") },
+ ],
+ },
+ {
+ "fieldname":"territory",
+ "label": __("Territory"),
+ "fieldtype": "Link",
+ "options": "Territory",
+ }
+ ]
+};
\ No newline at end of file
diff --git a/erpnext/crm/report/lead_details/lead_details.json b/erpnext/crm/report/lead_details/lead_details.json
index cdeb6bb..7871d08 100644
--- a/erpnext/crm/report/lead_details/lead_details.json
+++ b/erpnext/crm/report/lead_details/lead_details.json
@@ -7,16 +7,15 @@
"doctype": "Report",
"idx": 3,
"is_standard": "Yes",
- "modified": "2020-01-22 16:51:56.591110",
+ "modified": "2020-07-26 23:59:49.897577",
"modified_by": "Administrator",
"module": "CRM",
"name": "Lead Details",
"owner": "Administrator",
"prepared_report": 0,
- "query": "SELECT\n `tabLead`.name as \"Lead Id:Link/Lead:120\",\n `tabLead`.lead_name as \"Lead Name::120\",\n\t`tabLead`.company_name as \"Company Name::120\",\n\t`tabLead`.status as \"Status::120\",\n\tconcat_ws(', ', \n\t\ttrim(',' from `tabAddress`.address_line1), \n\t\ttrim(',' from tabAddress.address_line2)\n\t) as 'Address::180',\n\t`tabAddress`.state as \"State::100\",\n\t`tabAddress`.pincode as \"Pincode::70\",\n\t`tabAddress`.country as \"Country::100\",\n\t`tabLead`.phone as \"Phone::100\",\n\t`tabLead`.mobile_no as \"Mobile No::100\",\n\t`tabLead`.email_id as \"Email Id::120\",\n\t`tabLead`.lead_owner as \"Lead Owner::120\",\n\t`tabLead`.source as \"Source::120\",\n\t`tabLead`.territory as \"Territory::120\",\n\t`tabLead`.notes as \"Notes::360\",\n `tabLead`.owner as \"Owner:Link/User:120\"\nFROM\n\t`tabLead`\n\tleft join `tabDynamic Link` on (\n\t\t`tabDynamic Link`.link_name=`tabLead`.name \n\t\tand `tabDynamic Link`.parenttype = 'Address'\n\t)\n\tleft join `tabAddress` on (\n\t\t`tabAddress`.name=`tabDynamic Link`.parent\n\t)\nWHERE\n\t`tabLead`.docstatus<2\nORDER BY\n\t`tabLead`.name asc",
"ref_doctype": "Lead",
"report_name": "Lead Details",
- "report_type": "Query Report",
+ "report_type": "Script Report",
"roles": [
{
"role": "Sales User"
diff --git a/erpnext/crm/report/lead_details/lead_details.py b/erpnext/crm/report/lead_details/lead_details.py
new file mode 100644
index 0000000..5b1849a
--- /dev/null
+++ b/erpnext/crm/report/lead_details/lead_details.py
@@ -0,0 +1,164 @@
+# Copyright (c) 2020, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+from frappe import _
+import frappe
+
+def execute(filters=None):
+ columns, data = get_columns(), get_leads(filters)
+ return columns, data
+
+def get_columns():
+ columns = [
+ {
+ "label": _("Lead"),
+ "fieldname": "name",
+ "fieldtype": "Link",
+ "options": "Lead",
+ "width": 150,
+ },
+ {
+ "label": _("Lead Name"),
+ "fieldname": "lead_name",
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "fieldname":"status",
+ "label": _("Status"),
+ "fieldtype": "Data",
+ "width": 100
+ },
+ {
+ "fieldname":"lead_owner",
+ "label": _("Lead Owner"),
+ "fieldtype": "Link",
+ "options": "User",
+ "width": 100
+ },
+ {
+ "label": _("Territory"),
+ "fieldname": "territory",
+ "fieldtype": "Link",
+ "options": "Territory",
+ "width": 100
+ },
+ {
+ "label": _("Source"),
+ "fieldname": "source",
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "label": _("Email"),
+ "fieldname": "email_id",
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "label": _("Mobile"),
+ "fieldname": "mobile_no",
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "label": _("Phone"),
+ "fieldname": "phone",
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "label": _("Notes"),
+ "fieldname": "notes",
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "label": _("Owner"),
+ "fieldname": "owner",
+ "fieldtype": "Link",
+ "options": "user",
+ "width": 120
+ },
+ {
+ "label": _("Company"),
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "options": "Company",
+ "width": 120
+ },
+ {
+ "fieldname":"address",
+ "label": _("Address"),
+ "fieldtype": "Data",
+ "width": 130
+ },
+ {
+ "fieldname":"state",
+ "label": _("State"),
+ "fieldtype": "Data",
+ "width": 100
+ },
+ {
+ "fieldname":"pincode",
+ "label": _("Postal Code"),
+ "fieldtype": "Data",
+ "width": 90
+ },
+ {
+ "fieldname":"country",
+ "label": _("Country"),
+ "fieldtype": "Link",
+ "options": "Country",
+ "width": 100
+ },
+
+ ]
+ return columns
+
+def get_leads(filters):
+ return frappe.db.sql("""
+ SELECT
+ `tabLead`.name,
+ `tabLead`.lead_name,
+ `tabLead`.status,
+ `tabLead`.lead_owner,
+ `tabLead`.territory,
+ `tabLead`.source,
+ `tabLead`.email_id,
+ `tabLead`.mobile_no,
+ `tabLead`.phone,
+ `tabLead`.notes,
+ `tabLead`.owner,
+ `tabLead`.company,
+ concat_ws(', ',
+ trim(',' from `tabAddress`.address_line1),
+ trim(',' from tabAddress.address_line2)
+ ) AS address,
+ `tabAddress`.state,
+ `tabAddress`.pincode,
+ `tabAddress`.country
+ FROM
+ `tabLead` left join `tabDynamic Link` on (
+ `tabLead`.name = `tabDynamic Link`.link_name and
+ `tabDynamic Link`.parenttype = 'Address')
+ left join `tabAddress` on (
+ `tabAddress`.name=`tabDynamic Link`.parent)
+ WHERE
+ company = %(company)s
+ AND `tabLead`.creation BETWEEN %(from_date)s AND %(to_date)s
+ {conditions}
+ ORDER BY
+ `tabLead`.creation asc """.format(conditions=get_conditions(filters)), filters, as_dict=1)
+
+def get_conditions(filters) :
+ conditions = []
+
+ if filters.get("territory"):
+ conditions.append("territory=%(territory)s")
+
+ if filters.get("status"):
+ conditions.append("status=%(status)s")
+
+ return " and {}".format(" and ".join(conditions)) if conditions else ""