lost opportunity
diff --git a/erpnext/crm/report/lead_details/lead_details.py b/erpnext/crm/report/lead_details/lead_details.py
index 9f4ae60..eeaaec2 100644
--- a/erpnext/crm/report/lead_details/lead_details.py
+++ b/erpnext/crm/report/lead_details/lead_details.py
@@ -140,18 +140,19 @@
`tabAddress`.name=`tabDynamic Link`.parent)
WHERE
company = %(company)s
- AND `tabLead`.creation BETWEEN %(from_date)s AND %(to_date)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 = ""
+ conditions = []
if filters.get("territory"):
- conditions+=" and territory=%(territory)s "
+ conditions.append(" and `tabLead`.territory=%(territory)s")
if filters.get("status"):
- conditions+=" and status=%(status)s "
+ conditions.append(" and `tabLead`.status=%(status)s")
+
+ return " ".join(conditions) if conditions else ""
- return conditions
diff --git a/erpnext/crm/report/lost_opportunity/lost_opportunity.js b/erpnext/crm/report/lost_opportunity/lost_opportunity.js
index c6bf888..d79f8c8 100644
--- a/erpnext/crm/report/lost_opportunity/lost_opportunity.js
+++ b/erpnext/crm/report/lost_opportunity/lost_opportunity.js
@@ -13,6 +13,32 @@
"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":"lost_reason",
+ "label": __("Lost Reason"),
+ "fieldtype": "Link",
+ "options": "Opportunity Lost Reason"
+ },
+ {
+ "fieldname":"territory",
+ "label": __("Territory"),
+ "fieldtype": "Link",
+ "options": "Territory"
+ },
+ {
"fieldname":"opportunity_from",
"label": __("Opportunity From"),
"fieldtype": "Link",
diff --git a/erpnext/crm/report/lost_opportunity/lost_opportunity.py b/erpnext/crm/report/lost_opportunity/lost_opportunity.py
index 094fa96..1aa4afe 100644
--- a/erpnext/crm/report/lost_opportunity/lost_opportunity.py
+++ b/erpnext/crm/report/lost_opportunity/lost_opportunity.py
@@ -45,17 +45,31 @@
"width": 130
},
{
- "label": _("Next Contact By"),
- "fieldname": "contact_by",
- "fieldtype": "Link",
- "options": "User",
- "width": 120
- },
- {
"label": _("Lost Reasons"),
"fieldname": "lost_reason",
"fieldtype": "Data",
"width": 220
+ },
+ {
+ "label": _("Sales Stage"),
+ "fieldname": "sales_stage",
+ "fieldtype": "Link",
+ "options": "Sales Stage",
+ "width": 150
+ },
+ {
+ "label": _("Territory"),
+ "fieldname": "territory",
+ "fieldtype": "Link",
+ "options": "Territory",
+ "width": 150
+ },
+ {
+ "label": _("Next Contact By"),
+ "fieldname": "contact_by",
+ "fieldtype": "Link",
+ "options": "User",
+ "width": 150
}
]
return columns
@@ -69,30 +83,49 @@
`tabOpportunity`.customer_name,
`tabOpportunity`.opportunity_type,
`tabOpportunity`.contact_by,
- GROUP_CONCAT(`tabLost Reason Detail`.lost_reason separator ', ') lost_reason
+ GROUP_CONCAT(`tabOpportunity Lost Reason Detail`.lost_reason separator ', ') lost_reason,
+ `tabOpportunity`.sales_stage,
+ `tabOpportunity`.territory
FROM
- `tabOpportunity`
- LEFT JOIN `tabLost Reason Detail`
- ON `tabLost Reason Detail`.parenttype = 'Opportunity' and `tabLost Reason Detail`.parent = `tabOpportunity`.name
+ `tabOpportunity`
+ {join}
WHERE
`tabOpportunity`.status = 'Lost' and `tabOpportunity`.company = %(company)s
- {conditions}
+ AND `tabOpportunity`.modified BETWEEN %(from_date)s AND %(to_date)s
+ {conditions}
GROUP BY
- `tabOpportunity`.name
+ `tabOpportunity`.name
ORDER BY
- `tabOpportunity`.creation asc """.format(conditions=get_conditions(filters)), filters, as_dict=1)
+ `tabOpportunity`.creation asc """.format(conditions=get_conditions(filters), join=get_join(filters)), filters, as_dict=1)
-def get_conditions(filters) :
+def get_conditions(filters):
conditions = []
+ if filters.get("territory"):
+ conditions.append(" and `tabOpportunity`.territory=%(territory)s")
+
if filters.get("opportunity_from"):
- conditions.append("opportunity_from=%(opportunity_from)s")
+ conditions.append(" and `tabOpportunity`.opportunity_from=%(opportunity_from)s")
if filters.get("party_name"):
- conditions.append("party_name=%(party_name)s")
+ conditions.append(" and `tabOpportunity`.party_name=%(party_name)s")
if filters.get("contact_by"):
- conditions.append("contact_by=%(contact_by)s")
+ conditions.append(" and `tabOpportunity`.contact_by=%(contact_by)s")
- return " and {}".format(" and ".join(conditions)) if conditions else ""
+ return " ".join(conditions) if conditions else ""
+
+def get_join(filters):
+ join = """LEFT JOIN `tabOpportunity Lost Reason Detail`
+ ON `tabOpportunity Lost Reason Detail`.parenttype = 'Opportunity' and
+ `tabOpportunity Lost Reason Detail`.parent = `tabOpportunity`.name"""
+
+ if filters.get("lost_reason"):
+ join = """JOIN `tabOpportunity Lost Reason Detail`
+ ON `tabOpportunity Lost Reason Detail`.parenttype = 'Opportunity' and
+ `tabOpportunity Lost Reason Detail`.parent = `tabOpportunity`.name and
+ `tabOpportunity Lost Reason Detail`.lost_reason = '{0}'
+ """.format(filters.get("lost_reason"))
+
+ return join
\ No newline at end of file