[fix] query fix for match conditions (#10810)
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 b21027e..710099e 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
@@ -91,10 +91,10 @@
conditions = ""
for opts in (("company", " and company=%(company)s"),
- ("supplier", " and pi.supplier = %(supplier)s"),
- ("item_code", " and pi_item.item_code = %(item_code)s"),
- ("from_date", " and pi.posting_date>=%(from_date)s"),
- ("to_date", " and pi.posting_date<=%(to_date)s"),
+ ("supplier", " and `tabPurchase Invoice`.supplier = %(supplier)s"),
+ ("item_code", " and `tabPurchase Invoice Item`.item_code = %(item_code)s"),
+ ("from_date", " and `tabPurchase Invoice`.posting_date>=%(from_date)s"),
+ ("to_date", " and `tabPurchase Invoice`.posting_date<=%(to_date)s"),
("mode_of_payment", " and ifnull(mode_of_payment, '') = %(mode_of_payment)s")):
if filters.get(opts[0]):
conditions += opts[1]
@@ -104,20 +104,29 @@
def get_items(filters, additional_query_columns):
conditions = get_conditions(filters)
match_conditions = frappe.build_match_conditions("Purchase Invoice")
+
+ if match_conditions:
+ match_conditions = " and {0} ".format(match_conditions)
+
if additional_query_columns:
additional_query_columns = ', ' + ', '.join(additional_query_columns)
return frappe.db.sql("""
select
- pi_item.name, pi_item.parent, pi.posting_date, pi.credit_to, pi.company,
- pi.supplier, pi.remarks, pi.base_net_total, pi_item.item_code, pi_item.item_name,
- pi_item.item_group, pi_item.project, pi_item.purchase_order, pi_item.purchase_receipt,
- pi_item.po_detail, pi_item.expense_account, pi_item.stock_qty, pi_item.stock_uom,
- pi_item.base_net_rate, pi_item.base_net_amount,
- pi.supplier_name, pi.mode_of_payment {0}
- from `tabPurchase Invoice` pi, `tabPurchase Invoice Item` pi_item
- where pi.name = pi_item.parent and pi.docstatus = 1 %s %s
- order by pi.posting_date desc, pi_item.item_code desc
+ `tabPurchase Invoice Item`.`name`, `tabPurchase Invoice Item`.`parent`,
+ `tabPurchase Invoice`.posting_date, `tabPurchase Invoice`.credit_to, `tabPurchase Invoice`.company,
+ `tabPurchase Invoice`.supplier, `tabPurchase Invoice`.remarks, `tabPurchase Invoice`.base_net_total, `tabPurchase Invoice Item`.`item_code`,
+ `tabPurchase Invoice Item`.`item_name`, `tabPurchase Invoice Item`.`item_group`,
+ `tabPurchase Invoice Item`.`project`, `tabPurchase Invoice Item`.`purchase_order`,
+ `tabPurchase Invoice Item`.`purchase_receipt`, `tabPurchase Invoice Item`.`po_detail`,
+ `tabPurchase Invoice Item`.`expense_account`, `tabPurchase Invoice Item`.`stock_qty`,
+ `tabPurchase Invoice Item`.`stock_uom`, `tabPurchase Invoice Item`.`base_net_rate`,
+ `tabPurchase Invoice Item`.`base_net_amount`,
+ `tabPurchase Invoice`.supplier_name, `tabPurchase Invoice`.mode_of_payment {0}
+ from `tabPurchase Invoice`, `tabPurchase Invoice Item`
+ where `tabPurchase Invoice`.name = `tabPurchase Invoice Item`.`parent` and
+ `tabPurchase Invoice`.docstatus = 1 %s %s
+ order by `tabPurchase Invoice`.posting_date desc, `tabPurchase Invoice Item`.item_code desc
""".format(additional_query_columns) % (conditions, match_conditions), filters, as_dict=1)
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 eb50022..9892e03 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
@@ -93,37 +93,49 @@
conditions = ""
for opts in (("company", " and company=%(company)s"),
- ("customer", " and si.customer = %(customer)s"),
- ("item_code", " and si_item.item_code = %(item_code)s"),
- ("from_date", " and si.posting_date>=%(from_date)s"),
- ("to_date", " and si.posting_date<=%(to_date)s")):
+ ("customer", " and `tabSales Invoice`.customer = %(customer)s"),
+ ("item_code", " and `tabSales Invoice Item`.item_code = %(item_code)s"),
+ ("from_date", " and `tabSales Invoice`.posting_date>=%(from_date)s"),
+ ("to_date", " and `tabSales Invoice`.posting_date<=%(to_date)s")):
if filters.get(opts[0]):
conditions += opts[1]
if filters.get("mode_of_payment"):
conditions += """ and exists(select name from `tabSales Invoice Payment`
- where parent=si.name
- and ifnull(`tabSales Invoice Payment`.mode_of_payment, '') = %(mode_of_payment)s)"""
+ where parent=si.name
+ and ifnull(`tabSales Invoice Payment`.mode_of_payment, '') = %(mode_of_payment)s)"""
return conditions
def get_items(filters, additional_query_columns):
+ conditions = get_conditions(filters)
+ match_conditions = frappe.build_match_conditions("Sales Invoice")
+
+ if match_conditions:
+ match_conditions = " and {0} ".format(match_conditions)
+
if additional_query_columns:
additional_query_columns = ', ' + ', '.join(additional_query_columns)
- conditions = get_conditions(filters)
return frappe.db.sql("""
select
- si_item.name, si_item.parent, si.posting_date, si.debit_to, si.project,
- si.customer, si.remarks, si.territory, si.company, si.base_net_total,
- si_item.item_code, si_item.item_name, si_item.item_group, si_item.sales_order,
- si_item.delivery_note, si_item.income_account, si_item.cost_center,
- si_item.stock_qty, si_item.stock_uom, si_item.base_net_rate, si_item.base_net_amount,
- si.customer_name, si.customer_group, si_item.so_detail, si.update_stock {0}
- from `tabSales Invoice` si, `tabSales Invoice Item` si_item
- where si.name = si_item.parent and si.docstatus = 1 %s
- order by si.posting_date desc, si_item.item_code desc
- """.format(additional_query_columns or '') % conditions, filters, as_dict=1)
+ `tabSales Invoice Item`.name, `tabSales Invoice Item`.parent,
+ `tabSales Invoice`.posting_date, `tabSales Invoice`.debit_to,
+ `tabSales Invoice`.project, `tabSales Invoice`.customer, `tabSales Invoice`.remarks,
+ `tabSales Invoice`.territory, `tabSales Invoice`.company, `tabSales Invoice`.base_net_total,
+ `tabSales Invoice Item`.item_code, `tabSales Invoice Item`.item_name,
+ `tabSales Invoice Item`.item_group, `tabSales Invoice Item`.sales_order,
+ `tabSales Invoice Item`.delivery_note, `tabSales Invoice Item`.income_account,
+ `tabSales Invoice Item`.cost_center, `tabSales Invoice Item`.stock_qty,
+ `tabSales Invoice Item`.stock_uom, `tabSales Invoice Item`.base_net_rate,
+ `tabSales Invoice Item`.base_net_amount, `tabSales Invoice`.customer_name,
+ `tabSales Invoice`.customer_group, `tabSales Invoice Item`.so_detail,
+ `tabSales Invoice`.update_stock {0}
+ from `tabSales Invoice`, `tabSales Invoice Item`
+ where `tabSales Invoice`.name = `tabSales Invoice Item`.parent
+ and `tabSales Invoice`.docstatus = 1 %s %s
+ order by `tabSales Invoice`.posting_date desc, `tabSales Invoice Item`.item_code desc
+ """.format(additional_query_columns or '') % (conditions, match_conditions), filters, as_dict=1)
def get_delivery_notes_against_sales_order(item_list):
so_dn_map = frappe._dict()