Added Customers Not Buying Since Long Time against Sales Invoice
diff --git a/erpnext/selling/report/customers_not_buying_since_long_time/customers_not_buying_since_long_time.js b/erpnext/selling/report/customers_not_buying_since_long_time/customers_not_buying_since_long_time.js
index e975e9f..6d00b82 100644
--- a/erpnext/selling/report/customers_not_buying_since_long_time/customers_not_buying_since_long_time.js
+++ b/erpnext/selling/report/customers_not_buying_since_long_time/customers_not_buying_since_long_time.js
@@ -8,6 +8,13 @@
"label": __("Days Since Last Order"),
"fieldtype": "Int",
"default": 60
+ },
+ {
+ "fieldname":"doctype",
+ "label": __("Doctype"),
+ "fieldtype": "Select",
+ "default": "Sales Order",
+ "options": "Sales Order\nSales Invoice"
}
]
}
\ No newline at end of file
diff --git a/erpnext/selling/report/customers_not_buying_since_long_time/customers_not_buying_since_long_time.py b/erpnext/selling/report/customers_not_buying_since_long_time/customers_not_buying_since_long_time.py
index de4c655..86186aa 100644
--- a/erpnext/selling/report/customers_not_buying_since_long_time/customers_not_buying_since_long_time.py
+++ b/erpnext/selling/report/customers_not_buying_since_long_time/customers_not_buying_since_long_time.py
@@ -10,41 +10,51 @@
if not filters: filters ={}
days_since_last_order = filters.get("days_since_last_order")
+ doctype = filters.get("doctype")
+
if cint(days_since_last_order) <= 0:
frappe.throw(_("'Days Since Last Order' must be greater than or equal to zero"))
columns = get_columns()
- customers = get_so_details()
+ customers = get_sales_details(doctype)
data = []
for cust in customers:
if cint(cust[8]) >= cint(days_since_last_order):
- cust.insert(7,get_last_so_amt(cust[0]))
+ cust.insert(7,get_last_sales_amt(cust[0], doctype))
data.append(cust)
return columns, data
-def get_so_details():
+def get_sales_details(doctype):
+ cond = """sum(so.base_net_total) as 'total_order_considered',
+ max(so.posting_date) as 'last_order_date',
+ DATEDIFF(CURDATE(), max(so.posting_date)) as 'days_since_last_order' """
+ if doctype == "Sales Order":
+ cond = """sum(if(so.status = "Stopped",
+ so.base_net_total * so.per_delivered/100,
+ so.base_net_total)) as 'total_order_considered',
+ max(so.transaction_date) as 'last_order_date',
+ DATEDIFF(CURDATE(), max(so.transaction_date)) as 'days_since_last_order'"""
+
return frappe.db.sql("""select
cust.name,
cust.customer_name,
cust.territory,
cust.customer_group,
count(distinct(so.name)) as 'num_of_order',
- sum(base_net_total) as 'total_order_value',
- sum(if(so.status = "Stopped",
- so.base_net_total * so.per_delivered/100,
- so.base_net_total)) as 'total_order_considered',
- max(so.transaction_date) as 'last_sales_order_date',
- DATEDIFF(CURDATE(), max(so.transaction_date)) as 'days_since_last_order'
- from `tabCustomer` cust, `tabSales Order` so
+ sum(base_net_total) as 'total_order_value', {0}
+ from `tabCustomer` cust, `tab{1}` so
where cust.name = so.customer and so.docstatus = 1
group by cust.name
- order by 'days_since_last_order' desc """,as_list=1)
+ order by 'days_since_last_order' desc """.format(cond, doctype), as_list=1)
-def get_last_so_amt(customer):
- res = frappe.db.sql("""select base_net_total from `tabSales Order`
- where customer = %s and docstatus = 1 order by transaction_date desc
- limit 1""", customer)
+def get_last_sales_amt(customer, doctype):
+ cond = "posting_date"
+ if doctype =="Sales Order":
+ cond = "transaction_date"
+ res = frappe.db.sql("""select base_net_total from `tab{0}`
+ where customer = %s and docstatus = 1 order by {1} desc
+ limit 1""".format(doctype, cond), customer)
return res and res[0][0] or 0
@@ -58,6 +68,6 @@
_("Total Order Value") + ":Currency:120",
_("Total Order Considered") + ":Currency:160",
_("Last Order Amount") + ":Currency:160",
- _("Last Sales Order Date") + ":Date:160",
+ _("Last Order Date") + ":Date:160",
_("Days Since Last Order") + "::160"
]