[formatting] Added between in trends.py
diff --git a/erpnext/controllers/trends.py b/erpnext/controllers/trends.py
index 3a62716..2298c3f 100644
--- a/erpnext/controllers/trends.py
+++ b/erpnext/controllers/trends.py
@@ -43,6 +43,10 @@
inc, cond= '',''
query_details = conditions["based_on_select"] + conditions["period_wise_select"]
+ posting_date = 't1.transaction_date'
+ if conditions.get('trans') in ['Sales Invoice', 'Purchase Invoice', 'Purchase Receipt', 'Delivery Note']:
+ posting_date = 't1.posting_date'
+
if conditions["based_on_select"] in ["t1.project,", "t2.project,"]:
cond = 'and '+ conditions["based_on_select"][:-1] +' IS Not NULL'
@@ -65,11 +69,11 @@
else :
inc = 1
data1 = frappe.db.sql(""" select %s from `tab%s` t1, `tab%s Item` t2 %s
- where t2.parent = t1.name and t1.company = %s and t1.posting_date >= %s and %s >= t1.posting_date and
+ where t2.parent = t1.name and t1.company = %s and %s between %s and %s and
t1.docstatus = 1 %s %s
group by %s
""" % (query_details, conditions["trans"], conditions["trans"], conditions["addl_tables"], "%s",
- "%s", "%s", conditions.get("addl_tables_relational_cond"), cond, conditions["group_by"]), (filters.get("company"),
+ posting_date, "%s", "%s", conditions.get("addl_tables_relational_cond"), cond, conditions["group_by"]), (filters.get("company"),
year_start_date, year_end_date),as_list=1)
for d in range(len(data1)):
@@ -80,11 +84,11 @@
#to get distinct value of col specified by group_by in filter
row = frappe.db.sql("""select DISTINCT(%s) from `tab%s` t1, `tab%s Item` t2 %s
- where t2.parent = t1.name and t1.company = %s and t1.posting_date >= %s and %s >= t1.posting_date
+ where t2.parent = t1.name and t1.company = %s and %s between %s and %s
and t1.docstatus = 1 and %s = %s %s
""" %
(sel_col, conditions["trans"], conditions["trans"], conditions["addl_tables"],
- "%s", "%s", "%s", conditions["group_by"], "%s", conditions.get("addl_tables_relational_cond")),
+ "%s", posting_date, "%s", "%s", conditions["group_by"], "%s", conditions.get("addl_tables_relational_cond")),
(filters.get("company"), year_start_date, year_end_date, data1[d][0]), as_list=1)
for i in range(len(row)):
@@ -92,11 +96,11 @@
#get data for group_by filter
row1 = frappe.db.sql(""" select %s , %s from `tab%s` t1, `tab%s Item` t2 %s
- where t2.parent = t1.name and t1.company = %s and t1.posting_date >= %s and %s >= t1.posting_date
+ where t2.parent = t1.name and t1.company = %s and %s between %s and %s
and t1.docstatus = 1 and %s = %s and %s = %s %s
""" %
(sel_col, conditions["period_wise_select"], conditions["trans"],
- conditions["trans"], conditions["addl_tables"], "%s", "%s","%s", sel_col,
+ conditions["trans"], conditions["addl_tables"], "%s", posting_date, "%s","%s", sel_col,
"%s", conditions["group_by"], "%s", conditions.get("addl_tables_relational_cond")),
(filters.get("company"), year_start_date, year_end_date, row[i][0],
data1[d][0]), as_list=1)
@@ -109,12 +113,12 @@
data.append(des)
else:
data = frappe.db.sql(""" select %s from `tab%s` t1, `tab%s Item` t2 %s
- where t2.parent = t1.name and t1.company = %s and t1.posting_date >= %s and %s >= t1.posting_date and
+ where t2.parent = t1.name and t1.company = %s and %s between %s and %s and
t1.docstatus = 1 %s %s
group by %s
""" %
(query_details, conditions["trans"], conditions["trans"], conditions["addl_tables"],
- "%s", "%s", "%s", cond, conditions.get("addl_tables_relational_cond", ""), conditions["group_by"]),
+ "%s", posting_date, "%s", "%s", cond, conditions.get("addl_tables_relational_cond", ""), conditions["group_by"]),
(filters.get("company"), year_start_date, year_end_date), as_list=1)
return data