Merge pull request #36872 from deepeshgarg007/sales_purchase_register_query
fix: Sales/Purchase register showing duplicate records
diff --git a/erpnext/accounts/report/purchase_register/purchase_register.py b/erpnext/accounts/report/purchase_register/purchase_register.py
index c7b7e2f..ca8b9f0 100644
--- a/erpnext/accounts/report/purchase_register/purchase_register.py
+++ b/erpnext/accounts/report/purchase_register/purchase_register.py
@@ -10,8 +10,8 @@
from erpnext.accounts.party import get_party_account
from erpnext.accounts.report.utils import (
+ apply_common_conditions,
get_advance_taxes_and_charges,
- get_conditions,
get_journal_entries,
get_opening_row,
get_party_details,
@@ -378,11 +378,8 @@
def get_invoices(filters, additional_query_columns):
pi = frappe.qb.DocType("Purchase Invoice")
- invoice_item = frappe.qb.DocType("Purchase Invoice Item")
query = (
frappe.qb.from_(pi)
- .inner_join(invoice_item)
- .on(pi.name == invoice_item.parent)
.select(
ConstantColumn("Purchase Invoice").as_("doctype"),
pi.name,
@@ -402,23 +399,39 @@
.where((pi.docstatus == 1))
.orderby(pi.posting_date, pi.name, order=Order.desc)
)
+
if additional_query_columns:
for col in additional_query_columns:
query = query.select(col)
+
if filters.get("supplier"):
query = query.where(pi.supplier == filters.supplier)
- query = get_conditions(
+
+ query = get_conditions(filters, query, "Purchase Invoice")
+
+ query = apply_common_conditions(
filters, query, doctype="Purchase Invoice", child_doctype="Purchase Invoice Item"
)
+
if filters.get("include_payments"):
party_account = get_party_account(
"Supplier", filters.get("supplier"), filters.get("company"), include_advance=True
)
query = query.where(pi.credit_to.isin(party_account))
+
invoices = query.run(as_dict=True)
return invoices
+def get_conditions(filters, query, doctype):
+ parent_doc = frappe.qb.DocType(doctype)
+
+ if filters.get("mode_of_payment"):
+ query = query.where(parent_doc.mode_of_payment == filters.mode_of_payment)
+
+ return query
+
+
def get_payments(filters):
args = frappe._dict(
account="credit_to",
diff --git a/erpnext/accounts/report/sales_register/sales_register.py b/erpnext/accounts/report/sales_register/sales_register.py
index 35d8d16..d3fc373 100644
--- a/erpnext/accounts/report/sales_register/sales_register.py
+++ b/erpnext/accounts/report/sales_register/sales_register.py
@@ -11,8 +11,8 @@
from erpnext.accounts.party import get_party_account
from erpnext.accounts.report.utils import (
+ apply_common_conditions,
get_advance_taxes_and_charges,
- get_conditions,
get_journal_entries,
get_opening_row,
get_party_details,
@@ -415,14 +415,8 @@
def get_invoices(filters, additional_query_columns):
si = frappe.qb.DocType("Sales Invoice")
- invoice_item = frappe.qb.DocType("Sales Invoice Item")
- invoice_payment = frappe.qb.DocType("Sales Invoice Payment")
query = (
frappe.qb.from_(si)
- .inner_join(invoice_item)
- .on(si.name == invoice_item.parent)
- .left_join(invoice_payment)
- .on(si.name == invoice_payment.parent)
.select(
ConstantColumn("Sales Invoice").as_("doctype"),
si.name,
@@ -447,18 +441,36 @@
.where((si.docstatus == 1))
.orderby(si.posting_date, si.name, order=Order.desc)
)
+
if additional_query_columns:
for col in additional_query_columns:
query = query.select(col)
+
if filters.get("customer"):
query = query.where(si.customer == filters.customer)
- query = get_conditions(
+
+ query = get_conditions(filters, query, "Sales Invoice")
+ query = apply_common_conditions(
filters, query, doctype="Sales Invoice", child_doctype="Sales Invoice Item"
)
+
invoices = query.run(as_dict=True)
return invoices
+def get_conditions(filters, query, doctype):
+ parent_doc = frappe.qb.DocType(doctype)
+ if filters.get("owner"):
+ query = query.where(parent_doc.owner == filters.owner)
+
+ if filters.get("mode_of_payment"):
+ payment_doc = frappe.qb.DocType("Sales Invoice Payment")
+ query = query.inner_join(payment_doc).on(parent_doc.name == payment_doc.parent)
+ query = query.where(payment_doc.mode_of_payment == filters.mode_of_payment).distinct()
+
+ return query
+
+
def get_payments(filters):
args = frappe._dict(
account="debit_to",
diff --git a/erpnext/accounts/report/utils.py b/erpnext/accounts/report/utils.py
index 0753fff..9f96449 100644
--- a/erpnext/accounts/report/utils.py
+++ b/erpnext/accounts/report/utils.py
@@ -256,7 +256,8 @@
)
.orderby(je.posting_date, je.name, order=Order.desc)
)
- query = get_conditions(filters, query, doctype="Journal Entry", payments=True)
+ query = apply_common_conditions(filters, query, doctype="Journal Entry", payments=True)
+
journal_entries = query.run(as_dict=True)
return journal_entries
@@ -284,28 +285,17 @@
)
.orderby(pe.posting_date, pe.name, order=Order.desc)
)
- query = get_conditions(filters, query, doctype="Payment Entry", payments=True)
+ query = apply_common_conditions(filters, query, doctype="Payment Entry", payments=True)
payment_entries = query.run(as_dict=True)
return payment_entries
-def get_conditions(filters, query, doctype, child_doctype=None, payments=False):
+def apply_common_conditions(filters, query, doctype, child_doctype=None, payments=False):
parent_doc = frappe.qb.DocType(doctype)
if child_doctype:
child_doc = frappe.qb.DocType(child_doctype)
- if parent_doc.get_table_name() == "tabSales Invoice":
- if filters.get("owner"):
- query = query.where(parent_doc.owner == filters.owner)
- if filters.get("mode_of_payment"):
- payment_doc = frappe.qb.DocType("Sales Invoice Payment")
- query = query.where(payment_doc.mode_of_payment == filters.mode_of_payment)
- if not payments:
- if filters.get("brand"):
- query = query.where(child_doc.brand == filters.brand)
- else:
- if filters.get("mode_of_payment"):
- query = query.where(parent_doc.mode_of_payment == filters.mode_of_payment)
+ join_required = False
if filters.get("company"):
query = query.where(parent_doc.company == filters.company)
@@ -320,13 +310,26 @@
else:
if filters.get("cost_center"):
query = query.where(child_doc.cost_center == filters.cost_center)
+ join_required = True
if filters.get("warehouse"):
query = query.where(child_doc.warehouse == filters.warehouse)
+ join_required = True
if filters.get("item_group"):
query = query.where(child_doc.item_group == filters.item_group)
+ join_required = True
+
+ if not payments:
+ if filters.get("brand"):
+ query = query.where(child_doc.brand == filters.brand)
+ join_required = True
+
+ if join_required:
+ query = query.inner_join(child_doc).on(parent_doc.name == child_doc.parent)
+ query = query.distinct()
if parent_doc.get_table_name() != "tabJournal Entry":
query = filter_invoices_based_on_dimensions(filters, query, parent_doc)
+
return query