diff --git a/erpnext/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py b/erpnext/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py index 83f664c984a27e73c47ae19ecd6126e2e6072348..b313ed8b173fc76f5d5830994b415565d6236aa6 100644 --- a/erpnext/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py +++ b/erpnext/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py @@ -11,7 +11,7 @@ import erpnext from erpnext.accounts.report.item_wise_sales_register.item_wise_sales_register import ( add_sub_total_row, add_total_row, - apply_group_by_conditions, + apply_order_by_conditions, get_grand_total, get_group_by_and_display_fields, get_tax_accounts, @@ -305,12 +305,6 @@ def apply_conditions(query, pi, pii, filters): if filters.get("item_group"): query = query.where(pii.item_group == filters.get("item_group")) - if not filters.get("group_by"): - query = query.orderby(pi.posting_date, order=Order.desc) - query = query.orderby(pii.item_group, order=Order.desc) - else: - query = apply_group_by_conditions(query, pi, pii, filters) - return query @@ -372,7 +366,17 @@ def get_items(filters, additional_table_columns): query = apply_conditions(query, pi, pii, filters) - return query.run(as_dict=True) + from frappe.desk.reportview import build_match_conditions + + query, params = query.walk() + match_conditions = build_match_conditions("Sales Invoice") + + if match_conditions: + query += " and " + match_conditions + + query = apply_order_by_conditions(query, pi, pii, filters) + + return frappe.db.sql(query, params, as_dict=True) def get_aii_accounts(): diff --git a/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py b/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py index 3c3b4433510b540662b20e00fe55a81873be76c0..6190f0b0db89c95e5eedd499fd6e71b0e3c9bd57 100644 --- a/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py +++ b/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py @@ -385,27 +385,24 @@ def apply_conditions(query, si, sii, filters, additional_conditions=None): | (si.unrealized_profit_loss_account == filters.get("income_account")) ) - if not filters.get("group_by"): - query = query.orderby(si.posting_date, order=Order.desc) - query = query.orderby(sii.item_group, order=Order.desc) - else: - query = apply_group_by_conditions(query, si, sii, filters) - for key, value in (additional_conditions or {}).items(): query = query.where(si[key] == value) return query -def apply_group_by_conditions(query, si, ii, filters): - if filters.get("group_by") == "Invoice": - query = query.orderby(ii.parent, order=Order.desc) +def apply_order_by_conditions(query, si, ii, filters): + if not filters.get("group_by"): + query += f" order by {si.posting_date} desc, {ii.item_group} desc" + elif filters.get("group_by") == "Invoice": + query += f" order by {ii.parent} desc" elif filters.get("group_by") == "Item": - query = query.orderby(ii.item_code) + query += f" order by {ii.item_code}" elif filters.get("group_by") == "Item Group": - query = query.orderby(ii.item_group) + query += f" order by {ii.item_group}" elif filters.get("group_by") in ("Customer", "Customer Group", "Territory", "Supplier"): - query = query.orderby(si[frappe.scrub(filters.get("group_by"))]) + filter_field = frappe.scrub(filters.get("group_by")) + query += f" order by {filter_field} desc" return query @@ -480,7 +477,17 @@ def get_items(filters, additional_query_columns, additional_conditions=None): query = apply_conditions(query, si, sii, filters, additional_conditions) - return query.run(as_dict=True) + from frappe.desk.reportview import build_match_conditions + + query, params = query.walk() + match_conditions = build_match_conditions("Sales Invoice") + + if match_conditions: + query += " and " + match_conditions + + query = apply_order_by_conditions(query, si, sii, filters) + + return frappe.db.sql(query, params, as_dict=True) def get_delivery_notes_against_sales_order(item_list): diff --git a/erpnext/accounts/report/purchase_register/purchase_register.py b/erpnext/accounts/report/purchase_register/purchase_register.py index 2e25a97d7b18df88ae07207c891fea78c6fca295..dc5cb93564948a098925566d91ec41ef6ed7b1fa 100644 --- a/erpnext/accounts/report/purchase_register/purchase_register.py +++ b/erpnext/accounts/report/purchase_register/purchase_register.py @@ -397,7 +397,6 @@ def get_invoices(filters, additional_query_columns): pi.mode_of_payment, ) .where(pi.docstatus == 1) - .orderby(pi.posting_date, pi.name, order=Order.desc) ) if additional_query_columns: @@ -421,8 +420,17 @@ def get_invoices(filters, additional_query_columns): ) query = query.where(pi.credit_to.isin(party_account)) - invoices = query.run(as_dict=True) - return invoices + from frappe.desk.reportview import build_match_conditions + + query, params = query.walk() + match_conditions = build_match_conditions("Purchase Invoice") + + if match_conditions: + query += " and " + match_conditions + + query += " order by posting_date desc, name desc" + + return frappe.db.sql(query, params, as_dict=True) def get_conditions(filters, query, doctype): diff --git a/erpnext/accounts/report/sales_register/sales_register.py b/erpnext/accounts/report/sales_register/sales_register.py index 34d53238f50b1fa71a8ea5dafc9e46eb13c84a74..e55f217682dc26f1b5acddf4278d9c0727fc8a6b 100644 --- a/erpnext/accounts/report/sales_register/sales_register.py +++ b/erpnext/accounts/report/sales_register/sales_register.py @@ -439,7 +439,6 @@ def get_invoices(filters, additional_query_columns): si.company, ) .where(si.docstatus == 1) - .orderby(si.posting_date, si.name, order=Order.desc) ) if additional_query_columns: @@ -457,8 +456,17 @@ def get_invoices(filters, additional_query_columns): filters, query, doctype="Sales Invoice", child_doctype="Sales Invoice Item" ) - invoices = query.run(as_dict=True) - return invoices + from frappe.desk.reportview import build_match_conditions + + query, params = query.walk() + match_conditions = build_match_conditions("Sales Invoice") + + if match_conditions: + query += " and " + match_conditions + + query += " order by posting_date desc, name desc" + + return frappe.db.sql(query, params, as_dict=True) def get_conditions(filters, query, doctype):