Merge pull request #18038 from fproldan/sales_analytics_with_order_type_develop
feat: "Order Type" tree type for Sales Analytics report (develop)
diff --git a/erpnext/selling/report/sales_analytics/sales_analytics.js b/erpnext/selling/report/sales_analytics/sales_analytics.js
index fbe045b..149c923 100644
--- a/erpnext/selling/report/sales_analytics/sales_analytics.js
+++ b/erpnext/selling/report/sales_analytics/sales_analytics.js
@@ -8,7 +8,7 @@
fieldname: "tree_type",
label: __("Tree Type"),
fieldtype: "Select",
- options: ["Customer Group","Customer","Item Group","Item","Territory"],
+ options: ["Customer Group","Customer","Item Group","Item","Territory","Order Type"],
default: "Customer",
reqd: 1
},
diff --git a/erpnext/selling/report/sales_analytics/sales_analytics.py b/erpnext/selling/report/sales_analytics/sales_analytics.py
index 3239fc6..8a5e50a 100644
--- a/erpnext/selling/report/sales_analytics/sales_analytics.py
+++ b/erpnext/selling/report/sales_analytics/sales_analytics.py
@@ -23,15 +23,15 @@
self.get_columns()
self.get_data()
self.get_chart_data()
- return self.columns, self.data , None, self.chart
+ return self.columns, self.data, None, self.chart
def get_columns(self):
- self.columns =[{
+ self.columns = [{
"label": _(self.filters.tree_type + " ID"),
- "options": self.filters.tree_type,
+ "options": self.filters.tree_type if self.filters.tree_type != "Order Type" else "",
"fieldname": "entity",
- "fieldtype": "Link",
- "width": 140
+ "fieldtype": "Link" if self.filters.tree_type != "Order Type" else "Data",
+ "width": 140 if self.filters.tree_type != "Order Type" else 200
}]
if self.filters.tree_type in ["Customer", "Supplier", "Item"]:
self.columns.append({
@@ -73,6 +73,28 @@
self.get_sales_transactions_based_on_item_group()
self.get_rows_by_group()
+ elif self.filters.tree_type == "Order Type":
+ if self.filters.doc_type != "Sales Order":
+ self.data = []
+ return
+ self.get_sales_transactions_based_on_order_type()
+ self.get_rows_by_group()
+
+ def get_sales_transactions_based_on_order_type(self):
+ if self.filters["value_quantity"] == 'Value':
+ value_field = "base_net_total"
+ else:
+ value_field = "total_qty"
+
+ self.entries = frappe.db.sql(""" select s.order_type as entity, s.{value_field} as value_field, s.{date_field}
+ from `tab{doctype}` s where s.docstatus = 1 and s.company = %s and s.{date_field} between %s and %s
+ and ifnull(s.order_type, '') != '' order by s.order_type
+ """
+ .format(date_field=self.date_field, value_field=value_field, doctype=self.filters.doc_type),
+ (self.filters.company, self.filters.from_date, self.filters.to_date), as_dict=1)
+
+ self.get_teams()
+
def get_sales_transactions_based_on_customers_or_suppliers(self):
if self.filters["value_quantity"] == 'Value':
value_field = "base_net_total as value_field"
@@ -88,7 +110,7 @@
self.entries = frappe.get_all(self.filters.doc_type,
fields=[entity, entity_name, value_field, self.date_field],
- filters = {
+ filters={
"docstatus": 1,
"company": self.filters.company,
self.date_field: ('between', [self.filters.from_date, self.filters.to_date])
@@ -112,7 +134,7 @@
where s.name = i.parent and i.docstatus = 1 and s.company = %s
and s.{date_field} between %s and %s
"""
- .format(date_field=self.date_field, value_field = value_field, doctype=self.filters.doc_type),
+ .format(date_field=self.date_field, value_field=value_field, doctype=self.filters.doc_type),
(self.filters.company, self.filters.from_date, self.filters.to_date), as_dict=1)
self.entity_names = {}
@@ -135,7 +157,7 @@
self.entries = frappe.get_all(self.filters.doc_type,
fields=[entity_field, value_field, self.date_field],
- filters = {
+ filters={
"docstatus": 1,
"company": self.filters.company,
self.date_field: ('between', [self.filters.from_date, self.filters.to_date])
@@ -154,13 +176,13 @@
from `tab{doctype} Item` i , `tab{doctype}` s
where s.name = i.parent and i.docstatus = 1 and s.company = %s
and s.{date_field} between %s and %s
- """.format(date_field=self.date_field, value_field = value_field, doctype=self.filters.doc_type),
+ """.format(date_field=self.date_field, value_field=value_field, doctype=self.filters.doc_type),
(self.filters.company, self.filters.from_date, self.filters.to_date), as_dict=1)
self.get_groups()
def get_rows(self):
- self.data=[]
+ self.data = []
self.get_periodic_data()
for entity, period_data in iteritems(self.entity_periodic_data):
@@ -192,7 +214,7 @@
period = self.get_period(end_date)
amount = flt(self.entity_periodic_data.get(d.name, {}).get(period, 0.0))
row[scrub(period)] = amount
- if d.parent:
+ if d.parent and (self.filters.tree_type != "Order Type" or d.parent == "Order Types"):
self.entity_periodic_data.setdefault(d.parent, frappe._dict()).setdefault(period, 0.0)
self.entity_periodic_data[d.parent][period] += amount
total += amount
@@ -216,7 +238,7 @@
elif self.filters.range == 'Monthly':
period = str(self.months[posting_date.month - 1]) + " " + str(posting_date.year)
elif self.filters.range == 'Quarterly':
- period = "Quarter " + str(((posting_date.month-1)//3)+1) +" " + str(posting_date.year)
+ period = "Quarter " + str(((posting_date.month - 1) // 3) + 1) + " " + str(posting_date.year)
else:
year = get_fiscal_year(posting_date, company=self.filters.company)
period = str(year[0])
@@ -234,7 +256,7 @@
}.get(self.filters.range, 1)
if self.filters.range in ['Monthly', 'Quarterly']:
- from_date = from_date.replace(day = 1)
+ from_date = from_date.replace(day=1)
elif self.filters.range == "Yearly":
from_date = get_fiscal_year(from_date)[1]
else:
@@ -270,7 +292,22 @@
self.group_entries = frappe.db.sql("""select name, lft, rgt , {parent} as parent
from `tab{tree}` order by lft"""
- .format(tree=self.filters.tree_type, parent=parent), as_dict=1)
+ .format(tree=self.filters.tree_type, parent=parent), as_dict=1)
+
+ for d in self.group_entries:
+ if d.parent:
+ self.depth_map.setdefault(d.name, self.depth_map.get(d.parent) + 1)
+ else:
+ self.depth_map.setdefault(d.name, 0)
+
+ def get_teams(self):
+ self.depth_map = frappe._dict()
+
+ self.group_entries = frappe.db.sql(""" select * from (select "Order Types" as name, 0 as lft,
+ 2 as rgt, '' as parent union select distinct order_type as name, 1 as lft, 1 as rgt, "Order Types" as parent
+ from `tab{doctype}` where ifnull(order_type, '') != '') as b order by lft, name
+ """
+ .format(doctype=self.filters.doc_type), as_dict=1)
for d in self.group_entries:
if d.parent:
@@ -285,13 +322,13 @@
length = len(self.columns)
if self.filters.tree_type in ["Customer", "Supplier", "Item"]:
- labels = [d.get("label") for d in self.columns[2:length-1]]
+ labels = [d.get("label") for d in self.columns[2:length - 1]]
else:
- labels = [d.get("label") for d in self.columns[1:length-1]]
+ labels = [d.get("label") for d in self.columns[1:length - 1]]
self.chart = {
"data": {
'labels': labels,
- 'datasets':[]
+ 'datasets': []
},
"type": "line"
- }
\ No newline at end of file
+ }